Simple trigger based table audit example

audit_trail.sql

create table t1(x int);
create table t2 (x int, y varchar);
create table audit_trail(
    id bigserial primary key,
    table_name varchar,
    data_before jsonb,
    data_after jsonb,
    ts timestamp
);

create function audit_trg() returns trigger as $$
    begin
        insert into audit_trail(
            table_name,
            data_before,
            data_after,
            ts)
        values (
            tg_table_schema || '.' || tg_table_name,
            row_to_json(old.*)::jsonb,
            row_to_json(new.*)::jsonb,
            now()
        );

    return null;
    end;
$$ language plpgsql;

create trigger t1_audit after insert or update on t1
    for each row execute function audit_trg();

create trigger t2_audit after insert or update on t2
    for each row execute function audit_trg();

insert into t1 values(1),(2);
update t1 set x = 3 where x = 2;

insert into t2 values(1, 'one'), (2, 'two');
update t2 set y = 'updated';

select * from audit_trail;

--  id | table_name |     data_before      |        data_after        |             ts
-- ----+------------+----------------------+--------------------------+----------------------------
--   1 | public.t1  |                      | {"x": 1}                 | 2023-05-04 08:10:13.643402
--   2 | public.t1  |                      | {"x": 2}                 | 2023-05-04 08:10:13.643402
--   3 | public.t1  | {"x": 2}             | {"x": 3}                 | 2023-05-04 08:10:13.644917
--   4 | public.t2  |                      | {"x": 1, "y": "one"}     | 2023-05-04 08:10:13.645817
--   5 | public.t2  |                      | {"x": 2, "y": "two"}     | 2023-05-04 08:10:13.645817
--   6 | public.t2  | {"x": 1, "y": "one"} | {"x": 1, "y": "updated"} | 2023-05-04 08:10:13.646805
--   7 | public.t2  | {"x": 2, "y": "two"} | {"x": 2, "y": "updated"} | 2023-05-04 08:10:13.646805
-- (7 rows)