Database Agent

The Database agent (AG-DATABASE) is your database specialist for designing efficient schemas, writing safe migrations, optimizing queries, and managing data integrity. It ensures your database layer is performant, maintainable, and reliable.

Capabilities

• Design efficient database schemas (tables, relationships, constraints)
• Write reversible migration scripts with rollback strategies
• Optimize slow queries (identify missing indexes, improve query structure)
• Prevent N+1 query problems and SELECT * anti-patterns
• Ensure data integrity through constraints and validation
• Review queries from AG-API for performance issues
• Document data models and relationships with architecture ADRs
• Coordinate with AG-API on ORM usage and query patterns
• Monitor performance and database health

When to Use

Use the Database agent when:

• You need to design a new table or relationship structure
• You need to create a migration script for schema changes
• You notice slow queries or performance issues
• You want to optimize a complex database query
• You need to add indexes to frequently queried columns
• You're designing data models for a new feature
• You want to prevent N+1 query patterns before they happen
• You need to coordinate data layer work with AG-API

How It Works

1. Context Loading: Agent reads expertise, CLAUDE.md, and database configuration
2. Story Review: Agent identifies data requirements and relationships
3. Plan Mode: Agent designs schema and migration strategy (for high-risk changes)
4. Schema Design: Agent creates entity-relationship diagrams and normalizes data
5. Migration Creation: Agent writes reversible up/down scripts with testing
6. Coordination: Agent shares schema with AG-API and reviews their queries
7. Optimization: Agent adds indexes and prevents N+1 query patterns
8. Verification: Agent runs tests to ensure everything passes
9. Documentation: Agent updates status.json and appends bus messages

Example

Copy# Via /babysit (recommended)
/agileflow:babysit
> "I need to design the database schema for users and posts"

The Database agent will:

1. Ask about data relationships (one user to many posts?)
2. Design normalized schema:
   • users table: id, email, username, password_hash, created_at, updated_at
   • posts table: id, user_id, title, content, created_at, updated_at
   • Foreign key constraint: posts.user_id → users.id
3. Plan indexes: idx_users_email (for login), idx_posts_user_id (for queries)
4. Create migration script with up/down operations
5. Coordinate with AG-API on ORM models and queries
6. Test migration rollback before completion

Or spawn directly:

CopyTask(
  description: "Optimize slow user profile query",
  prompt: "Query is taking 2+ seconds. Need to analyze and add missing indexes.",
  subagent_type: "agileflow-database"
)

Key Behaviors

• Never Make Changes Without Migrations: All schema changes require reversible migration scripts
• Plan Mode for High-Risk Changes: Always uses Plan Mode to design schema/migration strategy
• Reversible Migrations: Every "up" migration has corresponding "down" for rollback
• Query Optimization: Analyzes queries for N+1 problems, missing indexes, SELECT * anti-patterns
• Naming Conventions: Tables lowercase plural (users, posts), columns snake_case (user_id, created_at)
• Required Columns: Every table has id, created_at, updated_at (and deleted_at for soft deletes)
• Coordination with AG-API: Reviews their queries and ORM usage; suggests optimizations
• Session Harness Integration: Verifies test status before starting, requires passing tests before in-review
• Proactive Documentation: Creates ADRs for major schema decisions
• Context Preservation: Uses compact_context (priority: high) to maintain focus during long conversations, preserving schema decisions and performance optimization tracking through context compaction

Compact Context Configuration

The Database agent uses high priority compact_context to ensure schema design and optimization decisions stay preserved:

Copycompact_context:
  priority: high
  preserve_rules:
    - "LOAD EXPERTISE FIRST: Always read packages/cli/src/core/experts/database/expertise.yaml"
    - "NEVER CHANGE SCHEMA WITHOUT MIGRATION: All changes require reversible up/down scripts"
    - "PLAN MODE FOR HIGH-RISK CHANGES: Design schema/migration strategy before implementation"
    - "VERIFY TEST BASELINE: Check test_status before starting new work"
    - "REQUIRED COLUMNS: Every table needs id, created_at, updated_at"
    - "COORDINATION WITH AG-API: Review their queries and ORM patterns"
  state_fields:
    - current_story
    - schema_changes_planned
    - migration_strategy
    - api_query_reviews
    - test_status_baseline

This ensures database-critical rules (migration requirements, schema naming conventions, required columns) and current state (what schema changes are planned, which API queries need review) remain in focus through context compaction.

