db-audit

Database Audit

Scan the database schema and codebase for performance, design, and security issues.

Load the database-optimizer and database-designer skills for index analysis and schema design patterns.

Usage

/db-audit [--focus indexes|relations|queries|all]

Arguments

Parse $ARGUMENTS to extract:

• --focus (optional, default: all) — What to audit
  • indexes — Missing indexes, unused indexes, duplicate indexes
  • relations — Missing foreign keys, orphaned references, cascade issues
  • queries — N+1 patterns, unparameterized queries, full table scans in code
  • all — Run everything

Instructions

1. Gather Schema Information

Read the schema from:

• ORM schema files (Prisma, TypeORM, Knex, Drizzle, etc.)
• Migration files
• MCP database connection (if available — use for live index and stats data)

2. Index Audit

Check for:

• Missing indexes on foreign keys — Every _id / *_id column should have an index
• Missing indexes on commonly queried columns — Search code for WHERE clauses and match against indexed columns
• Composite index opportunities — Queries filtering on multiple columns
• Unused indexes (if MCP available) — Check pg_stat_user_indexes for zero-scan indexes
• Duplicate indexes — Indexes that are prefixes of other indexes
• Missing unique constraints — Columns that should be unique (email, slug, etc.)

3. Relation Audit

Check for:

• Missing foreign key constraints — Code references user_id but no FK defined
• Dangerous CASCADE rules — ON DELETE CASCADE on tables with important data
• Orphaned references — FK targets that don't exist
• Missing NOT NULL — Columns that are always populated but allow NULL
• Inconsistent naming — Mixed naming conventions (userId vs user_id)

4. Query Audit

Search the codebase for:

• N+1 query patterns — Queries inside loops, missing eager loading/joins

  # Look for patterns like:
  for user in users:
      posts = db.query("SELECT * FROM posts WHERE user_id = ?", user.id)
  

• String interpolation in queries — SQL injection risk

  # Dangerous:
  db.query(`SELECT * FROM users WHERE id = ${id}`)
  

• **SELECT *** — Missing column selection (over-fetching)
• Missing LIMIT — Unbounded queries
• Missing transactions — Multi-step writes without transaction wrapping