schema.sql

-- Agent Worklog Database Schema
-- SQLite database for tracking AI agent work

-- Main worklog table
CREATE TABLE IF NOT EXISTS worklog (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    
    -- Task description
    task TEXT NOT NULL,
    
    -- Project information
    project_root TEXT NOT NULL,
    repo TEXT NOT NULL,           -- Repository name (from git remote or directory name)
    project TEXT NOT NULL,        -- Project/directory name (basename of project_root)
    worktree TEXT,
    
    -- Git information
    git_ref TEXT,
    commit_hash TEXT,
    commit_message TEXT,
    
    -- Code change statistics
    lines_added INTEGER DEFAULT 0,
    lines_removed INTEGER DEFAULT 0,
    
    -- Categorization
    hashtags TEXT,  -- Comma-separated, e.g., "#feature,#api,#auth"
    
    -- Timestamps
    timestamp TEXT NOT NULL,  -- ISO 8601 format, when work was done
    created_at TEXT DEFAULT (datetime('now'))  -- When entry was logged
);

-- Indexes for common query patterns
CREATE INDEX IF NOT EXISTS idx_worklog_timestamp ON worklog(timestamp);
CREATE INDEX IF NOT EXISTS idx_worklog_project ON worklog(project_root);
CREATE INDEX IF NOT EXISTS idx_worklog_repo ON worklog(repo);
CREATE INDEX IF NOT EXISTS idx_worklog_project_name ON worklog(project);
CREATE INDEX IF NOT EXISTS idx_worklog_hashtags ON worklog(hashtags);
CREATE INDEX IF NOT EXISTS idx_worklog_commit ON worklog(commit_hash);

-- View for quick statistics
CREATE VIEW IF NOT EXISTS worklog_stats AS
SELECT 
    date(timestamp) as work_date,
    COUNT(*) as tasks_completed,
    COUNT(DISTINCT commit_hash) as commits,
    SUM(lines_added) as total_added,
    SUM(lines_removed) as total_removed,
    COUNT(DISTINCT project_root) as projects
FROM worklog
GROUP BY date(timestamp)
ORDER BY work_date DESC;

-- View for project summary
CREATE VIEW IF NOT EXISTS project_stats AS
SELECT 
    project_root,
    COUNT(*) as total_tasks,
    SUM(lines_added) as total_added,
    SUM(lines_removed) as total_removed,
    MIN(timestamp) as first_entry,
    MAX(timestamp) as last_entry
FROM worklog
GROUP BY project_root
ORDER BY total_tasks DESC;