---
title: Database schema and migrations
description: The two SQLite planes, and every migration that builds them.
---

## 1. Two planes

The index plane is a projection of the git tree and can be thrown away: [`memhtml index rebuild`](/reference/commands/index-rebuild/) reproduces it from `HEAD`. The state plane holds what git cannot reproduce, which is why it ships a committed sidecar file alongside the database.

Both planes grow the same way. Adding a migration means adding one `.sql` file to the plane's directory: the files are read in filename order at run time, and no code changes.

## 2. The index plane

Where the rebuildable index's migrations live, applied in filename order.

12 migrations, in the order they are applied:

| File | Creates |
| --- | --- |
| `0001_files.sql` | `table files`, `index files_content_hash_active`, `index files_type_active`, `index files_workspace`, `index files_para`, `index files_updated`, `index files_event`, `index files_session`, `index files_ttl`, `index files_blob`, `table file_tags`, `index file_tags_tag`, `table file_entities`, `index file_entities_name`, `table file_facets`, `index file_facets_name`, `table file_citations` |
| `0002_chunks.sql` | `table chunks`, `index chunks_path`, `index chunks_hash`, `index chunks_hash_ord`, `table embeddings`, `index embeddings_model` |
| `0003_fts.sql` | `table files_fts`, `trigger files_fts_insert`, `trigger files_fts_delete`, `trigger files_fts_update` |
| `0004_edges.sql` | `table edges`, `index edges_src`, `index edges_dst`, `index edges_rel`, `index edges_derived` |
| `0005_traces.sql` | `table traces`, `index traces_slug`, `index traces_cwd`, `index traces_started`, `index traces_mtime`, `table traces_fts`, `trigger traces_fts_insert`, `trigger traces_fts_delete`, `trigger traces_fts_update`, `table trace_prompts`, `index trace_prompts_uuid`, `table memory_session_links`, `index msl_session`, `index msl_path` |
| `0006_sleep.sql` | `table sleep_runs`, `index sleep_runs_started`, `table sleep_phases` |
| `0007_watermark.sql` | `table index_state`, `table trace_watermarks` |
| `0008_tasks.sql` | `table files_next`, `table file_tags_snap`, `table file_entities_snap`, `table file_facets_snap`, `table file_citations_snap`, `table chunks_snap`, `table embeddings_snap`, `index files_content_hash_active`, `index files_type_active`, `index files_workspace`, `index files_para`, `index files_updated`, `index files_event`, `index files_session`, `index files_ttl`, `index files_blob`, `index files_task_status`, `table files_fts`, `trigger files_fts_insert`, `trigger files_fts_delete`, `trigger files_fts_update`, `table edges_next`, `index edges_src`, `index edges_dst`, `index edges_rel`, `index edges_derived` |
| `0009_frame_key.sql` | `index files_frame_key_active` |
| `0010_trace_consolidations.sql` | `table trace_consolidations`, `index trace_consolidations_run` |
| `0011_edge_indexes.sql` | `index edges_src`, `index edges_dst` |
| `0012_origin_path.sql` | `index files_origin` |

### 2.1. 0001\_files.sql

`table files`, `index files_content_hash_active`, `index files_type_active`, `index files_workspace`, `index files_para`, `index files_updated`, `index files_event`, `index files_session`, `index files_ttl`, `index files_blob`, `table file_tags`, `index file_tags_tag`, `table file_entities`, `index file_entities_name`, `table file_facets`, `index file_facets_name`, `table file_citations`

The memory corpus, one row per file in the git tree. Rebuildable: every column here is
derived from a committed file plus its blob sha, so `rm index.db` costs a rebuild and no data.

### 2.2. 0002\_chunks.sql

`table chunks`, `index chunks_path`, `index chunks_hash`, `index chunks_hash_ord`, `table embeddings`, `index embeddings_model`

Chunks and their vectors. Both key on `content_hash`, not on `path`: a `git mv`, which is what
eviction and every rename are, reuses the vector with zero Bedrock calls, and two files whose
bodies later diverge never share one.

### 2.3. 0003\_fts.sql

`table files_fts`, `trigger files_fts_insert`, `trigger files_fts_delete`, `trigger files_fts_update`

The lexical index: an FTS5 table over `files.fts_text`, plus the triggers that keep it in step.

### 2.4. 0004\_edges.sql

`table edges`, `index edges_src`, `index edges_dst`, `index edges_rel`, `index edges_derived`

The graph. Authored edges come from the files' \<link rel="memhtml-\*"> elements; derived edges are
sleep-mined and live only here, because they are a re-derivable function of the corpus and the
embedder. `derived` is the firewall: the retention penalty counts only `derived = 0`, so an
uncorroborated machine suspicion can never evict a memory.

