База даних — SQLite Schema

Загальна інформація

• Файл: data/citadel.db
• Режим: WAL (Write-Ahead Logging) — паралельне читання
• Foreign keys: увімкнені
• Busy timeout: 5000ms
• Cache: 64MB
• Міграції: shared/migrations/001-032

ER Diagram (Phase 52.1)

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 notebook_id "NotebookLM"
        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
    }

32 таблиці у 32 міграціях. Auth + проєкти + workspace-дані + intelligence + білінг + beta gating + cloud + token tracking. Усі FK-обмеження активні.

Таблиці

users — Користувачі

projects — Проєкти

UNIQUE(technical_name, owner_id)

account_settings — Налаштування акаунту

chat_messages — Історія чатів

INDEX(project_name, worker_id, timestamp DESC)

recovery_keys — Ключі відновлення (Phase 45.4)

INDEX(user_id)

github_links — Прив'язки GitHub repo (Phase 49.3)

UNIQUE(project_name, owner, repo) — multi-repo на проєкт дозволено. INDEX(project_name), INDEX(webhook_secret)

github_events — Лог GitHub-подій (Phase 49.3.1)

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

skills_global — Глобальні навички

skills_project_forks — Форки навичок

UNIQUE(project_name, skill_id)

skill_evolution_logs — Історія змін навичок

skill_update_requests — PR навичок (Sage)

skill_benchmarks — A/B-тести навичок

pinned_notes — Закріплені нотатки

project_phases — Фази Roadmap

UNIQUE(project_name, phase_id)

activity_log — Журнал активності

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

system_configs — Системні налаштування

marketplace_analysis_cache — Кеш маркетплейсу

Кеш аналізу сумісності навичок з маркетплейсу.

ephemeral_tokens — Короткоживучі токени (issue #27)

Persistent-сховище для одноразових токенів OAuth state, password reset та email verification. До міграції 022 ці токени жили в in-memory Map і губилися при кожному рестарті master, ламаючи Google/GitHub login (invalid_state).

INDEX(type), INDEX(expires_at). Одноразове використання: consume() в одній транзакції читає payload і видаляє рядок.

Міграції

Зв'язки (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)

Усі CASCADE — видалення навички видаляє всі форки, історію, PR та бенчмарки.

ER-діаграма (текстова)

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