Audit Tables

Audit Tables

Purpose

Waste Plus records database mutations in two PostgreSQL tables in the hrs schema: audit_event groups one mutation, and audit_change stores the affected row and its before-and-after images. The current trigger is attached to hrs.district_volume, which stores both district-volume and species-composition configuration rows.

The audit rows are written by PostgreSQL after each row mutation. The application maps both tables as JPA entities and exposes Spring Data repositories for direct repository access, but the trigger—not an application service—is the writer used by the configured district_volume mutation path.

hrs.audit_event

Column	PostgreSQL type	Nullability/default	Meaning
id	BIGSERIAL	Primary key	Event identifier generated by PostgreSQL.
action	VARCHAR(32)	NOT NULL	CREATE, UPDATE, SOFT_DELETE, or DELETE.
changed_by	VARCHAR(128)	NOT NULL	Actor selected from the mutated row's audit columns, with session_user fallback in the current SECURITY DEFINER function.
changed_at	TIMESTAMPTZ	NOT NULL DEFAULT NOW()	Database timestamp for the event. The JPA constructor also initializes the field when an entity is constructed directly.
reason	VARCHAR(500)	Nullable	Reserved event reason. The current district_volume trigger does not populate it because that table has no reason column.
correlation_id	VARCHAR(128)	Nullable	Transaction-local correlation value from app.correlation_id; NULL when the setting is absent or blank.

The primary key is the only table constraint declared for audit_event. The following indexes support actor/time and correlation/time lookups:

idx_audit_event_changed_by_time
  ON hrs.audit_event (changed_by, changed_at DESC)

idx_audit_event_correlation_time
  ON hrs.audit_event (correlation_id, changed_at DESC)
  WHERE correlation_id IS NOT NULL

The correlation index is created by V1.0.6__audit_correlation_id.sql. V1.0.5__audit_and_soft_delete.sql already declares the column as

hrs.audit_change

Column	PostgreSQL type	Nullability/default	Meaning
id	BIGSERIAL	Primary key	Change-row identifier.
event_id	BIGINT	NOT NULL, foreign key to audit_event(id)	Parent event for this row change.
entity_type	VARCHAR(64)	NOT NULL	Polymorphic type; the current function records SPECIES_COMPOSITION or DISTRICT_VOLUME.
entity_id	BIGINT	NOT NULL	Identifier of the changed source row. There is deliberately no foreign key because hard-delete images must remain queryable.
action	VARCHAR(32)	NOT NULL	Same operation classification as the parent event.
previous_values	JSONB	Nullable	Complete OLD row image for update/delete; NULL for insert.
current_values	JSONB	Nullable	Complete NEW row image for insert/update; NULL for delete.
changed_columns	TEXT[]	NOT NULL DEFAULT '{}'	Sorted changed keys. Insert/delete records contain the keys in the available image; update records contain keys whose values differ.

Indexes are declared on event_id, (entity_type, entity_id, id DESC), and cascade action. No retention job, archival policy, or scheduled deletion for either audit table is present in the inspected migrations or backend code.

Timestamp and identity semantics

changed_at is database-generated by NOW() for trigger-created events. It is the event time, not the source row's created_at or updated_at. The source row's created_by and updated_by values are separate fields. For an insert, the trigger prefers created_by; for delete it prefers updated_by, then created_by; for update it prefers updated_by, then created_by. Empty values are ignored before the function falls back to session_user.

DatabaseAuditor supplies the source row audit values through Spring Data JPA: an authenticated JWT's structured ID (for example, IDIR\\username) is used when available, and the token's sub claim is used when the structured claims are absent. Missing or unauthenticated security context produces no Spring auditor value; the database trigger still has its session_user fallback.

Correlation IDs

correlation_id is populated only on audit_event. CorrelationIdConnectionProvider reads the current non-noop Micrometer span and, when its trace ID is nonblank, binds it with:

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

The wrapper performs this once immediately before the first statement-producing JDBC call. The true argument makes the setting transaction-local, so commit or rollback removes it before a pooled connection is reused. No active span, noop span, or blank trace ID leaves the raw connection unchanged; the trigger then records NULL.

Query examples

Find the most recent event for a trace and its row image:

SELECT event.id,
       event.action,
       event.changed_by,
       event.changed_at,
       change.entity_type,
       change.entity_id,
       change.previous_values,
       change.current_values,
       change.changed_columns
FROM hrs.audit_event AS event
JOIN hrs.audit_change AS change ON change.event_id = event.id
WHERE event.correlation_id = :correlation_id
ORDER BY event.changed_at DESC, change.id DESC;

The web integration test uses the same relationship in the opposite direction to verify that a created event has at least one audit_change row:

SELECT COUNT(*)
FROM hrs.audit_change
WHERE event_id = :event_id;

Related pages

• Database Trigger Audit Process
• Correlation ID Audit Propagation
• CorrelationIdConnectionProvider

Sources

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