OpenClaw SQLite stable-to-main audit

openclaw-sqlite-audit-report.md

OpenClaw SQLite audit: v2026.7.1-2 to audit snapshot

Audit date: 2026-08-25

Updated: 2026-08-27

Scope

• Latest full stable release: v2026.7.1-2
• Stable commit: 0790d9f593ad30c940ed93b5872a8cf6d6f3cf8c
• Audit snapshot: f000fdb3174f88da24f6fc1e1702c464c84a5026
• Remediation follow-up main: c3eca4f50813307a9aa13245529495065b3ca1c5
• Stable-fixture remediation main: 2d465c2d75ed4a1ba1a171c151342180a2b367d6
• Surfaces: global state DB, per-agent DB, bundled plugin SQLite stores, native app stores, migrations, indexes, foreign keys, query plans, retention, compatibility tests, and database documentation.

This audit compares the exact stable release schema with the audit snapshot, then reopens stable databases through that migration code and checks integrity, foreign keys, row survival, index coverage, and representative query plans.

The audit evidence and schema counts remain pinned to f000fdb3174f88da24f6fc1e1702c464c84a5026. This follow-up records remediation merged through c3eca4f50813307a9aa13245529495065b3ca1c5; it does not recalculate the stable-to-audit-snapshot schema delta or claim unrelated intervening main changes were re-audited.

Executive verdict

The durable-data migration decision is resolved, three index families are remediated, and the in-repository stable-upgrade fixture gap is remediated. The exact stable schema upgrades cleanly through the audited migration chain. A broader published-package upgrade-survivor E2E gap remains tracked separately.

The accepted product decision is destructive retirement of the obsolete commitments surface. PR #130629 removes the recognized table, its rows, indexes, and exact retired JSON state without preserving an archive. Foreign keys, schema dependencies, virtual-table safety, and custom/unrecognized table shapes remain fail-closed.

The three index remediations are merged:

1. Two per-agent indexes for session lookup and transcript identity cascade behavior: PR #129569, commit c3eca4f50813307a9aa13245529495065b3ca1c5.
2. Four Logbook indexes used by capture, batch finalization, and retention: PR #129570, commit f21c70768976f64656e78408f5293e3a42c0fcde.
3. Eight Workboard child-table indexes used by card reads and deletion: PR #129568, commit fab76d4842de68fe3aea16517aa3ba0e8d12627b.

Schema delta

Global state database

• Schema version: 1 to 9
• Tables: 73 to 138
• Indexes: 103 to 135
• Net table changes: +69 / -4
• Net index changes: +41 / -9

The four removed tables have distinct owners and retirement semantics:

• commitments
• cron_run_logs
• node_pairing_pending
• node_pairing_paired

Schema 7 validates and drops the shipped commitments table without preserving its rows. Cron history is imported into task_runs before cron_run_logs is dropped. The two node_pairing_* tables had no shipped writer and were intentionally removed as empty legacy schema.

Per-agent database

• Schema version: 1 to 17
• Direct tables: 9 to 35, excluding FTS internal tables
• Indexes: 7 to 42
• Triggers: 6 to 13

The migration sequence from the stable schema to the audit-snapshot schema completed with:

• PRAGMA integrity_check: ok
• PRAGMA foreign_key_check: no violations
• Representative cron, memory, and cache rows preserved

Findings

Resolved: destructive commitments retirement

Remediation: merged in openclaw/openclaw#130629 as d62cb8aa0792ab54a83ffe8897d3211389f20d10.

The accepted contract is deletion, not compatibility:

• Recognized commitments rows, table, and indexes are discarded.
• Exact retired commitments JSON is discarded.
• No archive table, export artifact, fallback reader, or runtime shim remains.
• Foreign references, schema dependencies, unusable virtual tables, and custom/unrecognized commitments shapes block the destructive migration.
• The exact stable fixture migrates with integrity_check=ok and zero foreign key violations.

Tracking issue openclaw/openclaw#129547 is closed.

P1 at audit snapshot: missing per-agent indexes

Remediation: merged in openclaw/openclaw#129569 as c3eca4f50813307a9aa13245529495065b3ca1c5.

Schema:

• src/state/openclaw-agent-schema.sql:113
• src/state/openclaw-agent-schema.sql:443

Call sites:

• src/config/sessions/session-accessor.sqlite-entry-store.ts:407
• src/config/sessions/session-accessor.sqlite-entry-store.ts:553
• src/config/sessions/session-accessor.sqlite-lifecycle-state.ts:502

session_windows(session_key)

At the audit snapshot, code found and rehomed session windows by session_key, but the table had no index starting with that column. The foreign-key child column was also unindexed.

