/** * SQLite schema for aidimag. Lives in /.aidimag/memory.db * FTS5 powers Phase 1 search; sqlite-vec embeddings arrive in Phase 2. */ export declare const SCHEMA_VERSION = 13; /** Idempotent migrations for pre-existing DBs (failures = already applied). */ export declare const MIGRATIONS: string[]; /** * v6 guarded migration: SQLite can't ALTER a CHECK constraint, so adding the * TICKET_REF evidence type requires rebuilding the evidence table. Runs once, * gated on the stored schema_version (store.ts). */ export declare const EVIDENCE_REBUILD_V6 = "\nALTER TABLE evidence RENAME TO evidence_v5;\nCREATE TABLE evidence (\n id TEXT PRIMARY KEY,\n memory_id TEXT NOT NULL REFERENCES memories(id) ON DELETE CASCADE,\n type TEXT NOT NULL CHECK (type IN (\n 'COMMIT_REF','TEST_RESULT','EXEC_TRACE','STATIC_CHECK','HUMAN_ATTESTED','TICKET_REF')),\n payload TEXT NOT NULL,\n last_run TEXT,\n result TEXT NOT NULL DEFAULT 'UNKNOWN' CHECK (result IN ('PASS','FAIL','UNKNOWN'))\n);\nINSERT INTO evidence (id, memory_id, type, payload, last_run, result) SELECT id, memory_id, type, payload, last_run, result FROM evidence_v5;\nDROP TABLE evidence_v5;\nCREATE INDEX IF NOT EXISTS idx_evidence_memory ON evidence(memory_id);\n"; /** * v8 guarded rebuild: SQLite can't ALTER a CHECK constraint, so adding the * GUARDRAIL + SKILL memory kinds requires rebuilding the memories and proposals * tables. Rowids are preserved (FTS is a contentless mirror keyed on rowid) and * the FTS index is rebuilt afterwards. Runs once, gated on the stored * schema_version (store.ts), with foreign_keys disabled around it. */ export declare const MEMORIES_REBUILD_V8 = "\nCREATE TABLE memories_v8 (\n id TEXT PRIMARY KEY,\n kind TEXT NOT NULL CHECK (kind IN (\n 'DECISION','CONVENTION','GOTCHA','FAILED_APPROACH',\n 'ARCHITECTURE','INVARIANT','TODO_CONTEXT','GUARDRAIL','SKILL')),\n claim TEXT NOT NULL,\n confidence REAL NOT NULL DEFAULT 0.5,\n status TEXT NOT NULL DEFAULT 'UNVERIFIED' CHECK (status IN (\n 'VERIFIED','UNVERIFIED','STALE','REFUTED')),\n created_by TEXT NOT NULL DEFAULT 'human',\n created_at TEXT NOT NULL,\n verified_at TEXT,\n superseded_by TEXT REFERENCES memories(id),\n updated_at TEXT,\n pinned INTEGER NOT NULL DEFAULT 0,\n guardrail_level TEXT CHECK (guardrail_level IN ('always','ask-first','never'))\n);\nINSERT INTO memories_v8 (rowid, id, kind, claim, confidence, status, created_by,\n created_at, verified_at, superseded_by, updated_at, pinned, guardrail_level)\n SELECT rowid, id, kind, claim, confidence, status, created_by,\n created_at, verified_at, superseded_by, updated_at, pinned, guardrail_level FROM memories;\nDROP TABLE memories;\nALTER TABLE memories_v8 RENAME TO memories;\nINSERT INTO memories_fts(memories_fts) VALUES('rebuild');\n"; export declare const PROPOSALS_REBUILD_V8 = "\nCREATE TABLE proposals_v8 (\n id TEXT PRIMARY KEY,\n kind TEXT NOT NULL CHECK (kind IN (\n 'DECISION','CONVENTION','GOTCHA','FAILED_APPROACH',\n 'ARCHITECTURE','INVARIANT','TODO_CONTEXT','GUARDRAIL','SKILL')),\n claim TEXT NOT NULL,\n paths TEXT NOT NULL DEFAULT '[]',\n symbols TEXT NOT NULL DEFAULT '[]',\n evidence TEXT NOT NULL DEFAULT '[]',\n source TEXT NOT NULL,\n source_ref TEXT,\n rationale TEXT,\n created_at TEXT NOT NULL,\n status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','APPROVED','REJECTED')),\n memory_id TEXT REFERENCES memories(id) ON DELETE SET NULL,\n updated_at TEXT,\n ticket_ref TEXT,\n guardrail_level TEXT CHECK (guardrail_level IN ('always','ask-first','never'))\n);\nINSERT INTO proposals_v8 (id, kind, claim, paths, symbols, evidence, source, source_ref,\n rationale, created_at, status, memory_id, updated_at, ticket_ref, guardrail_level)\n SELECT id, kind, claim, paths, symbols, evidence, source, source_ref,\n rationale, created_at, status, memory_id, updated_at, ticket_ref, guardrail_level FROM proposals;\nDROP TABLE proposals;\nALTER TABLE proposals_v8 RENAME TO proposals;\n"; /** * v9 guarded rebuild: Add new event types (updated, evidence_added, evidence_removed) * to the events table CHECK constraint. SQLite can't ALTER a CHECK constraint. */ export declare const EVENTS_REBUILD_V9 = "\nALTER TABLE events RENAME TO events_v8;\nCREATE TABLE events (\n seq INTEGER PRIMARY KEY AUTOINCREMENT,\n id TEXT NOT NULL UNIQUE,\n type TEXT NOT NULL CHECK (type IN (\n 'memory_created','status_changed','evidence_result',\n 'refuted','superseded','forgotten',\n 'proposal_created','proposal_approved','proposal_rejected',\n 'verification_report','updated','evidence_added','evidence_removed')),\n memory_id TEXT,\n payload TEXT NOT NULL DEFAULT '{}',\n machine TEXT NOT NULL,\n schema_version INTEGER NOT NULL,\n created_at TEXT NOT NULL,\n synced INTEGER NOT NULL DEFAULT 0\n);\nINSERT INTO events (seq, id, type, memory_id, payload, machine, schema_version, created_at, synced) SELECT seq, id, type, memory_id, payload, machine, schema_version, created_at, synced FROM events_v8;\nDROP TABLE events_v8;\nCREATE INDEX IF NOT EXISTS idx_events_synced ON events(synced, seq);\n"; /** * v13 guarded rebuild: Add evidence_signed event type to the events table * CHECK constraint. SQLite can't ALTER a CHECK constraint. */ export declare const EVENTS_REBUILD_V13 = "\nALTER TABLE events RENAME TO events_v12;\nCREATE TABLE events (\n seq INTEGER PRIMARY KEY AUTOINCREMENT,\n id TEXT NOT NULL UNIQUE,\n type TEXT NOT NULL CHECK (type IN (\n 'memory_created','status_changed','evidence_result',\n 'refuted','superseded','forgotten',\n 'proposal_created','proposal_approved','proposal_rejected',\n 'verification_report','updated','evidence_added','evidence_removed',\n 'evidence_signed')),\n memory_id TEXT,\n payload TEXT NOT NULL DEFAULT '{}',\n machine TEXT NOT NULL,\n schema_version INTEGER NOT NULL,\n created_at TEXT NOT NULL,\n synced INTEGER NOT NULL DEFAULT 0\n);\nINSERT INTO events (seq, id, type, memory_id, payload, machine, schema_version, created_at, synced) SELECT seq, id, type, memory_id, payload, machine, schema_version, created_at, synced FROM events_v12;\nDROP TABLE events_v12;\nCREATE INDEX IF NOT EXISTS idx_events_synced ON events(synced, seq);\n"; export declare const SCHEMA_SQL = "\nPRAGMA journal_mode = WAL;\nPRAGMA foreign_keys = ON;\n\nCREATE TABLE IF NOT EXISTS meta (\n key TEXT PRIMARY KEY,\n value TEXT NOT NULL\n);\n\nCREATE TABLE IF NOT EXISTS memories (\n id TEXT PRIMARY KEY,\n kind TEXT NOT NULL CHECK (kind IN (\n 'DECISION','CONVENTION','GOTCHA','FAILED_APPROACH',\n 'ARCHITECTURE','INVARIANT','TODO_CONTEXT','GUARDRAIL','SKILL')),\n claim TEXT NOT NULL,\n confidence REAL NOT NULL DEFAULT 0.5,\n status TEXT NOT NULL DEFAULT 'UNVERIFIED' CHECK (status IN (\n 'VERIFIED','UNVERIFIED','STALE','REFUTED')),\n created_by TEXT NOT NULL DEFAULT 'human',\n created_at TEXT NOT NULL,\n verified_at TEXT,\n superseded_by TEXT REFERENCES memories(id),\n updated_at TEXT,\n pinned INTEGER NOT NULL DEFAULT 0,\n guardrail_level TEXT CHECK (guardrail_level IN ('always','ask-first','never')),\n cloud_synced INTEGER NOT NULL DEFAULT 0,\n cloud_seq INTEGER,\n applies_when TEXT -- JSON string[] of applicability conditions (FAILED_APPROACH)\n);\n\n-- scope: one row per path / symbol a memory applies to\nCREATE TABLE IF NOT EXISTS memory_scopes (\n memory_id TEXT NOT NULL REFERENCES memories(id) ON DELETE CASCADE,\n scope_type TEXT NOT NULL CHECK (scope_type IN ('path','symbol')),\n value TEXT NOT NULL,\n PRIMARY KEY (memory_id, scope_type, value)\n);\n\nCREATE TABLE IF NOT EXISTS evidence (\n id TEXT PRIMARY KEY,\n memory_id TEXT NOT NULL REFERENCES memories(id) ON DELETE CASCADE,\n type TEXT NOT NULL CHECK (type IN (\n 'COMMIT_REF','TEST_RESULT','EXEC_TRACE','STATIC_CHECK','HUMAN_ATTESTED','TICKET_REF')),\n payload TEXT NOT NULL,\n last_run TEXT,\n result TEXT NOT NULL DEFAULT 'UNKNOWN' CHECK (result IN ('PASS','FAIL','UNKNOWN')),\n signature TEXT,\n signed_by TEXT\n);\n\nCREATE TABLE IF NOT EXISTS memory_links (\n from_id TEXT NOT NULL REFERENCES memories(id) ON DELETE CASCADE,\n to_id TEXT NOT NULL REFERENCES memories(id) ON DELETE CASCADE,\n relation TEXT NOT NULL CHECK (relation IN ('supports','contradicts','refines')),\n PRIMARY KEY (from_id, to_id, relation)\n);\n\n-- Full-text search over claims (Phase 1 retrieval)\nCREATE VIRTUAL TABLE IF NOT EXISTS memories_fts USING fts5(\n claim,\n content='memories',\n content_rowid='rowid'\n);\n\nCREATE TRIGGER IF NOT EXISTS memories_ai AFTER INSERT ON memories BEGIN\n INSERT INTO memories_fts(rowid, claim) VALUES (new.rowid, new.claim);\nEND;\n\nCREATE TRIGGER IF NOT EXISTS memories_ad AFTER DELETE ON memories BEGIN\n INSERT INTO memories_fts(memories_fts, rowid, claim) VALUES ('delete', old.rowid, old.claim);\nEND;\n\nCREATE TRIGGER IF NOT EXISTS memories_au AFTER UPDATE OF claim ON memories BEGIN\n INSERT INTO memories_fts(memories_fts, rowid, claim) VALUES ('delete', old.rowid, old.claim);\n INSERT INTO memories_fts(rowid, claim) VALUES (new.rowid, new.claim);\nEND;\n\n-- Phase 2: capture pipeline \u2014 proposed memories awaiting human review\nCREATE TABLE IF NOT EXISTS proposals (\n id TEXT PRIMARY KEY,\n kind TEXT NOT NULL CHECK (kind IN (\n 'DECISION','CONVENTION','GOTCHA','FAILED_APPROACH',\n 'ARCHITECTURE','INVARIANT','TODO_CONTEXT','GUARDRAIL','SKILL')),\n claim TEXT NOT NULL,\n paths TEXT NOT NULL DEFAULT '[]', -- JSON string[]\n symbols TEXT NOT NULL DEFAULT '[]', -- JSON string[]\n evidence TEXT NOT NULL DEFAULT '[]', -- JSON {type,payload}[]\n source TEXT NOT NULL, -- 'commit-miner' | 'session:' | ...\n source_ref TEXT, -- e.g. commit sha\n rationale TEXT, -- why the source thinks this is worth remembering\n created_at TEXT NOT NULL,\n status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','APPROVED','REJECTED')),\n memory_id TEXT REFERENCES memories(id) ON DELETE SET NULL, -- set when approved\n updated_at TEXT,\n ticket_ref TEXT, -- ticket id (e.g. XXX-2100) when known\n guardrail_level TEXT, -- always | ask-first | never (GUARDRAIL proposals)\n cloud_synced INTEGER NOT NULL DEFAULT 0,\n cloud_seq INTEGER,\n applies_when TEXT -- JSON string[] of applicability conditions (FAILED_APPROACH proposals)\n);\n\nCREATE INDEX IF NOT EXISTS idx_proposals_status ON proposals(status);\nCREATE UNIQUE INDEX IF NOT EXISTS idx_proposals_dedupe ON proposals(source, source_ref, claim);\n\n-- Phase 6: sync \u2014 deletions must propagate, so deletes leave tombstones\nCREATE TABLE IF NOT EXISTS tombstones (\n id TEXT NOT NULL, -- deleted row id\n tbl TEXT NOT NULL CHECK (tbl IN ('memories','proposals')),\n deleted_at TEXT NOT NULL,\n PRIMARY KEY (id, tbl)\n);\n\nCREATE INDEX IF NOT EXISTS idx_memories_status ON memories(status);\nCREATE INDEX IF NOT EXISTS idx_memories_kind ON memories(kind);\nCREATE INDEX IF NOT EXISTS idx_memories_cloud_synced ON memories(cloud_synced);\nCREATE INDEX IF NOT EXISTS idx_proposals_cloud_synced ON proposals(cloud_synced);\nCREATE INDEX IF NOT EXISTS idx_scopes_value ON memory_scopes(value);\nCREATE INDEX IF NOT EXISTS idx_evidence_memory ON evidence(memory_id);\n\n-- Passive capture: every MCP memory_search is logged locally. Zero-hit\n-- queries are coverage gaps \u2014 things agents needed but memory couldn't answer.\n-- Surfaced via `dim gaps` and the session briefing; never synced.\nCREATE TABLE IF NOT EXISTS search_log (\n id TEXT PRIMARY KEY,\n query TEXT NOT NULL,\n paths TEXT NOT NULL DEFAULT '[]', -- JSON string[] (scope filter, if any)\n result_count INTEGER NOT NULL,\n source TEXT NOT NULL DEFAULT 'mcp', -- 'mcp' | 'cli'\n created_at TEXT NOT NULL\n);\nCREATE INDEX IF NOT EXISTS idx_search_log_created ON search_log(created_at);\nCREATE INDEX IF NOT EXISTS idx_search_log_hits ON search_log(result_count, created_at);\n\n-- SaaS groundwork: local append-only event log (CLOUD_DESIGN sync model).\n-- Every memory-lifecycle change is recorded here and shipped to the sync\n-- server on `dim sync`; the server aggregates evidence_result events from\n-- multiple machines into consensus confidence.\nCREATE TABLE IF NOT EXISTS events (\n seq INTEGER PRIMARY KEY AUTOINCREMENT,\n id TEXT NOT NULL UNIQUE, -- uuid (idempotent server ingest)\n type TEXT NOT NULL CHECK (type IN (\n 'memory_created','status_changed','evidence_result',\n 'refuted','superseded','forgotten',\n 'proposal_created','proposal_approved','proposal_rejected',\n 'verification_report','updated','evidence_added','evidence_removed',\n 'evidence_signed')),\n memory_id TEXT, -- subject memory/proposal id\n payload TEXT NOT NULL DEFAULT '{}', -- JSON event body\n machine TEXT NOT NULL, -- stable per-machine id\n schema_version INTEGER NOT NULL,\n created_at TEXT NOT NULL,\n synced INTEGER NOT NULL DEFAULT 0 -- 1 once pushed to the server\n);\nCREATE INDEX IF NOT EXISTS idx_events_synced ON events(synced, seq);\n\n-- v11: scratchpad \u2014 session-scoped short-term working memory.\n-- Intermediate findings, plans, and hypotheses for the CURRENT session. Entries\n-- expire automatically (TTL) and are purged on read. Local-only: never synced,\n-- never enters the review queue, never becomes durable memory. Promote anything\n-- worth keeping via `dim remember` / memory_propose.\nCREATE TABLE IF NOT EXISTS scratchpad (\n id TEXT PRIMARY KEY,\n session_id TEXT NOT NULL DEFAULT 'default',\n content TEXT NOT NULL,\n created_by TEXT NOT NULL DEFAULT 'agent',\n created_at TEXT NOT NULL,\n expires_at TEXT NOT NULL\n);\nCREATE INDEX IF NOT EXISTS idx_scratchpad_session ON scratchpad(session_id, created_at);\nCREATE INDEX IF NOT EXISTS idx_scratchpad_expiry ON scratchpad(expires_at);\n\n-- v13: Immutable history \u2014 prevent modification of event data columns.\n-- The events log is the tamper-evident audit trail; once written, event data\n-- must never be modified or removed. The synced column is exempt so that\n-- dim sync can mark events as pushed without triggering the guard.\nCREATE TRIGGER IF NOT EXISTS trg_events_no_update\nBEFORE UPDATE ON events\nWHEN NEW.type <> OLD.type\n OR NEW.id <> OLD.id\n OR NEW.memory_id IS NOT OLD.memory_id\n OR NEW.payload <> OLD.payload\n OR NEW.machine <> OLD.machine\n OR NEW.schema_version <> OLD.schema_version\n OR NEW.created_at <> OLD.created_at\nBEGIN\n SELECT RAISE(ABORT, 'events table is immutable: event data cannot be modified');\nEND;\nCREATE TRIGGER IF NOT EXISTS trg_events_no_delete\nBEFORE DELETE ON events\nBEGIN\n SELECT RAISE(ABORT, 'events table is immutable: DELETE not allowed');\nEND;\n";