The transactional outbox

In 1 minute

A booking must cause exactly one “booking confirmed” message, which later becomes an email and the tickets. Saving the booking and sending the message are two systems, so a crash between them could lose the message or send one for a booking that never committed. The outbox fixes this: the message is written as a row in Postgres, in the same transaction as the booking. A relay loop in the api then publishes pending rows to SNS and marks them sent.

Picture it

flowchart TB
  B["bookTicket"] -->|"one transaction"| T[("Postgres:<br/>booking row + outbox row")]
  R["relay loop<br/>(every api task)"] -->|"pending rows, 10 at a time"| T
  R -->|"Publish"| S["SNS topic<br/>booking-events"]
  R -->|"mark sent, or retry later"| T

How it works

1. Write both rows together. The booking transaction inserts the booking AND an outbox row whose payload is the message. Either both commit, or neither does.
2. The relay picks up work. Each api task runs one loop. It selects up to 10 pending rows with FOR UPDATE SKIP LOCKED, so two api tasks never take the same row.
3. Publish, then mark. For each row it calls SNS once. Accepted: the row gets sent_at. Refused or timed out: the row gets a later available_at.
4. Retry with growing waits. 1 s, 2 s, 4 s, … up to 60 s apart, without a limit. A committed booking is never given up on.
5. Loop. A full batch of 10 means more may wait, so the loop goes again at once; otherwise it sleeps 1 s.

In MiniTickets

The outbox row is written in apps/api/src/queries/bookings.ts → createBookingTx. The relay is apps/api/src/services/outbox.ts → startOutboxRelay and the SQL lives in apps/api/src/queries/outbox.ts → deliverBatch. The message carries the request’s correlation id, so the worker’s log lines match the booking request.

There is one honest gap: SNS can accept a message and the api can crash before the “sent” mark commits. The row is then published again. That is why every consumer must be idempotent (see DynamoDB). The outbox gives at-least-once, never “lost”.

From → to	What happens	What allows it
API app → Transactional outbox	the booking and its outbox row are written in one transaction	code createBookingTx inserts the booking and the BookingConfirmedMessage row togethercreateBookingTx
Transactional outbox → RDS Postgresonly when dataEnabled = true	the outbox is a Postgres table	code queries/outbox.ts deliverBatch reads pending rows in a transactiondeliverBatch
Transactional outbox → Booking-events SNS topic	the relay publishes each pending row once to SNS	code relayOnce → publishBookingConfirmed (PublishCommand, maxAttempts 1)publishBookingConfirmed

See the outbox on the map →

See it in AWS

Console

Nothing to click: the outbox is a Postgres table. Its relay lines are in CloudWatch → Log groups → /minitickets/dev/api.

CLI

aws logs tail /minitickets/dev/api --since 10m --filter-pattern outbox shows “accepted by SNS” and “retry scheduled” lines.

Code

The retry delays: apps/api/src/services/outbox.ts → retryInMs.

Break it

Symptom	Cause	Where to look
Bookings succeed, but no emails or tickets; outbox rows stay pending	BOOKING_EVENTS_TOPIC_ARN is unset, or the task role may not sns:Publish	api log: “outbox delivery failed”
The same message is published twice	SNS accepted it, but the “sent” mark did not commit	Consumers’ idempotency records

Say it in an interview

Say it like this (20-30 seconds)

We use a transactional outbox. The booking and an outbox row holding the “booking confirmed” message are inserted in one Postgres transaction, so they commit together. A relay loop in each api task locks up to ten pending rows with SKIP LOCKED, publishes each to SNS, and marks it sent, or schedules a retry from one to sixty seconds. That gives at-least-once delivery, so the consumers are idempotent.

Follow-ups

Why not publish to SNS right after the commit?

A crash between the commit and the publish loses the message for good. Publishing before the commit is worse: a rolled-back booking would still get an email.

What happens if SNS is down for an hour?

Bookings keep working. Their outbox rows wait and are retried every 60 s at most. When SNS is back, the relay drains them, 10 per transaction.

What is the trade-off of a relay inside the api?

No extra service to run, but each api task does background work, and messages wait up to about a second. A separate relay, or change data capture from the database log, would scale further.

Why does the relay never log the message?

The payload holds the customer’s email address. The log line has only the message id, the booking id and the correlation id.

Advanced: Details: the SNS client and the lock

The SNS client has maxAttempts: 1, a 1 s connect timeout and a 2 s request timeout (apps/api/src/queues/jobs.ts → publishBookingConfirmed). The outbox owns retries, so a slow SNS can never hold the transaction and its row locks for long. When the api stops, it waits for the current batch to commit before closing the database pool.

Checkpoint

Answer these without scrolling up. If one feels hard, reread that section.

1. Which two rows are written in one transaction?
2. Why does the relay use SKIP LOCKED?
3. How long does the relay wait between retries, at most?
4. In which case can the same message be published twice?
5. What does a reader of the api log never see in an outbox line, and why?

Redraw it from memory: booking transaction → booking row + outbox row → relay (10 rows, SKIP LOCKED) → SNS → mark sent or retry later

Try this: on the map, select the transactional outbox and follow its two arrows: one into Postgres, one to the SNS topic

Mark this page as read