Base de données — Schéma SQLite

Informations générales

• Fichier : data/citadel.db
• Mode : WAL (Write-Ahead Logging) — lectures concurrentes
• Clés étrangères : activées
• Busy timeout : 5000ms
• Cache : 64MB
• Migrations : shared/migrations/001-044

Diagramme 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
        TEXT model_mode "strict|optimize (#520, migration 060)"
    }

    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
    }

Plus de 30 tables réparties sur 35 migrations. Auth + projets + données de workspace + intelligence + facturation + gating bêta + audit de context-export. Toutes les contraintes FK appliquées.

Tables

users — Utilisateurs

projects — Projets

UNIQUE(technical_name, owner_id)

account_settings — Paramètres du compte

chat_messages — Historique du chat

INDEX(project_name, worker_id, timestamp DESC)

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

INDEX(user_id)

github_links — Liaisons de repos GitHub (Phase 49.3)

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

github_events — Log d'é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 changements de skills

skill_update_requests — PRs de skills (Sage)

skill_benchmarks — Tests A/B de skills

pinned_notes — Notes épinglées

project_phases — Phases de roadmap

UNIQUE(project_name, phase_id)

activity_log — Log 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 de l'analyse de compatibilité des skills du marketplace.

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

Store persistant pour les tokens à usage unique : OAuth state, reset de mot de passe, vérification d'email. Avant la migration 022, ces tokens vivaient dans une 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)

SSOT SQLite pour les issues, remplaçant le issues/issues.json par projet. Le fichier reste sur disque comme export dérivé (miroir), gitignoré — la DB fait autorité.

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

