# 03 — Kern-Datenmodell SQLAlchemy 2.0, dialektneutral (SQLite-Default, Postgres vorgesehen, aber ungetestet — siehe D-02 in [00-DECISIONS](00-DECISIONS.md)). Konventionen: - PK: `id` als UUIDv7 (RFC 9562) in `String(36)` (zeitlich sortierbar, dialektneutral, in Logs lesbar). IDs aus Versionen bis 0.9.0 haben 40 Zeichen (12 Zeichen Zeit vorne) und bleiben in SQLite gültig. - Zeitstempel: `DateTime(timezone=True)`, UTC, in Python gesetzt. - Strukturierte Felder: `sa.JSON` (nicht `JSONB`). - Listen: Assoziationstabelle, nie Array-Spalte. - Soft-Delete nur da, wo es fachlich nötig ist (`revoked_at`, `disabled_at`). - Extension-Tabellen: Präfix `ext__`, eigener Alembic-Branch, hier nicht beschrieben. --- ## 1. Identität und Zugriff ### `users` | Spalte | Typ | Anmerkung | |---|---|---| | id | str(36) PK | | | username | str(64) unique | Login | | email | str(255) null | optional, eine Angabe zum Konto; Nodvard Deck verschickt keine E-Mails | | display_name | str(128) | | | password_hash | str(255) | Argon2id | | totp_secret_id | FK secrets.id null | 2FA-Secret liegt im Vault, nicht hier | | is_active | bool | | | is_owner | bool | genau einer; umgeht RBAC, kann nicht entzogen werden | | locale | str(8) | `de` default — UI-Sprache ist Nutzereinstellung | | created_at, last_login_at | ts | | ### `roles`, `user_roles`, `role_permissions` `roles(id, name unique, description, is_builtin)`. Eingebaut: `owner`, `admin`, `operator`, `viewer`. Berechtigungen sind **Strings**, keine Tabelle — Extensions bringen eigene mit, ohne Migration: ``` .[:] hosts.read hosts.write hosts.execute secrets.read:ssh-* secrets.write audit.read settings.write extensions.manage system.read apps.write actions.approve actions.approve:high soc.read soc.manage (Nodvard Shield) inventory.read inventory.write (Inventar) ``` `*` als Suffix-Wildcard im Scope ist erlaubt (`secrets.read:ssh-*`), sonst exakter Vergleich. Auswertung in `core/rbac.py`, reine Funktionen, ohne DB testbar. ### `refresh_tokens` | Spalte | Anmerkung | |---|---| | id, user_id | | | token_hash | SHA-256 des opaken Werts; der Wert selbst wird nie gespeichert | | client_type | `web` \| `android` \| `cli` — steuert Cookie vs. Bearer | | device_name, user_agent, ip | für „Aktive Sitzungen" in den Einstellungen | | expires_at, revoked_at, last_used_at | | | replaced_by_id | Nachfolger-Sitzung, nur bei Rotation gesetzt (nicht bei Logout/Passwortwechsel), ohne Fremdschlüssel. Grundlage der 30-s-Gnadenfrist beim Erneuern: ein eben rotierter Token bekommt noch einen Access-Token der Nachfolger-Sitzung, aber keinen neuen Refresh-Token (zwei Tabs, App + Tab). Trägt außerdem die Prüfung der `sid` im Zugangs-Token (gilt, solange am Ende der Kette eine gültige Zeile steht) und das Abmelden der ganzen Kette (`logout` widerruft die Zeile am Ende, auch mit einem schon erneuerten Token, solange dessen Zeile noch steht, also bis eine Refresh-Laufzeit nach dem Erneuern, siehe „Aufräumen“). Gelesen wird die Kette in einer einzigen rekursiven Abfrage (`WITH RECURSIVE`, `services.auth.session_chain_end`), Grenzen in [04 §2](04-API.md#2-ohne-authentifizierung) „Access-Token und Anmeldung“ | **Aufräumen** (`services.auth.purge_old_logins`, Kern-Job `logins-retention`, nachts 03:50): Zeilen, deren `expires_at` oder `revoked_at` länger als `refresh_token_ttl_days` (Vorgabe 30) zurückliegt, werden gelöscht. Bis dahin bleiben auch erneuerte und beendete Zeilen stehen, denn die Kette über `replaced_by_id` braucht sie (Gnadenfrist, `sid` offener Terminal- und Live-Verbindungen). Ein Zugangs-Token zeigt nur während seiner Laufzeit (Vorgabe 15 Minuten) auf eine Zeile, eine Zeile geht erst eine Refresh-Laufzeit nach ihrem Ende. Weil die Erneuerungen einer Kette der Reihe nach enden, geht immer nur ihr ältester Teil; von jeder übrigen Zeile führt die Kette weiter bis zur laufenden Anmeldung (Abmelden, Prüfung offener Anmeldungen). Ohne den Job bliebe jede Erneuerung samt Adresse und Browser für immer liegen. ### `api_tokens` Für Automatisierung und die Android-App im Langzeitbetrieb. `id, user_id, name, token_hash, scopes (JSON), expires_at, revoked_at, last_used_at`. Auch Aufrufe per Cron brauchen damit ein Token; ungeschützte Endpunkte für Automatisierung gibt es nicht. --- ## 2. Hosts Der Kern besitzt die Host-Identität — siehe [01 §5](01-ARCHITECTURE.md#5-hosts-gehören-dem-kern-nicht-der-extension). ### `hosts` | Spalte | Anmerkung | |---|---| | id | | | name | str(64) unique, technisch (`pve1`, `bastel-pi`) | | display_name | str(128) | | address | str(255) — IP oder DNS | | os_family | `linux` \| `windows` \| `other` | | kind | frei, von der Extension gesetzt (`vm`, `container`, `bare-metal`) | | provider_ext_id | null bei manuell angelegten. Das ist auch das Merkmal „kein Modul pflegt den Zustand“: nur solche Server prüft der Kern-Job `host-reachability` | | provider_ref | Fremdschlüssel beim Anbieter (z. B. `node/qemu/110`) | | is_managed | ob Aktionen erlaubt sind | | enabled | | | last_seen_at | letzte erfolgreiche Prüfung (Modul, „Verbindung prüfen“ oder `host-reachability`); leer = noch nie gesehen, dann gibt es beim ersten Ausfall keine Meldung | | status | `up` \| `down` \| `unknown` \| `maintenance`. Von Hand angelegte Server führt `host-reachability` nach (TCP zum SSH-Port, „down“ erst nach 2 Fehlschlägen hintereinander); `maintenance` fasst der Job nicht an. Eingelesene Server führt ihr Modul nach, über `ctx.hosts.set_status` (Proxmox: der einzige Knoten einer Verbindung, die mindestens 4 Minuten am Stück bei keinem Abgleich antwortet, `down`; Knoten eines Clusters oder einer Verbindung, die nur ablehnt, Gäste, deren Liste nicht lesbar war, und Server deaktivierter Verbindungen `unknown`) | | metadata | JSON, anbieterspezifisch. Ausnahme ist der Schlüssel `login_ok`, er gehört dem Kern: der Beleg einer erfolgreichen SSH-Anmeldung mit dem Standard-Zugang (`credential_id`, `address`, `port`, `at`). Daraus entsteht `login_ok_at` in der API, solange Zugang, Adresse und Port noch passen. Der Abgleich eines Anbieters ersetzt den Rest und behält ihn | | created_at, updated_at | | `host_tags(host_id, tag)` — Tags sind das generische Ersatzkonstrukt für fest verdrahtete Sonderlisten (etwa „alle Docker-Hosts“ oder „kritische Container“). Eine Extension fragt „alle Hosts mit Tag `docker`" statt eine eigene Liste zu pflegen. `host_groups(id, name, description)` + `host_group_members(group_id, host_id)` — Zielgruppen für Skripte (Script-Repository, siehe [02 §8](02-EXTENSION-API.md#8-wie-drei-module-gegen-die-schnittstelle-implementiert-werden)). ### `host_credentials` | Spalte | Anmerkung | |---|---| | id, host_id | | | kind | `ssh_key` \| `ssh_password` \| `api_token` | | username | | | port | | | secret_id | FK `secrets.id` — der Wert liegt ausschließlich im Vault | | is_default | genau eine pro Host | ### `known_hosts` `(host_id, key_type, fingerprint, first_seen_at, accepted_by_user_id)` — ersetzt `StrictHostKeyChecking=no`. Abweichung ⇒ Verbindung scheitert sichtbar. Ein unbekannter Schlüssel wird beim ersten Verbinden nur dann automatisch gemerkt, wenn keine Bestätigung verlangt ist (§9 `ssh.confirm_new_host_keys`, `ssh.host_key_confirm_required`); sonst erst über `POST /hosts/{id}/known-hosts`. --- ## 3. Secrets-Vault ### `secrets` | Spalte | Anmerkung | |---|---| | id | | | label | str(128) unique — der Name, den Extensions referenzieren | | kind | `password` \| `ssh_private_key` \| `api_token` \| `totp` \| `generic` | | ciphertext | LargeBinary, Fernet | | key_version | int — ermöglicht Rotation ohne Big-Bang | | owner_ext_id | null = gehört dem Kern/Admin | | description | | | created_by_user_id, created_at, rotated_at, last_used_at | | | expires_at | Ablaufdatum, nur als Angabe: danach wird nichts gesperrt. Daran erinnert der Kern-Job `secret-expiry-reminder` | | metadata | JSON, **nie sensibel** (z. B. Public-Key-Fingerprint). Unter `expiry_notice` `{for, stage}` steht, welche Erinnerung für welches Ablaufdatum schon verschickt ist | `secret_grants(secret_id, grantee_type ext|role, grantee_id)` — wer darf lesen. `secret_usages(secret_id, consumer_type, consumer_id, first_used_at, last_used_at)` — je Verwender eine Zeile, geschrieben von `vault_use`. Feste Bindungen (Besitzer-Modul, Server-Zugang, Zwei-Faktor) stehen nicht dort, sie werden beim Lesen abgeleitet (`used_by` in `GET /secrets`, nur die festen Bindungen zusätzlich in `bound`). **Invarianten, die im Code erzwungen werden:** 1. Kein API-Endpunkt gibt `ciphertext` oder Klartext zurück. Nie. Auch nicht für `owner`. Schreiben und Ersetzen ja, Lesen nein. 2. Extensions bekommen ein `SecretHandle`; der Klartext existiert nur innerhalb eines `async with ctx.vault_use(handle) as value:`-Blocks. 3. Jede Materialisierung wird festgehalten (Label und Verwender, ohne Wert), gebündelt: höchstens eine Audit-Zeile `secret.used` je Stunde, Geheimnis und Verwender. Wann ein Verwender ein Geheimnis zuletzt benutzt hat, steht zusätzlich in `secret_usages` und in `secrets.last_used_at`. 4. Der Master-Key liegt nie in der DB. 5. `PUT /secrets/{id}/value` und `DELETE /secrets/{id}` lehnen das Secret hinter `users.totp_secret_id` (Zwei-Faktor-Schlüssel eines Kontos) mit `409` ab: Ersetzen oder Löschen geht dort nicht. Abschalten geht nur über Mein Konto, „Zwei-Faktor zurücksetzen“ oder den Notfall-Befehl. --- ## 4. Audit-Log ### `audit_log` — append-only | Spalte | Anmerkung | |---|---| | id | | | ts | indiziert | | actor_type | `user` \| `extension` \| `ai` \| `scheduler` \| `system` \| `anonymous` (nicht angemeldet) | | actor_id | User-ID, Extension-ID oder Modellname; bei `login.failed`/`login.locked` mit unbekanntem Benutzernamen (eine zu lange Eingabe zählt genauso) `unbekannt` mit `actor_type` `anonymous`, nie der getippte Text | | action | `host.restart`, `secret.used`, `action.denied`, `login.failed` … | | target_type, target_id | | | outcome | `success` \| `failure` \| `denied` \| `proposed` | | reason | **Text** — hier landet `BEGRUENDUNG:` | | detail | JSON — Befehl, Exit-Code, Gate-Entscheidung; bei `action.executed` von Ausgabe und Fehlertext nur die Länge, nie Text vom Server (ältere Einträge können noch Ausgabe enthalten); bei `login.failed`/`login.locked` mit unbekanntem Benutzernamen statt des Texts nur `username_ref` (12 Hex-Zeichen, HMAC-SHA256 der Eingabe mit dem JWT-Geheimnis der Installation; gleiche Eingabe, gleiche Kennung) und `username_length` | | correlation_id | verbindet Incident → Vorschlag → Ausführung → Benachrichtigung | | ip, user_agent | | Kein `UPDATE`, kein `DELETE` aus der Anwendung; nur ein Retention-Job löscht jenseits von `audit.retention_days`. Die Trennung `outcome=proposed` von `success` ist der Unterschied zwischen „die KI wollte" und „die KI tat" — ohne sie lässt sich im Nachhinein nicht sagen, was wirklich ausgeführt wurde. Zu lange Werte in `actor_type`, `actor_id`, `action`, `target_type`, `target_id`, `ip` und `user_agent` werden beim Schreiben auf die Spaltenlänge gekürzt (letztes Zeichen „…“); `reason` und `detail` werden nicht gekürzt. Ältere Versionen schrieben bei einer Anmeldung mit unbekanntem Namen den eingetippten Text oder eine Kennung `unbekannt:…` als `actor_id` ins Protokoll, bei `login.locked` auch in `detail.username`. Die Migration `f3a9c6d18e24` bereinigt diese Einträge einmalig: Akteur `anonymous`/`unbekannt`, `detail.username` fällt weg (unter SQLite mit `PRAGMA secure_delete`). Ein `login.locked` eines inzwischen gelöschten Kontos wird genauso behandelt. Das lässt sich nicht rückgängig machen. Die Kopien unter `backups/vor-update/` und ältere Sicherungen enthalten die Namen noch, bis sie ersetzt oder gelöscht sind. --- ## 5. Aktionen — das Gate-Journal ### `actions` | Spalte | Anmerkung | |---|---| | id | | | ext_id | wer vorschlägt | | action_type | `shell.exec`, `docker.restart`, `vm.start`, `script.run`, `file.delete` | | host_id | null bei nicht host-gebundenen Aktionen | | payload | JSON | | risk | `low` \| `medium` \| `high` \| `critical` | | status | `proposed` → `approved` → `executing` → `succeeded`/`failed`, oder `denied`/`expired`/`dismissed` | | proposed_by_type / _id | `ai` + Modell, `user`, `scheduler`, `event` | | reason | Pflichtfeld, NOT NULL | | gate_decision | JSON: welche Regel griff, welche Muster trafen | | approved_by_user_id, approved_at | | | executed_at, finished_at | | | result | JSON: exit_code, stdout/stderr (gekürzt), Dauer | | correlation_id | | | idempotency_key | verhindert Doppelausführung bei Retry/Doppelklick | | expires_at | unbestätigte Vorschläge verfallen (Default 24 h) | Diese Tabelle trennt Vorschlagen und Ausführen (siehe [01 §4](01-ARCHITECTURE.md#4-das-aktions-gate)). Die Bestätigungs-Karte in der UI ist eine Zeile mit `status=proposed`; „volle Autonomie" heißt, dass das Gate direkt auf `approved` setzt — derselbe Datensatz, dieselbe Nachvollziehbarkeit, nur ohne Klick. Ebenso bei einer gültigen Dauerfreigabe ([01 §4](01-ARCHITECTURE.md#4-das-aktions-gate)): `gate_decision` ist dann `{"rule": "standing_approval", "standing_approval": {granted_by, granted_at, label}}`, und `approved_by_user_id` ist die Person, die sie erteilt hat. Erkennt das Gate eine Dauerfreigabe nicht an, steht sie mit `problem` (und ggf. der fehlenden `permission`) unter `gate_decision.standing_approval_rejected`. `action_flap_history(host_id, fingerprint, ts, blocked)` — generalisiertes Anti-Flapping über einen Hash aus (host, action_type, normalisiertem Payload), nicht nur über Docker-Restarts. Statt fester Grenzwerte in einem einzelnen Watcher gilt es für **alle** Extensions, weil es im Kern sitzt. --- ## 6. Extension-Registry ### `extensions` | Spalte | Anmerkung | |---|---| | id | = Manifest-ID, PK. Nach einer Umbenennung (`legacy_ids`, [02 §1](02-EXTENSION-API.md#erweiterung-umbenennen-legacy_ids)) bleibt die Zeile unter der alten Kennung (**Speicher-Kennung**), z. B. `nexus-soc` für Nodvard Shield auf Installationen von vor 0.7; neue Installationen legen die Zeile mit der neuen Kennung an | | version, api_version | | | state | `enabled` \| `disabled` \| `error` \| `incompatible` \| `uninstalling` | | source | `bundled` \| `local` \| `pip` \| `marketplace` | | manifest | JSON, wie eingelesen | | granted_permissions | JSON — was der Admin bestätigt hat, kann weniger sein als beantragt | | settings | JSON, gegen das deklarierte Schema validiert | | last_error, last_error_at | | | installed_at, updated_at | | ### `connector_instances` `id, ext_id, type_id, name, config (JSON, Secrets als `{"$secret": "