Codex review: found issues before merge. Reviewed June 30, 2026, 2:28 PM ET / 18:28 UTC.

Summary
The PR flips active session metadata and transcript runtime storage from legacy JSON/JSONL files to per-agent SQLite, adds doctor/startup migration tooling, updates runtime/plugin callers, and refreshes docs/tests/proof harnesses.

Reproducibility: yes. from source inspection: explicit sessionFile targets remain valid, but the PR run path now invokes a SQLite-only runtime transcript truncation helper that returns without truncating non-SQLite targets; no tests were run because this was a read-only review.

Review metrics: 3 noteworthy metrics.

• Persistent schema tables: 7 added. The PR changes the per-agent stored data model, so maintainers need upgrade and migration confidence beyond green unit tests.
• Plugin runtime session helpers: 4 removed. Removing deprecated runtime helper methods changes a plugin-facing contract and needs explicit compatibility acceptance.
• Doctor migration mode: 1 added. A new operator-facing SQLite migration/fix mode affects how existing installations recover and upgrade.

Stored data model
Persistent data-model change detected: database schema: docs/refactor/database-first.md, database schema: src/state/openclaw-agent-db.test.ts, database schema: src/state/openclaw-agent-db.ts, database schema: src/state/openclaw-agent-schema.generated.ts, database schema: src/state/openclaw-agent-schema.sql, migration/backfill/repair: docs/channels/channel-routing.md, and 38 more. Confirm migration or upgrade compatibility proof before merge.

Root-cause cluster
Relationship: fixed_by_candidate
Canonical: #88838
Summary: this PR is the active implementation candidate for the canonical SQLite session/transcript migration tracker; older 3.2 foundation PRs are explicitly superseded and PR #98090 is an adjacent overflow fix that references this lane.

Members:

• canonical: Track core session/transcript SQLite migration via accessor seam #88838 - The tracker names this PR as the only outstanding Path 3 implementation lane.
• superseded: refactor: add SQLite session store foundation #89178 - The tracker says the old SQLite session store foundation split is superseded by this PR.
• superseded: refactor: add SQLite transcript identity adapter #90437 - The tracker says the old transcript identity adapter split is superseded by this PR.
• superseded: refactor: add SQLite embedded run target adapter #90438 - The tracker says the old embedded run target adapter split is superseded by this PR.
• partial_overlap: fix: recover precheck context overflows with measured counts #98090 - The overflow-token-count fix references this PR but solves a narrower precheck compaction measurement issue.

Proposal only: this assessment does not dispatch repair, suppress jobs, mutate sibling items, close, or merge anything.

Merge readiness
Overall: 🦐 gold shrimp
Proof: 🦞 diamond lobster
Patch quality: 🦐 gold shrimp
Result: needs maintainer review before merge.

Overall follows the weaker of proof and patch quality, so missing proof can cap an otherwise strong patch.

Rank-up moves:

• Restore explicit-file overflow truncation for non-SQLite sessionFile targets.
• Resolve the dirty merge state and refresh focused validation on the merge result.
• Get maintainer acceptance for the plugin runtime helper removal and forward-only SQLite migration behavior.

Mantis proof suggestion
A real Telegram conversation across the SQLite flip would demonstrate user-visible session continuity better than local row-count proof alone. A maintainer can ask Mantis to capture proof by posting this exact PR comment:

@openclaw-mantis telegram desktop proof: exercise an existing Telegram conversation across the SQLite session/transcript flip, send a follow-up message, and show the reply stays attached to the expected session with no stale JSONL dependency; redact private chat/user details.

Risk before merge

• [P1] Explicit session-file runs can keep oversized tool results after compaction because the new runtime-transcript truncation helper rejects non-SQLite targets instead of using the existing file-backed truncation path.
• [P1] The PR changes the persistent per-agent session/transcript data model and active runtime store, so merge requires maintainer acceptance of forward-only migration, archive-before-delete behavior, and upgrade proof.
• [P1] The PR removes deprecated plugin runtime session helper methods, which is an intentional compatibility-sensitive SDK/runtime contract cutover but still needs maintainer ownership before landing.
• [P1] The PR is currently dirty/conflicting against main, so the reviewed head needs a refreshed merge result and validation before it can be treated as land-ready.

Maintainer options:

1. Repair Explicit-File Overflow Recovery (recommended)
   Preserve the current file-backed truncation branch for explicit non-SQLite sessionFile targets before rerunning focused overflow and migration validation.
