Skip to content

Outbox table schema

The outbox table is application-owned: you create and migrate it. This is the schema the relay claims from and the columns it reads. The wiring that stages and relays events is in the Transactional outbox recipe.

Postgres

For Postgres (PostgresOutboxConfig(relation=("app", "outbox"))):

CREATE TABLE app.outbox (
    id UUID PRIMARY KEY,
    outbox_route TEXT NOT NULL,
    event_id UUID NOT NULL,
    event_type TEXT NOT NULL,
    tenant_id UUID,
    execution_id UUID,
    correlation_id UUID,
    causation_id UUID,
    occurred_at TIMESTAMPTZ NOT NULL,
    payload JSONB NOT NULL,
    status TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    published_at TIMESTAMPTZ,
    processing_at TIMESTAMPTZ,
    last_error TEXT,
    attempts INT NOT NULL DEFAULT 0,
    available_at TIMESTAMPTZ,
    ordering_key TEXT,
    UNIQUE (outbox_route, event_id)
);

-- covers the claim predicate (route + pending + ripe, oldest first)
CREATE INDEX outbox_claim_idx
    ON app.outbox (outbox_route, status, available_at, created_at);

attempts is the durable retry counter; available_at schedules the next retry (NULL = claimable now); ordering_key is the optional delivery partition key (NULL = no partitioning, key falls back to the event id). Existing tables migrate with:

ALTER TABLE app.outbox
    ADD COLUMN attempts INT NOT NULL DEFAULT 0,
    ADD COLUMN available_at TIMESTAMPTZ,
    ADD COLUMN ordering_key TEXT;

Trace propagation (optional column)

PostgresOutboxConfig(propagate_trace=True) persists each event's W3C traceparent so the relay forwards it as the traceparent header and the consume side links its span to the publish span. Opt in after adding the nullable column (legacy / NULL rows simply carry no parent); independent of hlc_ordering:

ALTER TABLE app.outbox ADD COLUMN traceparent TEXT;

Causal ordering (Hybrid Logical Clock)

By default claims are ordered by created_at — assigned per flush batch, so rows staged together share a timestamp and tie arbitrarily, and clocks on different replicas can disagree. Set PostgresOutboxConfig(hlc_ordering=True) to claim in causal order instead: every event carries a Hybrid Logical Clock stamp that stays close to wall time yet always exceeds any timestamp the process has observed (including one merged from a consumed event's forze_hlc header), so a reaction sorts after its cause across replicas, and the time-ordered id breaks any remaining tie. Add the column first, then opt in (legacy NULL-hlc rows fall back to created_at):

ALTER TABLE app.outbox ADD COLUMN hlc BIGINT;

-- claim order becomes (hlc NULLS LAST, created_at, id); index to match
CREATE INDEX outbox_claim_hlc_idx
    ON app.outbox (outbox_route, status, available_at, hlc, created_at, id);

Restart monotonicity (clock high-water mark)

The hlc column keeps causal order within a run, but a runtime's clock lives in memory: a restart resets it to (0, 0), after which it can re-issue a stamp at or below one it already relayed whenever wall time had regressed or a peer merge had carried the clock ahead. Persist the clock's high-water mark so a restart resumes above its prior emissions. The table is optional and node-global — one clock per runtime, spanning every tenant, so it is not tenant-partitioned:

CREATE TABLE app.hlc_checkpoint (
    node_key TEXT   NOT NULL,
    hlc      BIGINT NOT NULL,   -- packed HlcTimestamp: physical_ms << 16 | logical
    PRIMARY KEY (node_key)
);

Wire it with PostgresDepsModule(hlc_checkpoint=PostgresHlcCheckpointConfig(relation=("app", "hlc_checkpoint"))) and add hlc_checkpoint_recovery_lifecycle_step() to the runtime's lifecycle. For an outbox route that flushes inside a transaction (OutboxSpec(require_transaction=True)), the flush then advances the mark (the max HLC among the rows it stamps) inside that transaction — so a committed stamp is never durable without a mark covering it, and a rolled-back flush never advances it — while the lifecycle step loads the mark at startup and resumes the clock. A route that flushes standalone keeps resuming from (0, 0): its mark could not advance atomically with its rows, so no checkpoint is wired for it. Unwired entirely, every route resumes from (0, 0) as before. A single shared node_key (the default) records one deployment-wide mark; distinct per-replica keys avoid write contention on one row, and recovery reads the max across all keys either way.

Mongo

For Mongo (MongoOutboxConfig), documents mirror these fields; recommended indexes:

db.outbox.createIndex({ outbox_route: 1, event_id: 1 }, { unique: true })
db.outbox.createIndex({ outbox_route: 1, status: 1, available_at: 1, created_at: 1 })
db.outbox.createIndex({ outbox_route: 1, status: 1, processing_at: 1 })
// the relay reads each claimed batch back by its claim token
db.outbox.createIndex({ claim_token: 1 }, { sparse: true })

hlc_ordering=True works the same on Mongo: the packed HLC is stored on each document and the claim sorts [(hlc, 1), (created_at, 1), (id, 1)]. No schema migration is needed (documents are schemaless), but add an index to match, and note that Mongo sorts missing-hlc rows first — so during migration legacy rows drain oldest-first, the inverse of Postgres NULLS LAST (both best-effort):

db.outbox.createIndex({ outbox_route: 1, status: 1, available_at: 1, hlc: 1, created_at: 1, id: 1 })

Restart monotonicity on Mongo

The high-water mark works the same way and needs no index at all: the write is keyed on the document _id, and recovery reads one document per node. Wire it with MongoDepsModule(hlc_checkpoint=MongoHlcCheckpointConfig(collection=("app", "hlc_checkpoint"))) and the same hlc_checkpoint_recovery_lifecycle_step():

// { _id: <node_key>, hlc: <packed HlcTimestamp> } — created on first advance.

The advance is one $max upsert, which is what GREATEST does in the Postgres statement: the server keeps the larger value, so concurrent writers never lower the mark. It rides the ambient session, so inside a transaction it commits — or rolls back — with the rows it stamps. Mongo transactions need a replica set, so on a standalone server an outbox route with require_transaction=True cannot run at all, and with it the checkpoint has nothing to ride; the mark is only atomic where the flush itself is.

Two operational notes that differ from Postgres:

  • Give each replica its own node_key when more than one flushes concurrently. Two transactions advancing one document contend, and Mongo aborts one with a write conflict the flush must retry; Postgres serialises the same contention on a row lock.
  • Keep the keys stable and few — one per replica, not one per boot. Recovery reads every key's document, so per-process keys turn a fixed handful into an unbounded scan.

The mark is written with one atomic compare-and-set on both backends, so two replicas advancing it concurrently keep the higher value rather than one clobbering the other. That is asserted rather than assumed: the shared battery forces a second writer into the middle of a held write, which a read-modify-write implementation cannot survive.

The checkpoint also cannot be wired on a routed (per-tenant) client, on either backend: the mark is node-global while a routed client picks its backend from the bound tenant, which startup recovery does not have. Both modules refuse that combination at wiring rather than at the first read.