Most audit logs in production today are not audit logs. They are activity feeds. They write a row when something interesting happens, the row gets queried by an admin dashboard, and the table grows forever until someone notices the disk usage. Nobody has thought about what happens when a regulator or a plaintiff's lawyer asks who did what to a customer record on a specific date three years ago.
When that moment comes, the question is no longer whether the audit log exists. It is whether the log is trustworthy enough to be used as evidence. A log that nobody can prove was not edited after the fact is worth roughly the same as no log at all. This article walks through the design of an audit log that answers the legal question, the engineering question, and the operational question, without becoming the most expensive table in the database.
Photo by Kevin Bidwell on Pexels
What an Audit Log Is For
Three audiences pull on an audit log and pull in different directions.
Engineers want to know what happened during an incident. They need fast queries, structured data, and the ability to filter by user, resource, and time window. They will tolerate a few seconds of latency between an event and its appearance in the log.
Operators want to know what an admin did when troubleshooting a customer issue. They need full context for each event (request payload, affected fields, who triggered it, why) and a UI that does not require SQL to read.
Lawyers and auditors want to know what the system claims happened and whether that claim can be trusted. They need tamper-evidence, retention guarantees, and clear authorship. They will not tolerate any indication that the log could have been edited after the fact.
The system you build for engineers and operators only barely overlaps with the system you build for lawyers. The trick is to recognize this early and design two coordinated logs rather than one log that tries to be both.
The Two-Log Pattern
The pattern that works in practice is to split the audit log into a query log and an evidence log.
The query log is a normal database table. It is indexed, it is queried, it is read by the admin UI. It can be edited or truncated for operational reasons (a migration, a GDPR data deletion request, a bad data fix). Engineers and operators interact with this log directly.
The evidence log is append-only. Every event written to the query log is also written to the evidence log, in a different store, in a different format, with cryptographic links between successive entries. The evidence log is never edited. If a record needs to be redacted for compliance, the redaction is a new event in the log, not a modification of an old one.
This separation is described in detail in the OWASP logging cheat sheet, and it is the design pattern most large-scale audit systems use under the hood. The two logs hold equivalent data but answer different questions.
Tamper-Evidence Without Blockchain Hype
The way to make the evidence log tamper-evident is to chain its entries. Each entry includes the hash of the previous entry, so any modification to an old entry invalidates every subsequent hash. This is the same idea Bitcoin uses but it does not require any of the distributed-consensus machinery that gives blockchain its bad reputation.
A practical schema for the evidence log entry:
id(monotonic, gap-free)timestamp(ISO 8601, server-side, immutable)actor(user ID, service account ID, or system)action(verb, e.g. "user.update_email")resource(typed reference, e.g. "user:12345")before(hash of prior state)after(hash of new state)metadata(JSON, but no PII; PII lives in the query log)prev_hash(SHA-256 of the previous entry)entry_hash(SHA-256 of all the above)
The prev_hash chain means an attacker who edits any historical entry has to recompute every hash from that entry forward, which only works if they have write access to the entire evidence log. If the evidence log lives in a different system with different access controls (S3 with versioning and Object Lock, for example), that becomes a much higher bar than editing a database row.
For an external reference on the chained-hash design, the Wikipedia article on Merkle trees covers the broader pattern. Audit logs are usually a simpler linear chain, but the principle is the same.
Photo by Vladimir Srajber on Pexels
Where the Evidence Log Should Live
The evidence log should not live in the same database as the application. The reason is straightforward. If an attacker compromises the application database, they get write access to the audit log too, and the tamper-evidence does not save you because the attacker can just rewrite every hash.
The realistic options:
- A separate database with strict access controls and append-only enforcement at the schema level.
- An object store with versioning and object lock (S3 with Object Lock, GCS with Bucket Lock, Azure Blob Storage with immutability policies). The application writes new entries as objects and never has delete permission.
- A managed audit logging service. AWS CloudTrail, Datadog Audit Trail, and similar tools are designed for this and handle the immutability for you.
For most teams, the object-store approach is the right cost-performance tradeoff. The query log handles all the read traffic, and the evidence log is written once and read only when an investigation needs it. Storage is cheap. Reads are rare. The AWS Well-Architected Framework documentation covers the broader pattern of separating hot data from compliance data.
What to Log and What Not To
The temptation is to log everything. The reality is that overlogging makes the audit log harder to use, more expensive to store, and more dangerous from a privacy standpoint.
Log every state-changing action on a regulated resource (user records, financial data, health records, anything covered by a compliance regime). Log every administrative action (impersonation, password reset, role change). Log every authentication event (success, failure, MFA challenge, token revocation). Log every export of data to an external system.
Do not log every read. Read events on most resources are noise. The exceptions are reads of regulated data by privileged users, which should be logged because they often signal investigative behavior that needs to be reviewable later.
Do not log the data itself in the evidence log. Log a reference to the resource and a hash of the before-and-after state. The actual data lives in the application database (or in a redaction-aware blob store), and the audit log proves what changed without becoming a second copy of all the user data.
The audit log is the only system you cannot rebuild from a backup. Treat it like the financial records of the business it is documenting, because that is what it eventually becomes. - Dennis Traina, founder of 137Foundry
Retention and Deletion
Retention is the part most teams underestimate. A logged event has to live as long as the longest applicable retention requirement, which is usually six or seven years for financial data, and longer in some jurisdictions for healthcare or employment records.
That sounds simple until a user invokes a right-to-be-forgotten under GDPR or CCPA. The deletion request hits the application database and removes the user's PII. The audit log still references the user. If the audit log contained PII, you have a compliance problem. If the audit log only contained an identifier and a hash, you can mark the identifier as redacted in a new audit event without modifying the historical record, and the chain remains intact.
This is why the evidence log holds hashes and references, not the actual data. The application database can be cleaned. The audit log keeps its integrity by referencing the now-cleaned data through identifiers that point to nothing.
Photo by João Jesus on Pexels
Query Patterns That Actually Work
The query log needs indexes that support the questions investigators actually ask:
- "What did user X do between dates A and B?" needs a (actor, timestamp) index.
- "Who touched resource R in the last 30 days?" needs a (resource, timestamp) index.
- "What changes did anyone make to the email field on user records this quarter?" needs a (action, resource_type, timestamp) index and structured action names.
- "Find all impersonation events" needs a (action) index and a consistent vocabulary for action names.
The last one is the hardest in practice. If half your code writes user.impersonate and the other half writes admin.impersonate.start, you cannot search reliably. The fix is to define the action vocabulary up front, store it in a registry, and reject events that use unregistered actions. The OWASP guide referenced above covers the schema discipline in more depth.
The Integration With Application Code
The application emits audit events as part of the same transaction that changes the underlying data. If the transaction commits, the event is written. If the transaction rolls back, no event is written. This sounds obvious but it is the source of half the audit log bugs in production. A log that fires before the transaction commits will write events for changes that never happened.
A clean pattern is to enqueue audit events inside the transaction, into a transactional outbox table. A background worker reads from the outbox, writes to the evidence log, and marks the event as processed. The outbox table sits in the application database, so it commits or rolls back with the underlying change. The evidence log eventually receives every committed event, exactly once.
The microservices.io transactional outbox pattern covers the broader pattern. It works equally well in a monolith and is the cleanest way to keep the audit log consistent with application state.
When to Build Versus When to Buy
Building your own audit log makes sense when you have specific compliance requirements that off-the-shelf tools do not cover, when audit events are tightly coupled to your domain model, or when you want full control over the data and retention story.
Buying makes sense when you do not have the engineering capacity to maintain the storage, the retention rules, and the query layer, and when a vendor like Datadog Audit Trail or Splunk meets the compliance requirements you are subject to. The OWASP application logging checklist is a good reference to evaluate vendor offerings against.
For most teams, the realistic middle path is to build the application-level emission of audit events (the outbox pattern, the action vocabulary, the data classification) and use a managed service for the immutable storage of the evidence log. That isolates the boring infrastructure work to the vendor and keeps the domain-specific work in your codebase.
If you are setting up an audit log for a regulated industry and want to spec the design correctly the first time, 137Foundry helps teams build compliance-ready logging that engineers can use without a security background. The web development service page covers the broader scope of how this work fits with the rest of the application architecture.
The Short Version
An audit log that engineers can use and lawyers can rely on is two logs working together. A normal database table for queries, and an append-only chained-hash log for evidence. The application writes both inside the same transaction, the evidence log lives in immutable storage, and the action vocabulary is small enough that queries actually return useful results. Retention is decided up front. PII does not live in the audit log itself. The whole thing is set up before the first compliance audit asks for it, because retrofitting tamper-evidence onto a year of accumulated activity logs is, in practice, not possible.
