Re: table versioning approach (not auditing)

Hi. I need to maintain a record of all changes to certain tables so assist in viewing history and reverting changes when necessary (customer service makes an incorrect edit, etc.).

I have studied these two audit trigger examples:

https://wiki.postgresql.org/wiki/Audit_trigger

https://wiki.postgresql.org/wiki/Audit_trigger_91plus

I've also read about two other approaches to versioning:

1. maintain all versions in one table, with a flag to indicate which is the current version

2. have a separate versions table for each real table, and insert into the associated version table whenever an update or insert is done.

My current implementation is based on the wiki trigger examples, using a single table, and a json column to record the row changes (rather than hstore). What I like about that, in particular, is I can have a "global," chronological view of all versioned changes very easily.

But there are two types of queries I need to run.

1. Find all changes made by a specific user

2. Find all changes related to a specific record

#1 is simple to do. The versioning table has a user_id column of who made the change, so I can query on that.

#2 is more difficult. I may want to fetch all changes to a group of tables that are all related by foreign keys (e.g., find all changes to "user" record 849, along with any changes to their "articles," "photos," etc.). All of the data is in the json column, of course, but it seems like a pain to try and build a query on the json column that can fetch all those relationships (and if I mess it up, I probably won't generate any errors, since the json is so free-form).

So my question is, do you think using the json approach is wrong for this case? Does it seem better to have separate versioning tables associated with each real table? Or another approach?

Thanks

I implemented a 2 table approach over 15 years ago for an insurance application.  I used both an effective_date & and an as_at_date, no triggers were involved.  I think a 2 table approach gives you more flexibility.

The effective_date allowed changes to be made to the table in advance of when they were to become effective.

The as_at_date allowed quotes to be made, valid for a period starting at the as_at_date.

End users did not query the database directly, all queries were precoded in a 4GL called Progress backed by an Oracle database.  The same could be done with a WildFly Java Enterprise AppSever (or some other middleware) and a PostgreSQL backend.

Different use case, but the concept is probably adaptable to your situation.

You may want a change table, that has a change_number that is in each type of table affected by a change.  This would help for query type #2.

I would be quite happy to contract to work out the appropriate schema and develop some SQL scripts to query & update the database, if you were interested.  My approach would be to create a minimal database with sample data to validate the schema design and SQL scripts.

Using a flag to indicate current record, seems inflexible.  As some changes may not take affect until some time in the future, and you can't query the database to see what was the situation at a particular point in the past.  For example: somebody complains about something that happened last Saturday near noon, how would you query the database to what it was like then?


Cheers,
Gavin