Tools Available

This agent has access to: Read, Write, Edit, Bash, Glob, Grep

Schema Design Principles

Normalization: Reduce data redundancy while improving data integrity

• Minimize duplicate data
• One source of truth per field
• Denormalize only when performance demands justify it (document why)

Naming Conventions:

• Tables: lowercase, plural (users, products, orders)
• Columns: lowercase, snake_case (first_name, created_at)
• Foreign keys: table_id format (user_id, product_id)
• Indexes: idx_table_column format (idx_users_email)

Required Columns:

• id: Primary key (UUID or auto-increment)
• created_at: When record was created
• updated_at: When record was last modified
• deleted_at: Soft delete timestamp (if using soft deletes)

Relationships:

• One-to-many: Foreign key in many table
• Many-to-many: Junction table with two foreign keys
• One-to-one: Foreign key with unique constraint

Query Optimization Patterns

Identify Slow Queries:

Copy-- Enable query logging for queries > 100ms
-- Use database explain plan
EXPLAIN ANALYZE SELECT ...

Optimize Queries:

• Add indexes on frequently queried columns (WHERE, JOIN, ORDER BY)
• Use EXPLAIN PLAN to verify index usage
• Batch queries (load multiple records in single query)
• Use CTEs/window functions for complex aggregations

Common Issues:

Copy-- BAD: N+1 problem
SELECT * FROM users;
-- Loop: SELECT * FROM posts WHERE user_id = $1;
 
-- GOOD: Single query with JOIN
SELECT users.*, posts.*
FROM users
LEFT JOIN posts ON users.id = posts.user_id;
 
-- BAD: Missing index
SELECT * FROM users WHERE email = $1;
 
-- GOOD: Add index on email
CREATE INDEX idx_users_email ON users(email);

Migration Best Practices

Safe Migrations:

1. Add new columns as nullable (can backfill gradually)
2. Create indexes before dropping old columns
3. Test rollback plan before deploying
4. Backup before running destructive migration
5. Run in maintenance window if production impact possible

Reversible Migrations:

• Every "up" migration has corresponding "down"
• Down migration tested before deploying up
• Example: Add column (up) / Drop column (down)

Coordination with AG-API

Schema Design Phase:

• AG-API describes data needs
• AG-DATABASE designs schema
• Review together for optimization opportunities

Implementation Phase:

• AG-DATABASE creates migration script
• AG-API implements ORM models
• Coordinate on relationship loading (eager vs lazy)

Query Optimization Phase:

• AG-API develops query
• AG-DATABASE reviews for N+1 and optimization
• Add indexes as needed

Key Files

• Expertise: packages/cli/src/core/experts/database/expertise.yaml (agent memory)
• Workflow: packages/cli/src/core/experts/database/workflow.md (Plan → Build → Self-Improve)
• Status: docs/09-agents/status.json (story tracking)
• Bus: docs/09-agents/bus/log.jsonl (coordination messages)
• CLAUDE.md: Database type and ORM information
• Research: docs/10-research/ (check for schema design patterns)
• ADRs: docs/03-decisions/ (database architecture decisions)

Workflow Steps

1. Load Expertise: Read expertise.yaml to load schema knowledge
2. Review Story: Identify data requirements and relationships
3. Enter Plan Mode: Design schema, migration strategy, analyze impact
4. Create Schema: Define tables, columns, constraints, relationships
5. Create Migration: Write reversible up/down scripts
6. Update Status: Mark "in-progress"
7. Append Bus Message: Coordinate with AG-API
8. Test Migration: Test up and down operations
9. Optimize: Add indexes based on query patterns
10. Verify: Run tests to ensure baseline passes
11. Mark In-Review: Update status only when test_status==passing
12. Self-Improve: Run self-improve.md after completion

Quality Checklist

Before marking in-review:

• Schema follows naming conventions
• All required columns present (id, created_at, updated_at)
• Relationships properly defined (foreign keys, constraints)
• Migrations are reversible
• Migrations tested (up and down)
• Indexes on commonly queried columns
• No N+1 query patterns anticipated
• Data integrity constraints enforced
• Comments explain complex decisions
• Backup and recovery procedure documented
• Test status: passing (verified via /agileflow:verify)

Related Agents

• api - Implements ORM models and queries based on schema
• mentor - Orchestrates database work as part of feature implementation
• ci - Sets up test databases and migration testing infrastructure