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

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

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

ER Diagram (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
    }

30+ tables across 35 migrations. Auth + projects + workspace data + intelligence + billing + beta gating + context-export audit. All FK constraints enforced.

Таблиці

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 per project allowed. 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 store для one-time токенів OAuth state, password reset, email verification. До міграції 022 ці токени жили в in-memory Map і губилися при кожному рестарті master, ламаючи Google/GitHub login (invalid_state).

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

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

SQLite SSOT для issues замість per-project issues/issues.json. Файл лишається на диску як derived export (mirror), gitignored — DB авторитативна.

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

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

Migration 046 (#327, 2026-06-03): ALTER TABLE project_issues ADD COLUMN created_by TEXT NOT NULL DEFAULT 'legacy'. Lifecycle розширено новим статусом draft. handleCreateIssue форсить status='draft' коли assignee=null (інакше одразу 'open'), вимагає worker_id (body або auth fallback). handleUpdateIssue auto-promote draft → open при першому призначенні assignee та reject close для never-assigned issue (FR-ISS-101/102/103). Pre-existing rows backfill через DEFAULT як 'legacy'.

onboarding_progress — Post-wizard checklist state (issue #56, Phase 54.1)

Per-user derived cache над activity_log event stream (SSOT). UI читає одним запитом замість агрегації по подіях. Replayable: dismiss/replay не змінюють state, лише dismissed_at.

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

platform_audit_log — Super-admin secret rotation trail (Phase 57, Sentinel #103)

Append-only журнал mutation'ів на vault platform-secrets через Platform Settings UI. SSOT для post-incident forensics ("який admin rotated Anthropic key о 03:14 UTC?"). Vault.json сам цього не пише — value-only.

INDEXES: idx_platform_audit_ts (recent across keys), idx_platform_audit_user (per-admin trail), idx_platform_audit_key (rotation history per key). Append-only invariant: no UPDATE/DELETE handlers; UI exposes read-only recent() + lastRotated() через platformAuditQueries у shared/db.ts. Кожна дія (success+fail) пише row.

auth_events — End-user authentication event log (Migration 027, issue #130)

Append-only журнал auth-подій для security auditing та incident investigation. Окрема від platform_audit_log (admin-only vault) — ця таблиця покриває end-user auth flow з IP.

INDEXES: idx_auth_events_ts (newest first), idx_auth_events_user (per-user history), idx_auth_events_ip (per-IP threat detection), idx_auth_events_event (event+result filter). 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)

All 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 for Standard Cloud. cloudWaitlistQueries.join/findByUser/list/invite/activate/decline/stats у shared/db.ts. Endpoints: POST /api/crm/cloud/waitlist (join), GET /waitlist/status (user own), GET /waitlist (admin list), POST /waitlist/invite (admin activate + upgrades plan to 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 translation quality feedback. Admin review dashboard at /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())) — Per-request Claude token usage log. 3 indexes: owner_id, project_name, created_at. Populated by child-bot/claude-runner.ts via POST /api/internal/usage/log (fire-and-forget). Queried by GET /api/crm/account/usage → returns { rows: [...last 200], totals: { total, input, output } } for BillingPage + UserDropdown usage card. | | 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)) — Daily rate-limit counter for in-app AI Help chat (30 messages/day). Queried by GET /api/crm/help/usage; updated on 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')) — Persistent Arc Help chat history per user. INDEX on (user_id, created_at DESC). arcHelpQueries.save/history(limit=60)/clearHistory in shared/db.ts. GET /api/crm/help/history returns last 60 messages (oldest-first). DELETE /api/crm/help/history clears all for user. Frontend loads on mount. | | 034 | skills_owner_project | #157 | ALTER TABLE skills_global ADD COLUMN owner_project TEXT DEFAULT NULL — NULL=global/marketplace (visible to all), non-null=owned by that project (filtered in listForProject). Backfill: skills with non-generic category (odoo, python, etc.) get owner_project=category. Generic categories: general/frontend/backend/devops/security/testing/database/api/mobile. DB skills now injected into Claude prompts via routeContextFromDb() in child-bot (#158). |