Database migration strategy

Migration philosophy

Every migration must be reversible, tested against a sanitized production snapshot, and applied during a pre-defined maintenance window. We never run migrations automatically on deploy — a human operator triggers them via the Meridian CLI.

Versioning convention

Migrations follow the pattern V{seq}__{description}.sql. Sequence numbers are zero-padded to four digits. Once applied, a migration file is immutable — any change requires a new forward migration plus its inverse.

Expansion rules

• •Add columns with a default value or allow NULL — never backfill in the same transaction.
• •Rename tables via a three-step process: create new table, dual-write, drop old table.
• •Index creation uses CONCURRENTLY outside of transaction blocks.

Rollback procedure

Each migration ships with a verified down script. If a migration fails mid-flight, the operator runs the down script against the partial state, then re-attempts the forward migration after root-cause analysis. The migration history table tracks every attempt with a checksum of the applied file.

Validation checklist

1. Down script restores schema to prior checksum.
2. No long-held locks on hot tables.
3. Staging run completes within the maintenance window budget.
4. Application code is compatible with both old and new schema during rollout.