Building an Idempotent Webhook Receiver with PostgreSQL and Python

Jayant Chaudhary — Mon, 12 Oct 2026 01:02:28 +0000

Most webhook receivers I've reviewed were written on a quiet assumption: each event shows up exactly once, and in the order things happened. Neither is true, and the code usually works fine until the day it doesn't. Providers deliver at least once. They retry whenever your endpoint is slow or returns an error, and they don't promise anything about ordering. Stripe says this outright in its docs, and the Standard Webhooks spec builds its whole retry model around the receiver deduplicating by message ID. So the receiver's job isn't just to accept events. It has to soak up the duplicates, the retries and the shuffling and still leave your database right.

What follows is the receiver I'd actually put into production, built up one piece at a time: Python 3.11, FastAPI, psycopg 3, and PostgreSQL doing the work that really matters. I'm assuming PostgreSQL 13 or later and a provider that signs with the Standard Webhooks scheme, but the shape carries over to any provider that gives you signed payloads and stable event IDs.

Verify the signature against the raw body

Signature checking comes first, and it has to run against the exact bytes that came over the wire. This one bites people constantly: the framework parses the JSON, someone re-serializes it to verify, the key order or whitespace shifts, and the HMAC never matches again. Standard Webhooks sends three headers: webhook-id, webhook-timestamp and webhook-signature. The signed content is {id}.{timestamp}.{body}, the signature is a base64 HMAC-SHA256, and the header can carry several space-separated v1,<sig> entries so the sender can rotate secrets without downtime.

import base64
import hashlib
import hmac
import time


class SignatureError(Exception):
    pass


def verify_signature(secret: str, msg_id: str, timestamp: str,
                     body: bytes, header: str, tolerance: int = 300) -> None:
    try:
        ts = int(timestamp)
    except ValueError:
        raise SignatureError("invalid timestamp") from None
    if abs(time.time() - ts) > tolerance:
        raise SignatureError("timestamp outside tolerance")

    key = base64.b64decode(secret.removeprefix("whsec_"))
    signed = f"{msg_id}.{timestamp}.".encode() + body
    expected = base64.b64encode(hmac.new(key, signed, hashlib.sha256).digest())

    for entry in header.split():
        version, _, sig = entry.partition(",")
        if version == "v1" and hmac.compare_digest(sig.encode(), expected):
            return
    raise SignatureError("no matching signature")

Two small things in there matter more than they look. The comparison has to be constant-time, which is what hmac.compare_digest is for, and I compare bytes rather than strings because a header from an attacker can contain non-ASCII characters that make the string version throw. And since the timestamp is part of what's signed, rejecting old ones is what stops someone from replaying a request they captured.

The smallest correct version

Before adding queues and workers, it's worth seeing the core idea on its own, because everything later is built on it. You keep a table of the event IDs you've processed, and you insert the ID in the same transaction as whatever the event changes.

CREATE TABLE processed_events (
    provider   text        NOT NULL,
    event_id   text        NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (provider, event_id)
);

def handle_event(conn, provider: str, event_id: str, event: dict) -> None:
    with conn.transaction():
        cur = conn.execute(
            "INSERT INTO processed_events (provider, event_id) VALUES (%s, %s) "
            "ON CONFLICT (provider, event_id) DO NOTHING",
            (provider, event_id),
        )
        if cur.rowcount == 0:
            return  # already processed; acknowledge and stop
        apply_event(conn, event["type"], event)  # same database

It's worth slowing down on why this holds up under concurrency, because this is exactly the part people rebuild badly with a SELECT to check and then an INSERT. That version has a gap between the check and the write, and two copies of the same event will both slip through it. Here, when two copies arrive on two servers at the same moment, both transactions try to insert the same key, and PostgreSQL makes the second one wait on the unique index until the first commits or rolls back. If the first commits, the second hits the conflict, inserts nothing and gets a row count of zero. If the first rolls back because apply_event blew up, the second carries on as though it had been first all along. The whole guarantee comes from the dedup record and the business write committing, or disappearing, together.

The trouble with this version is that it does all the work inside the HTTP request. If apply_event is slow, the provider times out and retries, and those retries stack up during precisely the incidents when you can least afford extra load.

Acknowledge fast with an inbox table

