spb/groupe-ka

1// Auteur : Simon-Pierre Boucher — contact@spboucher.ai2// KA ID v2 — couche de données de la personnalisation Groupe KA.3// Le hub est LE feature store du groupe : événements comportementaux,4// recherches sauvegardées (+ alertes), éléments masqués, profil de5// préférences calculé (cache), réglages de confidentialité.6// Migrations idempotentes exécutées à l'import (même patron que db.ts).7import { db } from "./db";89/* ---------- migrations ---------- */1011db.exec(`12  CREATE TABLE IF NOT EXISTS user_events (13    id INTEGER PRIMARY KEY AUTOINCREMENT,14    user_id INTEGER NOT NULL,15    app TEXT NOT NULL,16    session_id TEXT,17    event_type TEXT NOT NULL,18    entity_type TEXT,19    entity_id TEXT,20    query TEXT,21    filters TEXT,22    position INTEGER,23    metadata TEXT,24    created_at TEXT NOT NULL DEFAULT (datetime('now'))25  );26  CREATE INDEX IF NOT EXISTS user_events_user ON user_events(user_id, created_at);27  CREATE INDEX IF NOT EXISTS user_events_user_app ON user_events(user_id, app);2829  CREATE TABLE IF NOT EXISTS saved_searches (30    id INTEGER PRIMARY KEY AUTOINCREMENT,31    user_id INTEGER NOT NULL,32    app TEXT NOT NULL,33    label TEXT NOT NULL,34    query TEXT,35    filters TEXT,36    location TEXT,37    url TEXT,38    alert_enabled INTEGER NOT NULL DEFAULT 0,39    alert_frequency TEXT NOT NULL DEFAULT 'daily',40    created_at TEXT NOT NULL DEFAULT (datetime('now')),41    last_run_at TEXT,42    last_triggered_at TEXT,43    fingerprint TEXT,44    UNIQUE(user_id, app, fingerprint)45  );46  CREATE INDEX IF NOT EXISTS saved_searches_user ON saved_searches(user_id, app);4748  CREATE TABLE IF NOT EXISTS hidden_items (49    user_id INTEGER NOT NULL,50    app TEXT NOT NULL,51    item_id TEXT NOT NULL,52    created_at TEXT NOT NULL DEFAULT (datetime('now')),53    PRIMARY KEY (user_id, app, item_id)54  );5556  CREATE TABLE IF NOT EXISTS user_prefs (57    user_id INTEGER PRIMARY KEY,58    profile TEXT NOT NULL,59    events_n INTEGER NOT NULL DEFAULT 0,60    updated_at TEXT NOT NULL DEFAULT (datetime('now'))61  );6263  CREATE TABLE IF NOT EXISTS pref_overrides (64    user_id INTEGER NOT NULL,65    app TEXT NOT NULL,66    dim TEXT NOT NULL,67    value TEXT NOT NULL,68    mode TEXT NOT NULL,               -- 'ban' (retirer) | 'boost' (renforcer)69    created_at TEXT NOT NULL DEFAULT (datetime('now')),70    PRIMARY KEY (user_id, app, dim, value)71  );72`);7374// Matrix factorization implicite (ALS) — recommandations batch écrites par75// scripts/kaid-mf.mjs (pm2 groupe-ka-kaid-mf, quotidien) ; lues par76// /api/sso/prefs et boostées au reranking des satellites.77db.exec(`78  CREATE TABLE IF NOT EXISTS mf_recs (79    user_id INTEGER NOT NULL,80    app TEXT NOT NULL,81    item_id TEXT NOT NULL,82    score REAL NOT NULL,83    rank INTEGER NOT NULL,84    run_at TEXT NOT NULL DEFAULT (datetime('now')),85    PRIMARY KEY (user_id, app, rank)86  );87  CREATE INDEX IF NOT EXISTS mf_recs_user ON mf_recs(user_id, app);88`);8990// Alertes actives (2026-08-26) : baseline du total de résultats + dernier envoi.91for (const col of ["last_total INTEGER", "last_notified_at TEXT"]) {92  try {93    db.exec(`ALTER TABLE saved_searches ADD COLUMN ${col}`);94  } catch {95    /* colonne déjà présente */96  }97}9899// Confidentialité — trois interrupteurs, tous ON par défaut (opt-out).100for (const col of [101  "personalization INTEGER DEFAULT 1", // reranking personnalisé102  "history_enabled INTEGER DEFAULT 1", // journal d'interactions103  "recos_enabled INTEGER DEFAULT 1",   // recommandations « Pour vous »104]) {105  try {106    db.exec(`ALTER TABLE users ADD COLUMN ${col}`);107  } catch {108    /* colonne déjà présente */109  }110}111112/* ---------- types ---------- */113114export type UserEventRow = {115  id: number;116  user_id: number;117  app: string;118  session_id: string | null;119  event_type: string;120  entity_type: string | null;121  entity_id: string | null;122  query: string | null;123  filters: string | null;124  position: number | null;125  metadata: string | null;126  created_at: string;127};128129export type SavedSearchRow = {130  id: number;131  user_id: number;132  app: string;133  label: string;134  query: string | null;135  filters: string | null;136  location: string | null;137  url: string | null;138  alert_enabled: number;139  alert_frequency: string;140  created_at: string;141  last_run_at: string | null;142  last_triggered_at: string | null;143  fingerprint: string | null;144};145146export type Privacy = {147  personalization: boolean;148  history: boolean;149  recos: boolean;150};151152export const EVENT_TYPES = new Set([153  "search", "impression", "click", "detail_view", "detail_dwell",154  "favorite", "unfavorite", "share", "compare", "hide", "unhide",155  "dismiss", "map_open", "map_marker_click", "filter_change",156  "price_filter", "location_filter", "scroll_depth", "return_visit",157  "alert_create", "alert_open", "external_click", "saved_search",158]);159160// Événements « forts » : ils invalident le profil calculé sur-le-champ.161const STRONG = new Set([162  "favorite", "unfavorite", "hide", "unhide", "alert_create", "saved_search",163]);164165const clip = (v: unknown, max: number): string | null => {166  if (typeof v === "number") return String(v).slice(0, max);167  return typeof v === "string" && v.trim() ? v.trim().slice(0, max) : null;168};169170/* ---------- confidentialité ---------- */171172export function privacyOf(userId: number): Privacy {173  const r = db174    .prepare(175      "SELECT personalization, history_enabled, recos_enabled FROM users WHERE id = ?",176    )177    .get(userId) as178    | { personalization: number; history_enabled: number; recos_enabled: number }179    | undefined;180  return {181    personalization: (r?.personalization ?? 1) !== 0,182    history: (r?.history_enabled ?? 1) !== 0,183    recos: (r?.recos_enabled ?? 1) !== 0,184  };185}186187export function setPrivacy(userId: number, p: Partial<Privacy>): void {188  if (p.personalization !== undefined)189    db.prepare("UPDATE users SET personalization = ? WHERE id = ?").run(190      p.personalization ? 1 : 0, userId);191  if (p.history !== undefined)192    db.prepare("UPDATE users SET history_enabled = ? WHERE id = ?").run(193      p.history ? 1 : 0, userId);194  if (p.recos !== undefined)195    db.prepare("UPDATE users SET recos_enabled = ? WHERE id = ?").run(196      p.recos ? 1 : 0, userId);197  invalidatePrefs(userId);198}199200/* ---------- événements ---------- */201202export function ingestEvents(203  userId: number,204  app: string,205  events: Array<Record<string, unknown>>,206): number {207  const ins = db.prepare(208    `INSERT INTO user_events (user_id, app, session_id, event_type,209       entity_type, entity_id, query, filters, position, metadata, created_at)210     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, COALESCE(?, datetime('now')))`,211  );212  let n = 0;213  let strong = false;214  const tx = db.transaction(() => {215    for (const e of events.slice(0, 50)) {216      const type = clip(e.type ?? e.event_type, 30) ?? "";217      if (!EVENT_TYPES.has(type)) continue;218      const meta: Record<string, unknown> = {};219      if (e.features && typeof e.features === "object")220        meta.features = e.features;221      if (typeof e.dwell_ms === "number") meta.dwell_ms = e.dwell_ms;222      const ts =223        typeof e.ts === "number" && e.ts > 1_600_000_000 && e.ts < Date.now() / 1000 + 60224          ? new Date(e.ts * 1000).toISOString().replace("T", " ").slice(0, 19)225          : null;226      ins.run(227        userId, app, clip(e.session_id, 60), type,228        clip(e.entity_type, 30), clip(e.entity_id, 200), clip(e.query, 200),229        e.filters && typeof e.filters === "object"230          ? JSON.stringify(e.filters).slice(0, 2000) : null,231        typeof e.position === "number" ? Math.floor(e.position) : null,232        Object.keys(meta).length ? JSON.stringify(meta).slice(0, 4000) : null,233        ts,234      );235      if (STRONG.has(type)) strong = true;236      n++;237    }238  });239  tx();240  if (strong) invalidatePrefs(userId);241  // Rétention : purge douce des événements de plus de 180 jours (1 fois sur ~50).242  if (Math.floor(Math.random() * 50) === 0)243    db.prepare(244      "DELETE FROM user_events WHERE created_at < datetime('now', '-180 days')",245    ).run();246  return n;247}248249export function eventsOf(250  userId: number,251  opts?: { app?: string; limit?: number; sinceDays?: number },252): UserEventRow[] {253  const limit = Math.min(opts?.limit ?? 4000, 8000);254  const since = `-${Math.min(opts?.sinceDays ?? 180, 365)} days`;255  if (opts?.app)256    return db.prepare(257      `SELECT * FROM user_events WHERE user_id = ? AND app = ?258         AND created_at > datetime('now', ?)259       ORDER BY id DESC LIMIT ?`,260    ).all(userId, opts.app, since, limit) as UserEventRow[];261  return db.prepare(262    `SELECT * FROM user_events WHERE user_id = ?263       AND created_at > datetime('now', ?)264     ORDER BY id DESC LIMIT ?`,265  ).all(userId, since, limit) as UserEventRow[];266}267268export function eventsCount(userId: number): number {269  return (db.prepare(270    "SELECT COUNT(*) AS n FROM user_events WHERE user_id = ?",271  ).get(userId) as { n: number }).n;272}273274export function clearHistory(userId: number): void {275  db.prepare("DELETE FROM user_events WHERE user_id = ?").run(userId);276  invalidatePrefs(userId);277}278279/* ---------- profil calculé (cache) ---------- */280281export function cachedProfile(282  userId: number,283): { profile: string; events_n: number; updated_at: string } | undefined {284  return db.prepare("SELECT profile, events_n, updated_at FROM user_prefs WHERE user_id = ?")285    .get(userId) as { profile: string; events_n: number; updated_at: string } | undefined;286}287288export function storeProfile(userId: number, profile: unknown, eventsN: number): void {289  db.prepare(290    `INSERT INTO user_prefs (user_id, profile, events_n, updated_at)291     VALUES (?, ?, ?, datetime('now'))292     ON CONFLICT(user_id) DO UPDATE SET profile = excluded.profile,293       events_n = excluded.events_n, updated_at = datetime('now')`,294  ).run(userId, JSON.stringify(profile), eventsN);295}296297export function invalidatePrefs(userId: number): void {298  db.prepare("DELETE FROM user_prefs WHERE user_id = ?").run(userId);299}300301export function resetPersonalization(userId: number): void {302  db.prepare("DELETE FROM user_events WHERE user_id = ?").run(userId);303  db.prepare("DELETE FROM pref_overrides WHERE user_id = ?").run(userId);304  db.prepare("DELETE FROM hidden_items WHERE user_id = ?").run(userId);305  invalidatePrefs(userId);306}307308/* ---------- corrections utilisateur (« Ce que KA a appris ») ---------- */309310export type OverrideRow = {311  user_id: number; app: string; dim: string; value: string; mode: string;312};313314export function overridesOf(userId: number): OverrideRow[] {315  return db.prepare("SELECT * FROM pref_overrides WHERE user_id = ?")316    .all(userId) as OverrideRow[];317}318319export function setOverride(320  userId: number, app: string, dim: string, value: string,321  mode: "ban" | "boost" | "clear",322): void {323  if (mode === "clear")324    db.prepare(325      "DELETE FROM pref_overrides WHERE user_id = ? AND app = ? AND dim = ? AND value = ?",326    ).run(userId, app, dim, value);327  else328    db.prepare(329      `INSERT INTO pref_overrides (user_id, app, dim, value, mode)330       VALUES (?, ?, ?, ?, ?)331       ON CONFLICT(user_id, app, dim, value) DO UPDATE SET mode = excluded.mode`,332    ).run(userId, app, dim.slice(0, 40), value.slice(0, 120), mode);333  invalidatePrefs(userId);334}335336/* ---------- éléments masqués (« Pas pour moi ») ---------- */337338export function hiddenOf(userId: number, app?: string): { app: string; item_id: string }[] {339  return (340    app341      ? db.prepare("SELECT app, item_id FROM hidden_items WHERE user_id = ? AND app = ?")342          .all(userId, app)343      : db.prepare("SELECT app, item_id FROM hidden_items WHERE user_id = ?").all(userId)344  ) as { app: string; item_id: string }[];345}346347export function setHidden(userId: number, app: string, itemId: string, on: boolean): void {348  if (on)349    db.prepare(350      `INSERT INTO hidden_items (user_id, app, item_id) VALUES (?, ?, ?)351       ON CONFLICT DO NOTHING`,352    ).run(userId, app, itemId.slice(0, 200));353  else354    db.prepare(355      "DELETE FROM hidden_items WHERE user_id = ? AND app = ? AND item_id = ?",356    ).run(userId, app, itemId);357  invalidatePrefs(userId);358}359360/* ---------- recommandations du modèle (matrix factorization) ---------- */361362/** Recommandations ALS fraîches (≤ 48 h) d'un membre pour un univers. */363export function mfRecsOf(userId: number, app: string): string[] {364  return (db.prepare(365    `SELECT item_id FROM mf_recs366     WHERE user_id = ? AND app = ? AND run_at > datetime('now', '-48 hours')367     ORDER BY rank ASC LIMIT 20`,368  ).all(userId, app) as { item_id: string }[]).map((r) => r.item_id);369}370371/* ---------- recherches sauvegardées ---------- */372373import crypto from "crypto";374375function fingerprint(app: string, query: string | null, filters: string | null): string {376  return crypto.createHash("sha256")377    .update(`${app}|${query ?? ""}|${filters ?? ""}`).digest("hex").slice(0, 24);378}379380export function savedSearchesOf(userId: number, app?: string): SavedSearchRow[] {381  return (382    app383      ? db.prepare(384          "SELECT * FROM saved_searches WHERE user_id = ? AND app = ? ORDER BY created_at DESC",385        ).all(userId, app)386      : db.prepare(387          "SELECT * FROM saved_searches WHERE user_id = ? ORDER BY created_at DESC",388        ).all(userId)389  ) as SavedSearchRow[];390}391392export function addSavedSearch(393  userId: number,394  app: string,395  s: { label: string; query?: string; filters?: unknown; location?: string; url?: string;396       alert?: boolean; frequency?: string },397): number {398  const filters =399    s.filters && typeof s.filters === "object"400      ? JSON.stringify(s.filters).slice(0, 2000)401      : typeof s.filters === "string" ? s.filters.slice(0, 2000) : null;402  const query = clip(s.query, 300);403  const fp = fingerprint(app, query, filters);404  db.prepare(405    `INSERT INTO saved_searches (user_id, app, label, query, filters, location,406       url, alert_enabled, alert_frequency, fingerprint)407     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)408     ON CONFLICT(user_id, app, fingerprint) DO UPDATE SET409       label = excluded.label, url = excluded.url, location = excluded.location,410       alert_enabled = excluded.alert_enabled, alert_frequency = excluded.alert_frequency`,411  ).run(412    userId, app, (s.label || "Recherche").slice(0, 120), query, filters,413    clip(s.location, 120), clip(s.url, 600),414    s.alert ? 1 : 0,415    ["instant", "daily", "weekly"].includes(s.frequency ?? "") ? s.frequency! : "daily",416    fp,417  );418  return (db.prepare(419    "SELECT id FROM saved_searches WHERE user_id = ? AND app = ? AND fingerprint = ?",420  ).get(userId, app, fp) as { id: number }).id;421}422423export function removeSavedSearch(userId: number, id: number): void {424  db.prepare("DELETE FROM saved_searches WHERE user_id = ? AND id = ?").run(userId, id);425}426427export function touchSavedSearch(userId: number, id: number): void {428  db.prepare(429    "UPDATE saved_searches SET last_run_at = datetime('now') WHERE user_id = ? AND id = ?",430  ).run(userId, id);431}432433export function setSavedSearchAlert(434  userId: number, id: number, enabled: boolean, frequency?: string,435): void {436  db.prepare(437    `UPDATE saved_searches SET alert_enabled = ?,438       alert_frequency = COALESCE(?, alert_frequency)439     WHERE user_id = ? AND id = ?`,440  ).run(enabled ? 1 : 0,441        ["instant", "daily", "weekly"].includes(frequency ?? "") ? frequency : null,442        userId, id);443}444445/* ---------- export Loi 25 ---------- */446447export function exportUserData(userId: number): Record<string, unknown> {448  const user = db.prepare(449    `SELECT ka_id, email, name, city, role, created_at, last_login,450            personalization, history_enabled, recos_enabled451     FROM users WHERE id = ?`,452  ).get(userId);453  const favorites = db.prepare(454    "SELECT app, item_id, title, subtitle, price_label, url, created_at FROM favorites WHERE user_id = ?",455  ).all(userId);456  const searches = savedSearchesOf(userId);457  const hidden = hiddenOf(userId);458  const overrides = overridesOf(userId);459  const events = db.prepare(460    `SELECT app, session_id, event_type, entity_type, entity_id, query,461            filters, position, metadata, created_at462     FROM user_events WHERE user_id = ? ORDER BY id DESC LIMIT 8000`,463  ).all(userId);464  const prefs = cachedProfile(userId);465  return {466    exported_at: new Date().toISOString(),467    user, favorites, saved_searches: searches, hidden_items: hidden,468    pref_overrides: overrides,469    preference_profile: prefs ? JSON.parse(prefs.profile) : null,470    events,471  };472}473