Apakah migration bisa membangun schema dari nol?
Apakah migration bisa upgrade schema versi production saat ini?
Apakah migration aman terhadap data production-like?
Apakah migration compatible dengan versi aplikasi lama dan baru?
Apakah migration deterministic?
Apakah failure-nya recoverable?
Apakah evidence-nya cukup untuk production approval?

from scratch  : empty DB     -> latest schema
from reality  : prod-like DB -> latest schema

@Test
void flywayCanBuildSchemaFromEmptyDatabase() {
    Flyway flyway = Flyway.configure()
            .dataSource(postgres.getJdbcUrl(), postgres.getUsername(), postgres.getPassword())
            .locations("classpath:db/migration")
            .cleanDisabled(false) // only in isolated test database
            .load();

    flyway.clean();
    flyway.migrate();

    assertTableExists("customer");
    assertTableExists("case_file");
    assertColumnExists("case_file", "status");
}

schema at release N
+ representative data
+ migration for release N+1
= expected schema/data state

src/test/resources/db/previous-release/
  schema-v2026.06.1.sql
  data-v2026.06.1.sql

@Test
void migrationUpgradesPreviousReleaseSchema() throws Exception {
    DatabaseClient db = connectTo(postgres);

    sqlScript(db, "db/previous-release/schema-v2026.06.1.sql");
    sqlScript(db, "db/previous-release/data-v2026.06.1.sql");

    Flyway flyway = Flyway.configure()
            .dataSource(postgres.getJdbcUrl(), postgres.getUsername(), postgres.getPassword())
            .locations("classpath:db/migration")
            .baselineOnMigrate(true) // only if previous fixture has no flyway history
            .baselineVersion("2026.06.1")
            .load();

    flyway.migrate();

    assertNoNulls("case_file", "status");
    assertForeignKeyValid("case_file", "fk_case_file_customer");
}

flyway_schema_history
DATABASECHANGELOG
DATABASECHANGELOGLOCK

@Testcontainers
class MigrationIT {

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16-alpine")
            .withDatabaseName("app")
            .withUsername("app")
            .withPassword("secret");

    @Test
    void flywayMigrationsApplyOnRealPostgres() {
        Flyway flyway = Flyway.configure()
                .dataSource(postgres.getJdbcUrl(), postgres.getUsername(), postgres.getPassword())
                .locations("classpath:db/migration")
                .load();

        MigrateResult result = flyway.migrate();

        assertThat(result.success).isTrue();
    }
}

@SpringBootTest
@Testcontainers
class ApplicationMigrationIT {

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16-alpine");

    @DynamicPropertySource
    static void databaseProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgres::getJdbcUrl);
        registry.add("spring.datasource.username", postgres::getUsername);
        registry.add("spring.datasource.password", postgres::getPassword);
        registry.add("spring.flyway.enabled", () -> "true");
        registry.add("spring.jpa.hibernate.ddl-auto", () -> "validate");
    }

    @Test
    void contextLoadsAfterMigration() {
        // Spring Boot starts, Flyway migrates, JPA validates mappings.
    }
}

@Test
void flywayValidatePassesAfterMigrate() {
    Flyway flyway = flywayFor(postgres);
    flyway.migrate();
    flyway.validate();
}

./mvnw -DskipTests=false flyway:validate
# or
./gradlew flywayValidate

@Test
void repeatableMigrationsAreStableAcrossSecondRun() {
    Flyway flyway = flywayFor(postgres);

    MigrateResult first = flyway.migrate();
    MigrateResult second = flyway.migrate();

    assertThat(second.migrationsExecuted).isZero();
}

git diff --name-only origin/main...HEAD \
  | grep 'src/main/resources/db/migration/V' \
  | while read file; do
      if git cat-file -e origin/main:"$file" 2>/dev/null; then
        echo "ERROR: existing versioned migration modified: $file"
        exit 1
      fi
    done

add V20260628_1400__add_case_priority.sql

modify V20260501_1000__create_case_table.sql

liquibase \
  --changelog-file=db/changelog/db.changelog-master.yaml \
  validate

liquibase \
  --changelog-file=db/changelog/db.changelog-master.yaml \
  update-sql > build/liquibase/update.sql

@Test
void liquibaseUpdateAppliesOnRealPostgres() throws Exception {
    try (Connection connection = DriverManager.getConnection(
            postgres.getJdbcUrl(), postgres.getUsername(), postgres.getPassword())) {

        Database database = DatabaseFactory.getInstance()
                .findCorrectDatabaseImplementation(new JdbcConnection(connection));

        Liquibase liquibase = new Liquibase(
                "db/changelog/db.changelog-master.yaml",
                new ClassLoaderResourceAccessor(),
                database);

        liquibase.update(new Contexts(), new LabelExpression());
    }
}

liquibase future-rollback-sql > build/liquibase/future-rollback.sql

Apakah rollback menjaga data?
Apakah rollback compatible dengan aplikasi lama?
Apakah rollback bisa dilakukan setelah user menulis data baru?
Apakah rollback justru destructive?

record ColumnContract(
        String table,
        String column,
        String dataType,
        boolean nullable
) {}

@Test
void caseTableContractIsStable() {
    assertColumn(new ColumnContract("case_file", "id", "uuid", false));
    assertColumn(new ColumnContract("case_file", "status", "character varying", false));
    assertIndexExists("case_file", "idx_case_file_status");
}

migration-test-support/
  SchemaAssert.java
  ConstraintAssert.java
  IndexAssert.java
  FlywayTestHarness.java
  LiquibaseTestHarness.java

