index.ts

import { Database } from 'bun:sqlite'
import { readFileSync } from 'fs'
import { join } from 'path'
import { resolveShippedRoot } from '../../utils/shipped-paths'

const migrationsDir = join(resolveShippedRoot(import.meta.url), 'storage', 'migrations')

interface Migration {
  id: string
  description: string
  apply: (db: Database) => void
}

function loadSql(filename: string): string {
  return readFileSync(join(migrationsDir, filename), 'utf-8')
}

function createLoopAttemptsTable(db: Database): void {
  const tables = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='loop_attempts'").all()
  if (tables.length > 0) return
  db.run(loadSql('145_create_loop_attempts.sql'))
}

export const migrations: Migration[] = [
  {
    id: '100',
    description: 'Create loops table for typed loop state storage',
    apply: (db: Database) => {
      db.run(loadSql('100_create_loops.sql'))
    },
  },
  {
    id: '101',
    description: 'Create loop_large_fields table for prompt and audit result',
    apply: (db: Database) => {
      db.run(loadSql('101_create_loop_large_fields.sql'))
    },
  },
  {
    id: '102',
    description: 'Create plans table for session-staged and loop-bound plans',
    apply: (db: Database) => {
      db.run(loadSql('102_create_plans.sql'))
    },
  },
  {
    id: '103',
    description: 'Create review_findings table for write-once review findings',
    apply: (db: Database) => {
      db.run(loadSql('103_create_review_findings.sql'))
    },
  },
  {
    id: '105',
    description: 'Create tui_preferences table for TUI recent models and execution preferences',
    apply: (db: Database) => {
      db.run(loadSql('105_create_tui_preferences.sql'))
    },
  },
  {
    id: '106',
    description: 'Drop project_kv table (replaced by typed repos + tui_preferences)',
    apply: (db: Database) => {
      db.run(loadSql('106_drop_project_kv.sql'))
    },
  },
  {
    id: '107',
    description: 'Add workspace_id column to loops table for workspace-backed worktree switching',
    apply: (db: Database) => {
      // Guard against test databases or legacy environments where the column
      // was added out-of-band. SQLite does not support `ADD COLUMN IF NOT EXISTS`.
      const cols = db.prepare('PRAGMA table_info(loops)').all() as Array<{ name: string }>
      if (cols.some((c) => c.name === 'workspace_id')) return
      db.run(loadSql('107_add_workspace_id_to_loops.sql'))
    },
  },
  {
    id: '108',
    description: 'Add host_session_id column to loops table for post-completion redirect',
    apply: (db: Database) => {
      const cols = db.prepare('PRAGMA table_info(loops)').all() as Array<{ name: string }>
      if (cols.some((c) => c.name === 'host_session_id')) return
      db.run(loadSql('108_add_host_session_id_to_loops.sql'))
    },
  },
  {
    id: '110',
    description: 'Drop completion_signal column from loops table (dead mechanism removal)',
    apply: (db: Database) => {
      const cols = db.prepare('PRAGMA table_info(loops)').all() as Array<{ name: string }>
      if (!cols.some((c) => c.name === 'completion_signal')) return
      db.run(loadSql('110_drop_completion_signal_from_loops.sql'))
    },
  },
  {
    id: '111',
    description: 'Make scenario column nullable in review_findings table',
    apply: (db: Database) => {
      db.run(loadSql('111_make_scenario_nullable.sql'))
    },
  },
  {
    id: '112',
    description: 'Drop audit column from loops table (dead flag removal)',
    apply: (db: Database) => {
      const cols = db.prepare('PRAGMA table_info(loops)').all() as Array<{ name: string }>
      if (!cols.some((c) => c.name === 'audit')) return
      db.run(loadSql('112_drop_audit_from_loops.sql'))
    },
  },
  {
    id: '113',
    description: 'Add audit_session_id column to loops table for audit session isolation',
    apply: (db: Database) => {
      const cols = db.prepare('PRAGMA table_info(loops)').all() as Array<{ name: string }>
      if (cols.some((c) => c.name === 'audit_session_id')) return
      db.run(loadSql('113_add_audit_session_id_to_loops.sql'))
    },
  },
  {
    id: '114',
    description: 'Ensure scenario column is nullable in review_findings table',
    apply: (db: Database) => {
      const cols = db.prepare('PRAGMA table_info(review_findings)').all() as Array<{ name: string; notnull: number }>
      const scenario = cols.find((c) => c.name === 'scenario')
      if (!scenario || scenario.notnull === 0) return
      db.run(`
        CREATE TABLE review_findings_new (
          project_id   TEXT NOT NULL,
          file         TEXT NOT NULL,
          line         INTEGER NOT NULL,
          severity     TEXT NOT NULL CHECK(severity IN ('bug','warning')),
          description  TEXT NOT NULL,
          scenario     TEXT,
          branch       TEXT,
          created_at   INTEGER NOT NULL,
          PRIMARY KEY (project_id, file, line)
        );
        INSERT INTO review_findings_new SELECT * FROM review_findings;
        DROP TABLE review_findings;
        ALTER TABLE review_findings_new RENAME TO review_findings;
        CREATE INDEX IF NOT EXISTS idx_review_findings_branch ON review_findings(project_id, branch);
      `)
    },
  },
  {
    id: '115',
    description: 'Create api_registry table for HTTP control plane (historical - dropped in 116)',
    apply: (db: Database) => {
      db.run(loadSql('115_create_api_registry.sql'))
    },
  },
  {
    id: '116',
    description: 'Drop api_registry table (bus-RPC migration - HTTP control plane removed)',
    apply: (db: Database) => {
      db.run(loadSql('116_drop_api_registry.sql'))
    },
  },
  {
    id: '117',
    description: 'Add branch to primary key for review_findings table (branch-scoped findings)',
    apply: (db: Database) => {
      // Guard: check if branch is already in primary key
      const pkInfo = db.prepare('PRAGMA table_info(review_findings)').all() as Array<{ name: string; pk: number }>
      const hasBranchInPk = pkInfo.some((c) => c.name === 'branch' && c.pk > 0)
      if (hasBranchInPk) return

      db.run(loadSql('117_branch_scope_review_findings.sql'))
    },
  },
  {
    id: '118',
    description: 'Drop audit_session_id column from loops table (single-session loop model)',
    apply: (db: Database) => {
      const cols = db.prepare('PRAGMA table_info(loops)').all() as Array<{ name: string }>
      if (!cols.some((c) => c.name === 'audit_session_id')) return
      db.run(loadSql('118_drop_audit_session_id_from_loops.sql'))
    },
  },
  {
    id: '119',
    description: 'Add loop_name scope to review_findings; drop legacy branch-only rows',
    apply: (db: Database) => {
      const cols = db.prepare('PRAGMA table_info(review_findings)').all() as Array<{ name: string }>
      if (cols.some((c) => c.name === 'loop_name') && !cols.some((c) => c.name === 'branch')) return
      db.run(loadSql('119_loop_scope_review_findings.sql'))
    },
  },
  {
    id: '120',
    description: 'Drop branch scope from review_findings table',
    apply: (db: Database) => {
      const cols = db.prepare('PRAGMA table_info(review_findings)').all() as Array<{ name: string }>
      const pkInfo = db.prepare('PRAGMA table_info(review_findings)').all() as Array<{ name: string; pk: number }>
      const hasBranch = cols.some((c) => c.name === 'branch')
      const hasLoopOnlyPk =
        pkInfo.find((c) => c.name === 'project_id')?.pk === 1 &&
        pkInfo.find((c) => c.name === 'loop_name')?.pk === 2 &&
        pkInfo.find((c) => c.name === 'file')?.pk === 3 &&
        pkInfo.find((c) => c.name === 'line')?.pk === 4
      if (!hasBranch && hasLoopOnlyPk) return
      db.run(loadSql('120_loop_only_review_findings.sql'))
    },
  },
  {
    id: '121',
    description: 'Create section_plans table for decomposed section plans',
    apply: (db: Database) => {
      const tables = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='section_plans'").all()
      if (tables.length > 0) return
      db.run(loadSql('121_create_section_plans.sql'))
    },
  },
  {
    id: '122',
    description: 'Add decomposition state columns to loops table',
    apply: (db: Database) => {
      const cols = db.prepare('PRAGMA table_info(loops)').all() as Array<{ name: string }>
      if (cols.some((c) => c.name === 'decomposition_status')) return
      db.run(loadSql('122_add_decomposition_state_to_loops.sql'))
    },
  },
  {
    id: '123',
    description: 'Add section_index column to review_findings table',
    apply: (db: Database) => {
      const cols = db.prepare('PRAGMA table_info(review_findings)').all() as Array<{ name: string }>
      if (cols.some((c) => c.name === 'section_index')) return
      db.run(loadSql('123_add_section_index_to_review_findings.sql'))
    },
  },
  {
    id: '124',
    description: 'Extend loops phase CHECK to include decomposing and final_auditing',
    apply: (db: Database) => {
      const phases = db.prepare("SELECT DISTINCT phase FROM loops").all() as Array<{ phase: string }>
      const hasNewPhases = phases.some(p => p.phase === 'decomposing' || p.phase === 'final_auditing')
      if (hasNewPhases) return
      db.run(loadSql('124_extend_loops_phase_check.sql'))
    },
  },
  {
    id: '125',
    description: 'Rebuild review_findings with section_index in primary key for section-scoped deduplication',
    apply: (db: Database) => {
      const pkInfo = db.prepare('PRAGMA table_info(review_findings)').all() as Array<{ name: string; pk: number }>
      if (pkInfo.some(c => c.name === 'section_index' && c.pk > 0)) return
      db.run(loadSql('125_add_section_index_to_review_findings_pk.sql'))
    },
  },
  {
    id: '126',
    description: 'Drop final_audit_attempts column from loops table',
    apply: (db: Database) => {
      const cols = db.prepare('PRAGMA table_info(loops)').all() as Array<{ name: string }>
      if (!cols.some((c) => c.name === 'final_audit_attempts')) return
      db.run(loadSql('126_drop_final_audit_attempts_from_loops.sql'))
    },
  },
  {
    id: '127',
    description: 'Consolidate loop plan storage: backfill prompt into plans, remove prompt column from loop_large_fields',
    apply: (db: Database) => {
      const cols = db.prepare('PRAGMA table_info(loop_large_fields)').all() as Array<{ name: string }>
      if (!cols.some((c) => c.name === 'prompt')) {
        // prompt already removed, just ensure index exists
        db.run("CREATE INDEX IF NOT EXISTS idx_plans_project_updated_at ON plans(project_id, updated_at DESC)")
        return
      }
      db.run(loadSql('127_consolidate_loop_plan_storage.sql'))
    },
  },
  {
    id: '128',
    description: 'Add unique index on loops(project_id, loop_name) for FK compliance with section_plans',
    apply: (db: Database) => {
      db.run(loadSql('128_add_loops_project_name_unique_index.sql'))
    },
  },
  {
    id: '129',
    description: 'Remove decomposer columns and decomposing phase',
    apply: (db: Database) => {
      db.run(loadSql('129_remove_decomposer.sql'))
    },
  },
  {
    id: '130',
    description: 'Create loop_session_usage table for persisting token usage snapshots',
    apply: (db: Database) => {
      db.run(loadSql('130_create_loop_session_usage.sql'))
    },
  },
  {
    id: '131',
    description: 'Add execution and auditor model variant columns to loops table',
    apply: (db: Database) => {
      const cols = db.prepare('PRAGMA table_info(loops)').all() as Array<{ name: string }>
      if (cols.some((c) => c.name === 'execution_variant')) return
      db.run(loadSql('131_add_loop_model_variants.sql'))
    },
  },
  {
    id: '132',
    description: 'Extend loops phase CHECK to include post_action',
    apply: (db: Database) => {
      const row = db.prepare("SELECT sql FROM sqlite_master WHERE type='table' AND name='loops'").get() as { sql: string } | null
      if (row?.sql?.includes("'post_action'")) return
      db.run(loadSql('132_extend_loops_phase_check_post_action.sql'))
    },
  },
  {
    id: '133',
    description: 'Create feature_groups and group_features tables for group-launch feature orchestration',
    apply: (db: Database) => {
      db.run(loadSql('133_create_feature_groups.sql'))
    },
  },
  {
    id: '134',
    description: 'Add post_action_report column to loop_large_fields',
    apply: (db: Database) => {
      const cols = db.prepare('PRAGMA table_info(loop_large_fields)').all() as Array<{ name: string }>
      if (cols.some((c) => c.name === 'post_action_report')) return
      db.run(loadSql('134_add_post_action_report.sql'))
    },
  },
  {
    id: '135',
    description: 'Add loop_kind discriminator to loops and goal text to loop_large_fields',
    // Guard each ALTER independently so partial legacy schemas (one column
    // present, the other absent) repair correctly. The migration runner wraps
    // apply() in a transaction, so both ALTERs that do run land atomically.
    apply: (db: Database) => {
      const loopsCols = db.prepare('PRAGMA table_info(loops)').all() as Array<{ name: string }>
      if (!loopsCols.some((c) => c.name === 'loop_kind')) {
        db.run('ALTER TABLE loops ADD COLUMN loop_kind TEXT NOT NULL DEFAULT \'plan\'')
      }
      const largeCols = db.prepare('PRAGMA table_info(loop_large_fields)').all() as Array<{ name: string }>
      if (!largeCols.some((c) => c.name === 'goal')) {
        db.run('ALTER TABLE loop_large_fields ADD COLUMN goal TEXT')
      }
    },
  },
  {
    id: '136',
    description: 'Add executor_session_id column to loops for goal-loop executor binding',
    apply: (db: Database) => {
      const cols = db.prepare('PRAGMA table_info(loops)').all() as Array<{ name: string }>
      if (cols.some((c) => c.name === 'executor_session_id')) return
      db.run('ALTER TABLE loops ADD COLUMN executor_session_id TEXT')
    },
  },
  {
    id: '137',
    description: 'Replace idx_loops_session with partial unique index on running rows; add executor participant lookup index',
    apply: (db: Database) => {
      const idxRow = db.prepare(
        "SELECT sql FROM sqlite_master WHERE type='index' AND name='idx_loops_session'"
      ).get() as { sql: string | null } | null

      if (!idxRow || !idxRow.sql || !idxRow.sql.includes("status = 'running'")) {
        db.run('DROP INDEX IF EXISTS idx_loops_session')
        db.run(
          "CREATE UNIQUE INDEX IF NOT EXISTS idx_loops_session ON loops(project_id, current_session_id) WHERE status = 'running'"
        )
      }

      db.run(
        "CREATE INDEX IF NOT EXISTS idx_loops_executor_session ON loops(project_id, executor_session_id) WHERE status = 'running' AND executor_session_id IS NOT NULL"
      )
    },
  },
  // 138-140 are reserved by the loop-metrics migrations (feat/loop-metrics);
  // this branch starts at 141 so the two ranges never collide regardless of
  // merge order (the runner skips any id already recorded).
  {
    id: '141',
    description: "Extend loops phase CHECK to include 'final_audit_fix'",
    apply: (db: Database) => {
      const row = db.prepare("SELECT sql FROM sqlite_master WHERE type='table' AND name='loops'").get() as { sql: string } | null
      if (row?.sql?.includes("'final_audit_fix'")) return
      db.run(loadSql('141_extend_loops_phase_check_final_audit_fix.sql'))
    },
  },
  {
    id: '142',
    description: 'Create loop_transitions table for persisted transition log',
    apply: (db: Database) => {
      const tables = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='loop_transitions'").all()
      if (tables.length > 0) return
      db.run(loadSql('142_create_loop_transitions.sql'))
    },
  },
  {
    id: '143',
    description: 'Create plan_amendments table for adaptive-plan adjustment audit trail',
    apply: (db: Database) => {
      const tables = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='plan_amendments'").all()
      if (tables.length > 0) return
      db.run(loadSql('143_create_plan_amendments.sql'))
    },
  },
  {
    id: '144',
    description: 'Add auditor_fallback_index to loops for the auditor model fallback chain',
    apply: (db: Database) => {
      const cols = db.prepare('PRAGMA table_info(loops)').all() as Array<{ name: string }>
      if (cols.some((c) => c.name === 'auditor_fallback_index')) return
      db.run('ALTER TABLE loops ADD COLUMN auditor_fallback_index INTEGER NOT NULL DEFAULT 0')
    },
  },
  {
    id: '145',
    description: 'Create loop_attempts table for durable loop attempt history storage',
    apply: createLoopAttemptsTable,
  },
  {
    id: '148',
    description: 'Ensure loop_attempts exists after historical migration ID collision',
    apply: createLoopAttemptsTable,
  },
  {
    id: '149',
    description: 'Add sandbox_settings column to loops for per-loop sandbox overrides',
    apply: (db: Database) => {
      const cols = db.prepare('PRAGMA table_info(loops)').all() as Array<{ name: string }>
      if (cols.some((c) => c.name === 'sandbox_settings')) return
      db.run('ALTER TABLE loops ADD COLUMN sandbox_settings TEXT')
    },
  },
  {
    id: '150',
    description: 'Remove goal loops: delete goal-kind rows and drop loop_kind, executor_session_id, and loop_large_fields.goal',
    apply: (db: Database) => {
      const tableExists = (name: string): boolean =>
        (db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name=?").all(name) as unknown[]).length > 0

      const loopsCols = db.prepare('PRAGMA table_info(loops)').all() as Array<{ name: string }>
      if (loopsCols.some((c) => c.name === 'loop_kind')) {
        const goalLoops = db.prepare("SELECT project_id, loop_name FROM loops WHERE loop_kind = 'goal'").all() as Array<{
          project_id: string
          loop_name: string
        }>
        const perLoopTables = [
          'loop_large_fields',
          'section_plans',
          'review_findings',
          'plans',
          'loop_transitions',
          'loop_attempts',
          'loop_session_usage',
          'plan_amendments',
        ]
        const deleteLoopRowStmt = db.prepare('DELETE FROM loops WHERE project_id = ? AND loop_name = ?')
        const deleteStmtsByTable = new Map<string, ReturnType<Database['prepare']>>()
        for (const table of perLoopTables) {
          if (!tableExists(table)) continue
          deleteStmtsByTable.set(table, db.prepare(`DELETE FROM ${table} WHERE project_id = ? AND loop_name = ?`))
        }
        for (const goalLoop of goalLoops) {
          for (const stmt of deleteStmtsByTable.values()) {
            stmt.run(goalLoop.project_id, goalLoop.loop_name)
          }
          deleteLoopRowStmt.run(goalLoop.project_id, goalLoop.loop_name)
        }
      }

      db.run('DROP INDEX IF EXISTS idx_loops_executor_session')

      if (loopsCols.some((c) => c.name === 'executor_session_id')) {
        db.run('ALTER TABLE loops DROP COLUMN executor_session_id')
      }
      if (loopsCols.some((c) => c.name === 'loop_kind')) {
        db.run('ALTER TABLE loops DROP COLUMN loop_kind')
      }

      const largeCols = db.prepare('PRAGMA table_info(loop_large_fields)').all() as Array<{ name: string }>
      if (largeCols.some((c) => c.name === 'goal')) {
        db.run('ALTER TABLE loop_large_fields DROP COLUMN goal')
      }
    },
  },
  {
    id: '151',
    description: 'Remove feature groups: drop group_features and feature_groups',
    apply: (db: Database) => {
      db.run('DROP TABLE IF EXISTS group_features')
      db.run('DROP TABLE IF EXISTS feature_groups')
    },
  },
  {
    id: '152',
    description: 'Add base_commit to loops: exact commit each loop worktree branched from',
    apply: (db: Database) => {
      const cols = db.prepare('PRAGMA table_info(loops)').all() as Array<{ name: string }>
      if (cols.some((c) => c.name === 'base_commit')) return
      db.run('ALTER TABLE loops ADD COLUMN base_commit TEXT')
    },
  },
  {
    id: '153',
    description: 'Create goals and goal_chunks tables for goal orchestration',
    apply: (db: Database) => {
      const tables = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='goals'").all()
      if (tables.length > 0) return
      db.run(loadSql('153_create_goals.sql'))
    },
  },
]