Advisor Database Champion

An agent that advocates for database best practices within development teams, providing guidance on query optimization, schema design decisions, migration safety, and database technology selection based on workload requirements.

When to Use This Agent

Choose Database Champion when:

• Evaluating database technology choices for new projects
• Reviewing schema designs and migration plans for safety
• Advising teams on query patterns and anti-patterns
• Establishing database coding standards and conventions
• Making build-vs-buy decisions for data infrastructure

Consider alternatives when:

• Doing hands-on DBA work (use a database admin agent)
• Writing specific complex queries (use a database-specific expert agent)
• Designing complete data architectures (use a data architect agent)

Quick Start

Copy
# .claude/agents/advisor-database-champion.yml
name: Database Champion
model: claude-sonnet-4-20250514
tools:
  - Read
  - Write
  - Bash
  - Glob
  - Grep
prompt: |
  You are a database champion who advocates for database best
  practices. Help teams choose the right database technologies,
  design safe migrations, write efficient queries, and establish
  coding standards. Always explain the "why" behind recommendations.

Example invocation:

Copy
claude --agent advisor-database-champion "We're choosing between
  PostgreSQL, MongoDB, and DynamoDB for our new user activity
  tracking system. Expected volume: 100M events/day with mostly
  time-range queries and user-based filtering."

Core Concepts

Database Technology Selection

Workload Type	Best Fit	Why
Relational with complex joins	PostgreSQL, MySQL	ACID, mature optimizer
Document-oriented, flexible schema	MongoDB	Schema flexibility, nested docs
Key-value at massive scale	DynamoDB, Redis	Single-digit ms at any scale
Time-series data	TimescaleDB, InfluxDB	Optimized for time partitions
Graph relationships	Neo4j, Neptune	Native graph traversal
Full-text search	Elasticsearch, Meilisearch	Inverted index, relevance scoring
Analytics/OLAP	BigQuery, ClickHouse	Columnar, aggregation-optimized

Query Anti-Pattern Guide

Copy
-- Anti-pattern: SELECT *
SELECT * FROM orders WHERE status = 'pending';
-- Better: Select only needed columns
SELECT id, customer_id, total FROM orders WHERE status = 'pending';

-- Anti-pattern: N+1 queries
for order in orders:
    items = db.query("SELECT * FROM items WHERE order_id = ?", order.id)
-- Better: Single query with JOIN or IN clause
SELECT * FROM items WHERE order_id IN (1, 2, 3, ...);

-- Anti-pattern: Missing index on filter column
SELECT * FROM events WHERE user_id = ? AND created_at > ?;
-- Better: Composite index matching query pattern
CREATE INDEX idx_events_user_date ON events(user_id, created_at);

Migration Safety Checklist

Copy
## Pre-Migration Review
- [ ] Migration tested on production-size dataset
- [ ] Rollback script written and tested
- [ ] Lock duration estimated for DDL operations
- [ ] Backward-compatible with current application version
- [ ] No destructive operations (DROP) in first deploy

## During Migration
- [ ] Maintenance window communicated (if needed)
- [ ] Monitoring active for lock waits and replication lag
- [ ] Rollback trigger criteria defined

## Post-Migration
- [ ] Application verified functional
- [ ] Query performance baseline comparison
- [ ] Statistics updated on affected tables

Configuration

Parameter	Description	Default
primary_database	Team's primary database	PostgreSQL
review_focus	Priority review areas	Performance, safety
migration_tool	Schema migration tool	Framework default
query_complexity_limit	Max acceptable query complexity	Medium
index_strategy	Indexing philosophy	Query-driven
naming_convention	Table/column naming standard	snake_case
review_checklist	Pre-deployment review items	Full safety check

Best Practices

1. Choose the database that fits your query patterns, not your data shape. A relational database can store documents (JSONB in PostgreSQL). A document database can model relationships (with $lookup). The deciding factor should be your most critical query patterns. If 80% of queries are time-range aggregations, use a time-series database even if the data could fit in a relational schema.

2. Enforce query review as part of code review. Every pull request that adds or modifies a database query should include the EXPLAIN plan for the new query. This catches missing indexes, unnecessary full table scans, and N+1 patterns before they reach production. Add linting rules for common anti-patterns (SELECT *, missing WHERE on UPDATE/DELETE).

3. Make migrations backward-compatible by default. Deploy new code that works with both old and new schemas before running the migration. This allows zero-downtime deployments and safe rollbacks. Add columns as nullable or with defaults before making them required. Rename columns using a two-phase approach: add new column, migrate data, deploy code using new column, remove old column.

4. Establish naming conventions and enforce them automatically. Inconsistent naming (userId vs user_id vs UserID) across tables creates confusion and bugs. Define conventions (snake_case for all identifiers, singular table names, _id suffix for foreign keys) and enforce them with linting or migration validation. Consistency matters more than which specific convention you choose.

5. Document the data model alongside the code. Include an entity-relationship diagram in the repository and update it when schemas change. Add comments to columns whose purpose isn't obvious from the name. Document non-obvious relationships (implicit foreign keys without constraints). A developer should be able to understand the data model from repository documentation without querying the database.

Common Issues

Team uses multiple databases without clear guidelines on which to use when. Create a decision matrix that maps use cases to databases. "User profiles and relationships → PostgreSQL. Session cache → Redis. Full-text search → Elasticsearch." Without guidelines, developers choose based on familiarity rather than fit, leading to the wrong database for the workload and data migration headaches later.

Migrations cause downtime or lock contention in production. Test migrations against a production-sized dataset to measure actual lock duration and execution time. Use non-blocking DDL operations where available (CREATE INDEX CONCURRENTLY in PostgreSQL). Schedule long-running migrations during low-traffic windows. For critical systems, implement blue-green database deployments where the migration runs on a copy.

Query performance degrades as data volume grows. Queries that work fine on 10,000 rows can become problematic at 10 million. Establish performance baselines early and monitor for regression. Partition large tables before they become unmanageable. Review query plans quarterly for your highest-frequency queries. The optimizer may choose different plans as data distribution changes, and what was fast six months ago may be slow today.