kaneo (minimalist kanban) fork to experiment adding a tangled integration
github.com/usekaneo/kaneo
1-- Clear session table since sessions can be recreated
2TRUNCATE TABLE "session";
3--> statement-breakpoint
4CREATE TABLE "account" (
5 "id" text PRIMARY KEY NOT NULL,
6 "account_id" text NOT NULL,
7 "provider_id" text NOT NULL,
8 "user_id" text NOT NULL,
9 "access_token" text,
10 "refresh_token" text,
11 "id_token" text,
12 "access_token_expires_at" timestamp,
13 "refresh_token_expires_at" timestamp,
14 "scope" text,
15 "password" text,
16 "created_at" timestamp DEFAULT now() NOT NULL,
17 "updated_at" timestamp DEFAULT now() NOT NULL
18);
19--> statement-breakpoint
20CREATE TABLE "verification" (
21 "id" text PRIMARY KEY NOT NULL,
22 "identifier" text NOT NULL,
23 "value" text NOT NULL,
24 "expires_at" timestamp NOT NULL,
25 "created_at" timestamp DEFAULT now() NOT NULL,
26 "updated_at" timestamp DEFAULT now() NOT NULL
27);
28--> statement-breakpoint
29-- Drop existing foreign key constraints
30ALTER TABLE "activity" DROP CONSTRAINT "activity_user_email_user_email_fk";
31--> statement-breakpoint
32ALTER TABLE "notification" DROP CONSTRAINT "notification_user_email_user_email_fk";
33--> statement-breakpoint
34ALTER TABLE "session" DROP CONSTRAINT "session_user_id_user_id_fk";
35--> statement-breakpoint
36ALTER TABLE "task" DROP CONSTRAINT "task_assignee_email_user_email_fk";
37--> statement-breakpoint
38ALTER TABLE "time_entry" DROP CONSTRAINT "time_entry_user_email_user_email_fk";
39--> statement-breakpoint
40ALTER TABLE "workspace" DROP CONSTRAINT "workspace_owner_email_user_email_fk";
41--> statement-breakpoint
42ALTER TABLE "workspace_member" DROP CONSTRAINT "workspace_member_workspace_id_workspace_id_fk";
43--> statement-breakpoint
44-- Add session columns for better-auth (idempotent)
45DO $$
46BEGIN
47 IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'session' AND column_name = 'token') THEN
48 ALTER TABLE "session" ADD COLUMN "token" text NOT NULL DEFAULT '';
49 ALTER TABLE "session" ALTER COLUMN "token" DROP DEFAULT;
50 END IF;
51 IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'session' AND column_name = 'created_at') THEN
52 ALTER TABLE "session" ADD COLUMN "created_at" timestamp DEFAULT now() NOT NULL;
53 END IF;
54 IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'session' AND column_name = 'updated_at') THEN
55 ALTER TABLE "session" ADD COLUMN "updated_at" timestamp DEFAULT now() NOT NULL;
56 END IF;
57 IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'session' AND column_name = 'ip_address') THEN
58 ALTER TABLE "session" ADD COLUMN "ip_address" text;
59 END IF;
60 IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'session' AND column_name = 'user_agent') THEN
61 ALTER TABLE "session" ADD COLUMN "user_agent" text;
62 END IF;
63END $$;--> statement-breakpoint
64-- Add user columns for better-auth (idempotent)
65DO $$
66BEGIN
67 IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'user' AND column_name = 'email_verified') THEN
68 ALTER TABLE "user" ADD COLUMN "email_verified" boolean DEFAULT false NOT NULL;
69 END IF;
70 IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'user' AND column_name = 'image') THEN
71 ALTER TABLE "user" ADD COLUMN "image" text;
72 END IF;
73 IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'user' AND column_name = 'updated_at') THEN
74 ALTER TABLE "user" ADD COLUMN "updated_at" timestamp DEFAULT now() NOT NULL;
75 END IF;
76 IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'user' AND column_name = 'is_anonymous') THEN
77 ALTER TABLE "user" ADD COLUMN "is_anonymous" boolean;
78 END IF;
79END $$;--> statement-breakpoint
80-- Convert activity.user_email to activity.user_id (idempotent)
81DO $$
82BEGIN
83 IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'activity' AND column_name = 'user_id') THEN
84 ALTER TABLE "activity" ADD COLUMN "user_id" text;
85 END IF;
86END $$;--> statement-breakpoint
87DO $$
88BEGIN
89 IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'activity' AND column_name = 'user_email') THEN
90 UPDATE "activity" SET "user_id" = (
91 SELECT u.id
92 FROM "user" u
93 WHERE u.email = "activity"."user_email"
94 ) WHERE "user_id" IS NULL;
95 END IF;
96END $$;--> statement-breakpoint
97DELETE FROM "activity" WHERE "user_id" IS NULL;--> statement-breakpoint
98DO $$
99BEGIN
100 IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'activity' AND column_name = 'user_id' AND is_nullable = 'YES') THEN
101 ALTER TABLE "activity" ALTER COLUMN "user_id" SET NOT NULL;
102 END IF;
103END $$;--> statement-breakpoint
104DO $$
105BEGIN
106 IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'activity' AND column_name = 'user_email') THEN
107 ALTER TABLE "activity" DROP COLUMN "user_email";
108 END IF;
109END $$;--> statement-breakpoint
110-- Convert notification.user_email to notification.user_id (idempotent)
111DO $$
112BEGIN
113 IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'notification' AND column_name = 'user_id') THEN
114 ALTER TABLE "notification" ADD COLUMN "user_id" text;
115 END IF;
116END $$;--> statement-breakpoint
117DO $$
118BEGIN
119 IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'notification' AND column_name = 'user_email') THEN
120 UPDATE "notification" SET "user_id" = (
121 SELECT u.id
122 FROM "user" u
123 WHERE u.email = "notification"."user_email"
124 ) WHERE "user_id" IS NULL;
125 END IF;
126END $$;--> statement-breakpoint
127DELETE FROM "notification" WHERE "user_id" IS NULL;--> statement-breakpoint
128DO $$
129BEGIN
130 IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'notification' AND column_name = 'user_id' AND is_nullable = 'YES') THEN
131 ALTER TABLE "notification" ALTER COLUMN "user_id" SET NOT NULL;
132 END IF;
133END $$;--> statement-breakpoint
134DO $$
135BEGIN
136 IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'notification' AND column_name = 'user_email') THEN
137 ALTER TABLE "notification" DROP COLUMN "user_email";
138 END IF;
139END $$;--> statement-breakpoint
140-- Convert task.assignee_email to task.assignee_id (idempotent)
141DO $$
142BEGIN
143 IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'task' AND column_name = 'assignee_id') THEN
144 ALTER TABLE "task" ADD COLUMN "assignee_id" text;
145 END IF;
146END $$;--> statement-breakpoint
147DO $$
148BEGIN
149 IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'task' AND column_name = 'assignee_email') THEN
150 UPDATE "task" SET "assignee_id" = (
151 SELECT u.id
152 FROM "user" u
153 WHERE u.email = "task"."assignee_email"
154 ) WHERE "assignee_email" IS NOT NULL AND "assignee_id" IS NULL;
155 END IF;
156END $$;--> statement-breakpoint
157DO $$
158BEGIN
159 IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'task' AND column_name = 'assignee_email') THEN
160 ALTER TABLE "task" DROP COLUMN "assignee_email";
161 END IF;
162END $$;--> statement-breakpoint
163-- Convert time_entry.user_email to time_entry.user_id (idempotent)
164DO $$
165BEGIN
166 IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'time_entry' AND column_name = 'user_id') THEN
167 ALTER TABLE "time_entry" ADD COLUMN "user_id" text;
168 END IF;
169END $$;--> statement-breakpoint
170DO $$
171BEGIN
172 IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'time_entry' AND column_name = 'user_email') THEN
173 UPDATE "time_entry" SET "user_id" = (
174 SELECT u.id
175 FROM "user" u
176 WHERE u.email = "time_entry"."user_email"
177 ) WHERE "user_email" IS NOT NULL AND "user_id" IS NULL;
178 END IF;
179END $$;--> statement-breakpoint
180DO $$
181BEGIN
182 IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'time_entry' AND column_name = 'user_email') THEN
183 ALTER TABLE "time_entry" DROP COLUMN "user_email";
184 END IF;
185END $$;--> statement-breakpoint
186-- Convert workspace.owner_email to workspace.owner_id (idempotent)
187DO $$
188BEGIN
189 IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'workspace' AND column_name = 'owner_id') THEN
190 ALTER TABLE "workspace" ADD COLUMN "owner_id" text;
191 END IF;
192END $$;--> statement-breakpoint
193DO $$
194BEGIN
195 IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'workspace' AND column_name = 'owner_email') THEN
196 UPDATE "workspace" SET "owner_id" = (
197 SELECT u.id
198 FROM "user" u
199 WHERE u.email = "workspace"."owner_email"
200 ) WHERE "owner_id" IS NULL;
201 END IF;
202END $$;--> statement-breakpoint
203DELETE FROM "workspace" WHERE "owner_id" IS NULL;--> statement-breakpoint
204DO $$
205BEGIN
206 IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'workspace' AND column_name = 'owner_id' AND is_nullable = 'YES') THEN
207 ALTER TABLE "workspace" ALTER COLUMN "owner_id" SET NOT NULL;
208 END IF;
209END $$;--> statement-breakpoint
210DO $$
211BEGIN
212 IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'workspace' AND column_name = 'owner_email') THEN
213 ALTER TABLE "workspace" DROP COLUMN "owner_email";
214 END IF;
215END $$;--> statement-breakpoint
216-- Convert workspace_member.user_email to workspace_member.user_id (idempotent)
217DO $$
218BEGIN
219 IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'workspace_member' AND column_name = 'user_id') THEN
220 ALTER TABLE "workspace_member" ADD COLUMN "user_id" text;
221 END IF;
222END $$;--> statement-breakpoint
223DO $$
224BEGIN
225 IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'workspace_member' AND column_name = 'user_email') THEN
226 UPDATE "workspace_member" SET "user_id" = (
227 SELECT u.id
228 FROM "user" u
229 WHERE u.email = "workspace_member"."user_email"
230 ) WHERE "user_id" IS NULL;
231 END IF;
232END $$;--> statement-breakpoint
233-- Clean up any remaining NULL values before setting NOT NULL
234DELETE FROM "workspace_member" WHERE "user_id" IS NULL;--> statement-breakpoint
235DO $$
236BEGIN
237 IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'workspace_member' AND column_name = 'user_id' AND is_nullable = 'YES') THEN
238 ALTER TABLE "workspace_member" ALTER COLUMN "user_id" SET NOT NULL;
239 END IF;
240END $$;--> statement-breakpoint
241DO $$
242BEGIN
243 IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'workspace_member' AND column_name = 'user_email') THEN
244 ALTER TABLE "workspace_member" DROP COLUMN "user_email";
245 END IF;
246END $$;--> statement-breakpoint
247-- Migrate existing passwords to account table before dropping user.password
248INSERT INTO "account" (
249 "id",
250 "account_id",
251 "provider_id",
252 "user_id",
253 "password",
254 "created_at",
255 "updated_at"
256)
257SELECT
258 'acc_' || substr(md5(random()::text || u.id), 1, 21) as id,
259 u.email as account_id,
260 'credential' as provider_id,
261 u.id as user_id,
262 u.password,
263 NOW() as created_at,
264 NOW() as updated_at
265FROM "user" u
266WHERE u.password IS NOT NULL;--> statement-breakpoint
267-- Add all foreign key constraints
268ALTER TABLE "account" ADD CONSTRAINT "account_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
269ALTER TABLE "activity" ADD CONSTRAINT "activity_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint
270ALTER TABLE "notification" ADD CONSTRAINT "notification_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint
271ALTER TABLE "session" ADD CONSTRAINT "session_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
272ALTER TABLE "task" ADD CONSTRAINT "task_assignee_id_user_id_fk" FOREIGN KEY ("assignee_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint
273ALTER TABLE "time_entry" ADD CONSTRAINT "time_entry_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint
274ALTER TABLE "workspace" ADD CONSTRAINT "workspace_owner_id_user_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
275ALTER TABLE "workspace_member" ADD CONSTRAINT "workspace_member_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
276ALTER TABLE "workspace_member" ADD CONSTRAINT "workspace_member_workspace_id_workspace_id_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspace"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
277-- Drop user password column (better-auth handles this)
278ALTER TABLE "user" DROP COLUMN "password";--> statement-breakpoint
279-- Add unique constraints
280ALTER TABLE "session" ADD CONSTRAINT "session_token_unique" UNIQUE("token");