Datenbank — SQLite Schema

Allgemeine Informationen

• Datei: data/citadel.db
• Modus: WAL (Write-Ahead Logging) — paralleles Lesen
• Foreign Keys: aktiviert
• Busy-Timeout: 5000ms
• Cache: 64MB
• Migrationen: shared/migrations/001-044

ER-Diagramm (Phase 65)

erDiagram
    users ||--o{ projects : owns
    users ||--|| account_settings : has
    users ||--o{ recovery_keys : holds
    users ||--o{ subscriptions : pays
    users ||--o{ invites : creates
    users ||--o{ chat_messages : sends
    users ||--o{ trial_credits : claims
    users ||--o{ activity_log : generates

    projects ||--o{ project_phases : tracks
    projects ||--o{ pinned_notes : pins
    projects ||--o{ chat_messages : contains
    projects ||--o{ timeline_events : records
    projects ||--o{ github_links : links
    projects ||--o{ skill_benchmarks : runs
    projects ||--o{ marketplace_cache : caches

    github_links ||--o{ github_events : receives

    invites }o--|| users : "consumed by"
    subscriptions ||--o{ stripe_events : "logs idempotency"

    users {
        TEXT id PK
        TEXT email UK
        TEXT password_hash
        TEXT role "admin · user"
        TEXT google_id UK
        TEXT github_id UK
        INTEGER email_verified
        INTEGER two_factor_enabled
        TEXT last_login
    }

    projects {
        INTEGER id PK
        TEXT technical_name
        TEXT owner_id FK
        TEXT display_name
        TEXT description
        TEXT color
        TEXT icon
        INTEGER is_archived
    }

    chat_messages {
        INTEGER id PK
        TEXT project_name
        TEXT worker_id
        TEXT role "user · assistant · system"
        TEXT content "AES-256-GCM if encrypted=1"
        INTEGER encrypted
        TEXT timestamp
    }

    subscriptions {
        TEXT user_id PK
        TEXT plan "free · min · max · beta"
        TEXT stripe_customer_id
        TEXT status
    }

    invites {
        TEXT code PK "arc-XXXX-XXXX"
        TEXT created_by FK
        TEXT used_by FK
        TEXT parent_invite_code
        TEXT status "active · used · revoked"
    }

    recovery_keys {
        INTEGER id PK
        TEXT user_id FK
        TEXT encrypted_key
        TEXT key_hint "first 4 chars"
    }

    trial_credits {
        TEXT email PK
        INTEGER credits_used
        TEXT first_used_at
    }

23 Tabellen über 24 Migrationen. Auth + Projekte + Workspace-Daten + Intelligence + Billing + Beta-Gating + Context-Export-Audit. Alle FK-Constraints werden durchgesetzt.

Tabellen

users — Benutzer

projects — Projekte

UNIQUE(technical_name, owner_id)

account_settings — Konto-Einstellungen

chat_messages — Chat-Verlauf

INDEX(project_name, worker_id, timestamp DESC)

recovery_keys — Wiederherstellungsschlüssel (Phase 45.4)

INDEX(user_id)

github_links — GitHub-Repo-Verknüpfungen (Phase 49.3)

UNIQUE(project_name, owner, repo) — mehrere Repos pro Projekt erlaubt. INDEX(project_name), INDEX(webhook_secret)

github_events — Log der GitHub-Ereignisse (Phase 49.3.1)

INDEX(project_name, created_at DESC), INDEX(link_id)

skills_global — Globale Skills

skills_project_forks — Skill-Forks

UNIQUE(project_name, skill_id)

skill_evolution_logs — Änderungshistorie der Skills

skill_update_requests — Skill-PRs (Sage)

skill_benchmarks — A/B-Tests für Skills

pinned_notes — Angeheftete Notizen

project_phases — Roadmap-Phasen

UNIQUE(project_name, phase_id)

activity_log — Aktivitätsjournal

INDEX(created_at DESC), INDEX(project_name, created_at DESC), INDEX(event_type, created_at DESC), INDEX(actor, created_at DESC)

system_configs — System-Einstellungen

marketplace_analysis_cache — Marketplace-Cache

Cache für die Kompatibilitätsanalyse von Marketplace-Skills.

ephemeral_tokens — Kurzlebige Token (Issue #27)

Persistenter Speicher für One-Time-Token für OAuth-State, Passwort-Reset und E-Mail-Verifizierung. Vor Migration 022 lebten diese Token in einer In-Memory-Map und gingen bei jedem Neustart des Masters verloren, was den Google/GitHub-Login zerstörte (invalid_state).

INDEX(type), INDEX(expires_at). Einmalige Verwendung: consume() liest in einer Transaktion den Payload und löscht die Zeile.

project_issues — Issues pro Projekt (Issue #53, Phase 53.14)

SQLite SSOT für Issues anstelle von projektspezifischen issues/issues.json-Dateien. Die Datei verbleibt auf der Festplatte als Derived Export (Mirror), wird per gitignore ausgeschlossen — die DB ist autoritativ.

PK (project_name, id) — jedes Projekt hat seinen eigenen unabhängigen ID-Raum. INDEX (project_name, status) — häufigster Filter (open only). Operationen befinden sich in issueQueries (shared/db.ts): list/get/nextId/insert/upsert/replaceAll/bulkImport. replaceAll — atomare Transaktion; bulkImport — INSERT OR IGNORE für idempotentes Re-Seeding.

Migration 036 (#187, 2026-05-23): ALTER TABLE project_issues ADD COLUMN assignee TEXT. Neue Statuses (in_progress, blocked, deferred) werden auf App-Ebene validiert (IssueStatus-Union in shared/db.ts), nicht per CHECK-Constraint (SQLite erlaubt kein Hinzufügen von Constraints via ALTER TABLE).

Migration 046 (#327, 2026-06-03): ALTER TABLE project_issues ADD COLUMN created_by TEXT NOT NULL DEFAULT 'legacy'. Lifecycle um den neuen Status draft erweitert. handleCreateIssue erzwingt status='draft' wenn assignee=null (sonst direkt 'open'), verlangt worker_id (Body oder Auth-Fallback). handleUpdateIssue macht Auto-Promote draft → open bei der ersten Assignee-Zuweisung und lehnt close für never-assigned Issues ab (FR-ISS-101/102/103). Bereits vorhandene Zeilen werden per DEFAULT als 'legacy' backfilled.

onboarding_progress — Post-Wizard-Checklisten-Status (Issue #56, Phase 54.1)

Benutzerspezifischer Derived Cache über den activity_log-Eventstream (SSOT). Die UI liest mit einer einzelnen Abfrage, anstatt über Ereignisse zu aggregieren. Replaybar: Dismiss/Replay ändert nicht den state, nur dismissed_at.

INDEX auf completed_at für Funnel-Analytik (#61 Phase 54.6). Operationen befinden sich in onboardingQueries (shared/db.ts): getProgress/recordEvent/dismiss/replay. recordEvent ist idempotent auf (chat_id, step, status) — ein wiederholter identischer Aufruf gibt changed=false zurück und schreibt nicht in activity_log. Whitelist: 5 Schritte × 2 Statuses (completed, skipped). skipped inkrementiert completed_count NICHT. Der Übergang skipped → completed erhöht den Zähler. Alle Mutationen emittieren Ereignisse in activity_log mit event_type LIKE 'onboarding_%' — echter SSOT für Funnel-Metriken; Tabellenspalten sind Derived Cache.

platform_audit_log — Super-Admin-Protokoll für Secret-Rotation (Phase 57, Sentinel #103)

Append-only-Journal für Mutationen an Vault-Platform-Secrets über die Platform-Einstellungen-UI. SSOT für Post-Incident-Forensik ("welcher Admin hat den Anthropic-Key um 03:14 UTC rotiert?"). vault.json selbst schreibt dies nicht — nur den Wert.

INDEXES: idx_platform_audit_ts (aktuell über alle Keys), idx_platform_audit_user (Pro-Admin-Trail), idx_platform_audit_key (Rotations-Historie pro Key). Append-only-Invariante: keine UPDATE/DELETE-Handler; UI stellt nur recent() + lastRotated() über platformAuditQueries in shared/db.ts bereit. Jede Aktion (Erfolg + Fehler) schreibt eine Zeile.

auth_events — Authentifizierungs-Ereignislog für Endnutzer (Migration 027, Issue #130)

Append-only-Journal der Auth-Ereignisse für Security-Auditing und Incident-Untersuchungen. Getrennt von platform_audit_log (Admin-only Vault) — diese Tabelle deckt den End-User-Auth-Flow inkl. IP ab.

INDEXES: idx_auth_events_ts (neueste zuerst), idx_auth_events_user (Historie pro User), idx_auth_events_ip (Bedrohungserkennung pro IP), idx_auth_events_event (Filter event+result). authEventQueries.insert/recent in shared/db.ts. Geloggt in master-bot/routes/auth.ts (register/login/oauth/magic-link) und master-bot/routes/cli.ts (device_code_approve).

Migrationen

Beziehungen (Foreign Keys)

• skills_project_forks.skill_id → skills_global.id (CASCADE DELETE)
• skill_evolution_logs.skill_id → skills_global.id (CASCADE DELETE)
• skill_update_requests.skill_id → skills_global.id (CASCADE DELETE)
• skill_benchmarks.request_id → skill_update_requests.id (CASCADE DELETE)

Alle CASCADE — das Löschen eines Skills löscht alle Forks, die Historie, PRs und Benchmarks.

ER-Diagramm (Textform)

users ─1:N──> projects
            ├── chat_messages
            ├── pinned_notes
            ├── project_phases
            └── skills_project_forks ──> skills_global
                                          ├── skill_evolution_logs
                                          ├── skill_update_requests
                                          │    └── skill_benchmarks
                                          └── (standalone tables)
                                               ├── activity_log
                                               ├── system_configs
                                               └── marketplace_analysis_cache