kaneo (minimalist kanban) fork to experiment adding a tangled integration
github.com/usekaneo/kaneo
1ALTER TABLE "activity" ADD COLUMN "updated_at" timestamp DEFAULT now() NOT NULL;--> statement-breakpoint
2ALTER TABLE "label" ADD COLUMN "updated_at" timestamp DEFAULT now() NOT NULL;--> statement-breakpoint
3CREATE INDEX "activity_task_id_idx" ON "activity" USING btree ("task_id");--> statement-breakpoint
4CREATE INDEX "label_task_id_idx" ON "label" USING btree ("task_id");--> statement-breakpoint
5CREATE INDEX "label_workspace_id_idx" ON "label" USING btree ("workspace_id");--> statement-breakpoint
6CREATE INDEX "task_projectId_idx" ON "task" USING btree ("project_id");--> statement-breakpoint
7WITH numbered AS (
8 SELECT
9 id,
10 project_id,
11 number,
12 ROW_NUMBER() OVER (
13 PARTITION BY project_id, number
14 ORDER BY created_at ASC, id ASC
15 ) AS dup_rank
16 FROM task
17),
18to_reassign AS (
19 SELECT n.id, n.project_id
20 FROM numbered n
21 WHERE n.dup_rank > 1
22),
23max_per_project AS (
24 SELECT project_id, MAX(number) AS max_num
25 FROM task
26 GROUP BY project_id
27),
28new_numbers AS (
29 SELECT
30 tr.id,
31 (mpp.max_num + ROW_NUMBER() OVER (PARTITION BY tr.project_id ORDER BY tr.id))::integer AS new_number
32 FROM to_reassign tr
33 JOIN max_per_project mpp ON mpp.project_id = tr.project_id
34)
35UPDATE task t
36SET number = nn.new_number
37FROM new_numbers nn
38WHERE t.id = nn.id;--> statement-breakpoint
39ALTER TABLE "task" ADD CONSTRAINT "task_project_number_unique" UNIQUE("project_id","number");