git:20260726.72e37c1 to git:20260812.eb6a1db

12 added, 1 removed. Audit A to A.

---

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

+ ## 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** — follow `V300000233`–`V300000235`: wrap the DML in `WHERE ${tz_repair}` (or a dedicated placeholder). Default `spring.flyway.placeholders.tz_repair: "1=0"` in `application.yml` makes it a no-op in dev/test/CI; enable (`1=1`) only via a system property on the production deploy that ships the migration (see `mig-zulu25-openai-app-instance-startup.sh`). Add a migration test that resolves the placeholder to `1=0` and proves zero rows change (see `NoteTimeZoneRepairMigrationTest`).

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

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