// sf-db-schema.js — SQLite schema creation and migration helpers.
//
// Purpose: keep schema DDL and historical migrations out of the runtime DB
// adapter module while preserving one DB-open path for SF state.

import { copyFileSync, existsSync } from "node:fs";
import { getErrorMessage } from "../error-utils.js";
import { logWarning } from "../workflow-logger.js";

function defaultQueryTimeout(operation, fallbackValue) {
	try {
		return operation();
	} catch {
		return fallbackValue;
	}
}

const SCHEMA_VERSION = 73;
function indexExists(db, name) {
	return !!db
		.prepare(
			"SELECT 1 as present FROM sqlite_master WHERE type = 'index' AND name = ?",
		)
		.get(name);
}
function dedupeVerificationEvidenceRows(db) {
	db.exec(`
    DELETE FROM verification_evidence
    WHERE rowid NOT IN (
      SELECT MIN(rowid)
      FROM verification_evidence
      GROUP BY task_id, slice_id, milestone_id, command, verdict
    )
  `);
}
function ensureVerificationEvidenceDedupIndex(db) {
	if (indexExists(db, "idx_verification_evidence_dedup")) return;
	dedupeVerificationEvidenceRows(db);
	db.exec(
		"CREATE UNIQUE INDEX IF NOT EXISTS idx_verification_evidence_dedup ON verification_evidence(task_id, slice_id, milestone_id, command, verdict)",
	);
}
function ensureRepoProfileTables(db) {
	db.exec(`
      CREATE TABLE IF NOT EXISTS repo_profiles (
        profile_id TEXT PRIMARY KEY,
        project_hash TEXT NOT NULL,
        project_root TEXT NOT NULL DEFAULT '',
        head TEXT DEFAULT NULL,
        branch TEXT DEFAULT NULL,
        remote_hash TEXT DEFAULT NULL,
        dirty INTEGER NOT NULL DEFAULT 0,
        profile_json TEXT NOT NULL DEFAULT '{}',
        created_at TEXT NOT NULL
      )
    `);
	db.exec(`
      CREATE TABLE IF NOT EXISTS repo_file_observations (
        path TEXT PRIMARY KEY,
        latest_profile_id TEXT NOT NULL,
        git_status TEXT NOT NULL,
        ownership TEXT NOT NULL,
        language TEXT DEFAULT NULL,
        size_bytes INTEGER NOT NULL DEFAULT 0,
        content_hash TEXT DEFAULT NULL,
        summary TEXT DEFAULT NULL,
        first_seen_at TEXT NOT NULL,
        last_seen_at TEXT NOT NULL,
        adopted_at TEXT DEFAULT NULL,
        adoption_unit_id TEXT DEFAULT NULL
      )
    `);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_repo_profiles_created ON repo_profiles(created_at DESC)",
	);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_repo_file_observations_status ON repo_file_observations(git_status, ownership)",
	);
}
function ensureBacklogTables(db) {
	db.exec(`
      CREATE TABLE IF NOT EXISTS backlog_items (
        id TEXT PRIMARY KEY,
        title TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT 'pending',
        note TEXT NOT NULL DEFAULT '',
        source TEXT NOT NULL DEFAULT '',
        triage_run_id TEXT DEFAULT NULL,
        sequence INTEGER NOT NULL DEFAULT 0,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL,
        promoted_at TEXT DEFAULT NULL
      )
    `);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_backlog_items_status_sequence ON backlog_items(status, sequence, id)",
	);
}
function ensureScheduleTables(db) {
	db.exec(`
      CREATE TABLE IF NOT EXISTS schedule_entries (
        seq INTEGER PRIMARY KEY AUTOINCREMENT,
        scope TEXT NOT NULL DEFAULT 'project',
        id TEXT NOT NULL,
        schema_version INTEGER NOT NULL DEFAULT 1,
        kind TEXT NOT NULL DEFAULT 'reminder',
        status TEXT NOT NULL DEFAULT 'pending',
        due_at TEXT NOT NULL DEFAULT '',
        created_at TEXT NOT NULL DEFAULT '',
        snoozed_at TEXT DEFAULT NULL,
        payload_json TEXT NOT NULL DEFAULT '{}',
        created_by TEXT NOT NULL DEFAULT 'user',
        autonomous_dispatch INTEGER NOT NULL DEFAULT 0,
        full_json TEXT NOT NULL DEFAULT '{}',
        imported_from TEXT DEFAULT NULL
      )
    `);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_schedule_entries_scope_id_created ON schedule_entries(scope, id, created_at DESC, seq DESC)",
	);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_schedule_entries_scope_due ON schedule_entries(scope, status, due_at)",
	);
	ensureColumn(
		db,
		"schedule_entries",
		"autonomous_dispatch",
		"ALTER TABLE schedule_entries ADD COLUMN autonomous_dispatch INTEGER NOT NULL DEFAULT 0",
	);
}
function ensureSolverEvalTables(db) {
	db.exec(`
      CREATE TABLE IF NOT EXISTS solver_eval_runs (
        run_id TEXT PRIMARY KEY,
        suite_source TEXT NOT NULL DEFAULT '',
        cases_count INTEGER NOT NULL DEFAULT 0,
        summary_json TEXT NOT NULL DEFAULT '{}',
        report_path TEXT NOT NULL DEFAULT '',
        results_path TEXT NOT NULL DEFAULT '',
        db_recorded INTEGER NOT NULL DEFAULT 1,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL
      )
    `);
	db.exec(`
      CREATE TABLE IF NOT EXISTS solver_eval_case_results (
        run_id TEXT NOT NULL,
        case_id TEXT NOT NULL,
        title TEXT NOT NULL DEFAULT '',
        mode TEXT NOT NULL,
        passed INTEGER NOT NULL DEFAULT 0,
        false_complete INTEGER NOT NULL DEFAULT 0,
        duration_ms INTEGER DEFAULT NULL,
        command_status INTEGER DEFAULT NULL,
        solver_outcome TEXT DEFAULT NULL,
        pdd_complete INTEGER DEFAULT NULL,
        result_json TEXT NOT NULL DEFAULT '{}',
        created_at TEXT NOT NULL,
        PRIMARY KEY (run_id, case_id, mode),
        FOREIGN KEY (run_id) REFERENCES solver_eval_runs(run_id) ON DELETE CASCADE
      )
    `);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_solver_eval_runs_created ON solver_eval_runs(created_at DESC)",
	);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_solver_eval_case_lookup ON solver_eval_case_results(run_id, case_id)",
	);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_solver_eval_case_false_complete ON solver_eval_case_results(false_complete, mode)",
	);
}
function ensureSessionTables(db) {
	db.exec(`
      CREATE TABLE IF NOT EXISTS sessions (
        session_id TEXT PRIMARY KEY,
        trace_id TEXT DEFAULT NULL,
        mode TEXT NOT NULL DEFAULT 'interactive',
        cwd TEXT NOT NULL DEFAULT '',
        repo TEXT DEFAULT NULL,
        branch TEXT DEFAULT NULL,
        summary TEXT DEFAULT NULL,
        summary_count INTEGER NOT NULL DEFAULT 0,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL
      )
    `);
	db.exec(`
      CREATE TABLE IF NOT EXISTS turns (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        session_id TEXT NOT NULL REFERENCES sessions(session_id) ON DELETE CASCADE,
        turn_index INTEGER NOT NULL,
        user_message TEXT,
        assistant_response TEXT,
        ts TEXT NOT NULL,
        UNIQUE(session_id, turn_index)
      )
    `);
	db.exec(`
      CREATE TABLE IF NOT EXISTS session_file_touches (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        session_id TEXT NOT NULL REFERENCES sessions(session_id) ON DELETE CASCADE,
        path TEXT NOT NULL,
        tool_name TEXT DEFAULT NULL,
        turn_id INTEGER DEFAULT NULL REFERENCES turns(id),
        first_seen_at TEXT NOT NULL,
        UNIQUE(session_id, path)
      )
    `);
	db.exec(`
      CREATE TABLE IF NOT EXISTS session_refs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        session_id TEXT NOT NULL REFERENCES sessions(session_id) ON DELETE CASCADE,
        ref_type TEXT NOT NULL,
        ref_value TEXT NOT NULL,
        turn_id INTEGER DEFAULT NULL REFERENCES turns(id),
        created_at TEXT NOT NULL,
        UNIQUE(session_id, ref_type, ref_value)
      )
    `);
	repairOrphanedTurnsFtsTables(db);
	// FTS5 external-content table over turns for keyword recall.
	// content_rowid links to turns.id; triggers below keep it in sync.
	db.exec(`
      CREATE VIRTUAL TABLE IF NOT EXISTS turns_fts USING fts5(
        user_message,
        assistant_response,
        content='turns',
        content_rowid='id'
      )
    `);
	db.exec(`
      CREATE TRIGGER IF NOT EXISTS turns_fts_insert AFTER INSERT ON turns BEGIN
        INSERT INTO turns_fts(rowid, user_message, assistant_response)
          VALUES (new.id, new.user_message, new.assistant_response);
      END
    `);
	db.exec(`
      CREATE TRIGGER IF NOT EXISTS turns_fts_update AFTER UPDATE ON turns BEGIN
        INSERT INTO turns_fts(turns_fts, rowid, user_message, assistant_response)
          VALUES ('delete', old.id, old.user_message, old.assistant_response);
        INSERT INTO turns_fts(rowid, user_message, assistant_response)
          VALUES (new.id, new.user_message, new.assistant_response);
      END
    `);
	db.exec(`
      CREATE TRIGGER IF NOT EXISTS turns_fts_delete AFTER DELETE ON turns BEGIN
        INSERT INTO turns_fts(turns_fts, rowid, user_message, assistant_response)
          VALUES ('delete', old.id, old.user_message, old.assistant_response);
      END
    `);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_sessions_created ON sessions(created_at DESC)",
	);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_sessions_repo ON sessions(repo, created_at DESC)",
	);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_turns_session ON turns(session_id, turn_index)",
	);
	db.exec("CREATE INDEX IF NOT EXISTS idx_turns_ts ON turns(ts DESC)");
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_session_file_touches_session ON session_file_touches(session_id, first_seen_at DESC)",
	);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_session_file_touches_path ON session_file_touches(path, session_id)",
	);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_session_refs_session ON session_refs(session_id, created_at DESC)",
	);
}

function repairOrphanedTurnsFtsTables(db) {
	const ftsTable = db
		.prepare(
			"SELECT name FROM sqlite_master WHERE type='table' AND name='turns_fts'",
		)
		.get();
	if (ftsTable) return;
	const orphan = db
		.prepare(
			"SELECT name FROM sqlite_master WHERE name IN ('turns_fts_data', 'turns_fts_idx', 'turns_fts_docsize', 'turns_fts_config', 'turns_fts_content', 'turns_fts_insert', 'turns_fts_update', 'turns_fts_delete') LIMIT 1",
		)
		.get();
	if (!orphan) return;
	db.exec("DROP TRIGGER IF EXISTS turns_fts_insert");
	db.exec("DROP TRIGGER IF EXISTS turns_fts_update");
	db.exec("DROP TRIGGER IF EXISTS turns_fts_delete");
	db.exec("DROP TABLE IF EXISTS turns_fts_data");
	db.exec("DROP TABLE IF EXISTS turns_fts_idx");
	db.exec("DROP TABLE IF EXISTS turns_fts_docsize");
	db.exec("DROP TABLE IF EXISTS turns_fts_config");
	db.exec("DROP TABLE IF EXISTS turns_fts_content");
}

