1 Topic by daniel.vergien (edited by daniel.vergien 27-06-2013 07:49:48)

  • daniel.vergien
  • Veteran
  • Offline
  • Registered: 28-10-2010
  • Posts: 55

Topic: Journal Tables

Hi,

I need an audit/journal mechanism in indicia, which takes track of changes on entities. My suggestion would be to create a 2nd schema 'aud' where we store this information. In the schema aud we put the audited tables. They get tow extra columns: rev and change_date. When ever an update occurs on a audited table, the effected rows are copied to the table in the aud schema before.
So the current value is stored in the main schema, the historical data in the aud schema. This saves disk space because not changed rows are not duplicated. Doing this inside the database and not in the php code has the advantage that also changes made by the administrator directly in the database are tracked.

Attached is a sql script for auditing the occurrence table.

What do you think about this?

Regards

Daniel

Post's attachments

aud.occurrences.sql 3.64 kb, 2 downloads since 2013-06-27 

You don't have the permssions to download the attachments of this post.

2 Reply by iain

  • iain
  • Veteran
  • Offline
  • Registered: 19-01-2009
  • Posts: 91

Re: Journal Tables

Hi Daniel

Don't know if this is relevant, but there is an audit trigger here which I assume can act on the indicia schema. Haven't tried it myself so don't know how it behaves but certainly seems worth a look. Maybe this is something you've already investigated?

http://wiki.postgresql.org/wiki/Audit_trigger_91plus

Certainly I like the idea of tracking changes and this is something I'd like to implement in some manner or form...

Iain

3 Reply by daniel.vergien

  • daniel.vergien
  • Veteran
  • Offline
  • Registered: 28-10-2010
  • Posts: 55

Re: Journal Tables

Hi Iain,

if seen the solution form the postgresql wiki before, but this one works different.:

  • "They" store all changes in one table, I suggested one table per audited table.

  • They save a lot of meta data, I suggested only an revision counter per table and a timestamp.

  • They make an entry on INSERT, DELETE, UPDATE and TRUNCATE, I suggested only on UPDATE.

The idea behind this suggestions is

  • we can easy query the history of eg. occurrence 4325

  • we do not delete, we just do update xyz set delteted = true, so we do not need to audit DELETE

  • the current data is in the audited table, so we don't need to use the space to duplicate the data in the audit tables

The disadvantage of the suggested approach is, that we need to maintain the ddl of the audit tables together with the audited tables.

The idea is stolen from hibernate envers, which keeps track for every audited table in an extra audit table.


Daniel

4 Reply by Jim Bacon

  • Jim Bacon
  • Veteran
  • Offline
  • From: BRC
  • Registered: 03-11-2008
  • Posts: 980

Re: Journal Tables

Hi

Another thing you might like to consider is, within your user interface, asking the editor to explain the change they are making. Storing this in occurrence_comments (or sample_comments) might provide a more easily understood audit trail than an audit table alone. Saving comments is what is happening in iRecord for tracking both automatic and human verification checks.

I like the possibility of rolling back changes that this suggestion offers. Combining the two, associating a comment with a revision, would give a comprehensive history.

Jim Bacon