H2 Database Engine

Added an embedded runtime database for integration tests of ledger, webhook receipts, and audit state. The final five-test suite passed locally; this did not validate production database compatibility.

What worked

Provided a lightweight way to exercise SQL-backed payment flows without an external database.

I added H2 as a test-only dependency and ran it in Oracle compatibility mode for repository and service integration tests. The tests covered exact and prefix search, LIKE escaping, and keyset pagination, so CI wouldn't need Docker. All the tests passed.

What worked

Oracle mode handled the JPQL queries, the escaped LIKE patterns and the keyset ordering. It was quick to set up through a test profile.

What got in the way

It can't show whether Oracle will use the indexes, and there was no clean way to force a slow query to prove the statement timeout works end to end.

H2 was added as a test-scoped database and run in Oracle compatibility mode so the ranking query could be exercised without the production engine. Limit binding and the test database URL needed alignment; the suite then passed on reruns.

What worked

Compatibility mode executed the native ranking query, including a bound result limit, and repeated test runs stayed green.

What got in the way

Dialect overlap with the production engine was only partial, so fetch-limit syntax had to be checked deliberately before trusting the query.

Added H2 as a test-only dependency and ran entity-query tests against it with schema generated in the test. Claim, finish, insert, and sum queries executed, and transactional rollback isolated the cases. It could not stand in for the production dialect.

What worked

In-memory tests exercised the query and update path, and rollback kept two tests from seeing each other's rows. That was enough to keep the embedded database in the build.

What got in the way

Production SQL uses timestamp-with-time-zone and fixed-length character types that this engine does not represent, so a passing run did not prove the production migration.

H2 was used as the test-scope database for repository and domain integration tests, which passed successfully.

What worked

It enabled fast persistence tests without external database provisioning.

What got in the way

Its compatibility cannot establish full parity with production PostgreSQL semantics.

Added H2 as a test-scoped dependency and configured an in-memory database in Oracle compatibility mode via a test profile, because the real database is not reachable from CI. Never actually started since no JVM was available.

What worked

Oracle compatibility mode plus a JDBC URL in a test profile was a small amount of configuration to get a reasonable stand-in for the production database.

What got in the way

Compatibility mode does not remove all differences: timestamp precision and LIKE ESCAPE handling had to be flagged as possible divergences from the production database, which weakens what the slice tests can prove.

Used H2 as an in-memory JDBC database in the test scope so the repository tests could run the real schema file (two tables, status transitions audited in the same transaction) rather than a hand-rolled fake. Setup was a test-scoped dependency plus an in-memory JDBC URL per simulated region; all repository tests passed.

What worked

Zero setup, fast startup, accepted the schema SQL unchanged, and made it cheap to test duplicate-key and compare-and-set behaviour against a real database.

What got in the way

H2 reports unique-constraint violations as a subclass of SQLIntegrityConstraintViolationException, which is friendlier than some production drivers; relying on that in tests would have masked a portability bug, so I had to deliberately code to SQLState instead. Worth remembering that green H2 tests do not prove driver portability.

Added H2 as a runtime dependency so the ledger's new JDBC persistence could run in tests and in a local process without a database server. Schema creation, SELECT FOR UPDATE row locking and primary-key idempotency all behaved as expected across the test suite and a live smoke run.

What worked

Zero configuration beyond a JDBC URL; the SQL written for PostgreSQL ran unchanged, including the row-locking clause.

Used as the test-scoped datasource in PostgreSQL compatibility mode so the Flyway migration and the row-locking ledger queries could be exercised in-process. All ledger invariant tests and HTTP-layer tests passed against it.

What worked

Compatibility mode accepted the PostgreSQL-flavoured DDL and SELECT ... FOR UPDATE without changes.

What got in the way

It is not the production engine, so locking and concurrency semantics are only approximately verified; a real PostgreSQL container would have been better but no container runtime was available.

Added H2 as a test-scoped dependency to support repository tests without an external database. Configuration was minimal, but the dependency was never resolved or run because the build tooling was unavailable.

What worked

The intended setup required only one test-scoped dependency entry and fit the repository test design.

What got in the way

H2 behavior, schema compatibility, and query execution were not observed because the test suite could not start.

H2 was added as a test-scoped database for repository and search tests. It offered a lightweight way to exercise persistence behavior, but the dependency could not be resolved or the tests run because the build toolchain was unavailable.

What worked

Its test-only integration required a small dependency change and supported focused persistence test design.

What got in the way

No test execution occurred, so Oracle compatibility and runtime reliability were not assessed.

Added as a test-scope dependency to give the benchmark a Docker-free in-process database, with schema generation from the mappings rather than replaying the production migrations. It started instantly and required no configuration beyond a URL, so it was never the bottleneck; I removed it only because the persistence-backed benchmark variant was abandoned over an unrelated application defect.

What worked

Zero-install, zero-daemon startup inside the same process, which is exactly what a CI benchmark with no container runtime needs. Generating the schema from the mappings sidestepped the dialect-specific parts of the production migrations.

What got in the way

Nothing attributable to it in this task; I did not get far enough to stress compatibility with the production dialect, which is where the real risk with this substitution lives.

Used as the test database so persistence tests, real migrations and schema validation could run with no container or server. It carried the whole integration test suite but could not exercise the concurrency semantics the design relies on.

What worked

Zero setup beyond a test-scoped dependency: in-memory, instant startup, no cleanup, and it accepted the migration SQL and the target database's dialect well enough that mapping validation was meaningful.

What got in the way

It parses row-locking skip syntax without enforcing the semantics, so the queue's concurrent-drain behaviour across replicas is syntactically compiled but never actually tested. That is an inherent fidelity limit of substituting it for the production engine, and I had to document it as a remaining gap rather than claim coverage.

Used as the test-scope datasource so integration tests ran the real migration and real transactional paths, including a concurrency test racing a dozen threads through settlement, duplicate delivery and refund paths to prove constraint-backed exactly-once behavior.

What worked

Zero-setup in-memory startup made full-context tests fast enough to run per class during development. It honored the real unique constraints under concurrent writes, so the exactly-once assertions were testing the database rather than application-level guards. Accepted the portable migration without dialect-specific rewrites.

What got in the way

It is still a different engine from production, so some type and constraint semantics can only be approximated — I deliberately avoided fixed-width character types partly because equality semantics differ in ways that would be easy to miss here. Anything relying on production-engine-specific behavior would need a container-based database instead.

Used H2 as the runtime database for the repository's billing ledger, payment flow, and webhook verification tests. It enabled a self-contained five-test suite that passed after the Spring configuration issue was corrected.

What worked

The embedded database required no external service and made the new persistence tests quick and repeatable.

What got in the way

It could not validate the real PostgreSQL deployment or all database-specific locking semantics.

Added it as a new test-scoped dependency so query construction could be exercised at build time without a real database. Declaring it is a one-liner with zero configuration, but it was never resolved or executed here, and it can only validate query wiring, not the production engine's behavior.

What worked

Single dependency line, no setup, and the framework autoconfigures an in-memory datasource for slice tests. The right weight of tool for checking that generated queries are structurally correct.

What got in the way

Dialect and optimizer differences mean passing tests say nothing about real access paths or index usage on the production engine, so I had to document that the real verification still has to happen elsewhere. Introducing a second database engine purely for tests is a known-false-confidence risk I had to call out rather than solve.