FireAdministration/prisma/migrations/20260903000300_wartung_rls/migration.sql

-- =============================================================================
-- Phase 2: Mandantentrennung, Unveränderbarkeit der Prüfprotokolle, Indizes.
--
-- Die vorige Migration hat die Tabellen angelegt. Drei Dinge lassen sich nur
-- in der Datenbank verlässlich durchsetzen und stehen deshalb hier:
--
--   1. Row Level Security für die elf neuen Tabellen — dieselbe Behandlung wie
--      für die Tabellen aus Phase 0 und 1.
--
--   2. Die Unveränderbarkeit abgeschlossener Prüfprotokolle. Ein Protokoll ist
--      ein Nachweis gegenüber der Unfallkasse. Wäre es nachträglich änderbar,
--      wäre es als Nachweis wertlos — und ein vergessener Zustandscheck im
--      Code reicht, um genau das zu ermöglichen. Deshalb sitzt die Sperre in
--      der Datenbank, nicht nur in lib/ und server/.
--
--   3. Indizes für das Fristen-Dashboard und die Suche über Mängel und
--      Rückrufe.
-- =============================================================================

-- -----------------------------------------------------------------------------
-- Row Level Security
-- -----------------------------------------------------------------------------
DO $$
DECLARE
    tabelle text;
BEGIN
    FOREACH tabelle IN ARRAY ARRAY[
        'wartungsvorlage',
        'checklistenpunkt',
        'wartungstermin',
        'pruefung',
        'pruefergebnis',
        'signatur',
        'mangel',
        'rueckruf',
        'rueckruf_geraetetyp',
        'rueckruf_betroffenheit',
        'erinnerung'
    ]
    LOOP
        EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', tabelle);
        EXECUTE format(
            'CREATE POLICY %I ON %I FOR ALL '
            'USING ("organisationId" = aktuelle_organisation()) '
            'WITH CHECK ("organisationId" = aktuelle_organisation())',
            tabelle || '_mandant', tabelle
        );
    END LOOP;
END;
$$;

-- -----------------------------------------------------------------------------
-- Unveränderbarkeit abgeschlossener Prüfungen
--
-- Solange eine Prüfung im Status ENTWURF ist, darf der Wizard alles ändern.
-- Mit dem Abschluss ist Schluss. Genau drei Änderungen bleiben danach zulässig,
-- und alle drei ergänzen das Protokoll, statt es zu verfälschen:
--
--   * status ABGESCHLOSSEN -> ERSETZT, wenn eine Korrekturprüfung nachfolgt
--   * protokollDateiId und protokollNummer, solange sie noch leer sind: das
--     PDF entsteht im Hintergrundprozess und wird nachgetragen
--   * geaendertAm, das Prisma bei jedem Schreibvorgang mitsetzt
--
-- Alles andere — Ergebnis, Datum, Prüfer, Messwerte — ist festgeschrieben.
-- Korrekturen laufen über eine neue Prüfung mit Verweis auf die alte.
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION pruefung_unveraenderlich()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    vergleich "pruefung"%ROWTYPE;
BEGIN
    -- Entwürfe sind frei änderbar.
    IF OLD."status" = 'ENTWURF' THEN
        RETURN NEW;
    END IF;

    -- Die zulässigen Änderungen auf den alten Stand zurückdrehen. Bleibt danach
    -- ein Unterschied übrig, wurde etwas Unzulässiges angefasst.
    vergleich := NEW;
    vergleich."geaendertAm" := OLD."geaendertAm";

    IF OLD."status" = 'ABGESCHLOSSEN' AND NEW."status" = 'ERSETZT' THEN
        vergleich."status" := OLD."status";
    END IF;

    IF OLD."protokollDateiId" IS NULL THEN
        vergleich."protokollDateiId" := OLD."protokollDateiId";
    END IF;

    IF OLD."protokollNummer" IS NULL THEN
        vergleich."protokollNummer" := OLD."protokollNummer";
    END IF;

    IF vergleich IS DISTINCT FROM OLD THEN
        RAISE EXCEPTION
            'Die Pruefung % ist abgeschlossen und damit unveraenderlich. '
            'Eine Korrektur wird als neue Pruefung mit Verweis auf diese erfasst.',
            OLD."id"
            USING ERRCODE = 'insufficient_privilege';
    END IF;

    RETURN NEW;
END;
$$;

COMMENT ON FUNCTION pruefung_unveraenderlich() IS
    'Sperrt Aenderungen an abgeschlossenen Pruefungen, bis auf Ersetzung und Nachtrag des Protokolls.';

-- Gelöscht wird ein Protokoll gar nicht — auch nicht weich. Die einzige
-- Ausnahme ist der Aufbewahrungslauf, derselbe Schalter wie beim Audit-Log.
CREATE OR REPLACE FUNCTION pruefung_kein_loeschen()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD."status" = 'ENTWURF' THEN
        RETURN OLD;
    END IF;

    IF coalesce(current_setting('app.audit_bereinigung', true), '') = 'an' THEN
        RETURN OLD;
    END IF;

    RAISE EXCEPTION
        'Die Pruefung % ist abgeschlossen und darf nicht geloescht werden '
        '(nur im Aufbewahrungslauf).',
        OLD."id"
        USING ERRCODE = 'insufficient_privilege';
END;
$$;

CREATE TRIGGER pruefung_abschluss_schuetzen
    BEFORE UPDATE ON "pruefung"
    FOR EACH ROW EXECUTE FUNCTION pruefung_unveraenderlich();

