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

FactValue
Current versionPRAGMA user_version in the authoritative schema below
Authoritative schemacrates/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 mismatchcrates/hippo-daemon/src/schema_handshake.rs
Migration runnercrates/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:

VersionSummaryTables/columnsOperational impact
v1Initial schema.events (without envelope_id), sessions.Baseline. Every fresh install since v1 lands here first then migrates forward.
v2Event dedup.events.envelope_id column + unique index.Browser visits and Claude tool events get a stable dedup key, enabling replay-safe ingest.
v3Claude 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).
v4Browser 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.
v5GitHub 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.
v6Full-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.
v7Multi-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.
v8Capture-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).
v9Watchdog 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.
v10Watcher 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.
v11Auto-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.
v12Claude 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.
v13env_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 ↗.
v14Agentic 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.
v15Codex 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 ↗.
v16Cursor 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 ↗.
v17Segment-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.
v18Repoint 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.
v19Add 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.
v20Auto-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.
v21Auto-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.
v22Watcher 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.
v26Event 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.
v28Agent-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.
v29Agent-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.

ColumnType / defaultMeaning
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_modelTEXT, NULLClaude 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.
effortTEXT, NULLReasoning 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)INTEGERToken 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_tokensINTEGER NOT NULL DEFAULT 0Uncached input tokens for the segment.
output_tokensINTEGER NOT NULL DEFAULT 0Output tokens for the segment (Codex: includes reasoning tokens).
cache_read_tokensINTEGER NOT NULL DEFAULT 0Prompt-cache read tokens for the segment.
cache_creation_tokensINTEGER NOT NULL DEFAULT 0Prompt-cache creation tokens for the segment.
spawn_depthINTEGER, NULLClaude Code subagent nesting depth (spawnDepth in meta.json).
parent_tool_use_idTEXT, NULLtoolUseId of the parent’s Agent call that spawned this subagent.
subagent_finishedINTEGER, NULLfinished from meta.json as 0/1; NULL when absent.
hook_runsINTEGER NOT NULL DEFAULT 0Claude Code: sum of hookCount over the segment’s system records with subtype = "stop_hook_summary".
hook_errorsINTEGER NOT NULL DEFAULT 0Claude Code: total length of the hookErrors arrays of those records. The error text is not stored.
prevented_continuationsINTEGER NOT NULL DEFAULT 0Claude Code: number of those records with preventedContinuation == true. stopReason text is not stored.
hook_names_jsonTEXT 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_jsonTEXT 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:

Hook and permission rules (Claude Code only; other harnesses keep the defaults):

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.

ColumnTypeMeaning
session_id, harnessTEXT NOT NULL, primary keySame keys as agentic_sessions; harness = 'claude-code'.
cost_usdREALtotalCostUSD of the last cost-state record in the file.
duration_msINTEGERtotalDuration of the last cost-state record (ms).
tool_duration_msINTEGERtotalToolDuration of the last cost-state record (ms).
lines_added, lines_removedINTEGERtotalLinesAdded and totalLinesRemoved of the last cost-state record.
titleTEXTLast aiTitle of the file, redacted. NULL when the file has none.
cli_versionTEXTTop-level version of the last assistant record.
pr_links_jsonTEXT 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_atINTEGER NOT NULLTime 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:

eval_review_runs: one row per review invocation. Insert once, never updated. No column is redacted.

ColumnType / defaultMeaning
run_idTEXT PRIMARY KEYProducer run id.
agent, modeTEXT NOT NULLReviewing agent, the registry agent name (for example blind-reviewer) and mode (for example mechanical).
requested_modelTEXT, NULLModel argument (for example haiku).
model_idTEXT NOT NULL DEFAULT ''Sorted, comma-joined served model ids.
effortTEXT, NULLRequested reasoning effort.
cli_versionTEXT NOT NULL DEFAULT ''Reviewer CLI version.
repo, repo_identityTEXT NOT NULL, TEXTLocal path and derived slug.
base, base_sha, head_shaTEXT NOT NULLBase ref name, that ref resolved at review start, and HEAD at review start.
input_sha256TEXT NOT NULLSHA-256 of the sorted per-unit input hashes.
units_total, units_failedINTEGER NOT NULL DEFAULT 0Reviewed units and failed units; a failed review stays distinct from a clean one.
input_tokens, output_tokens, cache_read_tokens, cache_creation_tokensINTEGER NOT NULL DEFAULT 0Reviewer token sums over all units.
session_id, harnessTEXT, NULLOrchestrating 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_atINTEGER NOT NULLReview 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.

ColumnType / defaultMeaning
finding_idTEXT PRIMARY KEYProducer finding id (includes the run id).
run_idTEXT NOT NULL REFERENCES eval_review_runs (run_id)Parent run; ingest rejects an unknown run as unknown_parent.
repo, repo_identityTEXT NOT NULL, TEXTRepeated from the run so linkage can use idx_eval_findings_repo_path without a join.
path, lineTEXT NOT NULL, INTEGER NOT NULLFlagged location.
sideTEXT NOT NULL CHECK (side IN ('+', '-'))Diff side.
line_textTEXT, redactedRaw source line at the reviewed revision, redacted. NULL after expiry or when absent.
line_hashTEXTlower_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, confidenceTEXT NOT NULLhigh, medium, or low.
categoryTEXT NOT NULL DEFAULT 'unknown'Reviewer category label.
claimTEXT, redactedThe finding text. NULL after expiry.
logged_at, ingested_at, payload_expiryINTEGER NOT NULLProducer 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.

