Transactional Outbox

The trip is TRIP_COMPLETED in PostgreSQL. The process dies before kafka.Publish. Payment never starts.

Or Kafka accepts the message and the database rolls back: you charge Alice for a ride that does not exist.

Alice just finished a trip with Dev. Completing it must write the trip row and start payment. Those are two systems. They do not share a commit.

That pair of failures is the dual-write problem.

The main design question is:

How do we make “the business row exists” and “the event will be published” succeed or fail together, without a distributed transaction across Kafka?

We will start with the two naive publish orders, reject two-phase commit as the interview answer, then keep the event on disk next to the trip. A publisher comes later. Consumers must tolerate duplicates.

Step through both failure paths in the Outbox Pattern Visualizer. This is the same publish path used when Uber completes a trip, Twitter persists a tweet, invoices freeze a charge, and tickets transition an issue.

1. Clarify the problem

“Make the event reliable” is not a product. It is a constraint that appears inside Uber, Twitter, invoices, and tickets. The interviewer wants the invariant, not a second marketplace.

I would ask:

• What write and what event? TripCompleted, TweetCreated, InvoiceIssued?
• Is at-least-once delivery acceptable if consumers are idempotent, or are they asking for end-to-end exactly-once?
• Kafka, or any durable log?
• How stale may payment, email, or search be?
• One service and one Postgres, or many writers?
• How long do we keep published rows?

If they say “exactly-once from the database to Kafka,” I do not promise a shared commit. I promise exactly-once effects: at-least-once delivery plus an idempotent consumer. Kafka transactions do not include PostgreSQL.

If the interviewer gives no extra constraints, I would state:

Write                 Complete trip in PostgreSQL
Event                 TripCompleted { trip_id, fare, rider_id }
Broker                Kafka (any durable log is fine)
Delivery              At-least-once
Exactly-once          At the consumer, not across DB + broker
Publish lag           Seconds when healthy; minutes if Kafka is sick
Not in scope          XA / 2PC, multi-region active-active outbox

The source of truth is the trip row. Kafka is how other systems hear about a commit they did not make.

2. Functional requirements, NFRs, and edge cases

The system must:

1. Complete a trip exactly once (a compare-and-set on status).
2. Persist, in that same commit, that TripCompleted must be published.
3. Publish the event to Kafka eventually.
4. Charge Alice once.
5. Fan the same event to notifications and analytics without those consumers deciding whether the trip exists.
6. Retry unpublished rows until they succeed or are declared poison.
7. Let an operator see pending events.

The first version does not include:

• publishing from inside the HTTP transaction;
• exactly-once across Postgres and Kafka;
• generating PDFs or sending email on the request thread;
• treating Redis as the record of “event sent.”

Non-functional requirements

trips                 source of truth
outbox_events         durable “must publish” next to the trip
Kafka                 derived fan-out
payments              derived; unique on trip_id
Redis GEO / caches    derived; repair from SQL
email / PDF / search  after commit, never in the request transaction

Scale

These are planning values for a busy city, not a global 10-K.

Peak trip completes              200 / s
State-change events per trip     ~5
Peak outbox inserts              ~1,000 / s
Typical payload                  ~1 KB

A thousand inserts a second is ordinary Postgres and an easy Kafka produce rate. The number that matters is backlog while Kafka is down, not steady-state QPS.

1 hour unpublished   1,000 × 3,600 × 1 KB ≈ 3.6 GB
1 day unpublished    ≈ 86 GB

A day of Kafka outage is still disk, not a science project — but you alert on pending age, not after the disk fills. Published rows kept for seven days without cleanup are hundreds of gigabytes. Retention is a design choice, not an afterthought.

APIs and entities

Complete is the write that people get wrong:

Request:

POST /trips/tr_9/complete
Idempotency-Key: alice-complete-tr_9

Response:

200 OK
{ "trip_id": "tr_9", "status": "TRIP_COMPLETED", "fare": 1840 }

The HTTP handler commits the trip and the outbox row, then returns. It does not talk to Kafka.

