Catching a silently-dropped payments webhook before finance does

A Friday deploy changed how a webhook handler parsed Stripe's payload. The handler kept returning 200 — Stripe was satisfied, so no retries fired — and the insert into payments quietly stopped. Six hours of successful charges were never recorded. Support found out when a customer asked why their receipt hadn't arrived.

The team had checks. Constraints on the tables, dbt tests on the models, schema assertions in the pipeline. None of them fired, and most of them couldn't have. Where a check belongs — at write time, at transform time, or continuously against production — is its own question, and the layers pillar lays out all three. This post takes the third one, the continuous layer, and walks this single failure through it end to end: the symptom, why the other two layers were blind, and the query — built up in stages — that would have caught the gap in about twenty minutes.

The symptom: a number that was wrong for six hours

Nothing looked broken. The app was up. The dashboards loaded. The payments table was there, queryable, full of rows — just none from the last six hours. A total charge count for the day was low, but not alarmingly so on a Friday, and no one was watching that number by the hour. The first real signal came from outside the system entirely: a customer, then support, then finance asking why the day's revenue didn't reconcile against Stripe's own dashboard.

That's the signature of this class of failure. It isn't an incorrect row — it's a row that should have existed and didn't. A check that waits for a row to inspect has nothing to inspect when the row never comes. The failure is an absence, and absence is invisible to anything looking at the rows in front of it.

Why write-time and transform-time both passed

The constraints passed because the handler wrote no bad row — it wrote no row at all, and a foreign key or CHECK can only judge a row that arrives. There was nothing to reject.

The transform tests passed for a subtler reason: they never ran against this. The payments table is written by the backend directly; no dbt model transforms it, so it was never in the DAG to be tested. And even the tables that are tested only get tested when a run fires — a source can break a minute after the morning run and nothing looks again until tomorrow. Both layers sit inside the system that produces the data. This failure was in the writing of the data, before any pipeline, on a table no pipeline touched. Neither layer was positioned to see it.

The continuous check that would have caught it

The continuous layer runs a query against production on its own schedule, independent of the handler that broke. Getting that query right takes three passes, because the version you'd write first has a problem worth understanding.

The naive version. Count recent payments; alert on zero.

-- Toy schema: payments(id, created_at, region, status). Pattern, not a check
-- to paste — your table, your window, your tolerance.
SELECT COUNT(*) AS payments_last_hour
FROM payments
WHERE created_at > NOW() - INTERVAL '1 hour'
  AND status = 'succeeded';
-- Alert if 0

This fires every quiet night. At 3am on a Sunday, zero payments in an hour may be entirely normal for your volume. Widen the window until it stops false-alarming and you've widened your detection delay to most of a day. A hard threshold can't tell "quiet because it's Sunday" from "quiet because the handler is dropping rows."

The transferable fix: baseline against the entity's own recent history. Stop comparing to a fixed number and compare to what this same slice normally does, using a window function to carry the trailing baseline.

-- Toy schema: payments(created_at, region, status). Pattern, not a check to
-- paste — your buckets, columns, and window differ.
WITH bucketed AS (
  SELECT date_trunc('hour', created_at) AS bucket,
         region,
         COUNT(*) AS n
  FROM payments
  WHERE status = 'succeeded'
    AND created_at > NOW() - INTERVAL '8 days'
  GROUP BY 1, 2
),
baseline AS (
  SELECT bucket, region, n,
         avg(n) OVER (
           PARTITION BY region, EXTRACT(hour FROM bucket)
           ORDER BY bucket
           ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING
         ) AS trailing_avg
  FROM bucketed
)
SELECT bucket, region, n, trailing_avg
FROM baseline
WHERE bucket = date_trunc('hour', NOW())
  AND n < trailing_avg * 0.5;   -- your threshold, your tolerance

