platform/scripts/migrate-auth-to-pg.ts

/**
 * Migration script: auth data from JSON files → PostgreSQL
 *
 * Migrates:
 * - users.json → users table
 * - passkeys.json → passkeys table (email → userId FK)
 * - token-blacklist.json → token_blacklist table
 *
 * Usage: bun run scripts/migrate-auth-to-pg.ts
 */

import { join } from 'node:path';
import { db } from 'officerdb/db';
import { users, passkeys, tokenBlacklist } from 'officerdb/schema';

const DATA_PATH = process.env.DATA_PATH ?? join(process.cwd(), 'data');
const AUTH_DIR = join(DATA_PATH, 'auth');

type OldUser = {
  id: number;
  email: string;
  password: string | null;
  role: string;
  status: string;
  name: string | null;
  username: string | null;
  avatar: string | null;
  passwordChangedAt: number | null;
};

type OldPasskey = {
  id: number;
  email: string;
  origin: string | null;
  credentialId: string | null;
  publicKey: string | null;
  counter: number;
};

type OldBlacklistEntry = {
  jti: string;
  expiresAt: number;
};

async function readJson<T>(path: string, fallback: T): Promise<T> {
  try {
    const file = Bun.file(path);
    if (!(await file.exists())) return fallback;
    return (await file.json()) as T;
  } catch {
    return fallback;
  }
}

async function migrate() {
  console.log(`[migrate] Reading JSON files from ${AUTH_DIR}`);

  const oldUsers = await readJson<OldUser[]>(join(AUTH_DIR, 'users.json'), []);
  const oldPasskeys = await readJson<OldPasskey[]>(join(AUTH_DIR, 'passkeys.json'), []);
  const oldBlacklist = await readJson<OldBlacklistEntry[]>(join(AUTH_DIR, 'token-blacklist.json'), []);

  console.log(`[migrate] Found: ${oldUsers.length} users, ${oldPasskeys.length} passkeys, ${oldBlacklist.length} blacklisted tokens`);

  if (oldUsers.length === 0) {
    console.log('[migrate] No users to migrate. Done.');
    process.exit(0);
  }

  // Build email → userId map for passkey migration
  const emailToUserId = new Map<string, number>();

  // Migrate users
  console.log('[migrate] Migrating users...');
  for (const u of oldUsers) {
    const [inserted] = await db
      .insert(users)
      .values({
        email: u.email,
        password: u.password,
        status: u.status as 'Unverified' | 'Active' | 'Prospect' | 'Invited' | 'Blocked' | 'Banned' | 'Deleted',
        name: u.name,
        username: u.username,
        avatar: u.avatar,
        passwordChangedAt: u.passwordChangedAt ? new Date(u.passwordChangedAt) : null,
      })
      .returning();

    emailToUserId.set(u.email, inserted!.id);
    console.log(`  [user] ${u.email} (old id=${u.id} → new id=${inserted!.id})`);
  }

  // Migrate passkeys
  if (oldPasskeys.length > 0) {
    console.log('[migrate] Migrating passkeys...');
    for (const p of oldPasskeys) {
      const userId = emailToUserId.get(p.email);
      if (!userId) {
        console.warn(`  [passkey] Skipping passkey for unknown email: ${p.email}`);
        continue;
      }

      await db.insert(passkeys).values({
        userId,
        origin: p.origin,
        credentialId: p.credentialId,
        publicKey: p.publicKey,
        counter: p.counter,
      });
      console.log(`  [passkey] ${p.email} / ${p.origin}`);
    }
  }

  // Migrate token blacklist
  if (oldBlacklist.length > 0) {
    const now = Math.floor(Date.now() / 1000);
    const active = oldBlacklist.filter((b) => b.expiresAt >= now);
    console.log(`[migrate] Migrating ${active.length} active blacklisted tokens (${oldBlacklist.length - active.length} expired, skipped)...`);

    for (const b of active) {
      await db
        .insert(tokenBlacklist)
        .values({
          jti: b.jti,
          expiresAt: new Date(b.expiresAt * 1000),
        })
        .onConflictDoNothing();
    }
  }

  console.log('[migrate] Done!');
  process.exit(0);
}

migrate().catch((err) => {
  console.error('[migrate] Failed:', err);
  process.exit(1);
});