Building an Intelligent Task Tracking System with SQLite: Beyond Traditional Project Management

TL;DR

We built a comprehensive task tracking system using SQLite that goes beyond traditional project management tools by integrating documentation, git changes, and feature mappings in a relational database. This system tracks hierarchical relationships between plans, tasks, and features while preserving implementation context and generating automated documentation. This article details our database schema design, implementation approach, and how this system has transformed our development workflow by providing structured context for both humans and AI assistants.

The Problem: Fragmented Project Context

Traditional project management tools fail to capture the deep context needed for effective software development, particularly when working with AI pair programmers like Claude. Our team faced several critical challenges:

• Disconnected Tools: Task tracking, documentation, and version control existed as separate silos
• Lost Context: The reasoning behind implementation decisions wasn’t preserved
• Feature Tracking Gaps: Difficult to track which implementations satisfied which requirements
• Documentation Drift: Documentation quickly became outdated as code evolved
• Limited AI Context: Claude lacked the structured context needed to understand our codebase deeply

We needed a unified system that would centralize project information, preserve implementation context, and provide structured data that both humans and AI could leverage effectively.

Solution Architecture

Our SQLite task tracking system is built around a hierarchical, relational model that mirrors how developers naturally think about their work:

┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
│                 │     │                 │     │                 │
│      Plans      │────▶│      Tasks      │────▶│    Features     │
│  (High-level)   │     │(Implementation) │     │  (Components)   │
│                 │     │                 │     │                 │
└─────────┬───────┘     └────────┬────────┘     └────────┬────────┘
          │                      │                       │
          │                      │                       │
          │                      │                       │
          ▼                      ▼                       ▼
┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
│                 │     │                 │     │                 │
│ Documentation   │     │  Git Tracking   │     │Feature Mappings │
│  Resources      │     │ (Start/End)     │     │(Req→Impl links) │
│                 │     │                 │     │                 │
└─────────────────┘     └─────────────────┘     └─────────────────┘
          │                      │                       │
          │                      │                       │
          └──────────────────────┼───────────────────────┘
                                 │
                                 ▼
                       ┌─────────────────┐
                       │                 │
                       │  Clood Groups   │
                       │  (Auto-docs)    │
                       │                 │
                       └─────────────────┘

Database Schema Design

The heart of our system is a carefully designed SQLite database schema that captures the complex relationships between different elements of our development process:

1. Core Tables Structure

-- Main items table (for both plans and tasks)
CREATE TABLE items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    type TEXT NOT NULL, -- 'plan' or 'task'
    parent_id INTEGER, -- for hierarchy within same type
    description TEXT,
    status TEXT NOT NULL DEFAULT 'active',
    priority INTEGER NOT NULL DEFAULT 0,
    assigned_to TEXT,
    due_date DATETIME,
    completion_date DATETIME,
    created_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    start_commit TEXT,
    end_commit TEXT,
    FOREIGN KEY (parent_id) REFERENCES items (id),
    UNIQUE (name, parent_id, type) -- ensure unique names within a parent
);

-- Features table
CREATE TABLE features (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    item_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    status TEXT NOT NULL DEFAULT 'not_started',
    created_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (item_id) REFERENCES items (id),
    UNIQUE (item_id, name) -- ensure unique feature names within an item
);

-- Feature mappings (to track which plan features match which task features)
CREATE TABLE feature_mappings (
    plan_feature_id INTEGER NOT NULL,
    task_feature_id INTEGER NOT NULL,
    PRIMARY KEY (plan_feature_id, task_feature_id),
    FOREIGN KEY (plan_feature_id) REFERENCES features (id),
    FOREIGN KEY (task_feature_id) REFERENCES features (id)
);

-- Document resources table for storing markdown and code samples
CREATE TABLE document_resources (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    item_id INTEGER,
    feature_id INTEGER,
    resource_type TEXT NOT NULL,
    filename TEXT NOT NULL,
    content TEXT NOT NULL,
    language TEXT,
    created_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (item_id) REFERENCES items (id),
    FOREIGN KEY (feature_id) REFERENCES features (id)
);

