Skip to content
Spectral Codex
GitHub
Decisions

ADR-044: Event substrate — Postgres LISTEN/NOTIFY + transactional outbox; single `core.outbox`; deployment-generation routing

Context

This ADR defines the event substrate: Postgres LISTEN/NOTIFY + a transactional outbox, with a single core.outbox table and deployment-generation routing. ADR-109 carries the deployment topology this substrate runs in, and ADR-063 carries the inter-context access pattern the substrate is one half of.

The substrate covers two flows:

  • Signal path between contexts — at-least-once + idempotent + producer-owned DLQ + best-effort ordering (D10, ADR-054)
  • Within-context worker dispatch (API → worker; the AgentTask surface, D12)

Research convergence: landscape survey (six substrates — LISTEN/NOTIFY, polling-only outbox, Supabase Realtime, Redis Streams, NATS JetStream, Kafka/Redpanda) plus an adversarial steelman that argued for NATS JetStream from day 1. Both confirmed LISTEN/NOTIFY + transactional outbox as the right alpha pick. Supabase Realtime was disqualified because its delivery guarantee is not published in primary sources. ADR-041 D9 pre-committed one dedicated direct-to-Postgres connection per listener process, bypassing Supavisor transaction mode (LISTEN is incompatible with transaction-mode pooling).

Decision

D1 — Primary substrate: Postgres LISTEN/NOTIFY + transactional outbox

Producer writes the outbox row inside the business transaction; an AFTER INSERT trigger fires NOTIFY <channel>, '<row_id>' at commit; a dedicated listener pulls via SELECT ... FOR UPDATE SKIP LOCKED. Substrate piggybacks on Supabase Postgres; zero new infrastructure at alpha.

D2 — Automatic polling fallback

If the dedicated LISTEN connection cannot establish or drops for longer than the reconnect-backoff cap (30s), the consumer falls back to polling at 5s cadence. Polling is not a second substrate; it is a degradation mode of D1 that keeps correctness under listener failure. NOTIFY is a latency optimization, not a correctness primitive.

D3 — Single core.outbox table

One table in core with the row schema described in D4; producers tag their rows with the source column. Schema-layer DRY plus envelope-contract consistency plus simpler permission model outweigh the per-context aesthetic argument at alpha scale.

D4 — Outbox row schema

Columns:

  • id uuid pk
  • event_type text
  • event_version int
  • occurred_at timestamptz
  • source text
  • target text null (null = broadcast)
  • content_class text (future-proofs per-class retention divergence; today uniform)
  • channel text default 'outbox_default'
  • generation bigint (stamped by publisher at write time)
  • domain_id uuid null
  • payload jsonb
  • idempotency_key text
  • trace_context text null
  • status text check in ('pending','in_flight','delivered','failed')
  • attempts int default 0
  • last_error text null
  • delivered_at timestamptz null
  • deleted_at timestamptz null (per ADR-042 D3)

Plus the failure-history columns (see ADR-054 D5): failure_history jsonb default '[]', first_failed_at timestamptz null. DLQ = status='failed' subset (producer-owned recovery per D10 / ADR-054; no separate table).

D5 — Inter-context access

Inter-context access discipline lives in ADR-063: no SQL grants between contexts ship at any layer, and call flow uses callee-owned OHS Protocols + DI through the framework-layer composition seam (per ADR-065 D3 + D5). The notification-flow portion — events flowing through this substrate — is what this ADR provides. The SECURITY DEFINER status-transition function for outbox row updates is preserved (substrate detail, not an inter-context application read).

D6 — EventEnvelope lives in spectral.core.events

Frozen pydantic model. Fields: event_id: UUID, event_type: str, event_version: int, occurred_at: datetime, source: str, target: str | None, domain_id: UUID | None, payload: dict[str, Any], idempotency_key: str, trace_context: str | None. The payload field is dict[str, Any] at the substrate layer; producer-owned typed payload models live in <producer>.contracts.events.* per ADR-065 D2. The generation value lives on the outbox row only — the envelope stays substrate-agnostic.

D7 — Publisher / Listener protocols in spectral.core.events.protocols

EventPublisher (async def publish(envelope) -> None), EventListener (async def listen(*, channel, handler_name, handler) -> None), EventHandler = Callable[[EventEnvelope], Awaitable[None]].

