База даних — схема SQLite

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

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

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
        TEXT model_mode "strict|optimize (#520, migration 060)"
    }

    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
    }

30+ таблиць у 35 міграціях. Auth + проєкти + дані робочого простору + intelligence + білінг + beta-гейтинг + аудит context-export. Усі 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-репозиторіїв (Phase 49.3)

UNIQUE(project_name, owner, 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 — Фази роадмапу

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 — Кеш marketplace

Кеш аналізу сумісності скілів із marketplace.

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

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

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

project_issues — Задачі на проєкт (issue #53, Phase 53.14)

SQLite SSOT для задач, що замінює issues/issues.json на проєкт. Файл залишається на диску як похідний експорт (дзеркало), gitignored — DB є авторитетною.

PK (project_name, id) — кожен проєкт має власний незалежний простір id. INDEX (project_name, status) — найчастіший фільтр (лише open). Операції живуть у issueQueries (shared/db.ts): list/get/nextId/insert/upsert/replaceAll/bulkImport. replaceAll — атомарна транзакція; bulkImport — INSERT OR IGNORE для ідемпотентного re-seed.

Migration 036 (#187, 2026-05-23): ALTER TABLE project_issues ADD COLUMN assignee TEXT. Нові статуси (in_progress, blocked, deferred) валідуються на рівні застосунку (об'єднання IssueStatus у shared/db.ts), а не через CHECK-обмеження (SQLite не дозволяє додавати обмеження через ALTER TABLE).

Migration 046 (#327, 2026-06-03): ALTER TABLE project_issues ADD COLUMN created_by TEXT NOT NULL DEFAULT 'legacy'. Життєвий цикл розширено новим статусом draft. handleCreateIssue примусово встановлює status='draft', коли assignee=null (інакше одразу 'open'), і вимагає worker_id (body або auth fallback). handleUpdateIssue авто-промоутить draft → open на першому призначенні assignee й відхиляє close для ніколи не призначеної задачі (FR-ISS-101/102/103). Наявні рядки заповнюються через DEFAULT як 'legacy'.

onboarding_progress — Стан post-wizard чекліста (issue #56, Phase 54.1)

Похідний кеш на користувача над потоком подій activity_log (SSOT). UI читає його одним запитом замість агрегування подій. Повторюваний: dismiss/replay не змінюють state, лише dismissed_at.

INDEX на completed_at для аналітики воронки (#61 Phase 54.6). Операції живуть в onboardingQueries (shared/db.ts): getProgress/recordEvent/dismiss/replay. recordEvent ідемпотентний на (chat_id, step, status) — повторний ідентичний виклик повертає changed=false і не пише в activity_log. Whitelist: 5 кроків × 2 статуси (completed, skipped). skipped НЕ інкрементує completed_count. Перехід skipped → completed додає до лічильника. Усі мутації емітять події в activity_log з event_type LIKE 'onboarding_%' — справжнє SSOT для метрик воронки; колонки таблиці — похідний кеш.

platform_audit_log — Слід ротації секретів супер-адміном (Phase 57, Sentinel #103)

Append-only лог мутацій vault platform-секретів через UI Platform Settings. SSOT для post-incident форензики ("який адмін ротував ключ Anthropic о 03:14 UTC?"). Сам vault.json цього не записує — лише значення.

INDEXES: idx_platform_audit_ts (недавні по ключах), idx_platform_audit_user (слід по адміну), idx_platform_audit_key (історія ротацій по ключу). Append-only інваріант: немає хендлерів UPDATE/DELETE; UI надає read-only recent() + lastRotated() через platformAuditQueries у shared/db.ts. Кожна дія (success+fail) пише рядок.

auth_events — Лог подій автентифікації кінцевого користувача (Migration 027, issue #130)

Append-only лог подій auth для аудиту безпеки та розслідування інцидентів. Окремий від platform_audit_log (лише admin vault) — ця таблиця покриває потік auth кінцевого користувача з IP.

INDEXES: idx_auth_events_ts (найновіші першими), idx_auth_events_user (історія по користувачу), idx_auth_events_ip (виявлення загроз по IP), idx_auth_events_event (фільтр event+result). authEventQueries.insert/recent у shared/db.ts. Логується в master-bot/routes/auth.ts (register/login/oauth/magic-link) та master-bot/routes/cli.ts (device_code_approve).

Міграції

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

| 029 | cloud_waitlist | Phase 60 #134 | cloud_waitlist (id INT PK AUTOINCREMENT, user_id TEXT UNIQUE, email, position INT, status (waiting|invited|activated|declined), notes, joined_at, invited_at, activated_at) — Early Access waitlist для Standard Cloud. cloudWaitlistQueries.join/findByUser/list/invite/activate/decline/stats у shared/db.ts. Ендпоінти: POST /api/crm/cloud/waitlist (join), GET /waitlist/status (own користувача), GET /waitlist (admin list), POST /waitlist/invite (admin activate + апгрейд плану до cloud) | | 030 | translation_feedback | Phase 59.4 #122 | translation_feedback (id INT PK AUTOINCREMENT, locale TEXT, message_id TEXT, original TEXT, current_translation TEXT, suggestion TEXT, user_id TEXT, status (pending|accepted|rejected), created_at, reviewed_at) — Per-locale фідбек якості перекладу. Дашборд адмін-рев'ю на /api/crm/translations/feedback (list/stats/accept/reject). | | 031 | token_usage_log | Phase 63 #148 | token_usage_log (id INT PK AUTOINCREMENT, project_name TEXT NOT NULL, owner_id TEXT NOT NULL, worker_id TEXT, input_tokens INT DEFAULT 0, output_tokens INT DEFAULT 0, cache_tokens INT DEFAULT 0, total_tokens INT DEFAULT 0, created_at INT DEFAULT (unixepoch())) — Лог token usage Claude на запит. 3 індекси: owner_id, project_name, created_at. Заповнюється child-bot/claude-runner.ts через POST /api/internal/usage/log (fire-and-forget). Запитується GET /api/crm/account/usage → повертає { rows: [...last 200], totals: { total, input, output } } для BillingPage + usage-картки UserDropdown. | | 032 | arc_help_usage | Phase 61 #147 | arc_help_usage (id INT PK AUTOINCREMENT, user_id TEXT NOT NULL, date TEXT NOT NULL (YYYY-MM-DD), count INT NOT NULL DEFAULT 0, PRIMARY KEY (user_id, date)) — Щоденний лічильник rate-limit для in-app AI Help chat (30 повідомлень/день). Запитується GET /api/crm/help/usage; оновлюється на POST /api/crm/help/chat. | | 033 | arc_help_messages | Phase 61 #153 | arc_help_messages (id INT PK AUTOINCREMENT, user_id TEXT NOT NULL, role TEXT NOT NULL CHECK(user|assistant), text TEXT NOT NULL, sources TEXT (JSON array, nullable), created_at TEXT DEFAULT datetime('now')) — Постійна історія чату Arc Help на користувача. INDEX на (user_id, created_at DESC). arcHelpQueries.save/history(limit=60)/clearHistory у shared/db.ts. GET /api/crm/help/history повертає останні 60 повідомлень (найстаріші першими). DELETE /api/crm/help/history очищає все для користувача. Фронтенд завантажує на mount. | | 034 | skills_owner_project | #157 | ALTER TABLE skills_global ADD COLUMN owner_project TEXT DEFAULT NULL — NULL=global/marketplace (видимий усім), non-null=owned by that project (фільтрується в listForProject). Backfill: скіли з не-універсальною категорією (odoo, python тощо) отримують owner_project=category. Універсальні категорії: general/frontend/backend/devops/security/testing/database/api/mobile. Скіли з DB тепер інжектуються в промти Claude через routeContextFromDb() у child-bot (#158). |

Migration 064 — token_usage_log.model (#562 S5)

Nullable-колонка model TEXT на token_usage_log: яка модель Claude обслуговувала рядок usage (проброшена з claude-runner через POST /api/internal/usage/log). Живить розбивку токенів по tier'ах у GET /api/crm/analytics/cascade, тож ефект вартості spec-gated каскаду вимірюваний. Застарілі рядки залишаються NULL (unattributed).

Migration 067 — cli_devices (#627, Phase B of #617)

cli_devices (id INT PK AUTOINCREMENT, chat_id TEXT NOT NULL, device_id TEXT NOT NULL, hostname, platform, arc_version, last_project, first_seen, last_seen; UNIQUE(chat_id, device_id); INDEX idx_cli_devices_chat (chat_id, last_seen DESC)) — серверний реєстр прив'язаних інсталяцій arc CLI, один рядок на (користувач, пристрій). device_id — випадковий 12-байтовий hex, згенерований на боці клієнта (arc ≥1.0.14) і збережений у ~/.arc/config.json. Пишеться POST /api/cli/heartbeat (кожен виклик arc + одразу після arc login) і опціонально через device/poll, коли клієнт надсилає інфо client{}. ПЕРШИЙ рядок пристрою користувача також логує activity_log(cli_invocation, event=first_device_linked), тож onboarding cli-status probe і воронка активації реєструють прив'язку. Читається GET /api/crm/cli/devices (cliDeviceQueries.upsert/listForUser у shared/db.ts) → список "Connected devices" у CLI-модалці сайдбара (у стилі Tailscale: hostname · platform · version · last seen).

Migration 068 — account_settings.hide_browser_chat (#630, Phase E of #617)

Nullable-default hide_browser_chat INTEGER DEFAULT 0 на account_settings — per-user feature flag, що ховає поверхні browser-chat (nav-елемент робочого простору + стандартна сторінка проєкту стає read-only стрічкою Sessions). Default 0 = чат видимий, нічого не змінюється; перемикання назад відновлює старий UI дослівно (жоден код чату не видалено). Виводиться як hideBrowserChat у GET/PUT /api/crm/account/settings; accountQueries.getHideBrowserChat/setHideBrowserChat у shared/db.ts.

Migration 070 — account_settings.calm_mode + quiet hours (#644, T8)

Додає calm_mode INTEGER DEFAULT 0 + quiet_start / quiet_end (INTEGER години 0–23, nullable) до account_settings. Calm mode = придушення success/info-тостів із центру (помилки завжди спливають); значимі події залишаються в стрічці Home + дзвіночку хедера. Quiet hours = вікно [start,end) (огортає північ), протягом якого не-error тости приглушуються, навіть якщо calm mode вимкнено. Гейтинг на боці клієнта (ToastViewport читає дзеркало localStorage['arc-calm'], яке App пише з цих налаштувань); колонки лише зберігають преференцію. Виводиться як calmMode/quietStart/quietEnd у GET/PUT /api/crm/account/settings; accountQueries.getCalm/setCalm.

Migration 069 — cli_transcripts + account_settings.upload_transcripts (#632)

cli_transcripts (id INT PK AUTOINCREMENT, chat_id TEXT NOT NULL, project_name TEXT NOT NULL, session_id TEXT NOT NULL, worker_id, device_id, message_count INT, first_prompt TEXT, size_bytes INT, content BLOB NOT NULL, created_at, updated_at; UNIQUE(chat_id, session_id); INDEX idx_cli_transcripts_lookup (chat_id, project_name, updated_at DESC)) — opt-in серверне зберігання повних транскриптів CLI-сесій для cross-device continuity. content — gzip-стиснутий сирий jsonl (Bun.gzipSync); size_bytes — нестиснутий розмір. Пишеться POST /api/cli/transcript/:project/:mode (arc ≥1.0.16) лише коли власник увімкнув прапорець; upsert на (chat_id, session_id), тож зростаюча сесія перезаписує. Читається owner-gated через GET /api/crm/projects/:name/cli-transcript?session=… (cliTranscriptQueries у shared/db.ts). Також додає upload_transcripts INTEGER DEFAULT 0 до account_settings — opt-in прапорець (default OFF; транскрипти можуть містити код/секрети), виводиться як uploadTranscripts у GET/PUT /api/crm/account/settings і повертається в CLI у блоці settings ендпоінта /api/cli/init. GDPR Art.17: cli_transcripts + cli_devices видаляються за chat_id у каскаді erasure.