malpercio.dev
WIP! A BB-style forum, on the ATmosphere!
We're still working... we'll be back soon when we have something to show off!
node
typescript
hono
htmx
atproto
1/**
2 * One-time data migration: copies permissions from roles.permissions[]
3 * into the role_permissions join table.
4 *
5 * Safe to re-run (ON CONFLICT DO NOTHING).
6 * Must be run AFTER migration 0011 (role_permissions table exists)
7 * and BEFORE migration 0012 (permissions column is dropped).
8 */
9import postgres from "postgres";
10import { drizzle } from "drizzle-orm/postgres-js";
11import * as schema from "@atbb/db/schema";
12import { sql } from "drizzle-orm";
13
14const databaseUrl = process.env.DATABASE_URL;
15if (!databaseUrl) {
16 console.error("DATABASE_URL is required");
17 process.exit(1);
18}
19
20const client = postgres(databaseUrl);
21const db = drizzle(client, { schema });
22
23async function run() {
24 // Read roles that still have permissions in the array column.
25 // We use raw SQL here because the Drizzle schema will have the
26 // permissions column removed by the time this script ships.
27 const roles = await db.execute(
28 sql`SELECT id, permissions FROM roles WHERE array_length(permissions, 1) > 0`
29 );
30
31 if (roles.length === 0) {
32 console.log("No roles with permissions to migrate.");
33 await client.end();
34 return;
35 }
36
37 let totalPermissions = 0;
38
39 for (const role of roles) {
40 const roleId = role.id as bigint;
41 const permissions = role.permissions as string[];
42
43 if (!permissions || permissions.length === 0) continue;
44
45 // Insert each permission as a row, skip duplicates (idempotent)
46 await db.execute(
47 sql`INSERT INTO role_permissions (role_id, permission)
48 SELECT ${roleId}, unnest(${sql.raw(`ARRAY[${permissions.map((p: string) => `'${p.replace(/'/g, "''")}'`).join(",")}]`)}::text[])
49 ON CONFLICT DO NOTHING`
50 );
51
52 totalPermissions += permissions.length;
53 console.log(` Role ${roleId}: migrated ${permissions.length} permissions`);
54 }
55
56 console.log(
57 `\nMigrated ${totalPermissions} permissions across ${roles.length} roles.`
58 );
59 console.log("Safe to proceed with migration 0012 (drop permissions column).");
60
61 await client.end();
62}
63
64run().catch((err) => {
65 console.error("Migration failed:", err);
66 process.exit(1);
67});