Audit Logs That Answer the Question an Auditor Will Actually Ask

An auditor doesn't ask to see your logs. They ask a specific question: who accessed this customer's record, when, and under what authority, between these two dates. If the answer takes you twenty minutes of joining tables and guessing at intent, you don't have an audit log. You have an application log that happens to record events.

This distinction gets discovered at the worst possible time, mid-audit, when someone asks for evidence that only authorized staff touched a specific PHI record during a support ticket. If your logging was built for debugging, it tells you an UPDATE happened on the orders table at 14:02. It does not tell you which human did it, on whose behalf, or what permission let them do it. That gap is the whole audit.

The fix is not more logging. It's logging the right three things, structured so the answer to the auditor's question is a single indexed query, not an investigation.

The Question Behind the Checkbox

Every compliance framework phrases the requirement differently, but the underlying question is the same shape: who touched what, on whose behalf, and were they allowed to. SOC 2 wants evidence of access controls in operation. HIPAA wants an accounting of disclosures. PCI wants proof that only authorized roles reached cardholder data. Strip the framework language away and you're left with five variables: actor, action, resource, tenant, and authority.

If your logging schema doesn't have first-class columns for all five, you'll be reconstructing at least one of them by hand every time someone asks. That reconstruction is exactly what auditors flag as a control weakness, independent of whether the access itself was legitimate.

Build the Schema Around Actor, Resource, and Tenant, Not the Event

Most teams log the way their ORM makes it easy: an event name and a payload blob. That's fine for debugging a crash. It's useless for an auditor, because the identity and authority context lives in application memory at request time and never makes it into the row. Put it in the row.

create table audit_log (
  id            bigint generated always as identity primary key,
  occurred_at   timestamptz not null default now(),
  tenant_id     uuid not null,
  actor_id      uuid not null,
  actor_type    text not null, -- 'user' | 'service' | 'support_impersonation'
  on_behalf_of  uuid,          -- set when actor_type is impersonation
  action        text not null, -- 'read' | 'update' | 'delete' | 'export'
  resource_type text not null,
  resource_id   uuid not null,
  authority     text not null, -- the role or permission that allowed it
  request_id    uuid not null,
  ip_address    inet
);

The field that most teams skip is authority. It's the difference between "this user updated the record" and "this user updated the record because they hold the billing_admin role on this tenant." That single column is what turns a log row into a defensible answer, because it proves the access was a consequence of a granted permission, not an accident of a bug in your authorization check. Every tenant-scoped table holding PII, PHI, or financial data should write here on read and write, not just write. Auditors ask about disclosure, and a SELECT is a disclosure.

Impersonation and Support Access Need Their Own Trail

The hardest audit question we've had to answer in practice isn't about ordinary user activity. It's about the moment a support engineer steps into a customer's account to fix a ticket. That access is legitimate, necessary, and exactly the kind of thing an auditor will zero in on, because it's the path with the least friction and the most potential for abuse.

The rule we apply when we design an admin console for support use is that impersonation gets its own actor_type, its own on_behalf_of column pointing back to the support agent, and a session-start and session-end row bracketing every write made during the impersonation window. If a support agent's session can't be reconstructed as a contiguous, bounded block of actions tied to a ticket number, the console isn't safe to hand to a support team, regardless of how good the rest of your access controls are.

Immutability Is the Point, Not a Feature

An audit log that a database admin can quietly edit or delete is not evidence of anything. Treat the table as append-only from day one: no UPDATE, no DELETE, enforced with a trigger or a restricted grant, not just a convention your team agrees to follow. In Postgres this is a five-line trigger that raises an exception on any statement other than INSERT. It costs nothing and it removes an entire category of question an auditor will otherwise ask about who can tamper with the record.

Keep the audit table logically separate from your operational tables even if it lives in the same database. If you're running per-tenant databases, as we do across the fleets we operate, the audit log for a tenant should live and travel with that tenant's data, so a database migration doesn't sever the history from the record it describes. The same discipline that makes a reconciliation report prove a migration actually worked is what keeps an audit trail intact across a cutover done without a maintenance window.

Retention, Export, and Tenant Isolation

An audit log with a 30-day retention window is the fastest way to fail an audit before the auditor asks a single hard question. Most compliance regimes expect multi-year retention on access records for regulated data, and "we rotate logs after a month to save on storage" is not an answer that survives the conversation. Decide retention per resource type, not globally: transactional access to financial or health records needs years, routine UI navigation events don't need to be kept at all.

• Every audit query must be scoped by tenant_id at the query layer, not filtered after the fact, so a support engineer investigating tenant A can never accidentally pull rows from tenant B.
• Exports for an auditor should come out as a filtered, timestamped extract (actor, resource, timeframe) that stands on its own, not a raw table dump they have to interpret.
• Log writes should never share a transaction with the operation they describe; if the audit write fails, the business operation should still fail loudly, not succeed silently with no trail.

When Full Audit Logging Is Overkill

This machinery isn't free. Writing an audit row on every read adds latency and storage cost, and building the schema discipline into every service takes engineering time. For a two-person internal tool with no regulated data and no compliance requirement, none of this is worth the cost. Build it where access to a resource is itself a compliance question: PII, PHI, payment data, anything a contract or a regulation says you must be able to account for.

The decision rule we use is simple: if the answer to "who could have seen this record and why" needs to survive a stranger asking it under oath, it belongs in the audit table with actor, resource, tenant, and authority filled in. Everything else can stay in your ordinary application logs, where it's cheaper and nobody will ever ask you to produce it on demand.