Module projection 

Store, don’t compute: the data paradigm and the queue that carries it.

Every analytics-shaped read in the system follows one rule. The answer a page, report or policy needs was known once — when the row it summarises was written — and is stored then, so no reader ever re-derives it from the raw history. The cost of a read is then the size of the answer, never the size of the ledger behind it.

The vocabulary, from raw to read:

1. Ledger — the append-only raw tables the gateway, the hook plane, the tool plane and the governance spine write (ai_requests, ai_request_payloads, ai_request_messages, mcp_tool_executions, plugin_usage_events, governance_decisions, logs, …). Read by exactly two things: projectors, and single-entity detail queries (WHERE pk = $1, no aggregate).
2. Stamped fact — a pure function of the row and what is in hand when it is written, stored as a column in the same transaction by the table’s owner (has_offered_tools, is_builtin, skill_ref).
3. Counter — an additive aggregate the writer keeps current through a FOR EACH STATEMENT … REFERENCING NEW TABLE trigger (never a row trigger: a row trigger’s cost is the fan-out that turned a 3,644-row update into 27 minutes).
4. Projection — one denormalised row per entity (<entity>_facts), maintained by refresh_<projection>(keys TEXT[]) and drained from the generic queue in this module.
5. Rollup — <subject>_rollup_hourly(bucket, dims…) of additive measures, upserted at write time. Hourly, because analytics windows are arbitrary timestamps; percentiles, distinct counts and top-N substrings are not additive and keep reading raw rows for their window.

Every fact has four parts, all in the base schema: the structure, the projector or stamp, the statement trigger (stamp directly when the transition table alone determines the value, else queue the keys) and the drainer job. An upgrade migration is the same DDL text plus one INSERT INTO projection_backlog SELECT '<projection>', <key> FROM …. Nothing else: a fresh install stamps migrations without running them, so anything a migration computes exists only on upgraded databases.

NULL means pending. A reader never falls back to the ledger expression (COALESCE(fact, <ledger expr>)); it shows the fact as pending and depth says how many are. Two static gates keep this true: the ledger-reads gate over repository SQL, and no_backfill_after_floor over migrations, built on crate::backfill_statements.

The queue

projection_backlog(projection, key, queued_at, attempts) is one table for every projection. Writers queue keys (queue or the SQL queue_projection_backlog); a drainer job calls drain with the projection’s refresh, which takes a batch with FOR UPDATE SKIP LOCKED, refreshes it and deletes the rows in one transaction. A failed batch is rolled back and re-queued one attempt dearer; a key past its retry budget stays queued for an operator and never blocks the rest. A refresh that can tell which key of its batch is the bad one may park that key on its own transaction instead of failing the batch: the key goes straight back to the queue past the budget, counted as pending, and the rest of the batch commits. A statement that keeps a refresh_*(keys TEXT[]) SQL function can use drain_with_sql and name it.

Copyright (c) systemprompt.io — Business Source License 1.1. See https://systemprompt.io for licensing details.

Structs

DrainReport

How a drainer tick went. remaining is the queue depth when it stopped, so a job can report “partly drained” without another query.

Constants

PARKED_ATTEMPTS

Functions

depth

depths

drain

drain_with_sql

park

queue

Type Aliases

RefreshFuture

A projection’s refresh: given a connection inside the drain transaction and the keys taken, rebuild them and return how many rows were written.