makima/migrations/20250107000000_simplify_task_depth.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

-- Simplify task hierarchy to only 2 levels: orchestrator (depth 0) and subtasks (depth 1)
-- Subtasks cannot have their own children

-- First, check for any existing depth-2 tasks and fail if found
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM tasks WHERE depth >= 2) THEN
        RAISE EXCEPTION 'Cannot migrate: tasks with depth >= 2 exist. Please flatten or delete these tasks first.';
    END IF;
END $$;

-- Drop the old constraint
ALTER TABLE tasks DROP CONSTRAINT IF EXISTS tasks_depth_check;

-- Add new stricter constraint (max depth 1)
ALTER TABLE tasks ADD CONSTRAINT tasks_depth_check CHECK (depth >= 0 AND depth < 2);

COMMENT ON COLUMN tasks.depth IS 'Task hierarchy depth: 0=orchestrator (top-level), 1=subtask. Maximum depth is 1.';