The transactional outbox pattern: webhooks you can't lose

Here is a bug that ships to production constantly and survives code review every time. A request comes in, you update a row, and then you fire the webhook that tells the rest of the world about it:

// The handler that looks correct and isn't.
sqlx::query("UPDATE orders SET status = 'paid' WHERE id = $1")
    .bind(order_id)
    .execute(&pool)
    .await?;

http_client
    .post(&customer_webhook_url)
    .json(&OrderPaid { order_id })
    .send()
    .await?;            // <- everything between here and the line above is a trap

Two writes to two systems that cannot commit together. The database row and the outbound HTTP call are independent, so every interleaving of failures is reachable: the DB commits and the process is killed before the POST; the POST succeeds and then the DB transaction rolls back; the POST times out but actually arrived. You cannot make these two side effects atomic by ordering them carefully, because there is no order that survives a crash in the gap. This is the dual-write problem, and webhooks are where it hurts most — a lost order.paid event is a customer who never gets provisioned.

Why retries and message queues don't fix the dual-write problem

The usual reflexes make it worse, not better.

"Wrap it in a transaction." A SQL transaction only covers the database. The HTTP POST is not transactional, so BEGIN ... COMMIT around both still leaves a window where the row is committed and the send never happens (process died) or the send happened and the row rolled back (POST succeeded, then COMMIT failed).

"Send to a message queue instead of HTTP." Now you have the same dual write with a different second system. Publishing to Redis/Kafka/SQS and committing to Postgres are still two independent commits. You have just moved the lost message from "lost webhook" to "lost queue publish," and added an operational dependency to babysit.

The only way out is to stop writing to two systems. You need the intent-to-send to live in the same transaction as the business write, in the same database, so the two commit or fail as one unit.

The transactional outbox pattern, in one sentence

The transactional outbox pattern is simple: instead of sending the webhook inside your handler, you insert a row describing the webhook into an outbox table — in the same transaction as the business change. Because it is one transaction against one database, atomicity is free. Either the order is marked paid and the outbox row exists, or neither does. There is no third state.

A separate process (a relay, or in our case a dispatch worker pool) then reads committed outbox rows and performs the actual HTTP delivery, marking each row done when the receiver returns 2xx and retrying when it doesn't. The send is decoupled in time from the transaction, which is exactly what lets it be reliable: the worker can retry for hours, the producer never blocks on a slow receiver, and a crash anywhere just leaves a committed row waiting to be picked up.

The pattern has one honest consequence worth stating up front: it gives you at-least-once delivery, not exactly-once. If a worker POSTs successfully but dies before recording that success, another worker will send the same row again. Exactly-once across a network is not achievable; the standard fix is to make each request carry an immutable idempotency key so the receiver dedupes. More on that below.

A Postgres outbox table schema (the outbox is also the queue)

Here is the shape of a Postgres outbox that is also the queue — no Redis, no Kafka, the same database that holds your business data. This is essentially the Dropless schema, lightly trimmed. The event is the message; one delivery row is the unit of work per (event, endpoint) so fan-out to N subscribers is N queue rows.

