Build a Reusable Field-Level Audit Log with JSONB in YugabyteDB

				
					CREATE OR REPLACE FUNCTION audit_data.capture_field_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
AS $function$
DECLARE
    old_row          JSONB;
    new_row          JSONB;
    row_identifier   JSONB := '{}'::JSONB;
    column_name      TEXT;
    old_column_value JSONB;
    new_column_value JSONB;
    audit_user       TEXT;
    change_timestamp TIMESTAMPTZ;
    argument_index   INTEGER;
BEGIN
    IF TG_OP <> 'UPDATE' THEN
        RAISE EXCEPTION
            'audit_data.capture_field_changes() supports UPDATE only';
    END IF;

    IF TG_NARGS = 0 THEN
        RAISE EXCEPTION
            'Supply at least one row-identifier column';
    END IF;

    old_row := pg_catalog.to_jsonb(OLD);
    new_row := pg_catalog.to_jsonb(NEW);

    audit_user := COALESCE(
        NULLIF(
            pg_catalog.current_setting(
                'app.audit_user',
                true
            ),
            ''
        ),
        session_user::TEXT
    );

    change_timestamp := pg_catalog.clock_timestamp();

    /*
     * Build a JSONB object containing the source row's
     * identifying column values.
     */
    FOR argument_index IN 0..(TG_NARGS - 1) LOOP
        IF NOT (
            new_row ? TG_ARGV[argument_index]
        ) THEN
            RAISE EXCEPTION
                'Identifier column "%" does not exist on %.%',
                TG_ARGV[argument_index],
                TG_TABLE_SCHEMA,
                TG_TABLE_NAME;
        END IF;

        row_identifier :=
            row_identifier ||
            pg_catalog.jsonb_build_object(
                TG_ARGV[argument_index],
                new_row -> TG_ARGV[argument_index]
            );
    END LOOP;

    /*
     * Compare every column in the OLD and NEW rows.
     */
    FOR column_name, new_column_value IN
        SELECT key, value
        FROM pg_catalog.jsonb_each(new_row)
    LOOP
        old_column_value := old_row -> column_name;

        IF old_column_value IS DISTINCT FROM new_column_value THEN
            INSERT INTO audit_data.field_change_log (
                actor_user,
                table_schema,
                table_name,
                row_identifier,
                field_changed,
                old_value,
                new_value,
                changed_at
            )
            VALUES (
                audit_user,
                TG_TABLE_SCHEMA,
                TG_TABLE_NAME,
                row_identifier,
                column_name,
                old_column_value,
                new_column_value,
                change_timestamp
            );
        END IF;
    END LOOP;

    RETURN NEW;
END;
$function$;