Automated data versioning

This article explains the automated database data versioning workflow for multiple grottocenter tables.

---

Initial Author : V. Verdon 30/01/21

Example table : t_location

Data versioning is handled automatically at the DBMS level by PostgreSQL triggers.

Data versioning is triggered automatically when an update occurred in the database for the tables where the automated versioning is activated.

It consists of copying the record before update into a table with the h_ prefix.

For example t_location is versioned in h_location.

With this versioning it is easy to retrieve all the successive changes for each record saved in the database.

In the h_ table, the primary key is made with the ID of the record of the t_ table and date of the latest update (date_review) : id + date_review

The h_location is identical to t_location except for:

• the primary key, as explained above
• if date_reviewed do not exists (first update), then the trigger will use the date_inscription for the date_reviewed
• the column id of the h_location table is foreign key if the id column of t_location
• the function started by the trigger handle the timestamp date_reviewed of t_location automatically
• the is_deleted column is not present of the h_ table

Delete Case

An other trigger handle the DELETE SQL request, stop it, and instead create an update of the record that need to be deleted by setting the is_deleted column to true.

To restore a deleted record, we only need to set is_deleted to false on the t_ table.