pub const SCHEMA_SQL: &str = "-- kindling SQLite Schema Contract\n-- =================================\n-- Canonical DDL reflecting the state after all migrations (through 005).\n-- Both the TypeScript store (better-sqlite3) and the Rust crate MUST produce\n-- an identical structure when creating a fresh database.\n--\n-- See README.md in this directory for update rules and breaking-change policy.\n\n-- Runtime schema version \u{2014} readable from any SQLite client via:\n-- PRAGMA user_version;\n-- Must match the \"version\" field in version.json.\nPRAGMA user_version = 5;\n\n-- ============================================================================\n-- Core tables\n-- ============================================================================\n\n-- Tracks which migrations have been applied (used by the TypeScript runner).\nCREATE TABLE IF NOT EXISTS schema_migrations (\n version INTEGER PRIMARY KEY,\n name TEXT NOT NULL,\n applied_at INTEGER NOT NULL -- epoch milliseconds\n);\n\n-- Seed migration history so the TypeScript runner (which checks\n-- schema_migrations to determine the current version) does not attempt\n-- to replay migrations against an already-complete schema.\nINSERT OR IGNORE INTO schema_migrations (version, name, applied_at) VALUES\n (1, \'001_init\', 0),\n (2, \'002_fts\', 0),\n (3, \'003_indexes\', 0),\n (4, \'004_denormalize_scopes\', 0),\n (5, \'005_pragma_user_version\', 0);\n\n-- Atomic units of captured context.\nCREATE TABLE IF NOT EXISTS observations (\n id TEXT PRIMARY KEY,\n kind TEXT NOT NULL CHECK(kind IN (\n \'tool_call\',\n \'command\',\n \'file_diff\',\n \'error\',\n \'message\',\n \'node_start\',\n \'node_end\',\n \'node_output\',\n \'node_error\'\n )),\n content TEXT NOT NULL,\n provenance TEXT NOT NULL DEFAULT \'{}\', -- JSON blob\n ts INTEGER NOT NULL, -- epoch milliseconds\n scope_ids TEXT NOT NULL DEFAULT \'{}\', -- JSON blob (legacy, kept for compat)\n redacted INTEGER NOT NULL DEFAULT 0 CHECK(redacted IN (0, 1)),\n -- Denormalized scope columns (migration 004) \u{2014} prefer these over scope_ids\n session_id TEXT,\n repo_id TEXT,\n agent_id TEXT,\n user_id TEXT\n);\n\n-- Bounded units that group observations.\nCREATE TABLE IF NOT EXISTS capsules (\n id TEXT PRIMARY KEY,\n type TEXT NOT NULL CHECK(type IN (\'session\', \'pocketflow_node\')),\n intent TEXT NOT NULL,\n status TEXT NOT NULL CHECK(status IN (\'open\', \'closed\')) DEFAULT \'open\',\n opened_at INTEGER NOT NULL, -- epoch milliseconds\n closed_at INTEGER, -- epoch milliseconds, NULL while open\n scope_ids TEXT NOT NULL DEFAULT \'{}\', -- JSON blob (legacy, kept for compat)\n -- Denormalized scope columns (migration 004)\n session_id TEXT,\n repo_id TEXT,\n agent_id TEXT,\n user_id TEXT\n);\n\n-- Many-to-many relationship between capsules and observations (with ordering).\nCREATE TABLE IF NOT EXISTS capsule_observations (\n capsule_id TEXT NOT NULL,\n observation_id TEXT NOT NULL,\n seq INTEGER NOT NULL, -- ordering within capsule\n PRIMARY KEY (capsule_id, observation_id),\n FOREIGN KEY (capsule_id) REFERENCES capsules(id) ON DELETE CASCADE,\n FOREIGN KEY (observation_id) REFERENCES observations(id) ON DELETE CASCADE\n);\n\n-- AI-generated summaries of closed capsules.\nCREATE TABLE IF NOT EXISTS summaries (\n id TEXT PRIMARY KEY,\n capsule_id TEXT NOT NULL UNIQUE,\n content TEXT NOT NULL,\n confidence REAL NOT NULL CHECK(confidence >= 0.0 AND confidence <= 1.0),\n created_at INTEGER NOT NULL, -- epoch milliseconds\n evidence_refs TEXT NOT NULL DEFAULT \'[]\', -- JSON array of observation IDs\n FOREIGN KEY (capsule_id) REFERENCES capsules(id) ON DELETE CASCADE\n);\n\n-- User-controlled priority content (non-evictable until expired/removed).\nCREATE TABLE IF NOT EXISTS pins (\n id TEXT PRIMARY KEY,\n target_type TEXT NOT NULL CHECK(target_type IN (\'observation\', \'summary\')),\n target_id TEXT NOT NULL,\n reason TEXT,\n created_at INTEGER NOT NULL, -- epoch milliseconds\n expires_at INTEGER, -- epoch milliseconds, NULL = no expiry\n scope_ids TEXT NOT NULL DEFAULT \'{}\', -- JSON blob (legacy, kept for compat)\n -- Denormalized scope columns (migration 004)\n session_id TEXT,\n repo_id TEXT,\n agent_id TEXT,\n user_id TEXT\n);\n\n-- ============================================================================\n-- Full-Text Search (FTS5)\n-- ============================================================================\n-- IMPORTANT: The tokenizer MUST be \'porter unicode61\' in both TypeScript and\n-- Rust implementations. Changing the tokenizer is a BREAKING CHANGE \u{2014} it\n-- invalidates existing FTS indexes and produces different search results.\n-- Porter stemming + Unicode normalization ensures consistent full-text search\n-- across languages and implementations.\n\n-- FTS index over observations.content (external content table).\nCREATE VIRTUAL TABLE IF NOT EXISTS observations_fts USING fts5(\n content,\n content=\'observations\',\n content_rowid=\'rowid\',\n tokenize=\'porter unicode61\'\n);\n\n-- FTS index over summaries.content (external content table).\nCREATE VIRTUAL TABLE IF NOT EXISTS summaries_fts USING fts5(\n content,\n content=\'summaries\',\n content_rowid=\'rowid\',\n tokenize=\'porter unicode61\'\n);\n\n-- ============================================================================\n-- Triggers \u{2014} keep FTS indexes in sync with content tables\n-- ============================================================================\n\n-- observations_fts sync\nCREATE TRIGGER IF NOT EXISTS observations_fts_insert\nAFTER INSERT ON observations\nWHEN NEW.redacted = 0\nBEGIN\n INSERT INTO observations_fts(rowid, content) VALUES (NEW.rowid, NEW.content);\nEND;\n\nCREATE TRIGGER IF NOT EXISTS observations_fts_update\nAFTER UPDATE ON observations\nBEGIN\n INSERT INTO observations_fts(observations_fts, rowid, content) VALUES(\'delete\', OLD.rowid, OLD.content);\n INSERT INTO observations_fts(rowid, content) SELECT NEW.rowid, NEW.content WHERE NEW.redacted = 0;\nEND;\n\nCREATE TRIGGER IF NOT EXISTS observations_fts_delete\nAFTER DELETE ON observations\nBEGIN\n INSERT INTO observations_fts(observations_fts, rowid, content) VALUES(\'delete\', OLD.rowid, OLD.content);\nEND;\n\n-- summaries_fts sync\nCREATE TRIGGER IF NOT EXISTS summaries_fts_insert\nAFTER INSERT ON summaries\nBEGIN\n INSERT INTO summaries_fts(rowid, content) VALUES (NEW.rowid, NEW.content);\nEND;\n\nCREATE TRIGGER IF NOT EXISTS summaries_fts_update\nAFTER UPDATE ON summaries\nBEGIN\n INSERT INTO summaries_fts(summaries_fts, rowid, content) VALUES(\'delete\', OLD.rowid, OLD.content);\n INSERT INTO summaries_fts(rowid, content) VALUES (NEW.rowid, NEW.content);\nEND;\n\nCREATE TRIGGER IF NOT EXISTS summaries_fts_delete\nAFTER DELETE ON summaries\nBEGIN\n INSERT INTO summaries_fts(summaries_fts, rowid, content) VALUES(\'delete\', OLD.rowid, OLD.content);\nEND;\n\n-- ============================================================================\n-- Indexes\n-- ============================================================================\n\n-- Observations\nCREATE INDEX IF NOT EXISTS idx_observations_ts ON observations(ts DESC);\nCREATE INDEX IF NOT EXISTS idx_observations_kind ON observations(kind);\nCREATE INDEX IF NOT EXISTS idx_obs_session_ts ON observations(session_id, ts DESC) WHERE session_id IS NOT NULL;\nCREATE INDEX IF NOT EXISTS idx_obs_repo_ts ON observations(repo_id, ts DESC) WHERE repo_id IS NOT NULL;\n\n-- Capsules\nCREATE INDEX IF NOT EXISTS idx_capsules_opened_at ON capsules(opened_at DESC);\nCREATE INDEX IF NOT EXISTS idx_caps_status_session ON capsules(status, session_id) WHERE session_id IS NOT NULL;\nCREATE INDEX IF NOT EXISTS idx_caps_repo ON capsules(repo_id) WHERE repo_id IS NOT NULL;\n\n-- Capsule-observations\nCREATE INDEX IF NOT EXISTS idx_capsule_observations_capsule ON capsule_observations(capsule_id, seq);\nCREATE INDEX IF NOT EXISTS idx_capsule_observations_observation ON capsule_observations(observation_id);\n\n-- Summaries\nCREATE INDEX IF NOT EXISTS idx_summaries_capsule ON summaries(capsule_id);\nCREATE INDEX IF NOT EXISTS idx_summaries_created_at ON summaries(created_at DESC);\n\n-- Pins\nCREATE INDEX IF NOT EXISTS idx_pins_expires_at ON pins(expires_at) WHERE expires_at IS NOT NULL;\nCREATE INDEX IF NOT EXISTS idx_pins_target ON pins(target_type, target_id);\nCREATE INDEX IF NOT EXISTS idx_pins_session_id ON pins(session_id) WHERE session_id IS NOT NULL;\nCREATE INDEX IF NOT EXISTS idx_pins_repo_id ON pins(repo_id) WHERE repo_id IS NOT NULL;\n";Expand description
Canonical DDL — the state of the schema after all migrations.
Embedded from the vendored copy (crates/kindling-store/schema/schema.sql),
kept in sync with the repo-root canonical schema/schema.sql.