MABEA/ergebnisse/20_datenbank_schema.md

Prompt 20 – Datenbankschema

Bezug: 06_datenmodell, 07_materialstamm, 08_beladungsvorlagen, 09_duplizieren, 10_individuelle_beladung, 03_fehlbestandsmanagement, 04_mindermengen, 05_rollen_rechte, 13_historie_audit. Basis: PostgreSQL (Prompt 19).

Konkretes relationales Schema, abgeleitet aus dem fachlichen Datenmodell. Migrationstool (z. B. Alembic) wird bei Code-Umsetzung eingesetzt, hier reines Ziel-Schema.

Update (Karte 13 – Satelliten-Server): Tabellen, die auch an einem getrennten Satelliten-Server neue Zeilen erzeugen können (Kontrolle, Kontrollposition, Fehlbestand, Nachfüllung, Mindermengen-Genehmigung, Historie, sowie Objektposition), verwenden UUID statt SERIAL als Primärschlüssel, damit beim späteren Zusammenführen keine ID-Kollisionen zwischen Hauptserver und Satellit entstehen. Reine Stammdaten-Tabellen (Bereich, Kategorie, Standort, Objekttyp, Material, Vorlage, Benutzer, Objekt selbst) bleiben SERIAL, da sie nur zentral am Hauptserver gepflegt werden. gen_random_uuid() ist seit PostgreSQL 13 fest eingebaut, keine Extension nötig (bei älteren Versionen CREATE EXTENSION pgcrypto).

Korrektur nach Gesamtprüfung (siehe Punkt 10/11): Reines Batch-Insert reicht als Sync-Mechanismus NICHT aus, da am Satelliten auch bestehende objektposition-Zeilen per UPDATE verändert werden (Ist-Menge, Seriennummer, Ablaufdatum, Chargennummer). Details siehe Punkt 10.

1. Stammdaten

CREATE TABLE bereich (
    id              SERIAL PRIMARY KEY,
    name            TEXT NOT NULL UNIQUE,
    beschreibung    TEXT
);

CREATE TABLE kategorie (
    id                  SERIAL PRIMARY KEY,
    bereich_id          INTEGER NOT NULL REFERENCES bereich(id),
    name                TEXT NOT NULL,
    ueberkategorie_id   INTEGER REFERENCES kategorie(id),
    UNIQUE (bereich_id, name)
);

CREATE TABLE standort (
    id      SERIAL PRIMARY KEY,
    name    TEXT NOT NULL UNIQUE,
    adresse TEXT
);

CREATE TABLE objekttyp (
    id          SERIAL PRIMARY KEY,
    bereich_id  INTEGER NOT NULL REFERENCES bereich(id),
    kategorie_id INTEGER REFERENCES kategorie(id),
    name        TEXT NOT NULL,
    UNIQUE (bereich_id, name)
);

CREATE TYPE materialtyp AS ENUM ('standard', 'ablauf_charge', 'geraet_sn');

CREATE TABLE material (
    id              SERIAL PRIMARY KEY,
    name            TEXT NOT NULL,
    artikelnummer   TEXT,
    einheit         TEXT NOT NULL,
    materialtyp     materialtyp NOT NULL,
    kategorie_id    INTEGER REFERENCES kategorie(id),
    hersteller      TEXT,
    beschreibung    TEXT,
    code            TEXT UNIQUE,              -- QR/Barcode, Code128 (Karte 10)
    warnzeitraum_tage INTEGER,                 -- Standard-Vorwarnung bei Ablaufdatum (Prompt 14)
    aktiv           BOOLEAN NOT NULL DEFAULT TRUE
);

2. Vorlagen

CREATE TYPE vorlage_status AS ENUM ('aktiv', 'veraltet');

CREATE TABLE beladungsvorlage (
    id              SERIAL PRIMARY KEY,
    objekttyp_id    INTEGER NOT NULL REFERENCES objekttyp(id),
    name            TEXT NOT NULL,
    version         INTEGER NOT NULL,
    gueltig_ab      TIMESTAMPTZ NOT NULL DEFAULT now(),
    status          vorlage_status NOT NULL DEFAULT 'aktiv',
    UNIQUE (objekttyp_id, name, version)
);

