> ## Documentation Index > Fetch the complete documentation index at: https://docs.getpioneer.dev/llms.txt > Use this file to discover all available pages before exploring further. # Persistence Layer > SQLite, SeaORM entities, CRUD repositories, projectors, events, runtime recovery tables, and secret storage boundaries. Pioneer persists normal gateway state in `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. Read [Gateway](/architecture/gateway) first if you want to understand who calls the persistence layer. Read [Agent Loop](/architecture/agent-loop) and [Tasks And Subagents](/architecture/tasks) if you want to understand the main event producers. ## 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 | Crate | Role | | ------------------ | ---------------------------------------------------------------------------------------------------------- | | `crates/entity` | SeaORM entity modules for every table. | | `crates/migration` | Schema migrations. | | `crates/sqlite` | SQLite-specific write coordination and helpers. | | `crates/crud` | Repository methods, typed records, turn projector, task projector, and materialization helpers. | | `crates/memory` | Service-owned memory write, recall, candidate policy, memvid backend coordination, and repair diagnostics. | | `crates/keystore` | `db-keystore` facade, stable secret ids, metadata, permission hardening, and test memory store. | 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 Prompt manifests, skill bindings, MCP bindings, and retained LLM context are persisted by gateway durable event handling around the core projector. ## 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 | Domain | Stored data | | ----------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Workspaces and threads | workspace rows, thread rows, folder placement, thread lineage, sandbox policy, summaries | | Turns | turn rows, turn input, turn items, turn item attempts, status history, timeline events, permission profile snapshots, execution security snapshots, permission audit events | | Prompt/context | prompt manifests, retained `turn_llm_context` rows during active recovery windows | | Artifacts/providers | artifact metadata, versions, bindings, projections, upload session rows, and provider external refs for attachment reuse | | Secrets | no raw values; domain rows store refs or redacted data only | | Skills | installations, workspace policy, turn bindings, audit events, dependency snapshots, upload sessions | | MCP | server installations, catalog snapshots, audit events, turn MCP bindings | | Tasks | tasks, triggers, runs, events, dependencies, agent specs, deliveries, write locks | | CLI runtime | native thread bindings, active turn bindings, runtime ids, native ids, input mapping, and recovery status | | Agent memory | memory records, memory candidates, policy decisions, tombstones, provenance, repair diagnostics, memvid capsule refs | | Thread episodic context | indexed conversation chunks, capsule segments, index jobs, exclusions, recall events, and thread directory metadata | | Recovery | recovery jobs and provider failure metadata | ## 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](/architecture/secrets) 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 or `gateway-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](/architecture/protocol) describes the public types that often drive storage changes. * [Gateway](/architecture/gateway) explains request handling and durable event persistence. * [Artifact Store](/architecture/artifacts) explains workspace-scoped file storage, bindings, previews, and provider attachment reuse. * [Prompt And Context](/architecture/prompt) explains prompt manifests and retained LLM context. * [Memory Architecture](/architecture/memory) explains memory records, candidates, tombstones, and memvid capsule storage. * [Thread Episodic Context](/architecture/thread-episodic-context) explains indexed conversation chunks and recall events. * [CLI Runtime Architecture](/architecture/cli-runtime), [MCP Architecture](/architecture/mcp), [Skills Architecture](/architecture/skills), and [Tasks And Subagents](/architecture/tasks) describe the domain-specific records stored here.