From 8b17a175c3e7e27b789812eba4e3cd760beadb10 Mon Sep 17 00:00:00 2001
From: soryu <soryu@soryu.co>
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

