Datenmodell

Stand: 30.07.2026 · gilt zusammen mit PROJEKT.md · Fassung: 1.0

SQLite, eine Datei: daten\betreuungsstellen.sqlite3. Alle Datums-/Zeitangaben als ISO-Text (2026-07-30 bzw. 2026-07-30T12:03:41), UTC-frei, lokale Zeit. INTEGER 0/1 für Wahrheitswerte. Fremdschlüssel sind einzuschalten (PRAGMA foreign_keys = ON).

1. Der tragende Gedanke

Ein Kontaktwert ist nie ein nackter String. Er ist Wert + Beleg + Datum + Methode. Deshalb zerfällt das Modell in drei Schichten:

• Soll-Seite (bundesland, gebiet) — wer müsste existieren. Amtlich, deterministisch.
• Ist-Seite (stelle, zustaendigkeit, person, kontakt) — was gefunden wurde.
• Beleg-Seite (beleg, nachweis, pruefung) — woher es stammt und wie alt es ist.

Dazu die Betriebs-Schicht: arbeitspaket (Fortschritt), sperre (Datenschutz), und die Kampagnen-Tabellen.

Nichts wird überschrieben. Eine erneute Bestätigung erzeugt eine neue nachweis- bzw. pruefung-Zeile. Die Historie ist der Beweis der Aktualität.

2. Schema

2.1 Soll-Seite

CREATE TABLE bundesland (
  code                TEXT PRIMARY KEY,        -- 'SL','BY','NW' …
  name                TEXT NOT NULL,
  ags_praefix         TEXT NOT NULL,           -- '10','09','05'
  ag_btog_titel       TEXT,                    -- Landesausführungsgesetz zum BtOG
  ag_btog_url         TEXT,
  ag_btog_beleg_id    INTEGER REFERENCES beleg(id),
  traegerschaft_regel TEXT,                    -- Klartext: wer ist örtl. Betreuungsbehörde
  sonderfaelle        TEXT,                    -- Klartext: Abweichungen, Delegationen
  status              TEXT NOT NULL DEFAULT 'offen'
                        CHECK(status IN ('offen','geklaert'))
);

CREATE TABLE gebiet (
  ags                 TEXT PRIMARY KEY,        -- amtl. Gemeindeschlüssel, Kreisebene
  name                TEXT NOT NULL,
  typ                 TEXT NOT NULL
                        CHECK(typ IN ('landkreis','kreisfreie_stadt','stadtkreis',
                                      'regionalverband','region','staedteregion',
                                      'bezirk','kreisangehoerige_stadt','sonstige')),
  bundesland_code     TEXT NOT NULL REFERENCES bundesland(code),
  regierungsbezirk    TEXT,
  einwohner           INTEGER,
  soll_eigene_stelle  INTEGER NOT NULL DEFAULT 1,  -- 0 = keine eigene Betreuungsbehörde
  soll_begruendung    TEXT,                        -- Pflicht, wenn soll_eigene_stelle = 0
  quelle_beleg_id     INTEGER NOT NULL REFERENCES beleg(id)
);

