Transactional Outbox: Two Systems, One Commit

A customer pays £80. The payment row commits, the API returns 201, the customer sees a green tick. The payment.captured event that tells the ledger to book it and the warehouse to ship it never gets published. Nothing errors. Nobody notices until reconciliation finds a payment with no ledger entry, or the customer emails asking where their order is.

Save, then publish: two writes, no transaction

Here's a capture endpoint. It records the payment, then tells the rest of the system about it.

@itsnas TypeScript

// POST /payments
async function capturePayment(req: Request) {
  const [payment] = await db`
    INSERT INTO payments (order_id, amount, status)
    VALUES (${req.body.orderId}, ${req.body.amount}, 'captured')
    RETURNING id, order_id, amount
  `
 
  await queue.publish('payment.captured', {
    paymentId: payment.id,
    orderId: payment.order_id,
    amount: payment.amount,
  })
 
  return json(201, payment)
}

main

Nas (@itsnas)

Two writes, two systems, nothing tying them together

Every test passes, because in a test nothing happens between those two awaits. In production, plenty does. A deploy kills the pod. The process runs out of memory. The broker is mid-failover and the publish times out. Any of those after the INSERT and before the publish lands, and the result is the same:

The payment commits. The publish never lands. The consumer has no way to know it missed anything.

This is the dual write problem. #postgres has a transaction; the broker has its own idea of durability; nothing spans both. Each write can succeed or fail on its own, so sooner or later one does and the other doesn't.

Flip the order and it lies the other way

The instinct is to publish first, so the event is at least out there:

@itsnas TypeScript

await queue.publish('payment.captured', event)
await db`INSERT INTO payments ...`

main

Nas (@itsnas)

Now the INSERT is the one that can fail after the other side has already happened: a constraint violation, a dropped connection, a deadlock that Postgres resolves by killing this transaction. The ledger books a payment that doesn't exist and the warehouse ships an order nobody paid for. A missing event was a reconciliation problem. A phantom event is a refund problem.

The fixes that don't close the gap

Each of these gets suggested, and each narrows the window without closing it:

Fix	Why it still leaks
Publish inside the transaction	a failed `COMMIT` still leaves a phantom event, and the transaction holds its locks while the broker answers
Retry the publish	handles a broker blip, not a crash: the retry loop dies with the process
Catch the error and roll back	the `INSERT` already committed; "undo" is another write to a system that just failed
Two-phase commit	most brokers can't take part in one

The shape of all four is the same: two systems, and a hope that both say yes. You can't make two systems commit atomically. You can make one system commit two things atomically.

Write the event where the data is

Give the database a table for events that are owed:

@itsnas SQL

CREATE TABLE outbox (
  id           uuid PRIMARY KEY,
  topic        text NOT NULL,
  payload      jsonb NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  published_at timestamptz
);
 
CREATE INDEX outbox_unpublished ON outbox (created_at)
  WHERE published_at IS NULL;

main

Nas (@itsnas)

One row per event still to be published

Then write the event into it in the same transaction as the payment:

@itsnas TypeScript

async function capturePayment(req: Request) {
  return tx(async db => {
    const [payment] = await db`
      INSERT INTO payments (order_id, amount, status)
      VALUES (${req.body.orderId}, ${req.body.amount}, 'captured')
      RETURNING id, order_id, amount
    `
 
    await db`
      INSERT INTO outbox (id, topic, payload)
      VALUES (${randomUUID()}, 'payment.captured', ${{
        paymentId: payment.id,
        orderId: payment.order_id,
        amount: payment.amount,
      }})
    `
 
    return json(201, payment)
  })
}

main

Nas (@itsnas)

Both rows commit together, or neither does

The handler doesn't talk to the broker at all any more. Kill the pod anywhere in that function and either both rows exist or neither does. If the payment is there, the obligation to announce it is there too, written down in the same place, by the same commit.

Publishing becomes someone else's job:

The API only ever writes to Postgres. The relay is the one thing that talks to the broker, and it works from rows that already committed.

The relay

The relay is a loop: claim a batch of unpublished rows, publish them, mark them sent.

@itsnas TypeScript

