Transactional outbox

Ironflow uses the transactional outbox pattern to decouple accepting an event from announcing it on NATS. This document explains the architecture, the guarantees it provides, and the operational surface an operator needs to know about.

Introduced in #487 (supersedes #477).

Why an outbox

Every event in Ironflow lives in two places: the events table (source of truth, queryable, durable) and the NATS JetStream PUBSUB stream (notification channel consumed by projections and real-time subscriptions). An event is only useful once it exists in both — the DB row without the NATS message means projections never see it; the NATS message without the DB row means consumers act on state that the application can’t prove exists.

Before the outbox, the publish path wrote the DB row, then called NATS, then patched events.nats_seq with the assigned sequence. Four paths left events.nats_seq permanently NULL:

1. Legacy Emit() API — non-entity events never published to NATS.
2. publisher == nil (test mode, misconfigured deployments) — no publish call at all.
3. Publish failure — the write-back path was documented as non-fatal, so transient NATS outages left the row unpublished.
4. The t1→t3 window — between the DB insert and the seq write-back, readers could observe NULL. Process crashes in that window made the NULL permanent.

These gaps broke WaitForEvent(eventID), which has no way to resolve an event ID to a NATS sequence when the column is NULL.

The outbox pattern

AppendEvent / Emit / NATS ingest:
  ┌───────────────────────────────────────────────────────┐
  │  BEGIN txn                                            │
  │   INSERT INTO events (..., nats_seq=NULL)             │
  │   INSERT INTO outbox (event_id, topic, kind, ...)     │
  │   ← optimistic concurrency check happens here         │
  │  COMMIT                                               │
  └───────────────────────────────────────────────────────┘
         │
         ▼ return to caller (no NATS in the hot path)


outbox worker (one goroutine per node):
  ┌───────────────────────────────────────────────────────┐
  │  SELECT ... FOR UPDATE SKIP LOCKED                    │
  │  pub.PublishWithSeqAndMsgID(topic, payload, entry.id) │
  │  UPDATE events SET nats_seq = ack.Sequence            │
  │  DELETE FROM outbox WHERE id = entry.id               │
  └───────────────────────────────────────────────────────┘

The event row and the outbox row ride the same transaction, so either both are written or neither is. Optimistic concurrency conflicts roll back before anything lands on NATS — no orphan messages. NATS outages no longer block event acceptance; the outbox absorbs the backlog and drains when NATS recovers.

Events published straight to NATS on events.ingest.> take the same path. The EventRouter writes the event row and its events:{name} outbox row in one transaction, the row Emit writes. So an ingested event gets events.nats_seq and WaitForEvent resolves it. A publish that PUBSUB cannot take stays in the outbox like an outage: the row keeps its attempts and publishes when PUBSUB can take it again (see Recovery). Examples are a stream with no leader (a non-leader answers nothing, so the publish times out or gets no responders) and an account over its JetStream limits. A default PUBSUB stream discards its oldest messages at capacity, so it takes the publish and does not answer with an error there. A publish that PUBSUB rejects for the message itself is retried, then moves to outbox_dead_letter. A NATS redelivery of the same ingest writes no second row, because the duplicate event insert rolls the transaction back. Before #3626 this path published to PUBSUB directly, logged and dropped a rejected publish, and skipped the publish on redelivery, so the event never reached projections.

Application database transaction boundary

Ironflow atomically writes its event and publication-outbox rows. A separate application database mutation is outside that transaction. Idempotency keys make retries safer; they do not create cross-database atomicity. Sharing an SDK object cannot provide remote atomic rollback. Ironflow exposes no shared-transaction or two-phase commit API for an application database.

Choose an integration based on where authoritative state lives:

1. Use Ironflow entity streams as authoritative state and derive read models with projections, as in the CQRS walkthrough. The event append and Ironflow publication-outbox write share a transaction; projections update asynchronously.
2. Keep authoritative state in your application database. Write the business mutation and an application-owned outbox row in the same local transaction. A relay sends committed rows to Ironflow using a stable idempotency key per event. Acknowledge or delete a row only after confirmed acceptance by Ironflow. If the request times out or the relay crashes before acknowledgment, retry with the same key. Acceptance confirms the Ironflow commit, not completion of NATS publication or projections.
3. Use idempotent steps with compensation or eventual repair when independent systems must change. Plan for partial success and retries. Compensation is a new business action that can itself fail; it is not ACID rollback.

