feat: add SQLite transcript frontier and delta API by 100yenadmin · Pull Request #79972 · openclaw/openclaw

100yenadmin · 2026-05-09T20:28:23Z

Summary

Implements the first raw public transcript replay seam needed for database-first companion consumers.

This PR adds a SQLite-explicit frontier and delta API on top of the canonical transcript_events table:

getSqliteSessionTranscriptFrontier(...)
loadSqliteSessionTranscriptDelta(...)

The new seam stays keyed to {agentId, sessionId}, returns raw transcript events, and makes rewrite invalidation explicit instead of pretending seq is a durable replay token forever.

Why this is better

This improves OpenClaw as a platform, not just for LCM:

companion or internal consumers can resume replay without full transcript reloads in append-only cases
same-session replacement no longer has to be guessed from private store logic
the contract is explicit about reset vs append semantics
the public seam uses canonical SQLite ownership instead of leaking JSONL/file-era identity

Scope

Included:

SqliteSessionTranscriptFrontier, SqliteSessionTranscriptCursor, and SqliteSessionTranscriptDelta
public frontier/delta exports on openclaw/plugin-sdk/session-store-runtime
targeted transcript-store tests plus a public subpath smoke test
plugin SDK API baseline refresh

Not included:

lineage/discovery policy from #79903
typed transcript projections from #79905
any companion-specific normalized table in OpenClaw core

Review Follow-up

This PR now keeps the replay seam additive and compatibility-preserving.

Fixes:

restored the legacy public helper names on plugin-sdk/session-store-runtime as compatibility adapters
removed raw SQLite handle exports from that public subpath
rerouted internal monorepo tests that still need direct DB access to the internal DB modules
added a monotonic per-session createdAt floor so same-millisecond full rewrites still force cursor reset

Why this was necessary:

documented public SDK exports should not disappear just because the runtime moved to SQLite
the replay seam should stay typed and narrow instead of widening into general raw DB access
same-session rewrites with identical eventCount and lastSeq still need a durable invalidation signal

Primary follow-up files:

src/plugin-sdk/session-store-runtime.ts
src/plugin-sdk/session-store-runtime.test.ts
src/config/sessions/transcript-store.sqlite.ts
src/config/sessions/transcript-store.sqlite.test.ts
extensions/memory-core/src/memory/index.test.ts
extensions/memory-core/src/memory/manager.fts-only-reindex.test.ts
extensions/qqbot/src/state-migrations.test.ts

Real Behavior Proof

Runtime proof from a direct module invocation on the PR branch:

{
  "frontier": {
    "sessionId": "session-1",
    "updatedAt": 100,
    "eventCount": 2,
    "lastSeq": 1,
    "baseCreatedAt": 100
  },
  "delta": {
    "mode": "reset",
    "frontier": {
      "sessionId": "session-1",
      "updatedAt": 101,
      "eventCount": 2,
      "lastSeq": 1,
      "baseCreatedAt": 101
    }
  }
}

That proof shows:

the first frontier is recorded normally
a same-millisecond replace with the same shape still bumps the rewrite marker
the next replay sees mode: "reset" instead of silently returning append/no-op

Validation

cd /Volumes/LEXAR/repos/openclaw-79904-cursor-frontier
OPENCLAW_TEST_PROJECTS_SERIAL=1 OPENCLAW_VITEST_NO_OUTPUT_TIMEOUT_MS=120000 \
  node scripts/run-vitest.mjs \
  src/config/sessions/transcript-store.sqlite.test.ts \
  src/plugin-sdk/session-store-runtime.test.ts

cd /Volumes/LEXAR/repos/openclaw-79904-cursor-frontier
OPENCLAW_TEST_PROJECTS_SERIAL=1 OPENCLAW_VITEST_NO_OUTPUT_TIMEOUT_MS=120000 \
  node scripts/run-vitest.mjs \
  extensions/memory-core/src/memory/index.test.ts \
  extensions/memory-core/src/memory/manager.fts-only-reindex.test.ts \
  extensions/qqbot/src/state-migrations.test.ts

