← Code-Übersicht

0001_audit_versioning.sql

Pfad: migrations/0001_audit_versioning.sql

Ext: sql

Größe: 3040 Bytes

Geändert: 2026-07-15 20:22:53+02

-- Migration 0001: Automatische JSONB-Versionierung (History) via Trigger + Event-Trigger.
-- Idempotent wiederholbar. Ausgeschlossen: Tabellennamen mit 'protokoll' oder 'log'
-- sowie die history_-Tabellen selbst. Vollstaendig in-DB, kein PHP noetig.

CREATE SCHEMA IF NOT EXISTS audit;

-- Trigger-Funktion: sichert den alten Zeilenzustand als JSONB, bevor er ueberschrieben
-- oder geloescht wird. Laeuft als AFTER-Trigger; OLD ist der Vorher-Zustand.
CREATE OR REPLACE FUNCTION audit.fn_write_history() RETURNS trigger
LANGUAGE plpgsql AS $fn$
BEGIN
    EXECUTE format(
        'INSERT INTO %I.%I (operation, geaendert_von, daten) VALUES ($1, $2, $3)',
        TG_TABLE_SCHEMA, 'history_' || TG_TABLE_NAME
    )
    USING TG_OP, current_setting('app.actor', true), to_jsonb(OLD);
    RETURN NULL;
END;
$fn$;

-- Provisioniert eine einzelne Tabelle: legt history_<t> und den Trigger an.
-- Idempotent; ueberspringt ausgeschlossene und history_-Tabellen.
CREATE OR REPLACE FUNCTION audit.enable_versioning(p_schema text, p_table text) RETURNS void
LANGUAGE plpgsql AS $fn$
BEGIN
    IF p_table ~* '(^|_)(protokoll|log)($|_)' OR p_table LIKE 'history\_%' THEN
        RETURN;
    END IF;
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I.%I (
            history_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
            operation     text NOT NULL,
            geaendert_am  timestamptz NOT NULL DEFAULT clock_timestamp(),
            geaendert_von text,
            daten         jsonb NOT NULL
        )',
        p_schema, 'history_' || p_table
    );
    EXECUTE format('DROP TRIGGER IF EXISTS trg_history ON %I.%I', p_schema, p_table);
    EXECUTE format(
        'CREATE TRIGGER trg_history AFTER UPDATE OR DELETE ON %I.%I
         FOR EACH ROW EXECUTE FUNCTION audit.fn_write_history()',
        p_schema, p_table
    );
END;
$fn$;

-- Backfill: alle bereits vorhandenen Basistabellen in public provisionieren.
DO $do$
DECLARE r record;
BEGIN
    FOR r IN
        SELECT n.nspname, c.relname
        FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind = 'r' AND c.relpersistence = 'p' AND n.nspname = 'public'
    LOOP
        PERFORM audit.enable_versioning(r.nspname, r.relname);
    END LOOP;
END;
$do$;

-- Event-Trigger-Funktion: provisioniert jede kuenftig angelegte Basistabelle automatisch.
CREATE OR REPLACE FUNCTION audit.fn_on_create_table() RETURNS event_trigger
LANGUAGE plpgsql AS $fn$
DECLARE obj record;
BEGIN
    FOR obj IN
        SELECT objid FROM pg_event_trigger_ddl_commands() WHERE command_tag = 'CREATE TABLE'
    LOOP
        PERFORM audit.enable_versioning(n.nspname, c.relname)
        FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE c.oid = obj.objid AND c.relkind = 'r' AND c.relpersistence = 'p';
    END LOOP;
END;
$fn$;

DROP EVENT TRIGGER IF EXISTS trg_audit_create_table;
CREATE EVENT TRIGGER trg_audit_create_table
    ON ddl_command_end WHEN TAG IN ('CREATE TABLE')
    EXECUTE FUNCTION audit.fn_on_create_table();