Corrections table pattern for tracking changes

Users
1

Annotating transactions that are sent to the database is useful in general, but particularly when ‘correcting’ data that already exists at an earlier period of time. To that end I just cooked up a small example to show one way of modelling such annotations - this might be worthy of documenting more formally at some point or incorporating into the system-maintained schema as a built-in feature - feedback welcome:

-- first transaction
INSERT INTO docs (_id, foo, _valid_from) VALUES (1, 'bar', DATE '2020-01-01')

-- correcting transaction, sometime later
INSERT INTO docs (_id, foo, _valid_from) VALUES (1, 'baz', DATE '2020-02-01');
INSERT INTO tx_corrections (_id, reason, description) VALUES (1, 'ABC001', 'docs ETL process had a bug, manually overriding to fix downstream report, see ticket #12345')

-- find all corrected documents along with information about the corrections
SELECT docs._id AS doc_id, docs.foo, docs._valid_from, docs._system_from, tx_corrections.*
  FROM docs FOR VALID_TIME ALL FOR SYSTEM_TIME ALL
JOIN tx_corrections ON (docs._system_from = tx_corrections._system_from)

Try it out live using XT Play

2 Likes