Artistic Auditability

When you are designing software that updates data based upon business rules, or that creates new rows of data from somewhere else, be sure to pay attention to the creation of a useful audit trail. Yes this is more work than just updating the data or appending new records, but taking the care to do so has several benefits.

Folks in the financial or security industries use the terminology of Audit Trail to identify who accessed or changed which records, and when. A business audit trail however can be a more useful resource than for just tracking down suspicious activity.

Audit trails are useful both at record-level granularity and also at the file-level of detail. At the record level track the date and time that rows got created or updated, along with the name of the process performing the updates. Along with a timestamp, updates to master tables should also include the user ID and their IP address.

When you are making a global change behind the scenes programmatically to a file, create an easily identifiable annotation in a new, otherwise unused field.

If you are executing complicated business logic that determines activity that could have material impact to a customer, store the intermediate values along with the timestamp and version information; be sure to save this information for both affirmative actions and those that, in the end, result in no activity (sometimes the questions are about why things didn't happen).

For file level auditing it is often useful to have a logging table to track the batch processing of files, indicating a timestamp and overall record counts. Files being passed between systems should have a trailer record that contains both a record counter and a version indicator from the program that created it.

Audit trails assist in ferreting out performance problems and flaws in applications. They are also useful documentational research tools when a Support department needs to explain why a particular behavior occurred (or did not occur).