Using the Outbox Pattern for Reliable Billing Events

You reach for this the moment a billing event goes missing in production: an invoice is marked paid in Postgres, but the email service and the revenue-recognition pipeline never heard about it because the publish failed after the commit. This page is the concrete implementation companion to Outbox Pattern & Event Publishing — the parent covers the concepts and semantics; here we write the actual table, relay loop, and consumer for invoice.paid and payment.failed. Every example uses real billing identifiers and is safe to adapt directly.

Trade-offs

The concrete implementation has five design choices, each a small fork. The recommended defaults — SKIP LOCKED, deterministic ids, aggressive retention, failed-status poison handling, and per-subscription ordering — are the right call for almost every billing system. The map shows the choice and its default.

Deterministic event_ids are worth the small effort: deriving event_id from (invoice_id, 'paid') means a replay of the same business operation produces the same id, so the consumer dedups it for free.

Why SKIP LOCKED beats the single-worker advisory lock

The advisory-lock variant is tempting because it is trivially correct: one worker holds pg_advisory_lock, drains the queue in strict created_at order, and nobody else touches a row. It also caps your relay at exactly one publisher, and a single publisher on a busy billing tenant becomes the bottleneck the moment your broker publish round-trip is 4-8ms. At that latency one worker tops out near 150-250 events per second, which is fine until a monthly renewal run enqueues 40,000 invoice.created and invoice.paid rows in a two-minute window. FOR UPDATE SKIP LOCKED lets you run eight relay pods that each claim a disjoint 200-row slice with zero cross-worker coordination, because a row locked by pod A is simply invisible to pod B’s SELECT. The cost is that strict global ordering is gone — but you never wanted global ordering, you wanted per-subscription_id ordering, and the broker partition key gives you that independently of how many relay workers are running. The one subtlety: keep the claim batch bounded (200 here) so a slow broker call on one row does not hold locks on 5,000 others for the length of the whole publish loop.

The deterministic id is a UUID collision you are choosing on purpose

Hashing md5(invoice_id || ':paid') into a UUID is not cryptographic — it is a deliberate, reproducible collision. Two distinct code paths that both mean “invoice 8a3f… was paid” must produce byte-identical event_ids, or the ON CONFLICT (event_id) DO NOTHING guard silently lets a duplicate through. That means the id recipe has to be stable across deploys: never fold a timestamp, a retry counter, or now() into the hash input. When you add a second event for the same aggregate — say invoice.voided after invoice.paid — the discriminator (:paid versus :voided) is what keeps their ids distinct, so treat that suffix as part of your event contract and version it alongside the payload schema.

Step-by-Step Implementation

The four steps wire a real invoice.paid event from the billing transaction through the relay to a dedup-guarded consumer. The flow below traces one event’s path — the atomic emit, the SKIP LOCKED claim, the keyed publish, and the consumer’s idempotent apply.

The load-bearing property across all four steps is that the outbox row and the business write share exactly one transaction boundary on the way in, and the publish plus mark-sent share exactly one on the way out. Everything else — retention, batching, poison routing — is operational tuning you can change later without re-reasoning about correctness. If you only remember two lines from this page, remember that the INSERT INTO event_outbox sits between the same BEGIN/COMMIT as the UPDATE invoices, and that the UPDATE ... status='sent' sits inside the same relay transaction that claimed the row.

1. Create the outbox table

CREATE TABLE event_outbox (
  outbox_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id       UUID NOT NULL UNIQUE,
  subscription_id UUID NOT NULL,
  event_type     TEXT NOT NULL,
  payload        JSONB NOT NULL,
  status         TEXT NOT NULL DEFAULT 'pending',  -- pending | sent | failed
  attempts       INT  NOT NULL DEFAULT 0,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  sent_at        TIMESTAMPTZ
);

-- Relay scan touches only unsent rows
CREATE INDEX idx_outbox_pending ON event_outbox (created_at) WHERE status = 'pending';

2. Emit the event inside the billing transaction

The insert shares the transaction with the invoice update. Deriving event_id deterministically from invoice_id makes re-running the same operation idempotent end to end.

BEGIN;
  UPDATE invoices
     SET status = 'paid', paid_at = now()
   WHERE invoice_id = $1
     AND status = 'open';   -- guard against double-paying

  INSERT INTO event_outbox (event_id, subscription_id, event_type, payload)
  VALUES (
    md5($1::text || ':paid')::uuid,        -- deterministic event_id
    $2,                                     -- subscription_id
    'invoice.paid',
    jsonb_build_object(
      'invoice_id', $1,
      'subscription_id', $2,
      'amount_cents', $3,
      'currency', $4
    )
  )
  ON CONFLICT (event_id) DO NOTHING;        -- safe on replay
