Skip to content

database-schemas.md

Latest commit

 

History

History
235 lines (203 loc) · 22 KB

database-schemas.md

File metadata and controls

235 lines (203 loc) · 22 KB
Raw
summary OpenClaw SQLite database locations, schema versions, integrity checks, and downgrade recovery
read_when
Diagnosing a newer database schema error
Checking database compatibility before an update or downgrade
Proposing a SQLite or persistent-store change
Preparing storage operations for another database backend
Recovering a database for an older OpenClaw release
title Database schemas

OpenClaw stores control-plane state in the shared state database and agent data in one SQLite database per agent. Schema migrations run forward when a database opens. Older OpenClaw builds refuse databases written by a newer schema.

Native SQLite initialization reads the loaded library's version and extension capability in one query before admitting real state databases. Auth-profile readers install their lock-wait timeout at connection open. Non-mutating WAL observations reuse the loaded library's admitted capability; they still observe current WAL frames and read freshness on each use. Quarantine decision readers and writers set their existing lock-wait timeout at connection open; each decision reads the current schema version and quarantine row in one SQLite snapshot and validates any recorded file generation. WAL safety, quarantine authority, and recovery behavior are unchanged.

Schema-version, integrity, canonical-index, and table-existence checks belong to open/admission and the migration owner after migrations; runtime paths must carry admitted schema facts with the handle, never re-query them, and use fresh PRAGMA data_version checks to observe foreign commits on the next unpinned read while preserving active SQLite snapshots. Existing per-call checks are legacy and must be migrated when touched.

Shared-state and agent read-only connections reuse bounded prepared statements under their native connection lifecycle. Prepared-statement reuse alone does not retain query results. Read admission shares one freshness check within its synchronous operation; schema-fact lookups reuse the admitted handle without checking again. Write transactions refresh after acquiring BEGIN, before consuming those facts. Explicit fresh checks always execute, even inside another read operation. A foreign commit compares the schema and user versions before retaining or replacing schema facts, preserving active SQLite snapshots. Closing or replacing the connection clears retained statements and facts.

Shared-state content-version checks reuse their value at the admitted connection revision. Foreign commits, local writes, schema changes, and connection closure invalidate reuse. Native transactions, pinned snapshots, unadmitted handles, and dynamic authorizers continue reading the marker directly. Cold admission still checks freshness after catalog capture before validating the supported version. Schema versions, stored bytes, and upgrade or downgrade behavior are unchanged.

New agent readers share the initial freshness probe with schema validation; subsequent unpinned uses still probe again. Shared-state worker reads keep admission and query execution in the same freshness scope. Point transcript statistics, mutation clocks, pending-archive checks, and hot/cold watermarks use a single statement's snapshot; composite reads retain their read transaction, and hot transcript reads reuse an existing transaction without a nested savepoint. Yielding write admission restores its temporary busy timeout once, immediately after acquiring the write transaction, and carries an inherited lock deadline without rereading the connection's timeout. FIFO, lock-wait budgets, schemas, stored data, and update behavior are unchanged.

The admitted catalog includes index names and trigger definitions alongside tables. Canonical session validation consumes these definitions without another catalog scan. Canonical index admission shares the schema contract reader's batched metadata snapshot instead of querying each table and index separately. Shadowed PRAGMA names retain native inspection, and authorization, drift detection, transactional repair, and integrity checks remain unchanged. First-use schema owners skip additive DDL only when all their tables and indexes are present in the current facts. Foreign schema changes, local DDL, rollback, and connection replacement invalidate those facts through the same connection owner; missing objects still use the existing installation transaction. Unadmitted and authorizer-controlled connections retain their native checks. Schemas, stored bytes, and update behavior are unchanged.

Schema facts gathered within a managed read operation survive data-only transaction settlement. The next operation still checks foreign commits. Unmanaged transaction snapshots, and sibling schema publications observed inside an active transaction, discard their facts when that snapshot ends.

Progress-card writes reuse the transaction's admitted table facts. The schema owner creates the lazy table only when it is absent, so warm writes preserve schema facts for that handle and its local siblings. First use after rollback or a foreign schema change still creates missing storage through normal write admission. Stored cards, revision tombstones, schema versions, and upgrade or downgrade behavior are unchanged.

The agent-database execution owner retains up to four idle physical-agent executors in least-recently-used order. Borrowing an executor refreshes its independent 30-minute idle timeout; a fifth idle executor evicts the least recently used one. Configuration changes to the agent roster or storage paths stop warm retention and drain affected executors after their last borrower settles. Already-admitted work retains its original physical store; new requests resolve the current configuration. Explicit database closure and Gateway shutdown still revoke and drain the existing lifecycle resources. This changes no schema, stored bytes, or update behavior.

Creating an agent database at an admitted absent path revokes the previous file's retained validation before worker preparation. A recreated file cannot borrow that proof even if Linux reuses its inode. Ordinary reopen still reuses live proof, and fresh stores keep their canonical certification. Receipt identifiers survive worker transfers so alias publication revokes superseded proof while preserving acknowledged copies. Later revocation still refuses publication. Schemas, stored bytes, and update behavior are unchanged.

Retaining an already-open agent handle holds its lifetime without querying SQLite. Its read or transaction owner refreshes schema facts when consuming data; canonical readiness owns the freshness check before reusing its clean-store decision.

Agent ownership metadata follows the admitted snapshot revision as well. Unchanged reads, including reads within one transaction or pinned snapshot, reuse the handle's metadata. Foreign commits on the next unpinned use, local mutations, rollback, and schema changes require a new ownership read. Dynamic authorizers keep querying the metadata. This changes no schema, stored bytes, or update behavior.

