---
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: `V300000231__rename_a_column.sql` (version must exceed **`300000230`**)

## 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`**; **`V300000230__db_migration_placeholder.sql`** is a placeholder for the next migration.

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

### 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).
* **`V300000230__db_migration_placeholder.sql`** is the tail placeholder; 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 migration you will delete**. Reusing 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 existing migration version (SQL under `resources/db/migration/` and Java under `java/db/migration/`).
2. Add a no-op tip placeholder, 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).

## 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.