spb/claude-remote

1/**2 * Applies migrations/*.sql in lexical order, each inside its own transaction, recorded in3 * `schema_migrations`. A session-level advisory lock serialises concurrent starters.4 * Runnable directly: `node dist/migrate.js` (uses DATABASE_URL).5 */6import fs from 'node:fs';7import path from 'node:path';8import { fileURLToPath, pathToFileURL } from 'node:url';9import pg from 'pg';1011const LOCK_KEY = 0x43525f4d; // "CR_M"1213export function migrationsDir(): string {14  return path.join(path.dirname(fileURLToPath(import.meta.url)), '..', 'migrations');15}1617export async function migrate(databaseUrl: string, dir = migrationsDir()): Promise<string[]> {18  const client = new pg.Client({ connectionString: databaseUrl });19  await client.connect();20  const applied: string[] = [];21  try {22    await client.query('SELECT pg_advisory_lock($1)', [LOCK_KEY]);23    await client.query(24      'CREATE TABLE IF NOT EXISTS schema_migrations (name text PRIMARY KEY, applied_at timestamptz NOT NULL DEFAULT now())',25    );26    const done = new Set((await client.query('SELECT name FROM schema_migrations')).rows.map((r: { name: string }) => r.name));27    const files = fs28      .readdirSync(dir)29      .filter((f) => /^\d+_.*\.sql$/.test(f))30      .sort();31    for (const file of files) {32      if (done.has(file)) continue;33      const sql = fs.readFileSync(path.join(dir, file), 'utf8');34      try {35        await client.query('BEGIN');36        await client.query(sql);37        await client.query('INSERT INTO schema_migrations (name) VALUES ($1)', [file]);38        await client.query('COMMIT');39        applied.push(file);40      } catch (err) {41        await client.query('ROLLBACK').catch(() => undefined);42        throw new Error(`migration ${file} failed: ${(err as Error).message}`);43      }44    }45  } finally {46    await client.query('SELECT pg_advisory_unlock($1)', [LOCK_KEY]).catch(() => undefined);47    await client.end();48  }49  return applied;50}5152if (process.argv[1] && import.meta.url === pathToFileURL(process.argv[1]).href) {53  const url = process.env.DATABASE_URL;54  if (!url) {55    console.error('DATABASE_URL is required');56    process.exit(1);57  }58  migrate(url).then(59    (applied) => {60      console.log(applied.length ? `applied: ${applied.join(', ')}` : 'schema up to date');61    },62    (err: unknown) => {63      console.error((err as Error).message);64      process.exit(1);65    },66  );67}68