database

name	database
description	This skill should be used when reviewing database queries, migrations, indexes, or schema changes.
user-invocable	false
allowed-tools	Read, Grep, Glob

Database Patterns

Domain expertise for database design and optimization. Use alongside devflow:review-methodology for complete database reviews.

Iron Law

EVERY QUERY MUST HAVE AN EXECUTION PLAN

Never deploy a query without understanding its execution plan. Every WHERE clause needs an index analysis. Every JOIN needs cardinality consideration. "It works in dev" is not validation. Production data volumes will expose every missing index and inefficient join.

Database Categories

1. Schema Design Issues

Issue	Problem	Solution
Missing Foreign Keys	No referential integrity, orphaned records	Add FK with ON DELETE action
Denormalization	Unnecessary duplication, update anomalies	Normalize unless performance requires
Poor Data Types	VARCHAR for everything, lost precision	Use appropriate types (DECIMAL, BOOLEAN, TIMESTAMP)
Missing Constraints	No data validation at DB level	Add NOT NULL, CHECK, UNIQUE constraints

Example - Missing Constraints:

-- VIOLATION
CREATE TABLE products (id SERIAL, name VARCHAR(100), price DECIMAL);

-- CORRECT
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL CHECK (LENGTH(TRIM(name)) > 0),
  price DECIMAL(10, 2) NOT NULL CHECK (price >= 0)
);

2. Query Optimization Issues

Issue	Problem	Solution
N+1 Queries	Query per iteration, O(n) round trips	JOIN or batch with IN/ANY
Missing Indexes	Full table scans on large tables	Add indexes for WHERE/JOIN columns
Full Table Scans	Functions prevent index use	Functional indexes or query rewrite
Inefficient JOINs	Joining before filtering	Filter early, select specific columns

Example - N+1 Query:

// VIOLATION: 101 queries for 100 users
for (const user of users) {
  user.orders = await db.query('SELECT * FROM orders WHERE user_id = ?', [user.id]);
}

// CORRECT: 2 queries total
const orders = await db.query('SELECT * FROM orders WHERE user_id = ANY($1)', [userIds]);

3. Migration Issues

Issue	Problem	Solution
Breaking Changes	Data loss, no recovery path	Phased approach with backups
Data Loss Risk	Type changes truncate data	Validate before changing types
Missing Rollback	Cannot undo migration	Always implement down() method
Performance Impact	Table locks during migration	Add columns nullable, backfill in batches

Example - Safe Column Addition:

-- Step 1: Add nullable (instant)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Step 2: Backfill in batches
UPDATE users SET phone = 'UNKNOWN' WHERE phone IS NULL AND id BETWEEN 1 AND 10000;
-- Step 3: Add constraint after backfill
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

4. Security Issues

Issue	Problem	Solution
SQL Injection	String interpolation in queries	Parameterized queries only
Excessive Privileges	App has GRANT ALL	Minimum required privileges

Example - SQL Injection:

// VULNERABLE
const query = `SELECT * FROM users WHERE email = '${email}'`;

// SECURE
const query = 'SELECT * FROM users WHERE email = $1';
await db.query(query, [email]);

---

Extended References

For detailed examples and detection commands, see:

• references/violations.md - Extended violation examples with explanations
• references/patterns.md - Correct patterns and migration strategies
• references/detection.md - Automated detection commands

---

Severity Guidelines

Severity	Criteria	Examples
CRITICAL	Data integrity or severe performance	SQL injection, N+1 unbounded, data loss migrations, missing FK on critical relations
HIGH	Significant database issues	Inefficient JOINs, missing constraints, migrations without rollback
MEDIUM	Moderate concerns	Minor denormalization, missing non-critical indexes
LOW	Minor improvements	Naming conventions, index organization

---

Database Checklist

Before approving database changes:

• All queries have appropriate indexes
• N+1 patterns identified and resolved
• Migrations have rollback scripts
• Data types are appropriate
• Constraints enforce business rules
• Foreign keys maintain referential integrity
• No SQL injection vulnerabilities
• Performance tested with production-like data volume