So split receiving from processing. The HTTP handler checks the signature, drops the raw event into an inbox table and returns, and a worker picks it up afterward. The inbox carries the same unique key, so the insert is still where deduplication happens; it has just moved to the front door.

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 'pending'
                    CHECK (status IN ('pending', 'processed', 'parked', 'dead')),
    attempts        int         NOT NULL DEFAULT 0,
    next_attempt_at timestamptz NOT NULL DEFAULT now(),
    last_error      text,
    received_at     timestamptz NOT NULL DEFAULT now(),
    processed_at    timestamptz,
    PRIMARY KEY (provider, event_id)
);

CREATE INDEX webhook_inbox_ready
    ON webhook_inbox (next_attempt_at) WHERE status = 'pending';

import json
import os
from contextlib import asynccontextmanager

from fastapi import FastAPI, Request, Response
from psycopg.types.json import Jsonb
from psycopg_pool import AsyncConnectionPool

SECRET = os.environ["WEBHOOK_SECRET"]
pool = AsyncConnectionPool(os.environ["DATABASE_URL"], open=False)


@asynccontextmanager
async def lifespan(app: FastAPI):
    await pool.open()
    yield
    await pool.close()


app = FastAPI(lifespan=lifespan)


@app.post("/webhooks/billing")
async def billing_webhook(request: Request) -> Response:
    body = await request.body()
    h = request.headers
    msg_id = h.get("webhook-id", "")
    try:
        if not msg_id:
            raise SignatureError("missing webhook-id")
        verify_signature(SECRET, msg_id, h.get("webhook-timestamp", ""),
                         body, h.get("webhook-signature", ""))
        event = json.loads(body)
        if not isinstance(event, dict):
            raise ValueError("payload is not a JSON object")
    except (SignatureError, ValueError):
        return Response(status_code=400)

    async with pool.connection() as conn:  # commits on clean exit
        await conn.execute(
            "INSERT INTO webhook_inbox (provider, event_id, event_type, payload) "
            "VALUES ('billing', %s, %s, %s) "
            "ON CONFLICT (provider, event_id) DO NOTHING",
            (msg_id, event.get("type", "unknown"), Jsonb(event)),
        )
    return Response(status_code=204)

Notice that a duplicate gets the same 204 as a brand-new event. That's on purpose. As far as the provider is concerned the event was delivered, and answering with anything other than success only buys you more retries.

The same thinking applies on the way out. If handling an event means telling some other system about it, have the worker write that message to an outbox table in the same transaction as its business writes, and let a separate relay deliver it. The inbox protects you from things that arrive twice; the outbox stops you from sending something you never committed, or committing something you never sent.

The worker: claim, apply, mark, all in one transaction

The worker grabs one pending row with FOR UPDATE SKIP LOCKED, which lets you run as many workers as you like without them tripping over the same event. It applies the event and marks it processed inside the same transaction that holds the lock, so the inbox can never claim something happened that the business tables don't reflect.

import psycopg


class EntityNotYetKnown(Exception):
    pass


CLAIM = """
    SELECT provider, event_id, event_type, payload, attempts
    FROM webhook_inbox
    WHERE status = 'pending' AND next_attempt_at <= now()
    ORDER BY next_attempt_at
    LIMIT 1
    FOR UPDATE SKIP LOCKED
"""


def process_one(conn: psycopg.Connection) -> bool:
    with conn.transaction():
        row = conn.execute(CLAIM).fetchone()
        if row is None:
            return False
        provider, event_id, event_type, payload, attempts = row
        try:
            with conn.transaction():  # savepoint around the business writes
                apply_event(conn, event_type, payload)
        except EntityNotYetKnown as exc:
            reschedule(conn, provider, event_id, str(exc), max_attempts=8, final="parked")
        except Exception as exc:
            reschedule(conn, provider, event_id, repr(exc), max_attempts=12, final="dead")
        else:
            conn.execute(
                "UPDATE webhook_inbox SET status = 'processed', processed_at = now() "
                "WHERE provider = %s AND event_id = %s",
                (provider, event_id),
            )
    return True


def reschedule(conn, provider, event_id, error, *, max_attempts, final):
    conn.execute(
        """
        UPDATE webhook_inbox
        SET attempts = attempts + 1,
            last_error = %s,
            next_attempt_at = now() + least(power(2, attempts), 3600) * interval '1 second',
            status = CASE WHEN attempts + 1 >= %s THEN %s ELSE 'pending' END
        WHERE provider = %s AND event_id = %s
        """,
        (error, max_attempts, final, provider, event_id),
    )