CREATE TABLE vorlagenposition (
    id              SERIAL PRIMARY KEY,
    vorlage_id      INTEGER NOT NULL REFERENCES beladungsvorlage(id),
    material_id     INTEGER NOT NULL REFERENCES material(id),
    fach            TEXT,                     -- Kategorie/Fach innerhalb der Vorlage
    sollmenge       NUMERIC NOT NULL,
    UNIQUE (vorlage_id, material_id)
);

3. Objekte (Rucksäcke/Fahrzeuge/Ressourcen)

CREATE TYPE knoten_typ AS ENUM ('haupt', 'satellit');

CREATE TABLE systemknoten (
    id              SERIAL PRIMARY KEY,
    name            TEXT NOT NULL UNIQUE,       -- z. B. 'Hauptserver', 'Satellit Einsatzort Nord'
    typ             knoten_typ NOT NULL DEFAULT 'satellit'
);
-- genau eine Zeile mit typ='haupt' vorgesehen.

CREATE TYPE objekt_status AS ENUM ('aktiv', 'ausser_dienst');

CREATE TABLE objekt (
    id                      SERIAL PRIMARY KEY,
    code                    TEXT NOT NULL UNIQUE,      -- QR/Barcode, Code128 (Karte 10)
    name                    TEXT NOT NULL,
    objekttyp_id            INTEGER NOT NULL REFERENCES objekttyp(id),
    vorlage_id              INTEGER REFERENCES beladungsvorlage(id),
    standort_id             INTEGER NOT NULL REFERENCES standort(id),
    status                  objekt_status NOT NULL DEFAULT 'aktiv',
    zustaendiger_server_id  INTEGER NOT NULL REFERENCES systemknoten(id)  -- Karte 13: aktuell schreibberechtigter Knoten
);

-- objektposition ist UUID (nicht SERIAL): kann am Satelliten sowohl per UPDATE (Ist-Menge/SN/Ablauf)
-- als auch per INSERT (neues Material am Objekt hinzugefügt, Prompt 10) verändert werden.
CREATE TYPE objektposition_status AS ENUM ('aktiv', 'entfernt');

CREATE TABLE objektposition (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),  -- Karte 13: sync-relevant
    objekt_id           INTEGER NOT NULL REFERENCES objekt(id),
    material_id         INTEGER NOT NULL REFERENCES material(id),
    sollmenge_override  NUMERIC,                    -- NULL = folgt Vorlage dynamisch
    ist_status          objektposition_status NOT NULL DEFAULT 'aktiv', -- korrigiert: Standard ist 'aktiv', nicht 'entfernt' (Prompt 10)
    istmenge            NUMERIC NOT NULL DEFAULT 0,
    seriennummer        TEXT,                       -- nur Materialtyp geraet_sn
    ablaufdatum         DATE,                        -- nur Materialtyp ablauf_charge
    chargennummer       TEXT,                        -- nur Materialtyp ablauf_charge
    zuletzt_geaendert_am TIMESTAMPTZ NOT NULL DEFAULT now(),  -- Basis für Delta-Sync (Punkt 10)
    UNIQUE (objekt_id, material_id)
);

4. Zuständigkeiten, Benutzer, Rollen

