← Code-Übersicht

0005_prozesse.sql

Pfad: migrations/0005_prozesse.sql

Ext: sql

Größe: 9523 Bytes

Geändert: 2026-07-15 23:32:24+02

-- Migration 0005: Prozesse-Datenmodell (v4). Siehe planung/prozesse.md.
-- Statements sind durch eine Zeile "-- @@" getrennt (Batch-Runner).
-- Historie entsteht automatisch je Tabelle via audit-Event-Trigger.
-- @@
CREATE TABLE rolle (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL UNIQUE,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);
-- @@
CREATE TABLE akteur (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL UNIQUE,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);
-- @@
CREATE TABLE system (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL UNIQUE,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);
-- @@
CREATE TABLE prozesse (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    slug text NOT NULL UNIQUE,
    titel text NOT NULL,
    beschreibung text NOT NULL DEFAULT '',
    tags text[] NOT NULL DEFAULT '{}',
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);
-- @@
CREATE TABLE prozess_schritt (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    prozess_id bigint NOT NULL REFERENCES prozesse(id) ON DELETE CASCADE,
    name text NOT NULL,
    beschreibung text NOT NULL DEFAULT '',
    akteur_id bigint NOT NULL REFERENCES akteur(id),
    system_id bigint NOT NULL REFERENCES system(id),
    rolle_id bigint REFERENCES rolle(id),
    ruft_prozess_id bigint REFERENCES prozesse(id) ON DELETE RESTRICT,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT chk_kein_selbstaufruf CHECK (ruft_prozess_id IS NULL OR ruft_prozess_id <> prozess_id)
);
-- @@
CREATE TABLE schritt_vorgaenger (
    schritt_id bigint NOT NULL REFERENCES prozess_schritt(id) ON DELETE CASCADE,
    vorgaenger_id bigint NOT NULL REFERENCES prozess_schritt(id) ON DELETE CASCADE,
    ausgang text,
    PRIMARY KEY (schritt_id, vorgaenger_id),
    CONSTRAINT chk_kante_nicht_selbst CHECK (schritt_id <> vorgaenger_id)
);
-- @@
CREATE TABLE artefakt (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    typ text NOT NULL CHECK (typ IN ('datei','datenbank','tabelle','spalte','code_symbol','schnittstelle')),
    bezeichner text NOT NULL,
    parent_id bigint REFERENCES artefakt(id) ON DELETE RESTRICT,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);
