kaneo (minimalist kanban) fork to experiment adding a tangled integration
github.com/usekaneo/kaneo
1CREATE TABLE IF NOT EXISTS "team_member" (
2 "id" text PRIMARY KEY NOT NULL,
3 "team_id" text NOT NULL,
4 "user_id" text NOT NULL,
5 "created_at" timestamp
6);
7--> statement-breakpoint
8CREATE TABLE IF NOT EXISTS "team" (
9 "id" text PRIMARY KEY NOT NULL,
10 "name" text NOT NULL,
11 "workspace_id" text NOT NULL,
12 "created_at" timestamp NOT NULL,
13 "updated_at" timestamp
14);
15--> statement-breakpoint
16
17-- Make task_id nullable in label table
18DO $$
19BEGIN
20 IF EXISTS (
21 SELECT 1 FROM information_schema.columns
22 WHERE table_name = 'label'
23 AND column_name = 'task_id'
24 AND is_nullable = 'NO'
25 ) THEN
26 ALTER TABLE "label" ALTER COLUMN "task_id" DROP NOT NULL;
27 END IF;
28END $$;
29--> statement-breakpoint
30
31-- Add team_id column to invitation table if it doesn't exist
32DO $$
33BEGIN
34 IF NOT EXISTS (
35 SELECT 1 FROM information_schema.columns
36 WHERE table_name = 'invitation'
37 AND column_name = 'team_id'
38 ) THEN
39 ALTER TABLE "invitation" ADD COLUMN "team_id" text;
40 END IF;
41END $$;
42--> statement-breakpoint
43
44-- Add workspace_id column to label table if it doesn't exist
45DO $$
46BEGIN
47 IF NOT EXISTS (
48 SELECT 1 FROM information_schema.columns
49 WHERE table_name = 'label'
50 AND column_name = 'workspace_id'
51 ) THEN
52 -- Add column as nullable first
53 ALTER TABLE "label" ADD COLUMN "workspace_id" text;
54
55 -- Update existing labels with workspace_id from their associated tasks
56 UPDATE "label" SET "workspace_id" = (
57 SELECT p.workspace_id
58 FROM "task" t
59 JOIN "project" p ON t.project_id = p.id
60 WHERE t.id = "label"."task_id"
61 ) WHERE "task_id" IS NOT NULL AND "workspace_id" IS NULL;
62
63 -- For labels without task_id, we need to assign them to a default workspace
64 -- or remove them. Let's assign them to the first available workspace
65 UPDATE "label" SET "workspace_id" = (
66 SELECT id FROM "workspace" LIMIT 1
67 ) WHERE "workspace_id" IS NULL;
68
69 -- Remove any labels that still don't have a workspace_id
70 DELETE FROM "label" WHERE "workspace_id" IS NULL;
71
72 -- Now make the column NOT NULL
73 ALTER TABLE "label" ALTER COLUMN "workspace_id" SET NOT NULL;
74 END IF;
75END $$;
76--> statement-breakpoint
77
78-- Add active_team_id column to session table if it doesn't exist
79DO $$
80BEGIN
81 IF NOT EXISTS (
82 SELECT 1 FROM information_schema.columns
83 WHERE table_name = 'session'
84 AND column_name = 'active_team_id'
85 ) THEN
86 ALTER TABLE "session" ADD COLUMN "active_team_id" text;
87 END IF;
88END $$;
89--> statement-breakpoint
90-- Add foreign key constraints idempotently
91DO $$
92BEGIN
93 -- team_member -> team foreign key
94 IF NOT EXISTS (
95 SELECT 1 FROM information_schema.table_constraints
96 WHERE constraint_name = 'team_member_team_id_team_id_fk'
97 AND table_name = 'team_member'
98 ) THEN
99 ALTER TABLE "team_member" ADD CONSTRAINT "team_member_team_id_team_id_fk"
100 FOREIGN KEY ("team_id") REFERENCES "public"."team"("id") ON DELETE cascade ON UPDATE no action;
101 END IF;
102
103 -- team_member -> user foreign key
104 IF NOT EXISTS (
105 SELECT 1 FROM information_schema.table_constraints
106 WHERE constraint_name = 'team_member_user_id_user_id_fk'
107 AND table_name = 'team_member'
108 ) THEN
109 ALTER TABLE "team_member" ADD CONSTRAINT "team_member_user_id_user_id_fk"
110 FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;
111 END IF;
112
113 -- team -> workspace foreign key
114 IF NOT EXISTS (
115 SELECT 1 FROM information_schema.table_constraints
116 WHERE constraint_name = 'team_workspace_id_workspace_id_fk'
117 AND table_name = 'team'
118 ) THEN
119 ALTER TABLE "team" ADD CONSTRAINT "team_workspace_id_workspace_id_fk"
120 FOREIGN KEY ("workspace_id") REFERENCES "public"."workspace"("id") ON DELETE cascade ON UPDATE no action;
121 END IF;
122
123 -- label -> workspace foreign key
124 IF NOT EXISTS (
125 SELECT 1 FROM information_schema.table_constraints
126 WHERE constraint_name = 'label_workspace_id_workspace_id_fk'
127 AND table_name = 'label'
128 ) THEN
129 ALTER TABLE "label" ADD CONSTRAINT "label_workspace_id_workspace_id_fk"
130 FOREIGN KEY ("workspace_id") REFERENCES "public"."workspace"("id") ON DELETE cascade ON UPDATE cascade;
131 END IF;
132
133 -- workspace_member unique constraint
134 IF NOT EXISTS (
135 SELECT 1 FROM information_schema.table_constraints
136 WHERE constraint_name = 'workspace_member_workspace_id_user_id_unique'
137 AND table_name = 'workspace_member'
138 ) THEN
139 ALTER TABLE "workspace_member" ADD CONSTRAINT "workspace_member_workspace_id_user_id_unique"
140 UNIQUE("workspace_id","user_id");
141 END IF;
142END $$;