Merged index shape:

CREATE INDEX ... ON session_windows(session_key, updated_at DESC, session_id);

This supports the latest-window lookup, rehome path, and parent-key checks.

transcript_event_identities(session_id, seq)

At the audit snapshot, the table had a composite foreign key to transcript events but no matching child index. Deleting transcript events therefore scanned the identity table.

Measured transcript cascade time:

Session size	Without index	With index	Speedup
1,000 events	0.1378 ms	0.0856 ms	1.61x
100,000 events	5.746 ms	0.0923 ms	62.29x

Merged index shape:

CREATE INDEX ... ON transcript_event_identities(session_id, seq);

P1 at audit snapshot: Logbook batch and retention scans

Remediation: merged in openclaw/openclaw#129570 as f21c70768976f64656e78408f5293e3a42c0fcde.

Owner:

• extensions/logbook/src/store.ts:43
• extensions/logbook/src/store.ts:58
• extensions/logbook/src/store.ts:67

Call sites:

• extensions/logbook/src/store.ts:429
• extensions/logbook/src/store.ts:444
• extensions/logbook/src/store.ts:625

Indexes missing at the audit snapshot:

1. frames(captured_at_ms) for the per-capture lastFrame() lookup and global frame-range reads. The existing (day, captured_at_ms) index cannot serve those global access paths efficiently.
2. frames(batch_id, captured_at_ms) for completed-batch reads. The existing frames(captured_at_ms) WHERE batch_id IS NULL index cannot serve them.
3. observations(batch_id) for batch finalization and reads.
4. cards(keyframe_id) WHERE keyframe_id IS NOT NULL for foreign-key cascade checks during frame pruning.

These were implemented as same-version, idempotent index additions. The store executes its canonical schema on open, so existing databases receive them without a version bump.

P2 at audit snapshot: Workboard child tables scan by card_id

Remediation: merged in openclaw/openclaw#129568 as fab76d4842de68fe3aea16517aa3ba0e8d12627b.

Owner:

• extensions/workboard/src/sqlite-store.ts:201

Call sites:

• extensions/workboard/src/sqlite-store.ts:424
• extensions/workboard/src/sqlite-store.ts:469
• extensions/workboard/src/sqlite-store.ts:874
• extensions/workboard/src/sqlite-store.ts:1291

At the audit snapshot, eight card-owned tables were queried or deleted by card_id and ordered by ordinal, but had no matching index:

• events
• attempts
• comments
• links
• proof
• artifacts
• notifications
• worker_logs

attachments already has the correct pattern.

Merged shape for each formerly missing table:

CREATE INDEX ... ON <table>(card_id, ordinal);

These indexes serve card materialization, ordered reads, and foreign-key cascade/delete checks.

P2 at audit snapshot: stable-upgrade fixture was misleading

Remediation: merged in openclaw/openclaw#129695 as 2d465c2d75ed4a1ba1a171c151342180a2b367d6.

Relevant tests and harness:

• src/state/openclaw-state-db.test.ts:1866
• scripts/e2e/lib/upgrade-survivor/sqlite-volume.mjs:304

At the audit snapshot, a test named for v2026.7.1-2 first materialized the then-current schema and then injected commitments tables. It did not open the exact stable SQLite schema. The upgrade-survivor harness seeded legacy JSON/files, not a stable-release SQLite image.

The merged repair replaces the synthetic database with a deterministic, compressed database produced by the actual v2026.7.1-2 runtime. Immutable compressed, raw-database, and schema hashes pin its provenance. The test now proves released catalog shape, expected migrations through current schema v10, Doctor repair, integrity and foreign keys, retained rows, sequence continuity, retired objects, canonical schema/indexes, and idempotent reopen.

The separate published-package upgrade-survivor E2E gap remains open:

• openclaw/openclaw#129689

Lower-priority observation

workers(environment_id) has no index, but the store is bounded to a small worker population. This is not currently worth another index without measured production pressure.

Rejected false positives

Android destructive migration

No durable-user-state defect was found. Destructive rebuilds are limited to cache/disposable stores; durable unsupported versions fail closed.

Apple cache rebuild

Cache rebuild behavior is intentional. Durable stores use forward migrations; no equivalent destructive durable migration was found.

Verification

Exact stable-to-audit-snapshot migration checks:

• Global schema reopened and repaired to version 9
• Agent schema reopened and repaired to version 17
• PRAGMA integrity_check returned ok
• PRAGMA foreign_key_check returned no rows
• Representative non-retired rows survived

Exact stable-fixture regression proof at PR #129695:

