SQLite FTS5 session search
SQLite FTS5 会话搜索
The exact-read `ctx.sessionQuery` service deliberately has no derived index. Large persisted histories need full-text search without scanning every event on every query, while current live sessions need an overlay newer than the last durability checkpoint. Search also needs concrete ranking, snippets, filters, pagination, cancellation, and rebuild behavior. Splitting those concerns across a provider coordinator and a
English
Problem
The exact-read ctx.sessionQuery service deliberately has no derived index. Large persisted histories need full-text search without scanning every event on every query, while current live sessions need an overlay newer than the last durability checkpoint. Search also needs concrete ranking, snippets, filters, pagination, cancellation, and rebuild behavior.
Splitting those concerns across a provider coordinator and a database implementation would create two coupled reconciliation state machines. The first implementation needs to own source observation, extraction, SQLite transactions, generations, and query execution as one lifecycle while still exposing a small provider-neutral call contract.
Decision
@deepseek-ai/dsh-session-query declares one abstract ctx.sessionQuery service whose exact reads, filters, and traces are concrete and whose two full-text methods are abstract. searchSessions(request, exec?) returns cursor-paginated SessionSearchHits grouped by each session's strongest matching event; searchEvents(request, exec?) returns SessionEventSearchHits within one logical session. Both requests require query, accept limit and an owned branded SessionSearchCursor, and support an optional abort signal. Session search accepts sessionFilters plus event metadata filters; event search accepts event metadata filters. Results expose bounded plain-text snippets but no provider identifier or numeric relevance score. The unified service decision owns the single-key topology.
@deepseek-ai/dsh-session-query-sqlite extends the interface service and is the sole concrete owner of ctx.sessionQuery. It depends on live ctx.sessions, observes optional ctx.sessionPersistence dynamically, and owns a dedicated derived SQLite database. There is no search-provider registry, coordinator, persistence event, or agent-loop integration.
The Service Definition package also owns shared first-party semantic extraction and provider-independent filtering. SessionResultFilter covers id, nullable cwd, created-at range, nullable parent, and availability; ctx.sessionQuery.filterSessions() applies it without an FTS provider. SessionEventResultFilter covers seq/time ranges, event type, surface, and literal semantic text. Arrays are ANDed and list values are ORed. The text clause escapes caller input into a Unicode case-insensitive regular expression whose whitespace runs match one or more whitespace characters; it is available through ctx.sessionQuery.filterEvents() and is not delegated to an FTS provider.
Search semantics
Each semantic event is one FTS document carrying session metadata, event metadata, surface classification, and extracted text. All current, shadowed, and log-only documents participate unless a surface filter narrows them. Metadata filters compile to parameterized SQL before ranking. Session results partition matching documents by session and retain the strongest one.
Ordering is deterministic and comparable across the persistent and TEMP FTS tables: actual FTS5 highlighted-match span count descending, indexed document code-point length ascending, event time descending, session id ascending for the cross-session scope, and seq descending. Snippets use those actual highlight positions, strip the reserved markers, normalize whitespace, and bound by Unicode code points. Opaque cursors bind to the service instance, scope, canonical normalized request, offset, and relevant generation. Any corpus change invalidates cross-session cursors; a within-session cursor changes only when its target source/generation changes, so unrelated sessions do not invalidate it. Reopening creates a new service instance and invalidates old cursors.
Queries are trimmed, whitespace-normalized, and quoted as one literal FTS5 phrase. Embedded quotes are doubled before binding, so MATCH operators such as OR, NEAR, quotes, parentheses, and * remain data rather than executable query syntax. NUL is rejected before SQLite execution. Reserved highlight noncharacters and NUL in documents are normalized before indexing, making inserted presentation markers collision-free. Phrase matching follows tokenizer tokens rather than arbitrary substrings.
Tokenizer choice
Both persistent and live FTS5 tables use unicode61. The implementation experiment found that this tokenizer supports the two-character token AI and produces an index about 2.1× smaller than the trigram alternative. The accepted limitation is token/phrase recall: AI does not match the larger token BRAID, and arbitrary substring search uses the provider-independent text scan instead.
Extraction and reconciliation
The shared extractor includes message text, reasoning, nested tool-call/result content, tool names and arguments, blocked-prompt reasons, todo status/content, and error or terminal status detail. Structural boundaries, stream chunks, request headers, successful completion markers, and unknown declaration-merged event/content variants produce no document. Surface classification reuses foldSurface() so search agrees with model-history derivation.
One serialized operation reads the provider-neutral SessionPersistence snapshot listing, compares each source-qualified opaque revision with the revision stored beside the indexed session, loads only new or changed logs, reconciles rows in one transaction, and executes the query. It passes the caller's exact abort signal into snapshot listing and non-mutating inspection, directly awaits every started backend operation, and checks cancellation after each await and before starting more work. Cancellation therefore rejects only after active backend work is quiescent, starts no subsequent observation or reconciliation step, and keeps a following search serialized behind cleanup even if a backend ignores the signal. The operation never calls the backend's mutating load() for an id currently owned by ctx.sessions; the TEMP overlay records persisted availability, and the durable base refreshes after the live owner detaches. A revision identifies its backing persistence store as well as the backend-local log revision, so reopening against the same store reuses indexed rows while switching to an independent store cannot collide on a session id and local counter. Observation repeats when listing changes during a load; this incorporates a mutating load repair's refreshed revision before commit. Repeated queries and unchanged reopen load no full persisted logs. New, changed, and deleted sessions update on the next stable search. A source or extraction failure cannot mark a row current, and a transaction failure rolls back so a later search retries.
Persisted documents survive restarts. Live sessions use connection-local TEMP tables, shadow the persisted base for the same id, and reveal that base on detach. Closing the database drops live rows. Unmounting persistence hides durable rows without treating absence as authoritative deletion; remounting observes and reconciles the backend again. Conflicting immutable live and durable headers fail rather than combining sources.
The derived schema has its own application id and monotonic schema version. Persistent and TEMP session metadata store the integer SessionHeader.createdAt contract in strict INTEGER columns. A recognized incompatible version resets only this derived database. A database with a foreign application id or unrecognized user tables is refused before journal-mode mutation, which prevents an accidentally configured canonical session database from being changed. On POSIX filesystems, missing directories and database files are created owner-only so new SQLite sidecars inherit that mode; existing modes are preserved. One service in one process exclusively owns a derived-index path; cross-process writers are unsupported because generations and live TEMP shadow state are connection-owned.
Cancellation rejects queued operations promptly. Once asynchronous source observation starts, the caller waits for that backend promise to settle before rejection, without committing an aborted observation or starting more source/index work. Node's synchronous DatabaseSync metadata and MATCH calls cannot be interrupted once executing on the JavaScript thread, so the service checks the signal around those calls but does not promise mid-statement preemption.
Alternatives considered
- Add FTS tables to the canonical persistence database — rejected because a rebuildable index must not share the authoritative log's schema, reset, or failure boundary.
- Add a phase-one provider registry and coordinator — rejected because one implementation provides no evidence for registration semantics and would split one reconciliation lifecycle across two owners.
- Persist live overrides immediately — rejected because live events are not canonical until the existing checkpoint commits.
- Use the FTS5 trigram tokenizer — rejected because it omits useful queries shorter than three characters and measured about 2.1× the index size of
unicode61; literal substring filtering remains available through the scan path. - Use FTS5 BM25 independently in each table — rejected because scores from differently populated persistent and TEMP corpora are not comparable; actual matched spans and document length have one shared scale.
Consequences
Search has a small provider-neutral API while its only backend owns every derived-index state transition. The separate database adds configuration and a lightweight snapshot read before queries, but index corruption, reset, and tokenizer changes cannot endanger canonical logs. Durable revisions avoid full-log reads and rewrites for unchanged sessions; TEMP live overlays preserve current-session truth without making uncheckpointed events durable.
The chosen tokenizer supports short tokens with a smaller index but does not promise substring recall. Literal phrases make query syntax safe and predictable at the cost of excluding boolean/full MATCH expressions. Cancellation is prompt while queued and quiescent while awaiting sources; synchronous SQLite execution remains a non-preemptible section bracketed by signal checks.
Unit coverage pins extraction, filters, both search scopes, all default surfaces, metadata-before-ranking, snippets, literal escaping, deterministic ties, complete pagination, scoped cursor invalidation, dynamic persistence mount/unmount, restart reconciliation, live shadow/reveal/reopen, schema safety, rollback retry, and queued/in-flight source-wait cancellation. A keyless real-Loader-path test combines the package with the real SQLite persistence backend.
中文
问题
精确读取的 ctx.sessionQuery 服务有意不维护派生索引。大规模持久化的历史记录需要全文搜索，而不是每次查询都扫描全部事件；当前的活跃会话则需要一个包含上次持久性检查点之后更新的覆盖层。搜索还需要具体的排序、摘要片段、过滤器、分页、取消以及重建行为。
如果把这些关注点拆分到提供方协调器和数据库实现之间，就会产生两个耦合的对齐状态机。第一个实现既要暴露精简的提供方无关调用约定，也要在同一个生命周期内管理源观察、提取、SQLite 事务、代际与查询执行。
决策
@deepseek-ai/dsh-session-query 声明一个抽象的 ctx.sessionQuery 服务，其精确读取、过滤与追踪均有具体实现，仅有两项全文方法为抽象方法。searchSessions(request, exec?) 返回按游标分页的 SessionSearchHit，每个会话以其中匹配度最高的事件作为命中结果；searchEvents(request, exec?) 返回一个逻辑会话内的 SessionEventSearchHit。两种请求都必须提供 query，可以接受 limit 和由服务拥有的品牌化 SessionSearchCursor，并支持可选的中止信号。会话搜索接受 sessionFilters 与事件元数据过滤器，事件搜索接受事件元数据过滤器。结果会公开有界的纯文本摘要片段，但不公开提供方标识符或数值相关性分数。单一键拓扑由统一服务决策定义。
@deepseek-ai/dsh-session-query-sqlite 扩展接口服务，并且是 ctx.sessionQuery 唯一的具体所有者。它依赖实时的 ctx.sessions，动态观察可选的 ctx.sessionPersistence，并拥有一个专用的派生 SQLite 数据库。系统没有搜索提供方注册表、协调器、持久化事件或 agent loop（智能体循环）集成。
Service Definition 包还拥有共享的第一方语义提取与提供方无关的过滤。SessionResultFilter 涵盖 id、可空的 cwd、创建时间范围、可空的父会话与可用性；ctx.sessionQuery.filterSessions() 无需 FTS 提供方即可应用这些过滤器。SessionEventResultFilter 涵盖 seq/时间范围、事件类型、surface 与字面语义文本。过滤器数组内各项按逻辑与（AND）组合，列表值按逻辑或（OR）组合。文本子句会将调用方输入转义为不区分大小写的 Unicode 正则表达式，其中每段连续空白都匹配一个或多个空白字符；该子句通过 ctx.sessionQuery.filterEvents() 提供，不会委托给 FTS 提供方。
搜索语义
每个语义事件对应一份 FTS 文档，其中携带会话元数据、事件元数据、surface 分类与提取文本。除非 surface 过滤器缩小范围，否则所有 current、shadowed 与 log-only 文档都会参与搜索。元数据过滤器在排序前编译为参数化 SQL。会话结果按会话划分匹配文档，并保留匹配度最强的文档。
排序在持久化 FTS 表与 TEMP FTS 表之间具有确定性和可比性：先按实际 FTS5 高亮匹配区段数量降序，再按已索引文档的码点长度升序、事件时间降序、跨会话范围内的会话 id 升序，最后按 seq 降序排列。摘要片段使用这些实际高亮位置，移除保留标记、规范化空白，并按 Unicode 码点限制长度。不透明游标会绑定到服务实例、范围、规范化后的标准请求、偏移量与相关代际。语料库发生任何变更都会使跨会话游标失效；会话内游标仅在其目标源或代际发生变化时失效，因此不相关的会话不会使其失效。重新打开服务会创建新的服务实例，并使旧游标失效。
查询会先去除首尾空白并规范化内部空白，再作为一个字面 FTS5 短语整体加引号。嵌入的引号在绑定前写成两个，因此 OR、NEAR、引号、括号与 * 等 MATCH 运算符会作为数据，而不是可执行的查询语法。系统会在 SQLite 执行前拒绝 NUL。文档中的保留高亮非字符与 NUL 会在索引前规范化，因此插入的呈现标记不会与源文本冲突。短语匹配遵循分词器 token，而不是任意子串。
分词器选择
持久化 FTS5 表与实时 FTS5 表都使用 unicode61。实现实验表明，该分词器支持由两个字符组成的 token AI，生成的索引体积约为 trigram 方案的 1/2.1。系统接受的限制是 token/短语召回：AI 不会匹配较长的 token BRAID，任意子串搜索改用提供方无关的文本扫描。
提取与对齐
共享提取器会提取消息文本、推理（reasoning）、嵌套的工具调用/结果内容、工具名称与参数、被阻止提示词的原因、待办事项状态与内容，以及错误或结束状态详情。结构性边界、流分片、请求头、成功完成标记，以及通过声明合并扩展的未知事件/内容变体都不会产生文档。surface 分类复用 foldSurface()，使搜索与模型历史派生保持一致。
一个串行化操作会读取提供方无关的 SessionPersistence 快照清单，将每个包含源身份的不透明修订号与同已索引会话一并存储的修订号比较，只加载新增或变更的日志，在一个事务中对齐各行，然后执行查询。它将调用方传入的原始中止信号传给快照清单查询和不会修改状态的检查，直接等待每个已启动的后端操作，并在每次等待结束后、启动更多工作之前检查取消状态。因此，即使后端忽略该信号，取消也只会在活跃的后端工作完全停稳后才拒绝，不会启动后续观察或对齐步骤；后续搜索仍会串行等待清理完成。它绝不会调用后端会修改状态的 load() 来处理当前由 ctx.sessions 拥有的 id；TEMP 覆盖层会记录持久化可用性，实时所有者分离后，持久化基础层随之刷新。修订号同时标识其底层持久化存储与后端本地日志修订版本，因此针对同一存储重新打开服务可以复用已索引行，而切换到独立存储时不会因会话 id 与本地计数器相同而发生冲突。如果加载期间清单发生变化，系统会重复观察；因此，会修改状态的加载修复所产生的新修订号会在提交前纳入结果。重复查询与针对未变更存储的重新打开都不会加载完整的持久化日志。新增、变更与删除的会话会在下一次稳定搜索中更新。源读取或提取失败时，不能将相应行标记为最新状态，事务失败则会回滚，使后续搜索能够重试。
持久化文档在重启后仍然存在。实时会话使用连接本地的 TEMP 表，遮蔽相同 id 的持久化基础行，并在实时所有者分离时重新显露该基础行。关闭数据库会删除实时行。卸载持久化服务会隐藏持久化行，但不会把缺失视为权威删除；重新挂载后，系统会再次观察并对齐后端。实时会话头与持久化会话头的不可变字段发生冲突时，系统会失败，而不会合并两个来源。
派生 schema 拥有独立的 application id 与单调递增的 schema 版本。持久化与 TEMP 会话元数据均遵循 SessionHeader.createdAt 的整数约定，将其存入严格的 INTEGER 列。系统识别到不兼容版本时，只会重置该派生数据库。如果数据库具有不属于本应用的 application id 或无法识别的用户表，系统会在修改日志模式前拒绝该数据库，防止意外配置的规范会话数据库遭到修改。在 POSIX 文件系统上，缺失的目录与数据库文件会以仅所有者可访问的权限创建，使新的 SQLite 伴随文件沿用该模式；现有权限模式保持不变。一个进程中的一个服务独占一条派生索引路径；代际与实时 TEMP 遮蔽状态都归连接所有，因此不支持跨进程写入方。
取消会使排队中的操作及时被拒绝。异步源观察一旦开始，调用方必须等待该后端 Promise 结算后才会收到拒绝；系统不会提交已中止的观察结果，也不会启动更多源观察或索引工作。Node 的同步 DatabaseSync 元数据与 MATCH 调用一旦开始在 JavaScript 线程上执行就无法中断，因此服务会在这些调用前后检查信号，但不承诺在语句执行期间抢占。
曾考虑的替代方案
- 将 FTS 表添加到规范持久化数据库：不予采纳，因为可重建索引不应与权威日志共享 schema、重置边界或故障边界。
- 添加第一阶段的提供方注册表与协调器：不予采纳，因为单一实现无法证明注册语义，并且会将一个对齐生命周期拆给两个所有者。
- 立即持久化实时覆盖层：不予采纳，因为在现有检查点提交前，实时事件并非规范数据。
- 使用 FTS5 trigram 分词器：不予采纳，因为它会遗漏短于三个字符的有用查询，并且测得的索引体积约为
unicode61的 2.1 倍；扫描路径仍可提供字面子串过滤。 - 在每个表中独立使用 FTS5 BM25：不予采纳，因为填充内容不同的持久化语料库与 TEMP 语料库所产生的分数不可比较；实际匹配区段与文档长度采用同一套尺度。
后果
搜索只公开精简的提供方无关 API，而唯一后端负责派生索引的全部状态转换。独立数据库增加了配置与查询前的轻量快照读取，但索引损坏、重置与分词器变更都不会危及规范日志。持久化修订号使未变更会话无需读取或重写完整日志；TEMP 实时覆盖层保留当前会话事实，同时不会让尚未经过检查点的事件具有持久性。
选定的分词器以较小的索引体积支持短 token，但不承诺子串召回。字面短语使查询语法安全且可预测，代价是不支持布尔表达式或完整 MATCH 表达式。取消在操作排队期间会及时生效，在等待数据源期间则会等待其完全停稳；同步 SQLite 执行仍是不可抢占区段。
单元测试将以下行为固化为约定：提取、过滤器、两种搜索范围、所有默认 surface、先过滤元数据再排序、摘要片段、字面量转义、确定性平局处理、完整分页、按范围的游标失效、动态挂载/卸载持久化服务、重启对齐、实时遮蔽、显露与重新打开、schema 安全、回滚重试，以及排队中或进行中的数据源等待取消。一个无需密钥的真实 Loader 路径测试会将该包与真实的 SQLite 持久化后端组合使用。