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 * Ensures API key schema matches Better Auth expectations:
6 * - reference_id exists and is populated from user_id when needed
7 * - config_id exists with default value
8 * - user_id is nullable (Better Auth inserts reference_id, not user_id)
9 */
10export async function migrateApiKeyReferenceId() {
11 console.log("🔄 Checking apikey table reference_id migration...");
12
13 try {
14 const tableExists = await db.execute(sql`
15 SELECT EXISTS (
16 SELECT 1
17 FROM information_schema.tables
18 WHERE table_name = 'apikey'
19 ) AS exists;
20 `);
21
22 const exists =
23 tableExists.rows[0]?.exists === true ||
24 tableExists.rows[0]?.exists === "t";
25 if (!exists) {
26 console.log("🛈 apikey table does not exist — skipping migration.");
27 return;
28 }
29
30 const hasReferenceIdColumn = await db.execute(sql`
31 SELECT column_name
32 FROM information_schema.columns
33 WHERE table_name = 'apikey'
34 AND column_name = 'reference_id'
35 `);
36
37 const hasConfigIdColumn = await db.execute(sql`
38 SELECT column_name
39 FROM information_schema.columns
40 WHERE table_name = 'apikey'
41 AND column_name = 'config_id'
42 `);
43
44 const hasUserIdColumn = await db.execute(sql`
45 SELECT column_name
46 FROM information_schema.columns
47 WHERE table_name = 'apikey'
48 AND column_name = 'user_id'
49 `);
50
51 if (hasReferenceIdColumn.rows.length === 0) {
52 console.log("➕ Adding reference_id column to apikey...");
53 await db.execute(sql`
54 ALTER TABLE "apikey" ADD COLUMN "reference_id" text;
55 `);
56 }
57
58 if (hasConfigIdColumn.rows.length === 0) {
59 console.log("➕ Adding config_id column to apikey...");
60 await db.execute(sql`
61 ALTER TABLE "apikey" ADD COLUMN "config_id" text DEFAULT 'default';
62 `);
63 }
64
65 if (hasUserIdColumn.rows.length > 0) {
66 await db.execute(sql`
67 UPDATE "apikey"
68 SET "reference_id" = "user_id"
69 WHERE "reference_id" IS NULL AND "user_id" IS NOT NULL;
70 `);
71
72 // Better Auth creates keys with reference_id and can leave user_id null.
73 await db.execute(sql`
74 ALTER TABLE "apikey"
75 ALTER COLUMN "user_id" DROP NOT NULL;
76 `);
77 }
78
79 await db.execute(sql`
80 UPDATE "apikey"
81 SET "config_id" = 'default'
82 WHERE "config_id" IS NULL;
83 `);
84
85 const hasConfigIndex = await db.execute(sql`
86 SELECT indexname
87 FROM pg_indexes
88 WHERE tablename = 'apikey'
89 AND indexname = 'apikey_configId_idx'
90 `);
91
92 if (hasConfigIndex.rows.length === 0) {
93 await db.execute(sql`
94 CREATE INDEX "apikey_configId_idx" ON "apikey" ("config_id");
95 `);
96 }
97
98 const hasReferenceIndex = await db.execute(sql`
99 SELECT indexname
100 FROM pg_indexes
101 WHERE tablename = 'apikey'
102 AND indexname = 'apikey_referenceId_idx'
103 `);
104
105 if (hasReferenceIndex.rows.length === 0) {
106 await db.execute(sql`
107 CREATE INDEX "apikey_referenceId_idx" ON "apikey" ("reference_id");
108 `);
109 }
110
111 const hasKeyIndex = await db.execute(sql`
112 SELECT indexname
113 FROM pg_indexes
114 WHERE tablename = 'apikey'
115 AND indexname = 'apikey_key_idx'
116 `);
117
118 if (hasKeyIndex.rows.length === 0) {
119 await db.execute(sql`
120 CREATE INDEX "apikey_key_idx" ON "apikey" ("key");
121 `);
122 }
123
124 console.log("✅ API key reference_id migration completed successfully!");
125 } catch (error) {
126 console.error("❌ Error during apikey migration:", error);
127 throw error;
128 }
129}