Database alerts that don't spam: dedup keys, backtests and shadow mode

Every team I've worked with keeps the same backlog, and it never shrinks:

• Billing: "Ping us when a card is declined. But not three times for one invoice."
• Sales: "Post in #sales when an Enterprise account signs up."
• Customer success: "Open a ticket when a trial ends in three days and never activated."
• Lifecycle: "Send trial_stalled to Braze so the rescue campaign can start."

The data for every one of these is already in the database. What's missing is the plumbing: application code that notices the moment, deduplicates it, renders a message and calls an API. Each request becomes a ticket, each ticket waits behind the roadmap, and most of them never get built.

I built RowFire to take that plumbing off engineering's plate. This post covers how it works, the design choices behind it, and what it deliberately doesn't do.

The split: who knows what

The idea that shaped everything else is that these automations have two owners who know different things.

Whoever knows the schema knows what "a declined charge" means in SQL, and where duplicates come from. So they write a trigger: one SELECT, plus two declarations.

SELECT a.id AS account_id, a.name AS account, a.owner_email,
       pa.invoice_no, pa.amount, pa.decline_code, pa.attempted_at
FROM   payment_attempts pa
JOIN   accounts a ON a.id = pa.account_id
WHERE  pa.status = 'declined'

• the clock: which column places a row in time (attempted_at)
• the key: which columns make a row "one thing" (account_id)

Whoever owns the customer experience decides how often to act and what to send. So they write a rule: which trigger, how often one key may fire, and an action.

• once ever, per key
• once per day, week or month, per key
• every nth time

Many rules can subscribe to one trigger. It's polled once and its rows are fanned out, so #billing can get a heads-up once a day per account while Zendesk gets one ticket per account per week, both from the same query.

Deduplication is the product

"Once per account per week" sounds like a detail. It's most of the value. A card that fails, retries and fails again is one billing problem, and the customer should hear about it once.

That only works if "once" really means once, including when several workers poll at the same time or one crashes halfway through. So a send happens if and only if an INSERT into the fire table actually inserted a row:

INSERT INTO fire (rule_name, dedup_key, dedup_bucket, event_time, payload, ...)
VALUES (...)
ON CONFLICT ON CONSTRAINT uq_fire_dedup DO NOTHING
RETURNING id

No row returned, no send. Every policy maps onto one unique constraint: the bucket is empty for "once ever", the period start for "once per week", and the occurrence number for "every nth". The database settles races under its unique index, rather than application code checking first and inserting second.

We measured it rather than assuming it. Racing 16 workers at the same row: SELECT-then-INSERT lets 16 of them send. ON CONFLICT lets 1.

Backtest before anything sends

A new automation is a guess about how often something happens. Guess wrong and you've posted 400 messages into #sales or opened a ticket for every retry.

So every rule can be backtested: replayed over the last N days, without sending anything. In the demo, "a charge was declined, at most once a week per account" over 90 days:

• 211 times it happened
• 92 messages it would have sent
• 119 repeats held back

The backtest also shows example rows, which is where a subtly wrong query gives itself away. The obvious "Enterprise sign-up" query forgot that internal QA tenants are Enterprise too. Backtested side by side, the naive version would have posted 38 announcements; the corrected one, 26. The example rows showed exactly which accounts made up the difference.

A backtest has a limit worth stating plainly: it evaluates rows as they are now. An order that was later refunded and no longer matches the query is invisible, so treat the number as a floor. RowFire says so in its output rather than hoping you read the docs.

Shadow mode, then promote

Once the numbers look right, a rule goes live in shadow mode. It polls for real and renders every real request (URL, headers, body), records it, and sends nothing. When the shadow log looks right, you promote it to live. Editing a live rule sends it back to shadow.

There's also a single Stop everything switch, and every poll is recorded, so "nothing happened" is distinguishable from "nothing ran".

Design choices you may want to argue with

Polling a clock column instead of reading the change log. A trigger is a read-only query run on a schedule, within a minute by default. There's no replication slot, no CDC pipeline and no extra database permissions, and the backtest comes almost for free, because the same query already works over history. RowFire wraps your query with the time window, WHERE t.attempted_at >= :since AND t.attempted_at < :until, rather than trusting it to filter itself. The cost is latency: this is for reactions (tickets, campaigns, follow-ups), not messages that must arrive in a second.

Read-only, enforced three ways. Read-only database sessions, a parser that accepts only a single SELECT, and whatever account you grant it. Queries are re-rendered from the validated syntax tree, with a statement timeout and a row cap.

Templates are substitution, not a language. A message template is {{ column }} and {{ a.b }} and nothing else: no filters, no loops, no expressions. These templates turn customer data into outbound HTTP requests, and template sandboxes are a well-known escape route. A template that names a missing column fails the delivery rather than sending a message with a hole in it.

Integrations are data, not code. Slack, Zendesk and Braze ship as YAML files describing a base URL, an auth shape and a REST call per action. Your own API works the same way, with no code. Credentials are encrypted at rest, added only at the moment of sending, and never returned by any endpoint.

What it isn't

• Not ETL or reverse ETL. Reverse ETL keeps a field in sync with a column. RowFire emits moments: this just became true for this key, act once.
• Not streaming. It polls. If you need sub-second delivery, emit the event from your app.
• Not hosted. It's self-hosted and the UI is local-only, because the whole point is that a production DSN never leaves your network.

Try it

• Live demo, no signup: demo.rowfire.com. You get a private workspace on a sample SaaS company with a Postgres app database and a MySQL support desk. Build a rule, backtest it, turn it on and watch it deliver to a demo inbox.
• Run it yourself: docker compose up -d, and it opens at a Get started page with sample data. Quickstart.
• Source: github.com/rowfirehq/rowfire, AGPL-3.0, PostgreSQL, MySQL and MariaDB. This is v0.1.0.

The question I most want answered: what's missing before you'd point this at a real database? Tell me in the discussions or the comments.