trips            id, rider_id, driver_id, status, fare, version
outbox_events    pending publish queue in the same database
payments         trip_id UNIQUE, amount, provider_key
processed_events (consumer, event_id) UNIQUE

Edge cases

• Process dies after COMMIT, before kafka.Publish.
• Kafka acks, then the publisher dies before marking the row published.
• Alice’s client retries POST /complete.
• Kafka is down for an hour.
• Two publisher pods claim the same pending row.
• A payload cannot deserialize (poison).
• The outbox table grows until Postgres disk is the incident.
• Payment sees the same TripCompleted twice.
• Clock skew makes ORDER BY created_at slightly unfair; it must not drop rows.
• A consumer is down; Kafka retains; the outbox is already published.

3. The dual-write problem

Two systems. No shared commit. There are only two orders, and both lose.

Failure A — commit, then die before Kafka

API                         Postgres                    Kafka
 │                              │                         │
 │  BEGIN                       │                         │
 │  UPDATE trips COMPLETED      │                         │
 │  COMMIT ────────────────────►│                         │
 │                              │  trip exists            │
 │  process dies                │                         │
 │  kafka.Publish  (never)      │                         │
 │                              │                         │  (no message)

The trip is real. Payment never starts. Support sees a finished ride and an empty payments table. Replay is a human process unless you stored something durable besides the trip status — and status alone does not tell you “we already tried to publish.”

Failure B — Kafka sent, then the database rolls back

API                         Kafka                       Postgres
 │                            │                             │
 │  kafka.Publish ───────────►│  TripCompleted              │
 │  BEGIN                     │                             │
 │  UPDATE trips              │                             │
 │  ROLLBACK (conflict,       │                             │
 │            deadlock,       │                             │
 │            crash)          │                             │
 │                            │  payment consumer charges   │
 │                            │                             │  trip never existed

Now you charge for a ride that is not in the database. Refunds and “the event is the source of truth” speeches do not help Alice.

The same pair appears if you swap Kafka for SQS, HTTP webhooks, or “just email from the API.” The second system is not in the SQL transaction. That is the whole problem.

Walk both sequences in the visualizer before you draw boxes.

4. Why 2PC / XA is the wrong interview answer

Two-phase commit is the textbook way to make two resource managers agree: every participant prepares (votes yes and holds locks), then a coordinator tells them to commit or abort. XA is the usual API for that dance.

Coordinator
   │
   ├─ PREPARE Postgres     lock trip row, wait
   ├─ PREPARE Kafka        …if the broker even plays XA
   │
   └─ COMMIT both          or abort both

I would not lead with this.

Kafka is a poor XA partner. Kafka’s own transactions coordinate producers and consumers inside Kafka. They do not enroll a Postgres row. Pretending they do is a confusion, not a design.

Locks become availability. Prepare holds the trip row while the network talks to the broker. A slow coordinator stalls completes. A dead coordinator leaves in-doubt transactions that operators have to resolve.

Partial failure still exists. The coordinator can die after one participant committed and before the other. You traded “I wrote a poller” for “I run a distributed transaction manager.”

Operational coupling. Postgres upgrades, Kafka upgrades, and the transaction coordinator now fail together. Interviews that want an outbox are testing whether you refuse that coupling.

State the opinion and move: I will not run XA between Postgres and Kafka. I will write an outbox row in the same SQL transaction as the trip.

5. Start with publish-after-commit

The simplest “fix” after Failure B is: never publish until the trip is committed.

BEGIN
  UPDATE trips
    SET status = 'TRIP_COMPLETED', fare = 1840
  WHERE id = 'tr_9' AND status = 'TRIP_STARTED'
COMMIT

kafka.Publish(TripCompleted)     // process can die here

Failure B is gone. You will not charge a trip that rolled back.

Failure A is still there. Draw it again, because this is the design most candidates stop at:

t0   COMMIT trips  →  tr_9 is COMPLETED
t1   process SIGKILL / OOM / deploy
t2   Publish never ran
t3   payment consumer idle