-- @@
CREATE UNIQUE INDEX ux_artefakt_bezeichner ON artefakt (typ, bezeichner, COALESCE(parent_id, 0));
-- @@
CREATE TABLE schritt_artefakt (
    schritt_id bigint NOT NULL REFERENCES prozess_schritt(id) ON DELETE CASCADE,
    artefakt_id bigint NOT NULL REFERENCES artefakt(id) ON DELETE CASCADE,
    beziehung text NOT NULL CHECK (beziehung IN ('eingabe','ausgabe','beruehrt')),
    PRIMARY KEY (schritt_id, artefakt_id, beziehung)
);
-- @@
CREATE TABLE doku_prozess (
    doku_id bigint NOT NULL REFERENCES dokumentation(id) ON DELETE CASCADE,
    prozess_id bigint NOT NULL REFERENCES prozesse(id) ON DELETE CASCADE,
    PRIMARY KEY (doku_id, prozess_id)
);
-- @@
CREATE TABLE doku_prozess_schritt (
    doku_id bigint NOT NULL REFERENCES dokumentation(id) ON DELETE CASCADE,
    schritt_id bigint NOT NULL REFERENCES prozess_schritt(id) ON DELETE CASCADE,
    PRIMARY KEY (doku_id, schritt_id)
);
-- @@
CREATE TRIGGER trg_touch_updated_at BEFORE UPDATE ON rolle FOR EACH ROW EXECUTE FUNCTION audit.fn_touch_updated_at();
-- @@
CREATE TRIGGER trg_touch_updated_at BEFORE UPDATE ON akteur FOR EACH ROW EXECUTE FUNCTION audit.fn_touch_updated_at();
-- @@
CREATE TRIGGER trg_touch_updated_at BEFORE UPDATE ON system FOR EACH ROW EXECUTE FUNCTION audit.fn_touch_updated_at();
-- @@
CREATE TRIGGER trg_touch_updated_at BEFORE UPDATE ON prozesse FOR EACH ROW EXECUTE FUNCTION audit.fn_touch_updated_at();
-- @@
CREATE TRIGGER trg_touch_updated_at BEFORE UPDATE ON prozess_schritt FOR EACH ROW EXECUTE FUNCTION audit.fn_touch_updated_at();
-- @@
CREATE TRIGGER trg_touch_updated_at BEFORE UPDATE ON artefakt FOR EACH ROW EXECUTE FUNCTION audit.fn_touch_updated_at();
-- @@
CREATE OR REPLACE FUNCTION public.fn_artefakt_hierarchie() RETURNS trigger
LANGUAGE plpgsql AS $fn$
DECLARE ptyp text;
BEGIN
    IF NEW.parent_id IS NOT NULL THEN
        SELECT typ INTO ptyp FROM artefakt WHERE id = NEW.parent_id;
    END IF;
    IF NEW.typ = 'spalte' AND ptyp IS DISTINCT FROM 'tabelle' THEN
        RAISE EXCEPTION 'Artefakt-Hierarchie: spalte benoetigt Eltern-Typ tabelle';
    ELSIF NEW.typ = 'tabelle' AND ptyp IS DISTINCT FROM 'datenbank' THEN
        RAISE EXCEPTION 'Artefakt-Hierarchie: tabelle benoetigt Eltern-Typ datenbank';
    ELSIF NEW.typ = 'code_symbol' AND (ptyp IS NULL OR ptyp NOT IN ('datei','code_symbol')) THEN
        RAISE EXCEPTION 'Artefakt-Hierarchie: code_symbol benoetigt Eltern datei oder code_symbol';
    ELSIF NEW.typ = 'datei' AND ptyp IS NOT NULL AND ptyp <> 'datei' THEN
        RAISE EXCEPTION 'Artefakt-Hierarchie: datei-Eltern muss datei sein';
    ELSIF NEW.typ IN ('datenbank','schnittstelle') AND ptyp IS NOT NULL THEN
        RAISE EXCEPTION 'Artefakt-Hierarchie: % erlaubt keinen Eltern-Knoten', NEW.typ;
    END IF;
    RETURN NEW;
END;
$fn$;
-- @@
CREATE TRIGGER trg_i4_hierarchie BEFORE INSERT OR UPDATE ON artefakt FOR EACH ROW EXECUTE FUNCTION public.fn_artefakt_hierarchie();
-- @@
CREATE OR REPLACE FUNCTION public.fn_schritt_vorgaenger_check() RETURNS trigger
LANGUAGE plpgsql AS $fn$
DECLARE p1 bigint; p2 bigint; zyklus boolean;
BEGIN
    SELECT prozess_id INTO p1 FROM prozess_schritt WHERE id = NEW.schritt_id;
    SELECT prozess_id INTO p2 FROM prozess_schritt WHERE id = NEW.vorgaenger_id;
    IF p1 IS DISTINCT FROM p2 THEN
        RAISE EXCEPTION 'Ablauf: Vorgaenger muss im selben Prozess liegen';
    END IF;
    WITH RECURSIVE nachf AS (
        SELECT NEW.schritt_id AS knoten
        UNION
        SELECT sv.schritt_id FROM schritt_vorgaenger sv JOIN nachf n ON sv.vorgaenger_id = n.knoten
    )
    SELECT EXISTS (SELECT 1 FROM nachf WHERE knoten = NEW.vorgaenger_id) INTO zyklus;
    IF zyklus THEN
        RAISE EXCEPTION 'Ablauf: Kante erzeugt einen Zyklus';
    END IF;
    RETURN NEW;
