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
| Fact | Value |
|---|---|
| Current version | PRAGMA user_version in the authoritative schema below |
| Authoritative schema | crates/hippo-core/src/schema.sql ↗ |
| Version constant (Rust) | crates/hippo-core/src/storage.rs::EXPECTED_VERSION |
| Version constant (Python) | brain/src/hippo_brain/schema_version.py::EXPECTED_SCHEMA_VERSION |
| Daemon refuses to bind on mismatch | crates/hippo-daemon/src/schema_handshake.rs |
| Migration runner | crates/hippo-core/src/storage.rs::open_db |
| Live version (yours) | sqlite3 ~/.local/share/hippo/hippo.db "PRAGMA user_version;" |
The daemon requires matching Rust and Python expected versions before migration. Python’s accepted database versions are defined by ACCEPTED_READ_VERSIONS in schema_version.py; the additive classification migration allows ordinary queries and enrichment on the preceding schema, with classification disabled when its table is absent. 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 throughadd_column_if_missing(which pre-checksPRAGMA table_info), and every seed insert usesINSERT OR IGNORE. A daemon that crashes after adding a column but before bumpinguser_versionretries the migration cleanly on next start. - Not crash-safe (v1→v2, v6→v7): these blocks issue unguarded
ALTER TABLE … ADD COLUMNinside the sameexecute_batchasPRAGMA user_version = N. A crash between theALTERand thePRAGMAleaves the column added but the version unchanged; the next start retries the sameALTERand SQLite errors with “duplicate column name”. Recovery is a manualPRAGMA user_version = Nafter confirming the column landed. Theadd_column_if_missingpattern was introduced at v8 and used consistently from there forward.
| Version | Summary | Tables/columns | Operational impact |
|---|---|---|---|
| v1 | Initial schema. | events (without envelope_id), sessions. | Baseline. Every fresh install since v1 lands here first then migrates forward. |
| v2 | Event dedup. | events.envelope_id column + unique index. | Browser visits and Claude tool events get a stable dedup key, enabling replay-safe ingest. |
| v3 | Claude session ingest. | claude_sessions, knowledge_node_claude_sessions, claude_enrichment_queue. | Hippo’s first non-shell capture path. The (session_id, segment_index) UNIQUE constraint introduced here remains the watcher’s upsert conflict target through every later migration (v12 added content_hash columns alongside it but did not change the conflict key itself). |
| v4 | Browser source. | browser_events, browser_enrichment_queue, knowledge_node_browser_events plus six indexes on browser_events and the queue (idx_browser_events_timestamp, idx_browser_events_domain, idx_browser_events_envelope_id [UNIQUE], idx_browser_events_enriched, idx_browser_queue_pending, idx_browser_events_ts_domain). | Firefox extension begins landing visits via Native Messaging. The unique-on-envelope_id index is what lets make_envelope_id dedup same-URL repeats within dedup_window_minutes. |
| v5 | GitHub Actions ingest. | workflow_runs, workflow_jobs, workflow_annotations, workflow_log_excerpts, workflow_enrichment_queue, sha_watchlist, lessons, lesson_pending, knowledge_node_workflow_runs, knowledge_node_lessons. | Workflow-poller (gh_poll.rs) starts ingesting CI runs; lessons becomes the substrate for graduating recurring CI failures into named tips. |
| v6 | Full-text search on knowledge nodes. | knowledge_nodes (created here for legacy v1 DBs that predate it), knowledge_fts (FTS5 virtual table over summary/embed_text/content), AI/AD/AU triggers to keep FTS in sync. Note: knowledge_vectors (vec0) is NOT created here — the Rust daemon doesn’t load the sqlite-vec extension; the Python brain creates it lazily on first embed. | hippo query --raw now does FTS5 lexical search. The MATCH operator fast-paths over embed_text without round-tripping through Python. |
| v7 | Multi-source events. | events.source_kind (default 'shell'), events.tool_name, idx_events_source_kind. | First step toward the multi-source capture stack. source_kind='claude-tool' rows enter the events table for tool-call envelopes derived from Claude session ingest. |
| v8 | Capture-reliability ground truth. | source_health table seeded with rows for shell, claude-tool, claude-session, browser from the latest existing event timestamps; events.probe_tag, claude_sessions.probe_tag, browser_events.probe_tag. | Introduces the capture/architecture.md invariant stack. Probe events get a probe_tag IS NOT NULL marker that all user-facing queries filter on (AP-6). |
| v9 | Watchdog alarm ledger. | capture_alarms, idx_capture_alarms_invariant_active (partial index over un-acked rows), idx_claude_sessions_start_time. | hippo watchdog run writes alarm rows; hippo alarms list / ack operates on this table. |
| v10 | Watcher resume state. | claude_session_offsets (per-file byte offset + inode for the FS watcher), claude_session_parity (now-unused; kept so v9→v10 migration on existing DBs converges with fresh installs). | The FS watcher (T-5/PR #86) becomes durable across daemon restarts. The legacy tmux tailer was deleted in T-8/PR #89; claude_session_parity is the residue. |
| v11 | Auto-resolve alarms. | capture_alarms.resolved_at, capture_alarms.clean_ticks (CHECK ≥ 0). The “active alarm” partial index is rebuilt to include resolved_at IS NULL. | Watchdog automatically resolves an alarm after 2 consecutive clean evaluations; resolved rows stop suppressing new alarms via rate-limiting. Cleared with hippo alarms prune. |
| v12 | Claude segment dedup-by-content. | claude_sessions.content_hash, claude_sessions.last_enriched_content_hash. | Phase 1 fix for the AP-12 INSERT-OR-IGNORE bug. The watcher upserts segments with a content-hash; the brain compares against last_enriched_content_hash to gate re-enrichment. Pre-v12 rows have content_hash IS NULL until the next watcher pass re-hashes them. |
| v13 | env_var entity type. | entities.type CHECK list extended with 'env_var'. SQLite cannot ALTER a CHECK constraint, so the migration follows the 12-step table-recreate recipe (PRAGMA foreign_keys=OFF → BEGIN → DROP TABLE IF EXISTS entities_new → CREATE TABLE entities_new with the expanded CHECK → INSERT … SELECT → DROP TABLE entities → ALTER TABLE entities_new RENAME TO entities → recreate indexes → foreign_key_check → PRAGMA user_version = 13 → COMMIT → foreign_keys=ON). The user_version bump is inside the same execute_batch as the COMMIT, so a crash after rename can’t leave the DB at v12 with the new CHECK. | RAG synthesis surfaces env-var identifiers (HIPPO_FORCE, HIPPO_PROJECT_ROOTS, etc.) on the dedicated Entities: line that lives outside the truncatable Detail: block. Closed #108 ↗. |
| v14 | Agentic session ingestion. | agentic_sessions, knowledge_node_agentic_sessions, agentic_enrichment_queue, agentic_cursor; source-health rows for agentic-session-claude, agentic-session-opencode, and brain-preflight. | Adds opencode session ingestion and the I-11/I-12 watchdog coverage for agentic-session failures and stuck brain preflight. |
| v15 | Codex session ingestion capture-health. | No new tables. Seeds the source_health row agentic-session-codex (NULL last_event_ts) via INSERT OR IGNORE (gated on the source_health table existing), then bumps PRAGMA user_version = 15 in a follow-up execute_batch. | The Codex poller (hippo codex-poll, launchd com.hippo.codex-session) records capture health under agentic-session-codex; without this row its source_health UPDATE is a silent no-op. Codex segments land in claude_sessions (distinguished by their .codex/ source_file path, not a harness column), but the capture-path health key follows the newer agentic-session-* form. See docs/superpowers/specs/2026-05-17-codex-ingestion-design.md ↗. |
| v16 | Cursor session ingestion capture-health. | No new tables. Seeds the source_health row agentic-session-cursor (NULL last_event_ts) via INSERT OR IGNORE, then bumps PRAGMA user_version = 16 in a follow-up execute_batch. | The Cursor poller (hippo cursor-poll, launchd com.hippo.cursor-session) records capture health under agentic-session-cursor; Cursor segments land in claude_sessions distinguished by their .cursor/ source_file path. See docs/superpowers/specs/2026-05-25-cursor-ingestion-design.md ↗. |
| v17 | Segment-capable agentic_sessions rebuild (unification step 1). | Table-recreate of agentic_sessions (the v14 shape was opencode-only — one row per session, no segments). Adds 7 columns — segment_index, git_branch, is_subagent, tool_calls_json, user_prompts_json, content_hash, last_enriched_content_hash (each defaulted so existing opencode rows migrate cleanly; segment_index hard-set to 0 in the INSERT … SELECT). Widens the harness CHECK to include 'cursor'. Swaps the UNIQUE constraint from (session_id, harness) to (session_id, harness, segment_index) — SQLite can’t alter UNIQUE in place, so this is the only true table-recreate after v13. knowledge_node_agentic_sessions and agentic_enrichment_queue re-bind to the rebuilt table automatically via textual-FK resolution through the ALTER TABLE … RENAME. FK-safe like v13: foreign_keys=OFF for the txn, DROP TABLE IF EXISTS agentic_sessions_new for crash-retry, a PRAGMA foreign_key_check run as a query (not batched) before the bump, then PRAGMA user_version = 17 bundled into the same execute_batch as COMMIT. Partial-schema test DBs with no agentic_sessions fall to a version-bump-only branch. | Makes one table able to hold all four harnesses (claude-code, opencode, codex, cursor), each segmented by its own boundary rule. No rows carry harness='cursor' yet at this step — the constraint must accept it so the next step’s writers aren’t blocked on another rebuild. |
| v18 | Repoint agentic writers + backfill the legacy Claude family + freeze it (unification step 2). | No new tables. The daemon writers (claude_session.rs, codex_session.rs, cursor_session.rs) and the brain claim/write path now write the agentic_* family exclusively; the migration backfills the historical claude_sessions / knowledge_node_claude_sessions / claude_enrichment_queue rows into agentic_sessions / knowledge_node_agentic_sessions / agentic_enrichment_queue. (#1) Sessions copy across with harness derived from source_file via CASE (%/.codex/% or %/CodingAssistant/codex/% → codex; %/.cursor/% → cursor; else claude-code), dropping the old id so agentic_sessions assigns fresh ids. (#2) Link rows re-resolve the new agentic id by joining the natural key (session_id, segment_index) + the same harness CASE, JOINing knowledge_nodes to skip dangling legacy links. (#3) Only un-terminal queue rows (pending/processing/failed) targeting a not-yet-enriched (enriched = 0) agentic row migrate, so the backfill can’t re-enrich an already-enriched session (no node dedup). Every statement is INSERT OR IGNORE, so a re-run is a no-op and a live (re-ingested) row wins. DROPs nothing — FK-safe like v17 (foreign_keys=OFF, FK check as a query, PRAGMA user_version = 18 bundled with COMMIT); partial-schema DBs lacking either family fall to a version-bump-only branch. | claude_sessions / knowledge_node_claude_sessions / claude_enrichment_queue become frozen legacy: still created by schema.sql, no longer written, backfilled here, dropped in a later unification step. Repointed brain readers (retrieval.py, workflow_enrichment.py, evaluation.py) and mcp_queries.py search now see the full Claude/Codex/Cursor history through the agentic tables. |
| v19 | Add read-only Claude Code auto-memory ingestion. | Adds document identity, immutable revisions, deterministic chunks, enrichment queue, and knowledge-node links. Seeds source_health.source = 'claude-auto-memory'. The migration is additive and idempotent; no existing rows are rewritten. | The brain and daemon require v19. Disable [auto_memory] to stop ingestion without removing stored projections. |
| v20 | Auto-memory taxonomy tables. | memory_categories, memory_index_links, and related taxonomy DDL via AUTO_MEMORY_TAXONOMY_SCHEMA. | Category/index metadata for auto-memory retrieval; additive only. |
| v21 | Auto-memory watcher source_health identity. | Seeds source_health.source = 'auto-memory-watcher' via INSERT OR IGNORE. | Watcher liveness for the auto-memory ingest path joins the capture-health family. |
| v22 | Watcher resume-state rename (SNUG-115 Phase A). | Creates agentic_session_offsets (same columns as legacy claude_session_offsets) and INSERT OR IGNORE copies existing rows. Does not drop legacy tables. | Claude FS watcher and backfill CLI read/write agentic_session_offsets. Legacy claude_session_offsets remains frozen on upgraded DBs until Phase B. |
| v26 | Event payload expiry and deletion receipts (HIPO-13). | Adds events.payload_expiry, an insert trigger and expiry index, and append-only deletion_receipts. No event table rebuild or bulk backfill. | Explicit payload GC retains event metadata and joins. See Event payload retention. |
| v28 | Agent-evaluation metadata, Phase 1 (HIPO-57). | Additive: 14 agentic_sessions columns via add_column_if_missing (no rebuild), partial indexes idx_agentic_sessions_agent (WHERE agent <> '') and idx_agentic_sessions_model (WHERE model <> ''), and the per-session agentic_session_facts table. Index and table DDL lives in schema/agentic_eval.sql ↗, shared by fresh installs and the migration. Existing rows take the column defaults. | Lets evaluation queries key on subagent type, model, tokens, and hook outcomes. See Agent-evaluation columns. |
| v29 | Agent-evaluation records (HIPO-64). | Additive: creates eval_review_runs, eval_findings, eval_verdicts (with four append-only triggers), eval_escapes, eval_bench_runs, and their indexes. DDL lives in schema/agent_eval.sql ↗, shared by fresh installs and the migration. No existing table changes. | hippo ingest agent-eval writes the tables; payload GC clears their expired free text. The brain’s hourly linkage job reads findings and writes automatic verdicts; no enrichment, embedding, or retrieval path reads them (AP-14). See Agent-evaluation records. |
The v24-to-v25 migration adds optional classification state without rewriting
knowledge content or vectors. Its authoritative DDL is
schema/classification.sql ↗,
with fresh-install parity covered by the storage migration tests. Operational
usage is in Jev decisions.
Agent-evaluation columns (v28)
All metadata columns below sit outside content_hash, so a change that touches only them never re-enqueues enrichment. The Claude Code writer (claude_session.rs::insert_segments_with_facts) sets them, and the Codex writer (codex_session.rs::upsert_segment_tx) sets the model, effort, and token columns. Other harnesses leave the defaults.
| Column | Type / default | Meaning |
|---|---|---|
agent (existing) | TEXT DEFAULT '' | Claude Code: agentType from the subagent’s sibling meta.json; '' for main sessions and when meta.json is missing or malformed. |
model (existing) | TEXT DEFAULT '' | Served model id. Claude Code: the most frequent message.model among the segment’s assistant records (ties: first seen). Codex: the most frequent turn_context.model among the segment’s turns. A new segment counts the context in effect at its first prompt; a later turn_context counts for the segment of the next prompt, decided after that prompt’s 5-minute gap check, or for the open segment when no prompt follows. '' when none was recorded. |
requested_model | TEXT, NULL | Claude Code: the most frequent top-level requestedModel of the segment’s assistant records (ties: first seen). NULL when absent. Codex rollouts carry no requested-model field, so it stays NULL. |
effort | TEXT, NULL | Reasoning effort. Claude Code: most frequent top-level effort of the assistant records. Codex: most frequent turn_context.effort, attributed like model. Ties: first seen. NULL when absent. |
token_count (existing) | INTEGER | Token total for the segment; the definition depends on the writer. Claude Code via the daemon: input_tokens + output_tokens (cache reads and creation excluded). Codex: the same, with cached input removed from input_tokens. Claude Code via the Xcode Python ingester (scripts/hippo-ingest-claude.py, claude_sessions.py): legacy value (input read from user records that carry none, output summed per content block), with model = '' and zero usage columns until that extractor is ported. opencode: the reported total, or the sum of all token fields including cache. Pi: input + output; its cache handling is not verified. Cursor: 0. |
input_tokens | INTEGER NOT NULL DEFAULT 0 | Uncached input tokens for the segment. |
output_tokens | INTEGER NOT NULL DEFAULT 0 | Output tokens for the segment (Codex: includes reasoning tokens). |
cache_read_tokens | INTEGER NOT NULL DEFAULT 0 | Prompt-cache read tokens for the segment. |
cache_creation_tokens | INTEGER NOT NULL DEFAULT 0 | Prompt-cache creation tokens for the segment. |
spawn_depth | INTEGER, NULL | Claude Code subagent nesting depth (spawnDepth in meta.json). |
parent_tool_use_id | TEXT, NULL | toolUseId of the parent’s Agent call that spawned this subagent. |
subagent_finished | INTEGER, NULL | finished from meta.json as 0/1; NULL when absent. |
hook_runs | INTEGER NOT NULL DEFAULT 0 | Claude Code: sum of hookCount over the segment’s system records with subtype = "stop_hook_summary". |
hook_errors | INTEGER NOT NULL DEFAULT 0 | Claude Code: total length of the hookErrors arrays of those records. The error text is not stored. |
prevented_continuations | INTEGER NOT NULL DEFAULT 0 | Claude Code: number of those records with preventedContinuation == true. stopReason text is not stored. |
hook_names_json | TEXT NOT NULL DEFAULT '[]' | Claude Code: JSON array of the distinct hookInfos[].command values of those records, each passed through redaction, sorted. Names only: hook output is never stored. |
permission_decisions_json | TEXT NOT NULL DEFAULT '{}' | Claude Code: JSON object mapping each permission decision to its count over the segment’s user records, for example {"accept":2,"reject":1}. The key is permissionDecision.decision; an older bare-string permissionDecision is used as the key directly. source, reasonType, and permissionMode are ignored, as is the field on any non-user record. |
Token accounting rules:
- Claude Code writes one
assistantrecord per content block. Records of one API turn sharemessage.id. Streamed records of a turn repeat every usage field exceptoutput_tokens, which grows, so the turn counts once at the field-wise maximum of its records. A turn belongs to the segment where itsmessage.idfirst appears, even when a segment split falls between its records.userrecords carry nousage. Records with model<synthetic>do not vote formodel. Before HIPO-60 the extractor readinput_tokensfromuserrecords, sotoken_countheld output tokens only. - Codex reports a cumulative
total_token_usageintoken_countevents. The writer adds the growth since the previous event, so repeated events add nothing. Codex counts cached tokens insideinput_tokens; the writer subtractscached_input_tokensso the four columns do not overlap. Tokens reported before a segment opens are not attributed to any segment. - A missing or malformed usage field counts as 0.
Hook and permission rules (Claude Code only; other harnesses keep the defaults):
- A
stop_hook_summaryrecord is still asystemrecord, so it starts and counts in segments exactly as before. The hook columns never entercontent_hash. - A field of the wrong type (for example a string
hookCount, a non-arrayhookErrors, a non-stringcommandorpermissionDecision) contributes nothing, logs onewarnfor the file, and never drops a segment. A missing field also contributes nothing. - A trailing segment that is identical in content to its predecessor is collapsed, and its hook counts go with it.
agentic_session_facts holds one row per (session_id, harness) for totals a harness reports for the whole session. Only Claude Code writes it today (claude_session.rs::upsert_session_facts), once per ingest of a transcript file, in the same transaction as the segments. Facts never enter content_hash, so a facts-only change never re-enqueues enrichment. The key is the session_id of the file (its name stem), not the sessionId inside the records.
| Column | Type | Meaning |
|---|---|---|
session_id, harness | TEXT NOT NULL, primary key | Same keys as agentic_sessions; harness = 'claude-code'. |
cost_usd | REAL | totalCostUSD of the last cost-state record in the file. |
duration_ms | INTEGER | totalDuration of the last cost-state record (ms). |
tool_duration_ms | INTEGER | totalToolDuration of the last cost-state record (ms). |
lines_added, lines_removed | INTEGER | totalLinesAdded and totalLinesRemoved of the last cost-state record. |
title | TEXT | Last aiTitle of the file, redacted. NULL when the file has none. |
cli_version | TEXT | Top-level version of the last assistant record. |
pr_links_json | TEXT NOT NULL DEFAULT '[]' | JSON array of {"number", "repo", "url"} from prNumber, prRepository, and prUrl (redacted), one entry per distinct pr-link record, in file order. |
updated_at | INTEGER NOT NULL | Time of the last ingest that wrote the row, epoch ms. |
A missing or mistyped field is stored as NULL and logs one warn per file. cost-state, pr-link, and ai-title records never start or split a segment and do not count toward message_count.
Resumed sessions: when a later file continues a session_id, the upsert is per column. A column the later file carries replaces the stored value. A NULL column (no cost-state, no aiTitle, no assistant version) or an empty pr-link list keeps the stored value, so a resume never erases a known cost. pr_links_json is replaced as a whole by the later file’s list, not merged.
agentic_sessions.title holds the same redacted last aiTitle on every segment of the file. '' when the file has none; a reparse never replaces a stored title with ''.
hippo ingest claude-session-backfill <glob> first repairs subagent identity in the database: claude-code rows whose stored source_file is under subagents/ but carry is_subagent = 0 (workflow subagents written before v28) get is_subagent, parent_session_id, and project_dir recomputed from that path, even when the transcript is gone, and the count prints as Subagent rows repaired. It then re-reads meta.json for transcripts still on disk and prints Subagent agent coverage: <filled> / <rows>, counting the processed files’ is_subagent = 1 rows by (session_id, harness). The first run after v28 also re-enqueues about 1,300 segments (counted on the development database) whose Agent tool-call summaries changed format, because those summaries are part of content_hash; later runs re-enqueue nothing for metadata-only changes. The live watcher also re-ingests a subagent transcript when its sibling meta.json changes, so subagent_finished no longer waits for a backfill. The 30 s reconciliation pass also compares the meta.json mtime with agentic_session_offsets.updated_at (the start of the last successful ingest), so a meta.json write whose filesystem event was dropped, or that landed while the watcher was stopped, is picked up within one pass. These re-ingests change metadata only and enqueue nothing. The first pass after an upgrade re-ingests each subagent transcript whose meta.json is newer than its stored offset once.
Agent-evaluation records (v29)
hippo ingest agent-eval <file.jsonl> is the only writer, apart from the payload GC and the brain linkage job, which inserts automatic eval_verdicts rows. The record contract (fields, id rules, rejection reasons, exit codes) is in docs/superpowers/specs/2026-10-25-agent-eval-schema-design.md ↗ section 4. Conventions for all five tables:
- The primary key is the producer’s record
id, the idempotency key. A rerun skips an existing id whose stored fields equal the record and rejects a differing one asid_conflict. The comparison leaves out the redacted columns and the hippo-generated ones (ingested_at,payload_expiry,line_hash,repo_identity). - Times are epoch milliseconds.
ingested_atis the time hippo wrote the row. base_sha,head_sha,commit_sha, andfix_commithold full 40-character lowercase hex SHAs; ingest rejects any other form.repois the local absolute path; ingest rejects a relative one asinvalid_field:repo.repo_identityis derived at ingest bygit_repo.rs::derive_git_repo(owner/repofromorigin, else the top-level directory name, else NULL).- Redacted columns pass through the builtin
RedactionEnginepatterns and then the operator’sredact.tomlbefore the insert. Ingest redacts first and then capsclaimandnoteat 4,000 characters,line_textat 1,000, andcategoryat 64. severity,confidence, andverdictare validated by the ingest code, not byCHECK, so a new value needs a contract version and no table rebuild.payload_expiry = ingested_at + 7,776,000,000(90 days), oneval_findingsandeval_verdictsonly. See Event payload retention.- No brain enrichment, embedding, FTS, classification, or retrieval path reads these tables (AP-14). The scorecard module
hippo_brain.scorecard_queries(MCP toolget_agent_scorecard, brainGET /agents/scorecard,hippo agents report) reads them for aggregates only and never selectsclaim,line_text, ornote(MCP reference).
eval_review_runs: one row per review invocation. Insert once, never updated. No column is redacted.
| Column | Type / default | Meaning |
|---|---|---|
run_id | TEXT PRIMARY KEY | Producer run id. |
agent, mode | TEXT NOT NULL | Reviewing agent, the registry agent name (for example blind-reviewer) and mode (for example mechanical). |
requested_model | TEXT, NULL | Model argument (for example haiku). |
model_id | TEXT NOT NULL DEFAULT '' | Sorted, comma-joined served model ids. |
effort | TEXT, NULL | Requested reasoning effort. |
cli_version | TEXT NOT NULL DEFAULT '' | Reviewer CLI version. |
repo, repo_identity | TEXT NOT NULL, TEXT | Local path and derived slug. |
base, base_sha, head_sha | TEXT NOT NULL | Base ref name, that ref resolved at review start, and HEAD at review start. |
input_sha256 | TEXT NOT NULL | SHA-256 of the sorted per-unit input hashes. |
units_total, units_failed | INTEGER NOT NULL DEFAULT 0 | Reviewed units and failed units; a failed review stays distinct from a clean one. |
input_tokens, output_tokens, cache_read_tokens, cache_creation_tokens | INTEGER NOT NULL DEFAULT 0 | Reviewer token sums over all units. |
session_id, harness | TEXT, NULL | Orchestrating agent session (soft link to agentic_sessions and agentic_session_facts; no foreign key). Ingest rejects session_id without harness. |
started_at, finished_at, ingested_at | INTEGER NOT NULL | Review start and end; hippo write time. |
Indexes: idx_eval_review_runs_agent_started_at (agent, started_at), idx_eval_review_runs_session_id_harness (session_id, harness) WHERE session_id IS NOT NULL, idx_eval_review_runs_repo_identity_started_at (repo_identity, started_at).
eval_findings: one row per finding. Insert once; the only later write is the payload-expiry partial UPDATE.
| Column | Type / default | Meaning |
|---|---|---|
finding_id | TEXT PRIMARY KEY | Producer finding id (includes the run id). |
run_id | TEXT NOT NULL REFERENCES eval_review_runs (run_id) | Parent run; ingest rejects an unknown run as unknown_parent. |
repo, repo_identity | TEXT NOT NULL, TEXT | Repeated from the run so linkage can use idx_eval_findings_repo_path without a join. |
path, line | TEXT NOT NULL, INTEGER NOT NULL | Flagged location. |
side | TEXT NOT NULL CHECK (side IN ('+', '-')) | Diff side. |
line_text | TEXT, redacted | Raw source line at the reviewed revision, redacted. NULL after expiry or when absent. |
line_hash | TEXT | lower_hex(sha256(normalize(raw line_text))), where normalize drops one trailing \r and then ASCII spaces and tabs at both ends. Computed from the raw line before redaction; a producer hash is ignored. NULL when line_text is absent. |
severity, confidence | TEXT NOT NULL | high, medium, or low. |
category | TEXT NOT NULL DEFAULT 'unknown' | Reviewer category label. |
claim | TEXT, redacted | The finding text. NULL after expiry. |
logged_at, ingested_at, payload_expiry | INTEGER NOT NULL | Producer log time, hippo write time, text expiry. |
Indexes: idx_eval_findings_run_id (run_id), idx_eval_findings_repo_path (repo, path), idx_eval_findings_payload_expiry (payload_expiry) WHERE line_text IS NOT NULL OR claim IS NOT NULL.
eval_verdicts: append-only, one row per verdict event. verdict is one of accepted, false-positive, not-useful, duplicate, likely-addressed. auto = 1 marks a machine verdict, auto = 0 a human one.
| Column | Type / default | Meaning |
|---|---|---|
verdict_id | TEXT PRIMARY KEY | Producer verdict id. |
finding_id | TEXT NOT NULL REFERENCES eval_findings (finding_id) | Parent finding; ingest rejects an unknown finding as unknown_parent. |
verdict | TEXT NOT NULL | Verdict value. |
commit_sha | TEXT, NULL | Commit that addressed the finding or that a human cites (record field commit). |
note | TEXT, redacted | Rationale. On linkage-job verdicts: NULL (observed fix), the inferred: marker, and/or PR <url>, .... NULL after expiry. |
auto | INTEGER NOT NULL CHECK (auto IN (0, 1)) | Machine (1) or human (0) verdict. |
decided_at, ingested_at, payload_expiry | INTEGER NOT NULL | Decision time, hippo write time, note expiry. |
Indexes: idx_eval_verdicts_finding_id_decided_at (finding_id, decided_at), idx_eval_verdicts_commit_sha (commit_sha) WHERE commit_sha IS NOT NULL, idx_eval_verdicts_payload_expiry (payload_expiry) WHERE note IS NOT NULL.
Triggers (the deletion_receipts pattern): eval_verdicts_no_delete aborts every DELETE; eval_verdicts_no_update aborts an UPDATE of any column except note; eval_verdicts_note_clear_only aborts an UPDATE that sets note to a non-NULL value; eval_verdicts_no_replace aborts an insert of an existing verdict_id, which covers INSERT OR REPLACE and INSERT ... ON CONFLICT DO NOTHING. Ingest therefore checks for the id before it inserts.
The effective verdict of a finding is the latest human verdict, else the latest automatic one, ordered by decided_at and then verdict_id. A finding with no verdict is open.
Automatic likely-addressed verdicts come from two writers: the producer’s review_log.py reconcile (through ingest) and the brain linkage job hippo_brain.eval_linkage (hourly, HIPO-66). The linkage job considers only findings with no verdict at all, so it never adds a verdict after a human one. It matches line_hash against raw lines of path in the commits that descend from the run’s head_sha (or from its merge-base with HEAD after a squash merge) and records the first commit whose file no longer contains the line while a parent’s file does (or that recreates the deleted file without it; after a squash merge, only a commit from after the review start), with auto = 1, that commit_sha, and verdict_id = auto- + the first 16 hex characters of sha256(finding_id|commit_sha|likely-addressed). When the flagged line never appears in the walked commits (fixed before its first commit, or on a branch before a squash merge), the job records the first walked commit that touches path at or after the review’s started_at, provided no branch or remote-tracking ref tip still holds the line, and sets note to inferred: line absent at walk start; first commit touching the path; an observed fix has a NULL note. With the opt-in gh lookup, note can also hold PR <url>, ..., after the marker and ; . Rules, skip reasons, and limits: docs/brain-watchdog.md.
SELECT finding_id, verdict, auto, commit_sha
FROM (
SELECT v.*, ROW_NUMBER() OVER (
PARTITION BY finding_id
ORDER BY (auto = 0) DESC, decided_at DESC, verdict_id DESC
) AS rn
FROM eval_verdicts v
)
WHERE rn = 1;
eval_escapes: one row per defect that reached review and that no finding reported. Insert once, never updated. Notes never expire, so the table has no payload_expiry.
| Column | Type / default | Meaning |
|---|---|---|
escape_id | TEXT PRIMARY KEY | Producer escape id. |
repo, repo_identity | TEXT NOT NULL, TEXT | Local path and derived slug. |
path, line | TEXT NOT NULL, INTEGER NOT NULL | Defect location. |
fix_commit | TEXT, NULL | Commit that fixed the defect. |
note | TEXT NOT NULL, redacted | Description of the escape. |
recorded_at, ingested_at | INTEGER NOT NULL | Producer record time; hippo write time. |
Index: idx_eval_escapes_repo_path (repo, path).
Bench case export. hippo eval export-bench-cases --out DIR [--since 90d] [--repo PATH] (crates/hippo-daemon/src/eval_export.rs) turns accepted findings and escapes into private cases for the agents repository’s blind-review bench (bench.py run --cases-dir DIR). It reads these tables and the repositories in repo; it writes nothing to the database. The bench measures whether a reviewer flags a defect that a change introduces, so the exported diff goes from the fixed file to the defective one and introduces the flagged line.
- Findings: the effective verdict (the query above) is
accepted,sideis+, and a fix commit is known: the accepted verdict’scommit_sha, else the latest automaticlikely-addressedcommit_sha. Escapes:fix_commitis set. - Each case is
<DIR>/f-<finding_id>.jsonor<DIR>/e-<escape_id>.json, so a rerun overwrites the same files.baseholdspathat the fix commit,headholdspathat the defective revision (the run’shead_sha, or the parent offix_commit), and the singlebugtrap anchors the flagged line as an added row (["+", line]), withclaimornoteaswhypluscategoryand, for findings,severity. - A finding’s anchor is the line whose raw-line hash equals
line_hash: the recorded line when it matches, else the only matching line (the review may have seen uncommitted edits). An escape’slineis read in the defective revision. The anchor must be an added row ofgit diff --histogram -U0 --inter-hunk-context=0 <fix> <defective> -- path(the explicit flag keeps adiff.interHunkContextsetting from merging hunks over unchanged lines). - File text,
claim, andnotepass through the builtin patterns and the operator’sredact.toml, the same engines as ingest. File text is redacted as a whole first (so a multi-line match such as a quoted secret or a PEM private key sees its opening delimiter), then per line (so line-anchored patterns apply), then as a whole again. A case whose output still matches a pattern is skipped asredaction_unstable. - Skip reasons, printed with counts:
removed_line_finding,no_linked_commit,repo_missing,file_missing_at_revision,anchor_not_found,anchor_not_changed_by_fix,redaction_unstable,redaction_changed_line_count(a multi-line match in the defective file would move the anchor),redaction_changed_anchor(redaction would alter the flagged line, so the case would not show the defect). - After writing, the export removes every
f-*.jsonande-*.jsoninDIRthat this run did not write and printsremoved N, soDIRis a snapshot: a finding no longer accepted, or now skipped, leaves the corpus. A run filtered by--sinceor--repoproduces the filtered snapshot. Other files inDIRare left alone. DIRis created with mode 0700. The command refuses aDIRthat contains a..component, an existing non-emptyDIRwith nof-*.jsonore-*.jsonfiles (it is not an export directory, and its mode would change), and aDIRinside a git checkout, so private cases cannot be committed to the public repository. The checkout check runsgit rev-parse --is-inside-work-treeon the nearest existing ancestor before creatingDIRand again on the canonicalDIRafter, withGIT_DIR,GIT_WORK_TREE, andGIT_CEILING_DIRECTORIEScleared andGIT_DISCOVERY_ACROSS_FILESYSTEM=1. It fails closed: any outcome except git’s “not a git repository” error (success, dubious ownership, a spawn failure) stops the export.
eval_bench_runs: one row per recorded benchmark run. Insert once, never updated, never expired. No column is redacted: a row holds scores and case ids, no code text. bench separates the public synthetic corpus (blind-review-mechanical) from runs on an exported private corpus (blind-review-private).
| Column | Type / default | Meaning |
|---|---|---|
bench_run_id | TEXT PRIMARY KEY | Producer bench run id. |
bench | TEXT NOT NULL | Benchmark name (for example blind-review-mechanical). |
recorded_at, ingested_at | INTEGER NOT NULL | Producer record time; hippo write time. |
repo_commit | TEXT NOT NULL | Bench version label (short SHA, optional -dirty); not a linkage key. |
cli_version | TEXT NOT NULL DEFAULT '' | Reviewer CLI version. |
model | TEXT NOT NULL | Model argument. |
model_ids_json | TEXT NOT NULL DEFAULT '[]' | JSON array of served model ids (record field model_ids). |
effort | TEXT, NULL | Requested effort. |
reps, cases | INTEGER NOT NULL | Repetitions and case count. |
cases_sha256, brief_sha256 | TEXT NOT NULL | Hashes of the case set and the brief. |
failed_reviews, tokens | INTEGER NOT NULL DEFAULT 0 | Failed reviews and total tokens. |
scores_json | TEXT NOT NULL | Producer score object as JSON (record field scores), re-serialized with object keys in sorted order; read with json_extract. |
per_case_json | TEXT NOT NULL DEFAULT '[]' | Producer per-case array as JSON (record field per_case), re-serialized with object keys in sorted order. |
Index: idx_eval_bench_runs_bench_recorded_at (bench, recorded_at). The JSON columns compare as parsed JSON on a rerun, with numbers compared by value, so 1 and 1.0 are equal.
Event payload retention
New event writes set payload_expiry = created_at + 7,776,000,000 milliseconds
(90 days). created_at is ingestion time, not the captured event timestamp.
The insert trigger covers direct SQL writers as well as capture and replay.
Existing rows retain NULL expiry until touched. GC reads their effective expiry
with COALESCE(payload_expiry, created_at + 7776000000) and stores it when clearing
the payload. The migration does not extend old payloads by another 90 days.
Payloads expire at the exact millisecond boundary (expiry <= sweep_time).
hippo gc-event-payloads --confirm explicitly authorizes a destructive sweep of
the configured database. Migration, daemon startup and ordinary reads do not
run GC. Do not run this command against a real store without deletion authority.
The sweep uses transactions of at most 100 event updates and a fixed initial
maximum event ID, so concurrent arrivals cannot extend it indefinitely.
GC sets stdout, stderr and env_snapshot_id to NULL. It retains event ledger
rows, commands, metadata, source links and enrichment queues. It clears a shared
snapshot’s env_json only after no event references it. This includes references
from unexpired and pinned events. The snapshot ID and hash remain. Capture can
restore the same snapshot inside the new event’s transaction. Ordinary
knowledge_node_events enrichment links do not pin raw payloads.
The optional node_evidence(ref_type, ref_id) and epitaphs.evidence_refs stores
are not created by this migration. GC recognizes the published evidence citation
namespace from evidence_packets.parse_ref: shell-<id> identifies events,
including Claude tool events. For node_evidence, the corresponding pair is
('shell', <id>); non-NULL epitaph references must be a JSON array of citation strings.
NULL means no evidence references.
The other published prefixes identify non-event stores, not event pins. GC
refuses destruction if a present evidence store has incompatible columns,
reference types, IDs or JSON. Future evidence writers must use the published
citation contract or supply an explicitly reviewed adapter before GC can run.
Each removed output has a SHA-256 receipt with timestamp, payload_expired
reason and a locator such as events/42/stdout. Removing an environment
reference records events/42/env_snapshot_id with the hash of its decimal ID;
this is not proof that shared snapshot bytes were destroyed. Actual snapshot
content removal records env_snapshots/7/env_json with its own content hash.
Receipts and field updates commit atomically. SQL triggers reject receipt
updates, deletes and replacement inserts. A failed batch rolls back both;
earlier committed batches remain and the error reports their count. Reruns
skip already-cleared payloads and do not duplicate receipts.
Runtime Hippo connections explicitly set secure_delete=ON. After a completed
sweep, GC requests wal_checkpoint(TRUNCATE). It reports busy, failure and
non-WAL outcomes separately from successful truncation. The CLI returns an
error for an incomplete checkpoint even though payload updates committed.
Release blocking readers and retry the sweep to retry the checkpoint. Receipts
prove named field removal, not non-existence of the original content. These
operations do not erase surviving derived knowledge, FTS/vectors, backups,
capture logs, fallback files, process memory or copies outside the database.
The same sweep then clears agent-evaluation free text whose payload_expiry
has passed: eval_findings.line_text and claim, then eval_verdicts.note. It
skips every finding whose effective verdict is accepted, and every verdict of
such a finding. eval_payload_pins derives that pin set from eval_verdicts
inside each batch transaction; no pin table is stored, so a later human verdict
that replaces accepted removes the pin at the next sweep. A finding accepted
after its text expired stays without text. All escapes are pinned:
eval_escapes has no expiry and GC does not visit it. Batches hold at most 100
rows per table, a cursor on the primary key moves past pinned rows, and each
cleared field gets a receipt such as eval_findings/<id>/claim or
eval_verdicts/<id>/note in the same transaction. Every other eval column stays,
including path, line, line_hash, verdicts, and commits. The CLI prints the
cleared finding and verdict row counts after the event count.
GC leaves the hippo_kb_events gauge unchanged because event ledger rows remain.
The hippo_kb_stdout_nonempty gauge can fall after a sweep; this is expected,
not lost capture. Both existing gauge names and definitions remain unchanged.
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
| Table | What it holds | Primary writer |
|---|---|---|
events | Shell commands and Claude tool-use events. source_kind distinguishes; probe_tag marks synthetic. | storage.rs::insert_event_at |
sessions | One row per zsh session (start time, hostname, shell, user). | Daemon at session start |
agentic_sessions | Live session store. Supported harnesses and uniqueness constraints are defined in schema.sql. Holds segment-derived summary, tool calls / user prompts JSON, message count, content hashes. | Daemon session ingesters; brain enrichment writers |
agentic_session_facts | One row per (session_id, harness): session-level cost, duration, line counts, title, CLI version, PR links (v28). | claude_session.rs::upsert_session_facts (Claude Code only) |
agentic_session_offsets | Per-file FS-watcher resume state (byte_offset, inode, device). updated_at is the start of the last successful ingest; reconciliation re-ingests a subagent whose sibling meta.json is at least that new. | watch_claude_sessions.rs::process_file |
browser_events | Firefox-extension visits with Readability-extracted main text, dwell, scroll depth. | storage.rs::insert_browser_event |
workflow_runs / _jobs / _annotations / _log_excerpts | GitHub Actions ingest. | gh_poll.rs::run_once |
sha_watchlist | Per-(repo, sha) follow flag for in-flight CI runs. Drives the gh-poller’s “wait for this SHA’s runs to settle” loop. | gh_poll.rs |
lessons / lesson_pending | Graduated recurring failure tips (CI annotations via workflow enrichment; capture alarms via capture_alarm_lessons). | Brain enrichment |
env_snapshots | Shared environment snapshots; see Event payload retention for clearing and restoration. | storage.rs::insert_event_at via upsert_env_snapshot; payload GC |
knowledge_nodes | The synthesized output of enrichment. The content column is a JSON blob (with summary / intent / entities / tool_calls / etc. as inner fields); embed_text and node_type/outcome/tags are real columns. | enrichment.py::write_knowledge_node, claude_sessions.py::write_claude_knowledge_node |
knowledge_node_agentic_sessions | Live session-link table tying knowledge nodes back to their agentic_sessions source rows. | claude_sessions.py::write_claude_knowledge_node |
knowledge_node_events / _browser_events / _workflow_runs / _lessons | Link tables tying knowledge nodes back to their source events. | Same writers as knowledge_nodes |
entities | Extracted identifiers (project, file, tool, service, repo, host, person, concept, domain, env_var). UNIQUE (type, canonical). | enrichment.py::upsert_entities |
event_entities / knowledge_node_entities | Many-to-many links from rows to extracted entities. | Same |
relationships | Directed (source_entity, predicate, target_entity) graph edges. | Brain enrichment |
agentic_enrichment_queue | Live agentic queue, shared across supported harnesses. Each row is a claim ticket with status, priority, retry_count, locked_at, locked_by, referencing agentic_sessions(id). | Daemon writers on insert; brain on claim/complete; watchdog reaper on timeout |
enrichment_queue / browser_enrichment_queue / workflow_enrichment_queue | Per-source queue tables for shell events, browser visits, and workflow runs. Each row is a claim ticket with status, priority, retry_count, locked_at, locked_by. | Daemon on insert; brain on claim/complete; watchdog reaper on timeout |
source_health | Per-source last_event_ts, consecutive_failures, probe_ok, probe_lag_ms. The watchdog’s source of truth. | Daemon (capture path), watchdog (probe results) |
capture_alarms | Watchdog invariant violations. Append-only ledger. | hippo watchdog run |
knowledge_fts | FTS5 virtual table over knowledge_nodes.summary / embed_text / content. | Triggers (auto-synced with knowledge_nodes) |
knowledge_vectors | sqlite-vec virtual table holding 768-dim embedding vectors. | embeddings.py::embed_knowledge_node (Python brain — Rust daemon doesn’t load vec0) |
embed_model_meta | Single-row tracking table for the model that produced the corpus’s vectors. | Same |
eval_review_runs / eval_findings / eval_verdicts / eval_escapes / eval_bench_runs | Agent-evaluation records: review runs, findings, append-only verdicts, escaped defects, benchmark runs (v29). Never indexed or enriched (AP-14). | agent_eval.rs::ingest (hippo ingest agent-eval); eval_linkage.py inserts automatic verdicts; payload GC clears expired text |
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)
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 (shared by supported harnesses)
browser_enrichment_queue ──> browser_events
workflow_enrichment_queue ──> workflow_runs
eval_review_runs ──< eval_findings ──< eval_verdicts
eval_review_runs ··> agentic_sessions (session_id, harness) (soft link, no FK)
eval_escapes, eval_bench_runs (no FKs)
storage.rs::open_db and vector_store.py::open_conn enable PRAGMA foreign_keys.
Migration guarantees
- Single-shot per version. Each migration block in
storage.rs::open_dbruns at most once per database lifetime: the version range guard (if (1..=N).contains(&version)) becomes false afterPRAGMA user_version = N+1lands. - Idempotent on partial-success crash (v8 and later). From v8 onward, every CREATE uses
IF NOT EXISTS; every ALTER goes throughadd_column_if_missingwhich pre-checksPRAGMA table_info; every seed insert usesINSERT OR IGNORE. A daemon that crashes after adding a column but before bumpinguser_versionretries 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 unguardedALTERand the matchingPRAGMA user_versioninside the sameexecute_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 bundlesPRAGMA user_version = Ninto the sameexecute_batchas theCOMMIT(preceded byDROP TABLE/RENAMEfor 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 plusadd_column_if_missingloop) issuePRAGMA user_version = Nin a separateexecute_batchafter 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_keysdiscipline. 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 aPRAGMA foreign_key_checkas a query before the version bump, and turn FKs back on at the end. Other migrations rely on the defaultforeign_keys=ONset byopen_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)"
The daemon and brain expected versions must match; the daemon migrates older databases forward. Python’s direct-read compatibility is defined above. Common causes and fixes:
| Cause | Fix |
|---|---|
Daemon updated, brain not yet — mise run install ran but the brain venv didn’t refresh. | mise run install --clean rebuilds and re-syncs the brain. |
Brain updated, daemon not yet — happens when only brain/ changed in a release. | Same — mise run install --clean. |
| DB at higher version than binaries — you upgraded then downgraded. | Migrations are forward-only. Either upgrade the binaries again, or restore a backup taken before the higher version was applied. |
| DB at lower version than binaries — fresh checkout against an old DB that hasn’t been touched. | Just start the daemon (mise run start); migrations will run forward. |
| DB at v0 — a fresh DB. | Same — daemon will create from scratch via schema.sql. |
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:
- Write the migration block in
storage.rs::open_dbguarded byif (1..=N).contains(&version). - Update
crates/hippo-core/src/schema.sqlso fresh installs match. - Bump
EXPECTED_VERSIONinstorage.rsANDEXPECTED_SCHEMA_VERSIONinbrain/src/hippo_brain/schema_version.pyin the same PR (they must agree). - Bump
[workspace.package].versioninCargo.tomland[project].versioninbrain/pyproject.tomlfor the lockstep release (seedocs/release.md). - Add a row to the changelog table above.
- If your migration touches
entities.typeor 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.