Agent Skills: Flyway Migration Consolidation

Flyway Migration Consolidation

Analyze incremental Flyway migrations and generate consolidated, domain-grouped CREATE TABLE migrations for pre-production projects where the database can be reset from scratch.

When to Use

| Scenario | Apply? | |----------|--------| | Pre-production project with migration sprawl | Yes | | Database can be reset from scratch | Yes | | Many incremental ALTER TABLE migrations | Yes | | Want domain-based organization before release | Yes | | Production database exists | No | | Migration history must be preserved | No |

Consolidation Workflow

1. Discover — Find all V*__*.sql files using Glob
2. Analyze — Read each migration, identify CREATE/ALTER/INSERT operations and affected tables
3. Infer final schema — Apply all changes in order to determine the intended final state
4. Group by domain — Organize tables into logical business domains
5. Resolve dependencies — Topological sort by FK relationships
6. Generate — Produce clean CREATE TABLE migrations when user confirms

See WORKFLOW.md for detailed step-by-step process.

Output Structure

Produce these deliverables in order:

1. Analysis Report

• Total migration count and breakdown by type (CREATE, ALTER, INSERT)
• Per-migration summary: what it does, which tables it affects
• Final table count and column inventory

2. Domain Grouping

• Tables organized by inferred business domain
• Migration-to-domain mapping showing which originals feed into each group

3. Proposed Structure

• New migration file list (e.g., V1–V6) with table assignments
• Dependency order rationale
• Reduction metrics (file count, estimated line savings)

4. Consolidated SQL (on request)

• Clean CREATE TABLE statements with final-form columns and constraints
• Separate migration for idempotent seed data
• Optional separate migration for performance indexes

Domain Grouping Heuristics

| Signal | Assignment | |--------|-----------| | Table prefix (user_*, order_*) | Prefix-based domain | | Foreign key cluster | Related tables share domain | | Join tables (user_roles) | Domain of primary entity | | Audit tables (*_audit, *_history) | Same domain as parent | | Config/settings tables | Infrastructure domain | | Explicit schema namespaces | Schema name as domain |

Present ambiguous cases to the user for decision.

Critical Constraints

1. Preserve the final schema exactly — no tables, columns, constraints, or relationships lost
2. Idempotent seed data — use ON CONFLICT DO NOTHING or equivalent for INSERT statements
3. Dependency order — referenced tables created before foreign keys that point to them
4. Prefer CREATE over ALTER — final-form table definitions, not incremental changes
5. History rewriting allowed — pre-production only, database will be reset
6. Document assumptions — call out any ambiguities in the original migrations explicitly

Tools

• Glob **/V*__*.sql and **/R*__*.sql to find versioned and repeatable migrations
• Read each migration file to parse SQL content
• Grep CREATE TABLE, ALTER TABLE, FOREIGN KEY, INSERT INTO to search across migrations

Examples

See EXAMPLES.md for complete before/after consolidation scenarios:

• Column evolution chains collapsed into single CREATE TABLE
• Multi-domain consolidation (40 migrations to 6)
• FK dependency resolution across domains
• Seed data made idempotent

Reminders

1. Always present the analysis report and proposed structure before generating SQL
2. Wait for user confirmation of domain groupings before generating consolidated files
3. Handle circular FK dependencies by deferring constraint creation with ALTER TABLE
4. Self-referential FKs: create table first, add FK in same migration via ALTER
5. Compare final column/constraint inventory against originals as a verification step