CREATE TRIGGER pruefung_loeschschutz
    BEFORE DELETE ON "pruefung"
    FOR EACH ROW EXECUTE FUNCTION pruefung_kein_loeschen();

-- -----------------------------------------------------------------------------
-- Dasselbe für die Bestandteile eines Protokolls.
--
-- Ohne diese Sperre ließe sich ein festgeschriebenes Protokoll über den Umweg
-- der Einzelergebnisse verändern: die Prüfung bliebe unangetastet, ihr Inhalt
-- nicht. Beim Löschen der Prüfung selbst greift die Kaskade — dann gibt es die
-- Prüfung schon nicht mehr, und die Abfrage unten liefert NULL.
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION pruefungsinhalt_gesperrt()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    betroffene_pruefung uuid;
    zustand "PruefungStatus";
BEGIN
    IF TG_OP = 'DELETE' THEN
        betroffene_pruefung := OLD."pruefungId";
    ELSE
        betroffene_pruefung := NEW."pruefungId";
    END IF;

    SELECT "status" INTO zustand FROM "pruefung" WHERE "id" = betroffene_pruefung;

    -- Prüfung existiert nicht mehr (Kaskade) oder ist noch Entwurf: erlaubt.
    IF zustand IS NULL OR zustand = 'ENTWURF' THEN
        IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF;
    END IF;

    IF coalesce(current_setting('app.audit_bereinigung', true), '') = 'an' THEN
        IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF;
    END IF;

    RAISE EXCEPTION
        'Die Pruefung % ist abgeschlossen. % auf %  ist nicht mehr zulaessig.',
        betroffene_pruefung, TG_OP, TG_TABLE_NAME
        USING ERRCODE = 'insufficient_privilege';
END;
$$;

CREATE TRIGGER pruefergebnis_gesperrt
    BEFORE INSERT OR UPDATE OR DELETE ON "pruefergebnis"
    FOR EACH ROW EXECUTE FUNCTION pruefungsinhalt_gesperrt();

CREATE TRIGGER signatur_gesperrt
    BEFORE INSERT OR UPDATE OR DELETE ON "signatur"
    FOR EACH ROW EXECUTE FUNCTION pruefungsinhalt_gesperrt();

-- -----------------------------------------------------------------------------
-- Ein Messwert außerhalb seines Bereichs ist kein Messwert, sondern ein Fehler
-- in der Vorlage: die Untergrenze darf nicht über der Obergrenze liegen.
-- -----------------------------------------------------------------------------
ALTER TABLE "checklistenpunkt" ADD CONSTRAINT "checklistenpunkt_bereich_sinnvoll" CHECK (
    "minWert" IS NULL OR "maxWert" IS NULL OR "minWert" <= "maxWert"
);

-- Ein Intervall von null Monaten würde im nächtlichen Lauf endlos Termine
-- erzeugen.
ALTER TABLE "wartungsvorlage" ADD CONSTRAINT "wartungsvorlage_intervall_positiv" CHECK (
    "intervallMonate" > 0 AND "vorwarnzeitTage" >= 0
);

-- -----------------------------------------------------------------------------
-- Indizes
--
-- Das Fristen-Dashboard fragt immer dasselbe: offene Termine, sortiert nach
-- Fälligkeit. Ein Teilindex darauf ist klein und bleibt es auch, wenn die
-- Tabelle nach Jahren zehntausende erledigte Termine enthält.
-- -----------------------------------------------------------------------------
CREATE INDEX "wartungstermin_offen" ON "wartungstermin" ("organisationId", "faelligAm")
    WHERE "status" = 'OFFEN' AND "geloeschtAm" IS NULL;

CREATE INDEX "mangel_offen" ON "mangel" ("organisationId", "prioritaet", "gemeldetAm")
    WHERE "status" <> 'BEHOBEN' AND "status" <> 'VERWORFEN' AND "geloeschtAm" IS NULL;

-- Volltextsuche wie in Phase 1: pg_trgm macht ILIKE '%text%' indexfähig.
CREATE INDEX "mangel_suche_beschreibung" ON "mangel" USING gin ("beschreibung" gin_trgm_ops);
CREATE INDEX "rueckruf_suche_titel" ON "rueckruf" USING gin ("titel" gin_trgm_ops);
CREATE INDEX "rueckruf_suche_hersteller" ON "rueckruf" USING gin ("hersteller" gin_trgm_ops);
CREATE INDEX "wartungsvorlage_suche_name" ON "wartungsvorlage" USING gin ("name" gin_trgm_ops);

-- -----------------------------------------------------------------------------
-- Rechte für die Anwendungsrolle auf die neuen Tabellen.
-- -----------------------------------------------------------------------------
DO $$
BEGIN
    IF EXISTS (SELECT FROM pg_roles WHERE rolname = 'florian_app') THEN
        EXECUTE 'GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO florian_app';
        EXECUTE 'GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO florian_app';

        -- Die Einschraenkungen aus Phase 0 gelten unveraendert weiter.
        EXECUTE 'REVOKE INSERT, UPDATE, DELETE ON "berechtigung" FROM florian_app';
        EXECUTE 'REVOKE UPDATE, DELETE ON "audit_log" FROM florian_app';

        -- DELETE auf "pruefung" bleibt erlaubt: ein abgebrochener Entwurf soll
        -- verschwinden koennen. Abgeschlossene Protokolle schuetzt der Trigger
        -- pruefung_loeschschutz, und der gilt fuer jede Rolle.
    ELSE
        RAISE NOTICE 'Rolle florian_app nicht vorhanden — Rechtevergabe uebersprungen.';
    END IF;
END;
$$;