spb/immbot-ai

1// Connexion SQLite unique (node:sqlite), migration idempotente au chargement.2import { DatabaseSync } from "node:sqlite";3import { mkdirSync } from "node:fs";4import { dirname, resolve } from "node:path";5import { SCHEMA } from "./schema.ts";67let _db: DatabaseSync | null = null;89export function db(): DatabaseSync {10  if (_db) return _db;11  const envPath = process.env.DATABASE_PATH || "./data/immbot.db";12  if (envPath === ":memory:") {13    _db = new DatabaseSync(":memory:");14  } else {15    const path = resolve(process.cwd(), envPath);16    mkdirSync(dirname(path), { recursive: true });17    _db = new DatabaseSync(path);18  }19  _db.exec(SCHEMA);20  // Micro-migrations idempotentes (colonnes ajoutées après la v1)21  try {22    _db.exec("ALTER TABLE messages ADD COLUMN tool_trace TEXT NOT NULL DEFAULT '[]'");23  } catch { /* colonne déjà présente */ }24  return _db;25}2627// Helpers minimalistes autour des requêtes préparées.28// node:sqlite renvoie des objets à prototype null — on les normalise en objets simples,29// sinon React refuse de les sérialiser des Server Components vers les Client Components.30export function get<T = Record<string, unknown>>(sql: string, ...params: unknown[]): T | undefined {31  const row = db().prepare(sql).get(...(params as never[]));32  return row === undefined ? undefined : ({ ...(row as object) } as T);33}34export function all<T = Record<string, unknown>>(sql: string, ...params: unknown[]): T[] {35  return (db().prepare(sql).all(...(params as never[])) as object[]).map((r) => ({ ...r }) as T);36}37export function run(sql: string, ...params: unknown[]) {38  return db().prepare(sql).run(...(params as never[]));39}40export function transaction<T>(fn: () => T): T {41  const d = db();42  d.exec("BEGIN");43  try {44    const out = fn();45    d.exec("COMMIT");46    return out;47  } catch (e) {48    d.exec("ROLLBACK");49    throw e;50  }51}5253// Paramètres globaux (table settings, valeurs JSON).54export function getSetting<T>(key: string, fallback: T): T {55  const row = get<{ value: string }>("SELECT value FROM settings WHERE key = ?", key);56  if (!row) return fallback;57  try {58    return JSON.parse(row.value) as T;59  } catch {60    return fallback;61  }62}63export function setSetting(key: string, value: unknown) {64  run(65    "INSERT INTO settings (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value",66    key,67    JSON.stringify(value)68  );69}70