# 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](https://codna.me/docs/adr/0010-mcp-first-no-llm-inside.md) 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.