Change in audit in Java+Spring+Oracle application

Question

I am tasked with changing an audit implementation in an application in which I work.

The requirement of the audit is to know the history of changes, and execution of certain tasks by a given user. (Initial Status -> Modification 1 -> ... -> Modification N)

The problem is that the current implementation saves the state of the serialized Java object in a field CLOB in the database, and with that we have some problems:

1. The application already has more than 3 years in production, and several changes have been made in the course this time, and with that the Java Object was modified, but no maintenance was done in the database for the object unless it has these changes. (For example: a new field added in a class).
2. Reverting something to the "Home State", or going back some version in time, whose Java Object no longer reflects running code is no easy task.
3. The simple fact of wanting to know the content of the Object under these conditions requires me to retrieve on SVN the version of the class the object was generated in, and this can be difficult, since I don’t know exactly in which commit of code that object was saved.
4. It is impossible to view the contents of CLOB performing a simple query in the database.

Discussing with other people, we come to some possible implementations:

• Serialize the Java object in JSON (which would easily discover the code version used by the number of JSON attributes, and it would be humanly possible to read the content in the database).
• Another solution I found would be to use an audit framework that is easily integratable with Spring: Audit4j. This has a customizable layout, and is also humanly readable, and has several functionalities which are very interesting. audit4j

I would like to know in this case what would be the best practices to perform an audit implementation that is: Humanly readable (in database), which has low maintenance, and that the problems between code versions are not so dramatic.

Answer 1

It was unclear whether changes in object states should be identified. For example, who changed a given attribute at a given time?

My answer will continue assuming that you do not need this information (who changed it?), just knowing only the old values, new values and date of change.

Once in a project we created an architecture where no record was deleted, and yes overwritten.

All tables had a boolean attribute hidden to determine whether a particular record was active in the system. And another attribute versao_anterior referencing the ID of the previous state, if necessary.

Example: a table to keep customer registrations, after entering a customer’s data we would have:

id | nome | telefone | versao_anterior | hidden | data_insercao |
1  | João | 99999999 | null            | false  | 09/02/2015

After editing customer data 1-John, your table would be:

id | nome | telefone | versao_anterior | hidden | data_insercao |
1  | João | 99999999 | null            | true   | 09/02/2015
2  | José | 12345678 | null            | false  | 12/02/2015
3  | João | 88888888 | 1               | false  | 13/02/2015

Note that only John’s phone changed from 09/02 to the day 13/02. In the meantime, another record was entered into the database without any problems (id 2).

This way, for each instance of the client entity it is possible to track all its states, its changes and the dates of each of them.

Just go listing the records while versao_anterior is different from null.

This type of approach is useful when system data cannot be lost.

The disadvantage are:

• is not stored from the originator of the change
• tables with multiple records that are changed all the time can get very large in a short time.

I hope I’ve helped.

Answer 2

Audit frameworks can be very useful at this time, but there are other ways to create an audit scheme. In the places where I worked always fall into one of the three options below:

• Database triggers: Create a "shadow" table, equal to the source table, and whenever there is a change in the data, Rigger saves the old data in the shadow. The positive point is that it would be transparent for application in everyday life, but would have to be careful when maintaining the table "live".

• Event log: Create a database schema that can store data historically. Something like an event table, which holds an ID that groups together all the changed data. Example: the Person table has five fields, when changed, creates a date ID 1 event that contains 5 value key elements (column name, column value). This event log can be completed via Trigger, or even via application.

• Audit frameworks: that would have to see how each behaves, and if it fits your needs.