Database Review
Audit the database schema, query patterns, and migrations for correctness and performance.
Steps
-
Review the schema:
- Check that tables and columns use consistent naming conventions (snake_case preferred).
- Verify all tables have a primary key.
- Check that foreign keys have proper constraints (ON DELETE, ON UPDATE).
- Verify NOT NULL constraints are applied to required fields.
- Check that default values are set where appropriate.
- Ensure sensitive data columns (passwords, tokens) are not stored in plain text.
-
Check normalization:
- Identify denormalized data that could cause update anomalies.
- Verify no data is duplicated across tables without justification.
- Check that junction tables are used for many-to-many relationships.
- Note any intentional denormalization and confirm it is documented.
-
Review indexes:
- Verify indexes exist on all foreign key columns.
- Check that columns used in WHERE, JOIN, and ORDER BY have appropriate indexes.
- Identify missing indexes on frequently queried columns.
- Check for redundant or duplicate indexes that waste space.
- Verify composite indexes have columns in the correct order.
-
Identify N+1 query patterns:
- Search for loops that execute individual queries inside them.
- Check that related data is loaded with JOINs or eager loading, not lazy loading in loops.
- Verify batch operations are used for bulk inserts and updates.
-
Review query patterns:
- Check for SELECT * usage — only select needed columns.
- Verify pagination is used on list queries (no unbounded result sets).
- Check that complex queries use query builders or named queries, not string concatenation.
- Verify all queries use parameterized inputs (no raw string interpolation).
-
Review migrations:
- Check that migrations are sequential and do not conflict.
- Verify each migration has a corresponding rollback.
- Check that data migrations handle existing data correctly.
- Verify destructive migrations (DROP, ALTER column type) have a safety plan.
If Issues Are Found
- Fix N+1 queries and missing indexes immediately — these are performance-critical.
- Fix missing constraints in a migration with a rollback plan.
- Document schema inconsistencies and plan a migration to resolve them.
Output
A database review report listing schema issues, missing indexes, query anti-patterns, and migration concerns with recommended fixes.