One setup detail: open the connection with autocommit=True, so the outer conn.transaction() is a real BEGIN/COMMIT and the inner one becomes a savepoint. That way a failing apply_event rolls back its own half-finished writes while the retry bookkeeping still gets committed.

I should be upfront about the limit of all this. The transaction only covers writes to this database. If apply_event calls some downstream API, you can't roll that call back, and a crash after the call but before the commit means it will run again. For those calls, either pass the event ID along as the downstream system's idempotency key, or put the call behind the outbox so the transaction only records the intention to make it.

Out-of-order events need a per-entity version guard

Deduplication doesn't fix ordering, and this is where a lot of otherwise careful receivers fall down. An updated event can turn up after a later updated event, and a plain upsert will cheerfully overwrite the new state with the old one. The defense is to keep, for each entity, the version of the source state you last applied, and to ignore anything older. PostgreSQL lets you put that rule right into the upsert.

INSERT INTO subscriptions (external_id, status, plan, source_version)
VALUES (%(id)s, %(status)s, %(plan)s, %(version)s)
ON CONFLICT (external_id) DO UPDATE
SET status         = EXCLUDED.status,
    plan           = EXCLUDED.plan,
    source_version = EXCLUDED.source_version,
    updated_at     = now()
WHERE subscriptions.source_version < EXCLUDED.source_version;

If two workers apply events for the same subscription at the same time, the row lock taken by ON CONFLICT DO UPDATE lines them up, and the WHERE clause is checked against the latest committed row. Whichever one wins the race, the older event ends up doing nothing.

The SQL is the easy part. The real question is what you put in source_version. It has to be something the provider assigns and moves forward, like a revision number or a fine-grained modification timestamp on the object itself. Don't use your own receive time, and be careful with coarse event-creation timestamps: Stripe specifically warns that its created field is in seconds, so different events can share a value, and that you shouldn't use it to order events. When the provider gives you nothing you can trust, my preference is to treat the webhook as a nudge, fetch the object's current state from the API inside apply_event, and use the version or timestamp from that response. It costs you an API call, and in exchange ordering stops mattering at all.

Events for entities you have not seen yet

The last case is the one that produces the weirdest bugs, the kind that take a day to track down. Say an invoice.paid event arrives for a subscription your system has never heard of, because the subscription.created event is still stuck in a retry loop somewhere. Inserting the invoice will either break a foreign key or leave you with an orphan.

There are three sensible ways to handle it, and I'd reach for them in this order. If the provider's API lets you fetch the parent, do that inside apply_event and create it from the real, current state. If you can't fetch it, raise EntityNotYetKnown and let the worker retry with backoff, which is what the code above does; the missing event usually shows up shortly. And if the retries run out, park the event instead of dropping it or calling it dead. "Parked" means a valid event that's missing context, and it deserves its own alert and its own runbook, separate from code failures. There's a fourth option people often grab, which is creating a placeholder parent row full of nulls. I'd steer clear of it. Placeholders have a way of leaking into reports and downstream syncs long after the real record has arrived.

The inbox needs a retention rule, because deduplication only works while the event ID is still in the table. A processed row has to outlive every window in which the same event might arrive again. That means the provider's automatic retries (Stripe, for example, keeps trying for up to three days) and the manual resends that someone on either side can trigger from a dashboard. Thirty days is a sensible default that covers both with room to spare. A scheduled job can then delete processed rows in small batches, so the cleanup never holds long locks on a busy table:

DELETE FROM webhook_inbox
WHERE (provider, event_id) IN (
    SELECT provider, event_id FROM webhook_inbox
    WHERE status = 'processed'
      AND processed_at < now() - interval '30 days'
    LIMIT 5000
);

Parked and dead rows stay until someone has dealt with them, because they are the record of what still needs fixing.

A nice side effect of parked and dead rows is that replay comes for free. Once you've fixed the bug or backfilled the missing parent, set the affected rows back to pending with next_attempt_at = now(), and the worker will reprocess them safely, because every step above is idempotent. Even with all of this in place, I'd still run a periodic reconciliation against the provider's API. Webhooks tell you what changed when delivery works; reconciliation tells you whether it actually did. I wrote about where this pattern fits in a broader integration, including what to demand when you are the one sending webhooks, in this guide to handling duplicate and out-of-order webhook events.

I'm Jayant Chaudhary, founder of eProxim, where we build event-driven integrations for implementation partners and the firms they serve.