Pattern — Transactional Outbox

The problem it solves

You have to do two things at once: commit a row to your database and tell the outside world about it — publish an event to a broker, ship a wire to a payment rail, write to a second store. There is no shared transaction across that boundary, so any ordering loses. Write the DB then publish, and a crash in the gap loses the event (Drill 85 — the settlement pod evicted after the ledger post but before the wire). Publish then write, and a crash loses the DB row. Fire-and-forget the second write inside a swallowed catch, and you get silent drift — 2% of writes vanishing for five months (Drill 55). The common shape is the dual write: two systems that must agree, with no atomic way to change both.

The pattern

Collapse the dual write into a single local transaction. In the same database commit as your business row, insert a row into an outbox table describing the event to send. Because both writes are in one transaction, they are atomically all-or-nothing — intent and state can never disagree. A separate relay (a polling publisher, or a change-data-capture tail of the write-ahead log) reads unpublished outbox rows and delivers them to the broker or external system, marking each as sent. You still can't buy exactly-once delivery across the boundary — the relay publishes at-least-once, so downstream consumers must be idempotent (Pattern 01) — but you have bought exactly-once capture: every committed effect produces exactly one durable, replayable intent.

How to apply it

1. One table, committed with the business write. Schema: id UUID PK, aggregate_id, event_type, payload JSONB, created_at, published_at NULL, attempts INT DEFAULT 0. The INSERT INTO outbox … runs in the same transaction as the ledger/order write — never a second connection, never after COMMIT.
2. A relay that polls, in batches, without stampeding. Poll every 200 ms–1 s; pull LIMIT 500 WHERE published_at IS NULL ORDER BY created_at behind a partial index (created_at) WHERE published_at IS NULL so the scan stays O(backlog), not O(table). Use SELECT … FOR UPDATE SKIP LOCKED so you can run 2–4 relay workers without double-publishing the same row.
3. Publish, then mark — in that order. Send to the broker/rail using aggregate_id (or the payment id) as the downstream idempotency key, then set published_at = now(). If the mark is lost after a successful publish, the row re-publishes — harmless, because the consumer dedupes. Cap attempts at 5, then route the row to a dead-letter / poison table so one bad payload can't wedge the relay.
4. Bound the state. Keep published rows for the max redelivery window — 7 days — then sweep them (DELETE … WHERE published_at < now() - interval '7 days') so the hot table stays small. For ordering, publish per aggregate_id so events for one entity stay in commit order.
5. Reconcile, don't trust. Run a nightly diff (Drill 55's discipline): count committed business rows vs. published outbox rows; the difference must be 0. This is the gate that turns "the relay is running" into "the stores provably agree."

flowchart TD
    A["Business write + outbox INSERT<br/>(one local transaction)"] --> B["COMMIT — atomic:<br/>row and intent, or neither"]
    B --> C["Relay polls WHERE published_at IS NULL<br/>(FOR UPDATE SKIP LOCKED, batch 500)"]
    C --> D["Publish to broker / rail<br/>(aggregate_id = idempotency key)"]
    D -->|"ack"| E["Mark published_at = now()"]
    D -->|"5 failed attempts"| F["Route to dead-letter table"]
    E --> G["Consumer dedupes<br/>(at-least-once → exactly-once effect)"]

When to use it — and when not

Reach for it whenever one commit must cause an external effect you don't share a transaction with — publishing events, moving money across a rail, feeding a second store during a migration. Skip it when there is no real dual write: a pure read, or two writes that already live in the same database and can share one transaction directly (just use the transaction). It is the wrong tool when you need a synchronous answer — the caller must have the downstream result before responding (a live authorization); the outbox is asynchronous by construction and adds publish latency (typically sub-2 s, but not zero). And it is not free: an extra table, a relay to operate, and eventual — not immediate — consistency. If your platform already tails the log (Debezium/CDC), prefer that over a hand-rolled poller so you're not paying for both.

Signals it's working

• Outbox backlog (COUNT(*) WHERE published_at IS NULL) hovers near 0 and drains fast; alarm if it exceeds 1,000 rows or grows for 5 min — the relay is stalled or the broker is down.
• Publish lag now() - created_at at p99 < 2 s.
• Dead-letter depth = 0; any row there is a stuck poison payload.
• Reconciliation diff = 0: committed business rows equal published events, every night.
• attempts is 1 for nearly every row — retries are the exception, not the norm.

Seen in the wild

• Debezium's Outbox Event Router — the reference CDC implementation: tail Postgres/MySQL's log, turn outbox inserts into Kafka records, no polling.
• microservices.io / Chris Richardson codified the "Transactional Outbox" and "Polling Publisher" / "Transaction Log Tailing" patterns.
• DynamoDB Streams, Postgres logical replication, Kafka Connect are all log-tailing rails teams wire an outbox onto.
• Payment platforms (the Drill 85 shape) commit the ledger row and the outbound-wire intent together, then relay the wire once using the payment id as the rail's idempotency key.

See it

A ChainScene walks the atomic commit → relay poll → publish → mark, and a CardsScene fixes the load-bearing config (poll 200 ms–1 s, batch 500, SKIP LOCKED, cap 5 → DLQ, 7-day sweep, backlog alarm > 1,000).

Named concepts: transactional outbox, outbox relay / polling publisher, dual-write hazard, change data capture.