spb/search-box Public

1/**2 * Search-box.ai3 * Author: Simon-Pierre Boucher4 * Contact: contact@spboucher.ai5 * File: packages/db/src/repositories.ts6 * Description: Data-access repositories for sessions, events and research state.7 */89import type {10  Claim,11  ClaimStatus,12  Contradiction,13  Evidence,14  ResearchSession,15  SessionStatus,16  Source,17  Stance18} from "@search-box/shared";19import { newId } from "@search-box/shared";20import type { ResearchEvent, ResearchEventPayload } from "@search-box/events";21import { getPool } from "./pool.js";2223/* ------------------------------- row mappers ------------------------------ */2425function rowToSession(r: Record<string, unknown>): ResearchSession {26  return {27    id: r.id as string,28    question: r.question as string,29    status: r.status as SessionStatus,30    answer: (r.answer as string) ?? null,31    error: (r.error as string) ?? null,32    objectives: (r.objectives as string[]) ?? [],33    budgets: (r.budgets as Record<string, number>) ?? {},34    meta: (r.meta as Record<string, unknown>) ?? {},35    createdAt: (r.created_at as Date).toISOString(),36    updatedAt: (r.updated_at as Date).toISOString()37  };38}3940function rowToSource(r: Record<string, unknown>): Source {41  return {42    id: r.id as string,43    sessionId: r.session_id as string,44    url: r.url as string,45    title: (r.title as string) ?? null,46    domain: r.domain as string,47    status: r.status as Source["status"],48    citationIndex: (r.citation_index as number) ?? null,49    metadata: (r.metadata as Record<string, unknown>) ?? {},50    createdAt: (r.created_at as Date).toISOString()51  };52}5354function rowToClaim(r: Record<string, unknown>): Claim {55  return {56    id: r.id as string,57    sessionId: r.session_id as string,58    text: r.text as string,59    status: r.status as ClaimStatus,60    confidence: r.confidence as number,61    publicReason: (r.public_reason as string) ?? null,62    createdAt: (r.created_at as Date).toISOString(),63    updatedAt: (r.updated_at as Date).toISOString()64  };65}6667function rowToEvidence(r: Record<string, unknown>): Evidence {68  return {69    id: r.id as string,70    sessionId: r.session_id as string,71    sourceId: r.source_id as string,72    claimId: (r.claim_id as string) ?? null,73    quote: r.quote as string,74    note: (r.note as string) ?? null,75    stance: r.stance as Stance,76    createdAt: (r.created_at as Date).toISOString()77  };78}7980function rowToContradiction(r: Record<string, unknown>): Contradiction {81  return {82    id: r.id as string,83    sessionId: r.session_id as string,84    claimId: r.claim_id as string,85    description: r.description as string,86    evidenceIds: (r.evidence_ids as string[]) ?? [],87    resolution: (r.resolution as string) ?? null,88    createdAt: (r.created_at as Date).toISOString()89  };90}9192/* -------------------------------- sessions -------------------------------- */9394export const sessions = {95  async create(question: string, budgets: Record<string, number>): Promise<ResearchSession> {96    const id = newId("ses");97    const { rows } = await getPool().query(98      `INSERT INTO research_sessions (id, question, budgets) VALUES ($1, $2, $3) RETURNING *`,99      [id, question, JSON.stringify(budgets)]100    );101    return rowToSession(rows[0]);102  },103104  async get(id: string): Promise<ResearchSession | null> {105    const { rows } = await getPool().query(`SELECT * FROM research_sessions WHERE id = $1`, [id]);106    return rows[0] ? rowToSession(rows[0]) : null;107  },108109  async list(limit = 30): Promise<ResearchSession[]> {110    const { rows } = await getPool().query(111      `SELECT * FROM research_sessions ORDER BY created_at DESC LIMIT $1`,112      [limit]113    );114    return rows.map(rowToSession);115  },116117  async setStatus(id: string, status: SessionStatus): Promise<void> {118    await getPool().query(119      `UPDATE research_sessions SET status = $2, updated_at = now() WHERE id = $1`,120      [id, status]121    );122  },123124  async setObjectives(id: string, objectives: string[]): Promise<void> {125    await getPool().query(126      `UPDATE research_sessions SET objectives = $2, updated_at = now() WHERE id = $1`,127      [id, JSON.stringify(objectives)]128    );129  },130131  async complete(id: string, answer: string): Promise<void> {132    await getPool().query(133      `UPDATE research_sessions SET status = 'completed', answer = $2, updated_at = now() WHERE id = $1`,134      [id, answer]135    );136  },137138  async fail(id: string, error: string): Promise<void> {139    await getPool().query(140      `UPDATE research_sessions SET status = 'failed', error = $2, updated_at = now() WHERE id = $1`,141      [id, error]142    );143  }144};145146/* --------------------------------- events --------------------------------- */147148export const events = {149  async append(sessionId: string, payload: ResearchEventPayload): Promise<ResearchEvent> {150    const id = newId("evt");151    const { rows } = await getPool().query(152      `INSERT INTO research_events (id, session_id, type, payload)153       VALUES ($1, $2, $3, $4) RETURNING seq, created_at`,154      [id, sessionId, payload.type, JSON.stringify(payload)]155    );156    return {157      id,158      sessionId,159      seq: Number(rows[0].seq),160      createdAt: (rows[0].created_at as Date).toISOString(),161      payload162    };163  },164165  async listAfter(sessionId: string, afterSeq: number, limit = 500): Promise<ResearchEvent[]> {166    const { rows } = await getPool().query(167      `SELECT * FROM research_events WHERE session_id = $1 AND seq > $2 ORDER BY seq ASC LIMIT $3`,168      [sessionId, afterSeq, limit]169    );170    return rows.map((r: Record<string, unknown>) => ({171      id: r.id as string,172      sessionId: r.session_id as string,173      seq: Number(r.seq),174      createdAt: (r.created_at as Date).toISOString(),175      payload: r.payload as ResearchEventPayload176    }));177  }178};179180/* --------------------------------- sources -------------------------------- */181182export const sources = {183  async upsertFound(184    sessionId: string,185    url: string,186    title: string | null,187    metadata: Record<string, unknown> = {}188  ): Promise<Source> {189    const id = newId("src");190    const domain = safeDomain(url);191    const { rows } = await getPool().query(192      `INSERT INTO sources (id, session_id, url, title, domain, metadata)193       VALUES ($1, $2, $3, $4, $5, $6)194       ON CONFLICT (session_id, url)195       DO UPDATE SET title = COALESCE(sources.title, EXCLUDED.title)196       RETURNING *`,197      [id, sessionId, url, title, domain, JSON.stringify(metadata)]198    );199    return rowToSource(rows[0]);200  },201202  async markFetched(id: string, title: string | null, content: string): Promise<Source> {203    const { rows } = await getPool().query(204      `UPDATE sources SET status = 'fetched', title = COALESCE($2, title), content = $3 WHERE id = $1 RETURNING *`,205      [id, title, content]206    );207    return rowToSource(rows[0]);208  },209210  async markFailed(id: string): Promise<Source> {211    const { rows } = await getPool().query(212      `UPDATE sources SET status = 'failed' WHERE id = $1 RETURNING *`,213      [id]214    );215    return rowToSource(rows[0]);216  },217218  async get(id: string): Promise<Source | null> {219    const { rows } = await getPool().query(`SELECT * FROM sources WHERE id = $1`, [id]);220    return rows[0] ? rowToSource(rows[0]) : null;221  },222223  async getContent(id: string): Promise<string | null> {224    const { rows } = await getPool().query(`SELECT content FROM sources WHERE id = $1`, [id]);225    return rows[0]?.content ?? null;226  },227228  async listBySession(sessionId: string): Promise<Source[]> {229    const { rows } = await getPool().query(230      `SELECT * FROM sources WHERE session_id = $1 ORDER BY created_at ASC`,231      [sessionId]232    );233    return rows.map(rowToSource);234  },235236  /** Assign stable citation indices (1..n) to every source that has evidence. */237  async assignCitationIndices(sessionId: string): Promise<Source[]> {238    const { rows } = await getPool().query(239      `WITH cited AS (240         SELECT DISTINCT s.id, min(e.created_at) AS first_use241         FROM sources s JOIN evidence e ON e.source_id = s.id242         WHERE s.session_id = $1243         GROUP BY s.id244       ), numbered AS (245         SELECT id, row_number() OVER (ORDER BY first_use ASC) AS idx FROM cited246       )247       UPDATE sources SET citation_index = numbered.idx248       FROM numbered WHERE sources.id = numbered.id249       RETURNING sources.*`,250      [sessionId]251    );252    return rows.map(rowToSource).sort((a: Source, b: Source) => (a.citationIndex ?? 0) - (b.citationIndex ?? 0));253  }254};255256/* --------------------------------- claims --------------------------------- */257258export const claims = {259  async add(sessionId: string, text: string, confidence: number): Promise<Claim> {260    const id = newId("clm");261    const { rows } = await getPool().query(262      `INSERT INTO claims (id, session_id, text, confidence) VALUES ($1, $2, $3, $4) RETURNING *`,263      [id, sessionId, text, confidence]264    );265    return rowToClaim(rows[0]);266  },267268  async update(269    id: string,270    patch: { status?: ClaimStatus; confidence?: number; publicReason?: string }271  ): Promise<Claim> {272    const { rows } = await getPool().query(273      `UPDATE claims SET274         status = COALESCE($2, status),275         confidence = COALESCE($3, confidence),276         public_reason = COALESCE($4, public_reason),277         updated_at = now()278       WHERE id = $1 RETURNING *`,279      [id, patch.status ?? null, patch.confidence ?? null, patch.publicReason ?? null]280    );281    return rowToClaim(rows[0]);282  },283284  async get(id: string): Promise<Claim | null> {285    const { rows } = await getPool().query(`SELECT * FROM claims WHERE id = $1`, [id]);286    return rows[0] ? rowToClaim(rows[0]) : null;287  },288289  async listBySession(sessionId: string): Promise<Claim[]> {290    const { rows } = await getPool().query(291      `SELECT * FROM claims WHERE session_id = $1 ORDER BY created_at ASC`,292      [sessionId]293    );294    return rows.map(rowToClaim);295  }296};297298/* -------------------------------- evidence -------------------------------- */299300export const evidence = {301  async add(302    sessionId: string,303    sourceId: string,304    quote: string,305    stance: Stance,306    claimId: string | null,307    note: string | null308  ): Promise<Evidence> {309    const id = newId("ev");310    const { rows } = await getPool().query(311      `INSERT INTO evidence (id, session_id, source_id, claim_id, quote, note, stance)312       VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING *`,313      [id, sessionId, sourceId, claimId, quote, note, stance]314    );315    return rowToEvidence(rows[0]);316  },317318  async listBySession(sessionId: string): Promise<Evidence[]> {319    const { rows } = await getPool().query(320      `SELECT * FROM evidence WHERE session_id = $1 ORDER BY created_at ASC`,321      [sessionId]322    );323    return rows.map(rowToEvidence);324  }325};326327/* ------------------------------ contradictions ----------------------------- */328329export const contradictions = {330  async add(331    sessionId: string,332    claimId: string,333    description: string,334    evidenceIds: string[]335  ): Promise<Contradiction> {336    const id = newId("ctr");337    const { rows } = await getPool().query(338      `INSERT INTO contradictions (id, session_id, claim_id, description, evidence_ids)339       VALUES ($1, $2, $3, $4, $5) RETURNING *`,340      [id, sessionId, claimId, description, JSON.stringify(evidenceIds)]341    );342    return rowToContradiction(rows[0]);343  },344345  async listBySession(sessionId: string): Promise<Contradiction[]> {346    const { rows } = await getPool().query(347      `SELECT * FROM contradictions WHERE session_id = $1 ORDER BY created_at ASC`,348      [sessionId]349    );350    return rows.map(rowToContradiction);351  }352};353354/* --------------------------------- helpers -------------------------------- */355356function safeDomain(url: string): string {357  try {358    return new URL(url).hostname;359  } catch {360    return "unknown";361  }362}363