Banco de Dados — SQLite Schema

Informações Gerais

• Arquivo: data/citadel.db
• Modo: WAL (Write-Ahead Logging) — leitura paralela
• Foreign keys: habilitadas
• Busy timeout: 5000ms
• Cache: 64MB
• Migrações: shared/migrations/001-044

Diagrama 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 tabelas distribuídas em 24 migrações. Auth + projetos + dados do workspace + inteligência + billing + controle de acesso beta + auditoria de exportação de contexto. Todas as constraints de FK são aplicadas.

Tabelas

users — Usuários

projects — Projetos

UNIQUE(technical_name, owner_id)

account_settings — Configurações da Conta

chat_messages — Histórico de Chats

INDEX(project_name, worker_id, timestamp DESC)

recovery_keys — Chaves de Recuperação (Phase 45.4)

INDEX(user_id)

github_links — Vínculos de Repositório GitHub (Phase 49.3)

UNIQUE(project_name, owner, repo) — múltiplos repositórios por projeto são permitidos. INDEX(project_name), INDEX(webhook_secret)

github_events — Log de Eventos GitHub (Phase 49.3.1)

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

skills_global — Skills Globais

skills_project_forks — Forks de Skills

UNIQUE(project_name, skill_id)

skill_evolution_logs — Histórico de Alterações de Skills

skill_update_requests — PRs de Skills (Sage)

skill_benchmarks — Testes A/B de Skills

pinned_notes — Notas Fixadas

project_phases — Fases do Roadmap

UNIQUE(project_name, phase_id)

activity_log — Registro de Atividade

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

system_configs — Configurações do Sistema

marketplace_analysis_cache — Cache do Marketplace

Cache de análise de compatibilidade de skills do marketplace.

ephemeral_tokens — Tokens de Vida Curta (issue #27)

Armazenamento persistente para tokens OAuth de uso único, redefinição de senha e verificação de email. Antes da migração 022, esses tokens viviam em um Map em memória e eram perdidos a cada reinício do master, quebrando o login via Google/GitHub (invalid_state).

INDEX(type), INDEX(expires_at). Uso único: consume() em uma única transação lê o payload e exclui o registro.

project_issues — Issues por projeto (issue #53, Phase 53.14)

SQLite como SSOT para issues, substituindo o issues/issues.json por projeto. O arquivo continua em disco como export derivado (mirror), ignorado pelo git — o DB é autoritativo.

PK (project_name, id) — cada projeto tem seu próprio espaço de IDs independente. INDEX (project_name, status) — filtro mais frequente (open only). As operações ficam em issueQueries (shared/db.ts): list/get/nextId/insert/upsert/replaceAll/bulkImport. replaceAll — transação atômica; bulkImport — INSERT OR IGNORE para re-seed idempotente.

Migration 036 (#187, 2026-05-23): ALTER TABLE project_issues ADD COLUMN assignee TEXT. Os novos status (in_progress, blocked, deferred) são validados no nível da aplicação (union IssueStatus em shared/db.ts), não por CHECK constraint (o SQLite não permite adicionar constraint via ALTER TABLE).

Migration 046 (#327, 2026-06-03): ALTER TABLE project_issues ADD COLUMN created_by TEXT NOT NULL DEFAULT 'legacy'. O lifecycle foi ampliado com o novo status draft. handleCreateIssue força status='draft' quando assignee=null (caso contrário, direto 'open'), exige worker_id (body ou fallback de auth). handleUpdateIssue faz auto-promote draft → open na primeira atribuição de assignee e rejeita close para issue nunca atribuído (FR-ISS-101/102/103). Linhas pré-existentes recebem backfill via DEFAULT como 'legacy'.

onboarding_progress — Estado do checklist pós-wizard (issue #56, Phase 54.1)

Cache derivado por usuário sobre o stream de eventos do activity_log (SSOT). A UI lê com uma única consulta em vez de agregar eventos. Replayável: dismiss/replay não alteram state, apenas dismissed_at.

INDEX em completed_at para análise de funil (#61 Phase 54.6). As operações ficam em onboardingQueries (shared/db.ts): getProgress/recordEvent/dismiss/replay. recordEvent é idempotente em (chat_id, step, status) — uma chamada idêntica repetida retorna changed=false e não escreve no activity_log. Whitelist: 5 steps × 2 statuses (completed, skipped). skipped NÃO incrementa completed_count. A transição skipped → completed adiciona ao count. Todas as mutações emitem eventos no activity_log com event_type LIKE 'onboarding_%' — verdadeiro SSOT para métricas de funil; as colunas da tabela são cache derivado.

platform_audit_log — Trilha de rotação de segredos para super-admin (Phase 57, Sentinel #103)

Registro append-only de mutações em vault platform-secrets via UI de Configurações da Plataforma. SSOT para forense pós-incidente ("qual admin rotacionou a chave Anthropic às 03:14 UTC?"). O próprio vault.json não registra isso — apenas o valor.

INDEXES: idx_platform_audit_ts (recentes por chave), idx_platform_audit_user (trilha por admin), idx_platform_audit_key (histórico de rotação por chave). Invariante append-only: sem handlers de UPDATE/DELETE; a UI expõe somente leitura via platformAuditQueries.recent + lastRotated em shared/db.ts. Cada ação (sucesso + falha) grava um registro.

auth_events — Log de eventos de autenticação de usuários finais (Migration 027, issue #130)

Registro append-only de eventos de auth para auditoria de segurança e investigação de incidentes. Separado do platform_audit_log (vault somente admin) — esta tabela cobre o fluxo de auth de usuários finais com IP.

INDEXES: idx_auth_events_ts (mais recentes primeiro), idx_auth_events_user (histórico por usuário), idx_auth_events_ip (detecção de ameaças por IP), idx_auth_events_event (filtro event+result). authEventQueries.insert/recent em shared/db.ts. Registrado em master-bot/routes/auth.ts (register/login/oauth/magic-link) e master-bot/routes/cli.ts (device_code_approve).

Migrações

Relacionamentos (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)

Todos com CASCADE — excluir uma skill exclui todos os seus forks, histórico, PRs e benchmarks.

Diagrama ER (textual)

users ─1:N──> projects
            ├── chat_messages
            ├── pinned_notes
            ├── project_phases
            └── skills_project_forks ──> skills_global
                                          ├── skill_evolution_logs
                                          ├── skill_update_requests
                                          │    └── skill_benchmarks
                                          └── (tabelas independentes)
                                               ├── activity_log
                                               ├── system_configs
                                               └── marketplace_analysis_cache