Audit-trail with start & end revisions

[vseadara]

Hi 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

[adamw]

Hello,

actually, Envers works quite differently - it never creates an audit-trail record for the entire object graph, but only for the modified entity. So if you have A->B->C relations, and you change a B, an audit record for B only will be persisted. If you add or remove a B from A's collection, than audit records for the affected A (optionally) and B's will be persisted.

Now, supporting start&end revisions is another problem. The "end" revision is in fact redundant information and can be calculated only basing on the start revision. However, having the field would allow for much faster history-queries - that's why the feature is on the roadmap for Envers for quite a long time, unfortunately I didn't yet find time to implement it.

But if I understand your questions correctly that's the way Envers works now.

Adam

[vseadara]

Thanks Adam. The earlier comment of duplication of audit-trail records is due to an incorrect example that I have setup. Our team is currently evaluating Envers further for our needs and will get back to you with any questions/concerns we might have.

[vseadara]

Hello Adam,

   In one of the presentations you made earlier, there is a specific mention about "Future Roadmap" w.r.t Different auditing strategies (storing only fields). Can you please elaborate on this and what exactly is going to be done. I am curious here to see if instead of storing the entire row in the audit-table, only the changes to the specific attributes will be recorded.

Thanks again,

Sagar

[adamw]

Hello,

firstly, I want to add support for persisting (and using) an "end revision" column in the audit tables, in addition to the current "start revision" column. This is a bit of redundancy, but will enable creating other features like queries that traverse relationships, and faster lookup of historic entities.

Further down the road I plan to add support for a way of storing an audit trail in a limited number of tables. The main table would only hold the name of the entity, id of the entity, id of the revision, name of the field and new value of the field. This is the most space-compact way, and a bit faster to store history than currently, but it's hard and expensive to reconstruct a historic entity.

But that's a long way ahead .

Adam

[vseadara]

Thanks Adam for the update.