• Stable tag commit: 0790d9f593ad30c940ed93b5872a8cf6d6f3cf8c
• Compressed fixture SHA-256: 62c2e22acc5b0d9d7a2d4c8afeb17735c5ef526593c937499614d9aba2ffd2f4
• Raw database SHA-256: 17807b83d33115fb9e523ae40a829d9177c8cab2ae3cb2ac872318a573fc0269
• Focused test: 1 passed, 156 skipped
• Full state database suite: 155 passed, 2 skipped
• Exact-head hosted CI: green

Focused audit-snapshot tests:

562 passed, 9 skipped

Core command:

node scripts/run-vitest.mjs \
  src/state/openclaw-state-db.test.ts \
  src/state/openclaw-agent-db.test.ts \
  src/state/openclaw-state-schema-compatibility.test.ts \
  src/state/openclaw-database-preflight.test.ts \
  src/state/sqlite-query-plan.test.ts \
  src/infra/sqlite-index-schema.test.ts \
  src/infra/sqlite-schema-contract.test.ts \
  src/infra/sqlite-transaction.test.ts \
  src/infra/sqlite-wal.test.ts

Plugin command:

node scripts/run-vitest.mjs \
  extensions/workboard/src/store.test.ts \
  extensions/logbook/src/store.test.ts

Remediation status

• Database documentation: openclaw/openclaw#129571, merged as 333ef0f2ce2183f9b942fa19004920f76d6c82c3.
• Core per-agent indexes: openclaw/openclaw#129569, merged as c3eca4f50813307a9aa13245529495065b3ca1c5.
• Logbook indexes: openclaw/openclaw#129570, merged as f21c70768976f64656e78408f5293e3a42c0fcde.
• Workboard indexes: openclaw/openclaw#129568, merged as fab76d4842de68fe3aea16517aa3ba0e8d12627b.
• Exact stable-fixture upgrade coverage: openclaw/openclaw#129695, merged as 2d465c2d75ed4a1ba1a171c151342180a2b367d6.
• Destructive commitments retirement: openclaw/openclaw#130629, merged as d62cb8aa0792ab54a83ffe8897d3211389f20d10; tracking issue openclaw/openclaw#129547 is closed.
• Published-package upgrade-survivor E2E remains open: openclaw/openclaw#129689.

Manual performance verification

All candidate indexes were measured manually against identical before/after SQLite fixtures. Core measurements use the exact schema at PR head 0966678598; those schema bytes were unchanged at final PR head ed16d269d45d17caf9792daa02abd328f230c66e and merged unchanged. Logbook and Workboard measurements used candidate schema definitions that were committed and merged unchanged. Measurements used warmed, alternating runs on Node v26.7.0 with SQLite 3.53.4. Absolute latency is a single-machine microbenchmark; planner changes and order-of-magnitude deltas are the useful signal.

Core agent indexes

Fixture:

• 2,000 session nodes
• 200,000 session windows
• 400,000 transcript events and identities
• 11 interleaved measured trials

Operation	Before	After	Speedup
Latest session window lookup	4.8505 ms	0.00436 ms	1,112x
Session-node cascade	10.804 ms	0.5577 ms	19.4x
Transcript cascade, 1,000-event session	0.1378 ms	0.0856 ms	1.61x
Transcript cascade, 100,000-event session	5.746 ms	0.0923 ms	62.3x

Storage/build cost in this fixture:

• Session-window index: 10.51 MB, 69.8 ms build
• Transcript identity index: 10.46 MB, 97.3 ms build
• Transcript identity index: about 26 bytes per identity row plus one B-tree write per inserted event

The transcript index is scale protection. It should not be described as a large typical-session latency win.

Logbook indexes

Fixture:

• 400,000 rows for each batch/timestamp scenario
• 31 alternating measured runs
• Three index-build runs

Operation	Before	After	Speedup
Batch frame read, 1,000 matching rows	11.485 ms	0.802 ms	14.3x
Observation replace/delete, 1,000 matching rows	10.51 ms	2.84 ms	3.70x
Frame FK maintenance across 400,000 cards	21.624 ms	0.0197 ms	1,098x
Latest-frame lookup	11.697 ms	about 0.005 ms	over 2,000x
2,000-row timestamp range	about 2.1 ms	about 1.8 ms	about 1.2x

Storage/build cost:

• Batch index: 5.84 MB, 106 ms build
• Observation index: 4.26 MB, 83 ms build
• Keyframe index: 4.39 MB, 83 ms build
• Captured-at index: 4.76 MB, about 94 ms build

The captured-at index is justified by the hot lastFrame() path. The measured retention-selection path did not materially improve and is not used as the index's justification.