Retries of the HTTP request do not save you. The second POST /complete finds status = TRIP_COMPLETED and returns 200 (or 409). It does not insert a new event. The lost publish stays lost.

You can try “publish in a try after commit, and if it throws, write to a retry table.” That retry table is an outbox, invented under pressure, without a transaction tying it to the trip. If the process dies between COMMIT and the retry insert, you are back at Failure A.

So the next requirement is: the “must publish” record has to commit with the trip, not after it.

6. The outbox table in the same transaction

Keep a table of events in the same database as trips. Insert the event in the same transaction as the status change.

BEGIN
  UPDATE trips
    SET status = 'TRIP_COMPLETED', fare = 1840, completed_at = now()
  WHERE id = 'tr_9' AND status = 'TRIP_STARTED'

  INSERT INTO outbox_events (
    id, aggregate_id, event_type, payload, status
  ) VALUES (
    'evt_44', 'tr_9', 'TripCompleted',
    '{"trip_id":"tr_9","fare":1840}',
    'PENDING'
  )
COMMIT

Two outcomes, both safe:

Rollback   no trip change, no outbox row, nothing to publish
Commit     trip is COMPLETED and evt_44 is on disk

Kafka being down does not block Alice’s complete. The row waits.

                    same transaction
                   ┌─────────────────┐
                   │  trips          │
 API ──BEGIN──────►│  outbox_events  │──COMMIT──► disk
                   └─────────────────┘
                            │
                            │  later, not in this request
                            ▼
                      publisher → Kafka

The HTTP response means “the trip and the intent to publish are durable.” It does not mean “payment has run.”

If the UPDATE matches zero rows, you do not insert an outbox row. The CAS is the lock. A retried complete does not emit a second TripCompleted.

This is the pattern Uber uses on complete, Twitter uses on post, and the same insert invoices and tickets put next to the business write.

7. The publisher: poll vs LISTEN/NOTIFY, SKIP LOCKED

A separate worker reads PENDING rows, publishes, then marks them published. It is not the API request.

Do not publish from inside the request transaction. That holds the trip row (and a connection) while the network talks to Kafka. Completes stall when the broker is slow. The outbox exists so the request can finish.

Poll

loop every 200 ms:
  claim a batch of PENDING rows
  publish each to Kafka
  on ack:  SET status = PUBLISHED, published_at = now()
  on error: increment retry_count, leave PENDING

Polling is boring and correct. Worst-case extra lag is one interval. It works if the listener process was down during the commit — the next poll still sees the row.

LISTEN / NOTIFY

Postgres can NOTIFY outbox on insert. The publisher wakes immediately.

NOTIFY is a hint, not a queue. Messages are lost if no listener is connected. Payload size is limited. After a restart you have heard nothing.

NOTIFY  →  low latency when the publisher is alive
POLL    →  catch-up after crash, deploy, or a missed notify

Use notify to go faster, poll so you still go. Do not make notify the only path.

SKIP LOCKED

Two publisher pods must not wait on the same row.

SELECT id, event_type, payload, aggregate_id
FROM outbox_events
WHERE status = 'PENDING'
  AND retry_count < 20
ORDER BY created_at
LIMIT 100
FOR UPDATE SKIP LOCKED

FOR UPDATE claims the rows until this transaction ends. SKIP LOCKED means the second worker takes the next free rows instead of blocking. That is how you scale the publisher horizontally without a separate lease store.

PENDING: evt_44  evt_45  evt_46  evt_47
           │                │
        worker A         worker B
        SKIP LOCKED      SKIP LOCKED

Mark published only after a successful produce (broker ack). If you mark first, you recreate Failure A with extra steps.

Backoff with jitter on errors. A hot poison row must not starve the batch — that is the next section’s cousin, section 9.

8. At-least-once, and why consumers must be idempotent

The publisher can die in the one-bit window after Kafka accepts the message and before PUBLISHED is committed.

publisher
  SELECT … FOR UPDATE SKIP LOCKED     // holds evt_44
  kafka.Publish(evt_44)               // broker has it
  process dies
  // status still PENDING

