Overview

The constraint above is the entire brief: webhooks from a third-party provider retry on timeout, duplicate on the provider's own infrastructure hiccups, and arrive in whatever order the provider's internal queues happen to flush them, and none of that is something an integrator gets to negotiate away. On top of that unpredictable input pattern sits a second constraint that has nothing to do with the provider: volume is high enough that a naive "check if we've seen this ID, then insert" pattern is itself a race under concurrent writers. And any manual process for catching what slips through has to scale down to effectively zero, because nobody is going to spend a day a week reading logs looking for a webhook that never arrived. Everything below exists to answer one question without a human in the loop: did every event the provider sent actually land, exactly once, in a form the rest of the system can trust?

At-most-once ingestion via external-ID upserts

Every inbound request is verified before it's trusted with anything else. The provider signs its payload with HMAC, and ingestion recomputes that signature against the raw request body before touching the content at all. A request that fails verification is rejected outright — it never reaches the queue, and it never gets a chance to pollute the dedup logic with a forged external ID. Only a verified event proceeds.

From there the core mechanism is deliberately simple. The write into the durable Postgres-backed queue is an upsert, keyed on the field the provider itself guarantees is stable across redeliveries — its external ID. It is not "check if this ID exists, then decide whether to insert"; it is a single atomic INSERT ... ON CONFLICT (external_id) DO UPDATE that either creates the row or reconciles it against the row that's already there, in one round trip, with the database's own locking doing the work a hand-rolled check-then-act would get wrong under concurrency.

INSERT INTO webhook_events (external_id, provider, payload, received_at)
VALUES ($1, $2, $3, now())
ON CONFLICT (external_id)
DO UPDATE SET payload = EXCLUDED.payload, received_at = EXCLUDED.received_at
WHERE webhook_events.processed_at IS NULL;

Upsert keyed on the provider's external ID

Redelivery of the same event — whether a legitimate retry after a slow response, or the provider's own at-least-once guarantee firing twice — lands on the same row every time. The ID is the row's identity, not an incidental column, so "have we seen this?" isn't a separate question the application has to ask; it's the same statement that does the writing.

Why not a separate dedup/idempotency-key table

The obvious alternative is a bolt-on idempotency layer: a separate table of seen IDs, checked before the real write proceeds, with application code responsible for keeping the two in sync. It's tempting because it feels safely decoupled from the domain table.

It's also strictly worse for a system that has to run at high write volume without a human checking on it. A separate table means two writes instead of one, and those two writes now have to stay consistent with each other under concurrent access — either wrapped in a transaction with its own contention profile, or exposed to a window where the dedup record exists and the real write hasn't landed yet, or the reverse. Every failure mode that "without manual detective work" is supposed to rule out either has to be handled twice, or, more realistically, drifts: the dedup table says one thing, the domain table says another, and now there's a class of bug invisible until reconciliation stumbles onto it anyway. Upserting directly against the domain table collapses that surface to zero — there is only one table, one write, and exactly one thing that can be true about whether an event was seen.

Concurrent ingestion with SKIP LOCKED workers

Upserts solve correctness for a single write; they don't solve throughput. High volume means many workers pulling from the same queue at once, and the naive way to hand out work — SELECT ... FOR UPDATE without qualification — makes every worker queue up behind whichever one locked a row first, turning what should be parallel work into a line.

SKIP LOCKED changes what happens when a worker's claim query encounters a row another worker already has locked. Instead of blocking until that lock releases, it skips the row and takes the next one that's actually free. A worker's claim query becomes something close to SELECT ... FROM webhook_events WHERE processed_at IS NULL ORDER BY received_at FOR UPDATE SKIP LOCKED LIMIT 50, and any number of workers can run that exact query at the same moment without ever contending, because none of them ever waits on another — each one simply gets handed whatever's still unclaimed.

That's what makes "add another worker" an actual scaling strategy instead of just adding another process that spends most of its life blocked. It comes with a specific cost: ORDER BY received_at is a preference for which row gets claimed next, not a promise about the order in which rows finish processing, because once more than one worker is pulling batches concurrently, whichever worker finishes its batch first processes its rows first, regardless of which batch was claimed a moment earlier. Anything downstream that assumes processing order mirrors write order has to provide that guarantee itself; this layer doesn't.

Reconciliation passes

Upserts and SKIP LOCKED workers get an event written and processed; neither one proves it stayed correct. Reconciliation is the layer that goes back and checks — a pass that re-reads what's already in the durable queue against what the rest of the system did with it, looking for rows that were written but never marked processed, or processed but in a state that doesn't match what a fresh read of the payload implies.

The pass is dual-triggered. It runs on a fixed schedule regardless of anything else happening, and it runs again whenever another layer — most often an anomaly check — raises a signal that something looks off. The schedule catches the slow, silent kind of drift that nothing else would notice in time; the triggered runs catch the kind that's urgent enough not to wait for the next scheduled pass. Neither trigger depends on the other, so a bug in one doesn't take down the other's coverage.

