stharrold/agentdb-state-manager

AgentDB State Manager Skill

Purpose

The AgentDB State Manager provides persistent database storage for workflow state tracking using DuckDB through Claude Code's AgentDB integration. This skill enables:

• Read-only analytics: Complex queries over workflow history without parsing files
• Checkpoint management: Store/recover context checkpoints at 100K token threshold
• Dependency resolution: Query task dependencies and critical paths
• Metrics analysis: Historical trends, time-in-phase, bottleneck identification

Primary source of truth: TODO_*.md files (AgentDB is an analytics cache)

Primary purpose: Data gathering and analysis

Workflow phase: Cross-phase (Utilities) - All phases (1-6)

When to Use

Use this skill when:

• Querying complex task dependencies or critical paths
• Analyzing workflow metrics (time-in-phase, completion rates)
• Storing context checkpoints at 100K tokens
• Tracking state transitions and historical trends
• Avoiding repetitive TODO file parsing for analytics

Triggered by keywords: "analyze workflow", "query state", "checkpoint", "workflow metrics", "task dependencies"

Integration with Workflow

Phase integration: Cross-phase (Utilities)

This skill integrates with all workflow phases:

Phase 0 (Setup):

• Initialize AgentDB schema on first use
• Load canonical state definitions from workflow-states.json

Phase 1 (Planning - BMAD):

• Track BMAD Q&A responses
• Store epic definitions and requirements

Phase 2 (Specification + Implementation):

• Sync tasks from TODO_*.md to AgentDB
• Query task dependencies for parallel execution
• Track implementation progress

Phase 3 (Quality):

• Store quality gate results
• Track test coverage trends
• Historical quality metrics

Phase 4 (Integration):

• Store as-built deviation data
• Metrics for feedback loop
• Compare planning vs actual

Phase 5 (Release):

• Release workflow state tracking
• Final QA checkpoint data

Phase 6 (Hotfix):

• Hotfix-specific state tracking
• Emergency rollback data

Context checkpoints (all phases):

• Store checkpoint at 100K tokens
• Faster resume with structured queries vs file parsing

Orchestrator integration:

• workflow-orchestrator optionally calls AgentDB for complex queries
• Fallback to file parsing if AgentDB unavailable
• Automatic sync after TODO file updates

Official Documentation Alignment

This skill follows the local workflow system patterns which extend official Claude Code skill specifications:

Official Claude Code Skills:

• Specification: https://docs.claude.com/en/docs/agents-and-tools/agent-skills
• Building Agents: https://docs.claude.com/en/docs/agents-and-tools/building-agents
• AgentDB Documentation: (Reference from TODO_agentdb-prompt-template.md)

Local Pattern Extensions:

file_structure:

• Local: ['SKILL.md', 'CLAUDE.md', 'README.md', 'CHANGELOG.md', 'ARCHIVED/', 'scripts/', 'templates/']
• Official: ['skill.md', 'README.md']
• Rationale: Extended structure supports multi-phase workflow with version tracking, context files, and archived materials

frontmatter:

• Local: YAML with 'name', 'version', 'description' fields
• Official: YAML with 'name', 'description' fields only
• Rationale: 'version' field enables semantic versioning integration with validate_versions.py and UPDATE_CHECKLIST.md workflow

directory_organization:

• Local: scripts/, templates/ subdirectories
• Official: Flat structure with skill.md
• Rationale: Separates Python code (scripts/) from templates (workflow-states.json) for better maintainability

These extensions support the multi-phase workflow system while maintaining compatibility with core Claude Code concepts.

Architecture

Data Flow (Read-Only Analytics Mode)

TODO_*.md (source of truth)
    ↓
sync_todo_to_db.py
    ↓
AgentDB (DuckDB)
    ↓
query_state.py / analyze_metrics.py
    ↓
Analytics results

Session ID Generation

AgentDB requires a session ID for database identification. This skill uses:

Method: Timestamp-based (reproducible)

from datetime import datetime
import hashlib

# Generate session ID from current timestamp
current_time = datetime.utcnow().isoformat()
session_id = hashlib.md5(current_time.encode()).hexdigest()[:16]
# Example: "f8e7d6c5b4a39281"

Rationale: Provides reproducible session IDs across agent invocations within the same timeframe, balancing uniqueness with reproducibility.

