When one write becomes two: the transactional outbox pattern

There is a line you reach for the moment a checkout succeeds: tell the world. The order went through, so publish the event and let Kafka carry it onward to everything downstream. Building the checkout endpoint for an event-driven e-commerce project, the obvious next thing to write after the commit was two more lines.

# DON'T DO THIS
order = create_order(…) # write #1: Postgres
db.commit()
kafka.produce("order_placed", order) # write #2: Kafka

Two lines, one silent data-corruption bug.

The problem of two writes

A checkout is never one write. An order touches the orders table, order_items, and inventory, all inside a single Postgres transaction that commits whole or not at all. But the event still has to reach Kafka, which shares nothing with that transaction. So the handler commits and then publishes, and in the gap between the two anything can happen. The commit succeeds and the publish fails, and now the order exists but no consumer hears of it: analytics undercounts revenue, the fraud check never runs, the index goes stale, and nothing errors. That is the worst kind of failure, the one that does not announce itself. Flip the order and a different ghost appears, Kafka insisting on a sale that Postgres rolled back.

Which write should go first? And how do you trust a system whose record of a sale and whose announcement of that sale can quietly disagree? You cannot retry your way out, because retrying the publish after a crash needs durable proof the order was saved, which is the very thing the crash took. This is the dual-write problem.

The only thing you can make atomic

The reframe is the whole pattern. The only two writes you can make atomic are two writes to the same transactional system. So you stop treating “publish an event” as its own kind of action that needs its own system, and you demote it to what it really is, a row in the same database, written in the same transaction as the business data it describes. That row is the outbox, and once the announcement is just data, the dual-write problem has nowhere left to live.

What the checkout does

So checkout writes the outbox rows inside that same transaction, beside the order and the inventory change.

async with db.begin():
    # lock inventory rows in product_id order to avoid deadlocks
    ... select(Inventory).with_for_update()
    # validate stock; raise 409 if insufficient

    db.add(Order(...))
    for pid, qty in cart.items.items():
        db.add(OrderItem(...))
        inventory[pid].quantity_on_hand -= qty
        await write_outbox(db, event_name="order_item_added", ...)
        await write_outbox(db, event_name="inventory_decremented", ...)
    await write_outbox(db, event_name="order_placed", ...)
# commit happens here, atomically, on a clean exit

A two-item order produces exactly five outbox rows, and the order data and the instructions to publish it land in the same write to disk. The failure path is boring on purpose: insufficient stock returns a 409 and leaves zero order rows, zero outbox rows, and inventory untouched. Nothing happened, which is exactly right. A test simulates a crash mid-transaction and confirms the outbox row rolls back with everything else. It runs against a real Postgres rather than a mock, because the behaviour under test is the database’s own transaction semantics, the one thing a mock would lie about.

The refusal that is the feature

One line is easy to get wrong and quietly fatal if you do. The outbox helper flushes; it does not commit.

async def write_outbox(session, *, event_name, ...):
    session.add(EventOutbox(...))
    await session.flush()   # sends the INSERT, does NOT commit

The flush makes the row visible inside the transaction, but commitment still belongs to the outer db.begin(). Had the helper committed, the event would become durable independently of the order, and the dual-write problem would be back wearing a disguise. The outbox helper is deliberately not transactional. It borrows the caller’s transaction and refuses to commit, and that refusal is the entire feature.

Not every event deserves a transaction

The project has two event tables, and choosing between them is the real design decision. The event_outbox holds state changes, the things correctness depends on, written synchronously inside the transaction with an at-least-once guarantee. The behavioral_events table holds telemetry, the pageviews and clicks, sent through a background queue that may drop events under load, at-most-once, because the user must never see a 500 over a lost click. The rule of thumb falls out of that: if correctness depends on the event arriving, revenue, inventory, fulfilment, it goes in the outbox; if it is nice-to-have signal where losing a fraction of a percent costs nothing, it goes in the fire-and-forget queue. Put pageviews in the outbox and you pay transactional durability for data you were happy to lose. Put “order placed” in the queue and you have written a correctness bug you only discover when an order quietly goes missing.

Each outbox row carries a versioned JSONB envelope, with a schema_version from the very first row, because it is a contract with consumers not yet built, and a time-ordered UUIDv7 key that doubles as the drainer’s cursor.

The other half: draining it

Writing the row is only half the pattern. A relay worker polls for unpublished rows and publishes them.

SELECT * FROM event_outbox
WHERE published_at IS NULL
ORDER BY created_at
FOR UPDATE SKIP LOCKED;
-- publish to Kafka, THEN stamp published_at

SKIP LOCKED lets several workers run at once, each taking a disjoint batch. The order is not negotiable: publish, then mark. A worker that crashes after publishing but before stamping will resend those rows on restart, which is the at-least-once guarantee made concrete and the reason consumers must dedupe on event_id. Mark first and you have quietly recreated the very gap you were trying to escape.

So the outbox does not eliminate the dual-write problem. It relocates it to a place where you can solve it. The unreliable publish is now backed by a durable to-do list and a retry loop, and a failed publish is no longer a lost event but an unstamped row waiting to be tried again. I traded “lost forever” for “delivered at least once,” and that is the whole victory, because at-least-once is a problem you finish off with idempotent consumers, and lost-forever is not a problem you can fix at all. The two lines I almost wrote would have made the order and its announcement into two truths that could disagree. The outbox made the announcement a row, and let one transaction speak for both.