cd /Volumes/LEXAR/repos/openclaw-79904-cursor-frontier
pnpm plugin-sdk:api:gen

Observed locally:

2 files passed, 16 tests passed
3 internal import-boundary files passed, 16 tests passed
plugin SDK API baseline regenerated cleanly

Stack placement

This PR is the first public replay seam in the companion-fit stack.

Related stack:

base refactor: openclaw/openclaw#78595
upstream companion umbrella: openclaw/openclaw#79902
tracked issue: openclaw/openclaw#79904
sibling lineage seam: openclaw/openclaw#79970
downstream consumer slice: Martian-Engineering/lossless-claw#646
later typed-projection follow-up: openclaw/openclaw#79905

Why this is separate:

replay frontier is the minimal upstream contract LCM needs before richer semantic mapping
OpenClaw still owns canonical runtime storage; LCM stays a downstream companion DB

Issue

Refs openclaw/openclaw#79904.

clawsweeper · 2026-05-09T20:31:49Z

Codex review: found issues before merge.

Summary
The branch moves session/transcript runtime state toward SQLite and adds public transcript frontier/delta helpers with focused tests and docs/API-baseline updates.

Reproducibility: Do we have a high-confidence way to reproduce the issue? Not applicable: this is a feature/API PR rather than a current-main bug; the blocking concern is source-visible on the PR head export surface.

Real behavior proof
Sufficient (live_output): The PR body now includes copied live output from a direct module invocation showing the fixed same-millisecond rewrite path returning mode: "reset".

Next step before merge
Protected security/API-boundary review is needed before deciding whether any transcript mutation helper belongs in the public plugin SDK.

Security
Needs attention: The diff still exposes a public transcript overwrite primitive, which needs maintainer/security approval or removal before merge.

Review findings

[P1] Keep transcript replacement internal — src/plugin-sdk/session-store-runtime.ts:42
[P3] Document the frontier cursor contract — docs/plugins/sdk-subpaths.md:204

Review details

Best possible solution:

Keep the public seam read-focused, document the cursor/reset contract, and leave transcript mutation behind an internal or explicitly trusted API boundary.

Do we have a high-confidence way to reproduce the issue?

Do we have a high-confidence way to reproduce the issue? Not applicable: this is a feature/API PR rather than a current-main bug; the blocking concern is source-visible on the PR head export surface.

Is this the best way to solve the issue?

Is this the best way to solve the issue? No: the frontier/delta direction is plausible, but publishing raw transcript replacement with the read seam is not the narrowest maintainable or safest SDK boundary.

Full review comments:

[P1] Keep transcript replacement internal — src/plugin-sdk/session-store-runtime.ts:42
session-store-runtime is a public plugin SDK subpath, but this export promotes replaceSqliteSessionTranscriptEvents, which deletes and rewrites canonical transcript rows for any caller-provided {agentId, sessionId}. The requested companion seam is read/replay-oriented, so keep transcript replacement in internal modules or behind an explicitly trusted API instead of giving every plugin a public overwrite primitive.
Confidence: 0.87
[P3] Document the frontier cursor contract — docs/plugins/sdk-subpaths.md:204
The public docs still describe this subpath as session-row/transcript-locator helpers even though the PR adds getSqliteSessionTranscriptFrontier and loadSqliteSessionTranscriptDelta with append/reset/missing modes. Before publishing the SDK surface, document what baseCreatedAt, eventCount, and lastSeq mean and when consumers must reset.
Confidence: 0.78

Overall correctness: patch is incorrect
Overall confidence: 0.86

Security concerns:

[medium] Avoid public transcript overwrite access — src/plugin-sdk/session-store-runtime.ts:42
Exporting replaceSqliteSessionTranscriptEvents from openclaw/plugin-sdk/session-store-runtime gives plugins a broad way to delete and replace canonical transcript rows, beyond the stated read-side replay API.
Confidence: 0.86

