Your Audit Log Is Not an Audit Trail

To make the distinction concrete, I will use an enterprise expense management platform as the running example. An employee submits a claim for review. The platform preserves that submitted version, and a reviewer can approve, reject, or return it for correction. Four Decisions I Made Before Writing Any Features explains the broader workflow and why the submitted versions are immutable.

Now suppose a reviewer approves a claim on Monday. On Thursday, somebody asks a simple question: what exactly did the reviewer approve?

The current row cannot answer. It only shows what the claim looks like now.

The application log may not answer either. It might contain a line such as this:

INFO claim updated successfully

That proves very little. It does not identify the business action. It does not show which version was reviewed. It may not name the actor, the reason, or the earlier state. It can also disappear when logs rotate.

This distinction mattered while I was working on that platform. The system handled decisions that might need to be explained long after the request that created them had finished. “The endpoint returned 200” was not enough evidence.

An audit log records events. An audit trail lets you reconstruct an attributable sequence of business decisions.

Those sound similar. They lead to very different designs.

Start with the question the trail must answer

I find it useful to ignore the table for a moment and write the future question instead.

For an approval, the question might be:

Who approved which version, under what authority, at what time, and what result did the system commit?

For an account change:

Who deactivated this account, why did they do it, and which active sessions or assignments were affected?

For configuration:

Which policy version was active when this decision was made, and what later replaced it?

If the stored evidence cannot answer the question, adding more log lines will not fix the design.

The OWASP Logging Cheat Sheet makes a useful separation here. Security logs, process logs, transaction logs, and audit trails serve different purposes. It also describes the basic event questions as when, where, who, and what.

I would add two more for business systems: which version and why.

Four records that should not be confused

A system often has four kinds of history. Each answers a different question.

Trying to make one of these do all four jobs produces weak evidence.

An operational log is excellent for debugging. It can show that a query was slow, a provider returned 503, or a retry took place. It is usually optimized for search, short retention, and engineering access.

An immutable version preserves content. It can prove what fields, totals, or configuration values existed at a point in the workflow. It does not always explain who moved the workflow or why.

An audit event records the decision around that content. It should point to the exact version instead of copying a vague description such as “claim approved.”

The current row remains useful because most product screens need the latest state quickly. It just should not be mistaken for history.

The audit trail is the other half of that versioning design. The version preserves the evidence. The event preserves the attributable action taken against it.

Record business events, not database verbs

This is a weak audit event:

{
  "action": "UPDATE",
  "table": "claims",
  "row_id": "..."
}

It describes storage mechanics. It says nothing about the business.

These are better:

claim.submitted
claim.returned_for_correction
claim.approved
account.deactivated
policy_version.published
document.access_granted

A semantic event remains understandable even if the schema changes. It also forces the application to name what it believes happened.

That matters because one SQL update can represent several different commands. Setting status = 'CLOSED' could mean approved, cancelled, expired, rejected, or administratively retired. Those actions have different actors, permissions, reasons, and consequences.

The audit trail should record the command that succeeded, not only the column that changed.

A practical event shape might include:

event_id
occurred_at
recorded_at
actor_type
actor_id
action
object_type
object_id
object_version
result
reason_code
correlation_id
metadata

event_id gives every event a stable identity. occurred_at records when the business action took place. recorded_at can be separate when events arrive from another system later. The actor may be a user, worker, service, or approved administrative process.

object_version is easy to omit and hard to recover later. Without it, “approved claim 123” points to a moving target. With it, the event names the exact evidence that received the decision.

reason_code is often more useful than a free-text reason. Codes can be validated, reported, and translated. A bounded optional note can add context when a human explanation is necessary.

metadata should be allowlisted and versioned. It is not an excuse to place the request body into JSON.

Write the event in the same transaction

An audit event that is written after the business transaction has the same dual-write problem as any other follow-up action.

claim = await approve_claim(command)
await session.commit()

