/** * Cross-session full-text search — SQLite FTS5 over the durable event log. * * The Web UI already does in-session search client-side (entries in memory); * this service powers search ACROSS sessions from the Dashboard — the thing * a terminal fundamentally cannot offer (CLI resume is per-machine JSONL). * * Indexing: persistSessionEvent feeds every user/assistant event's flattened * text (normalizePayload's `content`) into session_events_fts. Deletions * (wipeSessionConversation / archive cleanup) leave stale rows behind — * matches resolve against the live sessions table + visibility checks, so a * stale FTS row for a wiped/closed session is filtered out, not shown. * * Backfill: existing DBs predate the FTS table; the first search backfills * from session_events when the index is empty (count==0 check makes it * idempotent-ish; a fresh deployment with zero events also passes). */ import { getDb } from '../db/sqlite' import { error as logError } from '../logger' import { isSessionClosedStatus, isSessionVisibleToUser } from './session' import type { SessionEvent } from '../transport/event-bus' const INDEXED_EVENT_TYPES = new Set(['user', 'assistant', 'user_message']) /** Extract the flattened searchable text of an event payload. */ function eventSearchText(event: SessionEvent): string { if (!INDEXED_EVENT_TYPES.has(event.type)) return '' const payload = event.payload if (!payload || typeof payload !== 'object') return '' const content = (payload as Record).content return typeof content === 'string' ? content.trim() : '' } /** Index one persisted event. Best-effort — never throws. */ export function indexEventForSearch(event: SessionEvent): void { const text = eventSearchText(event) if (!text) return try { getDb() .query( 'INSERT INTO session_events_fts (text, session_id, event_id) VALUES ($text, $session_id, $event_id)', ) .run({ $text: text, $session_id: event.sessionId, $event_id: event.id }) } catch (err) { logError('[search] index insert failed:', err) } } let backfillChecked = false /** Test hook — reset the once-per-process backfill guard between cases. */ export function resetSearchBackfillState(): void { backfillChecked = false } /** * Populate the FTS index from session_events when it is empty (pre-FTS * deployments). Runs at most once per process; failures reset the flag so a * later search retries. */ export function backfillSearchIndex(): void { if (backfillChecked) return backfillChecked = true try { const db = getDb() const count = db .query('SELECT COUNT(*) AS n FROM session_events_fts') .get() as { n: number } if ((count?.n ?? 0) > 0) return // json_type(JSON 层类型)而非 typeof(SQLite 存储类型,字符串返回 // 'text' 而非 'string'——用错会导致谓词恒假、回填零行)。 db.exec(` INSERT INTO session_events_fts (text, session_id, event_id) SELECT json_extract(payload, '$.content'), session_id, id FROM session_events WHERE type IN ('user', 'assistant', 'user_message') AND json_type(payload, '$.content') = 'text' AND length(json_extract(payload, '$.content')) > 0 `) } catch (err) { backfillChecked = false logError('[search] backfill failed:', err) } } /** * Sanitize a raw user query into an FTS5 MATCH expression: each whitespace * token becomes a quoted phrase, joined with implicit AND. Quoting removes * FTS5 operator injection (NEAR, column filters, etc.). */ export function buildFtsMatchExpression(query: string): string | null { const tokens = query .split(/\s+/) .map(t => t.replace(/["]/g, '""')) .filter(t => t.length > 0) if (tokens.length === 0) return null return tokens.map(t => `"${t}"`).join(' ') } export interface SessionSearchResult { session_id: string title: string | null status: string /** Best snippet around the first match (FTS5 snippet()). */ snippet: string /** Content hit count; 0 = title/session-id match only (no content hit). */ match_count: number updated_at: number } /** Escape LIKE wildcards for a ESCAPE '\' pattern. */ function escapeLikePattern(s: string): string { return s.replace(/[\\%_]/g, ch => `\\${ch}`) } /** * Search conversation text across sessions visible to the user (ownership + * shares + team access reuse isSessionVisibleToUser). Results are grouped by * session, ordered by match count desc, capped at `limit` sessions. * * Three sources merged: * 1. FTS5 content hits (session_events_fts) — token-accurate for Latin text. * 2. Session title / id substring match — the Dashboard box must find a * session by its name or id even when the conversation text never * mentions it (a name match surfaces match_count 0). * 3. LIKE fallback over event content when FTS yields nothing — FTS5's * default unicode61 tokenizer keeps a CJK run as ONE token, so Chinese * substring queries ("登录" inside "帮我修复登录页面") match nothing; * instr() over the extracted content recovers them (Latin queries fall * back here too, gaining substring matching for free). */ export function searchSessions( userId: string, query: string, limit = 20, ): SessionSearchResult[] { const trimmed = query.trim() if (!trimmed) return [] backfillSearchIndex() const results: SessionSearchResult[] = [] const seen = new Set() const pushResult = (row: SessionSearchResult): void => { if (results.length >= limit || seen.has(row.session_id)) return seen.add(row.session_id) results.push(row) } const db = getDb() // -- 1) FTS content hits -------------------------------------------------- let rows: Array<{ session_id: string title: string | null status: string match_count: number updated_at: number }> = [] const match = buildFtsMatchExpression(trimmed) if (match) { try { // FTS5 的 snippet() 不能与 GROUP BY 同句(unable to use function snippet // in the requested context)— 分组统计与摘录分两段查询。 rows = db .query( `SELECT f.session_id, s.title, s.status, COUNT(*) AS match_count, MAX(s.updated_at) AS updated_at FROM session_events_fts f JOIN sessions s ON s.id = f.session_id WHERE session_events_fts MATCH $match GROUP BY f.session_id ORDER BY match_count DESC, updated_at DESC LIMIT 200`, ) .all({ $match: match }) as typeof rows } catch (err) { logError('[search] query failed:', err) rows = [] } } let snippetStmt: ReturnType['query']> | null = null try { snippetStmt = db.query( `SELECT snippet(session_events_fts, 0, '…', '…', '…', 24) AS snippet FROM session_events_fts WHERE session_events_fts MATCH $match AND session_id = $session_id LIMIT 1`, ) } catch (err) { logError('[search] prepare snippet failed:', err) } for (const row of rows) { if (results.length >= limit) break // Visibility — archived sessions and foreign sessions are filtered here // (stale FTS rows for wiped sessions resolve against the live table). if (isSessionClosedStatus(row.status)) continue if (!isSessionVisibleToUser(row.session_id, userId, db)) continue let snippet = '' try { const hit = snippetStmt?.get({ $match: match, $session_id: row.session_id, }) as { snippet?: string } | null snippet = hit?.snippet ?? '' } catch { // snippet 失败不致命 — 空摘录仍可导航 } pushResult({ session_id: row.session_id, title: row.title, status: row.status, snippet, match_count: row.match_count, updated_at: row.updated_at, }) } // -- 2) Session title / id substring match -------------------------------- try { const pattern = `%${escapeLikePattern(trimmed)}%` const titleRows = db .query( `SELECT s.id AS session_id, s.title, s.status, s.updated_at FROM sessions s WHERE s.title LIKE $pattern ESCAPE '\\' OR s.id LIKE $pattern ESCAPE '\\' ORDER BY s.updated_at DESC LIMIT 200`, ) .all({ $pattern: pattern }) as Array<{ session_id: string title: string | null status: string updated_at: number }> for (const row of titleRows) { if (results.length >= limit) break if (isSessionClosedStatus(row.status)) continue if (!isSessionVisibleToUser(row.session_id, userId, db)) continue pushResult({ session_id: row.session_id, title: row.title, status: row.status, snippet: '', match_count: 0, updated_at: row.updated_at, }) } } catch (err) { logError('[search] title/id query failed:', err) } // -- 3) LIKE content fallback when FTS found nothing ----------------------- // unicode61 把一串 CJK 合成单 token — 中文子串查询 FTS 恒空;instr() 对 // 提取后的 content 做字面子串匹配兜底(键名在 json_extract 之后不可见, // 不会误报)。 if (results.length === 0) { try { const rawLower = trimmed.toLowerCase() const fallbackRows = db .query( `SELECT e.session_id, s.title, s.status, COUNT(*) AS match_count, MAX(s.updated_at) AS updated_at FROM session_events e JOIN sessions s ON s.id = e.session_id WHERE e.type IN ('user', 'assistant', 'user_message') AND instr(lower(json_extract(e.payload, '$.content')), $raw) > 0 GROUP BY e.session_id ORDER BY match_count DESC, updated_at DESC LIMIT 200`, ) .all({ $raw: rawLower }) as typeof rows const needle = rawLower for (const row of fallbackRows) { if (results.length >= limit) break if (isSessionClosedStatus(row.status)) continue if (!isSessionVisibleToUser(row.session_id, userId, db)) continue pushResult({ session_id: row.session_id, title: row.title, status: row.status, snippet: buildLikeSnippet(db, row.session_id, needle), match_count: row.match_count, updated_at: row.updated_at, }) } } catch (err) { logError('[search] LIKE fallback failed:', err) } } return results } /** Extract a short window around the first LIKE-fallback match for display. */ function buildLikeSnippet( db: ReturnType, sessionId: string, needleLower: string, ): string { try { const hit = db .query( `SELECT json_extract(payload, '$.content') AS content FROM session_events WHERE session_id = $session_id AND type IN ('user', 'assistant', 'user_message') AND instr(lower(json_extract(payload, '$.content')), $needle) > 0 LIMIT 1`, ) .get({ $session_id: sessionId, $needle: needleLower }) as { content?: string | null } | null const content = hit?.content if (typeof content !== 'string' || content.length === 0) return '' const idx = content.toLowerCase().indexOf(needleLower) if (idx < 0) return content.slice(0, 60) const start = Math.max(0, idx - 24) const end = Math.min(content.length, idx + needleLower.length + 40) return `${start > 0 ? '…' : ''}${content.slice(start, end)}${end < content.length ? '…' : ''}` } catch { return '' } }