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

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.

Step-by-Step Implementation

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)

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

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.

-- 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

• 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.

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