You've already forked agentic-coding-workflow
35 lines
1.7 KiB
SQL
35 lines
1.7 KiB
SQL
-- Task decomposition + dependency graph (autonomy roadmap §10).
|
|
-- Additive only (follows the existing convention where 0003/0015 patched issues).
|
|
|
|
-- ============ issues: parent_id + priority ============
|
|
-- parent_id: self-FK for subtask hierarchy. ON DELETE SET NULL so deleting a
|
|
-- parent orphans (not cascades) its subtasks. CHECK forbids self-parent.
|
|
ALTER TABLE issues
|
|
ADD COLUMN parent_id UUID REFERENCES issues(id) ON DELETE SET NULL;
|
|
ALTER TABLE issues
|
|
ADD CONSTRAINT issues_parent_not_self CHECK (parent_id IS NULL OR parent_id <> id);
|
|
|
|
-- priority: higher = scheduled sooner. 0..3 (0 default = normal).
|
|
ALTER TABLE issues
|
|
ADD COLUMN priority INT NOT NULL DEFAULT 0;
|
|
ALTER TABLE issues
|
|
ADD CONSTRAINT issues_priority_check CHECK (priority BETWEEN 0 AND 3);
|
|
|
|
CREATE INDEX idx_issues_parent ON issues(parent_id) WHERE parent_id IS NOT NULL;
|
|
|
|
-- ============ issue_dependencies ============
|
|
-- Semantics: blocked_id is blocked-by blocker_id; blocked_id becomes ready only
|
|
-- when blocker_id.status='closed'. project_id denormalized for scope/cycle scoping.
|
|
CREATE TABLE issue_dependencies (
|
|
id UUID PRIMARY KEY,
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
blocked_id UUID NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
|
|
blocker_id UUID NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
|
|
created_by UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (blocked_id, blocker_id),
|
|
CONSTRAINT issue_deps_not_self CHECK (blocked_id <> blocker_id)
|
|
);
|
|
CREATE INDEX idx_issue_deps_blocked ON issue_dependencies(blocked_id);
|
|
CREATE INDEX idx_issue_deps_blocker ON issue_dependencies(blocker_id);
|