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.
Example
Real code from the repos — optional
Agents: safe to skip. The Decision above is the specification; everything below is
illustration. Skipping this block costs you nothing normative and saves tokens. Expand it
when you need the shape rather than the rule — porting the pattern to a new project, or
answering “what does this actually look like”.
-- V9: Backfill invoices for orphan installment expenses (series_id IS NOT NULL).-- These were created before the auto-invoice-linking fix in ExpenseService.save().---- Mirrors BillingCycleCalculator exactly:-- closing_this_month = MIN(closing_day, days_in_expense_month) [safeDate]-- if expense.date > closing_this_month:-- cycle_start = closing_this_month + 1 day-- cycle_end = closing_this_month + 1 month-- else:-- cycle_start = closing_this_month - 1 month + 1 day-- cycle_end = closing_this_month-- Step 1: Create missing invoices grouped by exact billing cycle.-- ON CONFLICT DO UPDATE adds the orphan amounts to an existing invoice's total.WITH expense_cycles AS ( SELECT e.payment_method_id, e.currency, ...
A migration is not only DDL. This one backfills history so old rows match a model
they predate — the counterpart to the rule in 0001-status-as-source-of-truth that a new lifecycle state comes with a migration for
existing data.
The header comment is doing real work: it states which Java class the SQL mirrors. When
logic exists in both places, the duplication is the risk, so the migration names its
source rather than leaving a reader to discover the drift later.
ON CONFLICT DO UPDATE makes it idempotent — re-running it adds nothing twice, which
is what lets the same migration run against a database that was partly fixed by hand.
The 2026-09-10 amendment — out of order, in dev only.
spring: # Adds a dev-only Flyway location. Anything under db/dev-migration runs only # when the dev profile is active; production deployments use the default # location list (db/migration) and never see these files. Use V900+ versions # to keep the dev range cleanly separated from real schema migrations. # # out-of-order is dev-only: V900 (dev seed) is already applied, so any new # db/migration file (V43+) lands "behind" it from Flyway's perspective. # Prod runs without this flag — production migrations must always be in order. flyway: locations: classpath:db/migration,classpath:db/dev-migration out-of-order: true
The two settings travel together, and the comment is the point: it says why the flag is
here and, more importantly, that it stops here. application.yml sets neither — production
never loads db/dev-migration, so it never sees a V9xx, and an out-of-order version
there is a mistake worth failing on.