-- Clood Groups table
CREATE TABLE clood_groups (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    item_id INTEGER NOT NULL,
    feature_id INTEGER,
    file_path TEXT NOT NULL,
    prompt_content TEXT NOT NULL,
    files_included TEXT,  -- JSON array of file paths
    created_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status TEXT NOT NULL DEFAULT 'generated',
    start_commit TEXT,
    end_commit TEXT,
    FOREIGN KEY (item_id) REFERENCES items (id),
    FOREIGN KEY (feature_id) REFERENCES features (id)
);

2. Key Schema Design Decisions

Several careful design decisions shaped our schema:

System Implementation

Database Initialization

We created a simple shell script to initialize the database with our schema:

#!/bin/bash
# Initialize SQLite database for task tracking

SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" &> /dev/null && pwd )"
DB_FILE="${SCRIPT_DIR}/../db/sql.db"

# Create database file if it doesn't exist
mkdir -p "$(dirname "$DB_FILE")"
touch "$DB_FILE"

# Run SQL schema creation script
sqlite3 "$DB_FILE" < "${SCRIPT_DIR}/create_tables.sql"

echo "Database initialized at $DB_FILE"

Task Management Workflow

Our task management process follows a structured flow that integrates with our database:

1. Planning Phase: Create plans with high-level features
2. Implementation Phase: Create tasks for each plan and track specific implementation features
3. Mapping Phase: Link task features to plan features they implement
4. Documentation Phase: Preserve markdown docs and code samples as document resources
5. Completion Phase: Record end commit and generate CloodGroups

Example Commands

Here are some example commands for interacting with our task system:

Creating a New Plan

sqlite3 /path/to/db/sql.db "
INSERT INTO items (name, type, description) 
VALUES ('VNC Desktop Implementation', 'plan', 'Implement a shared Ubuntu Desktop environment accessible via VNC');
"

Adding Plan Features

PLAN_ID=$(sqlite3 /path/to/db/sql.db "SELECT id FROM items WHERE name='VNC Desktop Implementation' AND type='plan'")

sqlite3 /path/to/db/sql.db "
INSERT INTO features (item_id, name, description) 
VALUES 
($PLAN_ID, 'Desktop Environment', 'Lightweight Ubuntu desktop with common development tools'),
($PLAN_ID, 'VNC Server', 'Multi-user VNC server with persistent sessions'),
($PLAN_ID, 'Security Implementation', 'SSL encryption and password authentication'),
($PLAN_ID, 'Performance Optimization', 'Tuning for low-bandwidth usage');
"

Creating an Implementation Task

sqlite3 /path/to/db/sql.db "
INSERT INTO items (name, type, parent_id, description, start_commit) 
VALUES ('Base VNC Installation', 'task', $PLAN_ID, 'Install and configure the base VNC desktop', 'a7b8c9d');
"

Mapping Features Between Plan and Task

TASK_ID=$(sqlite3 /path/to/db/sql.db "SELECT id FROM items WHERE name='Base VNC Installation' AND type='task'")

# Add task features
sqlite3 /path/to/db/sql.db "
INSERT INTO features (item_id, name, description) 
VALUES 
($TASK_ID, 'XFCE Installation', 'Install XFCE desktop environment'),
($TASK_ID, 'X11VNC Setup', 'Install and configure x11vnc server');
"

# Get feature IDs
PLAN_DESKTOP_FEATURE=$(sqlite3 /path/to/db/sql.db "SELECT id FROM features WHERE item_id=$PLAN_ID AND name='Desktop Environment'")
PLAN_VNC_FEATURE=$(sqlite3 /path/to/db/sql.db "SELECT id FROM features WHERE item_id=$PLAN_ID AND name='VNC Server'")
TASK_XFCE_FEATURE=$(sqlite3 /path/to/db/sql.db "SELECT id FROM features WHERE item_id=$TASK_ID AND name='XFCE Installation'")
TASK_X11VNC_FEATURE=$(sqlite3 /path/to/db/sql.db "SELECT id FROM features WHERE item_id=$TASK_ID AND name='X11VNC Setup'")

