spb/vquant

1import type Database from "better-sqlite3";2import { logger } from "./utils/logger";34/**5 * Migration idempotente du schéma d'authentification (SQLite).6 * Ajoute les colonnes courriel / mot de passe aux utilisateurs existants et crée les tables7 * de codes, d'appareils de confiance et du journal de sécurité.8 */9export function ensureAuthSchema(sqlite: Database.Database) {10  const cols = new Set((sqlite.prepare("PRAGMA table_info(users)").all() as { name: string }[]).map((c) => c.name));11  const add = (name: string, ddl: string) => {12    if (!cols.has(name)) {13      sqlite.exec(`ALTER TABLE users ADD COLUMN ${ddl}`);14      logger.info(`auth schema: users.${name} added`);15    }16  };17  add("email", "email TEXT");18  add("email_verified_at", "email_verified_at INTEGER");19  add("password_hash", "password_hash TEXT");20  add("last_login_at", "last_login_at INTEGER");2122  sqlite.exec(`23    CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON users(email) WHERE email IS NOT NULL;2425    CREATE TABLE IF NOT EXISTS auth_codes (26      id TEXT PRIMARY KEY,27      user_id TEXT,28      email TEXT NOT NULL,29      purpose TEXT NOT NULL,30      code_hash TEXT NOT NULL,31      attempts INTEGER NOT NULL DEFAULT 0,32      expires_at INTEGER NOT NULL,33      consumed_at INTEGER,34      ip TEXT,35      created_at INTEGER NOT NULL36    );37    CREATE INDEX IF NOT EXISTS idx_auth_codes_email_purpose ON auth_codes(email, purpose);3839    CREATE TABLE IF NOT EXISTS trusted_devices (40      id TEXT PRIMARY KEY,41      user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,42      token_hash TEXT NOT NULL UNIQUE,43      label TEXT,44      user_agent TEXT,45      ip TEXT,46      created_at INTEGER NOT NULL,47      last_used_at INTEGER NOT NULL,48      expires_at INTEGER NOT NULL49    );50    CREATE INDEX IF NOT EXISTS idx_trusted_devices_user ON trusted_devices(user_id);5152    CREATE TABLE IF NOT EXISTS auth_events (53      id TEXT PRIMARY KEY,54      user_id TEXT,55      email TEXT,56      type TEXT NOT NULL,57      ip TEXT,58      user_agent TEXT,59      meta TEXT,60      created_at INTEGER NOT NULL61    );62    CREATE INDEX IF NOT EXISTS idx_auth_events_email_created ON auth_events(email, created_at);63    CREATE INDEX IF NOT EXISTS idx_auth_events_ip_created ON auth_events(ip, created_at);64    CREATE INDEX IF NOT EXISTS idx_auth_events_user_created ON auth_events(user_id, created_at);65  `);66}67