Compare commits

...
Author SHA1 Message Date
Connor Yoh 92d3e009ef @
SaaS Flyway: switch new migrations to timestamp-based naming

The shared integer version counter (V25, V26, ...) collides whenever two
branches are in flight: each grabs "the next number" and they clash on merge,
breaking CI. Adopt UTC-timestamp naming (VYYYYMMDDHHMMSS__desc.sql) for new
migrations so parallel branches never pick the same version.

Enable spring.flyway.out-of-order so a just-merged migration whose timestamp
predates one already applied is still run (merge order != version order under
timestamps). Safe because the saas migrations are additive and idempotent.

Existing V2..V26 files are intentionally left as-is: they are already applied
and tracked by version in flyway_schema_history, so renaming would re-run them
or fail checksums. Timestamps sort after the legacy integers, so both coexist.

Convention documented in db/migration/saas/README.md.
@
2026-07-03 15:49:16 +01:00
2 changed files with 53 additions and 0 deletions
@@ -23,6 +23,12 @@ spring.flyway.baseline-on-migrate=true
spring.flyway.locations=classpath:db/migration,classpath:db/migration/saas
spring.flyway.schemas=stirling_pdf
spring.flyway.default-schema=stirling_pdf
# New migrations are named by UTC timestamp (VYYYYMMDDHHMMSS__desc.sql) instead of a
# shared integer counter, so parallel branches never collide on "the next number". Merge
# order then no longer matches version order, so out-of-order must be on or Flyway would
# refuse a pending migration whose timestamp predates one already applied. Safe here: the
# saas migrations are additive and idempotent (IF NOT EXISTS). See db/migration/saas/README.md.
spring.flyway.out-of-order=true
# ---------- Supabase JWT auth ----------
# Required: set SAAS_DB_PROJECT_REF via env.
@@ -0,0 +1,47 @@
# SaaS Flyway migrations
These are the incremental schema deltas Stirling applies **on top of** the base tables
(`users`, `teams`, …) that Supabase provisions in production. Supabase is the source of
truth for the schema; these migrations keep the Java backend and its tests in step with it.
## Naming: use a UTC timestamp, not the next integer
New migrations are named by the UTC time they were authored:
```
V<YYYYMMDDHHMMSS>__short_description.sql
```
Example:
```
V20260703143012__resource_grants.sql
```
Get the prefix with:
```bash
date -u +V%Y%m%d%H%M%S
```
### Why
The old scheme used a shared integer counter (`V25__…`, `V26__…`). Two branches in flight
each grabbed "the next number", so they collided the moment both merged — a duplicate
version or a checksum failure that broke CI. Timestamps are per-author and monotonic, so
independent branches never pick the same one (a clash needs two authors in the same second).
A 14-digit timestamp sorts numerically after the legacy integers (`26 < 20260703143012`),
so the two schemes coexist and apply in the right order.
Because merge order no longer matches version order, `spring.flyway.out-of-order=true` is set
in `application-saas.properties` — without it Flyway would reject a just-merged migration
whose timestamp predates one already applied. This is safe only because these migrations are
**additive and idempotent** (`ADD COLUMN IF NOT EXISTS`, `CREATE … IF NOT EXISTS`). Keep new
migrations that way.
## Do not rename the existing integer-versioned files
`V2__…` through `V26__…` are already applied in deployed and dev environments and are tracked
by version in `flyway_schema_history`. Renaming them would make Flyway treat them as new
(re-run) or fail the checksum check. Leave them as-is; only new migrations use timestamps.