gateway.db through SeaORM. Secret values are stored separately in keystore.db. The persistence layer is split into entity definitions, migrations, SQLite helpers, the CrudStore repository/projector facade, and a keystore boundary used by gateway and desktop secret flows.
Persistence is part of the runtime contract. The gateway uses it to show active turns, recover interrupted work, record each turn’s prompt and security state, track MCP catalogs, reconcile tasks, and restore client state after reconnect.
Why this layer exists
Pioneer has many pieces of state that change while work is running. A table that stored only the final answer would not be enough. The UI needs timeline items as they start and complete; recovery needs retained LLM context; task scheduling needs queued and retryable runs; MCP needs catalog snapshots; and skills need audit records and dependency snapshots. The persistence layer gives these flows a consistent place to append durable events, project read models, and repair deterministic state after restart.Crates
Business logic should not call SeaORM entities directly from the gateway. The normal boundary is
CrudStore.
That boundary keeps schema details from leaking upward. If a handler wants thread history, it asks CrudStore for thread history; it should not know which joins or tables produce that view.
CrudStore
CrudStore holds a DatabaseConnection, TurnProjector, TaskProjector, and SqliteWriteCoordinator. It exposes typed methods for threads, turns, items, skills, MCP, tasks, recovery jobs, artifacts, artifact external refs, prompt manifests, and retained LLM context.
The write coordinator is important because the gateway is async and can have concurrent request handlers, background workers, MCP runtime tasks, and task scheduler work touching SQLite.
Turn persistence
Turns use durable event payloads and projected read models.TurnEventPayload includes turn start, item start, item completion, item updates, timeout/recovery events, retry events, tool-loop budget exhaustion, permission audit events, turn completion, and turn failure.
TurnProjector writes or updates:
- threads
- sandbox/security policy
- turns
- turn input
- turn status history
- turn items
- turn item attempts
- turn permission profile snapshots
- turn execution security snapshots and audit references
Task persistence
Tasks have their own event stream.AppendedTaskEvent includes event id, task id, optional run/thread/turn ids, sequence, event type, payload, workspace id, root task id, parent task id, and timestamp.
The task projector maintains task read models: tasks, triggers, runs, dependencies, agent specs, deliveries, delivery attempts, write locks, and task trees.
Important tables by domain
Read-model repair
The gateway can detect and repair deterministic read-model invariant violations on startup. Examples include terminal tool payloads still marked in progress, terminal turns with running attempts, or terminal task/run rows missing completion timestamps. This repair path is not a substitute for correct projectors. It exists because a gateway can stop between durable event append and all downstream cleanup, especially while the project is evolving.Secret storage boundary
gateway.db and keystore.db have different responsibilities.
gateway.db stores domain state: threads, turns, tasks, skills, provider attachment metadata, MCP installations, MCP catalog snapshots, audit records, and secret refs.
keystore.db stores raw secret values: workspace-scoped provider API keys, MCP env/header secret values, superuser JWT signing material, and desktop gateway bearer tokens. It is created in the runtime home next to gateway.db for gateway use. The desktop app also uses the same keystore crate for saved gateway bearer tokens.
Current keystore storage is unencrypted. Pioneer hardens runtime directory and SQLite file permissions, but any OS user or process that can read the runtime home can read the keystore file. See Secret Storage for operational details.
Persistence guidelines
Add new user-visible state through protocol types first, then storage records, then projector/repository methods. For evented flows, persist the event and project the read model in one coordinated path. For runtime-only state, prefer explicit cleanup and recovery behavior over hidden global maps. Do not store raw secrets in ordinary domain tables orgateway-settings.toml. Workspace provider keys, MCP secrets, desktop gateway bearer tokens, and JWT signing material belong in the keystore. Database records should contain redacted config or secret refs.
Memory has an additional persistence rule: gateway.db is the control plane and remains authoritative even when the memvid capsule backend returns stale payloads. Memory records, statuses, scopes, candidates, tombstones, and policy decisions must be checked by MemoryService before a search hit becomes visible.
Thread episodic context follows a related but separate rule. The database tracks which conversation chunks are indexable, indexed, excluded, deleted, or due for retry, while memvid stores the searchable payload. A recall hit is useful only after the gateway checks the indexed chunk state, workspace/thread scope, status, and prompt budget.
Related pages
- Protocol Layer describes the public types that often drive storage changes.
- Gateway explains request handling and durable event persistence.
- Artifact Store explains workspace-scoped file storage, bindings, previews, and provider attachment reuse.
- Prompt And Context explains prompt manifests and retained LLM context.
- Memory Architecture explains memory records, candidates, tombstones, and memvid capsule storage.
- Thread Episodic Context explains indexed conversation chunks and recall events.
- CLI Runtime Architecture, MCP Architecture, Skills Architecture, and Tasks And Subagents describe the domain-specific records stored here.