Base de données

Le Labo du Yeti utilise SQLite (via better-sqlite3) comme moteur unique de persistance. Toute la donnée du CMS — utilisateurs, contenus, logs, extensions, consommation IA — vit dans un seul fichier content/cms.db, ouvert en mode WAL pour autoriser lectures concurrentes pendant les écritures. Cette page détaille le schéma complet, les seeds automatiques, les migrations exécutées au boot et les helpers exportés par backend/src/utils/db-store.js.

Vue d'ensemble

La base contient 8 tables. Une seule (stores) est un KV générique, héritée de la legacy json-store. Les sept autres sont des tables relationnelles dédiées à des domaines métier précis. Les contraintes de clés étrangères sont actives, et les soft-deletes existent uniquement pour les tokens API (revoked_at). Les autres suppressions sont matérielles.

Configuration SQLite (WAL & pragmas)

À l'ouverture de la connexion, quatre pragmas sont appliqués pour garantir un comportement prévisible en production multi-process (Plesk, Phusion Passenger ou cluster Node) :

db.pragma('journal_mode = WAL');      // Write-Ahead Logging (lectures concurrentes)
db.pragma('synchronous = NORMAL');    // Compromis perf/sécurité
db.pragma('foreign_keys = ON');       // Contraintes FK actives (ON DELETE CASCADE)
db.pragma('busy_timeout = 5000');     // 5 secondes d'attente sur lock

Le mode WAL sépare le journal d'écriture du fichier .db principal. Concrètement, trois fichiers cohabitent dans content/ : cms.db, cms.db-wal, cms.db-shm. Avant un backup, il faut impérativement faire un checkpoint pour fusionner le WAL dans le fichier principal — sinon le ZIP exporté contient une DB tronquée.

Table stores — KV générique

Drop-in replacement de l'ancien backend JSON-store. Toutes les clés sont auto-seedées au boot, ce qui rend l'installation zéro-config : une instance neuve démarre déjà utilisable.