The next poll publishes evt_44 again. That is at-least-once. It is not a bug in the outbox. It is the remaining uncertainty after you removed dual-write loss.

Exactly-once effects live in the consumer.

Payment:

TripCompleted
  INSERT INTO payments (trip_id, amount, idempotency_key)
  VALUES ('tr_9', 1840, 'tr_9')
  ON CONFLICT (trip_id) DO NOTHING

  charge provider with the same key

One payment row. A provider timeout retries the same key; you do not infer failure from silence.

A generic consumer uses a processed-event table:

processed_events
  consumer_name   'billing'
  event_id        'evt_44'
  PRIMARY KEY (consumer_name, event_id)

Insert first (or in the same transaction as the side effect). Conflict means “already done.” Search upserts by document id. Notifications key on (template, trip_id).

See the same retry-vs-dedup idea in the idempotency-key visualizer. The HTTP Idempotency-Key on POST /complete stops a double complete. The consumer key stops a double charge from a double Kafka delivery. You need both.

Do not require exactly-once Kafka as a substitute for this table. Broker-side transactions do not make INSERT payments automatic.

9. Ordering, partitions, and poison messages

Ordering

Alice’s trip should not arrive as TripCompleted before TripStarted on the consumer that cares. Kafka only preserves order inside a partition.

Key the produce by trip_id (the aggregate id):

topic trip.events
  key = tr_9
  ─────────────────────────────────
  p0   tr_1  tr_4  tr_9 …
  p1   tr_2  tr_7 …
  p2   tr_3  tr_9  ← same key, same partition

All events for tr_9 land on one partition and stay in publish order. Events for tr_2 may interleave in wall-clock time. That is fine. A global total order is not a ride-sharing requirement and does not scale.

If two publishers could publish tr_9 events out of commit order, you have a publisher bug. Claim-and-publish per row is enough when one trip emits events over minutes, not in a tight race. If you ever write many events for one aggregate in one transaction, give them a sequence and have consumers ignore a stale sequence.

Poison messages

A row that throws on every publish (bad JSON, huge payload, ACL deny) must not block ORDER BY created_at forever.

retry_count < 20     stay in the poll query
retry_count >= 20    status = FAILED, page an owner

Failed rows are a dead-letter workflow with an alert, not an invisible graveyard. An operator inspects last_error, fixes the payload or the topic ACL, and resets to PENDING.

Do not auto-delete poison. You would recreate Failure A on purpose.

10. CDC / Debezium as an alternative

Change-data capture (CDC) reads the database write-ahead log — the same bytes Postgres already writes to survive a crash — and turns committed changes into events. Debezium is the usual connector.

Postgres WAL
    │
    ▼
Debezium / CDC
    │
    ▼
Kafka

Same idea as the outbox: the commit is the source of truth. If the transaction rolled back, it never hit the WAL as a commit. If it committed, CDC will see it after a crash.

Two ways to use it:

CDC on trips          every column change becomes a message
                      (schema coupling, updates, deletes, PII)
CDC on outbox_events  you still insert a deliberate event;
                      the poller is replaced by the connector

Outbox-plus-CDC is common in large fleets: the application writes a clear event, the connector publishes it, you skip SELECT PENDING. The table remains inspectable: SELECT * FROM outbox_events WHERE status = 'PENDING' still means something if you mark or delete after publish — or you treat “row exists” as pending and delete after the connector confirms.

I mention CDC as same guarantee, different plumbing. In a 45-minute interview I draw the SQL outbox first. It is easier to explain, to debug, and to run on a laptop. CDC wins when many tables must emit changes and you do not want a poller per service.

CDC is not a free exactly-once switch. The connector can restart and re-emit. Consumers stay idempotent.

11. What still belongs in the request vs after commit

The request is for invariants. After commit is for work that may fail and retry.

In the request transaction

authenticate / authorize
CAS on trips.status
compute fare you are willing to freeze
INSERT outbox_events
COMMIT
return 200

Authorization and the CAS belong here because a completed trip without a rider check is a product bug, and a TripCompleted without a successful CAS is a lie.

After commit (publisher or other workers)

