What an audit trail should tell you

Nearly every piece of business software claims an audit trail. The phrase covers a wide range. At one end is a table of log lines saying that a record was updated. At the other is a system that can show, for any figure on any screen, the chain of actions that produced it.

Both get a tick in a feature comparison. Only one helps on the day someone asks a hard question about a number.

Five things it should tell you

For any figure that matters, whether a weight, a balance, a price or a status, an audit trail worth the name answers five questions.

1. What changed. The specific field on the specific record. Not “order updated”.
2. Who changed it. A named person, or a named automatic process. Not “system” for everything, and not a login that five people share.
3. When. The time the action happened, set by the system and not typed by the user.
4. Why. The reason, where one exists: a remark, a linked approval, the rule that was applied.
5. What it was before. The earlier value, exactly as it stood.

The fifth is the one most often missing, and it matters most. Without the earlier value you know that something happened. You cannot say what.

A log bolted on

The common design starts with a system that stores current values. Each record is a row. A change writes over the row. The audit trail is a second mechanism added alongside: whenever the software saves a record, it also writes a line to a log.

This can work well. It has four weaknesses worth understanding, because they come from the design and not from carelessness.

The log and the data are separate things. The figure on the screen comes from the row. The log is a commentary on it. If the two disagree, the row wins, and nothing forces them to agree.

It covers the routes someone remembered. The log line is written by the code that handles a normal edit. A bulk import, a correction by an administrator, a scheduled job or a fix applied straight to the database may each take a route that writes no line. The figure changes and the trail is silent.

It often records the fact and not the content. “Modified by user 14 at 14:02” says an edit took place. If the values before and after were not captured, the trail cannot say whether the edit fixed a typing slip or put in a different number altogether.

It can be configured away. A log that is switched on module by module, trimmed after a period, or kept where only technical staff can read it is a trail in name. Ask who can see it, and for how long.

A record that is the data

The other design turns the arrangement round. The system does not store the current value and then log the changes to it. It stores the changes, as a list of events that is only ever added to, and works out the current value from them.

In that design the audit trail is not a feature. The figure on the screen is the result of the events, so it has no way to change without one. A correction cannot overwrite anything, because nothing is overwritten. It is one more entry, placed after the original, and both remain. There is no second mechanism to forget and no log that can drift from the data, because the log is the data.

A paper ledger has always worked like this. Nobody describes a ledger as having an audit trail feature.

What this looks like in practice

A wealth advisory firm manages portfolios for high net worth households, across eleven asset classes. Its figures come from statements: PDFs and other files in many layouts, uploaded by staff.

In the system the firm runs today, an upload does not change the portfolio. The file is read and held as a preview. A member of staff sees what the system understood, settles anything that does not match, and then commits or discards it. The parse, the commit and the discard are each events. So is every change to a holding that follows, and so is every revaluation after a price refresh.

Each account therefore has a history showing who did what, and from which source file. When a household’s value moves, the question “why” has an answer that can be read off: this statement, committed by this person at this time, or this price refresh on this date. The customer’s app shows the same figures and never writes to them.

Nobody assembled that history. It exists because of how the figures are stored.

What no design gives you for free

Two of the five answers still depend on how the system is run.

“Who” depends on people having their own logins. One shared account at the gate or in the stores defeats any audit trail, however it is built.

“Why” has to be asked for at the time. A system can require a remark on a correction, or route it through an approval. It cannot recover a reason nobody gave. Decide which changes need a stated reason, and have the system insist on one for those and only those. A reason demanded for everything is soon a full stop typed into a box.

Who can change the record

The five questions above are about what the trail says. A sixth is about the trail itself: who is able to alter it. The honest answer is often longer than the list of users.

Work outwards through four groups.

• Users. Can someone with ordinary access edit or delete a past entry, or backdate a new one?
• Your administrators. Is there a “super user” route that corrects data without leaving a line?
• The vendor’s support staff. When they fix a problem for you, do they work through the application, where the fix is recorded under a name, or directly on the database, where it may not be?
• The software itself. Do upgrades, imports and scheduled jobs write to the same trail as people do?

None of this assumes bad intent. A support engineer who tidies a broken record at your request is doing you a favour. But a record that can be tidied without trace is evidence only for as long as everyone with access is trusted, and the people asking hard questions about a number, an auditor, a customer, a supplier in a dispute, are not obliged to extend that trust.

The strongest position is a record where a correction by anyone, at any level of access, is one more entry with a name on it. If the design does not give you that, you should at least know which routes leave no line, and who holds them.

What you keep if you change vendor

An audit trail is usually thought of as a feature of a product. It is better thought of as a possession of the business. The history of who did what in your operation is yours, and it should outlast any one supplier.

That is worth testing before you sign, because the usual export is a disappointment. Many systems will hand over current records: open orders, stock balances, customer lists. Fewer hand over the history behind them in a form anyone else can read. You leave with the closing figures and lose the account of how they came about.

Ask four things.

1. Can the full history be exported, not only current balances? Every change, with the person, the time and the earlier value.
2. Is the export readable without the product? A plain, documented format, not a backup file that only the vendor’s software can open.
3. Are the meanings written down? A list of what each kind of entry is, in words. A column called “status 4” is no use to the next team.
4. Who holds it day to day? Whether the record sits where you can reach it, or only where the vendor can.

There is a quiet connection between this section and the last. A record that is the data is easy to hand over, because the list of events is the whole truth and the current figures can be worked out from it again. A row with a log beside it is two things, and the log is the one that tends to be left behind.

Questions to ask any vendor

Ask for these to be shown, not described.

1. Change a figure on a record. Now show me the earlier value, who changed it and when, on a screen an ordinary user can reach.
2. Do the same after a bulk import. Is each imported row attributed, and to whom?
3. Have an administrator correct something. Does it appear in the same trail, or is there a route that leaves none?
4. Is the trail kept for every type of record, or enabled module by module?
5. Can a user, an administrator or your own support staff edit or delete an entry in the trail?
6. How long is it kept, and is anything ever trimmed?
7. Can I see what a record looked like at the end of last month?
8. When a calculated figure such as stock or a balance looks wrong, can I see the entries it was calculated from?
9. When an approval is given, are the values that were approved stored with it?
10. When your support staff correct our data, where does that appear, and under whose name?
11. If we leave, do we take the full history in a format another system can read, with a written list of what each entry means?

A well-made system with a bolted-on log can pass most of these. The point of asking is to learn which ones it passes by design and which by effort, because effort is what lapses.

Phlo, the framework Jetbro builds on, takes the second approach. Every action is stored as an event, the list is append-only, and every screen is worked out from it. The audit trail comes free: nobody assembles it, because it is how the system stores what happens. It is also what gets handed over. When your own engineers build on Phlo, they build on this: a record of your operation that cannot be quietly rewritten.