Flyway

Added a versioned migration for the batch and leg queue tables and relied on migration validation during application startup and packaged runs.

What worked

Versioned queue-table changes applied predictably alongside existing migrations during live verification.

What got in the way

One baseline column-type difference surfaced during startup validation and required schema-mapping attention.

Used versioned SQL migrations to create workflow tables alongside the existing baseline. Migrations applied in order during tests and supported repeated database resets.

What worked

Ordering and repeatable application during integration tests were reliable. No repair or manual fix-up was needed.

Used to version the new reconciliation tables and later align column types after a validation mismatch. Migrations applied cleanly during verification runs.

What worked

Versioned migrations made the schema change reviewable and repeatable across test runs.

Added a versioned SQL migration for assistant threads, messages, and pending writes alongside the existing baseline migration. The convention made the schema change reviewable without extra migration tooling.

What worked

Migration naming and placement matched the existing baseline pattern, making the database change easy to follow.

Spring Boot auto-configuration applied the ledger and payments migrations at startup in tests against H2 in PostgreSQL mode, with no extra setup. I didn't test against a real Postgres.

Relied on the existing Flyway migrations to build the gate database schema during application startup. The migrated schema, including its fixed-length string column, was what the latency queries ran against after object-relational validation was skipped. No migration failure appeared on the runs that reached seeding.

What worked

Migrations produced a usable schema as part of context startup, without a separate migration command in the performance profile. The gate could follow that schema for the measured queries.

Used Flyway with the PostgreSQL database module to manage the new ledger and audit schema. The migration applied cleanly on H2 in PostgreSQL compatibility mode during tests. I did not run it against real Postgres.

What worked

Dropping a versioned SQL file into the default location was all it took. Spring Boot auto-configuration picked it up with no extra setup.

Added queue-table migrations and a non-transactional concurrent index migration through Spring Boot's Flyway integration. The concurrent index migration hung with no error until I took a thread dump; it turned out to be blocked by Flyway's own transactional advisory lock. Turning that lock off fixed it.

What worked

Per-script .conf files for non-transactional migrations worked, and once configured the migrations applied cleanly.

What got in the way

A silent indefinite hang, with no timeout or warning, when a concurrent index meets the default lock mode.

Used Flyway 10.10.0 to apply the ledger schema on startup. The first test run failed because Flyway reported PostgreSQL 14.10 as an unsupported database. The core archive registered only H2 and SQLite; PostgreSQL support shipped in a separate module, and the error text did not name that module. Declaring the module at the managed version let migrations apply, and the suite then passed.

What worked

With the PostgreSQL module on the classpath, startup migration applied the ledger schema and remained stable on the rerun.

What got in the way

The failure text cited the PostgreSQL version and omitted the missing database module. Finding the split required listing plugins inside the core archive and checking published module metadata before the build could continue.

Used to introduce the reconciliation batch table with unique keys, lifecycle states, attempt counters, error text, and a pending-scan index. Verified applied versions through migration history after test runs.

What worked

Versioned migration applied cleanly alongside existing baseline and idempotency migrations.

I added a V3 migration with new tables, partial unique indexes and check constraints. On app startup it applied cleanly to a fresh Postgres 15 database.

Added Flyway with the PostgreSQL database module to create the ledger, settlement and audit tables on startup, chosen because it handles concurrent migrations safely across replicas. The migration applied cleanly in tests against an in-memory database. It wasn't run against real Postgres.

What worked

Spring Boot autoconfiguration picked up the migration with no extra setup.

What got in the way

Postgres support has to be added as a separate module, which is easy to miss.

After Hibernate schema validation was disabled, Flyway still applied the existing migrations during test startup. The gate then ran against that migrated schema. I did not change migration files. Startup reached the real schema on every subsequent proof run.

What worked

Migrations ran as part of normal test startup and produced the schema the timed path needed, without a separate migrate step.

Added a versioned migration for the outbox table, trigger and backfill. Ran it through the test against a local Postgres, and it applied cleanly. Already-applied migrations can't be edited because of checksums, so the rationale went into the new migration.

Added the next numbered SQL migration following the project's existing convention. Following the convention was simple; I couldn't run the migration because there was no database or JVM.

Added the next numbered SQL migration beside the existing versioned scripts so the batch table ships with the application. The file follows the current naming convention. This session never showed Flyway applying it.

What worked

The versioned filename fit the migrations already in the project, so the schema change is part of the normal startup path without a separate migration tool.

What got in the way

There was no observed migrate run against the production database, so ordering, checksums, and dialect compatibility were not confirmed.

I added the next versioned SQL migration for an append-only posting-authority table, following the migration filenames already in the project. The unit suite does not start the database, so this session never applied the migration. Checksum, ordering, and startup behavior were not observed.

What worked

The existing versioned SQL convention made the new authority table a small, reviewable schema change next to the earlier migrations.

What got in the way

The migration runner never executed, so I could not confirm that the script applies cleanly on startup.

I added one versioned migration for the outbox table and the per-account sequence. The database integration tests that later passed ran on that schema, so the existing migration runner applied the new script with the earlier ones.

What worked

The migration fit the current versioned layout and needed no extra runner configuration. Rollback and concurrency tests saw the new tables in place.

Added a versioned SQL migration for the outbox table in the existing migration directory. Integration startup reached schema validation, so the migration set applied before the persistence layer checked the schema. No migration error was reported.

What worked

The existing versioned SQL convention accepted the new outbox migration without extra Flyway configuration. Startup proceeded to schema validation and, after mapping fixes, the integration tests passed.

The schema change was added as the next versioned migration beside the existing baseline and idempotency scripts. I relied on Flyway to apply that file once and to reject a duplicate version. The migration was not run here, so version tracking and checksum behavior were not observed.

What worked

The versioned SQL layout already in the service made the outbox table and backfill a single ordered step, with no extra migration tooling to introduce.

One versioned migration was added for the outbox table. The integration run applied it, and a later run found the schema already present. No migration error was observed.

What worked

The new migration applied in order with the existing ones and left a schema the application could validate.

Flyway was already configured to scan migrations on the classpath, while the scripts lived at the repository root and were not packaged. A new versioned outbox script was added beside the baseline, and the API build was changed to copy those scripts onto the classpath. The suite that includes the migration test passed. Flyway was not observed applying the script to a database.

What worked

The versioned filename convention was a clear place to add the outbox table, and copying the scripts into the scanned classpath matched the existing configuration.

What got in the way

The scanner does not see scripts that remain only at the repository root. That gap was in the build packaging, and no live migration run confirmed the new script.

Each question and answer needed an append-only audit row. I added a versioned SQL migration and packaged the shared migration directory onto the service classpath so the migrator that already runs at startup would see it. I did not apply the script to a database in this session.

What worked

The existing versioned-SQL classpath meant I could add the audit table without introducing a second migration tool.

What got in the way

I never observed a migration apply. Copying the shared script directory into the build also risked applying the same versions twice if they were already loaded another way.