Using parent_id for epic → story → task nesting. How agents create subtasks under their assigned issue. Recursive queries for full subtree traversal and depth limits.
Design multi-step workflows using parent/child issue relationships, implement a manager-delegates-to-workers pattern, and query task progress across a hierarchy.
The most common hierarchical pattern: a high-level issue is assigned to a manager agent, which breaks it into subtasks and assigns each subtask to a specialist worker agent.
INSERT INTO issues (company_id, title, description, assigned_agent_id, priority)
VALUES (
'company-id',
'Build launch checklist for v2.0',
'Create a complete launch readiness checklist covering engineering, marketing, and support.',
'manager-agent-id',
'high'
)
RETURNING id;
-- returns: parent_id = 'abc-123'
The manager agent's system prompt instructs it to decompose parent tasks. Its result (stored in issues.result) contains a JSON array of subtasks, which a Paperclip post-processor automatically creates as child issues:
{
"subtasks": [
{
"title": "Engineering: verify all tests pass",
"agent_role": "Backend Developer",
"priority": "critical"
},
{
"title": "Marketing: prepare launch announcement",
"agent_role": "Content Writer",
"priority": "high"
}
]
}
Enable subtask decomposition in Company Settings → Workflows → Auto-create subtasks from structured results.
In the next heartbeat tick, each worker agent (Backend Dev, Content Writer) finds its assigned subtask in the open queue and works it.
-- Parent issue with subtask completion counts
SELECT
parent.title,
parent.status AS parent_status,
COUNT(child.id) AS total_subtasks,
COUNT(child.id) FILTER (WHERE child.status = 'resolved') AS done,
COUNT(child.id) FILTER (WHERE child.status = 'open') AS open,
COUNT(child.id) FILTER (WHERE child.status = 'in_progress') AS running
FROM issues parent
LEFT JOIN issues child ON child.parent_issue_id = parent.id
WHERE parent.company_id = 'your-company-id'
AND parent.parent_issue_id IS NULL -- only top-level
GROUP BY parent.id, parent.title, parent.status
ORDER BY parent.created_at DESC;
For deeply nested tasks (grandparent → parent → child):
WITH RECURSIVE task_tree AS (
-- Base: top-level issues
SELECT id, title, status, parent_issue_id, 0 AS depth
FROM issues
WHERE company_id = 'your-company-id'
AND parent_issue_id IS NULL
UNION ALL
-- Recursive: children
SELECT i.id, i.title, i.status, i.parent_issue_id, tt.depth + 1
FROM issues i
JOIN task_tree tt ON tt.id = i.parent_issue_id
)
SELECT repeat(' ', depth) || title AS indented_title, status
FROM task_tree
ORDER BY depth, title;
Configure a trigger to resolve the parent issue automatically when all children are resolved:
CREATE OR REPLACE FUNCTION close_parent_if_all_children_done()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
-- If all siblings are resolved, resolve the parent
IF NOT EXISTS (
SELECT 1 FROM issues
WHERE parent_issue_id = NEW.parent_issue_id
AND status NOT IN ('resolved', 'cancelled')
) THEN
UPDATE issues SET status = 'resolved', resolved_at = now()
WHERE id = NEW.parent_issue_id;
END IF;
RETURN NEW;
END;
$$;
CREATE TRIGGER auto_close_parent
AFTER UPDATE OF status ON issues
FOR EACH ROW
WHEN (NEW.status = 'resolved' AND NEW.parent_issue_id IS NOT NULL)
EXECUTE FUNCTION close_parent_if_all_children_done();