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: V300000334__rename_a_column.sql (version must exceed 300000333)

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; V300000333__notebook_follows_folder_containment.sql is the newest file. Version 300000330 is retired — its migration was deleted after production applied it — and stays reserved in flyway_schema_history.

New migrations need to use a greater version number than 300000333.

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).
• V300000333__notebook_follows_folder_containment.sql is the newest file; 300000330 is retired and reserved (its migration file was removed after production applied it) — add new migrations with a version higher than 300000333.
• 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.