migrations.ts

/**
 * Schema migration system for SecureContext.
 *
 * DESIGN:
 * - Each migration has a unique integer ID and is applied exactly once.
 * - Applied migrations are recorded in a `schema_migrations` table.
 * - Each migration runs inside a transaction — if it fails, the DB rolls back
 *   cleanly. No partial migrations ever land in the DB.
 * - Migrations are idempotent: re-running a failed migration is safe.
 * - New migrations are added to the MIGRATIONS array; existing ones are never edited.
 *
 * USAGE:
 *   import { runMigrations } from "./migrations.js";
 *   runMigrations(db); // call once after openDb()
 */

import { DatabaseSync } from "node:sqlite";

export interface Migration {
  id: number;
  description: string;
  up: (db: DatabaseSync) => void;
}

export const MIGRATIONS: Migration[] = [

  // ── v0.6.0 migrations ────────────────────────────────────────────────────

  {
    id: 1,
    description: "Add source_type to knowledge FTS5 via source_meta table",
    up: (db) => {
      db.exec(`
        CREATE TABLE IF NOT EXISTS source_meta (
          source      TEXT    PRIMARY KEY,
          source_type TEXT    NOT NULL DEFAULT 'internal',
          created_at  TEXT    NOT NULL
        );
      `);
    },
  },

  {
    id: 2,
    description: "Add working_memory table with agent_id namespacing and eviction index",
    up: (db) => {
      // Create with agent_id if table doesn't exist yet
      db.exec(`
        CREATE TABLE IF NOT EXISTS working_memory (
          id         INTEGER PRIMARY KEY AUTOINCREMENT,
          key        TEXT    NOT NULL,
          value      TEXT    NOT NULL,
          importance INTEGER NOT NULL DEFAULT 3,
          agent_id   TEXT    NOT NULL DEFAULT 'default',
          created_at TEXT    NOT NULL,
          UNIQUE(key, agent_id)
        );
        CREATE INDEX IF NOT EXISTS idx_wm_evict
          ON working_memory(agent_id, importance ASC, created_at ASC);
      `);
      // Add agent_id to existing tables upgrading from v0.5.0 (safe: silently ignored if already present)
      try { db.exec(`ALTER TABLE working_memory ADD COLUMN agent_id TEXT NOT NULL DEFAULT 'default'`); } catch {}
    },
  },

  {
    id: 3,
    description: "Add model_name and dimensions columns to embeddings for version tracking",
    up: (db) => {
      // SQLite doesn't support ADD COLUMN with constraints on existing tables easily.
      // We create the table fresh if it doesn't exist, or add columns if it does.
      db.exec(`
        CREATE TABLE IF NOT EXISTS embeddings (
          source      TEXT    PRIMARY KEY,
          vector      BLOB    NOT NULL,
          model_name  TEXT    NOT NULL DEFAULT 'unknown',
          dimensions  INTEGER NOT NULL DEFAULT 0,
          created_at  TEXT    NOT NULL
        );
      `);
      // Try to add columns to existing tables (safe: fails silently if already present)
      try { db.exec(`ALTER TABLE embeddings ADD COLUMN model_name TEXT NOT NULL DEFAULT 'unknown'`); } catch {}
      try { db.exec(`ALTER TABLE embeddings ADD COLUMN dimensions INTEGER NOT NULL DEFAULT 0`); } catch {}
    },
  },

  {
    id: 4,
    description: "Add retention_tier column to source_meta for tiered content expiry",
    up: (db) => {
      db.exec(`
        CREATE TABLE IF NOT EXISTS source_meta (
          source         TEXT    PRIMARY KEY,
          source_type    TEXT    NOT NULL DEFAULT 'internal',
          retention_tier TEXT    NOT NULL DEFAULT 'internal',
          created_at     TEXT    NOT NULL
        );
      `);
      // Add retention_tier to existing source_meta if upgrading from v0.5.0
      try { db.exec(`ALTER TABLE source_meta ADD COLUMN retention_tier TEXT NOT NULL DEFAULT 'internal'`); } catch {}
    },
  },

  {
    id: 5,
    description: "Add rate_limits table for persistent per-project fetch budget",
    up: (db) => {
      db.exec(`
        CREATE TABLE IF NOT EXISTS rate_limits (
          project_hash TEXT    NOT NULL,
          date         TEXT    NOT NULL,
          fetch_count  INTEGER NOT NULL DEFAULT 0,
          PRIMARY KEY (project_hash, date)
        );
      `);
    },
  },

  {
    id: 6,
    description: "Add db_stats view for zc_status tool",
    up: (db) => {
      db.exec(`
        CREATE TABLE IF NOT EXISTS db_meta (
          key   TEXT PRIMARY KEY,
          value TEXT NOT NULL
        );
        INSERT OR IGNORE INTO db_meta(key, value) VALUES ('schema_version', '6');
        INSERT OR REPLACE INTO db_meta(key, value) VALUES ('created_at', datetime('now'));
      `);
    },
  },

  {
    id: 7,
    description: "Add project_meta table for cross-project search labels",
    up: (db) => {
      db.exec(`
        CREATE TABLE IF NOT EXISTS project_meta (
          key   TEXT PRIMARY KEY,
          value TEXT NOT NULL
        );
      `);
    },
  },

  // ── v0.7.0 migrations ────────────────────────────────────────────────────

  {
    id: 8,
    description: "Add broadcasts table for A2A shared coordination channel",
    up: (db) => {
      db.exec(`
        CREATE TABLE IF NOT EXISTS broadcasts (
          id         INTEGER PRIMARY KEY AUTOINCREMENT,
          type       TEXT    NOT NULL
                             CHECK(type IN ('ASSIGN','STATUS','PROPOSED','DEPENDENCY','MERGE','REJECT','REVISE')),
          agent_id   TEXT    NOT NULL DEFAULT 'default',
          task       TEXT    NOT NULL DEFAULT '',
          files      TEXT    NOT NULL DEFAULT '[]',
          state      TEXT    NOT NULL DEFAULT '',
          summary    TEXT    NOT NULL DEFAULT '',
          depends_on TEXT    NOT NULL DEFAULT '[]',
          reason     TEXT    NOT NULL DEFAULT '',
          importance INTEGER NOT NULL DEFAULT 3,
          created_at TEXT    NOT NULL
        );
        CREATE INDEX IF NOT EXISTS idx_bc_type       ON broadcasts(type);
        CREATE INDEX IF NOT EXISTS idx_bc_agent      ON broadcasts(agent_id);
        CREATE INDEX IF NOT EXISTS idx_bc_created_at ON broadcasts(created_at DESC);
      `);
    },
  },

  // ── v0.7.1 migrations ────────────────────────────────────────────────────

  {
    id: 9,
    description: "Purge legacy SHA256 channel key hashes — scrypt upgrade required (security fix)",
    up: (db) => {
      // v0.7.0 stored the channel key as plain SHA256(key) with no salt — not a KDF.
      // This is vulnerable to offline brute force: ~10B guesses/sec on a GPU.
      // v0.7.1 replaces SHA256 with scrypt (N=65536, r=8, p=1, 256-bit random salt).
      // New format: "scrypt:v1:{N}:{r}:{p}:{salt_hex}:{hash_hex}"
      //
      // This migration deletes any stored key that is NOT in the new scrypt format.
      // Effect: users who had a channel key configured must re-run set_key once.
      // This is a deliberate, secure breaking change — SHA256 hashes must not be trusted.
      db.exec(`
        DELETE FROM project_meta
        WHERE key = 'zc_channel_key_hash'
          AND value NOT LIKE 'scrypt:v1:%'
      `);
    },
  },

  // ── v0.8.0 migrations ────────────────────────────────────────────────────

  {
    id: 10,
    description: "v0.8.0: agent_sessions RBAC table + hash chain columns on broadcasts + L0/L1 tiers on source_meta",
    up: (db) => {
      // Agent session registry (Chapter 6 session tokens + Chapter 14 RBAC)
      db.exec(`
        CREATE TABLE IF NOT EXISTS agent_sessions (
          token_id    TEXT    PRIMARY KEY,
          agent_id    TEXT    NOT NULL,
          role        TEXT    NOT NULL CHECK(role IN ('orchestrator','developer','marketer','researcher','worker')),
          token_hmac  TEXT    NOT NULL,
          issued_at   TEXT    NOT NULL,
          expires_at  TEXT    NOT NULL,
          revoked     INTEGER NOT NULL DEFAULT 0
        );
        CREATE INDEX IF NOT EXISTS idx_as_agent ON agent_sessions(agent_id, revoked);
      `);

      // Hash chain columns (Chapter 13 Biba integrity chain)
      try { db.exec(`ALTER TABLE broadcasts ADD COLUMN session_token_id TEXT NOT NULL DEFAULT ''`); } catch {}
      try { db.exec(`ALTER TABLE broadcasts ADD COLUMN prev_hash TEXT NOT NULL DEFAULT 'genesis'`); } catch {}
      try { db.exec(`ALTER TABLE broadcasts ADD COLUMN row_hash TEXT NOT NULL DEFAULT ''`); } catch {}
      try { db.exec(`ALTER TABLE broadcasts ADD COLUMN acked_at TEXT`); } catch {}

      // L0/L1 tier columns (tiered context loading)
      try { db.exec(`ALTER TABLE source_meta ADD COLUMN l0_summary TEXT NOT NULL DEFAULT ''`); } catch {}
      try { db.exec(`ALTER TABLE source_meta ADD COLUMN l1_summary TEXT NOT NULL DEFAULT ''`); } catch {}
    },
  },

  {
    id: 11,
    description: "Expand broadcasts type CHECK to include LAUNCH_ROLE and RETIRE_ROLE for on-demand agent spawning (COALESCE-safe)",
    up: (db) => {
      // SQLite cannot ALTER a CHECK constraint — must recreate the table.
      // Copy data, drop old, create new with expanded CHECK, restore data.
      //
      // v0.10.3 fix: pre-v0.7.0 broadcasts tables had no NOT NULL constraints,
      // so existing rows can contain NULLs in columns that ARE NOT NULL in the
      // new schema. A naive `INSERT INTO broadcasts_new SELECT * FROM broadcasts`
      // fails with "NOT NULL constraint failed: broadcasts_new.task" on any DB
      // with legacy rows. Use explicit column list + COALESCE to coerce NULLs
      // to the new-schema defaults.
      db.exec(`
        CREATE TABLE IF NOT EXISTS broadcasts_new (
          id               INTEGER PRIMARY KEY AUTOINCREMENT,
          type             TEXT    NOT NULL
                                   CHECK(type IN ('ASSIGN','STATUS','PROPOSED','DEPENDENCY','MERGE','REJECT','REVISE','LAUNCH_ROLE','RETIRE_ROLE')),
          agent_id         TEXT    NOT NULL DEFAULT 'default',
          task             TEXT    NOT NULL DEFAULT '',
          files            TEXT    NOT NULL DEFAULT '[]',
          state            TEXT    NOT NULL DEFAULT '',
          summary          TEXT    NOT NULL DEFAULT '',
          depends_on       TEXT    NOT NULL DEFAULT '[]',
          reason           TEXT    NOT NULL DEFAULT '',
          importance       INTEGER NOT NULL DEFAULT 3,
          created_at       TEXT    NOT NULL,
          session_token_id TEXT    NOT NULL DEFAULT '',
          prev_hash        TEXT    NOT NULL DEFAULT 'genesis',
          row_hash         TEXT    NOT NULL DEFAULT '',
          acked_at         TEXT
        );
        INSERT INTO broadcasts_new (
          id, type, agent_id, task, files, state, summary, depends_on, reason,
          importance, created_at, session_token_id, prev_hash, row_hash, acked_at
        )
        SELECT
          id,
          COALESCE(type,             'STATUS'),
          COALESCE(agent_id,         'default'),
          COALESCE(task,             ''),
          COALESCE(files,            '[]'),
          COALESCE(state,            ''),
          COALESCE(summary,          ''),
          COALESCE(depends_on,       '[]'),
          COALESCE(reason,           ''),
          COALESCE(importance,       3),
          COALESCE(created_at,       strftime('%Y-%m-%dT%H:%M:%fZ','now')),
          COALESCE(session_token_id, ''),
          COALESCE(prev_hash,        'genesis'),
          COALESCE(row_hash,         ''),
          acked_at
        FROM broadcasts;
        DROP TABLE broadcasts;
        ALTER TABLE broadcasts_new RENAME TO broadcasts;
        CREATE INDEX IF NOT EXISTS idx_bc_type  ON broadcasts(type);
        CREATE INDEX IF NOT EXISTS idx_bc_agent ON broadcasts(agent_id);
      `);
    },
  },

  {
    id: 12,
    description: "v0.10.0 Harness Engineering: project_card, session_read_log, tool_output_digest",
    up: (db) => {
      // ── project_card ─────────────────────────────────────────────────────────
      // Per-project "card" — the 500-token orientation summary returned by
      // zc_project_card(). Singleton row (CHECK(id=1)): each project DB describes
      // ITS OWN project. Fields are opaque TEXT so the agent/operator controls
      // what goes in. hot_files is a JSON array of top-N frequently-edited paths.
      db.exec(`
        CREATE TABLE IF NOT EXISTS project_card (
          id          INTEGER PRIMARY KEY CHECK(id = 1),
          stack       TEXT    NOT NULL DEFAULT '',
          layout      TEXT    NOT NULL DEFAULT '',
          state       TEXT    NOT NULL DEFAULT '',
          gotchas     TEXT    NOT NULL DEFAULT '',
          hot_files   TEXT    NOT NULL DEFAULT '[]',
          updated_at  TEXT    NOT NULL
        );
      `);

      // ── session_read_log ─────────────────────────────────────────────────────
      // Per-session file-read log. Powers the PreToolUse Read dedup hook:
      // before a Read fires, the hook queries this table — if the path is
      // already present for the current session, block and force the agent
      // to use zc_file_summary / zc_search instead. Session boundary = a
      // SessionStart event, which wipes rows for the previous session_id.
      db.exec(`
        CREATE TABLE IF NOT EXISTS session_read_log (
          session_id  TEXT NOT NULL,
          path        TEXT NOT NULL,
          read_at     TEXT NOT NULL,
          PRIMARY KEY (session_id, path)
        );
        CREATE INDEX IF NOT EXISTS idx_srl_session ON session_read_log(session_id);
      `);

      // ── tool_output_digest ───────────────────────────────────────────────────
      // Bash-output archive. PostToolUse bash hook summarizes long outputs and
      // stores them here (plus a full-content row in `knowledge` for FTS).
      // hash = sha256(cmd + stdout) — dedup identical re-runs.
      // summary kept compact for injection back into agent context.
      // full_ref = the `source` key in the knowledge table (FTS-searchable).
      db.exec(`
        CREATE TABLE IF NOT EXISTS tool_output_digest (
          hash        TEXT    PRIMARY KEY,
          command     TEXT    NOT NULL,
          summary     TEXT    NOT NULL,
          exit_code   INTEGER NOT NULL,
          full_ref    TEXT    NOT NULL,
          created_at  TEXT    NOT NULL
        );
        CREATE INDEX IF NOT EXISTS idx_tod_cmd ON tool_output_digest(command, created_at DESC);
      `);
    },
  },

  {
    id: 13,
    description: "v0.11.0 Sprint 1: tool_calls table — per-tool-call telemetry (highest-resolution cost data)",
    up: (db) => {
      // Per-tool-call telemetry. The highest-resolution source of truth for
      // cost / latency / outcome attribution. All aggregations (per-task,
      // per-session, per-role, per-skill, per-model) roll up from this table
      // via SQL views — never duplicate-store the rolled-up values.
      //
      // Hash-chained for tamper detection (per §15.4 Sprint 1 + §15.5):
      //   row_hash = HMAC-SHA256(machine_secret, prev_hash || canonical(row))
      // An attacker with DB write access cannot forge valid row_hash without
      // the machine secret — silent log manipulation is detectable.
      db.exec(`
        CREATE TABLE IF NOT EXISTS tool_calls (
          id             INTEGER PRIMARY KEY AUTOINCREMENT,  -- monotonic, used by chain ordering
          call_id        TEXT    NOT NULL UNIQUE,            -- UUID, externally addressable
          session_id     TEXT    NOT NULL,           -- Claude Code session ID
          agent_id       TEXT    NOT NULL,           -- e.g. "RevClear-developer"
          project_hash   TEXT    NOT NULL,           -- SHA256(projectPath)[:16]
          task_id        TEXT,                       -- nullable; from broadcast or skill
          skill_id       TEXT,                       -- nullable; if invoked under a skill
          tool_name      TEXT    NOT NULL,           -- e.g. "mcp__zc-ctx__zc_file_summary"
          model          TEXT    NOT NULL,           -- e.g. "claude-opus-4-7"
          input_tokens   INTEGER NOT NULL DEFAULT 0,
          output_tokens  INTEGER NOT NULL DEFAULT 0,
          cached_tokens  INTEGER NOT NULL DEFAULT 0,
          cost_usd       REAL    NOT NULL DEFAULT 0,
          cost_known     INTEGER NOT NULL DEFAULT 1, -- 0 if pricing unknown / tampered
          latency_ms     INTEGER NOT NULL DEFAULT 0,
          status         TEXT    NOT NULL DEFAULT 'ok',  -- ok | error | timeout
          error_class    TEXT,                       -- transient | permission | logic | unknown
          ts             TEXT    NOT NULL,           -- ISO 8601
          prev_hash      TEXT    NOT NULL DEFAULT 'genesis',
          row_hash       TEXT    NOT NULL DEFAULT '',
          trace_id       TEXT                        -- cross-log correlation
        );
        CREATE INDEX IF NOT EXISTS idx_tc_session   ON tool_calls(session_id, ts);
        CREATE INDEX IF NOT EXISTS idx_tc_task      ON tool_calls(task_id) WHERE task_id IS NOT NULL;
        CREATE INDEX IF NOT EXISTS idx_tc_skill     ON tool_calls(skill_id) WHERE skill_id IS NOT NULL;
        CREATE INDEX IF NOT EXISTS idx_tc_role      ON tool_calls(agent_id, model);
        CREATE INDEX IF NOT EXISTS idx_tc_tool_name ON tool_calls(tool_name, ts);
        CREATE INDEX IF NOT EXISTS idx_tc_ts        ON tool_calls(ts DESC);
        CREATE INDEX IF NOT EXISTS idx_tc_trace     ON tool_calls(trace_id) WHERE trace_id IS NOT NULL;
      `);

      // Pre-aggregated SQL views for common cost-attribution queries.
      // SQLite views are computed on demand (no materialization); query speed
      // is fine at our scale (~1k rows/day per active project).
      db.exec(`
        CREATE VIEW IF NOT EXISTS v_session_cost AS
          SELECT
            session_id,
            agent_id,
            COUNT(*) AS calls,
            SUM(input_tokens) AS input_tokens,
            SUM(output_tokens) AS output_tokens,
            SUM(cost_usd) AS cost_usd,
            MIN(ts) AS started_at,
            MAX(ts) AS last_call_at
          FROM tool_calls
          GROUP BY session_id, agent_id;

        CREATE VIEW IF NOT EXISTS v_task_cost AS
          SELECT
            task_id,
            COUNT(*) AS calls,
            SUM(input_tokens) AS input_tokens,
            SUM(output_tokens) AS output_tokens,
            SUM(cost_usd) AS cost_usd
          FROM tool_calls
          WHERE task_id IS NOT NULL
          GROUP BY task_id;

        CREATE VIEW IF NOT EXISTS v_role_cost AS
          SELECT
            agent_id,
            model,
            COUNT(*) AS calls,
            SUM(cost_usd) AS cost_usd,
            AVG(latency_ms) AS avg_latency_ms
          FROM tool_calls
          GROUP BY agent_id, model;

        CREATE VIEW IF NOT EXISTS v_tool_cost AS
          SELECT
            tool_name,
            model,
            COUNT(*) AS calls,
            SUM(cost_usd) AS cost_usd,
            SUM(input_tokens) AS input_tokens,
            SUM(output_tokens) AS output_tokens,
            AVG(latency_ms) AS avg_latency_ms
          FROM tool_calls
          GROUP BY tool_name, model;
      `);
    },
  },

  {
    id: 14,
    description: "v0.11.0 Sprint 1: outcomes table — deferred outcome tags joined to actions",
    up: (db) => {
      // Joined to tool_calls or other ref tables via (ref_type, ref_id).
      // Outcomes resolve LATER than the action they describe (e.g. a tool
      // call from 09:00 might get a "shipped" outcome at 14:00 when the
      // commit is verified). The temporal disconnect is intentional.
      //
      // Also hash-chained for tamper detection of the learning signal
      // (an attacker manipulating outcomes could poison the future
      // mutation engine — chain prevents silent forgery).
      db.exec(`
        CREATE TABLE IF NOT EXISTS outcomes (
          id             INTEGER PRIMARY KEY AUTOINCREMENT,  -- monotonic, used by chain ordering
          outcome_id     TEXT    NOT NULL UNIQUE,            -- UUID, externally addressable
          ref_type       TEXT    NOT NULL,           -- "tool_call" | "task" | "skill_run" | "session"
          ref_id         TEXT    NOT NULL,           -- FK into the referenced table
          outcome_kind   TEXT    NOT NULL,           -- shipped | reverted | accepted | rejected
                                                     -- | sufficient | insufficient | errored
          signal_source  TEXT    NOT NULL,           -- git_commit | user_prompt | follow_up | manual
          score_delta    REAL,                       -- nullable; how this changed parent score
          confidence     REAL    NOT NULL DEFAULT 1.0,  -- 0-1; lower for inferred outcomes
          evidence       TEXT,                       -- JSON: structured supporting evidence
          resolved_at    TEXT    NOT NULL,           -- ISO 8601 (when determined, not when action occurred)
          prev_hash      TEXT    NOT NULL DEFAULT 'genesis',
          row_hash       TEXT    NOT NULL DEFAULT ''
        );
        CREATE INDEX IF NOT EXISTS idx_o_ref      ON outcomes(ref_type, ref_id);
        CREATE INDEX IF NOT EXISTS idx_o_kind     ON outcomes(outcome_kind, resolved_at);
        CREATE INDEX IF NOT EXISTS idx_o_resolved ON outcomes(resolved_at DESC);

        -- Per-tool-call outcome rollup (most useful query: "which tool calls
        -- had a positive outcome?" → joins to tool_calls via call_id)
        CREATE VIEW IF NOT EXISTS v_tool_call_outcomes AS
          SELECT
            tc.call_id,
            tc.session_id,
            tc.tool_name,
            tc.cost_usd,
            o.outcome_kind,
            o.signal_source,
            o.confidence,
            o.resolved_at
          FROM tool_calls tc
          LEFT JOIN outcomes o
            ON o.ref_type = 'tool_call' AND o.ref_id = tc.call_id;
      `);
    },
  },

  {
    id: 15,
    description: "v0.11.0 Sprint 1: learnings table — structured mirror of dispatcher's JSONL learnings/",
    up: (db) => {
      // Mirror of <project>/learnings/{metrics,decisions,failures,...}.jsonl
      // populated by the PostToolUse `learnings-indexer.mjs` hook on every
      // write to those files.
      //
      // The JSONL files remain canonical (cat-able, grep-able by humans);
      // this table provides the query power (cross-project aggregation,
      // outcome correlation, pattern mining for the Sprint 2 mutation engine).
      //
      // Idempotency: dedup by (project_hash, source_path, source_line). The
      // indexer hook can safely re-run without creating duplicate rows.
      db.exec(`
        CREATE TABLE IF NOT EXISTS learnings (
          learning_id    TEXT    PRIMARY KEY,        -- UUID
          project_hash   TEXT    NOT NULL,           -- SHA256(projectPath)[:16]
          category       TEXT    NOT NULL,           -- metric | decision | failure | insight | experiment
          payload        TEXT    NOT NULL,           -- the JSON line (verbatim from JSONL)
          source_path    TEXT    NOT NULL,           -- e.g. "learnings/failures.jsonl"
          source_line    INTEGER,                    -- line number in source (for dedup)
          ts             TEXT    NOT NULL,           -- write timestamp
          UNIQUE (project_hash, source_path, source_line)
        );
        CREATE INDEX IF NOT EXISTS idx_l_project_cat ON learnings(project_hash, category, ts);
        CREATE INDEX IF NOT EXISTS idx_l_category    ON learnings(category, ts DESC);
      `);
    },
  },

  // ── v0.14.0 migrations ────────────────────────────────────────────────

  {
    id: 16,
    description: "v0.14.0: provenance column on working_memory (EXTRACTED|INFERRED|AMBIGUOUS|UNKNOWN)",
    up: (db) => {
      // Per Chin & Older 2011 Ch6 + Ch7 ('speaks-for' formalism): every claim
      // should carry its trust chain. Provenance flags the source's epistemic
      // status so downstream consumers can downweight INFERRED facts when
      // stakes are high (e.g. mutation engine ranking).
      //
      // Values:
      //   EXTRACTED  — read directly from a primary source (file, AST, git)
      //   INFERRED   — produced by an LLM or similarity heuristic
      //   AMBIGUOUS  — multiple plausible readings, user/agent should review
      //   UNKNOWN    — legacy rows from before v0.14.0 (default for migration)
      //
      // Stored as TEXT with a CHECK constraint so insert errors fail loud.
      // Defensive: idempotent — if provenance already exists (re-migration
      // attempt), no-op.
      const tbl = db.prepare(
        `SELECT name FROM sqlite_master WHERE type='table' AND name='working_memory'`
      ).get();
      if (!tbl) return;
      const cols = db.prepare(`PRAGMA table_info(working_memory)`).all() as Array<{ name: string }>;
      if (cols.some((c) => c.name === "provenance")) return;
      db.exec(`
        ALTER TABLE working_memory ADD COLUMN provenance TEXT NOT NULL DEFAULT 'UNKNOWN'
          CHECK (provenance IN ('EXTRACTED', 'INFERRED', 'AMBIGUOUS', 'UNKNOWN'));
        CREATE INDEX IF NOT EXISTS idx_wm_provenance ON working_memory(provenance, created_at);
      `);
    },
  },

  {
    id: 17,
    description: "v0.14.0: provenance column on source_meta (file-summary trust tier)",
    up: (db) => {
      // source_meta holds L0/L1 file summaries. AST-extracted summaries
      // (Phase B) should be tagged EXTRACTED — they're deterministic.
      // LLM-summarized files are INFERRED. Truncated-only fallback is
      // AMBIGUOUS (no semantic interpretation, just a slice).
      //
      // Defensive: source_meta may not exist on legacy fixtures that
      // skipped migration 1. In that case, no-op — the column will be
      // added when source_meta is eventually created.
      const tbl = db.prepare(
        `SELECT name FROM sqlite_master WHERE type='table' AND name='source_meta'`
      ).get();
      if (!tbl) return;
      // Don't re-ALTER if column already present (idempotent)
      const cols = db.prepare(`PRAGMA table_info(source_meta)`).all() as Array<{ name: string }>;
      if (cols.some((c) => c.name === "provenance")) return;
      db.exec(`
        ALTER TABLE source_meta ADD COLUMN provenance TEXT NOT NULL DEFAULT 'UNKNOWN'
          CHECK (provenance IN ('EXTRACTED', 'INFERRED', 'AMBIGUOUS', 'UNKNOWN'));
        CREATE INDEX IF NOT EXISTS idx_src_provenance ON source_meta(provenance);
      `);
    },
  },

  {
    id: 18,
    description: "v0.15.0 §8.1: structured ASSIGN broadcast columns (acceptance_criteria, complexity, file_ownership, dependencies, required_skills, estimated_tokens)",
    up: (db) => {
      // Per HARNESS_EVOLUTION_PLAN.md §8.1: extend ASSIGN broadcasts with
      // structured fields so dispatcher (Sprint 3 work-stealing queue) can
      // route by complexity, enforce file ownership, and resolve task
      // dependencies. All NULLABLE — backward-compatible with existing ASSIGN
      // broadcasts that don't provide them.
      const tbl = db.prepare(
        `SELECT name FROM sqlite_master WHERE type='table' AND name='broadcasts'`
      ).get();
      if (!tbl) return;
      const cols = db.prepare(`PRAGMA table_info(broadcasts)`).all() as Array<{ name: string }>;
      const have = new Set(cols.map((c) => c.name));
      // Each ALTER is independent so partial-migration on a previously-failed
      // run can resume cleanly.
      if (!have.has("acceptance_criteria"))      db.exec(`ALTER TABLE broadcasts ADD COLUMN acceptance_criteria TEXT`);
      if (!have.has("complexity_estimate"))      db.exec(`ALTER TABLE broadcasts ADD COLUMN complexity_estimate INTEGER`);
      if (!have.has("file_ownership_exclusive")) db.exec(`ALTER TABLE broadcasts ADD COLUMN file_ownership_exclusive TEXT`);
      if (!have.has("file_ownership_read_only")) db.exec(`ALTER TABLE broadcasts ADD COLUMN file_ownership_read_only TEXT`);
      if (!have.has("task_dependencies"))        db.exec(`ALTER TABLE broadcasts ADD COLUMN task_dependencies TEXT`);
      if (!have.has("required_skills"))          db.exec(`ALTER TABLE broadcasts ADD COLUMN required_skills TEXT`);
      if (!have.has("estimated_tokens"))         db.exec(`ALTER TABLE broadcasts ADD COLUMN estimated_tokens INTEGER`);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_b_complexity ON broadcasts(complexity_estimate, type)`);
    },
  },

  {
    id: 19,
    description: "v0.15.0 §8.6 T3.2: MAC-style classification labels on outcomes (public|internal|confidential|restricted)",
    up: (db) => {
      // Per HARNESS_EVOLUTION_PLAN.md §8.6 T3.2 + Chin & Older 2011 Ch5+Ch13:
      // outcomes.evidence may contain inferred-from-user-message data
      // (sentiment classifier, follow-up resolver). Classification labels
      // let consumers filter rows when querying:
      //   public/internal → readable by any agent on this project
      //   confidential    → readable by registered agents on this project
      //   restricted      → readable ONLY by created_by_agent_id
      //
      // SQLite enforces the read filter at the application layer (no RLS).
      // Postgres RLS policy ships with the Postgres backend (v0.16.0).
      //
      // Defensive: idempotent + handles missing outcomes table.
      const tbl = db.prepare(
        `SELECT name FROM sqlite_master WHERE type='table' AND name='outcomes'`
      ).get();
      if (!tbl) return;
      const cols = db.prepare(`PRAGMA table_info(outcomes)`).all() as Array<{ name: string }>;
      const have = new Set(cols.map((c) => c.name));
      if (!have.has("classification")) {
        db.exec(`ALTER TABLE outcomes ADD COLUMN classification TEXT NOT NULL DEFAULT 'internal'
                 CHECK (classification IN ('public', 'internal', 'confidential', 'restricted'))`);
      }
      if (!have.has("created_by_agent_id")) {
        // NULL allowed for legacy rows + non-restricted entries
        db.exec(`ALTER TABLE outcomes ADD COLUMN created_by_agent_id TEXT`);
      }
      db.exec(`CREATE INDEX IF NOT EXISTS idx_o_classification ON outcomes(classification, created_by_agent_id)`);
    },
  },

  {
    id: 20,
    description: "v0.18.0 Sprint 2: skills table — versioned hash-protected skill registry",
    up: (db) => {
      // Each skill is a (name, version, scope) tuple with HMAC-protected body.
      // Soft-delete via archived_at lets the mutation engine version-bump
      // without losing history. UNIQUE active row ensures only one
      // (name, scope) is "live" at a time.
      db.exec(`
        CREATE TABLE IF NOT EXISTS skills (
          skill_id        TEXT PRIMARY KEY,
          name            TEXT NOT NULL,
          version         TEXT NOT NULL,
          scope           TEXT NOT NULL,
          description     TEXT NOT NULL,
          frontmatter     TEXT NOT NULL,           -- JSON-serialized SkillFrontmatter
          body            TEXT NOT NULL,
          body_hmac       TEXT NOT NULL,
          source_path     TEXT,
          promoted_from   TEXT,
          created_at      TEXT NOT NULL,
          archived_at     TEXT,
          archive_reason  TEXT
        );
      `);
      db.exec(`CREATE UNIQUE INDEX IF NOT EXISTS idx_skills_active ON skills(name, scope) WHERE archived_at IS NULL;`);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_skills_name_scope ON skills(name, scope);`);
    },
  },

  {
    id: 21,
    description: "v0.18.0 Sprint 2: skill_runs — execution telemetry per skill invocation",
    up: (db) => {
      // Each invocation of a skill produces one row. outcome_score is the
      // composite (accuracy + cost + speed) used by the mutation engine
      // to rank candidates. failure_trace captures the structured failure
      // shape so the mutator has signal to work with.
      db.exec(`
        CREATE TABLE IF NOT EXISTS skill_runs (
          run_id         TEXT PRIMARY KEY,
          skill_id       TEXT NOT NULL,
          session_id     TEXT NOT NULL,
          task_id        TEXT,
          inputs         TEXT NOT NULL,           -- JSON
          outcome_score  REAL,
          total_cost     REAL,
          total_tokens   INTEGER,
          duration_ms    INTEGER,
          status         TEXT NOT NULL CHECK(status IN ('succeeded','failed','timeout')),
          failure_trace  TEXT,
          ts             TEXT NOT NULL
        );
      `);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_sr_skill_ts ON skill_runs(skill_id, ts);`);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_sr_status ON skill_runs(status, ts);`);
    },
  },

  {
    id: 22,
    description: "v0.18.0 Sprint 2: skill_mutations — proposal + replay + promotion ledger",
    up: (db) => {
      // Each candidate produced by the mutation engine gets a row.
      // candidate_hmac proves the body wasn't modified between proposal
      // and replay (RT-S2-09). promoted=true rows have promoted_to_skill_id
      // pointing at the new active row in skills.
      db.exec(`
        CREATE TABLE IF NOT EXISTS skill_mutations (
          mutation_id           TEXT PRIMARY KEY,
          parent_skill_id       TEXT NOT NULL,
          candidate_body        TEXT NOT NULL,
          candidate_hmac        TEXT NOT NULL,
          proposed_by           TEXT NOT NULL,
          judged_by             TEXT,
          judge_score           REAL,
          judge_rationale       TEXT,
          replay_score          REAL,
          promoted              INTEGER NOT NULL DEFAULT 0,  -- 0/1 boolean
          promoted_to_skill_id  TEXT,
          created_at            TEXT NOT NULL,
          resolved_at           TEXT
        );
      `);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_sm_parent ON skill_mutations(parent_skill_id, created_at);`);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_sm_promoted ON skill_mutations(promoted, created_at);`);
    },
  },

  {
    id: 23,
    description: "v0.18.1: skill_promotion_queue — operator-gated cross-project → global promotion ledger",
    up: (db) => {
      // L2 of the two-tier improvement loop. Cron (or manual) inserts
      // candidates surfaced by findGlobalPromotionCandidates with
      // status='pending'. Operator runs zc_skill_pending_promotions to
      // see them, then zc_skill_approve_promotion / zc_skill_reject_promotion.
      // Approved promotions atomically export+import to global scope.
      db.exec(`
        CREATE TABLE IF NOT EXISTS skill_promotion_queue (
          candidate_skill_id  TEXT NOT NULL,
          proposed_target     TEXT NOT NULL DEFAULT 'global',
          surfaced_at         TEXT NOT NULL,
          surfaced_by         TEXT NOT NULL CHECK(surfaced_by IN ('cron','manual')),
          best_avg            REAL,
          global_avg          REAL,
          project_count       INTEGER,
          status              TEXT NOT NULL DEFAULT 'pending'
                              CHECK(status IN ('pending','approved','rejected','superseded')),
          decided_at          TEXT,
          decided_by          TEXT,
          decision_rationale  TEXT,
          PRIMARY KEY (candidate_skill_id, proposed_target)
        );
      `);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_spq_status ON skill_promotion_queue(status, surfaced_at);`);
    },
  },

  {
    id: 24,
    description: "v0.18.1: mutation_results — side-channel for full-fidelity mutation candidate bodies (option-b architecture)",
    up: (db) => {
      // Mutation candidate bodies can be large (5+ markdown bodies per result,
      // each potentially many KB). Storing them in `broadcasts.summary` would
      // (a) blow the 1000-char sanitize cap and (b) bloat every zc_recall_context
      // call with multi-KB rows. Instead, the body lives here, and the broadcast
      // carries a tiny pointer (mutation_id + result_id + bodies_hash) for
      // tamper-evident reference. Pattern mirrors how PostBash hook archives
      // tool_outputs to KB and stores only a pointer.
      //
      // bodies_hash is SHA-256 of the canonical bodies JSON. The broadcast
      // includes the same hash, so consumers can verify the side-channel row
      // has not been tampered with relative to what was originally announced.
      db.exec(`
        CREATE TABLE IF NOT EXISTS mutation_results (
          result_id        TEXT PRIMARY KEY,
          mutation_id      TEXT NOT NULL,
          skill_id         TEXT NOT NULL,
          project_hash     TEXT NOT NULL,
          proposer_model   TEXT,
          proposer_role    TEXT,
          candidate_count  INTEGER NOT NULL,
          best_score       REAL,
          bodies           TEXT NOT NULL,
          bodies_hash      TEXT NOT NULL,
          headline         TEXT,
          created_at       TEXT NOT NULL,
          consumed_at      TEXT,
          consumed_by      TEXT
        );
      `);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_mres_mutation ON mutation_results(mutation_id, created_at DESC);`);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_mres_skill ON mutation_results(skill_id, created_at DESC);`);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_mres_project ON mutation_results(project_hash, created_at DESC);`);
    },
  },

  {
    id: 25,
    description: "v0.18.2 Sprint 2.6: operator review columns on mutation_results + skill_runs (retry-cap, auto-reassign, decision audit)",
    up: (db) => {
      // SQLite ALTER TABLE doesn't support multiple ADD COLUMN in one statement,
      // and re-running migrations should be idempotent (the migrations harness
      // wraps each in a transaction and skips already-applied IDs, so we don't
      // have to guard the ADDs themselves — but defensive try/catch keeps the
      // transaction alive if a column was manually added during dev).
      const safeAdd = (table: string, col: string, def: string) => {
        try { db.exec(`ALTER TABLE ${table} ADD COLUMN ${col} ${def}`); } catch { /* column exists */ }
      };
      // mutation_results — operator decision + originating task context for auto-reassign
      safeAdd("mutation_results", "original_task_id",       "TEXT");
      safeAdd("mutation_results", "original_role",          "TEXT");
      safeAdd("mutation_results", "consumed_decision",      "TEXT"); // 'approved' | 'rejected' | NULL
      safeAdd("mutation_results", "picked_candidate_index", "INTEGER");
      // skill_runs — retry-cap safeguard. When a worker runs a skill that was
      // just promoted (task payload had retry_after_promotion=true), it sets
      // this flag. The L1 trigger then SKIPS auto-mutation for runs flagged
      // this way — preventing infinite mutate→approve→fail→mutate loops.
      safeAdd("skill_runs", "was_retry_after_promotion", "INTEGER NOT NULL DEFAULT 0");
      db.exec(`CREATE INDEX IF NOT EXISTS idx_mres_pending ON mutation_results(project_hash, consumed_at, created_at DESC);`);
    },
  },

  {
    id: 26,
    description: "v0.18.4 Sprint 2.7: per-role mutator pools + skill_revisions audit ledger + decision-feedback context",
    up: (db) => {
      const safeAdd = (table: string, col: string, def: string) => {
        try { db.exec(`ALTER TABLE ${table} ADD COLUMN ${col} ${def}`); } catch { /* exists */ }
      };
      // 1) mutation_results: track which pool generated this result. Useful
      //    for analytics ("are mutator-marketing's candidates approved more
      //    than mutator-engineering's?") and for surfacing role-specific
      //    decision history into future mutations.
      safeAdd("mutation_results", "mutator_pool", "TEXT");
      db.exec(`CREATE INDEX IF NOT EXISTS idx_mres_pool ON mutation_results(mutator_pool, created_at DESC);`);

      // 2) skill_revisions: every skill version transition is audit-logged.
      //    Promote (mutation-approved) AND revert events both write here.
      //    Lets us answer "show me the history of this skill" without
      //    walking archived rows in skills.
      db.exec(`
        CREATE TABLE IF NOT EXISTS skill_revisions (
          revision_id      TEXT PRIMARY KEY,         -- rev-<uuid>
          skill_name       TEXT NOT NULL,
          scope            TEXT NOT NULL,            -- 'global' | 'project:<hash>'
          from_version     TEXT,                     -- previous active version (NULL for genesis)
          to_version       TEXT NOT NULL,            -- new active version after this rev
          action           TEXT NOT NULL CHECK(action IN ('promote','revert','manual')),
          source_result_id TEXT,                     -- mres-<id> when action='promote'
          reverted_to_body_of TEXT,                  -- skill_id whose body was restored on revert
          decided_by       TEXT NOT NULL,
          rationale        TEXT,
          created_at       TEXT NOT NULL
        );
      `);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_skill_revisions_name ON skill_revisions(skill_name, scope, created_at DESC);`);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_skill_revisions_source ON skill_revisions(source_result_id);`);
    },
  },

  {
    id: 27,
    description: "v0.18.8 Sprint 2.8: token_savings_snapshots — 4h + daily rollups of per-project SC tool usage + estimated savings",
    up: (db) => {
      // Aggregate of tool_calls_pg for fast trend queries. Two cadences:
      //   - cadence='4h'   : one row per project per 4-hour window (last 24h granularity)
      //   - cadence='daily': one row per project per UTC day (long-range trend)
      // The savings panel uses 4h for "last 24 hours" detail view and
      // daily for "last 30 days" sparkline. 4-hourly aligns with typical
      // Claude Code work-session blocks (not too frequent, not too sparse).
      db.exec(`
        CREATE TABLE IF NOT EXISTS token_savings_snapshots (
          snapshot_id      TEXT PRIMARY KEY,
          project_hash     TEXT NOT NULL,
          cadence          TEXT NOT NULL CHECK (cadence IN ('4h','daily')),
          period_start     TEXT NOT NULL,            -- ISO timestamp; bucket start (UTC-aligned: 00,04,08,12,16,20 for 4h; 00 for daily)
          period_end       TEXT NOT NULL,            -- ISO timestamp; bucket end exclusive
          total_calls            INTEGER NOT NULL,
          total_actual_tokens    INTEGER NOT NULL,
          total_actual_cost_usd  REAL    NOT NULL,
          total_estimated_native_tokens INTEGER NOT NULL,
          total_saved_tokens     INTEGER NOT NULL,
          total_saved_cost_usd   REAL    NOT NULL,
          reduction_pct          REAL    NOT NULL,
          confidence             TEXT    NOT NULL,
          per_tool               TEXT    NOT NULL,    -- JSON breakdown
          per_agent              TEXT    NOT NULL,    -- JSON: {agent_id: {calls, saved_tokens, reduction_pct}}
          created_at             TEXT    NOT NULL,
          UNIQUE(project_hash, cadence, period_start)
        );
      `);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_savings_snapshots_project ON token_savings_snapshots(project_hash, cadence, period_start DESC);`);
    },
  },

  {
    id: 28,
    description: "v0.22.0: full skill attribution — agent_id + project_hash on skill_runs + skill_run_tool_calls correlation table + mutation_reviews operator audit",
    up: (db) => {
      // ── skill_runs: per-agent + per-project attribution ────────────────
      // Without these columns we cannot answer "which skills is THIS agent
      // using on THIS project?" — the core question the self-improvement
      // loop needs to surface to the operator. project_hash is required
      // (denormalized from session_id for query efficiency).
      const safeAdd = (table: string, col: string, ddl: string) => {
        const cols = db.prepare(`PRAGMA table_info(${table})`).all() as Array<{ name: string }>;
        if (!cols.some((c) => c.name === col)) {
          db.exec(`ALTER TABLE ${table} ADD COLUMN ${col} ${ddl}`);
        }
      };
      safeAdd("skill_runs", "agent_id",     "TEXT");
      safeAdd("skill_runs", "project_hash", "TEXT");
      db.exec(`CREATE INDEX IF NOT EXISTS idx_sr_agent_project ON skill_runs(agent_id, project_hash, ts DESC);`);

      // ── skill_run_tool_calls: which tool calls happened DURING a skill_run? ──
      // The MCP server sets currentSkillContext on zc_skill_show; every
      // tool_call between then and zc_record_skill_outcome accumulates here.
      // Lets the operator answer "show me the 14 tool calls behind this
      // low-scoring run" — essential for debugging skill failures.
      db.exec(`
        CREATE TABLE IF NOT EXISTS skill_run_tool_calls (
          run_id    TEXT NOT NULL,
          call_id   TEXT NOT NULL,
          ts        TEXT NOT NULL,
          PRIMARY KEY (run_id, call_id)
        );
      `);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_srtc_run ON skill_run_tool_calls(run_id);`);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_srtc_call ON skill_run_tool_calls(call_id);`);

      // ── mutation_reviews: operator action audit log ─────────────────────
      // When the operator approves/rejects a mutation result on the dashboard,
      // log it here so we can audit "who approved what when, with what reasoning."
      // Without this, every approval is fire-and-forget — gone forever.
      db.exec(`
        CREATE TABLE IF NOT EXISTS mutation_reviews (
          review_id      TEXT PRIMARY KEY,
          mutation_id    TEXT NOT NULL,
          result_id      TEXT,
          action         TEXT NOT NULL CHECK (action IN ('approve', 'reject', 'defer')),
          operator       TEXT NOT NULL,           -- agent_id or 'operator' for human dashboard action
          rationale      TEXT,
          ts             TEXT NOT NULL
        );
      `);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_mr_mutation ON mutation_reviews(mutation_id, ts DESC);`);
    },
  },

  {
    id: 29,
    description: "v0.30.8: evidence column on skill_runs — structured what_worked/what_didnt/recommendation_for_skill (JSON text; mirrors PG migration 27)",
    up: (db) => {
      const safeAdd = (table: string, col: string, def: string) => {
        try { db.exec(`ALTER TABLE ${table} ADD COLUMN ${col} ${def}`); } catch { /* column exists */ }
      };
      safeAdd("skill_runs", "evidence", "TEXT");
    },
  },

  {
    id: 30,
    description: "v0.31.0: persistent typed knowledge graph — kb_edges (directed co-references) + kb_backlinks (materialized in-degree) for backlink-boosted search ranking (mirrors PG migration 28)",
    up: (db) => {
      db.exec(`
        CREATE TABLE IF NOT EXISTS kb_edges (
          from_source   TEXT    NOT NULL,
          to_source     TEXT    NOT NULL,
          relation_type TEXT    NOT NULL DEFAULT 'code_ref',
          match_kind    TEXT    NOT NULL DEFAULT 'full_key',
          weight        INTEGER NOT NULL DEFAULT 1,
          computed_at   TEXT    NOT NULL,
          PRIMARY KEY (from_source, to_source, relation_type)
        );
        CREATE INDEX IF NOT EXISTS idx_kbe_to   ON kb_edges(to_source);
        CREATE INDEX IF NOT EXISTS idx_kbe_from ON kb_edges(from_source);
        CREATE TABLE IF NOT EXISTS kb_backlinks (
          source       TEXT    PRIMARY KEY,
          in_degree    INTEGER NOT NULL DEFAULT 0,
          weighted_in  INTEGER NOT NULL DEFAULT 0,
          computed_at  TEXT    NOT NULL
        );
      `);
    },
  },

  {
    id: 31,
    description: "v0.31.0: epistemology layer on working_memory (kind|confidence|resolution_status|resolved_at) — fact/decision/hypothesis/prediction + confidence + resolution tracking (mirrors PG migration 29)",
    up: (db) => {
      const tbl = db.prepare(`SELECT name FROM sqlite_master WHERE type='table' AND name='working_memory'`).get();
      if (!tbl) return;
      const cols = new Set(
        (db.prepare(`PRAGMA table_info(working_memory)`).all() as Array<{ name: string }>).map((c) => c.name)
      );
      const safeAdd = (col: string, ddl: string) => {
        if (!cols.has(col)) { try { db.exec(`ALTER TABLE working_memory ADD COLUMN ${col} ${ddl}`); } catch { /* exists */ } }
      };
      safeAdd("kind",              `TEXT NOT NULL DEFAULT 'fact' CHECK (kind IN ('fact','decision','hypothesis','prediction','constraint','antipattern'))`);
      safeAdd("confidence",        `REAL`);
      safeAdd("resolution_status", `TEXT CHECK (resolution_status IN ('open','resolved_correct','resolved_incorrect','resolved_partial'))`);
      safeAdd("resolved_at",       `TEXT`);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_wm_kind ON working_memory(agent_id, kind, resolution_status);`);
    },
  },

  {
    id: 32,
    description: "v0.31.0: memory_contradictions — background suspected-contradiction signals over working memory (mirrors PG migration 30)",
    up: (db) => {
      db.exec(`
        CREATE TABLE IF NOT EXISTS memory_contradictions (
          id            INTEGER PRIMARY KEY AUTOINCREMENT,
          agent_id      TEXT NOT NULL,
          key_a         TEXT NOT NULL,
          key_b         TEXT NOT NULL,
          similarity    REAL NOT NULL,
          reason        TEXT NOT NULL,
          detail        TEXT,
          status        TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open','dismissed','acknowledged','resolved')),
          surfaced_by   TEXT NOT NULL CHECK (surfaced_by IN ('cron','manual')),
          surfaced_at   TEXT NOT NULL,
          reviewed_at   TEXT,
          UNIQUE(agent_id, key_a, key_b)
        );
        CREATE INDEX IF NOT EXISTS idx_mc_status ON memory_contradictions(agent_id, status, surfaced_at DESC);
      `);
    },
  },

  {
    id: 33,
    description: "v0.32.0: recency-decay/salience on working_memory (access_count + last_retrieved_at) — secondary recall signal under importance (mirrors PG migration 31)",
    up: (db) => {
      const tbl = db.prepare(`SELECT name FROM sqlite_master WHERE type='table' AND name='working_memory'`).get();
      if (!tbl) return;
      const cols = new Set(
        (db.prepare(`PRAGMA table_info(working_memory)`).all() as Array<{ name: string }>).map((c) => c.name)
      );
      const safeAdd = (col: string, ddl: string) => {
        if (!cols.has(col)) { try { db.exec(`ALTER TABLE working_memory ADD COLUMN ${col} ${ddl}`); } catch { /* exists */ } }
      };
      safeAdd("access_count",      `INTEGER NOT NULL DEFAULT 0`);
      safeAdd("last_retrieved_at", `TEXT`);
    },
  },

  {
    id: 34,
    description: "v0.37.0: temporal fact retirement — valid_to/superseded_by/retired_reason on working_memory + resolution_mode on memory_contradictions (mirrors PG migration 32)",
    up: (db) => {
      const safeAdd = (table: string, col: string, ddl: string) => {
        try { db.exec(`ALTER TABLE ${table} ADD COLUMN ${col} ${ddl}`); } catch { /* exists / table absent */ }
      };
      safeAdd("working_memory", "valid_to",       `TEXT`);
      safeAdd("working_memory", "superseded_by",  `TEXT`);
      safeAdd("working_memory", "retired_reason", `TEXT`);
      safeAdd("memory_contradictions", "resolution_mode", `TEXT`);
      safeAdd("source_meta", "entity_scanned_at", `TEXT`);  // v0.37.0 entity-extraction marker
      try { db.exec(`CREATE INDEX IF NOT EXISTS idx_wm_live ON working_memory(agent_id, valid_to)`); } catch { /* table absent */ }
      db.exec(`
        CREATE TABLE IF NOT EXISTS community_summaries (
          community_id   INTEGER PRIMARY KEY,
          size           INTEGER NOT NULL,
          sample_sources TEXT    NOT NULL,
          summary        TEXT    NOT NULL,
          computed_at    TEXT    NOT NULL
        );
      `);
    },
  },

  {
    id: 35,
    description: "v0.38.0: per-claim citations — origin column on working_memory (what created the fact: tool, compaction session, broadcast; mirrors PG migration 33)",
    up: (db) => {
      try { db.exec(`ALTER TABLE working_memory ADD COLUMN origin TEXT`); } catch { /* exists / table absent */ }
    },
  },

  {
    id: 36,
    description: "v0.39.0: content-addressable embeddings — content_hash column enables dedup (skip re-embedding identical content) + explicit model-migration detection (mirrors PG migration 34)",
    up: (db) => {
      try { db.exec(`ALTER TABLE embeddings ADD COLUMN content_hash TEXT`); } catch { /* exists / table absent */ }
    },
  },

  {
    id: 37,
    description: "M3 (v0.41.0): bi-temporal-lite — optional EVENT-time valid_at/invalid_at on working_memory (when the fact was true in the world), alongside the TRANSACTION timeline (created_at/valid_to). Nullable: most conversational facts carry no explicit event date (mirrors PG migration 35)",
    up: (db) => {
      try { db.exec(`ALTER TABLE working_memory ADD COLUMN valid_at TEXT`); } catch { /* exists */ }
      try { db.exec(`ALTER TABLE working_memory ADD COLUMN invalid_at TEXT`); } catch { /* exists */ }
    },
  },

  {
    id: 38,
    description: "R1 (v0.42.0): per-fact TTL — nullable expires_at on working_memory. Expired facts are excluded from recall and retired ('expired', revivable) by the enrichment sweep (mirrors PG migration 36)",
    up: (db) => {
      try { db.exec(`ALTER TABLE working_memory ADD COLUMN expires_at TEXT`); } catch { /* exists */ }
    },
  },

  {
    id: 39,
    description: "S3 (v0.46.0): team attribution — nullable created_by (user id) on working_memory (mirrors PG migration 38). The team control plane (users/keys/workspaces) is PG-only by design — multi-user coordination requires the shared store; SQLite carries the attribution column for parity so facts round-trip losslessly.",
    up: (db) => {
      try { db.exec(`ALTER TABLE working_memory ADD COLUMN created_by TEXT`); } catch { /* exists */ }
    },
  },

  {
    id: 40,
    description: "TKG-T1 (v0.47.0): KB bi-temporality — first_seen_at (immutable) + last_indexed_at (bumped on re-index) on knowledge, backfilled from created_at (mirrors PG migration 41). Fixes the created_at-bump-on-reindex gotcha that clustered file dates on the last index day and broke temporal Timeline ordering.",
    up: (db) => {
      const safeAdd = (col: string) => {
        try { db.exec(`ALTER TABLE knowledge ADD COLUMN ${col} TEXT`); } catch { /* exists / table absent */ }
      };
      safeAdd("first_seen_at");
      safeAdd("last_indexed_at");
      try {
        db.exec(`UPDATE knowledge SET first_seen_at = created_at WHERE first_seen_at IS NULL`);
        db.exec(`UPDATE knowledge SET last_indexed_at = created_at WHERE last_indexed_at IS NULL`);
      } catch { /* table absent on KB-less DB */ }
    },
  },

  {
    id: 41,
    description: "TKG-T3 (v0.47.0): world-time invalidation — invalid_from on working_memory (mirrors PG migration 42). Records when a fact stopped being true in the world, vs valid_to = when the system retired it.",
    up: (db) => {
      try { db.exec(`ALTER TABLE working_memory ADD COLUMN invalid_from TEXT`); } catch { /* exists */ }
    },
  },
  {
    id: 42,
    description: "Broadcast attribution fix: sender_agent_id — agent_id means TARGET on ASSIGN but SENDER elsewhere, so stored history misattributed who spoke (A2A #3070). Backfill copies agent_id where it truly was the sender; ASSIGN stays NULL (unknown, not fabricated). Mirrors PG migration 47.",
    up: (db) => {
      try { db.exec(`ALTER TABLE broadcasts ADD COLUMN sender_agent_id TEXT`); } catch { /* exists */ }
      db.exec(`UPDATE broadcasts SET sender_agent_id = agent_id WHERE sender_agent_id IS NULL AND type <> 'ASSIGN'`);
    },
  },

];

/**
 * Apply all pending migrations to the given database.
 * Each migration is wrapped in a transaction for atomicity.
 * Already-applied migrations are skipped.
 */
export function runMigrations(db: DatabaseSync): void {
  // Ensure the migrations tracking table exists
  db.exec(`
    CREATE TABLE IF NOT EXISTS schema_migrations (
      id          INTEGER PRIMARY KEY,
      description TEXT    NOT NULL,
      applied_at  TEXT    NOT NULL
    );
  `);

  for (const migration of MIGRATIONS) {
    const existing = db.prepare(
      "SELECT id FROM schema_migrations WHERE id = ?"
    ).get(migration.id);

    if (existing) continue; // already applied

    // Wrap each migration in a transaction for atomicity
    // If migration.up() throws, the transaction rolls back automatically
    db.exec("BEGIN");
    try {
      migration.up(db);
      db.prepare(
        "INSERT INTO schema_migrations(id, description, applied_at) VALUES (?, ?, ?)"
      ).run(migration.id, migration.description, new Date().toISOString());
      db.exec("COMMIT");
    } catch (err) {
      db.exec("ROLLBACK");
      throw new Error(
        `Migration ${migration.id} ("${migration.description}") failed: ${
          err instanceof Error ? err.message : String(err)
        }`
      );
    }
  }
}

/**
 * v0.18.9 — Heal session SQLite DBs that were created on an older schema.
 *
 * Background: Each Claude Code session opens a project-scoped SQLite DB in
 * `~/.claude/zc-ctx/sessions/<hash>.db`. If the MCP server is upgraded between
 * sessions, OLD session DBs are NOT auto-migrated unless the agent reconnects.
 * That left some session DBs missing columns (e.g. tool_calls.id) and silently
 * dropping every telemetry write with "no such column: id".
 *
 * Fix: at MCP server start, walk the sessions directory and run idempotent
 * migrations on every *.db. Cheap, idempotent, and transactional per-DB —
 * if any single DB fails, others are unaffected.
 *
 * Returns { scanned, healed, failed } counts. `healed` only counts DBs where
 * at least one new migration applied.
 */
export function healSessionDbs(sessionsDir: string): { scanned: number; healed: number; failed: number; failures: Array<{ path: string; error: string }> } {
  const result = { scanned: 0, healed: 0, failed: 0, failures: [] as Array<{ path: string; error: string }> };
  let entries: string[];
  try {
    // eslint-disable-next-line @typescript-eslint/no-require-imports
    const fs = require("node:fs") as typeof import("node:fs");
    if (!fs.existsSync(sessionsDir)) return result;
    entries = fs.readdirSync(sessionsDir).filter((f) => f.endsWith(".db"));
  } catch (e) {
    return { ...result, failed: 1, failures: [{ path: sessionsDir, error: (e as Error).message }] };
  }
  for (const fname of entries) {
    result.scanned++;
    // eslint-disable-next-line @typescript-eslint/no-require-imports
    const path = require("node:path") as typeof import("node:path");
    const full = path.join(sessionsDir, fname);
    try {
      const db = new DatabaseSync(full);
      const before = getCurrentSchemaVersion(db);
      runMigrations(db);
      const after = getCurrentSchemaVersion(db);
      db.close();
      if (after > before) result.healed++;
    } catch (e) {
      result.failed++;
      result.failures.push({ path: full, error: (e as Error).message });
    }
  }
  return result;
}

/** Returns the highest applied migration ID, or 0 if none applied yet. */
export function getCurrentSchemaVersion(db: DatabaseSync): number {
  try {
    const row = db.prepare(
      "SELECT MAX(id) as v FROM schema_migrations"
    ).get() as { v: number | null };
    return row?.v ?? 0;
  } catch {
    return 0;
  }
}