async function relayBatch() {
  return tx(async db => {
    const rows = await db`
      SELECT id, topic, payload FROM outbox
      WHERE published_at IS NULL
      ORDER BY created_at
      LIMIT 100
      FOR UPDATE SKIP LOCKED
    `
 
    for (const row of rows) {
      await queue.publish(row.topic, { id: row.id, ...row.payload })
    }
 
    await db`
      UPDATE outbox SET published_at = now()
      WHERE id = ANY(${rows.map(row => row.id)})
    `
    return rows.length
  })
}

main

Nas (@itsnas)

Claim, publish, mark: all inside one transaction

Run it on a short interval, and go straight back for another batch whenever it comes back full.

FOR UPDATE SKIP LOCKED is what lets you run more than one relay. FOR UPDATE locks the claimed rows; SKIP LOCKED makes a second relay step over them instead of waiting, so it claims the next hundred. Two relays never hold the same row at once.

That transaction does wrap a network call, which the last post warned against. It's fine here because of what the lock covers: only outbox rows, which only relays touch, and relays skip each other rather than queue. The API's INSERTs are new rows and never wait on it. Keep the batch bounded and give the publish a timeout, so a slow broker can't hold a batch forever.

Now crash the relay instead:

The relay dies after publishing but before marking the rows sent. Its transaction rolls back, so the next relay publishes the same rows again.

Nothing was lost. Three things were sent twice.

At least once means consumers dedupe

That's the trade the outbox makes: "maybe never" becomes "maybe twice". You can't do better than that without the broker and the database sharing a transaction, which is the thing you don't have. So the consumer has to make the second delivery harmless.

It's the same idea as an idempotency key, with the outbox row's id as the key. The relay stamps it on every message it sends; the consumer records every id it has handled:

@itsnas TypeScript

async function onPaymentCaptured(event: PaymentCaptured) {
  await tx(async db => {
    const [fresh] = await db`
      INSERT INTO processed_events (event_id) VALUES (${event.id})
      ON CONFLICT DO NOTHING
      RETURNING event_id
    `
    if (!fresh) return
 
    await db`
      INSERT INTO ledger_entries (payment_id, amount)
      VALUES (${event.paymentId}, ${event.amount})
    `
  })
}

main

Nas (@itsnas)

The dedupe and the side effect commit together

The dedupe row and the ledger entry go in one transaction for the same reason the payment and the outbox row do. Record the id in one step and do the work in another, and a crash between them is the dual write problem again, moved to the consumer.

Polling or change data capture

The relay above polls. The alternative is change data capture: a tool like Debezium reads Postgres's write-ahead log through logical replication and publishes each new outbox row as it commits. No polling queries, and latency drops from the poll interval to near zero. It doesn't change the delivery guarantee, though: after a restart the reader resumes from its last saved position and re-sends whatever came after it, so consumers still dedupe.

The cost is a new piece of infrastructure to run, and a replication slot that makes Postgres keep WAL until the reader catches up. If the reader stalls, that WAL piles up on the database's disk. Polling is a query you already know how to run and monitor. Start there and switch when the poll load or the latency actually hurts.

Where it hurts in production

Problem	Fix
Events out of order	parallel relays race before the broker sees anything; give each payment one relay (hash the outbox by payment id), or add a per-payment sequence number the consumer waits on
A dead relay is silent	the API keeps returning `201` while the outbox grows; alert on the age of the oldest unpublished row
The table grows forever	delete published rows after a retention window, or partition by day and drop old partitions
One bad row blocks every batch	count `attempts` in a separate write and move a row aside after a few failures
Payload is only an id	consumers read the payment now, not as it was; put the facts in the payload, it's a consistent snapshot already

Back to the £80. With the outbox, the payment and the promise to announce it commit as one. The pod can die, the broker can fail over, the relay can crash mid-batch, and the ledger still books the payment, possibly a few seconds late, possibly delivered twice and dropped the second time. The handler stopped asking two systems to agree and wrote everything to the one that could guarantee it.

Pairs with

Why Payment Retries Need Idempotency Keys Two Writes, One Row: Who Wins?Make a Write Endpoint Safely Retryable

Transactional Outbox: Two Systems, One Commit

Save, then publish: two writes, no transaction

Flip the order and it lies the other way

The fixes that don't close the gap

Write the event where the data is

The relay

At least once means consumers dedupe

Polling or change data capture

Where it hurts in production

When the Settlement Report Disagrees With Your Ledger

Why Does Google Obsess Over Its Slowest 1% of Requests?

You Don't Need to Store 4 Billion Users to Count Them

How Does Facebook's Distributed Like Counter Work?