Flyway Consolidate

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

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

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 and TROUBLESHOOTING.md for common issues:

• 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

Flyway Migration Consolidation Examples

Complete before/after examples demonstrating consolidation patterns.

---

Example 1: Simple Table Evolution

A single table modified across 6 migrations.

Before: 6 Migrations

V1__create_users.sql

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(100) NOT NULL
);

V5__add_email_to_users.sql

ALTER TABLE users ADD COLUMN email VARCHAR(100);

V12__make_email_required.sql

ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users ADD CONSTRAINT uk_users_email UNIQUE (email);

V18__add_timestamps.sql

ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT NOW();
ALTER TABLE users ADD COLUMN updated_at TIMESTAMP DEFAULT NOW();

V30__increase_email_length.sql

ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);

V35__add_password.sql

ALTER TABLE users ADD COLUMN password_hash VARCHAR(255) NOT NULL;
ALTER TABLE users ADD CONSTRAINT uk_users_username UNIQUE (username);

After: 1 Consolidated Migration

V1__user_management.sql

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_users_username UNIQUE (username),
    CONSTRAINT uk_users_email UNIQUE (email)
);

Result: 6 migrations → 1, all ALTER TABLE eliminated, schema identical.

---

Example 2: Multi-Domain Consolidation

12 tables across 25 migrations consolidated into 5 domain-based files.

Before: Migration Summary

Domain Analysis

After: 5 Consolidated Migrations

V1__user_management.sql

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_users_username UNIQUE (username),
    CONSTRAINT uk_users_email UNIQUE (email)
);

CREATE TABLE roles (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    CONSTRAINT uk_roles_name UNIQUE (name)
);

CREATE TABLE user_roles (
    user_id BIGINT NOT NULL,
    role_id BIGINT NOT NULL,
    PRIMARY KEY (user_id, role_id),
    CONSTRAINT fk_user_roles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_roles_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
);

