How to build a change tracking system - not audit system

Question

I have a requirement in which I need to capture data changes (not auditing) and life cycle states on inventory.

Technology: Jave, Oracle, Hibernate + JPA

For the data changes, we have been given a list of data elements that are to be monitored. If the element changes we are to notify a given 3rd party vendor. What I want to do is make this a generic service that we can provide to any of our current and future 3rd party vendors.

We don't care who made the change or what the new value is just that it changed.

The thought is that the data layer of our application would use annotation on each of the data elements. If that data element changed, then it would place a message into a queue. The message bean would then read the queue and make an entry in a table.

Table to look something like the following:

Table Name: ATL_CHANGE_TRACKER  
Key  columns  
INVENTORY_ID          Inventory Id of the vehicle
SALEEVENT_ITEM_ID     SaleEvent item of the vehicle
FIELD_CHANGED_ID      Id of the field that got changed or action. Link to subscription
UPDATE_DTM            Indicates the date time when change occured.

For a given inventory, we could have up to 200 entries in this table (monitoring 200 fields across many tables).

Then a daemon for the given 3rd party would then read from this table based on the fields that it has subscribed to (could be all the fields). It would then read what every table it is required to to create the message to be sent to the 3rd party. Decouple the provider of the data and the user of the data.

Identify the list of fields/actions that are available

Table Name: ATL_FIELD_ACTION  
Key columns  
ID  
NAME                    Name of the field/action - Example Color,Make
REC_CRE_TIME_STAMP  
REC_CRE_USER_ID  
LAST_UPDATE_USER_ID  
LAST_UPDATE_TIME_STAMP  

Subscription table, if 3rd Party company xyz is interested in 60 fields, the 60 fields will be mapped to this table.

ATL_FIELD_ACTION_SUBSCRIPTION  
Key columns  
ATL_FIELD_ACTION_ ID      ID of the atl_field_action table
CONSUMER                  3rd Party Name
FUNCTION                  Name of the 3rd Party Transmission that it is used for
STATUS  
REC_CRE_TIME_STAMP  
REC_CRE_USER_ID  
LAST_UPDATE_USER_ID  
LAST_UPDATE_TIME_STAMP  

The second part is that there will be actions on the life cycle of the inventory which will need to be recored also. In this case, when the state of the inventory changes a message will be placed on the same queue and that entry will be entered in the same table.

Again, the daemon will have subscribed to these states and will collect the ones it is interested in.

The goal here is to not have the business tier/data tier care who wants the data - just that it needs to provide it so those interested can get it.

Wonder if anyone has done something like this - any gotchas - off the shelf - open source solutions to do this.

Answer 1

For a high-level discussion on the topic, I would suggest reading this article by Martin Fowler.

Its sounds like you have write-once, read-many type of data, it might produce large volumes of data, and the data is different for different clients. If you ask me, it sounds like this may be a good place to make use of either a NOSQL database or hack your Oracle database to act as a NOSQL database. See here for a discussion on how someone did this with MySQL.

Otherwise, you may look at creating an "immutable" database table and have Hibernate write new records every time it does an update as described here.

Answer 2

Couple things.

First, you get to do all of this work yourself. The JPA/Hibernate lifecycle listeners, while they have an event for when an update occurs, you aren't passed the "old" object and the "new" object. So, you're going to have to keep track of what fields change using some other method.

Second, again with lifecycle listeners, be careful inside of them, as the transaction state is a bit murky. At least on Glassfish/EclipseLink, I've had "strange" problems using either the JPA or JMS from a lifecycle listener. Just weird behavior. We went to a non-transactional queue to capture all of our information that we track from the lifecycle events.

If having the change data committed on its own transaction is acceptable, then there is value is pushing the data on to a faster, internal queue (which can feed a listener that posts it to an MDB). This just gets the auditing "out of band" with your transaction, give you better transaction throughput. But if you need to have the change information committed with the same transaction, this won't work. For example, you could put something on the queue and then the transaction may be rolled back (for whatever) reason, leaving the change on the queue showing it happened, when it in fact failed. That's a potential issue with this.

But if you're posting a lot of audit information, then this can be a concern.

If the auditing information has a short life span (with respect to the rest of the data), then you should probably make an effort to cull the audit tables, they can get pretty large.

Also, if practical, don't disregard the use of DB triggers for this. They can be quite efficient and effective at this process.