Schema Design (Immutable Append-Only)

Core principle: NEVER UPDATE or DELETE records - always INSERT new records

Primary table: workflow_records

CREATE TABLE IF NOT EXISTS workflow_records (
    record_id UUID PRIMARY KEY DEFAULT uuid(),
    record_datetimestamp TIMESTAMP DEFAULT current_timestamp,
    object_id VARCHAR NOT NULL,     -- Stable ID for workflow object
    object_type VARCHAR NOT NULL,   -- 'feature', 'task', 'epic', etc.
    object_state VARCHAR NOT NULL,  -- '20_in-progress', '99_done', etc.
    object_metadata JSON             -- Framework, dependencies, etc.
);

CREATE INDEX idx_records_object
ON workflow_records(object_id, record_datetimestamp DESC);

Current state query pattern:

-- Get latest state for each object
SELECT DISTINCT ON (object_id)
    object_id, object_type, object_state, object_metadata
FROM workflow_records
WHERE object_type = 'task'
ORDER BY object_id, record_datetimestamp DESC;

Usage

1. Initialize AgentDB (First Use)

# Initialize database schema and load state definitions
python .claude/skills/agentdb-state-manager/scripts/init_database.py

What it does:

• Creates workflow_records table
• Loads workflow-states.json
• Sets up indexes for efficient queries
• Stores session metadata

2. Sync TODO Files to AgentDB

# Sync all TODO_*.md files to database
python .claude/skills/agentdb-state-manager/scripts/sync_todo_to_db.py

# Sync specific TODO file
python .claude/skills/agentdb-state-manager/scripts/sync_todo_to_db.py \
  TODO_feature_20251023T143000Z_my-feature.md

What it does:

• Parses YAML frontmatter from TODO files
• Converts to immutable append-only records
• Stores workflow progress, tasks, quality gates
• Preserves full state history

3. Query Current Workflow State

# Get current state of all workflows
python .claude/skills/agentdb-state-manager/scripts/query_state.py

# Query specific workflow
python .claude/skills/agentdb-state-manager/scripts/query_state.py \
  --slug my-feature

# Query task dependencies
python .claude/skills/agentdb-state-manager/scripts/query_state.py \
  --dependencies --task impl_003

What it does:

• Queries latest state for each object
• Resolves task dependencies
• Identifies blocked tasks
• Shows critical path

4. Analyze Workflow Metrics

# Generate metrics report
python .claude/skills/agentdb-state-manager/scripts/analyze_metrics.py

# Historical trends
python .claude/skills/agentdb-state-manager/scripts/analyze_metrics.py \
  --trends --days 30

# Bottleneck analysis
python .claude/skills/agentdb-state-manager/scripts/analyze_metrics.py \
  --bottlenecks

What it does:

• Calculates time-in-phase for workflows
• Identifies bottlenecks and slow phases
• Tracks quality gate pass/fail rates
• Compares planning estimates vs actual

5. Context Checkpoint Management

# Store checkpoint at 100K tokens
python .claude/skills/agentdb-state-manager/scripts/checkpoint_manager.py \
  store --todo TODO_feature_*.md

# List available checkpoints
python .claude/skills/agentdb-state-manager/scripts/checkpoint_manager.py list

# Restore from checkpoint
python .claude/skills/agentdb-state-manager/scripts/checkpoint_manager.py \
  restore --checkpoint-id <uuid>

What it does:

• Stores complete workflow state to AgentDB
• Records token count, phase, step, last task
• Provides resume instructions
• Faster recovery than parsing TODO files

Best Practices

1. AgentDB as Analytics Cache

✓ DO: Treat TODO_*.md files as source of truth ✗ DON'T: Modify TODO files based on AgentDB state

Rationale: Files are git-tracked and permanent; AgentDB sessions last 24 hours

2. Sync After TODO Updates

✓ DO: Run sync_todo_to_db.py after every TODO file update ✗ DON'T: Let AgentDB state drift from file state

Rationale: AgentDB is a cache - it must stay synchronized

3. Use for Complex Queries Only

✓ DO: Use AgentDB for dependency graphs, metrics, historical analysis ✗ DON'T: Use for simple current state queries (parse TODO file instead)

Rationale: Minimize token overhead - use database for queries that justify the cost

4. Immutable Records

✓ DO: Always INSERT new records for state changes ✗ DON'T: UPDATE or DELETE existing records

