Base de datos — SQLite Schema

Información general

• Archivo: data/citadel.db
• Modo: WAL (Write-Ahead Logging) — lectura paralela
• Foreign keys: habilitadas
• Busy timeout: 5000ms
• Cache: 64MB
• Migraciones: 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 tablas distribuidas en 24 migraciones. Auth + proyectos + datos del workspace + inteligencia + billing + control de acceso beta + auditoría de exportación de contexto. Todas las restricciones FK están aplicadas.

Tablas

users — Usuarios

projects — Proyectos

UNIQUE(technical_name, owner_id)

account_settings — Ajustes de cuenta

chat_messages — Historial de chats

INDEX(project_name, worker_id, timestamp DESC)

recovery_keys — Claves de recuperación (Phase 45.4)

INDEX(user_id)

github_links — Vínculos de repositorio GitHub (Phase 49.3)

UNIQUE(project_name, owner, repo) — se permiten múltiples repos por proyecto. 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 globales

skills_project_forks — Forks de skills

UNIQUE(project_name, skill_id)

skill_evolution_logs — Historial de cambios de skills

skill_update_requests — PRs de skills (Sage)

skill_benchmarks — Tests A/B de skills

pinned_notes — Notas fijadas

project_phases — Fases del Roadmap

UNIQUE(project_name, phase_id)

activity_log — Registro de actividad

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

system_configs — Configuración del sistema

marketplace_analysis_cache — Cache del marketplace

Cache de análisis de compatibilidad de skills del marketplace.

ephemeral_tokens — Tokens de vida corta (issue #27)

Almacén persistente para tokens OAuth state, restablecimiento de contraseña y verificación de email de un solo uso. Antes de la migración 022, estos tokens vivían en un Map en memoria y se perdían en cada reinicio del master, rompiendo el login con Google/GitHub (invalid_state).

INDEX(type), INDEX(expires_at). Uso único: consume() lee el payload y elimina la fila en una sola transacción.

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

SQLite SSOT para issues en lugar de issues/issues.json por proyecto. El archivo permanece en disco como export derivado (mirror), ignorado por git — la DB es la fuente autoritativa.

PK (project_name, id) — cada proyecto tiene su propio espacio de IDs independiente. INDEX (project_name, status) — filtro más frecuente (solo open). Las operaciones viven en issueQueries (shared/db.ts): list/get/nextId/insert/upsert/replaceAll/bulkImport. replaceAll es una transacción atómica; bulkImport usa INSERT OR IGNORE para re-seed idempotente.

Migración 036 (#187, 2026-05-23): ALTER TABLE project_issues ADD COLUMN assignee TEXT. Los nuevos statuses (in_progress, blocked, deferred) se validan a nivel de aplicación (union IssueStatus en shared/db.ts), no con CHECK constraint (SQLite no permite añadir constraints vía ALTER TABLE).

Migración 046 (#327, 2026-06-03): ALTER TABLE project_issues ADD COLUMN created_by TEXT NOT NULL DEFAULT 'legacy'. El lifecycle se amplía con el nuevo status draft. handleCreateIssue fuerza status='draft' cuando assignee=null (en caso contrario, directamente 'open') y requiere worker_id (body o fallback de auth). handleUpdateIssue auto-promociona draft → open al asignar el primer assignee y rechaza close para un issue nunca asignado (FR-ISS-101/102/103). Las filas preexistentes se rellenan vía DEFAULT como 'legacy'.

onboarding_progress — Estado del checklist post-wizard (issue #56, Phase 54.1)

Cache derivado por usuario sobre el event stream de activity_log (SSOT). La UI lee con una sola consulta en lugar de agregar eventos. Reproducible: dismiss/replay no modifican state, solo dismissed_at.

INDEX en completed_at para análisis de funnel (#61 Phase 54.6). Las operaciones viven en onboardingQueries (shared/db.ts): getProgress/recordEvent/dismiss/replay. recordEvent es idempotente en (chat_id, step, status) — una llamada idéntica repetida devuelve changed=false y no escribe en activity_log. Whitelist: 5 pasos × 2 statuses (completed, skipped). skipped NO incrementa completed_count. La transición skipped → completed suma al conteo. Todas las mutaciones emiten eventos en activity_log con event_type LIKE 'onboarding_%' — verdadero SSOT para métricas de funnel; las columnas de la tabla son cache derivado.

platform_audit_log — Registro de rotación de secretos super-admin (Phase 57, Sentinel #103)

Registro append-only de mutaciones sobre los platform-secrets del vault a través de la UI de Platform Settings. SSOT para forense post-incidente ("¿qué admin rotó la clave de Anthropic a las 03:14 UTC?"). El propio vault.json no escribe esto — solo el valor.

INDEXES: idx_platform_audit_ts (recientes entre claves), idx_platform_audit_user (trail por admin), idx_platform_audit_key (historial de rotación por clave). Invariante append-only: sin handlers UPDATE/DELETE; la UI expone solo lectura recent() + lastRotated() a través de platformAuditQueries en shared/db.ts. Cada acción (success+fail) escribe una fila.

auth_events — Registro de eventos de autenticación de usuario final (Migración 027, issue #130)

Registro append-only de eventos de auth para auditoría de seguridad e investigación de incidentes. Separado de platform_audit_log (vault solo-admin) — esta tabla cubre el flujo de auth del usuario final con IP.

INDEXES: idx_auth_events_ts (más recientes primero), idx_auth_events_user (historial por usuario), idx_auth_events_ip (detección de amenazas por IP), idx_auth_events_event (filtro event+result). authEventQueries.insert/recent en shared/db.ts. Se registra en master-bot/routes/auth.ts (register/login/oauth/magic-link) y master-bot/routes/cli.ts (device_code_approve).

Migraciones

Relaciones (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 los CASCADE — eliminar un skill elimina todos sus forks, historial, PRs y 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
                                          └── (tablas independientes)
                                               ├── activity_log
                                               ├── system_configs
                                               └── marketplace_analysis_cache