Transactional Outbox and Inbox

Key features

• A practical outbox row typically needs: a unique event ID, an aggregate/entity type and ID (what business object this event is about), an event type (what kind of thing happened), a serialized payload, a created timestamp, and a published flag or timestamp
• The published flag (or a nullable published_at timestamp) is what the relay updates only after confirmed delivery — this is the same "mark on confirmed success, not optimistically" discipline the relay concept in this section already covers
• Unlike a general-purpose event log meant to be a durable, queryable historical record, an outbox table is a transient staging area — its rows exist only to bridge the moment between "committed locally" and "confirmed published," and have no ongoing purpose after that
• Because it is transient, an outbox table needs an explicit cleanup/retention policy (delete or archive rows past a certain age once published) — without one, a table meant to be small and fast to scan grows indefinitely and its own performance degrades over time
• If a durable historical record of every event actually is needed for other reasons (audit, replay, analytics), that is a genuinely different requirement best served by a separate event log or stream (e.g. writing the same events to Kafka after they are published, or reading the outbox's own already-published rows into a separate durable store) — not by keeping outbox rows around indefinitely for a purpose they were never designed for

Command

CREATE TABLE outbox (
  event_id      UUID PRIMARY KEY,
  aggregate_type TEXT NOT NULL,
  aggregate_id   TEXT NOT NULL,
  event_type     TEXT NOT NULL,
  payload        JSONB NOT NULL,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  published_at   TIMESTAMPTZ           -- NULL until relayed
);

Key features

• Polling publisher: a scheduled job periodically runs something like SELECT * FROM outbox_events WHERE published = false, publishes each row, then marks it published — simple to build, but adds latency up to the poll interval and repeated read load on the table
• CDC/log-tailing: a process (e.g. Debezium) reads the database's own write-ahead/binlog replication stream and reacts to outbox inserts as they commit — lower latency and lower query load, but adds an operational dependency on a log-tailing connector
• Both approaches must mark or otherwise track which rows were already published, because the relay can crash after publishing but before recording that fact — the pattern is inherently at-least-once, so the downstream consumer still needs its own idempotency (an inbox) regardless of which relay style is used
• Rows are typically either flagged published = true and later purged, or deleted outright once the broker acknowledges — an outbox table with an ever-growing set of published rows and no cleanup job will eventually degrade write performance on the table
• Ordering across rows for the same aggregate should be preserved by the relay (e.g. publish in insertion order per aggregate ID) — this matters for consumers that assume events for one entity arrive in the order they happened

Command

-- polling publisher's core query
SELECT id, event_type, payload
  FROM outbox_events
  WHERE published_at IS NULL
  ORDER BY created_at
  LIMIT 100;

-- after a successful publish to the broker:
UPDATE outbox_events
  SET published_at = now()
  WHERE id = $1;

Key features

• The inbox table stores processed event/message IDs, typically with a unique constraint on the ID column so a duplicate insert fails atomically rather than needing a separate read-then-check step
• The dedup-table insert and the actual business-data update happen in the SAME local transaction — this is what makes the check itself race-free under concurrent delivery of the same event
• A duplicate delivery is detected BEFORE the business logic runs (insert into the inbox table first; if it conflicts, skip processing and stop) — this is what makes reprocessing a no-op instead of a partial double-effect
• Inbox tables need their own cleanup — old rows accumulate forever unless pruned by age or by a retention window matching how long the upstream producer might realistically redeliver
• The inbox pattern assumes at-least-once delivery is normal and unavoidable — it does not try to prevent redelivery, only to make redelivery harmless

Command

-- inbox table: one row per processed event ID
CREATE TABLE inbox_processed_events (
  event_id UUID PRIMARY KEY,
  processed_at TIMESTAMPTZ DEFAULT now()
);

-- consumer, on receiving an event:
BEGIN;
  INSERT INTO inbox_processed_events (event_id)
    VALUES ($1);            -- fails if event_id seen before
  -- ... apply the event's actual effect here ...
COMMIT;