Skip to main content

ADR-0001: PostgreSQL 16 + Flyway versioned SQL migrations

Status: Accepted (retroactive) Date: 2026-07-24

Context​

The legacy JavaFX desktop app (archived README) ran on MySQL with a Hibernate-owned schema: entities were mapped in hibernate.cfg.xml and the database structure followed the Java classes, with seed data imported by hand. That works for a single-developer desktop app, but the web rewrite needed:

  • a reviewable, replayable schema history (the schema is now the product's compliance record — statutory rate cohorts, payroll snapshots);
  • schema changes that merge cleanly when several feature branches each add migrations in parallel (the dominant workflow in this repo);
  • a database that a fresh docker compose up can build from zero, identically, for dev, demo, CI, and every per-client production stack (ADR-0007).

Letting Hibernate generate DDL (hbm2ddl-style auto-DDL) fails all three: no history, no review, and silent drift between environments.

Decision​

  • PostgreSQL 16 (postgres:16 in every compose file) replaces MySQL.
  • Flyway owns the schema exclusively. 87 versioned SQL files in backend/src/main/resources/db/migration/, versions V1–V88, from V1__core_rbac.sql to V88__employee_holidays_view_permission.sql.
  • Hibernate never writes DDL: spring.jpa.hibernate.ddl-auto: validate in application.yml — startup fails if entities and schema disagree, instead of "fixing" it.
  • out-of-order: true and baseline-on-migrate: true (same file) so that a branch merged late — carrying, say, V83 after V85 already ran — still applies, and existing databases can adopt Flyway without a rebuild.
  • Demo stacks layer extra seed migrations via SPRING_FLYWAY_LOCATIONS rather than forking the schema (see architecture.md §5).

Consequences​

Positive

  • Every schema change is a reviewed SQL file in git; the full 91-table schema is reproducible from V1 on any empty Postgres.
  • ddl-auto: validate turns entity/schema drift into a boot-time error rather than a runtime surprise.
  • Parallel feature branches each ship their own migration file and merge without renumbering, which the team uses constantly.

Negative

  • No down-migrations. Flyway community edition has no undo here and none are written; a bad migration on a client stack is fixed by a new forward migration or a restore from backup.
  • Version gaps are tolerated forever: V26 was never used (the directory jumps V25 → V27), and nothing flags that as an error. Gaps are indistinguishable from "migration missing".
  • out-of-order: true weakens ordering guarantees. A migration can run after later-numbered ones already have, so migrations must not assume that higher-numbered state is absent — an invariant enforced only by convention.
  • Migration SQL is Postgres-specific (identity columns, TIMESTAMP defaults, views); a future database switch means rewriting history, not flipping a dialect flag.

References​