---
name: db-migration
description: Database migration, Flyway, SQL migrations, schema changes, database versioning, migration files, squashing historical migrations. Use when adding or changing Flyway migrations or schema.
paths:
  - "backend/src/main/resources/db/**"
  - "backend/src/main/java/db/migration/**"
---
# Database Migration Guidelines

## When to Use This Rule

**Use this rule when:**
- Creating new database migrations
- Modifying database schema
- Understanding migration file naming conventions
- Working with Flyway migrations
- Troubleshooting migration issues
- Versioning database changes
- Squashing / collapsing historical migrations into the baseline

## Migration Tool

The project uses Flyway for database migrations, configured in Spring Boot.

## Migration Files Location

* SQL migrations: `backend/src/main/resources/db/migration/`
* Java migrations: `backend/src/main/java/db/migration/`
* Files follow the naming convention: `V{version}__{description}.sql` (or `.java`)
    * Example: `V300000350__rename_a_column.sql` (version must exceed **`300000349`**)

## Version Numbering

* Versions use a numerical format

The project uses versioned files named `V{number}__{description}.sql`. The current full application DDL is collapsed into **`V100000000__baseline.sql`**; the upgrade migrations after it run in order on every install, and **`V300000349__create_spring_session_tables.sql`** is the newest file. Versions **`300000330`** and **`300000339`** are retired — their migrations were deleted after production applied them — and stay reserved in `flyway_schema_history`.

New migrations need to use a **greater** version number than **`300000349`**.

### Migration Process

* Migrations run automatically when the application starts (non-test environments)
* Non-test startup runs **`flyway.repair()` then `flyway.migrate()`** (`FlyWayFreeVersionRealMigration`). `repair()` is what makes squashing safe on existing databases (checksum realign + remove history for deleted files). Deleting the **newest** applied migration file is different: until a newer version ships, databases that recorded it treat it as a future migration and keep its row unchanged; the next newer migration's startup then marks it deleted.
* For unit tests, DB migration is included in the test command (see `backend-development` rule for test execution)

### Release safety

* `FlyWayFreeVersionRealMigration` runs on `ApplicationReadyEvent`, **after** the instance is ready, so new code can serve requests before its own migrations finish. Ship a schema change that code must not see early in two releases: first the compatible schema change (for example relaxing a column to nullable while code still writes it), then the code that relies on it.
* A migration that throws there closes the application context and the JVM exits; production instance-group autohealing then restarts it in a loop, retrying after `repair()`, until the data is fixed. Production runs two instances and the deploy replaces them one at a time (`--max-surge 0 --max-unavailable 1`), so the other instance keeps serving; on a single instance the same failure is an outage.
* A destructive migration that must only run when data is ready should check that precondition first and throw before any DDL, as `V300000338__DropNotebookGitBindingBundleBytes` does.

## Migration file structure

* **`V100000000__baseline.sql`** holds the collapsed full DDL (fresh installs).
* The upgrade migrations after the baseline run in order; the newest file, the retired versions, and the next-version rule are under Version Numbering.
* Each **new** file should contain one atomic change (create/alter/rename/drop as needed).

## Best Practices:

* Each migration should be reversible when possible
* Migrations are version controlled and should never be modified once committed (except the intentional baseline content replace during a squash — see below)
* New changes should always be added as new migration files
* Clear, descriptive names should be used for migration files to indicate their purpose

## Squashing historical migrations

Rare maintenance: collapse applied migrations into the baseline so the repo keeps only baseline + tip placeholder (+ any newer work after the next tip). **Do not squash casually.** Production (and other long-lived DBs) survive because startup always **`repair()`s before `migrate()`**.

### Invariants