CREATE TABLE stores (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

Le contenu de value est toujours un JSON sérialisé. Les helpers readStore() / writeStore() font les conversions, et updateStore(key, mutator) applique une boucle compare-and-swap sur 5 tentatives pour limiter les pertes en cas de concurrence.

Table users — RBAC

CREATE TABLE users (
  id TEXT PRIMARY KEY,
  fullname TEXT NOT NULL,
  username TEXT NOT NULL,
  email TEXT NOT NULL,
  password_hash TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'admin',
  status TEXT NOT NULL DEFAULT 'active',
  last_login_at TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE UNIQUE INDEX users_email_uk    ON users(lower(email));
CREATE UNIQUE INDEX users_username_uk ON users(lower(username));

Les rôles canoniques sont au nombre de 4 : owner, admin, webmaster, editor. Le détail de la matrice RBAC est exposé dans Authentification & RBAC. Les contraintes d'unicité sont case-insensitive grâce à l'index sur lower(...) — un compte Alice@example.com et alice@example.com ne peuvent pas coexister.

Table logs — Audit append-only

CREATE TABLE logs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  timestamp TEXT NOT NULL,
  type TEXT NOT NULL,
  action TEXT,
  resource_id TEXT,
  user_id TEXT,
  message TEXT,
  meta TEXT
);
CREATE INDEX logs_ts     ON logs(timestamp DESC);
CREATE INDEX logs_type   ON logs(type);
CREATE INDEX logs_action ON logs(action);
CREATE INDEX logs_user   ON logs(user_id);

La table est délibérément sans clé étrangère vers users(id) : on veut conserver l'historique d'un compte supprimé. Le champ meta contient un JSON sérialisé libre (contexte structuré : IP, user-agent, anciennes valeurs, diff). Quatre index ciblent les filtres les plus fréquents de l'écran Logs : tri chronologique, filtre par type, par action, et par utilisateur.

Table api_tokens — Jetons M2M

CREATE TABLE api_tokens (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  name TEXT NOT NULL,
  token_hash TEXT NOT NULL UNIQUE,
  scopes TEXT NOT NULL DEFAULT 'read',
  last_used_at TEXT,
  created_at TEXT NOT NULL,
  expires_at TEXT,
  revoked_at TEXT,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX api_tokens_user ON api_tokens(user_id);

Format public d'un token : cmstok_xxxxxxxx. Le token brut n'est jamais stocké : seul son hash SHA-256 entre en base. Le hash est unique, ce qui permet à apiTokensFindByHash() de retrouver l'identité associée à un header Authorization: Bearer cmstok_... en O(1).

Tables extensions (3 tables)

Le système d'extensions WordPress-like utilise trois tables liées par cascade. Voir le détail du système d'extensions.

extensions — Inventaire

CREATE TABLE extensions (
  id TEXT PRIMARY KEY,
  version TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'installed',
  installed_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  manifest_json TEXT NOT NULL,
  error TEXT
);
CREATE INDEX extensions_status ON extensions(status);

Le manifest_json est copié en DB au moment de l'install. C'est volontaire : si le filesystem est altéré ou si extensions/installed/<id>/extension.json disparaît, le CMS sait toujours quelles capabilities et hooks l'extension prétendait utiliser. Les statuts possibles : installed, active, inactive, failed.

extension_settings — Config UI

CREATE TABLE extension_settings (
  extension_id TEXT NOT NULL,
  key TEXT NOT NULL,
  value TEXT,
  PRIMARY KEY (extension_id, key),
  FOREIGN KEY (extension_id) REFERENCES extensions(id) ON DELETE CASCADE
);

Clé primaire composite. Le formulaire Configurer de chaque extension est auto-généré depuis manifest.settings_schema et persisté ici. Désinstaller l'extension efface ses settings.

extension_data — KV générique

CREATE TABLE extension_data (
  extension_id TEXT NOT NULL,
  key TEXT NOT NULL,
  value TEXT,
  updated_at TEXT NOT NULL,
  PRIMARY KEY (extension_id, key),
  FOREIGN KEY (extension_id) REFERENCES extensions(id) ON DELETE CASCADE
);

Exposé aux extensions via ext.storage dans le contexte injecté par le loader. Permet à une extension légère de persister sans créer de table dédiée.

Table ai_usage — Consommation IA

CREATE TABLE ai_usage (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  timestamp TEXT NOT NULL,
  kind TEXT NOT NULL DEFAULT 'other',
  action TEXT,
  provider TEXT,
  model TEXT,
  input_tokens INTEGER NOT NULL DEFAULT 0,
  output_tokens INTEGER NOT NULL DEFAULT 0,
  success INTEGER NOT NULL DEFAULT 1,
  error_code TEXT,
  duration_ms INTEGER NOT NULL DEFAULT 0,
  resource_id TEXT
);
CREATE INDEX ai_usage_ts      ON ai_usage(timestamp DESC);
CREATE INDEX ai_usage_kind    ON ai_usage(kind);
CREATE INDEX ai_usage_success ON ai_usage(success);

Chaque appel IA (cocon, blog, page, action ad hoc) est journalisé pour alimenter le dashboard de consommation visible dans Réglages → IA. Les types de kind reconnus : cocon, page, article, blog, other. Les helpers d'agrégation aiUsageSummary(), aiUsageByKind() et aiUsageByDay() sont utilisés directement par l'API GET /api/ai/usage.

Seeds — Initialisation zéro-config

À chaque démarrage, seedMissingStores() vérifie la présence des clés attendues dans stores. Si une clé manque, elle est créée avec sa valeur par défaut. Aucune écriture n'est faite si la clé existe déjà — les seeds ne remplacent jamais une config utilisateur.

const SEEDS = {
  auth: {
    security: { mode: 'development', two_factor_enabled: false }
  },
  global: {
    site:   { name: '', url: '', default_locale: 'fr', locales: ['fr', 'en'] },
    layout: { active: '' },
    header: {},
    footer: {},
    ai: {
      pricing: {
        openai:    { input_per_1k: 0.00015, output_per_1k: 0.0006 },
        anthropic: { input_per_1k: 0.003,   output_per_1k: 0.015  }
      }
    }
  },
  content: { pages: [], generated_pages: [] },
  blog:    { articles: [], categories: [], tags: [], config: {} },
  cocon:   { content_groups: [], media: [] },
  logs:    { entries: [] },                  // Legacy, vidé au boot
  permissions: { version: 1, overrides: {} } // Matrice RBAC
};

Migrations automatiques au boot

Trois migrations idempotentes tournent au démarrage à l'intérieur d'une transaction globale (migrateLegacyKv()). Idempotence garantie par des gardes COUNT(*) === 0 sur la table cible, ou par des UPDATE filtrés sur l'ancienne valeur.

migrateAdminsToUsers()

Si la table users est vide et que stores.auth.admins existe, transfère chaque admin KV vers une ligne users. Le premier admin de la liste devient owner, les autres conservent leur rôle d'origine ou tombent sur admin. Le store auth est ensuite purgé pour ne garder que auth.security.

migrateLegacyRoles()

Normalise les rôles vers les 4 valeurs canoniques. Les anciens rôles chef_projet, client_editor et viewer n'existent plus dans le code.

migrateLogsKvToTable()

Si la table logs est vide et que stores.logs.entries contient un historique, recopie chaque entrée en table puis vide le store legacy.

Helpers exportés

Le module backend/src/utils/db-store.js expose une cinquantaine de fonctions. Voici les plus utilisées.

Stores (KV legacy)

• readStore(key) — lit et parse, seed si absent
• writeStore(key, data) — upsert INSERT...ON CONFLICT
• updateStore(key, mutator) — boucle CAS sur 5 tentatives
• appendToLog(entry) — alias async vers logsInsert()

Users

• usersInsert(user) — INSERT puis renvoie l'utilisateur via usersFindById()
• usersUpdate(id, patch) — UPDATE partielle (whitelist : fullname, username, email, password_hash, role, status, last_login_at)
• usersFindById(id, { includeSecret }) — option pour inclure password_hash
• usersFindByLogin(login) — résout email ou username, case-insensitive
• usersTouchLastLogin(id) — met à jour last_login_at

Logs

• logsInsert(entry) — INSERT { timestamp, type, action, resource_id, user_id, message, meta }
• logsList({ type, action, q, since, limit, offset }) — pagination + full-text sur action/resource_id/message/meta
• logsCount(filters) — COUNT avec les mêmes filtres que logsList

API Tokens

• apiTokensInsert({ id, user_id, name, token_hash, scopes, expires_at })
• apiTokensList(user_id) / apiTokensListAll() — sans le token_hash
• apiTokensFindByHash(token_hash) — résolution Bearer en O(1)
• apiTokensRevoke(id, user_id) — soft delete avec vérif ownership
• apiTokensTouchLastUsed(id)

Extensions

• extensionsUpsert({ id, version, status, manifest }) — INSERT...ON CONFLICT DO UPDATE
• extensionsList({ status }) — parse manifest_json en manifest
• extensionsSetStatus(id, status, error)
• extensionSettingsAll(id) — retourne un { key: value } prêt à exposer
• extensionDataGet(id, key) / extensionDataSet(id, key, value) / extensionDataKeys(id)

AI Usage

• aiUsageInsert(entry) — journalisation d'un appel
• aiUsageSummary({ from, to, kind }) — totaux (calls, failures, input/output tokens)
• aiUsageByKind({ from, to }) — agrégat par catégorie
• aiUsageByDay({ from, to, kind }) — timeline pour sparkline
• aiUsageRecent({ from, to, kind, limit, offset }) — pagination debug

Backup & maintenance

• checkpointDb() — wal_checkpoint(TRUNCATE), fusionne le WAL avant backup
• closeDb() — libère le handle, requis avant restore
• getDbPath() — chemin absolu cms.db (utilisé par /api/backup/export)

Requêter la base manuellement

Pour debug ou audit terrain, ouvrir content/cms.db avec sqlite3 (CLI), DB Browser for SQLite, ou DBeaver. Toujours faire un checkpoint avant, puis idéalement copier le fichier dans un dossier temporaire pour ne pas bloquer le serveur en écriture.

cd /var/www/vhosts/example.com/cms-ia/content
sqlite3 cms.db

-- Derniers logins
SELECT u.username, u.last_login_at, u.role
FROM users u
WHERE u.status = 'active'
ORDER BY u.last_login_at DESC
LIMIT 20;

-- Logs des dernières 24h, filtré sur les modifications de pages
SELECT timestamp, user_id, action, resource_id, message
FROM logs
WHERE type = 'page'
  AND timestamp >= datetime('now', '-1 day')
ORDER BY timestamp DESC;

-- Top 10 des extensions par appels enregistrés (via logs)
SELECT action, COUNT(*) AS n
FROM logs
WHERE type = 'extension'
GROUP BY action
ORDER BY n DESC
LIMIT 10;

-- Consommation IA cumulée du mois en cours
SELECT
  kind,
  COUNT(*)                 AS calls,
  SUM(input_tokens)        AS in_tokens,
  SUM(output_tokens)       AS out_tokens,
  SUM(duration_ms) / 1000  AS total_seconds
FROM ai_usage
WHERE timestamp >= datetime('now', 'start of month')
GROUP BY kind
ORDER BY in_tokens + out_tokens DESC;

-- Jetons API encore actifs (non révoqués, non expirés)
SELECT t.id, u.username, t.name, t.scopes, t.last_used_at
FROM api_tokens t
JOIN users u ON u.id = t.user_id
WHERE t.revoked_at IS NULL
  AND (t.expires_at IS NULL OR t.expires_at > datetime('now'))
ORDER BY t.last_used_at DESC NULLS LAST;

Bonnes pratiques

1. Toujours passer par les helpers — ils encapsulent les transactions, la validation et le JSON serialisé.
2. Checkpoint avant backup — checkpointDb() fusionne le WAL ; sans ça, le ZIP exporté est incomplet.
3. Ne pas committer cms.db — le fichier est listé dans .gitignore avec ses compagnons -wal et -shm.
4. Restore = stop + close + replace + restart — appeler closeDb(), remplacer le fichier, redémarrer l'app.
5. Migrations idempotentes uniquement — toute nouvelle migration doit pouvoir tourner plusieurs fois sans dégât (garde COUNT(*) ou UPDATE filtré).
6. Pas de FK sur logs — l'historique survit à la suppression d'un user. C'est volontaire pour l'audit légal.

Pour aller plus loin

• Architecture technique — vue d'ensemble de la stack
• Authentification & RBAC — détail des rôles et capabilities
• Système d'extensions — usage des tables extensions*
• Déploiement — backup, restore, Plesk