Transactional outbox: commit business state and event intent together

Last updated: 5 Oct 20266 min read

tutorial

A service that writes a database row and publishes a message as separate actions can fail between them. The database may commit while no event is sent, or an event may be sent before the database write is durable. An outbox row inserted in the same database transaction as the business change closes that local gap. A dispatcher still may publish the event twice if it crashes after sending but before recording progress, so consumers must guard their effects with the event ID.

Operational decision

A payout API changes a payout from pending to settled. In one PostgreSQL transaction, update the payout only if it is pending and insert an outbox event only for the row that actually changed. The SQL fragment assumes the tables, event ID uniqueness, and status constraint already exist; execute it against a disposable database first. A CDC connector or polling dispatcher reads committed outbox rows, publishes the event, and checkpoints its source position. Crash the dispatcher after publish but before checkpoint: the same event may be published again, and the consumer must record its event ID before applying an irreversible downstream effect. Then crash the API before transaction commit: neither payout state nor outbox intent should appear. Monitor oldest unpublished event age and compare business transitions with emitted event IDs. Deleting outbox rows requires a retention rule tied to confirmed downstream processing and replay needs, not merely the passage of a few hours.

BEGIN;
WITH settled AS (
  UPDATE payouts
  SET status = 'settled'
  WHERE payout_id = 47 AND status = 'pending'
  RETURNING payout_id
)
INSERT INTO payout_outbox (event_id, payout_id, event_type)
SELECT 'payout-47-settled', payout_id, 'PayoutSettled' FROM settled
ON CONFLICT (event_id) DO NOTHING;
COMMIT;

Cost and verification

The outbox adds writes, storage, a dispatcher, and retention work. In return it makes event intent durable with the business transition. A fast publisher can still outpace a slow consumer; size broker retention and replay capacity accordingly. The unique event ID protects only the outbox row unless every effectful consumer enforces its own deduplication. Measure oldest event age and downstream completion, not only publish success.