Using envers ... one step further -> 4 eye principle

[bestage]

Adam,

Envers is an elegant solution, a generic approachon audit logging when using JPA / Hibernate.

What is your point of view on the following business use case:

- any record changes made must be verified before becoming productive = 4 eye principle used in most business organisations.

In envers we have two tables: original / history for each entity (in a simple case).

If I enter a new customer address for example, it is persisted to both tables. It has a state column, say STATE = PENDING (after entry).
Now the verifier approves the entry and it becomes active: STATE = ACTIVE.
So far so good, everything is working fine.

Now what happens next is the active record is modified again (e.g. the address has changed).
Before this modification becomes active it must be verified. Until that happens, the previous record must remain active.

But here we have a problem: the original (productive) table alreay contains the modified record. The previous record (before modification) is
already in the history table.

So the consequence is:
the new (modified) address record remains inactive until it is verified. This would potentially cause problems.

Have you had such a use case? What possible solution would you suggest?

I see two ways to solve this problem:

- extending your module (not the best option)
- use the provided audit API in a smart way, so that when a record remains in an inactive state (waiting for verification) the last revision with the verified version is obtained via AuditReader.

The 2nd seems to me to be a better solution, but probably not the perfect one.

It would be interesting to hear your thoughts on this.

[bestage]

Small addition: my 2nd solution might be OK, if the access to the data is hidden behind an API. But what if another non-Java app needs this data and wants to access productive records. In that case one would need to make complex SQL queries (Union, etc) on both original / history table,
since one cannot rely only on teh original table.

[adamw]

Hello,

as I understand you want to get the newest revision of an entity which has a flag set to true. You can do this using a revision-of-entity query, by maximizing the revision number property (there's a special constraint for that), adding another constraint on the flag value.

Adam