# Create mappings
sqlite3 /path/to/db/sql.db "
INSERT INTO feature_mappings (plan_feature_id, task_feature_id) VALUES 
($PLAN_DESKTOP_FEATURE, $TASK_XFCE_FEATURE),
($PLAN_VNC_FEATURE, $TASK_X11VNC_FEATURE);
"

Saving Documentation

sqlite3 /path/to/db/sql.db "
INSERT INTO document_resources (item_id, resource_type, filename, content) 
VALUES ($TASK_ID, 'markdown', 'vnc-setup-instructions.md', 'Content of markdown file here...');
"

Marking Task as Complete

sqlite3 /path/to/db/sql.db "
UPDATE items 
SET status = 'completed', 
    completion_date = CURRENT_TIMESTAMP, 
    end_commit = 'e2f3a4b' 
WHERE id = $TASK_ID;

UPDATE features
SET status = 'completed'
WHERE item_id = $TASK_ID;
"

Real-World Example: VNC Desktop Implementation

Let's walk through how our system tracked the implementation of our VNC Desktop feature:

1. Plan Definition

We started by creating a "VNC Desktop Implementation" plan with these features:

2. Implementation Tasks

We broke down the plan into four implementation tasks:

3. Feature Mappings

We created mappings between plan features and their implementing task features:

4. Documentation Resources

For each task, we preserved relevant documentation:

• VNC Task Definition: Markdown file with requirements and phases
• VNC Technical Design: Detailed architecture and implementation steps
• Installation Commands: Shell scripts and commands used
• Security Configuration: SSL certificate setup and firewall rules

5. CloodGroup Generation

Upon completion, our system generated CloodGroups for each task, capturing the files changed and the implementation intent:

{
  "files": [
    "docs/vnc-desktop-task.md",
    "docs/vnc-desktop-design.md"
  ],
  "prompt": "Design a shared Ubuntu Desktop environment accessible via VNC for collaborative development. The system should allow multiple team members to connect simultaneously to the same desktop session, with secure authentication and encrypted connections. Include both a task document outlining requirements and phases, and a technical design document with installation steps, configuration details, and security considerations. The solution should solve the problem of command-line only development by providing a persistent graphical workspace."
}

Powerful Queries for Development Insights

One of the greatest benefits of our SQLite database is the ability to run powerful queries that provide deep insights into our development process:

Overall Project Status

SELECT 
    p.name as plan, 
    COUNT(DISTINCT t.id) as total_tasks,
    SUM(CASE WHEN t.status = 'completed' THEN 1 ELSE 0 END) as completed_tasks,
    COUNT(DISTINCT f.id) as total_features,
    SUM(CASE WHEN f.status = 'completed' THEN 1 ELSE 0 END) as completed_features,
    ROUND(SUM(CASE WHEN f.status = 'completed' THEN 1 ELSE 0 END) * 100.0 / COUNT(DISTINCT f.id), 1) as completion_percentage
FROM 
    items p
    LEFT JOIN items t ON p.id = t.parent_id AND t.type = 'task'
    LEFT JOIN features f ON p.id = f.item_id
WHERE 
    p.type = 'plan'
GROUP BY 
    p.id
ORDER BY 
    completion_percentage DESC;

Feature Implementation Tracking

SELECT 
    pf.name as plan_feature,
    p.name as plan,
    tf.name as task_feature,
    t.name as task,
    tf.status as implementation_status
FROM 
    features pf
    JOIN items p ON pf.item_id = p.id
    LEFT JOIN feature_mappings fm ON pf.id = fm.plan_feature_id
    LEFT JOIN features tf ON fm.task_feature_id = tf.id
    LEFT JOIN items t ON tf.item_id = t.id
