How do you design a transactional outbox for database and message consistency?

1. Prompt

When an order is created, the order service must commit state and publish an OrderCreated event for inventory and notification consumers. The database and broker have no shared two-phase commit. Design a transactional outbox so a process crash does not silently lose the event, while duplicate delivery and relay backlog remain manageable.

2. Constraints and clarifications

• Order data and the outbox table share one local transactional database.
• The broker provides at-least-once delivery, without global ordering or transactional sends.
• Eventual consistency is acceptable; the inventory consumer must be idempotent.
• Explain per-aggregate ordering, whether cross-aggregate ordering is needed, and retention/deletion windows.

3. Core approach

Write the order change and one outbox row in the same database transaction. The row carries a unique event_id, aggregate key, event type, sequence, payload, creation time, and publish state. A successful commit makes both business data and the pending event durable; a rollback exposes neither, removing the application-level dual-write window.

An independent relay polls or subscribes to the outbox, publishes to the broker, and then marks the row sent. If the process crashes between broker acknowledgement and the status update, the event can be published again. Consumers therefore deduplicate by event_id instead of assuming exactly-once delivery.

4. Reference implementation

createOrder(command):
  begin transaction
  order = insert orders(...)
  event = insert outbox(
    event_id=uuid(), aggregate_id=order.id,
    aggregate_version=order.version, type="OrderCreated",
    payload=serialize(order), status="pending"
  )
  commit
  return order.id

relayBatch():
  rows = select pending outbox rows
         order by aggregate_id, aggregate_version, created_at
         for update skip locked limit BATCH_SIZE
  for row in rows:
    try:
      broker.publish(key=row.aggregate_id, id=row.event_id, body=row.payload)
      mark_sent(row.event_id)  // conditional update
    except transient_error:
      increment_attempts_and_schedule_retry(row.event_id)

consume(message):
  begin transaction
  inserted = insert processed_messages(message.id) on conflict do nothing
  if inserted:
    apply_business_change(message)
  commit

5. Reliability and correctness

If the business transaction commits but the relay crashes before publishing, the pending row is found by a later scan. If publishing succeeds but the status update crashes, the next pass republishes it. The end-to-end semantic is therefore at least once; a consumer deduplication table or business idempotency key must share a transaction with the consumer’s business update.

Per-aggregate ordering can use a monotonic version and partitioning by aggregate key; do not promise global ordering across aggregates. SELECT ... FOR UPDATE SKIP LOCKED or lease fields prevent multiple relays from claiming the same row, but they do not replace consumer idempotency. Index state, retry time, and creation time, then archive or safely delete old rows to bound table growth.

6. Follow-ups and traps

• Deleting a row immediately after a “successful” publish can create an unrecoverable gap if the acknowledgement was lost; persist send state or retain an audit record first.
• A broker acknowledgement timeout does not prove the broker missed the message, so retries must tolerate duplicates.
• Writing the database first and then calling the broker inside application error handling still has a dual-write race; try/catch cannot make it atomic.
• If the outbox and business tables cannot share a transaction boundary, use CDC, transactional messaging, or redefine the consistency guarantee.

7. Further reading

Compare polling relays with CDC relays: polling is simpler to deploy but adds scans and latency, while CDC lowers latency at the cost of log-capture and operational dependencies. Discuss poison messages, exponential backoff, dead-letter queues, pending-age monitoring, and consumer schema compatibility.

8. Interview scoring points

Can locate the dual-write window

The candidate should explain why ordinary local transactions cannot commit a database update and broker send together, then place the order change and outbox row in one transaction.

Can explain at-least-once and idempotency

They should describe the relay crash window that creates duplicates and make the consumer deduplicate by event ID in the same transaction as its business update.

Can handle ordering and concurrency

They should distinguish per-aggregate order from global order and explain how partition keys, versions, locks, or leases constrain concurrent claims.

Can cover operational boundaries

They should propose retry backoff, dead letters, backlog alerts, archival cleanup, and schema evolution rather than stopping at a table definition.