atbb / apps / appview / scripts / migrate-permissions.ts

/**
 * One-time data migration: copies permissions from roles.permissions[]
 * into the role_permissions join table.
 *
 * Safe to re-run (ON CONFLICT DO NOTHING).
 * Must be run AFTER migration 0011 (role_permissions table exists)
 * and BEFORE migration 0012 (permissions column is dropped).
 */
import postgres from "postgres";
import { drizzle } from "drizzle-orm/postgres-js";
import * as schema from "@atbb/db/schema";
import { sql } from "drizzle-orm";

const databaseUrl = process.env.DATABASE_URL;
if (!databaseUrl) {
  console.error("DATABASE_URL is required");
  process.exit(1);
}

const client = postgres(databaseUrl);
const db = drizzle(client, { schema });

async function run() {
  // Read roles that still have permissions in the array column.
  // We use raw SQL here because the Drizzle schema will have the
  // permissions column removed by the time this script ships.
  const roles = await db.execute(
    sql`SELECT id, permissions FROM roles WHERE array_length(permissions, 1) > 0`
  );

  if (roles.length === 0) {
    console.log("No roles with permissions to migrate.");
    await client.end();
    return;
  }

  let totalPermissions = 0;

  for (const role of roles) {
    const roleId = role.id as bigint;
    const permissions = role.permissions as string[];

    if (!permissions || permissions.length === 0) continue;

    // Insert each permission as a row, skip duplicates (idempotent)
    await db.execute(
      sql`INSERT INTO role_permissions (role_id, permission)
          SELECT ${roleId}, unnest(${sql.raw(`ARRAY[${permissions.map((p: string) => `'${p.replace(/'/g, "''")}'`).join(",")}]`)}::text[])
          ON CONFLICT DO NOTHING`
    );

    totalPermissions += permissions.length;
    console.log(`  Role ${roleId}: migrated ${permissions.length} permissions`);
  }

  console.log(
    `\nMigrated ${totalPermissions} permissions across ${roles.length} roles.`
  );
  console.log("Safe to proceed with migration 0012 (drop permissions column).");

  await client.end();
}

run().catch((err) => {
  console.error("Migration failed:", err);
  process.exit(1);
});