--- name: mav-bp-database-management description: Database and data management conventions for all projects using databases. Covers schema migrations, backup strategy, data lifecycle, index management, and connection pooling. Applied when designing, implementing, or reviewing database interactions. --- # Database & Data Management Standards Ensure all database interactions are safe, reproducible, and well-managed. Covers schema migrations, backup strategy, data lifecycle, index management, and connection pooling. ## Principles 1. **Migrations are versioned and reproducible** — every schema change is a migration file checked into version control, producing the same result on every environment 2. **Forward-only in production** — never roll back a production migration manually; write a new forward migration to undo changes 3. **Every migration is reversible** — each migration must include an up and a down operation, even if the down is only used in development 4. **No manual schema changes** — all schema modifications go through the migration pipeline; never run ad-hoc DDL against any shared environment 5. **Data isolation between environments** — development, staging, and production databases are completely separate; never share a database across environments ## Project Implementation Lookup Before applying these standards, load the project-specific database management implementation: ```dot digraph lookup { "docs/maverick/skills/database-management/SKILL.md exists?" [shape=diamond]; "Read and use alongside these standards" [shape=box]; "Invoke upskill" [shape=box]; "Read generated skill" [shape=box]; "docs/maverick/skills/database-management/SKILL.md exists?" -> "Read and use alongside these standards" [label="yes"]; "docs/maverick/skills/database-management/SKILL.md exists?" -> "Invoke upskill" [label="no"]; "Invoke upskill" -> "Read generated skill"; "Read generated skill" -> "Read and use alongside these standards"; } ``` 1. Check for `docs/maverick/skills/database-management/SKILL.md` 2. If missing, invoke the `do-upskill` skill with: - topic: database-management - scan hints: - dependencies: prisma, typeorm, sequelize, knex, drizzle, sqlalchemy, alembic, django, flyway, liquibase, diesel, sqlx, gorm, ecto - grep: `migration|migrate|createTable|addColumn|Schema\.create|db\.execute|pool\.query|connectionString|DATABASE_URL` - files: `**/migrations/**`, `**/db/**`, `**/database/**`, `**/schema.*`, `**/knexfile.*`, `**/prisma/schema.prisma` 3. Read the project skill and apply these best practices in the context of the project's specific technology ## Schema Migrations ### Migration File Structure Every migration must be a versioned file with a sequential or timestamp-based identifier: ``` migrations/ 001_create_users_table.sql 002_add_email_index.sql 003_create_orders_table.sql ``` Or with timestamps: ``` migrations/ 20240115100000_create_users_table.sql 20240116143000_add_email_index.sql 20240117091500_create_orders_table.sql ``` ### Migration Rules - Each migration file contains both **up** (apply) and **down** (revert) operations - Migrations run in order and are tracked in a migration history table - Never edit a migration that has already been applied to a shared environment — write a new migration instead - Test migrations against a copy of production data before deploying - Migrations must be idempotent where possible (use `IF NOT EXISTS`, `IF EXISTS` guards) ### Migration Tooling by Ecosystem | Ecosystem | Tool | Notes | | ----------- | ----------------------------- | ---------------------------------------------- | | Node.js | Prisma Migrate, Knex, TypeORM | Use the project's chosen ORM migration system | | Python | Alembic, Django Migrations | Auto-generate from models, review before apply | | Java/Kotlin | Flyway, Liquibase | SQL-based or XML/YAML changelogs | | Go | golang-migrate, goose | Plain SQL migrations | | Ruby | Active Record Migrations | Ruby DSL with reversible blocks | | Rust | Diesel, sqlx | Compile-time checked SQL | ### Dangerous Migration Patterns Avoid these patterns in production migrations: | Pattern | Risk | Safe Alternative | | --------------------------------- | --------------------------------- | -------------------------------------------------------- | | `DROP COLUMN` immediately | Breaks running application code | Deploy code that stops reading the column first | | `ALTER COLUMN` change type | Locks table, may lose data | Add new column, backfill, switch reads, drop old column | | `RENAME COLUMN` | Breaks running queries | Add new column, backfill, update code, drop old column | | Large `UPDATE` in migration | Locks table for extended period | Backfill in batches outside the migration | | `NOT NULL` on existing column | Fails if any rows have null | Backfill nulls first, then add constraint | ## Backup and Restore ### Backup Strategy - Automate database backups on a defined schedule (daily minimum, hourly for critical systems) - Store backups in a separate location from the database (different region, different account) - Encrypt backups at rest and in transit - Retain backups according to the data retention policy (e.g., daily for 30 days, weekly for 90 days, monthly for 1 year) - Include both full and incremental backups where the database supports it ### Testing Restores - Test restore procedures on a regular schedule (monthly minimum) - Document the restore procedure with exact commands and expected duration - Measure and record Recovery Time Objective (RTO) and Recovery Point Objective (RPO) - Automate restore testing where possible — a backup that has never been restored is not a backup ### Documented Procedures Maintain a runbook that covers: - How to trigger a manual backup - How to restore to a specific point in time - How to restore to a new environment (for disaster recovery) - Who has access to perform restores - Expected time for each operation ## Data Retention and Archival - Define a retention policy for every table or data category - Implement soft deletes where business rules require audit trails - Archive old data to cold storage (e.g., S3, GCS) rather than keeping it in the primary database - Schedule automated archival jobs that move data past its retention window - Ensure archived data remains queryable if business needs require it - Document retention periods and archival procedures ## Index Strategy ### Rules - **Do not add indexes speculatively** — index only columns that appear in `WHERE`, `JOIN`, `ORDER BY`, and `GROUP BY` clauses of actual queries - **Ensure every production query has appropriate indexes** — review query plans for table scans on large tables - **Composite indexes should follow the left-prefix rule** — order columns from most selective to least selective - **Monitor index usage** — remove unused indexes, as they slow down writes - **Avoid over-indexing** — each index adds overhead to every `INSERT`, `UPDATE`, and `DELETE` ### Index Review Checklist | Check | Action | | ---------------------------------------- | -------------------------------------------------------- | | New query without index coverage | Add index or verify table is small enough for scan | | New index on a high-write table | Measure write performance impact before deploying | | Composite index with low-selectivity lead| Reorder columns for better selectivity | | Duplicate or overlapping indexes | Remove the redundant index | | Index on a boolean or low-cardinality column | Usually not useful — verify with query plan | ## Connection Pooling and Resource Management - Use connection pooling for all database access — never open a new connection per request - Set pool size based on the application's concurrency and the database's maximum connections - Configure connection timeouts and idle timeouts to prevent leaked connections - Monitor pool utilisation and alert on exhaustion - Use read replicas for read-heavy workloads where available ### Common Pool Configuration | Setting | Guideline | | -------------------- | -------------------------------------------------------------- | | `min` pool size | 2-5 connections for low-traffic services | | `max` pool size | Start with `(CPU cores * 2) + disk spindles`, tune from there | | `idleTimeout` | 10-30 seconds — release unused connections back to the pool | | `connectionTimeout` | 5-10 seconds — fail fast if pool is exhausted | | `maxLifetime` | 30 minutes — rotate connections to handle DNS/IP changes | ## Seed Data - Provide seed data scripts for development and test environments - Seed data must be clearly separated from production data and migration scripts - Seed scripts must be idempotent — running them multiple times produces the same result - Include realistic but synthetic data (never copy production data into seed files) - Document how to run seed scripts in the project's README or contributing guide ## Environment Isolation - **Never share a database between environments** — development, staging, and production each have their own database instance - **Never connect to production from a local machine** — use bastion hosts or admin tools with audit logging for production access - **Never copy production data to development** — use seed data or anonymised snapshots - **Use environment-specific connection strings** — stored in environment variables or secrets management, never hardcoded - **Apply migrations to each environment independently** — same migration files, separate execution ## Detecting Database Issues in Code Review When reviewing code, flag these patterns: | Pattern | Issue | Fix | | -------------------------------------------------- | ---------------------------------- | ------------------------------------------------------ | | Raw SQL without parameterised queries | SQL injection risk | Use parameterised queries or ORM | | Manual schema change (ad-hoc DDL) | Unreproducible, untracked change | Create a migration file | | Editing an already-applied migration | Breaks migration history | Write a new migration | | No index on columns used in WHERE/JOIN | Performance degradation at scale | Add appropriate index | | Opening connection per request (no pooling) | Connection exhaustion | Use connection pool | | Hardcoded connection string | Security risk, environment coupling| Use environment variables or secrets manager | | Missing down migration | Cannot revert in development | Add reversible down operation | | Production data in seed files | Data leak, privacy violation | Use synthetic data only | | `DROP TABLE` or `DROP COLUMN` without staged rollout| Breaks running application | Stage the change: remove code references first | | No transaction wrapping for multi-step migration | Partial application on failure | Wrap in transaction where the database supports it |