WHERE 
    p.type = 'plan'
ORDER BY 
    p.name, pf.name, t.name;

Documentation Search

SELECT 
    dr.filename,
    i.name as item_name,
    i.type as item_type,
    dr.created_date,
    dr.content
FROM 
    document_resources dr
    JOIN items i ON dr.item_id = i.id
WHERE 
    dr.content LIKE '%VNC%' AND
    dr.resource_type = 'markdown'
ORDER BY 
    dr.created_date DESC;

Git Activity Analysis

SELECT 
    i.name as task,
    i.start_commit,
    i.end_commit,
    i.completion_date,
    COUNT(cg.id) as clood_groups_generated
FROM 
    items i
    LEFT JOIN clood_groups cg ON i.id = cg.item_id
WHERE 
    i.type = 'task' AND
    i.status = 'completed' AND
    i.end_commit IS NOT NULL
GROUP BY 
    i.id
ORDER BY 
    i.completion_date DESC;

Integrating with Development Workflow

We integrated our SQLite task system into our development workflow to make it a natural part of our process:

Planning Phase

1. Create Plan: Define high-level initiative with features
2. Add Documentation: Store markdown files with requirements
3. Set Priorities: Assign priorities to features

Implementation Phase

1. Start Task: Create task with start commit
2. Define Features: Break down into implementation features
3. Map to Requirements: Link task features to plan features
4. Track Progress: Update feature status as work progresses

Completion Phase

1. Record End Commit: Store the final commit hash
2. Update Status: Mark features and task as completed
3. Generate CloodGroup: Create documentation from changes
4. Update Plan Status: Recalculate plan completion percentage

API Integration

We developed simple API wrappers to make interacting with the database easier:

def create_plan(name, description, features=None):
    """Create a new plan with optional features."""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # Insert plan
    cursor.execute("""
        INSERT INTO items (name, type, description)
        VALUES (?, 'plan', ?)
    """, (name, description))
    
    plan_id = cursor.lastrowid
    
    # Add features if provided
    if features:
        feature_values = [(plan_id, f['name'], f.get('description', '')) for f in features]
        cursor.executemany("""
            INSERT INTO features (item_id, name, description)
            VALUES (?, ?, ?)
        """, feature_values)
    
    conn.commit()
    conn.close()
    return plan_id

def create_task(name, plan_id, description=None, start_commit=None, features=None):
    """Create a new task for a plan."""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # Insert task
    cursor.execute("""
        INSERT INTO items (name, type, parent_id, description, start_commit, status)
        VALUES (?, 'task', ?, ?, ?, 'in_progress')
    """, (name, plan_id, description, start_commit))
    
    task_id = cursor.lastrowid
    
    # Add features if provided
    if features:
        feature_values = [(task_id, f['name'], f.get('description', '')) for f in features]
        cursor.executemany("""
            INSERT INTO features (item_id, name, description)
            VALUES (?, ?, ?)
        """, feature_values)
    
    conn.commit()
    conn.close()
    return task_id

def complete_task(task_id, end_commit):
    """Mark a task as completed and record end commit."""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # Update task status
    cursor.execute("""
        UPDATE items
        SET status = 'completed', completion_date = CURRENT_TIMESTAMP, end_commit = ?
        WHERE id = ? AND type = 'task'
    """, (end_commit, task_id))
    
    # Mark all features as completed
    cursor.execute("""
        UPDATE features
        SET status = 'completed'
        WHERE item_id = ? AND status != 'completed'
    """, (task_id,))
    
    # Generate CloodGroup
    generate_clood_group(task_id, end_commit)
    
    conn.commit()
    conn.close()

Results and Benefits

Implementing our SQLite task tracking system has led to significant improvements in our development process:

Quantitative Benefits

Qualitative Benefits

• Unified Context: All project information in one structured database
• Preserved Intent: Documentation tied directly to code changes
• Feature Traceability: Clear mappings between requirements and implementations
• Better Claude Context: Structured information for AI-assisted development
• Knowledge Persistence: Team knowledge preserved even as members change