Migration 036 (#187, 2026-05-23) : ALTER TABLE project_issues ADD COLUMN assignee TEXT. Les nouveaux statuts (in_progress, blocked, deferred) sont validés au niveau applicatif (union IssueStatus dans shared/db.ts), pas via une contrainte CHECK (SQLite ne permet pas d'ajouter des contraintes via ALTER TABLE).

Migration 046 (#327, 2026-06-03) : ALTER TABLE project_issues ADD COLUMN created_by TEXT NOT NULL DEFAULT 'legacy'. Le cycle de vie est étendu d'un nouveau statut draft. handleCreateIssue force status='draft' quand assignee=null (sinon immédiatement 'open'), et exige worker_id (body ou fallback auth). handleUpdateIssue promeut automatiquement draft → open à la première assignation d'assignee et rejette close pour une issue jamais assignée (FR-ISS-101/102/103). Les lignes préexistantes sont remplies via DEFAULT comme 'legacy'.

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

Cache dérivé par utilisateur au-dessus du flux d'événements activity_log (SSOT). L'UI le lit avec une seule requête au lieu d'agréger sur les événements. Rejouable : dismiss/replay ne changent pas state, seulement dismissed_at.

INDEX sur completed_at pour les analytics de funnel (#61 Phase 54.6). Les opérations vivent dans onboardingQueries (shared/db.ts) : getProgress/recordEvent/dismiss/replay. recordEvent est idempotent sur (chat_id, step, status) — un appel identique répété renvoie changed=false et n'écrit pas dans activity_log. Whitelist : 5 étapes × 2 statuts (completed, skipped). skipped n'incrémente PAS completed_count. La transition skipped → completed ajoute au compte. Toutes les mutations émettent des événements dans activity_log avec event_type LIKE 'onboarding_%' — la vraie SSOT pour les métriques de 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)

Log append-only des mutations sur les secrets de plateforme du vault via l'UI Platform Settings. SSOT pour la forensique post-incident (« quel admin a tourné la clé Anthropic à 03:14 UTC ? »). Le vault.json lui-même n'enregistre pas cela — value-only.

INDEX : idx_platform_audit_ts (récents sur toutes les clés), 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 recent() + lastRotated() en lecture seule via platformAuditQueries dans shared/db.ts. Chaque action (succès+échec) écrit une ligne.

auth_events — Log d'événements d'authentification utilisateur (Migration 027, issue #130)

Log append-only des événements d'auth pour l'audit de sécurité et l'investigation d'incidents. Distinct de platform_audit_log (vault admin uniquement) — cette table couvre le flow d'auth de l'utilisateur final avec IP.

INDEX : idx_auth_events_ts (plus récents d'abord), idx_auth_events_user (historique par utilisateur), idx_auth_events_ip (détection de menace par IP), idx_auth_events_event (filtre event+result). authEventQueries.insert/recent dans shared/db.ts. Loggé dans master-bot/routes/auth.ts (register/login/oauth/magic-link) et master-bot/routes/cli.ts (device_code_approve).

Migrations

Relations (clés étrangères)

• 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 retire tous ses forks, historique, PRs et 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
                                          └── (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) — waitlist Early Access pour Standard Cloud. cloudWaitlistQueries.join/findByUser/list/invite/activate/decline/stats dans shared/db.ts. Endpoints : POST /api/crm/cloud/waitlist (join), GET /waitlist/status (own de l'utilisateur), GET /waitlist (liste admin), POST /waitlist/invite (activation admin + passe le plan à 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) — feedback de qualité de traduction par locale. Dashboard de review admin à /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())) — log d'utilisation des tokens Claude par requête. 3 index : owner_id, project_name, created_at. Rempli par child-bot/claude-runner.ts via POST /api/internal/usage/log (fire-and-forget). Interrogé par GET /api/crm/account/usage → renvoie { rows: [...last 200], totals: { total, input, output } } pour BillingPage + carte d'usage du UserDropdown. | | 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)) — compteur de rate-limit quotidien pour le chat d'aide IA in-app (30 messages/jour). Interrogé par GET /api/crm/help/usage ; mis à jour sur 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 (tableau JSON, nullable), created_at TEXT DEFAULT datetime('now')) — historique de chat Arc Help persistant par utilisateur. INDEX sur (user_id, created_at DESC). arcHelpQueries.save/history(limit=60)/clearHistory dans shared/db.ts. GET /api/crm/help/history renvoie les 60 derniers messages (plus anciens d'abord). DELETE /api/crm/help/history efface tout pour l'utilisateur. Le frontend charge au montage. | | 034 | skills_owner_project | #157 | ALTER TABLE skills_global ADD COLUMN owner_project TEXT DEFAULT NULL — NULL=global/marketplace (visible par tous), non-null=possédé par ce projet (filtré dans listForProject). Backfill : les skills à catégorie non générique (odoo, python, etc.) reçoivent owner_project=category. Catégories génériques : general/frontend/backend/devops/security/testing/database/api/mobile. Les skills DB sont désormais injectés dans les prompts Claude via routeContextFromDb() dans le child-bot (#158). |

Migration 064 — token_usage_log.model (#562 S5)

Colonne nullable model TEXT sur token_usage_log : quel modèle Claude a servi la ligne d'usage (propagé depuis claude-runner via POST /api/internal/usage/log). Alimente le split de tokens par tier dans GET /api/crm/analytics/cascade pour que l'effet coût de la cascade gated par spec soit mesurable. Les lignes legacy restent NULL (unattributed).

Migration 067 — cli_devices (#627, Phase B de #617)

cli_devices (id INT PK AUTOINCREMENT, chat_id TEXT NOT NULL, device_id TEXT NOT NULL, hostname, platform, arc_version, last_project, first_seen, last_seen; UNIQUE(chat_id, device_id); INDEX idx_cli_devices_chat (chat_id, last_seen DESC)) — registry côté serveur des installations arc CLI liées, une ligne par (utilisateur, appareil). device_id est un hex aléatoire de 12 octets généré côté client (arc ≥1.0.14) et persisté dans ~/.arc/config.json. Écrit par POST /api/cli/heartbeat (à chaque invocation arc + juste après arc login) et optionnellement par device/poll quand le client envoie des infos client{}. La PREMIÈRE ligne d'appareil de l'utilisateur logge aussi activity_log(cli_invocation, event=first_device_linked) pour que la sonde cli-status de l'onboarding et le funnel d'activation enregistrent la liaison. Lu par GET /api/crm/cli/devices (cliDeviceQueries.upsert/listForUser dans shared/db.ts) → liste « Connected devices » dans la modale CLI de la sidebar (style Tailscale : hostname · platform · version · last seen).

Migration 068 — account_settings.hide_browser_chat (#630, Phase E de #617)

hide_browser_chat INTEGER DEFAULT 0 (nullable-default) sur account_settings — feature flag par utilisateur qui masque les surfaces de chat navigateur (item de nav du Workspace + la page par défaut du projet devient le feed Sessions en lecture seule). Par défaut 0 = chat visible, rien ne change ; le rebasculer restaure l'ancienne UI à l'identique (aucun code de chat supprimé). Exposé comme hideBrowserChat dans GET/PUT /api/crm/account/settings ; accountQueries.getHideBrowserChat/setHideBrowserChat dans shared/db.ts.

Migration 070 — account_settings.calm_mode + heures calmes (#644, T8)

Ajoute calm_mode INTEGER DEFAULT 0 + quiet_start / quiet_end (heures INTEGER 0–23, nullable) à account_settings. Calm mode = supprime les toasts success/info du centre (les erreurs apparaissent toujours) ; les événements significatifs restent dans le feed Home + la cloche de l'en-tête. Heures calmes = une fenêtre [start,end) (qui enjambe minuit) durant laquelle les toasts non-erreur sont silencieux même si le calm mode est off. Le gating est côté client (ToastViewport lit un miroir localStorage['arc-calm'] que App écrit depuis ces paramètres) ; les colonnes ne font que persister la préférence. Exposé comme calmMode/quietStart/quietEnd dans GET/PUT /api/crm/account/settings ; accountQueries.getCalm/setCalm.

Migration 069 — cli_transcripts + account_settings.upload_transcripts (#632)

cli_transcripts (id INT PK AUTOINCREMENT, chat_id TEXT NOT NULL, project_name TEXT NOT NULL, session_id TEXT NOT NULL, worker_id, device_id, message_count INT, first_prompt TEXT, size_bytes INT, content BLOB NOT NULL, created_at, updated_at; UNIQUE(chat_id, session_id); INDEX idx_cli_transcripts_lookup (chat_id, project_name, updated_at DESC)) — stockage serveur opt-in des transcripts complets de sessions CLI pour la continuité cross-device. content est le jsonl brut gzippé (Bun.gzipSync) ; size_bytes est la taille non compressée. Écrit par POST /api/cli/transcript/:project/:mode (arc ≥1.0.16) uniquement quand le propriétaire a activé le flag ; upsert sur (chat_id, session_id) pour qu'une session grandissante écrase. Lu, limité au propriétaire, par GET /api/crm/projects/:name/cli-transcript?session=… (cliTranscriptQueries dans shared/db.ts). Ajoute aussi upload_transcripts INTEGER DEFAULT 0 à account_settings — le flag opt-in (par défaut OFF ; les transcripts peuvent contenir du code/secrets), exposé comme uploadTranscripts dans GET/PUT /api/crm/account/settings et renvoyé au CLI dans le bloc settings de /api/cli/init. GDPR Art.17 : cli_transcripts + cli_devices sont supprimés par chat_id dans la cascade d'effacement.