Implementing Audit Trail for your application

I recently came across Davy’s post where he talks about implementing auditing. Davy talks about 3 requirements which I guess are quite common for audit trail:

1) We have to be able to easily retrieve what user X changed in the database on day Y or during a certain time frame.
2) We need to know what record X looked like at point Y in time, and be able to show a historic list of changes for a specific record.
3) We need to configure the duration for which auditing data must be kept in the database on a per-table (or per-schema) level

In this post I would like to talk about the way we did it for one of our recent projects. We had prepared a generic sort of table which would hold the audit data. This table mainly consists of AuditID, EntityID, EntityTypeID, UserID, AuditTime, AuditLevel & AuditData. AuditData is a TEXT column (we support SQL 2000) holding XML structure, for those changes made to an entity by a given user in form of old and new values, at a level specified. This would cater to the first requirement as it’s quite easy to search for activities of a given user.

Second requirement is little tough, as with this model we don’t allow direct queries against record attributes. But this can be achieved by 2 variants. First simple solution could be you have to search by entity type & a specific entity in turn for a date range & browse through records to see changes done on them. Second option is take this AuditData out and restructure its layout with help of OLAP cubes to produce meaningful quick search.

Third requirement is very much there for us in order to control size of this massive table but we for simplicity purge entire database at a given time interval & do not make it adjustable for each entity. Though I feel providing that flexibility shouldn’t be difficult considering the design we have laid out.

A final thing I wanted to highlight is about generating the XML data itself. It’s always better to do such processing in .NET code & if you have been using DataSets you get old values extraction feature out of box. We are yet to see a significant performance degradation due to this design but as a contingency plan I was thinking of making this XML conversion & insertion into AuditDB an asynchronous activity with help of Queue like data structure.

Look forward to read your thoughts on above.

Food for thought – Designing Presentation Layer

I was having discussion with a fellow architect where he shared an idea with me. It was pertaining to automating forms creation in presentation layer. Normally there are techniques like visual inheritance relevant here. But what he did is created few custom controls. Specialty of these controls is they take an object graph and automatically build the UI depending on the properties and associations of the object.

Benefits -

1) If tomorrow you update your object model with additional properties you don’t have to alter your UI as its auto generated.

2) You update your custom control UI changes throughout the application.

This would normally require you to map every primitive type to a UI control, but what he did is created his own custom types by inheriting from primitive types & these custom types would then map to the corresponding UI control. Though he has a successful running application build on this framework I am yet to try it. Let me know if you have similar ideas (can I call this a pattern ???) for UI or if you use this one in your project.