Providing Context to Claude

One of the most powerful benefits of our SQLite task system is the ability to provide Claude with rich, structured context. Here's how we use it in practice:

Example: Adding a Feature to an Existing Component

When asking Claude to add a feature, we now provide structured context from our task system:

I'm working on implementing a feature described in our task system:

Plan: "VNC Desktop Implementation"
Task: "Performance Tuning"
Feature: "Resolution Optimization"
Description: "Implement dynamic resolution adjustment based on client window size"

Current implementation context:
- This feature is part of our VNC desktop system that allows shared desktop access
- The base VNC server (x11vnc) is already installed and configured
- Current setup uses a fixed resolution of 1920x1080
- We need to make this resolution adjust to the client's window size

Related implementation features:
- XFCE desktop environment is already installed
- Systemd service is configured to start the VNC server
- SSL encryption is in place for security

Can you help me implement the resolution optimization feature by modifying the x11vnc startup script?

With this rich context, Claude can generate code that:

• Fits perfectly into our existing architecture
• Addresses the specific requirement
• Respects existing patterns and implementations
• Understands the relationship to other features

Challenges and Lessons Learned

Implementing our SQLite task system wasn't without challenges:

1. Schema Evolution

Challenge: As our needs evolved, we needed to extend the database schema.

Solution: We implemented a versioned migration system for schema changes:

#!/bin/bash

DB_FILE="/path/to/db/sql.db"
MIGRATIONS_DIR="/path/to/migrations"

# Get current schema version
CURRENT_VERSION=$(sqlite3 $DB_FILE "PRAGMA user_version;")

# Find migration files newer than current version
for migration in $(ls $MIGRATIONS_DIR/*.sql | sort); do
    filename=$(basename $migration)
    version=${filename%_*}
    
    if [[ $version -gt $CURRENT_VERSION ]]; then
        echo "Applying migration $filename..."
        sqlite3 $DB_FILE < $migration
        
        # Update schema version
        sqlite3 $DB_FILE "PRAGMA user_version = $version;"
    fi
done

echo "Database schema updated to version $(sqlite3 $DB_FILE 'PRAGMA user_version;')"

2. Data Integrity

Challenge: Ensuring data consistency across related tables was difficult.

Solution: We added triggers to maintain data integrity:

-- Update item status based on feature completion
CREATE TRIGGER update_item_status_on_feature_completion
AFTER UPDATE ON features
WHEN NEW.status = 'completed' AND OLD.status != 'completed'
BEGIN
    -- Check if all features for this item are completed
    SELECT CASE
        WHEN NOT EXISTS (
            SELECT 1 FROM features 
            WHERE item_id = NEW.item_id AND status != 'completed'
        )
        THEN
            -- All features are completed, update item status
            UPDATE items SET 
                status = 'completed', 
                completion_date = CURRENT_TIMESTAMP
            WHERE id = NEW.item_id AND status != 'completed';
    END;
END;

3. Command-Line Usability

Challenge: Raw SQL commands were cumbersome for team members.

Solution: We created a simple CLI tool to interact with the database:

#!/usr/bin/env python3
import argparse
import sqlite3
import os
import json
from datetime import datetime

DB_PATH = os.path.expanduser("~/db/sql.db")

def connect_db():
    return sqlite3.connect(DB_PATH)

def list_plans(args):
    conn = connect_db()
    cursor = conn.cursor()
    
    cursor.execute("""
        SELECT i.id, i.name, i.description, i.status,
               COUNT(DISTINCT f.id) as feature_count,
               SUM(CASE WHEN f.status = 'completed' THEN 1 ELSE 0 END) as completed_features
        FROM items i
        LEFT JOIN features f ON i.id = f.item_id
        WHERE i.type = 'plan'
        GROUP BY i.id
    """)
    
    plans = cursor.fetchall()
    
    print(f"{'ID':<5} {'Name':<30} {'Status':<12} {'Progress':<10} {'Description':<30}")
    print(f"{'-'*5} {'-'*30} {'-'*12} {'-'*10} {'-'*30}")
    
    for plan in plans:
        plan_id, name, desc, status, feature_count, completed = plan
        progress = f"{completed}/{feature_count}" if feature_count else "0/0"
        progress_pct = f"({int(completed/feature_count*100)}%)" if feature_count else "(0%)"
        
        print(f"{plan_id:<5} {name[:30]:<30} {status:<12} {progress + ' ' + progress_pct:<10} {desc[:30] if desc else '':<30}")
    
    conn.close()

def add_plan(args):
    conn = connect_db()
    cursor = conn.cursor()
    
    cursor.execute("""
        INSERT INTO items (name, type, description, status)
        VALUES (?, 'plan', ?, 'active')
    """, (args.name, args.description))
    
    plan_id = cursor.lastrowid
    
    if args.features:
        features = args.features.split(',')
        for feature in features:
            feature = feature.strip()
            cursor.execute("""
                INSERT INTO features (item_id, name, status)
                VALUES (?, ?, 'not_started')
            """, (plan_id, feature))
    
    conn.commit()
    print(f"Created plan '{args.name}' with ID {plan_id}")
    conn.close()

# Main argument parser
parser = argparse.ArgumentParser(description='Task Tracking CLI')
subparsers = parser.add_subparsers(dest='command', help='Command to run')

# List plans command
list_parser = subparsers.add_parser('list-plans', help='List all plans')
list_parser.set_defaults(func=list_plans)

# Add plan command
add_parser = subparsers.add_parser('add-plan', help='Add a new plan')
add_parser.add_argument('name', help='Plan name')
add_parser.add_argument('--description', '-d', help='Plan description')
add_parser.add_argument('--features', '-f', help='Comma-separated list of features')
add_parser.set_defaults(func=add_plan)

# Add more commands for tasks, features, etc.

if __name__ == '__main__':
    args = parser.parse_args()
    if hasattr(args, 'func'):
        args.func(args)
    else:
        parser.print_help()

Future Enhancements

While our current system has been transformative, we have several enhancements planned:

1. Web-based Dashboard

We're developing a web interface for easier interaction with the task system:

• Visual representation of plans and tasks
• Feature mapping visualization
• Progress tracking with charts and metrics
• Documentation browsing and searching

2. Integration with CI/CD

We plan to integrate our task system with our CI/CD pipeline:

• Automatic task status updates based on build results
• CloodGroup generation triggered by successful builds
• Test coverage linked to features

3. Enhanced AI Integration

We're working on deeper integration with Claude and other AI tools:

• Automated extraction of task context for AI prompts
• AI-generated feature suggestions based on implementation patterns
• Automated prompt generation for implementation tasks

4. Custom Query Builder

We're developing a custom query builder to make complex queries accessible to non-technical team members:

• Visual query builder interface
• Saved queries for common reports
• Export options for sharing and presentations

Conclusion

Our SQLite task tracking system has fundamentally changed how we approach software development by providing a structured, relational model for project information that goes far beyond traditional task tracking tools. By integrating plans, tasks, features, documentation, and git changes into a unified system, we've created a powerful knowledge repository that preserves the deep context needed for effective development.

The benefits have been substantial: reduced time searching for context, improved documentation, better feature traceability, and enhanced AI assistance. Most importantly, we've created a system that preserves institutional knowledge as our team and codebase evolve.

For teams considering a similar approach, we highly recommend exploring SQLite as a foundation. Its simplicity, portability, and powerful relational capabilities make it an ideal choice for a lightweight, developer-focused task system. By tailoring the schema to your specific workflow, you can create a system that naturally fits how your team thinks about and implements software.

As we continue to refine and extend our system, we're increasingly convinced that the future of software development lies not just in better tools, but in better integration between tools - creating a seamless flow of context that empowers both human developers and AI assistants to work more effectively together.