Repository navigation
[Task]: Qualify SQLite D2 capacity and crash/replay recovery on the merged provider #4224
Description
Task ID or area
Shared authority RFC D2 / section 7.2: first SQLite capacity and recovery qualification slice after #4121.
Technical direction
Shared Goal Authority and cross-host coordination
Intent
I am proposing a bounded follow-up task and would like to own it after maintainer scope confirmation.
Summary
#4121 is merged. At main e602dd9273ff08a7c392129989366ac9be696cae, SQLite supplies the opt-in AuthorityStore conformance and real CLI path. D2 remains open: the existing examples/coordination/sqlite-capacity.ts measures a fixed 4 KiB payload at 10k/100k commits, but does not exercise section 7.2's matched 64 KiB profile or report all required latency, growth, recovery and consumer evidence.
The store retains complete historical projections and verifies cursor continuity with a covering-index count. This is an identified qualification boundary, not a demonstrated budget failure. The next useful deliverable is a reproducible measurement and recovery slice over this implementation, before selecting a checkpoint/delta redesign.
Source-backed preflight on this main: 50 SQLite conformance/admission tests and 5 real Python-to-TypeScript SQLite CLI tests passed on Node 22.14.0. A disposable 64 KiB, 100/1,000-commit rehearsal preserved the original receipt and ordered full-projection scan and verified cleanup. That rehearsal does not qualify the RFC's 10k/100k profile, concurrency, recovery matrix or elapsed-time horizon.
Proposed scope
In scope:
- Extend the existing capacity helper into an explicitly invoked, reproducible D2 measurement entrypoint. Keep a small rehearsal mode separate from the 64 KiB 10k/100k matched profile; use fresh disposable databases, fixed live-state dimensions, explicit read/write mix and comparable sample windows. Represent workload settings, source/provider revision and missing evidence in the result.
- Measure p50/p95/p99 and sample counts for commits, head loads, original-receipt lookup and 100-transaction historical scans. Separate warm-process store calls from cold subprocess/CLI reads. Report runtime/database/durability settings, RSS and database/WAL size. Final WAL size is not cumulative WAL traffic or logical writes; unmeasured metrics must remain unknown and prevent a full-pass claim.
- Reuse the shared production-scale coordination fixture for a representative real CLI/readback arm, separately from the fixed-byte storage axis. Verify historical receipts/projections, operation-digest conflict behavior and cursor ordering against independently authored expectations, including after reopen and intervening commits.
- Extend the existing real SQLite/process tests with the missing bounded interrupted-transaction and storage-capacity failure cases. Verify no lost acknowledged commit, no partially published head/receipt/outbox, and no duplicated effect on retry. Distinguish real process interruption, SQLite capacity injection and actual filesystem exhaustion; do not label an injected SQLite limit as an OS disk-full test.
- Produce a compact qualification ledger mapping each section 7.2/D2 requirement to passed, failed or missing evidence. A runner can be correct while the provider remains unqualified. Keep large runs opt-in and ordinary CI focused on durable correctness and report validity.
Out of scope:
- No provider default change, new store, Todo/lease rule change, checkpoint/delta schema redesign, retention deletion, or migration API in this first slice.
- No active user Goal mutation, live file-to-SQLite migration, promotion, PostgreSQL deployment or scheduled soak.
- The 1 MiB/300k headroom axes, full mixed-writer recovery/lag coverage and actual >=10-day synthetic soak remain explicit D2 requirements. This first slice must not mark D2 complete while they are absent.
- No raw run dumps, host-local paths, private data or credentials in commits. Only reusable helper/test code and compact public-safe evidence belong in the PR.
Placement and dependency rationale
Owner: the existing built-in shared-goal coordination domain and TypeScript AuthorityStore contract; provider: the merged opt-in sqlite_v0. Reuse examples/coordination/, existing provider tests and shared fixtures. No new capability, extension, storage engine or semantic owner is needed.
D2 is independent of the T1/T2 transaction work and PostgreSQL deployment. No open PR touching the SQLite store or shared store-conformance helper was found in this preflight. Keep T1–T3, D1 and D3 approval/cutover gates independent of this evidence.
Intended base branch
main, refreshed again after admission, using a dedicated clean codex/ worktree.
Relevant files or commands
examples/coordination/sqlite-capacity.tstests/control_plane_ts/sqlite_authority_store.test.tstests/control_plane_ts/sqlite_authority_process.tstests/control_plane_ts/authority_store_conformance.tstests/control_plane_ts/production_scale_coordination_fixture.tstests/control_plane/test_sqlite_authority_cli.pytests/control_plane/canonical_authority_fixture.pyloopx/control_plane/coordination/sqlite_authority_store.ts(behavior under qualification)docs/architecture/rfcs/shared-goal-authority-state-provider-v0.md, section 7.2 and D2
Validation plan
- Real SQLite conformance, recovery/process and public CLI tests, with isolated synthetic runtime state.
- A small deterministic rehearsal verifies runner execution and report completeness; independent negative tests reject missing/failed required evidence instead of deriving pass criteria from observed timings.
- An opt-in 64 KiB 10k/100k matched run records budgets without relaxing the RFC. Preserve failed measurements; do not average away a failing axis.
npm run typecheck:control-plane,npm run test:control-plane, affected CLI tests, exact-pathloopx check,git diff --check, and risk-basedloopx canary premerge --from-git-diff.- If a discovered defect requires changing shared transaction/provider semantics, narrow and confirm that repair separately, including all affected real-provider suites.
Requested maintainer confirmation
Please confirm this first measurement/recovery slice and its ownership. I will keep tracked implementation on hold until the scope is admitted; any storage redesign will follow the measured gap rather than be bundled speculatively.
Public/private boundary
- This issue contains no private benchmark traces, verifier output, credentials, internal links, raw sessions or local runtime state.
- This proposal does not launch maintainer-owned experiments or a scheduled soak.
Activity
huangruiteng commentedon Sep 12, 2026
@LIHUA919 确认由你继续负责 SQLite D2。感谢你完成 #4121，也欢迎把这条线持续推进到一个有完整证据、可以提交本地 profile 晋升评审的阶段，而不只停在第一份测量报告。你提出的“先测量现有实现，再根据缺口决定设计”很合适。
建议分成几个可独立评审、但共享同一验证入口的完整批次：
main，完成 64 KiB、10k/100k 的匹配负载；保留小型 CI rehearsal。分别报告 cold CLI / warm store 的 p50/p95/p99、样本数、RSS、锁等待和数据库/WAL 大小。逻辑写入量、累计 WAL 流量、最终文件大小分开，不可互相替代。用表格明确每项 RFC 条件是 passed、failed 还是 missing。AuthorityStore，不要另建 provider 或第二个业务语义 owner。历史scanCommitted必须仍能返回精确完整 projection，原 operation/digest/receipt 在重启和无关提交后仍可验证。本次确认允许你开始第一批实现，并继续承担 D2 的后续闭合；不要求把所有内容塞进一个 PR。发现真实缺陷应保留失败证据并修复，不要为了“全绿”缩小负载或放宽预算。默认 provider 切换、活跃 Goal 迁移、D3 promotion 和 PostgreSQL 服务部署仍分别评审，不由 D2 测试通过自动授权。
避免撞车：我们会把另一条重构放到永久投影交付/相关 typed owner，暂不推进 SQLite checkpoint 格式草稿。当前还有 #4283、#4286、#4287 的 authority/fixture 工作，请在修改共同文件前核对最新合并状态，尤其 #4287 的 journal scan 校验，尽量复用已有合同。
请先回复首批的实施顺序与预计交付节点；有证据支持时可以主动推进后续批次，并及时把设计决策、结果和阻塞点同步到这里。期待把 SQLite 的这条长程存储主线完整做扎实。
LIHUA919 commentedon Sep 20, 2026
@huangruiteng 收到，按批次 1 推进。以下是首批实施顺序与预计节点，并同步当前长线状态：
实施顺序（批次 1：测量与恢复切片，基于最新 main 的
loopx_sqlite_authority_store_v2保留层）预计节点：测量代码 + rehearsal/账本 PR 目标 2026-09-21 前送审；正式运行证据在其后 72 小时内贴回。批次 1 不标记 D2 完成：1 MiB/300k 余量、并发 CAS/响应丢失恢复矩阵、backup/restore lineage、受支持 runtime/OS 矩阵与 >=10 天自然时间 soak 仍为 missing 或独立评审。
长线状态同步：10 天 synthetic soak 已于 2026-09-14 在一次性 goal 上启动（pinned
e98191faa，Node 22.22.3/SQLite 3.51.3，8 agents；当前 ~20.2k commits、2777 ticks、无 recovery 事件，minimum_end 2026-09-25）。批次 1 的正式测量与该 soak 同机并行，负载互不重叠（soak 为每分钟级轻量 tick），并将在报告 host 节披露这一并发负载。撞车核对：#4283/#4286/#4287 均已合并；#4328 之后的 checkpoint/delta 保留层已在 main，本次测量直接基于它。
docs(rfc): record the first matched 64 KiB storage-traffic evidence
LIHUA919 commentedon Sep 20, 2026
@huangruiteng 批次 1 的测量能力 PR 与正式 matched-64k 运行证据如下。
测量能力 PR：#4778（
codex/sqlite-d2-batch1-metrics，含 runner/report/ledger/负例测试/参考文档与 RFC 证据段落更新；typecheck、容量测试 6/6、store 套件 176/176、全量 control-plane 套件仅 7 个与改动无关的预存失败、loopx check边界干净、canary premerge通过）。正式运行：matched-64k（10k + 100k 独立一次性库，各 +1000 提交 WAL 窗口 + 12 样本锁探针 + 20 样本 ×3 冷 CLI 命令），参考运行时 Node 22.22.3 / SQLite 3.51.3，WAL + synchronous=FULL 未为测量改动；分支 head
f5b41f451。同机并行一个轻量 10 天 soak（每分钟级 tick），已按约定披露。报告原文保留在忽略的本地存储，以下为全部资格账本与关键数字。三项分离测量（10k → 100k，每提交）
最终文件大小独立分列：DB 51.7MB / 515.8MB，WAL 归零（clean close），三列互不替代。方法边界如实标注：整跑 WAL 总量、busy-handler 内部计时、物理设备写入仍为 missing。
完整资格账本（passed 14 / failed 0 / missing 9）
其他关键观测：填充 183.5s / 1,825.6s（预算 2,400s）；RSS 峰值 171→226MB（不随历史 10x 线性放大，但按账本语义仍不构成 steady-state 证明）；recovery tail 15/31 commits、replay budget 63；两轴 cleanup 验证通过。
结论：64 KiB 匹配轴上全部 14 项预算通过，写入放大按设计线性（每提交逻辑 ~70KB 中 head 全量重写占 65.5KB —— 这是批次 2 存储成本闭合时最明显的优化对象）；D2 不标记完成，9 项 missing 保持 hold。下一步按既定批次推进批次 2/3（1 MiB+300k 余量、真实进程恢复矩阵、backup/restore lineage）。
LIHUA919 commentedon Sep 22, 2026
@huangruiteng 批次 2 开工说明（存储成本闭合 —— 余量轴测量）。
依据：批次 1 实测 64 KiB 匹配轴全部 14 项预算通过、写入放大按设计线性（logical 10.00x / WAL 10.01x，均 ≤15x），无需要先修的违反。"根据证据闭合存储成本"的下一步是把同一测量臂推进到 RFC 7.2 的余量维度，看违反是否在更大 payload 或更深历史处出现。
交付（一个 PR，仍只含 helper/测试/文档，不改 provider 语义与阈值）：
--profile matched-1m：1 MiB live projection，10k/100k 匹配负载；读/写、WAL 窗口、锁探针、冷 CLI 臂与已合并的 64 KiB profile 完全同构，只有 payload 维度不同，保证跨轴可比。--profile headroom-64k：64 KiB、单报告内 100k→300k 双轴（30 天余量轴）；增长预算按 3x 深度比 × 每提交流量评估（per-commit 放大 5x 以内仍过、超出即 failed），fill 预算按轴规模同比放大并在报告 budgets 里声明。one_mib_*/headroom_300k_*），预算矩阵不变；负例延续"三列不可互相替代、窗口无效即 missing"的语义。payload_and_headroom行改为按报告实际覆盖的轴声明缺失面。结论规则（按你批次 2 的要求）：若 1 MiB/300k 出现预算违反，先在本 issue 贴精简修复方案（含持久格式兼容/回滚合同）再开实现 PR；若无违反，余量行转 passed、存储成本线以"按证据无需修复"闭合，剩余 hold 只剩恢复矩阵与自然时间证据。
运行计划：正式运行仍在本机参考运行时（Node 22.22.3 / SQLite 3.51.3）执行、与 soak 并行并披露（soak 现 ~37k commits，minimum_end 2026-09-25）。1 MiB 报告预计 ~1 小时、300k 报告预计 ~2 小时，完成后把资格账本贴回。
test(sqlite): extend capacity profiles to the 1 MiB and 300k headroom…
LIHUA919 commentedon Sep 23, 2026
SQLite D2 批次 2 的正式结果与修复方案如下。先记录失败，再提交实现 PR；本评论不申请 provider 晋升。
正式测量与账本。
matched-1m（1 MiB，10k→100k）和headroom-64k（64 KiB，100k→300k）均完成了声明的独立一次性库、样本窗口、冷 CLI、WAL 窗口和清理。两份报告的测量源码均为e99a83b63c149685625ecc80e5065c31b2ad36d6、source-stable=true，macOS arm64、Node 22.22.3 / SQLite 3.51.3、WAL + synchronous=FULL；存储介质未采集。用已合并 #4910 的4a9729d603fe4fa85ba6e5526991df788171858e精确 profile 账本重算后，行状态、观测值和预算均与原报告一致。其余 D2 缺口（真实 domain workload、steady-state RSS、large-history recovery、consumer lag、restore/upgrade/rollback、自然时间 soak、OS/runtime、promotion）仍按各自证据保留 missing；两个报告不合并成“D2 通过”。
定位。 SQLite
readReceipt从覆盖该笔的 checkpoint 重放并证明最多 64 行；scanCommitted(100)证明返回页和 lookahead。v2 的每行证明均对完整投影重复做 canonical JSON 编码与 SHA-256。1 MiB 固定投影下，原始 SQL 回执查找 p95 < 0.01 ms、101 行读取 p95 < 1 ms；独立真实 SQLite 小样本重现回执窗口末端约 300 ms、scan 100 约 500 ms。CPU 样本主要落在规范字节生成。该证据支持读取证明成本为主要原因；同机负载可能影响绝对数值，不能用它抹去正式失败。拟议修复（先限 v2 读取证明，不动持久格式）。 在单次历史验证／审计调用内，对重复的长 JSON 字符串复用有上限的规范编码；每笔仍按原始 v0
commit_digest证明完整投影／事件／回执，并逐行校验 state digest。后续空 delta 可继承已经验证的前驱 state digest，但仍与本行声明的 state digest、parent lineage 对照；根提交仍独立计算。缓存不跨调用，不以旧验证结果授权新读取。DDL、schema version、磁盘行、原始 receipt/digest、writer、默认 provider 和 RFC 阈值均不变；回滚只需撤回这次读路径改动，前后 v2 数据互相可读，无迁移或清理历史的步骤。本地隔离原型在相同 Node 22 的 1 MiB / 128 笔真实 SQLite 小样本上，将固定投影的窗口末端回执约 302→43 ms、scan 100 约 503→98 ms；动态改变投影的回执约 302→63 ms，仍超过 50 ms，因此不将小样本等同于 1 MiB 通用资格。协议摘要等价、篡改历史行与空根等负例已加入验证；#4910 的错误深度准入负例继续保留。当前候选源码的 SQLite TS 回归为沙箱内 292/296，余下四项因本地 runtime socket 被沙箱阻断而在隔离实环境 4/4 通过；迁移／保留／容量 19/19、真实 Python CLI 5/5、TypeScript 类型检查通过。正式同负载复测仍须以实现 PR 的精确 head 报告。若正式复测仍失败，保留 failed，并另行评审支持范围或持久证明格式，不通过改预算或缩小样本消除失败。
huangruiteng commentedon Oct 3, 2026
Current-main diagnostic checkpoint, not a replacement for the frozen D2 reports.
Source
d74554b48758ce484633c9088af85c55143630af, macOS arm64, Node 24.21.0 / SQLite 3.53.4, WAL/FULL. Disposable synthetic stores only; source remained unchanged and all stores/processes were cleaned up.examples/coordination/sqlite-capacity.ts --profile rehearsal --cliagainst the source-checkout Python environment. Both 64 KiB axes (100 and 1,000 commits) passed their functional assertions: original history audit, canonical Todo in status/quota, three real CLI mutations per axis and final cursor readback. The report correctly remains incomplete, not D2-qualified.performance-diagnosis, points at retained-window SHA-256 work: hash-update leaf samples account for roughly 77% of receipt-path and 84% of scan-path observed CPU weight. These are sample weights, not uninstrumented latency. The payload-size intervention supports a byte/proof-cost hypothesis; it does not justify dropping integrity checks or changing a persistent digest format.Next useful work remains split at the existing boundaries: diagnose fresh CLI/Effect startup on an independent current-release control; preserve the common replay and complete proof contract for large historical reads; obtain the completed soak and missing recovery/consumer evidence separately. The shadow-capture
drain-cursoris a pre-promotion source-capture cursor, so its tests alone cannot certify a post-promotion lagged projection consumer. No new consumer framework, timeout relaxation, provider-default change or live migration was introduced by this checkpoint.Same-source replication in a clean dedicated worktree (64 KiB first, then 1 MiB) again preserved complete readback/history. Window-tail receipt p95 was 8.27/11.34 ms at nominal 64 KiB and 64.31/125.47 ms at nominal 1 MiB (unchanged/changing scalar); scan-100 at 1 MiB was 128.50/243.39 ms. Run-to-run variation reinforces that these are diagnostic observations on a shared machine, not stable formal tail or release-baseline comparisons.
huangruiteng commentedon Oct 3, 2026
Follow-up: separate cold CLI cost from SQLite retention cost
This follows the previous current-source probe. It is diagnostic evidence, not a new D2 pass or a change to admission thresholds.
d74554b48758ce484633c9088af85c55143630af; installed-source control:9ac4efa902c50b0d52348513eae807da323bd8cd(all 1,473 tracked LoopX Python/TS/JSON files matched this revision; generated assets/dependencies were outside that comparison). Same Python 3.13.13 / Node 24.21.0 / SQLite 3.53.4, macOS arm64, sequential execution on a shared host.Important measurement correction:
sqlite-capacity.tsinvokes status/quota without an explicit scan root. Those commands default to scanning the LoopX package directory, so the elapsed time includes public-boundary discovery/classification as well as imports, Effect startup and backend work. The initial 48 cold/warm calls retained those defaults; their different source/package trees must not be treated as an identical-input source regression comparison.A follow-up fixed the same complete package scan input across all four arms: 1,275 files, no boundary errors or warnings, 24 fresh-Python/fresh-Effect calls in alternating source/provider order. No scan was disabled and no timing threshold was relaxed. Three samples per cell are diagnostic only:
These results retain the slow observations. They do not establish statistical equivalence, a regression, p95, or satisfaction of the formal cold-status budget. Host load was variable; source/provider selection alone does not explain the latency. This fixture is not the production-complex Goal or formal large-history matrix.
Independent local-only profiles explain where to investigate next:
Next boundary: reuse #5283's existing lazy-loading/compilation work and qualify cold CLI separately from its long-lived MCP reuse result. Preserve the complete scan/decision contract; do not mask costs by dropping checks or trimming inputs. Keep the existing large-receipt proof-cost result, actual elapsed soak, recovery/lagged-consumer evidence and installed adoption open. No additional provider abstraction or parallel Python decision owner is justified by this probe.
中文：这轮把“命令整体慢”和“SQLite 历史读取慢”拆开了。容量演练的 CLI 时间还包含整份 LoopX 包扫描，之前不能直接归因给 SQLite。固定同一批 1,275 文件后，两种 provider 都有秒级公共成本；完整投影与原始回执核对通过，但小样本和共享主机波动不支持新的准入结论。后续复用 #5283 的既有冷路径工作，单独验证 CLI；不删检查、不放宽预算、不重复造缓存或决策层。