An audit trail that survives a compliance review

An audit trail passes review when it can answer four questions about any change, for any entity: who, what, when, and under whose authority. Most homegrown audit code answers two of them, for some entities, in a format that requires a developer to interpret.

Here is a reusable one that answers all four, without a schema change per domain.

Start from the questions, not the table

The design falls out of what an auditor actually asks, which is never "show me your audit table".

The last two are what kills naive implementations. Audit tables get large, and OFFSET 50000 gets slower every month.

The annotation

The interface is one annotation. Domain code says what is auditable and nothing else.

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Auditable {
  String entityType();
  /** SpEL against the method arguments, resolving the entity id. */
  String entityId();
  String action();
}

@Auditable(entityType = "CARD", entityId = "#cardId", action = "BLOCK")
@Transactional
public void blockCard(String cardId, BlockReason reason) {
  var card = cards.findByIdOrThrow(cardId);
  card.block(reason);
}

That is the entire footprint in business code. No audit calls interleaved with logic, nothing to forget on a new method, nothing to keep in sync by hand.

The aspect

@Aspect
@Component
@RequiredArgsConstructor
public class AuditAspect {
 
  private final AuditRepository audit;
  private final EntitySnapshotter snapshotter;
  private final SpelResolver spel;
 
  @Around("@annotation(auditable)")
  public Object record(ProceedingJoinPoint pjp, Auditable auditable)
      throws Throwable {
 
    var entityId = spel.resolve(auditable.entityId(), pjp);
    var before = snapshotter.snapshot(auditable.entityType(), entityId);
 
    var result = pjp.proceed();
 
    var after = snapshotter.snapshot(auditable.entityType(), entityId);
    var principal = SecurityContextHolder.getContext().getAuthentication();
 
    audit.save(AuditEntry.builder()
        .entityType(auditable.entityType())
        .entityId(entityId)
        .action(auditable.action())
        .actor(principal.getName())
        .authorities(authorityNames(principal))
        .correlationId(MDC.get("correlationId"))
        .before(before)
        .after(after)
        .occurredAt(Instant.now())
        .build());
 
    return result;
  }
}

Three decisions inside that are worth defending.

The snapshot is taken, not diffed later. Reconstructing what a record looked like from a sequence of diffs is fine until one write bypasses the aspect, and then every subsequent reconstruction is wrong. Storing both sides makes each entry independently true.

The authorities are recorded, not just the username. "Under whose authority" is a distinct question from "who". A user whose roles changed last month makes an entry ambiguous unless the roles at the time were captured with it.

The correlation id ties the audit entry to the logs. An auditor asking why a change happened needs the request, and the request lives in the log pipeline. One shared id joins the two.

Why JSONB

The snapshots are stored as JSONB rather than as typed columns.

CREATE TABLE audit_entry (
  id             BIGSERIAL PRIMARY KEY,
  entity_type    TEXT        NOT NULL,
  entity_id      TEXT        NOT NULL,
  action         TEXT        NOT NULL,
  actor          TEXT        NOT NULL,
  authorities    TEXT[]      NOT NULL,
  correlation_id TEXT,
  before_state   JSONB,
  after_state    JSONB,
  occurred_at    TIMESTAMPTZ NOT NULL
);
 
CREATE INDEX audit_entity_idx
  ON audit_entry (entity_type, entity_id, id DESC);
 
CREATE INDEX audit_occurred_idx
  ON audit_entry (occurred_at DESC, id DESC);

A typed audit table has to change every time an audited entity changes, and old rows then no longer match the schema that describes them. JSONB stores what the entity looked like at that moment, including fields that no longer exist. That is precisely the property an audit trail needs: an entry from two years ago should still be readable after four refactors.

The cost is that you cannot easily constrain or join on the contents. For an append-only table that is read by humans and exported for review, that is a trade worth making.

Keyset pagination, because audit tables get large

OFFSET makes the database scan and discard every skipped row. On an audit table that grows monotonically, page one is instant and page four hundred times out.

Keyset pagination carries the position instead:

SELECT id, action, actor, before_state, after_state, occurred_at
FROM audit_entry
WHERE entity_type = :type
  AND entity_id   = :id
  AND (:cursor IS NULL OR id < :cursor)
ORDER BY id DESC
LIMIT :size;

The response returns the last id as the next cursor. Every page costs the same regardless of depth, because the index seeks straight to the position.

{
  "entries": [ /* ... */ ],
  "nextCursor": "8823411"
}

The trade-off is that you lose random access to page N. For an audit API that is correct — auditors follow a trail, they do not jump to page 400 — and it is worth resisting the request to add offset paging "just for the UI", because it will be used and it will time out.

Where the aspect does not reach

Being honest about the gaps is part of passing review.

An AOP aspect intercepts Spring-managed method calls. It does not see:

• writes made by a bulk SQL statement or a migration
• writes from another service against the same database
• a call to an audited method from inside the same bean, which bypasses the proxy entirely — the classic self-invocation trap
• changes made directly by a DBA

The first three are addressable in design. The last one is not, and pretending otherwise is worse than documenting it. Where the guarantee has to hold regardless of the application, the mechanism is a database trigger or change data capture — Debezium against a database you do not own describes what that costs operationally.

State that boundary explicitly in the design document. "This trail covers all changes made through the application's service layer" is a claim you can defend. "This trail covers all changes" is one you cannot.

Retention is a requirement, not an afterthought

Audit data usually has a mandated retention period, and often a mandated maximum too under data-protection rules. Both belong in the design from the start: a partitioned table by month makes dropping an expired partition a metadata operation instead of a very long DELETE against a table someone is querying.