* **Tip placeholder first.** Add a **new** no-op placeholder whose version is **greater than every version ever applied** — not just the files currently in the repo. Spent migrations that were already deleted still own their version in every long-lived `flyway_schema_history`, so check git history and an existing database before picking. Reusing a version (or an old placeholder that already sits behind later versions) is wrong.
* **Deploy and confirm** that tip placeholder on production (and any other long-lived environments) before deleting files. Check `flyway_schema_history`.
* **Freeze** new schema migrations until the squash commit is deployed.
* **Keep the baseline version number** (`V100000000`). Replace **file contents only**. Renaming/renumbering the baseline makes Flyway treat it as a new pending migration and can run full `CREATE` DDL on an existing DB.
* **Dump at the tip.** Local schema must have applied through the new placeholder before dumping.
* **Delete SQL and Java** migrations strictly between baseline and the tip placeholder.
* Dump is **CREATE-only DDL, no data**; omit **`flyway_schema_history`** (match the header comment on the current baseline).

### Procedure

1. Find the highest version **ever used** — current files (SQL under `resources/db/migration/`, Java under `java/db/migration/`), versions deleted in git history, and `SELECT MAX(version) FROM flyway_schema_history` on a long-lived database.
2. Add a no-op tip placeholder above that, e.g. `V{max+1}__db_migration_placeholder.sql`, with a short comment that future migrations must use a greater version.
3. Commit, deploy, and confirm the placeholder row exists in production `flyway_schema_history`. Freeze further migrations.
4. On a local DB migrated through that tip, dump the full schema (no data). Prefer the same shape as the current baseline (CREATE statements; no `flyway_schema_history`).
5. Replace the **contents** of `V100000000__baseline.sql` with that dump (same filename/version). Delete every migration file (`.sql` and `.java`) with version **strictly between** baseline and the tip placeholder. Keep baseline + tip placeholder.
6. Update this rule’s baseline/placeholder version references and the “greater than” example so they match the new tip.
7. Commit, deploy the squash. Confirm startup succeeds and `flyway_schema_history` looks sane (baseline checksum repaired; deleted versions gone; tip still present).
8. Regenerate **`docs/database-erd.md`** if the collapsed schema should be re-exported (see below).

## Destructive DML (`DELETE`, gated `UPDATE`)

**Prefer not to ship one-off data cleanup in the permanent Flyway chain.** Cosmetic or historical row fixes belong in a one-time ops script, not a migration that runs on every startup forever.

If destructive DML **must** ship as a migration:

1. **Placeholder gate** — wrap the DML in `WHERE ${some_repair_gate}` and declare that Flyway placeholder in every application profile (`spring.flyway.placeholders`) with the default `1=0`, so the migration is a no-op in dev/test/CI. Enable it (`1=1`) only for the deliberate production deploy that ships the migration, then remove the production override after confirming Flyway applied it. Drop the placeholder from the profiles once the gated migration is spent. While the migration is pending, add a focused migration test that proves the default no-op and intended enabled selection; remove that migration-only test after successful production application because committed migrations are immutable and no product behavior depends on their test harness.
2. **Record the production row count** before enabling the gate.
3. **Walk the FK delete closure** before any `DELETE` — CASCADE edges extend the blast radius; `NO ACTION` / `RESTRICT` edges block it. Regenerate **`docs/database-erd.md`** and read delete-rule labels on every edge in the closure. For declared hard-delete roots, `DeletableEntityFkClosureTest` fails CI when a restricting FK enters the subtree.
4. **CI cannot validate DML alone** — `migrateTestDB` (see `ci.yml`) runs migrations against an **empty** database, so a `DELETE` that matches zero rows always passes. Row-selection tests and schema-structural guards are both required.

## Entity-relationship diagram

After adding or changing schema migrations, regenerate **`docs/database-erd.md`** so the Mermaid ERD stays aligned with Flyway. Follow the **`database-erd`** skill (`.agents/skills/database-erd/SKILL.md`): run `CURSOR_DEV=true nix develop -c pnpm export:database-erd` (or `python3 scripts/export_database_erd.py`) against a migrated local MySQL schema. Edge labels include `DELETE_RULE` — use them when reviewing any destructive change.