await audit_log.write("claim.approved", claim.id)

The process can stop after the first commit. The claim is approved, but the audit trail says nothing happened.

The reverse order is worse. The trail can say an approval happened even though the business transaction rolled back.

The state change and its audit event belong in one transaction:

async def approve_claim(command: ApproveClaim) -> Claim:
    async with session.begin():
        claim = await claims.get_for_update(command.claim_id)
        version = await claim.approve(
            actor_id=command.actor_id,
            expected_version=command.expected_version,
        )

        session.add(
            AuditEvent(
                action="claim.approved",
                actor_id=command.actor_id,
                object_id=claim.id,
                object_version=version.number,
                reason_code=command.reason_code,
                correlation_id=command.correlation_id,
                result="SUCCEEDED",
            )
        )

    return claim

Again, this is simplified sample code. The point is that the application service owns the transaction. A repository that quietly commits its own row makes the combined invariant impossible to reason about.

What about failed attempts? They should not be mixed blindly into the same business trail.

If authorization rejects the command before any state changes, that failure is usually a security or operational event. Record it in the appropriate protected log. If the business needs a durable record of rejected attempts, model that requirement explicitly. Do not add a failed audit row inside a transaction that then rolls back and assume it survived.

Append-only must be enforced below the service method

A comment that says “never update audit rows” is not a control.

For a product that reads its own timeline, the runtime may need SELECT and INSERT on the audit table. It should not need permission to rewrite history. If reads go through a separate service, the writer can be INSERT-only.

GRANT SELECT, INSERT ON audit_events TO application_runtime;
REVOKE UPDATE, DELETE, TRUNCATE ON audit_events FROM application_runtime;

PostgreSQL supports separate SELECT, INSERT, UPDATE, DELETE, and TRUNCATE privileges. The privilege documentation is worth reading because ownership matters: a table owner retains powers that an ordinary runtime role does not. The runtime role should not own the audit table.

I also like a trigger as an independent guard:

CREATE FUNCTION reject_audit_mutation()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE EXCEPTION 'audit events are append-only';
END;
$$;

CREATE TRIGGER audit_events_reject_mutation
BEFORE UPDATE OR DELETE ON audit_events
FOR EACH ROW
EXECUTE FUNCTION reject_audit_mutation();

PostgreSQL can execute BEFORE triggers for row updates and deletes, as its trigger documentation describes. The trigger and the revoked privileges protect the same rule through different mechanisms.

That duplication is deliberate.

If an accidental grant restores UPDATE, the trigger still rejects the change. If a migration drops the trigger, the runtime role still lacks permission. A migration owner or superuser remains more powerful than both controls, which is why this is append-only for the application threat model, not magically tamper-proof against every database administrator.

Stronger threats need stronger storage. That may mean separate credentials, external archival, write-once storage, signed exports, or independent replication. Hash-chaining rows can help detect some tampering, but it does not stop a privileged actor from rewriting the chain unless an independent trusted copy of the head exists.

Use the word tamper-evident carefully. Do not use tamper-proof casually.

Corrections are new facts

People make mistakes. An append-only trail still needs a correction model.

The wrong response is to edit the old event until history looks clean. That destroys the fact that the earlier record existed and was later corrected.

Instead, append a new event:

event 801: claim.approved
event 844: claim.approval_reversed
           corrects_event_id = 801
           reason_code = WRONG_REVIEW_QUEUE

The original event remains true as history: the system recorded an approval at that time. The correction is also true: an authorized action later reversed it for a stated reason.

The same rule works for imports and late-arriving events. Keep both occurred_at and recorded_at. If an event was performed elsewhere on Monday and imported on Wednesday, collapsing those times changes the story.

Corrections should be subject to normal authorization. “Append-only” must not become “anyone can append a convenient explanation.”

Derive timelines from evidence you already trust