CREATE TABLE benutzer (
    id              SERIAL PRIMARY KEY,
    name            TEXT NOT NULL,
    login           TEXT NOT NULL UNIQUE,
    passwort_hash   TEXT NOT NULL,
    aktiv           BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TYPE rolle_typ AS ENUM ('mitarbeiter', 'materialverantwortlicher', 'leitungsverantwortlicher', 'administration');

CREATE TABLE benutzer_rolle (
    benutzer_id     INTEGER NOT NULL REFERENCES benutzer(id),
    rolle           rolle_typ NOT NULL,
    PRIMARY KEY (benutzer_id, rolle)
);

-- zustaendigkeit/kontrollverantwortung sind UUID: Administration könnte theoretisch auch am
-- Satelliten Zuordnungen anlegen/ändern (z. B. Kontrollverantwortung vor Ort neu vergeben).
CREATE TABLE zustaendigkeit (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    benutzer_id     INTEGER NOT NULL REFERENCES benutzer(id),
    standort_id     INTEGER REFERENCES standort(id),
    objekt_id       INTEGER REFERENCES objekt(id),
    CHECK (standort_id IS NOT NULL OR objekt_id IS NOT NULL)
);

CREATE TABLE kontrollverantwortung (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    objekt_id       INTEGER NOT NULL REFERENCES objekt(id),
    benutzer_id     INTEGER REFERENCES benutzer(id),   -- Einzelperson, ODER
    gruppe          TEXT                                -- Gruppe (frei benannt, V1-Vereinfachung ohne eigene Gruppen-Entität; echte Gruppen-Verwaltung Roadmap), Karte 01
);

5. Kontrolle

CREATE TYPE kontroll_status AS ENUM ('nicht_gestartet', 'in_bearbeitung', 'abgeschlossen', 'abgebrochen');

CREATE TABLE kontrolle (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),  -- Karte 13: sync-relevant
    erzeugt_von_server_id INTEGER NOT NULL REFERENCES systemknoten(id),
    objekt_id       INTEGER NOT NULL REFERENCES objekt(id),
    benutzer_id     INTEGER NOT NULL REFERENCES benutzer(id),
    status          kontroll_status NOT NULL DEFAULT 'in_bearbeitung',
    gestartet_am    TIMESTAMPTZ NOT NULL DEFAULT now(),
    beendet_am      TIMESTAMPTZ,
    abbruch_grund   TEXT,
    signatur        BYTEA  -- optionales Touch-Signaturbild am Kontrollnachweis (Prompt 16), nullable, standardmäßig ungenutzt
);

CREATE TABLE kontrollposition (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),  -- Karte 13: sync-relevant
    kontrolle_id    UUID NOT NULL REFERENCES kontrolle(id),
    material_id     INTEGER NOT NULL REFERENCES material(id),
    sollmenge_snapshot  NUMERIC NOT NULL,
    istmenge_erfasst    NUMERIC NOT NULL,
    abweichung          BOOLEAN NOT NULL
);

6. Fehlbestand, Nachfüllung, Mindermenge

CREATE TYPE fehlbestand_status AS ENUM ('offen', 'in_bearbeitung', 'nachgefuellt_teilweise', 'erledigt');

CREATE TABLE fehlbestand (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),  -- Karte 13: sync-relevant
    erzeugt_von_server_id INTEGER NOT NULL REFERENCES systemknoten(id),
    objekt_id           INTEGER NOT NULL REFERENCES objekt(id),
    material_id         INTEGER NOT NULL REFERENCES material(id),
    standort_id         INTEGER NOT NULL REFERENCES standort(id),
    sollmenge           NUMERIC NOT NULL,
    istmenge             NUMERIC NOT NULL,
    fehlmenge            NUMERIC NOT NULL,
    entstanden_am         TIMESTAMPTZ NOT NULL DEFAULT now(),  -- Basis Eskalation, Karte 12
    festgestellt_von      INTEGER NOT NULL REFERENCES benutzer(id),
    kontrolle_id          UUID REFERENCES kontrolle(id),
    ursache               TEXT,
    verantwortlicher_id   INTEGER REFERENCES benutzer(id),
    status                fehlbestand_status NOT NULL DEFAULT 'offen',
    erledigt_am           TIMESTAMPTZ
);

