01 · Definition
What is the transactional outbox pattern?
The transactional outbox pattern solves a deceptively simple problem: when code changes the database and also needs to send a message, how do you make sure both happen or neither does? Instead of calling the WhatsApp Cloud API, or publishing to a queue, straight after the commit, the code inserts a row describing the message into an outbox table inside the same database transaction as the business change. The database's atomicity then guarantees that the order update and the pending notification are saved together or not at all.
A separate relay process reads unsent outbox rows, publishes each one to the message queue or hands it to the sender, and marks the row as sent. The relay can poll the table — SELECT … FOR UPDATE SKIP LOCKED lets several relays share the work without stepping on each other — or follow the database's change log with change data capture. If a relay crashes after publishing but before marking the row, the row is published again on restart: the outbox gives at-least-once delivery, never exactly-once.
That is why the outbox and the idempotency key are partners, not alternatives. The outbox fixes lost writes: the order confirmed but its COD confirmation never sent, or a message sent for an order whose transaction then rolled back. The idempotency key fixes duplicate sends: the consumer records each outbox ID it has processed and skips repeats. Passing the same ID in biz_opaque_callback_data lets status webhooks map straight back to the outbox row.
Key takeaways
- The transactional outbox pattern writes the outbound message to an outbox table in the same transaction as the business change, then relays it.
- It solves the dual-write problem: no order is confirmed without its notification queued, and no notification goes out for a rolled-back order.
- Relays deliver at least once, so the sender must be idempotent: use the outbox row ID as the deduplication key.
- The outbox solves lost writes; an idempotency key solves duplicate sends. Reliable pipelines use both.
- Relay by polling with
FOR UPDATE SKIP LOCKED, or by change data capture on the database log.
02 · Code samples
The transactional outbox pattern in code
CREATE TABLE outbox (id bigserial PRIMARY KEY,aggregate_id text NOT NULL, -- e.g. the order IDkind text NOT NULL, -- e.g. 'whatsapp.cod_confirmation'payload jsonb NOT NULL,created_at timestamptz NOT NULL DEFAULT now(),sent_at timestamptz);CREATE INDEX outbox_unsent ON outbox (id) WHERE sent_at IS NULL;-- In the request that confirms the order:BEGIN;UPDATE orders SET status = 'confirmed' WHERE id = 'ORD-58213';INSERT INTO outbox (aggregate_id, kind, payload)VALUES ('ORD-58213', 'whatsapp.cod_confirmation','{"to": "919812345678", "template": "cod_confirmation", "params": ["ORD-58213", "1,499"]}');COMMIT;
The order change and the pending message commit together. The partial index keeps the relay's query fast however large the table grows.
03Playground
A working model of Transactional Outbox. Use its controls to replay it step by step.
Follow one order confirmation through the outbox
- Order service
- Databaseorders + outbox
- Outbox relay
- Send workeridempotent by outbox ID
- Cloud API
Step 10 of 10Event
Cloud APIOrder service
Status webhook echoes outbox-48213
- Request — solid arrow — a call that expects an answer
- Response — dashed arrow — the answer coming back
- Event — dotted arrow — something happened and is pushed out
- Async — double head — fire-and-forget, nobody waits
Step 10 of 10: Cloud API to Order service — Status webhook echoes outbox-48213
04 · Operation
How does the transactional outbox pattern work?
Create an outbox table
Add a table with an ID, the aggregate it belongs to (such as the order ID), a message kind, the payload as JSON, a created timestamp and a nullable sent timestamp.
Write in the same transaction
In the code path that confirms the order, insert the outbox row inside the same transaction as the order update, so both commit or both roll back.
Relay pending rows
A relay selects unsent rows in ID order, locking them so parallel relays skip each other's batch, publishes each one to the queue and then sets
sent_at.Send idempotently
Before calling the Cloud API, the worker checks whether this outbox ID was already processed, and it passes the ID in
biz_opaque_callback_dataso status webhooks can be matched back to the row.Clean up and monitor
Delete or archive sent rows after a retention period, and alert on the age of the oldest unsent row — the outbox's equivalent of consumer lag.
05 · Rationale
Why does the transactional outbox pattern matter?
Without an outbox, every 'update the database, then send the message' sequence has a gap. A crash, a deploy or a network blip between the two steps leaves a confirmed order with no confirmation message — or, if you send first, a customer told about an order that was then rolled back. At scale these gaps happen every day and surface as support tickets nobody can explain. The outbox closes the gap with nothing more exotic than a table and a transaction you already have.
06 · Specifications
The transactional outbox pattern: key facts and limits
| Guarantee | Business change and message saved atomically |
|---|---|
| Delivery to the queue | At least once: duplicates possible after a relay crash |
| Relay options | Polling with FOR UPDATE SKIP LOCKED, or change data capture |
| SKIP LOCKED support | PostgreSQL 9.5+, MySQL 8.0+ |
| Deduplication key | Outbox row ID, carried in biz_opaque_callback_data |
| Health metric | Age of the oldest unsent row |
07 · Worked example: COD confirmations for a Jodhpur handicrafts seller
Example: COD confirmations for a Jodhpur handicrafts seller
A Jodhpur handicrafts seller confirms cash-on-delivery orders and sends a cod_confirmation utility template asking the buyer to confirm before dispatch. Before the outbox, a deploy that restarted the app mid-request occasionally left orders marked confirmed with no message sent, and those parcels shipped unconfirmed and came back as returns. Now the confirmation step writes the order status and an outbox row in one transaction. If the app restarts mid-request, either both rows exist and the relay sends the message seconds later, or neither exists and the order stays unconfirmed for the checkout to retry. A relay batch replayed after a crash is harmless: the worker sees the outbox ID already processed and skips it.
08 · Cautions
Common mistakes with the transactional outbox pattern
Calling the API inside the transaction
Sending the WhatsApp message before COMMIT means a rollback cannot unsend it, and a slow API call holds database locks. Only write the outbox row in the transaction; send afterwards.
Assuming exactly-once
The relay can publish a row and crash before marking it sent. Make the worker idempotent by outbox ID, or customers will occasionally get two confirmations.
Letting the outbox grow forever
Sent rows pile up and slow the relay. Use a partial index on unsent rows and delete or archive sent ones on a schedule.
Losing order where it matters
Parallel relays and workers can reorder messages for the same order. If 'dispatched' must never overtake 'confirmed', route each aggregate through one partition or check state before sending.
Keeping more personal data than needed
Outbox payloads persist phone numbers and template parameters. Store only what the send needs and purge sent rows promptly.
09 · Questions and answers
Transactional Outbox FAQs
- What problem does the transactional outbox pattern solve?
- It solves the dual-write problem. Updating a database and sending a message touch two separate systems, and a crash between them leaves one done and the other not; writing the message to an outbox in the same transaction makes the pair atomic.
- What is the difference between the outbox pattern and an idempotency key?
- The outbox prevents lost or phantom messages; an idempotency key prevents duplicates. Because the outbox delivers at least once, you need the key to make the repeats harmless.
- Polling or change data capture: which relay should I use?
- Polling is simpler and works on any SQL database; change data capture has lower latency and no polling load but needs access to the database log and more infrastructure. Many teams start by polling every second and move to CDC at high volume.
- Does the outbox pattern guarantee message order?
- Not across parallel relays and workers. The table keeps insert order, but if order matters per customer or per order, route messages for the same aggregate through one partition or check current state before sending.
10 · Recommended practice
Best practices for the transactional outbox pattern
- Alert when the oldest unsent row is more than a minute or two old: the relay is stuck.
- Store the exact Cloud API request in the payload so replays are deterministic.
- Record the returned
wamidagainst the outbox row for traceability from order to delivery. - Use the same pattern for webhook-triggered changes: write the state change and its follow-up message in one transaction.