Trigger based logging - improve archivability
We would like to time-limit our database logging but there is a challenge when deleting old rows from the log_
tables.
Example rows from log_civicrm_contact
log_date | log_action | id | first_name |
---|---|---|---|
2015-11-09 | Initialize | 8 | Bob |
2018-09-09 | Update | 8 | Robert |
2022-10-09 | Update | 8 | John |
In each case the rows are logged to have the status of the updated value. So on 2022-10-09 the value in civicrm_contact
is "John"
If the change was made in error the change can be rolled back to the last change - ie 'Robert'
However, if we say that we don't want to retain logging data older than 4 years and we have just deleted all rows older than than the after the change the table looks like this
log_date | log_action | id | first_name |
---|---|---|---|
2022-10-09 | Update | 8 | John |
And we can no longer roll back the change
The options seem to be
- Have an archive routine that adds an
initialization
of current value when we truncate - Switch the mechanism to log the PREVIOUS value not the current value on each update (this means the live table would need to be considered when calculating any diffs)