Transactional outbox

A service often needs to change its database and publish an event: "order created", "invoice paid". Doing both from application code is a dual write, and there's no safe order:

• Commit, then publish: a crash in between loses the event.
• Publish, then commit: a failed commit leaves an event for a change that never happened.

The database and the broker can't share a transaction, so one of the two must become the source of truth.

#The pattern

1. In the same transaction as the business change, insert the event into an outbox table.
2. A separate relay reads unpublished outbox rows, publishes them to the broker, and marks them published.

BEGIN;
UPDATE invoices SET status = 'paid', paid_at = now() WHERE id = 'inv_31';
INSERT INTO outbox (topic, key, payload)
VALUES ('invoices', 'inv_31', '{"type":"invoice.paid","invoice_id":"inv_31"}');
COMMIT;

-- The relay: claim a batch without blocking other relays.
SELECT id, topic, key, payload FROM outbox
WHERE published_at IS NULL ORDER BY id LIMIT 100
FOR UPDATE SKIP LOCKED;
-- publish, wait for the broker's acknowledgement, then:
UPDATE outbox SET published_at = now() WHERE id = ANY($1);

If the change commits, the event is guaranteed to be published eventually; if it rolls back, there's no event.

#Properties and consequences

• At least once: a relay that crashes after publishing but before marking rows will publish them again, so consumers must be idempotent (idempotent consumers).
• Ordering: publish in outbox ID order per key to keep per-entity order.
• Housekeeping: delete or archive published rows regularly.

#Change data capture instead of polling

Rather than polling, a CDC tool such as Debezium can read the database's write-ahead log and publish outbox inserts (or table changes directly) to Kafka with low latency and no polling load.

The outbox is how derived stores — search indexes, caches, analytics — stay in step with the system of record, and how sagas pass reliable messages between services (sagas).