How to call a webhook from a SQL query

Some events need to reach a system that isn’t a chat or a help desk: revoke access when a subscription lapses, send a flagged order to a fraud service, tell your CRM an account crossed a threshold. Rowfire, which is open source, turns a SQL query into those calls: one HTTP request for each new row, built from the row’s columns, at most as often as you allow per key.

1. The query

A trigger is one read-only SELECT on PostgreSQL, MySQL or MariaDB, with a clock and a key:

SELECT s.account_id,
       a.name AS account,
       s.plan,
       s.ended_at
FROM subscriptions s
JOIN accounts a ON a.id = s.account_id
WHERE s.status = 'lapsed'

The clock is ended_at and the key is account_id. A rule says how often one key may fire: once ever, once per day, week or month, or every nth time. Once per account suits this one.

2. Your API as an integration

An integration is your API’s base URL and how it authenticates: a bearer token, HTTP Basic, or an API key in a header you name. You set it up under Integrations in the UI, or over Rowfire’s API:

curl -X POST localhost:8000/api/integrations -H 'content-type: application/json' \
  -d '{"name":"billing-api","base_url":"https://api.example.com/v2",
       "auth_kind":"header","auth_header_name":"X-API-Key",
       "auth_credential":"api_key","credentials":{"api_key":"k-…"}}'

Credentials are encrypted and never come back from any endpoint. The token is added at the moment of the call, so the copy of each request that Rowfire records and shows never contains it.

3. The call

An action is one REST call: a method, a path and a body, where any value can be a column from the row with {{ column }}:

{
  "method": "POST",
  "path": "/accounts/{{ account_id }}/revoke",
  "body": { "reason": "subscription_lapsed", "plan": "{{ plan }}" }
}

Templates are substitution only, with no logic. A template that names a column the row doesn’t have fails that delivery rather than sending an empty value.

4. See the requests before they’re sent

A backtest replays the rule over past days and shows how many calls it would have made, with example rows. Shadow mode then renders every request against live data and records it, method, URL and body, without sending it. Promote the rule when the requests look right. A new trigger starts at now, so switching it on doesn’t replay history into your API.

Good to know

Triggers are polled, within a minute by default, so this suits reactions, not calls that must happen inside the request that caused them. A self-hosted Rowfire can call any http or https address, including ones on your private network.

For use cases built this way, see revoke access when a subscription lapses and send flagged orders to a fraud service. The same trigger can also post to Slack: how to send a Slack alert from a SQL query.