Dashboard v2.0 Quick Reference
One-page cheat sheet for developers using the v2.0 schema
Migration Commands​
# Check status
python database.py
# Preview migration (dry run)
python migrate_to_v2.py
# Execute migration
python migrate_to_v2.py --execute
# Rollback if needed
python migrate_to_v2.py --rollback
New v2.0 Functions​
Get Blocked Tasks​
from database import get_blocked_tasks
# All blocked tasks across all projects
blockers = get_blocked_tasks()
# Blocked tasks for specific project
blockers = get_blocked_tasks(project_id=1)
# Returns: [{'id', 'title', 'blocked_reason', 'days_blocked', 'project_name'}]
Get Project Progress (Checkbox-Based)​
from database import get_project_progress
progress = get_project_progress(project_id=1)
# Returns:
# {
# 'total_tasks': 220,
# 'checked_tasks': 42,
# 'completion_pct': 19.1,
# 'tasks_by_status': {'pending': 150, 'in_progress': 28, ...},
# 'tasks_by_complexity': {'S': 50, 'M': 100, 'L': 50, 'XL': 20}
# }
Update Task Checkbox (Source of Truth)​
from database import update_task_checked
# Mark as checked
success = update_task_checked(task_id=42, checked=True)
# Uncheck
success = update_task_checked(task_id=42, checked=False)
# Returns: True if successful, False if task not found
Get Task Links (Commits and Sessions)​
from database import get_task_links
links = get_task_links(task_id=42)
# Returns:
# {
# 'commits': [
# {'sha', 'message', 'author', 'timestamp', 'confidence', 'link_type', 'evidence'}
# ],
# 'sessions': [
# {'id', 'summary', 'message_count', 'started_at', 'confidence', 'link_type', 'evidence'}
# ]
# }
Get Recent Activity​
from database import get_recent_activity
# Last 5 activities across all projects
activity = get_recent_activity(limit=5)
# Last 10 activities for specific project
activity = get_recent_activity(project_id=1, limit=10)
# Returns: [{'type', 'title', 'timestamp', 'project_name', ...}]
# Types: 'task_completed', 'commit', 'session', 'status_change', 'blocked'
Create Task-Commit Link​
from database import create_task_commit_link
link_id = create_task_commit_link(
task_id=42,
commit_sha='abc123def456',
confidence=0.95, # 0.0 to 1.0
link_type='explicit', # 'explicit' or 'inferred'
evidence='Commit message: "Fix TASK-42: Add authentication"'
)
# Returns: UUID string if created, None if link already exists
Create Task-Session Link​
from database import create_task_session_link, generate_uuid
session_id = generate_uuid() # Or from Claude Code session tracking
link_id = create_task_session_link(
task_id=42,
session_id=session_id,
confidence=1.0,
link_type='explicit',
evidence='Session focused on implementing TASK-42'
)
# Returns: UUID string if created, None if link already exists
API Endpoint Examples​
Get Blocked Tasks​
GET /api/v1/dashboard/blockers
GET /api/v1/dashboard/blockers?project_id=1
# Response:
[
{
"task_id": 42,
"task_title": "Integrate payment gateway",
"project_name": "E-commerce Platform",
"blocked_reason": "Waiting for API credentials from vendor",
"days_blocked": 3
}
]
Get Project Progress​
GET /api/v1/projects/1/progress
# Response:
{
"total_tasks": 220,
"checked_tasks": 42,
"completion_pct": 19.1,
"tasks_by_status": {
"pending": 150,
"in_progress": 28,
"completed": 42
},
"tasks_by_complexity": {
"S": 50,
"M": 100,
"L": 50,
"XL": 20
}
}
Update Task Checkbox​
PATCH /api/v1/tasks/42
Content-Type: application/json
{
"checked": true
}
# Response:
{
"id": 42,
"title": "Implement user authentication",
"checked": true,
"status": "in_progress",
"updated_at": "2025-11-27T16:30:00Z"
}
Get Task Evidence (Links)​
GET /api/v1/tasks/42/evidence
# Response:
{
"commits": [
{
"sha": "abc123",
"message": "feat: Add JWT authentication",
"author": "John Doe",
"timestamp": "2025-11-27T14:30:00Z",
"confidence": 0.95,
"link_type": "explicit",
"evidence": "Commit message references TASK-42"
}
],
"sessions": [
{
"id": "uuid-here",
"summary": "Implemented user authentication with JWT",
"message_count": 42,
"started_at": "2025-11-27T10:00:00Z",
"confidence": 1.0,
"link_type": "explicit",
"evidence": "Session worked on TASK-42"
}
]
}
Get Recent Activity​
GET /api/v1/dashboard/activity?limit=5
# Response:
[
{
"type": "task_completed",
"title": "Implement user authentication",
"task_id": 42,
"project_name": "Platform Backend",
"timestamp": "2025-11-27T16:30:00Z"
},
{
"type": "commit",
"title": "feat: Add JWT authentication",
"sha": "abc123",
"author": "John Doe",
"project_name": "Platform Backend",
"timestamp": "2025-11-27T14:30:00Z"
}
]
Database Schema Quick Look​
Modified: tasks table​
-- New columns added
checked BOOLEAN DEFAULT FALSE -- SOURCE OF TRUTH for completion
blocked_reason TEXT -- Why task is blocked
complexity TEXT CHECK(complexity IN ('S', 'M', 'L', 'XL')) -- Sizing
updated_at TEXT -- Last modification timestamp
New: git_repos table​
CREATE TABLE git_repos (
id TEXT PRIMARY KEY,
project_id INTEGER NOT NULL,
url TEXT NOT NULL,
default_branch TEXT DEFAULT 'main',
current_commit TEXT,
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);
New: git_commits table​
CREATE TABLE git_commits (
sha TEXT PRIMARY KEY,
repo_id TEXT NOT NULL,
author TEXT NOT NULL,
message TEXT NOT NULL,
timestamp TEXT NOT NULL,
additions INTEGER DEFAULT 0,
deletions INTEGER DEFAULT 0,
FOREIGN KEY (repo_id) REFERENCES git_repos(id) ON DELETE CASCADE
);
New: llm_sessions table​
CREATE TABLE llm_sessions (
id TEXT PRIMARY KEY,
project_id INTEGER NOT NULL,
started_at TEXT DEFAULT CURRENT_TIMESTAMP,
ended_at TEXT,
message_count INTEGER DEFAULT 0,
summary TEXT,
model TEXT,
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);
New: task_commit_links table​
CREATE TABLE task_commit_links (
id TEXT PRIMARY KEY,
task_id INTEGER NOT NULL,
commit_sha TEXT NOT NULL,
confidence REAL DEFAULT 1.0 CHECK(confidence BETWEEN 0 AND 1),
link_type TEXT CHECK(link_type IN ('explicit', 'inferred')),
evidence TEXT,
FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
FOREIGN KEY (commit_sha) REFERENCES git_commits(sha) ON DELETE CASCADE,
UNIQUE(task_id, commit_sha)
);
New: task_session_links table​
CREATE TABLE task_session_links (
id TEXT PRIMARY KEY,
task_id INTEGER NOT NULL,
session_id TEXT NOT NULL,
confidence REAL DEFAULT 1.0 CHECK(confidence BETWEEN 0 AND 1),
link_type TEXT CHECK(link_type IN ('explicit', 'inferred')),
evidence TEXT,
FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
FOREIGN KEY (session_id) REFERENCES llm_sessions(id) ON DELETE CASCADE,
UNIQUE(task_id, session_id)
);
Common Patterns​
Creating a Git Commit with Task Link​
from database import get_connection, generate_uuid, create_task_commit_link
# 1. Create git repo (once per project)
repo_id = generate_uuid()
conn = get_connection()
cursor = conn.cursor()
cursor.execute("""
INSERT INTO git_repos (id, project_id, url, default_branch)
VALUES (?, ?, ?, ?)
""", (repo_id, 1, 'https://github.com/user/repo', 'main'))
conn.commit()
# 2. Create commit
cursor.execute("""
INSERT INTO git_commits (sha, repo_id, author, message, timestamp, additions, deletions)
VALUES (?, ?, ?, ?, ?, ?, ?)
""", ('abc123', repo_id, 'John Doe', 'feat: Add auth', '2025-11-27T14:30:00Z', 150, 20))
conn.commit()
conn.close()
# 3. Link to task
create_task_commit_link(
task_id=42,
commit_sha='abc123',
confidence=1.0,
link_type='explicit',
evidence='Commit message mentions implementing authentication'
)
Creating an LLM Session with Task Link​
from database import get_connection, generate_uuid, create_task_session_link
# 1. Create session
session_id = generate_uuid()
conn = get_connection()
cursor = conn.cursor()
cursor.execute("""
INSERT INTO llm_sessions (id, project_id, message_count, summary, model)
VALUES (?, ?, ?, ?, ?)
""", (session_id, 1, 42, 'Implemented JWT authentication', 'claude-sonnet-4'))
conn.commit()
conn.close()
# 2. Link to task
create_task_session_link(
task_id=42,
session_id=session_id,
confidence=1.0,
link_type='explicit',
evidence='Session focused on TASK-42'
)
Marking Task as Blocked​
from database import get_connection
conn = get_connection()
cursor = conn.cursor()
cursor.execute("""
UPDATE tasks
SET status = 'blocked',
blocked_reason = 'Waiting for API credentials from vendor',
updated_at = CURRENT_TIMESTAMP
WHERE id = ?
""", (42,))
conn.commit()
conn.close()
# Now appears in get_blocked_tasks()
Updating Task with Complexity​
from database import get_connection
conn = get_connection()
cursor = conn.cursor()
cursor.execute("""
UPDATE tasks
SET complexity = 'L', -- S/M/L/XL
updated_at = CURRENT_TIMESTAMP
WHERE id = ?
""", (42,))
conn.commit()
conn.close()
# Now appears in get_project_progress()['tasks_by_complexity']
Important Notes​
Source of Truth: tasks.checked​
- NOT
status='completed' - Checkbox click updates
checked - Progress calculations use
checked statusis workflow state (pending/in_progress/review/blocked/done)
Confidence Scores​
- 1.0 = Explicit reference (commit message "#TASK-42")
- 0.8-0.9 = High confidence inference (file overlap + title match)
- 0.6-0.7 = Medium confidence (title similarity)
- 0.4-0.5 = Low confidence (file overlap only)
Link Types​
- explicit = Confirmed link (user-created or commit reference)
- inferred = Automatic link (fuzzy matching, file analysis)
Foreign Key Constraints​
- Always create parent record first (git_repo before git_commit)
- Links automatically deleted when task/commit/session deleted (CASCADE)
- Use
PRAGMA foreign_keys = ONat connection level
Troubleshooting​
Error: "FOREIGN KEY constraint failed"​
Cause: Trying to link to non-existent record Solution: Create git_commit or llm_session first
Error: "UNIQUE constraint failed"​
Cause: Link already exists between task and commit/session
Solution: create_task_*_link() returns None, not an error
Error: "CHECK constraint failed"​
Cause: Invalid confidence (not 0.0-1.0) or link_type (not explicit/inferred) Solution: Clamp confidence, use valid link_type
Slow queries after migration​
Cause: Indexes not created
Solution: Check .indexes tasks in sqlite3, re-run migration step 7
For More Information​
- migration-guide.md - Complete migration procedures
- migration-summary.md - Executive summary and test results
- sdd-activity-dashboard.md - Full schema specification
- database.py - Function documentation and examples