kaneo (minimalist kanban) fork to experiment adding a tangled integration
github.com/usekaneo/kaneo
1import { sql } from "drizzle-orm";
2import db from "../database";
3
4/**
5 * Migration script to handle conversion from user_email to user_id in workspace_member table.
6 * This runs before Drizzle migrations to ensure no NULL user_id values exist and prevents
7 * column collision errors during migration.
8 */
9export async function migrateWorkspaceUserEmail() {
10 console.log(
11 "🔄 Checking workspace_member table for user_email to user_id migration...",
12 );
13
14 try {
15 const tableExists = await db.execute(sql`
16 SELECT EXISTS (
17 SELECT 1
18 FROM information_schema.tables
19 WHERE table_name = 'workspace_member'
20 ) AS exists;
21 `);
22
23 const exists =
24 tableExists.rows[0]?.exists === true ||
25 tableExists.rows[0]?.exists === "t";
26 if (!exists) {
27 console.log(
28 "🛈 workspace_member table does not exist — skipping migration.",
29 );
30 return;
31 }
32
33 // Check if user_email column still exists
34 const hasUserEmailColumn = await db.execute(sql`
35 SELECT column_name
36 FROM information_schema.columns
37 WHERE table_name = 'workspace_member'
38 AND column_name = 'user_email'
39 `);
40
41 // Check if user_id column already exists
42 const hasUserIdColumn = await db.execute(sql`
43 SELECT column_name
44 FROM information_schema.columns
45 WHERE table_name = 'workspace_member'
46 AND column_name = 'user_id'
47 `);
48
49 if (hasUserEmailColumn.rows.length > 0) {
50 console.log("📧 Found user_email column, migrating to user_id...");
51
52 // Add user_id column if it doesn't exist
53 if (hasUserIdColumn.rows.length === 0) {
54 await db.execute(sql`
55 ALTER TABLE "workspace_member" ADD COLUMN "user_id" text;
56 `);
57 console.log("➕ Added user_id column");
58 }
59
60 // Update user_id based on user_email
61 await db.execute(sql`
62 UPDATE "workspace_member"
63 SET "user_id" = (
64 SELECT u.id
65 FROM "user" u
66 WHERE u.email = "workspace_member"."user_email"
67 )
68 WHERE "user_id" IS NULL AND "user_email" IS NOT NULL;
69 `);
70
71 // Remove records where user_email doesn't match any existing user
72 const orphanedRecords = await db.execute(sql`
73 SELECT COUNT(*) as count
74 FROM "workspace_member"
75 WHERE "user_id" IS NULL AND "user_email" IS NOT NULL;
76 `);
77
78 if (
79 orphanedRecords.rows[0]?.count &&
80 Number(orphanedRecords.rows[0].count) > 0
81 ) {
82 console.log(
83 `⚠️ Found ${orphanedRecords.rows[0].count} workspace_member records with invalid user_email. Removing them...`,
84 );
85
86 await db.execute(sql`
87 DELETE FROM "workspace_member"
88 WHERE "user_id" IS NULL AND "user_email" IS NOT NULL;
89 `);
90 }
91
92 // Remove records where both user_email and user_id are NULL
93 const nullRecords = await db.execute(sql`
94 SELECT COUNT(*) as count
95 FROM "workspace_member"
96 WHERE "user_id" IS NULL AND ("user_email" IS NULL OR "user_email" = '');
97 `);
98
99 if (nullRecords.rows[0]?.count && Number(nullRecords.rows[0].count) > 0) {
100 console.log(
101 `⚠️ Found ${nullRecords.rows[0].count} workspace_member records with no user identification. Removing them...`,
102 );
103
104 await db.execute(sql`
105 DELETE FROM "workspace_member"
106 WHERE "user_id" IS NULL AND ("user_email" IS NULL OR "user_email" = '');
107 `);
108 }
109
110 // Drop the user_email column (completing the migration)
111 await db.execute(sql`
112 ALTER TABLE "workspace_member" DROP COLUMN "user_email";
113 `);
114
115 console.log(
116 "✅ Successfully migrated user_email to user_id and dropped user_email column",
117 );
118 } else if (hasUserIdColumn.rows.length === 0) {
119 // Neither column exists, add user_id column
120 console.log("➕ Adding user_id column to workspace_member table...");
121 await db.execute(sql`
122 ALTER TABLE "workspace_member" ADD COLUMN "user_id" text;
123 `);
124 }
125
126 // Check if there are any remaining NULL user_id values
127 const nullUserIds = await db.execute(sql`
128 SELECT COUNT(*) as count
129 FROM "workspace_member"
130 WHERE "user_id" IS NULL;
131 `);
132
133 if (nullUserIds.rows[0]?.count && Number(nullUserIds.rows[0].count) > 0) {
134 console.log(
135 `⚠️ Found ${nullUserIds.rows[0].count} workspace_member records with NULL user_id. Removing them...`,
136 );
137
138 await db.execute(sql`
139 DELETE FROM "workspace_member"
140 WHERE "user_id" IS NULL;
141 `);
142
143 console.log("✅ Removed records with NULL user_id");
144 }
145
146 console.log("✅ Workspace member migration completed successfully!");
147 } catch (error) {
148 console.error("❌ Error during workspace member migration:", error);
149 throw error;
150 }
151}