Because a reconciliation pass reads and potentially rewrites a wide swath of the table, only one instance of it is allowed to run at a time. That's enforced with a Postgres advisory lock taken at the start of the pass and released at the end, rather than a row lock scoped to any particular record. A second trigger firing while a pass is already running finds the lock held and exits immediately instead of starting a redundant, contending pass.

Diagram: the reconciliation loop — an inbound webhook is upserted by external ID into the durable queue, a reconciliation pass periodically re-verifies what was written, a tombstone anti-join scans for gaps in the provider's external-ID sequence, any gap triggers a backfill, and anomaly checks continuously watch latency and orphaned records for drift.

None of this makes any single pass load-bearing on its own. A reconciliation run that misses something this cycle isn't a failure, because the next trigger — scheduled or signaled — gets another look.

Tombstone gap detection

Reconciliation checks what's already in the queue against what happened to it; it doesn't catch an event that never arrived at all. If a provider's own delivery attempt is lost before it reaches ingestion — a network partition, an outage on the provider's side, a webhook endpoint that was briefly down — there's no row to reconcile, because nothing was ever written. Catching that requires a different technique: comparing the external IDs actually present in the queue against the sequence the provider implies those IDs should form, and flagging what's missing.

That comparison is an anti-join — a query that returns exactly the IDs on one side that have no matching row on the other.

SELECT expected.external_id
FROM generate_external_id_sequence($1, $2) AS expected(external_id)
LEFT JOIN webhook_events e ON e.external_id = expected.external_id
WHERE e.external_id IS NULL
  AND expected.external_id NOT IN (SELECT external_id FROM webhook_tombstones);

Anti-join: IDs the sequence implies should exist but don't

The tombstones table is what keeps this from re-flagging the same false positive forever. Not every gap the anti-join finds is real data loss — some IDs in a provider's sequence are legitimately never sent, a draft that never fired, a test event filtered upstream — and once a flagged gap has been investigated and confirmed as one of those, it's recorded as a tombstone so the next anti-join run skips it instead of re-raising the same non-issue on every backfill cycle. A gap that isn't tombstoned and doesn't resolve itself on a retry triggers a backfill: a request back to the provider's API for that specific external ID, which either produces the missing event — proving it really was lost in transit, and repairing it — or comes back empty, at which point it gets tombstoned and the loop moves on.

Anomaly checks

The layers above are all reactive to a specific, named failure: a duplicate, a gap in a sequence, a stalled reconciliation pass. Anomaly checks exist for the failures nobody named in advance. The suite is more than thirty automated checks, each one comparing a metric against its own expected range and raising a signal when it drifts outside it. Most of them read from materialized views refreshed on a schedule rather than from live tables directly, so running the checks doesn't add read pressure to the same tables ingestion is busy writing into.

Two families cover most of the surface. Latency checks watch the time between an event being written and being marked processed, because a queue that's silently falling behind looks fine in isolation — every row eventually gets processed — right up until "eventually" is unacceptable, and the only early warning is the gap between received and processed widening. Referential checks watch for orphaned records: rows that reference an external ID with no corresponding entry where one should exist, often the first visible symptom of a bug three layers away from where the orphan actually shows up.

Any one of these checks firing is also one of the two reconciliation triggers described above — an anomaly is itself a reason to re-run reconciliation immediately rather than wait for the schedule. That's the same redundancy principle running through every layer in this system: no individual check has to be exhaustive, because the checks overlap in what they'd catch, and what one misses, another one two layers away is likely to flag anyway. The practical effect is that "did we lose data?" stopped being a question that required someone to go looking. It's a dashboard someone can glance at, because the system is continuously asking the question of itself.

Tradeoffs and limitations

None of this is free, and being honest about what it doesn't solve is part of the design:

• Upserting on external ID only protects against redelivery of the same ID. A provider that mints a new ID for what is, semantically, the same logical event sails straight through as a fresh row; nothing here recognizes that on its own.
• SKIP LOCKED buys concurrency by giving up strict ordering. Anything downstream that assumes processing order matches enqueue order needs to provide that guarantee itself — this layer doesn't.
• Reconciliation passes read, and sometimes rewrite, a wide slice of the table on every run. At high enough volume, that's periodic load competing with the same ingestion writes it exists to protect.
• A tombstone anti-join scales with the size of the ID sequence window being scanned, not with the number of actual gaps. A provider with sparse or very large ID spaces makes the comparison itself expensive unless the window is bounded.
• Anomaly-check thresholds are tuned, not derived. Too tight, and the suite trains people to ignore its own alerts; too loose, and it misses the drift it exists to catch — tuning is ongoing, not a one-time setup step.
• All of this protects against loss and duplication of events that were signed and delivered correctly. It does not protect against a provider sending a valid, correctly-signed event that is simply wrong — corrupted or semantically incorrect data arrives exactly once, verified and reconciled, and still wrong.

References and further reading

The techniques here are standard Postgres and webhook-design practice, not novel:

• PostgreSQL — INSERT ... ON CONFLICT
• PostgreSQL — SELECT ... FOR UPDATE / SKIP LOCKED
• PostgreSQL — Materialized Views
• PostgreSQL — Advisory Locks
• Stripe — Designing idempotent APIs