Design – Build – Run: Applied Practices and Principles for Production-Ready Software Development

17.2. Providing an Audit Trail

An audit trail can provide valuable information for reporting purposes. Remember, reports are not just for business people. Incident investigation, for example, can involve looking further into the database and transaction activity. Pulling the timeline together may require looking at a particular transaction and how the data has changed from end-to-end. A report showing changes to records, especially reference data, might be very useful for Service Delivery. A complete audit trail can often help with this by identifying which records have changed and who or what changed them. Knowing who or what changed (or created) a record generally means adding columns to the database tables, such as:

• CreatedDateTime

• CreatedBy

• LastModifiedDateTime

• LastModifiedBy

• LastAccessedDateTime

• LastAccessedBy

The columns act much like a "file explorer" in that you can see when a file was last modified and by whom. The drawback is that the application needs to update the columns, which presents a couple of challenges:

• What should the "By" columns contain? When a user triggers an action, the "By" column would normally contain that user's username or unique identifier. However, other processes and people are operating on the database records as well, such as batch processes and the operational staff. Ideally, the values should represent exactly who or what updated (or created) the record. In the case of a system-generated update, the value really should be a unique process identifier, ...