ADR 006: Work Item Hierarchy and Sprint Management for Project Planning
ADR-006: Work Item Hierarchy and Sprint Management for Project Planning
Document: ADR-006-work-item-hierarchy
Version: 1.0.0
Purpose: Document architectural decisions for hierarchical work item tracking (Project/Sub-Project/Epic/Feature/Task) with sprint management and completion rollup
Audience: Framework contributors, developers, project managers
Date Created: 2025-12-13
Status: PROPOSED
Related ADRs:
- ADR-005-token-accounting (database integration pattern)
- ADR-CXQ-context-query-system (query interface pattern)
Related Documents:
- commands/cxq.md
- scripts/context-db.py
- CODITECT-CORE-STANDARDS/TEMPLATES/PROJECT-PLAN-TEMPLATE.md
- CODITECT-CORE-STANDARDS/TEMPLATES/TASKLIST-WITH-CHECKBOXES-TEMPLATE.md
Context and Problem Statement
The Project Tracking Problem
CODITECT currently uses flat task lists organized by Phase, lacking hierarchical structure for:
- Work Item Hierarchy - No Epic → Feature → Task relationships
- Completion Rollup - Cannot calculate Epic/Feature % complete from child tasks
- Traceability - No link between tasks and parent requirements/features
- Sprint Planning - No time-boxed iteration management
- Cross-Submodule Tracking - 57 submodules need unified project view
Current State:
Phase (0-5) → Tasks (flat list with checkboxes)
Target State:
Project → Sub-Project → Epic → Feature → Task → Subtask
↓
Sprint (time-boxed assignment)
Business Impact:
- Cannot report Epic/Feature progress to stakeholders
- No visibility into which tasks belong to which capabilities
- Sprint planning requires manual task grouping
- Progress metrics limited to raw task counts
- Requirements traceability matrix impossible to generate
Industry Standard (SDLC Work Item Hierarchy)
Enterprise tools implement:
- Jira: Epic → Story → Subtask
- Azure DevOps: Epic → Feature → User Story → Task
- Linear: Projects → Issues with parent/child relationships
- GitHub Projects: Milestones → Issues → Tasks
Completion Rollup Pattern:
Task 100% complete when status = 'completed'
Feature % = (completed child tasks / total child tasks) × 100
Epic % = weighted average of child Feature percentages
Project % = weighted average of child Epic percentages
Requirements
Must-Have (Critical):
- Project and Sub-Project containers (maps to CODITECT submodules)
- Epic → Feature → Task hierarchy with parent_id relationships
- Sprint time-boxing with task assignment
- Automatic completion percentage rollup
- Query commands via /cxq integration
Should-Have (Important):
- Story points estimation
- Burndown/burnup data for sprints
- Label/tag system for cross-cutting concerns
- Priority ordering within containers
- Assignee tracking
Nice-to-Have:
- Gantt chart data export
- Critical path analysis
- Dependency tracking between work items
- Integration with Linear/Jira APIs
- Requirements traceability matrix generation
Decision Drivers
Technical Constraints
- Must use existing SQLite database (context.db)
- Must integrate with /cxq query interface
- Python-only implementation (stdlib preference)
- Support incremental updates (not full rebuilds)
User Experience Goals
- Simple slash commands (/epic, /feature, /task, /sprint)
- Natural language-like queries
- Automatic progress calculation (no manual updates)
- JSON export for external tools
Data Model Requirements
- Flexible hierarchy depth (can skip levels)
- Support markdown import from existing project-plan.md
- Bi-directional navigation (parent → children, child → parent)
- Temporal queries (sprint history, velocity trends)
Decision Outcome
CHOSEN: Database-Backed Work Item Hierarchy with Sprint Management
Implementation Architecture
┌─────────────────────────────────────────────────────────────────────────┐
│ Work Item Hierarchy Data Model │
└─────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────┐
│ projects │
│ ─────────────────────── │
│ id: TEXT (PK) │
│ name: TEXT │
│ description: TEXT │
│ status: TEXT │
│ created_at: TIMESTAMP │
└───────────┬─────────────┘
│ 1:N
┌───────────┴─────────────┐
│ sub_projects │
│ ─────────────────────── │
│ id: TEXT (PK) │
│ project_id: FK │
│ name: TEXT │
│ path: TEXT (submodule) │
│ status: TEXT │
└───────────┬─────────────┘
│ 1:N
┌───────────────────────────────┴───────────────────────────────┐
│ work_items │
│ ───────────────────────────────────────────────────────────── │
│ id: TEXT (PK) - Unique identifier (E001, F001.1) │
│ type: TEXT - 'epic', 'feature', 'task', 'subtask' │
│ title: TEXT - Work item title │
│ description: TEXT - Detailed description │
│ ───────────────────────────────────────────────────────────── │
│ project_id: FK - Parent project │
│ sub_project_id: FK - Parent sub-project (optional) │
│ parent_id: FK (self) - Parent work item (hierarchy) │
│ ───────────────────────────────────────────────────────────── │
│ sprint_id: FK - Assigned sprint (optional) │
│ ───────────────────────────────────────────────────────────── │
│ status: TEXT - 'backlog', 'planned', 'in_progress', │
│ 'blocked', 'review', 'completed' │
│ priority: INTEGER - 0 (lowest) to 100 (highest) │
│ ───────────────────────────────────────────────────────────── │
│ estimate_points: INTEGER - Story points (fibonacci) │
│ estimate_hours: REAL - Time estimate │
│ actual_hours: REAL - Time spent │
│ ───────────────────────────────────────────────────────────── │
│ assignee: TEXT - Who is working on this │
│ labels: TEXT (JSON) - Tags for categorization │
│ ───────────────────────────────────────────────────────────── │
│ created_at: TIMESTAMP - When created │
│ started_at: TIMESTAMP - When work began │
│ completed_at: TIMESTAMP - When marked complete │
│ due_date: TEXT - Target completion date │
└───────────────────────────────────────────────────────────────┘
│ N:1
┌───────────┴─────────────┐
│ sprints │
│ ─────────────────────── │
│ id: TEXT (PK) │
│ project_id: FK │
│ name: TEXT │
│ goal: TEXT │
│ start_date: DATE │
│ end_date: DATE │
│ status: TEXT │
└─────────────────────────┘
Hierarchy Example:
─────────────────
Project: CODITECT Platform (P001)
├── Sub-Project: coditect-core (SP001)
│ ├── Epic: Token Accounting System (E001)
│ │ ├── Feature: Usage Extraction (F001.1)
│ │ │ ├── Task: Extract token fields (T001) [Sprint-23] ✓
│ │ │ ├── Task: Add to JSONL format (T002) [Sprint-23] ✓
│ │ │ └── Task: Handle missing data (T003) [Sprint-23] ✓
│ │ ├── Feature: Query Interface (F001.2)
│ │ │ ├── Task: Add --tokens flag (T004) [Sprint-24] ✓
│ │ │ ├── Task: Add --cost flag (T005) [Sprint-24] ✓
│ │ │ └── Task: Add date filtering (T006) [Sprint-24] □
│ │ └── Feature: Cache Analytics (F001.3) [Not Started]
│ │ ├── Task: Calculate hit rate (T007) □
│ │ └── Task: Estimate savings (T008) □
│ └── Epic: Work Item Hierarchy (E002) [This ADR]
└── Sub-Project: coditect-cloud-backend (SP002)
└── Epic: User Authentication (E003)
└── ...
Completion Rollup:
──────────────────
F001.1: 3/3 tasks = 100%
F001.2: 2/3 tasks = 67%
F001.3: 0/2 tasks = 0%
E001: (100% + 67% + 0%) / 3 = 56% (or weighted by points)
Core Components
1. Database Schema
-- ============================================================
-- PROJECTS - Top-level container
-- ============================================================
CREATE TABLE IF NOT EXISTS projects (
id TEXT PRIMARY KEY, -- P001, P002, etc.
name TEXT NOT NULL, -- "CODITECT Platform"
description TEXT,
status TEXT DEFAULT 'active', -- active, on_hold, completed, archived
owner TEXT, -- Project owner/lead
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);
-- ============================================================
-- SUB-PROJECTS - Maps to submodules in CODITECT
-- ============================================================
CREATE TABLE IF NOT EXISTS sub_projects (
id TEXT PRIMARY KEY, -- SP001, SP002, etc.
project_id TEXT NOT NULL,
name TEXT NOT NULL, -- "coditect-core"
path TEXT, -- "submodules/core/coditect-core"
description TEXT,
status TEXT DEFAULT 'active',
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_sub_projects_project ON sub_projects(project_id);
-- ============================================================
-- SPRINTS - Time-boxed iterations
-- ============================================================
CREATE TABLE IF NOT EXISTS sprints (
id TEXT PRIMARY KEY, -- Sprint-23, Sprint-24, etc.
project_id TEXT NOT NULL,
name TEXT NOT NULL, -- "Sprint 23" or "2025-W50"
goal TEXT, -- Sprint goal statement
start_date TEXT NOT NULL, -- YYYY-MM-DD
end_date TEXT NOT NULL, -- YYYY-MM-DD
status TEXT DEFAULT 'planned', -- planned, active, completed, cancelled
velocity_planned INTEGER, -- Planned story points
velocity_actual INTEGER, -- Actual story points completed
retrospective TEXT, -- Sprint retrospective notes
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_sprints_project ON sprints(project_id);
CREATE INDEX IF NOT EXISTS idx_sprints_status ON sprints(status);
CREATE INDEX IF NOT EXISTS idx_sprints_dates ON sprints(start_date, end_date);
-- ============================================================
-- WORK_ITEMS - Unified table for Epic, Feature, Task, Subtask
-- ============================================================
CREATE TABLE IF NOT EXISTS work_items (
id TEXT PRIMARY KEY, -- E001, F001.1, T001, etc.
type TEXT NOT NULL CHECK (type IN ('epic', 'feature', 'task', 'subtask')),
title TEXT NOT NULL,
description TEXT,
acceptance_criteria TEXT, -- Definition of done
-- Hierarchy relationships
project_id TEXT,
sub_project_id TEXT,
parent_id TEXT, -- Self-referential for hierarchy
-- Sprint assignment (for tasks/subtasks)
sprint_id TEXT,
-- Status tracking
status TEXT DEFAULT 'backlog' CHECK (status IN (
'backlog', -- Not yet planned
'planned', -- Planned for sprint
'in_progress', -- Actively being worked
'blocked', -- Blocked by dependency
'review', -- In review/testing
'completed', -- Done
'cancelled' -- Cancelled/won't do
)),
-- Priority and ordering
priority INTEGER DEFAULT 50, -- 0 (lowest) to 100 (highest)
sort_order INTEGER DEFAULT 0, -- Manual ordering within parent
-- Estimation
estimate_points INTEGER, -- Story points (1,2,3,5,8,13,21)
estimate_hours REAL, -- Time estimate in hours
actual_hours REAL, -- Actual time spent
-- Assignment and categorization
assignee TEXT, -- Who is responsible
labels TEXT, -- JSON array: ["bug", "enhancement"]
-- Dependencies (JSON array of work_item IDs)
depends_on TEXT, -- ["T001", "T002"]
blocks TEXT, -- ["T005", "T006"]
-- Timestamps
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
started_at TEXT, -- When status changed to in_progress
completed_at TEXT, -- When status changed to completed
due_date TEXT, -- Target completion date
-- Source tracking (for imports)
source_file TEXT, -- Original markdown file
source_line INTEGER, -- Line number in source
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL,
FOREIGN KEY (sub_project_id) REFERENCES sub_projects(id) ON DELETE SET NULL,
FOREIGN KEY (parent_id) REFERENCES work_items(id) ON DELETE CASCADE,
FOREIGN KEY (sprint_id) REFERENCES sprints(id) ON DELETE SET NULL
);
-- Indexes for common query patterns
CREATE INDEX IF NOT EXISTS idx_work_items_type ON work_items(type);
CREATE INDEX IF NOT EXISTS idx_work_items_parent ON work_items(parent_id);
CREATE INDEX IF NOT EXISTS idx_work_items_project ON work_items(project_id);
CREATE INDEX IF NOT EXISTS idx_work_items_sub_project ON work_items(sub_project_id);
CREATE INDEX IF NOT EXISTS idx_work_items_sprint ON work_items(sprint_id);
CREATE INDEX IF NOT EXISTS idx_work_items_status ON work_items(status);
CREATE INDEX IF NOT EXISTS idx_work_items_assignee ON work_items(assignee);
CREATE INDEX IF NOT EXISTS idx_work_items_due_date ON work_items(due_date);
-- ============================================================
-- VIEWS - Pre-computed aggregations for performance
-- ============================================================
-- Direct children count and completion for any work item
CREATE VIEW IF NOT EXISTS work_item_children_stats AS
SELECT
parent_id,
COUNT(*) as child_count,
SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed_count,
SUM(CASE WHEN status = 'in_progress' THEN 1 ELSE 0 END) as in_progress_count,
SUM(CASE WHEN status = 'blocked' THEN 1 ELSE 0 END) as blocked_count,
SUM(COALESCE(estimate_points, 0)) as total_points,
SUM(CASE WHEN status = 'completed' THEN COALESCE(estimate_points, 0) ELSE 0 END) as completed_points,
SUM(COALESCE(estimate_hours, 0)) as total_hours,
SUM(COALESCE(actual_hours, 0)) as actual_hours
FROM work_items
WHERE parent_id IS NOT NULL
GROUP BY parent_id;
-- Work item with completion percentage
CREATE VIEW IF NOT EXISTS work_item_progress AS
SELECT
wi.id,
wi.type,
wi.title,
wi.status,
wi.parent_id,
wi.project_id,
wi.sub_project_id,
wi.sprint_id,
wi.estimate_points,
wi.estimate_hours,
wi.assignee,
wi.due_date,
COALESCE(cs.child_count, 0) as child_count,
COALESCE(cs.completed_count, 0) as completed_children,
COALESCE(cs.in_progress_count, 0) as in_progress_children,
COALESCE(cs.blocked_count, 0) as blocked_children,
COALESCE(cs.total_points, 0) as total_child_points,
COALESCE(cs.completed_points, 0) as completed_child_points,
CASE
WHEN cs.child_count IS NULL OR cs.child_count = 0 THEN
CASE WHEN wi.status = 'completed' THEN 100.0 ELSE 0.0 END
ELSE
ROUND(100.0 * cs.completed_count / cs.child_count, 1)
END as percent_complete,
CASE
WHEN cs.total_points IS NULL OR cs.total_points = 0 THEN
CASE WHEN wi.status = 'completed' THEN 100.0 ELSE 0.0 END
ELSE
ROUND(100.0 * cs.completed_points / cs.total_points, 1)
END as percent_complete_weighted
FROM work_items wi
LEFT JOIN work_item_children_stats cs ON wi.id = cs.parent_id;
-- Epic progress summary
CREATE VIEW IF NOT EXISTS epic_progress AS
SELECT
e.id as epic_id,
e.title as epic_title,
e.project_id,
e.sub_project_id,
e.status as epic_status,
COUNT(DISTINCT f.id) as feature_count,
COUNT(t.id) as task_count,
SUM(CASE WHEN t.status = 'completed' THEN 1 ELSE 0 END) as completed_tasks,
SUM(CASE WHEN t.status = 'in_progress' THEN 1 ELSE 0 END) as in_progress_tasks,
SUM(CASE WHEN t.status = 'blocked' THEN 1 ELSE 0 END) as blocked_tasks,
SUM(COALESCE(t.estimate_points, 0)) as total_points,
SUM(CASE WHEN t.status = 'completed' THEN COALESCE(t.estimate_points, 0) ELSE 0 END) as completed_points,
CASE
WHEN COUNT(t.id) = 0 THEN 0.0
ELSE ROUND(100.0 * SUM(CASE WHEN t.status = 'completed' THEN 1 ELSE 0 END) / COUNT(t.id), 1)
END as percent_complete
FROM work_items e
LEFT JOIN work_items f ON f.parent_id = e.id AND f.type = 'feature'
LEFT JOIN work_items t ON (t.parent_id = f.id OR t.parent_id = e.id) AND t.type IN ('task', 'subtask')
WHERE e.type = 'epic'
GROUP BY e.id;
-- Sprint burndown data
CREATE VIEW IF NOT EXISTS sprint_burndown AS
SELECT
s.id as sprint_id,
s.name as sprint_name,
s.start_date,
s.end_date,
s.status as sprint_status,
s.goal,
COUNT(wi.id) as total_items,
SUM(CASE WHEN wi.status = 'completed' THEN 1 ELSE 0 END) as completed_items,
SUM(CASE WHEN wi.status = 'in_progress' THEN 1 ELSE 0 END) as in_progress_items,
SUM(CASE WHEN wi.status = 'blocked' THEN 1 ELSE 0 END) as blocked_items,
SUM(COALESCE(wi.estimate_points, 0)) as total_points,
SUM(CASE WHEN wi.status = 'completed' THEN COALESCE(wi.estimate_points, 0) ELSE 0 END) as completed_points,
SUM(COALESCE(wi.estimate_hours, 0)) as total_hours,
SUM(CASE WHEN wi.status = 'completed' THEN COALESCE(wi.estimate_hours, 0) ELSE 0 END) as completed_hours,
CASE
WHEN COUNT(wi.id) = 0 THEN 0.0
ELSE ROUND(100.0 * SUM(CASE WHEN wi.status = 'completed' THEN 1 ELSE 0 END) / COUNT(wi.id), 1)
END as percent_complete
FROM sprints s
LEFT JOIN work_items wi ON wi.sprint_id = s.id
GROUP BY s.id;
-- Project overview
CREATE VIEW IF NOT EXISTS project_progress AS
SELECT
p.id as project_id,
p.name as project_name,
p.status as project_status,
COUNT(DISTINCT sp.id) as sub_project_count,
COUNT(DISTINCT e.id) as epic_count,
COUNT(DISTINCT f.id) as feature_count,
COUNT(DISTINCT t.id) as task_count,
SUM(CASE WHEN t.status = 'completed' THEN 1 ELSE 0 END) as completed_tasks,
SUM(COALESCE(t.estimate_points, 0)) as total_points,
SUM(CASE WHEN t.status = 'completed' THEN COALESCE(t.estimate_points, 0) ELSE 0 END) as completed_points,
CASE
WHEN COUNT(DISTINCT t.id) = 0 THEN 0.0
ELSE ROUND(100.0 * SUM(CASE WHEN t.status = 'completed' THEN 1 ELSE 0 END) / COUNT(DISTINCT t.id), 1)
END as percent_complete
FROM projects p
LEFT JOIN sub_projects sp ON sp.project_id = p.id
LEFT JOIN work_items e ON e.project_id = p.id AND e.type = 'epic'
LEFT JOIN work_items f ON f.parent_id = e.id AND f.type = 'feature'
LEFT JOIN work_items t ON (t.parent_id = f.id OR t.parent_id = e.id) AND t.type IN ('task', 'subtask')
GROUP BY p.id;
-- Sub-project progress
CREATE VIEW IF NOT EXISTS sub_project_progress AS
SELECT
sp.id as sub_project_id,
sp.name as sub_project_name,
sp.path,
sp.project_id,
COUNT(DISTINCT e.id) as epic_count,
COUNT(DISTINCT f.id) as feature_count,
COUNT(t.id) as task_count,
SUM(CASE WHEN t.status = 'completed' THEN 1 ELSE 0 END) as completed_tasks,
CASE
WHEN COUNT(t.id) = 0 THEN 0.0
ELSE ROUND(100.0 * SUM(CASE WHEN t.status = 'completed' THEN 1 ELSE 0 END) / COUNT(t.id), 1)
END as percent_complete
FROM sub_projects sp
LEFT JOIN work_items e ON e.sub_project_id = sp.id AND e.type = 'epic'
LEFT JOIN work_items f ON f.parent_id = e.id AND f.type = 'feature'
LEFT JOIN work_items t ON (t.parent_id = f.id OR t.parent_id = e.id) AND t.type IN ('task', 'subtask')
GROUP BY sp.id;
2. Command Interface
New Slash Commands:
# ============================================================
# PROJECT MANAGEMENT
# ============================================================
/project create "CODITECT Platform" --id P001
/project list
/project status P001
/project archive P001
# ============================================================
# SUB-PROJECT MANAGEMENT (maps to submodules)
# ============================================================
/subproject create "coditect-core" --project P001 --path submodules/core/coditect-core
/subproject list --project P001
/subproject sync # Auto-discover from git submodules
# ============================================================
# EPIC MANAGEMENT
# ============================================================
/epic create "Token Accounting System" --id E001 --project P001 --subproject SP001
/epic list --project P001
/epic progress E001 # Show completion %
/epic details E001 --tree # Show full hierarchy
# ============================================================
# FEATURE MANAGEMENT
# ============================================================
/feature create "Usage Extraction" --id F001.1 --epic E001
/feature list --epic E001
/feature progress F001.1
# ============================================================
# TASK MANAGEMENT
# ============================================================
/task create "Extract token fields from JSONL" --id T001 --feature F001.1
/task create "Quick bug fix" --epic E001 # Task can be direct child of Epic
/task assign T001 --sprint Sprint-23
/task start T001 # Mark in_progress
/task complete T001 # Mark completed
/task block T001 --reason "Waiting for API access"
/task unblock T001
/task list --sprint Sprint-23
/task list --assignee "hal"
/task list --status in_progress
# ============================================================
# SPRINT MANAGEMENT
# ============================================================
/sprint create "Sprint 23" --project P001 --start 2025-12-16 --end 2025-12-27
/sprint list --project P001
/sprint active # Show current sprint
/sprint burndown Sprint-23 # Show burndown data
/sprint plan Sprint-24 # Interactive planning mode
/sprint close Sprint-23 # Complete sprint with retrospective
# ============================================================
# QUERIES (via /cxq extension)
# ============================================================
/cxq --epic-progress E001 # Epic completion status
/cxq --feature-progress F001.1 # Feature completion status
/cxq --sprint-status Sprint-23 # Sprint burndown
/cxq --project-status P001 # Project overview
/cxq --velocity --sprints 5 # Velocity trend (last 5 sprints)
/cxq --blocked # All blocked items
/cxq --overdue # Past due items
/cxq --unassigned # Unassigned tasks
/cxq --my-tasks --status in_progress # Current user's active tasks
# ============================================================
# IMPORT/EXPORT
# ============================================================
/workitems import project-plan.md # Parse existing markdown
/workitems export --format json # Export to JSON
/workitems export --format csv # Export to CSV
/workitems export --format markdown # Export to markdown
3. ID Convention
Hierarchical ID Pattern:
P001 # Project
SP001 # Sub-Project
E001 # Epic
F001.1, F001.2 # Features under E001
T001, T002 # Tasks
ST001.1, ST001.2 # Subtasks under T001
Sprint-23, Sprint-24 # Sprints
Alternative: Descriptive IDs:
PROJ-CODITECT # Project
SUB-CORE # Sub-Project
EPIC-TOKEN-ACCOUNTING # Epic
FEAT-USAGE-EXTRACTION # Feature
TASK-EXTRACT-FIELDS # Task
Recommendation: Use short numeric IDs (E001, F001.1) for system, allow aliases for human reference.
4. Markdown Import Parser
Parse existing project-plan.md structure:
def parse_project_plan(filepath: str) -> List[Dict]:
"""
Parse project-plan.md into work items.
Expected format:
## Phase 1: Foundation
### Epic: E001 - Token Accounting
#### Feature: F001.1 - Usage Extraction
- [ ] T001: Extract token fields
- [x] T002: Add to JSONL format
"""
work_items = []
current_phase = None
current_epic = None
current_feature = None
with open(filepath) as f:
for line_num, line in enumerate(f, 1):
# Parse headings
if line.startswith('## Phase'):
current_phase = extract_phase(line)
elif line.startswith('### Epic:'):
current_epic = parse_epic(line, current_phase)
work_items.append(current_epic)
elif line.startswith('#### Feature:'):
current_feature = parse_feature(line, current_epic)
work_items.append(current_feature)
elif re.match(r'^- \[([ x])\]', line):
task = parse_task(line, current_feature or current_epic, line_num)
work_items.append(task)
return work_items
5. Completion Rollup Algorithm
def calculate_completion(work_item_id: str, db: Connection) -> Dict[str, float]:
"""
Calculate completion percentage for a work item.
Returns:
{
'percent_by_count': 66.7, # Tasks completed / total tasks
'percent_by_points': 75.0, # Points completed / total points
'child_stats': {...}
}
"""
# Get all descendant tasks (recursive)
descendants = get_all_descendants(work_item_id, db)
tasks = [d for d in descendants if d['type'] in ('task', 'subtask')]
if not tasks:
# Leaf node - return own status
item = get_work_item(work_item_id, db)
return {
'percent_by_count': 100.0 if item['status'] == 'completed' else 0.0,
'percent_by_points': 100.0 if item['status'] == 'completed' else 0.0,
}
total_count = len(tasks)
completed_count = sum(1 for t in tasks if t['status'] == 'completed')
total_points = sum(t.get('estimate_points', 0) or 0 for t in tasks)
completed_points = sum(
t.get('estimate_points', 0) or 0
for t in tasks
if t['status'] == 'completed'
)
return {
'percent_by_count': round(100 * completed_count / total_count, 1),
'percent_by_points': round(100 * completed_points / total_points, 1) if total_points > 0 else 0.0,
'total_tasks': total_count,
'completed_tasks': completed_count,
'total_points': total_points,
'completed_points': completed_points,
}
def get_all_descendants(work_item_id: str, db: Connection) -> List[Dict]:
"""Recursively get all descendants of a work item."""
query = """
WITH RECURSIVE descendants AS (
SELECT * FROM work_items WHERE parent_id = ?
UNION ALL
SELECT wi.* FROM work_items wi
JOIN descendants d ON wi.parent_id = d.id
)
SELECT * FROM descendants
"""
return db.execute(query, (work_item_id,)).fetchall()
Example Output
$ /epic progress E001
Epic: E001 - Token Accounting System
════════════════════════════════════════════════════════════════════
Progress: ████████████░░░░░░░░ 56% (by tasks)
█████████████░░░░░░░ 62% (by points)
Features:
┌──────────┬────────────────────────┬────────┬───────┬──────────┐
│ ID │ Title │ Tasks │ Done │ Progress │
├──────────┼────────────────────────┼────────┼───────┼──────────┤
│ F001.1 │ Usage Extraction │ 3 │ 3 │ 100% ✓ │
│ F001.2 │ Query Interface │ 3 │ 2 │ 67% │
│ F001.3 │ Cache Analytics │ 2 │ 0 │ 0% │
└──────────┴────────────────────────┴────────┴───────┴──────────┘
Statistics:
Total Tasks: 8
Completed: 5
In Progress: 1
Blocked: 0
Not Started: 2
Story Points: 21 total, 13 completed
Hours Estimated: 24h
Hours Actual: 18h (on track)
Sprint Assignment:
Sprint-23: 3 tasks (all completed)
Sprint-24: 3 tasks (2 completed, 1 in progress)
Backlog: 2 tasks (not yet planned)
════════════════════════════════════════════════════════════════════
$ /sprint burndown Sprint-24
Sprint: Sprint-24 (2025-12-16 to 2025-12-27)
════════════════════════════════════════════════════════════════════
Goal: Complete Token Accounting query interface
Progress: ████████████████░░░░ 80% complete
Days Remaining: 5
Points Tasks
Planned: 13 5
Completed: 10 4
Remaining: 3 1
Burndown:
Day 1 ████████████████████████████████ 13 pts remaining
Day 2 ██████████████████████████████ 12 pts remaining
Day 3 ████████████████████████████ 11 pts remaining
Day 4 ██████████████████████ 8 pts remaining
Day 5 ████████████ 5 pts remaining
Day 6 ██████ 3 pts remaining ← Today
...
Day 12 [target] 0 pts remaining
Status: ON TRACK (1 point ahead of ideal burndown)
════════════════════════════════════════════════════════════════════
Consequences
Positive Consequences
Project Visibility:
- Clear Epic/Feature/Task hierarchy
- Automatic completion rollup
- Progress visible at any level
- Stakeholder-friendly reporting
Sprint Management:
- Time-boxed planning
- Burndown/burnup tracking
- Velocity measurement
- Sprint retrospective data
Traceability:
- Requirements → Epic → Feature → Task chain
- Audit trail for completion
- Source file linking for imports
Integration:
- Extends existing /cxq infrastructure
- SQLite storage (no new dependencies)
- JSON export for external tools
- Markdown import from existing files
Negative Consequences
Learning Curve:
- New commands to learn (/epic, /feature, /task, /sprint)
- Hierarchy management overhead
- ID convention discipline required
Migration Effort:
- Existing project-plan.md files need import
- Manual categorization of existing tasks
- Historical data may lack structure
Maintenance:
- Schema migrations for updates
- View rebuilds on schema changes
- Performance tuning for large projects
Risk Mitigation
Adoption Risk:
- Provide migration script for existing projects
- Default to flat task lists if hierarchy not needed
- Gradual rollout with feature flags
Performance Risk:
- Recursive queries use CTE (efficient in SQLite)
- Pre-computed views for common aggregations
- Indexes on all foreign keys
Complexity Risk:
- Start with minimal commands (epic, task, sprint)
- Add feature/subtask later if needed
- Keep markdown as source of truth option
Implementation Plan
Phase 1: Schema and Models (Priority 1)
- Create database tables (projects, sub_projects, sprints, work_items)
- Create indexes and views
- Add schema migration to context-db.py
- Write unit tests for schema
Phase 2: Core Commands (Priority 1)
- Implement /project command
- Implement /epic command
- Implement /task command
- Implement /sprint command
- Add completion rollup calculation
Phase 3: Query Integration (Priority 1)
- Extend /cxq with --epic-progress
- Add --sprint-status
- Add --project-status
- Implement --velocity calculation
Phase 4: Import/Export (Priority 2)
- Markdown parser for project-plan.md import
- JSON export
- CSV export
- Markdown export (round-trip)
Phase 5: Advanced Features (Priority 3)
- /feature command (intermediate level)
- /subtask command (leaf level)
- Dependency tracking
- Linear/Jira integration
Validation Criteria
Functional:
- Can create Project → Sub-Project → Epic → Feature → Task hierarchy
- Completion % rolls up correctly through hierarchy
- Sprint burndown calculates correctly
- Import from existing project-plan.md works
- Export to JSON/CSV/Markdown works
Performance:
- Queries complete in <1s for 1000+ work items
- Recursive rollup efficient for deep hierarchies
- Views update automatically on data changes
Usability:
- Commands are intuitive and well-documented
- Output is human-readable with progress bars
- JSON output is complete for automation
Related Systems
Upstream (Data Sources)
- project-plan.md files (markdown import)
- tasklist-with-checkboxes.md files
- Linear/Jira (future API integration)
Downstream (Consumers)
- /cxq query interface
- Progress dashboards
- Sprint planning tools
- External project management systems
Integration Points
- Extends context.db database
- Adds new slash commands
- Compatible with existing /cxq commands
Alternatives Considered
Alternative 1: Pure Markdown (No Database)
Pros: No schema migration, human-editable, version controlled Cons: No computed rollup, slow queries, no API access Rejected: Insufficient for real-time progress tracking
Alternative 2: External Tool Integration Only (Linear/Jira)
Pros: Proven tools, team collaboration features Cons: Cloud dependency, cost, data sovereignty Rejected: CODITECT needs offline-first, self-contained solution
Alternative 3: Flat Task Table with Tags
Pros: Simpler schema, flexible categorization Cons: No true hierarchy, rollup requires tag conventions Rejected: Does not support natural Epic → Feature → Task relationships
References
Documentation
Related ADRs
- ADR-005: Token Accounting - Database integration pattern
- ADR-CXQ: Context Query System - Query interface pattern
External References
- Jira Work Item Hierarchy: https://support.atlassian.com/jira-software-cloud/docs/what-is-an-epic/
- Azure DevOps Work Item Types: https://docs.microsoft.com/en-us/azure/devops/boards/work-items/about-work-items
- Scrum Guide - Sprint: https://scrumguides.org/scrum-guide.html#the-sprint
Status: PROPOSED Decision Date: 2025-12-13 Implementation Status: NOT STARTED Maintainer: CODITECT Core Team Review Date: 2025-12-20 (initial review)
Document Version: 1.0.0 Last Updated: 2025-12-13 Author: CODITECT Architecture Team Copyright: 2025 AZ1.AI INC. All rights reserved.