SQLite Schema Reference

The state of ~/.local/share/hippo/hippo.db: the live tables, the per-version migration history that produced them, and the recovery story when daemon and brain disagree on what version is loaded. Companion to lifecycle.md (which traces what writes to which table) and capture/operator-runbook.md (recipes for the most common schema-related failure: version mismatch).

At a glance

Daemon and brain handshake on this constant at startup. If they disagree the daemon refuses to bind its socket, the brain refuses to enrich, and hippo doctor surfaces the mismatch with a remediation hint. See Version mismatch recovery.

Per-version changelog

The Rust migration runner at storage.rs::open_db walks every version from the loaded PRAGMA user_version up to EXPECTED_VERSION, applying the migration block for each step. Migration crash-safety is mixed:

• Crash-safe (v8 and later): every CREATE uses IF NOT EXISTS, every ALTER goes through add_column_if_missing (which pre-checks PRAGMA table_info), and every seed insert uses INSERT OR IGNORE. A daemon that crashes after adding a column but before bumping user_version retries the migration cleanly on next start.
• Not crash-safe (v1→v2, v6→v7): these blocks issue unguarded ALTER TABLE … ADD COLUMN inside the same execute_batch as PRAGMA user_version = N. A crash between the ALTER and the PRAGMA leaves the column added but the version unchanged; the next start retries the same ALTER and SQLite errors with “duplicate column name”. Recovery is a manual PRAGMA user_version = N after confirming the column landed. The add_column_if_missing pattern was introduced at v8 and used consistently from there forward.

Reading the live schema

# Full schema dump (pipe through less for large outputs)
sqlite3 ~/.local/share/hippo/hippo.db .schema

# Single table
sqlite3 ~/.local/share/hippo/hippo.db ".schema events"

# Indexes only
sqlite3 ~/.local/share/hippo/hippo.db ".indexes events"

# Confirm version
sqlite3 ~/.local/share/hippo/hippo.db "PRAGMA user_version;"

Top-level table map

Foreign-key relationships (high level)

sessions ──< events
             ├── source_kind in {'shell', 'claude-tool', ...}
             └── probe_tag NULL except for synthetic probes

agentic_sessions ──< knowledge_node_agentic_sessions >── knowledge_nodes   (LIVE)
claude_sessions  ──< knowledge_node_claude_sessions  >── knowledge_nodes   (FROZEN — backfilled into agentic_* at v18)
events           ──< knowledge_node_events            >── knowledge_nodes
browser_events   ──< knowledge_node_browser_events    >── knowledge_nodes
workflow_runs    ──< knowledge_node_workflow_runs     >── knowledge_nodes
lessons          ──< knowledge_node_lessons           >── knowledge_nodes

knowledge_nodes ──< knowledge_node_entities >── entities
                ──< knowledge_fts (FTS5 mirror, trigger-synced)
                ──< knowledge_vectors (sqlite-vec mirror, brain-managed)

source_health      (no FKs; one row per logical source)
capture_alarms     (no FKs; references invariant_id by string)
enrichment_queue   ──> events
agentic_enrichment_queue ──> agentic_sessions   (LIVE — shared by all four harnesses)
claude_enrichment_queue  ──> claude_sessions    (FROZEN — backfilled into agentic_* at v18)
browser_enrichment_queue ──> browser_events
workflow_enrichment_queue ──> workflow_runs

PRAGMA foreign_keys is ON for every connection (storage.rs::open_db, vector_store.py::open_conn).

Migration guarantees

• Single-shot per version. Each migration block in storage.rs::open_db runs at most once per database lifetime: the version range guard (if (1..=N).contains(&version)) becomes false after PRAGMA user_version = N+1 lands.
• Idempotent on partial-success crash (v8 and later). From v8 onward, every CREATE uses IF NOT EXISTS; every ALTER goes through add_column_if_missing which pre-checks PRAGMA table_info; every seed insert uses INSERT OR IGNORE. A daemon that crashes after adding a column but before bumping user_version retries the migration cleanly on next start. The v1→v2 and v6→v7 blocks predate this discipline (see the carve-out at the top of the changelog) — they require manual recovery if interrupted between the unguarded ALTER and the matching PRAGMA user_version inside the same execute_batch.
• Atomic version bumps for the table-recreate path. v13 (entities CHECK) and v17 (segment-capable agentic_sessions) are the two true table-recreates, and v18 (Claude-family backfill) reuses the same transactional discipline without dropping anything. Each bundles PRAGMA user_version = N into the same execute_batch as the COMMIT (preceded by DROP TABLE / RENAME for the recreates) — a crash after the rename/backfill can’t leave the DB at the prior version with the new structure live. Earlier versions (including v8, which is a CREATE-IF-NOT-EXISTS plus add_column_if_missing loop) issue PRAGMA user_version = N in a separate execute_batch after the migration body completes, so re-run safety on those steps comes from the idempotency of each individual statement, not from atomicity with the version bump.
• PRAGMA foreign_keys discipline. Migrations that drop or rebuild a table (v13, v17) — and the v18 backfill that crosses FK boundaries — explicitly turn FKs off, run inside a transaction, run a PRAGMA foreign_key_check as a query before the version bump, and turn FKs back on at the end. Other migrations rely on the default foreign_keys=ON set by open_db.

Version mismatch recovery

Symptom: daemon refuses to bind its socket; hippo doctor reports a schema-version mismatch.

# Run the unified handshake check (compares all three at once).
hippo doctor --explain | grep -A 4 "schema"

# Or check each side individually:

# 1. What does the live DB say?
sqlite3 ~/.local/share/hippo/hippo.db "PRAGMA user_version;"

# 2. What version does the daemon binary expect? (compiled-in constant)
grep -E "^pub const EXPECTED_VERSION" \
  ~/projects/hippo/crates/hippo-core/src/storage.rs

# 3. What version does the brain expect?
uv run --project brain python -c \
  "from hippo_brain.schema_version import EXPECTED_SCHEMA_VERSION; print(EXPECTED_SCHEMA_VERSION)"

All three numbers must match. Common causes and fixes:

Don’t manually PRAGMA user_version = N. The version bump is a marker for “the migration body has run.” Setting it manually skips the migration body, leaving the DB structurally inconsistent with what the binary expects.

Backup before manual surgery. cp ~/.local/share/hippo/hippo.db{,.bak.$(date +%Y%m%d-%H%M)}.

Adding a new migration

When you bump EXPECTED_VERSION from N to N+1:

1. Write the migration block in storage.rs::open_db guarded by if (1..=N).contains(&version).
2. Update crates/hippo-core/src/schema.sql so fresh installs match.
3. Bump EXPECTED_VERSION in storage.rs AND EXPECTED_SCHEMA_VERSION in brain/src/hippo_brain/schema_version.py in the same PR (they must agree).
4. Bump [workspace.package].version in Cargo.toml and [project].version in brain/pyproject.toml for the lockstep release (see docs/release.md).
5. Add a row to the changelog table above.
6. If your migration touches entities.type or other CHECK constraints, follow the 12-step table-recreate recipe from v13. Test the partial-success-crash case: kill the migration mid-way and confirm re-run lands cleanly.

See also

• lifecycle.md — what writes to which table.
• capture/architecture.md — source_health, capture_alarms, and the watchdog’s view of the schema.
• capture/operator-runbook.md — recipes for the most common schema-related failure (version mismatch).
• release.md — lockstep version contract and release workflow.
• crates/hippo-core/src/schema.sql ↗ — authoritative SQL for fresh installs.