The context-agnostic EventListener concrete implementation — the listener that drains core.outbox via SELECT ... FOR UPDATE SKIP LOCKED (D1, D9) and dispatches to a registered handler — is the shared event substrate, not any one context’s machinery, so it lives at core.events.infrastructure per ADR-099 (it passes the ADR-099 killer test: a context-agnostic concretion of a core contract used by every listening context). The context-specific projection EventHandler implementations (e.g. the platform System Card projection that consumes worlds.world_model_card.published) encode one context’s domain logic and stay in spectral.<context>.infrastructure.events; they are wired to the shared outbox listener via DI at the framework-layer composition seam.

D8 — NOTIFY channel taxonomy

Column-value-driven routing: outbox_default is the baseline channel, plus per-generation channels outbox_gen_<N>. The core.outbox_notify() trigger fires pg_notify(NEW.channel, NEW.id::text) on INSERT; the trigger function stays frozen. Workers LISTEN only on their own generation’s channel — V2 worker is structurally incapable of receiving V1 NOTIFY. The split axis is processing-characteristic (hot/cold, latency class) plus deployment-generation, not context.

NOTIFY payload is the outbox row ID only (UUID string, ~36 bytes — trivially within the 8 KB Postgres cap). Full payload lives in the outbox row, pulled by the handler.

D9 — Listener lifecycle

Dedicated direct-to-Postgres connection (port 5432, autocommit mode) per listening context process (per ADR-041 D9). On startup: drain existing PENDING rows once via SKIP LOCKED, then LISTEN <channel>. On each NOTIFY: drain batch N=10 via SKIP LOCKED. Reconnect with exponential backoff (1s → 30s cap); polling fallback (D2) activates when backoff exceeds the cap. Listener health is a monitored metric.

D10 — At-least-once + idempotent semantics

Idempotency key = event_id by default. Consumers persist handled event IDs in a single core.event_handled table with UNIQUE(handler_name, idempotency_key). handler_name must be scope-qualified (e.g., platform.decision_recorded_aggregator) to guarantee global uniqueness; protocol docstring enforces. Duplicate redelivery is caught by the ON CONFLICT path. The event_handled retention policy (D13) must strictly exceed the outbox active+grace floor to guarantee dedup correctness across the full outbox window.

D11 — Schema versioning

event_version integer on envelope; bumped when payload shape changes. Consumers ignore unknown fields (forward-compatible). Producers never remove fields within a major version (additive-only). Breaking payload change = new event_type with suffix (e.g., decision.recorded.v2) plus deprecation deadline on the old type. Contract test pins the envelope shape.

D12 — AgentTask dispatch (closes ADR-043 / TA-14 D11)

platform.agent_tasks remains a separate domain-scoped table carrying business state (status, HITL approval linkage, conversation_id, result back-reference, retention). Dispatch flows through core.outbox with event_type='agent.task.dispatched' and payload={agent_task_id}. The worker listens on the appropriate channel, reads the payload, pulls the agent_tasks row, executes. State transitions on agent_tasks that require worker action emit their own outbox rows. Separates transactional business state from transport. Trigger event ID for proactive conversations (conversations.trigger_event_id) = outbox row ID when applicable.

D13 — Retention integration

Two POLICY_REGISTRY entries (per ADR-042 D4):

  • (outbox_row, *) → RetentionPolicy(active_ttl_days=45, tombstoned_grace_days=7, disposal=HARD_DELETE) — 45-day window gives real forensic runway without a separate audit archive. Content-class-agnostic.
  • (event_handled, *) → RetentionPolicy(active_ttl_days=60, tombstoned_grace_days=7, disposal=HARD_DELETE) — must strictly exceed the outbox active+grace floor (52 days) to guarantee dedup correctness. 8-day buffer above floor; reducible only with a matching outbox reduction.

A contract test pins the inequality: event_handled.active_ttl > outbox.active_ttl + outbox.grace.

D14 — Observability

Per-publish structlog entry with the seven ADR-036 D5 canonical fields plus {event_id, event_type, source, target, domain_id}. Per-handle structlog entry adds {attempts, duration_ms, status_result, handler_name}. OTel span linkage: producer emits a span; envelope carries W3C traceparent in trace_context; consumer continues the span. pg_notification_queue_usage() is exported as a gauge via the OTel Collector; alert at 25% fill.

D15 — Forward triggers (two-step migration ladder)

  • Step 1 (in-Postgres, cheap). Outbox > 5M rows steady-state → partition by time-range on occurred_at (native Postgres range partitioning, monthly). Operationally routine; no event contract change.
  • Step 2 (substrate swap, substantive). Any of: outbox > 20M rows sustained after partitioning + retention sweep; consumer ack latency p99 > 500 ms sustained over a 1-week window; first production incident attributable to LISTEN/NOTIFY queue-fill, lock contention, or outbox bloat → swap relay to NATS JetStream or Redis Streams. Event schemas (owned by spectral.core.events) unchanged; consumer API swaps from LISTEN to broker subscription. ~2–3 engineer-weeks when triggered.