END;
$fn$;
-- @@
CREATE TRIGGER trg_i3_ablauf BEFORE INSERT OR UPDATE ON schritt_vorgaenger FOR EACH ROW EXECUTE FUNCTION public.fn_schritt_vorgaenger_check();
-- @@
CREATE OR REPLACE FUNCTION public.fn_komposition_azyklik() RETURNS trigger
LANGUAGE plpgsql AS $fn$
DECLARE zyklus boolean;
BEGIN
    IF NEW.ruft_prozess_id IS NULL THEN
        RETURN NEW;
    END IF;
    WITH RECURSIVE ruft AS (
        SELECT NEW.ruft_prozess_id AS p
        UNION
        SELECT ps.ruft_prozess_id FROM prozess_schritt ps JOIN ruft r ON ps.prozess_id = r.p
        WHERE ps.ruft_prozess_id IS NOT NULL
    )
    SELECT EXISTS (SELECT 1 FROM ruft WHERE p = NEW.prozess_id) INTO zyklus;
    IF zyklus THEN
        RAISE EXCEPTION 'Komposition: zyklischer Teilprozess-Aufruf';
    END IF;
    RETURN NEW;
END;
$fn$;
-- @@
CREATE TRIGGER trg_i5_komposition BEFORE INSERT OR UPDATE ON prozess_schritt FOR EACH ROW EXECUTE FUNCTION public.fn_komposition_azyklik();
-- @@
CREATE OR REPLACE FUNCTION public.fn_pruef_schritt_abdeckung() RETURNS trigger
LANGUAGE plpgsql AS $fn$
DECLARE sid bigint; ist_aufruf boolean; anzahl integer;
BEGIN
    IF TG_TABLE_NAME = 'prozess_schritt' THEN
        sid := COALESCE(NEW.id, OLD.id);
    ELSE
        sid := COALESCE(NEW.schritt_id, OLD.schritt_id);
    END IF;
    SELECT (ruft_prozess_id IS NOT NULL) INTO ist_aufruf FROM prozess_schritt WHERE id = sid;
    IF NOT FOUND THEN
        RETURN NULL;
    END IF;
    SELECT count(*) INTO anzahl FROM schritt_artefakt WHERE schritt_id = sid;
    IF ist_aufruf AND anzahl > 0 THEN
        RAISE EXCEPTION 'I1: Aufruf-Schritt % darf keine Artefakte tragen', sid;
    END IF;
    IF NOT ist_aufruf AND anzahl = 0 THEN
        RAISE EXCEPTION 'I1: atomarer Schritt % ohne Artefakt (verwaist)', sid;
    END IF;
    RETURN NULL;
END;
$fn$;
-- @@
CREATE CONSTRAINT TRIGGER trg_i1_schritt AFTER INSERT OR UPDATE ON prozess_schritt DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION public.fn_pruef_schritt_abdeckung();
-- @@
CREATE CONSTRAINT TRIGGER trg_i1_link AFTER INSERT OR UPDATE OR DELETE ON schritt_artefakt DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION public.fn_pruef_schritt_abdeckung();
-- @@
CREATE OR REPLACE FUNCTION public.fn_pruef_artefakt_abdeckung() RETURNS trigger
LANGUAGE plpgsql AS $fn$
DECLARE aid bigint; anzahl integer;
BEGIN
    IF TG_TABLE_NAME = 'artefakt' THEN
        aid := COALESCE(NEW.id, OLD.id);
    ELSE
        aid := COALESCE(NEW.artefakt_id, OLD.artefakt_id);
    END IF;
    PERFORM 1 FROM artefakt WHERE id = aid;
    IF NOT FOUND THEN
        RETURN NULL;
    END IF;
    SELECT count(*) INTO anzahl FROM schritt_artefakt WHERE artefakt_id = aid;
    IF anzahl = 0 THEN
        RAISE EXCEPTION 'I2: Artefakt % ohne Schritt (verwaist)', aid;
    END IF;
    RETURN NULL;
END;
$fn$;
-- @@
CREATE CONSTRAINT TRIGGER trg_i2_artefakt AFTER INSERT OR UPDATE ON artefakt DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION public.fn_pruef_artefakt_abdeckung();
-- @@
CREATE CONSTRAINT TRIGGER trg_i2_link AFTER INSERT OR UPDATE OR DELETE ON schritt_artefakt DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION public.fn_pruef_artefakt_abdeckung();