2. Accept The Storage/API Cutover
   Maintainers may intentionally accept the forward-only SQLite migration and deprecated runtime helper removals after reviewing the refreshed upgrade proof and docs.
3. Pause Or Split The Migration
   If the dirty merge state or API/storage cutover is too broad to review safely, pause this branch and split the remaining migration, SDK cleanup, or doctor work into narrower PRs.

@clawsweeper automerge

Special instructions:
Preserve file-backed explicit sessionFile overflow truncation when the runtime transcript target is not SQLite-backed, then rerun the focused overflow-compaction tests and migration proof commands relevant to this PR.

Next step before merge

• [P2] Protected labels, the dirty merge state, and storage/API compatibility decisions require maintainer handling even though the explicit truncation repair itself is mechanical.

Security
Cleared: The diff is security-sensitive because it migrates local session/transcript state and touches plugin/runtime surfaces, but no concrete supply-chain or security-boundary regression was found in the inspected files.

Review findings

• [P2] Preserve explicit session-file truncation fallback — src/agents/embedded-agent-runner/run.ts:2993-2999

Behavior proof for the storage flip follow-up at 7d4e7d5af0223ada5bdccc1e0d31d68399c2116f:

• Addressed the ClawSweeper SDK storage finding: readSessionTranscriptEvents, readLatestAssistantTextByIdentity, assistant mirror appends, write locks, and transcript update publication now use the SQLite accessor path for scoped transcript identities instead of reading/writing the legacy JSONL locator. The only file write fallback left is the explicit-file/no-session-key legacy path.
• Restored the public resolveAndPersistSessionFile SDK export and updated the accessor-boundary allowlist/test so this remains intentional.
• Preserved mirror append contracts: updateMode: "none" does not publish, idempotency replays do not republish, unkeyed delivery mirrors still dedupe by latest equivalent assistant text, and stale session bindings return session-rebound.

Live local migration proof against Josh's local OpenClaw store:

• Original pre-import backup exists at /tmp/openclaw-session-sqlite-preimport-20260625T030716Z.tar.gz.
• A later read-only validate found live drift after the first import, so I made a targeted row backup before re-importing: /tmp/openclaw-main-session-sqlite-targeted-20260625T040207Z.json (about 3.2 MB).
• Re-ran runDoctorSessionSqlite({ mode: "import", allAgents: true }): imported 58 legacy entries and 4547 transcript events. The only import-time issues were two pre-existing missing legacy JSONL transcript files.
• Re-ran runDoctorSessionSqlite({ mode: "validate", allAgents: true }): issues: 0, validatedEntries: 58, validatedTranscriptEvents: 4547, sqliteEntries: 315, targets: 47.
• Direct SQLite probe after validate:
  • main: 311 session entries, 36696 transcript events.
  • codex-proof: 2 session entries, 15 transcript events.
  • codex-image-proof: 2 session entries, 18 transcript events.

Local verification:

• node scripts/run-vitest.mjs src/plugin-sdk/session-transcript-runtime.test.ts -> 17 tests passed.
• node scripts/run-vitest.mjs src/plugin-sdk/session-transcript-runtime.test.ts src/config/sessions/session-accessor.conformance.test.ts src/config/sessions/session-accessor.test.ts -> 74 tests passed across 2 shards.
• node scripts/run-tsgo.mjs -p tsconfig.core.json --incremental --tsBuildInfoFile .artifacts/tsgo-cache/core.tsbuildinfo -> passed.
• node scripts/check-session-accessor-boundary.mjs -> passed.
• git diff --check -> passed.
• .agents/skills/autoreview/scripts/autoreview --mode local --base upstream/main -> clean, no accepted/actionable findings.

Thermonuclear review note: the behavior fixes are materially cleaner now, but the strict maintainability review still flags the large SQLite backend/test shape from the broader branch (session-accessor.sqlite.ts is over 2.5k LOC and the conformance test is over 1.5k LOC). I did not split that architecture in this follow-up because the active blocker was the SDK storage contract regression and the branch already had live migration proof.

@clawsweeper re-review

🦞🧹
ClawSweeper re-review requested.

I asked ClawSweeper to review this item again.
Action: item re-review queued (workflow sweep.yml, event repository_dispatch).
Result: the existing ClawSweeper review comment will be edited in place when the review finishes.

Superseded by #98236, which carries the same SQLite sessions/transcripts storage flip on canonical upstream branch sqlite/sessions-transcripts-storage-flip.