1  #!/usr/bin/env node
  2  /**
  3   * init-ops-db.js — Populate ops.db and telemetry.db from sites.db
  4   *
  5   * Copies tables from sites.db into two new database files:
  6   *   ops.db       — cron_jobs, cron_job_logs, cron_locks, pipeline_control,
  7   *                  migrations, settings
  8   *   telemetry.db — llm_usage, agent_logs, agent_tasks, agent_messages,
  9   *                  agent_outcomes, agent_state, agent_llm_usage,
 10   *                  system_metrics, system_health, pipeline_metrics
 11   *
 12   * Safe to run while pipeline is running — uses INSERT OR IGNORE so it won't
 13   * duplicate rows, and does not delete anything from sites.db.
 14   *
 15   * Usage:
 16   *   node scripts/init-ops-db.js [--dry-run]
 17   */
 18  
 19  import Database from 'better-sqlite3';
 20  import { join, dirname } from 'path';
 21  import { fileURLToPath } from 'url';
 22  
 23  const __dirname = dirname(fileURLToPath(import.meta.url));
 24  const projectRoot = join(__dirname, '..');
 25  
 26  const sitesDbPath = process.env.DATABASE_PATH || join(projectRoot, 'db/sites.db');
 27  const opsDbPath = process.env.OPS_DB_PATH || join(projectRoot, 'db/ops.db');
 28  const telDbPath = process.env.TEL_DB_PATH || join(projectRoot, 'db/telemetry.db');
 29  
 30  const dryRun = process.argv.includes('--dry-run');
 31  
 32  if (dryRun) console.log('[init-ops-db] DRY RUN — databases will not be modified');
 33  
 34  console.log('[init-ops-db] Source:', sitesDbPath);
 35  console.log('[init-ops-db] ops.db:', opsDbPath);
 36  console.log('[init-ops-db] telemetry.db:', telDbPath);
 37  
 38  const OPS_TABLES = ['cron_jobs', 'cron_job_logs', 'cron_locks', 'pipeline_control', 'migrations', 'settings'];
 39  const TEL_TABLES = ['llm_usage', 'agent_logs', 'agent_tasks', 'agent_messages', 'agent_outcomes', 'agent_state', 'agent_llm_usage', 'system_metrics', 'system_health', 'pipeline_metrics'];
 40  
 41  /**
 42   * Strip REFERENCES and FOREIGN KEY clauses from a CREATE TABLE DDL.
 43   * This allows copying telemetry tables without needing sites.db.
 44   */
 45  function stripForeignKeys(ddl) {
 46    // Remove inline REFERENCES clauses: e.g. "col TYPE REFERENCES tbl(col)"
 47    let cleaned = ddl.replace(/\s+REFERENCES\s+\w+\s*\([^)]+\)/gi, '');
 48    // Remove full FOREIGN KEY lines
 49    cleaned = cleaned.replace(/,\s*FOREIGN KEY\s*\([^)]+\)\s*REFERENCES\s*\w+\s*\([^)]+\)(\s*ON\s+(DELETE|UPDATE)\s+\w+)*/gi, '');
 50    return cleaned;
 51  }
 52  
 53  /**
 54   * Copy tables from source DB to a target DB file.
 55   * Opens source once, attaches target, copies all tables, detaches.
 56   */
 57  function copyTables(sourceDb, tableNames, targetDbPath, label) {
 58    if (dryRun) {
 59      for (const tableName of tableNames) {
 60        const row = sourceDb.prepare(`SELECT COUNT(*) as n FROM main."${tableName}"`).get();
 61        console.log(`[init-ops-db] [DRY RUN] would copy ${tableName} → ${label} (${row.n} rows)`);
 62      }
 63      return;
 64    }
 65  
 66    // Open target DB directly for schema setup
 67    const targetDb = new Database(targetDbPath);
 68    targetDb.pragma('journal_mode = WAL');
 69    targetDb.pragma('busy_timeout = 30000');
 70    targetDb.pragma('foreign_keys = OFF');
 71  
 72    for (const tableName of tableNames) {
 73      try {
 74        // Get source row count
 75        const sourceCount = sourceDb.prepare(`SELECT COUNT(*) as n FROM main."${tableName}"`).get().n;
 76  
 77        // Get DDL
 78        const ddlRow = sourceDb.prepare(
 79          `SELECT sql FROM sqlite_master WHERE type = 'table' AND name = ?`
 80        ).get(tableName);
 81  
 82        if (!ddlRow) {
 83          console.warn(`[init-ops-db] WARNING: no DDL found for ${tableName}, skipping`);
 84          continue;
 85        }
 86  
 87        // Strip FK references (telemetry tables reference sites.id)
 88        let ddl = stripForeignKeys(ddlRow.sql);
 89  
 90        // Add IF NOT EXISTS
 91        ddl = ddl.replace(/^CREATE TABLE\s+(?!IF NOT EXISTS)/i, 'CREATE TABLE IF NOT EXISTS ');
 92  
 93        // Create table in target
 94        try {
 95          targetDb.exec(ddl);
 96        } catch (ddlErr) {
 97          console.warn(`[init-ops-db] DDL failed for ${tableName} (${ddlErr.message}), using SELECT fallback`);
 98          // Attach source to target temporarily for the AS SELECT approach
 99          targetDb.exec(`ATTACH DATABASE '${sitesDbPath}' AS src_tmp`);
100          try {
101            targetDb.exec(`CREATE TABLE IF NOT EXISTS "${tableName}" AS SELECT * FROM src_tmp."${tableName}" WHERE 0`);
102          } finally {
103            targetDb.exec(`DETACH DATABASE src_tmp`);
104          }
105        }
106  
107        // Create indexes in target
108        const idxRows = sourceDb.prepare(
109          `SELECT sql FROM sqlite_master WHERE type = 'index' AND tbl_name = ? AND sql IS NOT NULL`
110        ).all(tableName);
111  
112        for (const idxRow of idxRows) {
113          try {
114            let idxDDL = idxRow.sql;
115            // Add IF NOT EXISTS
116            idxDDL = idxDDL.replace(
117              /^(CREATE\s+(?:UNIQUE\s+)?INDEX\s+)(?!IF NOT EXISTS)/i,
118              '$1IF NOT EXISTS '
119            );
120            targetDb.exec(idxDDL);
121          } catch (idxErr) {
122            // Non-critical
123            console.warn(`[init-ops-db] Index creation skipped for ${tableName}: ${idxErr.message}`);
124          }
125        }
126  
127        // Attach source to target for bulk copy
128        targetDb.exec(`ATTACH DATABASE '${sitesDbPath}' AS src`);
129        try {
130          const copied = targetDb.prepare(
131            `INSERT OR IGNORE INTO main."${tableName}" SELECT * FROM src."${tableName}"`
132          ).run().changes;
133  
134          const targetCount = targetDb.prepare(`SELECT COUNT(*) as n FROM main."${tableName}"`).get().n;
135          console.log(`[init-ops-db] ${label}.${tableName}: copied ${copied} rows (${sourceCount} in source, ${targetCount} in target)`);
136        } finally {
137          targetDb.exec(`DETACH DATABASE src`);
138        }
139      } catch (err) {
140        console.error(`[init-ops-db] ERROR copying ${tableName} to ${label}: ${err.message}`);
141      }
142    }
143  
144    targetDb.close();
145  }
146  
147  // Open source DB (readonly — we never write to it)
148  const sourceDb = new Database(sitesDbPath, { readonly: true });
149  
150  console.log('\n--- Phase 1: ops.db ---');
151  copyTables(sourceDb, OPS_TABLES, opsDbPath, 'ops');
152  
153  console.log('\n--- Phase 2: telemetry.db ---');
154  copyTables(sourceDb, TEL_TABLES, telDbPath, 'telemetry');
155  
156  sourceDb.close();
157  
158  console.log('\n[init-ops-db] Done.');
159  if (!dryRun) {
160    console.log('[init-ops-db] Verify with: sqlite3 db/ops.db ".tables" && sqlite3 db/telemetry.db ".tables"');
161    console.log('[init-ops-db] Next: run Step 6 to rename old tables in sites.db to _toDelete suffix.');
162  }