Baza danych — SQLite Schema

Informacje ogólne

• Plik: data/citadel.db
• Tryb: WAL (Write-Ahead Logging) — równoległe odczyty
• Foreign keys: włączone
• Busy timeout: 5000ms
• Cache: 64MB
• Migracje: shared/migrations/001-044

Diagram ER (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
        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 tabele w 24 migracjach. Auth + projekty + dane workspace + warstwa intelligence + billing + beta gating + audit eksportu kontekstu. Wszystkie ograniczenia FK są egzekwowane.

Tabele

users — Użytkownicy

projects — Projekty

UNIQUE(technical_name, owner_id)

account_settings — Ustawienia konta

chat_messages — Historia czatów

INDEX(project_name, worker_id, timestamp DESC)

recovery_keys — Klucze odzyskiwania (Phase 45.4)

INDEX(user_id)

github_links — Powiązania GitHub repo (Phase 49.3)

UNIQUE(project_name, owner, repo) — dozwolone multi-repo per projekt. INDEX(project_name), INDEX(webhook_secret)

github_events — Log zdarzeń GitHub (Phase 49.3.1)

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

skills_global — Globalne skille

skills_project_forks — Forki skillów

UNIQUE(project_name, skill_id)

skill_evolution_logs — Historia zmian skillów

skill_update_requests — PR skillów (Sage)

skill_benchmarks — Testy A/B skillów

pinned_notes — Przypięte notatki

project_phases — Fazy Roadmap

UNIQUE(project_name, phase_id)

activity_log — Dziennik aktywności

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

system_configs — Konfiguracje systemowe

marketplace_analysis_cache — Cache marketplace

Cache analizy zgodności skillów z marketplace.

ephemeral_tokens — Tokeny krótkotrwałe (issue #27)

Persistent store dla jednorazowych tokenów OAuth state, resetowania hasła i weryfikacji email. Przed migracją 022 tokeny te żyły w in-memory Map i przepadały przy każdym restarcie mastera, psując logowanie przez Google/GitHub (invalid_state).

INDEX(type), INDEX(expires_at). Jednorazowe użycie: consume() w jednej transakcji odczytuje payload i usuwa wiersz.

project_issues — Issues per projekt (issue #53, Phase 53.14)

SQLite SSOT dla issues zamiast per-projekt issues/issues.json. Plik pozostaje na dysku jako derived export (mirror), gitignored — DB jest autorytatywna.

PK (project_name, id) — każdy projekt ma własną niezależną przestrzeń id. INDEX (project_name, status) — najczęstszy filtr (tylko open). Operacje żyją w issueQueries (shared/db.ts): list/get/nextId/insert/upsert/replaceAll/bulkImport. replaceAll — atomowa transakcja; bulkImport — INSERT OR IGNORE dla idempotentnego re-seeda.

Migration 036 (#187, 2026-05-23): ALTER TABLE project_issues ADD COLUMN assignee TEXT. Nowe statusy (in_progress, blocked, deferred) są walidowane na poziomie aplikacji (union IssueStatus w shared/db.ts), nie przez CHECK constraint (SQLite nie pozwala dodawać constraintów przez ALTER TABLE).

Migration 046 (#327, 2026-06-03): ALTER TABLE project_issues ADD COLUMN created_by TEXT NOT NULL DEFAULT 'legacy'. Lifecycle rozszerzony o nowy status draft. handleCreateIssue wymusza status='draft' gdy assignee=null (inaczej od razu 'open'), wymaga worker_id (body lub fallback z auth). handleUpdateIssue auto-promuje draft → open przy pierwszym przypisaniu assignee oraz odrzuca close dla zgłoszenia, które nigdy nie miało assignee (FR-ISS-101/102/103). Istniejące wiersze backfillowane przez DEFAULT jako 'legacy'.

onboarding_progress — Stan checklisty post-wizard (issue #56, Phase 54.1)

Derived cache per użytkownik nad strumieniem zdarzeń activity_log (SSOT). UI odczytuje jednym zapytaniem zamiast agregować po zdarzeniach. Replayable: dismiss/replay nie zmieniają state, tylko dismissed_at.

INDEX na completed_at dla analityki lejka (#61 Phase 54.6). Operacje żyją w onboardingQueries (shared/db.ts): getProgress/recordEvent/dismiss/replay. recordEvent jest idempotentny na (chat_id, step, status) — powtórne identyczne wywołanie zwraca changed=false i nie zapisuje do activity_log. Whitelist: 5 kroków × 2 statusy (completed, skipped). skipped NIE inkrementuje completed_count. Przejście skipped → completed dodaje do count. Wszystkie mutacje emitują zdarzenia do activity_log z event_type LIKE 'onboarding_%' — prawdziwy SSOT dla metryk lejka; kolumny tabeli to derived cache.

platform_audit_log — Ślad rotacji sekretów super-admina (Phase 57, Sentinel #103)

Append-only dziennik mutacji na platform-secrets vault przez UI Platform Settings. SSOT dla forensyki po incydentach ("który admin rotował klucz Anthropic o 03:14 UTC?"). Sam vault.json tego nie zapisuje — tylko wartości.

INDEXES: idx_platform_audit_ts (najnowsze wpisy po wszystkich kluczach), idx_platform_audit_user (ślad per admin), idx_platform_audit_key (historia rotacji per klucz). Niezmiennik append-only: brak handlerów UPDATE/DELETE; UI udostępnia read-only recent() + lastRotated() przez platformAuditQueries w shared/db.ts. Każda akcja (success+fail) zapisuje wiersz.

auth_events — Log zdarzeń uwierzytelniania użytkowników (Migration 027, issue #130)

Append-only dziennik zdarzeń auth do audytu bezpieczeństwa i badania incydentów. Oddzielny od platform_audit_log (admin-only vault) — ta tabela pokrywa flow auth użytkowników końcowych wraz z IP.

INDEXES: idx_auth_events_ts (najnowsze najpierw), idx_auth_events_user (historia per użytkownik), idx_auth_events_ip (detekcja zagrożeń per IP), idx_auth_events_event (filtr event+result). authEventQueries.insert/recent w shared/db.ts. Logowane w master-bot/routes/auth.ts (register/login/oauth/magic-link) i master-bot/routes/cli.ts (device_code_approve).

Migracje

Powiązania (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)

Wszystkie CASCADE — usunięcie skilla usuwa wszystkie forki, historię, PR i benchmarki.

Diagram ER (tekstowy)

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