| name | java-database-migration-safety |
| description | Use this skill when statically reviewing a Flyway or Liquibase schema-migration PR for rolling or blue-green deploy safety — immutable applied-migration discipline (checksum/history-table drift on an edited already-applied migration), expand-contract phasing for column/table drops and renames, destructive DDL (drop column/table, type narrowing, NOT NULL without a default on a populated table) landing in the same release that stops using it, long-locking DDL on large tables, out-of-order migrations, non-idempotent repeatable migrations, and a stated rollback/backfill plan. Trigger when a user provides migration scripts or changelogs, a schema diff, or asks whether a migration is safe to ship, safe to roll back, or safe to run alongside an older application version during rollout. Reads migration files and sanitized schema/config only; it never opens a database connection, runs a migration, or queries live schema state. |
| allowed-tools | Read Grep Glob |
| metadata | {"author":"github: Raishin","version":"0.1.0","updated":"2026-07-17","category":"database","lifecycle":"experimental"} |
java-database-migration-safety
Purpose
This skill statically reviews Flyway/Liquibase schema migrations for deploy safety under rolling and blue-green rollout, where two application versions run against one shared schema at the same time. A migration is only safe if it never edits an already-applied versioned migration, follows expand-contract phasing for any column or table drop or rename, keeps destructive DDL out of the release that still uses the shape being removed, avoids long-locking DDL on large tables without an online/concurrent variant, keeps repeatable migrations idempotent, preserves migration ordering across every environment, and states a rollback or backfill plan. The review catches checksum/history drift, premature drops paired with live usage, unphased renames, unsafe NOT NULL additions, lock-risk DDL, out-of-order migrations, and non-idempotent repeatable scripts before they reach a mixed-version production rollout.
Trigger conditions
- A user provides Flyway migration files (versioned V*, undo U*, or repeatable R__ — SQL or Java-based) or Liquibase changelogs (XML/YAML/JSON/formatted SQL) for review before merge or release.
- A user asks whether a schema migration is safe for a rolling or blue-green deploy, or whether it is safe to run while an older application version is still serving traffic against the same schema.
- A user asks about editing an already-applied migration, dropping or renaming a column/table, adding a NOT NULL constraint, or recovering from a failed or partially-applied migration.
When not to use
- The task is ORM fetch-strategy or query-shape correctness (N+1, JOIN FETCH vs @EntityGraph vs @BatchSize, DTO projections, HikariCP pool sizing) — route to java-jpa-hibernate-performance-agent.
- The task is @Transactional boundary, propagation, or isolation-level correctness — route to java-transaction-and-consistency-agent.
- The task requires actually connecting to a database, running a migration, measuring real lock duration, or inspecting live table size/row counts — out of scope for static review; describe what to measure and who should run it.
Lean operating rules
- CRITICAL — treat any edit to the body of an already-applied versioned migration (a run Flyway V*/U* file, or a Liquibase changeSet whose id/author/path was already applied) as a defect that produces a checksum mismatch or silent drift. Require a new forward migration; treat repair/checksum-clearing suggestions as removing the safety net, not fixing it.
- CRITICAL — treat destructive DDL (DROP COLUMN, DROP TABLE, type-narrowing ALTER COLUMN, NOT NULL added to a populated table without a DEFAULT) shipped in the same release as the last code path still using the old shape as unsafe for rolling/blue-green rollout. Require the code-removal release to fully roll out first.
- HIGH — require expand-contract phasing for every column/table drop or rename: expand (add new, dual-write), migrate (backfill, switch reads), contract (remove old reads/writes from code, roll out fully), then drop in a later release. Flag any migration that collapses these into one release.
- HIGH — treat a rename done as DROP+ADD, or a bare
renameColumn/renameTable with no compatibility step, as a rolling-deploy break for whichever version isn't yet redeployed; require the expand-contract sequence for renames too.
- HIGH — treat a migration with no stated rollback or backfill plan as incomplete. Flyway undo-migration availability is edition-dependent and changes over time — verify against current official documentation rather than assuming it, and mark the claim unknown if the edition is unstated.
- HIGH — treat DDL plausible or known to hold a long lock on a large table (non-online index add, full-table-rewrite ALTER) as a rolling-deploy risk needing an online/concurrent variant or a justified low-traffic window; without stated table size/engine version, label the risk inference, not confirmed.
- HIGH — treat an out-of-order migration (lower version number than one already applied in a target environment) as a hazard unless out-of-order application is deliberately enabled and verified against every environment's real applied-migration history.
- MEDIUM — treat a repeatable Liquibase changeSet or Flyway R__ script with non-idempotent DDL (unconditional CREATE/DROP/INSERT, no existence guard) as a defect since repeatable migrations rerun on checksum change and must tolerate reapplication.
- MEDIUM — require additive-first sequencing by default: new columns nullable/defaulted, new tables independent of any same-release drop. A PR that adds X and removes old X in one release is a same-release destructive-DDL violation.
- MEDIUM — treat NOT NULL added without a DEFAULT to a populated table as a defect; require a default value or a phased backfill-then-constrain sequence, and label engine-specific locking claims by evidence basis since engine/version is often unstated.
- LOW — flag DDL mixed with unbatched large-volume DML in one script as a lock-duration and rollback-blast-radius risk; recommend separating schema change from data backfill and batching the backfill.
References
Load these only when needed:
Response minimum
Return, at minimum:
- A verdict (pass / pass-with-conditions / block) and an evidence level (which migration files, schema snapshot, and application call sites were provided).
- Applied-migration integrity findings (edited already-applied migration, checksum/history drift, out-of-order migration, non-idempotent repeatable migration).
- Expand-contract and destructive-DDL findings (unphased drop/rename, same-release destructive DDL still in use, missing dual-write/backfill phase).
- Lock-risk and rollback/backfill findings (long-locking DDL on a large table, missing or unverified rollback/backfill plan).
- A severity-labelled finding list (critical / high / medium / low), each with an evidence-basis label.
- Safe next actions and open questions.
