4.6 KiB
0001 — H2 file storage with jOOQ
Status: accepted, revised after the practical three-way comparison on 2026-09-08. This replaces the initial JdbcTemplate choice; H2 remains accepted. The user delegated the final database/access choice to the prototype. Production delivery now uses a managed JDK; GraalVM is deferred.
Evidence
H2 2.4.240 stores and reloads Unicode data across separate JVM processes. Liquibase 4.33.0 formatted SQL runs idempotently with unquoted identifiers and normal H2 case folding, without PostgreSQL compatibility mode. A closed-file backup restores both data and migration history after an upgrade that successfully changes the schema and then fails. These are executable tests, not documentation-only assumptions.
JdbcTemplate and Spring Data JDBC were tested against the same schema. Both support the required reads and optimistic updates. Spring Data JDBC maps Kotlin data classes and automatically increments @Version, rejecting stale saves. Its owner-scoped finder behaves correctly. Its generic save API still needs an application-level ownership guard; JdbcTemplate expresses owner ID and expected version together in the update predicate.
The daily invariant spans several records and customers. Both variants therefore need a shared transaction boundary. The prototype locks the existing owner row with SELECT FOR UPDATE before reading the daily total and inserting. Two simultaneous first entries of eight hours produce one accepted write and one limit rejection, leaving eight hours. A twelve-hour day is accepted. Monthly aggregates include both ends of September correctly, exclude adjacent months and other owners, and group by customer.
The jOOQ probe additionally verifies date moves, stale version rejection, owner isolation, monthly aggregates, concurrent daily limits and rollback through an enclosing Spring transaction. Its generated Kotlin schema is derived from the same Liquibase SQL used by the other probes. All eleven JVM tests and the production JAR smoke pass locally.
Decision
Use H2 file mode, Liquibase formatted SQL, generated jOOQ Kotlin fields and Spring-managed transactions for the application. Keep JdbcTemplate and Spring Data JDBC probes for the executable comparison. Generate from a disposable database migrated with the authoritative SQL changelog; never generate against the live application database. Use unquoted snake_case identifiers and normal H2 behavior; do not enable PostgreSQL compatibility mode.
Each work-entry mutation must serialize through the authenticated owner's existing row. Inserts, updates, date moves and deletions will use the same owner lock. Check the daily sum inside that transaction, excluding the old entry when replacing it. Every record lookup/update/delete must include the owner predicate; versioned updates additionally include the expected version. The prototype establishes these building blocks; complete application operations and their acceptance tests belong to the foundation/work nodes.
Migrations own their connection lifecycle separately from business transactions. The prototype exposed a missing commit after Liquibase left its connection in manual-commit mode. Never copy an open H2 file for deployment rollback: stop writers, close the database, copy the complete database state, and restore only while stopped. DDL failure can leave earlier changesets applied, so SQL rollback is not a substitute for the agreed matched application/database fallback.
Consequences
jOOQ keeps ownership and concurrency predicates explicit while generating schema field types and supporting typed aggregates. The added build step is justified by less manual mapping and compile-time feedback on schema changes. JdbcTemplate remains the simplest build; Spring Data JDBC remains viable. Neither alternative eliminates the shared daily-limit transaction. Use the free org.jooq distribution, pinned to 3.21.8 for this prototype. Generated sources stay under build/. Spring transaction participation must be configured explicitly and tested; the probe uses TransactionAwareDataSourceProxy. This choice makes no performance, memory or native-image compatibility claim. A future PostgreSQL migration can retain unquoted identifiers, prepared SQL and service transaction boundaries, but needs separate migration scripts and database integration tests. PostgreSQL compatibility is not claimed by this prototype.
The SQL under src/test/resources/db/prototype is a probe schema. The production schema, customer/project relationships and complete account lifecycle follow in the foundation node. Hostsharing's routine backup restoration remains a separate deployment acceptance check.