Three mechanics carry to any table. A window function builds the baseline from the slice's own recent history, so "normal" is learned from the data instead of hard-coded and going stale. PARTITION BY region — the entity — means one region dropping to zero still fires even while the total looks fine; an aggregate count over the whole table is exactly where a partial drop hides. And a relative threshold (< trailing_avg * 0.5) instead of an absolute one is what lets the check run at 3am on a Sunday without paging you: a quiet hour last week makes the baseline low too. Bucket by the hour and run it every few minutes for payments, where an hour of latency is expensive; bucket by the day for a slower feed. Swap region for tenant_id, source, or whatever entity a partial drop would hide behind in your data.

The strongest version, for payments specifically: reconcile against a second source of truth. You have one that most tables don't — the provider's own webhooks. If you log webhook receipts (one insert in the handler before any business logic), you can ask the sharpest possible question: which charges the provider confirmed never became payment rows?

-- Toy schema: webhook_events(external_id, event_type, received_at),
-- payments(external_id, ...). Pattern, not a check to paste.
SELECT w.external_id, w.received_at
FROM webhook_events w
LEFT JOIN payments p ON p.external_id = w.external_id
WHERE w.event_type = 'charge.succeeded'
  AND w.received_at BETWEEN NOW() - INTERVAL '24 hours'
                        AND NOW() - INTERVAL '10 minutes'
  AND p.id IS NULL;
-- Alert if any rows come back

This states a relationship a constraint can't — "every confirmed charge has a payment row" — because the missing row isn't there to constrain. The 10-minute exclusion at the recent edge keeps you from racing your own handler: a charge received 30 seconds ago may simply not be processed yet. Run it every 5–15 minutes and require two consecutive failures before paging, which absorbs deploys and brief queue backups for the price of a few minutes' delay. This is the check that turns six silent hours into about twenty minutes — and it only works because something looked at the table from outside, on a schedule the broken handler couldn't take down with it.

Threshold reasoning: why the hard count had to go

The whole progression is one idea: a fixed threshold encodes an assumption about your traffic that stops being true the first time traffic changes. < 1000 fires every quiet Sunday and stays silent during a partial outage on a busy Monday. A baseline-relative threshold moves the goalpost with the slice's own rhythm, so the only thing that trips it is a genuine departure from that slice's normal — which is the definition of the failure you actually want paged on. The reconciliation check goes one better by needing no numeric threshold at all: the answer is a set of specific charges that should have a row and don't, and any row in that set is real.

What the alert has to contain to be actionable

A check that fires at 3am is only worth having if the person it wakes can act on it half-asleep. Three things make the difference:

• An owner. Not a channel. A payments gap that lands in #alerts belongs to everyone and so to no one; it has to be an item a specific person has taken.
• The query and its result. "Payments low" sends someone digging. "42 charge.succeeded events since 14:00 have no payment row — here they are, here's the SQL" starts them at the diagnosis.
• Last-good. When did this slice last look normal? "Fine until 14:07" points straight at the 14:00 deploy. Without it, the first ten minutes go to establishing when the bleeding started.

Adapt it, don't paste it

None of these is a check you can paste and expect to work. Your payments table isn't ours; your quiet days, your acceptable drift, your entity of interest are all different. What transfers is the shape: baseline the entity against its own recent history, group so partial failures can't hide, tune the threshold to your traffic, and — when you have a second source of truth like a webhook log — reconcile the two directly. That's the reasoning. The SQL is just where it lands.

Every query here runs anywhere — psql, a cron job, or a tool that handles the scheduling. If you'd rather not stand up cron plus a SQL runner plus a webhook and maintain the baseline logic yourself, that's what we built Alertee for: describe the check in plain English, we generate the SQL against your actual schema, and you review and edit it before it runs — then it schedules the check, owns the incident, and keeps the history. Start free. For the full map of where checks belong, the layers pillar covers all three; if you're choosing what to run this on, the data quality tools comparison goes vendor by vendor.