Who changed this record? Designing an application audit trail

When a customer calls to say the total on an order has changed and asks who changed it, being able to answer within minutes means your application has a working audit trail. An audit trail records business-meaningful actions in a separate place that cannot be altered afterwards: who did what, when, to which record, and from which value to which. It is not the application log a developer reads while chasing a bug. The audience is different, the retention period is different, and the integrity requirement is different. It is also one of the hardest things to add late, for a simple reason: you cannot generate history retroactively.

An application log and an audit trail are different records

The application log is written for developers. It is free text, it is noisy, it is kept for days, and it stops being useful the moment the bug is fixed. The audit trail is read by support agents, internal audit, legal counsel and often the customer. It has to be structured and queryable, it stays around for years, and its contents get used as evidence when someone disputes what happened.

The cleanest test of the difference: delete the application log and the system keeps running, with nobody noticing. Delete the audit trail and you have nothing to show when a disagreement lands on your desk.

Collecting server and infrastructure logs, running a SIEM and monitoring around the clock is a separate subject, and we covered it in the piece on log management and SIEM. This article is about the business record your own application produces.

What belongs in an audit record

The OWASP Logging Cheat Sheet reduces the problem to four questions: when, where, who, what. OWASP ASVS 5.0, released in May 2025, states the same requirement in V16.2.1: every entry should carry enough metadata to allow a detailed investigation of the event timeline. In practice that maps to these fields:

• Event type: a fixed, machine-readable name such as order.total_updated. Not a sentence.
• Timestamp: ASVS V16.2.2 asks for synchronized clocks across logging components and timestamps written in UTC or with an explicit offset.
• Actor: user id, actor type (person, service account, scheduled job) and, when relevant, the account being acted on behalf of. If support staff can operate inside a customer account, record both identities.
• Context: source address, client, and the request or correlation id.
• Object: type and id of the affected record.
• Outcome: did the action succeed, was it denied, did it error.
• Change: the before and after values of the fields that changed. Storing the diff rather than a full row copy saves space and reads better.
• Reason: refund justification, ticket number, who approved it.

That last field is the one most often skipped and most often needed later. "The price went from 1,200 to 900" tells you very little on its own. "The price went from 1,200 to 900 against ticket #4471, approved by the regional manager" ends the argument.

What must stay out

Passwords, session tokens, API keys, card data, database connection strings and encryption keys do not belong in audit records. ASVS V16.2.5 draws that line explicitly, and allows session tokens only in masked or hashed form. When you record access to sensitive data, record the fact of access, not the data itself. Keys leaking into code and logs is its own topic, covered in our guide to secrets management.

Building entries by string concatenation carries a second risk. If a user-supplied value goes straight into a log line, someone can embed a line break and forge an entry. This is log injection, and ASVS V16.4.1 requires data to be encoded against it. Writing entries as structured records, JSON for instance, removes most of that exposure.

Audit records usually contain personal data, so who may read them and how long they are kept belong in the design rather than in a later decision.

Which events to record

Recording every database write is tempting. The result is usually a table nobody opens and nothing can search quickly. The better question is which actions might be disputed one day.

A short starter list does more work than an exhaustive one. Authentication attempts, both successful and failed (ASVS V16.3.1). Authorization denials and permission changes (V16.3.2). User creation, removal and role assignment. Anything that moves money: prices, discounts, refunds, credit limits. Deletions and bulk updates. Data exports and report downloads. Configuration changes. Contract, approval and consent records. Actions taken by support on a customer's behalf.

Start with fifteen event types, not a hundred. After three months, look at the questions you could not answer from the table and grow the list from there.

Where the record is produced: application or database

There are three approaches, and each one is incomplete on its own.

The application layer knows intent: who did it, from which screen, with what justification. Its weak spot is that somebody connecting straight to the database and running an UPDATE leaves no trace.

