Database Trigger Audit Process

Database Trigger Audit Process

Scope

The current audit trigger covers every INSERT, UPDATE, and DELETE on hrs.district_volume. It emits one audit_event and one audit_change for each changed row. deleted = FALSE to TRUE is classified as SOFT_DELETE; a physical delete is classified as DELETE. The trigger also classifies inserts as CREATE and all other updates as UPDATE.

The function derives entity_type from config_type: SPECIES_COMPOSITION remains that value, and every other value is recorded as DISTRICT_VOLUME. This is the current migration behavior; no trigger attachment for another table was found in the inspected migrations.

Trigger flow

district_volume row mutation
        |
        v
AFTER row trigger: trg_district_volume_audit
        |
        v
hrs.audit_district_volume_change() [SECURITY DEFINER]
        |
        +--> classify operation and entity type
        +--> build OLD/NEW JSONB images
        +--> calculate changed_columns
        +--> choose actor and correlation_id
        +--> insert audit_event
        +--> insert audit_change linked by event_id

The function sets search_path = pg_catalog, hrs and is SECURITY DEFINER. Because current_user inside a security-definer function resolves to the function owner, V1.0.6 intentionally uses session_user for the final actor fallback. Row-provided created_by/updated_by values are preferred, so the database owner is not normally recorded for an authenticated application mutation.

Transaction behavior

The audit inserts execute in the same database transaction as the source row mutation. A successful mutation therefore commits with its event/change pair; rollback removes both. The trigger is AFTER ... FOR EACH ROW, so a statement that changes multiple rows produces one pair per row.

The correlation setting follows the same transaction boundary. The Hibernate connection decorator binds it lazily after Spring has applied transaction isolation, and PostgreSQL removes the setting on commit or rollback because the set_config call uses is_local = true. This ordering is required for the project's SERIALIZABLE service paths.

Actor and correlation propagation

Spring Data auditing populates district_volume.created_by, created_at, updated_by, and updated_at. DatabaseAuditor resolves the actor from the authenticated JWT. The trigger then reads those row values and applies its operation-specific precedence before falling back to session_user.

Correlation is independent of the row audit columns. A nonblank trace ID from an active Micrometer span is bound with the following statement before the first JDBC statement:

SELECT set_config('app.correlation_id', ?, true)

The trigger reads the setting safely with:

current_setting('app.correlation_id', true)

The second true is missing_ok; an unset setting returns NULL rather than raising an error. A no-span path, noop span, blank trace ID, or direct raw JDBC write therefore remains auditable but has a NULL correlation_id.

Raw JDBC and no-span behavior

CorrelationIdConnectionProvider only decorates Hibernate connections. A JdbcTemplate statement that bypasses that provider still fires the database trigger, but it does not receive the application correlation binding. This is intentional: database auditing remains available for direct writes while trace correlation is present only when the connection path has a usable span.

The transaction-local GUC also prevents connection-pool bleed. Sequential transactions using the same physical connection can record different trace IDs, or NULL, without inheriting the previous transaction's value.

Application and repository boundary

The backend contains AuditEventRepository and AuditChangeRepository as standard JpaRepository interfaces. AuditEvent and AuditChange provide the read/write entity mappings, including JSONB mapping for the row images. The inspected district-volume services mutate the source configuration entity; the database trigger creates the audit pair. No application service audit insert was found in the current source path.

Test coverage

The audit path is covered at unit, repository, database-integration, and web levels:

• AuditEventTest checks constructor state, including nullable reason and correlation values.
• AuditChangeTest checks JSON parsing, defensive copies of changed_columns, and rejection of invalid JSON.
• DatabaseAuditorTest checks structured ID selection, sub fallback, and missing or unauthenticated contexts.
• CorrelationIdAuditIntegrationTest covers active span, no span, raw SQL, distinct sequential transactions, pooled-connection reuse without bleed, and direct verification that the trigger uses current_setting(..., true).
• DistrictVolumeCorrelationIdAuditIntegrationTest sends a real MockMvc POST with B3 headers and verifies the trace ID in audit_event plus an associated audit_change row.
• Repository integration tests authenticate a JWT so the non-null source-row audit columns can be populated.

These integration tests are intentionally not @Transactional. Fixture cleanup uses TransactionTemplate because Hikari auto-commit is disabled. Tests truncate only hrs.audit_change and hrs.audit_event; they do not wipe hrs.district_volume, which contains Flyway-seeded rows used by other tests.

Known boundaries

• No audit-table retention or archival policy is present in the inspected source.
• The current trigger is named for district volume and is attached only to hrs.district_volume.
• Native-image compilation and runtime validation were not part of the PR #1258 test evidence.
• The reason column exists in the schema and entity mapping, but the current district-volume trigger intentionally leaves it NULL.

Related pages

• Audit Tables
• Correlation ID Audit Propagation
• Correlation-ID Audit Trigger — Deferred Binding and Test Hygiene
• ADR 001: Deferred-Bind ConnectionProvider for Transaction-Local GUC

Sources

Code paths are on main; correlation propagation was merged through PR #1258.

• V1.0.5__audit_and_soft_delete.sql
• V1.0.6__audit_correlation_id.sql
• CorrelationIdConnectionProvider.java
• DatabaseAuditor.java
• AuditEvent.java
• AuditChange.java
• CorrelationIdAuditIntegrationTest.java
• DistrictVolumeCorrelationIdAuditIntegrationTest.java