Database schema and migrations

1. Two planes

The index plane is a projection of the git tree and can be thrown away: memhtml 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.