CREATE TABLE nachfuellung (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),  -- Karte 13: sync-relevant
    fehlbestand_id  UUID REFERENCES fehlbestand(id),
    objekt_id       INTEGER NOT NULL REFERENCES objekt(id),
    material_id     INTEGER NOT NULL REFERENCES material(id),
    menge           NUMERIC NOT NULL,
    benutzer_id     INTEGER NOT NULL REFERENCES benutzer(id),
    zeitpunkt       TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TYPE mindermenge_status AS ENUM ('aktiv', 'abgelaufen', 'beendet_durch_erledigung');

CREATE TABLE mindermengen_genehmigung (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),  -- Karte 13: sync-relevant
    fehlbestand_id      UUID NOT NULL REFERENCES fehlbestand(id),
    genehmigt_von        INTEGER NOT NULL REFERENCES benutzer(id),
    begruendung          TEXT NOT NULL,
    genehmigt_am          TIMESTAMPTZ NOT NULL DEFAULT now(),
    ausloesende_kontrolle_id UUID NOT NULL REFERENCES kontrolle(id),
    status                mindermenge_status NOT NULL DEFAULT 'aktiv',
    beendet_am            TIMESTAMPTZ,
    beendende_kontrolle_id UUID REFERENCES kontrolle(id)
);

7. Historie/Audit

CREATE TABLE historie (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),  -- Karte 13: sync-relevant
    erzeugt_von_server_id INTEGER NOT NULL REFERENCES systemknoten(id),
    zeitpunkt       TIMESTAMPTZ NOT NULL DEFAULT now(),
    benutzer_id     INTEGER REFERENCES benutzer(id),   -- NULL bei System-Ereignissen
    ereignistyp     TEXT NOT NULL,                     -- z. B. 'fehlbestand_entstanden', 'mindermenge_genehmigt', ...
    entitaet_typ    TEXT NOT NULL,                      -- z. B. 'fehlbestand', 'objektposition', 'vorlage'
    entitaet_id     TEXT NOT NULL,                       -- TEXT statt INTEGER: referenzierte Entität kann INTEGER- oder UUID-ID haben
    alter_wert      JSONB,
    neuer_wert      JSONB,
    begruendung     TEXT
);
-- Append-only: Anwendungs-DB-Rolle erhält nur INSERT-Recht auf diese Tabelle, kein UPDATE/DELETE.

8. Indizes (Auswahl, wichtigste Zugriffspfade)

CREATE INDEX idx_fehlbestand_status ON fehlbestand(status);
CREATE INDEX idx_fehlbestand_objekt ON fehlbestand(objekt_id);
CREATE INDEX idx_fehlbestand_entstanden ON fehlbestand(entstanden_am);  -- für Alter-Sortierung, Prompt 12
CREATE INDEX idx_objektposition_objekt ON objektposition(objekt_id);
CREATE INDEX idx_historie_entitaet ON historie(entitaet_typ, entitaet_id);
CREATE INDEX idx_zustaendigkeit_benutzer ON zustaendigkeit(benutzer_id);

9. Wichtige Constraints/Prinzipien

• Kein Fremdschlüssel-Löschen mit CASCADE auf historie-relevante Tabellen (fehlbestand, kontrolle, historie) – Löschung von Stammdaten (Material/Objekt) erfolgt nur über aktiv = false, nie physisches DELETE, damit Historie referenzierbar bleibt (Prompt 13).
• mindermengen_genehmigung referenziert Fehlbestand 1:0..n, in der Praxis max. 1 mit Status aktiv gleichzeitig – wird auf Anwendungsebene erzwungen (kein reiner DB-Constraint, da Historie mehrerer vergangener Genehmigungen erhalten bleiben muss).
• objektposition.sollmenge_override IS NULL bedeutet: Sollmenge wird zur Laufzeit aus aktueller vorlagenposition der referenzierten Vorlage berechnet (Prompt 10).

10. Satelliten-Server – Objekt-Sperre und Synchronisation (Karte 13)

