Designing an Audit Log Somebody Can Actually Use

Audit logging is added because a customer's security questionnaire asks for it, implemented as a model observer writing every change to a table, and discovered eighteen months later to be both the largest table in the database and useless for answering the question anyone actually asks — which is not 'what changed' but 'who did this, and why'.

Log actions, not mutations

A row-level change log records that a column moved from 2 to 3 at 14:07. An audit log worth having records that a named user approved inspection 4417 from a particular device, under a particular permission, as part of a particular request. The first is derived from your schema; the second is derived from your domain, and only the second survives a refactor or answers an auditor.

@dataclass(frozen=True)
class AuditEntry:
    id: UUID
    occurred_at: datetime
    tenant_id: str

    # Who: the real human, plus whoever they were acting as.
    actor_id: str
    actor_type: Literal['user', 'api_key', 'system', 'support']
    impersonated_by: str | None       # support access is the entry auditors read first

    # What: a domain action, stable across refactors.
    action: str                       # 'inspection.approved', not 'inspections.UPDATE'
    resource_type: str
    resource_id: str

    # Context: what makes it investigable rather than merely recorded.
    request_id: str                   # ties to logs and traces
    ip: str | None
    changes: dict | None              # before/after for the fields that moved
    reason: str | None                # supplied by the user where the action warrants it

# Written in the same transaction as the change it describes. An audit entry
# written afterwards can be missing for exactly the operation you care about.
with db.transaction():
    inspection.approve(actor=user)
    audit.record(AuditEntry(action='inspection.approved', ...))

Writing the entry inside the transaction is not a detail. An audit log that can be missing entries because the process died between the change and the log write cannot be used as evidence, which defeats the entire purpose.

Growth, retention, and the read path

This table only grows, and it grows faster than anything else. Partition by time from the beginning, so old partitions can be archived or dropped without a delete of a hundred million rows. Set a retention period per action category — security-relevant actions kept for years, routine reads for months — and make it configurable, because different customers are subject to different obligations.

• Index for how it is read: by resource, by actor, and by time range. Those three queries are ninety percent of the usage.
• Make it append-only at the database level. An audit log your application can update is not an audit log.
• Log the reads that matter too. Who viewed a record is frequently the question in a privacy investigation, and it is almost never recorded.
• Give customers a self-service view. Otherwise every audit request becomes an engineering task, forever.
• Redact secrets in the changes payload. An audit log full of tokens and personal data is a breach with excellent metadata.

The audit log is the one table where deleting a row should be impossible, and the one people most often write with an ORM that can.

The entry type worth getting right before any other is support impersonation. When an engineer or support agent acts as a customer, that must be unambiguous in the record — who they really were, who they acted as, and why. It is the first thing a serious auditor looks for, and it is the hardest thing to reconstruct later if the schema never had a place to put it.