Audit trails and recording actions

Question

Background

A discussion that has come up at work recently is how we handle audit logging and the recording of events. We are integrating with a 3rd party app so triggers are a no no from the off so we are handling it in code. We've written a number of prototype components for handling it but nothing feels right as yet. The main issue being we want to create Facebook style time lines for the users to see what action have happened recently but these don't seem to fit well with how we record audits.

My question is how would be best to handle this type of scenario?

• Should we tailor the audit log tables to fit the requirements of the front end?
• Should we have separate tables to handle the "Actions" and have the events and auditing separate
• Should we look to a more message based architecture so this will be more like an Event sourcing type component?

Input from somebody who has done this type of system would be much appreciated.

Answer 1

Should we tailor the audit log tables to fit the requirements of the front end?

No. Generally this is better when you tailor the audit log tables to fit the requirements of auditors and then worry about the front-end later.

Should we have separate tables to handle the "Actions" and have the events and auditing separate

Yes. This gives you more control over events, and adding new ones as you need.

Should we look to a more message based architecture so this will be more like an Event sourcing type component?

I prefer to keep things simple and merely store in the db. However a message-based architecture may work well if you are integrating into a multi-tier application and your code is somewhere in the middleware. But here it depends on what you need and we'd need to talk more specific requirements than we have here.

Answer 2

As long as you are aware that the actions outside the application will not be logged (I personally would not try to do auditing except through triggers but third part products are usually a separate problem because you have a limited ability to change the db itself.), the approach is OK.

What is generally needed in auditing though is an approach where you store in separate tables for each table audited (to avoid the audit table causing locks). Next you store what you will need to know to revert the data and find the culprit if the change is a problem as well as what an auditor will want to know.

So we store both the old and new values, we store the id of the person making teh change and the name of application that sent in the change (We have multiple applications hitting the same database) and we also store the datetime of the change (very critical in finding all th3 records that need to be reverted from a bad production push that happened on 02/01/2013 at midnight).

Becasue our changes are often in groups and not just one record at a time, each audit table is also split up into a table that stores the info about the insert/update or delete (datetime, person, application, type of change) and one that stores the specific records changed or inserted or deleted. That way if you find one bad record change, you can see if there were possibly other records changed by the same person or application at the same time.

While you are designing teh system, go ahead and write the sql you need to revert data to it's old form. Even if you don't intend to use auditing for that, there is about a 100% chance you wil use it that way once the data is there. Sionce that usually happens under rpessure, ti s better to have a script or stored proc ready for reverting data.