From 8b17a175c3e7e27b789812eba4e3cd760beadb10 Mon Sep 17 00:00:00 2001 From: soryu Date: Tue, 6 Jan 2026 04:08:11 +0000 Subject: Initial Control system --- makima/migrations/20250106000000_add_task_depth.sql | 21 +++++++++++++++++++++ 1 file changed, 21 insertions(+) create mode 100644 makima/migrations/20250106000000_add_task_depth.sql (limited to 'makima/migrations/20250106000000_add_task_depth.sql') diff --git a/makima/migrations/20250106000000_add_task_depth.sql b/makima/migrations/20250106000000_add_task_depth.sql new file mode 100644 index 0000000..e0fdd03 --- /dev/null +++ b/makima/migrations/20250106000000_add_task_depth.sql @@ -0,0 +1,21 @@ +-- Add depth column for task hierarchy (0=top-level, 1=subtask, 2=sub-subtask) +-- Max depth is 2 (3 levels total) + +ALTER TABLE tasks ADD COLUMN depth INTEGER NOT NULL DEFAULT 0; +ALTER TABLE tasks ADD CONSTRAINT tasks_depth_check CHECK (depth >= 0 AND depth < 3); + +-- Backfill existing tasks based on parent chain +WITH RECURSIVE task_depth AS ( + SELECT id, parent_task_id, 0 as calculated_depth + FROM tasks + WHERE parent_task_id IS NULL + UNION ALL + SELECT t.id, t.parent_task_id, td.calculated_depth + 1 + FROM tasks t + JOIN task_depth td ON t.parent_task_id = td.id +) +UPDATE tasks SET depth = task_depth.calculated_depth +FROM task_depth WHERE tasks.id = task_depth.id; + +-- Index for depth queries +CREATE INDEX idx_tasks_depth ON tasks(depth); -- cgit v1.2.3