What an audit trail has to record

An audit trail settles arguments. Someone asks what happened to a record, and the log answers without relying on anyone's memory. Whether an auditor accepts that answer depends on what each entry records and who can change it afterwards.

Record six facts about every change

Each entry should stand on its own. An auditor should be able to read it without a second system or a guess.

• Who. The individual user account, not a shared login. If a scheduled job or another system made the change, name it. If support staff acted for a user, record both.
• What. The record type, its identifier, the field and the action: created, updated, deleted, approved, rejected or exported.
• When. The time the server recorded the event, to the second or finer.
• Before and after. The old and new value of every field that changed.
• Where from. The network address, the session and the channel: web screen, mobile app, file import or another system.
• Why. A reason code, an approval reference or a ticket number that ties the change to a decision.

Suppose a clerk raises a customer's credit limit. A good entry names the clerk, the customer, the old limit, the new limit, the time, the terminal and the approval reference. A poor entry says "customer updated".

Record the old value as well as the new one. The new value alone shows where a record ended up, not what it replaced. To rebuild the old value, you would have to find the previous entry and trust that nothing was missed in between.

Deletions need the same care. When a user deletes a record, log the full record as it stood.

Make the log append-only, even for the application

An auditor will ask whether anyone could have changed the log afterwards. The answer has to be no, and that includes the application itself. If the application's database account can update or delete log entries, then a bug, a careless script or a stolen administrator password can rewrite history. Nobody could prove it did not happen.

• Give the application permission to add log entries and nothing else.
• Keep the log apart from the business tables, under separate access rights.
• Chain the entries. Each carries a fingerprint computed from the one before, so an altered or missing entry shows up in a routine check.
• Send a copy to a second location that the application's administrators cannot reach.
• Correct a mistake with a new entry that refers to the old one. Never edit the original.

Rule of thumb: if the person who could commit a fraud could also tidy the log afterwards, you do not have an audit trail. You have a diary.

Let an auditor search it without a developer

A log that only a developer can query slows every audit, and the auditor starts to wonder what is being filtered out. Searchable without a developer means a screen inside the system that a read-only auditor role can use unaided.

• Filter by user, record, field, action and date range.
• Show the full history of one record in time order, with old and new values side by side.
• Show everything one user did in a given period.
• Show field names a business person recognises, not internal codes.
• Export the results to a file, and log that export as an event.

This only works if entries are structured. A sentence such as "user changed the limit to 500,000" reads well, but nobody can filter or compare it. Store the user, the record, the field and both values as separate fields. Build the readable sentence on the screen.

Take the time from one trusted clock

Timestamps are evidence. Take the time from the server, never from the user's device, because a device clock can be wrong or set wrong on purpose. Keep all servers synchronised to one time source. Store every timestamp in UTC, and convert to local time only on display.

Show the time zone on screen and in every export. An entry that says 09:15 with no zone reads one way in Colombo and another in London. Where two events can land in the same second, add a sequence number so the order is never in doubt.

Log reads and failed attempts as well as changes

For sensitive records such as a patient file or a salary record, who looked matters as much as who changed. Log each view, search, print and export with the same who, when and where from. Without that, a privacy complaint has no answer.

Log failures too. A failed login, a refused permission and a rejected approval are all events. A run of refused attempts on one record followed by a success is a story the log should tell.

Keep the log as long as the records it describes

The log is itself a regulated record. Keep it at least as long as the records it describes, and confirm the period your regulator expects. Plan for volume: move older entries to cheaper storage that stays searchable. Include the log in backups and test the restore. When the system deletes a customer record on schedule, decide what happens to its audit history, and log the purge when it runs.

Check for the common mistakes

Four mistakes turn up again and again.

• Logging to a table the application can update.
• Logging free text instead of structured fields.
• Recording the new value only.
• Leaving out failed attempts.

Then run this checklist.

1. Every entry names an individual user or a named system process.
2. Every entry holds the record, the field, the action and both values.
3. Timestamps come from a synchronised server clock and are stored in UTC.
4. Entries carry a source and a reason or approval reference.
5. The application can add entries and cannot edit or delete them.
6. The system logs reads of sensitive records and failed attempts.
7. A read-only auditor role can search and export the log unaided.
8. The log has a retention period, sits in the backups and has passed a restore test.

Stack9 builds audit trails this way: append-only, with the user, the time and the values before and after, and searchable by an auditor without a developer in the room. We write these points into the specification as acceptance tests, so we check them before go-live.