Database Schema Versioning

Each module owns its schema and evolves it via Flyway. Change control is strict: no manual DDL, no shared write tables, and every migration must be deterministic, reversible (when possible), and tested with real MySQL 9.5.0.

Flyway per module
No cross-module FK
Immutable migrations
CI validation

Ownership & layout

  • Each module maintains its own Flyway directory: modules/<name>/src/main/resources/db/migration.
  • Filename pattern: V<YYYYMMDDHHmm>__<description>.sql (zero-padded UTC timestamps).
  • Schema name convention: <module>_svc; never share schemas.
  • Migrations include ASSIGNMENT comments describing purpose + ticket.
  • Baseline scripts live in V000000000000__baseline.sql and are immutable once merged.
modules/audit/src/main/resources/db/migration/
  V202501011030__create_audit_tables.sql
  V202501051200__add_finding_history.sql
modules/inspection/src/main/resources/db/migration/
  V202501021000__init_inspection_tables.sql

Review checklist

  • Migrations are additive; destructive changes use blue/green strategy (create new tables, backfill, swap).
  • All columns specify charset/collation; timestamps use TIMESTAMP(6) with UTC default.
  • Foreign keys only reference tables inside the same module schema.
  • Indexes include explicit names (idx_module_column).
  • Every change comes with integration tests that exercise new columns/constraints.

Operational discipline

  • Schema migrations run during deployment; never run ad-hoc SQL in production.
  • Rollback plan required for every breaking change (e.g., deploy new code that reads both schemas before dropping old columns).
  • Backups verified weekly; restore rehearsals before any major schema refactor.
  • Audit log of Flyway runs stored in flyway_schema_history per schema; do not truncate.
  • DB access limited to CI/CD runners + DBAs; developers use Testcontainers for real integration tests.