Product timelines often become a second mutable history table. Every command updates the business state, writes an audit row, and then separately maintains a friendly timeline entry. Eventually one of those writes is missed and the timeline drifts.

I prefer the timeline to be a projection over durable evidence:

• immutable submitted versions;
• workflow decisions;
• semantic audit events;
• explicit correction links.

The UI can turn those records into readable sentences. It does not need a second source of truth.

09:14  Claim version 3 submitted by employee
09:16  Review assigned to Finance queue
11:42  Version 3 approved by reviewer
11:43  Notification delivery requested

The display text can change. The underlying facts do not.

This separation also helps with localization. Store a stable event type and structured fields. Translate the sentence at the presentation layer instead of preserving English prose as the only meaning of the event.

The audit trail is sensitive data

An audit trail often contains identities, resource references, access decisions, and internal reasons. It can become more sensitive than the table it describes because it connects those facts over time.

Do not store passwords, access tokens, cookies, signed URLs, full request bodies, raw documents, or unrestricted exception text. A secret does not become safe because it appears in a compliance table.

Use opaque identifiers, reason codes, event types, version numbers, and bounded allowlisted metadata. Put rare diagnostic detail behind a separate access path with its own retention and authorization.

This is closely related to the problem in Tracing a Request You're Not Allowed to Log. Observability should help reconstruct behavior without copying the sensitive payload everywhere. An audit trail has a longer memory, so its data discipline needs to be even stricter.

Access to the trail should itself be auditable when the domain requires it. Export is a high-risk action. So are bulk searches across employee or financial history. Read access should not automatically be granted to every developer who can view ordinary logs.

Retention also needs a real policy. Keeping everything forever is not a neutral choice. It increases storage, legal, privacy, and breach exposure. The right period depends on the domain and applicable obligations, not on how cheap the table seems today.

Test the property, not only the insert

The happy-path test is easy: perform a command and assert that an audit row exists.

The valuable tests go further:

• the state change and audit event roll back together;
• the event names the exact object version;
• the runtime role cannot update, delete, or truncate events;
• the database trigger rejects mutation even if a broader grant appears;
• a correction appends a linked event instead of changing the original;
• concurrent commands produce one valid business decision and one matching trail;
• sensitive fields never enter metadata;
• pagination gives a stable chronological view;
• export access is authorized and itself recorded when required.

Run the privilege tests using the runtime database role. A test that connects as the table owner proves very little about least privilege.

Also test failure paths. If audit insertion fails, the protected business command should fail with it. That may sound harsh. It is the only honest behavior when the audit event is part of the business invariant.

For lower-risk diagnostic logging, the trade can be different. OWASP recommends ensuring that logging failures do not stop the application from otherwise running. Both positions can be correct because an operational log and a mandatory audit trail do not have the same purpose.

When a normal log is enough

Not every application needs a database-backed append-only trail.

A personal tool with no shared authority, regulated data, money, or historical decisions may be well served by structured application logs. Adding immutable versions, database roles, correction events, export controls, and retention jobs can be needless weight.

The stronger design becomes worthwhile when at least one of these is true:

• a past decision must be defended;
• several people can change the same business object;
• actions affect money, access, policy, or obligations;
• an operator must distinguish a user action from automated work;
• a regulator, customer, or internal reviewer can ask for a sequence of events.

The deciding question is not “Do we need more logs?”

It is “Which past claim must this system be able to prove?”

The standard I use

I do not call a table an audit trail because it has created_at and user_id columns.

I call it an audit trail when:

• events describe business actions rather than database verbs;
• each action points to the exact version it affected;
• the event and the state change commit together;
• the runtime can insert history but cannot rewrite it;
• corrections are new linked facts;
• sensitive content is excluded by design;
• the stored sequence can reconstruct the decision a person is asking about.

An application log can tell you that code ran.

An audit trail should let you explain what the system decided, who was responsible for the decision, and which evidence was true at the time.

That is a much higher bar. For systems that handle money or authority, it is the useful one.