2

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.

Thank you.

  • One term to look for is database auditing – user416 Jun 11 '15 at 12:55
  • I did it with a changes table once. On every change I add a row with date, user, area/table, entry id, previous and new field values. Then just query for area/table/id. I automaGically checked every field with a filter before saving on the app, and if changed, inserted to this table. – Fernando Cordeiro Jun 17 '15 at 8:42
  • I wrote program for oracle ages ago. You monitor the insert,update, and delete calls via a procedure stored in the SQL server. As long as each user logins in with there own credentials you can add that and the other info into a change table. The changes sit there quietly until someone decides to query them. – cybernard Jul 21 '15 at 20:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.