What I checked:

Protected label: Live PR metadata and provided context show the protected security label, so cleanup closure is not allowed and the API boundary needs maintainer/security handling. (699af7238b5c)
Current public SDK contract: Current main's plugin-sdk/session-store-runtime exposes legacy session store/path/session-key helpers, not SQLite transcript replay or transcript mutation helpers. (src/plugin-sdk/session-store-runtime.ts:3, 749dc78b8d74)
PR public export surface: The PR head exports getSqliteSessionTranscriptFrontier, loadSqliteSessionTranscriptDelta, loadSqliteSessionTranscriptEvents, and also replaceSqliteSessionTranscriptEvents from the public SDK subpath. (src/plugin-sdk/session-store-runtime.ts:38, 699af7238b5c)
Replacement mutates canonical transcript rows: replaceSqliteSessionTranscriptEvents deletes all rows for a session and reinserts caller-provided events, which is broader than a read-side replay seam. (src/config/sessions/transcript-store.sqlite.ts:558, 699af7238b5c)
Follow-up proof: The PR body and follow-up comment now include copied runtime output showing same-millisecond replacement returns mode: "reset" with baseCreatedAt: 101. (699af7238b5c)
Docs mismatch: The PR docs still summarize this public subpath as session-row/transcript-locator helpers, without documenting the new frontier/delta cursor modes or reset semantics. Public docs: docs/plugins/sdk-subpaths.md. (docs/plugins/sdk-subpaths.md:204, 699af7238b5c)

Likely related people:

steipete: Current main's public session-store-runtime SDK subpath and docs line are attributed to Peter Steinberger's recent commit, and the related SQLite runtime refactor is also authored under this area. (role: recent maintainer / adjacent owner; confidence: high; commits: 18aeac86a101; files: src/plugin-sdk/session-store-runtime.ts, docs/plugins/sdk-subpaths.md, src/config/sessions)

Remaining risk / open question:

Publicly exporting transcript replacement lets plugins overwrite canonical transcript rows until maintainers explicitly approve that SDK/data-integrity boundary.
The branch is dirty against current main and very large, so merge readiness remains separate from the narrow API review findings.

Codex review notes: model gpt-5.5, reasoning high; reviewed against 749dc78b8d74.

100yenadmin · 2026-05-09T20:50:27Z

Architecture fit for this slice:

flowchart TD
    A["canonical transcript_events"] --> B["frontier(agentId, sessionId)"]
    B --> C["cursor"]
    C --> D["delta load"]
    D --> E["append"]
    D --> F["reset"]
    D --> G["missing"]
    D --> H["future companion consumers"]

Why this slice exists:

file offsets and mtime are not a durable replay contract for a database-first runtime
consumers need a typed, public way to resume replay from canonical SQLite transcript ownership
explicit append vs reset semantics are safer than asking every consumer to infer rewrite behavior from raw rows

Why it was chosen before typed projections:

LCM first needs a replay boundary, not a fully normalized projection layer
this is the smallest public seam that unblocks SQLite-native frontier work downstream

Stack placement:

companion umbrella: #79902
child issue: #79904
downstream consumer slice: Martian-Engineering/lossless-claw#646
later follow-up: #79905

Lossless Claw connection:

this is the direct upstream seam that lets LCM replace JSONL mtime / size / offset bootstrap logic
LCM can now resume from a canonical SQLite cursor while keeping its own richer DB separate

100yenadmin · 2026-05-09T21:19:19Z

Follow-up on the live review findings:

addressed the compatibility-export regression
addressed the rewrite-invalidation gap
pushed in 699af7238b
updated the PR body with direct runtime proof and refreshed validation counts

What changed:

restored the legacy public helper names on plugin-sdk/session-store-runtime as compatibility adapters
kept the new replay seam additive
removed raw DB-handle exports from that public subpath
added a monotonic per-session createdAt floor so same-session rewrites still force reset
rerouted internal monorepo tests that still need direct DB access to the internal DB modules

