Storage layout: which store holds each table, and why

Each Loom table belongs in Postgres or ClickHouse by how it is read and written; this page places every table, with the wave that moves it. Postgres holds state read by key, changed in place or locked, and ClickHouse what is appended and scanned (storage authority). The page sets out:

• the rule that places a table by its access pattern, and the schema rules of each store;
• the shared Postgres structures that replace queues, claims and leases built on ClickHouse, and how statements that cross the two stores are rewritten;
• a row for every ClickHouse and Postgres table, with its store, key, kind and erasure coverage, and for a ClickHouse table up to two modules whose statement, table-name literal or table constant names it;
• the waves, how a table moves, and what a restore keeps.

A table's Today column is where it is now, until its wave ships. A table added later follows the same rule, and the change that adds it adds its row here.

The placement rule

A table goes to the store whose guarantees its readers depend on.

A table goes to Postgres when it is	A table goes to ClickHouse when it is
read by key, and a reader needs the value as of the last commit (a revocation, a grant, a policy, a lock, a head)	appended and scanned: vectors, terms, chunks, statistics, the graph projections, the code index
changed in place: a status, a lease, a cursor, a counter two replicas must agree on exactly	history: version rows, append-only and changed only by erasure and, for memory_versions, redaction
claimed or locked: a queue, an outbox, a lease, a claim, a compare-and-set	a log or a rollup: events, traces, audit, usage, cost, exposures, SLIs
small per tenant: registrations, configuration, user-written state outside the search corpus	a cache with a TTL: provider answers, search memos

State that search joins with the memories inside ClickHouse (operator-authored categories and the memory_categories links, server/memory/domain/search_sql.py) stays in ClickHouse even when it is small and per tenant. Its by-key reads need no Postgres commit: the import's insert-if-absent read of the links (link_states in server/memory/store/categories.py) runs with sequential consistency, and a duplicate link collapses on the ReplacingMergeTree key; the registry read of memory.assign_category and memory.import (registered_ids) runs without it today, so on a lagging replica a category registered a moment before answers CATEGORY_NOT_FOUND or skips the import's link, and wave 3 reads it with sequential consistency too.

Each test comes from a cost ClickHouse pays today for state it does not fit:

• FINAL on point reads. A ReplacingMergeTree read by key under FINAL merges the key's parts at read time, so its latency depends on how far merges are behind.
• Merge-dependent correctness. Without FINAL, a read sees every version until the merge; with a cache in front, the cache must fail closed when the read fails (revoked_tokens, namespace_grants).
• No row locks. A queue on ReplacingMergeTree claims an item by inserting a version and reading back who won (memory_extraction_queue), or records an item's state by a mutation (memory_projection_failures), and keys of the KeeperMap table loom._migration_lock stand in for compare-and-set and for leases (server/core/leases.py). A KeeperMap key is not in a transaction with the rows it guards, and it gives its holder no fencing token.
• No transactions. A change that must touch two tables together (a task and its lease, a job and its cancel) is written as two inserts with reconciliation code between them.

A table that needs both a current value and a scan splits, as memory does: the head in Postgres, and in ClickHouse a projection of it, published from the commit, that the scans, joins and history views read.

Each fact has one owner

Every table is one of five kinds. Each store is backed up whole (a ClickHouse backup of the loom and loom_telemetry databases, a Postgres backup of the cluster), so every table is in its store's backup. A ClickHouse backup copies each table at a slightly different moment, so a ledger that says how far a table is built may come back ahead of or behind it. The ledger rule, which every other section points to, sets each after a ClickHouse restore:

• A ledger whose output is idempotent to re-run is reset to no later than its output (a ledger keyed by a commit, code_index_state, forces a full re-diff).
• A ledger that keeps output from being written twice (usage_acted_credits, whose output memory_usage_events does not collapse duplicates) is set no earlier than its output, by rebuilding it from that output, which wave 4 makes possible by putting the pair digest on each acted event.
• A Derived table fed by a view from its source (entity_degree, memory_usage_totals, the term and document-length folds) is reconciled or rebuilt from its source, except a table that is SoR past its source's retention (subject_last_seen, agent_cost_rollup_daily, loom_telemetry.sli_minute), which is only refolded over the source's retained window, never truncated or rebuilt whole. conversation_embeddings, written directly and SoR past its messages' retention, is re-embedded only for retained messages and never truncated.

The kind says what is lost, and what a rebuild needs, when a backup is not there:

Kind	Meaning	Without a backup
SoR	system of record: the only copy of the fact	lost
Log	an append-only system of record, often with a retention TTL (in the schema, or set by retention_reconcile): events, traces, audit, usage	history is lost, not correctness
Derived	rebuilt from a named source; the row says from what, and whether the rebuild calls a model	rebuilt from its source, at a model's cost where it calls one
Cache	expendable: provider answers, memos, published assets	nothing; it refills
Coord	coordination state: leases, claims, acknowledgements, change counters; meaningful only while processes run	nothing; a restore starts with none held

A row's kind may be qualified: "SoR (head)" is a Postgres head with a ClickHouse projection; "SoR, then Derived" is a table that becomes derivable once its wave ships; "Derived, SoR past its source's retention" and "Derived (SoR for …)" name the rows only that table holds; "SoR, Coord" and "Cache; SoR (head)" give the kinds of a row's tables or roles in order.

A Postgres row with a ClickHouse projection is owned by Postgres; the projection is Derived, rebuilt by republishing the heads (except datasets, documents and knowledge_ingestions, whose rows ClickHouse alone holds for some keys, so each row gives its rebuild and its system-of-record rows), and a split table's row gives the kind of the Postgres half. Postgres stores no vector, so a lost vector table is rebuilt only by re-embedding every row at provider cost. memory_versions is a system of record too: it holds history written before a deployment's journal existed, and it is the only copy of a superseded version whose journal payload was cleared (payload clearing). The revision metadata in memory_revisions (key, version, hash, status, seq) is kept until an erasure deletes it: a ClickHouse restore's reconcile anti-joins the restored history against it.