kafka.Publish
charge the card
send the receipt email
render a PDF
invalidate a cache
put Dev back in Redis GEO
update search

Invoices freeze money in SQL and render the PDF later. Tickets commit the transition and let search catch up. Uber adds Dev back to GEO after the trip row says AVAILABLE, not instead of it.

Cache invalidation in the request is optional and still racy. If you delete a Redis key and then roll back, the cache is empty and will refill from the old row. If you invalidate after commit, you can crash and serve stale for one TTL. Stale GEO is recoverable. A charged phantom trip is not.

Do not “just send the email in the handler” because the outbox felt heavy. Email is a second system. It is Failure A with a nicer SMTP error.

12. Data model for outbox_events

outbox_events
  id              UUID PRIMARY KEY
  aggregate_type  TEXT        -- 'trip'
  aggregate_id    TEXT        -- 'tr_9'
  event_type      TEXT        -- 'TripCompleted'
  payload         JSONB
  partition_key   TEXT        -- usually aggregate_id
  status          TEXT        -- PENDING | PUBLISHED | FAILED
  retry_count     INT
  last_error      TEXT
  created_at      TIMESTAMPTZ
  published_at    TIMESTAMPTZ

INDEX outbox_poll (status, created_at)
      WHERE status = 'PENDING'

A partial index keeps the poll cheap when most rows are published. id is the event id consumers store. Do not reuse it.

payload is a snapshot of what consumers need, not “go read the trip again if you want the fare.” If fare is corrected later, that is a new event, not a silent edit of evt_44.

Uniqueness: the CAS on trips already prevents a second TripCompleted. You can add a unique (aggregate_id, event_type) for events that must occur once. Do not unique-key events that legitimately repeat (LocationUpdated).

Retention:

PUBLISHED older than 7 days   delete or archive
FAILED                        keep until an owner acts
PENDING                       never expire; that is a lost event

A cleanup job is part of the design. An unbounded outbox becomes a Postgres incident that looks like “the pattern does not scale.”

Related tables the interviewer will ask for:

trips              source of truth
payments           UNIQUE (trip_id)
processed_events   PRIMARY KEY (consumer_name, event_id)

13. Failure table

The invariant to say out loud: a committed trip is never waiting on Kafka to exist. A committed trip may wait on Kafka to be noticed.

14. Observability: pending age and publish lag

If you only alert on “publisher process up,” you will miss a silent SELECT that returns zero because of a bad WHERE.

Oldest pending age — now() - min(created_at) FILTER (WHERE status = 'PENDING'). This is the outbox SLO. Five minutes on a healthy city cluster is an incident. Five seconds is normal poll jitter.

Pending count — backlog size. Age without count can hide a thundering pile of young rows; count without age can hide one stuck old row.

Publish lag — published_at - created_at on newly published rows. This is how long the trip sat on disk before Kafka. Separate from Kafka consumer lag, which is how far billing is behind the log.

complete ──► outbox created_at
                │
                │  pending age (if still PENDING)
                │  publish lag (once PUBLISHED)
                ▼
             Kafka produce
                │
                │  consumer group lag
                ▼
             payment effect

Also ship:

publish error rate
retry_count histogram
FAILED (DLQ) depth
dedup / conflict rate on payments
outbox table size and disk

Trace id goes into payload or a column so a complete request and a late payment share one search. Twitter treats end-to-end visibility lag the same way: outbox age, then broker lag, then consumer lag. No single number proves the pipeline.

A dashboard that only shows Kafka consumer lag will look green while PENDING rows never leave Postgres.

15. Final architecture

 Alice / Dev
      │
      ▼
  Ride API
      │
      │  BEGIN
      │    CAS trips → TRIP_COMPLETED
      │    INSERT outbox_events PENDING
      │  COMMIT
      │  200 OK
      ▼
 PostgreSQL
   trips              source of truth
   outbox_events      durable publish intent
   payments           unique trip_id
      │
      │  poll / LISTEN+NOTIFY
      │  FOR UPDATE SKIP LOCKED
      ▼
 Outbox publisher
      │
      │  produce key = trip_id
      ▼
 Kafka  trip.events
      │
      ├──────────────┬──────────────┐
      ▼              ▼              ▼
  Payment         Notify         Analytics
  idempotent      idempotent     append-only
  on trip_id      on trip_id     OK to see twice