ColumnType / defaultMeaning
verdict_idTEXT PRIMARY KEYProducer verdict id.
finding_idTEXT NOT NULL REFERENCES eval_findings (finding_id)Parent finding; ingest rejects an unknown finding as unknown_parent.
verdictTEXT NOT NULLVerdict value.
commit_shaTEXT, NULLCommit that addressed the finding or that a human cites (record field commit).
noteTEXT, redactedRationale. On linkage-job verdicts: NULL (observed fix), the inferred: marker, and/or PR <url>, .... NULL after expiry.
autoINTEGER NOT NULL CHECK (auto IN (0, 1))Machine (1) or human (0) verdict.
decided_at, ingested_at, payload_expiryINTEGER NOT NULLDecision 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.

ColumnType / defaultMeaning
escape_idTEXT PRIMARY KEYProducer escape id.
repo, repo_identityTEXT NOT NULL, TEXTLocal path and derived slug.
path, lineTEXT NOT NULL, INTEGER NOT NULLDefect location.
fix_commitTEXT, NULLCommit that fixed the defect.
noteTEXT NOT NULL, redactedDescription of the escape.
recorded_at, ingested_atINTEGER NOT NULLProducer 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.

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).

ColumnType / defaultMeaning
bench_run_idTEXT PRIMARY KEYProducer bench run id.
benchTEXT NOT NULLBenchmark name (for example blind-review-mechanical).
recorded_at, ingested_atINTEGER NOT NULLProducer record time; hippo write time.
repo_commitTEXT NOT NULLBench version label (short SHA, optional -dirty); not a linkage key.
cli_versionTEXT NOT NULL DEFAULT ''Reviewer CLI version.
modelTEXT NOT NULLModel argument.
model_ids_jsonTEXT NOT NULL DEFAULT '[]'JSON array of served model ids (record field model_ids).
effortTEXT, NULLRequested effort.
reps, casesINTEGER NOT NULLRepetitions and case count.
cases_sha256, brief_sha256TEXT NOT NULLHashes of the case set and the brief.
failed_reviews, tokensINTEGER NOT NULL DEFAULT 0Failed reviews and total tokens.
scores_jsonTEXT NOT NULLProducer score object as JSON (record field scores), re-serialized with object keys in sorted order; read with json_extract.
per_case_jsonTEXT 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

TableWhat it holdsPrimary writer
eventsShell commands and Claude tool-use events. source_kind distinguishes; probe_tag marks synthetic.storage.rs::insert_event_at
sessionsOne row per zsh session (start time, hostname, shell, user).Daemon at session start
agentic_sessionsLive 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_factsOne 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_offsetsPer-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_eventsFirefox-extension visits with Readability-extracted main text, dwell, scroll depth.storage.rs::insert_browser_event
workflow_runs / _jobs / _annotations / _log_excerptsGitHub Actions ingest.gh_poll.rs::run_once
sha_watchlistPer-(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_pendingGraduated recurring failure tips (CI annotations via workflow enrichment; capture alarms via capture_alarm_lessons).Brain enrichment
env_snapshotsShared environment snapshots; see Event payload retention for clearing and restoration.storage.rs::insert_event_at via upsert_env_snapshot; payload GC
knowledge_nodesThe 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_sessionsLive 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 / _lessonsLink tables tying knowledge nodes back to their source events.Same writers as knowledge_nodes
entitiesExtracted identifiers (project, file, tool, service, repo, host, person, concept, domain, env_var). UNIQUE (type, canonical).enrichment.py::upsert_entities
event_entities / knowledge_node_entitiesMany-to-many links from rows to extracted entities.Same
relationshipsDirected (source_entity, predicate, target_entity) graph edges.Brain enrichment
agentic_enrichment_queueLive 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_queuePer-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_healthPer-source last_event_ts, consecutive_failures, probe_ok, probe_lag_ms. The watchdog’s source of truth.Daemon (capture path), watchdog (probe results)
capture_alarmsWatchdog invariant violations. Append-only ledger.hippo watchdog run
knowledge_ftsFTS5 virtual table over knowledge_nodes.summary / embed_text / content.Triggers (auto-synced with knowledge_nodes)
knowledge_vectorssqlite-vec virtual table holding 768-dim embedding vectors.embeddings.py::embed_knowledge_node (Python brain — Rust daemon doesn’t load vec0)
embed_model_metaSingle-row tracking table for the model that produced the corpus’s vectors.Same
eval_review_runs / eval_findings / eval_verdicts / eval_escapes / eval_bench_runsAgent-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

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:

CauseFix
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:

  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