soll_eigene_stelle = 0 braucht immer eine Begründung mit Beleg (z. B. „nach § x AG-BtOG nimmt der Kreis die Aufgabe für die kreisangehörigen Städte wahr"). Sonst entsteht wieder eine unerklärte Lücke.

2.2 Ist-Seite

CREATE TABLE stelle (
  id                  INTEGER PRIMARY KEY,
  name                TEXT NOT NULL,           -- amtliche Bezeichnung wie auf der Website
  traeger_ags         TEXT NOT NULL REFERENCES gebiet(ags),
  traeger_name        TEXT,                    -- Anzeigename des Trägers (Export Spalte 2)
  uebergeordnet       TEXT,                    -- z. B. "Amt für Soziales und Senioren"
  strasse             TEXT,
  hausnummer          TEXT,
  plz                 TEXT,
  ort                 TEXT,
  postfach            TEXT,
  postfach_plz        TEXT,
  website_url         TEXT,                    -- Startseite des Trägers (Export Spalte 10)
  stellen_url         TEXT,                    -- Seite der Betreuungsstelle selbst
  status              TEXT NOT NULL DEFAULT 'offen'
                        CHECK(status IN ('offen','in_arbeit','verifiziert',
                                         'nicht_auffindbar','aufgeloest','zusammengelegt')),
  status_notiz        TEXT,                    -- Pflicht bei allen Status außer verifiziert
  nachfolger_id       INTEGER REFERENCES stelle(id),
  erfasst_am          TEXT,
  zuletzt_geprueft_am TEXT
);

-- Eine Stelle kann für mehrere Gebiete zuständig sein (Rhein-Erft-Fall),
-- ein Gebiet kann in Ausnahmefällen von mehreren Stellen bedient werden.
CREATE TABLE zustaendigkeit (
  stelle_id   INTEGER NOT NULL REFERENCES stelle(id),
  gebiet_ags  TEXT    NOT NULL REFERENCES gebiet(ags),
  art         TEXT    NOT NULL DEFAULT 'originaer'
                CHECK(art IN ('originaer','delegiert','mitbetreut')),
  beleg_id    INTEGER NOT NULL REFERENCES beleg(id),
  PRIMARY KEY (stelle_id, gebiet_ags)
);

CREATE TABLE person (
  id            INTEGER PRIMARY KEY,
  stelle_id     INTEGER NOT NULL REFERENCES stelle(id),
  anrede        TEXT CHECK(anrede IN ('Frau','Herr','')),  -- leer = unbekannt/divers
  titel         TEXT,                                      -- 'Dr.', 'Prof. Dr.'
  vorname       TEXT,
  nachname      TEXT,
  funktion      TEXT,                                      -- 'Leitung der Betreuungsstelle'
  ist_leitung   INTEGER NOT NULL DEFAULT 0,
  gesperrt      INTEGER NOT NULL DEFAULT 0,                -- Widerspruch nach Art. 21 DSGVO
  loeschen_nach TEXT                                       -- Datenschutz, s. DATENSCHUTZ.md
);

CREATE TABLE kontakt (
  id            INTEGER PRIMARY KEY,
  stelle_id     INTEGER NOT NULL REFERENCES stelle(id),
  person_id     INTEGER REFERENCES person(id),             -- NULL = Funktionskontakt
  art           TEXT NOT NULL
                  CHECK(art IN ('email','telefon','fax','web','kontaktformular','de_mail')),
  wert          TEXT NOT NULL,                             -- exakt wie in der Quelle
  wert_norm     TEXT NOT NULL,                             -- Mail klein; Telefon +49…
  rolle         TEXT NOT NULL
                  CHECK(rolle IN ('funktionspostfach_betreuungsstelle','sekretariat',
                                  'leitung','person','zentrale','poststelle','unbekannt')),
  rang          INTEGER NOT NULL,                          -- aus rolle abgeleitet, s. u.
  gefunden_als  TEXT CHECK(gefunden_als IN ('mailto_link','tel_link','sichtbarer_text',
                                            'pdf','portal_datensatz','telefonisch',
                                            'ruecklauf','manuell')),
  ist_aktiv     INTEGER NOT NULL DEFAULT 1,
  notiz         TEXT,
  UNIQUE(stelle_id, art, wert_norm)
);

Rang-Ableitung

Das ist Thomas' Prioritätsregel, hart kodiert:

Der Hauptkontakt einer Stelle ist der aktive Kontakt mit dem niedrigsten Rang. Bei Gleichstand: der mit dem jüngsten bestätigten Nachweis.

2.3 Beleg-Seite

CREATE TABLE beleg (
  id            INTEGER PRIMARY KEY,
  url           TEXT,
  quellklasse   TEXT NOT NULL
                  CHECK(quellklasse IN ('offizielle_website','amtliches_portal',
                                        'amtliche_datei','verbandsliste','sonstige_web',
                                        'telefonat','mail_antwort','ruecklauf','manuell')),
  titel         TEXT,
  abgerufen_am  TEXT NOT NULL,
  http_status   INTEGER,
  content_hash  TEXT,                       -- SHA-256 des Rohinhalts
  archiv_pfad   TEXT,                       -- relativ zu archiv\
  notiz         TEXT
);

-- Feingranulare Herkunft: je Feld eines Objekts ein Nachweis. Das ist die
-- "filigrane Aktualität", die Thomas verlangt hat.
CREATE TABLE nachweis (
  id              INTEGER PRIMARY KEY,
  entitaet        TEXT NOT NULL
                    CHECK(entitaet IN ('stelle','kontakt','person','zustaendigkeit','gebiet')),
  entitaet_id     TEXT NOT NULL,
  feld            TEXT NOT NULL,            -- 'wert','strasse','plz','name' …
  wert            TEXT NOT NULL,
  beleg_id        INTEGER NOT NULL REFERENCES beleg(id),
  festgestellt_am TEXT NOT NULL,
  methode         TEXT NOT NULL
                    CHECK(methode IN ('mailto_extrakt','tel_extrakt','muster_extrakt',
                                      'agent_gelesen','portal_import','telefonisch',
                                      'ruecklauf','manuell')),
  konfidenz       TEXT NOT NULL CHECK(konfidenz IN ('hoch','mittel','niedrig')),
  fundstelle      TEXT                      -- Textumfeld aus dem Beleg (Zitat)
);

CREATE TABLE pruefung (
  id             INTEGER PRIMARY KEY,
  nachweis_id    INTEGER NOT NULL REFERENCES nachweis(id),
  geprueft_am    TEXT NOT NULL,
  ergebnis       TEXT NOT NULL
                   CHECK(ergebnis IN ('bestaetigt','abweichung','quelle_weg','unklar')),
  abweichung_text TEXT,
  neuer_beleg_id INTEGER REFERENCES beleg(id)
);

2.4 Betrieb

-- Ein Paket = ein Gebiet. Feinste sinnvolle Granularität; ein Abbruch kostet
-- damit höchstens einen Kreis.
CREATE TABLE arbeitspaket (
  id             INTEGER PRIMARY KEY,
  runde          TEXT NOT NULL
                   CHECK(runde IN ('erstrecherche','gegenpruefung','nachpruefung','nachfassen')),
  gebiet_ags     TEXT REFERENCES gebiet(ags),
  bundesland_code TEXT REFERENCES bundesland(code),
  status         TEXT NOT NULL DEFAULT 'offen'
                   CHECK(status IN ('offen','in_arbeit','fertig','blockiert')),
  zugewiesen_an  TEXT,                      -- freier Bezeichner des Laufs/Unteragenten
  begonnen_am    TEXT,
  beendet_am     TEXT,
  versuche       INTEGER NOT NULL DEFAULT 0,
  notiz          TEXT,
  UNIQUE(runde, gebiet_ags)
);

CREATE TABLE lauf (
  id          INTEGER PRIMARY KEY,
  gestartet_am TEXT NOT NULL,
  beendet_am  TEXT,
  runde       TEXT,
  umfang      TEXT,                          -- 'SL', 'alle', …
  bericht     TEXT
);

-- Widerspruch nach Art. 21 DSGVO. Greift auf dem Wert-Hash, nicht auf der Zeilen-ID,
-- damit ein späterer Rechercheslauf den Kontakt nicht wieder einträgt.
CREATE TABLE sperre (
  id             INTEGER PRIMARY KEY,
  wert_norm_hash TEXT NOT NULL UNIQUE,       -- SHA-256 des normalisierten Werts
  art            TEXT NOT NULL CHECK(art IN ('email','telefon','person','stelle')),
  klartext_hinweis TEXT,                     -- z. B. "Nachname S., LRA Musterau"
  eingegangen_am TEXT NOT NULL,
  grund          TEXT,
  beleg_id       INTEGER REFERENCES beleg(id)
);

2.5 Kampagne (Gerüst — Ausbau in Stufe 3)

CREATE TABLE kampagne (
  id INTEGER PRIMARY KEY, name TEXT NOT NULL, zweck TEXT,
  absender TEXT, gestartet_am TEXT, beendet_am TEXT
);

CREATE TABLE frage (
  id INTEGER PRIMARY KEY, kampagne_id INTEGER NOT NULL REFERENCES kampagne(id),
  nummer INTEGER NOT NULL, text TEXT NOT NULL,
  typ TEXT CHECK(typ IN ('zahl','text','ja_nein','auswahl'))
);

CREATE TABLE sendung (
  id INTEGER PRIMARY KEY,
  kampagne_id INTEGER NOT NULL REFERENCES kampagne(id),
  stelle_id   INTEGER NOT NULL REFERENCES stelle(id),
  kontakt_id  INTEGER REFERENCES kontakt(id),
  kanal       TEXT CHECK(kanal IN ('email','brief','fax')),
  anrede_text TEXT,
  versendet_am TEXT,
  status      TEXT DEFAULT 'geplant'
                CHECK(status IN ('geplant','versendet','zugestellt','unzustellbar','abgebrochen')),
  bounce_grund TEXT
);

CREATE TABLE antwort (
  id INTEGER PRIMARY KEY, sendung_id INTEGER NOT NULL REFERENCES sendung(id),
  eingegangen_am TEXT NOT NULL, kanal TEXT, freitext TEXT,
  beleg_id INTEGER REFERENCES beleg(id)
);

CREATE TABLE antwort_wert (
  antwort_id INTEGER NOT NULL REFERENCES antwort(id),
  frage_id   INTEGER NOT NULL REFERENCES frage(id),
  wert       TEXT,
  PRIMARY KEY (antwort_id, frage_id)
);

Ein Rückläufer ist zugleich ein erstklassiger Beleg: Die Stelle bestätigt ihre Daten selbst. Deshalb hängt an antwort ein beleg_id.

3. Qualitätsstufen

Wird berechnet, nie eingetragen. Grundlage: jüngster bestätigter Nachweis eines Werts.

Vor jedem Versand wird gefiltert (Vorgabe: nur A und B; C nur nach Nachprüfung).

Als Views bereitzustellen:

• v_kontakt_qualitaet — je Kontakt: Stufe, letztes Bestätigungsdatum, Beleg-URL
• v_stelle_hauptkontakt — je Stelle der Kontakt mit niedrigstem Rang je Art
• v_abdeckung — je Bundesland: Soll-Gebiete, gefundene Stellen, Stufenverteilung
• v_export_basis — je Stelle eine Zeile mit allen Feldern des Katalogs aus Abschnitt 4a; der Export setzt daraus nach Profil die Spalten zusammen

4. Excel-Export — Thomas' Wunschlayout

Blatt 1 „Betreuungsstellen", feste Spalten:

Zwei bewusste Abweichungen von Thomas' Urfassung — bitte prüfen:

1. Er schrieb „Link in Spalte 10" und „weitere Namen ab Spalte 10". Das widerspricht sich. Gelöst: Link bleibt Spalte 10, die variablen Paare beginnen hinten. Grund: Sonst verschieben sich bei einer Stelle mit zwölf Kontakten alle festen Spalten, und der Serienbrief bricht.
2. PLZ und Ort stehen in der Datenbank getrennt (sauber sortier- und filterbar), im Export wie gewünscht zusammengefasst in Spalte 4. Ein zusätzliches Blatt „Rohdaten" liefert sie auch getrennt.

Blatt 2 „Offen und Prüfen" — alle Gebiete ohne verifizierte Stelle, alle Stufen D und X, mit Notiz, was versucht wurde. Das ist Thomas' politische Arbeitsliste.

Blatt 3 „Quellen" — alle Belege mit URL, Klasse, Abrufdatum, Archivpfad.

Blatt 4 „Abdeckung" — je Bundesland Soll/Ist und Stufenverteilung.

Exportprofile:

• versand — mit Personennamen und Anrede (interner Gebrauch, Serienbrief)
• veroeffentlichung — ohne Personennamen, nur Funktionskontakte und Kennzahlen (für politische Auswertung, → DATENSCHUTZ.md)
• roh — alle Felder einzeln, für eigene Auswertungen

4a. Das Exportlayout ist von Thomas selbst änderbar — in der Weboberfläche

Das Layout aus Abschnitt 4 ist der Auslieferungszustand, keine Festverdrahtung. Thomas muss Spalten umbenennen, umsortieren, entfernen und hinzufügen können — ohne Agent, ohne Programmierer, ohne Konfigurationsdatei.

Ort dafür ist die Weboberfläche (→ AUFTRAG-01-SYSTEM-CC.md), Seite „Export". Die Profile liegen in der Datenbank, nicht in einer Datei:

CREATE TABLE exportprofil (
  name          TEXT PRIMARY KEY,          -- 'versand','veroeffentlichung','roh', eigene
  beschreibung  TEXT,
  geschuetzt    INTEGER NOT NULL DEFAULT 0, -- 1 = Datenschutzregel greift (s. u.)
  min_qualitaet TEXT DEFAULT 'B'  CHECK(min_qualitaet IN ('A','B','C','D','X')),
  nur_status    TEXT,                      -- leer = alle
  bundeslaender TEXT,                      -- leer = alle, sonst 'SL,SH,BY'
  sortierung    TEXT DEFAULT 'gebiet.bundesland_code, gebiet.name',
  blatt_offen   INTEGER NOT NULL DEFAULT 1,
  blatt_quellen INTEGER NOT NULL DEFAULT 1,
  blatt_abdeckung INTEGER NOT NULL DEFAULT 1,
  geaendert_am  TEXT
);

CREATE TABLE exportspalte (
  id           INTEGER PRIMARY KEY,
  profil       TEXT NOT NULL REFERENCES exportprofil(name) ON DELETE CASCADE,
  position     INTEGER NOT NULL,
  ueberschrift TEXT NOT NULL,
  feld         TEXT NOT NULL,              -- Feldname aus dem Katalog unten
  breite       INTEGER,
  notiz        TEXT,                       -- Thomas' eigene Anmerkung, wird ignoriert
  UNIQUE(profil, position)
);

In der Oberfläche heißt das: Spalten per Ziehen umsortieren, Überschrift direkt überschreiben, Spalte über ein Auswahlfeld aus dem Feldkatalog hinzufügen, Zeile löschen. Eine Vorschau zeigt jederzeit die ersten Zeilen mit echten Daten. „Als neues Profil speichern" legt ein eigenes Profil an. bs export --profil <name> nutzt dieselben Profile — Oberfläche und Kommandozeile teilen sich die Definition.

Verfügbare Felder (Feldkatalog)

Regeln, die das Programm durchsetzt

1. Nur Felder aus dem Katalog. Die Oberfläche bietet ausschließlich gültige Felder zur Auswahl an; ein unbekanntes Feld kann gar nicht erst entstehen. Kommt es dennoch vor (Profil aus einer älteren Fassung), bricht der Export mit Klartext ab und nennt Profil, Position und die gültigen Alternativen — nie eine stillschweigend leere Spalte.
2. Datenschutz sticht Layout: In Profilen mit geschuetzt = 1 (Auslieferungszustand: veroeffentlichung) sind personenbezogene Felder nicht wählbar; der Export lehnt sie ab, mit Verweis auf DATENSCHUTZ.md Abschnitt 6. Das Kennzeichen geschuetzt kann in der Oberfläche nicht entfernt werden, damit die Regel nicht versehentlich fällt.
3. Die Sperrliste gilt immer, unabhängig vom Layout und Profil.
4. Die Auslieferungsprofile versand, veroeffentlichung und roh lassen sich jederzeit auf den Auslieferungszustand zurücksetzen (Schaltfläche in der Oberfläche bzw. bs profil-zuruecksetzen --profil X). Thomas kann nichts kaputtmachen, was nicht in zehn Sekunden zurückzuholen wäre.
5. Eigene Profile lassen sich anlegen, umbenennen und löschen; die drei Auslieferungsprofile nur zurücksetzen, nicht löschen.

Der Export bleibt eine Einbahnstraße. Thomas ändert in der Oberfläche das Layout und — an anderer Stelle, mit Quellenangabe — auch Daten. Was er nie tut: Werte in der erzeugten Excel-Datei korrigieren. Die Datei ist ein Erzeugnis, kein Speicher; Korrekturen dort gehen beim nächsten Export verloren und reißen die Belegkette.

5. Das Eingangstor (erfasse) — Prüfungen

Der Agent schreibt nie per SQL. Jeder Fund läuft über einen Befehl, der prüft:

1. Beleg vorhanden? Kein beleg_id → Ablehnung.
2. Wortlaut-Regel: Bei Web-Quellklassen muss der Wert wörtlich im archivierten Inhalt vorkommen (bei Telefonnummern nach Entfernen von Leer-, Punkt-, Klammer- und Bindestrichzeichen auf beiden Seiten). Sonst Ablehnung mit Begründung. Ausnahme nur für telefonat, mail_antwort, ruecklauf, manuell.
3. Domain-Plausibilität: Weicht die Domain einer E-Mail von der Domain der amtlichen Website ab → Warnung, Konfidenz wird auf mittel gesetzt, Wert wird gespeichert. (Kein Ablehnungsgrund: Viele Kreise nutzen abweichende Maildomains.)
4. Sperrliste: Steht der Wert-Hash in sperre → Ablehnung, unabhängig von allem anderen.
5. Dublette: Gleicher wert_norm bei derselben Stelle → kein neuer Kontakt, sondern ein zusätzlicher nachweis auf dem bestehenden. Das ist der Normalfall bei Nachprüfungen und erhöht die Qualitätsstufe.

Jede Ablehnung wird dem Agenten im Klartext gemeldet, damit er nachbessern kann — und nicht etwa umgeht.