D16 — Queue-depth observability

pg_notification_queue_usage() polled on 30s cadence by a dedicated metrics emitter, emitted as an OTel gauge. Alert at 25% fill. Addresses the Recall.ai-style failure mode (queue fills under sustained load or stuck listener) before user-visible impact. PG 15+ already fixes the pre-PG13 AccessExclusiveLock serialization that caused the Recall.ai postmortem; the queue-fill failure mode persists and monitoring catches it.

Alternatives considered

NATS JetStream from day 1. Rejected. Adversarial’s own threshold-for-change-of-mind is met by D15 + D13 + the queued runbook. Adopting NATS adds a second stateful component to ops for alpha volumes that do not warrant it.

Redis Streams. Rejected for similar reasons, plus no native Kafka-mirror path.

Supabase Realtime on the outbox. Disqualified — delivery guarantee not published in any primary Supabase source.

Polling-only outbox (no LISTEN/NOTIFY). Rejected as primary; accepted as the D2 fallback.

CDC via Debezium + logical replication slot. Rejected. Logical slots are shared cluster resources; a stuck consumer can block WAL cleanup cluster-wide.

Separate DLQ table per producer context. Rejected. DLQ = outbox rows with status='failed'; producer observes the failed subset directly via query.

Per-producer-context outbox tables. Rejected. The single core.outbox table wins on schema-layer DRY, envelope-contract consistency, and a simpler permission model at alpha scale.

Column-level UPDATE grants for inter-context writes. Rejected (D5 uses SECURITY DEFINER for outbox status transitions); the inter-context default is no SQL grants per ADR-063.

Channel-per-context NOTIFY. Rejected in favor of outbox_default plus per-generation channels (D8) — the split axis is processing-characteristic and deployment-generation, not context.

AgentTask via Supabase Realtime channel. Rejected (D12).

Typed generic payload (EventEnvelope[TPayload]). Rejected. Typed payload models land additively in <producer>.contracts.events.* per ADR-065 D2.

Consequences

  • Zero new infrastructure at alpha. Substrate is Supabase Postgres.
  • Each consumer context process consumes one direct-to-Postgres connection slot. At alpha (3 contexts × ~2 processes each = ~6 slots out of Supabase Pro’s 60-slot direct pool), well-margined.
  • The substrate is a single core.outbox and a single core.event_handled. Migrations: 20260422170200_core_outbox.sql, 20260422170300_core_event_handled.sql, plus 20260425014302_core_outbox_failure_history.sql.
  • spectral.core.events is the substrate transport surface for events between contexts per ADR-065 admission discipline (D1, core/events/ functional area), with contract tests pinning the envelope shape and the retention inequality.
  • POLICY_REGISTRY carries the outbox and event_handled entries defined in D13.
  • conversations.trigger_event_id = outbox row ID; AgentTask persists as a separate table with dispatch via outbox (closes ADR-043 D11).
  • Listener process is a new daemon per listening context. Lifespan-scoped; joins FastAPI/worker startup.
  • The 8 KB NOTIFY cap is structurally unreachable — only UUIDs ship.
  • Recall.ai-class incident mitigated by PG 15+ + D16 queue monitoring + D2 polling fallback.
  • The substrate is extended by ADR-054 (failure-history + retry/replay), ADR-055 (typed payloads + a consumer outcome write), and producer-owned typed payloads per ADR-065 D2.

References

  • ADR-065 — spectral.core admission discipline
  • ADR-099 — core infrastructure zone; D7’s EventListener concretion lives at core.events.infrastructure, projection handlers at <context>.infrastructure.events
  • ADR-031 — single-library structure
  • ADR-032 — core schema
  • ADR-036 — canonical fields; OTel substrate
  • ADR-041 — D9 dedicated listener connection
  • ADR-042 — POLICY_REGISTRY shape
  • ADR-043 — D11 closure (AgentTask via outbox)
  • ADR-109 — single-outbox topology + generation routing
  • ADR-054 — failure-history + DLQ/retry semantics
  • ADR-055 — curation→worlds typed payloads
  • ADR-063 — inter-context SQL grants don’t ship between contexts
  • TA-5 disposition — SPEC-308 comment 0da0b862
  • TA-5 verification — SPEC-308 comment c8ed3fec
  • src/spectral/core/events/ — substrate contract surface
  • Codex system-design/foundations/contract-surfaces/event-substrate.mdx — close-pass new page
  • docs/runbooks/event-substrate.md — operational runbook