Saturday, May 19, 2012

High level design of audit trail system

This is the high level architecture design of my audit trail system. Initially I though Hibernate Interceptor could be great help, but when I found out that we are using WAS datasource, there is no way for me to use that in our system. Thus, I need to think a way out. After few weeks of design, POC, and testing, finally I come out this design.

I though this is an audit trail system, but why the design look like a database log? I have this feeling at the design stage. If I make a database log, I understand that the IO transaction will gonna eat up the resources, but we need to judge what is the most important one? Resource cost how much? Data integrity cost how much. It has been a hard time for me to put this consideration when designing the system. Thus I decide to make both.

There are few pieces in this design.

  1. Parameter table - Allow owner to decide which table that gonna need to audit. Just register here and let the audit engine do the rest.
  2. Stored Procedure - The audit trail engine. It read information from parameter table on what is needed to audit, create trigger and tie up with the original table, spawn a new shadow table from original table.
  3. Trigger - Act as database logger.
  4. Shadow table - Contain pre and post data of original table.
  5. Audit trail table - The main table that tells the summary story of shadow table.
I use GUID as the key to link the data across shadow table, original table, and audit trail table together. The GUID was generated from JAVA every time a record is save/update/delete. I put this key together with the BEAN object so that the system know what/which does for each audit trail entry.

The whole thing was made up from 10% work on JAVA, 60% work on Dynamic SQL and 20% work on Stored Procedure. I try not to limit the design only for JAVA, thus a lot of consideration need to think of when designing this shit.

No comments: