Dev Handbook

0010-schema-changes-via-migrations

3 min read

ADR-0010: Schema changes only via versioned migrations; dev seeds in a profile-gated folder

  • Status: Accepted
  • Date: 2026-06-05
  • Scope: core (any backend owning a relational schema)

Context

Letting the ORM mutate the schema (ddl-auto: update) makes the database’s
shape an emergent property of whatever code last booted — unreviewable,
unreproducible, and divergent between environments. Expenses ran this way early
on and the practice was abandoned; its leftover docs described a schema that no
longer existed. Separately, local development needs seed data (an admin user, a
demo account) that must never reach production.

Decision

  • The ORM never touches the schema: ddl-auto: validate. Hibernate (or
    equivalent) only checks that entities match the database.
  • Every schema change is a versioned migration (Flyway V*.sql or
    equivalent), reviewed in the same PR as the code that needs it.
  • An applied migration is immutable — once a V* file has been committed or
    run, its Flyway checksum is frozen; never edit it. Any later change (even
    adding a single audit column) is a new, higher-numbered migration, not an
    edit of the old one. Editing an already-applied file breaks checksum validation
    on every environment that ran it. (This bit us: an audit column had to ship as a
    new V2, not a tweak to V1.)
  • Glob the existing V* files before creating a new one — the next number
    is derived from what’s on disk, never guessed from memory.
  • A new lifecycle state or model change backfills existing rows in the same
    migration, so historical data matches the new model
    (0001-status-as-source-of-truth).
  • Dev-only seeds live in a separate migration folder, gated by profile —
    numbered far above the real sequence so they can never collide. In Expenses:
    db/dev-migration/V900__seed_dev_admin.sql, included via
    spring.flyway.locations only when profile=dev. Production never sees the
    folder; docker compose down -v + restart recreates the seeds.
  • Dev seed scripts are idempotent (INSERT ... ON CONFLICT DO NOTHING, or an
    existence guard) so re-running them against an already-seeded database is safe
    and never errors on duplicates.

Consequences

  • The schema is reviewable history; any environment can be rebuilt from zero.
  • Slightly more ceremony per change (write the migration, mind the numbering,
    never edit an applied file) — the cost that makes the database trustworthy.
  • Dev convenience data is structurally incapable of leaking to production.

Amendment — 2026-09-10: the dev profile runs Flyway out of order

The dev-seed rule above has a consequence it never stated. A dev database has already
applied V900, so every real migration written afterwards (V5, V76, …) has a lower
version than one already applied. Flyway applies in order only, so validation fails at
startup and the new migration never runs.

  • The dev profile sets spring.flyway.out-of-order: true, next to the dev-seed
    location. The base profile never does: production never sees V9xx, and its
    migrations must always apply in order, so an out-of-order version there is a mistake to
    catch, not accept.
  • Evidence: Expenses (application-dev.yml) and dracomania (application-dev.yaml) both
    carry the pair, each with a comment explaining it.

Graph View

Backlinks