Correlation ID Audit Trigger Deferred Binding and Test Hygiene

Pattern: Correlation-ID Audit Trigger — Deferred Binding and Test Hygiene

Type: Pattern Domain: Backend — persistence and testing Applies to: Spring Boot 4.1, Hibernate ORM 7.x, PostgreSQL 17 (pgjdbc), Micrometer Tracing, Testcontainers Enforced by: java-specialist (code review) Last updated: 2026-08-26 Status: Current

Overview

PostgreSQL GUCs set with set_config(..., true) are transaction-local. Bind them lazily — on first statement use, never at connection acquisition — and test the path with real tracer auto-configuration, explicit fixture transactions, and a shared database that stays intact.

Pattern 1 — Deferred-bind Connection decorator

Structure

CorrelationIdConnectionProvider decorates Hibernate's ConnectionProvider. On getConnection() it asks the delegate for a pooled connection and, when a Micrometer span carries a non-blank traceId, wraps it in a DeferredCorrelationIdConnection. The wrapper runs SELECT set_config('app.correlation_id', ?, true) exactly once, immediately before the FIRST createStatement, prepareStatement, prepareCall, or setSavepoint call.

The bind therefore lands AFTER Spring has applied transaction isolation, inside whatever transaction Spring/Hibernate established.

@Override
public Connection getConnection() throws SQLException {
  Connection connection = delegate.getConnection();
  Span span = tracer.currentSpan();
  if (span == null || span.isNoop()) {
    return connection;
  }
  String traceId = span.context().traceId();
  if (traceId == null || traceId.isBlank()) {
    return connection;
  }
  return new DeferredCorrelationIdConnection(connection, traceId);
}

/** Executes the transaction-local GUC binding exactly once, before first use. */
private void bindOnce() throws SQLException {
  if (bound) {
    return;
  }
  bound = true;
  try (PreparedStatement statement = target.prepareStatement(BIND_CORRELATION_ID)) {
    statement.setString(1, traceId);
    statement.execute();
  }
}

Why deferred binding matters

Binding eagerly broke every SERIALIZABLE service method. The eager implementation called setAutoCommit(false) and executed the set_config statement directly inside getConnection(). That opened the PostgreSQL transaction block BEFORE Spring ran SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, and PostgreSQL rejected the switch:

PSQLException: Cannot change transaction isolation level in the middle of a transaction

Hibernate acquires the connection before it applies the caller's isolation level. Any statement issued during acquisition runs outside the transaction the caller asked for.

Pool prerequisite: hikari auto-commit=false

Pool-wide spring.datasource.hikari.auto-commit=false is REQUIRED for this design. set_config(..., true) needs an active transaction block; autocommit-on single statements never have one.

spring:
  datasource:
    hikari:
      auto-commit: false

Anti-simplification warning

Warning

The manual HibernatePropertiesCustomizer bean in GlobalConfiguration (correlationIdConnectionProviderCustomizer) must stay as-is. It constructs DatasourceConnectionProviderImpl directly, wraps it in CorrelationIdConnectionProvider, and registers the result under JdbcSettings.CONNECTION_PROVIDER via map customization.