loom.memory keeps the current text of each key in ClickHouse. It is the source of memory_versions_mv, one of the tables insights.query grants, and read by statements that join it inside ClickHouse; the lexical leg reads memory_embeddings.content_excerpt (server/memory/app/retrieval/lexical.py). On a synthetic namespace of 250k keys, system.parts (bytes on disk over rows) measured about 91 B per row for memory (the harness writes one to four version rows per key) against about 12.9 KiB per row for memory_embeddings (one per key; the harness's key vector is a constant that compresses to almost nothing, so the real figure is larger): the cost lever is the vectors (vectors). To reproduce it, load, into a ClickHouse of your own, with PYTHONPATH=$PWD .venv/bin/python bench/scale_load.py load-ch --shape single --rows 250000 and run:

SELECT table, sum(bytes_on_disk) / sum(rows) FROM system.parts
WHERE active AND database = 'loom' AND table IN ('memory', 'memory_embeddings')
GROUP BY table

Cells

A cell is one whole Loom deployment (cells): its replicas and workers, one Postgres unit, one ClickHouse service and one document store (a bucket prefix, or document_blobs in its ClickHouse where no bucket is configured). Every table on this page lives in a cell, and a cell shares no store, key, lease or worker with another. Its signing keys, revoked tokens, service accounts, machine identities, SSO, SCIM and OAuth state, grants, namespace registry, collections, blacklist (the global org = '*' patterns included), process registry, configuration and secrets are its own, and their waves move them to its own Postgres beside its memory (each table's Target column). Only the router's directory is shared, in the router's own Postgres; cells places it, with each cell's copy of its own row (loom.cell_self). An org lives in one cell and never leaves it, so this page has no org move, and no fact is split between two Postgres databases: a check over Postgres tables reads one database, and a grant or blacklist change commits in the database whose memory commits it fences. (The memory, knowledge and code planes of the rest of the docs are surfaces of every cell.)

Once they move, security reads (tokens, grants, the blacklist, the registry) go through caches invalidated by cache generations that fail closed. Sign-in, sign-up, OIDC-start and workload-token attempt counts (auth_attempts) become a bucketed counter in Postgres, one row per kind, hashed key and window, so admitting a sign-in reads no ClickHouse. Deployment-wide workers (configuration, the provider rebuild) run under the cell's own leases. Every org-keyed Postgres table a wave adds carries the tenancy trigger of cells in the migration that creates it (exempting '', '-' and '*'), and enters the schema-generated org list that attach and loom doctor read.

Statements that cross the stores

A table that leaves ClickHouse breaks every ClickHouse statement that names it. Each wave's pull request finds them all and rewrites each in one of four ways. It finds:

• every statement by grep -rnw '<table>' over the whole repository but the generated schemas/schema.sql, by the bare name, since some statements name tables without loom.;
• the materialized views in schemas/schema.sql whose source or target is the table;
• code that finds tables at run time through system.tables or system.columns, such as _STORED_SQL in server/core/rmt_carry.py.

The rewrites:

Rewrite	When	Example
Bind a parameter	the moved table narrows a ClickHouse read to a small set of keys	switched-off orgs, open runs, candidate ids
Keep a projection or mirror	the moved table is joined with large ClickHouse tables or feeds a view	memory_records with memory_record_fields (the record side leg), agent_runs in the mentor's scans, namespaces in worker scans
Split per store	one check reads a table in each store while its tables move in different waves	the sign-up check that refuses a name an existing principal holds (_refuse_empowered in server/auth/self_signup.py): each part reads its store, under the claim that guards it today, until every table it reads is in Postgres
Move together	two tables are always read together	session_briefs with its revisions

The nested claims that guard grant, group, collection and repository changes today (grant_change, admins_change and the other claims in server/auth/grant_claims.py) become row locks, taken in today's order, once the rows they guard are in Postgres; a claim that guards a set (the admins of a namespace, the shape of a collection tree) locks a named parent row (the namespace's registry row, the org's collection root), so two changes to the set cannot each see the other's row unchanged. Namespace auto-registration (ensure_registered in server/identity/app/namespace_autoregister.py) decides the owner of the first write into an unregistered namespace from the namespace census (a ClickHouse read, outside the transaction) and inserts its namespaces row with ON CONFLICT. An owned row wins over an ownerless one and never over an explicit registration, as ReplacingMergeTree ordering decides today. It stays best effort and never fails the write it rides on. A split check keeps its semantics: the sign-up check still refuses every holder _refuse_empowered checks today (server/auth/self_signup.py), a grant, a namespace, a token, an inbox, a poster rule, a task, and the names a setting grants a role. tests/unit/test_package_contracts.py keeps the store modules separate; each rewrite lives in the store module of the table that moved.

Shared Postgres structures

These structures hold the queues, claims, leases, cursors, change counters and the per-feature state that moves with them; each new one is a table with one library every caller uses, and the deliveries keep their two existing tables and loops. This page fixes what each is for, its key, and the requirements its design must meet; the wave that builds it designs it against them and is reviewed on them.

Cache generations

scope_generations (org, scope, generation) is unchanged. A namespace's row allocates its revision seq under its row lock; that generation bounds the published watermark that pg_memory.capture computes from memory_deliveries. * is the org's policy scope, and ('', '*') the global one.

The change counters that process caches poll get their own table, cache_generations (org, kind, generation). Its kinds:

• grants, groups and collections, which today's acl_versions counts apart (server/core/grant_cache.py);
• blacklist, revocation and config;
• namespaces, the registry, whose narrowing changes answer only once their bump has committed, as a grant change does;
• the table_moves records (requirement 3's re-read).

Requirements:

• a change bumps its row in its own commit and sends NOTIFY loom_generation; a memory commit never notifies (a NOTIFY takes a database-wide lock at commit);
• a cache listens on a session connection to the primary that bypasses any transaction pooler, re-reads on a short TTL when the listener is down, and fails closed past it;
• a cache compares (restore generation, generation), so a counter that climbs back to a cached value after a restore still reads as a change;
• the restore generation is a column of the singleton authority row, kept apart from the paired authority epoch that boot checks (server/core/pg.py) and that a restore must not change;
• every restore of the cell's Postgres raises it above every value an earlier restore set, taken from outside the restored database (the restore's clock in microseconds, never the restored value plus one);
• every reader on this page that orders by a restore generation reads this one.

Unauthenticated writes to Postgres are bounded per window, as the KeeperMap key limit bounds sign-in state today:

• attempt counts in auth_attempts for new keys fold into a shared overflow bucket with a strict limit, so reaching the bound never refuses a key that already holds a row;
• sign-in state in oauth_signin_state (sign-in and device starts), a value read back per key, refuses a new key with 429 once its bound is reached, existing keys unaffected;
• a client registration in oauth_state is admitted per address and refused with 429 past its bound;
• /v1/health reports each bound reached.

The policy fence. A memory commit holds its policy scopes shared and starts its verdict over when one moved (server/memory/store/pg_memory.py). Every change that bumps the policy scope today keeps bumping it (the org's * row, ('', '*') for a global pattern) in its own commit, in the same database, found by grep for pg.bump_policy, grant_cache.changed and grant_cache.announced (a grant, group or collection change through server/core/grant_cache.py, a namespace registered, deactivated, restricted or opted out, an SSO link, a sign-up, a claim release, a blacklist change), so a verdict judged under the old policy starts over as it does today. Authorization and blacklist checks read caches, so a request a replica checked before its cache heard of a change can still commit within the cache's invalidation bound, today's bound with a poll replaced by a notification.

Jobs

jobs (org, id) in the cell's Postgres replaces the queues built on ClickHouse (memory_extraction_queue, memory_projection_failures, memory_webhook_outbox, code_scip_batches, delete_version_checks), one row per job with a kind, a typed state machine (ready, running, done, failed, cancelled), a lease and a fencing token, and the kind's arguments in an opaque jsonb payload. Knowledge ingestion is not a jobs kind: it keeps its own knowledge_jobs, a named exception: it already holds its jobs in Postgres with a fencing token and a cancel column, knowledge_ingestions is its ClickHouse projection for reports, and folding it into jobs would be a Postgres-to-Postgres move this page does not need. Requirements:

• Claim. FOR UPDATE SKIP LOCKED on due rows of a kind, one org per statement, orgs taken in turn so one backlog does not starve the rest, and inside an org the kind's fairness key in turn (the webhook for webhook jobs, as delivery round-robins webhooks today, due_events in server/memory/store/webhooks.py), a webhook's events still going out in order, its id their serial key; a claim raises the token and sets the lease from the database clock; an expired running row is reclaimed in place.
• Serialization. A kind that runs one at a time per key (extraction per namespace, user and enqueuer while a request is held, as claim_batch in server/memory/app/extraction_queue.py rules today; knowledge_jobs keeps its one-at-a-time per source) has that key and its held state as typed columns, enforced by a partial unique index, with an advisory-lock pre-filter so claims do not wait on each other.
• Dedup. One ready job per (org, kind, dedup_key); work enqueued while a job runs is a ready job of its own, never folded into a run that already read its inputs.
• Fencing. Every save and the completion name the token; a job whose effect is a Postgres commit locks its row with its token in that commit; effects outside Postgres are bounded by the lease, as publications are (server/core/pg_lease.py).
• Transactional outbox. A job a commit causes (a webhook event, a delete's follow-up) is inserted in that commit.
• Erasure and redaction. job_users (org, user_id, job_id) and job_memories (org, namespace, memory_id, job_id), each ON DELETE CASCADE, index the users and memories a job carries. A user's erasure deletes a job that names only that user and, from one that names several, removes the user and scrubs the user's content (an extraction job's messages) from its payload; a redaction scrubs the redacted text from the jobs that name the memory, matched by namespace and id, since a memory id is not unique within an org. A scrub raises the token, and a delete or scrub returns the jobs' lease_until for the saga's ClickHouse steps to wait out. Jobs a memory commit causes are deleted in the memory step's own transaction under the scope's lock; the jobs step keeps today's exclusion of the saga's own memory.erased events (server/erasure/store/census.py).
• Retention. A kind keeps what its table keeps today: a finished job keeps its row for its kind's status op, then the sweep for its kind deletes it on its table's TTL today (an extraction job 30 days after enqueue, a projection_repair job 30 days after it is resolved, one that failed kept until resolved); each attempt is also an events row.

memory_code_link_queue is not merged: the journal already orders every write, so the code-link worker reads memory_revisions from its cursor.

Deliveries

What a memory or knowledge commit owes ClickHouse stays in memory_deliveries and knowledge_deliveries. Those rows are the one publication path: the committing writer publishes its own delivery under the commit's token (an admission publishes a namespace's backlog), and the cell's pg_delivery and knowledge_pg_delivery loops publish what is left, all under the delivery fencing. Nothing else writes the rows a commit owes ClickHouse but the datasets exception below; rewrites of derived rows in those tables (a pending-embed row's re-embed, the conflict resolver's version rows, the provider rebuild's table renamed in whole with writers drained) publish no commit. No staging table or queue is in ClickHouse. Work a revision owes ClickHouse is a target bit on its delivery row.

Journaled heads. The shared and records heads join the memory journal: their revisions are memory_revisions rows with a typed head column (memory, shared, record, with typed schema_name and record_id for a record) that every journal reader matches on, so record history (memory_record_versions_mv) is fed once per revision and a ClickHouse restore reconciles both like memory; their erasure keeps today's rules: shared is matched by the users it was promoted from, so a shared revision's sources are a typed index, revision_sources (org, user_id, namespace, seq), through which a pg: census row and the provenance step reach shared_current and its revisions.

Redaction. Every table that holds a copy of a version's text is scrubbed by redaction or its row names why not. In ClickHouse, the rows a model wrote from a redacted version are matched and retired: the entity_vectors rows of the redacted key for the entities the version's entity_edge_rows and entity_triple_rows name, matched by (entity_text, memory_key) and rebuilt by the entity projection from the redacted memory; the entity_summaries of those entities, retired and not rebuilt, since no module writes that table today; the session_summaries whose derived_from names the key (any version of it, since the column holds keys), retired and not rebuilt, since a digest is written from the session's turns; and the derived_facts of the version, matched by the memory version that wave 3 adds to them (a fact written before the column, with no version, is retired with any redaction of its key), retired and not rebuilt (no column tells a stated operand from an extracted one, and extraction reads the messages, not the version). These rules are requirements of wave 3, the next wave that touches redaction. A redaction scrubs a memory version's text wherever a copy of it lives in Postgres: the version's journal row, memory_current (the key's own head when it holds the text, and the profile heads folded from the memory, as pg_memory.redact does today), shared_current when a promotion copied the text, and job payloads (above); a redaction that reaches shared_current also scrubs or republishes the ClickHouse shared rows and the shared-scope memory_embeddings rows of that version. A replay answer in idempotency that quotes the text is deleted. Redaction finds it through idempotency_revisions (org, key_hash, namespace, seq), a typed index of the revisions each op committed; today they are the revisions jsonb (server/memory/store/pg_memory.py). A trigger on INSERT OR UPDATE OF revisions fills the index whatever the build, and the backfill step fills it for existing rows. Until that backfill is recorded, redaction also matches the revisions jsonb.

Other heads. The other heads with a projection (agent_runs, operator_edges) are republished by a publish job their commit inserts, and a task transition owes its task_events row the same way. agent_runs' projection keeps today's nanosecond version, strictly increasing per head, so rows written before the flip never outrank a later commit and the readers that compare version with the clock (idle_session_runs in server/tasks/store/runs.py) keep working.

pg_delivery itself claims publish jobs under the delivery fencing (server/core/pg_lease.py), so each cell has one publication path per journal. pg_delivery publishes the memory journal and every head outside the knowledge plane that is not journaled. knowledge_pg_delivery publishes the knowledge commits and the knowledge_ingestions projection of knowledge_jobs; wave 2 moves that projection's inline insert there.

The datasets exception. The datasets projection of knowledge_datasets is a named exception: its writer inserts it inline after the catalog commit (insert_catalog and project in server/knowledge/store/datasets.py), as today. The reasons:

• The projection exists for a build that predates the table and a rollback to one (storage authority).
• A catalog write (a publish, an approval, a carry) commits no document version a delivery row could carry.
• A projection that lands after a delete of its source is harmless. A dataset is served only while the source's live document row has its version, and the next delete of the source removes it (_delete in server/knowledge/app/writes.py). One that lands after an erasure's documents.tombstone is found by the residue census, which runs the saga again.
• An approval deletes the snapshot it replaced only once the projection has landed (_switch, server/knowledge/app/datasets.py), since until then loom.datasets still names it for N-1 and a rollback; through a delivery that delete would wait on the pass.
• N needs no projection row of its own: a read takes Postgres's rows and only the newer rows ClickHouse alone holds (datasets_repo.at_version), while N's reports and knowledge.inspect read knowledge_ingestions, which therefore moves to a delivery.

The inline insert of rows Postgres holds retires by rolling-upgrades rule 8 once no build that predates knowledge_datasets can serve; loom.datasets then holds only the rows Postgres cannot store, and the approval's wait on the projection goes with it. The projection is never rebuilt whole from Postgres, since a row only ClickHouse holds is its system of record. After a Postgres restore a newer projection row is kept, served and reported, never deleted. After a ClickHouse restore loom reconcile names each source to ingest again (backup and recovery).

Task events. For task_events the tasks wave meets these requirements: a task's events land in the order its transitions committed, each lands once (by an event_id column), it is visible before a watcher's cursor can pass it, and seq stays a microsecond stamp (rolling-upgrades rule 4).

Mirrors. The namespaces mirror, a code_repos mirror without credentials and the sql_access mirror, in the cell's ClickHouse, serve the ClickHouse statements that join them (worker scans, and the security-definer SQL surface views, which leave out restricted namespaces by them). Each is published by pg_delivery as a publish job its head's commit inserts, and is ReplacingMergeTree(version) on the head's bigint commit version, ordered with the restore generation of the cell's Postgres, so the order rows arrive in does not matter; republishing from the heads rebuilds a mirror after a restore, and the wave that moves each head builds its mirror before any view reads it (wave 1 for namespaces and sql_access, wave 3 for code_repos). Two kinds of write answer only once their rows have landed, else 503 PUBLICATION_PENDING with committed: true, as a memory write does, and their replay answers the same until they land:

• a change that narrows what a scan or a view may take (a namespace deactivated, opted out or restricted, or registered restricted, a repository likewise, an SQL-access revoke), on its mirror rows, so no row of it is written before the views can leave it out;
• a write that is a synchronous mutation today, so that the next read sees it (a conversation thread delete, a compaction tombstone, a save and an unsave), on its projection rows.

A rebuild swaps in a complete table and never empties the live one. Every projection of a Postgres head that is not journaled (the mirrors, agent_runs, operator_edges, conversation_states, knowledge_ingestions) is likewise ReplacingMergeTree on the head's commit version; the journal's projections (memory, shared, memory_records) keep their key's own version and are reconciled after a restore by its anti-join against memory_revisions, and documents is reconciled from knowledge_heads, a document with no head kept as restored. sql_access grants, its wildcards (namespace = '*' for one org, org = '*' for every org) included, reach only the cell's ClickHouse, whose user a SQL user is.

An erasure saga's steps that change identity tables (grants.revoke, groups.remove_member, personal.retire, and collections.forget_owner) commit in the cell's Postgres, idempotently, and owe the same cache bumps and mirror rows the change would owe if an admin made it.

Conversation state

Messages are an append log read by scans, but today thread delete and compaction tombstone them, and a save marks them, by mutation (is_saved, saved_at, and the saver as user_id, server/knowledge/store/conversations.py). Wave 3 moves both, a message's tombstone and its saved state, to Postgres conversation_states (org, namespace, agent, thread_id, msg_id). Requirements:

• The saver. The state row's typed user_id is the saver, written only for a message whose row's user_id is '-', '' or the saver, by INSERT … ON CONFLICT DO NOTHING and a read-back, so the first saver of an unowned message takes it, as mark_message_saved claims today. A message's user is its row's user_id (the writer) or its state row's (the saver).
• Readers. Every statement that reads a moved column (tombstoned_at, is_saved, saved_at, or user_id as the message's user: a filter, a selected or grouping column as in sql_v1_conversations, or an export) on conversations is rewritten onto the state rows, found by grep, its user filters matching either user (among them search_substring, the erasure exports in server/erasure/store/exports.py, the thread check of server/memory/app/evidence_reads.py, the user-scope census predicate and the conversations.tombstone user step).
• The projection. A ClickHouse projection, ReplacingMergeTree(version) and published by pg_delivery, serves every live read, the saved-only scans and the census's live predicate. It has no TTL of its own: its rows leave when their state row is deleted and the delete is published.
• Retention. A state row is deleted only once no row it governs is left: a message's row once its message row is gone from ClickHouse (the sweep takes rows past the message's retention expression, ts with the global retention_conversations_days and the per-namespace retention_days that _per_namespace in server/storage/retention_overrides.py applies, and deletes only those whose message a ClickHouse read no longer finds). The removing conversation.unsave_message deletes its state row in the same change.
• Erasure. The split conversations.tombstone runs its ClickHouse step before its Postgres step. The Postgres step records the (org, namespace, agent, thread_id, msg_id) of every message it matched by the saver in Postgres erased_messages (ids only, swept with the erasure's erasure_ledger row), and the ClickHouse step's replay after a ClickHouse restore matches those ids as well as the row's own user_id, with a replay test of a saved '-' message in both restore directions.
• Embeddings. conversation_embeddings has no TTL and outlives its messages, so its saved state and saver stay on its own rows. Wave 3 replaces today's mutations (mark_message_saved, and unsave_message's flag clear and removing tombstone) by version-row inserts carrying is_saved, saved_at, tombstoned_at and the saver as user_id (ReplacingMergeTree(ts), the later insert winning a ts tie, with today's agent-less hand-off rule). The saver's erasure, the census and conversation_search keep matching the row's own user_id.
• Threads. conversation_threads does not move: its tombstones stay mutations, a thread delete's and an erasure's, which set tombstoned_at on every version of the row. A write that reads the row before a tombstone and inserts after it (conversation.update, the titler) undoes it; that race is today's code, and no wave changes it.
• No mutations. Wave 3 rewrites conversations.tombstone (server/erasure/store/steps_ledgers.py) so no statement on conversations or conversation_embeddings is an ALTER … UPDATE before the flip's revoke: it deletes the messages with their state rows, and tombstones conversation_embeddings by version-row inserts (ReplacingMergeTree). The conversations UPDATE entry of _EXPECTED_MUTATIONS (tests/unit/test_clickhouse_mutation_inventory.py; the message tombstones of tombstone_thread and tombstone_messages) retires, and its conversation_threads entry stays; tombstone_scoped (whose conversation_threads statement stays), mark_message_saved and unsave_message name their table through a variable the inventory does not match, so the wave finds them by grep.

Write marks

write_marks (org, namespace, surface) holds the last insert per namespace and surface (memory, chunk, evidence, record, summary). It serves the namespace sweep and read-your-writes alike.

• Every write, memory included, sets its surface's mark before it answers, by an autocommit GREATEST upsert skipped while the mark is within its granularity, so concurrent writes coalesce instead of queueing on the row.
• The memory surface's read-your-writes check alone compares the namespace's scope_generations generation with its published watermark.
• A restore leaves no mark, and a missing mark counts as a write at the restore time, so the sweep never purges on its absence.

Context offloads

context.offload stores the only copy of replaced context (handle_context_offload in server/memory/app/context_offload.py, up to context_offload_max_bytes).

• It becomes a Postgres head, context_offloads, of metadata and a pointer. Its bytes go to the cell's bucket or, where none is configured, to offload_bytes rows the head owns, deleted in the head's own transaction (document_blobs, shared by every document of the same bytes, is not).
• The head carries its writer's expiry (ttl_seconds today) as expires_at; a sweep deletes the head, its object and its rows.
• Its erasure step deletes the objects and rows its heads point to, and the census counts them.

Leases

leases (org, name) takes the lease keys of loom._migration_lock (every lease caller of server/core/leases.py, and its direct writers, server/insights/store/provider_lock.py and server/core/schema/migrate_lease.py); the table's claim keys follow the claims below, its probe and nonce keys stay (how a table moves), and the KeeperMap task_leases and agent_run_claims become columns and keys of the rows they guard (merges and deletions). Requirements:

• a take or renewal is one upsert on the database clock; a take draws its token from a sequence, so no holder ever reuses one; a renewal keeps it, and a renewal that returns another token is a lost lease, whose fresh row the holder releases at once;
• a release deletes only WHERE holder AND token; a sweep deletes rows a grace period past until;
• a lease bounds its holder by time, as today; work that must be fenced against a later holder runs as a job and names the job's token;
• a claim that guards one row's change becomes a row lock, a claim that guards a set (the admins of a namespace, a collection tree) locks a named parent row, and a claim over a key that may not exist yet (a create: group_change, service_account_change, bot_change, the sign-up name and email claims, personal-ns:) becomes the insert's primary-key conflict where its check reads only that table, and otherwise a pg_advisory_xact_lock on the claim key, taken in today's order inside the change's transaction (the sign-up check, trusts_change);
• a Postgres restore runs with every process stopped, starts with no lease held (it empties leases, as for every Coord table) and pairs every token with the database's restore generation, so no token issued before the restore, held or lost, compares equal to one issued after it.

Cursors

cursors (org, consumer, scope) holds where a consumer stands whose output is not in ClickHouse: the task webhook's delivery cursor per inbox, with its retry state as typed columns, and from wave 3 each knowledge source's sync state (its changed-after stamp and mined revisions, today knowledge_source:<name> rows of code_index_state), beside the knowledge_sources registry and the knowledge_heads it feeds, so a Postgres restore rewinds both together. A consumer writes its idempotent output, then advances its cursor. A cursor that records how far a ClickHouse table has been built stays in that ClickHouse beside the table, in the same backup, so a ClickHouse restore rewinds both, each ledger set by the ledger rule: the code index's build state (code_index_state), the code-link worker's cursor into memory_revisions (kept in code_index_state, one per (org, namespace) since seq is numbered per namespace, holding a seq and never a memory id), the worker resume cursors in worker_status, the tracker's rollup ledger (qa_rollup_days) and the usage fold's credits (usage_acted_credits). A cursor into the Postgres journal also records the Postgres restore generation it was advanced under, and resets (the worker relinks from the heads) when a Postgres restore has raised it, since a restored journal hands out its seq values again. The provider rebuild's ledger (its table index and cursor, today the progress column of provider_configuration) is a named exception: it is in its job's Postgres row, since its targets are scratch tables of the run, and a ClickHouse restore fails the job, so the rebuild starts again from its snapshot.

Schema rules

Postgres

• Typed columns for what is read. Every column a predicate, an index, a guard, a constraint or a join reads is a typed column. jsonb holds only an opaque, sparse remainder the database never filters on (a job's arguments). A jsonb field that a predicate starts reading moves to a column in that change. The exception is the full rows that heads, journals and deliveries keep so every row builder stays unchanged (storage authority): memory_current.row, memory_revisions.row and .audit, and the row of knowledge_heads, knowledge_deliveries, knowledge_jobs and knowledge_datasets. A field of them that a predicate reads (row->>'memory_type' in the profile index, the originals a knowledge delivery owes) becomes a typed column in the wave that next touches its table: wave 3, which adds memory_revisions.head, owns memory_current and memory_revisions and types the head fields today's request-path predicates read (tombstoned_at, redacted_at, sensitivity, expires_at, status, memory_type, and created_at, an ORDER BY key; server/memory/store/pg_memory.py). Each such column on an existing table:
  • is filled by a trigger from row on every write, whatever the build;
  • is read by N's predicates through coalesce(<col>, row->><field>) until a batched backfill has set it on every row;
  • on a table that grows with content, is indexed CONCURRENTLY by the backfill step after it has set every row, never by a boot migration file; the step drops and rebuilds an index pg_index.indisvalid shows invalid, and no N reader depends on the index until it is valid. A content-free or small table may take the boot path migrations names.
• redacted_ts. On the journal, redacted_at is text today, so it moves to a typed column over three builds (rolling-upgrades rules 8 and 1: a type change is not additive):
  • N adds redacted_ts timestamptz, filled by the revision-metadata trigger from coalesce(NEW.redacted_at, NEW.row->>'redacted_at') (the column a redaction sets when row is NULL) on INSERT OR UPDATE OF row, redacted_at, and reads it through coalesce(redacted_ts, <text>::timestamp AT TIME ZONE 'UTC'), the text being coalesce(redacted_at, row->>'redacted_at'), as pg_memory._meta falls back today, payload included, until the backfill has set it on every revision where that text is not NULL.
  • N+1, once the backfill is recorded complete, reads redacted_ts alone and neither writes nor reads redacted_at, except that its trigger still sets redacted_ts from NEW.redacted_at, so a redaction by N during the window is kept.
  • N+2 or later drops redacted_at and that clause together.
• The backfill step is a leased worker pass that records its completion per column, index and check in Postgres backfill_steps and never holds the boot's migration advisory lock (Postgres migrations); a later file's SET NOT NULL, a build that reads a typed column alone, and a drop each refuse to boot until that record exists, as requirement 9 of how a table moves does for flips. A boot migration that waits on a lock held by the backfill step's session cancels that session (pg_cancel_backend, found by its application_name), and the step records itself not done, drops any index indisvalid shows invalid, and retries after the boot; the step holds no transaction open between statements (each CREATE INDEX CONCURRENTLY, VALIDATE CONSTRAINT and batch is a statement of its own), so a cancel releases every lock it holds.

• Stated relations. Wave 3 keeps the relations a write states (memory.set's relations, a memory.bulk_set entry's, and the facet triples of files, concepts and topics) in a typed revision_relations (org, namespace, seq, ord) table:

  • one row per stated relation, its subject, relation and object as plain columns, both as written and as entity resolution resolved them (server/memory/app/entity_resolution.py), so a rebuild in revision order is deterministic, with a hash index where a lookup by triple needs one;
  • kept as long as the revision's metadata and cascading with it, with a census row (pg:loom.revision_relations, owned by pg_memory.delete), reached by erasure;
  • reached by redaction too, which also scrubs or retires the revision's triples in ClickHouse, found through entity_triple_rows rather than in the payload, which may be NULL;
  • backfilled from entity_triple_rows where a row maps to a revision (wave 4's row-link backfill backfills it too, for the values it links).

  The triple tables are then derived from it for every relation a revision maps to, rebuilt without a model call, and stay the system of record for relations no revision maps to.

• Keys lead with org in every tenant table. Coordination and job tables carry the identity they act on (org, namespace, a job's users in job_users, and in its payload the rest), not the full tuple. The exceptions are the maintenance indexes of the sweeps and claim scans and the cross-org principal index of namespace_grants ((lower(subject), org), read across orgs by SCIM deprovisioning; the sign-up check uses it within its org), which are local to one database, and the tables of the cell rather than a tenant, each keyed by what identifies its row there: signing_keys by kid, sso_links by (email, org, namespace), instances by process, revoked_tokens by (kind, key), oauth_state by (record_type, id), oauth_signin_state by its claim key, move_acks by (move, process), the singleton rows, auth_attempts by kind, key and window, scim_users by its id, machine_identities by name with an index on org for the per-org reads, carry_acks by process, table_moves by move, the configuration tables by their ClickHouse key (deployment_config by name, runtime_overrides by (kind, key), provider_configuration by config_id, embedder_calibration by model), analysis_secrets and assistant_service by id, backfill_steps by what it sets, pg_migrations by file, and cell_probe by name.
• Identifiers. uuid for ids made by writers (UUIDv7 where order helps an index), bigint for sequences and tokens. Text keys stay where they are user-chosen names (memory_key, namespace), with COLLATE "C" so ordering and equality match ClickHouse byte order. Postgres text cannot hold a NUL byte, so a ClickHouse key that uses one as a separator becomes typed columns.
• Checks in the schema. States, kinds, roles and non-negative counters are CHECK constraints where the check is cheap; a state column without one today (idempotency.state, knowledge_jobs.state, memory_revisions.status) gets it in the wave that next touches its table. A check on an existing table is added NOT VALID (new rows are checked at once) and validated with VALIDATE CONSTRAINT by the backfill step once it has set every row (never by a boot migration file on a table that grows with content), which takes only SHARE UPDATE EXCLUSIVE; a later file runs SET NOT NULL only once that check is validated. A NOT NULL on an existing table is added the same way as CHECK (col IS NOT NULL) NOT VALID; once that is validated, SET NOT NULL uses it instead of scanning the table, and the check is then dropped (NOT NULL … NOT VALID exists only from Postgres 18). So no migration scans a large table under an exclusive lock. A new value follows rolling-upgrades rule 3: N's migration widens the check, N's readers accept it, N+1 writes it.
• Hot tables. A table updated in place on the request path or by every pass of a worker (scope_generations, leases, jobs, auth_attempts, idempotency, write_marks, graph_inflight, instances, cursors, the deliveries, memory_current) sets, or gets in its wave, fillfactor 70 to 90 and keeps the columns it updates out of its indexes where it can, so updates stay heap-only; a claim index that reads lease_until (the deliveries', jobs') makes those updates not heap-only, which is the price of a partial claim index. A queue or counter sets autovacuum_vacuum_scale_factor = 0 with an absolute threshold, so vacuum runs by dead rows, not by table size; a table that grows with content (memory_current) gets a small scale factor instead, in wave 3. scope_generations and idempotency take a fillfactor in the first wave that touches them.
• TTLs become sweeps. A table that moves with a TTL keeps it as a sweep on the same expression, run under a lease, unless its row names another retention; a KeeperMap expiry (oauth_signin_state) becomes an expires_at column the same sweep reads.
• Partitioning only when measured. No Postgres table is partitioned until a measurement shows vacuum, a per-org dump or a per-org erasure growing badly; the candidates are memory_revisions and jobs, hash-partitioned by org, which keeps every org-led key and index; the cross-org maintenance indexes become per-partition.
• Foreign keys where one row's life ends another's (a delivery and its revision, a supporter and its candidate), with ON DELETE CASCADE, and an index on the referencing columns, org first, wherever the key does not lead with them (job_users, job_memories on (org, job_id); revision_sources on (org, namespace, seq), which is also the redaction lookup). idempotency_revisions cascades with its idempotency row, whose key its own leads with, and has an (org, namespace, seq) index for the redaction lookup.

ClickHouse

• The sort key leads with the identity tuple (org, namespace, …), and the narrowest prefix every hot read filters on, for every table added or rebuilt; a table that stays keeps its key and its engine (the rollups written directly, memory_served_daily, query_nodes, code_unit_signal, among them). The deliberate exceptions: tables of a cell rather than a tenant (assets by path, worker_status by worker), the provider caches (tenant rows keyed by model, which keep their key), audit_checkpoints by window, loom_telemetry.sli_minute by SLO class, and the sql_access mirror by user, which its readers filter by currentUser().
• Narrowest types. LowCardinality(String) for org, namespace, agent, kinds and models; Enum8 for statuses; DateTime unless milliseconds are read; CODEC(ZSTD(3)) on text over a few hundred bytes; Delta, ZSTD on monotone integers where a measurement shows it pays.
• Engines by kind. Append logs are MergeTree, with a TTL where retention applies; rollups are AggregatingMergeTree or SummingMergeTree fed by materialized views; projections of Postgres state and versioned derived rows are ReplacingMergeTree(version), with is_deleted only where tombstones are a material share of bytes and the erasure census still holds.
• Graph validity. The graph tables are also a named exception to the in-place rule today: a close or reopen of a triple's or an edge's validity is an ALTER TABLE … UPDATE (close_triples, reopen_triples and end_edges in server/memory/app/projections.py); wave 3 moves validity to versioned interval rows of their own (entity_validity, ReplacingMergeTree(version), new tables), while an edge's co-occurrence stays an additive log that entity_degree_mv sums once per write and interval rows never feed, so a rebuild from revision_relations and revision order reproduces validity; an offline open-facts parity check, which wave 3 builds (bench/graph_eval.py calls a model, so it is not one), proves results identical, and the change ships default-off where it does not.
• No queues, leases, claims or staging tables. Anything with a state machine or an owner is a Postgres row. A pending-embed row (a vector table's stub with embed_attempts and embed_failed_at, which a worker re-embeds) is not a queue: it has no claim or owner, and the re-embed is an idempotent overwrite of a derived row. The conflict resolver's mark (memory_embeddings.conflict_checked_at, server/memory/store/conflict_resolver.py) is not a queue either: it rides the row its write already emits, no row has a claim or owner, one resolver runs under a lease, and a judgement is a new version row of a derived table. erasure_requests is a named exception: it is ClickHouse's own erasure record, saga checkpoints as version rows that the recovery worker resumes without a claim, kept beside the ClickHouse tables it erases so that each store keeps its own record (erasure_ledger in Postgres) for loom reconcile (restores). A SCIP upload's parts, which its apply worker reads once a batch is complete, are bytes the batch's code_scip job owns. They go to the cell's bucket keyed by the job, or, where no bucket is configured (a single-node deployment), to Postgres job_parts (org, job_id, seq): bytea rows of at most code_scip_max_part_bytes that cascade with the job, the upload refusing a batch larger than a per-job byte cap there. A job's parts are deleted when its batch is applied, and otherwise by a sweep 7 days after created_at, today's TTL, apart from the job row's 30-day retention. The sweep finds a part's bucket object by its age under the jobs' prefix, so an object whose job is gone is still removed. The provider rebuild's snapshot, target, publish and backup tables (server/insights/store/provider_staging.py) are a named exception to "no staging tables in ClickHouse": each is built by one run under an exclusive lease with writers drained, no row waits on a per-row publisher, and the publish table is renamed into place whole (RENAME TABLE, server/insights/store/provider_publish.py). A rebuilt mirror swapped in whole is the same kind of table.
• Skip indexes only where a system.query_log measurement shows the reads they save.
• Partitioning on parts and erasure grounds. A partition key exists only where a TTL drops whole partitions or an erasure replaces them. Tables that stay keep their layout here; changing a sort or partition key is a rebuild (migrations), done in its own measured change.

Vectors are the cost lever

A memory carries two 3,072-dimension BFloat16 vectors in memory_embeddings (embedding and memory_key_embedding), 12 KiB of raw values. The codes-first codec (Quantized('rabitq', …)) speeds the scan; it does not shrink the table. Three levers, each measured with the offline ranking-neutral checks before it ships, and default-off when it changes ranking:

1. Whether memory_key_embedding earns its bytes. It serves the key axis of the fact leg (server/memory/app/retrieval/fact_sql.py) and the goal similarity of procedure.match (server/memory/app/procedure.py), and the offline checks cover both: bench/retrieval_determinism.py --save before and --against after covers the fact leg through memory.search, on a fixed namespace, identical keys and order (and scores within tolerance, which the first wave that uses it adds), and that wave adds the same before-and-after comparison for procedure.match (fixed namespace, identical procedure ids and order). Run it with the axis off; if the results are identical, a setting that defaults to writing the column can stop it (half the vector bytes). Turning it back on re-embeds the keys at provider cost, so the default stays on.
2. The dimension. An embedder whose vectors keep their order when truncated can store 1,536 dimensions. The width is in each vector column's codec (Quantized('rabitq', 3072, 0)), so it is a column and codec rebuild, from the stored values truncated and renormalised where they are the full-precision prefix (a re-embed only where they are not), gated by a setting that defaults to the full width, since it changes ranking.
3. The codec. Store the codes and a narrower rescoring copy; measure the codec's bytes per row and the rescore's recall on the fixtures.

Merges and deletions

Every ClickHouse table that merges into another or is deleted, and the KeeperMap tables that become shared Postgres structures. Each table's row in the ClickHouse tables is authoritative; a row whose Target is a merge or a deletion names its table here too (tests/unit/test_storage_layout_rows.py).

Today	Becomes	Condition
memory_term_freq, chunk_term_freq, code_term_freq	ClickHouse term_df (org, namespace, corpus, term), uniqExact over a UInt64 row hash, with the views that feed them (chunk_term_freq_mv, chunk_context_term_freq_mv, code_term_freq_mv, memory_term_freq_refresh) and code_term_df_refresh rewritten onto it	bench/retrieval_determinism.py --save/--against shows identical keys and order, and scores within the tolerance the first wave that uses it adds
memory_doc_stats, code_doc_stats	ClickHouse doc_lengths (org, namespace, corpus, …), with memory_doc_stats_refresh and code_doc_stats_mv	as above
entity_edge_memories, entity_triple_memories	entity_edge_rows, entity_triple_rows	a backfill links the values written before row links; an offline open-facts parity check, built with the merge, shows the same open facts; user erasure (delete_entity_graph_by_user, server/erasure/store/steps_graph.py) reads ownership from the row links in the same change, proved by the census
memory_extraction_queue, memory_webhook_outbox, memory_projection_failures, code_scip_batches, delete_version_checks	Postgres jobs	none
memory_webhook_attempts	Postgres jobs columns, and each attempt an events row	none
keyring_readers	Postgres instances.keyring_refresh_s	none
cell_heartbeats (KeeperMap)	Postgres instances columns cell, tenancy_mode, fingerprint	none
_provider_participants	Postgres instances	none
memory_schema_fields	Postgres record_schemas.fields	none
blob_uploads	Postgres document_uploaders	none
task_webhook_cursor	Postgres cursors	none
code_scip_parts	the code_scip job's bytes (bucket, or Postgres job_parts)	none
acl_versions, blacklist_version	Postgres cache_generations	none
namespace_write_keys (KeeperMap), namespace_write_marks	Postgres write_marks (org, namespace, surface)	none
graph_projections (KeeperMap)	Postgres graph_inflight	none
KeeperMap task_leases, agent_run_claims, and the keys of _migration_lock but the cell probe and nonce keys	leases, the claims where leases places them, and columns and keys of the rows they guard	none
ClickHouse idempotency	deleted: retires with the ClickHouse-authority path; its census row goes and its ClickHouse delete leaves idempotency.delete with the table; the step keeps deleting Postgres idempotency and ClickHouse embedding_cache	retires by rolling-upgrades rule 8
memory_code_link_queue	deleted: the link worker reads the journal	retires by rolling-upgrades rule 8
knowledge_ingest_cancels	deleted: replaced by knowledge_jobs.cancel_requested_at	retires by rolling-upgrades rule 8
_migration_lease	deleted: no module names it	none
task_claims, grant_claims, oauth_claims	deleted: nothing writes them	none

Checked and kept: memory against memory_embeddings (memory is about 91 B per row measured and serves the ClickHouse-side reads; the vectors are apart so a text read never touches them); code_term_df (an hourly snapshot the lexical leg reads in one small statement, kept unless the merged term_df read meets its budget); code_symbol_ids and code_symbol_names (re-keyings of code_symbols that table projections could replace, which ReplacingMergeTree supports only with deduplicate_merge_projection_mode; measured before any change); subject_last_seen (a refreshable view's rollup of agent_events; namespace_visits, written by namespace.mark_seen, moves apart); the conversation, evidence and chunk tables (one fact each).

New ClickHouse tables the waves add, each with its kind and erasure, which its wave's pull request adds to the rows below:

Table	Wave	Kind	Erasure
entity_validity (the graph's versioned interval rows, ReplacingMergeTree(version))	3	Derived, from revision_relations and revision order	census and entity_graph.delete, user scope too; redaction through entity_triple_rows
blacklist_hits (org, namespace, pattern_id): blacklist.list's all-time hit_count and last_hit_at, a rollup of the hit events that the flip seeds with each pattern's counts	1	SoR past the retention of events	no content; deleted with its pattern
term_df, doc_lengths	4	Derived	as the tables they merge: term_aggregates.delete for the memory and chunk corpora, code_index.purge for the code corpus
the move markers paired with table_moves	1	Coord	no content

The ClickHouse tables

Every ClickHouse table, by family: those of schemas/schema.sql and, last, the tables whose names start with _ that a migration creates, which scripts/ops/dump_schema.py leaves out of it, as it leaves out every _ name. Today is the engine (RMT ReplacingMergeTree, MT MergeTree, AMT AggregatingMergeTree, SMT SummingMergeTree), the sort key, the partition key and whether a TTL applies. Target is where it goes: "stays", a Postgres table, a merge, or a deletion. Kind, from each fact has one owner, is the table's kind in the target layout. Erasure is its entry in server/erasure/store/census.py: a census row and its namespace step, a saga step without a census row, or no memory content; the _ tables are outside the census. Wave is the wave that moves the table, or the part of it its Target names, or the precondition a table retires on (waves); a table whose Target is "stays" has none (tests/unit/test_storage_layout_rows.py), and its last column names each wave that changes it in place. The last column names why, and after "named in" up to two modules where a statement, a table-name literal or a module-level …TABLE constant names the table, docstrings and logger names aside; for a table in both stores, only a module with no pg part in its file name. _names_table in tests/unit/test_storage_layout_rows.py holds the exact forms and checks every citation. A citation names a module, not a symbol, so the test can check it, and grep -rnw finds the other code that names the table (statements that cross the stores). The migrator creates loom._migration_lock itself, before any migration file runs; leases places its keys. Tables an operator command creates for its own run (the provider rebuild's snapshot, target, publish and backup tables, server/insights/store/provider_staging.py) are scratch copies of tables listed here, owned and removed by that run, and erasure reaches them (server/erasure/store/provider_copies.py).

Identity, tokens and access

Table	Today	Target	Kind	Erasure	Wave	Why, and code that names it
revoked_tokens	RMT(version) jti, TTL	Postgres revoked_tokens	SoR	no content	1	read on every request through a cache that must fail closed; keyed (kind, key), kind subject_or_jti, cutoff or scim (today's jti string holds a token id or a subject, either with an issued_before that stays a column, plus subject cutoffs and SCIM keys); named in server/auth/revocation.py
signing_keys	RMT(version) kid	Postgres signing_keys	SoR	no content	1	few rows, read by kid on every verify; rotation is a compare-and-set on activate_at; named in server/auth/keyring.py, server/cli/secrets.py
keyring_readers	MT (seen_at, process), TTL	merge into Postgres instances.keyring_refresh_s	Coord	no content	1	one value per live process; the instance row already exists; named in server/auth/keyring.py
service_accounts	RMT(version) (org, name), by org	Postgres service_accounts	SoR	no content	1	point read by (org, name) on token mint and on every delegated request; named in server/auth/service_accounts.py, server/identity/app/me_service_accounts.py
machine_identities	RMT(version) name	Postgres machine_identities	SoR	no content	1	workload trusts, read by name on token exchange; named in server/auth/machines.py, server/observability/otel_probes.py
scim_users	RMT(version) id	Postgres scim_users	SoR	no content	1	deprovisioning revokes tokens: a stale read keeps access; named in server/auth/scim.py
sso_links	RMT(version) (email, org, namespace)	Postgres sso_links	SoR	no content	1	account ownership decided at sign-in by (email, org, namespace); named in server/auth/sso_links.py, server/identity/store/namespace_census.py
principal_groups	RMT(version) (org, group_id)	Postgres principal_groups	SoR	step groups.remove_member	1	group membership expands grants at check time; named in server/identity/app/scim_groups.py, server/erasure/store/steps_principals.py
oauth_state	RMT(version) (record_type, id), by record_type, TTL	Postgres oauth_state	SoR	no content	1	registered clients, refresh tokens' grants (written before the token is handed out) and owed revocations (revocation_owed); what a sign-in spends once is in oauth_signin_state; today's 90-day TTL becomes a sweep; named in server/auth/oauth_store.py, server/auth/revocation.py
oauth_claims	KeeperMap claim_key	delete	Coord	no content	1	no code path writes it (its census entry says so); no module outside the census names the table
oauth_signin_state	KeeperMap claim_key	Postgres oauth_signin_state	SoR	no content	1	strict compare-and-set per key with an expiry: an UPDATE … WHERE value = $old; the KeeperMap expiry becomes expires_at, read by a sweep; named in server/auth/claims.py, server/cli/secrets.py
grant_claims	KeeperMap claim_key	delete	Coord	no content	1	no code path writes it (its census entry says so); no module outside the census names the table (server/auth/grant_claims.py is the claim module, whose keys are in _migration_lock)
namespace_grants	RMT(version) (org, namespace, subject), by org	Postgres namespace_grants	SoR	step grants.revoke	1	every request's role check; a revoke takes effect within the grant cache's invalidation bound; named in server/identity/store/grants.py, server/auth/self_signup.py
namespaces	RMT(updated_at) (org, namespace), by org	Postgres namespaces + ClickHouse mirror	SoR (head)	step personal.retire; step collections.forget_owner	1	the registry with its per-namespace settings, read by grants, sign-in and request paths through a cache; the mirror serves ClickHouse worker scans and the SQL surface views; named in server/auth/self_signup.py, server/cli/hygiene.py
collections	RMT(version) (org, namespace)	Postgres collections	SoR	step collections.forget_owner	1	the collection tree grants inherit through; named in server/identity/store/collections.py, server/identity/store/namespace_census.py
acl_versions	SMT(changes) (org, kind)	merge into Postgres cache_generations (kinds grants, groups, collections)	Coord	no content	1	a change counter caches poll; a generation row plus NOTIFY replaces it; named in server/core/grant_cache.py
auth_attempts	MT (kind, key_sha256, at), TTL	Postgres auth_attempts	Coord	no content	1	attempt counts per kind and hashed key (sign-in, sign-up, OIDC start, workload-token exchange; server/auth/attempts.py), read before any org is known, so one count per cell, not per org; it admits no more per key than today's sliding window and keeps the releases of successful attempts; named in server/auth/attempts.py, server/cli/doctor.py
org_model_calls	RMT(version) org	Postgres org_model_calls	SoR	no content	1	the org's switch for every model call, read at model-call time; one per org; named in server/identity/store/model_calls.py, server/llm/customer.py
user_state	RMT(version) (org, subject), by org	Postgres user_state	SoR	no content	3	per-user dashboard state, read and written by key; named in server/identity/app/me_routes.py, server/identity/store/user_state.py
namespace_visits	RMT(version) (org, subject, namespace), by org	Postgres namespace_visits	SoR	no content	3	written by namespace.mark_seen and read by key, like user_state; named in server/identity/app/namespace_membership.py
subject_last_seen	AMT (org, subject)	stays	Derived, SoR past its source's retention	step subject_last_seen.delete	—	max aggregate per subject, filled by a refreshable view over agent_events, without a model; past the source's retention the only copy; named in server/tasks/store/repo.py, server/erasure/store/steps_memory.py

Deployment configuration and secrets

Table	Today	Target	Kind	Erasure	Wave	Why, and code that names it
deployment_config	RMT(version) name	Postgres deployment_config	SoR	no content	1	the settings snapshot the leader publishes and every process checks its own against; named in server/core/deployment_config.py
runtime_overrides	RMT(version) (kind, key)	Postgres runtime_overrides	SoR	no content	1	rate limits, budgets and live settings every replica applies; named in server/core/runtime_overrides.py
provider_configuration	RMT(version) config_id	Postgres provider_configuration + rebuild jobs	SoR	no content	1	sealed provider envelopes and activation state; a rebuild is a jobs row with its ledger (cursors); named in server/insights/store/provider_configuration.py, server/cli/secrets.py
embedder_calibration	RMT(version) embedder_model	Postgres embedder_calibration	Derived	no content	1	one row per embedder model, adopted by every replica; rebuilt by a recalibration, at embedding cost; named in server/memory/store/calibration.py
analysis_secrets	RMT(version) id	Postgres analysis_secrets	SoR	no content	1	sealed insights.query scope key and analyst password, written as literals into its ClickHouse's row policies (server/insights/domain/analysis.py); named in server/insights/store/analysis.py, server/cli/secrets.py
assistant_service	RMT(version) id	Postgres assistant_service	SoR	no content	1	the sealed password and API key the assistant service logs in with (server/assistant/app/service.py), written step by step under a lease: one secret per cell with no org, embedded in no ClickHouse object; named in server/assistant/store/service.py, server/assistant/app/service.py
sql_access	RMT(updated_at) (user_name, org, namespace)	Postgres sql_access + ClickHouse mirror	SoR (head)	no content	1	which SQL user may read which org and namespace through the scoped sql_v1_* views: a grant, so its head and its revocations are in Postgres; the views read the mirror by currentUser() inside ClickHouse, and a revoke answers once its mirror rows have landed; its wildcard grants (namespace = '*', org = '*') are rows like any other; named in server/insights/store/sql_access.py, server/identity/store/namespace_census.py
analysis_canary	RMT id	stays	SoR	no content	—	must stay in ClickHouse: insights.query reads it through the same row policies as real data; named in server/insights/domain/analysis.py
storage_authority	RMT(epoch) tuple()	stays	SoR	no content	—	ClickHouse's half of the pairing with Postgres's authority row (the deployment id); named in server/core/pg_profile.py
frontend_assets	RMT(published_at) path, TTL	stays	Cache	no content	—	write-once per build and path, read by path, 30-day TTL; each cell's replicas publish their own; named in server/storage/frontend_assets.py
instances	KeeperMap instance_id	Postgres instances	Coord	no content	1	heartbeat registry the rate limiter, the workers lease and key rotation read; one row per process; named in server/core/instances.py
cell_heartbeats	KeeperMap instance_id	merge into Postgres instances (cell, tenancy_mode, fingerprint columns)	Coord	no content	1	each process's LOOM_CELL, tenancy mode and configuration fingerprint, which the loom cell commands (prepare, attach, update) compare across a cell's live processes; a second KeeperMap table only because a KeeperMap table takes no new column, so its fields become columns of the process's instances row (cells); named in server/core/instances.py
carry_acks	RMT(at) process, TTL	Postgres carry_acks	Coord	no content	2	per-process acknowledgement of the cell's ClickHouse column lists; a boot waits on it; named in server/core/rmt_carry.py
worker_status	RMT(version) (worker, instance_label, pass_started), TTL	stays	Log	no content	—	one row per worker pass, 30-day TTL, scanned by status pages; its resume-cursor rows are a build ledger of the ClickHouse output they describe, which the TTL can expire, so a worker idle past it restarts from the beginning; named in server/workers/status_store.py, server/cli/restore.py

Memory

Table	Today	Target	Kind	Erasure	Wave	Why, and code that names it
memory	RMT(version) (org, namespace, agent, user_id, session_id, memory_key), by toYYYYMM(created_at)	stays	Derived	census, memory.tombstone, user memory.retire; step consolidated.tombstone	—	the current rows in ClickHouse: the source of memory_versions_mv, granted to insights.query, read by ClickHouse-side joins; published from memory_current and rebuilt by republishing it; named in server/memory/store/history.py, server/memory/store/inserts.py
memory_embeddings	RMT(version) (org, namespace, agent, user_id, session_id, scope, embedding_model, memory_key), by toYYYYMM(created_at)	stays	Derived	census, memory_embeddings.tombstone, user scope too; step shared_embeddings.tombstone; step consolidated.tombstone	—	the vectors of the memory heads (memory_current) and the shared heads (shared, and from wave 3 shared_current); Postgres stores none (re-embedding costs provider calls); wave 4's vector levers change its columns and codec; named in server/memory/store/inserts.py, server/memory/store/shared.py
memory_versions	RMT (org, namespace, agent, user_id, session_id, memory_key, version), by toYYYYMM(created_at)	stays	SoR	census, memory_versions.delete, user scope too	—	version history, fed by memory_versions_mv: the system of record for history written before a deployment's journal existed, derived from memory_revisions for later rows; for a version superseded with its journal payload cleared, the only copy of its content; named in server/memory/store/conflict_resolver.py, server/memory/store/history.py
shared	RMT(version) (org, namespace, memory_key), by (org, namespace)	Postgres shared_current + ClickHouse shared	SoR (head)	census, shared.tombstone	3	promoted namespace-wide memory: a head per (org, namespace, memory_key) on the memory journal (its revisions in memory_revisions, its deliveries in memory_deliveries); the ClickHouse table stays its search projection; a namespace erasure tombstones it and a user erasure matches it by provenance; named in server/memory/store/shared.py, server/erasure/store/steps_memory.py
working_memory	RMT(version) (org, namespace, agent, user_id, session_id, run_id, block_key), by (org, namespace)	Postgres working_memory	SoR	census, working_memory.tombstone, user scope too	3	keyed blocks read and replaced by key, never ranked; named in server/memory/app/working_memory.py, server/erasure/store/steps_memory.py
memory_reinforcement	RMT(version) (org, namespace, agent, user_id, memory_key)	stays	Derived	census, memory_reinforcement.delete, user scope too	—	folded usage counters, rebuildable from memory_usage_events; named in server/memory/app/reinforcement.py, server/erasure/store/steps_memory.py
memory_term_freq	AMT (org, namespace, term), by toYYYYMM(df_added_at)	merge into ClickHouse term_df	Derived	step term_aggregates.delete	4	document frequency per term, folded by memory_term_freq_refresh from memory_embeddings without a model; one table for memory, chunks and code with a corpus column; named in server/memory/store/term_freq.py, server/memory/store/bm25_stats.py
chunk_term_freq	AMT (org, namespace, term), by toYYYYMM(df_added_at)	merge into ClickHouse term_df	Derived	step term_aggregates.delete	4	document frequency per term of the chunks, fed by chunk_term_freq_mv and chunk_context_term_freq_mv from chunks; named in server/memory/store/term_freq.py, server/erasure/store/steps_memory.py
code_term_freq	AMT (org, namespace, term)	merge into ClickHouse term_df	Derived	step code_index.purge	4	as above; named in server/code/store/index/search.py
memory_doc_stats	RMT(version) (org, namespace, agent, user_id, session_id, scope, embedding_model, memory_key)	merge into ClickHouse doc_lengths	Derived	step term_aggregates.delete	4	document length per searchable row, folded by memory_doc_stats_refresh from memory_embeddings; one table with code_doc_stats; named in server/memory/store/bm25_stats.py, server/erasure/store/steps_memory.py
code_doc_stats	RMT(version) (org, namespace, chunk_id)	merge into ClickHouse doc_lengths	Derived	step code_index.purge	4	as above; named in server/code/store/index/search.py
code_term_df	MT (org, namespace, term)	stays	Derived	step code_index.purge	—	an hourly snapshot of code_term_freq the lexical leg reads in one small statement; kept unless the merged read meets its budget; wave 4 rewrites its feed, code_term_df_refresh, onto term_df; named in server/code/store/index/search.py
memory_evidence	RMT(created_at) (org, namespace, agent, user_id, session_id, observed_at, evidence_id), by toYYYYMM(created_at)	stays	SoR	census, raw_evidence.delete, user scope too	—	raw evidence the extraction reads, append-only; named in server/memory/app/evidence.py, server/erasure/store/mutations.py
memory_chunks	RMT(created_at) (org, namespace, session_id, chunk_index, content_sha256), by toYYYYMM(created_at)	stays	Derived (SoR for chunks memory.import_evidence writes)	census, memory_chunks.tombstone, user scope too	—	chunked evidence and chunks of long memory.set values, with vectors (server/memory/app/set_chunks.py); rebuilt from memory_evidence and the heads at embedding cost, except the chunks memory.import_evidence writes with no memory_evidence row (server/memory/app/evidence_transfer.py), held only here; named in server/memory/app/set_chunks.py, server/memory/app/chunk_projection.py
memory_chunk_sources	RMT(created_at) (org, namespace, session_id, chunk_index, memory_key, evidence_id), by toYYYYMM(created_at)	stays	SoR	census, source_links.tombstone, user scope too	—	chunk-to-memory links written by the extraction handler after a run and by memory.import_evidence (server/memory/app/evidence_transfer.py), which takes links a caller states; held only here, so lost without a backup; named in server/memory/app/chunk_links.py, server/erasure/store/steps_graph.py
memory_belief_sources	RMT(created_at) (org, namespace, memory_key, evidence_id)	stays	SoR	census, source_links.tombstone, user scope too	—	evidence links of a belief, written by the extraction handler after a run (server/memory/app/extract/handler.py), by consolidation linking merged rows to their summary (server/memory/app/consolidate.py) and by period digests (server/memory/app/period_digest.py); held only here, so lost without a backup; named in server/memory/app/evidence.py, server/erasure/store/steps_graph.py
session_summaries	RMT(version) (org, namespace, agent, user_id, session_id, embedding_model), by toYYYYMM(created_at)	stays	Derived (SoR for digests memory.import_evidence writes)	census, summaries.tombstone, user scope too	—	session digests with vectors, written by a model from the session's turns (server/memory/app/session_summary.py) and rebuildable at model cost from those turns in memory_evidence; the digests memory.import_evidence writes are held only here; wave 3 retires a digest whose derived_from names a redacted key (deliveries); named in server/memory/app/session_summary.py, server/memory/app/evidence_transfer.py
derived_facts	MT (org, namespace, subject, predicate, category), by toYYYYMM(created_at)	stays	Derived (SoR for operands a memory.bulk_set entry states)	census, derived_facts.delete, user scope too	—	per-occurrence operands for multi-hop answers, append-only, written by memory.bulk_set from each entry's derived list, which a caller states or extraction from messages fills; a stated operand is held only here, so a rebuild, by re-running extraction over the messages, restores only the extracted ones; wave 3 adds the fact's memory version and retires the facts of a redacted version (deliveries); named in server/memory/app/triple_projections.py, server/erasure/store/steps_memory.py
memory_records	RMT(version) (org, namespace, schema_name, memory_key, record_id)	Postgres records + ClickHouse projection	SoR (head)	census, records.tombstone, user scope too	3	a record's current fields, read by key and compared on write; Postgres records keeps each field's state and evidence id with its value, which only memory_record_fields holds today; the projection stays for the record side leg and record query, which join it with memory_record_fields, and feeds memory_record_versions_mv; named in server/memory/store/records.py, server/memory/app/records.py
memory_record_versions	RMT (org, namespace, schema_name, memory_key, record_id, version), by toYYYYMM(created_at)	stays	SoR	census, records.tombstone, user scope too	—	record history, fed by memory_record_versions_mv from memory_records; from wave 3, one row per delivered revision, reconciled after a restore by the journal replay; named in server/erasure/store/steps_graph.py, server/memory/app/records.py
memory_record_fields	RMT(version) (org, namespace, schema_name, field_name, record_id)	stays	SoR, then Derived	census, records.tombstone, user scope too	—	field-level projection record_query scans; each field's state and evidence id are held only here today (memory_records keeps the values), so it is rebuildable only from wave 3, from Postgres records; named in server/memory/app/records.py, server/memory/store/record_query.py
memory_record_conflicts	RMT(version) (org, namespace, schema_name, memory_key, field_name, incoming_record_id)	stays	SoR, then Derived	census, records.tombstone, user scope too	—	the conflicts a record commit found; from wave 3 they ride the record's revision, so the journal replay restores them after a ClickHouse restore and they are derived from it; its name is one of the tables insights.query grants (server/insights/domain/analysis.py); named in server/memory/app/records.py, server/erasure/store/steps_graph.py
memory_schemas	RMT(version) (org, namespace, name)	Postgres record_schemas	SoR	no content	3	per-namespace schema definitions, read by name on every record write; named in server/memory/app/records.py
memory_schema_fields	RMT(version) (org, namespace, schema_id, field_name)	merge into Postgres record_schemas.fields	SoR	no content	3	the fields of one schema are read and written with it; named in server/memory/app/records.py
profile_schemas	RMT(version) (org, namespace)	Postgres profile_schemas	SoR	no content	3	one JSON Schema per namespace, read on every profile build; named in server/memory/store/profile_schemas.py
categories	RMT(version) (org, namespace, name)	stays	SoR	no content	—	operator-authored categories with vectors; it stays because search joins it with the memories inside ClickHouse, and its by-key reads (registered_ids, extraction's category fetch) need no Postgres commit; wave 3 reads registered_ids with sequential consistency; named in server/cli/import_clickgap.py, server/identity/app/namespace_catalog.py
memory_categories	RMT(created_at) (org, namespace, memory_key, category_id)	stays	SoR	census, curation.tombstone	—	the category links of each memory, written by memory.assign_category, memory.import and a CLI importer, and held nowhere else; it stays because search joins it with the memories inside ClickHouse, and the import's insert-if-absent read runs with sequential consistency, a duplicate collapsing on the key; named in server/memory/store/categories.py, server/cli/import_clickgap.py
memory_extraction_queue	RMT(version) (org, namespace, agent, user_id, session_id, queue_id), by toYYYYMM(enqueued_at), TTL	merge into Postgres jobs (kind extraction)	SoR	census, raw_evidence.delete, user scope too	2	a queue with leases and statuses on an engine without row locks; retention: the jobs sweep for its kind (jobs); named in server/memory/app/extraction_queue.py, server/erasure/store/steps_memory.py
learnings_queue	RMT(version) (org, namespace, candidate_id), by toYYYYMM(proposed_at)	Postgres learning_candidates	SoR	census, learnings_queue.delete, user scope too	3	candidates a human promotes or rejects: a status a decision changes once; named in server/memory/store/learnings.py, server/erasure/store/steps_ledgers.py
learnings_supporters	MT (org, namespace, candidate_id, user_id, session_id)	Postgres learning_supporters	SoR	census, learnings_queue.delete, user scope too	3	each supporter's bounded quotes, deleted with the user; foreign key to its candidate; named in server/memory/store/learnings.py, server/erasure/store/predicates.py
memory_code_link_queue	MT queued_at, by toDate(queued_at), TTL	delete; the link worker reads memory_revisions from its cursor in code_index_state	Derived	census, code_links.delete, user scope too	2	the journal is already the ordered list of every write; memory_code_link_queue_mv fills it until N+1; named in server/memory/store/code_links.py, server/erasure/store/steps_memory.py
memory_code_links	RMT(version) (org, code_namespace, path, qualified_name, namespace, memory_id)	stays	Derived (SoR for anchors)	census, code_links.delete, user scope too; step consolidated.tombstone	—	memory-to-code links built by the link worker; a rebuild anchors each link at the current head, so the anchor, baseline and former identities that say what changed since a link was made are held only here; their suggestion comes from a model (server/memory/app/code_link_judge.py); named in server/memory/store/code_links.py, server/memory/store/redaction.py
memory_webhooks	RMT(version) (org, namespace, webhook_id)	Postgres webhooks	SoR	no content	2	registrations read on every delivery pass; named in server/memory/store/webhooks.py, server/identity/store/namespace_census.py
memory_webhook_outbox	MT (org, namespace, webhook_id, occurred_at, event_id), by toDate(occurred_at), TTL	merge into Postgres jobs (kind webhook)	SoR	census, memory_webhook_outbox.delete, user scope too	2	a transactional outbox: the event row commits with the revision that caused it; named in server/memory/store/webhooks.py, server/erasure/store/steps_ledgers.py
memory_webhook_attempts	MT (event_id, version), by toDate(recorded_at), TTL	merge into Postgres jobs columns + ClickHouse events	Log	no content	2	the job row keeps attempts and last status; each attempt is also an events row; named in server/memory/store/webhooks.py
memory_projection_failures	MT (org, namespace, projection, created_at, failure_id), TTL	merge into Postgres jobs (kind projection_repair)	SoR	census, projection_failures.delete, user scope too	2	a retry queue of failed projections; retention: the jobs sweep for its kind (jobs); named in server/memory/app/projection_failures.py, server/erasure/store/steps_ledgers.py
delete_version_checks	RMT(updated_at) (org, namespace, agent, user_id, session_id, memory_key, tombstone), TTL	merge into Postgres jobs (kind delete_followup)	SoR	census, delete_version_checks.delete, user scope too	2	the Postgres commit already checks the head, but a delete still owes derived retirement and a webhook after its tombstone; that is a job its commit inserts; named in server/memory/store/delete_checks.py, server/erasure/store/steps_ledgers.py
memory_drops	MT (org, namespace, stage, reason, ts), TTL	stays	Log	census, memory_drops.delete	—	why a write was dropped, 30-day TTL; named in server/memory/app/drops.py, server/erasure/store/steps_ledgers.py
memory_ask_precedents	RMT(version) (org, namespace, precedent_key), TTL	stays	Cache	census, ask_precedents.delete, user scope too	—	memory.ask answers keyed by question, 30-day TTL; named in server/memory/store/ask_precedents.py, server/memory/store/redaction.py
conflict_allowlist	RMT(allowed_at) (org, namespace, memory_key_a, memory_key_b)	Postgres conflict_allowlist	SoR	census, curation.tombstone	3	a user's decision that two keys do not conflict, read on every conflict check; named in server/memory/store/conflicts.py, server/erasure/store/steps_ledgers.py
extraction_policies	RMT(version) (org, namespace)	Postgres extraction_policies	SoR	no content	1	rules every extraction call must carry; read by (org, namespace); named in server/memory/store/extraction_policy.py
namespace_calibration_result	RMT(version) (org, namespace)	Postgres namespace_calibration	Derived	no content	3	one row per namespace a calibration writes and searches read; rebuilt by a recalibration, at embedding cost; named in server/memory/app/embedder_calibration.py
namespace_policies	RMT(version) (org, namespace, policy_id)	Postgres namespace_policies	SoR	no content	1	policy rules the gate reads on every guarded op; named in server/identity/store/policy.py
namespace_types	RMT(updated_at) (org, namespace, memory_type), by org	Postgres namespace_types	SoR	no content	3	memory types a namespace declares, read on write; named in server/identity/app/namespace.py, server/identity/app/namespace_snapshot.py
namespace_ontology	RMT(version) (org, namespace, kind, name), by org	Postgres namespace_ontology	SoR	no content	3	entity kinds and relations a namespace declares; named in server/identity/app/namespace_ontology.py
blacklist	RMT(created_at) (org, namespace, pattern_id)	Postgres blacklist	SoR	no content	1	refusal patterns checked on every write, global (org = '*') and per org, read through a cache; a change bumps the policy scope in its commit; hit counts move to events and the blacklist_hits rollup (new tables); named in server/memory/store/blacklist.py, server/memory/app/blacklist_guard.py
blacklist_version	RMT(updated_at) singleton	merge into Postgres cache_generations (kind blacklist)	Coord	no content	1	a change stamp caches poll; named in server/memory/store/blacklist.py, server/core/namespace_write_marks.py
consolidation_runs	MT (org, namespace, ran_at, run_id), by toYYYYMM(ran_at), TTL	stays	Log	census, consolidation_runs.delete, user scope too	—	one row per consolidation pass, 180-day TTL; named in server/memory/app/consolidate.py, server/erasure/store/steps_ledgers.py
context_blobs	RMT(version) (org, namespace, blob_key), by toYYYYMM(expires_at), TTL	stays (search pages) + Postgres context_offloads (offloaded context)	Cache; SoR (head)	census, context_blobs.tombstone, user scope too	3	two writers: memory.search page handles past top_k (server/memory/app/search_pages.py), a cache whose loss answers page_expired, which stays here; and context.offload's replaced context, the only copy, which becomes a Postgres head (context offloads); named in server/memory/app/context_offload.py, server/memory/app/search_pages.py
operator_edges	RMT(created_at) (org, namespace, from_key, to_key, relation), by toYYYYMM(created_at)	Postgres operator_edges + ClickHouse projection	SoR (head)	census, curation.tombstone	3	links an op writes and unwrites between keys, changed in place: a Postgres head ordered by its commit, the projection kept for the graph statements that join it inside ClickHouse; the projection is rebuilt as ReplacingMergeTree(version) on the head's version in that wave; named in server/memory/app/digest_chain.py, server/memory/app/ops.py
session_briefs	RMT(version) (org, namespace, brief_key)	Postgres session_briefs	SoR	census, session_briefs.delete, user scope too	3	a brief's head with a compare-and-set on its revision; read by its store module and its erasure step; named in server/memory/store/session_briefs.py, server/erasure/store/steps_ledgers.py
session_brief_revisions	MT (org, namespace, brief_key, revision, version)	Postgres session_brief_revisions	SoR	census, session_briefs.delete, user scope too	3	brief history; small, and owned with the head's compare-and-set, so it moves with it and session_brief_revisions_mv goes; named in server/memory/store/session_briefs.py, server/erasure/store/steps_ledgers.py
namespace_guides	MT (org, namespace, revision)	Postgres namespace_guides	SoR	census, namespace_guides.delete	3	revisioned text whose writer names the revision it read: a compare-and-set; named in server/identity/store/namespace_guides.py, server/erasure/store/steps_ledgers.py

Knowledge and conversations

Table	Today	Target	Kind	Erasure	Wave	Why, and code that names it
documents	RMT(version) (org, namespace, source_uri), by toYYYYMM(ingested_at)	stays	Derived (SoR for documents with no head: written before their namespace had heads, or with a source URI or writer Postgres cannot store)	census, documents.tombstone, user scope too	—	the searchable document rows, published from Postgres knowledge_heads; a document with no head is kept as restored; named in server/knowledge/store/documents.py, server/insights/store/mentor.py
document_blobs	RMT(created_at) (org, namespace, sha256)	Postgres document_catalog + bytes in the document store	SoR	census, documents.tombstone	3	the originals' catalog, read by key and changed in place (a re-upload restamps it, switch_to and unwrite rewrite its location, server/knowledge/store/blobs.py), with sweeping decided by an age heuristic rather than a lock: the catalog moves beside knowledge_heads, and the bytes stay in the bucket, or in document_blobs where no bucket is configured, written once and deleted by the catalog's owner; named in server/knowledge/store/blobs.py, server/erasure/store/steps_memory.py
blob_uploads	MT (org, user_id, namespace, sha256)	merge into Postgres document_uploaders	SoR	census, documents.tombstone, user scope too	3	who uploaded each original, read by a user's erasure to find the user's originals; it moves with the catalog, document_uploaders becoming its one owner, with the census row it has today; named in server/knowledge/store/blobs.py, server/erasure/store/steps_memory.py
chunks	MT (org, namespace, embedding_model, source_uri, chunk_idx), by toYYYYMM(created_at)	stays	Derived	census, chunks.supersede, user scope too	—	document chunks with vectors; rebuildable from the originals at embedding cost; named in server/knowledge/store/chunks.py, server/knowledge/store/documents.py
chunk_supersessions	RMT(created_at) (org, namespace, embedding_model, source_uri, doc_version)	stays	Derived	no content	—	which chunk versions a newer document version retired, rebuilt from documents versions; named in server/knowledge/domain/live_chunks.py, server/knowledge/store/inspect.py
datasets	RMT(version) (org, namespace, source_uri, dataset_id, doc_version)	stays	Derived (SoR for rows only ClickHouse holds)	census, documents.tombstone, user scope too	—	tables extracted from documents, versioned with the document; under the Postgres authority a best-effort projection of Postgres knowledge_datasets that its writer inserts inline (the datasets exception); named in server/knowledge/store/datasets.py, server/erasure/store/steps_memory.py
dataset_rows	MT (org, namespace, dataset_id, snapshot_id, row_id)	stays	Derived	census, documents.tombstone, user scope too	—	typed slots of each extracted table row, written once per snapshot; rebuilt only by ingesting the source again; named in server/knowledge/store/datasets.py, server/erasure/store/steps_memory.py
knowledge_ingestions	RMT(version) (org, namespace, ingestion_id), TTL	stays	Derived (SoR for jobs Postgres cannot hold, within its 30-day TTL)	census, documents.tombstone, user scope too	—	the ClickHouse projection of Postgres knowledge_jobs, which holds ingestion jobs under the Postgres authority with a fencing token and a cancel column; kept for reports and knowledge.inspect; rebuilt in wave 2 as ReplacingMergeTree on the job's commit version; named in server/knowledge/store/ingestions.py, server/erasure/store/steps_memory.py
knowledge_ingest_cancels	MT (org, namespace, ingestion_id), TTL	delete; knowledge_jobs.cancel_requested_at	SoR	census, documents.tombstone, user scope too	2	a user's cancel request, counted by user in the census, kept so a build that predates knowledge_jobs reads it; retires by rolling-upgrades rule 8; named in server/erasure/store/steps_memory.py, server/knowledge/store/ingestions.py
knowledge_uploads	MT (org, namespace, upload_id), TTL	Postgres knowledge_uploads	SoR	census, documents.tombstone, user scope too	2	an upload's scoped identity, checked at ingest, expiring; today's TTL, 7 days past expires_at, becomes a sweep; named in server/knowledge/store/ingestions.py, server/erasure/store/steps_memory.py
knowledge_sources	RMT(version) (org, namespace, name)	Postgres knowledge_sources	SoR	step knowledge_sources.retire	3	registrations with sealed credentials, read by name; named in server/knowledge/store/sources.py, server/identity/store/namespace_census.py
conversations	MT (org, namespace, thread_id, ts, msg_id), by toYYYYMM(ts)	stays + Postgres conversation_states	SoR	census, conversations.tombstone, user scope too	3	messages, an append log read by scans; their tombstones and saved state move to Postgres conversation_states in wave 3 (conversation state); named in server/knowledge/store/conversations.py, server/erasure/store/exports.py
conversation_threads	RMT(updated_at) (org, namespace, thread_id), by toYYYYMM(created_at)	stays	SoR	census, conversations.tombstone, user scope too	—	threads: the title, metadata and run_id a caller sets with conversation.create and conversation.update, and a model title for an untitled thread, with no TTL; it stays because its tombstones must reach every version of the row, as today's mutations do, and no reader needs a Postgres commit of it; an update merges onto the row it reads, so a write racing another write can lose a field, and one racing a thread delete or an erasure brings the thread back, a race of today's code that no wave changes (conversation state); named in server/knowledge/store/conversations.py, server/erasure/store/exports.py
conversation_embeddings	RMT(ts) (org, namespace, embedding_model, thread_id, msg_id), by toYYYYMM(ts)	stays	Derived, SoR past its source's retention	census, conversations.tombstone, user scope too	—	message vectors, rebuildable at embedding cost while their messages are retained; it has no TTL, so past conversations retention its excerpt, vector and saved state are the only copy; its saved state and saver stay on its own rows as version-row inserts from wave 3 (conversation state); named in server/knowledge/store/conversations.py, server/erasure/store/mutations.py

Entity graph

Table	Today	Target	Kind	Erasure	Wave	Why, and code that names it
entity_edges	MT (org, namespace, entity_a, entity_b), by toYYYYMM(last_seen)	stays	Derived	census, entity_graph.delete	—	pair edges with validity, from entity resolution and extraction over the memories, which need a model call to recompute; wave 3 writes their validity as entity_validity rows; named in server/memory/app/graph_undo.py, server/memory/app/projections.py
entity_degree	SMT (org, namespace, entity)	stays	Derived	census, entity_graph.delete	—	SummingMergeTree fed by entity_degree_mv from entity_edges inserts; named in server/memory/store/fact_graph.py, server/erasure/store/steps_graph.py
entity_triples	MT (org, namespace, subject, relation, object), by toYYYYMM(last_seen)	stays	SoR, then Derived	census, entity_graph.delete	—	subject-relation-object facts with validity; the system of record for relations no revision maps to, derived from Postgres revision_relations from wave 3, without a model call, for the rest; wave 3 writes their validity as entity_validity rows; named in server/memory/app/graph_undo.py, server/memory/app/triple_projections.py
entity_edge_rows	RMT (org, namespace, memory_id, user_id, content_sha256, entity_a, entity_b)	stays	Derived	census, graph_links.delete, user scope too	—	which row and value named each pair, rebuilt by the entity projection from the memories, without a model; wave 4 backfills it from entity_edge_memories; named in server/memory/app/edge_links.py, server/memory/app/projections.py
entity_triple_rows	RMT (org, namespace, memory_id, user_id, content_sha256, subject, relation, object, inferred)	stays	SoR, then Derived	census, graph_links.delete, user scope too	—	which row and value stated each triple; the system of record for relations no revision maps to, derived from Postgres revision_relations from wave 3, without a model call, for the rest; wave 4 backfills it from entity_triple_memories; named in server/memory/app/triple_projections.py, server/memory/app/projections.py
entity_edge_memories	RMT(seen_at) (org, namespace, entity_a, entity_b, memory_key), by toYYYYMM(seen_at)	merge into ClickHouse entity_edge_rows	Derived	census, graph_links.delete, user scope too	4	key links kept for values written before row links; a backfill of row links retires them; named in server/memory/app/projections.py, server/erasure/store/predicates.py
entity_triple_memories	RMT(seen_at) (org, namespace, subject, relation, object, memory_key), by toYYYYMM(seen_at)	merge into ClickHouse entity_triple_rows	SoR, then Derived	census, graph_links.delete, user scope too	4	as above; the system of record for relations no revision maps to, derived from Postgres revision_relations from wave 3, without a model call, for the rest; named in server/memory/app/triple_projections.py, server/erasure/store/steps_graph.py
entity_vectors	RMT(created_at) (org, namespace, agent, user_id, entity_text, memory_key), by toYYYYMM(created_at)	stays	Derived	census, entity_vectors.delete, user scope too	—	entity mention vectors, one row per entity and memory, embedded from the memories by the entity projection; wave 3 retires a redacted key's rows for the entities its version's graph rows name, and the projection rebuilds them (deliveries); named in server/memory/store/embeddings.py, server/cli/import_clickgap.py
entity_summaries	RMT(version) (org, namespace, agent, user_id, entity, embedding_model), by toYYYYMM(created_at)	stays	SoR	census, summaries.tombstone, user scope too	—	entity summaries with vectors, read by search; no module writes them today (the provider rebuild re-embeds their text), so its rows are the only copy; wave 3 retires the summaries of the entities a redacted version's graph rows name, not rebuilt (deliveries); named in server/memory/store/rows.py, server/erasure/store/steps_graph.py
entity_canonical_map	RMT(version) (org, namespace, mention_text), by org	stays	Derived	step entity_canonical_map.tombstone	—	mention-to-canonical resolution, rebuilt by the entity projection from the memories, without a model but order-dependent; named in server/memory/app/entity_resolution.py, server/erasure/store/steps_memory.py
entity_communities	RMT(version) (org, namespace, level, entity)	stays	Derived	census, entity_communities.delete, user graph_links.delete	—	community detection over entity_edges, rebuilt by its worker without a model; named in server/memory/app/entity_communities.py, server/erasure/store/steps_graph.py
graph_projections	KeeperMap token	Postgres graph_inflight	Coord	no content	2	up to SLOTS in-flight projection slots (server/memory/store/graph_projections.py) per namespace that graph walks wait on, plus the namespace's last graph write; named in server/memory/store/graph_projections.py, server/core/leases.py
namespace_write_keys	KeeperMap key	merge into Postgres write_marks	Coord	no content	2	a namespace's last ClickHouse write for read-your-writes on any replica (written_within); one table with the per-surface marks; named in server/core/namespace_write_marks.py
namespace_write_marks	AMT (org, namespace, plane)	merge into Postgres write_marks	Coord	no content	2	the last insert per namespace and surface, for the namespace sweep and read-your-writes (write marks); named in server/core/namespace_write_marks.py, server/identity/store/namespace_census.py
query_nodes	AMT (org, namespace, query_hash)	stays	Log	step query_nodes.delete	—	query text and its embedding, written directly by search telemetry behind its own setting (server/memory/app/search/telemetry.py); nothing rebuilds it; named in server/erasure/store/steps_memory.py, server/memory/app/search/telemetry.py

Code index

Table	Today	Target	Kind	Erasure	Wave	Why, and code that names it
code_repos	RMT(version) (org, namespace)	Postgres code_repos + ClickHouse mirror	SoR (head)	no content	3	registered repositories with sealed credentials, read by namespace; a mirror without credentials stays in ClickHouse for the SQL surface views that join it; named in server/code/store/index/code_repos.py, server/identity/store/namespace_census.py
code_index_state	RMT(version) (org, namespace, state_key)	stays + Postgres cursors (its knowledge_source:<name> rows)	Derived	step code_index.purge	3	how far the code tables are built (git_head, pr_cursor, scip_commit, _FINGERPRINT_KEYS in server/code/store/index/status.py), and, once wave 2 retires the link queue, the link worker's per-namespace cursor into memory_revisions: a build ledger kept beside the tables it describes, so a restore brings both back together; the link cursor holds a seq, never a memory id, and resets on a Postgres restore; its knowledge_source:<name> rows, each source's sync state, move to Postgres cursors with knowledge_sources in wave 3 (cursors); named in server/code/store/index/base.py, server/knowledge/store/sources.py
code_coverage_watches	RMT(version) (org, namespace, commit_sha, inbox)	Postgres code_coverage_watches	SoR	step code_index.purge	3	subscriptions a notify worker reads by commit; named in server/code/store/coverage_watches.py
code_scip_batches	RMT(version) (org, namespace, batch_id), by toYYYYMM(created_at), TTL	merge into Postgres jobs (kind code_scip)	SoR	step code_index.purge	2	upload batches with a state machine; retention: the jobs sweep for its kind (jobs); named in server/code/store/index/scip_staging.py
code_scip_parts	MT (org, namespace, batch_id, seq), by toYYYYMM(created_at), TTL	merge into the code_scip job's bytes (bucket, or Postgres job_parts)	SoR	step code_index.purge	2	a SCIP upload's parts, held until its batch is applied (server/code/store/index/scip_staging.py); owned by the batch's job and leaving with it; deleted when its batch is applied, else by a sweep 7 days after created_at, today's TTL, apart from the job row's retention; named in server/code/store/index/scip_staging.py
code_search_memo	RMT(created_at) (org, namespace, key), TTL	stays	Cache	step code_index.purge	—	search answers by key, 60-minute TTL; named in server/code/store/index/search_memo.py
code_search_trace	MT (org, namespace, ts), by toYYYYMM(ts), TTL	stays	Log	census, code_search_trace.delete, user scope too	—	search traces, 180-day TTL; named in server/code/app/common.py, server/code/store/index/search.py
code_search_outcome	MT (org, namespace, trace_run_id, ts), by toYYYYMM(ts), TTL	stays	Log	census, code_search_trace.delete	—	search outcomes, 180-day TTL; named in server/code/app/outcome.py, server/code/store/index/search.py
code_symbol_ids	RMT (org, namespace, symbol_id, tier, qualified_name)	stays	Derived	step code_index.purge	—	code_symbols re-keyed by id through code_symbol_ids_mv; named in server/code/store/index/symbols.py
code_symbol_names	RMT (org, namespace, name, tier, qualified_name, symbol_id)	stays	Derived	step code_index.purge	—	code_symbols re-keyed by short name through code_symbol_names_mv; named in server/code/store/index/symbols.py
code_answers	RMT(version) (org, namespace, embedding_model, question_id)	stays	Cache	census, code_answers.delete, user scope too	—	cached model answers that may quote memory, so erasure reaches them; named in server/code/store/index/answers.py, server/memory/store/redaction.py
code_unit_signal	SMT((positive, negative)) (org, namespace, unit_id)	stays	SoR	step code_index.purge	—	feedback increments per unit, summed; named in server/code/store/index/search.py
code_gh_items	RMT(version) (org, namespace, number)	stays	Derived	step code_index.purge	—	issues and pull requests mirrored from the repository host, refetched by code_pr_history back to code_gh_items_backfill_days; named in server/code/store/index/gh_items.py
code_pr_history	RMT(version) (org, namespace, pr_number)	stays	Derived	step code_index.purge	—	merged pull requests and their files mirrored from the repository host, refetched by code_pr_history; named in server/code/store/index/pr_history.py, server/code/store/index/reviews.py
code_pr_reviews	RMT(version) (org, namespace, path, pr_number, comment_id)	stays	Derived	step code_index.purge	—	pull request review comments mirrored from the repository host, refetched by code_pr_history; named in server/code/store/index/reviews.py, server/cli/code_prs.py
code_chunks	RMT(version) (org, namespace, embedding_model, path, chunk_id), by (org, namespace)	stays	Derived	census, code_index.purge	—	code chunks with vectors, rebuilt from the repository by loom code backfill (by code_ingest's first-index slices for a self-service entry) at embedding cost; named in server/code/app/index/compaction.py, server/core/vectors/code_tables.py
code_summaries	RMT(version) (org, namespace, embedding_model, scope_type, scope_path)	stays	Derived	census, code_index.purge	—	code summaries with vectors, rebuilt by code_summary at model cost; named in server/code/store/index/cards.py, server/code/store/index/graph_stats.py
code_backport_gaps	RMT(version) (org, code_namespace, branch_namespace, pr_number)	stays	Derived	no content	—	backport verdicts per release branch, rebuilt by code_backport_gaps from the pull request ledger and tracker reads over the last code_backport_gaps_days; named in server/code/store/backport_gaps.py
code_coverage_files	RMT(version) (org, namespace, path)	stays	Derived	step code_index.purge	—	executed line ranges per file, rebuilt by code_coverage_import from the CI source's newest run (server/code/app/index/coverage_import.py); named in server/code/store/index/coverage.py
code_symbol_stats	RMT(version) (org, namespace, symbol_id)	stays	Derived	step code_index.purge	—	fan-in and graph statistics per symbol, rebuilt by code_graph_stats from the live edges; named in server/code/store/index/graph_stats.py
code_test_map	RMT(version) (org, namespace, src_path, method, test_path)	stays	Derived	step code_index.purge	—	test-to-file links, rebuilt by code_crossref, code_graph_stats and code_coverage_import; named in server/code/store/index/coverage.py, server/code/store/index/facets.py
code_unit_risk	RMT(version) (org, namespace, unit_id)	stays	Derived	step code_index.purge	—	risk per code unit, rebuilt by code_ingest's pass commit and code_graph_stats; named in server/code/store/index/graph_stats.py, server/code/app/unit_risk.py
code_chunks_prefix	RMT(version) (org, namespace, embedding_model, path, chunk_id)	stays	Derived	step code_index.purge	—	the code_chunks vectors truncated to a prefix, written with each chunk insert and refilled from code_chunks by loom code vector-twin without a provider call (server/code/domain/index/prefix_twin.py); named in server/code/app/index/compaction.py, server/insights/app/provider_coordinator.py
code_review_briefs	RMT(version) (org, namespace, pr_number, merge_sha)	stays	Derived	step code_index.purge	—	review briefs, built by code_ingest for each merge it passes and on a miss by code.review_brief (server/code/app/review_brief.py); named in server/code/store/index/reviews.py
code_edges	RMT(version) (org, namespace, tier, to_symbol_id, edge_kind, from_symbol_id, path, line)	stays	Derived	step code_index.purge	—	code edges: the treesitter tier rebuilt from the repository by loom code backfill (by code_ingest's first-index slices for a self-service entry), the scip tier by code_scip_full, code_scip_delta and the code_scip_{ts,py,go}_delta workers, and, for a repository whose SCIP comes only from uploads, only by a new upload code_scip_apply applies; named in server/code/store/index/edges.py, server/code/store/index/graph_stats.py
code_symbols	RMT(version) (org, namespace, tier, qualified_name, symbol_id)	stays	Derived	step code_index.purge	—	code symbols: the treesitter tier rebuilt from the repository by loom code backfill (by code_ingest's first-index slices for a self-service entry), the scip tier by code_scip_full, code_scip_delta and the code_scip_{ts,py,go}_delta workers, and, for a repository whose SCIP comes only from uploads, only by a new upload code_scip_apply applies; named in server/code/store/index/symbols.py, server/code/store/index/graph_stats.py
code_context_packs	RMT(version) (org, namespace, pr_number)	stays	Derived (SoR for packs of merges before the git_head watermark)	step code_index.purge	—	context packs, built by code_ingest for each merge it walks past the git_head watermark; nothing rebuilds a pack of an earlier merge, so those are held only here; named in server/code/store/index/reviews.py, server/code/store/index/status.py
code_call_sites_unresolved	RMT(version) (org, namespace, callee_name, path, line, from_symbol_id)	stays	Derived	step code_index.purge	—	code index projection, rebuilt from the repository by loom code backfill (by code_ingest's first-index slices for a self-service entry); named in server/code/store/index/edges.py
code_caller_guards	RMT(version) (org, namespace, from_symbol_id, path)	stays	Derived	step code_index.purge	—	code index projection, rebuilt from the repository by loom code backfill (by code_ingest's first-index slices for a self-service entry); named in server/code/store/index/edges.py
code_files	RMT(version) (org, namespace, path)	stays	Derived	step code_index.purge	—	code index projection, rebuilt from the repository by loom code backfill (by code_ingest's first-index slices for a self-service entry); named in server/code/store/index/files.py, server/code/store/index/answers.py
code_test_facets	RMT(version) (org, namespace, path, facet_kind, name, ordinal)	stays	Derived	step code_index.purge	—	code index projection, rebuilt from the repository by loom code backfill (by code_ingest's first-index slices for a self-service entry); named in server/code/store/index/facets.py

Tracker

Table	Today	Target	Kind	Erasure	Wave	Why, and code that names it
qa_sources	RMT(version) (org, name)	Postgres qa_sources	SoR	no content	3	registered sources with sealed credentials; named in server/tracker/app/registry_sources.py, server/identity/store/namespace_census.py
qa_item_embeddings	RMT(version) (org, namespace, kind, number)	stays	Derived	no content	—	issue and PR vectors, rebuilt by tracker_embed at embedding cost; named in server/tracker/store/embed.py, server/core/vectors/placeholders.py
qa_link_candidates	RMT(version) (org, qa_namespace, issue_number, pr_number)	stays	Derived	no content	—	issue-PR link candidates, rebuilt by tracker_links from the tracker items at embedding cost; named in server/tracker/store/links.py, server/tracker/workers/links.py
qa_test_daily	RMT(version) (org, qa_namespace, test_name, day)	stays	Derived	no content	—	daily test rollups, rebuilt by tracker_test_daily from the check runs; named in server/tracker/store/daily.py, server/tracker/workers/test_daily.py
qa_rollup_days	RMT(version) (org, qa_namespace, day)	stays	Derived	no content	—	rollup progress per day of tracker_test_daily: a build ledger kept beside the rollups it describes; named in server/tracker/store/daily.py, server/tracker/workers/test_daily.py
qa_fail_hours	RMT(version) (org, qa_namespace, hour)	stays	Derived	no content	—	failing hours per test, rebuilt by tracker_test_daily; named in server/tracker/store/daily.py

Runs, tasks and leases

Table	Today	Target	Kind	Erasure	Wave	Why, and code that names it
tasks	RMT(version) (org, inbox, task_id)	Postgres tasks	SoR	census, tasks.delete	2	a task's status, owner, fence and lease change in place; a claim is UPDATE … WHERE fence = $f; named in server/tasks/store/repo.py, server/erasure/store/steps_ledgers.py
task_leases	KeeperMap task_id	merge into Postgres tasks (owner, fence, lease_until)	Coord	no content	2	a KeeperMap copy of columns the task row already has; named in server/tasks/store/repo.py
task_claims	MT (task_id, fence, claim_ts, claim_id)	delete	Coord	no content	2	no code path writes it (its census entry says so); no module outside the census names the table
task_inboxes	RMT(version) (org, inbox)	Postgres task_inboxes	SoR	no content	2	inbox registry, read by key on every task op; named in server/tasks/store/repo.py, server/identity/store/namespace_census.py
task_poster_policies	RMT(version) (org, inbox, poster, kind)	Postgres task_poster_policies	SoR	no content	2	who may post what, read on every task.create; named in server/tasks/store/repo.py, server/auth/self_signup.py
task_events	MT (org, inbox, seq), by toYYYYMM(ts)	stays	Log	census, tasks.delete	—	an inbox's append-only event stream; watchers page by its microsecond seq stamp, a wire format, so it stays a log; wave 2 adds event_id; named in server/code/store/coverage_watches.py, server/tasks/store/repo.py
task_webhook_cursor	RMT(version) (org, inbox)	merge into Postgres cursors	SoR	no content	2	a delivery cursor per inbox (consumer task_webhook, scope the inbox), copied at the flip so no webhook is replayed or skipped; named in server/tasks/store/repo.py
agent_runs	RMT(version) (org, namespace, run_id)	Postgres agent_runs + ClickHouse projection	SoR (head)	census, agent_runs.delete, user scope too	2	a run's lifecycle with a unique (org, namespace, run_id); the projection serves the scans of the mentor, proposals and decisions, and keeps its nanosecond version (deliveries); named in server/tasks/store/runs.py, server/insights/store/mentor.py
agent_run_claims	KeeperMap claim_key	delete; the primary key of agent_runs	Coord	step agent_runs.delete	2	a KeeperMap claim imitating a unique constraint; named in server/tasks/store/runs.py, server/erasure/store/steps_ledgers.py
agent_turn_usage	RMT(version) (org, namespace, session_id, message_id), by toYYYYMM(turn_at)	stays	Log	no content	—	token usage per turn; named in server/insights/store/cost.py, server/tasks/store/runs.py
session_outcomes_by_user	RMT(version) (org, namespace, agent, user_id, session_id)	stays	Derived (SoR for client labels)	step session_outcomes.delete	—	per-session outcome labels: judged labels, rebuilt at model cost from session_summaries (server/memory/app/session_outcomes.py), and the client labels run.complete writes, held only here; named in server/memory/store/session_outcomes.py, server/erasure/store/steps_memory.py
procedure_runs	MT (org, namespace, memory_key, ts), by toYYYYMM(ts)	stays	Log	step procedure_runs.delete	—	procedure executions, append-only; named in server/memory/app/procedure_ledger.py, server/memory/store/lifecycle.py

Erasure and idempotency

Table	Today	Target	Kind	Erasure	Wave	Why, and code that names it
erasure_requests	RMT(version) id, TTL	stays	SoR	no content	—	ClickHouse's erasure record, saga checkpoints as version rows; Postgres keeps its own record in erasure_ledger, so after a restore of either store loom reconcile runs again the erasures the other records past the point (restores); moving it would put both records in one store; named in server/erasure/store/ledger.py, server/identity/store/namespace_census.py
erasure_reversals	RMT(version) (org, erasure_id, memory_id), TTL	stays	Log	no content	—	evidence of restored rows, append-only with a 5-year TTL; read only joined with erasure_requests, so it stays beside it; named in server/memory/app/restore.py, server/erasure/store/ledger.py
idempotency	RMT(created_at) idempotency_key, TTL	delete; retires with the ClickHouse-authority path	SoR	census, idempotency.delete, user scope too	before wave 1	the Idempotency-Key records of the ClickHouse authority; under Postgres every idempotent write op records in Postgres idempotency already (server/transport/gates.py); kept while N-1's erasure deletes from it, it retires by rolling-upgrades rule 8; named in server/core/idempotency.py, server/erasure/store/steps_ledgers.py
usage_acted_credits	RMT(credited_at) pair_sha256, TTL	stays	Derived	no content	—	which acted usage events the fold has emitted into memory_usage_events, so no pair is credited twice; wave 4 rebuilds it with an org column and puts the pair digest on each acted event, so it is rebuilt from memory_usage_events; named in server/memory/workers/usage_fold.py

Telemetry, audit and usage

Table	Today	Target	Kind	Erasure	Wave	Why, and code that names it
events	MT (org, namespace, kind, ts), by toYYYYMM(ts), TTL	stays	Log	census, events.delete, user scope too	—	the event stream, 30-day TTL; named in server/observability/events_stream.py, server/insights/store/admin.py
agent_events	MT (org, namespace, agent, ts), by toYYYYMM(ts)	stays	Log	step agent_events.scrub	—	the op ledger, monthly partitions; named in server/observability/events.py, server/assistant/store/ledger.py
audit_log	MT (org, namespace, event_type, ts), by toYYYYMM(ts)	stays	Log	no content	—	hash-only audit rows, kept unless an operator's retention (retention_reconcile) expires them; named in server/memory/store/pg_publish.py, server/observability/audit.py
audit_checkpoints	MT window_start	stays	Log	no content	—	verifier checkpoints over windows of audit_log across every org; named in server/insights/store/audit.py, server/observability/otel_probes.py
search_trace_log	MT (org, namespace, ts), by toYYYYMM(ts), TTL	stays	Log	census, search_trace_log.delete	—	search traces, 180-day TTL; named in server/insights/store/deploy_delta.py, server/tasks/store/runs.py
policy_decisions	MT (org, namespace, ts), by toYYYYMM(ts), TTL	stays	Log	no content	—	policy gate decisions, 90-day TTL; named in server/identity/store/policy.py
memory_exposures	MT (org, namespace, actor, ts), by toYYYYMM(ts), TTL	stays	Log	census, memory_exposures.delete, user scope too	—	which actor was shown which memory, 180-day TTL; named in server/identity/domain/policy_rules.py, server/memory/app/exposure_ledger.py
memory_exposure_breadth_hourly	AMT (org, namespace, actor, agent, hour), by toYYYYMM(hour), TTL	stays	Derived	no content	—	hourly uniq rollup of exposures; named in server/memory/app/exposure_ledger.py
memory_usage_events	MT (org, namespace, memory_id, ts), by toYYYYMM(ts)	stays	Log	no content	—	usage events per memory; wave 4 puts the pair digest on each acted event; named in server/memory/store/usage.py, server/cli/import_clickgap.py
memory_usage_totals	SMT (org, namespace, memory_id)	stays	Derived	no content	—	SummingMergeTree totals per memory fed by memory_usage_totals_mv from memory_usage_events; named in server/memory/store/reads.py, server/memory/workers/usage_fold.py
memory_served_daily	SMT (org, namespace, memory_id, agent, day), by toYYYYMM(day)	stays	Log	no content	—	served counts per memory, agent and day, inserted directly by the agent read ops (server/memory/app/served_reads.py) and summed by SummingMergeTree; a log on SummingMergeTree, which keeps its layout; named in server/memory/store/served.py, server/memory/app/health.py
agent_cost_rollup_daily	AMT (org, namespace, agent, day, operation), by toYYYYMM(day)	stays	Derived, SoR past its source's retention	no content	—	daily cost rollup from agent_events; once an operator's retention expires the source, the only copy; named in server/insights/store/cost.py
namespace_snapshots	MT (org, source_namespace, created_at), by org	stays	Log	no content	—	who took each snapshot and when; the payload goes to the caller and is not stored; named in server/identity/app/namespace_snapshot.py
loom_telemetry.sli_minute	AMT (slo_class, minute, operation, transport, traffic_class, org), by toYYYYMM(ingested_hwm), TTL	stays	Derived, SoR past its source's retention	no content	—	SLI rollup in loom_telemetry, refolded from agent_events by sli_minute_refresh; 400-day TTL, so past the source's retention the only copy; named in server/observability/otel_probes.py
embedding_cache	RMT(created_at) (model, key), TTL	stays	Cache	step idempotency.delete	—	embedder answers by model and key, 30-day TTL; named in server/embedding/_cache.py, server/erasure/store/mutations.py
llm_response_cache	RMT(created_at) (model, key), TTL	stays	Cache	census, llm_response_cache.delete, user scope too	—	model answers by key, 7-day TTL; named in server/memory/store/redaction.py, server/erasure/store/steps_graph.py

Tables outside schemas/schema.sql (names that start with _)

Table	Today	Target	Kind	Erasure	Wave	Why, and code that names it
_migrations	RMT(applied_at) filename	stays	SoR	outside the census (not in schemas/schema.sql); no content	—	the ClickHouse migration ledger; named in server/core/schema/migrate.py, server/core/rmt_carry.py
_migration_lease	RMT(version) (name, holder), TTL	delete	Coord	outside the census (not in schemas/schema.sql); no content	2	a lease table the migrations create and no module names; no build reads it, so its wave drops it
_migration_lock	KeeperMap name	Postgres leases (its lease keys) and its claim keys where leases places claims, with their callers' waves; ClickHouse (the probe and nonce keys)	Coord	outside the census (not in schemas/schema.sql); no content	1, 2, 3	KeeperMap keys that leases and claims write in the table the migrator creates, with no fencing token; the probe and nonce keys the loom cell commands write stay (how a table moves); named in server/core/leases.py, server/insights/store/provider_lock.py
_provider_participants	RMT(version) participant_id	merge into Postgres instances	Coord	outside the census (not in schemas/schema.sql); no content	1	a second process registry with heartbeats, host proofs and transition acknowledgements that provider rebuilds drain on (server/insights/store/provider_participants.py, server/insights/app/provider_barrier.py); one registry holds every process; named in server/insights/store/provider_participants.py

The Postgres tables

Every table here is in the cell's Postgres. Today's tables (storage authority) keep their keys; the target adds the tables the ClickHouse tables section moves. Every tenant table leads its primary key with org. The router's directory is placed by cells. Where a row names a step today's ClickHouse census owns, the pg: census row is owned by the Postgres step the flip splits off it, which joins PG_ONLY_STEPS (restores).

Table	Key	Kind	Erasure
authority	storage authority; paired with ClickHouse's storage_authority	SoR	no content
pg_migrations	the migration file	SoR	no content
scope_generations	storage authority	SoR (a namespace's row allocates its revision seq)	no content
cache_generations	(org, kind)	Coord	no content
memory_current	storage authority	SoR	census pg:loom.memory_current
memory_revisions	storage authority	SoR	census pg:loom.memory_revisions
memory_deliveries	storage authority	SoR	cascades with its revision
idempotency, idempotency_fences	storage authority	SoR	census pg:loom.idempotency; idempotency_fences holds no content (the census's bound)
erased_memories, erased_documents	storage authority	SoR	evidence, no content
audit_outbox	storage authority	SoR	keys and ids, no values
document_catalog, document_uploaders	(org, namespace, sha256), (org, user_id, namespace, sha256)	SoR	a pg: census row each, owned by the Postgres step the flip splits off the steps that own the census rows of document_blobs and blob_uploads today, which joins PG_ONLY_STEPS (documents.tombstone, and the user step through document_uploaders)
knowledge_datasets	storage authority	SoR	census pg:loom.knowledge_datasets, step pg_knowledge.delete
knowledge_jobs	storage authority	SoR	census pg:loom.knowledge_jobs, step pg_knowledge.delete
knowledge_heads, knowledge_deliveries	storage authority	SoR	census pg:loom.knowledge_heads, pg:loom.knowledge_deliveries
jobs, job_users, job_memories	(org, id), (org, user_id, job_id), (org, namespace, memory_id, job_id)	SoR	a census row, a step by namespace and user, a redaction step
leases, write_marks, graph_inflight	(org, name), (org, namespace, surface), (org, namespace, token)	Coord	no content
cursors	(org, consumer, scope)	SoR	no content
carry_acks	process	Coord	no content
analysis_secrets	id	SoR	no content
sql_access	(org, namespace, user_name)	SoR	no content
cell_self	singleton	SoR until attach writes its fingerprint to the directory row, then Derived (rewritten from the directory on a restore, cells)	no content
tenancy	org	SoR until attach places its org (the first cell's recording rows), then Derived (written back from the directory's placements on a restore, never deleted, cells)	no content; an erasure leaves the org's row
tenancy_mode	singleton	Derived (the mode raised only upward from the directory's recorded mode on a restore, cells)	no content
cell_first	singleton	Derived (written when absent and refused when it differs on a restore; written once, its update, delete and truncate refused by triggers, cells)	no content
cell_probe (the probe nonce the loom cell commands write, cells)	name	Coord	no content; deleted once the probe has read it
job_parts	(org, job_id, seq)	SoR	cascades with its job; code_index.purge reaches it through the job
idempotency_revisions	(org, key_hash, namespace, seq)	SoR	cascades with its record; redaction reaches replay answers through it
revision_relations	(org, namespace, seq, ord)	SoR	census pg:loom.revision_relations, step pg_memory.delete; cascades with its revision; redaction reaches it through its revision
revision_sources	(org, user_id, namespace, seq)	SoR	cascades with its revision; the user step deletes the user's rows; the user step and the census reach shared revisions through it
table_moves, move_acks	move, (move, process)	SoR, Coord	no content; table_moves also holds the registry of kept tables (each kept table's name and scope predicates)
backfill_steps	the column, index or check it sets	SoR	no content; the backfill step's completion record, which boots read (schema rules)
working_memory, records, operator_edges, context_offloads, session_briefs, session_brief_revisions, namespace_guides, learning_candidates, learning_supporters, conflict_allowlist	led by (org, namespace, …), the ClickHouse sort key today	SoR	a census row and a saga step each
conversation_states	(org, namespace, agent, thread_id, msg_id)	SoR	a census row and the conversations.tombstone step, by namespace and by the saver's user_id
offload_bytes	(org, namespace, offload_id, part), its head's id and a part number	SoR	deleted with its head by the context_offloads step, and counted by its census row
shared_current	(org, namespace, memory_key)	SoR	a pg: census row and the provenance step shared has today, through revision_sources
tasks, agent_runs	(org, inbox, task_id), (org, namespace, run_id)	SoR	a pg: census row each, owned by the Postgres step the flip splits off the step that owns its ClickHouse table's census row today, which joins PG_ONLY_STEPS (tasks in the namespace scope, agent_runs in both)
task_inboxes, task_poster_policies	(org, inbox), (org, inbox, poster, kind)	SoR	no content
erased_messages	(org, erasure_id, namespace, agent, thread_id, msg_id)	SoR	evidence, no content; the messages a conversation erasure matched by their saver, which its ClickHouse replay reads, swept with its erasure_ledger row
erasure_ledger	storage authority	SoR	evidence, no content; Postgres's erasure record, which loom reconcile runs again after a ClickHouse restore, swept after erasure_ledger_days
record_schemas, namespace_visits, extraction_policies, namespace_policies, namespace_types, namespace_ontology, profile_schemas, user_state, webhooks, knowledge_sources, code_repos, qa_sources	led by org, as the ClickHouse key	SoR	no memory content
knowledge_uploads	led by org, as the ClickHouse key	SoR	a pg: census row owned by the Postgres step split off documents.tombstone (restores)
code_coverage_watches	led by org, as the ClickHouse key	SoR	no memory content; reached by the Postgres part of code_index.purge (restores)
revoked_tokens	(kind, key), kind subject_or_jti (the bare key, which holds both today), cutoff or scim	SoR	no content
signing_keys, service_accounts, machine_identities, scim_users, sso_links, principal_groups, oauth_state, oauth_signin_state, namespace_grants, namespaces, collections, blacklist, org_model_calls	as the ClickHouse key, org first where the table is per org, except the tables of the cell that keys lead with org names	SoR	no memory content; the identity steps (grants.revoke, groups.remove_member, personal.retire, collections.forget_owner) rewrite namespace_grants, principal_groups, namespaces and collections
auth_attempts	kind, hashed key and window	Coord	no content
deployment_config, runtime_overrides, provider_configuration, assistant_service	as the ClickHouse key	SoR	no content
instances	process	Coord	no content
embedder_calibration, namespace_calibration	as the ClickHouse key, org first for namespace_calibration	Derived (rebuilt by a recalibration, at embedding cost)	no content

A sweep deletes a revoked_tokens row ten days past its stored expires_at, as the ClickHouse TTL does today; a token without exp, an operator cutoff and every SCIM revocation are stored with the latest expiry the table holds (revocation_expiry in server/auth/revocation.py), so they never lapse. namespace_grants keys by (org, namespace, subject), with an index on (lower(subject), org) for a principal's reach across orgs, which SCIM deprovisioning reads case-folded (_orgs_beyond in server/auth/scim.py); the sign-up check reads it within one org (_refuse_empowered in server/auth/self_signup.py). Erasure deletes the org's replay answers in Postgres idempotency (its idempotency_revisions rows cascading) and sets the org's idempotency_fences row, as server/erasure/store/steps_pg.py does today: by org, since the table has no user column.

Waves

Each wave is one concern per pull request, ordered by correctness and latency payoff, and a table's wave is its row's Wave column; a wave moves together the tables its statements read together (a statement that spans the stores is rewritten, statements that cross the stores):

Wave	Tables	Why
1	the tables whose Wave column says 1, and with them the mover with leases, table_moves, move_acks, the process registry (with _provider_participants) and jobs; tokens, keys, identities, grants, namespaces, collections, configuration and secrets, the blacklist, the org model switch, with cache generations and the namespaces and sql_access mirrors; extraction and namespace policies; the keys of _migration_lock whose callers move in it	security reads that today depend on FINAL or a fail-closed cache
2	the tables whose Wave column says 2, and with them cursors, write_marks, graph_inflight; the queues, outboxes and webhook registrations, knowledge uploads and ingestion cancels, KeeperMap leases and claims, the keys of _migration_lock whose callers move in it and those of every caller whose tables stay in ClickHouse, carry_acks; tasks and runs; in place, the knowledge_ingestions rebuild and task_events.event_id	correctness that today rests on read-back claims and keys outside a transaction
3	the tables whose Wave column says 3, and with them operator_edges, context_offloads, conflict_allowlist, revision_relations, the graph validity rows, and user-written state beyond memory heads: shared, working memory, records, briefs, guides, learnings, visits, conversation states, registrations; the keys of _migration_lock whose callers move in it; in place, version rows for the conversation tables' mutations, and the rebuilds a redaction owes the derived tables	read-your-writes for state users write and read by key
4	the tables whose Wave column says 4, the merges inside ClickHouse: term_df, doc_lengths, row links; in place, the code_term_df feed and the row-link backfill, the org rebuild of usage_acted_credits with the pair digest on memory_usage_events, then the vector levers	bytes and parts, proven ranking-neutral

How a table moves

Tables move once every deployment records the Postgres memory authority and the ClickHouse-authority path is deleted: a deployment that still holds memory under ClickHouse moves first with the authority migration command. The layout depends on it: memory is derived from memory_current, shared and records join the journal, and the code-link worker reads memory_revisions.

A move is one table, or a group that flips together: the tables one statement reads in one store, and the tables a cache reads with the counter that invalidates it (namespace_grants, namespaces, principal_groups and collections with acl_versions; blacklist with blacklist_version; revoked_tokens with oauth_state, whose revocation_owed rows the revocation cache also reads). No table's rows are split across the stores, the exceptions being the tables that split by role (requirement 4) and _migration_lock (below). Build N carries both paths for each table it moves and picks one by the move's recorded authority in table_moves; N-1 knows only ClickHouse. One mover, shared with the authority migration command, runs every move. The first wave's pull request designs it, and is reviewed on these requirements:

1. One authority at a time. Until a table's flip, every build reads, decides, claims and writes it on ClickHouse exactly as today, and no reader serves a Postgres copy. The coordination tables requirement 3 cuts over are the exception. A table that can hold memory content is copied inside its flip, once every writer and saga step on it has stopped, or pre-copied under requirement 7's coverage. A content-free table may be pre-copied and reconciled at the flip.
2. The flip is the operator's, after the window. It starts once no process older than N runs, and is a point past which no rollback below N serves, as switching the memory authority is (rolling-upgrades rule 33). It runs as a jobs row of a kind that serializes on one cell-wide key (jobs), so two flips never overlap; its Postgres steps lock the job row with its token, and its ClickHouse steps, bounded by the lease as every effect outside Postgres is, are idempotent and re-applied from the record (requirement 8), so a late step of a flipper that paused past its lease is overwritten.
3. Every writer stops before the copy. Every process that can write the table, CLIs included, either acknowledges the flip or re-reads the record before each write. A process whose registration lapsed stops writing a table with a pending move. The flip copies only after every acknowledgement, and once no statement or pending asynchronous insert touches the table on any replica. Coordination tables that refill on their own are the exception:
   • instances, cell_heartbeats, keyring_readers, _provider_participants, carry_acks and auth_attempts are cut over without a copy or a flip, the process registry first, before the mover's first flip relies on it;
   • while N-1 runs, N writes both stores and reads ClickHouse, which holds every build's rows, so a limit keeps its bound across the window; N reads Postgres alone only a full sliding window after the last N-1 write;
   • the rebuild barrier reads ClickHouse while N-1 runs, and _provider_participants cuts over with no provider transition in flight.
4. Every stale access fails closed. The table's name becomes a view that refuses every read and write, swapped in atomically so the name is never absent, and no later ClickHouse migration touches it. A table that splits into a Postgres head and a ClickHouse projection (shared, memory_records, agent_runs, operator_edges, the namespaces, code_repos and sql_access mirrors) keeps its name, and a mirror that leaves a column behind (the sealed credentials of code_repos) is rebuilt without it and swapped in whole at the flip; a write to it by a process older than N after the flip is the rollback the flip forbids. A table that splits by role keeps its name for the role that stays (search pages in context_blobs, the bytes in document_blobs where no bucket is configured, the messages in conversations, the code-index ledger rows in code_index_state), and the moved role is refused by a guard the flip adds: an insert constraint (no offload row, no catalog column change, no knowledge_source: key in code_index_state) and, for state written in place today, the revoke of ALTER UPDATE on that table from Loom's ClickHouse user, so a stale write of it fails.
5. One commit decides. The copy, its verify against the source key by key (inserts, updates, and deletes of owned rows the source no longer holds) and the record commit in one Postgres transaction. Before it, any failure or a flip past its deadline rolls back: the name, the views and the record return to ClickHouse, and the move's rows leave Postgres. After it:
   • the old table is kept renamed until it may be dropped (restores), listed in one registry of kept tables that the census, erasure, redaction and recovery read;
   • the flip classifies the table's saga steps by store (restores);
   • each registry row carries the table's own scope predicates, reviewed with its move, which, with the mover (requirement 8), is the bounded exception to "no code for the old home" until the table is dropped.
6. The flip deletes only rows its move owns. A target other writers share (jobs, cursors, write_marks) is written only through the move's owned rows.
7. The window fits the caches and an availability budget. A content table either flips within an availability budget its wave states and measures, or is pre-copied with the census, erasure and redaction covering both copies until the flip, so only the delta is copied inside it. Every access to the table waits or fails while the flip runs, so a request-path table flips within its caches' bound (grant_cache_ttl_seconds, acl_watermark_max_stale_seconds), and each wave's pull request measures its flips.
8. The record survives a restore. Each move's record is paired with a marker in the source ClickHouse, as storage_authority pairs with authority, and the record is authoritative. The marker is written as pending before the flip's commit. On a mismatch (a crash between the commit and the ClickHouse steps, a ClickHouse restore to before the flip), the mover finishes or re-applies the flip from the record (marker, refusing view and kept-table rename) before a process refuses to boot. A Postgres restore to before a flip re-runs that flip from the kept copy (copy, verify, commit); N+1 and later builds keep the mover with the registry until the kept table is dropped. The restored record is never re-applied back to ClickHouse (restores).
9. N+1 depends on every flip. N+1 refuses to boot while a move N carries has not flipped, and carries no code for the old home but the kept-table registry (requirement 5) and the mover, which the census, erasure and recovery read until each kept table is dropped. A fresh install records every move as flipped and has the refusing views, the shape of an upgraded deployment.

loom._migration_lock holds keys of callers in more than one wave, so its keys move with their callers' waves; the keys of callers whose tables stay in ClickHouse move in wave 2. Each key moves at a flip of its own move, after the window (requirement 2), and until then every build, N included, takes it in KeeperMap. A moved key is taken in Postgres alone and the table refuses it by name. The last move retires run_pass in server/workers/keeper_sweep.py. The probe and nonce keys the loom cell commands write (cells) prove the ClickHouse service is the cell's own and live, so they stay: _migration_lock keeps those reserved keys alone, still KeeperMap and still checked at boot (require_keeper in server/boot.py) and by loom doctor. idempotency does not move: under the Postgres authority every idempotent write op records in Postgres already (server/transport/gates.py), identity and admin ops included. A table that is deleted rather than moved while a build still reads or feeds it (memory_code_link_queue, whose view fills it until N+1) retires by rolling-upgrades rule 8: N stops using it, keeping what feeds it while N-1 may read it, and a later release drops it. A wave-4 merge inside ClickHouse is a backfill of the merged table, then the same retirement of the old ones.

Restores

The backup and restore work designs both restores, with the restore drill. This layout gives it the kind and backup of every table (above), and keeps each move's old table, renamed and reached by the census, erasure and redaction, until the oldest restorable Postgres backup postdates the move's flip, so a Postgres restore to before a flip can recover the table from it. A restore of either store must keep these promises for every row written before its recovery point, and re-apply every narrowing change and erasure committed after it:

• No erased row comes back, and no delete its writer committed is undone (a bucket object an erasure removed included: the bucket is part of the cell's backup, and a document catalog and its bytes are reconciled after either is restored).
• No change that narrows who can authenticate, what they reach, or where an org's data may go is undone: an org's model calls switched off, an extraction policy tightened, a revocation, a revoke, a group removal, a signing-key retirement or rotation, a service account or machine identity removed, a blacklist addition, an SQL-access revoke, a namespace restricted, deactivated or opted out. The SQL surface and the scans that join a mirror stay closed after a ClickHouse restore until the mirrors are republished.
• Every projection of a Postgres head that is not journaled (conversation_states, operator_edges, agent_runs, namespaces, code_repos, sql_access, and knowledge_ingestions from knowledge_jobs) is rebuilt whole from its heads after a restore of either store, so a projection row of a head the restore lost is not served; every read that uses one as a delete or access filter (the SQL surface views, live conversation reads, graph joins) fails closed until that rebuild is done. The rebuild of knowledge_ingestions keeps only the jobs Postgres cannot hold (keys it cannot store, or a row a build that predates knowledge_jobs wrote), a job whose knowledge_jobs row the restore lost leaving with it; datasets is the named exception its row describes.
• No credential issued before the recovery point and spent before the restore (a sign-in mark, a code, a device grant, a rotated refresh token) becomes spendable again: a Postgres restore invalidates every single-use credential issued under an older restore generation, while OAuth client registrations, and the signing keys still active in the newest state the restore can re-apply, stay valid.
• Nothing its writer did not ask to delete goes, and writes after the point that a store does not replay are reported as lost.
• Since a ClickHouse backup is not consistent across tables, each build ledger is set by the ledger rule and each view-fed Derived table is reconciled or rebuilt from its source, except a table that is SoR past its source's retention, which is only refolded over the source's retained window, never truncated or rebuilt whole.

Erasures already keep these promises: each store keeps its own erasure record (erasure_requests in ClickHouse, erasure_ledger in Postgres), and loom reconcile runs again in a restored store the erasures and redactions the other records past the point (backup and recovery); no move puts both records in one store. That holds for a moved table only once its saga steps are classified by store. The wave that moves a table:

• splits each saga step that touches it into a Postgres step and a ClickHouse step at the flip; a step over tables of both stores, such as curation.tombstone or records.tombstone, becomes one of each;
• puts the kept old table's delete in the ClickHouse step, never in a PG_ONLY_STEPS step, so the replay after a ClickHouse restore reaches it;
• adds the Postgres step to PG_ONLY_STEPS (server/erasure/app/saga_steps.py, and so to the replay's Postgres steps, server/erasure/app/replay.py), recording itself in erasure_ledger.pg_steps in its own transaction;
• makes redaction replay after a Postgres restore scrub every Postgres copy of a version's text the layout adds;
• adds its moved steps to the replay tests of both restore directions.

Which record a restore re-applies revocations and the other narrowing changes from, how that record is written durably before the change answers, and how long it is kept, are the backup and restore work's design, reviewed on these promises. They bind that work: until it ships, a cell's restore has a single deployment's limits (cells): keys, revocations, sessions, bots and SCIM rows come back as the backup holds them.

A table that leaves ClickHouse also leaves any step that finds its tables at run time: code_index.purge finds the code tables by name in system.columns (code_tables_keyed_by_namespace in server/code/app/index/retire.py; it leaves code_repos out today, a registration retired by active = false, and keeps doing so), so the waves that move code_coverage_watches and code_scip_batches give it a Postgres part, and the wave that moves code_scip_parts has it delete each purged job's bucket objects before the job row, as the context_offloads step does, and wave 4 gives it the corpus = 'code' rows of term_df and doc_lengths.

Each wave's pull request names its tables, rewrites the statements that read them, runs the offline ranking-neutral check where search is touched (bench/retrieval_determinism.py --save/--against), puts a small local before-and-after timing in its body, and updates this page's rows and the rolling-upgrades rules it changes (rules 6 and 9 name ClickHouse leases and ClickHouse tables for replica-wide state).

Objections, answered

"Two stores per request doubles the failure surface." The security reads move from a ClickHouse FINAL read behind a cache to a Postgres primary-key read behind the same cache; the request depended on a store for them either way. Once Postgres is required, every deployment runs it for memory, so the set of stores a deployment depends on does not grow.

"The read path gets slower." Point reads by key get faster: a Postgres primary-key lookup does not merge parts at read time. The search legs keep reading ClickHouse: vectors, terms, chunks, the graph and the record projection stay there, and the search path's Postgres reads are the ones it makes today (the barrier and validation) plus cached point reads after a cache miss (namespace_calibration), and, from wave 2, the read-your-writes check on write_marks and the graph walk's poll of graph_inflight, which move from KeeperMap keys to Postgres point reads. The wave-4 merges touch the lexical leg's statistics and ship only when bench/retrieval_determinism.py shows identical keys and order, and scores within the tolerance the first wave that uses it adds.

"A grant change no longer fences memory writes." The change bumps the policy scope in its own commit, in the database where memory commits hold that scope, so verdicts judged under the old policy start over as they do today; authorization is bounded by the grant cache's invalidation, as it is today (the policy fence).

"Moving a table is a migration risk per table." One mover serves every move, the authority flips in one Postgres commit per move, and until it does no request path reads Postgres; only the census, erasure and redaction of a pre-copy do, and the self-refilling coordination tables of requirement 3. A move that fails its verify rolls back, and the table stays on ClickHouse.

"Rolling upgrades break." Builds N-1 and N both treat ClickHouse as the authority until no N-1 serves; the flip happens among N's processes only, after every one has acknowledged it, and the refusing view stops anything that missed it; N+1 boots only once every move has flipped. Nothing runs a compatibility path past N+1 but the kept-table registry and the mover (requirements 5 and 8), until each kept table is dropped.

"Erasure gets a second place to miss." A content table is copied inside the flip, or pre-copied with the census, erasure and redaction reaching both copies; the flip copies what ClickHouse holds once every process has stopped writing it, and a saga step that meets the flip retries; from the flip, the census and the saga cover the Postgres table, and a saga that cannot confirm a deletion fails closed as it does today.

"jsonb everywhere is a schema-less store." Columns that a predicate, index or guard reads are typed; jsonb holds only what the database never looks inside.

"One job table is a hot spot." Claims touch only due rows, skip locked rows, take orgs in turn, and finished rows leave the claim index at once. A cell where this shows hash-partitions jobs by org, which keeps every org-led key and index (the cross-org maintenance indexes become per-partition).

"Every cell repeats the deployment-wide rows." It does, by design: a cell's keys, revocations, global blacklist and settings are its own, loom cell each applies an org-free admin change in every cell until all succeed, and loom doctor names the cells whose settings differ (cells). A store shared by the cells would put a security check, a worker lease, an erasure, a policy generation and the audit chain across two databases, each needing a protocol of its own.

Invariants

• A table has one authority at a time, and every build reads and writes it there until its flip.
• Every table that can hold memory content is covered by erasure in each store that holds a copy, the kept old table of a move included.
• Every cache over a moved table fails closed past its bound.
• A restore keeps the promises of restores.

Source entry points

• schemas/schema.sql, schemas/pg_migrations/: the tables the rows place.
• server/erasure/store/census.py (_CENSUS, STEP_ONLY_TABLES, NO_MEMORY_CONTENT) and server/erasure/store/steps_pg.py (PG_CENSUS, PG_NO_MEMORY_CONTENT): the census each Erasure cell names.
• server/erasure/app/saga_steps.py (PG_ONLY_STEPS) and server/erasure/app/replay.py (PG_STEPS, replay): the saga steps classified by store.
• server/core/leases.py (take, cluster_lease) and server/core/pg_lease.py (check_lease, leased): today's leases and the delivery fencing.
• server/memory/store/pg_memory.py (capture, redact, redacted_since) and server/knowledge/store/pg_documents.py (commit, complete_own, claim_due): the memory and knowledge commits and their deliveries.
• tests/unit/test_storage_layout_rows.py: the checks this page cites, each named in its module docstring.

Related

• Cells: what a cell holds, the router and its directory.
• Storage authority: the memory heads, the journal, publication and erasure under Postgres.
• ClickHouse: engines, sort keys, vector storage and mutations.
• Rolling upgrades: the rules the move patterns follow.
• Memory architecture: the erasure saga and the residue census.