AfiS-Aktensystem/packages/db/prisma/migrations/20260904100000_abteilungen/migration.sql

-- Abteilungen: die Ebene über der Personalakte.
--
-- Bisher stand die Abteilung als Freitext in der Personalakte. Damit liess sich
-- niemand fragen, was es zu einer Abteilung insgesamt gibt - jede Schreibweise
-- war eine eigene Abteilung. Als eigenes Modell ist sie verwaltbar, und die
-- Zuordnung einer Fallakte ergibt sich aus ihren Beteiligten und deren
-- Personalakten. Eine Fallakte trägt deshalb **keine** Abteilung: die
-- Zuordnung soll aktuell bleiben, wenn sich die Beteiligten ändern.

CREATE TABLE "Department" (
    "id"           TEXT         NOT NULL,
    "key"          TEXT         NOT NULL,
    "name"         TEXT         NOT NULL,
    "description"  TEXT,
    "minClearance" INTEGER      NOT NULL DEFAULT 0,
    "isArchived"   BOOLEAN      NOT NULL DEFAULT false,
    "createdById"  TEXT,
    "createdAt"    TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt"    TIMESTAMP(3) NOT NULL,

    CONSTRAINT "Department_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "Department_key_key" ON "Department"("key");
CREATE INDEX "Department_isArchived_name_idx" ON "Department"("isArchived", "name");

-- Die Personalakte zeigt jetzt auf die Abteilung.
ALTER TABLE "PersonnelFile" ADD COLUMN "departmentId" TEXT;

-- Was schon als Freitext dastand, wird zur Abteilung. Der Schlüssel ist der
-- getrimmte Text in Grossbuchstaben, der Name der Text selbst - beides lässt
-- sich im Adminbereich nachziehen.
INSERT INTO "Department" ("id", "key", "name", "updatedAt")
SELECT gen_random_uuid()::text,
       upper(btrim("department")),
       btrim("department"),
       CURRENT_TIMESTAMP
  FROM (SELECT DISTINCT "department" FROM "PersonnelFile"
         WHERE "department" IS NOT NULL AND btrim("department") <> '') AS vorhanden;

UPDATE "PersonnelFile" p
   SET "departmentId" = d."id"
  FROM "Department" d
 WHERE p."department" IS NOT NULL
   AND upper(btrim(p."department")) = d."key";

-- Zwei Suchauslöser hängen am Freitextfeld und müssen vorher weg. Sie kommen
-- gleich darunter wieder, dann über die Beziehung: gesucht wird nach dem Namen
-- der Abteilung, nicht mehr nach der Schreibweise in der Akte.
DROP TRIGGER IF EXISTS personnel_search_trg ON "PersonnelFile";
DROP TRIGGER IF EXISTS search_on_personnel ON "PersonnelFile";

ALTER TABLE "PersonnelFile" DROP COLUMN "department";

CREATE OR REPLACE FUNCTION personnel_refresh_search_vector() RETURNS trigger AS $$
DECLARE
  abteilung text := '';
BEGIN
  IF NEW."departmentId" IS NOT NULL THEN
    SELECT coalesce(d."name", '') || ' ' || coalesce(d."key", '')
      INTO abteilung FROM "Department" d WHERE d."id" = NEW."departmentId";
  END IF;

  NEW."searchVector" :=
    setweight(to_tsvector('german', coalesce(NEW."displayName", '')), 'A')
    ||
    setweight(to_tsvector('german',
      coalesce(NEW."rank", '') || ' ' || coalesce(abteilung, '') || ' ' ||
      coalesce(array_to_string(NEW."statusTags", ' '), '')), 'B');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER personnel_search_trg
  BEFORE INSERT OR UPDATE OF "displayName", "rank", "departmentId", "statusTags"
  ON "PersonnelFile"
  FOR EACH ROW EXECUTE FUNCTION personnel_refresh_search_vector();

CREATE OR REPLACE FUNCTION refresh_personnel_search(target_id text) RETURNS void AS $$
DECLARE
  stufe int;
  vec tsvector;
BEGIN
  DELETE FROM "SearchIndex" WHERE "targetType" = 'personnel' AND "targetId" = target_id;

  SELECT setweight(to_tsvector('german', p."displayName"), 'A')
      || setweight(to_tsvector('german',
           coalesce(p."rank", '') || ' ' ||
           coalesce(d."name", '') || ' ' || coalesce(d."key", '') || ' ' ||
           coalesce(array_to_string(p."statusTags", ' '), '')), 'B')
      || setweight(to_tsvector('german', coalesce(p."note", '')), 'C')
    INTO vec
    FROM "PersonnelFile" p
    LEFT JOIN "Department" d ON d."id" = p."departmentId"
   WHERE p."id" = target_id;

  IF vec IS NULL THEN
    RETURN;
  END IF;

  FOR stufe IN 0..5 LOOP
    INSERT INTO "SearchIndex" ("targetType", "targetId", "minClearance", "vector")
    VALUES ('personnel', target_id, stufe, vec);
  END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER search_on_personnel
  AFTER INSERT OR UPDATE OF "displayName", "rank", "departmentId", "statusTags", "note"
  ON "PersonnelFile"
  FOR EACH ROW EXECUTE FUNCTION search_index_trigger();

-- Bestehende Zeilen einmal nachziehen, sonst tragen sie den alten Vektor und
-- die Suche findet die Abteilung nicht.
UPDATE "PersonnelFile" SET "displayName" = "displayName";

ALTER TABLE "PersonnelFile"
    ADD CONSTRAINT "PersonnelFile_departmentId_fkey"
    FOREIGN KEY ("departmentId") REFERENCES "Department"("id")
    ON DELETE SET NULL ON UPDATE CASCADE;

CREATE INDEX "PersonnelFile_departmentId_idx" ON "PersonnelFile"("departmentId");

-- Ein Dokument hängt weiterhin an einer Fall- oder Personalakte. Eine
-- Abteilung als dritter Besitzer wäre eine halbe Sache: die Vorlagen kennen
-- über TemplateUse nur die beiden Orte, und ein Feld, das nichts füllen kann,
-- ist schlimmer als keins. Was zu einer Abteilung an Dokumenten gehört, wird
-- wie alles andere abgeleitet - über ihre Mitglieder und deren Fälle.

-- Der Chat bekommt einen dritten Ort. Der CHECK aus der vorigen Migration wird
-- entsprechend erweitert: genau einer, weiterhin.
ALTER TABLE "ChatMessage" ADD COLUMN "departmentId" TEXT;
ALTER TABLE "ChatMessage"
    ADD CONSTRAINT "ChatMessage_departmentId_fkey"
    FOREIGN KEY ("departmentId") REFERENCES "Department"("id")
    ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX "ChatMessage_departmentId_createdAt_idx"
    ON "ChatMessage"("departmentId", "createdAt" DESC);

ALTER TABLE "ChatMessage" DROP CONSTRAINT "ChatMessage_genau_ein_ort";
ALTER TABLE "ChatMessage"
    ADD CONSTRAINT "ChatMessage_genau_ein_ort"
    CHECK (num_nonnulls("caseId", "personnelFileId", "departmentId") = 1);

CREATE OR REPLACE FUNCTION chat_immutable_guard() RETURNS trigger AS $$
BEGIN
  IF NEW."authorId"        IS DISTINCT FROM OLD."authorId"
  OR NEW."createdAt"       IS DISTINCT FROM OLD."createdAt"
  OR NEW."caseId"          IS DISTINCT FROM OLD."caseId"
  OR NEW."personnelFileId" IS DISTINCT FROM OLD."personnelFileId"
  OR NEW."departmentId"    IS DISTINCT FROM OLD."departmentId" THEN
    RAISE EXCEPTION 'authorId, createdAt und der Ort einer ChatMessage sind unveränderlich';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ===========================================================================
-- Vermerke an einer Abteilung.
--
-- Dieselben Zusicherungen wie beim Verlauf einer Akte (harte Regel 2): kein
-- DELETE, jede Änderung erzeugt eine Version, jede Zurücknahme lässt einen
-- Grabstein stehen. Die Regeln stehen in der Datenbank und nicht nur in der
-- Anwendung - die Anwendung kann man umgehen.
-- ===========================================================================

CREATE TABLE "DepartmentEntry" (
    "id"               TEXT         NOT NULL,
    "departmentId"     TEXT         NOT NULL,
    "body"             TEXT         NOT NULL,
    "minClearance"     INTEGER      NOT NULL DEFAULT 0,
    "authorId"         TEXT         NOT NULL,
    "createdAt"        TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "version"          INTEGER      NOT NULL DEFAULT 1,
    "editedAt"         TIMESTAMP(3),
    "editedById"       TEXT,
    "retractedAt"      TIMESTAMP(3),
    "retractedById"    TEXT,
    "retractionReason" TEXT,

    CONSTRAINT "DepartmentEntry_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "DepartmentEntryVersion" (
    "id"           TEXT         NOT NULL,
    "entryId"      TEXT         NOT NULL,
    "version"      INTEGER      NOT NULL,
    "body"         TEXT         NOT NULL,
    "minClearance" INTEGER      NOT NULL,
    "editedById"   TEXT         NOT NULL,
    "editedAt"     TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "DepartmentEntryVersion_pkey" PRIMARY KEY ("id")
);

ALTER TABLE "DepartmentEntry"
    ADD CONSTRAINT "DepartmentEntry_departmentId_fkey"
    FOREIGN KEY ("departmentId") REFERENCES "Department"("id")
    ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "DepartmentEntryVersion"
    ADD CONSTRAINT "DepartmentEntryVersion_entryId_fkey"
    FOREIGN KEY ("entryId") REFERENCES "DepartmentEntry"("id")
    ON DELETE RESTRICT ON UPDATE CASCADE;

CREATE INDEX "DepartmentEntry_departmentId_createdAt_idx"
    ON "DepartmentEntry"("departmentId", "createdAt" DESC);
CREATE INDEX "DepartmentEntry_authorId_idx" ON "DepartmentEntry"("authorId");
CREATE UNIQUE INDEX "DepartmentEntryVersion_entryId_version_key"
    ON "DepartmentEntryVersion"("entryId", "version");
CREATE INDEX "DepartmentEntryVersion_entryId_version_idx"
    ON "DepartmentEntryVersion"("entryId", "version" DESC);

CREATE RULE dept_entry_no_delete AS ON DELETE TO "DepartmentEntry" DO INSTEAD NOTHING;
CREATE RULE dept_entry_version_no_delete
    AS ON DELETE TO "DepartmentEntryVersion" DO INSTEAD NOTHING;

-- Autor, Entstehungszeit und Abteilung sind unantastbar. Wer etwas anderes
-- sagen will, zieht zurück und schreibt neu - und genau das ist dann sichtbar.
CREATE OR REPLACE FUNCTION dept_entry_immutable_guard() RETURNS trigger AS $$
BEGIN
  IF NEW."authorId"     IS DISTINCT FROM OLD."authorId"
  OR NEW."createdAt"    IS DISTINCT FROM OLD."createdAt"
  OR NEW."departmentId" IS DISTINCT FROM OLD."departmentId" THEN
    RAISE EXCEPTION 'authorId, createdAt und die Abteilung eines Vermerks sind unveränderlich';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER dept_entry_immutable_guard_trg
  BEFORE UPDATE ON "DepartmentEntry"
  FOR EACH ROW EXECUTE FUNCTION dept_entry_immutable_guard();

-- Eine Version darf nur eines: die Stufe mitziehen, wenn der Vermerk höher
-- eingestuft wird. Sonst läse jemand den ursprünglichen Text in der Historie.
CREATE OR REPLACE FUNCTION dept_entry_version_guard() RETURNS trigger AS $$
BEGIN
  IF NEW."id"         IS DISTINCT FROM OLD."id"
  OR NEW."entryId"    IS DISTINCT FROM OLD."entryId"
  OR NEW."version"    IS DISTINCT FROM OLD."version"
  OR NEW."body"       IS DISTINCT FROM OLD."body"
  OR NEW."editedById" IS DISTINCT FROM OLD."editedById"
  OR NEW."editedAt"   IS DISTINCT FROM OLD."editedAt" THEN
    RAISE EXCEPTION 'Versionen eines Abteilungsvermerks sind unveränderlich';
  END IF;
  IF NEW."minClearance" < OLD."minClearance" THEN
    RAISE EXCEPTION 'Die Stufe einer Version kann nur steigen';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER dept_entry_version_guard_trg
  BEFORE UPDATE ON "DepartmentEntryVersion"
  FOR EACH ROW EXECUTE FUNCTION dept_entry_version_guard();