We have an existing product which is moving out of beta and will start to roll out to a large audience now. They like the product, but we've received feedback that they need some history logging. They would like to be able to ask questions like "when was this piece of data changed, and by who" at a minimum.
They effectively require that we save changes for every field in our database, because their old tool made it possible (if painful) to see any change that occurred and they refuse to lose this ability. There are enough fields that we are not wanting to roll something by hand for historical tracking like this. I'm looking for existing tools which will automate recording historic changes and querying them.
We are currently using Postgress, JPA, and hibernate as our basic technology stack.
To further spell out my 'wish list', not that I'm sure I could get all of this. I would like a tool that
automatically saves a change record.
provides a way to query that change record to find changes.
Convenient query syntax is worth a trade off in power.
Ideally were looking at having something that responds to basic historic queries in no more then a few minutes.
We anticipate a decent number of elements (or however you wish to name rows in a non-join table) created in a day, in the order of the low thousands. Changes to an existing element will pretty infrequent.
One final complication, we do not yet do any archiving of historic data, but we are aware that we would need to archive data eventually. We haven't yet decided which archiving approach we're using. However, we would much prefer to be able to do history tracking going back beyond the date where we age out data from our main database.
Any suggestions for tools is very much welcome. Links or descriptions of pros and cons are great, but mainly, I just need to know what tools are worth researching so I know where to begin with a more through analysis of options.