Do not "simplify" it to bean-container SPI instantiation (Hibernate's beans.container reflection lookups). The current wiring uses no reflection, no proxies, no runtime bytecode, and is deliberately GraalVM/native-image safe. Reflection-based service lookup is a known native-image failure source.

@Bean
@SuppressWarnings({"deprecation", "removal"})
public HibernatePropertiesCustomizer correlationIdConnectionProviderCustomizer(
    DataSource dataSource, Tracer tracer) {
  return hibernateProperties -> {
    DatasourceConnectionProviderImpl delegate = new DatasourceConnectionProviderImpl();
    delegate.setDataSource(dataSource);
    CorrelationIdConnectionProvider provider = new CorrelationIdConnectionProvider(delegate, tracer);
    provider.configure(hibernateProperties);
    hibernateProperties.put(JdbcSettings.CONNECTION_PROVIDER, provider);
  };
}

Lesson 2 — Real Tracer over hand-rolled test doubles

CorrelationIdAuditIntegrationTest originally shipped a ~110-line ThreadLocalTracer implementing the full Micrometer SPI to fake span context. Replaced by @AutoConfigureTracing on AbstractTestContainerIntegrationTest:

import org.springframework.boot.micrometer.tracing.test.autoconfigure.AutoConfigureTracing;

@Testcontainers
@ExtendWith({SpringExtension.class})
@AutoConfigureTracing
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public abstract class AbstractTestContainerIntegrationTest {

The annotation comes from org.springframework.boot:spring-boot-micrometer-tracing-test, test scope. @SpringBootTest supplies no-op tracing components unless this annotation enables real ones. See Spring Boot Tracing in Integration Tests for the diagnostic order.

Lesson 3 — Test hygiene under auto-commit=false and a shared Testcontainers DB

Four rules from debugging this spike:

Wrap fixture writes in TransactionTemplate

With auto-commit=false, a bare jdbcTemplate write outside an explicit transaction is rolled back when Hikari reclaims the connection. Wrap fixture and raw-SQL setup in TransactionTemplate so rows commit.

Assert NULL correctly

pgjdbc returns Java null for SQL NULL columns. Assert isNullOrEmpty(), not isEmpty() — calling isEmpty() on a null string throws before the real assertion runs.

Never wipe hrs.district_volume

The integration database is shared across all test classes in the suite. hrs.district_volume holds Flyway-seeded reference rows other tests depend on for grade validation. Deleting them breaks unrelated tests. Prevent duplicate-open-entry collisions instead: give test entities closed date ranges so two tests never hold open-ended rows for the same key.

Clear stale report directories before declaring a broken build

Leftover target/surefire-reports and target/failsafe-reports from out-of-band runs can fail failsafe:verify even with green tests — failsafe-summary.xml carries old failure state. Run rm -rf target/surefire-reports target/failsafe-reports before diagnosing.

When to Apply

• A Hibernate ConnectionProvider decorator, or any acquisition-time hook, must set transaction-scoped state.
• Integration tests exercise audit triggers that read session/GUC state.
• Several test classes share one Testcontainer database.

When NOT to Apply

Eager binding is safe only when nothing configures the transaction after acquisition — no Spring isolation attributes and no SET TRANSACTION anywhere in the touched paths. This codebase has SERIALIZABLE service methods, so the deferred shape applies everywhere here.

Escape Hatch

A session-scoped GUC (set_config(..., false)) plus an explicit reset in closeConnection() solves the ordering problem differently but leaks state on any missed close path. Deviations from the deferred decorator require an ADR.

Detection

Code-review checks:

• A statement executed inside getConnection() or any DataSource acquire path — verify it cannot precede Spring's isolation setup.
• Hand-rolled tracer/SPI implementations under src/test — replace with @AutoConfigureTracing.
• jdbcTemplate fixture writes without a wrapping transaction while auto-commit=false.
• Fixture deletes against hrs.district_volume.
• Failing failsafe:verify with green tests — check report-directory staleness first.

Related Pages

• Correlation ID Audit Propagation — the architecture this pattern implements.
• Spring Boot Tracing in Integration Tests — the no-op tracer gotcha behind Lesson 2.
• Tracing — B3 header forwarding in the application.

Sources

Code paths on main (merged via PR #1258):

• backend/src/main/java/ca/bc/gov/nrs/hrs/security/CorrelationIdConnectionProvider.java
• backend/src/main/java/ca/bc/gov/nrs/hrs/configuration/GlobalConfiguration.java
• backend/src/main/resources/db/migration/V1.0.6__audit_correlation_id.sql
• backend/src/test/java/ca/bc/gov/nrs/hrs/extensions/AbstractTestContainerIntegrationTest.java
• backend/src/test/java/ca/bc/gov/nrs/hrs/security/CorrelationIdAuditIntegrationTest.java
• backend/src/test/java/ca/bc/gov/nrs/hrs/controller/DistrictVolumeCorrelationIdAuditIntegrationTest.java

[1] PostgreSQL SET documentation

[2] Using Tracing in Spring Boot tests