

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();