0009 · Transactional outbox for all derived state

Status: Accepted · 2026-09-22

Context

A document write must update Postgres, the Typesense index, the link graph, any matching automations, and possibly several notifications. Those cannot share a transaction, and the search index in particular must never disagree with the ACL — a revoked grant that remains searchable is a disclosure, not a glitch.

Decision

Every mutation writes its change and a row in events in one transaction. A worker drains events with FOR UPDATE SKIP LOCKED, woken by LISTEN/NOTIFY with a periodic poll as a floor, and dispatches to consumers: indexer, link resolver, automation runner, notifier.

Consumers must be idempotent; delivery is at-least-once.

Consequences

Postgres and Typesense cannot silently diverge. A crash leaves an unprocessed event, which retries.
New consumers are additive. Server-side AI workers plug in as one more subscriber rather than a change to the write path — which is what makes the hybrid AI position in 0010 cheap to hold.
Automations and scheduled jobs consume the same stream, so a trigger family is a filter, not a subsystem.
Search is eventually consistent, typically within milliseconds.
events grows and needs a retention policy.
Debugging is one step removed: "why was this not indexed?" is answered in the worker, not the request.

Correction

2026-09-22 — This record originally specified FOR UPDATE SKIP LOCKED alone. That is not sufficient: the claiming statement commits immediately, and the row stays unprocessed for as long as the consumer takes, so every other worker re-claims it in that window. A test running six concurrent claimers found single events claimed five times over. The claimed_at lease above is the fix.

Amendment

2026-10-11 — Three gaps between this record and what the system did:

Retries had no wait. Three attempts were spent within seconds, so a Typesense restart dead-lettered every event in its window, revocations included, until someone retried each by hand. A failed event now waits before its next attempt (retry_at), doubling from thirty seconds to an hour, ten attempts in all.
A retry re-ran every consumer, and not all of them were idempotent: the notifier repeated its notifications and automations acted twice when only the indexer had failed. An event now records the consumers that succeeded (handled_by), and a retry runs only the rest.
"Cannot silently diverge" held for the event stream, not for search. Search answered from the index alone, so for as long as the index lagged -- a pending retry, a dead letter, a rebuild that missed a change made while it ran -- a revoked reader still got the title and a snippet. Every page of hits is now confirmed against Postgres before it is returned, and codna reindex catches up on whatever changed while it ran. The index is what ADR 0005 always said it was: a derived copy, never read as truth.

Alternatives rejected

Write to Typesense inline. Simpler and immediately consistent. Rejected because it half-succeeds: either the user's save fails because the index was briefly unavailable, or it succeeds and the document is invisible with nothing recording that it should not be.

A dedicated queue — Redis, NATS, Kafka. Better throughput and mature tooling. Rejected because it adds an operational dependency for a workload Postgres handles comfortably at knowledge-base scale, and because enqueueing then falls outside the transaction, reintroducing the exact failure mode the outbox removes.

Postgres triggers. Keeps the fan-out next to the data. Rejected because the rules are real logic — link state transitions, automation scope matching — and that logic belongs in Go where it can be tested.