import type { Database } from 'bun:sqlite' import { getDb } from '../../db/sqlite' import { archiveMemory, createMemory, getMemoryRow, updateMemory, } from './store' import { canManageMemory, isMemoryVisibleToUser, isTeamMember, listMemoryRowsForUser, } from './permissions' import { promoteMemoryToTeam } from './scopes' import { parseMemoryFrontmatter, serializeMemoryFile } from './fileFormat' import { matchMemoryLines } from './searchMatch' import { MemoryError, type MemoryRow } from './types' import type { MemoryBackend, MemoryBackendStatus, MemoryCreateInput, MemoryCreateResult, MemoryFileMatch, MemoryFileMeta, MemoryGovernanceStatus, MemoryProjectInfo, MemoryScope, MemorySearchHit, } from '../memoryBackend' /** * SQLite MemoryBackend — the multi-user store adapter behind /web/memory/* * when no in-process memdir backend is injected (standalone `slz rcs`). * * Constructed PER REQUEST by the routes with the authenticated caller's * identity (never caller-supplied) — every method re-checks visibility / * manage permissions through services/memory's permission layer, so the * adapter adds team-memory space management on top of the shared * MemoryBackend capability surface without duplicating any authorization. * * Path identity: memdir entries are files identified by path; SQLite rows * are identified by id. The adapter uses the row id AS the opaque `path` * value — read/write/delete/promote all resolve it back to the row, so no * synthesized pseudo-filename can ever collide or drift. * * Content shape: reads serve the SAME memdir md file shape the CLI and the * injection pack use (serializeMemoryFile), and writes parse it back * (parseMemoryFrontmatter) — the unified web UI edits identical content in * both modes. */ /** Caller-visible capacity scale — aligned with the CLI memdir * TOTAL_MEMORY_CAPACITY (20 working + 500 episodic + 1000 semantic + * 100 procedural) so utilization reads on the same meter. */ const SQLITE_MEMORY_CAPACITY = 1620 const CAPACITY_WARNING_THRESHOLD = 0.7 const CAPACITY_CRITICAL_THRESHOLD = 0.85 export function createSqliteMemoryBackend( userId: string, isAdmin = false, db: Database = getDb(), ): MemoryBackend { /** Batch team-name lookup for listing facets. */ const teamNames = (teamIds: Set): Map => { if (teamIds.size === 0) return new Map() const rows = db .query( `SELECT id, name FROM teams WHERE id IN (${[...teamIds].map((_, i) => `$t${i}`).join(', ')})`, ) .all( Object.fromEntries([...teamIds].map((id, i) => [`$t${i}`, id])), ) as unknown as { id: string; name: string }[] return new Map(rows.map(r => [r.id, r.name])) } const toFileMeta = ( row: MemoryRow, names: Map, ): MemoryFileMeta => ({ path: row.id, name: row.name, description: row.description, type: row.type, layer: row.layer, mtimeMs: Date.parse(row.updated_at) || 0, scope: row.scope_kind, confidence: row.confidence, accessCount: row.access_count, canManage: canManageMemory(row.id, userId, isAdmin), ...(row.team_id ? { teamName: names.get(row.team_id) } : {}), }) /** All rows visible to the caller (personal + member teams), optionally * narrowed to one scope — the permission-filtered listing is reused * verbatim, so cross-team rows can never leak into a UI listing. */ const visibleRows = (scope?: MemoryScope): MemoryRow[] => { const rows = listMemoryRowsForUser( userId, scope === 'personal' ? { personalScope: true } : {}, isAdmin, ) return scope === 'team' ? rows.filter(r => r.scope_kind === 'team') : rows } return { mode: 'sqlite' as const, async status(): Promise { return { mode: 'sqlite', enabled: true, memoryDir: '', // Teams are a first-class space in multi-user mode — always on. teamEnabled: true, automation: { prefetch: false, extract: true, // bridge write-back (POST /v1/memories/sync) autoDream: false, mode: 'normal', }, } }, async listProjects(): Promise { // No project concept in multi-user mode — one shared memory space // per user/team. The UI hides the project selector on empty lists. return [] }, async list(scope?: MemoryScope): Promise { const rows = visibleRows(scope) const names = teamNames( new Set(rows.map(r => r.team_id).filter((t): t is string => !!t)), ) return rows.map(r => toFileMeta(r, names)) }, async read(path: string): Promise { const row = getMemoryRow(path) if (!row || !isMemoryVisibleToUser(path, userId, isAdmin)) return null return serializeMemoryFile({ name: row.name, description: row.description, type: row.type, content: row.content, }) }, async write( path: string, content: string, ): Promise<{ error: string | null }> { const row = getMemoryRow(path) if (!row) return { error: 'Memory not found' } if (!canManageMemory(path, userId, isAdmin)) { return { error: 'Not authorized to edit this memory' } } const parsed = parseMemoryFrontmatter(content) if (!parsed) { return { error: 'Content must keep the memory file format (--- frontmatter with name/type, then body)', } } try { const updated = updateMemory(path, { name: parsed.name, description: parsed.description ?? undefined, type: parsed.type, content: parsed.content, }) return updated ? { error: null } : { error: 'Memory not found' } } catch (err) { return { error: err instanceof Error ? err.message : 'update failed', } } }, async delete(path: string): Promise<{ error: string | null }> { const row = getMemoryRow(path) if (!row) return { error: 'Memory not found' } if (!canManageMemory(path, userId, isAdmin)) { return { error: 'Not authorized to delete this memory' } } archiveMemory(path) return { error: null } }, async create(input: MemoryCreateInput): Promise { const name = input.name.trim() if (!name) return { error: 'name is required' } if (!input.content.trim()) return { error: 'content is required' } let teamId: string | undefined if (input.scope === 'team') { teamId = input.teamId?.trim() if (!teamId) return { error: 'team_id is required for team memories' } if (!isAdmin && !isTeamMember(db, userId, teamId)) { return { error: 'Not a member of this team' } } } // Duplicate-name guard — the row-store counterpart of memdir's // "a memory file already exists at " (file-path collision). // Scope-local: personal matches within the caller's personal space, // team within the TARGET team only (same name in two different teams // is a legal multi-team state, not a collision). const dup = visibleRows(input.scope).find( r => r.name === name && r.scope_kind === input.scope && (input.scope === 'personal' || r.team_id === teamId), ) if (dup) { return { error: `a memory named "${name}" already exists in this ${ input.scope === 'team' ? `team (${teamId})` : 'personal' } space`, path: dup.id, } } try { const row = createMemory({ scopeKind: input.scope, ownerUserId: input.scope === 'personal' ? userId : undefined, teamId, name, description: input.description?.trim() || undefined, type: input.type, content: input.content, createdBy: userId, }) return { error: null, path: row.id } } catch (err) { if (err instanceof MemoryError) return { error: err.message } return { error: err instanceof Error ? err.message : 'create failed', } } }, async promoteToTeam( path: string, teamId?: string, ): Promise<{ error: string | null }> { if (!teamId) { return { error: 'team_id is required to promote into a team' } } try { promoteMemoryToTeam(path, userId, teamId) return { error: null } } catch (err) { if (err instanceof MemoryError) return { error: err.message } return { error: err instanceof Error ? err.message : 'promote failed', } } }, async search(pattern: string): Promise { const rows = visibleRows() const names = teamNames( new Set(rows.map(r => r.team_id).filter((t): t is string => !!t)), ) const results: MemoryFileMatch[] = [] for (const row of rows) { const content = serializeMemoryFile({ name: row.name, description: row.description, type: row.type, content: row.content, }) const matches = matchMemoryLines(pattern, row.name, content) if (matches.length > 0) { results.push({ path: row.id, name: row.name, description: row.description, type: row.type, layer: row.layer, scope: row.scope_kind, matches, ...(row.team_id ? { teamName: names.get(row.team_id) } : {}), }) } } return results }, async recallTest(query: string, topK?: number): Promise { const q = query.trim() if (!q) return [] const k = topK ?? 5 const rows = visibleRows() if (rows.length === 0) return [] const byId = new Map(rows.map(r => [r.id, r])) // Path 1 — semantic: FTS5 BM25 over name/description/content, the same // index the write-back dedup path uses. FTS syntax edge cases degrade // to an empty path (not an empty preview — the keyword path remains). let semanticIds: string[] = [] try { // No table alias on memories_fts — Bun's SQLite rejects // `f MATCH` / `bm25(f)` alias references in FTS5 joins. const hits = db .query( `SELECT memories_fts.memory_id AS id, bm25(memories_fts) AS rank FROM memories_fts JOIN memories m ON m.id = memories_fts.memory_id WHERE memories_fts MATCH $q AND m.status != 'archived' ORDER BY rank LIMIT 200`, ) // NOTE: explicit key — Bun's binder mis-handles `{$q}` shorthand // ("$q is not defined" ReferenceError). .all({ $q: q }) as unknown as { id: string; rank: number }[] semanticIds = hits.map(h => h.id).filter(id => byId.has(id)) } catch { semanticIds = [] } // Path 2 — keyword: exact token hits over name/description. A different // algorithm from BM25 by design (term presence vs probabilistic // weighting) — the dual-path cross-validation analog of smartRetrieve's // keyword TF-IDF path. const tokens = q .toLowerCase() .split(/[\s,,;;]+/) .map(tk => tk.replace(/^[^\p{L}\p{N}]+|[^\p{L}\p{N}]+$/gu, '')) .filter(Boolean) const keywordIds = rows .map(r => { const name = r.name.toLowerCase() const desc = (r.description ?? '').toLowerCase() let hits = 0 for (const tk of tokens) { if (name.includes(tk) || desc.includes(tk)) hits++ } return hits > 0 ? { id: r.id, hits } : null }) .filter((x): x is { id: string; hits: number } => x !== null) .sort((a, b) => b.hits - a.hits) .map(x => x.id) // Recency gating — recency only boosts rows a relevance path already // hit (rows arrive ordered updated_at DESC), mirroring smartRetrieve. const relevance = new Set([...semanticIds, ...keywordIds]) if (relevance.size === 0) return [] semanticIds = semanticIds.slice(0, k * 2) const keywordTop = keywordIds.slice(0, k * 2) const recencyIds = rows .map(r => r.id) .filter(id => relevance.has(id)) .slice(0, k * 2) // Weighted RRF fusion (k = 60) — same constants and path weights as // smartRetrieve (semantic 0.45, keyword 0.35, recency 0.10). Scores // stay on the same small RRF scale the memdir preview displays. const RRF_K = 60 const fused = new Map() const addPath = (ids: string[], weight: number): void => { for (let rank = 0; rank < ids.length; rank++) { const id = ids[rank]! fused.set(id, (fused.get(id) ?? 0) + weight / (RRF_K + rank + 1)) } } addPath(semanticIds, 0.45) addPath(keywordTop, 0.35) addPath(recencyIds, 0.1) // Relevance gate — must rank top-5 of at least one relevance path // (applyRelevanceGate: no "weak everywhere, strong nowhere" survivors). const gate = new Set() for (const ids of [semanticIds, keywordTop]) { for (let i = 0; i < Math.min(5, ids.length); i++) { gate.add(ids[i]!) } } // Layer boost with the 7-day maturity ramp + confidence weighting — // mirrors smartRetrieve's applyLayerBoosters/applyConfidenceWeight. // No working-layer weight: the store's CHECK constraint has no L0. const LAYER_WEIGHTS: Record = { procedural: 2.0, semantic: 1.5, episodic: 1.0, } const MATURITY_MS = 7 * 24 * 60 * 60 * 1000 const now = Date.now() return [...fused.entries()] .filter(([id]) => gate.has(id)) .map(([id, score]) => { const row = byId.get(id)! let weight = LAYER_WEIGHTS[row.layer] ?? 1.0 if (weight > 1.0) { const ageMs = now - (Date.parse(row.updated_at) || 0) if (ageMs < MATURITY_MS) { const maturity = Math.max(0, ageMs / MATURITY_MS) weight = 1.0 + (weight - 1.0) * maturity } } return { row, score: score * weight * (0.5 + row.confidence), } }) .sort((a, b) => b.score - a.score) .slice(0, k) .map(({ row, score }) => ({ path: row.id, score, confidence: row.confidence, mtimeMs: Date.parse(row.updated_at) || 0, })) }, async governanceStatus(): Promise { // Caller-scoped governance — an admin sees team rows, never other // users' personal rows ("private means private"). const rows = visibleRows() const memoriesByType: Record = {} const memoriesByLayer: Record = {} let totalConfidence = 0 for (const r of rows) { memoriesByType[r.type] = (memoriesByType[r.type] ?? 0) + 1 memoriesByLayer[r.layer] = (memoriesByLayer[r.layer] ?? 0) + 1 totalConfidence += r.confidence } const utilizationPct = (rows.length / SQLITE_MEMORY_CAPACITY) * 100 const tier = utilizationPct >= CAPACITY_CRITICAL_THRESHOLD * 100 ? ('critical' as const) : utilizationPct >= CAPACITY_WARNING_THRESHOLD * 100 ? ('warning' as const) : ('healthy' as const) return { health: { totalMemories: rows.length, memoriesByType, memoriesByLayer, avgConfidence: rows.length ? totalConfidence / rows.length : 0, capacity: { total: rows.length, maxFiles: SQLITE_MEMORY_CAPACITY, utilizationPct, tier, }, }, layers: { // No working/episodic layers in the store — write-back lands in // semantic, web creates default to semantic. The two remaining // layer counts stay honest. working: 0, episodic: memoriesByLayer.episodic ?? 0, semantic: memoriesByLayer.semantic ?? 0, procedural: memoriesByLayer.procedural ?? 0, }, alerts: [], conflicts: null, autoDreamSetting: undefined, automation: { prefetch: false, extract: true, autoDream: false, mode: 'normal', }, teamEnabled: true, } }, } }