Transactional Outbox: When the Database Commits but Kafka Never Hears

The dual-write problem

await db.orders.insert(order);        // committed ✅
await kafka.publish("OrderPlaced", e); // process dies here ❌

// Database:  the order exists
// Kafka:     never heard of it
// Inventory: never reserved
// Nobody logged an error

Flipping the order does not help — then you can publish an event for an order that failed to commit, and downstream services act on something that never happened. Wrapping the publish in a retry does not help either, because the process can die before the retry runs.

One transaction, two tables

The outbox is an ordinary table in the same database as your business data. The event is inserted in the same transaction as the state change, so either both exist or neither does. A separate relay process reads unpublished rows and pushes them to the broker.

Outbox schema

CREATE TABLE outbox (
  id            BIGSERIAL PRIMARY KEY,
  aggregate_type TEXT NOT NULL,          -- 'order'
  aggregate_id   TEXT NOT NULL,          -- used as the partition key
  event_type     TEXT NOT NULL,          -- 'OrderPlaced'
  payload        JSONB NOT NULL,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  published_at   TIMESTAMPTZ
);

CREATE INDEX outbox_unpublished
  ON outbox (id) WHERE published_at IS NULL;

• aggregate_id becomes the broker partition key, which preserves per-order ordering.
• The partial index keeps the polling query fast even when the table is large.
• Include an event version field from day one; schemas always change.

Polling relay vs change data capture

Start with polling. It is fifty lines of code and covers the correctness problem completely. Move to CDC when the poll interval or the write amplification starts to hurt.

At-least-once, ordering and dedup

The relay can publish successfully and crash before marking the row. That is unavoidable, so the guarantee is at-least-once, and consumers must be idempotent.

1. Put a stable event_id in every message.
2. Consumers keep a processed_events table and insert the id in the same transaction as their side effect. Duplicate id → primary key violation → skip.
3. Partition by aggregate id so events for one order stay ordered.
4. Never assume global ordering across aggregates; you will not get it.

Implementation sketch

// write path
await db.transaction(async (tx) => {
  const order = await tx.orders.insert(input);
  await tx.outbox.insert({
    aggregate_type: "order",
    aggregate_id: order.id,
    event_type: "OrderPlaced",
    payload: toEvent(order),
  });
});

// relay loop
async function relayBatch() {
  await db.transaction(async (tx) => {
    const rows = await tx.query(`
      SELECT * FROM outbox
      WHERE published_at IS NULL
      ORDER BY id
      LIMIT 200
      FOR UPDATE SKIP LOCKED`);            // safe with N relay instances

    for (const row of rows) {
      await broker.publish(row.event_type, row.payload, { key: row.aggregate_id });
      await tx.query("UPDATE outbox SET published_at = now() WHERE id = $1", [row.id]);
    }
  });
}

FOR UPDATE SKIP LOCKED is the detail that makes the relay horizontally scalable without duplicating whole batches.

Operating it

• Alert on outbox lag — now() - min(created_at) where unpublished. A growing value means the broker or relay is down and nobody downstream knows.
• Prune published rows. Delete or partition by day; this table grows at the rate of your business.
• Poison messages. Track attempt counts and move repeated failures aside, or one bad payload blocks the whole stream.
• Replay. Because the outbox is durable history, you can rebuild a downstream projection by republishing a time range.

Interview framing

“Order creation and the OrderPlaced event go into one transaction — orders table plus outbox table. A relay polls with SKIP LOCKED and publishes keyed by order id, giving at-least-once delivery with per-order ordering. Consumers dedup on event id inside their own transaction. We alert on outbox lag, which is the single metric that tells us the pipeline is healthy.”