db-review

Database Review Skill

Analyze database schemas, queries, migrations, and data access patterns for performance, safety, and operational excellence.

When to Use

This skill activates when:

• User requests "review my database", "db review", "query review"
• After writing new database migrations
• After adding new queries or ORM models
• When experiencing slow queries in production
• Before deploying schema changes to a large table
• When setting up database connection pooling

What It Does

Delegates to the db-reviewer agent (Opus model) for comprehensive database analysis:

1. Schema Review

   • Missing indexes on foreign keys and frequently queried columns
   • Missing constraints (NOT NULL, UNIQUE, CHECK, FOREIGN KEY)
   • Data type appropriateness (varchar size, numeric precision, timestamps with timezone)
   • Normalization level assessment
   • Soft delete vs hard delete patterns

2. Query Analysis

   • N+1 query detection (ORM lazy loading traps)
   • SELECT * usage in production code
   • Missing WHERE on UPDATE/DELETE (catastrophic risk)
   • Unbounded queries (no LIMIT on large tables)
   • Full table scans on indexed columns (implicit type coercion)
   • Transaction scope assessment (too broad = locks, too narrow = inconsistency)
   • Cartesian join detection

3. Migration Safety

   • Table lock risk assessment (ALTER TABLE on large tables)
   • Backward compatibility check (can old code still work?)
   • Data migration volume and timing estimation
   • Rollback strategy verification
   • Breaking change identification (column drops, type changes, constraint adds)
   • Zero-downtime migration patterns

4. Connection Management

   • Pool size vs expected concurrency
   • Connection timeout settings
   • Idle connection cleanup
   • Read replica configuration (if applicable)
   • Connection leak detection patterns

Agent Delegation

Task(
  subagent_type="oh-my-claudecode:db-reviewer",
  model="opus",
  prompt="DATABASE REVIEW TASK

Analyze database schemas, queries, and migrations for performance, safety, and reliability.

Scope: [specific files, tables, or entire data layer]

Review Checklist:
1. Schema design (indexes, constraints, data types)
2. Query patterns (N+1, SELECT *, unbounded queries)
3. Migration safety (locks, backward compat, rollback)
4. Connection management (pool size, timeouts)
5. Transaction handling (scope, isolation)

Output: Database review report with:
- Per-table schema assessment
- Query issues with file:line and performance impact
- Migration risk level per migration file
- Connection configuration recommendations
- Critical issues that must be fixed before deploy"
)

External Consultation (Optional)

Protocol

1. Form your OWN database review FIRST - Complete the analysis independently
2. Consult for validation - Cross-check findings via a Claude Task agent
3. Critically evaluate - Never blindly adopt external findings
4. Graceful fallback - Never block if delegation is unavailable

When to Consult

• Complex query optimization with multiple JOINs
• Migration safety on tables with millions of rows
• Sharding or partitioning strategy decisions
• Replication and consistency trade-offs

When to Skip

• Simple CRUD queries
• Small tables (<10k rows)
• Well-known ORM patterns
• Routine column additions

Output Format

DATABASE REVIEW REPORT
======================

Risk Level: HIGH
Tables Analyzed: 12
Queries Scanned: 45
Migrations Reviewed: 3

SCHEMA ISSUES
-------------
1. Table: orders
   Issue: Missing index on (user_id, created_at)
   Impact: Query at orderRepo.ts:89 does full table scan (O(n) → O(log n))
   Fix: CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

2. Table: products
   Issue: price column is FLOAT (floating-point arithmetic errors)
   Impact: Financial calculations have rounding errors
   Fix: ALTER TABLE products ALTER COLUMN price TYPE DECIMAL(10,2);

QUERY ISSUES
------------
1. src/services/userService.ts:67 — N+1 Query
   Pattern: Loop fetching profiles individually
   Impact: 1000 users = 1001 queries (~5 seconds)
   Fix: Use eager loading or JOIN

2. src/repositories/orderRepo.ts:34 — Unbounded Query
   Pattern: SELECT * FROM orders WHERE status = 'pending'
   Impact: Returns all pending orders (could be millions)
   Fix: Add LIMIT and cursor-based pagination

MIGRATION SAFETY
----------------
Migration: 20240115_add_email_unique.sql
  ⚠️  Lock Risk: HIGH — UNIQUE constraint on users table (5M rows)
  ⏱️  Estimated Time: ~30 seconds (table lock during index build)
  🔄 Backward Compatible: YES
  📋 Rollback: DROP INDEX idx_users_email_unique
  💡 Recommendation: Use CREATE UNIQUE INDEX CONCURRENTLY (PostgreSQL)

CONNECTION CONFIG
-----------------
  Pool Size: 5 (default) → Recommended: 20 (based on 50 concurrent requests)
  Idle Timeout: Not set → Recommended: 10000ms
  Connection Timeout: Not set → Recommended: 5000ms

CRITICAL ISSUES (MUST FIX)
--------------------------
1. Missing WHERE on UPDATE at adminService.ts:112 — can update ALL rows
2. No connection pool configured — each request creates new connection

Review Checklist

Schema

• All foreign keys have indexes
• Appropriate constraints (NOT NULL, UNIQUE, CHECK)
• No FLOAT/DOUBLE for monetary values (use DECIMAL)
• Timestamps use timezone-aware types
• Soft delete flag indexed if used in queries
• Primary keys are appropriate type (UUID vs auto-increment)

Queries

• No N+1 patterns (check ORM eager/lazy loading)
• No SELECT * in production queries
• All UPDATE/DELETE have WHERE clauses
• List queries have LIMIT/pagination
• No implicit type coercion in WHERE clauses
• Transactions have appropriate scope
• Prepared statements used (no string concatenation)

Migrations

• Large table alterations use online DDL
• New columns have defaults or are nullable
• Index creation uses CONCURRENTLY (PostgreSQL)
• Each migration has a rollback plan
• No data + schema changes in same migration

Connection Management

• Connection pool configured
• Pool size matches expected concurrency
• Connection and idle timeouts set
• Connection leak detection enabled
• Read replicas used for read-heavy workloads

Use with Other Skills

With ops-check:

/db-review then /ops-check

Database + operational readiness review.

With Ralph:

/ralph db-review then fix all query issues

Review, fix N+1s and missing indexes, re-verify.

With Team:

/team 3:executor "fix all N+1 queries found in db-review"

Parallel fix of query issues.

Best Practices

• Index early — Add indexes when you add queries, not when production is slow
• Migrate safely — Always check lock behavior on large tables
• Pool connections — Never use one-connection-per-request in production
• Paginate everything — No unbounded list queries
• Test at scale — Run queries against production-sized data in staging