SQLite physical chunk-row compression
SQLite 物理分片行压缩
The scalar [`session-persistence-sqlite`](../../../../packages/session/session-persistence-sqlite/README.md) layout stores one physical row per logical `SessionEvent`. Provider streams produce token-sized `assistant/chunk` events with repeated turn, step, block, type, and envelope fields, so transaction batching reduces commits without reducing row count or repeated JSON payload. The logical stream cannot be coalesce
English
Problem
The scalar session-persistence-sqlite layout stores one physical row per logical SessionEvent. Provider streams produce token-sized assistant/chunk events with repeated turn, step, block, type, and envelope fields, so transaction batching reduces commits without reducing row count or repeated JSON payload. The logical stream cannot be coalesced because chunk boundaries, sequence numbers, timestamps, replay, partial output, UI fidelity, and sourceEventSeqs remain observable.
A physical row that represents several events affects append contiguity, crash repair, suffix seeks, schema ownership, revisions, and stale writers. Durable decoding must also be fixed by the schema version; a configurable codec set could make one schema version unreadable under a different Cordis composition.
Decision
@deepseek-ai/dsh-session-persistence-sqlite uses the packed schema-17 implementation. It is the only SQLite persistence package and provider; the predecessor scalar layout and the temporary versioned sibling are not retained. SQLite remains an opt-in switch, while shipped default compositions continue to use JSONL. Both backends implement the same SessionPersistence service through PersistenceCoordinator, so physical packing changes neither live event delivery nor the logical session API.
Schema 17 keeps ordinary ROWID tables and the composite events(session_id, seq) primary-key index. Scalar rows represent one logical event. Packed rows use the storage tags text-chunks, reasoning-chunks, and tool-call-chunks; the SQL seq and time columns hold the first logical member, and data holds the packed payload. Packed rows set ignorable=0 as a physical discriminator and leave source_event_seqs and surface_op as NULL; scalar rows use ignorable=1 only for logical ignorable events and NULL otherwise. A future ignorable logical event may therefore reuse a storage-tag name without being decoded as a packed row. The tags are storage vocabulary, not SessionEventMap members.
SQLite owns chunk encoding and validation inside the schema-17 package. Exact-field whitelisting means unknown fields, surface metadata, incompatible chunk identity, sequence gaps, and unsafe timestamps remain scalar rather than losing information. One packed row represents at most 1,024 events and 1 MiB of uncompressed UTF-8 data; the encoder partitions longer runs, and the decoder rejects rows outside those format limits.
The data column accepts TEXT or BLOB. Serialized values below 4 KiB remain text. At or above the threshold, the writer uses Zstandard level 3 and retains the frame only when it is smaller than the text; the reader decompresses the blob before strict UTF-8 decoding and JSON parsing. The fixed moderate level and threshold limit frame overhead and synchronous CPU work while capturing the repeated payloads that dominate retained bytes.
source_event_seqs remains the complete ordered list of earlier events cited by a surface node, including every streamed chunk behind an assembled assistant message. Schema 17 stores the first sequence as an unsigned varint and every subsequent signed difference as a ZigZag varint. This preserves arbitrary order and every sequence while exploiting the overwhelmingly consecutive lists produced by streaming. An empty list is an empty non-null blob, distinct from absent provenance.
Transactional append packing
Each append acquires BEGIN IMMEDIATE, rechecks schema ownership, selects the bounded physical span that may cover the last stored sequence, and derives the next logical sequence from that decoded tail. A mismatch rejects a stale writer before mutation. The codec packs only the new durable batch. Its inserts, lazy session materialization, and one revision increment commit or roll back together.
Normal append never deletes or replaces an earlier event row. Fixed write-behind windows normally collect high-frequency deltas into useful runs, while sparse or explicitly flushed batches may remain scalar. This makes physical event writes proportional to newly durable batches and prevents a stable retained-row count from hiding repeated replacement of a growing JSON value.
Reads and repair
Full reads decode each physical row as one all-or-nothing logical span and validate contiguous logical sequences. A reverse pass identifies the last valid turn/end without retaining a second decoded copy of the full physical scan; the forward pass decodes one row at a time into the required logical result. A malformed row or gap before that committed boundary is corruption; a malformed final physical row becomes the opaque repair marker at that row's base sequence. Recovery re-reads and validates that marker while holding the write lock, then deletes the whole physical row and any later rows before binding synthetic closers as scalar events. A stale repair cannot delete a newer writer's valid suffix.
readFrom(id, fromSeq) examines packed predecessors only within the maximum schema-17 row span, then reads from the earliest candidate that may contain fromSeq. The decoder filters reconstructed members below fromSeq, so a suffix may begin inside a packed row without parsing an unrelated earlier scalar row. Reading from that candidate also exposes an overlapping scalar row to contiguity validation instead of letting it hide the packed member. Packed data exceeding the uncompressed format byte limit rejects before JSON parsing.
Schema ownership
A pristine database initializes at schema 17. Older physical schemas, foreign application identities, non-pristine unversioned databases, and incompatible schema objects reject; the pre-release package supplies no migration. Every connection disables trusted schemas and memory-mapped I/O before inspecting durable schema, then reads both settings back. After selecting and verifying the journal mode, the provider pins synchronous=FULL and verifies it so SQLite build defaults cannot weaken committed-append durability. Package code loads every statement and fixed pragma from closed-name .sql resources and binds runtime values as parameters.
Physical-write regression
The repository regression guard writes 1,000 streamed deltas in 40-event durable batches. After every committed batch it compares every retained physical field, requires cumulative inserts to equal the final row count, and rejects changed or removed rows. It also checks the exact 31-row bound, the largest persisted record against the schema byte limit, and an idle interval with no WAL extent change. These checks prove bounded row structure and catch coarse write amplification; they do not establish device traffic because WAL frames can be overwritten in place and checkpoints also write the main database. Incident-class validation separately samples process physical bytes around active and idle periods and stresses synchronized multi-process access. Lock tests hold BEGIN IMMEDIATE in another process and verify bounded waiting and successful continuation.
Alternatives considered
Coalesce logical chunk events. Rejected because it changes sequence references, replay, partial output, and live delivery. Physical records provide the storage reduction while restoring the authoritative log exactly.
Run a periodic or post-commit compactor. Rejected because it adds another writer lifecycle, races append and repair, changes revisions without a logical append, and adds disposal work.
Merge each new batch into the prior packed tail. Rejected because a stable database and row count can hide repeated delete-and-insert churn. Paced-stream measurement found higher process and WAL writes than the predecessor scalar layout even when the retained database was smaller. Batch-local packing gives up timing-independent row convergence to bound physical writes.
Use synchronous=NORMAL with WAL. Rejected because it permits a recent committed transaction to roll back after an operating-system crash or power loss. append() resolves only after its batch is durable, so the provider explicitly retains SQLite's FULL durability level across builds.
Remove ROWID from events. Rejected because the composite text/integer primary key then becomes the table B-tree key and is repeated through internal pages. On the 105-session comparison corpus, selective Zstandard with ordinary ROWID used 107.02 MB; the otherwise equivalent WITHOUT ROWID database used 126.75 MB.
Set a larger SQLite page size. Rejected because the retained-size change was negligible: 4 KiB pages used 107.08 MB and 32 KiB pages used 106.89 MB in the layout reconstruction. The larger page also increases WAL-frame and cache granularity. The provider therefore issues no page_size pragma.
Compress every payload. Rejected because small independent Zstandard frames add headers and synchronous CPU work while losing the cross-record dictionary opportunity of a whole-file stream. On the 105-session comparison corpus, a threshold sweep produced 75.01 MB at 4 KiB, versus 93.87 MB at 16 KiB and 60.92 MB at 1 KiB. The writer fixes level 3 rather than inheriting a library default, matching the moderate level used by Codex cold-rollout compression while retaining independent row access.
The final frozen comparison used 105 sessions, 2,507,860 logical events, 512-event durable batches, three independent builds per backend, and three read passes per build. SQLite used 75.01 MB, wrote in 8.58 s, read complete sessions at 3.95/21.58 ms p50/p95, read 50-event tails at 0.253/0.378 ms, and forked every session in 13.10 s. Zstandard JSONL used 30.65 MB and measured 28.21 s, 4.49/23.36 ms, 10.58/80.90 ms, and 14.48 s. The predecessor scalar SQLite layout used 709.57 MB and measured 10.64 s, 9.02/69.16 ms, 0.189/0.293 ms, and 19.30 s. The packed layout is 89.4% smaller than the predecessor, writes 19.4% faster, improves complete-read p50/p95 by 56.2%/68.8%, and reduces 2,507,860 physical event rows to 65,810. Scalar tail-50 and list micro-latency are lower, but the packed provider remains materially faster than JSONL on those paths and wins the dominant size, write, full-read, and fork costs. The 4 KiB threshold is the accepted balance rather than a strict dominance claim.
Store packed payloads under the logical assistant/chunk type. Rejected because payload heuristics make malformed rows ambiguous and couple physical decoding to future logical payload fields. Explicit tags fail loudly.
Store SessionHeader fields in an extensible metadata blob. Rejected for schema 17 because agentPreset is a typed core resume invariant shared by JSONL and SQLite, not provider extension metadata. Persisting validated core fields directly keeps both backends aligned; an untyped catch-all would add another compatibility mechanism without a current producer. Revisit this only with a core-owned, namespaced SessionHeader extension protocol implemented by every backend.
Expose compression rules through configuration or a live registry. Rejected because same-version databases must be readable independently of runtime topology. The codec is modular source code, but the durable rule set is fixed by schema version.
Migrate older schemas in place. Rejected under the pre-release policy. Changing strict column types requires rebuilding the event table, which turns the first append into an unbounded historical rewrite and temporarily duplicates storage. A new database keeps activation explicit and failure predictable.
Store forked history as a parent reference. Deferred because it changes independent-session persistence rather than physical row encoding. Codex uses referenced history and excludes referenced or pointer-bearing rollouts from cold compression, but this provider would first need explicit parent retention, deletion, repair, export, and cross-backend semantics. Copying remains the bounded local choice until the session service owns those rules.
Keep the packed implementation as a versioned sibling. Rejected because the pre-release repository has no compatibility promise for the scalar format, while two SQLite package names duplicate configuration, documentation, tests, and ownership. Historical benchmark artifacts retain the comparison without exposing a rollback provider.
Consequences
The canonical SQLite provider preserves every logical persistence, replay, revision, crash-recovery, and model-facing behavior. High-frequency batches use fewer rows and fewer measured process disk-written bytes than the predecessor in paced-stream validation; idle samples add no measured writes. Packing ratio depends on durable batch boundaries, but previously committed rows are immutable outside explicit crash repair.
The cost is no migration from older pre-release SQLite schemas and timing-dependent physical row count. SQLite and Zstandard remain synchronous: each connection uses the configured busyTimeoutMs for a competing lock and blocks its JavaScript thread during that wait, while large row encoding and decoding also run on that thread. A cold open yields after an immediate SQLITE_BUSY journal-mode transition and starts no further attempt after an open-relative retry cutoff; an in-progress synchronous call may finish later. External SQL tooling must use the provider decoder rather than assuming every physical events.type is a logical event type or every payload column is text.
The JSONL packed-row decision, bounded persistence batching, and original session-persistence decision remain active: they respectively own the JSONL format, write scheduling, and backend-neutral service semantics.
中文
问题
标量 session-persistence-sqlite 后端为每个逻辑 SessionEvent 存储一个物理行。提供方流会生成 token 大小的 assistant/chunk 事件，并重复轮次、步骤、块、类型和 envelope 字段，因此事务批处理可以减少提交次数，却不能减少行数或重复 JSON payload。逻辑流不能合并，因为分片边界、序列号、时间戳、回放、部分输出、UI 保真度和 sourceEventSeqs 仍然可观察。
一个表示多个事件的物理行会影响追加连续性、崩溃修复、后缀定位、schema 所有权、revision 和陈旧写入方。持久解码规则还必须由包版本固定；可配置 codec 集可能导致同一 schema 版本在不同 Cordis 组合下无法读取。
决策
@deepseek-ai/dsh-session-persistence-sqlite 使用打包后的 schema 17 实现。它是唯一的 SQLite 持久化包和提供方；仓库不保留此前的标量布局与临时版本化同级包。SQLite 仍是可选开关，随产品交付的默认组合继续使用 JSONL。两个后端都通过 PersistenceCoordinator 实现同一 SessionPersistence 服务，因此物理打包既不改变实时事件投递，也不改变逻辑会话 API。
Schema 17 保留普通 ROWID 表以及复合主键索引 events(session_id, seq)。标量行表示一个逻辑事件。打包行使用存储标签 text-chunks、reasoning-chunks 与 tool-call-chunks；SQL 的 seq 和 time 列保存第一个逻辑成员，data 保存打包 payload。打包行把 ignorable=0 用作物理判别值，并让 source_event_seqs 与 surface_op 保持 NULL；标量行仅在逻辑事件可忽略时使用 ignorable=1，否则使用 NULL。因此，未来的可忽略逻辑事件即使复用了某个存储标签名称，也不会被解码为打包行。这些标签属于存储词汇，而不是 SessionEventMap 成员。
SQLite 在 schema 17 包内拥有分片编码和验证。字段完全匹配的白名单意味着未知字段、surface 元数据、不兼容的分片身份、序列缺口和不安全时间戳仍保持标量表示，不会丢失信息。一个打包行最多表示 1,024 个事件和 1 MiB 未压缩 UTF-8 data；编码器会分割更长的连续段，解码器则拒绝超出这些格式上限的行。
data 列接受 TEXT 或 BLOB。序列化值小于 4 KiB 时保持为文本。达到或超过该阈值时，写入方使用 Zstandard level 3，并且只在 frame 小于原文本时保留该 frame；读取方会先解压，再进行严格 UTF-8 解码和 JSON 解析。固定的适中级别与阈值限制 frame 开销与同步 CPU 工作，同时覆盖占据大部分保留字节的重复 payload。
source_event_seqs 是 surface 节点引用的早期事件的完整有序列表，包括组装后的 assistant 消息背后的每个流式分片。Schema 17 把第一个序列存为无符号 varint，把后续每个有符号差值存为 ZigZag varint。这样既能保留任意顺序和每个序列，又能利用流式处理所产生的绝大多数连续列表。空列表表示为空的非 NULL blob，与不存在来源区分开来。
事务化追加打包
每次追加会获取 BEGIN IMMEDIATE、重新检查 schema 所有权、选择可能覆盖最后存储序列的有界物理范围，并根据解码后的尾部推导下一逻辑序列。若不匹配，系统会在变更前拒绝陈旧写入方。Codec 只打包新的持久批次；其插入、会话惰性物化和一次 revision 递增会一起提交或回滚。
普通追加绝不删除或替换既有事件行。固定写后缓冲窗口通常会把高频 delta 收集成有效连续段，而稀疏或显式 flush 的批次可能保持标量形式。这样，物理事件写入量与新增持久批次成正比，稳定的保留行数无法再掩盖对不断增长 JSON 值的反复替换。
读取与修复
完整读取把每个物理行解码为全有或全无的逻辑范围，并验证逻辑序列连续。反向扫描会定位最后一个有效 turn/end，但不会保留完整物理扫描的第二份解码副本；正向扫描则逐行解码并写入必需的逻辑结果。在该已提交边界之前出现的畸形行或缺口属于损坏；畸形最终物理行则以该行的起始序列作为不透明修复标记。恢复会在持有写锁时重新读取并验证该 marker，再删除整个物理行及其后所有行，然后把合成 closers 绑定为标量事件。陈旧修复无法删除较新写入方的有效后缀。
readFrom(id, fromSeq) 只检查 schema 17 最大行跨度内的打包前驱，再从可能包含 fromSeq 的最早候选项开始读取。解码器会过滤重建后序列小于 fromSeq 的成员，因此后缀可以从打包行内部开始，而无需解析无关的更早标量行。从该候选项开始读取，还会让连续性验证看到相互重叠的标量行，而不是让它隐藏打包成员。打包数据超出未压缩格式字节上限时，会在解析 JSON 前拒绝。
Schema 所有权
全新数据库初始化为 schema 17。旧物理 schema、外部 application identity、非空未版本化数据库以及不兼容 schema 对象都会被拒绝；该预发布提供方不提供迁移。每个连接都会在检查持久 schema 前禁用可信 schema 和内存映射 I/O，然后读回这两项设置。选择并验证 journal mode 后，提供方会把 synchronous 固定为 FULL 并验证该设置，避免 SQLite 构建默认值削弱已提交追加的持久性。包代码通过封闭名称的 .sql 资源加载每条语句和固定 pragma，并把运行时值作为参数绑定。
物理写入回归
仓库回归守卫以 40 个事件为持久批次写入 1,000 个流式 delta。它会在每个批次提交后比较所有保留物理字段，要求累计插入数等于最终行数，并拒绝发生变化或被移除的行。它还会检查精确的 31 行上限、最大持久记录不超过 schema 字节上限，并观察空闲区间内 WAL 范围不再变化。这些检查证明行结构有界并捕获粗粒度写放大；它们不能证明设备写流量，因为 WAL 帧可在原位覆写，检查点还会写入主数据库。事故级验证另行采样活动期和空闲期前后的进程物理写入字节，并对同步多进程访问进行压力测试。锁测试在另一个进程中持有 BEGIN IMMEDIATE，验证有界等待及之后成功继续。
考虑过的替代方案
合并逻辑分片事件。 不予采用，因为它会改变序列引用、回放、部分输出和实时投递。物理记录可以在准确恢复权威日志的同时获得存储缩减。
运行周期性或提交后压缩器。 不予采用，因为它会增加另一个写入方生命周期，与追加和修复竞争，在没有逻辑追加的情况下改变 revision，并增加资源释放工作。
把每个新批次合并进已有打包尾部。 不予采用，因为稳定的数据库与行数可能掩盖反复删除和插入产生的写入流量。节奏化流测量表明，即使保留数据库更小，该方案写入的进程字节与 WAL 字节仍高于此前的标量布局。逐批打包放弃与时序无关的行收敛，以换取有界物理写入。
在 WAL 模式下使用 synchronous=NORMAL。 不予采用，因为操作系统崩溃或断电后，最近提交的事务可能回滚。append() 只会在批次持久化后返回，因此提供方会在不同 SQLite 构建中显式保留 FULL 持久性级别。
从 events 移除 ROWID。 不予采用，因为复合文本／整数主键随后会成为表 B-tree 的键，并在内部页中重复。在 105 个会话的对比语料上，使用普通 ROWID 的选择性 Zstandard 数据库为 107.02 MB；其余条件相同的 WITHOUT ROWID 数据库为 126.75 MB。
设置更大的 SQLite page size。 不予采用，因为保留体积变化可以忽略：在独立的 page-size 布局重建中，4 KiB page 使用 107.08 MB，32 KiB page 使用 106.89 MB。更大的 page 还会增大 WAL frame 和 cache 粒度。因此提供方不设置 page_size pragma。
压缩每个 payload。 不予采用，因为小型独立 Zstandard frame 会增加 header 和同步 CPU 工作，也无法利用整文件流的跨记录字典。在 105 个会话的对比语料上，阈值扫描结果为：4 KiB 生成 75.01 MB，16 KiB 为 93.87 MB，1 KiB 为 60.92 MB。写入方固定使用 level 3，而不是继承库默认值；这与 Codex 冷 rollout 压缩所用的适中级别一致，同时保留独立行访问。
最终冻结对比包含 105 个会话、2,507,860 个逻辑事件，以 512 个事件为持久批次；每个后端独立构建三次，每次构建执行三轮读取。SQLite 使用 75.01 MB，写入耗时 8.58 秒，完整读取 p50/p95 为 3.95/21.58 毫秒，读取最后 50 个事件为 0.253/0.378 毫秒，对所有会话执行 fork 为 13.10 秒。Zstandard JSONL 使用 30.65 MB，对应指标为 28.21 秒、4.49/23.36 毫秒、10.58/80.90 毫秒和 14.48 秒。此前的标量 SQLite 布局使用 709.57 MB，对应指标为 10.64 秒、9.02/69.16 毫秒、0.189/0.293 毫秒和 19.30 秒。打包布局比此前布局小 89.4%，写入快 19.4%，完整读取 p50/p95 改善 56.2%/68.8%，并把 2,507,860 个物理事件行减少到 65,810 行。标量布局的最后 50 个事件读取与 list 微延迟更低，但打包提供方在这些路径上仍明显快于 JSONL，并改善主要的空间、写入、完整读取和 fork 成本。4 KiB 阈值是接受的平衡点，而不是严格支配所有指标的结论。
把打包 payload 存在逻辑 assistant/chunk 类型下。 不予采用，因为 payload 启发式判断会使畸形行产生歧义，并把物理解码耦合到未来逻辑 payload 字段。显式标签会明确失败。
把 SessionHeader 字段存入可扩展元数据 blob。 Schema 17 不采用该方案，因为 agentPreset 是 JSONL 与 SQLite 共同使用的强类型核心恢复不变量，而不是提供方扩展元数据。直接持久化已校验的核心字段可使两个后端保持一致；在没有当前生产方的情况下加入无类型兜底字段，只会增加另一套兼容机制。只有核心层定义由所有后端实现、带命名空间的 SessionHeader 扩展协议后，才应重新考虑该方案。
通过配置或实时注册表暴露压缩规则。 不予采用，因为同一版本数据库必须能独立于运行时拓扑被读取。Codec 在源码层保持模块化，但持久规则集由 schema 版本固定。
原地迁移旧 schema。 预发布策略不采用此方案。改变 strict 列类型需要重建事件表，这会把第一次追加变成无界的历史改写，并暂时复制存储。使用新数据库可让启用行为明确、失败方式可预测。
把 fork 历史存为父级引用。 延期处理，因为它改变的是独立会话持久化语义，而不是物理行编码。Codex 使用引用历史，并避免对被引用或带指针的 rollout 做冷压缩；但该提供方首先需要明确父级保留、删除、修复、导出和跨后端语义。在会话服务拥有这些规则之前，复制仍是有界的本地选择。
把打包实现保留为版本化同级包。 不予采用，因为预发布仓库不承诺兼容此前的标量格式，而两个 SQLite 包名会重复配置、文档、测试和所有权。历史 benchmark 产物保留对比，无需暴露回滚提供方。
后果
标准 SQLite 提供方保留每一项逻辑持久化、回放、revision、崩溃恢复和模型可见行为。在节奏流验证中，高频批次使用的行数和测得的进程磁盘写入字节少于此前布局；空闲样本没有新增测得写入。打包率取决于持久批次边界，但除显式崩溃修复外，已经提交的行保持不可变。
代价是不迁移旧的预发布 SQLite schema，以及取决于时序的物理行数。SQLite 与 Zstandard 都是同步操作：每个连接以配置的 busyTimeoutMs 等待竞争锁，该等待期间会阻塞其 JavaScript 线程，大型行的编码与解码也在该线程上执行。冷打开会在 journal-mode 切换立即返回 SQLITE_BUSY 后让出执行，并在从打开时计算的重试截止点后不再发起新尝试；正在执行的同步调用可能更晚才完成。外部 SQL 工具必须使用提供方解码器，而不能假定每个物理 events.type 都是逻辑事件类型或每个 payload 列都是文本。
JSONL 打包行决策、有界持久化批处理和原始会话持久化决策继续保持 active：它们分别负责 JSONL 格式、写入调度以及后端无关的服务语义。