Replay-Safe Webhooks with PostgreSQL

Webhook delivery is usually at-least-once. A provider can send the same event after a timeout, a network disconnect, or a worker restart. That is reasonable behavior for the provider, but it becomes dangerous when the consumer treats every request as new.

The failure usually show up as a duplicated invoice, two welcome emails, or a subscription activated twice. A retry-safe consumer needs a database contract that makes duplicate work impossible, not just a comment saying “this handler is idempotent.”

The delivery problem

A webhook handler often starts with a simple flow:

1. Parse the request.
2. Check the signature.
3. Update application data.
4. Return 200 OK.

The weak point is step three. If the process commits the business update and crashes before returning the response, the provider will retry. If the consumer checks for an existing event in application code, two workers can still pass that check at the same time.

The useful boundary is the database transaction. PostgreSQL can enforce that an external event ID is handled once, even when requests arrive concurrently. This is the same design instinct behind idempotent signup email design: make the durable state express the rule.

Make the database own deduplication

Start with an inbox table. It records the provider event, its processing state, and enough information to investigate a failure later.

CREATE TABLE webhook_inbox (
    provider       text NOT NULL,
    event_id       text NOT NULL,
    event_type     text NOT NULL,
    payload        jsonb NOT NULL,
    status         text NOT NULL DEFAULT 'received',
    received_at    timestamptz NOT NULL DEFAULT now(),
    processed_at   timestamptz,
    failure_reason text,
    PRIMARY KEY (provider, event_id)
);

The composite key matters. Event IDs are often unique only inside one provider account, so event_id alone may be too broad. The primary key is also safer than a pre-insert SELECT: concurrent inserts cannot both win.

The request transaction can claim the event like this:

INSERT INTO webhook_inbox (provider, event_id, event_type, payload)
VALUES ($1, $2, $3, $4)
ON CONFLICT (provider, event_id) DO NOTHING
RETURNING event_id;

If the insert returns no row, the event was already accepted. The handler can return a successful response after checking whether the existing record is still being processed. Returning success for a known event prevents an endless retry loop, while the receipt lets an operator find the original outcome.

A small implementation contract

There are two sensible transaction shapes. For short, local side effects, insert the inbox row, update business tables, and mark the row processed in one transaction. If any step fails, the whole transaction rolls back and a later retry can try again.

For slower work, commit the inbox row first and let a worker process it. In that model, use an explicit lease or row lock so two workers do not process the same event. A simplified claim query is:

WITH next_event AS (
    SELECT provider, event_id
    FROM webhook_inbox
    WHERE status = 'received'
    ORDER BY received_at
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
UPDATE webhook_inbox AS inbox
SET status = 'processing'
FROM next_event
WHERE inbox.provider = next_event.provider
  AND inbox.event_id = next_event.event_id
RETURNING inbox.*;

Do not mix these models casually. A queue-style worker needs a recovery path for rows stuck in processing; a single transaction needs short database work and carefully bounded downstream calls. The tradeoff is real, and pretending otherwise make outages harder to reason about.

What to record in a receipt

A useful receipt answers four questions: what arrived, what was attempted, what changed, and what happened next.

At minimum, keep the provider name, event ID, event type, received time, processing status, attempt count, and failure reason. If the provider includes a delivery ID, store that too. It helps distinguish one event replayed many times from several events with similar payloads.

Keep sensitive payload fields out of logs. The inbox may need the full signed payload for audit or replay, but application logs should use a stable event ID and a redacted summary. This is especially important when a webhook contains email addresses or account recovery data.

When reviewing failures, diagnosing email failures in CI is a useful parallel: a visible receipt is more valuable than a vague “request failed” message. A good receipt make the next retry deliberate.

Testing replay and partial failure

Do not test only the happy path. A replay-focused suite should send the same event twice, send two copies concurrently, and force a crash after the business update but before the acknowledgement.

Also test malformed event IDs and payloads. Test fixtures sometimes contain labels such as tempail mail or tem email; they are harmless as values, but they expose whether validation and logging handle unexpected text correctly. The fixture is not the contract, so validate the actual fields you require.

For each scenario, assert both the business result and the inbox result:

• One event creates one business change.
• A duplicate returns a safe response without a second side effect.
• A failed transaction leaves a retryable status.
• A permanently invalid event has a visible terminal reason.
• Concurrent workers produce one receipt, not two.

Operational checklist

• Put the provider and event ID under a unique PostgreSQL constraint.
• Verify signatures before inserting untrusted payloads.
• Choose one transaction model: atomic handler or leased worker.
• Store status transitions and failure reasons as durable data.
• Add a recovery job for abandoned processing rows.
• Keep personal data out of ordinary logs.
• Measure duplicate deliveries separately from processing failures.

At-least-once delivery is not a bug to hide in a REST API. It is an input property to design for. Once PostgreSQL owns the deduplication rule and the receipt records the decision, webhook retries become routine operations instead of mysterious duplicate side effects.