feat(storage): migrate remaining stores to SQLite and remove the dual-track writer

[Astro-Han]

Problem

Maka's durable storage is mid-migration and currently dual-tracked:

• Already SQLite (canonical, but gated): runtime_events (runtime.sqlite) and session_metadata + agent-graph tables (sessions.sqlite).
• Still file/JSONL: run headers and the AgentRunEvent stream (FileAgentRunStore, writeAtomic + appendJsonl), interaction, shell-run, task-ledger, artifact, pricing, automation, message-receipt, plan, deep-research (all File*Store).
• The sqliteCanonical switch is conditional: CLI uses agentGraphEnabled || MAKA_RUNTIME_SQLITE_CANONICAL=1; Desktop uses only the env flag and defaults to JSONL for runtime events.

On a shared root, Desktop (JSONL) and CLI (SQLite) can diverge and rely on import-on-open to reconcile. This is a migration state, not a steady state, and it is the kind of debt that gets harder to remove as users accumulate.

Desired outcome

One SQLite authority is the single canonical writer for every structured durable store. Specifically:

• run header, AgentRunEvent stream, interaction, shell-run, task-ledger, artifact, pricing, automation, message-receipt, plan, and deep-research are backed by SQLite;
• the sqliteCanonical switch and every legacy JSONL / file write path are removed;
• a one-time, idempotent import-on-open remains so existing roots migrate their legacy data into SQLite without losing sessions;
• CLI, Desktop, and Headless all use the same SQLite-backed store implementations;
• the existing importLegacyRuntimeEventJsonlTree and importLegacySessionMetadataTree importers are kept (and extended to the newly migrated stores) as the only legacy read path.

This is a breaking change to storage internals. It is acceptable now while the user base is small; it gets harder later.

Open questions to resolve in this issue

1. Session transcript bodies. #1370 deliberately left StoredMessage transcript payloads as JSONL (non-goal). Should this issue override that and move transcript bodies into SQLite too, or keep them as the one remaining append-only file store? Decide with a concrete plan for compaction, large-text row storage, and DB size/vacuum if moving them.
2. Single workspace database. runtime.sqlite and sessions.sqlite are two separate SQLite authorities today. #1370 already flagged "avoid two independently coordinated SQLite authorities." Decide whether the remaining stores extend one of these or a single renamed workspace-state database, and document the cutover.

Proposed scope

• Add SQLite schemas and transactional read/write APIs for each remaining store, preserving current API semantics for callers.
• Remove the sqliteCanonical flag and all ongoing JSONL / file write paths for migrated data.
• Extend the one-time import-on-open to each newly migrated store; keep it idempotent, restart-safe, and fail-closed on corrupt input.
• Wire CLI, Desktop, and Headless through the same SQLite-backed implementations.
• Resolve the two open questions above.

Acceptance criteria

• run header, AgentRunEvent, interaction, shell-run, task-ledger, artifact, pricing, automation, message-receipt, plan, and deep-research read/write through SQLite.
• The sqliteCanonical switch and all legacy JSONL / file write paths for migrated data are removed.
• Existing roots import their legacy data into SQLite once, idempotently, and remain readable after restart.
• CLI, Desktop, and Headless share one SQLite-backed store implementation per domain.
• Crash/failpoint tests cover migration and multi-row mutations for each migrated store.
• Open questions (transcript, single-DB) are decided and documented.
• No production dual-writer ambiguity remains for migrated data.

Non-goals

