A log is not one thing

Almost every system has an audit_log table with a JSONB column that holds everything: logins, errors, configuration changes, role grants, documents opened. It works until someone asks a real governance question, and then querying turns into archaeology.

I split the trail into three planes, because they answer different questions:

Each plane has its own volume, retention, indexes and criticality. Grants are low volume and high evidential value. Access is high volume and calls for time partitioning. Operational is useful noise. Mixing them all makes an important trail depend on a writer built for something else.

State is not an event

A user_role table tells you who has the role today. It doesn't tell you who had it three months ago. That's what an append-only events table is for. Both are needed, and neither replaces the other.

A state table says who has access. Only the event trail says how they got it.

Asymmetric failure

Not every audit failure should have the same consequence:

That's a regulatory and operational decision, and it has a cost: failing closed puts the trail's storage on the critical path. Before adopting it, measure insert p50 and p99, the error rate caused by the trail, daily growth, bloat and pool behaviour. The policy can't demand availability the infrastructure wasn't sized to sustain.

Grant and event in the same transaction

BEGIN;
  INSERT INTO grant_event (...);
  INSERT INTO user_role (...);
  UPDATE app_user
     SET permission_version = permission_version + 1
   WHERE id = ...;
COMMIT;

If any step fails, roll back everything. An event claiming a grant that doesn't exist is as bad as a grant with no event. Without a global Unit of Work, a narrow helper does the job:

security_transaction(operation):
    begin
    operation(shared_session)
    commit_or_rollback

And the rule that gives the helper teeth: any grant call site outside it is a structural bug, and CI looks for it.

Record before serving

When sensitive access fails closed, the order is authorize, record the event and only then serialize and return the content. The residual error is recording an access that never reached the user because something failed afterwards. That error “on the safe side” is auditable. The reverse, content delivered with no event, is exactly what the policy is there to prevent.

Record the path

When someone opens a resource, it's worth storing which path satisfied the authorization:

access_via = own_partition | share | all_partitions | platform_bypass | legacy

If the share is revoked tomorrow, the trail still explains today's access without reconstructing past state. When more than one path is true at once, define a precedence and stick to it, or record them all. What matters is leaving no historical ambiguity.

What not to record

Interface polling isn't access. Listing metadata and opening content are different events, and recording every automatic refresh produces huge volume with little evidential value. Define what counts as access before you switch the writer on.

And remember the trail itself is personal data: identity, IP, time, the object accessed, usage patterns. It needs access control, explicit retention and, in the stricter environments, an application user with no UPDATE or DELETE on it.