Request path:

POST /trips/tr_9/complete
  authorize
  BEGIN
    UPDATE trips … WHERE status = TRIP_STARTED
    INSERT outbox TripCompleted
  COMMIT
  return 200

Publish path (later):

claim PENDING
publish to Kafka
ack → PUBLISHED
error → retry / FAILED

Consumer path:

TripCompleted
  insert payment ON CONFLICT DO NOTHING
  charge with key tr_9

CDC, if you graduate the plumbing, replaces the poller box with a WAL connector. The API transaction does not change.

16. Interview-ready summary

How to walk through in 10–15 minutes

0–2 min. Dual-write: die after commit, or charge after rollback.
2–5 min. Reject XA. Show publish-after-commit still loses the event.
5–9 min. Outbox in the same transaction. Publisher, SKIP LOCKED, at-least-once.
9–12 min. Idempotent payment, partitions by trip_id, poison.
12–15 min. CDC as the same idea, failure table, pending age.

Key decisions to remember

1. Ask which event and whether at-least-once plus idempotent consumers is the contract.
2. Do not publish in the same breath as a SQL commit.
3. Do not offer 2PC/XA as the design.
4. Publish-after-commit still loses the event on crash.
5. Insert outbox_events in the same transaction as the business row.
6. The request does not talk to Kafka.
7. Poll always; NOTIFY is an optional wake-up.
8. FOR UPDATE SKIP LOCKED so publishers do not block each other.
9. Mark published only after a broker ack — delivery is at-least-once.
10. Consumers dedupe (trip_id, or (consumer, event_id)).
11. Partition by aggregate id; poison goes to FAILED, not an infinite head-of-line.
12. Alert on oldest pending age, not only Kafka consumer lag.

Likely interviewer follow-up questions

• Why not publish inside the SQL transaction?
• Why is XA the wrong answer here?
• What if the publisher dies after Kafka acks?
• How do two publisher pods avoid claiming the same row?
• How do you get per-trip order?
• What do you do with a payload that always fails?
• Outbox table vs Debezium on trips?
• What lives in the request vs after commit?
• How long do you keep PUBLISHED rows?
• What if Kafka is down for six hours?
• How is this different from an HTTP idempotency key?
• How would you shard the outbox at much higher QPS?

Senior-level points that differentiate the answer

• Name both dual-write sequences before drawing the outbox.
• Call exactly-once an effect at the consumer, not a broker feature that includes Postgres.
• NOTIFY without a poll is a lost-wakeup bug.
• Pending age and publish lag are different from consumer lag.
• CDC is the same invariant with different plumbing; still idempotent.
• Retention and disk are part of the pattern; unbounded PUBLISHED rows are an outage.
• Recurring product writes — Uber complete, Twitter post, invoice, ticket transition — all use this insert.

A 1–2 minute verbal answer

I will not publish to Kafka in the same breath as a SQL commit. Completing Alice’s trip compare-and-sets TRIP_COMPLETED and inserts a TripCompleted outbox row in one Postgres transaction. If that transaction rolls back, there is no event. If it commits, the event is on disk next to the trip. The HTTP handler returns then.

A publisher polls PENDING rows with FOR UPDATE SKIP LOCKED, optionally woken by LISTEN/NOTIFY, produces to Kafka keyed by trip_id, and marks published only after an ack. A crash after the ack republishes, so delivery is at-least-once. Payment inserts on trip_id and charges with that same key so a duplicate does not double-bill. If Kafka is down, the trip is still correct and the outbox waits. I watch oldest pending age. I would not run XA between Postgres and Kafka.

Open the Outbox Pattern Visualizer and the idempotency-key visualizer while you talk. For the hour-long framework, see the System Design Interview Complete Guide. Drill the one-minute version from the questions hub.