• Solving the multi-open ownership race. Two processes sharing one SQLite DB still produce double terminal facts, double turns, and broken ledger invariants, because that is an application-layer ownership problem, not a storage problem. That is tracked by the Runtime Host work (#853, #1167).
• Changing the RuntimeEvent or Tool Journal schema semantics.
• Remote/distributed storage or cloud CAS.

Context

• #1370 / #1371 established the SQLite metadata foundation and explicitly deferred transcript bodies and the single-DB question.
• This issue closes the remaining dual-track debt while the user base is small.

简体中文

问题

Maka 的持久化存储处于迁移中途，目前是双轨：

• 已 SQLite（canonical，但有开关）： runtime_events（runtime.sqlite）和 session_metadata + agent-graph 表（sessions.sqlite）。
• 仍是文件/JSONL： run header 与 AgentRunEvent 流（FileAgentRunStore，writeAtomic + appendJsonl），以及 interaction、shell-run、task-ledger、artifact、pricing、automation、message-receipt、plan、deep-research（全是 File*Store）。
• sqliteCanonical 开关是条件性的： CLI 用 agentGraphEnabled || MAKA_RUNTIME_SQLITE_CANONICAL=1；Desktop 只认 env，默认仍走 JSONL。

同一 root 上 Desktop（JSONL）和 CLI（SQLite）会分叉，靠打开时 import 对齐。这是迁移态，不是稳态，且越晚越难删。

期望结果

单一 SQLite authority 作为所有结构化持久 store 的唯一 canonical writer：

• run header、AgentRunEvent 流、interaction、shell-run、task-ledger、artifact、pricing、automation、message-receipt、plan、deep-research 都走 SQLite；
• 删除 sqliteCanonical 开关和所有遗留 JSONL / 文件写路径；
• 保留一次性、幂等的 import-on-open，让老 root 的遗留数据迁进 SQLite、不丢会话；
• CLI、Desktop、Headless 统一用同一套 SQLite-backed store 实现；
• 现有的 importLegacyRuntimeEventJsonlTree 和 importLegacySessionMetadataTree 保留（并扩展到新迁的 store）作为唯一遗留读路径。

这是存储内部的 breaking change。趁现在用户少可以做，越晚越难。

需在本 issue 内解决的 open question

1. session transcript 正文。 #1370 有意把 StoredMessage transcript 正文留 JSONL（列为 non-goal）。本 issue 要不要覆盖该决定、把正文也搬进 SQLite，还是保留它作为唯一剩下的 append-only 文件 store？若搬，需给出 compaction、大文本行存、DB 体积/vacuum 的具体方案。
2. 单一 workspace DB。 当前 runtime.sqlite 和 sessions.sqlite 是两个独立 SQLite authority。#1370 已提"避免两个独立协调的 SQLite authority"。决定剩余 store 是扩展其中一个、还是用单个重命名的 workspace-state DB，并记录切换方式。

范围

• 为每个剩余 store 增加 SQLite schema 与事务读写 API，保持对外 API 语义不变。
• 删除 sqliteCanonical 开关和所有已迁数据的 ongoing JSONL / 文件写路径。
• 把一次性 import-on-open 扩展到每个新迁 store；保持幂等、可重启、对损坏输入 fail-closed。
• CLI、Desktop、Headless 统一走同一套 SQLite-backed 实现。
• 解决上述两个 open question。

验收标准

• run header、AgentRunEvent、interaction、shell-run、task-ledger、artifact、pricing、automation、message-receipt、plan、deep-research 经 SQLite 读写。
• sqliteCanonical 开关和所有已迁数据的遗留 JSONL / 文件写路径已删除。
• 老 root 的遗留数据一次性、幂等导入 SQLite，重启后仍可读。
• CLI、Desktop、Headless 每个域共享同一套 SQLite-backed store 实现。
• crash/failpoint 测试覆盖每个新迁 store 的迁移与多行 mutation。
• 两个 open question（transcript、单 DB）已决定并记录。
• 已迁数据不再有 production 双写歧义。

不在范围

• 解决多开 ownership 竞态。两个进程共享同一 SQLite DB 仍会产生双终态、双 turn 和破裂的 ledger 不变量，因为这是应用层 ownership 问题，不是存储问题。由 Runtime Host 工作跟踪（#853、#1167）。
• 改变 RuntimeEvent 或 Tool Journal schema 语义。
• 远程/分布式存储或云 CAS。

背景

• #1370 / #1371 建立了 SQLite metadata 基础，并明确推迟了 transcript 正文与单 DB 问题。
• 本 issue 在用户少时清掉剩余双轨债。

[likun666661]

I ran a read-only Graph planning pass against current main. The direction in this issue looks right, but I recommend treating it as an umbrella and landing it as a sequence of reviewable cutovers rather than one large storage PR.

Recommended architecture decisions

• Use the existing runtime.sqlite under stateRoot as the unified operational database instead of introducing a third database name. Import sessions.sqlite into it and retire the latter after validation.
• Introduce one OperationalStateStore / database owner. It should own the connection lifecycle, pragmas, schema migrations, transactions, backup/snapshot API, and repositories. Pointing independently opened stores at the same SQLite file is not sufficient to create one transactional authority.
• Keep the stateRoot / configRoot boundary explicit. Session/execution state, usage/pricing state, and artifact metadata belong under stateRoot. Credentials, model connections, settings, skills, device identity, and automation configuration remain configRoot; automation should either stay out of this operational migration or move in a separate config-database issue.
• Keep StoredMessage transcript bodies as append-only JSONL for now, but make them compatibility/import-export material rather than a second structured authority. Runtime facts, run state, receipts, projections, and metadata should be SQLite-only.
• Move artifact metadata and lifecycle/index state to SQLite, but keep artifact payload bytes as files. Store validated relative paths, size/digest/content type, ownership, and timestamps in SQLite.

Proposed PR sequence

PR 1 — remove the current RuntimeEvent writer divergence

This should be the smallest first PR:

• make openRuntimeEventPersistence always create/open runtime.sqlite;
• import legacy RuntimeEvent JSONL idempotently before returning;
• remove sqliteCanonical, the CLI graph/env gating, and the Desktop-specific choice;
• route ordinary CLI, TUI, Desktop, and Headless through the same SQLite RuntimeEvent factory;
• retain JSONL codecs only for import/export and prove that normal writes no longer touch legacy JSONL.

This eliminates the immediate dual-writer risk without waiting for every other store migration.

PR 2 — establish the single operational DB owner and cutover protocol

• add the shared DB owner/factory and schema migration registry;
• add a durable cutover_journal containing store name, source identity/hash, state (started/completed), timestamps, and validation result;
• move/import the existing session metadata tables from sessions.sqlite into runtime.sqlite;
• require repositories to use the owner-provided transaction boundary rather than opening their own connections.

PR 3 — migrate core execution state

Migrate session metadata, AgentRun headers/events/projections, root-turn admissions/proofs, interaction, message receipts/host epochs, and shell-run records. Update execution-stores.ts so Headless readers/writers use the same repositories as CLI/Desktop. Preserve public store APIs and error semantics during the cutover.

PR 4 — migrate workflow and artifact metadata stores

Migrate task ledger, plan, deep-research, pricing/usage state, and artifact metadata. Keep artifact payloads in the existing artifact tree and make filesystem publication recoverable against the SQLite metadata transaction. Also explicitly classify currently omitted stores such as agent mailbox and plan reminders so “all structured durable stores” has a closed scope.

PR 5 — bundle, export, import, and backup

Replace the current special handling of runtime.sqlite plus protection of sessions.sqlite with a transactionally consistent export API. A selected-session bundle should contain only that session’s relational closure plus referenced artifact payloads. Full backup should use a WAL-safe SQLite snapshot and a verified artifact manifest; configRoot secrets must remain excluded.

PR 6 — remove legacy production writers

After every domain has completed cutover:

• delete production constructors and write paths for migrated file stores;
• keep narrow read-only import/export codecs for the announced support window;
• reject newer SQLite schema versions without mutation;
• document that in-place downgrade after new SQLite writes is unsupported: recovery is restore the automatic pre-cutover backup or use a copied/separate state root.

Cutover invariant

Each import should run under the canonical storage-root writer lease plus a SQLite transaction:

1. record started with source hash/fingerprint;
2. decode and validate all source data fail-closed;
3. import with unique idempotency keys;
4. validate row counts and identity/key sets;
5. atomically record completed.

An unchanged completed source is a no-op. A changed legacy source after cutover should fail closed and require an explicit repair path; it must not silently overwrite newer SQLite state. Crash/failpoint tests should cover every transition.

The highest-risk gates are RuntimeEvent/receipt loss or duplication, cross-session leakage in filtered bundles, Headless recovery parity, artifact metadata/payload crash windows, and backup/downgrade behavior.