COMMIT;  -- ✅ invoice paid AND event queued, atomically

3. Run the SKIP LOCKED relay loop

Multiple relay instances can run concurrently; SKIP LOCKED hands each worker a disjoint slice of pending rows with no coordination.

import time, json

POLL_INTERVAL = 0.2   # seconds
BATCH = 200
MAX_ATTEMPTS = 8

def relay_forever(db, broker):
    while True:
        published = relay_tick(db, broker)
        if published == 0:
            time.sleep(POLL_INTERVAL)   # idle backoff when queue is empty

def relay_tick(db, broker) -> int:
    with db.transaction() as txn:
        rows = txn.fetch(f"""
            SELECT outbox_id, event_id, subscription_id, event_type, payload, attempts
              FROM event_outbox
             WHERE status = 'pending'
             ORDER BY created_at
             LIMIT {BATCH}
               FOR UPDATE SKIP LOCKED
        """)
        for r in rows:
            try:
                broker.publish(
                    topic=r["event_type"],
                    key=str(r["subscription_id"]),     # per-subscription ordering
                    value=json.dumps(r["payload"]),
                    headers={"event_id": str(r["event_id"])},
                )
                txn.execute("UPDATE event_outbox SET status='sent', sent_at=now() "
                            "WHERE outbox_id=$1", r["outbox_id"])         # ✅ delivered
            except BrokerError:
                if r["attempts"] + 1 >= MAX_ATTEMPTS:
                    txn.execute("UPDATE event_outbox SET status='failed', "
                                "attempts=attempts+1 WHERE outbox_id=$1",
                                r["outbox_id"])                            # ✗ poison
                    alert_operator(r["event_id"])
                else:
                    txn.execute("UPDATE event_outbox SET attempts=attempts+1 "
                                "WHERE outbox_id=$1", r["outbox_id"])      # ⚠️ retry
        return len(rows)

The POLL_INTERVAL = 0.2 and BATCH = 200 constants are the two knobs that trade latency against database load. At a 200ms idle poll the worst-case delivery latency for a quiet queue is roughly the poll interval plus one publish round-trip, which keeps a payment.failed event reaching dunning inside half a second. Dropping the interval to 50ms buys you lower tail latency at the price of four times the empty SELECT traffic against the idx_outbox_pending partial index; on a queue that is empty 90% of the time that is pure waste, so prefer notifying the relay with LISTEN/NOTIFY on insert and treating the poll purely as a backstop. MAX_ATTEMPTS = 8 with no backoff means a genuinely broken broker burns through all attempts in seconds — if the failure is a broker outage rather than a poison payload you want exponential backoff on the whole relay_tick, not per-row, so a five-minute outage does not permanently fail every row that happened to be mid-batch.

4. Deduplicate downstream

The consumer records event_id and applies its effect in one transaction. A payment.failed consumer, for example, advances dunning exactly once even if the relay republishes.

def on_payment_failed(event_id: str, payload: dict, db) -> None:
    with db.transaction() as txn:
        claimed = txn.execute(
            "INSERT INTO consumed_events (event_id, consumed_at) "
            "VALUES ($1, now()) ON CONFLICT DO NOTHING RETURNING event_id",
            event_id)
        if not claimed:
            return  # ✅ duplicate redelivery, already handled
        advance_dunning(payload["subscription_id"], txn)

Verification & Testing

The three assertions prove the pattern end to end: atomicity (a rolled-back transaction leaves no outbox row), at-least-once with dedup (a crash mid-publish still applies the effect once), and poison handling (a failing row lands in failed, not stuck at the head). The panel lists them.

Assert atomicity first: roll back the billing transaction and confirm no outbox row exists. Assert at-least-once: crash the relay between publish and the mark-sent UPDATE, restart, and confirm the consumer’s effect ran exactly once thanks to dedup. Assert poison handling: force a row to fail MAX_ATTEMPTS times and confirm it lands in failed, not stuck at the head of the queue.

Making the crash test deterministic

“Crash the relay between publish and mark-sent” is easy to say and hard to reproduce by hand, because the window is a few milliseconds wide. Make it deterministic by injecting a fault: wrap broker.publish so that for one designated event_id it publishes successfully and then raises before the UPDATE runs, exactly simulating a process kill after the broker acked. Run the relay once, observe that the row is still pending and the consumer has recorded the event_id in consumed_events, then run the relay a second time and assert the effect count stayed at one while the row flipped to sent. This is the single test that proves the whole at-least-once-plus-dedup contract, so it is worth building the fault-injection seam into the relay rather than mocking around it. A useful companion assertion: publish the same event_id twice on purpose and confirm advance_dunning fired once — that isolates the consumer’s idempotency from the relay’s behaviour, so a regression tells you which half broke.