Runtime proof is now in the PR body and shows:

a first frontier at baseCreatedAt: 100
a same-millisecond replace with the same transcript shape
a follow-up replay result of mode: "reset" with baseCreatedAt: 101

This keeps the public seam narrow, compatibility-preserving, and durable enough for SQLite-native replay consumers like LCM.

steipete added 30 commits May 9, 2026 18:01

test: read compaction successors from sqlite

429e1dd

test: read truncated transcripts from sqlite

5ac8878

refactor: append cli hook blocks through sqlite

a522ac0

test: read gateway compaction checkpoints from sqlite

6b7c14b

test: seed embedded e2e transcripts through sqlite

67d38b9

test: seed memory qa sessions through sqlite

cbc1fbb

test: seed commitment qa session through sqlite

2bebadf

test: seed stale subagent sessions through sqlite

b058082

test: seed active memory state through sqlite

80c5b35

test: assert crestodian audit through sqlite

cfc3687

test: seed commitment qa state through sqlite

12aa05b

refactor: store config audit in sqlite

80e2508

refactor: move restart handoff state to sqlite

1f845f7

refactor: store node host config in sqlite

b7cd488

refactor: store msteams delegated tokens in sqlite

1157ce6

refactor: store workspace setup state in sqlite

47b8d2a

refactor: store exec approvals in sqlite

3aa2381

refactor: clear restart sentinels from sqlite

8bf1da3

refactor: store config health in sqlite

1fc4e4c

refactor: stop reading legacy workspace state

6cd133a

refactor: allocate fresh sqlite transcript sessions

fc65a5e

refactor: compare transcript locators without filesystem probes

3cfa97f

docs: align trajectory capture with sqlite runtime

15bcd5c

refactor: keep legacy state migration in doctor path

2fff5bd

refactor: trim unused state migration logger

3d73a4e

refactor: log sqlite checkpoint failures

ec56cf5

refactor: drop legacy transcript path builders

79e5e67

refactor: stop exporting legacy session transcript dirs

f192db2

refactor: remove transcript jsonl import helper

767d407

refactor: parse sqlite transcript topics centrally

f172fc9

This was referenced May 9, 2026

fix: tighten SQLite runtime truth in session refactor #79971

Open

feat: expose durable session id match selection helpers #79970

Open

feat: add SQLite-aware bootstrap frontier Martian-Engineering/lossless-claw#646

Open

fix: preserve session runtime compatibility exports

699af72

clawsweeper Bot added the proof: sufficient ClawSweeper judged the real behavior proof convincing. label May 9, 2026

openclaw-barnacle Bot removed the proof: sufficient ClawSweeper judged the real behavior proof convincing. label May 9, 2026

100yenadmin mentioned this pull request May 10, 2026

feat: add typed SQLite transcript projections and rebuild helpers 100yenadmin/openclaw#1

Open

BradGroux mentioned this pull request May 10, 2026

WORKING: All Microsoft Issues and PRs (refresh) #74163

Draft

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

feat: add SQLite transcript frontier and delta API#79972

feat: add SQLite transcript frontier and delta API#79972
100yenadmin wants to merge 409 commits intoopenclaw:mainfrom
100yenadmin:feat/openclaw-79904-cursor-frontier

100yenadmin commented May 9, 2026 •

edited

Loading

Uh oh!

clawsweeper Bot commented May 9, 2026 •

edited

Loading

Uh oh!

100yenadmin commented May 9, 2026

Uh oh!

100yenadmin commented May 9, 2026

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Uh oh!

Conversation

100yenadmin commented May 9, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Summary

Why this is better

Scope

Review Follow-up

Real Behavior Proof

Validation

Stack placement

Issue

Uh oh!

clawsweeper Bot commented May 9, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

100yenadmin commented May 9, 2026

Uh oh!

100yenadmin commented May 9, 2026

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

100yenadmin commented May 9, 2026 •

edited

Loading

clawsweeper Bot commented May 9, 2026 •

edited

Loading