Record historie bijhouden met HSTORE

Het is verbazend hoe vaak het handig zou zijn om bij te kunnen houden wat er allemaal met een record gebeurt. Auditing blijkt heel eenvoudig met een HSTORE veld en een trigger.

code

-- For testing purposes, create a schema and put everything in there so it can be dropped at cleanup.
-- Cleanup with:
--
DROP SCHEMA IF EXISTS temp CASCADE;


CREATE SCHEMA temp;

-- Two tables that wil be audited
CREATE TABLE temp.products
(
    id    SERIAL,
    title text,
    price NUMERIC(8, 2)
);

CREATE TABLE temp.users
(
    id   INT,
    name TEXT
);

-- Create a single history table where everything is logged.
CREATE TABLE temp.audit_history
(
    id           serial,
    date_created timestamp DEFAULT now(),
    schemaname   text,
    tablename    text,
    operation    text,
    whodidit     text      DEFAULT current_user,
    old_val      hstore
);

-- Create the trigger function that will do the auditing.
CREATE FUNCTION temp.change_trigger() RETURNS trigger AS
$$
BEGIN
    IF TG_OP = 'INSERT' THEN
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE'
    THEN
        INSERT INTO temp.audit_history (tablename, schemaname, operation, old_val)
        VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP,
                hstore(OLD));
        RETURN NEW;
    ELSIF TG_OP = 'DELETE'
    THEN
        INSERT INTO temp.audit_history (tablename, schemaname, operation, old_val)
        VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD));
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;


-- Create triggers for the tables that are going to be audited.
CREATE TRIGGER trig_audit_products
    BEFORE INSERT OR UPDATE OR DELETE
    ON temp.products
    FOR EACH ROW
EXECUTE PROCEDURE temp.change_trigger();

CREATE TRIGGER trig_audit_users
    BEFORE INSERT OR UPDATE OR DELETE
    ON temp.users
    FOR EACH ROW
EXECUTE PROCEDURE temp.change_trigger();

code

-- Insert some data and make some changes.

INSERT INTO temp.users (id, name)
VALUES (43, 'Benny');

INSERT INTO temp.products (id, title, price)
VALUES (1, 'Funky shoes', 12);

UPDATE temp.users SET name='Benny' WHERE id = 43;

UPDATE temp.products SET title = 'Funky tapas' WHERE id = 1;

UPDATE temp.products SET price = 15    WHERE id = 1;
UPDATE temp.products SET price = 15.25 WHERE id = 1;
UPDATE temp.products SET price = 15.75 WHERE id = 1;
UPDATE temp.products SET price = 18.15 WHERE id = 1;
UPDATE temp.products SET price = 18.00 WHERE id = 1;
UPDATE temp.products SET price = 18.10 WHERE id = 1;

code

-- The history table now contains HSTORE values of the states of each record that has been updated.
SELECT * FROM temp.audit_history;

id	date_created	                schemaname  tablename	operation  whodidit	old_val
1	2020-01-08 12:05:13.274925	temp	    users       UPDATE	   vinny	name => Benny, id => 43
2	2020-01-08 12:05:13.298814	temp	    products	UPDATE	   vinny	price => 12.00, id => 1, title => "Funky shoes"
3	2020-01-08 12:05:13.321988	temp	    products	UPDATE	   vinny	price => 12.00, id => 1, title => "Funky tapas"
4	2020-01-08 12:05:13.351825	temp	    products	UPDATE	   vinny	price => 15.00, id => 1, title => "Funky tapas"
5	2020-01-08 12:08:21.224172	temp	    products	UPDATE	   vinny	price => 15.25, id => 1, title => "Funky tapas"
6	2020-01-08 12:08:23.311041	temp	    products	UPDATE	   vinny	price => 15.75, id => 1, title => "Funky tapas"
7	2020-01-08 12:08:46.849261	temp	    products	UPDATE	   vinny	price => 18.15, id => 1, title => "Funky tapas"
8	2020-01-08 12:08:46.858744	temp	    products	UPDATE	   vinny	price => 18.00, id => 1, title => "Funky tapas"


-- Now you can select specific details from the history
SELECT date_created, old_val->'price' AS price
FROM temp.audit_history
WHERE schemaname='temp' AND tablename='products' ORDER BY date_created;


date_created	                price
2020-01-08 12:05:13.298814	12.00
2020-01-08 12:05:13.321988	12.00
2020-01-08 12:05:13.351825	15.00
2020-01-08 12:08:21.224172	15.25
2020-01-08 12:08:23.311041	15.75
2020-01-08 12:08:46.849261	18.15
2020-01-08 12:08:46.858744	18.00


-- Using Windowing you can now show how a value changed over time
SELECT date_created,
       old_val -> 'price'                                              AS price,
       (old_val -> 'price')::NUMERIC -
       LAG((old_val -> 'price')::NUMERIC) OVER (ORDER BY date_created) AS price_change
FROM temp.audit_history
WHERE schemaname = 'temp'
  AND tablename = 'products'
ORDER BY date_created;


date_created	                price   price_change
2020-01-08 12:05:13.298814	12.00
2020-01-08 12:05:13.321988	12.00	0
2020-01-08 12:05:13.351825	15.00	3
2020-01-08 12:08:21.224172	15.25	0.25
2020-01-08 12:08:23.311041	15.75	0.5
2020-01-08 12:08:46.849261	18.15	2.4
2020-01-08 12:08:46.858744	18.00	-0.15