Database triggers and system-versioned (temporal) tables are the mirror image. They catch every change but usually cannot tell you who made it, because on a pooled connection the user they see is the application's database account. The fix is to write the end user's identity into a session variable at the start of the transaction and have the trigger read it from there. These tables have shipped in SQL Server since the 2016 release and in MariaDB since 10.3.4. PostgreSQL has no core support for them, so you build the equivalent with triggers or an extension.

The third option is change data capture, reading row-level changes from the database write-ahead log. It does not slow the application down and it misses nothing, but it still carries no actor and no reason.

The combination that works in practice: make the application layer the primary source of the audit trail, since that is where intent lives, and keep the database-side mechanism as a safety net for changes that were never supposed to happen.

Write the audit record in the same transaction

A common mistake is to update the business record first and write the audit entry afterwards, over a separate connection or straight onto a queue. The two can fail independently. The order gets updated and no audit entry appears, or the reverse happens and a change that never took effect is recorded as fact. Both outcomes destroy trust in the table.

Write the audit entry inside the same database transaction as the data it describes. If the entry also has to reach another system, the transactional outbox pattern solves it: write to an outbox table in the same transaction and let a separate process move it to its destination. We covered that pattern in the article on background jobs and queues.

How immutable is immutable

ASVS V16.4.2 requires logs to be protected from unauthorized access and from modification, and V16.4.3 asks for copies to be shipped to a system isolated from the application. In day-to-day terms that comes down to three moves:

• Grant the application's database role INSERT only on the audit table. No UPDATE, no DELETE.
• Keep the archive copy in a separate account, on storage locked against deletion and overwriting (object lock, WORM).
• If you want tampering to be detectable, chain the records: include the previous entry's hash in the input when hashing the current one. Inserting an entry in the middle or editing an old one breaks the chain.

To be straight about it, a database administrator with full rights can change anything. The chain and the off-box copy do not prevent that, they make it visible. That visibility is where the evidentiary value of a record actually comes from.

Clocks, ordering and correlation

ISO/IEC 27001:2022 lists clock synchronization as its own Annex A control (A.8.17). The reason is practical rather than theoretical: if you cannot order events, the analytical value of your records drops, entries from two systems collide, and incident review turns into guesswork. Point every server's NTP at the same source, store timestamps in UTC, and convert to local time only for display.

Even millisecond resolution produces ties, and you cannot recover the order of two entries written in the same millisecond from the timestamp alone. Add a monotonic sequence number. When one user action fans out into several records, stamp them all with the same correlation id. That is the only way to answer what a single click set in motion.

How long to keep it

The answer depends on what the record proves. In card data environments, PCI DSS v4 requirement 10.5.1 calls for at least 12 months of audit history, with the most recent three months immediately available for analysis. Where entries carry the weight of commercial records the scale is different: in Turkey, article 82 of the Commercial Code requires commercial books and supporting documents to be kept for ten years from the end of the calendar year of the last entry.

Ten years of history in a single table will slow your database down. Partition the table by date, keep the last three to six months online and indexed, and move the rest to cheaper locked storage. What growing tables do to query plans is covered in our piece on database slowdowns.

Can you show the trail to the customer

"Are user actions recorded, and can we see those records?" has become close to a standard line in enterprise security questionnaires, and the same records get requested as evidence during ISO 27001 certification. Past a certain point the audit trail stops being an internal detail and becomes a product feature you can sell.

Three things matter when you build that screen. First, who can see which entries is an authorization question: a customer admin should see their own tenant's records and nobody else's (authorization models). Second, without filtering and export the screen goes unused. Third, keep internal fields out of it: table names, stack traces, service identifiers.

There is a side benefit too. Support stops walking over to a developer to ask who changed a record.

Where to start

Do not try to build a perfect audit system. Build one that works. List the fifteen events that would need evidence in a dispute. Create one table with a fixed schema, write entries inside the same transaction as the business change, take UPDATE and DELETE away from the application role, and include a reason field. Three months later, take the questions support actually received and try to answer them from that table. Every question you cannot answer will tell you exactly which field or event is missing.