База данных — 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
    }

23 таблицы в 24 миграциях. Auth + проекты + данные workspace + интеллект-слой + billing + бета-gating + аудит контекстного экспорта. Все 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 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 — Кеш marketplace

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

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 и удаляет строку.

project_issues — Задачи per project (issue #53, Phase 53.14)

SQLite SSOT для задач вместо 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 задачи (FR-ISS-101/102/103). Pre-existing rows backfill через DEFAULT как 'legacy'.

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

Per-user derived cache над event stream из activity_log (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 trail ротации секретов (Phase 57, Sentinel #103)

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

INDEXES: idx_platform_audit_ts (recent across keys), idx_platform_audit_user (per-admin trail), idx_platform_audit_key (история ротаций per 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-событий для security auditing и расследования инцидентов. Отдельная от 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). 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