-- The message: persisted before any 2xx is returned (source of truth).
CREATE TABLE events (
    id         uuid PRIMARY KEY,           -- UUIDv7: time-ordered
    tenant_id  text NOT NULL,
    event_type text NOT NULL,
    payload    jsonb NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

-- The queue AND the state: one row per (event, endpoint).
CREATE TABLE deliveries (
    id              uuid PRIMARY KEY,
    event_id        uuid NOT NULL REFERENCES events (id),
    endpoint_id     uuid NOT NULL REFERENCES endpoints (id),
    status          text NOT NULL DEFAULT 'pending',
    attempt_count   integer NOT NULL DEFAULT 0,
    next_attempt_at timestamptz NOT NULL DEFAULT now(),
    locked_until    timestamptz,        -- lease / visibility timeout
    locked_by       text,               -- which worker owns it
    idempotency_key text NOT NULL,      -- immutable, travels on every retry
    last_error      text,
    UNIQUE (event_id, endpoint_id)
);

-- Dequeue scan: rows that are due OR whose lease has lapsed.
-- This index must cover every status the claim query touches,
-- including 'in_progress', or the reclaim path falls back to a seq scan.
CREATE INDEX deliveries_due_idx
    ON deliveries (next_attempt_at)
    WHERE status IN ('pending', 'failed', 'in_progress');

The partial index on next_attempt_at is what keeps the dequeue cheap as the table grows: the worker only ever scans rows that are actually claimable, and terminal rows (succeeded/dead) fall out of the index entirely. One subtlety the schema above gets right and most home-grown ones get wrong: the index WHERE clause has to include in_progress, because the claim query (below) also reclaims leased rows whose worker died. Index only pending/failed and your crash-recovery scan quietly degrades to a sequential scan.

The enqueue transaction and the commit-then-2xx invariant

The whole correctness argument lives in one function. Persist the event, fan it out to every subscribed endpoint, and commit — all in a single transaction. The HTTP API returns 2xx only after that commit returns Ok. We call this invariant commit-then-2xx.

pub async fn enqueue_message(
    pool: &PgPool, tenant_id: &str,
    event_type: &str, payload: &serde_json::Value,
) -> CoreResult<Enqueued> {
    let event_id = Uuid::now_v7();          // time-ordered id
    let mut tx = pool.begin().await?;

    // 1) Persist the raw event (source of truth).
    store::insert_event(&mut *tx, event_id, tenant_id, event_type, payload).await?;

    // 2) Snapshot the active endpoints, 3) one delivery (queue) row each.
    let endpoints = store::active_endpoints(&mut *tx, tenant_id).await?;
    let mut delivery_ids = Vec::new();
    for ep in endpoints.iter().filter(|e| e.subscribes_to(event_type)) {
        let id = Uuid::now_v7();
        // The delivery id IS the idempotency key — fixed here, re-sent every attempt.
        store::insert_delivery(&mut *tx, id, event_id, ep.id, tenant_id).await?;
        delivery_ids.push(id);
    }

    // 4) Commit. Only after this returns Ok may the caller return 2xx.
    tx.commit().await?;
    Ok(Enqueued { event_id, delivery_ids })
}

And the handler, which is disciplined about ordering — the 201 is constructed only in the Ok arm:

match outbox::enqueue_message(&state.pool, &tenant, &body.event_type, &body.payload).await {
    Ok(enq) => {
        state.notifier.ping();          // wake idle workers, OFF the response path
        (StatusCode::CREATED, Json(enq)).into_response()
    }
    Err(e) => ApiError::from(e).into_response(),
}

This ordering is the entire point. If the process is kill -9'd before tx.commit(), Postgres rolls the whole transaction back: no event, no deliveries, and crucially the producer never saw a 2xx, so it knows to retry. If the process dies after the commit but before the response is written, the data is durable and the producer's own retry is harmless (the receiver will dedupe). What can never happen is an acknowledged-but-lost event: a 2xx that corresponds to nothing on disk. A crash commits everything or nothing.

This is not a thought experiment. Dropless ships a CI gate that kill -9s the dispatcher mid-delivery and asserts dropped == 0 (duplicates are allowed and expected — subscribers dedupe). The invariant is tested, not just asserted in prose.

Note one deliberate detail: waking the workers (notifier.ping(), a coalesced LISTEN/NOTIFY) happens after the commit and off the response path. The NOTIFY is a latency optimization, not part of the durability contract — if it is lost, a poll-interval fallback still finds the due row. We keep it outside the transaction and rate-limit it because issuing a NOTIFY per commit serializes commits on the async-notify queue lock and throttles ingest under load.

Draining the outbox with FOR UPDATE SKIP LOCKED

The relay side is where most home-grown outboxes leak. The naive "SELECT due rows, then UPDATE them" races: two workers grab the same row. The fix is to claim and lock atomically with FOR UPDATE SKIP LOCKED, leasing each claimed row for a visibility timeout:

UPDATE deliveries AS d
SET status = 'in_progress',
    locked_by = $1,
    locked_until = now() + ($2::int * interval '1 second')
WHERE d.id IN (
    SELECT id FROM deliveries
    WHERE (status IN ('pending','failed') AND next_attempt_at <= now())
       OR (status = 'in_progress' AND locked_until < now())   -- reclaim dead worker's rows
    ORDER BY next_attempt_at
    FOR UPDATE SKIP LOCKED
    LIMIT $3
)
RETURNING d.*;

SKIP LOCKED lets N workers pull disjoint batches with zero coordination — the row lock is the only lock you need, so you scale dispatch by just running more workers against the same table. The second predicate is the crash-recovery path: a worker killed mid-delivery leaves its row in_progress with a locked_until that eventually lapses, and any worker reclaims it. That is precisely why nothing is dropped, and also precisely why delivery is at-least-once: a worker that POSTs successfully but dies before writing the success will have its row re-sent.

At-least-once delivery and the idempotency key

Because reclaim can resend, the receiver has to deduplicate. Every request carries an immutable Idempotency-Key header — in Dropless it is the delivery id itself, fixed at enqueue and re-sent on every attempt — so a redundant delivery is a no-op on the receiving end. This is the at-least-once contract made usable: the gateway guarantees the message arrives at least once, and the key lets the receiver collapse duplicates into effectively-once processing. It is never exactly-once on the wire, and any system that claims otherwise is hiding a dedupe step somewhere.

Each request is also signed with Svix-compatible HMAC-SHA256 headers — webhook-id, webhook-timestamp, and webhook-signature over the signed content {id}.{timestamp}.{body} — so the receiver can verify authenticity and reject replays by timestamp. Around this core you layer the usual reliability machinery — exponential backoff with jitter via next_attempt_at, a per-endpoint circuit breaker, a dead-letter table for exhausted deliveries, and one-click replay — but none of it changes the durability story. The durability story is entirely the outbox commit.

One database, one invariant

The transactional outbox pattern is not glamorous, and that is its appeal: webhook reliability comes from a single Postgres transaction and a disciplined response ordering, not from a fleet of brokers. Postgres is the source of truth and the queue. The producer's contract is exactly as strong as the commit.

If you'd rather not write the dispatcher, leases, signing, and dead-letter handling yourself, Dropless is an open-source (AGPL-3.0) Rust implementation of exactly this — commit-then-2xx enqueue, FOR UPDATE SKIP LOCKED dispatch with lease-based crash recovery, Svix-compatible signing, and the kill -9 CI test described above. Postgres is its only runtime dependency. The schema and code in this post are taken from it; run it yourself from ghcr.io/r4nd0menryu/dropless, or use the hosted version if you'd rather not operate it.