old app + expanded schema = must pass
new app + expanded schema = must pass

ALTER TABLE case_file ADD COLUMN priority VARCHAR(20);

UPDATE case_file
SET priority = CASE
    WHEN severity = 'CRITICAL' THEN 'HIGH'
    ELSE 'NORMAL'
END
WHERE priority IS NULL;

@Test
void priorityBackfillPreservesBusinessInvariant() {
    assertEquals(0, count("case_file", "priority IS NULL"));
    assertEquals(0, count("case_file", "severity = 'CRITICAL' AND priority <> 'HIGH'"));
    assertEquals(totalCasesBefore, count("case_file", "1 = 1"));
}

happy path data is not enough

INSERT INTO case_file(id, status, severity, created_at) VALUES
('00000000-0000-0000-0000-000000000001', 'OPEN', 'LOW', now()),
('00000000-0000-0000-0000-000000000002', 'ESCALATED', 'CRITICAL', now()),
('00000000-0000-0000-0000-000000000003', 'LEGACY_UNKNOWN', null, now());

INSERT INTO user_account(email) VALUES ('same@example.com');
INSERT INTO user_account(email) VALUES ('same@example.com');

CREATE UNIQUE INDEX ux_user_account_email ON user_account(email);

Migration must fail before production.
Failure message must explain duplicate email.
Remediation script or pre-check must exist.

BEGIN;
SELECT * FROM case_file WHERE id = '...' FOR UPDATE;
-- hold transaction open in test thread

SET lock_timeout = '5s';
ALTER TABLE case_file ADD COLUMN reviewed_at timestamptz;

Migration fails fast, not hang indefinitely.
Pipeline captures failure.
Rerun strategy is documented.

@Test
void backfillProcessesInBatchesAndCanResume() {
    seedRows(100_000);

    BackfillWorker worker = new BackfillWorker(dataSource, 1_000);
    worker.runUntilBatchCount(10);
    worker.simulateCrash();

    BackfillWorker resumed = new BackfillWorker(dataSource, 1_000);
    resumed.runToCompletion();

    assertEquals(0, count("case_file", "new_column IS NULL"));
    assertCheckpointComplete("case_file_priority_backfill");
}

1. Create database from expected previous release.
2. Apply intentional drift.
3. Run migration pre-check.
4. Assert drift is detected before destructive action.

ALTER TABLE customer ALTER COLUMN external_id DROP NOT NULL;
DROP INDEX idx_case_file_status;

Precondition or validation stops deployment.
Evidence says what drift exists.
Operator does not discover drift halfway through production migration.

- changeSet:
    id: 20260628-verify-customer-external-id
    author: platform-db
    preConditions:
      onFail: HALT
      not:
        columnExists:
          tableName: customer
          columnName: legacy_external_id
    changes:
      - addColumn:
          tableName: customer
          columns:
            - column:
                name: external_id_v2
                type: varchar(64)

migration files -> ephemeral DB -> snapshot schema -> compare with expected snapshot

@SpringBootTest
class FullApplicationMigrationIT {
    @Test
    void applicationStartsWithMigratedDatabase() {}
}

@DataJpaTest
@Testcontainers
@TestPropertySource(properties = {
    "spring.test.database.replace=none",
    "spring.datasource.url=jdbc:tc:postgresql:16-alpine:///app",
    "spring.flyway.enabled=true",
    "spring.jpa.hibernate.ddl-auto=validate"
})
class RepositorySchemaCompatibilityIT {
}

class FlywayOnlyIT {
    @Test
    void migrationsApply() {
        Flyway.configure()
              .dataSource(url, user, pass)
              .locations("classpath:db/migration")
              .load()
              .migrate();
    }
}

name: migration-tests

on:
  pull_request:

jobs:
  migration:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-java@v4
        with:
          distribution: temurin
          java-version: '21'
      - name: Static migration checks
        run: ./scripts/check-migrations.sh
      - name: Integration tests with Testcontainers
        run: ./mvnw -Pmigration-test verify
      - name: Generate SQL preview
        run: ./mvnw -Pdb-plan liquibase:updateSQL || true
      - name: Upload migration evidence
        uses: actions/upload-artifact@v4
        with:
          name: migration-evidence
          path: build/migration-evidence/**

[ ] Is this migration tested from empty database?
[ ] Is it tested from previous release schema?
[ ] Is it tested with dirty/edge-case data?
[ ] Does it use the same DB engine family as production?
[ ] Does it validate app compatibility during rolling deployment?
[ ] Are destructive changes guarded by expand/contract?
[ ] Are data invariants asserted?
[ ] Is rollback/roll-forward tested or explicitly waived?
[ ] Are lock/time/performance risks rehearsed?
[ ] Are generated SQL and evidence artifacts attached?
[ ] Are tool-specific validate commands run?
[ ] Are history/changelog tables preserved in realistic upgrade tests?

MUST:
- run Flyway/Liquibase validate in CI
- run migration against real DB engine via Testcontainers or equivalent
- run clean DB migration test
- run previous release upgrade test
- disable Hibernate DDL generation for migration tests
- assert key schema contracts
- assert data invariants for DML/backfill
- archive generated SQL/evidence for risky changes

SHOULD:
- include dirty data fixtures
- include rollback/roll-forward rehearsal
- include lock timeout rehearsal for risky DDL
- compare schema snapshot against golden schema
- run old/new app compatibility tests for expand/contract

MAY:
- run production-volume rehearsal in staging
- run tenant fan-out simulation
- run query plan regression checks

You should be able to explain not just that the migration passes,
but what production risk each test reduces.