agentdb-state-manager

name	agentdb-state-manager
version	1.0.0
description	Persistent state management using AgentDB (DuckDB) for workflow analytics and checkpoints. Provides read-only analytics cache synchronized from TODO_*.md files, enabling: - Complex dependency graph queries - Historical workflow metrics - Context checkpoint storage/recovery - State transition analysis Use when: Data gathering and analysis for workflow state tracking Triggers: "analyze workflow", "query state", "checkpoint", "workflow metrics"

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

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