Workboard indexes

Fixture:

• 2,500 cards
• 80,000 rows per indexed child table
• 640,000 total child rows
• 15 warmed samples, 20 reads per sample

Child table	Before	After	Speedup
Events	2.331 ms	0.026 ms	89.7x
Attempts	2.428 ms	0.029 ms	83.7x
Comments	2.482 ms	0.023 ms	107.9x
Links	1.838 ms	0.025 ms	73.5x
Proof	1.865 ms	0.026 ms	71.7x
Artifacts	2.474 ms	0.024 ms	103.1x
Notifications	2.534 ms	0.027 ms	93.9x
Worker logs	2.356 ms	0.026 ms	90.6x

Parent-card cascade:

• Before: 41.275 ms
• After: 0.400 ms
• Speedup: 103.2x

Cost:

• Eight-index build: 166 ms
• Total index storage: 13.27 MB for 640,000 child rows
• About 1.66 MB per 80,000-row index
• 25.7% database-size increase in this deliberately child-row-heavy fixture

2026-08-27 core SQLite performance audit

Environment and refs

• Apple M3 Ultra, macOS 26.6.2, AC power
• Node 24.15.0, embedded SQLite 3.51.3
• Stable: v2026.7.1-2 / 0790d9f593ad30c940ed93b5872a8cf6d6f3cf8c
• Immediately before commitments repair: 372850d59fc56945e06c881476ddaaa477fa8a78
• Commitments repair: d62cb8aa0792ab54a83ffe8897d3211389f20d10
• Benchmark main snapshot: 7334f8d9ba9ccce8cf3bc03c7625d7da88ebb6f8

After the run, main advanced to 8dac217ce94e14a67ad2fc65837e518a2fbe0669. The benchmark harness, state and agent schema SQL, pinned query-plan tests, and audited task/ingress/delivery query implementations are byte-identical at that head. The intervening SQLite-adjacent changes do not alter these measured plans.

The native benchmark harness is byte-identical across pre-repair, post-repair, and current main. The stable harness has only six timed queries; main has eight and seeds different task/transcript surfaces. Cross-version comparisons therefore use only the six byte-equivalent query shapes. Headline seed, total, WAL, and aggregate maximum-p95 values are not compared between stable and main.

Run matrix:

• One discarded smoke run per ref
• 12 interleaved default-profile process runs per ref
• Six interleaved large-profile process runs per ref
• 20 exact-fixture migration/open samples per ref
• Separate production-shaped query and index-cost microbenchmarks

Stable versus main hot reads

Median of each process run's query p95:

Ref	Default: slowest common query	Large: slowest common query
Stable	0.0450 ms	0.0480 ms
Pre-repair	0.0430 ms	0.0440 ms
Post-repair	0.0425 ms	0.0435 ms
Current main	0.0435 ms	0.0435 ms

Verdict: no credible regression in the six shared hot reads. All are below 0.05 ms at the median p95. Differences are a few microseconds, change direction across profiles, and are smaller than host noise.

Exact stable-fixture migration

The exact v2026.7.1-2 fixture was copied outside the timed region for every sample. Every repaired database ended at schema version 12 with:

• commitments table absent
• named commitments indexes absent
• PRAGMA integrity_check=ok
• zero PRAGMA foreign_key_check rows
• identical resulting page count, freelist count, and file size

Ref	Repair median	Physical-open median	Cached-open median
Pre-repair	271.6 ms	199.7 ms	0.0586 ms
Post-repair	285.6 ms	201.7 ms	0.0558 ms
Current main	290.8 ms	230.3 ms	0.0610 ms

Pre-to-post deltas were +14.0 ms for repair and +2.0 ms for physical open. Bootstrap confidence intervals crossed zero broadly; effect sizes were effectively zero. The runs were noisy and sequential, with repair outliers above one second, so the correct conclusion is no measurable commitments regression, not “5% slower.”

Benchmark coverage defects

The checked-in benchmark gives false confidence:

1. It seeds 50,000 task_runs rows but never times a task_runs query.
2. It seeds 10,000 channel-ingress rows but never times ingress claims, failures, or retention.
3. Timed cron, delivery, plugin-state, and cache queries are bounded synthetic variants, while production has important unbounded or differently ordered reads.
4. Transcript scale is fixed at 128 rows for every profile.
5. Reads are warm-cache repetitions in fixed order.
6. CI runs only the smoke profile.
7. Reporting collapses all queries into one maximum p95, hiding which access path changed.
8. The query-plan test passes while missing the production plans below.

Production-query-shaped paired checks