function ensureSessionSnapshotTable(db) {
	db.exec(`
      CREATE TABLE IF NOT EXISTS session_snapshots (
        id       INTEGER PRIMARY KEY AUTOINCREMENT,
        -- Session that triggered this checkpoint. FK to sessions(session_id).
        session_id      TEXT NOT NULL,
        -- Zero-based counter within the session (first snapshot = 0).
        snapshot_index  INTEGER NOT NULL DEFAULT 0,
        -- Optional git stash ref so the snapshot can be restored exactly.
        -- NULL when the working tree had no changes to stash.
        git_stash_ref   TEXT,
        -- Free-text label for the snapshot (e.g. "before migration deploy").
        label           TEXT,
        ts TEXT NOT NULL,
        UNIQUE(session_id, snapshot_index)
      )
    `);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_session_snapshots_session ON session_snapshots(session_id, snapshot_index)",
	);
}
function ensureHeadlessRunTables(db) {
	db.exec(`
      CREATE TABLE IF NOT EXISTS headless_runs (
        run_id TEXT PRIMARY KEY,
        command TEXT NOT NULL DEFAULT '',
        status TEXT NOT NULL DEFAULT '',
        exit_code INTEGER NOT NULL DEFAULT 0,
        timed_out INTEGER NOT NULL DEFAULT 0,
        interrupted INTEGER NOT NULL DEFAULT 0,
        restart_count INTEGER NOT NULL DEFAULT 0,
        max_restarts INTEGER NOT NULL DEFAULT 0,
        duration_ms INTEGER NOT NULL DEFAULT 0,
        total_events INTEGER NOT NULL DEFAULT 0,
        tool_calls INTEGER NOT NULL DEFAULT 0,
        solver_eval_run_id TEXT DEFAULT NULL,
        solver_eval_report_path TEXT DEFAULT NULL,
        details_json TEXT NOT NULL DEFAULT '{}',
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL
      )
    `);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_headless_runs_created ON headless_runs(created_at DESC)",
	);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_headless_runs_status ON headless_runs(status, created_at DESC)",
	);
}
function ensureUokMessageTables(db) {
	db.exec(`
      CREATE TABLE IF NOT EXISTS uok_messages (
        id TEXT PRIMARY KEY,
        from_agent TEXT NOT NULL,
        to_agent TEXT NOT NULL,
        body TEXT NOT NULL DEFAULT '',
        metadata_json TEXT NOT NULL DEFAULT '{}',
        sent_at TEXT NOT NULL DEFAULT '',
        delivered_at TEXT DEFAULT NULL
      )
    `);
	db.exec(`
      CREATE TABLE IF NOT EXISTS uok_message_reads (
        message_id TEXT NOT NULL,
        agent_id TEXT NOT NULL,
        read_at TEXT NOT NULL DEFAULT '',
        PRIMARY KEY (message_id, agent_id),
        FOREIGN KEY (message_id) REFERENCES uok_messages(id) ON DELETE CASCADE
      )
    `);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_uok_messages_to ON uok_messages(to_agent, sent_at DESC)",
	);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_uok_messages_conversation ON uok_messages(from_agent, to_agent, sent_at DESC)",
	);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_uok_messages_sent ON uok_messages(sent_at DESC)",
	);
}
function ensureDeployTables(db) {
	db.exec(`
      CREATE TABLE IF NOT EXISTS deploy_runs (
        id TEXT PRIMARY KEY,
        milestone_id TEXT NOT NULL,
        target TEXT NOT NULL,
        command TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT 'pending',
        exit_code INTEGER DEFAULT NULL,
        output TEXT DEFAULT NULL,
        deployed_url TEXT DEFAULT NULL,
        created_at TEXT NOT NULL,
        finished_at TEXT DEFAULT NULL
      )
    `);
	db.exec(`
      CREATE TABLE IF NOT EXISTS smoke_results (
        id TEXT PRIMARY KEY,
        deploy_run_id TEXT NOT NULL,
        milestone_id TEXT NOT NULL,
        url TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT 'pending',
        verdict TEXT DEFAULT NULL,
        checks_json TEXT NOT NULL DEFAULT '[]',
        created_at TEXT NOT NULL,
        finished_at TEXT DEFAULT NULL,
        FOREIGN KEY (deploy_run_id) REFERENCES deploy_runs(id) ON DELETE CASCADE
      )
    `);
	db.exec(`
      CREATE TABLE IF NOT EXISTS release_records (
        id TEXT PRIMARY KEY,
        milestone_id TEXT NOT NULL,
        version TEXT NOT NULL,
        prev_version TEXT DEFAULT NULL,
        changelog_entry TEXT DEFAULT NULL,
        git_tag TEXT DEFAULT NULL,
        published INTEGER NOT NULL DEFAULT 0,
        created_at TEXT NOT NULL
      )
    `);
	db.exec(`
      CREATE TABLE IF NOT EXISTS rollback_runs (
        id TEXT PRIMARY KEY,
        deploy_run_id TEXT NOT NULL,
        milestone_id TEXT NOT NULL,
        reason TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT 'pending',
        output TEXT DEFAULT NULL,
        created_at TEXT NOT NULL,
        finished_at TEXT DEFAULT NULL,
        FOREIGN KEY (deploy_run_id) REFERENCES deploy_runs(id) ON DELETE CASCADE
      )
    `);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_deploy_runs_milestone ON deploy_runs(milestone_id, created_at DESC)",
	);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_smoke_results_deploy ON smoke_results(deploy_run_id)",
	);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_release_records_milestone ON release_records(milestone_id, created_at DESC)",
	);
}
function ensureSleeptimeQueueTable(db) {
	db.exec(`
      CREATE TABLE IF NOT EXISTS sleeptime_consolidation_queue (
        id TEXT PRIMARY KEY,
        conversation_agent TEXT NOT NULL,
        memory_agent TEXT NOT NULL,
        content TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT 'pending',
        created_at TEXT NOT NULL,
        processed_at TEXT DEFAULT NULL,
        result TEXT DEFAULT NULL
      )
    `);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_sleeptime_queue_status ON sleeptime_consolidation_queue(status, created_at ASC)",
	);
}
function ensureSelfFeedbackTables(db) {
	db.exec(`
      CREATE TABLE IF NOT EXISTS self_feedback (
        id TEXT PRIMARY KEY,
        ts TEXT NOT NULL,
        kind TEXT NOT NULL,
        severity TEXT NOT NULL,
        blocking INTEGER NOT NULL DEFAULT 0,
        repo_identity TEXT NOT NULL DEFAULT '',
        sf_version TEXT NOT NULL DEFAULT '',
        base_path TEXT NOT NULL DEFAULT '',
        unit_type TEXT DEFAULT NULL,
        milestone_id TEXT DEFAULT NULL,
        slice_id TEXT DEFAULT NULL,
        task_id TEXT DEFAULT NULL,
        summary TEXT NOT NULL DEFAULT '',
        evidence TEXT NOT NULL DEFAULT '',
        suggested_fix TEXT NOT NULL DEFAULT '',
        full_json TEXT NOT NULL,
        resolved_at TEXT DEFAULT NULL,
        resolved_reason TEXT DEFAULT NULL,
        resolved_by_sf_version TEXT DEFAULT NULL,
        resolved_evidence_json TEXT DEFAULT NULL,
        resolved_criteria_json TEXT DEFAULT NULL,
        impact_score INTEGER DEFAULT NULL,
        effort_estimate INTEGER DEFAULT NULL,
        purpose_anchor TEXT DEFAULT NULL
      )
    `);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_self_feedback_open ON self_feedback(resolved_at, severity, ts)",
	);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_self_feedback_kind ON self_feedback(kind, ts)",
	);
}
function ensureRetrievalEvidenceTables(db) {
	db.exec(`
      CREATE TABLE IF NOT EXISTS retrieval_evidence (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        backend TEXT NOT NULL,
        source_kind TEXT NOT NULL DEFAULT 'code',
        query TEXT NOT NULL DEFAULT '',
        strategy TEXT NOT NULL DEFAULT '',
        scope TEXT NOT NULL DEFAULT '',
        project_root TEXT NOT NULL DEFAULT '',
        git_head TEXT DEFAULT NULL,
        git_branch TEXT DEFAULT NULL,
        worktree_dirty INTEGER NOT NULL DEFAULT 0,
        freshness TEXT NOT NULL DEFAULT 'unknown',
        status TEXT NOT NULL DEFAULT 'ok',
        hit_count INTEGER NOT NULL DEFAULT 0,
        elapsed_ms INTEGER NOT NULL DEFAULT 0,
        cache_path TEXT DEFAULT NULL,
        error TEXT DEFAULT NULL,
        result_json TEXT NOT NULL DEFAULT '{}',
        recorded_at TEXT NOT NULL
      )
    `);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_retrieval_evidence_backend_recorded ON retrieval_evidence(backend, recorded_at DESC)",
	);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_retrieval_evidence_scope_recorded ON retrieval_evidence(scope, recorded_at DESC)",
	);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_retrieval_evidence_status_recorded ON retrieval_evidence(status, recorded_at DESC)",
	);
}
function ensureTriageTables(db) {
	db.exec(`
      CREATE TABLE IF NOT EXISTS triage_runs (
        id TEXT PRIMARY KEY,
        source_file TEXT,
        status TEXT NOT NULL DEFAULT 'complete',
        result_summary_json TEXT,
        created_at TEXT NOT NULL
      )
    `);
	db.exec(`
      CREATE TABLE IF NOT EXISTS triage_evals (
        id TEXT PRIMARY KEY,
        run_id TEXT NOT NULL REFERENCES triage_runs(id),
        task_input TEXT NOT NULL,
        expected_behavior TEXT,
        evidence TEXT,
        failure_mode TEXT,
        status TEXT NOT NULL DEFAULT 'pending',
        created_at TEXT NOT NULL
      )
    `);
	db.exec(`
      CREATE TABLE IF NOT EXISTS triage_items (
        id TEXT PRIMARY KEY,
        run_id TEXT NOT NULL REFERENCES triage_runs(id),
        kind TEXT NOT NULL,
        content TEXT NOT NULL,
        evidence TEXT,
        status TEXT NOT NULL DEFAULT 'pending',
        created_at TEXT NOT NULL
      )
    `);
	db.exec(`
      CREATE TABLE IF NOT EXISTS triage_skills (
        id TEXT PRIMARY KEY,
        run_id TEXT NOT NULL REFERENCES triage_runs(id),
        name TEXT,
        description TEXT,
        trigger TEXT,
        raw_json TEXT,
        status TEXT NOT NULL DEFAULT 'pending',
        created_at TEXT NOT NULL
      )
    `);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_triage_evals_run ON triage_evals(run_id)",
	);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_triage_items_run_kind ON triage_items(run_id, kind)",
	);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_triage_skills_run ON triage_skills(run_id)",
	);
}
function ensureRuntimeCounterTable(db) {
	db.exec(`
      CREATE TABLE IF NOT EXISTS runtime_counters (
        key TEXT PRIMARY KEY,
        value INTEGER NOT NULL DEFAULT 0,
        updated_at TEXT NOT NULL
      )
    `);
}
function ensureValidationAttentionMarkersTable(db) {
	db.exec(`
      CREATE TABLE IF NOT EXISTS validation_attention_markers (
        milestone_id TEXT PRIMARY KEY,
        created_at TEXT NOT NULL,
        source TEXT,
        remediation_round INTEGER,
        revalidation_round INTEGER,
        revalidation_requested_at TEXT
      )
    `);
}
function ensureContextBoardTable(db) {
	db.exec(`
      CREATE TABLE IF NOT EXISTS context_board (
        id TEXT PRIMARY KEY,
        content TEXT NOT NULL,
        category TEXT,
        added_at TEXT NOT NULL,
        repository TEXT NOT NULL,
        branch TEXT NOT NULL
      )
    `);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_context_board_repo_branch ON context_board(repository, branch, added_at ASC)",
	);
}
function ensureSelfFeedbackRelationsTable(db) {
	db.exec(`
      CREATE TABLE IF NOT EXISTS self_feedback_relations (
        from_id TEXT NOT NULL,
        to_id TEXT NOT NULL,
        relation_kind TEXT NOT NULL CHECK (relation_kind IN (
          'supersedes',
          'duplicate_of',
          'blocks',
          'root_cause_of',
          'partial_fix_of'
        )),
        created_at TEXT NOT NULL,
        PRIMARY KEY (from_id, to_id, relation_kind),
        FOREIGN KEY (from_id) REFERENCES self_feedback(id),
        FOREIGN KEY (to_id) REFERENCES self_feedback(id),
        CHECK (from_id != to_id)
      )
    `);
	db.exec(
		"CREATE INDEX IF NOT EXISTS idx_self_feedback_relations_to ON self_feedback_relations(to_id, relation_kind)",
	);
}
function ensureSpecSchemaTables(db) {
	// Tier 1.3: Spec/Runtime/Evidence schema separation
	// Creates 9 normalized tables for milestone, slice, task entities
	// Each entity type has: <entity>_specs (immutable intent), <entity> (runtime state), <entity>_evidence (audit trail)

	// ── Milestone Spec Table (immutable record of intent) ───────────
	db.exec(`
      CREATE TABLE IF NOT EXISTS milestone_specs (
        id TEXT NOT NULL,
        vision TEXT NOT NULL DEFAULT '',
        success_criteria TEXT DEFAULT '',
        key_risks TEXT DEFAULT '',
        proof_strategy TEXT DEFAULT '',
        verification_contract TEXT DEFAULT '',
        verification_integration TEXT DEFAULT '',
        verification_operational TEXT DEFAULT '',
        verification_uat TEXT DEFAULT '',
        definition_of_done TEXT DEFAULT '',
        requirement_coverage TEXT DEFAULT '',
        boundary_map_markdown TEXT DEFAULT '',
        vision_meeting_json TEXT DEFAULT '',
        product_research_json TEXT DEFAULT '',
        spec_version INTEGER NOT NULL DEFAULT 1,
        created_at TEXT NOT NULL,
        PRIMARY KEY (id),
        FOREIGN KEY (id) REFERENCES milestones(id)
      )
    `);

	// ── Slice Spec Table (immutable record of intent) ───────────
	db.exec(`
      CREATE TABLE IF NOT EXISTS slice_specs (
        milestone_id TEXT NOT NULL,
        slice_id TEXT NOT NULL,
        goal TEXT NOT NULL DEFAULT '',
        success_criteria TEXT DEFAULT '',
        proof_level TEXT DEFAULT '',
        integration_closure TEXT DEFAULT '',
        observability_impact TEXT DEFAULT '',
        adversarial_partner TEXT DEFAULT '',
        adversarial_combatant TEXT DEFAULT '',
        adversarial_architect TEXT DEFAULT '',
        planning_meeting_json TEXT DEFAULT '',
        spec_version INTEGER NOT NULL DEFAULT 1,
        created_at TEXT NOT NULL,
        PRIMARY KEY (milestone_id, slice_id),
        FOREIGN KEY (milestone_id) REFERENCES milestones(id),
        FOREIGN KEY (milestone_id, slice_id) REFERENCES slices(milestone_id, id)
      )
    `);

	// ── Task Spec Table (immutable record of intent) ───────────
	db.exec(`
      CREATE TABLE IF NOT EXISTS task_specs (
        milestone_id TEXT NOT NULL,
        slice_id TEXT NOT NULL,
        task_id TEXT NOT NULL,
        verify TEXT NOT NULL DEFAULT '',
        inputs TEXT DEFAULT '',
        expected_output TEXT DEFAULT '',
        risk TEXT NOT NULL DEFAULT 'low',
        mutation_scope TEXT NOT NULL DEFAULT 'isolated',
        verification_type TEXT NOT NULL DEFAULT 'self-check',
        plan_approval TEXT NOT NULL DEFAULT 'not-required',
        estimated_effort INTEGER DEFAULT NULL,
        dependencies TEXT NOT NULL DEFAULT '[]',
        blocks_parallel INTEGER NOT NULL DEFAULT 0,
        requires_user_input INTEGER NOT NULL DEFAULT 0,
        auto_retry INTEGER NOT NULL DEFAULT 1,
        max_retries INTEGER NOT NULL DEFAULT 2,
        spec_version INTEGER NOT NULL DEFAULT 1,
        created_at TEXT NOT NULL,
        PRIMARY KEY (milestone_id, slice_id, task_id),
        FOREIGN KEY (milestone_id, slice_id) REFERENCES slices(milestone_id, id),
        FOREIGN KEY (milestone_id, slice_id, task_id) REFERENCES tasks(milestone_id, slice_id, id)
      )
    `);

	// ── Milestone Evidence Table (append-only audit trail) ───────────
	db.exec(`
      CREATE TABLE IF NOT EXISTS milestone_evidence (
        milestone_id TEXT NOT NULL,
        evidence_type TEXT NOT NULL,
        content TEXT NOT NULL,
        recorded_at TEXT NOT NULL,
        phase_name TEXT DEFAULT '',
        recorded_by TEXT DEFAULT '',
        evidence_id TEXT NOT NULL DEFAULT (lower(hex(randomblob(16)))),
        PRIMARY KEY (milestone_id, evidence_id),
        FOREIGN KEY (milestone_id) REFERENCES milestones(id)
      )
    `);

	// ── Slice Evidence Table (append-only audit trail) ───────────
	db.exec(`
      CREATE TABLE IF NOT EXISTS slice_evidence (
        milestone_id TEXT NOT NULL,
        slice_id TEXT NOT NULL,
        evidence_type TEXT NOT NULL,
        content TEXT NOT NULL,
        recorded_at TEXT NOT NULL,
        phase_name TEXT DEFAULT '',
        recorded_by TEXT DEFAULT '',
        evidence_id TEXT NOT NULL DEFAULT (lower(hex(randomblob(16)))),
        PRIMARY KEY (milestone_id, slice_id, evidence_id),
        FOREIGN KEY (milestone_id, slice_id) REFERENCES slices(milestone_id, id)
      )
    `);

	// ── Task Evidence Table (append-only audit trail) ───────────
	db.exec(`
      CREATE TABLE IF NOT EXISTS task_evidence (
        milestone_id TEXT NOT NULL,
        slice_id TEXT NOT NULL,
        task_id TEXT NOT NULL,
        evidence_type TEXT NOT NULL,
        content TEXT NOT NULL,
        recorded_at TEXT NOT NULL,
        phase_name TEXT DEFAULT '',
        recorded_by TEXT DEFAULT '',
        evidence_id TEXT NOT NULL DEFAULT (lower(hex(randomblob(16)))),
        PRIMARY KEY (milestone_id, slice_id, task_id, evidence_id),
        FOREIGN KEY (milestone_id, slice_id, task_id) REFERENCES tasks(milestone_id, slice_id, id)
      )
    `);

	// Indices for efficient querying of evidence trails
	db.exec(`
      CREATE INDEX IF NOT EXISTS idx_milestone_evidence_type 
      ON milestone_evidence(milestone_id, evidence_type, recorded_at DESC)
    `);
	db.exec(`
      CREATE INDEX IF NOT EXISTS idx_slice_evidence_type 
      ON slice_evidence(milestone_id, slice_id, evidence_type, recorded_at DESC)
    `);
	db.exec(`
      CREATE INDEX IF NOT EXISTS idx_task_evidence_type 
      ON task_evidence(milestone_id, slice_id, task_id, evidence_type, recorded_at DESC)
    `);
}
export function initSchema(db, fileBacked, options = {}) {
	const { currentPath = null, withQueryTimeout = defaultQueryTimeout } =
		options;
	if (fileBacked) db.exec("PRAGMA journal_mode=WAL");
	if (fileBacked) db.exec("PRAGMA busy_timeout = 5000");
	if (fileBacked) db.exec("PRAGMA synchronous = NORMAL");
	// Disable SQLite's automatic WAL checkpoint (default: every 1000 pages).
	// Auto-checkpoint fires at unpredictable times — if the process is killed
	// mid-checkpoint (e.g., OOM), the main DB is partially written with an
	// empty WAL and cannot be recovered. Explicit checkpoints are issued at
	// safe loop boundaries instead (post-unit finalize, close).
	if (fileBacked) db.exec("PRAGMA wal_autocheckpoint=0");
	// auto_vacuum=NONE removed the ptrmap pages that caused the 2026-05-17
	// "Bad ptr map entry" corruption when multiple node:sqlite writers
	// concurrently mutated the requirements btree (rebuilt DB has none).
	// The incremental_vacuum() calls in sf-db-core.js become no-ops, which
	// is fine — the DB is small and disk pressure is handled separately.
	if (fileBacked) db.exec("PRAGMA auto_vacuum = NONE");
	if (fileBacked) db.exec("PRAGMA cache_size = -8000"); // 8 MB page cache
	if (fileBacked && process.platform !== "darwin")
		db.exec("PRAGMA mmap_size = 67108864"); // 64 MB mmap
	db.exec("PRAGMA temp_store = MEMORY");
	db.exec("PRAGMA foreign_keys = ON");
	db.exec("BEGIN");
	try {
		db.exec(`
      CREATE TABLE IF NOT EXISTS schema_version (
        version INTEGER NOT NULL,
        applied_at TEXT NOT NULL
      )
    `);
		db.exec(`
      CREATE TABLE IF NOT EXISTS decisions (
        seq INTEGER PRIMARY KEY AUTOINCREMENT,
        id TEXT NOT NULL UNIQUE,
        when_context TEXT NOT NULL DEFAULT '',
        scope TEXT NOT NULL DEFAULT '',
        decision TEXT NOT NULL DEFAULT '',
        choice TEXT NOT NULL DEFAULT '',
        rationale TEXT NOT NULL DEFAULT '',
        revisable TEXT NOT NULL DEFAULT '',
        made_by TEXT NOT NULL DEFAULT 'agent',
        superseded_by TEXT DEFAULT NULL
      )
    `);
		db.exec(`
      CREATE TABLE IF NOT EXISTS requirements (
        id TEXT PRIMARY KEY,
        class TEXT NOT NULL DEFAULT '',
        status TEXT NOT NULL DEFAULT '',
        description TEXT NOT NULL DEFAULT '',
        why TEXT NOT NULL DEFAULT '',
        source TEXT NOT NULL DEFAULT '',
        primary_owner TEXT NOT NULL DEFAULT '',
        supporting_slices TEXT NOT NULL DEFAULT '',
        validation TEXT NOT NULL DEFAULT '',
        notes TEXT NOT NULL DEFAULT '',
        full_content TEXT NOT NULL DEFAULT '',
        superseded_by TEXT DEFAULT NULL
      )
    `);
		db.exec(`
      CREATE TABLE IF NOT EXISTS artifacts (
        path TEXT PRIMARY KEY,
        artifact_type TEXT NOT NULL DEFAULT '',
        milestone_id TEXT DEFAULT NULL,
        slice_id TEXT DEFAULT NULL,
        task_id TEXT DEFAULT NULL,
        full_content TEXT NOT NULL DEFAULT '',
        imported_at TEXT NOT NULL DEFAULT ''
      )
    `);
		db.exec(`
      CREATE TABLE IF NOT EXISTS memories (
        seq INTEGER PRIMARY KEY AUTOINCREMENT,
        id TEXT NOT NULL UNIQUE,
        category TEXT NOT NULL,
        content TEXT NOT NULL,
        confidence REAL NOT NULL DEFAULT 0.8,
        source_unit_type TEXT,
        source_unit_id TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL,
        superseded_by TEXT DEFAULT NULL,
        hit_count INTEGER NOT NULL DEFAULT 0,
        tags TEXT NOT NULL DEFAULT '[]',
        tenant TEXT
      )
    `);
		db.exec(`
      CREATE TABLE IF NOT EXISTS memory_processed_units (
        unit_key TEXT PRIMARY KEY,
        activity_file TEXT,
        processed_at TEXT NOT NULL
      )
    `);
		db.exec(`
      CREATE TABLE IF NOT EXISTS memory_extraction_attempts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        unit_key TEXT NOT NULL,
        unit_type TEXT,
        unit_id TEXT,
        activity_file TEXT,
        status TEXT NOT NULL,
        failure_class TEXT,
        reason TEXT,
        error TEXT,
        created_at TEXT NOT NULL
      )
    `);
		db.exec(`
      CREATE INDEX IF NOT EXISTS idx_memory_extraction_attempts_created
      ON memory_extraction_attempts(created_at)
    `);
		// memory_embeddings, memory_relations, memory_sources used to be referenced
		// by helper functions and queries (memory-embeddings.ts, memory-relations.ts,
		// memory-ingest.ts) without a corresponding CREATE TABLE — any actual write
		// would have failed with "no such table". Creating them as IF NOT EXISTS so
		// existing DBs that somehow have them survive, and fresh DBs work.
		db.exec(`
      CREATE TABLE IF NOT EXISTS memory_embeddings (
        memory_id TEXT PRIMARY KEY,
        model TEXT NOT NULL,
        dim INTEGER NOT NULL,
        vector BLOB NOT NULL,
        updated_at TEXT NOT NULL,
        FOREIGN KEY (memory_id) REFERENCES memories(id) ON DELETE CASCADE
      )
    `);
		db.exec(`
      CREATE TABLE IF NOT EXISTS memory_relations (
        from_id TEXT NOT NULL,
        to_id TEXT NOT NULL,
        rel TEXT NOT NULL,
        confidence REAL NOT NULL DEFAULT 0.8,
        created_at TEXT NOT NULL,
        PRIMARY KEY (from_id, to_id, rel),
        FOREIGN KEY (from_id) REFERENCES memories(id) ON DELETE CASCADE,
        FOREIGN KEY (to_id) REFERENCES memories(id) ON DELETE CASCADE
      )
    `);
		// PK covers from_id as leading column already; reverse lookups
		// (memory-relations.ts queries WHERE to_id = ?) need their own index
		// to avoid a full table scan as the relation count grows.
		db.exec(
			"CREATE INDEX IF NOT EXISTS idx_memory_relations_to ON memory_relations(to_id)",
		);
		db.exec(`
      CREATE TABLE IF NOT EXISTS memory_sources (
        id TEXT PRIMARY KEY,
        kind TEXT NOT NULL,
        uri TEXT,
        title TEXT,
        content TEXT NOT NULL,
        content_hash TEXT NOT NULL,
        imported_at TEXT NOT NULL,
        scope TEXT NOT NULL DEFAULT 'project',
        tags TEXT NOT NULL DEFAULT '[]'
      )
    `);
		// content_hash is queried on every insert for deduplication; without an
		// index the lookup becomes a full table scan as ingestion volume grows.
		db.exec(
			"CREATE INDEX IF NOT EXISTS idx_memory_sources_content_hash ON memory_sources(content_hash)",
		);
		// Category GROUP BY queries (e.g. /memory stats) need a covering
		// index that filters active memories and groups by category.
		db.exec(
			"CREATE INDEX IF NOT EXISTS idx_memories_category ON memories(superseded_by, category)",
		);
		db.exec(`
      CREATE TABLE IF NOT EXISTS judgments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        unit_id TEXT NOT NULL,
        decision TEXT NOT NULL DEFAULT '',
        alternatives_json TEXT NOT NULL DEFAULT '[]',
        reasoning TEXT NOT NULL DEFAULT '',
        confidence TEXT NOT NULL DEFAULT 'medium',
        ts TEXT NOT NULL
      )
    `);
		db.exec(
			"CREATE INDEX IF NOT EXISTS idx_judgments_unit_id ON judgments(unit_id, ts DESC)",
		);
		db.exec(`
      CREATE TABLE IF NOT EXISTS milestones (
        id TEXT PRIMARY KEY,
        title TEXT NOT NULL DEFAULT '',
        status TEXT NOT NULL DEFAULT 'active',
        depends_on TEXT NOT NULL DEFAULT '[]',
        created_at TEXT NOT NULL DEFAULT '',
        completed_at TEXT DEFAULT NULL,
        vision TEXT NOT NULL DEFAULT '',
        success_criteria TEXT NOT NULL DEFAULT '[]',
        key_risks TEXT NOT NULL DEFAULT '[]',
        proof_strategy TEXT NOT NULL DEFAULT '[]',
        verification_contract TEXT NOT NULL DEFAULT '',
        verification_integration TEXT NOT NULL DEFAULT '',
        verification_operational TEXT NOT NULL DEFAULT '',
        verification_uat TEXT NOT NULL DEFAULT '',
        definition_of_done TEXT NOT NULL DEFAULT '[]',
        requirement_coverage TEXT NOT NULL DEFAULT '',
        boundary_map_markdown TEXT NOT NULL DEFAULT '',
        vision_meeting_json TEXT NOT NULL DEFAULT '',
        product_research_json TEXT NOT NULL DEFAULT '',
        sequence INTEGER DEFAULT 0,
        tier INTEGER NOT NULL DEFAULT 5
      )
    `);
		db.exec(`
      CREATE TABLE IF NOT EXISTS slices (
        milestone_id TEXT NOT NULL,
        id TEXT NOT NULL,
        title TEXT NOT NULL DEFAULT '',
        status TEXT NOT NULL DEFAULT 'pending',
        risk TEXT NOT NULL DEFAULT 'medium',
        depends TEXT NOT NULL DEFAULT '[]',
        demo TEXT NOT NULL DEFAULT '',
        created_at TEXT NOT NULL DEFAULT '',
        completed_at TEXT DEFAULT NULL,
        full_summary_md TEXT NOT NULL DEFAULT '',
        full_uat_md TEXT NOT NULL DEFAULT '',
        goal TEXT NOT NULL DEFAULT '',
        success_criteria TEXT NOT NULL DEFAULT '',
        proof_level TEXT NOT NULL DEFAULT '',
        integration_closure TEXT NOT NULL DEFAULT '',
        observability_impact TEXT NOT NULL DEFAULT '',
        adversarial_partner TEXT NOT NULL DEFAULT '',
        adversarial_combatant TEXT NOT NULL DEFAULT '',
        adversarial_architect TEXT NOT NULL DEFAULT '',
        planning_meeting_json TEXT NOT NULL DEFAULT '',
        sequence INTEGER DEFAULT 0, -- Ordering hint: tools may set this to control execution order
        replan_triggered_at TEXT DEFAULT NULL,
        is_sketch INTEGER NOT NULL DEFAULT 0, -- SF ADR-011: 1 = slice is a sketch awaiting refine-slice
        sketch_scope TEXT NOT NULL DEFAULT '', -- SF ADR-011: 2-3 sentence scope hint from plan-milestone
        traces_vision_fragment TEXT, -- ADR-0000 P2 (schema v69): literal milestone.vision clause this slice serves; structured vision trace for validate-milestone (NULL allowed for legacy rows)
        PRIMARY KEY (milestone_id, id),
        FOREIGN KEY (milestone_id) REFERENCES milestones(id)
      )
    `);
		db.exec(`
      CREATE TABLE IF NOT EXISTS tasks (
        milestone_id TEXT NOT NULL,
        slice_id TEXT NOT NULL,
        id TEXT NOT NULL,
        title TEXT NOT NULL DEFAULT '',
        status TEXT NOT NULL DEFAULT 'pending',
        one_liner TEXT NOT NULL DEFAULT '',
        narrative TEXT NOT NULL DEFAULT '',
        verification_result TEXT NOT NULL DEFAULT '',
        duration TEXT NOT NULL DEFAULT '',
        completed_at TEXT DEFAULT NULL,
        blocker_discovered INTEGER DEFAULT 0,
        deviations TEXT NOT NULL DEFAULT '',
        known_issues TEXT NOT NULL DEFAULT '',
        key_files TEXT NOT NULL DEFAULT '[]',
        key_decisions TEXT NOT NULL DEFAULT '[]',
        full_summary_md TEXT NOT NULL DEFAULT '',
        description TEXT NOT NULL DEFAULT '',
        estimate TEXT NOT NULL DEFAULT '',
        files TEXT NOT NULL DEFAULT '[]',
        verify TEXT NOT NULL DEFAULT '',
        inputs TEXT NOT NULL DEFAULT '[]',
        expected_output TEXT NOT NULL DEFAULT '[]',
        observability_impact TEXT NOT NULL DEFAULT '',
        full_plan_md TEXT NOT NULL DEFAULT '',
        created_at TEXT NOT NULL DEFAULT '',
        verification_status TEXT NOT NULL DEFAULT '',
        risk TEXT NOT NULL DEFAULT 'low',
        mutation_scope TEXT NOT NULL DEFAULT 'isolated',
        verification_type TEXT NOT NULL DEFAULT 'self-check',
        plan_approval TEXT NOT NULL DEFAULT 'not-required',
        task_status TEXT NOT NULL DEFAULT 'todo',
        estimated_effort INTEGER DEFAULT NULL,
        dependencies TEXT NOT NULL DEFAULT '[]',
        blocks_parallel INTEGER NOT NULL DEFAULT 0,
        requires_user_input INTEGER NOT NULL DEFAULT 0,
        auto_retry INTEGER NOT NULL DEFAULT 1,
        max_retries INTEGER NOT NULL DEFAULT 2,
        frontmatter_version INTEGER NOT NULL DEFAULT 1,
        sequence INTEGER DEFAULT 0, -- Ordering hint: tools may set this to control execution order
        escalation_pending INTEGER NOT NULL DEFAULT 0, -- ADR-011 P2 (SF): pause-on-escalation flag
        escalation_awaiting_review INTEGER NOT NULL DEFAULT 0, -- ADR-011 P2 (SF): continueWithDefault=true marker (no pause)
        escalation_override_applied INTEGER NOT NULL DEFAULT 0, -- SF ADR-011 P2: 1 once carry-forward injected into a downstream prompt
        escalation_artifact_path TEXT DEFAULT NULL, -- ADR-011 P2 (SF): path to T##-ESCALATION.json
        purpose_trace TEXT DEFAULT NULL, -- ADR-0000 (SF v70): goal-anchored sentence the executor recorded at complete_task time
        PRIMARY KEY (milestone_id, slice_id, id),
        FOREIGN KEY (milestone_id, slice_id) REFERENCES slices(milestone_id, id)
      )
    `);
		ensureTaskSchedulerTable(db);
		if (columnExists(db, "tasks", "escalation_pending")) {
			db.exec(`
	      CREATE INDEX IF NOT EXISTS idx_tasks_escalation_pending ON tasks(milestone_id, slice_id, escalation_pending)
	    `);
		}
		db.exec(`
      CREATE TABLE IF NOT EXISTS verification_evidence (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        task_id TEXT NOT NULL DEFAULT '',
        slice_id TEXT NOT NULL DEFAULT '',
        milestone_id TEXT NOT NULL DEFAULT '',
        command TEXT NOT NULL DEFAULT '',
        exit_code INTEGER DEFAULT 0,
        verdict TEXT NOT NULL DEFAULT '',
        duration_ms INTEGER DEFAULT 0,
        created_at TEXT NOT NULL DEFAULT '',
        FOREIGN KEY (milestone_id, slice_id, task_id) REFERENCES tasks(milestone_id, slice_id, id)
      )
    `);
		db.exec(`
      CREATE TABLE IF NOT EXISTS replan_history (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        milestone_id TEXT NOT NULL DEFAULT '',
        slice_id TEXT DEFAULT NULL,
        task_id TEXT DEFAULT NULL,
        summary TEXT NOT NULL DEFAULT '',
        previous_artifact_path TEXT DEFAULT NULL,
        replacement_artifact_path TEXT DEFAULT NULL,
        created_at TEXT NOT NULL DEFAULT '',
        FOREIGN KEY (milestone_id) REFERENCES milestones(id)
      )
    `);
		db.exec(`
      CREATE TABLE IF NOT EXISTS assessments (
        path TEXT PRIMARY KEY,
        milestone_id TEXT NOT NULL DEFAULT '',
        slice_id TEXT DEFAULT NULL,
        task_id TEXT DEFAULT NULL,
        status TEXT NOT NULL DEFAULT '',
        scope TEXT NOT NULL DEFAULT '',
        full_content TEXT NOT NULL DEFAULT '',
        created_at TEXT NOT NULL DEFAULT '',
        FOREIGN KEY (milestone_id) REFERENCES milestones(id)
      )
    `);
		db.exec(`
      CREATE TABLE IF NOT EXISTS quality_gates (
        milestone_id TEXT NOT NULL,
        slice_id TEXT NOT NULL,
        gate_id TEXT NOT NULL,
        scope TEXT NOT NULL DEFAULT 'slice',
        task_id TEXT NOT NULL DEFAULT '',
        status TEXT NOT NULL DEFAULT 'pending',
        verdict TEXT NOT NULL DEFAULT '',
        rationale TEXT NOT NULL DEFAULT '',
        findings TEXT NOT NULL DEFAULT '',
        evaluated_at TEXT DEFAULT NULL,
        -- Schema v2 metadata (v66): populated by the UOK adapter; NULL on
        -- pre-v2 rows. headless-uok-status classifyCoverage reads surface
        -- as the schema-v2 indicator.
        surface TEXT,
        run_control TEXT,
        permission_profile TEXT,
        trace_id TEXT,
        parent_trace TEXT,
        PRIMARY KEY (milestone_id, slice_id, gate_id, task_id),
        FOREIGN KEY (milestone_id, slice_id) REFERENCES slices(milestone_id, id)
      )
    `);
		// Slice dependency junction table (v14)
		db.exec(`
      CREATE TABLE IF NOT EXISTS slice_dependencies (
        milestone_id TEXT NOT NULL,
        slice_id TEXT NOT NULL,
        depends_on_slice_id TEXT NOT NULL,
        PRIMARY KEY (milestone_id, slice_id, depends_on_slice_id),
        FOREIGN KEY (milestone_id, slice_id) REFERENCES slices(milestone_id, id),
        FOREIGN KEY (milestone_id, depends_on_slice_id) REFERENCES slices(milestone_id, id)
      )
    `);
		db.exec(`
      CREATE TABLE IF NOT EXISTS gate_circuit_breakers (
        gate_id TEXT PRIMARY KEY,
        state TEXT NOT NULL DEFAULT 'closed',
        failure_streak INTEGER NOT NULL DEFAULT 0,
        last_failure_at TEXT DEFAULT NULL,
        opened_at TEXT DEFAULT NULL,
        half_open_attempts INTEGER NOT NULL DEFAULT 0,
        updated_at TEXT NOT NULL DEFAULT ''
      )
    `);
		db.exec(`
      CREATE TABLE IF NOT EXISTS audit_turn_index (
        trace_id TEXT NOT NULL,
        turn_id TEXT NOT NULL,
        first_ts TEXT NOT NULL,
        last_ts TEXT NOT NULL,
        event_count INTEGER NOT NULL DEFAULT 0,
        PRIMARY KEY (trace_id, turn_id)
      )
    `);
		db.exec(`
      CREATE TABLE IF NOT EXISTS llm_task_outcomes (
        model_id TEXT NOT NULL,
        provider TEXT NOT NULL,
        unit_type TEXT NOT NULL,
        unit_id TEXT NOT NULL,
        succeeded INTEGER NOT NULL DEFAULT 0,
        retries INTEGER NOT NULL DEFAULT 0,
        escalated INTEGER NOT NULL DEFAULT 0,
        verification_passed INTEGER DEFAULT NULL,
        blocker_discovered INTEGER NOT NULL DEFAULT 0,
        duration_ms INTEGER DEFAULT NULL,
        tokens_total INTEGER DEFAULT NULL,
        cost_usd REAL DEFAULT NULL,
        failure_mode TEXT DEFAULT NULL,
        recorded_at INTEGER NOT NULL
      )
    `);
		db.exec(`
      CREATE TABLE IF NOT EXISTS uok_runs (
        run_id TEXT PRIMARY KEY,
        session_id TEXT DEFAULT NULL,
        path TEXT NOT NULL DEFAULT '',
        status TEXT NOT NULL DEFAULT 'started',
        started_at TEXT NOT NULL,
        ended_at TEXT DEFAULT NULL,
        error TEXT DEFAULT NULL,
        flags_json TEXT NOT NULL DEFAULT '{}',
        updated_at TEXT NOT NULL
      )
    `);
		ensureSelfFeedbackTables(db);
		ensureSolverEvalTables(db);
		ensureRetrievalEvidenceTables(db);
		db.exec(
			"CREATE INDEX IF NOT EXISTS idx_memories_active ON memories(superseded_by)",
		);
		db.exec(
			"CREATE INDEX IF NOT EXISTS idx_replan_history_milestone ON replan_history(milestone_id, created_at)",
		);
		// v13 indexes — hot-path dispatch queries
		db.exec(
			"CREATE INDEX IF NOT EXISTS idx_tasks_active ON tasks(milestone_id, slice_id, status)",
		);
		db.exec(
			"CREATE INDEX IF NOT EXISTS idx_slices_active ON slices(milestone_id, status)",
		);
		db.exec(
			"CREATE INDEX IF NOT EXISTS idx_milestones_status ON milestones(status)",
		);
		db.exec(
			"CREATE INDEX IF NOT EXISTS idx_quality_gates_pending ON quality_gates(milestone_id, slice_id, status)",
		);
		db.exec(
			"CREATE INDEX IF NOT EXISTS idx_verification_evidence_task ON verification_evidence(milestone_id, slice_id, task_id)",
		);
		ensureVerificationEvidenceDedupIndex(db);
		// v14 index — slice dependency lookups
		db.exec(
			"CREATE INDEX IF NOT EXISTS idx_slice_deps_target ON slice_dependencies(milestone_id, depends_on_slice_id)",
		);
		db.exec(
			"CREATE UNIQUE INDEX IF NOT EXISTS idx_llm_task_outcomes_identity ON llm_task_outcomes(unit_type, unit_id, recorded_at)",
		);
		db.exec(
			"CREATE INDEX IF NOT EXISTS idx_llm_task_outcomes_model_unit ON llm_task_outcomes(model_id, unit_type, recorded_at DESC)",
		);
		db.exec(
			"CREATE INDEX IF NOT EXISTS idx_llm_task_outcomes_unit ON llm_task_outcomes(unit_type, recorded_at DESC)",
		);
		db.exec(
			"CREATE INDEX IF NOT EXISTS idx_llm_task_outcomes_provider ON llm_task_outcomes(provider, recorded_at DESC)",
		);
		db.exec(
			"CREATE INDEX IF NOT EXISTS idx_uok_runs_status_started ON uok_runs(status, started_at DESC)",
		);
		db.exec(
			"CREATE INDEX IF NOT EXISTS idx_uok_runs_session ON uok_runs(session_id, started_at DESC)",
		);
		db.exec(
			"CREATE INDEX IF NOT EXISTS idx_self_feedback_open ON self_feedback(resolved_at, severity, ts)",
		);
		db.exec(
			"CREATE INDEX IF NOT EXISTS idx_self_feedback_kind ON self_feedback(kind, ts)",
		);
		ensureRepoProfileTables(db);
		ensureBacklogTables(db);
		ensureScheduleTables(db);
		ensureSolverEvalTables(db);
		ensureHeadlessRunTables(db);
		ensureSessionTables(db);
		ensureSessionSnapshotTable(db);
		ensureUokMessageTables(db);
		ensureDeployTables(db);
		ensureSleeptimeQueueTable(db);
		ensureSpecSchemaTables(db);
		ensureTaskFrontmatterColumns(db);
		ensureRetrievalEvidenceTables(db);
		ensureTriageTables(db);
		ensureRuntimeCounterTable(db);
		ensureValidationAttentionMarkersTable(db);
		ensureContextBoardTable(db);
		ensureSelfFeedbackRelationsTable(db);
		db.exec(
			`CREATE VIEW IF NOT EXISTS active_decisions AS SELECT * FROM decisions WHERE superseded_by IS NULL`,
		);
		db.exec(
			`CREATE VIEW IF NOT EXISTS active_requirements AS SELECT * FROM requirements WHERE superseded_by IS NULL`,
		);
		db.exec(
			`CREATE VIEW IF NOT EXISTS active_memories AS SELECT * FROM memories WHERE superseded_by IS NULL`,
		);
		db.exec(
			`CREATE VIEW IF NOT EXISTS active_tasks AS SELECT * FROM tasks WHERE status NOT IN ('done','complete','completed','cancelled')`,
		);
		db.exec(`
			CREATE VIEW IF NOT EXISTS v_task_full AS
			SELECT t.*, ts.spec_version, ts.verify AS spec_verify,
			       ts.inputs AS spec_inputs, ts.expected_output AS spec_expected_output
			FROM tasks t
			LEFT JOIN task_specs ts
			  ON t.milestone_id = ts.milestone_id
			 AND t.slice_id    = ts.slice_id
			 AND t.id          = ts.task_id
		`);
		const existing = db
			.prepare("SELECT count(*) as cnt FROM schema_version")
			.get();
		if (existing && existing["cnt"] === 0) {
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": SCHEMA_VERSION,
				":applied_at": new Date().toISOString(),
			});
		}
		db.exec("COMMIT");
	} catch (err) {
		db.exec("ROLLBACK");
		throw err;
	}
	migrateSchema(db, { currentPath, withQueryTimeout });
}
function columnExists(db, table, column) {
	const rows = db.prepare(`PRAGMA table_info(${table})`).all();
	return rows.some((row) => row["name"] === column);
}
function tableExists(db, table) {
	const row = db
		.prepare(`SELECT name FROM sqlite_master WHERE type='table' AND name=?`)
		.get(table);
	return row != null;
}
function ensureColumn(db, table, column, ddl) {
	if (!columnExists(db, table, column)) db.exec(ddl);
}
function ensureTaskCreatedAtColumn(db) {
	ensureColumn(
		db,
		"tasks",
		"created_at",
		`ALTER TABLE tasks ADD COLUMN created_at TEXT NOT NULL DEFAULT ''`,
	);
}
function ensureTaskFrontmatterColumns(db) {
	ensureColumn(
		db,
		"tasks",
		"risk",
		`ALTER TABLE tasks ADD COLUMN risk TEXT NOT NULL DEFAULT 'low'`,
	);
	ensureColumn(
		db,
		"tasks",
		"mutation_scope",
		`ALTER TABLE tasks ADD COLUMN mutation_scope TEXT NOT NULL DEFAULT 'isolated'`,
	);
	ensureColumn(
		db,
		"tasks",
		"verification_type",
		`ALTER TABLE tasks ADD COLUMN verification_type TEXT NOT NULL DEFAULT 'self-check'`,
	);
	ensureColumn(
		db,
		"tasks",
		"plan_approval",
		`ALTER TABLE tasks ADD COLUMN plan_approval TEXT NOT NULL DEFAULT 'not-required'`,
	);
	ensureColumn(
		db,
		"tasks",
		"task_status",
		`ALTER TABLE tasks ADD COLUMN task_status TEXT NOT NULL DEFAULT 'todo'`,
	);
	ensureColumn(
		db,
		"tasks",
		"estimated_effort",
		`ALTER TABLE tasks ADD COLUMN estimated_effort INTEGER DEFAULT NULL`,
	);
	ensureColumn(
		db,
		"tasks",
		"dependencies",
		`ALTER TABLE tasks ADD COLUMN dependencies TEXT NOT NULL DEFAULT '[]'`,
	);
	ensureColumn(
		db,
		"tasks",
		"blocks_parallel",
		`ALTER TABLE tasks ADD COLUMN blocks_parallel INTEGER NOT NULL DEFAULT 0`,
	);
	ensureColumn(
		db,
		"tasks",
		"requires_user_input",
		`ALTER TABLE tasks ADD COLUMN requires_user_input INTEGER NOT NULL DEFAULT 0`,
	);
	ensureColumn(
		db,
		"tasks",
		"auto_retry",
		`ALTER TABLE tasks ADD COLUMN auto_retry INTEGER NOT NULL DEFAULT 1`,
	);
	ensureColumn(
		db,
		"tasks",
		"max_retries",
		`ALTER TABLE tasks ADD COLUMN max_retries INTEGER NOT NULL DEFAULT 2`,
	);
	for (const table of ["task_specs"]) {
		ensureColumn(
			db,
			table,
			"risk",
			`ALTER TABLE ${table} ADD COLUMN risk TEXT NOT NULL DEFAULT 'low'`,
		);
		ensureColumn(
			db,
			table,
			"mutation_scope",
			`ALTER TABLE ${table} ADD COLUMN mutation_scope TEXT NOT NULL DEFAULT 'isolated'`,
		);
		ensureColumn(
			db,
			table,
			"verification_type",
			`ALTER TABLE ${table} ADD COLUMN verification_type TEXT NOT NULL DEFAULT 'self-check'`,
		);
		ensureColumn(
			db,
			table,
			"plan_approval",
			`ALTER TABLE ${table} ADD COLUMN plan_approval TEXT NOT NULL DEFAULT 'not-required'`,
		);
		ensureColumn(
			db,
			table,
			"estimated_effort",
			`ALTER TABLE ${table} ADD COLUMN estimated_effort INTEGER DEFAULT NULL`,
		);
		ensureColumn(
			db,
			table,
			"dependencies",
			`ALTER TABLE ${table} ADD COLUMN dependencies TEXT NOT NULL DEFAULT '[]'`,
		);
		ensureColumn(
			db,
			table,
			"blocks_parallel",
			`ALTER TABLE ${table} ADD COLUMN blocks_parallel INTEGER NOT NULL DEFAULT 0`,
		);
		ensureColumn(
			db,
			table,
			"requires_user_input",
			`ALTER TABLE ${table} ADD COLUMN requires_user_input INTEGER NOT NULL DEFAULT 0`,
		);
		ensureColumn(
			db,
			table,
			"auto_retry",
			`ALTER TABLE ${table} ADD COLUMN auto_retry INTEGER NOT NULL DEFAULT 1`,
		);
		ensureColumn(
			db,
			table,
			"max_retries",
			`ALTER TABLE ${table} ADD COLUMN max_retries INTEGER NOT NULL DEFAULT 2`,
		);
	}
}
function ensureTaskSchedulerTable(db) {
	db.exec(`
      CREATE TABLE IF NOT EXISTS task_scheduler (
        milestone_id TEXT NOT NULL,
        slice_id TEXT NOT NULL,
        task_id TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT 'queued',
        due_at TEXT DEFAULT NULL,
        claimed_by TEXT DEFAULT NULL,
        dispatched_at TEXT DEFAULT NULL,
        consumed_at TEXT DEFAULT NULL,
        expires_at TEXT DEFAULT NULL,
        updated_at TEXT NOT NULL DEFAULT '',
        PRIMARY KEY (milestone_id, slice_id, task_id),
        FOREIGN KEY (milestone_id, slice_id, task_id) REFERENCES tasks(milestone_id, slice_id, id)
      )
    `);
	db.exec(`
      CREATE INDEX IF NOT EXISTS idx_task_scheduler_status
      ON task_scheduler(status, due_at)
    `);
}
function migrateCostUsdToMicroUsd(db) {
	// Tier 2.7: Migrate cost_usd REAL to cost_micro_usd INTEGER
	// Converts floating-point USD values to integer micro-USD (multiply by 1,000,000)
	// Benefits: eliminates float drift on accumulated costs, easier reasoning about totals
	// Purpose: Enable accurate cost tracking at scale without rounding errors
	// Consumer: gate_runs cost tracking, cost analytics, budget checks

	// Guard: gate_runs may not exist in minimal legacy DBs (it will be dropped in v58)
	if (!tableExists(db, "gate_runs")) return;

	// Add cost_micro_usd column if it doesn't exist
	if (!columnExists(db, "gate_runs", "cost_micro_usd")) {
		db.exec(
			`ALTER TABLE gate_runs ADD COLUMN cost_micro_usd INTEGER DEFAULT NULL`,
		);
	}

	// Migrate data: convert cost_usd to cost_micro_usd
	// NULL values stay NULL; non-NULL values are multiplied by 1,000,000
	if (columnExists(db, "gate_runs", "cost_usd")) {
		db.prepare(`
			UPDATE gate_runs
			SET cost_micro_usd = CAST(ROUND(cost_usd * 1000000) AS INTEGER)
			WHERE cost_usd IS NOT NULL
			  AND cost_micro_usd IS NULL
		`).run();
	}

	// Drop old cost_usd column (SQLite ALTER TABLE DROP is only available in 3.35.0+)
	// For safety, we keep the old column as deprecated but unused
	// Future: drop after confirming all queries use cost_micro_usd
}
function populateSpecTablesFromExisting(db) {
	// Tier 1.3 Phase 2: Migrate existing spec data to new spec tables
	// This populates milestone_specs, slice_specs, task_specs from existing columns
	// Evidence tables are left empty; they populate as tools create new evidence.

	const now = new Date().toISOString();

	// Migrate milestone specs
	db.prepare(`
    INSERT OR IGNORE INTO milestone_specs (
      id, vision, success_criteria, key_risks, proof_strategy,
      verification_contract, verification_integration, verification_operational, verification_uat,
      definition_of_done, requirement_coverage, boundary_map_markdown, vision_meeting_json, product_research_json,
      spec_version, created_at
    )
    SELECT
      id, vision, success_criteria, key_risks, proof_strategy,
      verification_contract, verification_integration, verification_operational, verification_uat,
      definition_of_done, requirement_coverage, boundary_map_markdown, vision_meeting_json, '',
      1, COALESCE(created_at, ?)
    FROM milestones
    WHERE id NOT IN (SELECT id FROM milestone_specs)
  `).run(now);

	// Migrate slice specs
	db.prepare(`
    INSERT OR IGNORE INTO slice_specs (
      milestone_id, slice_id, goal, success_criteria, proof_level,
      integration_closure, observability_impact,
      adversarial_partner, adversarial_combatant, adversarial_architect,
      planning_meeting_json, spec_version, created_at
    )
    SELECT
      milestone_id, id, goal, success_criteria, proof_level,
      integration_closure, observability_impact,
      adversarial_partner, adversarial_combatant, adversarial_architect,
      planning_meeting_json, 1, COALESCE(created_at, ?)
    FROM slices
    WHERE (milestone_id, id) NOT IN (SELECT milestone_id, slice_id FROM slice_specs)
  `).run(now);

	// Migrate task specs
	db.prepare(`
    INSERT OR IGNORE INTO task_specs (
      milestone_id, slice_id, task_id, verify, inputs, expected_output,
      spec_version, created_at
    )
    SELECT
      milestone_id, slice_id, id, verify, inputs, expected_output,
      1, COALESCE(created_at, ?)
    FROM tasks
    WHERE (milestone_id, slice_id, id) NOT IN (SELECT milestone_id, slice_id, task_id FROM task_specs)
  `).run(now);
}
function migrateSchema(db, { currentPath, withQueryTimeout }) {
	const row = withQueryTimeout(
		() => db.prepare("SELECT MAX(version) as v FROM schema_version").get(),
		null,
	);
	const currentVersion = row ? row["v"] : 0;
	if (currentVersion >= SCHEMA_VERSION) return;
	// Backup database before migration so a mid-migration crash doesn't
	// leave a partially-migrated DB with no recovery path.
	// WAL-safe: checkpoint first to flush WAL into the main DB file, then copy.
	if (currentPath && currentPath !== ":memory:" && existsSync(currentPath)) {
		try {
			const backupPath = `${currentPath}.backup-v${currentVersion}`;
			if (!existsSync(backupPath)) {
				// Flush WAL to main DB file before copying — without this, the backup
				// may be missing committed data that only exists in the -wal file.
				try {
					db.exec("PRAGMA wal_checkpoint(TRUNCATE)");
				} catch {
					/* checkpoint is best-effort */
				}
				copyFileSync(currentPath, backupPath);
			}
		} catch (backupErr) {
			// Log but proceed — blocking migration leaves the DB stuck at an old
			// schema version permanently on read-only or full filesystems.
			logWarning(
				"db",
				`Pre-migration backup failed: ${getErrorMessage(backupErr)}`,
			);
		}
	}

	// Per-migration transaction boundaries: each migration step commits
	// independently so a failure in a late migration does not roll back
	// already-successful earlier migrations. This prevents the "stuck at
	// v53 forever" scenario where a v62 failure rolled back v53 DDL.
	// (See self-feedback sf-mp36kfqm-rjrzju.)
	function runMigrationStep(label, fn) {
		db.exec("BEGIN");
		try {
			fn();
			db.exec("COMMIT");
			return true;
		} catch (err) {
			try {
				db.exec("ROLLBACK");
			} catch {
				/* rollback is best-effort */
			}
			logWarning(
				"db",
				`Schema migration step ${label} failed: ${getErrorMessage(err)}. Database remains at its previously committed version.`,
			);
			return false;
		}
	}

	let appliedVersion = currentVersion;
	if (appliedVersion < 2) {
		const ok = runMigrationStep("v2", () => {
			db.exec(`
				        CREATE TABLE IF NOT EXISTS artifacts (
				          path TEXT PRIMARY KEY,
				          artifact_type TEXT NOT NULL DEFAULT '',
				          milestone_id TEXT DEFAULT NULL,
				          slice_id TEXT DEFAULT NULL,
				          task_id TEXT DEFAULT NULL,
				          full_content TEXT NOT NULL DEFAULT '',
				          imported_at TEXT NOT NULL DEFAULT ''
				        )
				      `);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 2,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 2;
	}
	if (appliedVersion < 3) {
		const ok = runMigrationStep("v3", () => {
			db.exec(`
				        CREATE TABLE IF NOT EXISTS memories (
				          seq INTEGER PRIMARY KEY AUTOINCREMENT,
				          id TEXT NOT NULL UNIQUE,
				          category TEXT NOT NULL,
				          content TEXT NOT NULL,
				          confidence REAL NOT NULL DEFAULT 0.8,
				          source_unit_type TEXT,
				          source_unit_id TEXT,
				          created_at TEXT NOT NULL,
				          updated_at TEXT NOT NULL,
				          superseded_by TEXT DEFAULT NULL,
				          hit_count INTEGER NOT NULL DEFAULT 0
				        )
				      `);
			db.exec(`
				        CREATE TABLE IF NOT EXISTS memory_processed_units (
				          unit_key TEXT PRIMARY KEY,
				          activity_file TEXT,
				          processed_at TEXT NOT NULL
				        )
				      `);
			db.exec(`
				        CREATE TABLE IF NOT EXISTS memory_extraction_attempts (
				          id INTEGER PRIMARY KEY AUTOINCREMENT,
				          unit_key TEXT NOT NULL,
				          unit_type TEXT,
				          unit_id TEXT,
				          activity_file TEXT,
				          status TEXT NOT NULL,
				          failure_class TEXT,
				          reason TEXT,
				          error TEXT,
				          created_at TEXT NOT NULL
				        )
				      `);
			db.exec(`
				        CREATE INDEX IF NOT EXISTS idx_memory_extraction_attempts_created
				        ON memory_extraction_attempts(created_at)
				      `);
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_memories_active ON memories(superseded_by)",
			);
			db.exec("DROP VIEW IF EXISTS active_memories");
			db.exec(
				"CREATE VIEW active_memories AS SELECT * FROM memories WHERE superseded_by IS NULL",
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 3,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 3;
	}
	if (appliedVersion < 4) {
		const ok = runMigrationStep("v4", () => {
			ensureColumn(
				db,
				"decisions",
				"made_by",
				`ALTER TABLE decisions ADD COLUMN made_by TEXT NOT NULL DEFAULT 'agent'`,
			);
			db.exec("DROP VIEW IF EXISTS active_decisions");
			db.exec(
				"CREATE VIEW active_decisions AS SELECT * FROM decisions WHERE superseded_by IS NULL",
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 4,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 4;
	}
	if (appliedVersion < 5) {
		const ok = runMigrationStep("v5", () => {
			db.exec(`
				        CREATE TABLE IF NOT EXISTS milestones (
				          id TEXT PRIMARY KEY,
				          title TEXT NOT NULL DEFAULT '',
				          status TEXT NOT NULL DEFAULT 'active',
				          created_at TEXT NOT NULL,
				          completed_at TEXT DEFAULT NULL
				        )
				      `);
			db.exec(`
				        CREATE TABLE IF NOT EXISTS slices (
				          milestone_id TEXT NOT NULL,
				          id TEXT NOT NULL,
				          title TEXT NOT NULL DEFAULT '',
				          status TEXT NOT NULL DEFAULT 'pending',
				          risk TEXT NOT NULL DEFAULT 'medium',
				          created_at TEXT NOT NULL DEFAULT '',
				          completed_at TEXT DEFAULT NULL,
				          PRIMARY KEY (milestone_id, id),
				          FOREIGN KEY (milestone_id) REFERENCES milestones(id)
				        )
				      `);
			db.exec(`
				        CREATE TABLE IF NOT EXISTS tasks (
				          milestone_id TEXT NOT NULL,
				          slice_id TEXT NOT NULL,
				          id TEXT NOT NULL,
				          title TEXT NOT NULL DEFAULT '',
				          status TEXT NOT NULL DEFAULT 'pending',
				          one_liner TEXT NOT NULL DEFAULT '',
				          narrative TEXT NOT NULL DEFAULT '',
				          verification_result TEXT NOT NULL DEFAULT '',
				          duration TEXT NOT NULL DEFAULT '',
				          completed_at TEXT DEFAULT NULL,
				          blocker_discovered INTEGER DEFAULT 0,
				          deviations TEXT NOT NULL DEFAULT '',
				          known_issues TEXT NOT NULL DEFAULT '',
				          key_files TEXT NOT NULL DEFAULT '[]',
				          key_decisions TEXT NOT NULL DEFAULT '[]',
				          full_summary_md TEXT NOT NULL DEFAULT '',
				          PRIMARY KEY (milestone_id, slice_id, id),
				          FOREIGN KEY (milestone_id, slice_id) REFERENCES slices(milestone_id, id)
				        )
				      `);
			db.exec(`
				        CREATE TABLE IF NOT EXISTS verification_evidence (
				          id INTEGER PRIMARY KEY AUTOINCREMENT,
				          task_id TEXT NOT NULL DEFAULT '',
				          slice_id TEXT NOT NULL DEFAULT '',
				          milestone_id TEXT NOT NULL DEFAULT '',
				          command TEXT NOT NULL DEFAULT '',
				          exit_code INTEGER DEFAULT 0,
				          verdict TEXT NOT NULL DEFAULT '',
				          duration_ms INTEGER DEFAULT 0,
				          created_at TEXT NOT NULL DEFAULT '',
				          FOREIGN KEY (milestone_id, slice_id, task_id) REFERENCES tasks(milestone_id, slice_id, id)
				        )
				      `);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 5,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 5;
	}
	if (appliedVersion < 6) {
		const ok = runMigrationStep("v6", () => {
			ensureColumn(
				db,
				"slices",
				"full_summary_md",
				`ALTER TABLE slices ADD COLUMN full_summary_md TEXT NOT NULL DEFAULT ''`,
			);
			ensureColumn(
				db,
				"slices",
				"full_uat_md",
				`ALTER TABLE slices ADD COLUMN full_uat_md TEXT NOT NULL DEFAULT ''`,
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 6,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 6;
	}
	if (appliedVersion < 7) {
		const ok = runMigrationStep("v7", () => {
			ensureColumn(
				db,
				"slices",
				"depends",
				`ALTER TABLE slices ADD COLUMN depends TEXT NOT NULL DEFAULT '[]'`,
			);
			ensureColumn(
				db,
				"slices",
				"demo",
				`ALTER TABLE slices ADD COLUMN demo TEXT NOT NULL DEFAULT ''`,
			);
			ensureColumn(
				db,
				"milestones",
				"depends_on",
				`ALTER TABLE milestones ADD COLUMN depends_on TEXT NOT NULL DEFAULT '[]'`,
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 7,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 7;
	}
	if (appliedVersion < 8) {
		const ok = runMigrationStep("v8", () => {
			ensureColumn(
				db,
				"milestones",
				"vision",
				`ALTER TABLE milestones ADD COLUMN vision TEXT NOT NULL DEFAULT ''`,
			);
			ensureColumn(
				db,
				"milestones",
				"success_criteria",
				`ALTER TABLE milestones ADD COLUMN success_criteria TEXT NOT NULL DEFAULT '[]'`,
			);
			ensureColumn(
				db,
				"milestones",
				"key_risks",
				`ALTER TABLE milestones ADD COLUMN key_risks TEXT NOT NULL DEFAULT '[]'`,
			);
			ensureColumn(
				db,
				"milestones",
				"proof_strategy",
				`ALTER TABLE milestones ADD COLUMN proof_strategy TEXT NOT NULL DEFAULT '[]'`,
			);
			ensureColumn(
				db,
				"milestones",
				"verification_contract",
				`ALTER TABLE milestones ADD COLUMN verification_contract TEXT NOT NULL DEFAULT ''`,
			);
			ensureColumn(
				db,
				"milestones",
				"verification_integration",
				`ALTER TABLE milestones ADD COLUMN verification_integration TEXT NOT NULL DEFAULT ''`,
			);
			ensureColumn(
				db,
				"milestones",
				"verification_operational",
				`ALTER TABLE milestones ADD COLUMN verification_operational TEXT NOT NULL DEFAULT ''`,
			);
			ensureColumn(
				db,
				"milestones",
				"verification_uat",
				`ALTER TABLE milestones ADD COLUMN verification_uat TEXT NOT NULL DEFAULT ''`,
			);
			ensureColumn(
				db,
				"milestones",
				"definition_of_done",
				`ALTER TABLE milestones ADD COLUMN definition_of_done TEXT NOT NULL DEFAULT '[]'`,
			);
			ensureColumn(
				db,
				"milestones",
				"requirement_coverage",
				`ALTER TABLE milestones ADD COLUMN requirement_coverage TEXT NOT NULL DEFAULT ''`,
			);
			ensureColumn(
				db,
				"milestones",
				"boundary_map_markdown",
				`ALTER TABLE milestones ADD COLUMN boundary_map_markdown TEXT NOT NULL DEFAULT ''`,
			);
			ensureColumn(
				db,
				"slices",
				"goal",
				`ALTER TABLE slices ADD COLUMN goal TEXT NOT NULL DEFAULT ''`,
			);
			ensureColumn(
				db,
				"slices",
				"success_criteria",
				`ALTER TABLE slices ADD COLUMN success_criteria TEXT NOT NULL DEFAULT ''`,
			);
			ensureColumn(
				db,
				"slices",
				"proof_level",
				`ALTER TABLE slices ADD COLUMN proof_level TEXT NOT NULL DEFAULT ''`,
			);
			ensureColumn(
				db,
				"slices",
				"integration_closure",
				`ALTER TABLE slices ADD COLUMN integration_closure TEXT NOT NULL DEFAULT ''`,
			);
			ensureColumn(
				db,
				"slices",
				"observability_impact",
				`ALTER TABLE slices ADD COLUMN observability_impact TEXT NOT NULL DEFAULT ''`,
			);
			ensureColumn(
				db,
				"slices",
				"uat_verdict",
				`ALTER TABLE slices ADD COLUMN uat_verdict TEXT DEFAULT NULL`,
			);
			ensureColumn(
				db,
				"tasks",
				"description",
				`ALTER TABLE tasks ADD COLUMN description TEXT NOT NULL DEFAULT ''`,
			);
			ensureColumn(
				db,
				"tasks",
				"estimate",
				`ALTER TABLE tasks ADD COLUMN estimate TEXT NOT NULL DEFAULT ''`,
			);
			ensureColumn(
				db,
				"tasks",
				"files",
				`ALTER TABLE tasks ADD COLUMN files TEXT NOT NULL DEFAULT '[]'`,
			);
			ensureColumn(
				db,
				"tasks",
				"verify",
				`ALTER TABLE tasks ADD COLUMN verify TEXT NOT NULL DEFAULT ''`,
			);
			ensureColumn(
				db,
				"tasks",
				"inputs",
				`ALTER TABLE tasks ADD COLUMN inputs TEXT NOT NULL DEFAULT '[]'`,
			);
			ensureColumn(
				db,
				"tasks",
				"expected_output",
				`ALTER TABLE tasks ADD COLUMN expected_output TEXT NOT NULL DEFAULT '[]'`,
			);
			ensureColumn(
				db,
				"tasks",
				"observability_impact",
				`ALTER TABLE tasks ADD COLUMN observability_impact TEXT NOT NULL DEFAULT ''`,
			);
			db.exec(`
				        CREATE TABLE IF NOT EXISTS replan_history (
				          id INTEGER PRIMARY KEY AUTOINCREMENT,
				          milestone_id TEXT NOT NULL DEFAULT '',
				          slice_id TEXT DEFAULT NULL,
				          task_id TEXT DEFAULT NULL,
				          summary TEXT NOT NULL DEFAULT '',
				          previous_artifact_path TEXT DEFAULT NULL,
				          replacement_artifact_path TEXT DEFAULT NULL,
				          created_at TEXT NOT NULL DEFAULT '',
				          FOREIGN KEY (milestone_id) REFERENCES milestones(id)
				        )
				      `);
			db.exec(`
				        CREATE TABLE IF NOT EXISTS assessments (
				          path TEXT PRIMARY KEY,
				          milestone_id TEXT NOT NULL DEFAULT '',
				          slice_id TEXT DEFAULT NULL,
				          task_id TEXT DEFAULT NULL,
				          status TEXT NOT NULL DEFAULT '',
				          scope TEXT NOT NULL DEFAULT '',
				          full_content TEXT NOT NULL DEFAULT '',
				          created_at TEXT NOT NULL DEFAULT '',
				          FOREIGN KEY (milestone_id) REFERENCES milestones(id)
				        )
				      `);
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_replan_history_milestone ON replan_history(milestone_id, created_at)",
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 8,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 8;
	}
	if (appliedVersion < 9) {
		const ok = runMigrationStep("v9", () => {
			ensureColumn(
				db,
				"slices",
				"sequence",
				`ALTER TABLE slices ADD COLUMN sequence INTEGER DEFAULT 0`,
			);
			ensureColumn(
				db,
				"tasks",
				"sequence",
				`ALTER TABLE tasks ADD COLUMN sequence INTEGER DEFAULT 0`,
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 9,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 9;
	}
	if (appliedVersion < 10) {
		const ok = runMigrationStep("v10", () => {
			ensureColumn(
				db,
				"slices",
				"replan_triggered_at",
				`ALTER TABLE slices ADD COLUMN replan_triggered_at TEXT DEFAULT NULL`,
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 10,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 10;
	}
	if (appliedVersion < 11) {
		const ok = runMigrationStep("v11", () => {
			ensureColumn(
				db,
				"tasks",
				"full_plan_md",
				`ALTER TABLE tasks ADD COLUMN full_plan_md TEXT NOT NULL DEFAULT ''`,
			);
			// Add unique constraint to replan_history for idempotency:
			// one replan record per blocker task per slice per milestone.
			db.exec(`
				        CREATE UNIQUE INDEX IF NOT EXISTS idx_replan_history_unique
				        ON replan_history(milestone_id, slice_id, task_id)
				        WHERE slice_id IS NOT NULL AND task_id IS NOT NULL
				      `);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 11,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 11;
	}
	if (appliedVersion < 12) {
		const ok = runMigrationStep("v12", () => {
			db.exec(`
				        CREATE TABLE IF NOT EXISTS quality_gates (
				          milestone_id TEXT NOT NULL,
				          slice_id TEXT NOT NULL,
				          gate_id TEXT NOT NULL,
				          scope TEXT NOT NULL DEFAULT 'slice',
				          task_id TEXT DEFAULT NULL,
				          status TEXT NOT NULL DEFAULT 'pending',
				          verdict TEXT NOT NULL DEFAULT '',
				          rationale TEXT NOT NULL DEFAULT '',
				          findings TEXT NOT NULL DEFAULT '',
				          evaluated_at TEXT DEFAULT NULL,
				          PRIMARY KEY (milestone_id, slice_id, gate_id, COALESCE(task_id, '')),
				          FOREIGN KEY (milestone_id, slice_id) REFERENCES slices(milestone_id, id)
				        )
				      `);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 12,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 12;
	}
	if (appliedVersion < 13) {
		const ok = runMigrationStep("v13", () => {
			// Hot-path indexes for auto-loop dispatch queries
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_tasks_active ON tasks(milestone_id, slice_id, status)",
			);
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_slices_active ON slices(milestone_id, status)",
			);
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_milestones_status ON milestones(status)",
			);
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_quality_gates_pending ON quality_gates(milestone_id, slice_id, status)",
			);
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_verification_evidence_task ON verification_evidence(milestone_id, slice_id, task_id)",
			);
			ensureVerificationEvidenceDedupIndex(db);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 13,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 13;
	}
	if (appliedVersion < 14) {
		const ok = runMigrationStep("v14", () => {
			db.exec(`
				        CREATE TABLE IF NOT EXISTS slice_dependencies (
				          milestone_id TEXT NOT NULL,
				          slice_id TEXT NOT NULL,
				          depends_on_slice_id TEXT NOT NULL,
				          PRIMARY KEY (milestone_id, slice_id, depends_on_slice_id),
				          FOREIGN KEY (milestone_id, slice_id) REFERENCES slices(milestone_id, id),
				          FOREIGN KEY (milestone_id, depends_on_slice_id) REFERENCES slices(milestone_id, id)
				        )
				      `);
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_slice_deps_target ON slice_dependencies(milestone_id, depends_on_slice_id)",
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 14,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 14;
	}
	if (appliedVersion < 15) {
		const ok = runMigrationStep("v15", () => {
			db.exec(`
				        CREATE TABLE IF NOT EXISTS gate_runs (
				          id INTEGER PRIMARY KEY AUTOINCREMENT,
				          trace_id TEXT NOT NULL,
				          turn_id TEXT NOT NULL,
				          gate_id TEXT NOT NULL,
				          gate_type TEXT NOT NULL DEFAULT '',
				          unit_type TEXT DEFAULT NULL,
				          unit_id TEXT DEFAULT NULL,
				          milestone_id TEXT DEFAULT NULL,
				          slice_id TEXT DEFAULT NULL,
				          task_id TEXT DEFAULT NULL,
				          outcome TEXT NOT NULL DEFAULT 'pass',
				          failure_class TEXT NOT NULL DEFAULT 'none',
				          rationale TEXT NOT NULL DEFAULT '',
				          findings TEXT NOT NULL DEFAULT '',
				          attempt INTEGER NOT NULL DEFAULT 1,
				        max_attempts INTEGER NOT NULL DEFAULT 1,
				        retryable INTEGER NOT NULL DEFAULT 0,
				        evaluated_at TEXT NOT NULL DEFAULT '',
				        duration_ms INTEGER DEFAULT NULL,
				        cost_micro_usd INTEGER DEFAULT NULL
				      )
				    `);
			db.exec(`
				        CREATE TABLE IF NOT EXISTS turn_git_transactions (
				          trace_id TEXT NOT NULL,
				          turn_id TEXT NOT NULL,
				          unit_type TEXT DEFAULT NULL,
				          unit_id TEXT DEFAULT NULL,
				          stage TEXT NOT NULL DEFAULT 'turn-start',
				          action TEXT NOT NULL DEFAULT 'status-only',
				          push INTEGER NOT NULL DEFAULT 0,
				          status TEXT NOT NULL DEFAULT 'ok',
				          error TEXT DEFAULT NULL,
				          metadata_json TEXT NOT NULL DEFAULT '{}',
				          updated_at TEXT NOT NULL DEFAULT '',
				          PRIMARY KEY (trace_id, turn_id, stage)
				        )
				      `);
			db.exec(`
				        CREATE TABLE IF NOT EXISTS audit_events (
				          event_id TEXT PRIMARY KEY,
				          trace_id TEXT NOT NULL,
				          turn_id TEXT DEFAULT NULL,
				          caused_by TEXT DEFAULT NULL,
				          category TEXT NOT NULL,
				          type TEXT NOT NULL,
				          ts TEXT NOT NULL,
				          payload_json TEXT NOT NULL DEFAULT '{}'
				        )
				      `);
			db.exec(`
				        CREATE TABLE IF NOT EXISTS audit_turn_index (
				          trace_id TEXT NOT NULL,
				          turn_id TEXT NOT NULL,
				          first_ts TEXT NOT NULL,
				          last_ts TEXT NOT NULL,
				          event_count INTEGER NOT NULL DEFAULT 0,
				          PRIMARY KEY (trace_id, turn_id)
				        )
				      `);
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_gate_runs_turn ON gate_runs(trace_id, turn_id)",
			);
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_gate_runs_lookup ON gate_runs(milestone_id, slice_id, task_id, gate_id)",
			);
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_turn_git_tx_turn ON turn_git_transactions(trace_id, turn_id)",
			);
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_audit_events_trace ON audit_events(trace_id, ts)",
			);
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_audit_events_turn ON audit_events(trace_id, turn_id, ts)",
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 15,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 15;
	}
	if (appliedVersion < 16) {
		const ok = runMigrationStep("v16", () => {
			db.exec(`
				        CREATE TABLE IF NOT EXISTS llm_task_outcomes (
				          model_id TEXT NOT NULL,
				          provider TEXT NOT NULL,
				          unit_type TEXT NOT NULL,
				          unit_id TEXT NOT NULL,
				          succeeded INTEGER NOT NULL DEFAULT 0,
				          retries INTEGER NOT NULL DEFAULT 0,
				          escalated INTEGER NOT NULL DEFAULT 0,
				          verification_passed INTEGER DEFAULT NULL,
				          blocker_discovered INTEGER NOT NULL DEFAULT 0,
				          duration_ms INTEGER DEFAULT NULL,
				          tokens_total INTEGER DEFAULT NULL,
				          cost_usd REAL DEFAULT NULL,
				          failure_mode TEXT DEFAULT NULL,
				          recorded_at INTEGER NOT NULL
				        )
				      `);
			db.exec(
				"CREATE UNIQUE INDEX IF NOT EXISTS idx_llm_task_outcomes_identity ON llm_task_outcomes(unit_type, unit_id, recorded_at)",
			);
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_llm_task_outcomes_model_unit ON llm_task_outcomes(model_id, unit_type, recorded_at DESC)",
			);
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_llm_task_outcomes_unit ON llm_task_outcomes(unit_type, recorded_at DESC)",
			);
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_llm_task_outcomes_provider ON llm_task_outcomes(provider, recorded_at DESC)",
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 16,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 16;
	}
	if (appliedVersion < 17) {
		const ok = runMigrationStep("v17", () => {
			ensureColumn(
				db,
				"tasks",
				"verification_status",
				`ALTER TABLE tasks ADD COLUMN verification_status TEXT NOT NULL DEFAULT ''`,
			);
			// Backfill verification_status from existing verification_evidence rows so the
			// prior-task guard works on databases upgraded mid-project (not just new ones).
			db.exec(`
				        UPDATE tasks
				        SET verification_status = CASE
				          WHEN (SELECT COUNT(*) FROM verification_evidence ve
				                WHERE ve.milestone_id = tasks.milestone_id
				                  AND ve.slice_id    = tasks.slice_id
				                  AND ve.task_id     = tasks.id) = 0
				            THEN ''
				          WHEN (SELECT COUNT(*) FROM verification_evidence ve
				                WHERE ve.milestone_id = tasks.milestone_id
				                  AND ve.slice_id    = tasks.slice_id
				                  AND ve.task_id     = tasks.id
				                  AND ve.exit_code  != 0) = 0
				            THEN 'all_pass'
				          WHEN (SELECT COUNT(*) FROM verification_evidence ve
				                WHERE ve.milestone_id = tasks.milestone_id
				                  AND ve.slice_id    = tasks.slice_id
				                  AND ve.task_id     = tasks.id
				                  AND ve.exit_code   = 0) > 0
				            THEN 'partial'
				          ELSE 'all_fail'
				        END
				        WHERE tasks.status IN ('complete', 'done')
				      `);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 17,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 17;
	}
	if (appliedVersion < 18) {
		const ok = runMigrationStep("v18", () => {
			ensureColumn(
				db,
				"slices",
				"adversarial_partner",
				`ALTER TABLE slices ADD COLUMN adversarial_partner TEXT NOT NULL DEFAULT ''`,
			);
			ensureColumn(
				db,
				"slices",
				"adversarial_combatant",
				`ALTER TABLE slices ADD COLUMN adversarial_combatant TEXT NOT NULL DEFAULT ''`,
			);
			ensureColumn(
				db,
				"slices",
				"adversarial_architect",
				`ALTER TABLE slices ADD COLUMN adversarial_architect TEXT NOT NULL DEFAULT ''`,
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 18,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 18;
	}
	if (appliedVersion < 19) {
		const ok = runMigrationStep("v19", () => {
			ensureColumn(
				db,
				"slices",
				"planning_meeting_json",
				`ALTER TABLE slices ADD COLUMN planning_meeting_json TEXT NOT NULL DEFAULT ''`,
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 19,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 19;
	}
	if (appliedVersion < 20) {
		const ok = runMigrationStep("v20", () => {
			ensureColumn(
				db,
				"milestones",
				"vision_meeting_json",
				`ALTER TABLE milestones ADD COLUMN vision_meeting_json TEXT NOT NULL DEFAULT ''`,
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 20,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 20;
	}
	if (appliedVersion < 21) {
		const ok = runMigrationStep("v21", () => {
			ensureRepoProfileTables(db);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 21,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 21;
	}
	if (appliedVersion < 22) {
		const ok = runMigrationStep("v22", () => {
			// SF ADR-011: progressive planning. is_sketch=1 means the slice is a 2-3
			// sentence sketch awaiting refine-slice expansion; refine fills in the
			// real plan and clears the flag. sketch_scope holds the milestone
			// planner's stored scope hint that refine treats as a hard boundary.
			ensureColumn(
				db,
				"slices",
				"is_sketch",
				`ALTER TABLE slices ADD COLUMN is_sketch INTEGER NOT NULL DEFAULT 0`,
			);
			ensureColumn(
				db,
				"slices",
				"sketch_scope",
				`ALTER TABLE slices ADD COLUMN sketch_scope TEXT NOT NULL DEFAULT ''`,
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 22,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 22;
	}
	if (appliedVersion < 23) {
		const ok = runMigrationStep("v23", () => {
			// ADR-011 Phase 2 (SF ADR): mid-execution escalation. escalation_pending=1
			// marks a task that paused for a user decision; escalation_artifact_path
			// points to the T##-ESCALATION.json file containing options + recommendation.
			// State derivation will emit phase='escalating-task' when any task in the
			// active slice has escalation_pending=1; dispatch returns 'stop' so the
			// loop never bypasses a pending decision.
			ensureColumn(
				db,
				"tasks",
				"escalation_pending",
				`ALTER TABLE tasks ADD COLUMN escalation_pending INTEGER NOT NULL DEFAULT 0`,
			);
			ensureColumn(
				db,
				"tasks",
				"escalation_artifact_path",
				`ALTER TABLE tasks ADD COLUMN escalation_artifact_path TEXT DEFAULT NULL`,
			);
			try {
				db.exec(
					"CREATE INDEX IF NOT EXISTS idx_tasks_escalation_pending ON tasks(milestone_id, slice_id, escalation_pending)",
				);
			} catch {
				/* index creation is opportunistic — fall through if backend lacks it */
			}
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 23,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 23;
	}
	if (appliedVersion < 24) {
		const ok = runMigrationStep("v24", () => {
			// ADR-011 P2 (SF ADR): the third escalation flag for the
			// continueWithDefault=true case — an artifact is recorded for human
			// review later, but the loop is NOT paused. Mutually exclusive with
			// escalation_pending (the writer flips one or the other).
			ensureColumn(
				db,
				"tasks",
				"escalation_awaiting_review",
				`ALTER TABLE tasks ADD COLUMN escalation_awaiting_review INTEGER NOT NULL DEFAULT 0`,
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 24,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 24;
	}
	if (appliedVersion < 25) {
		const ok = runMigrationStep("v25", () => {
			// SF ADR-011 P2 carry-forward: when an escalation is resolved, the user's
			// choice should be visible to the next execute-task agent in the same
			// slice. escalation_override_applied=0 marks "resolved but not yet
			// injected into a downstream prompt"; the prompt builder calls
			// claimEscalationOverride which atomically flips it to 1 (idempotent
			// race-safe claim). Per-task granularity so multi-task slices can
			// carry multiple resolved escalations forward independently.
			ensureColumn(
				db,
				"tasks",
				"escalation_override_applied",
				`ALTER TABLE tasks ADD COLUMN escalation_override_applied INTEGER NOT NULL DEFAULT 0`,
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 25,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 25;
	}
	if (appliedVersion < 26) {
		const ok = runMigrationStep("v26", () => {
			db.exec(`
				        CREATE TABLE IF NOT EXISTS uok_runs (
				          run_id TEXT PRIMARY KEY,
				          session_id TEXT DEFAULT NULL,
				          path TEXT NOT NULL DEFAULT '',
				          status TEXT NOT NULL DEFAULT 'started',
				          started_at TEXT NOT NULL,
				          ended_at TEXT DEFAULT NULL,
				          error TEXT DEFAULT NULL,
				          flags_json TEXT NOT NULL DEFAULT '{}',
				          updated_at TEXT NOT NULL
				        )
				      `);
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_uok_runs_status_started ON uok_runs(status, started_at DESC)",
			);
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_uok_runs_session ON uok_runs(session_id, started_at DESC)",
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 26,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 26;
	}
	if (appliedVersion < 27) {
		const ok = runMigrationStep("v27", () => {
			ensureSolverEvalTables(db);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 27,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 27;
	}
	if (appliedVersion < 28) {
		const ok = runMigrationStep("v28", () => {
			// UOK observability: gate execution latency
			// Guard: gate_runs table may not exist in minimal legacy DBs (it will be dropped in v58)
			if (tableExists(db, "gate_runs")) {
				ensureColumn(
					db,
					"gate_runs",
					"duration_ms",
					"ALTER TABLE gate_runs ADD COLUMN duration_ms INTEGER DEFAULT NULL",
				);
			}
			// UOK circuit breaker state
			db.exec(`
								CREATE TABLE IF NOT EXISTS gate_circuit_breakers (
									gate_id TEXT PRIMARY KEY,
									state TEXT NOT NULL DEFAULT 'closed',
									failure_streak INTEGER NOT NULL DEFAULT 0,
									last_failure_at TEXT DEFAULT NULL,
									opened_at TEXT DEFAULT NULL,
									half_open_attempts INTEGER NOT NULL DEFAULT 0,
									updated_at TEXT NOT NULL DEFAULT ''
								)
							`);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 28,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 28;
	}
	if (appliedVersion < 29) {
		const ok = runMigrationStep("v29", () => {
			ensureHeadlessRunTables(db);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 29,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 29;
	}
	if (appliedVersion < 30) {
		const ok = runMigrationStep("v30", () => {
			ensureSelfFeedbackTables(db);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 30,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 30;
	}
	if (appliedVersion < 31) {
		const ok = runMigrationStep("v31", () => {
			ensureUokMessageTables(db);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 31,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 31;
	}
	if (appliedVersion < 32) {
		const ok = runMigrationStep("v32", () => {
			ensureTaskCreatedAtColumn(db);
			ensureSpecSchemaTables(db);
			// Populate spec tables from existing spec columns in runtime tables
			populateSpecTablesFromExisting(db);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 32,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 32;
	}
	if (appliedVersion < 33) {
		const ok = runMigrationStep("v33", () => {
			ensureColumn(
				db,
				"milestones",
				"sequence",
				`ALTER TABLE milestones ADD COLUMN sequence INTEGER DEFAULT 0`,
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 33,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 33;
	}
	if (appliedVersion < 34) {
		const ok = runMigrationStep("v34", () => {
			ensureTaskCreatedAtColumn(db);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 34,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 34;
	}
	if (appliedVersion < 35) {
		const ok = runMigrationStep("v35", () => {
			ensureBacklogTables(db);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 35,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 35;
	}
	if (appliedVersion < 36) {
		const ok = runMigrationStep("v36", () => {
			migrateCostUsdToMicroUsd(db);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 36,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 36;
	}
	if (appliedVersion < 37) {
		const ok = runMigrationStep("v37", () => {
			ensureScheduleTables(db);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 37,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 37;
	}
	if (appliedVersion < 38) {
		const ok = runMigrationStep("v38", () => {
			try {
				db.exec(
					"ALTER TABLE memories ADD COLUMN tags TEXT NOT NULL DEFAULT '[]'",
				);
			} catch {
				// Column may already exist on fresh DBs
			}
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 38,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 38;
	}
	if (appliedVersion < 39) {
		const ok = runMigrationStep("v39", () => {
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_memory_sources_content_hash ON memory_sources(content_hash)",
			);
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_memories_category ON memories(superseded_by, category)",
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 39,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 39;
	}
	if (appliedVersion < 40) {
		const ok = runMigrationStep("v40", () => {
			db.exec(`
				        CREATE TABLE IF NOT EXISTS judgments (
				          id INTEGER PRIMARY KEY AUTOINCREMENT,
				          unit_id TEXT NOT NULL,
				          decision TEXT NOT NULL DEFAULT '',
				          alternatives_json TEXT NOT NULL DEFAULT '[]',
				          reasoning TEXT NOT NULL DEFAULT '',
				          confidence TEXT NOT NULL DEFAULT 'medium',
				          ts TEXT NOT NULL
				        )
				      `);
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_judgments_unit_id ON judgments(unit_id, ts DESC)",
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 40,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 40;
	}
	if (appliedVersion < 41) {
		const ok = runMigrationStep("v41", () => {
			ensureRetrievalEvidenceTables(db);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 41,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 41;
	}
	if (appliedVersion < 42) {
		const ok = runMigrationStep("v42", () => {
			ensureColumn(
				db,
				"milestones",
				"product_research_json",
				`ALTER TABLE milestones ADD COLUMN product_research_json TEXT NOT NULL DEFAULT ''`,
			);
			ensureColumn(
				db,
				"milestone_specs",
				"product_research_json",
				`ALTER TABLE milestone_specs ADD COLUMN product_research_json TEXT DEFAULT ''`,
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 42,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 42;
	}
	if (appliedVersion < 43) {
		const ok = runMigrationStep("v43", () => {
			db.exec(`
								CREATE TABLE IF NOT EXISTS session_mode_state (
									id INTEGER PRIMARY KEY CHECK (id = 1),
									work_mode TEXT NOT NULL DEFAULT 'chat',
									run_control TEXT NOT NULL DEFAULT 'manual',
									permission_profile TEXT NOT NULL DEFAULT 'restricted',
									model_mode TEXT NOT NULL DEFAULT 'smart',
									surface TEXT NOT NULL DEFAULT 'tui',
									updated_at TEXT NOT NULL DEFAULT ''
								)
							`);
			db.exec(`
								INSERT OR IGNORE INTO session_mode_state (id, work_mode, run_control, permission_profile, model_mode, surface, updated_at)
								VALUES (1, 'chat', 'manual', 'restricted', 'smart', 'tui', datetime('now'))
							`);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 43,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 43;
	}
	if (appliedVersion < 44) {
		const ok = runMigrationStep("v44", () => {
			ensureSpecSchemaTables(db);
			ensureTaskFrontmatterColumns(db);
			db.exec(`
								UPDATE tasks
								SET task_status = CASE status
									WHEN 'complete' THEN 'done'
									WHEN 'completed' THEN 'done'
									WHEN 'done' THEN 'done'
									WHEN 'running' THEN 'running'
									WHEN 'in_progress' THEN 'running'
									WHEN 'blocked' THEN 'blocked'
									WHEN 'failed' THEN 'failed'
									WHEN 'cancelled' THEN 'cancelled'
									ELSE COALESCE(NULLIF(task_status, ''), 'todo')
								END
							`);
			db.exec(`
								UPDATE task_specs
								SET risk = COALESCE((SELECT tasks.risk FROM tasks
									WHERE tasks.milestone_id = task_specs.milestone_id
									  AND tasks.slice_id = task_specs.slice_id
									  AND tasks.id = task_specs.task_id), risk),
								    mutation_scope = COALESCE((SELECT tasks.mutation_scope FROM tasks
									WHERE tasks.milestone_id = task_specs.milestone_id
									  AND tasks.slice_id = task_specs.slice_id
									  AND tasks.id = task_specs.task_id), mutation_scope),
								    verification_type = COALESCE((SELECT tasks.verification_type FROM tasks
									WHERE tasks.milestone_id = task_specs.milestone_id
									  AND tasks.slice_id = task_specs.slice_id
									  AND tasks.id = task_specs.task_id), verification_type),
								    plan_approval = COALESCE((SELECT tasks.plan_approval FROM tasks
									WHERE tasks.milestone_id = task_specs.milestone_id
									  AND tasks.slice_id = task_specs.slice_id
									  AND tasks.id = task_specs.task_id), plan_approval),
								    estimated_effort = COALESCE((SELECT tasks.estimated_effort FROM tasks
									WHERE tasks.milestone_id = task_specs.milestone_id
									  AND tasks.slice_id = task_specs.slice_id
									  AND tasks.id = task_specs.task_id), estimated_effort),
								    dependencies = COALESCE((SELECT tasks.dependencies FROM tasks
									WHERE tasks.milestone_id = task_specs.milestone_id
									  AND tasks.slice_id = task_specs.slice_id
									  AND tasks.id = task_specs.task_id), dependencies),
								    blocks_parallel = COALESCE((SELECT tasks.blocks_parallel FROM tasks
									WHERE tasks.milestone_id = task_specs.milestone_id
									  AND tasks.slice_id = task_specs.slice_id
									  AND tasks.id = task_specs.task_id), blocks_parallel),
								    requires_user_input = COALESCE((SELECT tasks.requires_user_input FROM tasks
									WHERE tasks.milestone_id = task_specs.milestone_id
									  AND tasks.slice_id = task_specs.slice_id
									  AND tasks.id = task_specs.task_id), requires_user_input),
								    auto_retry = COALESCE((SELECT tasks.auto_retry FROM tasks
									WHERE tasks.milestone_id = task_specs.milestone_id
									  AND tasks.slice_id = task_specs.slice_id
									  AND tasks.id = task_specs.task_id), auto_retry),
								    max_retries = COALESCE((SELECT tasks.max_retries FROM tasks
									WHERE tasks.milestone_id = task_specs.milestone_id
									  AND tasks.slice_id = task_specs.slice_id
									  AND tasks.id = task_specs.task_id), max_retries)
							`);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 44,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 44;
	}
	if (appliedVersion < 45) {
		const ok = runMigrationStep("v45", () => {
			ensureTaskSchedulerTable(db);
			db.exec(`
								INSERT OR IGNORE INTO task_scheduler (
									milestone_id, slice_id, task_id, status, updated_at
								)
								SELECT milestone_id, slice_id, id, 'queued', datetime('now')
								FROM tasks
							`);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 45,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 45;
	}
	if (appliedVersion < 46) {
		const ok = runMigrationStep("v46", () => {
			// validation_runs: mirrors droid's validation-contract.md + validation-state.json
			// pattern. Each run stores the contract spec inline and its execution state.
			db.exec(`
								CREATE TABLE IF NOT EXISTS validation_runs (
									run_id TEXT PRIMARY KEY,
									milestone_id TEXT NOT NULL,
									slice_id TEXT,
									task_id TEXT,
									contract TEXT NOT NULL DEFAULT '',
									status TEXT NOT NULL DEFAULT 'pending',
									verdict TEXT NOT NULL DEFAULT '',
									rationale TEXT NOT NULL DEFAULT '',
									findings TEXT NOT NULL DEFAULT '',
									started_at TEXT,
									completed_at TEXT,
									created_at TEXT NOT NULL,
									FOREIGN KEY (milestone_id) REFERENCES milestones(id)
								)
							`);
			db.exec(`
								CREATE INDEX IF NOT EXISTS idx_validation_runs_scope
									ON validation_runs(milestone_id, slice_id, task_id)
							`);
			db.exec(`
								CREATE VIEW IF NOT EXISTS latest_validation_state AS
								SELECT vr.*
								FROM validation_runs vr
								WHERE vr.rowid = (
									SELECT MAX(v2.rowid)
									FROM validation_runs v2
									WHERE v2.milestone_id = vr.milestone_id
									  AND v2.slice_id IS vr.slice_id
									  AND v2.task_id IS vr.task_id
								)
							`);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 46,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 46;
	}
	if (appliedVersion < 47) {
		const ok = runMigrationStep("v47", () => {
			// Drop unused superseded_by column from validation_runs.
			// The column was never written or queried — dead schema from v46.
			const cols = db
				.prepare("PRAGMA table_info(validation_runs)")
				.all()
				.map((c) => c.name);
			if (cols.includes("superseded_by")) {
				db.exec("ALTER TABLE validation_runs DROP COLUMN superseded_by");
			}
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 47,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 47;
	}
	if (appliedVersion < 48) {
		const ok = runMigrationStep("v48", () => {
			// Session layer: create tables, backfill from existing headless_runs and
			// audit_turn_index so historical data is queryable from day one.
			// Message text will be NULL for backfilled turns — it was never stored.
			ensureSessionTables(db);
			// Backfill: one session per headless run.
			db.exec(`
				        INSERT OR IGNORE INTO sessions (session_id, trace_id, mode, cwd, created_at, updated_at)
				        SELECT run_id, NULL, 'headless', '', created_at, updated_at
				        FROM headless_runs
				      `);
			// Backfill: one session per distinct trace_id in audit_turn_index.
			// Reconstruct created_at/updated_at from the min/max timestamps.
			db.exec(`
				        INSERT OR IGNORE INTO sessions (session_id, trace_id, mode, cwd, created_at, updated_at)
				        SELECT trace_id, trace_id, 'interactive',
				               '', MIN(first_ts), MAX(last_ts)
				        FROM audit_turn_index
				        GROUP BY trace_id
				      `);
			// Backfill: one turn row per (trace_id, turn_id) in audit_turn_index.
			// turn_index derived from row order within trace; message text is NULL.
			db.exec(`
				        INSERT OR IGNORE INTO turns (session_id, turn_index, user_message, assistant_response, ts)
				        SELECT
				          trace_id,
				          ROW_NUMBER() OVER (PARTITION BY trace_id ORDER BY first_ts) - 1,
				          NULL, NULL,
				          first_ts
				        FROM audit_turn_index
				      `);
			// Rebuild FTS index from any turns that have text.
			// None from backfill yet, but required so the FTS table is consistent.
			db.exec(`INSERT INTO turns_fts(turns_fts) VALUES ('rebuild')`);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 48,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 48;
	}
	if (appliedVersion < 49) {
		const ok = runMigrationStep("v49", () => {
			// Add session_snapshots table — checkpoints before irreversible ops.
			// Safe to call on fresh DBs too (CREATE TABLE IF NOT EXISTS).
			ensureSessionSnapshotTable(db);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 49,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 49;
	}
	if (appliedVersion < 50) {
		const ok = runMigrationStep("v50", () => {
			// Add sleeptime_consolidation_queue — decouples memory consolidation
			// from the conversation turn so the daemon can drain it asynchronously.
			ensureSleeptimeQueueTable(db);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 50,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 50;
	}
	if (appliedVersion < 51) {
		const ok = runMigrationStep("v51", () => {
			// Add deploy/smoke/release/rollback tables — closes the vision→production loop.
			// deploy_runs tracks each deployment attempt; smoke_results tracks live verification;
			// release_records tracks version bumps and publishes; rollback_runs tracks reversions.
			ensureDeployTables(db);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 51,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 51;
	}
	if (appliedVersion < 52) {
		const ok = runMigrationStep("v52", () => {
			// Add triage_runs/evals/items/skills, runtime_counters, and
			// validation_attention_markers tables — migrate JSONL structured state to DB.
			ensureTriageTables(db);
			ensureRuntimeCounterTable(db);
			ensureValidationAttentionMarkersTable(db);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 52,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 52;
	}
	if (appliedVersion < 53) {
		const ok = runMigrationStep("v53", () => {
			// Add routing_history and routing_feedback tables — migrate file-based
			// routing history to DB-first storage.
			db.exec(`
								CREATE TABLE IF NOT EXISTS routing_history (
									pattern TEXT NOT NULL,
									tier TEXT NOT NULL,
									success_count INTEGER NOT NULL DEFAULT 0,
									fail_count INTEGER NOT NULL DEFAULT 0,
									updated_at TEXT NOT NULL,
									PRIMARY KEY (pattern, tier)
								);
								CREATE TABLE IF NOT EXISTS routing_feedback (
									id INTEGER PRIMARY KEY AUTOINCREMENT,
									pattern TEXT NOT NULL,
									tier TEXT NOT NULL,
									feedback TEXT NOT NULL,
									recorded_at TEXT NOT NULL
								);
							`);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 53,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 53;
	}
	if (appliedVersion < 54) {
		const ok = runMigrationStep("v54", () => {
			// Migrate metrics ledger from .sf/runtime/metrics.json to DB-first
			// unit_metrics and project_metrics_meta tables.
			db.exec(`
								CREATE TABLE IF NOT EXISTS unit_metrics (
									type TEXT NOT NULL,
									id TEXT NOT NULL,
									started_at INTEGER NOT NULL,
									finished_at INTEGER NOT NULL,
									model TEXT NOT NULL,
									auto_session_key TEXT,
									tokens_input INTEGER NOT NULL DEFAULT 0,
									tokens_output INTEGER NOT NULL DEFAULT 0,
									tokens_cache_read INTEGER NOT NULL DEFAULT 0,
									tokens_cache_write INTEGER NOT NULL DEFAULT 0,
									tokens_total INTEGER NOT NULL DEFAULT 0,
									cost REAL NOT NULL DEFAULT 0,
									tool_calls INTEGER NOT NULL DEFAULT 0,
									assistant_messages INTEGER NOT NULL DEFAULT 0,
									user_messages INTEGER NOT NULL DEFAULT 0,
									api_requests INTEGER NOT NULL DEFAULT 0,
									tier TEXT,
									model_downgraded INTEGER,
									context_window_tokens INTEGER,
									truncation_sections INTEGER,
									continue_here_fired INTEGER,
									prompt_char_count INTEGER,
									baseline_char_count INTEGER,
									cache_hit_rate INTEGER,
									skills TEXT,
									PRIMARY KEY (type, id, started_at)
								);
								CREATE TABLE IF NOT EXISTS project_metrics_meta (
									key TEXT PRIMARY KEY,
									value TEXT NOT NULL
								);
							`);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 54,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 54;
	}
	if (appliedVersion < 55) {
		const ok = runMigrationStep("v55", () => {
			// Schema v55: composite index for audit_events + task access-pattern views
			// Guard: audit_events may not exist in minimal legacy DBs (it will be dropped in v58)
			if (tableExists(db, "audit_events")) {
				db.exec(
					`CREATE INDEX IF NOT EXISTS idx_audit_events_category ON audit_events(category, type, ts DESC)`,
				);
			}
			db.exec(
				`CREATE VIEW IF NOT EXISTS active_tasks AS SELECT * FROM tasks WHERE status NOT IN ('done','complete','completed','cancelled')`,
			);
			db.exec(`
								CREATE VIEW IF NOT EXISTS v_task_full AS
								SELECT t.*, ts.spec_version, ts.verify AS spec_verify,
								       ts.inputs AS spec_inputs, ts.expected_output AS spec_expected_output
								FROM tasks t
								LEFT JOIN task_specs ts
								  ON t.milestone_id = ts.milestone_id
								 AND t.slice_id    = ts.slice_id
								 AND t.id          = ts.task_id
							`);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 55,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 55;
	}
	if (appliedVersion < 56) {
		const ok = runMigrationStep("v56", () => {
			// Schema v56: move metrics table to dedicated metrics.db — drop from main DB
			// to eliminate WAL pressure from high-frequency telemetry writes.
			db.exec(`DROP TABLE IF EXISTS metrics`);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 56,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 56;
	}
	if (appliedVersion < 57) {
		const ok = runMigrationStep("v57", () => {
			// Schema v57: add archived_at to sessions for soft-delete / archive support.
			db.exec(`ALTER TABLE sessions ADD COLUMN archived_at TEXT DEFAULT NULL`);
			db.exec(
				`CREATE INDEX IF NOT EXISTS idx_sessions_archived ON sessions(archived_at) WHERE archived_at IS NOT NULL`,
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 57,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 57;
	}
	if (appliedVersion < 58) {
		const ok = runMigrationStep("v58", () => {
			// Schema v58: move trace data to JSONL files — drop gate_runs, turn_git_transactions, audit_events
			db.exec("DROP TABLE IF EXISTS gate_runs");
			db.exec("DROP TABLE IF EXISTS turn_git_transactions");
			db.exec("DROP TABLE IF EXISTS audit_events");
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 58,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 58;
	}
	if (appliedVersion < 59) {
		const ok = runMigrationStep("v59", () => {
			// Schema v59: add failure_mode to llm_task_outcomes so the learning system
			// can differentiate transient failures (rate_limit) from hard failures
			// (quota_exhausted, auth_error) when weighting model demotions.
			ensureColumn(
				db,
				"llm_task_outcomes",
				"failure_mode",
				"ALTER TABLE llm_task_outcomes ADD COLUMN failure_mode TEXT DEFAULT NULL",
			);
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_llm_task_outcomes_failure_mode ON llm_task_outcomes(model_id, failure_mode, recorded_at DESC)",
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 59,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 59;
	}
	if (appliedVersion < 60) {
		const ok = runMigrationStep("v60", () => {
			// Schema v60: add frontmatter_version to tasks table for future frontmatter
			// schema migrations. Defaults to 1 for all existing rows.
			ensureColumn(
				db,
				"tasks",
				"frontmatter_version",
				"ALTER TABLE tasks ADD COLUMN frontmatter_version INTEGER NOT NULL DEFAULT 1",
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 60,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 60;
	}
	if (appliedVersion < 61) {
		const ok = runMigrationStep("v61", () => {
			// Schema v61: intent_chapters — crash-resume context for autonomous units.
			// Each chapter records the agent's declared intent when a unit begins
			// (chapter_open) and clears it on normal close (chapter_close). On
			// crash-resume, the open chapter is surfaced to the prompt so the agent
			// knows where it left off without replaying the full transcript.
			db.exec(`
								CREATE TABLE IF NOT EXISTS intent_chapters (
									id TEXT PRIMARY KEY,
									unit_type TEXT NOT NULL,
									unit_id TEXT NOT NULL,
									milestone_id TEXT,
									slice_id TEXT,
									task_id TEXT,
									intent TEXT NOT NULL,
									opened_at TEXT NOT NULL,
									closed_at TEXT,
									outcome TEXT,
									metadata_json TEXT
								);
								CREATE INDEX IF NOT EXISTS idx_intent_chapters_unit
									ON intent_chapters(unit_type, unit_id);
								CREATE INDEX IF NOT EXISTS idx_intent_chapters_open
									ON intent_chapters(closed_at, opened_at)
									WHERE closed_at IS NULL;
							`);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 61,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 61;
	}
	if (appliedVersion < 62) {
		const ok = runMigrationStep("v62", () => {
			// Schema v62: tracked_md_files — sha-track source-of-truth markdown files
			// so SF can detect external edits (hand-edits, git pulls, cross-agent edits)
			// and surface diffs at session start.
			db.exec(`
								CREATE TABLE IF NOT EXISTS tracked_md_files (
									relpath          TEXT PRIMARY KEY,
									sha256           TEXT NOT NULL,
									size_bytes       INTEGER NOT NULL DEFAULT 0,
									last_seen_at     TEXT NOT NULL,
									last_seen_commit TEXT DEFAULT NULL,
									category         TEXT NOT NULL DEFAULT 'meta',
									active           INTEGER NOT NULL DEFAULT 1
								);
							`);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 62,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 62;
	}
	if (appliedVersion < 63) {
		const ok = runMigrationStep("v63", () => {
			// Schema v63: context_board — always-in-context invariants board.
			// Per-repo/per-branch entries rendered into every dispatch system
			// prompt. Add/prune-only discipline enforced at the tool layer.
			db.exec(`
				CREATE TABLE IF NOT EXISTS context_board (
					id TEXT PRIMARY KEY,
					content TEXT NOT NULL,
					category TEXT,
					added_at TEXT NOT NULL,
					repository TEXT NOT NULL,
					branch TEXT NOT NULL
				)
			`);
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_context_board_repo_branch ON context_board(repository, branch, added_at ASC)",
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 63,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 63;
	}
	if (appliedVersion < 64) {
		const ok = runMigrationStep("v64", () => {
			// Schema v64: self_feedback_relations — explicit causal links
			// between self-feedback entries (sf-mp4rxkwx-jz0soh). Without
			// this table, the ledger is a flat list and patterns like
			// "X supersedes Y", "A is the root cause of B, C", or
			// "this entry is a duplicate of that one" can only be
			// expressed in prose, where reflection passes and detectors
			// can't act on them. Adding relational semantics lets the
			// requirement-promoter, reflection layer, and operator
			// tooling reason about graphs of related findings.
			//
			// CHECK constraints enforce the closed enum of relation kinds
			// AND prevent self-loops. The composite primary key allows
			// multiple kinds between the same pair (e.g. supersedes AND
			// blocks) but prevents exact duplicates.
			db.exec(`
				CREATE TABLE IF NOT EXISTS self_feedback_relations (
					from_id TEXT NOT NULL,
					to_id TEXT NOT NULL,
					relation_kind TEXT NOT NULL CHECK (relation_kind IN (
						'supersedes',
						'duplicate_of',
						'blocks',
						'root_cause_of',
						'partial_fix_of'
					)),
					created_at TEXT NOT NULL,
					PRIMARY KEY (from_id, to_id, relation_kind),
					FOREIGN KEY (from_id) REFERENCES self_feedback(id),
					FOREIGN KEY (to_id) REFERENCES self_feedback(id),
					CHECK (from_id != to_id)
				)
			`);
			db.exec(
				"CREATE INDEX IF NOT EXISTS idx_self_feedback_relations_to ON self_feedback_relations(to_id, relation_kind)",
			);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 64,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 64;
	}
	if (appliedVersion < 65) {
		const ok = runMigrationStep("v65", () => {
			// Schema v65: prioritization signals on self_feedback
			// (sf-mp4rxkx0-fkt3e2). Adds impact_score (0-100) and
			// effort_estimate (1-5) so selectInlineFixCandidates can sort
			// by impact desc, effort asc, ts asc instead of pure FIFO.
			//
			// Defaults are derived from severity at insert time
			// (low=20 / medium=50 / high=80 / critical=95) but operators
			// can override per-entry. NULL is allowed for backfill of
			// existing rows; the selection sort treats NULL as "use
			// severity-derived default" so legacy rows participate in
			// prioritization without a data migration.
			//
			// Idempotent ALTER: ensureSelfFeedbackTables (the fresh-DB CREATE
			// path) already includes both columns, so for fresh DBs the
			// migration step needs to run for schema_version-tracking but
			// skip the ALTER. Older fixtures that pre-date the fresh-DB
			// CREATE update do need the ALTER. Probe via PRAGMA table_info
			// before each ALTER.
			const cols = new Set(
				db
					.prepare("PRAGMA table_info(self_feedback)")
					.all()
					.map((r) => r.name),
			);
			if (!cols.has("impact_score")) {
				db.exec("ALTER TABLE self_feedback ADD COLUMN impact_score INTEGER");
			}
			if (!cols.has("effort_estimate")) {
				db.exec("ALTER TABLE self_feedback ADD COLUMN effort_estimate INTEGER");
			}
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 65,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 65;
	}

	if (appliedVersion < 66) {
		const ok = runMigrationStep("v66", () => {
			// Schema v66: UOK schema-v2 metadata on quality_gates.
			//
			// "Make UOK the SF Control Plane" plan, slice 2. Adds the columns
			// that classifyCoverage in headless-uok-status.ts checks for to
			// distinguish "ok"/"stale"/"incomplete" from "legacy":
			//
			//   - surface             headless | autonomous | interactive
			//   - run_control         bare | supervised | autonomous
			//   - permission_profile  high | medium | low
			//   - trace_id            flow id from the journal
			//   - parent_trace        parent flow id (nested traces)
			//
			// All columns are nullable so existing rows stay valid; the
			// classifier reads them as "legacy" when surface IS NULL.
			//
			// Idempotent ALTERs: probe via PRAGMA table_info because the
			// fresh-DB CREATE path may have already added them.
			const cols = new Set(
				db
					.prepare("PRAGMA table_info(quality_gates)")
					.all()
					.map((r) => r.name),
			);
			if (!cols.has("surface")) {
				db.exec("ALTER TABLE quality_gates ADD COLUMN surface TEXT");
			}
			if (!cols.has("run_control")) {
				db.exec("ALTER TABLE quality_gates ADD COLUMN run_control TEXT");
			}
			if (!cols.has("permission_profile")) {
				db.exec("ALTER TABLE quality_gates ADD COLUMN permission_profile TEXT");
			}
			if (!cols.has("trace_id")) {
				db.exec("ALTER TABLE quality_gates ADD COLUMN trace_id TEXT");
			}
			if (!cols.has("parent_trace")) {
				db.exec("ALTER TABLE quality_gates ADD COLUMN parent_trace TEXT");
			}
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 66,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 66;
	}

	if (appliedVersion < 67) {
		const ok = runMigrationStep("v67", () => {
			// Schema v67: explicit extraction attempt ledger for memory closeout.
			//
			// memory_processed_units is only the completed-inspection ledger.
			// Skips and errors need their own DB-backed status rows so operators can
			// tell whether memory is idle, blocked by provider config, rate-limited,
			// or failing at parse/apply time.
			db.exec(`
				CREATE TABLE IF NOT EXISTS memory_extraction_attempts (
					id INTEGER PRIMARY KEY AUTOINCREMENT,
					unit_key TEXT NOT NULL,
					unit_type TEXT,
					unit_id TEXT,
					activity_file TEXT,
					status TEXT NOT NULL,
					failure_class TEXT,
					reason TEXT,
					error TEXT,
					created_at TEXT NOT NULL
				)
			`);
			db.exec(`
				CREATE INDEX IF NOT EXISTS idx_memory_extraction_attempts_created
				ON memory_extraction_attempts(created_at)
			`);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 67,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 67;
	}
	if (appliedVersion < 68) {
		const ok = runMigrationStep("v68", () => {
			// Schema v68: tenant column on memories for cross-project isolation.
			//
			// When SM_ENABLED is on, memory records are federated across projects.
			// Without a tenant tag we cannot restrict retrieval to the current
			// project, leaking customer A's memories into customer B's context.
			// This column carries the repoIdentity hash (or package.json name on
			// forked projects) so the SM retrieval path can filter same-tenant only.
			//
			// NULL tenant = legacy record written before v68; the migration in
			// memory-store.js (backfillMemoryTenants) fills these on session start.
			try {
				db.exec("ALTER TABLE memories ADD COLUMN tenant TEXT");
			} catch {
				// Column may already exist on fresh DBs — idempotent.
			}
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 68,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 68;
	}
	if (appliedVersion < 69) {
		const ok = runMigrationStep("v69", () => {
			// Schema v69: classify memory extraction failures.
			//
			// The attempt ledger already showed that extraction failed, but parse,
			// transient provider, filesystem, and apply errors were indistinguishable.
			// Autonomous retry policy depends on that distinction.
			try {
				db.exec(
					"ALTER TABLE memory_extraction_attempts ADD COLUMN failure_class TEXT",
				);
			} catch {
				// Column may already exist on fresh DBs - idempotent.
			}
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 69,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 69;
	}

	if (appliedVersion < 70) {
		const ok = runMigrationStep("v70", () => {
			// Schema v70: per-task purpose trace (ADR-0000 restoration).
			//
			// complete-slice must mechanically verify every task served the
			// slice goal before flipping status. To do that we need the
			// goal-anchored sentence the task agent supplied when it called
			// complete_task — stored verbatim alongside the row, NULL on
			// legacy rows so older slices keep loading. complete_task refuses
			// to record a completion without this field; complete_slice
			// refuses to close while any task in the slice has NULL.
			if (!columnExists(db, "tasks", "purpose_trace")) {
				db.exec(`ALTER TABLE tasks ADD COLUMN purpose_trace TEXT`);
			}
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 70,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 70;
	}

	if (appliedVersion < 71) {
		const ok = runMigrationStep("v71", () => {
			// Schema v71: purpose_anchor on self_feedback (ADR-0000 restoration).
			//
			// SF is a purpose-to-software compiler — every artifact must name the
			// purpose it serves. self_feedback rows historically lacked any link
			// to the milestone vision or slice goal they're filed against, which
			// left triage unable to prioritize against purpose. Adding a free-text
			// `purpose_anchor` (typically the vision/goal sentence fragment) gives
			// operators and triage a structured "what purpose is at risk?" signal.
			//
			// NULL is allowed: legacy rows keep working, and the CLI accepts
			// omission. Future doctor checks can warn on missing anchors without
			// rejecting historical entries.
			//
			// Idempotent ALTER: ensureSelfFeedbackTables (the fresh-DB CREATE
			// path) already includes the column on a clean install. Older fixtures
			// need the ALTER. Probe via PRAGMA table_info first.
			const cols = new Set(
				db
					.prepare("PRAGMA table_info(self_feedback)")
					.all()
					.map((r) => r.name),
			);
			if (!cols.has("purpose_anchor")) {
				db.exec("ALTER TABLE self_feedback ADD COLUMN purpose_anchor TEXT");
			}
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 71,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 71;
	}

	if (appliedVersion < 72) {
		const ok = runMigrationStep("v72", () => {
			// Schema v72: per-slice vision trace (ADR-0000 P2, restoration of doctrine).
			// Renumbered from the originally-planned v69 because parallel work on
			// main used v69 for memory_extraction_attempts.failure_class.
			//
			// plan-milestone must emit a literal fragment of milestone.vision that each
			// slice serves so validate-milestone has structured grounds for assessment
			// instead of re-reading the entire vision via the LLM every time. The
			// fragment is the canonical "this slice traces back to that purpose" link.
			//
			// NULL is allowed for legacy rows (slices planned before v72); the
			// planning prompt and validation begin requiring it for new slices.
			//
			// Idempotent ALTER: probe via columnExists because the fresh-DB CREATE
			// path already adds the column.
			if (!columnExists(db, "slices", "traces_vision_fragment")) {
				db.exec("ALTER TABLE slices ADD COLUMN traces_vision_fragment TEXT");
			}
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 72,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 72;
	}

	if (appliedVersion < 73) {
		const ok = runMigrationStep("v73", () => {
			// Schema v73: tier column on milestones for tier-aware dispatch (R073).
			//
			// PROJECT.md's Priority Tier Order table was the operator-facing
			// authority on milestone priority, but the dispatcher read only
			// `milestones.sequence ASC`. That created drift: Tier 1 milestones
			// (M038 Wiggums, M048 Regression Firewall) sat at seq=38 / seq=48
			// and were unreachable for months, despite being declared Tier 1.
			//
			// v73 makes tier canonical: dispatcher queries `ORDER BY tier ASC,
			// sequence ASC`. tier defaults to 5 for legacy rows so older fixtures
			// keep loading without surprise.
			//
			// Seed values reflect the 2026-05-17 PROJECT.md priority decision:
			//   Tier 1 (self-heal + regression firewall): M048, M038, M011
			//   Tier 2 (compiler front-end + evidence gate): M030, M034, M019, M025
			//   Tier 3 (dispatch foundation): M012, M040, M010, M041
			//   All others default to Tier 5.
			//
			// Codex adversarial review 2026-05-17 [high] argued M048 must
			// precede M038 because source-level crashes happen before Wiggums
			// can observe anything. Hence M048 = tier 1 / seq 1, M038 = tier 1
			// / seq 2.
			if (!columnExists(db, "milestones", "tier")) {
				db.exec(
					"ALTER TABLE milestones ADD COLUMN tier INTEGER NOT NULL DEFAULT 5",
				);
			}
			// Seed BOTH tier and intra-tier sequence — codex 2026-05-17 [high] found
			// that updating only tier preserved the starvation class because legacy
			// rows kept their old sequences (M011=11, M038=38, M048=48), so within
			// tier 1 the dispatcher would still pick M011 first.
			const tierSeqMap = [
				["M048", 1, 1],
				["M038", 1, 2],
				["M011", 1, 3],
				["M030", 2, 4],
				["M034", 2, 5],
				["M019", 2, 6],
				["M025", 2, 7],
				["M012", 3, 8],
				["M040", 3, 9],
				["M010", 3, 10],
				["M041", 3, 11],
			];
			const upd = db.prepare(
				"UPDATE milestones SET tier = ?, sequence = ? WHERE id = ?",
			);
			for (const [id, tier, seq] of tierSeqMap) upd.run(tier, seq, id);
			db.prepare(
				"INSERT INTO schema_version (version, applied_at) VALUES (:version, :applied_at)",
			).run({
				":version": 73,
				":applied_at": new Date().toISOString(),
			});
		});
		if (ok) appliedVersion = 73;
	}

	// Post-migration assertion: ensure critical tables created by historical
	// migrations are actually present. If a prior migration claimed success but
	// the table is missing (e.g., due to a rolled-back transaction that failed
	// silently), recreate it now and log a warning so silent drift is visible.
	// (See self-feedback sf-mp36kfqm-rjrzju.)
	if (!tableExists(db, "routing_history")) {
		db.exec(`
			CREATE TABLE IF NOT EXISTS routing_history (
				pattern TEXT NOT NULL,
				tier TEXT NOT NULL,
				success_count INTEGER NOT NULL DEFAULT 0,
				fail_count INTEGER NOT NULL DEFAULT 0,
				updated_at TEXT NOT NULL,
				PRIMARY KEY (pattern, tier)
			)
		`);
		db.exec(`
			CREATE TABLE IF NOT EXISTS routing_feedback (
				id INTEGER PRIMARY KEY AUTOINCREMENT,
				pattern TEXT NOT NULL,
				tier TEXT NOT NULL,
				feedback TEXT NOT NULL,
				recorded_at TEXT NOT NULL
			)
		`);
		logWarning(
			"db",
			"Post-migration assertion: routing_history was missing despite schema_version >= 53. Recreated tables. This indicates a prior migration rollback that left schema_version out of sync with actual DDL state.",
		);
	}
}