• objekt.zustaendiger_server_id bestimmt, welcher Server (Haupt oder ein bestimmter Satellit) aktuell Schreibrechte für dieses Objekt hat. Anwendungslogik (nicht reiner DB-Constraint) prüft vor jedem Schreibzugriff auf kontrolle, fehlbestand, nachfuellung, mindermengen_genehmigung, objektposition, ob der ausführende Server mit objekt.zustaendiger_server_id übereinstimmt.
• „Objekt auslagern" (Platzhalter :satelliten_id/:objekt_id durch konkrete Werte ersetzen):

  UPDATE objekt SET zustaendiger_server_id = :satelliten_id WHERE id = :objekt_id;
  

  Ab diesem Zeitpunkt lehnt der Hauptserver Schreibzugriffe auf dieses Objekt ab.
• Synchronisation ist NICHT reines Batch-Insert (Korrektur nach Gesamtprüfung): drei unterschiedliche Sync-Fälle müssen unterschieden werden:
  1. Neue Zeilen (Kontrolle, Kontrollposition, Nachfüllung, Historie, neu hinzugefügte Objektposition, neue Zuständigkeits-/Kontrollverantwortungs-Zuordnung, falls am Satelliten durch Administration vergeben): einfacher Insert am Hauptserver, da UUID bereits eindeutig vergeben – keine Kollision möglich.
  2. Geänderte bestehende Zeilen (objektposition: Ist-Menge, Seriennummer, Ablaufdatum, Chargennummer – vom Hauptserver vor Auslagerung an den Satelliten kopiert, dort per UPDATE verändert): werden beim Sync per Upsert übertragen (INSERT ... ON CONFLICT (id) DO UPDATE), nicht als reiner Insert. zuletzt_geaendert_am dient als Erkennungsmerkmal, welche Zeilen sich am Satelliten geändert haben.
  3. Fehlbestand und Mindermengen-Genehmigung, die bereits vor der Auslagerung existierten (offener Fehlbestand am Objekt, evtl. aktive Genehmigung) und am Satelliten weiterbearbeitet werden (Nachfüllung, neue Genehmigung, Statuswechsel auf erledigt): ebenfalls Upsert, nicht reiner Insert – dieselben UUIDs, status/istmenge/erledigt_am bzw. status/beendet_am können sich am Satelliten ändern.
  • Da ein Objekt während der Auslagerung exklusiv einem Server zugeordnet ist (siehe Objekt-Sperre), gibt es keine gleichzeitige Änderung derselben Zeile an zwei Orten – Upsert ist damit konfliktfrei, kein Merge nötig.
  • Reihenfolge beim Sync: zuerst Objektpositionen-Upsert, danach Fehlbestand/Mindermengen-Genehmigung-Upsert, danach neue Kontrollen/Nachfüllungen/Historie per Insert (referenzielle Integrität).
• Nach vollständiger Übertragung:

  UPDATE objekt SET zustaendiger_server_id = :hauptserver_id WHERE id = :objekt_id;
  

• Einschränkung für Struktur­änderungen am Satelliten: Anlegen komplett neuer Vorlagen/Materialstamm-Einträge bleibt dem Hauptserver vorbehalten (diese Tabellen sind SERIAL, nicht sync-fähig). Am Satelliten sind nur Änderungen an bereits vorhandenen objektposition-Zeilen, das Hinzufügen individueller Zusatzpositionen (Prompt 10, eigene UUID-Zeile) sowie neue Zuständigkeits-/Kontrollverantwortungs-Einträge möglich.
• Benötigte PostgreSQL-Version: ≥ 13 für eingebautes gen_random_uuid() (siehe oben).
• Vorab-Bestückung des Satelliten (Klärung L2): neben Materialstamm, Vorlagen und den betroffenen Objekten/Objektpositionen müssen auch offene Fehlbestände und aktive Mindermengen-Genehmigungen der ausgelagerten Objekte in die Satelliten-Kopie übernommen werden – sonst kann am Satelliten weder nachgefüllt noch genehmigt werden, ohne einen Duplikat-Fehlbestand zu erzeugen. Details der Bestückungslogik bleiben offen (siehe 13_satelliten_server).

Referenzen

Bezug: 06_datenmodell, 19_technische_architektur, 13_satelliten_server