Rationale: Preserves full history for temporal analysis

5. Session Lifetime Awareness

✓ DO: Re-initialize at session start if needed ✗ DON'T: Assume AgentDB persists across days

Rationale: 24-hour auto-deletion - treat as ephemeral cache

Scripts Reference

init_database.py

Purpose: Initialize AgentDB schema and load state definitions When to run: First use in a session, or if schema corrupted Token cost: ~300-400 tokens

sync_todo_to_db.py

Purpose: Sync TODO_*.md files to AgentDB When to run: After every TODO file update Token cost: ~200-300 tokens per TODO file

query_state.py

Purpose: Query current workflow state and dependencies When to run: Complex state queries (dependency graphs, critical paths) Token cost: ~100-200 tokens per query

analyze_metrics.py

Purpose: Historical metrics and trend analysis When to run: End of phase, workflow retrospectives Token cost: ~400-600 tokens (complex queries)

checkpoint_manager.py

Purpose: Store/restore context checkpoints When to run: At 100K token threshold, or before context reset Token cost: ~200-400 tokens (store + verify)

Token Efficiency

Before AgentDB (Manual File Parsing)

Complex dependency query:

1. Read TODO_*.md file (~1,500 tokens)
2. Parse YAML frontmatter (~500 tokens)
3. Iterate through tasks (~1,000 tokens)
4. Resolve dependencies (~800 tokens) Total: ~3,800 tokens

After AgentDB (Database Query)

Same query:

1. Run query_state.py --dependencies (~200 tokens)
2. Parse SQL results (~100 tokens) Total: ~300 tokens

Savings: ~3,500 tokens (92% reduction) for complex queries

Context Checkpoint Recovery

Before AgentDB:

• Parse TODO_*.md (~1,500 tokens)
• Reconstruct state (~1,000 tokens)
• Determine next step (~800 tokens) Total: ~3,300 tokens

After AgentDB:

• Query checkpoint table (~150 tokens)
• Load resume instructions (~100 tokens) Total: ~250 tokens

Savings: ~3,050 tokens (92% reduction)

Integration with Other Skills

workflow-orchestrator

• Calls query_state.py for complex next-step determination
• Uses checkpoint_manager.py at 100K tokens
• Falls back to file parsing if AgentDB unavailable

workflow-utilities

• todo_updater.py triggers sync_todo_to_db.py after updates
• Dual write: update file + sync to database

speckit-author

• Uses AgentDB for as-built metrics (update_asbuilt.py)
• Queries historical planning accuracy

quality-enforcer

• Stores quality gate results to AgentDB
• Tracks coverage trends over time

bmad-planner

• Stores BMAD Q&A responses
• Epic definition tracking

Related Documentation

• CLAUDE.md - Claude Code usage context
• README.md - Human-readable overview
• CHANGELOG.md - Version history
• templates/workflow-states.json - Canonical state definitions

Related Skills

• workflow-orchestrator - Calls AgentDB for complex queries
• workflow-utilities - Triggers sync after TODO updates
• speckit-author - Uses for as-built metrics
• quality-enforcer - Stores quality gate results
• bmad-planner - Stores planning data

Limitations

1. Session lifetime: AgentDB persists for 24 hours, then auto-deleted
2. Source of truth: TODO_*.md files remain authoritative
3. Sync overhead: ~200-300 tokens per sync operation
4. DuckDB constraints: Optimized for OLAP (analytics), not OLTP (transactions)
5. No vector search: Unlike PostgreSQL pgvector

Academic References

This skill implements the MIT Agent Synchronization Pattern based on:

Meng, E., & Jackson, D. (2025). "What You See Is What It Does: A Structural Pattern for Legible Software." Onward! at SPLASH 2025. arXiv:2508.14511v1. https://arxiv.org/abs/2508.14511

Key concepts from the paper:

• Concepts: Fully independent services (implemented as agents/worktrees)
• Synchronizations: Event-based rules mediating between services (implemented as agent_synchronizations table)

The pattern improves incrementality, integrity, and transparency in multi-agent workflows.

Version History

v1.0.0 (2025-11-02):

• Initial release
• Read-only analytics mode
• Sync from TODO files to AgentDB
• Complex query support (dependencies, metrics)
• Context checkpoint storage
• All phases (1-6) integration