Keep the history of records that settle disputes

How to do it

An UPDATE destroys the old value, and later nobody can show what a customer agreed to. SQL Server and Azure SQL have system-versioned temporal tables that do this, queried with FOR SYSTEM_TIME AS OF. In PostgreSQL and MySQL, a trigger copies the old row into a history table. Put the acting user in a session setting so the trigger can store it. Write history in the database, not the app, so a manual fix is recorded too. History rows hold personal data, so Delete old data automatically covers them. For who did an action, such as an export, write an audit event instead.

Examples

SQL example

-- PostgreSQL: copy the old row on every change
CREATE TABLE price_history (LIKE prices, valid_to timestamptz NOT NULL, changed_by text);

CREATE FUNCTION keep_price_history() RETURNS trigger AS $$
BEGIN
  INSERT INTO price_history SELECT OLD.*, now(), current_setting('app.user_id', true);
  RETURN NULL;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER prices_history AFTER UPDATE OR DELETE ON prices
  FOR EACH ROW EXECUTE FUNCTION keep_price_history();

How to check

On staging, change a test price twice, once through the app and once by hand in SQL. The history table holds both earlier versions with their times and the acting user. A query for the first moment returns the first price.

Sources

• Microsoft Learn Temporal tables

Related rules

• DAT-31 Delete old data automatically in Data and databases
• CMP-26 Write an audit event for every sensitive action in Compliance and governance
• DEN-34 Keep the history of changing values with snapshots in Data engineering