The relay crosses the network between two independent commits. An Ironflow failure cannot undo transaction A; the application outbox preserves the work for retry.

Guarantees

• Atomicity. Accepting an event and scheduling its publish are one database transaction, on every path that writes an event row: AppendEvent, Emit and NATS ingest. Concurrency conflicts leave no trace on NATS.
• Eventual consistency. events.nats_seq is NULL transiently — typically 10-50ms under healthy conditions, bounded by outbox worker lag. It stops being NULL once the worker publishes successfully. Readers that need read-your-writes call WaitForEvent(eventID) to block until the row drains.
• No duplicate NATS messages on retry, within 2 minutes of a lost ack. The worker passes the event ID as the NATS msg-id for events rows (the outbox entry ID for other kinds); a worker that publishes successfully then crashes before marking the row published will re-publish with the same msg-id, and JetStream’s dedup window returns the original sequence. The window is 2 minutes (DuplicateWindow) and runs from the publish that lost its ack. A duplicate ack does not renew it. A row’s own failures retry at about 0, 1, 3, 7, 15, 31, 63, 123, 183 and 243 seconds (the delay caps at 60s and each one is jittered by ±10%), so the protection holds for the retries up to about 63 seconds and not for the last three. A row held through an outage retries at the same cap with no attempt limit. A retry more than 2 minutes after the publish that lost its ack, whether the row’s own or held, is assigned a second sequence if that publish reached the server. The two constants are asserted against each other by TestBackoffStaysInsideDedupWindow (issue #1970), which keeps one delay below the window. Both a SQL projection and a managed reducer can then apply the event twice. A SQL projection skips by stream sequence, and a managed reducer does not dedupe by event ID on its own: Delivery.Admit checks sequence numbers only (see projections). A reducer that must not apply an event twice has to be idempotent by event ID.
• Per-entity FIFO, within one node, while no row fails. Within a node, each batch is bucketed by entity_id and each bucket runs on a single goroutine that iterates serially, in the order the batch was returned. Order does not hold across retries. A row that fails goes back to the outbox with a later retry time, and a newer row of the same entity publishes in the meantime, in the same batch or a later one. So a row waiting on a retry can be overtaken, and an outage makes that window as long as the outage. Non-entity rows (plain Emit calls) dispatch in parallel with no ordering constraint. SELECT FOR UPDATE SKIP LOCKED and the 30s claim lease stop two nodes from claiming the same row; they do not order two different rows across nodes, so node A can be publishing v1 while node B publishes v2. That gap is stated as out of scope below, and this bullet used to contradict it.
• Poison-row handling. A row whose own publish fails 10 times (default) moves to outbox_dead_letter. A failure that says NATS cannot take publishes does not count: it holds the row (see Recovery), so an outage does not normally dead-letter it. Recovery lists the two exceptions. The main outbox stays small and hot; dead-letter rows stay for manual inspection until explicitly requeued or discarded. A projection rebuild waits while an events dead letter whose event has no nats_seq exists in its environment, because that event may already be in NATS with every publish ack lost. Discarding that dead letter fails each such rebuild in flight, and every later rebuild runs without the event, so requeue it instead. See projection rebuilds.

Redaction

RedactEvent rewrites the event’s rows in outbox and outbox_dead_letter in the transaction that replaces events.data. An events row takes the placeholder as its payload. An entity row keeps its envelope, with the placeholder in data. The row stays, so the event still publishes, still gets its nats_seq and keeps its place in a rebuild.

Redaction does not reach what is already published:

• A published message stays in its stream until the retention window expires, at most 7 days, and a subscriber that received it keeps it.
• The worker reads each row again just before it publishes. A publish past that read when the redaction commits counts as published, so it can still send the original. A claimed row that has not reached that read publishes the placeholder. So does a retry. An external NATS connection keeps no reconnect buffer, so a publish made during an outage fails at once, the row is held, and that publish never reaches the stream.
• A publish that already left the client can still put the original in the stream. This happens when the publish reached the server and lost its ack, or timed out on a connection that stalled without closing and was delivered when the connection recovered. RedactEvent does not change the stream, so a second call does not remove the message. What follows depends on when the retry lands:
  • Inside the 2-minute dedup window, the placeholder returns as a duplicate at that sequence, and events.nats_seq points at the original.
  • Past the window, as after a longer outage, the stream takes the placeholder as a new message. events.nats_seq then points at the placeholder, so the row looks clean while the original stays in the stream at an earlier sequence.

On PostgreSQL, a dead-letter move and a requeue lock the event row before they read a payload, so neither can write the original back after a redaction.

An event redacted on a release before this behavior keeps its original payload in any dead letter. Call RedactEvent again before you requeue it. A second call keeps the first sha256 and scrubs the dead letter.

Non-guarantees

• Global FIFO across entities. Not provided. Events from different entities, and plain Emit events, can reach the stream in a different order than they were written. A projection must not depend on that order. SQL projections apply events in stream order and skip by stream sequence, so their handler SQL must give the same result in either order. Let each event upsert only its own columns. An upsert that sets a shared column, such as a status, from the event still depends on arrival order.
• Zero duplicate deliveries at the consumer. NATS is at-least-once; consumers still need idempotence by event ID. This includes the second stream copy that a retry more than 2 minutes after a lost ack can produce. A managed reducer applies that copy unless it is idempotent by event ID.
• AppendEventResponse.Sequence in the same round trip. Under the outbox pattern the sequence is not known at AppendEvent time. The field is always 0 on the response — callers that need the sequence call WaitForEvent(eventID) (now blocking) or WaitProjectionCatchup (streaming).

Operational surface

Schema

Two tables, introduced in migration 011:

• outbox — the live queue. Columns: id, event_id, entity_id, topic, kind, payload, metadata, environment_id, created_at, next_retry_at, attempts, last_error, seq. seq is an insert-order identity column (migration 108) that a projection rebuild uses to gate on the outbox. Migration 035 later added aggregate_type and aggregate_id; no code writes them yet.
• outbox_dead_letter — rows whose own publish failures used up the retry budget. Same shape minus next_retry_at, plus dead_at. Its seq is nullable (migration 110); a requeue copies it back into outbox.

kind is events (consumed by projections, triggers UPDATE events.nats_seq on publish), entity (consumed by entity stream subscriptions only) or steps.dispatch (crash-safe pull-mode job dispatch).

Tuning

The server runs the worker with fixed defaults. No flag, YAML field or environment variable changes them:

• Pool size 8 — concurrent publish goroutines per batch.
• Batch size 32 — rows claimed per poll.
• Poll interval 50 ms — idle poll interval.
• Max attempts 10 — move to dead-letter after this many failures of the row’s own. A row held by an outage keeps retrying and spends none.

Inspecting the outbox

For ad-hoc inspection beyond the CLI and HTTP API (covered below), use direct SQL:

-- How many rows pending?
SELECT COUNT(*) FROM outbox;

-- Oldest pending
SELECT event_id, topic, attempts, last_error, created_at
FROM outbox
ORDER BY created_at ASC
LIMIT 20;

-- Dead-letter summary
SELECT event_id, topic, attempts, last_error, dead_at
FROM outbox_dead_letter
ORDER BY dead_at DESC
LIMIT 20;

Recovery

If the outbox backlogs (NATS down, sustained publish failures), the worker keeps retrying with exponential backoff (1s → 60s cap, each delay jittered by ±10% and never above the cap) until NATS recovers, however long that takes. An outage does not normally move a row to outbox_dead_letter: the dead-letter count normally stays 0 through it. Two exceptions, a stream leader that lost its peers and a storage failure on a non-clustered NATS, are the last two costs below. A held row keeps its attempts and retries on a delay that grows with its age, capped at 60s. During an outage, watch ironflow_outbox_pending_count and ironflow_outbox_oldest_pending_age_seconds. No operator action is normally needed.

The worker also runs behind a circuit breaker. After 5 consecutive outage failures it stops polling for 60s, then lets one batch through as a probe; a successful publish closes the breaker. Only failures that say NATS cannot take publishes count. A row that fails for its own reasons (a payload over the server’s limit, an invalid subject, a subject the credentials deny) does not, so a poison row never opens the breaker or pauses the worker. It can still delay healthy rows. The worker waits for every row of a batch, and a row that times out holds its batch for the client’s 5 second default on each attempt, so the healthy rows queued in that batch wait that long every time. A row that fails fast does not. The publish breaker’s own refusals do not count either. The breaker only pauses the worker, so no row is lost or dead-lettered by it. The shared nats-publish breaker that wraps each publish follows the same rule, so a bad row cannot open it for the healthy ones.

During an outage of an external connection a publish fails in about 700 ms, so a burst opens both breakers in under a second. When the connection returns, both close at once and do not wait out the 60s. A publish that started before then does not count when it fails, so it cannot reopen them. A NATS that stays connected and keeps failing does not reconnect, so its breakers stay open for the 60s. If JetStream is not ready when the connection returns, its failures count again and can reopen the breakers.

The worker sorts a failed publish into three classes. This is internal/nats (ClassifyPublishFailure), and the publish wrapper and the worker both use it.

• An outage, always. No connection, JetStream not enabled, an API answer of 503 or 429, an account over its JetStream limits, a msg-id the stream leader still holds in flight (API error 10158, “duplicate message id is in process”), or a refusal by the publish breaker. While an external connection is down, the error reads nats: outbound buffer limit exceeded. The buffer it names is the client’s reconnect buffer, which is off on purpose, so there is no limit to raise. The worker holds the row. The row keeps its attempts and retries on a delay that grows with its age, capped at 60s, until NATS is back.
• Ambiguous. A timeout, no responders, no stream response, or a broken socket or EOF. A subject the credentials deny, and a subject no stream binds, fail this way against a healthy server. The worker asks a shared health check: a stream-info call on PUBSUB with a 1 second limit. The answer is cached for 2 seconds, and callers that find no fresh answer share one call. The check before a dead-letter skips the cache and does not share. The target is up when the call answers and the stream has a leader or is not clustered. An error, a timeout and a stream with no leader all read as down. While the target is up, the failure is the row’s own. While it is down, the failure is an outage.
• The row’s own. Everything else: a payload over the limit, an invalid subject, an API 4xx, and any error nobody classified. The row spends an attempt, and at the limit it moves to outbox_dead_letter.

Three costs follow.

• A row that fails inside the 2 second answer cache of a real outage, while the health check still reads up, spends one attempt. The last attempt is the exception: a row about to move to outbox_dead_letter on an ambiguous failure asks the target again, with no cache, and is held if the target is not up. If the target is up, the worker publishes the row once more under the same msg-id first, in case the connection came back after the publish died. It moves to outbox_dead_letter only if that publish fails as the row’s own.
• A stream leader that lost both of its peers keeps answering stream info as the leader for 10 to 20 seconds, although its publishes cannot commit (the leader counts a peer as lost 10 seconds after its last reply, lostQuorumIntervalDefault, and checks every 10 seconds, lostQuorumCheckIntervalDefault, both in the NATS server’s raft.go; we measured 17.1 and 20.1 seconds). The health check reads up for that window. The first publish of a msg-id times out, which is ambiguous, so it costs an attempt. Each retry of that msg-id then gets API error 10158 and holds the row. A fresh row can spend up to 3 attempts in the window, because its retries come close together. Later attempts wait up to 60 seconds, so a row near its limit spends fewer, but one can still move to outbox_dead_letter in it. The fresh check before a dead-letter does not see through this.
• The guarantee does not cover a storage failure on a non-clustered NATS (a full, read-only or failing disk). A stream-info read cannot see that, so those rows dead-letter at their limit. That is tracked separately.

The worker has a health check in the server because the PUBSUB bridge builds one. With none (only a fake publisher in a test has none), the worker reads every ambiguous failure as the row’s own, so no row is held for good by a check that does not exist.

If rows land in outbox_dead_letter, use the CLI:

# Inspect what's in the DLQ
ironflow outbox dlq list --env env_default_prod

# Push every DLQ row of this event back to the outbox for another publish attempt
ironflow outbox dlq requeue <event-id> --env env_default_prod

# Permanently delete every DLQ row of this event (the underlying event row is kept)
ironflow outbox dlq discard <event-id> --env env_default_prod --yes

The CLI is a thin wrapper over three HTTP routes (handler in internal/server/outbox_handler.go):

Method	Path	Action
GET	/api/v1/outbox/dead-letter	List DLQ rows (env-scoped via auth context).
POST	/api/v1/outbox/dead-letter/{eventID}/requeue	Move every DLQ row of this event ID back to outbox for retry.
DELETE	/api/v1/outbox/dead-letter/{eventID}	Permanently delete every DLQ row of this event ID (event row kept).

One entity-stream append can leave two DLQ rows (entity and events namespaces) with the same event ID. Requeue and discard act on all of them. Use these routes directly when integrating with dashboards or external tooling.

AI agents reach the same queue through the MCP tools ironflow_outbox_dlq_list (always on) and ironflow_outbox_dlq_requeue and ironflow_outbox_dlq_discard (both need --allow-writes).

For raw-SQL access when the CLI is unavailable, the outbox_dead_letter table follows the schema above and accepts the usual requeue pattern (INSERT ... SELECT ... into outbox, then DELETE from outbox_dead_letter). Copy the seq column too: a requeued row with a new seq falls outside the outbox gate of a rebuild already running, and that rebuild can skip its event.

What the SDK change means for callers

The breaking change: AppendEventResponse.Sequence and EntityEmitResult.Sequence are always 0. Callers that previously used them for read-your-writes patterns should switch to WaitForEvent(eventID), which blocks until the outbox worker drains the row (or returns a terminal error if the row is dead-lettered):

// before
resp, _ := client.AppendEvent(ctx, req)
_, _ = client.WaitProjectionCatchup(ctx, &WaitReq{MinSeq: resp.Msg.Sequence})

// after
resp, _ := client.AppendEvent(ctx, req)
wait, _ := client.WaitForEvent(ctx, &WaitForEventReq{
    EventId: resp.Msg.EventId,
    Timeout: durationpb.New(5 * time.Second),
})
// wait.Msg.TargetSeq is the resolved NATS seq.

Observability

• ironflow_outbox_dead_letter_count{environment_id} — gauge, current DLQ depth per environment, computed at scrape time.
• ironflow_outbox_pending_count and ironflow_outbox_oldest_pending_age_seconds — gauges, rows waiting in the outbox and the age of the oldest. Both grow during a NATS outage; use max() across nodes.
• ironflow_outbox_dlq_collector_errors_total — counter, cumulative scrape-time failures of the DLQ count query. An increase records a failed query, which omits the gauge from that scrape. The counter stays non-zero after recovery.
• ironflow_outbox_lag_collector_errors_total — counter, failed samples of the two lag gauges.
• ironflow_dlq_writes_total{source} — counter, incremented every time the worker moves an outbox row to the DLQ (source="outbox").
• system.outbox.{event_id}.{dead_lettered|requeued|discarded} — pub/sub frames for each DLQ transition (#1729), so a subscriber can render the queue live instead of polling. Subscribe to system.outbox.>. Shape and fields: REST API reference.

An outage does not normally move rows to the DLQ, so the dead-letter count normally stays 0 through one (see Recovery for the exceptions). Alert on an outage with ironflow_outbox_pending_count and ironflow_outbox_oldest_pending_age_seconds, which grow while rows wait. A dead letter means a row’s own publish failed up to its limit.

The gauge and the frames answer different questions. The gauge is recomputed from the table at every scrape, so it is correct even after a NATS failure. The frames ride NATS itself, so a subject that the credentials deny drops them too. Build alerting on the gauge; use the frames for the live view, and reconcile against the table.

Out of scope for the initial release

• Prometheus publish latency histogram. The backlog is already exported as ironflow_outbox_pending_count and ironflow_outbox_oldest_pending_age_seconds.
• Cross-node per-entity advisory locking — current implementation serializes per-entity only within one node. Multiple nodes can publish concurrently for the same entity; acceptable until cluster scale warrants pg_try_advisory_xact_lock.

These are tracked as follow-ups on #487.