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 * Repairs notification preference tables for instances where migration state
6 * drift left the schema partially applied.
7 */
8export async function migrateNotificationPreferencesSchema() {
9 console.log("🔄 Checking notification preference schema...");
10
11 try {
12 await db.execute(sql`
13 CREATE TABLE IF NOT EXISTS "user_notification_preference" (
14 "id" text PRIMARY KEY NOT NULL,
15 "user_id" text NOT NULL,
16 "email_enabled" boolean DEFAULT false NOT NULL,
17 "ntfy_enabled" boolean DEFAULT false NOT NULL,
18 "ntfy_server_url" text,
19 "ntfy_topic" text,
20 "ntfy_token" text,
21 "gotify_enabled" boolean DEFAULT false NOT NULL,
22 "gotify_server_url" text,
23 "gotify_token" text,
24 "webhook_enabled" boolean DEFAULT false NOT NULL,
25 "webhook_url" text,
26 "webhook_secret" text,
27 "created_at" timestamp DEFAULT now() NOT NULL,
28 "updated_at" timestamp DEFAULT now() NOT NULL
29 );
30 `);
31
32 await db.execute(sql`
33 ALTER TABLE "user_notification_preference"
34 ADD COLUMN IF NOT EXISTS "email_enabled" boolean DEFAULT false NOT NULL,
35 ADD COLUMN IF NOT EXISTS "ntfy_enabled" boolean DEFAULT false NOT NULL,
36 ADD COLUMN IF NOT EXISTS "ntfy_server_url" text,
37 ADD COLUMN IF NOT EXISTS "ntfy_topic" text,
38 ADD COLUMN IF NOT EXISTS "ntfy_token" text,
39 ADD COLUMN IF NOT EXISTS "gotify_enabled" boolean DEFAULT false NOT NULL,
40 ADD COLUMN IF NOT EXISTS "gotify_server_url" text,
41 ADD COLUMN IF NOT EXISTS "gotify_token" text,
42 ADD COLUMN IF NOT EXISTS "webhook_enabled" boolean DEFAULT false NOT NULL,
43 ADD COLUMN IF NOT EXISTS "webhook_url" text,
44 ADD COLUMN IF NOT EXISTS "webhook_secret" text,
45 ADD COLUMN IF NOT EXISTS "created_at" timestamp DEFAULT now() NOT NULL,
46 ADD COLUMN IF NOT EXISTS "updated_at" timestamp DEFAULT now() NOT NULL;
47 `);
48
49 await db.execute(sql`
50 CREATE TABLE IF NOT EXISTS "user_notification_workspace_rule" (
51 "id" text PRIMARY KEY NOT NULL,
52 "user_id" text NOT NULL,
53 "workspace_id" text NOT NULL,
54 "is_active" boolean DEFAULT true NOT NULL,
55 "email_enabled" boolean DEFAULT false NOT NULL,
56 "ntfy_enabled" boolean DEFAULT false NOT NULL,
57 "gotify_enabled" boolean DEFAULT false NOT NULL,
58 "webhook_enabled" boolean DEFAULT false NOT NULL,
59 "project_mode" text DEFAULT 'all' NOT NULL,
60 "created_at" timestamp DEFAULT now() NOT NULL,
61 "updated_at" timestamp DEFAULT now() NOT NULL
62 );
63 `);
64
65 await db.execute(sql`
66 ALTER TABLE "user_notification_workspace_rule"
67 ADD COLUMN IF NOT EXISTS "is_active" boolean DEFAULT true NOT NULL,
68 ADD COLUMN IF NOT EXISTS "email_enabled" boolean DEFAULT false NOT NULL,
69 ADD COLUMN IF NOT EXISTS "ntfy_enabled" boolean DEFAULT false NOT NULL,
70 ADD COLUMN IF NOT EXISTS "gotify_enabled" boolean DEFAULT false NOT NULL,
71 ADD COLUMN IF NOT EXISTS "webhook_enabled" boolean DEFAULT false NOT NULL,
72 ADD COLUMN IF NOT EXISTS "project_mode" text DEFAULT 'all' NOT NULL,
73 ADD COLUMN IF NOT EXISTS "created_at" timestamp DEFAULT now() NOT NULL,
74 ADD COLUMN IF NOT EXISTS "updated_at" timestamp DEFAULT now() NOT NULL;
75 `);
76
77 await db.execute(sql`
78 CREATE TABLE IF NOT EXISTS "user_notification_workspace_project" (
79 "id" text PRIMARY KEY NOT NULL,
80 "workspace_id" text NOT NULL,
81 "workspace_rule_id" text NOT NULL,
82 "project_id" text NOT NULL,
83 "created_at" timestamp DEFAULT now() NOT NULL,
84 "updated_at" timestamp DEFAULT now() NOT NULL
85 );
86 `);
87
88 await db.execute(sql`
89 ALTER TABLE "user_notification_workspace_project"
90 ADD COLUMN IF NOT EXISTS "created_at" timestamp DEFAULT now() NOT NULL,
91 ADD COLUMN IF NOT EXISTS "updated_at" timestamp DEFAULT now() NOT NULL;
92 `);
93
94 await db.execute(sql`
95 CREATE UNIQUE INDEX IF NOT EXISTS "user_notification_preference_user_id_unique"
96 ON "user_notification_preference" ("user_id");
97 `);
98 await db.execute(sql`
99 CREATE UNIQUE INDEX IF NOT EXISTS "user_notification_workspace_rule_user_workspace_unique"
100 ON "user_notification_workspace_rule" ("user_id", "workspace_id");
101 `);
102 await db.execute(sql`
103 CREATE UNIQUE INDEX IF NOT EXISTS "user_notification_workspace_rule_workspace_id_id_unique"
104 ON "user_notification_workspace_rule" ("workspace_id", "id");
105 `);
106 await db.execute(sql`
107 CREATE UNIQUE INDEX IF NOT EXISTS "user_notification_workspace_project_rule_project_unique"
108 ON "user_notification_workspace_project" ("workspace_rule_id", "project_id");
109 `);
110 await db.execute(sql`
111 CREATE INDEX IF NOT EXISTS "user_notification_workspace_rule_userId_idx"
112 ON "user_notification_workspace_rule" ("user_id");
113 `);
114 await db.execute(sql`
115 CREATE INDEX IF NOT EXISTS "user_notification_workspace_rule_workspaceId_idx"
116 ON "user_notification_workspace_rule" ("workspace_id");
117 `);
118 await db.execute(sql`
119 CREATE INDEX IF NOT EXISTS "user_notification_workspace_project_ruleId_idx"
120 ON "user_notification_workspace_project" ("workspace_rule_id");
121 `);
122 await db.execute(sql`
123 CREATE INDEX IF NOT EXISTS "user_notification_workspace_project_projectId_idx"
124 ON "user_notification_workspace_project" ("project_id");
125 `);
126
127 await db.execute(sql`
128 DO $$
129 BEGIN
130 IF NOT EXISTS (
131 SELECT 1 FROM pg_constraint
132 WHERE conname = 'user_notification_preference_user_id_user_id_fk'
133 ) THEN
134 ALTER TABLE "user_notification_preference"
135 ADD CONSTRAINT "user_notification_preference_user_id_user_id_fk"
136 FOREIGN KEY ("user_id") REFERENCES "public"."user"("id")
137 ON DELETE cascade ON UPDATE cascade;
138 END IF;
139 END $$;
140 `);
141
142 await db.execute(sql`
143 DO $$
144 BEGIN
145 IF NOT EXISTS (
146 SELECT 1 FROM pg_constraint
147 WHERE conname = 'user_notification_workspace_rule_user_id_user_id_fk'
148 ) THEN
149 ALTER TABLE "user_notification_workspace_rule"
150 ADD CONSTRAINT "user_notification_workspace_rule_user_id_user_id_fk"
151 FOREIGN KEY ("user_id") REFERENCES "public"."user"("id")
152 ON DELETE cascade ON UPDATE cascade;
153 END IF;
154 END $$;
155 `);
156
157 await db.execute(sql`
158 DO $$
159 BEGIN
160 IF NOT EXISTS (
161 SELECT 1 FROM pg_constraint
162 WHERE conname = 'user_notification_workspace_rule_workspace_id_workspace_id_fk'
163 ) THEN
164 ALTER TABLE "user_notification_workspace_rule"
165 ADD CONSTRAINT "user_notification_workspace_rule_workspace_id_workspace_id_fk"
166 FOREIGN KEY ("workspace_id") REFERENCES "public"."workspace"("id")
167 ON DELETE cascade ON UPDATE cascade;
168 END IF;
169 END $$;
170 `);
171
172 await db.execute(sql`
173 DO $$
174 BEGIN
175 IF NOT EXISTS (
176 SELECT 1 FROM pg_constraint
177 WHERE conname = 'user_notification_workspace_project_workspace_id_workspace_id_fk'
178 ) THEN
179 ALTER TABLE "user_notification_workspace_project"
180 ADD CONSTRAINT "user_notification_workspace_project_workspace_id_workspace_id_fk"
181 FOREIGN KEY ("workspace_id") REFERENCES "public"."workspace"("id")
182 ON DELETE cascade ON UPDATE cascade;
183 END IF;
184 END $$;
185 `);
186
187 console.log("✅ Notification preference schema check complete!");
188 } catch (error) {
189 console.error("❌ Error during notification preference migration:", error);
190 throw error;
191 }
192}