1  #!/usr/bin/env node
  2  
  3  /**
  4   * Database Migration System
  5   *
  6   * Automatically applies pending SQL migrations from db/migrations/ directory.
  7   * Tracks applied migrations in a `migrations` table to prevent duplicate execution.
  8   *
  9   * Usage:
 10   *   npm run db-migrate        # Apply all pending migrations
 11   *   node scripts/db-migrate.js # Direct execution
 12   *
 13   * Migration files should be named with a sortable prefix (e.g., 001-description.sql)
 14   * and contain valid SQL statements.
 15   */
 16  
 17  import { createDatabaseConnection } from '../src/utils/db.js';
 18  import fs from 'fs';
 19  import path from 'path';
 20  import { fileURLToPath } from 'url';
 21  
 22  const __filename = fileURLToPath(import.meta.url);
 23  const __dirname = path.dirname(__filename);
 24  
 25  // Database path
 26  const dbPath = process.env.DATABASE_PATH || path.join(__dirname, '../db/sites.db');
 27  const migrationsDir = path.join(__dirname, '../db/migrations');
 28  
 29  // Initialize database connection
 30  const db = createDatabaseConnection(dbPath);
 31  db.pragma('foreign_keys = ON');
 32  
 33  /**
 34   * Initialize the migrations tracking table
 35   */
 36  function initMigrationsTable() {
 37    db.exec(`
 38      CREATE TABLE IF NOT EXISTS ops.migrations (
 39        id INTEGER PRIMARY KEY AUTOINCREMENT,
 40        filename TEXT UNIQUE NOT NULL,
 41        applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
 42      );
 43    `);
 44  }
 45  
 46  /**
 47   * Get list of already applied migrations
 48   * @returns {string[]} Array of migration filenames
 49   */
 50  function getAppliedMigrations() {
 51    const rows = db.prepare('SELECT filename FROM ops.migrations ORDER BY id').all();
 52    return rows.map(row => row.filename);
 53  }
 54  
 55  /**
 56   * Get list of pending migrations that haven't been applied
 57   * @returns {string[]} Array of migration filenames
 58   */
 59  function getPendingMigrations() {
 60    const applied = new Set(getAppliedMigrations());
 61  
 62    // Read all .sql files from migrations directory
 63    const files = fs
 64      .readdirSync(migrationsDir)
 65      .filter(f => f.endsWith('.sql'))
 66      .sort(); // Ensure alphabetical order
 67  
 68    return files.filter(f => !applied.has(f));
 69  }
 70  
 71  /**
 72   * Apply a single migration file
 73   * @param {string} filename - Migration filename
 74   * @param {boolean} force - Force apply even if column exists (for existing databases)
 75   */
 76  function applyMigration(filename, force = false) {
 77    // nosemgrep: javascript.lang.security.audit.path-traversal.path-join-resolve-traversal.path-join-resolve-traversal
 78    const filePath = path.join(migrationsDir, filename);
 79    // Safe: filename comes from readdirSync of our own migrations directory
 80    // eslint-disable-next-line security/detect-non-literal-fs-filename
 81    const sql = fs.readFileSync(filePath, 'utf8');
 82  
 83    console.log(`Applying migration: ${filename}`);
 84  
 85    // Capture site count before migration for canary check
 86    let preMigrationSiteCount = 0;
 87    try {
 88      const row = db.prepare('SELECT COUNT(*) as n FROM sites').get();
 89      preMigrationSiteCount = row ? row.n : 0;
 90    } catch (_e) { /* sites table may not exist yet */ }
 91  
 92    // Use transaction for safety — but skip outer transaction if SQL manages its own
 93    const hasOwnTransaction = /^\s*(BEGIN|BEGIN\s+TRANSACTION)/im.test(sql);
 94    if (!hasOwnTransaction) {
 95      db.exec('BEGIN TRANSACTION;');
 96    }
 97    try {
 98      // Execute the migration SQL
 99      db.exec(sql);
100  
101      // Record the migration as applied
102      db.prepare('INSERT INTO ops.migrations (filename) VALUES (?)').run(filename);
103  
104      if (!hasOwnTransaction) {
105        db.exec('COMMIT;');
106      }
107  
108      // Canary: if sites table existed before and is now empty, something went wrong
109      try {
110        const siteCount = db.prepare('SELECT COUNT(*) as n FROM sites').get();
111        if (siteCount && siteCount.n === 0 && preMigrationSiteCount > 0) {
112          console.error(`🚨 CANARY TRIGGERED: sites table was ${preMigrationSiteCount} rows before migration, now 0!`);
113          console.error(`🚨 Migration ${filename} may have wiped the sites table. HALTING.`);
114          process.exit(1);
115        }
116      } catch (_e) { /* sites table may not exist in fresh DB — that's fine */ }
117  
118      console.log(`✅ Applied: ${filename}`);
119    } catch (error) {
120      // Always attempt rollback — even if the SQL managed its own transaction,
121      // it may have started one that failed mid-way (leaving a dangling txn)
122      try { db.exec('ROLLBACK;'); } catch (_e) { /* no active transaction */ }
123  
124      // Handle schema conflicts gracefully for existing databases
125      // This allows migration system to work with databases where changes were applied manually
126      const isDuplicateColumn = error.message.includes('duplicate column name');
127      const isTableExists = error.message.includes('already exists');
128      const isNoSuchColumn = error.message.includes('no such column');
129      const isNoSuchTable = error.message.includes('no such table');
130  
131      if ((isDuplicateColumn || isTableExists || isNoSuchColumn || isNoSuchTable) && force) {
132        console.log(`⚠️  Migration already applied (skipping): ${filename}`);
133        // Record as applied even though we didn't execute it
134        db.prepare('INSERT INTO ops.migrations (filename) VALUES (?)').run(filename);
135        return;
136      }
137  
138      console.error(`❌ Failed to apply migration: ${filename}`);
139      console.error(error.message);
140      throw error;
141    }
142  }
143  
144  /**
145   * Main migration runner
146   * @param {Object} options - Migration options
147   * @param {boolean} options.force - Force apply even if columns exist (for existing databases)
148   */
149  function runMigrations(options = {}) {
150    const { force = false } = options;
151  
152    try {
153      console.log('🔍 Checking for pending migrations...\n');
154  
155      // Initialize migrations table if it doesn't exist
156      initMigrationsTable();
157  
158      // Get pending migrations
159      const pending = getPendingMigrations();
160  
161      if (pending.length === 0) {
162        console.log('✨ No pending migrations. Database is up to date.\n');
163        return;
164      }
165  
166      console.log(`Found ${pending.length} pending migration(s):\n`);
167      pending.forEach(file => console.log(`  - ${file}`));
168      console.log('');
169  
170      if (force) {
171        console.log('⚠️  Running in FORCE mode - will skip already-applied schema changes\n');
172      }
173  
174      // Apply each pending migration
175      for (const migration of pending) {
176        applyMigration(migration, force);
177      }
178  
179      console.log(`\n✅ Successfully applied ${pending.length} migration(s).\n`);
180    } catch (error) {
181      console.error('\n❌ Migration failed:', error.message);
182      process.exit(1);
183    } finally {
184      db.close();
185    }
186  }
187  
188  /**
189   * Safety check: refuse to run destructive migrations on a large DB without explicit confirmation.
190   * This prevents accidental data loss when running db-migrate.js on a production database.
191   *
192   * Pass --i-have-a-backup to bypass this check (only needed when sites count > SAFETY_THRESHOLD).
193   */
194  function safetyCheck() {
195    const SAFETY_THRESHOLD = 1000; // Sites count above which we require --i-have-a-backup
196    const hasBackupFlag = process.argv.includes('--i-have-a-backup');
197  
198    // Check if sites table exists and has data
199    const sitesExists = db.prepare("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='sites'").get()['COUNT(*)'];
200    if (!sitesExists) return; // Fresh DB, no data to protect
201  
202    const siteCount = db.prepare('SELECT COUNT(*) as cnt FROM sites').get().cnt;
203    if (siteCount > SAFETY_THRESHOLD && !hasBackupFlag) {
204      console.error(`\n🛑 SAFETY ABORT: sites table has ${siteCount.toLocaleString()} rows.`);
205      console.error('   Some migrations involve table recreation (DROP + recreate) which can lose data.');
206      console.error('   If you have a verified backup, re-run with: --i-have-a-backup\n');
207      console.error('   Example: node scripts/db-migrate.js --i-have-a-backup\n');
208      db.close();
209      process.exit(1);
210    }
211  }
212  
213  // Run migrations if executed directly
214  if (import.meta.url === `file://${process.argv[1]}`) {
215    // Check for --force flag
216    const force = process.argv.includes('--force');
217    safetyCheck();
218    runMigrations({ force });
219  }
220  
221  export { runMigrations, getPendingMigrations, getAppliedMigrations };