Cursor rule

1---
2description: Database migration, Flyway, SQL migrations, schema changes, database versioning, migration files, squashing historical migrations
3alwaysApply: false
4---
5# Database Migration Guidelines
6 
7## When to Use This Rule
8 
9**Use this rule when:**
10- Creating new database migrations
11- Modifying database schema
12- Understanding migration file naming conventions
13- Working with Flyway migrations
14- Troubleshooting migration issues
15- Versioning database changes
16- Squashing / collapsing historical migrations into the baseline
17 
18## Migration Tool
19 
20The project uses Flyway for database migrations, configured in Spring Boot.
21 
22## Migration Files Location
23 
24* SQL migrations: `backend/src/main/resources/db/migration/`
25* Java migrations: `backend/src/main/java/db/migration/`
26* Files follow the naming convention: `V{version}__{description}.sql` (or `.java`)
27    * Example: `V300000231__rename_a_column.sql` (version must exceed **`300000230`**)
28 
29## Version Numbering
30 
31* Versions use a numerical format
32 
33The 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.
34 
35New migrations need to use a **greater** version number than **`300000230`**.
36 
37### Migration Process
38 
39* Migrations run automatically when the application starts (non-test environments)
40* 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).
41* For unit tests, DB migration is included in the test command (see `backend-development` rule for test execution)
42 
43## Migration file structure
44 
45* **`V100000000__baseline.sql`** holds the collapsed full DDL (fresh installs).
46* **`V300000230__db_migration_placeholder.sql`** is the tail placeholder; add new migrations with a higher version.
47* Each **new** file after that should contain one atomic change (create/alter/rename/drop as needed).
48 
49## Best Practices:
50 
51* Each migration should be reversible when possible
52* Migrations are version controlled and should never be modified once committed (except the intentional baseline content replace during a squash — see below)
53* New changes should always be added as new migration files
54* Clear, descriptive names should be used for migration files to indicate their purpose
55 
56## Squashing historical migrations
57 
58Rare 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()`**.
59 
60### Invariants
61 
62* **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.
63* **Deploy and confirm** that tip placeholder on production (and any other long-lived environments) before deleting files. Check `flyway_schema_history`.
64* **Freeze** new schema migrations until the squash commit is deployed.
65* **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.
66* **Dump at the tip.** Local schema must have applied through the new placeholder before dumping.
67* **Delete SQL and Java** migrations strictly between baseline and the tip placeholder.
68* Dump is **CREATE-only DDL, no data**; omit **`flyway_schema_history`** (match the header comment on the current baseline).
69 
70### Procedure
71 
721. Find the highest existing migration version (SQL under `resources/db/migration/` and Java under `java/db/migration/`).
732. 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.
743. Commit, deploy, and confirm the placeholder row exists in production `flyway_schema_history`. Freeze further migrations.
754. 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`).
765. 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.
776. Update this rule’s baseline/placeholder version references and the “greater than” example so they match the new tip.
787. Commit, deploy the squash. Confirm startup succeeds and `flyway_schema_history` looks sane (baseline checksum repaired; deleted versions gone; tip still present).
798. Regenerate **`docs/database-erd.md`** if the collapsed schema should be re-exported (see below).
80 
81## Entity-relationship diagram
82 
83After 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.
84