Data Migrations and Auditing

Data, Migrations and Auditing

Storage

Customers and users are stored in H2 (in-memory). Application code uses R2DBC (reactive, non-blocking); Flyway owns the schema and seed data but runs over JDBC (Flyway has no R2DBC support). Both point at the same in-memory database:

• R2DBC — r2dbc:h2:mem:///customerdb?options=DB_CLOSE_DELAY=-1
• Flyway (JDBC) — jdbc:h2:mem:customerdb;DB_CLOSE_DELAY=-1

The shared DB name + DB_CLOSE_DELAY=-1 keep one in-memory database alive for the JVM's lifetime, so Flyway can migrate it and R2DBC can read/write it. spring.sql.init is disabled — Flyway is the single source of truth.

Migrations

In src/main/resources/db/migration/:

• V1__init.sql — users, customers, audit_log tables + the audit triggers.
• V2__seed_users.sql — the seeded admin / user accounts (BCrypt hashes).

Row auditing

Customer rows carry created_by, last_modified_by, created_at, updated_at, populated by Spring Data R2DBC auditing (@EnableR2dbcAuditing, a ReactiveAuditorAware that reads the current username from the security context, and the @CreatedBy / @LastModifiedBy / @CreatedDate / @LastModifiedDate annotations).

Change history (audit_log)

Every customer update/delete is recorded in audit_log by an H2 database trigger (CustomerAuditTrigger), registered by the V1 migration. Recording it at the database level means the trail can't be bypassed by going around the service layer.

Caveat (H2): on a delete, the trigger attributes the change to the row's last modifier (H2 triggers can't see the current app user on delete). On Postgres this becomes exact attribution via a SET LOCAL drop-in.

Moving to Postgres

In-memory H2 means data resets on restart and limits the app to a single instance. To go persistent / multi-instance: add r2dbc-postgresql, point the Flyway JDBC URL at Postgres, and port the schema + audit trigger (the trigger is H2-specific). See Design Decisions.