存储层设计

// src/services/sqlite/Database.ts
this.db.run('PRAGMA journal_mode = WAL');       // WAL 模式：读写互不阻塞（类似 optimistic locking）
this.db.run('PRAGMA synchronous = NORMAL');      // 不要每次写都等磁盘确认，换取性能
this.db.run('PRAGMA foreign_keys = ON');         // 开启外键约束
this.db.run('PRAGMA temp_store = memory');       // 临时计算在内存中完成
this.db.run('PRAGMA mmap_size = 268435456');     // 用 256MB 内存映射文件，加速读取
this.db.run('PRAGMA cache_size = 10000');        // 缓存 10000 个数据页

-- 会话表：每次 Claude Code 会话的生命周期
CREATE TABLE sdk_sessions (
  id INTEGER PRIMARY KEY,
  content_session_id TEXT NOT NULL,    -- Claude Code 分配的会话 ID
  memory_session_id TEXT,              -- SDK Agent 的内部 ID
  project TEXT,                        -- 项目名称
  status TEXT DEFAULT 'active',        -- active → summarizing → completed
  user_prompt TEXT,                    -- 首条用户 prompt
  cwd TEXT,                           -- 工作目录
  platform_source TEXT,               -- claude-code / cursor / gemini-cli
  created_at_epoch INTEGER,
  completed_at_epoch INTEGER
);
 
-- 观察表：AI 压缩后的结构化记忆单元
CREATE TABLE observations (
  id INTEGER PRIMARY KEY,
  memory_session_id TEXT NOT NULL,
  type TEXT NOT NULL,                  -- decision/bugfix/discovery/change/...
  title TEXT NOT NULL,                 -- 10 字内的标题
  narrative TEXT,                      -- 详细叙述
  facts TEXT,                         -- JSON 数组，关键事实
  files_read TEXT,                    -- JSON 数组
  files_modified TEXT,                -- JSON 数组
  concepts TEXT,                      -- JSON 数组，概念标签
  content_hash TEXT,                  -- SHA256[:16] 去重
  token_estimate INTEGER,             -- 预估 Token 数
  created_at_epoch INTEGER,
  FOREIGN KEY (memory_session_id) REFERENCES sdk_sessions(memory_session_id)
);
 
-- 会话摘要表：AI 生成的会话级总结
CREATE TABLE session_summaries (
  id INTEGER PRIMARY KEY,
  memory_session_id TEXT NOT NULL,
  project TEXT,
  request TEXT,                       -- 用户原始需求
  investigated TEXT,                  -- 调查了什么
  learned TEXT,                       -- 发现了什么
  completed TEXT,                     -- 完成了什么
  next_steps TEXT,                    -- 下一步建议
  files_read TEXT,                    -- JSON 数组
  files_modified TEXT,                -- JSON 数组
  notes TEXT,                         -- 补充说明
  created_at_epoch INTEGER
);
 
-- 用户 Prompt 表：原始 prompt 存储
CREATE TABLE user_prompts (
  id INTEGER PRIMARY KEY,
  content_session_id TEXT NOT NULL,
  prompt_text TEXT NOT NULL,
  prompt_number INTEGER DEFAULT 1,
  created_at_epoch INTEGER
);
 
-- 待处理消息队列
CREATE TABLE pending_messages (
  id INTEGER PRIMARY KEY,
  session_db_id INTEGER NOT NULL,
  message_type TEXT NOT NULL,         -- observation / summary
  payload TEXT NOT NULL,              -- JSON 序列化的原始数据
  status TEXT DEFAULT 'pending',      -- pending → processing
  created_at_epoch INTEGER,
  FOREIGN KEY (session_db_id) REFERENCES sdk_sessions(id)
);
 
-- 观察反馈表：跟踪哪些 Observation 被实际使用
CREATE TABLE observation_feedback (
  id INTEGER PRIMARY KEY,
  observation_id INTEGER NOT NULL,
  signal_type TEXT NOT NULL,          -- viewed / fetched / cited
  created_at_epoch INTEGER,
  FOREIGN KEY (observation_id) REFERENCES observations(id)
);

-- FTS5 虚拟表，索引 observations 的文本字段
CREATE VIRTUAL TABLE observations_fts USING fts5(
  title,
  narrative,
  facts,
  concepts,
  content='observations',
  content_rowid='id',
  tokenize='unicode61'
);
 
-- 触发器：observations 表变更时自动同步 FTS5
CREATE TRIGGER observations_ai AFTER INSERT ON observations BEGIN
  INSERT INTO observations_fts(rowid, title, narrative, facts, concepts)
  VALUES (new.id, new.title, new.narrative, new.facts, new.concepts);
END;

-- 基本搜索
SELECT o.*, rank
FROM observations_fts
JOIN observations o ON o.id = observations_fts.rowid
WHERE observations_fts MATCH ?
ORDER BY rank
LIMIT ? OFFSET ?;
 
-- 带过滤的搜索
SELECT o.*, rank
FROM observations_fts
JOIN observations o ON o.id = observations_fts.rowid
WHERE observations_fts MATCH ?
  AND o.type = ?
  AND o.created_at_epoch >= ?
  AND o.created_at_epoch <= ?
ORDER BY rank
LIMIT ?;

// 双引号翻倍转义，再把整个查询包成一个短语
const escapedQuery = '"' + query.replace(/"/g, '""') + '"';

PRAGMA journal_mode = WAL;

// 简化的同步逻辑
// 完整实现见 src/services/sync/ChromaSync.ts
async function syncObservation(obs: Observation): Promise<void> {
  const documents = [];
 
  // 主叙述
  documents.push({
    id: `obs_${obs.id}_narrative`,
    text: obs.narrative,
    metadata: { type: obs.type, project: obs.project }
  });
 
  // 每个 fact 单独建索引
  for (let i = 0; i < obs.facts.length; i++) {
    documents.push({
      id: `obs_${obs.id}_fact_${i}`,
      text: obs.facts[i],
      metadata: { type: obs.type, project: obs.project }
    });
  }
 
  await chromaCollection.add(documents);
}

Worker 进程 ←── stdio（JSON-RPC）──→ ChromaDB MCP 进程

// 简化的混合搜索逻辑
// 完整实现见 src/services/worker/search/strategies/HybridSearchStrategy.ts
async function hybridSearch(query: string, options: SearchOptions): Promise<SearchResult[]> {
  // 并行执行两种搜索
  const [ftsResults, vectorResults] = await Promise.all([
    sqliteSearch(query, options),    // FTS5 关键词搜索
    chromaSearch(query, options),    // 向量语义搜索
  ]);
 
  // 合并去重（同一个 observation ID 可能出现在两种结果中）
  const merged = mergeAndDeduplicate(ftsResults, vectorResults);
 
  // 按综合相关度排序
  return merged.sort((a, b) => b.score - a.score);
}

INSERT INTO observations
(memory_session_id, project, type, title, subtitle, facts, narrative, concepts,
 files_read, files_modified, prompt_number, discovery_tokens, agent_type, agent_id,
 content_hash, created_at, created_at_epoch)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(memory_session_id, content_hash) DO NOTHING
RETURNING id

export function computeObservationContentHash(
  memorySessionId: string,
  title: string | null,
  narrative: string | null
): string {
  return createHash('sha256')
    .update([memorySessionId || '', title || '', narrative || ''].join('\x00'))
    .digest('hex')
    .slice(0, 16);
}