92 lines
3.1 KiB
PL/PgSQL
92 lines
3.1 KiB
PL/PgSQL
|
||
|
||
CREATE SCHEMA audit;
|
||
|
||
CREATE TABLE audit_trail (
|
||
|
||
id serial PRIMARY KEY,
|
||
|
||
tstamp timestamp DEFAULT now(),
|
||
|
||
schemaname text,
|
||
|
||
tabname text,
|
||
|
||
operation text,
|
||
recordid uuid,
|
||
|
||
-- who text DEFAULT current_user,
|
||
|
||
new_val json,
|
||
|
||
old_val json,
|
||
useremail text,
|
||
bodyshopid uuid
|
||
|
||
);
|
||
|
||
-- More as an example than anything else, I wanted a function that would take two JSONB objects in PostgreSQL, and return how the left-hand side differs from the right-hand side. This means any key that is in the left but not in the right would be returned, along with any key whose value on the left is different from the right.
|
||
|
||
-- Here’s a quick example of how to do this in a single SELECT. In real life, you probably want more error checking, but it shows how nice the built-in primitives are:
|
||
CREATE OR REPLACE FUNCTION json_diff(l JSONB, r JSONB) RETURNS JSONB AS
|
||
$json_diff$
|
||
SELECT jsonb_object_agg(a.key, a.value) FROM
|
||
( SELECT key, value FROM jsonb_each(l) ) a LEFT OUTER JOIN
|
||
( SELECT key, value FROM jsonb_each(r) ) b ON a.key = b.key
|
||
WHERE a.value != b.value OR b.key IS NULL;
|
||
$json_diff$
|
||
LANGUAGE sql;
|
||
|
||
|
||
CREATE OR REPLACE FUNCTION audit_trigger() RETURNS trigger AS $$
|
||
|
||
DECLARE
|
||
shopid text ;
|
||
email text;
|
||
|
||
BEGIN
|
||
|
||
select b.id, u.email INTO shopid, email from users u join associations a on u.email = a.useremail join bodyshops b on b.id = a.shopid where u.authid = current_setting('hasura.user', 't')::jsonb->>'x-hasura-user-id' and a.active = true;
|
||
|
||
IF TG_OP = 'INSERT'
|
||
|
||
THEN
|
||
|
||
INSERT INTO public.audit_trail (tabname, schemaname, operation, new_val, recordid, bodyshopid, useremail)
|
||
|
||
VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW), NEW.id, shopid, email);
|
||
|
||
RETURN NEW;
|
||
|
||
ELSIF TG_OP = 'UPDATE'
|
||
|
||
THEN
|
||
|
||
INSERT INTO public.audit_trail (tabname, schemaname, operation, old_val, new_val, recordid, bodyshopid, useremail)
|
||
|
||
VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP,
|
||
|
||
json_diff(to_jsonb(OLD), to_jsonb(NEW)) , json_diff(to_jsonb(NEW), to_jsonb(OLD)), OLD.id, shopid, email);
|
||
|
||
RETURN NEW;
|
||
|
||
ELSIF TG_OP = 'DELETE'
|
||
|
||
THEN
|
||
|
||
INSERT INTO public.audit_trail (tabname, schemaname, operation, old_val, recordid, bodyshopid, useremail)
|
||
|
||
VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD), OLD.ID, shopid, email);
|
||
|
||
RETURN OLD;
|
||
|
||
END IF;
|
||
|
||
END;
|
||
|
||
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;
|
||
|
||
|
||
|
||
CREATE TRIGGER audit_trigger_users AFTER INSERT OR UPDATE OR DELETE ON users
|
||
FOR EACH ROW EXECUTE PROCEDURE audit_trigger(); |