Base de données — Schéma SQLite

Informations générales

• Fichier : data/citadel.db
• Mode : WAL (Write-Ahead Logging) — lecture parallèle
• Foreign keys : activées
• Busy timeout : 5000ms
• Cache : 64MB
• Migrations : shared/migrations/001-035

Diagramme ER (Phase 52.1)

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
        TEXT notebook_id "NotebookLM"
        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 réparties sur 35 migrations. Auth + projets + données workspace + intelligence + billing + bêta gating + audit d'export de contexte. Toutes les contraintes FK sont appliquées.

Tables

users — Utilisateurs

projects — Projets

UNIQUE(technical_name, owner_id)

account_settings — Paramètres du compte

chat_messages — Historique des chats

INDEX(project_name, worker_id, timestamp DESC)

recovery_keys — Clés de récupération (Phase 45.4)

INDEX(user_id)

github_links — Liaisons dépôts GitHub (Phase 49.3)

UNIQUE(project_name, owner, repo) — multi-repo par projet autorisé. INDEX(project_name), INDEX(webhook_secret)

github_events — Log des événements GitHub (Phase 49.3.1)

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

skills_global — Skills globaux

skills_project_forks — Forks de skills

UNIQUE(project_name, skill_id)

skill_evolution_logs — Historique des modifications de skills

skill_update_requests — PR de skills (Sage)

skill_benchmarks — Tests A/B de skills

pinned_notes — Notes épinglées

project_phases — Phases Roadmap

UNIQUE(project_name, phase_id)

activity_log — Journal d'activité

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

system_configs — Paramètres système

marketplace_analysis_cache — Cache du marketplace

Cache d'analyse de compatibilité des skills du marketplace.

ephemeral_tokens — Tokens éphémères (issue #27)

Store persistant pour les tokens OAuth à usage unique : state, réinitialisation de mot de passe, vérification d'email. Avant la migration 022, ces tokens vivaient dans un Map en mémoire et étaient perdus à chaque redémarrage du master, cassant le login Google/GitHub (invalid_state).

INDEX(type), INDEX(expires_at). Usage unique : consume() lit le payload et supprime la ligne dans une seule transaction.

project_issues — Issues par projet (issue #53, Phase 53.14)

SQLite SSOT pour les issues en remplacement du fichier issues/issues.json par projet. Le fichier reste sur le disque comme export dérivé (mirror), gitignored — la DB fait référence.

PK (project_name, id) — chaque projet a son propre espace d'identifiants indépendant. INDEX (project_name, status) — filtre le plus fréquent (open only). Les opérations sont dans issueQueries (shared/db.ts) : list/get/nextId/insert/upsert/replaceAll/bulkImport. replaceAll — transaction atomique ; bulkImport — INSERT OR IGNORE pour un re-seed idempotent.

onboarding_progress — État de la checklist post-wizard (issue #56, Phase 54.1)

Cache dérivé par utilisateur sur le flux d'événements activity_log (SSOT). L'UI lit en une seule requête plutôt qu'en agrégeant les événements. Rejouable : dismiss/replay ne modifient pas state, seulement dismissed_at.

INDEX sur completed_at pour l'analyse funnel (#61 Phase 54.6). Les opérations sont dans onboardingQueries (shared/db.ts) : getProgress/recordEvent/dismiss/replay. recordEvent est idempotent sur (chat_id, step, status) — un appel identique répété retourne changed=false sans écrire dans activity_log. Whitelist : 5 steps × 2 statuses (completed, skipped). skipped n'incrémente PAS completed_count. La transition skipped → completed ajoute au count. Toutes les mutations émettent des événements dans activity_log avec event_type LIKE 'onboarding_%' — véritable SSOT pour les métriques funnel ; les colonnes de la table sont un cache dérivé.

platform_audit_log — Trail de rotation des secrets super-admin (Phase 57, Sentinel #103)

Journal append-only des mutations sur les secrets de la plateforme dans le vault via l'UI Platform Settings. SSOT pour la forensique post-incident ("quel admin a fait tourner la clé Anthropic à 03:14 UTC ?"). Le vault.json lui-même n'écrit pas ça — value-only.

INDEXES : idx_platform_audit_ts (récent toutes clés confondues), idx_platform_audit_user (trail par admin), idx_platform_audit_key (historique de rotation par clé). Invariant append-only : aucun handler UPDATE/DELETE ; l'UI expose en lecture seule recent() + lastRotated() via platformAuditQueries dans shared/db.ts. Chaque action (succès + échec) écrit une ligne.

Migrations

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

Tout en CASCADE — supprimer un skill supprime tous ses forks, son historique, ses PRs et ses benchmarks.

Diagramme ER (textuel)

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