`scripts/migrate.js`

54 lines
#!/usr/bin/env node
/**
 * Plain SQL migration runner.
 * - Tracks applied migrations in schema_migrations table.
 * - Applies db/schema.sql first (idempotent), then any db/migrations/*.sql in lexical order.
 */
const fs = require('node:fs');
const path = require('node:path');
const { pool } = require('../agents/shared/db');

const ROOT = path.resolve(__dirname, '..');
const SCHEMA = path.join(ROOT, 'db/schema.sql');
const MIG_DIR = path.join(ROOT, 'db/migrations');

(async () => {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS schema_migrations (
      filename TEXT PRIMARY KEY,
      applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    );
  `);

  // schema.sql is idempotent — apply every run
  console.log('→ applying db/schema.sql');
  await pool.query(fs.readFileSync(SCHEMA, 'utf8'));
  console.log('✓ schema.sql');

  if (fs.existsSync(MIG_DIR)) {
    const files = fs.readdirSync(MIG_DIR)
      .filter(f => f.endsWith('.sql') && !f.startsWith('0001_init'))   // 0001 = pointer to schema.sql
      .sort();

    for (const f of files) {
      const exists = await pool.query('SELECT 1 FROM schema_migrations WHERE filename = $1', [f]);
      if (exists.rowCount > 0) { console.log(`✓ already applied  ${f}`); continue; }
      const sql = fs.readFileSync(path.join(MIG_DIR, f), 'utf8');
      console.log(`→ applying ${f}`);
      const client = await pool.connect();
      try {
        await client.query(sql);
        await client.query('INSERT INTO schema_migrations(filename) VALUES ($1)', [f]);
        console.log(`✓ applied  ${f}`);
      } finally {
        client.release();
      }
    }
  }

  await pool.end();
})().catch(err => {
  console.error('migrate failed:', err);
  process.exit(1);
});