Audit-trail with start & end revisions
vseadara Feb 14, 2010 12:42 AMHi Adam,
First of all this is a great solution for AuditingTrail needs that you have here and thanks for it. As part of our requirements we do have a requirement to record audit-trail for all the changes that happen in the system and also the ability to recreate entities as of a certain revision.
However there are a couple of concerns which is actually preventing us from using this solution.
For a quick overview, we have an object A which holds a collection<B> (one-many relationship). Simillary B holds collection<C> and this extends further. So now as I understand any change to the B in the relationship will actually create audit-trail records for the entire object graph starting from A and then the entire collection of B's in the relationship. This will further propagate to any objects further deep in the relationship.
The concern with this approach is the transaction time with the insertion of audit-trail changes for all the objects in the object graph. In addition the size of the database is also a concern since our system is very change intensive and with all the complex relationships it will bloat up the size of the database.
Instead I am interested in just persisiting only the changed objects in the audit-trail table along with the starting and final revisions that are valid for each of the changes.
Consider the following scenario where A holds a one-many relationship with B.
At Rev1 A is created along with B.
At Rev3, there is a change to an attribute of B( b-value changed from b1->b2), hence only the changed B record will be inserted in the audit-trail table with the appropriate revision #. A will not be duplicated in the audit table for Rev3.
A_audit
Revision # a-id a-value Low Revision High Revision
Rev1 1 a1 Rev1 infinity
B_audit
Revision # a-id b-id b-value Low Revision High Revision
Rev1 1 1 b1 Rev1 Rev2
Rev3 1 1 b2 Rev3 infinity
When querying for A as of Rev2, the appropriate records have to be used from the audit tables based on the "Low Revision" & "High Revision". In this case the 1st row from B_audit applies. If the user queries for Rev3, then the 2nd row applies.
So as I understand there will be 2 changes from the current approach:
- Only record the audit-trail for the changed object in the relation-ship with the appropriate revision #'s. This way we do not duplicate the audit-trail records for the entire object graph.
- The AuditReader/Query should be modified to query the appropriate revision of the object while building the object-graph.
Do you think if this can be achieved with Envers or see it in your road-map.
Thanks for going through this long posting and let me know if any of this is not clear enough.
Thanks,
Sagar