These are stress fixtures built from real production SQL shapes, not claimed production distributions. Results use 12 independent paired processes with alternating current/candidate order, five warmups, and repeated timed reads.

Query shape	Current median p95	Candidate median p95	Planner result
Task list, 2,000 rows/source	7.179 ms	5.324 ms	Candidate removes temp sort
Ingress claims, 910 rows	1.422 ms	1.158 ms	Candidate removes partial temp sort
Failed ingress, 100 of 5,000	1.026 ms	0.125 ms	Candidate removes temp sort
Delivery pending, 30,769 rows	83.130 ms	unchanged control	Existing index is used; cost is row hydration
Plugin state, 417 rows	0.255 ms	unchanged control	Existing listing index is used
Agent cache by update time, 1,000 rows	0.444 ms	redundant candidate	Existing idx_agent_cache_updated is used

Candidate-index costs:

Candidate	Fixture bytes	Build time	Decision
task_runs(runtime, source_id, created_at, task_id)	2.81 MB	included in 33.9 ms state build	Reject for now
Ingress claim order	471 KB	included above	Reject for now
Ingress failed order	430 KB	included above	Measure real failure density first
Cache scope/update order	102 KB	2.2 ms	Reject as redundant

The task candidate's paired median ratio is 36% faster, but its bootstrap interval crosses parity because of host outliers. It also adds a 14% median task-insert penalty in the full paired runner, with a separate tightly scoped write benchmark measuring 28.2% (22.60 ms to 28.98 ms). Do not add it without evidence that this list path is materially hotter than task creation.

The failed-ingress candidate is the strongest read candidate: 83.9% faster by paired median, with a bootstrap interval of 79.5% to 92.5% faster. It still saves less than one millisecond for a 100-row page and adds write/storage cost to every ingress row lifecycle. Measure realistic failure density and status transition cost before merging it.

The claim-order candidate is about 21% faster by paired median, but saves only 0.26 ms at this cardinality. It is not worth another index without evidence that claim listing is operator-visible or frequent enough.

The delivery result is a product/API issue, not an index issue. SQLite already uses idx_delivery_queue_pending; loading more than 30,000 payload rows costs 83.1 ms median p95 before application-level JSON inflation. The unchanged control varied widely across runs, reinforcing that small candidate deltas are not merge evidence.

Dead cron indexes

No current production query matches:

• idx_cron_jobs_store_updated
• idx_cron_jobs_enabled_next_run

The second is exercised by the synthetic benchmark and plan test only. At 1,000 cron jobs they occupy 135,168 bytes. In 12 paired runs, dropping both reduced a 10,000-update transaction from:

• 42.12 ms median / 42.79 ms p95
• to 30.19 ms median / 31.03 ms p95

That is a 28.3% median write improvement.

Implementation is now in openclaw/openclaw#130466 at exact head f88768764d5585237894442a0773d50c786262ed. The existing v13 cron_jobs table rebuild retires both physical indexes without another schema bump. The PR also removes the synthetic enabled/due benchmark query and query-plan assertion while retaining the production-shaped store-order check.

An exact-v13 follow-up isolated the remaining descending index. Across 12 paired runs, retiring idx_cron_jobs_store_updated reduced a 10,000-update transaction from 21.33 ms median to 16.93 ms, a 20.7% improvement, and saved 61,440 bytes at 1,000 jobs. Before and after, the production cron-store read used idx_cron_jobs_store_order; integrity_check remained ok and foreign_key_check remained empty.

The change landed on main through PR #130466 as squash commit 1ea2640f5428eacb70e182137e9501fbdfd8cbca. The merged code also repairs the pre-existing migration findings: canonical cron JSON wins conflicting legacy projections, malformed cron rows survive migration for durable quarantine, malformed rebuildable plugin-index caches no longer block startup, and the documented v13-to-v12 path fully reprojects populated cron rows. Exact-head CI finished with 179 passing checks, 30 intentional skips, and no failures or pending checks. ClawSweeper reported no actionable findings or rank-up moves. The final follow-up also preserves upgrades from valid v12 databases that never materialized the formerly lazy gateway_origin_device_tokens table and synchronizes the public backup and plugin-index documentation with the v13 storage contract.

Recommended follow-ups

1. Add production-shaped task_runs, ingress, delivery, plugin-state, and cache queries to the benchmark and query-plan tests.
2. Report each query independently in performance CI; stop using one maximum p95 as the verdict.
3. Keep the task and ingress candidate indexes out until production-scale frequency/density evidence outweighs their write and storage costs.
4. Address delivery queue startup/hydration with bounded loading or paging, not another index.