### 2.5. 0005\_traces.sql

`table traces`, `index traces_slug`, `index traces_cwd`, `index traces_started`, `index traces_mtime`, `table traces_fts`, `trigger traces_fts_insert`, `trigger traces_fts_delete`, `trigger traces_fts_update`, `table trace_prompts`, `index trace_prompts_uuid`, `table memory_session_links`, `index msl_session`, `index msl_path`

The trace plane: a read-only index over ~/.claude/projects. `.memhtml` never holds session content,
so every column here is a pointer or a capped head, never a copy.

### 2.6. 0006\_sleep.sql

`table sleep_runs`, `index sleep_runs_started`, `table sleep_phases`

Sleep-run reporting. Not load-bearing: the commit trailers on the sleep branch are what
`memhtml sleep resume` reads (`git log --format=%B base..HEAD | grep '^Memhtml-Phase:'`), so this pair of
tables is a reporting convenience the git history can regenerate. That is deliberate. A journal
table that a resume depended on would be a second source of truth for what already happened.

### 2.7. 0007\_watermark.sql

`table index_state`, `table trace_watermarks`

The two watermarks. Each answers "what did the last run already consume", and each is what makes
the corresponding incremental path cheap enough to run on a cron.

### 2.8. 0008\_tasks.sql

`table files_next`, `table file_tags_snap`, `table file_entities_snap`, `table file_facets_snap`, `table file_citations_snap`, `table chunks_snap`, `table embeddings_snap`, `index files_content_hash_active`, `index files_type_active`, `index files_workspace`, `index files_para`, `index files_updated`, `index files_event`, `index files_session`, `index files_ttl`, `index files_blob`, `index files_task_status`, `table files_fts`, `trigger files_fts_insert`, `trigger files_fts_delete`, `trigger files_fts_update`, `table edges_next`, `index edges_src`, `index edges_dst`, `index edges_rel`, `index edges_derived`

`task` becomes the tenth `memory_type`, `task` the fourth `edge_class`, and `files` gains the two
columns a task carries. Recreate-and-copy, because SQLite cannot ALTER a CHECK constraint and both
tables carry one. Every existing row passes the WIDENED CHECKs, so the copies are lossless.

### 2.9. 0009\_frame\_key.sql

`index files_frame_key_active`

`files` gains `frame_key`: the claim's SLOT, derived from its gist at projection time by
`@memhtml/domain`'s `frameKeyOf`. Two active memories sharing a frame key state the same relation with
(possibly) different values, which is what makes a contradiction findable by one indexed lookup
instead of an O(corpus) scan or an LLM pass over every pair.

### 2.10. 0010\_trace\_consolidations.sql

`table trace_consolidations`, `index trace_consolidations_run`

The trace-consolidation watermark: which sessions the sleep cycle has already distilled.

### 2.11. 0011\_edge\_indexes.sql

`index edges_src`, `index edges_dst`

`edges_src` and `edges_dst` carry NO predicate: `(src_path, edge_class)` and `(dst_path, edge_class)`
over every row, authored and derived alike. That is what the memory-graph walk needs, and a partial
index on `derived = 0` cannot serve it at all.

### 2.12. 0012\_origin\_path.sql

`index files_origin`

The archive mapping, read backwards. `origin_path` holds the pre-archive path of a file under
`archive/<YYYY>/`, derived from the path itself by the projection, and NULL for an active file.

## 3. The state plane

The state plane's own migration ledger. A separate directory because these statements are applied
to the ATTACHed `state` database, which has its own `schema_migrations` table. The two planes have
independent lifetimes, and `index.db` is deleted and rebuilt without touching `state.db`.

2 migrations, in the order they are applied:

| File | Creates |
| --- | --- |
| `S0001_access.sql` | `table state.access`, `index state.access_last`, `table state.edge_corroboration` |
| `S0002_entity_corroboration.sql` | `table state.entity_corroboration`, `index state.entity_corroboration_pending` |

### 3.1. S0001\_access.sql

`table state.access`, `index state.access_last`, `table state.edge_corroboration`

The state plane, applied to the ATTACHed `state` database.

### 3.2. S0002\_entity\_corroboration.sql

`table state.entity_corroboration`, `index state.entity_corroboration_pending`

The corroboration counter on a machine-proposed ENTITY MERGE, the second one-way door that earns a
place in the durable plane.

## 4. Provenance

A loader generates this page from `packages/index/migrations and packages/index/state-migrations` while the site builds, so no file in the repository holds it: each row is the file's own leading rationale and the objects its statements create. Change the registry and this page changes with it.