Comment by cryptonector

There's lots of extensions that automatically create audit tables and record history in them. The trick is to a) create audit tables for all _existing_ tables, b) create an event trigger so you can have audit tables created automatically for any future new tables. Here's an example of how to do it: https://github.com/twosigma/postgresql-contrib/blob/master/a...

Another thing you might do is to go with a schema that follows the event shipping pattern. In this pattern you have the "truth" held in insert-only tables (deletes and updates not allowed), then turn those "event" tables into ones you can query naturally using VIEWs, MATERIALIZED VIEWs, or live tables that you update with triggers on the event tables. Then your event tables _are your history/audit_ tables.

    CREATE TABLE public.audit (
        id uuid NOT NULL,
        created_time timestamp without time zone DEFAULT now() NOT NULL,
        schema_name text NOT NULL,
        table_name text NOT NULL,
        record_id uuid NOT NULL,
        user_name text,
        action text NOT NULL,
        old_data jsonb,
        new_data jsonb
    );

    CREATE OR REPLACE FUNCTION audit_if_modified_func() RETURNS TRIGGER AS $body$
    DECLARE
        v_old_data JSONB;
        v_new_data JSONB;
    BEGIN
        IF (TG_OP = 'UPDATE') THEN
            v_old_data := to_jsonb(OLD.*);
            v_new_data := to_jsonb(NEW.*);
            IF (TG_TABLE_NAME::TEXT = 'users') THEN
                v_old_data = v_old_data - 'last_login_time';
                v_new_data = v_new_data - 'last_login_time';
            END IF;
            IF (v_old_data <> v_new_data) THEN
                INSERT INTO audit (id,record_id,schema_name,table_name,user_name,action,old_data,new_data)
                    VALUES (uuid_generate_v4(), NEW.id, TG_TABLE_SCHEMA::TEXT, TG_TABLE_NAME::TEXT,
                            session_user::TEXT, substring(TG_OP,1,1), v_old_data, v_new_data);
            END IF;
            RETURN NEW;
        ELSIF (TG_OP = 'DELETE') THEN
            v_old_data := to_jsonb(OLD.*);
            INSERT INTO audit (id, record_id,schema_name,table_name,user_name,action,old_data)
            VALUES (uuid_generate_v4(), OLD.id, TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_old_data);
            RETURN OLD;
        ELSIF (TG_OP = 'INSERT') THEN
            v_new_data := to_jsonb(NEW.*);
            INSERT INTO audit (id, record_id,schema_name,table_name,user_name,action,new_data)
            VALUES (uuid_generate_v4(), NEW.id, TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_new_data);
            RETURN NEW;
        ELSE
            RAISE WARNING '[AUDIT_IF_MODIFIED_FUNC] - Other action occurred: % at %',TG_OP,now();
            RETURN NULL;
        END IF;
    
    EXCEPTION
        WHEN data_exception THEN
            RAISE WARNING '[AUDIT_IF_MODIFIED_FUNC] - UDF ERROR [DATA EXCEPTION] - SQLSTATE: % SQLERRM: %',SQLSTATE,SQLERRM;
            RETURN NULL;
        WHEN unique_violation THEN
            RAISE WARNING '[AUDIT_IF_MODIFIED_FUNC] - UDF ERROR [UNIQUE] - SQLSTATE: % SQLERRM: %',SQLSTATE,SQLERRM;
            RETURN NULL;
        WHEN OTHERS THEN
            RAISE WARNING '[AUDIT_IF_MODIFIED_FUNC] - UDF ERROR [OTHER] - SQLSTATE: % SQLERRM: %',SQLSTATE,SQLERRM;
            RETURN NULL;
    END;
    $body$
    LANGUAGE plpgsql
    SECURITY DEFINER;
    
    CREATE TRIGGER audit_logger_accounts
        AFTER INSERT OR UPDATE OR DELETE ON accounts
        FOR EACH ROW EXECUTE PROCEDURE audit_if_modified_func();

    CREATE TRIGGER audit_logger_users
        AFTER INSERT OR UPDATE OR DELETE ON users
        FOR EACH ROW EXECUTE PROCEDURE audit_if_modified_func();

:)

Hey, I see you've added a web server. How does it compare to PostgREST?