Flyway Monolith Migration Traps

Symptom

Backend startup fails or hangs during Flyway bootstrap after a datasource or migration change. The common patterns behind the May and June 2026 fixes were:

• Flyway connects with the runtime lumie_app role and fails DDL that requires table ownership.
• Fresh environments hang or deadlock on migrations that use CREATE INDEX CONCURRENTLY.
• Boot breaks after someone edits an already-applied V<number>__*.sql file instead of adding a new migration.

Relevant current contracts live in the Backend Infrastructure and Migration Guide reference pages.

Likely Causes

• RoutingDataSourceConfig lost the dedicated @FlywayDataSource, so Flyway fell back to the runtime pool instead of the migrator credentials.
• spring.flyway.user/password or spring.flyway.postgresql.transactional-lock=false disappeared from application.yaml.
• A migration that has already run was modified in place. Commit 2ff2bb48 is the canonical example: it reverted an accidental edit to V18__rls_baseline.sql.

Diagnosis

Check the two current source-of-truth files first.

RoutingDataSourceConfig must still create a separate migrator datasource:

// lumie-backend/app/src/main/java/com/lumie/app/config/RoutingDataSourceConfig.java
@Bean
@FlywayDataSource
public DataSource flywayMigratorDataSource(
        @Qualifier("primaryDataSourceProperties") DataSourceProperties primary,
        @Value("${spring.flyway.user}") String migratorUser,
        @Value("${spring.flyway.password}") String migratorPassword) {
    return DataSourceBuilder.create()
            .url(primary.determineUrl())
            .driverClassName(primary.determineDriverClassName())
            .username(migratorUser)
            .password(migratorPassword)
            .build();
}

lumie-backend/app/src/main/resources/application.yaml must still pin the Flyway credentials and non-transactional advisory lock mode:

spring:
  flyway:
    user: ${DB_MIGRATOR_USERNAME:${DB_USERNAME:lumie}}
    password: ${DB_MIGRATOR_PASSWORD:${DB_PASSWORD:lumie}}
    postgresql:
      transactional-lock: false

Then inspect the migration diff itself:

cd lumie-backend
git diff -- app/src/main/resources/db/migration/public

Expected success signal: only new migration files appear. If an old versioned file changed, treat that as the primary suspect.

Fix

• Restore the dedicated Flyway datasource in app/src/main/java/com/lumie/app/config/RoutingDataSourceConfig.java if Flyway is using the runtime pool.
• Restore spring.flyway.user/password when the migrator role was accidentally dropped from config.
• Keep spring.flyway.postgresql.transactional-lock=false so from-scratch environments do not deadlock on older concurrent-index migrations such as V21 and V23.
• Revert edits to any already-applied versioned migration and ship a new forward-only migration instead. Do not rely on checksum repair as the normal path.

For the SMS rollout specifically, the final shape was:

• V76__sms_provider_dispatch_tracking.sql owns the provider columns and indexes.
• V77__sms_provider_dispatch_tracking_indexes.sql is intentionally a transactional no-op so environments that still encounter it can advance safely.

Prevention

• Treat Flyway and the runtime pool as different security boundaries. The runtime role should stay RLS-bound and non-owning; the migrator role owns DDL.
• When adding indexes that need PostgreSQL CONCURRENTLY, design the migration and lock strategy deliberately instead of assuming the default Flyway transaction model will work.
• Never edit an existing V<number>__*.sql after it is applied anywhere. Lumie's own migration guide already treats that as a hard rule.
• When datasource wiring changes, verify both source files together: RoutingDataSourceConfig.java for bean wiring and application.yaml for role and lock settings.

Operational Detail

This failure class is dangerous because it can look like an ordinary SQL syntax or startup problem while the real regression is a broken ownership boundary. The application role and the migration role are intentionally not interchangeable:

Role	Expected behavior	Failure if confused
lumie_app	Runtime reads and writes through RLS-bound application paths.	DDL fails, or a workaround weakens RLS ownership.
Flyway migrator	Owns forward-only schema changes during bootstrap.	Startup may pass locally but fail in fresh or production-like environments.

During triage, separate three questions:

1. Is Flyway connecting with the migrator credentials?
2. Is the migration itself forward-only and compatible with environments that already ran older versions?
3. Is the PostgreSQL lock mode compatible with any CONCURRENTLY statements in the historical chain?

Do not fix the third question by rewriting an old migration. That only moves the failure to any environment where the checksum has already been recorded.

Recovery Playbook

1. Stop the rollout if multiple pods are crash-looping on the same Flyway error. Let one failing instance preserve the logs.
2. Read the first Flyway exception, not only the final Spring boot failure. The first exception normally identifies the migration version, role, lock, or checksum issue.
3. Compare flyway_schema_history in the affected database with the files in app/src/main/resources/db/migration/public.
4. If an applied file changed, restore the old file exactly and create a new V<number>__*.sql migration for the forward fix.
5. If the error is ownership or permission related, restore the @FlywayDataSource and spring.flyway.* credentials before changing SQL.
6. If the error is a concurrent-index lock failure, keep transactional-lock=false and validate the migration in a fresh database.

Verification Evidence

Collect all of the following before closing the incident:

• backend starts with Flyway enabled in a fresh local or staging database,
• flyway_schema_history shows the new migration as success,
• no previously applied migration file is modified in the final diff,
• application traffic still runs through the runtime datasource rather than the migrator datasource,
• RLS checks still pass for tenant-scoped queries.

Anti-Patterns

• Running Flyway with the application role because it is the quickest way to make startup pass.
• Editing a historical migration and relying on flyway repair as normal remediation.
• Removing transactional-lock=false without replaying the whole migration chain in an empty database.
• Treating local H2 or a partially migrated developer database as enough evidence for production readiness.