Is it good practice to put service business logic based on audit trail?

Question

Our application creates audit trails in response to system actions and user actions. Our business logic depends on these audit trails to find out which user performed what action.

Is it good practice to have business logic use audit trail data?

Answer 1

It depends (but I guess for most real-world situations the answer is no, you risk to run into trouble).

A general-purpose audit trail may collect data which, depending on the details, might be affected by laws of data security and privacy of the jurisdiction where your application will be used. If those laws

• will force you to keep the data only accessible for privileged personal, or if they
• will force you to anonymize that data, or if they
• force you to delete the data after a certain period of time

you might run into trouble if this data is also used for controlling other business logic.

Another thing to consider is if the audit data is stored in a form which can be directly used for your business, or if it is stored as something like a weakly structured log file. The latter is likely when the audit trail was originally not meant to be used for business logic. If that is the case, you will have to parse the log file and reconstruct things like the user name from it. Such an approach can easily lead to a brittle and overcomplicated solution.

So if none of those reasons don't apply to your system, and you are confident they won't apply within a reasonable time period, then you may take that route.

Otherwise, it is probably simpler and more maintainable to build a "business-specific" trail which stores exactly the information you need to control your business logic, in exactly the form you are going to use it, with the access rights and life time your system requires.

Answer 2

No.

The problem is the audit trail should never change once written. So lets say I have some business logic

"Show the buy button if the customer has not bought previously"

And when the customer buys we add an Audit record.

We implement the business logic by checking that customers audit record and display the button accordingly.

Now, lets say an order goes wrong in some way. The audit trail has a buy written to it, but something went wrong somewhere and the customer is saying they didn't buy the item.

Maybe your suspicion is that they did in fact buy the item, but at this stage you just want to accept the possible loss and to get a happy customer. You are happy that your business logic is correct AND happy that the audit trail is correct AND that this case is so rare that a new process is not needed.

The temptation here is to do a data fix for the customer. Manually erasing their buy record so that the button reappears, and in normal circumstances you might do just that.

But! because your logic is driven from the audit trail this would mean you have to change that audit trail. Which you really, really don't want to do and perhaps legally can't do.

Answer 3

I agree with every word of @DocBrown's answer.

In general, any design of journals or audit trails should include some facility for clearance after a specific reasonable period of time - it cannot simply be a case of holding everything forever or indefinitely.

A good practice in databases is to design as many master tables as possible as insert-only, with no in-place updates. Queries which depend only on the latest data then filter for it. Past data is then pruned independently from the introduction of new data, perhaps as a daily or weekly batch job.

You then effectively get debug logs and audit trails (to some degree) entirely for free as part of the basic design, as well as the ability to cope with any business logic that doesn't just depend on the latest data but also on changes over time. And you can retain or prune different kinds of data on different schedules.

If there is then some performance requirement to maintain a table purely containing latest data, this is plumbed in separately, and perhaps maintained automatically by a trigger on the master table. This is a bridge that should only be crossed if you come to it, and is unlikely to apply to core elements of "personal" data.

If necessary, debug logs that provide additional technical detail can be stored for each row in the master tables, but separately, and which themselves contain no identifiable personal information. These can be pruned either more (or, if still useful in anonymous or statistical form, less) often than the primary personal data itself, without a confusion about where personal data actually resides and without cluttering tables which have a core business purpose.

Another common issue is with how to apply the principles of "normalisation". Generally speaking, it is perfectly appropriate to store "copies" of data which attest to what the facts were at a particular point in time and for a particular purpose. So for example, customer invoices may contain an address which is initially just a copy of their latest address on record, but which later attests to what their address was at the time the invoice was processed (independently of any changes which may have been made subsequently).

The existence of such copies also decouples such records from the master data, so that old addresses in the master table (and any associated data, like descriptive names for each address like "Mum's house", or the explicit period of residence, which isn't relevant for any invoice record-keeping purpose, or any past addresses which were not used for any invoicing purpose) can be freely and easily pruned, without complex pruning logic, and without affecting the integrity of invoice data (which typically has a fairly long, legally-mandated retention period).

And to come back around to the question itself, yes, any business logic that depends on an "audit trail" (other than logic for fraud detection and security assurance, which are the main purposes of audit trails), should either depend on master tables which keep a history by design (and which isn't itself treated purely as audit trail data), or the strictly necessary data for the business logic should be identified and transferred to its own table. Audit trails are, in general, the things you keep which have no business purpose other than for fraud detection and security.