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, native terminal effects, 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, terminal-effect preparation, 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.
Native terminal-effect outbox
native_terminal_effect_outbox stores side effects that become eligible only after a canonical turn terminal commit or an accepted task result. The row contains the workspace, thread, and turn scope, the runtime generation, effect kind and gate, serialized payload, immutable payload hashes, an optional handler checkpoint, attempt state, claim lease, and completion timestamps.
The outbox supports post_turn_hook and attached_task_cleanup. Its state machine is prepared, waiting_acceptance, ready, running, retry_wait, succeeded, unresolved, discarded, or superseded. A prepared row cannot run before the terminalization transaction activates it. An accepted-task-result gate is activated only after the candidate projection reaches accepted; rejected, superseded, or cancelled candidates discard the waiting effect.
The repository enforces a maximum of two effects per turn, a 256 KiB payload, a 128 KiB handler checkpoint, and 20 attempts. Claims use an expiring token so a worker that stops mid-execution does not keep ownership forever. The resilience worker retries due rows and purges only resolved rows. The gateway retains resolved rows for 30 days, runs the purge daily in batches of 256, and retries retention maintenance hourly after a transient storage failure. A migration rollback refuses to remove the table while unresolved obligations remain.
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, provider and CLI runtime proxy credentials, remote-access credentials, and gateway-internal auth material. It is created in the runtime home next to gateway.db for gateway use. The desktop app stores its device-session credentials in the platform keystore and keeps only credential references in its gateway registry.
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.