Database

• Schema changes are breaking changes — treat them with the same care
• Every schema change needs a migration
• Every migration needs a rollback plan

Schema Design Rules

Naming conventions

Required columns on every table

id          -- Primary key (UUID or auto-increment)
created_at  -- When the record was created (UTC timestamp)
updated_at  -- When the record was last modified (UTC timestamp)

Soft delete pattern

Prefer soft deletes over hard deletes for important data:

deleted_at  -- NULL if active, timestamp if soft-deleted

Always filter by WHERE deleted_at IS NULL in queries (or use a view/scope).

Migrations

Rules

• One change per migration — don't bundle unrelated schema changes
• Always write both up and down — every migration must be reversible
• Never modify a deployed migration — create a new migration instead
• Test migrations against real-sized data — tiny test DBs hide performance problems
• Name migrations descriptively — add_email_index_to_users not migration_042

Migration checklist

• Has both up and down (forward and rollback)
• Tested with existing data (not just empty tables)
• Doesn't lock tables for extended periods on large datasets
• Backward compatible (old code can still work during deploy)
• Documented in the exec plan decision log

Zero-downtime migration pattern

For breaking schema changes, use the expand-contract pattern:

1. Expand — add new column/table alongside old one
2. Migrate — backfill data, update code to write to both
3. Switch — update code to read from new location
4. Contract — remove old column/table after verification

Query Patterns

Rules

• Always parameterize queries — never string-concatenate user input
• Select only needed columns — no SELECT * in application code
• Paginate large result sets — never return unbounded rows
• Use transactions for multi-step operations — ensure atomicity
• Index columns used in WHERE, JOIN, ORDER BY — but don't over-index

Query performance checklist

• Uses indexes effectively (check EXPLAIN plan)
• No N+1 query patterns (use JOINs or batch loading)
• Results are paginated (limit + offset or cursor-based)
• Large operations use batch processing
• Timeouts configured for long-running queries

Pagination

Prefer cursor-based pagination for large/changing datasets:

-- Instead of: OFFSET 1000 LIMIT 20 (slow for high offsets)
-- Use: WHERE id > :last_seen_id ORDER BY id LIMIT 20

Use offset pagination only for small, static datasets.

Indexing Strategy

When to add an index

When NOT to add an index

• Tables with < 1000 rows (full scan is faster)
• Columns with very low cardinality (e.g., boolean flags)
• Write-heavy tables where index maintenance outweighs read benefits
• Columns rarely used in WHERE/JOIN/ORDER BY

Data Integrity

Constraints (use the database, not just the application)

• NOT NULL — unless the column is genuinely optional
• UNIQUE — for columns that must be unique (email, username, slug)
• FOREIGN KEY — for all references between tables
• CHECK — for value constraints (e.g., quantity > 0, status IN (...))
• DEFAULT — for columns with sensible defaults

Validation layers

1. UI — immediate feedback (client-side)
2. API — schema validation at the boundary
3. Database — constraints as the last line of defense

All three layers should agree, but the database is the ultimate truth.

Backup & Recovery

• Define backup frequency per data criticality
• Test restore procedures regularly
• Document recovery time objectives (RTO) and recovery point objectives (RPO)
• Store backups in a different region/zone than primary data

PR Checklist for Database Changes

• Migration has both up and down
• Migration tested with existing data
• No SELECT * in new queries
• Queries are parameterized (no SQL injection risk)
• Appropriate indexes added for new queries
• Constraints added (NOT NULL, UNIQUE, FK, CHECK)
• Backward compatible during deployment
• Performance tested with realistic data volume
• Rollback plan documented in exec plan