CREATE TABLE addresses (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    street VARCHAR(255),
    city VARCHAR(100),
    postal_code VARCHAR(20),
    is_default BOOLEAN NOT NULL DEFAULT FALSE,
    CONSTRAINT fk_addresses_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

V2__catalog.sql

CREATE TABLE categories (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    category_id BIGINT,
    CONSTRAINT chk_products_price CHECK (price > 0),
    CONSTRAINT fk_products_category FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE product_images (
    id BIGSERIAL PRIMARY KEY,
    product_id BIGINT NOT NULL,
    url VARCHAR(512) NOT NULL,
    CONSTRAINT fk_product_images_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE reviews (
    id BIGSERIAL PRIMARY KEY,
    product_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    rating INT NOT NULL,
    comment TEXT,
    CONSTRAINT fk_reviews_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    CONSTRAINT fk_reviews_user FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_reviews_product ON reviews(product_id);

V3__order_management.sql

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    address_id BIGINT,
    total_amount DECIMAL(12,2),
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT fk_orders_address FOREIGN KEY (address_id) REFERENCES addresses(id)
);

CREATE TABLE order_lines (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    CONSTRAINT fk_order_lines_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_order_lines_product FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE order_status_history (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL,
    status VARCHAR(50) NOT NULL,
    changed_at TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT fk_order_status_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

CREATE INDEX idx_orders_user ON orders(user_id);

V4__reference_data.sql

INSERT INTO roles (name) VALUES
    ('ADMIN'),
    ('USER'),
    ('MODERATOR')
ON CONFLICT (name) DO NOTHING;

Result: 25 migrations → 4 files, domain-grouped, all ALTER TABLE eliminated.

---

Example 3: Complex FK Dependencies

Tables with cross-domain foreign keys requiring careful ordering.

Before

-- V1: Users table
CREATE TABLE users (id BIGSERIAL PRIMARY KEY, name VARCHAR(255));

-- V4: Organizations table
CREATE TABLE organizations (id BIGSERIAL PRIMARY KEY, name VARCHAR(255));

-- V8: Users belong to organizations (FK added later)
ALTER TABLE users ADD COLUMN org_id BIGINT;
ALTER TABLE users ADD CONSTRAINT fk_users_org
    FOREIGN KEY (org_id) REFERENCES organizations(id);

-- V15: Projects reference both
CREATE TABLE projects (
    id BIGSERIAL PRIMARY KEY,
    org_id BIGINT NOT NULL,
    owner_id BIGINT NOT NULL
);

-- V16: Add FKs to projects
ALTER TABLE projects ADD CONSTRAINT fk_projects_org
    FOREIGN KEY (org_id) REFERENCES organizations(id);
ALTER TABLE projects ADD CONSTRAINT fk_projects_owner
    FOREIGN KEY (owner_id) REFERENCES users(id);

After: Dependency-Ordered Consolidation

-- V1__organizations.sql (no dependencies)
CREATE TABLE organizations (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- V2__users.sql (depends on organizations)
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    org_id BIGINT,
    CONSTRAINT fk_users_org FOREIGN KEY (org_id) REFERENCES organizations(id)
);

-- V3__projects.sql (depends on both)
CREATE TABLE projects (
    id BIGSERIAL PRIMARY KEY,
    org_id BIGINT NOT NULL,
    owner_id BIGINT NOT NULL,
    CONSTRAINT fk_projects_org FOREIGN KEY (org_id) REFERENCES organizations(id),
    CONSTRAINT fk_projects_owner FOREIGN KEY (owner_id) REFERENCES users(id)
);

Key: Migration order follows FK dependency chain: organizations → users → projects.

---

Example 4: Seed Data Made Idempotent

Before: Non-Idempotent Seed Data

-- V10__add_countries.sql
INSERT INTO countries (code, name) VALUES ('US', 'United States');
INSERT INTO countries (code, name) VALUES ('CA', 'Canada');
INSERT INTO countries (code, name) VALUES ('UK', 'United Kingdom');

-- V20__add_more_countries.sql
INSERT INTO countries (code, name) VALUES ('DE', 'Germany');
INSERT INTO countries (code, name) VALUES ('FR', 'France');

-- V28__fix_country_name.sql
UPDATE countries SET name = 'United Kingdom of Great Britain' WHERE code = 'UK';

After: Idempotent Consolidated Seed Data

V1__reference_tables.sql (schema)

CREATE TABLE countries (
    code VARCHAR(3) PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

V5__reference_data.sql (seed data)

INSERT INTO countries (code, name) VALUES
    ('US', 'United States'),
    ('CA', 'Canada'),
    ('UK', 'United Kingdom of Great Britain'),
    ('DE', 'Germany'),
    ('FR', 'France')
ON CONFLICT (code) DO NOTHING;

Notes:

• The UK name uses the final corrected value from V28
• ON CONFLICT (code) DO NOTHING makes this safe to run repeatedly
• Schema and data are in separate migrations

Flyway Consolidation Troubleshooting

Common issues when using the flyway-consolidate skill.

---

Migration Discovery Issues

No migrations found

Symptoms: Glob returns empty results.

Cause: Migration files are not in the expected location or don't follow Flyway naming conventions.

Fix:

• Verify migration directory: default is src/main/resources/db/migration/
• Check file naming: must match V*__*.sql (versioned) or R*__*.sql (repeatable)
• Custom migration locations are configured in application.yml under spring.flyway.locations

Repeatable migrations (R__) not included

Cause: The skill focuses on versioned migrations (V*__*.sql) for consolidation.

Fix: Repeatable migrations are typically kept as-is since they're idempotent by design. If you need to consolidate them, mention it explicitly.

---

Schema Analysis Issues

Circular foreign key dependencies

Symptoms: Cannot determine a valid creation order for tables.

Fix: The skill handles circular FKs by: 1. Creating tables without the circular FK constraint 2. Adding the constraint via ALTER TABLE after both tables exist

If the circular dependency is not detected, flag the specific tables.

Column type ambiguity from ALTER chains

Symptoms: Final column type unclear after multiple ALTER TABLE modifications.

Cause: A column was created, altered, and renamed across multiple migrations.

Fix: The skill applies migrations in version order to determine the final state. If the result looks wrong, verify by checking the actual database schema as the source of truth.

---

Safety Issues

Accidentally running on a production database

Symptoms: Consolidated migrations would destroy production migration history.

Fix: This skill is pre-production only. Never apply consolidated migrations to a database with existing Flyway history. Check:

• flyway_schema_history table exists → production database, do NOT consolidate
• The skill warns about this constraint in its "When to Use" section

Seed data not idempotent

Symptoms: INSERT statements fail on re-run with duplicate key errors.

Fix: The skill generates idempotent seed data using ON CONFLICT DO NOTHING (PostgreSQL) or equivalent. If using MySQL, ensure INSERT IGNORE or REPLACE INTO is used.

---

Output Issues

Domain grouping seems wrong

Symptoms: Tables assigned to unexpected domains.

Fix: Domain grouping uses heuristics (table prefixes, FK clusters). The skill presents groupings for confirmation before generating SQL. Reassign tables when prompted.

Generated SQL missing constraints

Cause: Some constraints (CHECK, UNIQUE) may be lost if they were added via ALTER TABLE and the migration was ambiguous.

Fix: Compare the generated schema against the original by running both sets of migrations against an empty database. Report any discrepancies.

Flyway Migration Consolidation Workflow

Step-by-step process for analyzing and consolidating Flyway migrations.

---

Step 1: Discover Migration Files

Find all Flyway migration files in the project:

Glob: **/db/migration/V*__*.sql
Glob: **/resources/db/migration/V*__*.sql
Glob: **/db/migration/R*__*.sql           # Repeatable migrations

For each file:

• Extract version number from filename (V1, V2, ... V40)
• Extract description from filename (e.g., V5__add_email_to_users.sql → "add email to users")
• Sort by version number ascending

Output: Ordered list of migration files with version and description.

---

Step 2: Analyze Each Migration

Read each migration file and classify its operations:

For each migration, produce a brief summary:

V5__add_email_to_users.sql:
  - ALTER TABLE users ADD COLUMN email VARCHAR(100)
  - Affects: users

---

Step 3: Infer Final Schema

Build the final schema by replaying all migrations in order:

Schema Tracking Model

For each table, maintain:

• Columns: name, data type, nullability, default value, constraints
• Table constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK
• Indexes: name, columns, uniqueness
• Created in: original migration version
• Modified in: list of migration versions that changed it

Resolution Rules

Verification

After replaying all migrations, produce a final schema inventory:

• Total tables with full column lists
• All constraints (PK, FK, UNIQUE, CHECK) with their definitions
• All indexes
• All custom types and extensions
• All seed data (final state)

---

Step 4: Group Tables by Domain

Organize tables into logical business domains using these heuristics (in priority order):

4a. Table Name Prefixes

Group tables sharing a common prefix:

user_*, users, user_profiles, user_addresses → "User Management"
order_*, orders, order_lines → "Order Management"
product_*, products, categories → "Catalog"

4b. Foreign Key Clusters

Tables connected by FK relationships belong together:

• order_lines.product_id → products.id connects orders and products
• Assign join tables to the domain of the "owning" side (e.g., user_roles → User Management)

4c. Functional Grouping

4d. Ambiguity Resolution

When a table could belong to multiple domains: 1. Use the strongest FK relationship as primary signal 2. If still ambiguous, present options to the user and ask them to decide 3. Document the decision in the analysis report

Output: Domain-to-tables mapping.

---

Step 5: Resolve Dependencies

Determine migration creation order using foreign key dependencies.

Topological Sort

1. Build a directed graph: edge from table A to table B if B has a FK referencing A 2. Topological sort produces creation order (referenced tables first)

Special Cases

Self-referential FKs (e.g., employees.manager_id → employees.id):

CREATE TABLE employees (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    manager_id BIGINT
);
ALTER TABLE employees ADD CONSTRAINT fk_employees_manager
    FOREIGN KEY (manager_id) REFERENCES employees(id);

Circular cross-table FKs (e.g., A references B, B references A):

-- Create both tables without the circular FK
CREATE TABLE table_a (id BIGSERIAL PRIMARY KEY, b_id BIGINT);
CREATE TABLE table_b (id BIGSERIAL PRIMARY KEY, a_id BIGINT);
-- Add FKs after both tables exist
ALTER TABLE table_a ADD CONSTRAINT fk_a_b FOREIGN KEY (b_id) REFERENCES table_b(id);
ALTER TABLE table_b ADD CONSTRAINT fk_b_a FOREIGN KEY (a_id) REFERENCES table_a(id);

Cross-domain FKs (e.g., orders.customer_id → users.id):

• The domain containing the referenced table must have a lower migration version
• Example: V2__user_management.sql before V4__order_management.sql

---

Step 6: Generate Consolidated Migrations

Migration Naming Convention

V1__infrastructure.sql
V2__user_management.sql
V3__catalog.sql
V4__order_management.sql
V5__reference_data.sql
V6__performance_indexes.sql

Adjust domain names and count based on the actual schema.

SQL Generation Rules

CREATE TABLE statements:

• Use final column definitions (all ALTER TABLE changes applied)
• Include inline column constraints (NOT NULL, DEFAULT, CHECK where single-column)
• Add table-level constraints (PK, FK, UNIQUE, multi-column CHECK)
• Use explicit constraint names: pk_{table}, fk_{table}_{column}, uk_{table}_{column}, chk_{table}_{description}

Foreign keys:

• Include ON DELETE / ON UPDATE clauses (use final version)
• Place as table-level constraints, not inline
• Order tables so referenced tables appear first in the file

Indexes:

• Place at the end of the domain migration or in a separate V*__indexes.sql
• Use explicit names: idx_{table}_{column}
• Include unique indexes that aren't already covered by UNIQUE constraints

Seed data:

• Separate migration file (e.g., V5__reference_data.sql)
• Use ON CONFLICT DO NOTHING for idempotency
• Requires a UNIQUE constraint on the conflict target column(s)
• Preserve data values exactly as in the final state

Custom types / extensions:

• Place in V1__infrastructure.sql before any tables that use them
• Include CREATE EXTENSION IF NOT EXISTS for PostgreSQL extensions

Example Output

-- V2__user_management.sql

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_users_username UNIQUE (username),
    CONSTRAINT uk_users_email UNIQUE (email)
);

CREATE TABLE user_profiles (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    bio TEXT,
    CONSTRAINT fk_user_profiles_user FOREIGN KEY (user_id)
        REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX idx_user_profiles_user_id ON user_profiles(user_id);

---

Step 7: Generate Analysis Report

Present this report to the user before generating any SQL files.

Report Template

## Flyway Migration Consolidation Report

### Current State
- **Total migrations**: N
- **Operations**: X CREATE TABLE, Y ALTER TABLE, Z INSERT
- **Total tables**: N
- **Domains identified**: N

### Per-Migration Summary

| Migration | Operations | Tables Affected |
|-----------|-----------|-----------------|
| V1__initial_schema.sql | CREATE TABLE users, orders | users, orders |
| V5__add_email.sql | ALTER TABLE users ADD COLUMN | users |
| ... | ... | ... |

### Domain Analysis

#### Domain Name (N tables)
- `table_a` — Created V1, modified V5, V12
- `table_b` — Created V3

[repeat for each domain]

### Proposed Consolidated Structure

| New Migration | Tables | Source Migrations |
|---------------|--------|-------------------|
| V1__infrastructure.sql | extensions, config | V0, V13 |
| V2__user_management.sql | users, profiles | V1, V5, V12, V18 |
| ... | ... | ... |

**Reduction**: N migrations → M migrations

### Assumptions & Ambiguities
1. [List any interpretation decisions made]
2. [List any ambiguous cases]

### Next Steps
1. Review domain groupings above
2. Confirm or adjust proposed structure
3. Request consolidated SQL generation

---

Common Patterns

Column Evolution

Before (5 migrations):

V1:  CREATE TABLE t (id SERIAL, name VARCHAR(50))
V5:  ALTER TABLE t ADD COLUMN email VARCHAR(100)
V12: ALTER TABLE t ALTER COLUMN email SET NOT NULL
V18: ALTER TABLE t ALTER COLUMN email TYPE VARCHAR(255)
V25: ALTER TABLE t ADD CONSTRAINT uk_t_email UNIQUE (email)

After (1 CREATE TABLE):

CREATE TABLE t (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(255) NOT NULL,
    CONSTRAINT uk_t_email UNIQUE (email)
);

Constraint Evolution

Before (3 migrations):

V2:  CREATE TABLE products (id SERIAL, price DECIMAL(10,2))
V7:  ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price >= 0)
V22: ALTER TABLE products DROP CONSTRAINT chk_price;
     ALTER TABLE products ADD CONSTRAINT chk_price_positive CHECK (price > 0)

After (1 CREATE TABLE):

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price DECIMAL(10,2) NOT NULL,
    CONSTRAINT chk_price_positive CHECK (price > 0)
);

FK Addition Over Time

Before (3 migrations):

V3:  CREATE TABLE profiles (id SERIAL, user_id INT)
V14: ALTER TABLE profiles ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)
V20: ALTER TABLE profiles DROP CONSTRAINT fk_user;
     ALTER TABLE profiles ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

After (1 CREATE TABLE):

CREATE TABLE profiles (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    CONSTRAINT fk_profiles_user FOREIGN KEY (user_id)
        REFERENCES users(id) ON DELETE CASCADE
);

Seed Data Consolidation

Before (3 migrations):

V13: INSERT INTO roles (name) VALUES ('ADMIN'), ('USER')
V27: INSERT INTO roles (name) VALUES ('GUEST'), ('PREMIUM')
V32: DELETE FROM roles WHERE name = 'GUEST' AND id > (SELECT MIN(id) FROM roles WHERE name = 'GUEST')

After (1 idempotent migration):

INSERT INTO roles (name) VALUES
    ('ADMIN'),
    ('USER'),
    ('GUEST'),
    ('PREMIUM')
ON CONFLICT (name) DO NOTHING;