openclaw - 💡(How to fix) Fix [Feature]: Add cursored SQLite transcript read API for companion consumers [4 pull requests]

Summary

Add a cursored SQLite transcript read API with frontier metadata to openclaw/plugin-sdk/session-store-runtime.

Related:

• umbrella: #79902
• active refactor: #78595

Problem to solve

The database-first runtime already has the right canonical raw-event store, but serious consumers still need to load entire transcripts or reach into deeper internal modules to do incremental work safely.

That is fine for one-off reads, but it is not a good public contract for:

• companion databases that need resumable ingest
• export/audit/index features that should resume from a durable frontier
• high-volume or long-lived sessions where full reloads are wasteful

The current implementation already has most of the raw material:

• transcript stats in transcript-store.sqlite.ts
• append sequencing in transcript-store.sqlite.ts
• message append with idempotency and parent linkage in transcript-store.sqlite.ts
• full replacement in transcript-store.sqlite.ts
• full ordered read in transcript-store.sqlite.ts

The public SDK layer currently exports only full load and full replace in session-store-runtime.ts.

Proposed solution

Extend openclaw/plugin-sdk/session-store-runtime with a small, public read seam for canonical SQLite transcript rows:

• cursored read by {agentId, sessionId}
• resume from afterSeq or afterEventId
• bounded page size / limit support
• companion-friendly frontier stats such as maxSeq, eventCount, updatedAt, and last durable event identity
• cursor semantics that are robust to full replace and same-session rewrite paths, not just append-at-tail

Suggested semantics:

• reads are ordered by ascending seq
• cursor inputs are scope-local to one {agentId, sessionId}
• frontier metadata reflects canonical persisted state, not caller-local cache state
• read seam stays event-first and does not reintroduce path or locator identity
• the durable replay contract should not treat seq or created_at alone as sufficient proof of continuity once replacement/rewrite paths exist

This should stay a read-side API over the canonical transcript store. It should not reintroduce file-era transcript locators or JSONL compatibility into runtime paths.

Alternatives considered

1. Full transcript loads only

This is simple but forces consumers to invent their own incremental frontier and replay rules.

2. Let consumers inspect internal gateway/runtime modules

This works short-term for one consumer, but it is not a stable ecosystem boundary.

3. Expect consumers to infer frontier state from replacement behavior

This is especially fragile because replaceSqliteSessionTranscriptEvents(...) currently deletes and reinserts all rows for a session in transcript-store.sqlite.ts. Consumers should not have to reverse-engineer safe replay behavior around that.

Impact

Affected:

• future memory/index/export/audit companions
• internal tools that want resumable transcript processing

Severity:

• medium to high for correctness and scale

Frequency:

• common for any serious consumer that ingests transcripts incrementally

Consequence:

• duplicated replay/frontier logic across consumers
• harder to trust replay after reset, compaction, or rotation

Platform value:

• enables native database-backed indexers, searchers, exporters, and audit tools
• reduces replay cost on large or long-lived sessions
• keeps advanced consumers on supported SDK surface instead of private modules

Evidence/examples

Current public surface is centered on full event load/replace, while the runtime already maintains enough information to make incremental reads viable.

This issue intentionally asks for a generic read seam, not a plugin-specific special case.

Representative anchors:

• canonical transcript schema: openclaw-agent-schema.sql
• stats helper not exported publicly: transcript-store.sqlite.ts
• current SDK export surface: session-store-runtime.ts
• full replace renumbers a session from seq=0: transcript-store.sqlite.ts
• same-session rewrite appends a new active-branch suffix instead of mutating rows in place: transcript-rewrite.ts

Additional information

Acceptance scenarios:

• resume after reset/rotation without missing or duplicating events
• cursoring works after compaction/checkpoint activity
• populated installs do not require full transcript reloads for resumable companion ingest
• tool-heavy transcripts preserve stable event ordering and durable replay identity
• replay remains correct after logical rewrites that invalidate raw seq continuity

Non-goals:

• file-path or sqlite-transcript://... compatibility
• companion-specific normalized tables in core