Audit Logs

Audit logs or audit trails are records of data changes made in the system. These trails capture the source of change (user or another application) and details of the change. I have seen that such requirements are provided like security requirements, "the application should be secure". In other words most of the times auditing requirements are implicit and not much time is spent in specifying how it would be used. The expectation is that when (if ever) such information is required it better be there. What usually follows is that the technical people define the requirements and come up with the solution.

Most of the time the solution is something like: persist the change source (or user), date(time) or modification, column name, table name, old value and new value. This is the safest solution, because when implemented all the information about all changes are present in the database. There are couple of ways of implementing this. Do this in the code or use database triggers. This solution needs to be mindful of the following:

• If you are using object relational mapping then the auditing component needs to handle multiple updates within a single request of the same entity. For example in hibernate every time when a query which returns a list is executed the all the entities in memory are flushed (updated) to the database.
• The user information which typically is available at the top layer in the application (service or controller) needs to be made available to auditing component. This is done either by passing this through various layers in the application or via some thread global approach. The database trigger based component wouldn't even know who the user is unless the user information is put in every row in the database.
• If the auditing approach depends on reading the current state from the database then you need to manage additional connection in which the data can be read.
• You need to keep an eye on how many audit rows getting created in database and are they increasing the load on the database.