---
title: "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](/docs/pt-BR/architecture/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](#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](/docs/pt-BR/architecture/storage-authority#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](#vectors-are-the-cost-lever)). 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:

```sql
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](/docs/pt-BR/architecture/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](/docs/pt-BR/architecture/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](#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](/docs/pt-BR/architecture/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](#cursors).

### 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](/docs/pt-BR/architecture/storage-authority#datasets)).
- 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](/docs/pt-BR/operations/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](#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](#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

Mirrored source retirement requests use dedicated `knowledge_source_retire:<source id>`
keys under the mirror in `code_index_state`. These are durable queued work, rather than
rebuildable cursors: a replacement registry row must not lose its predecessor's deletion
request. They share the table's namespace erasure and backup; source sync never rewrites
these keys. Their worker's public resume cursor contains registration metadata only.

`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](#each-fact-has-one-owner): 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](/docs/pt-BR/architecture/storage-authority#rejected-layouts)): `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](/docs/pt-BR/development/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](/docs/pt-BR/development/migrations#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](#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](#merges-and-deletions)), 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](#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](/docs/pt-BR/development/migrations#changing-a-sort-key-or-a-partition-key)),
  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](#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](#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](#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](#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](#statements-that-cross-the-stores)). The migrator creates `loom._migration_lock`
itself, before any migration file runs; [leases](#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 grants including optional OIDC-verified email metadata (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 and optional verified sign-in email metadata: 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](#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](/docs/pt-BR/architecture/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/knowledge/store/connected_accounts.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](#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](#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](#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](#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](#merges-and-deletions)); 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](#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

`connected_accounts` is a registration keyed by `(org, subject, kind)`, stored in
ClickHouse under either memory authority and placed with `knowledge_sources` for wave 3.
It is source-of-record identity and credential state, with no retention TTL. Its optional
display email and long-lived credential are sealed under separate purposes. The private
canonical-owner lifecycle physically deletes historical rows and verifies the separate
canonical-owner census; namespace and end-user erasure do not select unrelated accounts.
Disconnect can retain one empty inactive owner marker, while canonical-owner deletion
counts and deletes that marker too. Keeper holds only opaque random generation fences;
it holds no provider identity or credential.

| 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`; materialized `access_keys` and its bloom index are coarse permission candidates erased with the row; 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](#deliveries)); 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`                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| `connected_accounts`        | RMT(version) `(org, subject, kind)`                                                      | Postgres `connected_accounts`                             | SoR                                                                                                                                      | canonical-owner census, `connected_accounts.erase` | 3    | canonical sign-in owner registrations with sealed display email and credentials; independent of namespace and end-user scopes; named in `server/knowledge/store/connected_accounts.py`, `server/erasure/store/census.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`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| `knowledge_source_push`     | RMT(version) `(org, namespace, source_id)`                                               | Postgres `knowledge_source_push`                          | SoR                                                                                                                                      | census, `knowledge_source_push.delete`             | 3    | sealed provider setup and channel bindings; callback hints use the existing source sync state; named in `server/knowledge/store/source_push.py`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| `knowledge_mirror_latch`    | KeeperMap `id`                                                                           | stays                                                     | SoR                                                                                                                                      | no content                                         | —    | one fixed deployment enablement timestamp, no TTL or tenant identity; absence requires a fresh ordered Keeper proof; named in `server/knowledge/store/mirror_latch.py`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| `knowledge_mirrors`         | RMT(version) `(org, namespace, mirror)`                                                  | Postgres `knowledge_mirrors`                              | SoR                                                                                                                                      | step `knowledge_mirrors.forget`                    | 3    | the `acl-` namespaces a namespace's mirrored sources' documents live in ([document sources](/docs/pt-BR/architecture/document-sources#data-model)), read by key; no memory content; named in `server/knowledge/store/mirrors.py`, `server/identity/store/namespace_census.py`                                                                                                                                                                                                                                                                                                                                                                                                       |
| `knowledge_source_listings` | RMT(version) `(org, namespace, source_id)`                                               | stays                                                     | Derived                                                                                                                                  | census, `knowledge_source_listings.delete`         | —    | when each mirrored source's last complete listing started, under its mirror's name; read beside the lists in `loom.documents`, so it stays in ClickHouse; no memory content; named in `server/knowledge/store/mirrors.py`                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| `knowledge_group_members`   | RMT(version) `(org, namespace, source_id, group)`                                        | stays                                                     | Derived                                                                                                                                  | census, `knowledge_group_members.delete`           | —    | source-scoped directory snapshots for mirror permissions; read beside listings under sequential consistency, including with Postgres heads; no memory content; named in `server/knowledge/store/source_access.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](#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](#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](#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](#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](#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](#write-marks)); named in `server/core/namespace_write_marks.py`, `server/identity/store/namespace_census.py`                                                                                                                                              |
| `query_history_sources`  | RMT(version) `(org, namespace, name)`                                                                  | Postgres registration                      | State             | step `query_history_sources.delete`                            | 3    | Opt-in connection metadata and sealed credentials, without query text; named in `server/knowledge/store/query_history.py`, `server/erasure/store/steps_ledgers.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 + SoR (source retirement requests)                        | 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>` sync-state rows and `knowledge_source_retire:<source id>` durable deletion requests move with `knowledge_sources` in wave 3 ([cursors](#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](#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`, user scope too      | —    | 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](#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](#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`                           | —    | ordinary op ledger, monthly partitions; named in `server/observability/events.py`                                                                                                                                                                                                     |
| `agent_events_private`           | MT `(org, namespace, agent, ts)`, by `toYYYYMM(ts)`                                                   | stays  | Log                                      | step `agent_events.scrub`                           | —    | marked requests and mirror-worker events, without analyst grants; named in `server/observability/events.py`                                                                                                                                                                           |
| `audit_log`                      | MT `(org, namespace, event_type, ts)`, by `toYYYYMM(ts)`                                              | stays  | Log                                      | no content                                          | —    | hash-only audit rows, kept unless the dedicated audit retention expires them; namespace retention does not expire audit rows; 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`                                                                                                                                                                            |
| `search_misses`                  | MT `(org, namespace, ts)`, by `toYYYYMM(ts)`, TTL                                                     | stays  | Log                                      | census, `search_misses.delete`, user scope too      | —    | searches that found nothing, normalised query and asker only, 16-day TTL; named in `server/knowledge/store/gaps.py`, `server/erasure/store/steps_ledgers.py`                                                                                                                          |
| `knowledge_gaps`                 | MT `(org, namespace, build, gap_id)`, TTL                                                             | stays  | Derived                                  | census, `knowledge_gaps.delete`                     | —    | the misses grouped daily by the `knowledge_gaps` worker, counts only, 7-day TTL; a user erasure's `search_misses.delete` step deletes the builds of the user's namespaces; named in `server/knowledge/store/gaps.py`, `server/erasure/store/steps_ledgers.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 ordinary and private event stores; 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](#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](#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](/docs/pt-BR/architecture/storage-authority#the-tables)) keep their keys; the target adds the tables
the [ClickHouse tables](#the-clickhouse-tables) section moves. Every tenant table leads
its primary key with `org`. The router's directory is placed by [cells](/docs/pt-BR/architecture/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](#restores)).

| Table                                                                                                                                                                                                                                         | Key                                                                                                                                       | Kind                                                                                                                                                                            | Erasure                                                                                                                                                                                                                                                      |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `authority`                                                                                                                                                                                                                                   | [storage authority](/docs/pt-BR/architecture/storage-authority#the-tables); paired with ClickHouse's `storage_authority`                                        | SoR                                                                                                                                                                             | no content                                                                                                                                                                                                                                                   |
| `pg_migrations`                                                                                                                                                                                                                               | the migration file                                                                                                                        | SoR                                                                                                                                                                             | no content                                                                                                                                                                                                                                                   |
| `scope_generations`                                                                                                                                                                                                                           | [storage authority](/docs/pt-BR/architecture/storage-authority#the-tables)                                                                                      | SoR (a namespace's row allocates its revision `seq`)                                                                                                                            | no content                                                                                                                                                                                                                                                   |
| `cache_generations`                                                                                                                                                                                                                           | `(org, kind)`                                                                                                                             | Coord                                                                                                                                                                           | no content                                                                                                                                                                                                                                                   |
| `memory_current`                                                                                                                                                                                                                              | [storage authority](/docs/pt-BR/architecture/storage-authority#the-tables)                                                                                      | SoR                                                                                                                                                                             | census `pg:loom.memory_current`                                                                                                                                                                                                                              |
| `memory_revisions`                                                                                                                                                                                                                            | [storage authority](/docs/pt-BR/architecture/storage-authority#the-tables)                                                                                      | SoR                                                                                                                                                                             | census `pg:loom.memory_revisions`                                                                                                                                                                                                                            |
| `memory_deliveries`                                                                                                                                                                                                                           | [storage authority](/docs/pt-BR/architecture/storage-authority#the-tables)                                                                                      | SoR                                                                                                                                                                             | cascades with its revision                                                                                                                                                                                                                                   |
| `idempotency`, `idempotency_fences`                                                                                                                                                                                                           | [storage authority](/docs/pt-BR/architecture/storage-authority#the-tables)                                                                                      | SoR                                                                                                                                                                             | census `pg:loom.idempotency`; `idempotency_fences` holds no content (the census's bound)                                                                                                                                                                     |
| `erased_memories`, `erased_documents`                                                                                                                                                                                                         | [storage authority](/docs/pt-BR/architecture/storage-authority#the-tables)                                                                                      | SoR                                                                                                                                                                             | evidence, no content                                                                                                                                                                                                                                         |
| `audit_outbox`                                                                                                                                                                                                                                | [storage authority](/docs/pt-BR/architecture/storage-authority#the-tables)                                                                                      | 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](/docs/pt-BR/architecture/storage-authority#the-tables)                                                                                      | SoR                                                                                                                                                                             | census `pg:loom.knowledge_datasets`, step `pg_knowledge.delete`                                                                                                                                                                                              |
| `knowledge_jobs`                                                                                                                                                                                                                              | [storage authority](/docs/pt-BR/architecture/storage-authority#the-tables)                                                                                      | SoR                                                                                                                                                                             | census `pg:loom.knowledge_jobs`, step `pg_knowledge.delete`                                                                                                                                                                                                  |
| `knowledge_heads`, `knowledge_deliveries`                                                                                                                                                                                                     | [storage authority](/docs/pt-BR/architecture/storage-authority#the-tables)                                                                                      | 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](/docs/pt-BR/architecture/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](/docs/pt-BR/architecture/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](/docs/pt-BR/architecture/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](/docs/pt-BR/architecture/cells))                    | no content                                                                                                                                                                                                                                                   |
| `cell_probe` (the probe nonce the `loom cell` commands write, [cells](/docs/pt-BR/architecture/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](#postgres))                                                                                                                                                              |
| `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](/docs/pt-BR/architecture/storage-authority#the-tables)                                                                                      | 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`, `knowledge_mirrors`, `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](#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](#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`](#postgres) 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](#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](#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](#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](#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](#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](/docs/pt-BR/architecture/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](#each-fact-has-one-owner) 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](/docs/pt-BR/operations/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](/docs/pt-BR/architecture/cells#what-each-cell-runs)): 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](#cache-generations)).

**"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](/docs/pt-BR/architecture/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](#restores).

## Source entry points

- `schemas/schema.sql`, `schemas/pg_migrations/`: the tables the rows place.
- `server/erasure/store/census.py` (`_CENSUS`, `STEP_ONLY_TABLES`,
  `CANONICAL_OWNER_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](/docs/pt-BR/architecture/cells): what a cell holds, the router and its directory.
- [Storage authority](/docs/pt-BR/architecture/storage-authority): the memory heads, the journal, publication
  and erasure under Postgres.
- [ClickHouse](/docs/pt-BR/architecture/clickhouse): engines, sort keys, vector storage and mutations.
- [Rolling upgrades](/docs/pt-BR/architecture/rolling-upgrades): the rules the move patterns follow.
- [Memory architecture](/docs/pt-BR/architecture/memory-architecture#erasure-saga): the erasure saga and the
  residue census.