What the SKIP LOCKED claim does under concurrency

A test that runs a single relay never exercises the property that justifies SKIP LOCKED in the first place. Add a concurrency test that inserts 1,000 pending rows, starts four relay workers against a broker stub that records every (event_id, subscription_id) it receives, and then asserts two things after the queue drains: every event_id was published exactly once (no worker double-claimed a row), and for each subscription_id the events arrived in created_at order on their partition. The first assertion is what FOR UPDATE SKIP LOCKED guarantees — a row locked by one transaction is skipped, not blocked, by the others. The second is what the key=subscription_id publish guarantees, and it holds no matter how the four workers interleave, because ordering is enforced per partition by the broker rather than by the relay’s claim order.

-- Backlog health: oldest unsent event age (alert if this climbs)
SELECT event_type,
       count(*) AS pending,
       now() - min(created_at) AS oldest_pending
  FROM event_outbox
 WHERE status = 'pending'
 GROUP BY event_type
 ORDER BY oldest_pending DESC;

-- Poison events awaiting operator action
SELECT event_id, event_type, attempts, created_at
  FROM event_outbox
 WHERE status = 'failed'
 ORDER BY created_at;

Gotchas & Production Pitfalls

The pitfalls here reopen the very gaps the outbox exists to close: marking sent in a separate transaction, random ids that defeat replay dedup, missing retention, count-based alerting, and a forgotten partition key. The map groups them.

• Marking sent in a separate transaction reopens the dual-write gap. The UPDATE ... status='sent' must be in the same transaction that claimed the row, or a crash leaves it locked-but-unmarked. Claim and mark together.
• Random event_ids defeat dedup on replay. If you re-emit invoice.paid from a backfill with a fresh UUID, the consumer treats it as new. Derive the id from the domain key so replays collapse.
• Forgetting retention turns the outbox into a slow table. Delivered rows accumulate forever. Run a retention job (DELETE ... WHERE status='sent' AND sent_at < now() - interval '7 days') or partition by month.
• Alerting on row count misses slow drains. A steady 10k pending rows can be healthy; a single event pending for ten minutes is not. Alert on oldest-pending age, not total count.
• Per-key ordering breaks if you forget the partition key. Publishing without key=subscription_id lets the broker spread one subscription’s events across partitions, and consumers see invoice.paid before invoice.created.

The partial index is doing more work than it looks

idx_outbox_pending is defined WHERE status = 'pending', and that predicate is the difference between a relay SELECT that scans a few thousand live rows and one that scans every row you have ever emitted. On a full index the planner has to walk past millions of sent tuples to find the pending tail; on the partial index those sent rows are not in the index at all, so it stays roughly the size of your live backlog even as the table grows. This is also why the retention job and the index reinforce each other: deleting sent rows keeps the heap small, and the partial predicate keeps the index small, and together they keep the relay’s claim query on a flat latency curve. Watch for one trap — if you ever add a status = 'retrying' state, the partial index no longer covers it and those rows become invisible to the relay, so any new non-terminal status has to be added to the index predicate in the same migration.

Consumed-events retention is a separate decision from outbox retention

It is easy to delete sent outbox rows aggressively and forget that the consumed_events table on the consumer side has its own growth problem. That table is the dedup ledger, so you cannot truncate it as freely — an event_id you forget is an event_id a late redelivery can re-apply. Size its retention to your maximum realistic redelivery window: how long could a broker hold an un-acked message, plus how far back could an operator replay from failed. A 30-day window on consumed_events is usually safe for billing and keeps the table bounded, but tie the number to your replay tooling rather than picking it arbitrarily, because the day you replay a 45-day-old payment.failed to re-drive dunning is the day a too-short window lets it double-charge attention.

Frequently Asked Questions

Why not publish directly after committing? Because the process can die between the commit and the publish, and the event is then lost with no record that it should have existed. The outbox makes the intent to publish part of the transaction.

Does the outbox guarantee exactly-once delivery? No — it guarantees at-least-once. Consumers still need to be idempotent, and that combination is what produces effectively-once behaviour.

How should the outbox be drained? By a relay that reads unpublished rows in order and marks them published after a successful send. Ordering per aggregate matters more than global ordering.

What stops the outbox table from growing without bound? A retention job that deletes or archives published rows past a window. Keep them long enough to replay a bad day, not forever.

Related

• Outbox Pattern & Event Publishing
• Redis vs Postgres for Webhook Idempotency Keys
• Building Idempotent Webhook Handlers in Node.js
• Webhook Retry & Timeout Strategies
• Webhook Processing & Backend State Management