# Schema reference (store v23) The live `tuneloop query --schema` is authoritative; this is a checked-in copy, regenerated from the store schema so it stays in sync. ## Tables ```sql CREATE TABLE analyzed_roots ( source TEXT, path TEXT, last_analyzed_at TEXT, PRIMARY KEY (source, path) ); CREATE TABLE annotations ( session_id TEXT, processor TEXT, key TEXT, value TEXT, -- json PRIMARY KEY (session_id, processor, key), FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE ); CREATE TABLE artifact_links ( from_id TEXT, to_id TEXT, relation TEXT, source TEXT, confidence REAL, producer TEXT, PRIMARY KEY (from_id, to_id, relation) ); CREATE TABLE artifacts ( id TEXT PRIMARY KEY, kind TEXT, repo TEXT, ident TEXT, external_id TEXT, source TEXT, title TEXT, owner TEXT, complexity REAL, complexity_basis TEXT, status TEXT, created_at TEXT, completed_at TEXT, parent_artifact_id TEXT, json TEXT, producer TEXT ); CREATE TABLE block_annotations ( session_id TEXT, block_idx INTEGER, processor TEXT, key TEXT, value TEXT, -- json PRIMARY KEY (session_id, block_idx, processor, key), FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE ); CREATE TABLE block_artifacts ( session_id TEXT, block_idx INTEGER, artifact_id TEXT, role TEXT, source TEXT, confidence REAL, producer TEXT, PRIMARY KEY (session_id, block_idx, artifact_id, role), FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE ); CREATE TABLE block_tool ( session_id TEXT, tool_idx INTEGER, -- tool_calls.idx block_idx INTEGER, producer TEXT, PRIMARY KEY (session_id, tool_idx), FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE ); CREATE TABLE block_usage ( session_id TEXT, usage_idx INTEGER, -- usage_facts.idx block_idx INTEGER, producer TEXT, PRIMARY KEY (session_id, usage_idx), FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE ); CREATE TABLE blocks ( session_id TEXT, idx INTEGER, -- 0-based main-thread block ordinal start_seq INTEGER, -- inclusive main-thread seq end_seq INTEGER, -- inclusive boundary_kind TEXT, -- 'user_turn' | 'commit' | 'pr_create' | 'pr_merge' | 'session_end' ts_start TEXT, ts_end TEXT, producer TEXT, PRIMARY KEY (session_id, idx), FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE ); CREATE VIEW cache_classified_turn AS SELECT session_id, idx, ts, repo, model, provider, prev_ctx, reads, input, creates_5m, creates_1h, creates, CASE WHEN reads < prev_ctx * 0.5 THEN 1 ELSE 0 END AS is_miss, -- 2-arg MIN() returns NULL if EITHER arg is NULL; -- safe only because the WHERE guarantees prev_ctx is non-null. MIN(prev_ctx - reads, CASE WHEN creates > 0 THEN creates ELSE input END) AS avoidable_tokens, CAST((julianday(ts) - julianday(prev_ts)) * 86400000 AS INTEGER) AS gap_ms FROM usage_turns WHERE is_sidechain = 0 AND session_cache_tokens > 0 -- provider reports caching at all AND prev_ctx >= 10000 AND new_ctx >= prev_ctx * 0.5; CREATE VIEW cache_miss_event AS SELECT * FROM cache_classified_turn WHERE is_miss = 1; CREATE VIEW capability_invocation AS WITH derived AS ( SELECT t.session_id, t.idx, t.ts, t.is_sidechain, s.source, s.repo, CASE t.action WHEN 'mcp_call' THEN 'mcp' ELSE 'skill' END AS kind, -- Installed unit is the SERVER: text between the 1st and 2nd '__' in -- mcp____. Empty when there's no 2nd '__' — the substr length -- would go negative, which SQLite reads backwards. CASE t.action WHEN 'mcp_call' THEN CASE WHEN instr(substr(t.name, 6), '__') > 0 THEN substr(t.name, 6, instr(substr(t.name, 6), '__') - 1) ELSE '' END ELSE t.name END AS name FROM tool_calls t JOIN sessions s ON s.id = t.session_id WHERE t.action IN ('mcp_call', 'skill') ) SELECT session_id, idx, ts, is_sidechain, source, repo, kind, name FROM derived WHERE name <> ''; CREATE VIEW capability_usage AS SELECT source, kind, name, repo, COUNT(DISTINCT session_id) AS sessions, -- adoption breadth, not chattiness COUNT(*) AS calls, -- strftime normalizes any offset to UTC before MIN/MAX, so mixed timestamp -- formats can't produce a wrong "latest" — fixed at source, not per comparison. MIN(strftime('%Y-%m-%dT%H:%M:%SZ', ts)) AS first_invoked_at, MAX(strftime('%Y-%m-%dT%H:%M:%SZ', ts)) AS last_invoked_at FROM capability_invocation WHERE is_sidechain = 0 -- a subagent runs against its own context; we ask what the GROUP BY source, kind, name, repo; CREATE VIEW compaction_event AS SELECT session_id, idx, ts, repo, model, prev_occupancy, occupancy, prev_occupancy - occupancy AS dropped_tokens FROM usage_turns WHERE is_sidechain = 0 AND prev_occupancy >= 100000 AND occupancy <= prev_occupancy * 0.4; CREATE TABLE detector_runs ( id INTEGER PRIMARY KEY, -- rowid alias; ascending = run order (the log is never deleted from) detector TEXT NOT NULL, -- detector name (e.g. 'permission-friction') version INTEGER NOT NULL, -- detector version at time of run (for cache invalidation) status TEXT NOT NULL, -- 'ok' | 'error' model TEXT, -- LLM model that ran it (NULL for S-tier / non-LLM / error runs) in_tokens INTEGER, -- LLM input tokens (NULL for S-tier) out_tokens INTEGER, -- LLM output tokens (NULL for S-tier) cost_usd REAL, -- LLM cost in USD (NULL for S-tier) ran_at TEXT NOT NULL -- ISO timestamp of this run ); CREATE TABLE detector_session_runs ( detector TEXT NOT NULL, session_id TEXT NOT NULL, content_hash TEXT NOT NULL, ran_at TEXT NOT NULL, PRIMARY KEY (detector, session_id), FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE ); CREATE TABLE environment_snapshots ( source TEXT NOT NULL, -- harness, e.g. 'claude-code' scope TEXT NOT NULL, -- 'global' | 'project' scope_key TEXT NOT NULL, -- '_global' for global; repo root for project category TEXT NOT NULL, -- 'settings' | 'mcp' | 'agents' | 'skills' | 'instructions' content_hash TEXT NOT NULL, -- hash of snapshot_json (change-detection key) snapshot_json TEXT NOT NULL, -- redacted, allowlisted payload for this category captured_at TEXT NOT NULL, -- when this state was recorded (change timeline) last_observed_at TEXT NOT NULL, -- most recent analyze run that confirmed this state PRIMARY KEY (source, scope, scope_key, category, captured_at) ); CREATE TABLE facets ( key TEXT PRIMARY KEY, label TEXT, type TEXT, source TEXT, col TEXT, base TEXT, multi INTEGER, roles TEXT, -- json array of 'chart' | 'filter' | 'detail' producer TEXT ); CREATE TABLE files_index ( repo TEXT, path TEXT, session_id TEXT, producer TEXT, PRIMARY KEY (repo, path, session_id) ); CREATE TABLE fix_marker_sightings ( session_id TEXT NOT NULL, insight_id TEXT NOT NULL, -- as claimed by the marker seq INTEGER NOT NULL, -- main-thread event seq of the sighted user turn turn_at TEXT NOT NULL, -- EVENT time: transcript timestamp of that turn — the -- "fix applied" date; measurement windows key off this matched_at TEXT, -- processing time; NULL = reconcile hasn't resolved this -- against an existing insight yet. Re-stamped on -- re-scans — never use for cycle-scoping (use turn_at) PRIMARY KEY (session_id, insight_id), FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE ); CREATE TABLE insight_evidence ( insight_id TEXT NOT NULL, session_id TEXT NOT NULL, turn_idx INTEGER NOT NULL DEFAULT -1, -- Optional per-occurrence human-readable note (e.g. the recurring-themes event -- description). Lets the insight detail show WHAT happened at each evidence -- turn, not just a session chip. Generic — any detector may set it. note TEXT, added_at TEXT NOT NULL, PRIMARY KEY (insight_id, session_id, turn_idx), FOREIGN KEY(insight_id) REFERENCES insights(id) ON DELETE CASCADE, FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE ); CREATE TABLE insight_state_log ( insight_id TEXT NOT NULL, from_state TEXT, to_state TEXT NOT NULL, at TEXT NOT NULL ); CREATE TABLE insights ( id TEXT PRIMARY KEY, detector TEXT NOT NULL, signal_key TEXT NOT NULL, repo TEXT NOT NULL DEFAULT '_unknown', severity TEXT NOT NULL, state TEXT NOT NULL DEFAULT 'surfaced', title TEXT NOT NULL, description TEXT NOT NULL, count INTEGER NOT NULL, fix_type TEXT, fix_label TEXT, fix_content TEXT, recommendation TEXT, -- one-line action shown beneath the signal in the list first_seen_at TEXT NOT NULL, last_seen_at TEXT NOT NULL, state_changed_at TEXT, detector_version INTEGER NOT NULL, UNIQUE(detector, repo, signal_key) ); CREATE TABLE kitchen_sink_verdict ( session_id TEXT PRIMARY KEY, is_kitchen_sink INTEGER NOT NULL, -- 1 = mixed unrelated work, 0 = coherent split_block_idx INTEGER, -- block where the 2nd objective begins (NULL if coherent) split_seq INTEGER, -- that block's opening main-thread seq (the evidence pointer) reason TEXT, -- the LLM's one-sentence explanation model TEXT, -- model that produced the verdict detector_version INTEGER NOT NULL, -- detector version at judge time judged_at TEXT NOT NULL, FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE ); CREATE TABLE measures ( key TEXT PRIMARY KEY, label TEXT, source TEXT, expr TEXT, agg TEXT, base TEXT, format TEXT, producer TEXT ); CREATE TABLE meta ( key TEXT PRIMARY KEY, value TEXT ); CREATE TABLE outcomes ( session_id TEXT, type TEXT, artifact_id TEXT, ts TEXT, producer TEXT, FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE ); CREATE TABLE processor_runs ( session_id TEXT, processor TEXT, version INTEGER, input_hash TEXT, model TEXT, status TEXT, in_tokens INTEGER, out_tokens INTEGER, cost_usd REAL, ran_at TEXT, -- Set to 1 by a user link/unlink to force the next analyze to re-run this -- processor; reset to 0 (the default) whenever persistResult rewrites the row. invalidated INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (session_id, processor) ); CREATE TABLE session_artifacts ( session_id TEXT, artifact_id TEXT, role TEXT, source TEXT, confidence REAL, producer TEXT, PRIMARY KEY (session_id, artifact_id, role), FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE ); CREATE TABLE sessions ( id TEXT PRIMARY KEY, session_id TEXT, source TEXT, provider TEXT, title TEXT, -- native adapter title (may be absent) first_prompt TEXT, -- full opening human prompt; display-title fallback when no native/enriched title repo TEXT, branch TEXT, cwd TEXT, started_at TEXT, ended_at TEXT, n_turns INTEGER, n_tool_calls INTEGER, models TEXT, -- json array tok_input INTEGER, tok_output INTEGER, -- Cache creation split by TTL: DISJOINT, so total cache-write is the sum of the -- two. They bill at different rates (1h = 2x input, 5m = 1.25x), which is why -- cost needs them apart. Sources with no TTL report everything as 5m. tok_cache_create_5m INTEGER, tok_cache_create_1h INTEGER, tok_cache_read INTEGER, cost_usd REAL, price_table_version TEXT, content_hash TEXT, parse_version INTEGER, analyzed_at TEXT ); CREATE TABLE theme ( id TEXT PRIMARY KEY, -- permanent; INSERT OR IGNORE, never renamed (a rename mislabels past members) label TEXT NOT NULL, description TEXT, -- one-sentence gap explanation (minted with the label); feeds merge + fix prompts type TEXT NOT NULL, -- frozen enum: re-steer|context-supply|tool-gap|rework|preference|other remedy TEXT, -- remedy-class hint: add_doc|add_skill|add_tool|model_or_prompt|none repo TEXT, -- NULL = global (spans repos); set only when the LLM marks a theme project-specific source TEXT NOT NULL DEFAULT 'derived', first_seen TEXT NOT NULL, resolved INTEGER NOT NULL DEFAULT 0, -- 1 keeps the theme in the extraction feed after its insight resolved -- LLM-generated fix, cached + hash-gated on the theme's occurrence set so a -- quiet re-analyze reuses it. fix_type is the InsightInput fix.type the LLM chose. fix_type TEXT, fix_content TEXT, fix_recommendation TEXT, -- one-line action for the fix, cached beside it (regenerated with the fix) fix_hash TEXT -- hash of the occurrence set the current fix was generated from ); CREATE TABLE theme_events ( session_id TEXT NOT NULL, idx INTEGER NOT NULL, -- 0-based within the session's extraction turn_seq INTEGER, -- main-thread seq of the user turn (evidence pointer); NULL if unknown type TEXT NOT NULL, trigger TEXT NOT NULL, -- unprompted|after_tool_error|after_review|agent_stated description TEXT NOT NULL, -- one abstract sentence; recurrences read the same theme_id TEXT, -- NULL = event survived but its proposed label was junk (topicless) added_at TEXT NOT NULL, -- when this row was written (analyze-run wall clock, bookkeeping) occurred_at TEXT, -- timestamp of the user message itself (the real friction moment); drives first/last-seen PRIMARY KEY (session_id, idx), FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE ); CREATE TABLE tool_call_commands ( session_id TEXT, idx INTEGER, -- the tool_calls row this came from: (session_id, idx) seq INTEGER, -- first appearance in the command chain (repeats collapse) binary TEXT, -- 'git', './deploy.sh', ... PRIMARY KEY (session_id, idx, seq), FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE ); CREATE TABLE tool_calls ( session_id TEXT, idx INTEGER, name TEXT, action TEXT, ok INTEGER, is_error INTEGER, error_category TEXT, error_message TEXT, -- Did a SUCCESSFUL retrieval call return nothing? NULL when the question doesn't -- apply: a non-retrieval action (silent output is success for a write/shell) or a -- failed call (already counted as an error). See core/empty-result.ts. result_empty INTEGER, target_path TEXT, command TEXT, is_sidechain INTEGER, ts TEXT, duration_ms INTEGER, PRIMARY KEY (session_id, idx), FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE ); CREATE TABLE tool_error_advice ( source TEXT, kind TEXT, -- 'mcp' | 'builtin' name TEXT, -- server name, tool name, or shell binary diagnosis TEXT, -- what is going wrong, in a sentence or two snippet TEXT, -- paste-ready agent-instructions block ('' when the pass declined) evidence_hash TEXT NOT NULL, model TEXT, generated_at TEXT, PRIMARY KEY (source, kind, name) ); CREATE TABLE usage_facts ( session_id TEXT, idx INTEGER, model TEXT, is_sidechain INTEGER, ts TEXT, tok_input INTEGER, tok_output INTEGER, tok_cache_create_5m INTEGER, -- disjoint from _1h; see sessions tok_cache_create_1h INTEGER, tok_cache_read INTEGER, cost_usd REAL, PRIMARY KEY (session_id, idx), FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE ); CREATE VIEW usage_turns AS WITH live AS ( SELECT u.session_id, u.idx, u.ts, u.model, u.is_sidechain, s.provider, s.started_at, COALESCE(NULLIF(s.repo,''), NULLIF(s.cwd,''), '_unknown') AS repo, COALESCE(u.tok_input,0) AS input, COALESCE(u.tok_output,0) AS output, COALESCE(u.tok_cache_create_5m,0) AS creates_5m, COALESCE(u.tok_cache_create_1h,0) AS creates_1h, COALESCE(u.tok_cache_read,0) AS reads FROM usage_facts u JOIN sessions s ON s.id = u.session_id -- All-zero rows aren't API calls (content flushes, ingest-deduped repeats). Dropped -- HERE so the LAGs below mean "previous real turn", matching the JS loops' `continue` -- BEFORE prevOcc/prevCtx update. WHERE COALESCE(u.tok_input,0) + COALESCE(u.tok_output,0) + COALESCE(u.tok_cache_create_5m,0) + COALESCE(u.tok_cache_create_1h,0) + COALESCE(u.tok_cache_read,0) > 0 ) SELECT session_id, idx, ts, model, provider, repo, is_sidechain, started_at, input, output, creates_5m, creates_1h, reads, creates_5m + creates_1h AS creates, -- Occupancy excludes output: the reply isn't part of the prompt. input + reads + creates_5m + creates_1h AS occupancy, -- What the next warm turn would read back: reads plus what THIS turn cached -- (creates, or billed input under read-discount caching). reads + CASE WHEN creates_5m + creates_1h > 0 THEN creates_5m + creates_1h ELSE input END AS new_ctx, LAG(input + reads + creates_5m + creates_1h) OVER w AS prev_occupancy, LAG(reads + CASE WHEN creates_5m + creates_1h > 0 THEN creates_5m + creates_1h ELSE input END) OVER w AS prev_ctx, LAG(ts) OVER w AS prev_ts, -- Unordered window p → whole-session max. MAX(...) OVER w (ordered) would be a -- RUNNING max, failing early turns that later turns pass. MAX(creates_5m + creates_1h + reads) OVER p AS session_cache_tokens FROM live -- Partition on (session_id, is_sidechain): sidechain rows share the session and -- interleave by idx; without it a subagent turn becomes a main turn's "previous". -- All subagents share is_sidechain=1 — no per-agent series here. WINDOW w AS (PARTITION BY session_id, is_sidechain ORDER BY idx), p AS (PARTITION BY session_id, is_sidechain); CREATE TABLE user_link_overrides ( session_id TEXT, artifact_id TEXT, action TEXT, -- 'reject' created_at TEXT, PRIMARY KEY (session_id, artifact_id), FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE ); ``` ## Facets (chartable/filterable dimensions) - `harness` — enum, session grain → `source` - `repo` — string, session grain - `model` — enum, usage grain - `skill` — string, tool-call grain → `name`, base `action = 'skill'` - `error_category` — enum, tool-call grain, base `error_category IS NOT NULL` ## Measures (aggregations) - `cost` — sum(`u.cost_usd`) at usage grain - `tokens` — sum(`u.tok_input + u.tok_output + u.tok_cache_create_5m + u.tok_cache_create_1h + u.tok_cache_read`) at usage grain - `sessions` — count_distinct(`s.id`) at session grain - `success_rate` — rate(`EXISTS (SELECT 1 FROM outcomes o WHERE o.session_id = s.id AND o.type = 'session_success')`) at session grain - `tool_calls` — count(`1`) at tool-call grain - `error_count` — sum(`t.is_error`) at tool-call grain - `error_rate` — rate(`t.is_error`) at tool-call grain