1 files changed, 21 insertions, 0 deletions
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);