The shared-state content-version marker uses the same admitted read revision. Unchanged reads reuse its successful result; foreign commits, local writes, rollback, schema changes, and connection disposal invalidate reuse. Transactions and pinned snapshots reuse the marker at their admitted revision; authorizer-controlled reads still query it. Version validation and upgrade or downgrade behavior are unchanged.

Registry discovery reuses successful migration checks for the admitted schema generation. The minute retention sweep reads deletion history in a worker and shares one matcher across its agent stores; live deletion status and lifecycle commit guards still apply. Cron registry retention validates the complete listing in its reader worker and returns only cron-run entries to the Gateway; ordinary session metadata stays in the worker. Legacy watch-marker discovery uses an indexed prefix range. Retention continues as rows age, even without writes; schema, upgrade, and retention policies are unchanged.

Session row-facts reads reuse a canonical continuation's existing transaction instead of nesting a savepoint. Reads without an active transaction still open one so entry metadata, board presence, and transcript watermarks share a snapshot. Board presence travels in the exact-entry query, including its single-key error fallback, instead of a separate Board read.

Placement projections read placement, move, pending-result, journal, and environment facts in one statement per bounded batch. Batches share the existing read transaction; journal and result-claim checks consume those same snapshot facts. Optional columns come from admitted schema facts and refresh with that owner.

Shared-state read operations retain their admission revision through their synchronous domain read. ACP metadata reuses at most 128 rows per connection under that revision; foreign commits, local writes, schema changes, and close invalidate reuse. Transactions and pinned or authorizer-controlled reads still query SQLite. Supplied shared-state writers reuse their selected handle and check schema and ownership after BEGIN, without a duplicate pre-transaction row read. Stored bytes, schemas, permissions, and update behavior are unchanged.

Exact entry and participant readers retain their last result at the admitted connection revision. Repeated reads reuse those facts until a local write, rollback, schema change, or observed foreign commit invalidates them. A new transaction probes freshness before reusing unchanged facts; nested savepoints share the transaction's probe. Unexpected transaction loss expires both facts and freshness. Session revision guards and maintenance snapshots share that admitted transaction's freshness probe while continuing to check the local mutation generation. Unpinned uses and transactions without an admitted probe still check foreign commits; transaction entry, rollback, and settlement retain their existing invalidation. No schema, stored bytes, permissions, or update behavior change. Returned entries and participant identities remain caller-owned. Transcript watermark reads select the hot generation and the retained cold or hot sequence in one statement; hot-only readers keep their existing meaning. These query changes preserve schemas, stored bytes, live authority, and update behavior.

Display-history readers resolve selected activity anchors by session and event ID, retaining the sequence fence inside the same read snapshot. These point lookups use the existing primary key and require no schema or data migration.

Session entry writes batch their saved snapshot fields in one upsert, preserving per-field revision triggers and rollback.

Canonical main-key policy reads reuse a connection-owned value at the current read revision, including within transactions and pinned snapshots. The connection owner tracks local SQL mutations, including raw and trigger-driven writes; its mutation revision, admitted schema facts, observed foreign-commit version, and pinned snapshot identity invalidate that value. Native mutation and transaction-control callbacks and authorizer-controlled reads continue querying the policy. Policy facts do not grant canonical admission or continuation authority.

The Gateway does not schedule daily full-database scans. Admission-requested background checks stay limited to the requested agent database: quick_check for clean restart proof, or a full check after proven same-boot process death. See integrity admission and Doctor maintenance for the provenance requirements and operator-requested verification.

Two mechanisms back that contract. CI runs scripts/check-native-state-schema-version.mjs, which fails the build when the Swift and TypeScript state-database contracts declare different schema versions. openclaw doctor --fix owns file-to-SQLite migrations and records a receipt for each one in the shared migration_runs and migration_sources tables.

Execution step receipts are separate from these persisted import receipts. A step blocked by an earlier refusal includes optional originatingRefusal fields stepId, code, and message naming the first failure. See legacy state migration for how to resolve it.

This page is an index. The reference is documented on focused pages, one per reader job. Open the page that matches your task and stay there.

Page Read it when
Database layout The two database roles, their on-disk paths, and the tables behind individual features.
Versioning contract How schema versions are recorded, when a bump is required, and how updaters cross one.
Per-person and companion storage Personal GitHub connections, personal model accounts, and Apple companion delivery journals.
Storage changes and release preflight Preparing for another backend, the material-change review checkpoint, and openclaw database preflight.
Database access in workers Moving runtime reads and writes off the Gateway main thread while preserving their owners.
Worker migration inventory Reproducing the synchronous-access inventory and choosing the next migration.
Agent schema history Per-agent database schema versions, their changes, and their first releases.
State schema history Shared state database schema versions, their changes, and their first releases.
Integrity, troubleshooting, and recovery Integrity checks, common database errors, and the supported downgrade recovery path.

Related

  • Backups — archives, per-database snapshots, scheduling, and offsite copies for the databases described here
  • Updating — updating safely, including the verified backup to take before a schema bump, and the rollback strategy
  • Doctor — the repair and migration tool that fixes stale config/state and reports health problems
  • openclaw doctor — CLI reference for the command that runs those migrations
  • openclaw update — CLI reference for the updater that preflights schema support

Where each section moved

Every section heading from the previous single-page version keeps its anchor here, so an existing link such as /reference/database-schemas#schema-bumps-and-older-updaters still resolves. Each entry points at the page that now holds the content.