candle-annotator/scripts/migrate-users.ts

/**
 * Data migration script for user-accounts feature (Task 2.5)
 *
 * 1. Creates a default admin user from DEFAULT_ADMIN_EMAIL / DEFAULT_ADMIN_PASSWORD env vars
 * 2. Backfills user_id on all existing rows in charts, annotations, annotation_types,
 *    span_annotations, span_label_types to the default admin user's ID
 *
 * Idempotent: safe to run multiple times.
 *
 * Usage:  DATABASE_URL=... DEFAULT_ADMIN_EMAIL=... DEFAULT_ADMIN_PASSWORD=... npx tsx scripts/migrate-users.ts
 */

import { Pool } from 'pg';
import bcrypt from 'bcryptjs';

const SALT_ROUNDS = 12;

async function main() {
  const DATABASE_URL = process.env.DATABASE_URL;
  if (!DATABASE_URL) {
    throw new Error('DATABASE_URL environment variable is required');
  }

  const adminEmail = process.env.DEFAULT_ADMIN_EMAIL;
  const adminPassword = process.env.DEFAULT_ADMIN_PASSWORD;
  if (!adminEmail || !adminPassword) {
    throw new Error(
      'DEFAULT_ADMIN_EMAIL and DEFAULT_ADMIN_PASSWORD environment variables are required'
    );
  }

  const pool = new Pool({ connectionString: DATABASE_URL });

  try {
    // ---- Step 1: Upsert default admin user ----
    console.log(`Creating/finding default admin user: ${adminEmail}`);

    // Check if user already exists
    const existing = await pool.query(
      'SELECT id FROM users WHERE email = $1',
      [adminEmail]
    );

    let adminUserId: string;

    if (existing.rows.length > 0) {
      adminUserId = existing.rows[0].id;
      console.log(`  Admin user already exists (id: ${adminUserId})`);
    } else {
      const passwordHash = await bcrypt.hash(adminPassword, SALT_ROUNDS);
      const inserted = await pool.query(
        `INSERT INTO users (email, password_hash, name, provider)
         VALUES ($1, $2, $3, 'credentials')
         RETURNING id`,
        [adminEmail, passwordHash, 'Admin']
      );
      adminUserId = inserted.rows[0].id;
      console.log(`  Admin user created (id: ${adminUserId})`);
    }

    // ---- Step 2: Backfill user_id on all existing rows ----
    const tables = [
      'charts',
      'annotations',
      'annotation_types',
      'span_annotations',
      'span_label_types',
    ];

    for (const table of tables) {
      const result = await pool.query(
        `UPDATE ${table} SET user_id = $1 WHERE user_id IS NULL`,
        [adminUserId]
      );
      console.log(`  ${table}: backfilled ${result.rowCount} rows`);
    }

    console.log('Migration complete.');
  } finally {
    await pool.end();
  }
}

main()
  .then(() => process.exit(0))
  .catch((err) => {
    console.error('Migration failed:', err);
    process.exit(1);
  });