---
description: Database migration, Flyway, SQL migrations, schema changes, database versioning, migration files, squashing historical migrations
alwaysApply: false
---
# 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: `V300000330__rename_a_column.sql` (version must exceed **`300000329`**)

## 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`**; **`V300000329__ReplaceNoteTitleFunctionalIndex.java`** is the current tip.

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

### 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).
* For unit tests, DB migration is included in the test command (see `backend-development` rule for test execution)

## Migration file structure

* **`V100000000__baseline.sql`** holds the collapsed full DDL (fresh installs).
* **`V300000329__ReplaceNoteTitleFunctionalIndex.java`** is the current tip; add new migrations with a higher version.
* Each **new** file after that 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.