04. Data Storage (Schema v27)
Lossless tool-image storage (#1868)
Transcript format 2 uses an optional storage-private binaryStorage version 1 envelope. Each recognized image/blob string appears once in its record; typed JSON paths and exact text templates reconstruct the original structured result and JSON mirror. Templates preserve key order, whitespace and escape spelling. Nested proven mirrors share text references when their outer JSON string token has canonical JSON spelling, as produced by JSON.stringify; other spellings stay literal. Arbitrary narrative text is never inferred to be an image or a result mirror. The public UiMessage, plugin card inputs, extension context, model history and existing RPC request/response objects contain the complete decoded values.
Live transcripts, revisions, checkpoints, compaction tails and the Desktop outbox share the lossless representation. A first append/update converts an existing legacy file through a verified sibling temporary file and atomic replacement. Reading alone never converts files. Conversion failure preserves the original and propagates an error. Invalid references/unknown codec versions are errors; restoration must not continue with an empty history.
New code reads both plain legacy records and encoded records. Database schema 26 is a version-only migration with a schema-25 backup: it stops older applications from misreading a new encoded file. It does not alter user configuration or rewrite transcripts at startup. Downgrading to an old application requires a complete compatible backup, not merely swapping the SQLite file. No external blob directory, new file cleanup lifecycle or lossy image transform is added.
0. Ownership decision
Rust host-core owns SQLite exclusively (D002), and the transcript file store with it (D119). Plan/Goal artifacts and queue records are also host-owned (D189); shell defaults are host settings (D190).
- Node pi sidecar does not open the DB or transcript files directly
- Electron main does not write DB or transcript files directly
- All persistent app data — sessions, settings, providers, scheduled tasks, artifacts, notifications, audit — goes through host RPC. (v1 violation fixed: scheduled tasks previously lived in an Electron-owned
scheduled-tasks.json.)
1. Goals
Local-first, recoverable after restart, sensitive data isolated — plus, for schema v7, v8, v11, and v14:
- Lossless transcripts — store the runtime message shape (content blocks), not the UI projection; UI shapes are derived at the RPC boundary.
- SQLite is an index, not a payload store (D119) — message content lives in one JSONL file per session (codex/claude-code style): human-readable, greppable, copyable, and the database stays small no matter how much is chatted.
- High performance — O(1) file appends, covering indexes for every hot query, integer times, single-writer WAL, no JSON scans on hot paths, and bounded renderer transcript reads even when a session contains very large message content.
- Extensible without migrations where cheap (block vocabulary, JSONL line types, kv namespaces,
config_jsoncolumns), with migrations where structural (new entities), versioned byPRAGMA user_version.
Project groups use the existing kv extension boundary rather than a new relational schema. The host stores one JSON record per group in the projectGroups namespace, shared memory in projectGroupMemory, and shared instructions in projectGroupInstructions. The record contains the stable group id, display name, ordered canonical roots, primary root, timestamps, and optional detachedPaths. Removed roots without sessions stay in detachedPaths so an old path project record is not recreated as a standalone legacy group. A removed root with sessions is omitted from detachedPaths and remains readable as a standalone legacy group; removing a root from group membership never deletes sessions or files. Existing path projects are projected as legacy single-root groups at read time; their path-scoped memory and filesystem instructions remain readable. 5. Plan/Goal checkpoints are immutable host artifacts with recorded path, hash, and size; the existing approval row also carries execution fields. Startup interruption is the process-epoch fence and no work is replayed.
Delegation settlement snapshots
A completed Task tool call may still describe a running delegate. A later terminal snapshot with the same session, message/call ID and delegation ID replaces only that tool result and its display text. Existing metadata, message order and index identity are preserved, including namespaced IDs after a cross-session tool-call collision. A stale running replay, another delegation's result or a later terminal replay cannot overwrite the settled snapshot. Other duplicate appends retain their existing idempotent behavior. No schema migration or transcript format change is required.
2. File layout
User-selected storage location (issue #1213)
Settings → General → Storage can select an empty directory on a different volume. A selected directory contains data/ (the complete host/application profile) and browser/ (Chromium default and persistent plugin/browser session state). The existing default directories remain unchanged until the user explicitly migrates. Project files outside the application profile are not moved.
The original Electron userData directory remains the installation identity, single-instance lock, and owner-only storage-location.json bootstrap anchor. Chromium sessionData follows browser/; this preserves existing localStorage, cookies, IndexedDB and persistent partition state by copying the complete old profile. An explicit PI_DESKTOP_DATA_DIR still overrides the default and disables settings-driven maintenance, since such profiles opt out of the installation lock. A managed relaunch discards only the environment root published for child services through the internal --pi-managed-storage argument before reacquiring the lock. The location is machine-local and never part of cloud configuration sync.
Migration is cold: the accepted settings action journals pending work, then uses existing ordered shutdown to settle turns/outbox and stop writers. The next launch points Chromium sessionData at a temporary directory, then opens only a sandboxed, nonpersistent maintenance window before importing the application composition root. The default session initializes with that first window, so it must not be inside a profile the job is about to copy or clean. It inventories bytes/files, checks free space, streams the copy, preserves permissions and internal/external links, and SHA-256 verifies both source and copied files. An interrupted copy may be retried only with its matching ownership marker; nonempty/unrelated destinations and overlapping roots are rejected. The stable installation lock prevents competing managed launches.
Filesystem maintenance uses Electron's original-fs: archives such as app.asar inside session scratch or downloaded applications remain physical files, not virtual directories. Their bytes and adjacent .asar.unpacked directories migrate unchanged. Windows links are classified in one batch by their reparse-point kind, preserving junctions as junctions and symbolic links as symbolic links. For a symbolic link whose target exists, the target's kind determines whether it is recreated as a directory or file link; for dangling or unavailable targets, the link's own directory attribute determines that type. Internal targets are relocated without requiring them to exist. Windows absolute link targets are lexically normalized before creation and verification, so trailing separators from junctions do not cause false failures. Copy failures include the source and destination paths in the existing local error detail. They never skip an entry or publish the destination as the active profile.
Confirming a directory selected in Settings creates it when it does not exist, including missing parent directories. Overlap checks run before creating anything. A first-install data root may be absent together with its parents; validation resolves the nearest existing ancestor without creating an unused old profile. This does not recreate an unavailable custom storage volume during startup.
Rust's offline --relocate-data <old-root> <copied-root> mode owns structured path relocation in the copied SQLite index, transcripts/revisions/checkpoints, outbox, installed plugin registry and agent capability metadata. It does not boot RPC, upgrade schemas, recover turns, or sweep scratch or visualizations. It changes only known path-bearing fields under the old root. External projects, dev/builtin plugins, narrative text, commands, source code, secrets, and arbitrary plugin-private formats are preserved. Credentials and their machine key migrate as bytes with their permissions. SQLite ownership stays exclusively in Rust.
Relocated project database paths and project-memory/instruction keys retain the repository's normalized project-path spelling. In particular, Windows migration must not turn slash-normalized lookup keys into mixed-separator keys that make existing project memory appear empty after reopening the Host.
Only after validation/relocation succeeds is the flushed bootstrap pointer atomically replaced. Errors keep the old profile active and visible in settings; retrying the same destination uses the failed job's ownership identity. A crash before publication leaves pending work to recopy from the source. An unavailable selected volume refuses startup rather than creating a blank profile elsewhere. If the managed installation's default data root is missing, startup pauses before loading app services and offers two recovery choices: create a fresh default data root or select an existing Nib storage root. The selected location must contain data/pi.sqlite or be the data directory containing pi.sqlite; its pointer is written only after validation. A sibling browser/ directory is used when present, otherwise the installation's current browser root is retained. Dismissal exits without changing the pointer or creating a profile. Missing custom roots and pending cache/backup maintenance continue to refuse startup. A queued migration may resume with an absent default data root only when its browser root still exists, preserving first-install migration. An explicit PI_DESKTOP_DATA_DIR remains outside this recovery flow. Original directories remain explicit backups. Deleting these requires a separate settings confirmation after checking new-location functionality, including plugins that may own absolute references the host cannot safely rewrite. Backup cleanup preflights every root and protects active storage and bootstrap/lock files. The stable anchor retains Chromium's Windows lockfile alongside Singleton* and storage-location.json; these installation files are also excluded from browser-profile copies. Cleanup never releases the live single-instance lock. The exclusion applies only to the browser root, not same-named nested user files or files inside the host data root. Retry after a partially completed cleanup tolerates already-absent old roots, then clears the backup list only after the remaining cleanup succeeds.
Cache cleanup is a separate confirmed cold-restart operation. Its filesystem allowlist is cache/, plugins/cache/download/, plugins/cache/backup/, openable-attachments/, and Chromium's Cache/Code Cache/GPU/shader cache folders in the default profile and persistent partitions. Intermediate or leaf symlinks cannot redirect cleanup, even within the same profile. It never clears cookies/localStorage/IndexedDB, transcripts, attachments, secrets, scratch, review snapshots, plugin code/data, models, configuration, or logs. Partial cleanup failure remains observable, retains active roots, and can be retried.
A packaged installation keeps this tree in ~/.pi-desktop. A development build keeps the same tree in ~/.pi-desktop-dev, because a shipped app and a pnpm dev host are two installations that have to run at the same time (D599, ADR 0094). PI_DESKTOP_DATA_DIR replaces either root outright and is resolved to an absolute path before it reaches host-core as a child-process variable.
~/.pi-desktop/
├── pi.sqlite # index database (WAL: + -wal/-shm) — host-core only
├── pi.sqlite.v6.bak # archived pre-v7 database (D119 breaking reset)
├── pi.sqlite.v8.bak # exact readable backup before v8→v15 destructive work
├── pi.sqlite.v9.bak # exact readable backup before v9→v15 destructive work
├── pi.sqlite.v10.bak # exact readable backup before v10→v15 destructive work
├── sessions/ # transcript file store (D119) — host-core only
│ ├── <sessionId>.jsonl # live transcript (header + messages)
│ ├── <sessionId>.jsonl.layout # disposable, versioned byte-offset cache
│ ├── <sessionId>.revisions.jsonl # regenerate branches, append-only
│ └── <sessionId>.inflight.json # streaming reply checkpoint (D299), transient
├── secrets/ # encrypted secret blobs + .machine-key (unchanged)
├── config-sync/ # encrypted sync base/pending bundles — host-core only
├── attachments/ # content-addressed blobs (sha256 name), refs from messages
├── plugins/ # code + data + registry.json (unchanged, spec 07-11)
├── logs/ # NDJSON app/<category>, host/<category>, agent/<category> logs
├── cache/ # disposable caches
├── crash-dumps/ # local Crashpad minidumps (never uploaded; D602)
├── crash-dumps.json # last-reported dump mtime (best-effort marker)
├── review-changes/<sessionId>/<snapshotId>/
│ ├── before # bounded pre-tool bytes, when reversible
│ └── meta.json # path, hashes, diff state, and ownership
├── scratch/<sessionId>/ # per-session agent temp files (D114), including
│ # composer pasted files under pasted/ and replayed/
│ # image fallbacks — deleted
│ # with the session; startup sweep removes orphans
│ # and stale dirs
└── visualizations/<sessionId>/ # inline visualization .html fragments (D671) —
# deleted with the session; startup sweep removes
# orphans only, never by ageOne database file keeps cross-entity writes transactional (e.g. session + turn + artifact in one commit). The DB stores no large payloads: message
turn + artifact in one commit). The DB stores no large payloads: message content lives in sessions/, attachments and tool outputs beyond the limits of 16-tool-result-limits live on disk, referenced by path/hash.
1.3 Portable configuration sync
Host Core stores S3 configuration at configSyncS3/config in the key-value store. Credentials and the vault key use existing encrypted secret storage. config-sync/s3/<state-id>/base.bin stores shared and observed baselines; pending.bin and local recovery points are also authenticated encrypted bundles replaced through temp-file rename. Connection or mode changes create a fresh state ID. The legacy configSync namespace, old credentials and old files are preserved, not migrated or deleted. Remote immutable objects remain indefinitely; only the mode-specific history index is capped at 30 revisions. This does not change the SQLite schema or transcript retention.
2.0 Message-owned review snapshots (ADR 0043)
Successful workspace Write/Edit tool results carry the bounded details.review record described in 03-tools-and-permissions. The transcript JSONL is the durable index for the visible card; the previous bytes and hashes live outside the workspace in review-changes/<sessionId>/<snapshotId>/. The host removes a session's snapshot directory with session.delete and sweeps directories whose session no longer exists at startup. A snapshot is never inferred from Git, so a later commit does not remove historical review evidence.
2.1 Transcript files (D119)
sessions/<sessionId>.jsonl — first line is a session header, then one line per message; seq is implied by line order:
{"type":"session","schema":2,"sessionId":"0b0e…","createdAt":"2026-07-26T09:00:00.000Z"}
{"type":"message","id":"m1","role":"user","createdAt":"…","blocks":[{"type":"text","text":"…"}]}
{"type":"message","id":"m2","role":"tool","toolName":"Write","blocks":[{"type":"tool_call","callId":"c1","args":{},"result":{},"status":"success"}]}
{"type":"message","id":"m3","role":"assistant","createdAt":"…","blocks":[{"type":"thinking","text":"…"},{"type":"text","text":"…"}],"meta":{"usage":{},"modelId":"…"}}
{"type":"compaction","id":"cp1","summary":"…","firstKeptMessageId":"m2","throughMessageId":"m3","tokensBefore":917000,"retainedTail":[…],"providerId":"…","modelId":"…","createdAt":"…"}Internal system-state rows use role system, empty visible content, and optional meta.modelSystem = { version: 1, messageJson, beforeMessageId?, afterMessageId? }. messageJson is validated JSON text of a Pi system message with sections and tool schema deltas; executable functions are excluded. JSON text preserves section and schema key order across Rust storage; parsing for validation never reserializes it. Stable row IDs make retries idempotent. The anchors restore logical model order when a user row was already persisted before its preceding declaration; a surviving following anchor takes precedence, then a preceding anchor, then the record's continuation position. Forks remap surviving anchor IDs. Normal system notices remain visible; internal model-state rows do not produce transcript bubbles or search text.
Compaction details may include one systemMessageJson checkpoint. It replaces old system updates in the retained tail and is restored before the summary. These optional metadata fields use the existing JSONL/SQLite index and require no schema migration. Old sessions remain readable; their first continuation records a new baseline. Older app versions ignore the metadata and reconstruct their usual current prompt, so downgrade does not promise the same cache prefix.
sessions/<sessionId>.inflight.json — the assistant reply currently streaming in the session, as one { schema, sessionId, turnId, savedAt, message } object that host-core replaces atomically (temp + rename) on every session.saveInflightMessage (D299). Electron main sends a checkpoint at most every 1.5 s while message_update events carry visible text, so a quit or crash mid-reply loses at most the last interval of output instead of the whole reply. The file is transient: the final row's session.appendMessage with the same id removes it; a completed/error turn end removes it only when that id is already indexed (D327); and the boot sweep plus a sidecar-loss turn end (recoverInflight) promote a leftover whose final row never landed — as complete when the turn already completed, otherwise as an aborted assistant message under its turn. It is never appended to, never read by the sidecar, and never mirrored into SQLite; a late checkpoint for an id that is already indexed is dropped. Delegate replies are not checkpointed.
sessions/<sessionId>.revisions.jsonl — append-only, one line per archived regenerate branch; the active flag lives only in the DB index so switching revisions never rewrites this file:
{"type":"revision","rootUserId":"u1","revisionIndex":1,"createdAt":"…","messages":[…message records…],"turns":{"<messageId>":"<turnId>"}}
{"type":"revision_live","rootUserId":"u2","revisionIndex":2,"createdAt":"…"}A branch is the transcript suffix rooted at its user turn, so a branch that is still live is byte-identical to content the transcript already stores. That branch gets a revision_live line: identity only, no messages payload, with the reader resolving the branch against the transcript. Storing the suffix instead is what made this file grow quadratically — a measured session wrote 107 MB to hold 16 MB of unique content, because every finished turn rewrote a tail that had grown since the turn before. revision stays the line for a branch that has left the transcript, and it is the only line kind a legacy file contains.
A reference line is constant-sized (99 bytes for a UUID family in a measured session) and does not grow with the branch it names. It deliberately omits the turns map a stored branch carries: that map exists for messages a restore has to re-attach after they left the index, and a live branch's messages are the session's index rows, so a restore reads their turns from the index. A copy here would put one entry per branch message on every finished turn — measured at 197 bytes on the first turn and 1943 by the fortieth, which is the same quadratic growth this line kind exists to remove.
Rules:
blocksis the canonical block vocabulary (§4.7) — not the UiMessage projection.metais the parsed metadata object (usage / modelId / providerId / status / error / revision fields).- Timestamps in files are RFC3339 wire spellings (readability); the DB index keeps integer ms.
- Readers skip unknown
typelines and a torn trailing line: new line kinds need no migration, and a crash mid-append cannot poison the file. - A
revision_liveline is meaningful only while its branch is the live transcript suffix. Three writers keep that true:archive_live_branch(revision switch) andarchive_discarded_regenerate_branch(regenerate, edit) each write the fullrevisionline before the rewrite that stops holding the branch, andarchive_dropped_live_branches— reached throughreplace_messages— does the same when a rewrite deletes the root turn itself (message delete, smart Stop). A branch that is no longer live therefore always ends with a stored payload, and a reader only ever resolves a reference for the branch that is live right now. A new path that drops a branch from the transcript without archiving it first would strand that branch's reference line. compactionis a model-context checkpoint, not a message — but it is rendered, as a divider row rather than a chat bubble (D203). Readers return every message unchanged and separately return every still-valid checkpoint, oldest first; the newest is the active one and the whole chain is what the transcript draws its rows from, so a checkpoint outlives the compaction that produced it. A record whosethroughMessageIdanchor no longer exists is dropped, per record, on read and on fork.throughMessageIdis the durable transcript boundary;firstKeptMessageIdandretainedTailreproduce the summary + applicable context after restart.retainedTailholds at most the latest user message for an active turn; a completed-turn checkpoint has an empty tail. Thedetails.retainedTailModevalue (active_turnorcompleted_turn) preserves that boundary, while legacy records are normalized to their latest user message. When the bound model requires DeepSeek-style reasoning replay,details.retainedReasoningmay hold a bounded list of prior thinking turns (text + thinking only) so post-compaction Completions requests can echo usable reasoning without restoring tool-call pairs (ADR 0256 / #296). If the active message crossed the retention limit it is stored in marked, truncated form; the original message lines stay complete and authoritative for UI/diagnostics. An automatic compaction failure may storedetails.fallback = "retained_tail"and a short recovery summary instead of an LLM-generated summary; the complete transcript remains authoritative and the fallback tail is only a model-context recovery view.detailsalso carries the checkpoint generation and the compaction family, both opaque to the host.- Writers append with flush + fsync (message durability ≈ WAL
synchronous=NORMAL); full transcript rewrites (regenerate/edit, revision switch, import) go through a sibling temp file + atomic rename. A normal context checkpoint is one appended line and never rewrites visible messages. A rewrite carries forward every checkpoint that is still valid against the rewritten messages, not just the newest one. - Ordering: the file is written before the DB index transaction. A crash between the two costs one derived index row — never content — and the next full rewrite self-heals; transcript reads dedupe repeated message ids keep-last.
- Transcript files are user data: removed only when their session is deleted, never by an age or orphan sweep (unlike
scratch/).
3. Connection bootstrap
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL; -- durable enough under WAL; app-crash safe
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA temp_store = MEMORY;
PRAGMA cache_size = -16000; -- 16 MB page cache
PRAGMA trusted_schema = ON; -- required by the FTS triggers (§4.8); the DB
-- is app-owned at a fixed path, never an
-- untrusted input, so schema trust is safe
PRAGMA auto_vacuum = INCREMENTAL; -- set at creation, before any table- Schema version lives in
PRAGMA user_version(v15 =15). The v1metatable is gone. - host-core is the single writer; statements use
prepare_cached; every multi-row write runs in one transaction. - Boot maintenance runs before RPC service: one transaction marks every
plan_approvalsrow withstatus='pending'asinterruptedand every row withexecution_state IN ('queued', 'running')asinterrupted; it also aborts running turns and appends the recovery audit records. No process epoch is serialized. An already-approved queued/running Plan/Goal interruption leavessessions.mode = 'agent'. The transaction then proceeds to the normalPRAGMA incremental_vacuumand audit retention pruning (§9).
4. Schema
4.1 kv — namespaced configuration
Replaces v1 settings + meta, and hosts plugin settings (spec 07-11 §5).
CREATE TABLE kv (
ns TEXT NOT NULL,
key TEXT NOT NULL,
value_json TEXT NOT NULL,
updated_at INTEGER NOT NULL,
PRIMARY KEY (ns, key)
) WITHOUT ROWID;| ns | contents |
|---|---|
app | the settings blob (settings.get/set), currentProjectId. Optional networkProxy (mode/url/bypass) is a JSON field in that blob: url/bypass survive a switch to System or Direct and are applied only in Custom mode (issue #1541); no schema version bump (D340) |
ui | non-critical UI state the renderer asks the host to keep |
cache | model-refresh stamps, recent model refs (spec 13 §3) |
plugin:<id> | per-plugin settings; uninstall = DELETE WHERE ns = ? |
projectMemory | durable user-authored context keyed by canonical project path; structured values contain format: "entries-v1", visual entries, derived content, and updatedAt |
The app settings JSON optionally stores showThinking (boolean) for transcript reasoning visibility. It is a portable application preference and synchronizes across devices like the legacy field. Missing values fall back to the legacy thinkingDisplayMode field (compact hides, detailed/unknown/absent shows), which is retained only for persisted-settings compatibility and as that fallback. The app settings JSON also optionally stores transcriptDisplayMode (flat | command-first | fully-folded) for transcript process grouping: flat renders no nested activity groups, command-first renders Bash/run rows as standalone rows beside activity groups, and fully-folded keeps each command inside the group around it. Missing or unknown values normalize to fully-folded in both the renderer and main-process settings normalizers. It is a portable application preference and changes presentation only. The deprecated keepCommandsExpanded boolean is retained only for persisted-settings compatibility; it is no longer exposed in Settings and no longer read for rendering. These additive display preferences neither rewrite stored reasoning nor change the database schema.
The settings blob may still contain legacy prompt-enhancement keys from an earlier release. They are retained for rollback and downgrade compatibility, but the host no longer reads or writes them as active preferences. When the user installs and grants the standalone pi.prompt-enhancement plugin, Electron copies valid legacy values into that plugin's private settings once (see 04-ux/12-prompt-enhancement.md). The migration marker is also stored in the plugin's private data directory; the host settings schema does not change.
New config domains (e.g. MCP servers) start as a namespace; they graduate to tables only when they need relations or indexes.
Renderer sidebar preferences (D093)
Sidebar organization is non-authoritative presentation state stored best-effort under renderer localStorage key pi.desktop.sidebarPreferences:
type SidebarPreferences = {
sessionMeta: Record<string, {
pinned?: boolean;
archived?: boolean;
order?: number; // renderer-local manual order
}>;
projectMeta: Record<string, {
pinned?: boolean;
archived?: boolean;
collapsed?: boolean;
order?: number; // renderer-local manual order
}>;
projectSort: "recent" | "created" | "oldest" | "name" | "manual";
sessionView: {
sort: "recent" | "created" | "oldest" | "name" | "manual";
archived: boolean;
};
openProjectPaths: string[];
};- Project keys and retained paths use normalized full paths; session keys use durable session ids. Duplicate/slash-variant paths are discarded on load.
projectSort: "manual"andprojectMeta[*].orderstore renderer-local project presentation order. Dragging a project title or using ArrowUp and ArrowDown on that title writes contiguous order values for the visible normalized paths. Missing or invalid values fall back to stable path order; pinned and archived priority remains applied before manual order. Sessionmanual/orderremain compatibility fields and are not exposed by the sidebar.- Missing, malformed, or unwritable preferences fall back to empty metadata,
recent, archived hidden, and the host-selected project. Preference failure never blocks a host operation. - The record never contains transcript content, tool arguments, provider configuration, or secrets. Clearing it changes presentation only.
openProjectPathsretains sidebar tabs. The selected workspace remains host-ownedkv(app, currentProjectId)and is restored throughworkspace.get; the renderer does not persist a competing active path.
4.2 projects — places work happens
Replaces the v1 workspace singleton. Feeds the Settings Project archive index (D066/D133), sidebar group-by-project (benchmark §3.8), and future per-project defaults.
CREATE TABLE projects (
id INTEGER PRIMARY KEY,
path TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
pinned INTEGER NOT NULL DEFAULT 0,
created_at INTEGER NOT NULL,
last_opened_at INTEGER NOT NULL
);- Rows are auto-upserted by path whenever a workspace is opened, a session is created with a project path, or an import references one.
- Project paths are trimmed, separators are normalized to
/, and trailing separators are removed before the unique-path upsert. Imports therefore materialize one durable logical project directory per distinct path. projects.listis the Project archive index source of truth. Renderer preferences may hide an archived project from the default sidebar, but cannot remove or hide its durable Projects-index row.- A project row is a logical index entry. Import never creates an operating system directory: historical paths may be missing, remote, or read-only.
- The current visible workspace is
kv(app, currentProjectId)— no singleton table, no partial-unique flag. Retained tabs do not add more current-project fields. - Project memory is host-owned in
kv(ns='projectMemory', key=<canonical path>)rather than renderer preferences. It is independent for every project path, capped at 32 KiB, and is loaded by Electron main when a project session starts. Visual entries are normalized and rendered to plaincontentfor the runtime; legacy plain-text values remain readable and are shown as one untitled entry when opened in the editor. The runtime labels it as user-provided context so it cannot become a replacement for safety, tool, or collaboration rules.
4.3 providers
Same role as v1; headers_json + compatibility_json collapse into one extensible config_json (shape per 12-provider-config-schema).
CREATE TABLE providers (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
vendor_key TEXT NOT NULL DEFAULT 'custom',
type TEXT NOT NULL DEFAULT 'openai_compatible',
protocol TEXT NOT NULL DEFAULT 'openai_compatible',
api_style TEXT,
auth_kind TEXT NOT NULL DEFAULT 'api_key_and_base_url',
base_url TEXT,
enabled INTEGER NOT NULL DEFAULT 1,
secret_ref TEXT,
default_model_id TEXT,
config_json TEXT NOT NULL DEFAULT '{}',
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL,
-- Owning plugin id for a row a plugin declared in `contributes.providers`
-- (schema v17, ADR 0259). NULL is a user-owned row: the plugin refreshes its
-- own fields on every load, while the user path may edit or delete only the
-- rows it owns.
owner_plugin_id TEXT
);
CREATE INDEX idx_providers_owner ON providers(owner_plugin_id)
WHERE owner_plugin_id IS NOT NULL;4.4 models — catalog cache
Implements 13-model-catalog-and-selection (v1's dead provider_models never did).
CREATE TABLE models (
provider_id TEXT NOT NULL REFERENCES providers(id) ON DELETE CASCADE,
model_id TEXT NOT NULL,
display_name TEXT NOT NULL,
source TEXT NOT NULL DEFAULT 'user', -- bundled | discovered | user
capabilities_json TEXT NOT NULL DEFAULT '[]',
context_window INTEGER,
max_output_tokens INTEGER,
deprecated INTEGER NOT NULL DEFAULT 0,
discovery_state TEXT CHECK (discovery_state IN ('published', 'unlisted')),
updated_at INTEGER NOT NULL,
PRIMARY KEY (provider_id, model_id)
) WITHOUT ROWID;Refresh (spec 13 §6/§9) upserts discovered rows and never overwrites source='user' rows. Recent-model MRU stays in kv(cache) — it is a bounded display list, not relational data.
Schema v25 adds nullable discovery_state, distinct from metadata deprecation. The v24 upgrade backs up first, adds the field transactionally, and marks existing discovered rows as past publication evidence. User-owned rows remain unknown. No provider configuration, credentials, sessions, or turns are changed. Configured missing rows retain an unlisted marker across restart; an endpoint change clears it. Older apps refuse the newer schema rather than interpreting it incorrectly; rollback requires the pre-migration backup.
4.5 sessions
CREATE TABLE sessions (
id TEXT PRIMARY KEY,
title TEXT NOT NULL DEFAULT '',
project_id INTEGER REFERENCES projects(id) ON DELETE SET NULL,
provider_id TEXT, -- loose ref, see below
model_id TEXT,
mode TEXT NOT NULL DEFAULT 'agent', -- plan | agent
title_source TEXT NOT NULL DEFAULT 'legacy' -- legacy | default | manual | generated
CHECK (title_source IN ('legacy', 'default', 'manual', 'generated')),
thinking_level TEXT NOT NULL DEFAULT 'off'
CHECK (thinking_level IN ('off', 'minimal', 'low', 'medium',
'high', 'xhigh', 'max', 'omit')),
permission_mode TEXT NOT NULL DEFAULT 'inherit' -- D115: inherit follows settings
CHECK (permission_mode IN ('inherit', 'ask', 'accept-edits', 'auto')),
fast_mode INTEGER NOT NULL DEFAULT 0 -- ADR 0334: session /fast switch
CHECK (fast_mode IN (0, 1)),
source TEXT, -- import origin: claude-code | codex | opencode | pi
deleted_at INTEGER, -- plugin trash marker; null means active
pinned INTEGER NOT NULL DEFAULT 0,
last_seq INTEGER NOT NULL DEFAULT 0, -- current message count / ordinal allocator
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL
);
CREATE INDEX idx_sessions_updated_id ON sessions(updated_at DESC, id DESC);
CREATE INDEX idx_sessions_project ON sessions(project_id) WHERE project_id IS NOT NULL;
CREATE INDEX idx_sessions_deleted ON sessions(deleted_at) WHERE deleted_at IS NOT NULL;Plugin imports add a host-owned origin sidecar. It is deliberately separate from the core session identity and scopes every plugin read/write to the plugin_id that created the row:
CREATE TABLE session_import_origins (
plugin_id TEXT NOT NULL,
source_id TEXT NOT NULL,
external_id TEXT NOT NULL,
session_id TEXT NOT NULL UNIQUE REFERENCES sessions(id) ON DELETE CASCADE,
source_label TEXT,
origin_json TEXT,
created_at INTEGER NOT NULL,
UNIQUE(plugin_id, source_id, external_id)
);
CREATE INDEX idx_session_import_origins_plugin
ON session_import_origins(plugin_id, source_id, created_at DESC);provider_id/model_idare loose references (no FK), like onturns: selection is(providerId, modelId)per spec 13 with custom ids always allowed, and built-in runtimes (e.g.pi) never exist inproviders.thinking_levelis the durable session selector. New and v2-migrated sessions default tooff; capability resolution may clamp the effective request without rewriting the stored preference. Schema v19 addsomit(ADR 0295): send no thinking override. Existing rows keep their stored canonical values.project_idnormalizes v1's free-textproject_path(grouping, badges, hover-+new-session-in-project all become indexed lookups).last_seqis exposed asSessionSummary.messageCount. Appends allocate the next ordinal and full transcript rewrites reseat it to the current message count, so zero is the durable empty-session predicate.titleis user-visible session metadata. Thesession.renameboundary trims and validates it to 1–80 Unicode code points before persisting it. A manual rename does not updateupdated_at, so changing a label cannot reorder recent activity; transcript rows, message count, and session state remain unchanged.title_sourcerecordslegacy,default,manual, orgenerated. The v22→v23 migration leaves pre-existing titles aslegacy; new session creation and manual rename write the corresponding source.defaultmeans "not chosen by the user and still replaceable": it covers a new session's placeholder and the deterministic first-prompt fallback, which host-core writes only while the stored title is still a recognized placeholder. A placeholder is recognized in every shipped locale, because the renderer writes its localizedchat.untitledTasklabel when it creates a session; recognition trims and lowercases the stored title against the placeholder set. Schema v23 retains pre-existing v22 titles aslegacyto protect them from automatic replacement, because historical sessions lack provenance to distinguish an untouched placeholder from a deliberate rename to the same text. Existing v23/v24 rows are not reclassified. The standalone title plugin can read first-turn text only whiledefault, and writes using exact-title compare-and-set, so a manual rename wins a race.Import binds every non-empty normalized
projectPathtoproject_id; path-less imports remainNULL. Re-importing a deterministic session id creates neither another session nor another project row.The schema
pinnedcolumn is retained for project-index ordering and migration compatibility. D093 sidebar pin/archive/collapse state is the renderer preference overlay and does not require a schema migration. Nostatuscolumn: live running/waiting state is runtime truth, not durable truth; badge data comes from the latestturnsrow (§4.6) plus in-memory state.source+ deterministic imported ids keep re-imports idempotent and let the UI badge imported sessions.deleted_atis a host timestamp used by the plugintrashoperation. A trashed plugin session is hidden from normal session lists and plugin reads, but its transcript and origin remain until the owning plugin purges it. Core session deletion cascades the sidecar; purging also removes transcript files.session_import_originsstores the plugin/source/external idempotency key and the originalprojectPath,modelId, andproviderIdas history JSON. Those values never become active session bindings for plugin imports. A plugin may explicitly supply a host-createdprojectId; only that id becomes the activeproject_id, while the historical fields remain unchanged.project_idis also the tool-root authority for that session. Switching the visible workspace cannot redirect an in-flight or later tool call belonging to a different session.Forking a session copies its current active transcript into a new session row while retaining the exact
project_id, provider/model, mode, thinking, and permission configuration. No parent/child column is stored: the result is an independent session, not a durable navigation tree.modeis the authoritative operating mode.planandgoalmean the same pi Agent is negotiating a contract of that kind; neither ever selects another runtime. A livependingrow inplan_approvalsprojectsawaiting_approval;execution_statevaluesqueued/runningproject post-approval execution. Otherwise a Plan or Goal session isplanningwhen its Agent is active or ready. The row'skindis what tells the two apart, since the projected state is shared. Terminal approval rows are historical durable records, not renderer gates; reject, expiry, or pending interruption returns live planning to editable state. The renderer may retain the latest proposal/execution snapshot per session only for its current lifetime from live Host events;plans.pendingrehydrates only pending rows.New sessions default to
agent. Imported legacychatvalues are normalized toplan; forked sessions copy the durable mode but never copy pending, queued, or running approval rows.A message-scoped fork copies only the canonical prefix through the selected message. Assistant Edit uses that child and records the original/edited response tails in the child's existing
message_revisionsstore; the source transcript and source revisions are never rewritten.Forks copy existing referenced files from
scratch/<sourceId>/pasted/toscratch/<childId>/pasted/and rewrite message/checkpoint paths before indexing. Source deletion cannot remove the child copies. Unreferenced files, later-message inputs outside a bounded fork, and other scratch outputs are excluded. Missing expired inputs stay missing; no cross-session read grant is added. Handled fork failures remove copied inputs and child transcripts.Forks copy only the
visualizations/<sourceId>/fragments the copied transcript references intovisualizations/<childId>/and rewrite those::visualizepaths; unreferenced fragments stay behind. Cards always load from the owning session's directory, so deleting the source session does not affect the fork (D671). Reference identity is the source session and file name, independent of the old absolute data prefix, JSON escaping, or canonical alias spelling. Relocating the data directory before forking must retain those child copies. Cleanup and copying refuse redirected visualization base/session directories.
4.6 turns — one row per agent run
The persistence half of 10-session-state-machine (turn_runs in the old logical model), and the rollup point for usage/cost.
CREATE TABLE turns (
id TEXT PRIMARY KEY,
session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
status TEXT NOT NULL DEFAULT 'running', -- running | completed | aborted | error
provider_id TEXT, -- snapshot at run time, no FK
model_id TEXT, -- snapshot at run time
error_code TEXT,
input_tokens INTEGER NOT NULL DEFAULT 0,
output_tokens INTEGER NOT NULL DEFAULT 0,
usage_json TEXT, -- full provider usage (cached breakdown, …)
started_at INTEGER NOT NULL,
ended_at INTEGER
);
CREATE INDEX idx_turns_session ON turns(session_id, started_at DESC);
CREATE INDEX idx_turns_ended_at ON turns(ended_at DESC);4.6a plan_approvals — immutable checkpoint and execution fields (schema v11)
The host writes each submitted Markdown snapshot to a new unique file under the proposal kind's directory: <workspaceRoot>/.pi/plan/ for a plan and <workspaceRoot>/.pi/goal/ for a goal. The existing plan_approvals row stores the kind, the structured title/question, artifact metadata, and post-approval execution descriptor. The file path is relative to the session workspace and always has the form .pi/<kind>/<unique-name>.md. One table serves both kinds (D198), so the single-pending-approval invariant, the execution queue, and every index are shared rather than duplicated.
CREATE TABLE plan_approvals (
request_id TEXT PRIMARY KEY,
session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
turn_id TEXT NOT NULL,
tool_call_id TEXT NOT NULL UNIQUE,
kind TEXT NOT NULL DEFAULT 'plan'
CHECK (kind IN ('plan', 'goal')),
plan_json TEXT NOT NULL, -- exact submitted Markdown snapshot
title TEXT NOT NULL DEFAULT '',
question TEXT NOT NULL DEFAULT '',
status TEXT NOT NULL CHECK (status IN (
'pending', 'approved', 'changes_requested', 'rejected',
'expired', 'interrupted'
)),
action TEXT CHECK (action IN ('approve', 'request_changes', 'reject')),
target_permission_mode TEXT CHECK (target_permission_mode IN ('ask', 'accept-edits', 'auto')),
feedback TEXT,
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL,
expires_at INTEGER,
resolved_at INTEGER,
error_code TEXT,
artifact_relative_path TEXT,
artifact_sha256 TEXT,
artifact_size_bytes INTEGER,
version INTEGER NOT NULL DEFAULT 1,
execution_id TEXT UNIQUE,
execution_state TEXT CHECK (execution_state IN (
'queued', 'running', 'completed', 'interrupted'
))
);
CREATE INDEX idx_plan_approvals_session
ON plan_approvals(session_id, created_at DESC);
CREATE INDEX idx_plan_approvals_pending
ON plan_approvals(status, created_at DESC);
CREATE UNIQUE INDEX idx_plan_approvals_one_pending_session
ON plan_approvals(session_id) WHERE status = 'pending';
CREATE INDEX idx_plan_approvals_execution_queue
ON plan_approvals(execution_state, created_at DESC)
WHERE execution_state IN ('queued', 'running');
CREATE INDEX idx_plan_approvals_execution_id
ON plan_approvals(execution_id) WHERE execution_id IS NOT NULL;plan_json is the exact Markdown snapshot kept for the approval/execution record; it is not a canonical wrapper. title and question are separate structured fields. Each artifact file is immutable and unique, so a later Plan/Goal turn creates a new complete snapshot/approval row and never replaces an earlier file. Hash and byte size authenticate the file before approval, but the approval UI may simply open the relative path.
Approval changes status to approved, sets execution_id and execution_state = 'queued', updates sessions.mode to agent, and stores the explicit permission mode in one transaction. Reject/expiry leave the session in its contract mode — Plan stays Plan and Goal stays Goal — and close the active gate; a later prompt can create a new pending row. The new protocol has no request-changes action; compatibility columns remain for older records.
At startup, before serving RPC, one transaction changes every pending row to interrupted and every queued or running execution state to interrupted. The associated running turn is aborted. There is no serialized process-epoch column and no replay. A pending interruption leaves the session in its contract mode, while an already-approved queued/running interruption leaves it Agent. Renderer reload within the same host can list the pending row and its original expires_at; plans.pending returns no terminal rows, so rejected, expired, approved, completed, and interrupted cards are not rehydrated.
Serves: mid-session model switches ("next turn only", spec 13 §4), the per-message cost chip's session rollup (benchmark §3.2), failed/aborted badges (§3.8), and retry lineage.
4.6b turn_queue — Host-owned turn queue (introduced in schema v15)
CREATE TABLE turn_queue (
id TEXT PRIMARY KEY,
session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
principal TEXT NOT NULL,
idempotency_key TEXT,
input_hash TEXT NOT NULL,
content TEXT NOT NULL,
attachments_json TEXT,
session_message_id TEXT,
user_message_id TEXT,
permission_mode TEXT NOT NULL,
position INTEGER NOT NULL,
priority INTEGER,
voice_origin_json TEXT,
created_at INTEGER NOT NULL
);
CREATE INDEX idx_turn_queue_session ON turn_queue(session_id, position);
CREATE UNIQUE INDEX idx_turn_queue_idempotency
ON turn_queue(session_id, principal, idempotency_key)
WHERE idempotency_key IS NOT NULL;- One row per prompt admitted behind an active turn (D375 / ADR 0213). The headless Agent Host module is the only writer through
session.queuePush,session.queueList,session.queueRemove,session.queuePrioritize, andsession.queueReorder; the store never starts a turn. positionis per session and only grows, so a removed entry never reorders the rest.principalplusidempotency_keymake a retried push return the same row; a reused key with a differentinput_hashfails withIDEMPOTENCY_CONFLICT. A session holds at most eight entries.priority(schema v18, ADR 0265) isNULLuntil the entry is promoted with "send now"; a promotion writesMAX(priority) + 1inside the session, so promoted entries are delivered first in click order and the remaining entries keep theirpositionorder.queueReorderswaps two adjacent non-promotedpositionvalues and refuses a promoted entry.user_message_idandvoice_origin_json(schema v20) retain the stable user-message identity and optional Live Voice operation provenance across restart. They are metadata only: queue recovery still does not replay work.IDEMPOTENCY_CONFLICT. A session holds at most eight entries.attachments_jsonkeeps the prompt's attachment references; bytes stay in the session scratch or project root like any other prompt attachment.- After a restart the module lists every entry, holds each session's queue until a controller attaches, and drains one entry after the active turn's terminal event. Deleting the session cascades to its entries.
Session Todo checklist — schema v21
CREATE TABLE session_todo (
session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
position INTEGER NOT NULL CHECK (position >= 0 AND position < 50),
content TEXT NOT NULL,
status TEXT NOT NULL CHECK (status IN ('pending', 'in_progress', 'completed', 'cancelled')),
priority TEXT NOT NULL DEFAULT 'medium' CHECK (priority IN ('high', 'medium', 'low')),
updated_at INTEGER NOT NULL,
PRIMARY KEY (session_id, position)
);sessions.todo_revision and sessions.todo_updated_at retain ordering metadata even when the checklist is empty. A host transaction updates those fields, deletes the prior rows, and inserts the normalized replacement. The revision advances for every successful write, including a clear. A unique partial in_progress index enforces the single active item invariant at the database boundary. Forks begin with revision zero and no rows; session deletion cascades the rows.
The row content is bounded at 500 Unicode scalar values, contains no NUL, and is trimmed before storage. TodoWrite is the only writer; renderer and sidecar code access this state through host RPC.
4.6c session collaboration ledger — Host-owned delivery state (schema v16)
CREATE TABLE session_collaboration_links (
session_id TEXT PRIMARY KEY REFERENCES sessions(id) ON DELETE CASCADE,
created_by_session_id TEXT NOT NULL,
plugin_id TEXT NOT NULL,
created_at INTEGER NOT NULL
);
CREATE TABLE session_collaboration_messages (
id TEXT PRIMARY KEY,
plugin_id TEXT NOT NULL,
source_session_id TEXT NOT NULL,
source_title TEXT NOT NULL,
target_session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
target_title TEXT NOT NULL,
kind TEXT NOT NULL CHECK(kind IN ('task', 'message', 'completion')),
content TEXT NOT NULL,
status TEXT NOT NULL CHECK(status IN
('queued', 'running', 'completed', 'failed', 'cancelled', 'interrupted')),
notify_on_completion INTEGER NOT NULL DEFAULT 0,
turn_id TEXT REFERENCES turns(id) ON DELETE SET NULL,
reply_to_message_id TEXT,
idempotency_key TEXT NOT NULL,
remaining_hops INTEGER NOT NULL,
permission_ceiling TEXT NOT NULL CHECK(permission_ceiling IN
('ask', 'accept-edits', 'auto')),
result TEXT,
error TEXT,
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL,
UNIQUE(plugin_id, source_session_id, idempotency_key)
);
CREATE UNIQUE INDEX idx_session_collaboration_turn
ON session_collaboration_messages(turn_id) WHERE turn_id IS NOT NULL;
CREATE UNIQUE INDEX idx_session_collaboration_receipt
ON session_collaboration_messages(reply_to_message_id)
WHERE kind = 'completion';- The ledger is the authoritative identity and lifecycle record for a plugin-mediated delivery.
source_session_idandtarget_session_idare real durable Session IDs; titles are display snapshots only.turn_idis assigned when the target actually begins the delivery, not when a plugin creates the record.source_session_iddeliberately has no foreign key, unliketarget_session_id, which cascades, so a delivery record and its completion receipt outlive a deleted sender. Read projections therefore report such references withavailable: falseinstead of dropping the row. turn_queue.session_message_idbinds a queued Agent Host admission to its ledger row. A retry with the same(plugin_id, source_session_id, idempotency_key)returns the original delivery; changing its target, body, kind, or callback flag fails withIDEMPOTENCY_CONFLICT.- A callback is a
kind = 'completion'row withreply_to_message_idpointing at the original delivery. The partial unique index and the host settlement transaction make callback creation at-most-once. Callback bodies contain a bounded result/error projection; the original target transcript remains the full source of truth. - The host snapshots the sender's effective permission ceiling and rejects a target whose current effective mode exceeds it. Autonomous chains decrement
remaining_hops; completion rows cannot create another automatic callback. - On startup, queued work that still has a
turn_queuerow remains held for the Agent Host controller. Running work and queued deliveries without a queue admission are markedinterrupted; the startup fence never replays a turn without a new controller admission. - Collaboration provenance is stored in the transcript line's
metaassessionMessageand is projected to the UI asUiMessage.sessionMessage. Host validation prevents forged, stripped, edited, or regenerated collaboration input from becoming ordinary human input. This metadata is additive and does not require a column inmessages.
4.7 messages — transcript index
The transcript itself is the per-session JSONL file (§2.1); this table is its derived index: one row per message carrying ordering, promoted filter columns, and the extracted plain text that feeds FTS. Tool calls are rows in the stream (as today) with text = NULL.
CREATE TABLE messages (
mid INTEGER PRIMARY KEY, -- stable rowid: FTS anchor, VACUUM-safe
id TEXT NOT NULL UNIQUE, -- caller-facing uuid (optimistic UI); colliding provider toolCallIds remap to {sessionId}:{id} (D444)
session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
turn_id TEXT REFERENCES turns(id) ON DELETE SET NULL,
seq INTEGER NOT NULL, -- per-session ordinal
role TEXT NOT NULL, -- user | assistant | tool | system
tool_name TEXT, -- promoted for tool rows (filters, audit joins)
is_error INTEGER NOT NULL DEFAULT 0,
text TEXT, -- extracted plain text (search/preview); NULL for tool rows
created_at INTEGER NOT NULL,
UNIQUE (session_id, seq)
);Block vocabulary (open set — new types need no migration; stored in the transcript file's blocks array):
type Block =
| { type: "text"; text: string }
| { type: "thinking"; text: string }
| { type: "tool_call"; callId: string; name: string; args: unknown;
status: "ok" | "error" | "denied"; result?: unknown;
completedAt?: string; durationMs?: number;
toolUsage?: ToolTokenUsage }
| { type: "attachment"; kind: "image" | "file" | "session"; name: string;
ref: string /* attachments/<sha256>, absolute path, or session id */;
mimeType?: string; size?: number;
text?: string /* bounded referenced-conversation excerpt */ }
| { type: "hostedSearch"; status: "searching" | "completed" | "failed";
rounds: Array<{ id: string;
status: "searching" | "completed" | "failed";
kind?: "search" | "openPage" | "findInPage";
query?: string; url?: string;
sources: Array<{ url: string; title?: string }> }>;
replay?: Array<{ type: "hostedSearch"; phase: string;
blockId?: string; name?: string; input?: unknown;
status?: string; isError?: boolean; wire?: unknown }> };- Tool results are stored post-truncation (16-tool-result-limits); full raw output is not a storage concern.
- A user attachment block stores only kind, display name, MIME/size metadata, and a ref. Image bytes are content-addressed under
attachments/before the turn is dispatched; transient base64 used to build a pi-ai image block never enters the transcript, database, or renderer message. Non-vision and oversized-image turns retain a safe scratch/project@pathfallback for the model. Replayed content-store images usescratch/<sessionId>/replayed/when a path fallback is required. Images above the inline bound are hashed and copied with streaming file operations; startup and history hydration must not load the whole image into memory. - A
kind: "session"block is a conversation reference: it stores the session id it names, the display title, and the bounded excerpt quoted to the model, so a later turn reads the same reference instead of re-reading the referenced conversation. The excerpt bound, the same-project rule, and the<session_reference>prompt block belong to the reference contract (04-ux/08-component-spec.md§20B); the host stores exactly what it is given and never reads the referenced session to build one. - Assistant thinking is stored only in
thinkingblocks inside the file. The derivedtextcolumn contains final answer text, so transcript search and answer previews do not expose or mix reasoning. - Per-response usage/model metadata rides in the file line's
metaobject;turnsholds the summable rollup — nojson_eachat query time. Optional response stream duration and estimated tool token footprints are preserved with the message metadata so the context inspector survives reload. - Ordering:
seqis allocated O(1) inside the index transaction viaUPDATE sessions SET last_seq = last_seq + 1 … RETURNING last_seq; the file's line order is the same ordering.UNIQUE(session_id, seq)doubles as the covering index for index scans; transcript content loads from the file, not this table. - The index is derived state: losing a row (crash between file append and index commit) degrades search for that message until the next full rewrite, but never loses content.
mid(explicit INTEGER PRIMARY KEY) pins rowids acrossVACUUM, which the FTS external-content mapping depends on;idstays the wire-format uuid.
4.7a Subagent attribution (D201, ADR 0062)
Rows a subagent produced are stored in the same transcript file and the same index as the parent's; what marks them is two fields in the file line's meta object, written by host-core when the sidecar sends them:
meta.parentToolCallId?: string // the `Task` call that spawned the delegate
meta.agentName?: string // the definition name, e.g. "code-reviewer"No column, no table, no migration: attribution is metadata about a message, and promoting it would buy a query nobody makes.
Both fields survive reload, which is what makes a restored session nest exactly like a live one (04-ux/08-component-spec.md §9.9). Two consumers read them:
- The renderer groups attributed rows under their
Taskrow and renders them one level in; the turn stream and the minimap never see them. - The session runtime excludes attributed rows when it rebuilds model context on restore. The parent only ever saw the delegate's report, which is the
Tasktool result and is stored as such; replaying the delegate's own rows would both misrepresent the conversation and reintroduce the context cost delegation exists to avoid.
Retention and deletion treat them as ordinary rows: a deleted session takes its delegate rows with it, and regenerate archives them with the branch they belong to.
4.8 messages_fts — full-text search
The legacy search.query message search uses a trigram tokenizer for CJK and substring matches; queries shorter than 3 chars fall back to LIKE on messages.text. The desktop session search below reuses this index with a Unicode-aware literal verification step.
Desktop session discovery (search.sessions) counts every matching indexed user/assistant message before paginating by session. It excludes sessions with deleted_at set and treats title/project matches separately from body counts. FTS queries are quoted literals and all candidates are verified with a host-owned Unicode lowercase literal predicate. Short queries and non-ASCII case mappings use that predicate directly, preserving title search behavior and keeping message retrieval consistent with renderer highlighting. %, _, quotes, and backslashes are literal text. Snippets surround the match, including short CJK queries, rather than always taking the start of the message. Context navigation resolves stable IDs against physical JSONL positions, and displays canonical JSONL text without modifying SQLite or the live transcript cache. See ADR session-content-search.
CREATE VIRTUAL TABLE messages_fts USING fts5(
text,
content='messages', content_rowid='mid',
tokenize='trigram'
);
CREATE TRIGGER messages_ai AFTER INSERT ON messages WHEN new.text IS NOT NULL
BEGIN INSERT INTO messages_fts(rowid, text) VALUES (new.mid, new.text); END;
CREATE TRIGGER messages_ad AFTER DELETE ON messages WHEN old.text IS NOT NULL
BEGIN INSERT INTO messages_fts(messages_fts, rowid, text)
VALUES ('delete', old.mid, old.text); END;
CREATE TRIGGER messages_au AFTER UPDATE OF text ON messages
BEGIN
INSERT INTO messages_fts(messages_fts, rowid, text)
SELECT 'delete', old.mid, old.text WHERE old.text IS NOT NULL;
INSERT INTO messages_fts(rowid, text)
SELECT new.mid, new.text WHERE new.text IS NOT NULL;
END;Session titles are searched with a plain scan (sessions number in the hundreds; no second FTS table). Index maintenance uses triggers rather than application code so that cascade deletes (session → messages) clean the index too; this is why trusted_schema = ON is part of the bootstrap. DDL validated end-to-end (insert/update/delete/cascade + CJK trigram match) with sqlite3 3.43+.
4.9 message_revisions — regenerate history index
Archives discarded regenerate branches so users can page previous variants without stacking them in the live transcript (D105/D109). One row is one linear branch rooted at a user turn; the branch payload lives in the append-only sessions/<id>.revisions.jsonl (§2.1), keyed by (rootUserId, revisionIndex). A branch that is still live stores a reference (revision_live) instead of a second copy of a transcript suffix the session already has; see §2.1.
CREATE TABLE message_revisions (
id TEXT PRIMARY KEY,
session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
root_user_id TEXT NOT NULL, -- wire id of the root user message
revision_index INTEGER NOT NULL, -- 1-based per (session, root)
is_active INTEGER NOT NULL DEFAULT 0,
message_count INTEGER NOT NULL DEFAULT 0, -- pager label, no payload parse
created_at INTEGER NOT NULL,
UNIQUE (session_id, root_user_id, revision_index)
);
CREATE INDEX idx_message_revisions_root
ON message_revisions(session_id, root_user_id, revision_index);- Live transcript remains the active branch only (transcript file + index).
- Switching a pager entry reads the branch from the revisions file, rewrites the live transcript file, rebuilds index rows, and flips
is_active— the revisions file itself is never rewritten. - Cascade on
session_idclears index rows; file deletion rides on session deletion. root_user_idis the stable regenerate-family key. Live rewritten user prompts may carry a new messageid, butmeta.revisionRootIdkeeps pointing at the original family so later regenerates append to one set.- Root user
metaalso storesrevisionCount/activeRevisionfor the transcript pager; those fields are presentation metadata, not a second source of truth for branch payloads. - The branch that finished a turn is archived by
session.saveActiveRevision, which reads the transcript, appends the revision line, and stamps the root's pager metadata inside one host call. The stamp rewrites only the root's own transcript line, and the file is re-read at write time, so an assistant or tool line appended by the persistence outbox in the meantime survives. A whole-transcript rewrite from a snapshot taken outside the host lock would delete it (ADR 0060). - Electron acknowledges the stamped root with
user_message_persisted, using the root's current messageidasoptimisticMessageId. This updates the pager only where that exact user row is already loaded: the active transcript, retained pane, and session cache. A root outside the current history window must not be appended after the latest reply. Archive completion is not a new user-message lifecycle event and does not change the loaded history boundary. If no projection exists, the acknowledgement leaves the cache absent and does not mark the session live, so later prefetch still loads durable history. - A branch keeps growing after its archive: later prompts append to it and an error-ended turn never reaches
agent_end. So every operation that discards the live branch first writes it back over the revision it belongs to (D307):session.activateRevisionre-archives the live branch of the family from the durable transcript before the switch,session.truncateFromarchives the discarded tail on regenerate/retry (refreshing the stamped variant, or minting an inactive one), andsession.saveActiveRevisionrefreshes an already-archived index instead of skipping it. The refresh is one more line in the append-only file (last record for(rootUserId, revisionIndex)wins) plus amessage_countupdate. The variant named by the live root'sactiveRevisionstamp is the one refreshed; a stamped variant with no index row yet (its turn failed before archive) is stored as its own new variant, never over a previous one.
4.10 artifacts — files a session produced
Backs the Artifacts surface (benchmark §3.7). v1 planned to derive this from audit_log, but audit payloads never recorded file paths; an explicit projection is precise, indexed, and survives audit pruning.
CREATE TABLE artifacts (
session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
path TEXT NOT NULL, -- absolute, workspace-resolved
op TEXT NOT NULL, -- write | edit | delete
turn_id TEXT,
updated_at INTEGER NOT NULL,
PRIMARY KEY (session_id, path)
) WITHOUT ROWID;
CREATE INDEX idx_artifacts_time ON artifacts(updated_at DESC);Upserted by host-core in the same transaction as the tool_execute audit row whenever Write/Edit (or a plugin tool declaring file effects) succeeds — repeat edits update op/updated_at, keeping one row per file per session. Writes into the session scratch directory (D114) or the session visualization directory (D671) are excluded: artifacts list workspace deliverables only.
4.11 scheduled_tasks + task_runs — automations
Moves scheduled tasks out of Electron's scheduled-tasks.json (D002 fix) and adds the run-history the Automations page needs (定时任务 / 运行记录 tabs).
CREATE TABLE scheduled_tasks (
id TEXT PRIMARY KEY,
title TEXT NOT NULL,
prompt TEXT NOT NULL,
cadence TEXT NOT NULL DEFAULT 'manual', -- manual | hourly | interval | daily | weekly
enabled INTEGER NOT NULL DEFAULT 1,
project_id INTEGER REFERENCES projects(id) ON DELETE SET NULL,
config_json TEXT NOT NULL DEFAULT '{}', -- mode, cron expr, model override, notify policy
last_run_at INTEGER,
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL
);
CREATE TABLE task_runs (
id TEXT PRIMARY KEY,
task_id TEXT NOT NULL REFERENCES scheduled_tasks(id) ON DELETE CASCADE,
session_id TEXT REFERENCES sessions(id) ON DELETE SET NULL, -- the run's transcript
status TEXT NOT NULL DEFAULT 'running', -- running | completed | aborted | error
error_code TEXT,
started_at INTEGER NOT NULL,
ended_at INTEGER
);
CREATE INDEX idx_task_runs ON task_runs(task_id, started_at DESC);A run that spawns a session gets its transcript for free via session_id. That transcript is identified as automation output by an EXISTS check against task_runs that every session summary and search hit carries as scheduledRun. The ownership is derived on read and never stored on the session row, so the SessionList and session search hide the transcript while it has a run, and deleting the task returns it to the ordinary lists instead of leaving it unreachable (issue #1291). scheduled.listRuns answers two shapes: one task's own history (taskId, at most 200 rows) and one newest run per task (latestPerTask, one row per task, never combined with taskId). The task column reads the second shape. A global window over task_runs can be filled by one busy task — retention keeps the last 100 runs per task — and would then report an idle task as never run, so the read that feeds the column is per task rather than a shared window. Retention keeps the newest 100 runs per task (TASK_RUNS_KEEP, applied on every boot). Ownership is derived from those rows, so a pruned run takes two things with it: the run leaves the task's history, and its session stops carrying scheduledRun, which returns that transcript to the SessionList and to session search. A task that runs faster than the kept window — an interval task from five minutes up, an hourly task after roughly four days — reaches that boundary; replacing the derived marker with a persistent origin is tracked with the rest of issue #1291. The task page also reads at most 200 runs per task, the bound scheduled.listRuns enforces for a single task's history. The existing JSON extension stores schedule: {hour, minute, weekday}, intervalMinutes (5–1440; required by an interval cadence and read by no other one, so a schedule that keeps the field keeps its value), nextRunAt (epoch milliseconds), workspacePath, and sessionMode (perRun or reuse, absent means perRun) for desktop automations. Optional weekdays stores 1–7 unique integers in 0–6, overriding legacy weekday for weekly schedules. Missing weekdays preserves the single-day behavior. Invalid or empty selections are rejected before mutation. No table migration is needed. Daily/weekly schedules use the host local timezone; hourly and interval schedules count elapsed time from the moment they were armed: hourly computes nextRunAt = now + 3_600_000 and interval computes nextRunAt = now + intervalMinutes × 60_000, both ignoring calendar fields. An interval task whose schedule carries no intervalMinutes is refused rather than saved unarmed. Absence of schedule leaves legacy tasks unarmed. No physical schema change is made. Task wire fields project schedule, RFC3339 nextRunAt, workspacePath and the optional task-owned permissionMode plus paired providerId/modelId values. These additive values stay in config_json; no physical migration is required. Missing model fields retain run-time app-default resolution. Missing permission keeps legacy behavior: Ask for automatic runs and inherited permission for Run now. See the automation ADR and ADR 0305.
Tasks also persist optional thinkingLevel using the existing session values (including off and omit). The full Composer model/reasoning picker and controller are reused with a task-draft configuration callback. Both manual and automatic runs apply the saved level. Missing or cleared levels retain the legacy off behavior; no database migration is required.
Scheduled task config_json.mode is a durable operating-mode value. There is intentionally no physical scheduled_tasks.mode column. The v7→v8 and v9/v10→v11 migration paths map legacy chat values to plan; new scheduled tasks default to agent, and create/update/import normalize the same values. The top-level wire ScheduledTask.mode is only a normalized projection of this JSON value. A scheduled or unattended run whose mode is a contract mode (Plan or Goal) is explicitly rejected before provider work, .pi/<kind>/*.md creation, approval, or queue insertion with PLAN_REQUIRES_INTERACTIVE_SESSION — one shared code for both kinds. It cannot display an approval card or auto-approve a proposal in the background. The user must explicitly switch the task/session to Agent before an unattended run can execute.
4.12 secrets_meta
Registry of which secrets exist (blob files are sha256-named and otherwise unenumerable). owner_kind/owner_id generalizes v1's provider-only column for future plugin/MCP secrets.
CREATE TABLE secrets_meta (
secret_ref TEXT PRIMARY KEY,
owner_kind TEXT NOT NULL DEFAULT 'provider',
owner_id TEXT,
kind TEXT NOT NULL DEFAULT 'api_key',
backend TEXT NOT NULL, -- file_fallback (safe_storage reserved)
updated_at INTEGER NOT NULL
) WITHOUT ROWID;Secret values never enter the DB (D028/D031). The shipped backend is the host-core file store: AES-256-GCM ciphertexts under secrets/, keyed by a machine key that host-core generates once and keeps beside them as secrets/.machine-key (owner-only file mode). host-core records file_fallback for every write; the safe_storage value is reserved for an OS keychain backend that neither host-core nor Electron main implements today, so a same-user process that can read the data directory can also decrypt the secrets.
4.13 audit_log
Append-only; now indexed and prunable. Integer autoincrement PK replaces v1's random uuids (cheaper inserts, natural order).
CREATE TABLE audit_log (
id INTEGER PRIMARY KEY,
ts INTEGER NOT NULL,
kind TEXT NOT NULL, -- tool_execute | tool_denied | …
session_id TEXT,
payload_json TEXT NOT NULL DEFAULT '{}'
);
CREATE INDEX idx_audit_ts ON audit_log(ts);
CREATE INDEX idx_audit_session ON audit_log(session_id, ts)
WHERE session_id IS NOT NULL;4.14 notifications — durable local inbox (D117)
One row records one terminal agent-turn outcome that was not already visible in the focused current chat. It stores structured source data only; renderer and Electron derive localized title/body strings at the presentation boundary.
CREATE TABLE notifications (
id TEXT PRIMARY KEY,
kind TEXT NOT NULL
CHECK (kind IN ('task.completed', 'task.failed')),
session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
session_title TEXT NOT NULL, -- snapshot at terminal transition
turn_id TEXT NOT NULL UNIQUE, -- exactly one inbox row per turn
error_code TEXT, -- populated for task.failed when known
created_at INTEGER NOT NULL,
read_at INTEGER
);
CREATE INDEX idx_notifications_created
ON notifications(created_at DESC);
CREATE INDEX idx_notifications_unread
ON notifications(created_at DESC) WHERE read_at IS NULL;session.endTurnalways updates the turn and, whencreateNotificationis true, insertstask.completedforcompletedortask.failedforerrorin the same transaction. Electron passes false only when the main window is visible/focused and that exact session is the current chat.abortednever inserts a row.- Repeating a terminal update cannot duplicate a notification because
turn_idis unique. The RPC result includes the record only when this call inserted it; otherwise thenotificationfield is omitted. session_titleis the stable session-name snapshot at notification creation, not a localized notification title/body. An empty title remains valid and receives a localized “Untitled task” fallback only at presentation time.- No title/body prose is stored. Permission requests, scheduled reminders, plugin notices, and aborted turns are not notification sources.
- After an insert, the same transaction prunes all but the newest 200 rows by
(created_at DESC, id DESC). This is a global cap; session deletion also cascades its rows. - Mark-read updates are idempotent (
read_atchanges only from null), mark all read is one indexed update, and clear deletes notification rows only. None of these operations changes sessions, turns, or transcripts.
Dropped from v1
| v1 table | v2 home |
|---|---|
meta | PRAGMA user_version |
settings | kv(ns='app') |
workspace (singleton) | projects + kv(app, currentProjectId) |
plugins (dead code) | plugins/registry.json stays authoritative (spec 07-11); plugin settings → kv(ns='plugin:<id>') |
provider_models (dead code) | models |
5. Write paths (consistency)
Persistence points follow 10-session-state-machine §4; streaming deltas never touch storage. Message writes are two steps in a fixed order — transcript file first, index transaction second (§2.1): the file is the source of truth, the index is derived and self-healing.
| event | file step | index/DB transaction |
|---|---|---|
| prompt accepted | append user message line | last_seq alloc (RETURNING) + index row + touch sessions.updated_at; then insert turns(running) |
| assistant/tool message end | append message line; remove the in-flight checkpoint when its id matches | validate optional turnId belongs to this session; if stale/missing, keep the message with a null turn link and emit a warning; otherwise insert the index row and touch session |
| stale-turn outbox replay after an index failure | update all existing transcript lines for the message id to the latest snapshot; do not append another line | rebuild deduplicated message rows and last_seq from transcript order in one transaction, preserving only valid same-session turn links; FTS triggers stay synchronized |
streaming reply checkpoint (session.saveInflightMessage, D299) | atomically replace <id>.inflight.json; no-op for an empty message or an id already indexed | — |
context checkpoint (session.appendCompaction) | append typed checkpoint line after its referenced message boundary | — (checkpoint is not searchable transcript content) |
| tool succeeded (Write/Edit) | — | upsert artifacts + audit_log row, same tx as result persistence |
turn terminal via session.endTurn | completed/error: remove the in-flight checkpoint only when its id is already indexed; otherwise leave it for the outbox or boot (D327). recoverInflight: append the leftover as complete when the turn is completed, otherwise as aborted, when its final row never landed | update turns; for completed/error insert one notification and prune to 200 in the same tx; aborted inserts none; a promoted checkpoint gets an index row under the turn |
| plan/goal submission | host writes the exact Markdown bytes to a new unique <workspaceRoot>/.pi/<kind>/*.md file | insert one plan_approvals(pending) row with the kind, structured title/question, artifact path/hash/size, and expiry before emitting the approval request |
| plan/goal approval | verify the immutable artifact path/hash/size | atomically resolve plan_approvals, update sessions.mode and explicit permission_mode, and set execution_state = 'queued'; reject/expiry stay in the contract mode |
transcript truncate / retry / edit (session.truncateFrom) | host-owned suffix cut: abort leftover running turn, archive discarded regenerate tail, atomic prefix rewrite (temp + rename); preserve only a checkpoint whose boundary remains | single tx via replace_messages: delete index rows, bulk reinsert carrying each surviving message's owning turn_id, reset last_seq; drop inflight checkpoint |
message delete / unanswered smart Stop (session.replaceMessages) | atomic transcript rewrite (temp + rename); preserve only a checkpoint whose boundary remains | single tx: delete index rows, bulk reinsert carrying each surviving message's owning turn_id, reset last_seq; smart Stop keeps its structured composer snapshot only in renderer memory |
| session fork (session.fork) | write a new transcript with remapped message/tool-call ids; copy/remap the checkpoint only when its boundary is included | single tx: clone session configuration, insert child index rows, set last_seq; remove child file on failure | | regenerate branch save | append revision line (with revisionIndex: a refresh line for that existing variant) | index row with message_count (+ is_active flip); a refresh only updates message_count | | turn-completion branch archive (session.saveActiveRevision) | append a revision_live reference line — the branch is still live, so its payload is the transcript — then rewrite only the root user's transcript line for the pager stamp | index row with message_count (+ is_active flip); index rows for other messages untouched | | revision switch | append a full revision line for the live branch's own variant (it is about to leave the transcript, so its reference is materialized), read the target branch, atomic transcript rewrite keeping checkpoints whose anchors survive | flip is_active, rebuild index rows carrying each surviving message's owning turn_id, reset last_seq | | import | write transcript file | one tx per session: session row + index rows; on failure the file is removed | | session delete | remove both session files after row delete | DELETE FROM sessions (cascades); Electron main drops that session's outbox entries (D318) | | project delete (projects.remove) | remove each owned session's files after its row delete | one tx per session (DELETE FROM sessions, cascades) plus the project row and its projectMemory kv entry; the project folder on disk is never touched | | orphaned session restore (boot / session.appendMessage, D318) | leave the live JSONL in place | reinsert the missing sessions row and rebuild index rows from the file; if the file is also gone, append inserts a stub row under the existing id so the outbox can drain |
Rules: user message durable (fsync'd file line) before the turn starts; assistant/tool lines durable at their end events; the streaming assistant reply is additionally checkpointed at most every 1.5 s (D299), and the finished message_end snapshot is checkpointed before the outbox append (D327), so a quit or crash mid-turn loses at most the last checkpoint interval of the in-flight reply plus any tool rows still running. The boot sweep promotes a leftover checkpoint whose final row never landed: as complete when the turn already completed, otherwise as aborted under an aborted turn. completed/error endTurn does not delete an unindexed checkpoint. A user Stop does not touch the checkpoint, because the runtime's own aborted final row is still on its way and removes it on arrival. Electron handshake awaits the outbox drain before a cold session.get. Renderer-side Stop never rewrites a transcript that has a started reply (spec 01 §5.3); its only rewrite is the undo of an unanswered prompt, computed from the full durable transcript merged with the live rows. A checkpoint is installed into the live runtime only after its append succeeds; therefore a failed/crashed checkpoint write leaves the previous full context or previous checkpoint authoritative rather than creating a memory-only state. An append failure after the file write leaves the transcript authoritative while its derived index is repaired by the next rewrite or by replaying an unindexed outbox message with a stale turn reference; repeated ids are deduped keep-last.
The renderer never needs the whole JSONL file to open a session. Its session.get request may specify a zero-based exclusive messageBefore, a positive messageLimit, and a positive contentLimit. Host-core returns only that message window and applies the content cap only to the derived UiMessage projection. The full transcript remains lossless on disk and the sidecar's uncapped session.get path is unchanged for model context, edits, revisions, and other host-owned mutations. The renderer opens with the newest window and requests older windows on demand; the response's messageStart and hasMoreBefore fields support backward paging. Search navigation additionally uses messageAround to center a bounded original-message window on a stable ID, plus exclusive physical messageEnd and hasMoreAfter for forward paging. Only the explicitly selected user/assistant text bypasses the display cap. The retained pane owns that reading window separately from live/model caches; missing targets never fall back to a different message (ADR session-content-search). A nested target additionally resolves its owning Task by tool-call ID and returns that latest capped projection as navigationParent, without adding a physical line to the bounded page. This is derived read-only context, not a new persisted relationship or index. The renderer's unified reading view is shared by ordinary history and search; it never becomes canonical mutation or model input.
A bounded window is served through a per-session transcript layout: the byte offset of every message and compaction line, plus the file identity and complete prefix length those offsets describe. Serving a window seeks to its first selected line instead of parsing the history in front of it, so the common cached read is proportional to the requested page. Consequences:
- The layout is derived data, cached in memory and, when reliable file identity is available, in the sibling
<sessionId>.jsonl.layoutfile. Unix uses the device and inode; Windows uses the volume serial number and file ID. If the filesystem cannot provide a reliable identity, the host skips the disk cache and scans the transcript normally. - The disk cache has an explicit format version and validates its identity, checksum, counts, globally increasing in-range offsets, and the complete-line prefix before reuse. Missing, old, unknown, corrupt, or mismatched caches are ignored and rebuilt from the JSONL source. Cache read/write failures are diagnostic only and do not make the session unreadable.
- A matching identity and complete prefix permits append-only incremental scanning. A transcript rewrite invalidates both in-memory and disk layouts only after its atomic replacement succeeds; delete also removes both. Cache invalidation stays in transcript rewrite/delete ownership, not the generic atomic-swap helper used by unrelated files.
- A point read that finds an invalid indexed line boundary drops both cache layers, scans the complete transcript without consulting the disk cache, and retries once with message counts, page ranges, and message location recomputed from the rebuilt layout. Reads tolerate invalid UTF-8 within a complete line: parseable records use U+FFFD for the damaged bytes, while records that remain invalid JSON or do not match the record schema are skipped with a diagnostic. Physical message-line positions stay consistent across bounded reads. The transcript bytes are never rewritten; actual file I/O errors remain errors.
- New cache files use a nonnumeric version header that older parsers safely reject and rebuild. Downgrading to an older app preserves the transcript and database, though that app's own cache defect may recur. No database or transcript migration is involved.
- A torn trailing line (crash mid-append) is excluded from both the offsets and
file_len, so a later refresh picks it up once the writer completes it. - Line classification reads the
typediscriminator with a single depth-aware scan, never by parsing the line into a value. Only a top-leveltypekey decides the kind: tool results and checkpoint details are open-ended JSON and may nest an object whose owntypenames a line kind.typeis written first on every new line so the scan usually stops at the first key, and lines written before that ordering carry it after their payload and are read by the same scan. - Window offsets are physical message-line positions, the same space the layout counts in. They are never clamped against the session index counter (
last_seq), which is a deduplicated logical count: a file line whose index commit never landed leaves the counter permanently behind the file, and clamping to it cut the newest messages out of the tail. - The compaction chain is always returned whole with any window, because the newest checkpoint drives model context regardless of which messages are visible.
A regenerate or edit-resend names its cut by message identity, not by a count. The renderer holds a bounded, deduplicated, display-filtered view, so an index into it is not a transcript position; the host resolves the named message against its own transcript and rejects a boundary it cannot find rather than truncating at a guessed position.
6. Performance notes
- Single writer + WAL: readers never block; no lock contention by design.
- All timestamps INTEGER Unix ms — smaller rows, integer compares, index-friendly.
- Hot queries and their indexes:
- renderer transcript open → one sequential streaming read of the JSONL file for the requested window; only the bounded page and capped display values cross the host/Electron/renderer boundary
- full transcript consumers → one sequential read of
sessions/<id>.jsonl(no DB), retained for sidecar context and mutations - session list →
idx_sessions_updated_id(updated_at DESC, id DESC) - group-by-project →
idx_sessions_project - badges/cost rollup →
idx_turns_session(latest turn per session) - global token history →
idx_turns_ended_at(completed turns by end time) - artifacts by session → PK; global recent artifacts →
idx_artifacts_time - run history →
idx_task_runs - audit forensics/pruning →
idx_audit_session/idx_audit_ts - notification inbox →
idx_notifications_created; unread filter/count →idx_notifications_unread
- O(1)
seqallocation; noMAX()+1scans anywhere. prepare_cachedon all statements; batch inserts inside one tx (import, replace).- JSON columns are read blind on hot paths (shipped to the renderer as-is); anything filtered or summed is a promoted column by rule.
7. Versioning, v7 reset, and v8-to-v27 migration
PRAGMA user_versionstays the schema authority; future structural changes add ordered Rust migration fns again, each in one transaction, with api.sqlite.v<n>.bakcopy before destructive steps.v7 is a breaking reset (D119), not a migration. Opening a database with
user_version1–6 WAL-checkpoints it, renames it topi.sqlite.v6.bak(removing stale-wal/-shmsiblings), and bootstraps a fresh v7 file. Sessions, providers, and settings from the old file are not carried over; the archive remains for manual recovery. All pre-v7 migration code (v1settings.sqliteimport, v2→v6 chain) is deleted.Fresh installs run the full v27 DDL directly.
Schema v7 first reaches v8, then uses the guarded path. The v7→v8 migration is followed by the same guarded v8→v15 migration; schema-v9 and schema-v10 databases take the same guarded path and receive an exact readable
pi.sqlite.v9.bak/pi.sqlite.v10.bakbefore destructive work.The historical v8-to-v11 core migration is in-place and transactional. Before migration, host-core checkpoints the WAL, then creates the exact readable
pi.sqlite.v8.bak; both happen before destructive work. Within one atomic transaction it:- validates every
sessions.modevalue and mapschattoplan; - parses the structured app settings value and maps its top-level
defaultMode: "chat"to"plan"; - parses each scheduled task's
config_jsonand maps its top-level storedmode: "chat"to"plan", leaving nested extension modes untouched; - preserves/migrates the existing
plan_approvalstable and adds its artifact and execution fields/indexes; - preserves transcripts, turns, revisions, projects, permissions, grants, providers, and scheduled task history;
- validates all new mode values as
plan | goal | agent; and - validates
defaultCommandShellas a known current-platform catalog ID, retaining a valid ID that is temporarily unavailable so normal runtime fallback can select the first available shell; and - adds
plan_approvals.kind(NOT NULL DEFAULT 'plan', checked againstplan | goal) when the column is absent, probingpragma_table_infofirst so a v8 database that already created the table from the current DDL is not altered twice; existing rows are Plan contracts by definition, which is exactly the column default; and - sets
PRAGMA user_version = 11only after every change succeeds; the subsequent v14 migration adds the plugin-session ownership sidecar. A malformed app-settings value, malformed scheduled-taskconfig_json, invalid session or top-level scheduled mode, unknown or wrong-platformdefaultCommandShell, parse, constraint, or write failure fails closed, rolls back the transaction, and leaves the pre-migration schema authoritative; the backup remains available for recovery. LegacyplanApprovalPermissionModeis removed from the app settings JSON during migration; all unrelated settings remain intact.
- validates every
Schema v15 is additive. It adds the
turn_queuetable and its two indexes (D386 / ADR 0213) so the Host-owned turn queue survives a restart; no existing row changes, and api.sqlite.v14.bakcopy precedes the step.Schema v16 is additive. It adds the session collaboration link and delivery tables, their lifecycle indexes, and the nullable
turn_queue.session_message_idbinding (D409 / ADR 0239). Existing conversations, turns, queue entries, and plugin data remain valid. Api.sqlite.v15.bakcopy precedes the migration; boot recovery retains durable queued deliveries but never replays interrupted work automatically.Schema v17 is additive. It adds the nullable
providers.owner_plugin_idownership column and its partial index (ADR 0259), so a provider row a plugin declares incontributes.providersis distinguishable from a user-created one — every pre-v17 row keeps a NULL owner. Api.sqlite.v16.bakcopy precedes the step. The v15→v16 session-collaboration step now stamps16(its own version) instead of the latest schema constant, so a v15 file can walk both steps in one launch.Schema v20 is additive. It adds nullable
turn_queue.user_message_idandturn_queue.voice_origin_json; existing queue rows remain valid and unset. The migration keeps a v19 backup, and queue entries remain held until the existing Agent Host controller attaches.Schema v22 is additive. It replaces
idx_sessions_updatedwithidx_sessions_updated_id(updated_at DESC, id DESC)for session-list ordering. It changes no rows or persisted fields; a v21 backup precedes the migration.Schema v23 is additive. It adds
sessions.title_sourcewith defaultlegacy. Pre-existing v22 titles remainlegacy, safely protecting deliberate renames from automatic replacement. The migration keeps a v22 backup.Schema v24 is additive. It adds the
worktreesregistry andprojects.worktree_setup_commandfor isolated sessions (ADR 0329). The migration keeps a v23 backup.Schema v25 is additive. It adds nullable
models.discovery_state(§ 4). The migration keeps a v24 backup.Schema v26 is additive. It marks the storage-private lossless tool-image envelope (#1868). It is a version-only migration with a v25 backup: it stops older applications from misreading an encoded transcript file, without altering user configuration or rewriting transcripts at startup.
Schema v27 is additive. It adds
sessions.fast_mode(ADR 0334), the session/fastswitch, defaulting to0for every existing session. Forks copy it; scheduled runs and delegated workers start at0. The migration keeps a v26 backup.Schema v14 is additive. It adds nullable
sessions.deleted_at, the partial deletion index, andsession_import_origins. Existing sessions stay active and have no origin rows. The migration runs in the same guarded transaction and leaves the pre-v14 backup until the new schema passes its integrity checks.
The largePasteThreshold app setting is additive JSON rather than a database schema field. Host settings reads normalize a missing, malformed, or out-of-range value to 600, and settings writes validate the integer range of 1–1,000,000. Existing databases therefore gain the default lazily without a destructive migration or a second settings store.
- Plan and Goal artifacts are never reconstructed from transcript content. On startup, one transaction marks every
pendingapproval and everyqueuedorrunningexecution state inplan_approvalsasinterrupted; associated running turns are markedabortedbefore RPC service begins. Pending sessions remain in their contract mode and already-approved queued/running sessions remain Agent. No approval response or execution from before the restart is accepted. - The transcript file format carries its own
schemafield in the session header line; unknown line types are skipped, so additive file-format growth needs no reset.
8. Retention & maintenance
- audit_log: prune rows older than 90 days (configurable) at boot;
incremental_vacuumafterwards. - task_runs: keep last 100 per task (prune with the same boot pass).
- notifications: enforce the newest-200 global cap after every insert and at boot as a defensive repair; rows otherwise survive restart until cleared, pruned, or cascade-deleted with their session.
- transcript files: user data, never pruned or swept — removed only with their session (delete or scheduled-run cleanup). Orphan files (session row gone, file present) are preserved, not garbage-collected: the file is the source of truth and a future re-index can recover it.
- logs rotate at the file layer (D082); sessions are never auto-deleted.
- Attachment GC (later): sweep
attachments/for hashes unreferenced by any transcript file.
9. Extensibility playbook
| need | mechanism | migration? |
|---|---|---|
| new message content kind (citations, diffs, voice) | new block type in the transcript file | no |
| new per-response metadata | meta key in the message line | no |
| new transcript line kind | new JSONL type (readers skip unknown) | no |
| new config domain (MCP servers, memories) | kv namespace | no |
| new provider/task knob | config_json key | no |
| new model capability | value in capabilities_json | no |
| new queryable/filterable field | promoted column | yes (additive) |
| new entity with relations (knowledge base, connectors) | new table | yes |
Rule of thumb: files/JSON for payloads the host merely stores and ships; columns for anything the host filters, joins, sums, or indexes.
10. Secrets rules (unchanged)
- The renderer never persists secrets
- OS safeStorage remains the target primary backend; the shipped store is the encrypted-file backend (
file_fallback) with its machine key beside the ciphertexts, and Settings must state that risk - Secret values never in SQLite; only
secrets_metabookkeeping - Exported sessions exclude secrets by default
11. Acceptance
- Sessions and transcripts survive restart byte-identically (blocks, usage, tool results) — content reloads from
sessions/<id>.jsonlwith no UI-projection loss - Transcript load for a 5k-message session is one sequential file read; no message-content SQL on the hot path
- Kill -9 during a running turn: boot marks the turn
aborted, transcript intact up to the last fsync'd message line; a torn trailing line is skipped on read - Kill -9 between file append and index commit: the message still renders after restart; search misses it only until the next transcript rewrite
- Opening a pre-v7 database archives it as
pi.sqlite.v6.bakand starts a fresh v7 file; reopening the fresh file is a plain open - Scheduled tasks CRUD + run history round-trip through host RPC only
- FTS finds CJK and ASCII substrings across sessions; deleting a session removes its index entries and both session files
- Plugin uninstall clears
kv(plugin:<id>)in one statement - Resetting sidebar preferences changes no
projects,sessions, or transcript data; retained paths and organization choices survive a normal renderer restart when preferences are available - A tool call for session A resolves A's persisted project root even after the visible workspace switches to project B
- A session's thinking level survives restart
- Assistant thinking blocks round-trip independently from final answer text; the derived search text excludes thinking content
- Regenerated assistant variants survive restart in
sessions/<id>.revisions.jsonl; the live root user turn reloads withrevisionCount/activeRevision, the pager can restore any archived branch, and switching branches never rewrites the revisions file. A branch that is still live is referenced, not copied, so repeated turns over one branch cost the file nothing; a branch that leaves the transcript is stored in full first, and a file written only by an older build still reads back message-for-message. - Completed and failed turns atomically create one durable notification; repeated terminal updates do not duplicate it, aborted turns create none, and the newest-200 cap survives restart
- Notification list/unread, mark-read, mark-all-read, clear, and session cascade deletion use the documented indexes/transactions without changing turn or transcript data
- Schema v7 first reaches v8 and then uses the guarded v8→v11 path. The v8→v11 migration is one atomic transaction with a WAL checkpoint and exact readable
pi.sqlite.v8.bakbefore destructive work; schema v9 and v10 receivepi.sqlite.v9.bak/pi.sqlite.v10.bak. Persisted session, app-default, and scheduledchatvalues map toplan, sessions/transcripts andplan_approvalsartifact/ execution fields survive,plan_approvals.kindis added with existing rows defaulting toplan, and malformed app settings/scheduled config, invalid modes, or invalid default shells fail closed with the pre-migration schema intact - SubmitPlan and SubmitGoal write exact Markdown bytes to a unique
.pi/plan/*.mdor.pi/goal/*.mdfile with SHA-256 and size; title/question stay structured and renderer reload retains only the pending row and original absolute deadline - Full process restart marks pending/queued/running approval rows interrupted, aborts associated turns, performs no replay, keeps pending sessions in their contract mode, keeps already-approved interrupted sessions Agent, and rejects stale responses
- A scheduled or unattended Plan or Goal run fails before provider/artifact/ queue work with
PLAN_REQUIRES_INTERACTIVE_SESSION; no background path auto-approves either kind - Schema v14 plugin imports have host-generated session ids, one origin row per session,
(pluginId, source, externalId)idempotency, no project or model binding unless an explicit host-createdprojectIdis supplied, ownership-scoped reads/mutations, and recoverable trash before purge. - Schema v16 collaboration rows preserve source/target Session IDs and idempotency across retries, bind deliveries to their actual target turns, persist transcript provenance, create no duplicate completion callback, enforce permission ceilings and hop limits, retain queued work across a restart without replay, and cancel without deleting the target session.
Active-turn steering transcript reservations
An accepted steering input is journaled through Electron's existing message outbox with meta.steering: true, round-tripped as UiMessage.steering. Smart Stop preserves that input even after renderer reload loses submission state. If an assistant is still streaming, its provisional snapshot is queued first to reserve its transcript position before the new user row. The host stores the provisional row and an in-flight checkpoint, including an empty reservation so crash recovery can settle it. Further stream checkpoints remain valid while the indexed assistant has status: streaming.
session.appendMessage retains idempotent replay for completed messages. Its narrow exception lets a terminal assistant replace an indexed streaming assistant with the same session/message id. It updates exactly that transcript line and search text, retaining sequence, owning turn and every other row. Late partial snapshots and duplicate terminal snapshots cannot overwrite the settled result. Recovery promotes the latest checkpoint in that same position. The outbox likewise keeps a newer snapshot that replaces an append while its host call is still pending. An optional turnId is retained only when the turn exists and belongs to the target session; a missing or cross-session turn is logged and omitted without rejecting or losing the message. If an older failed append already wrote that message to JSONL, replay updates those lines and rebuilds the deduplicated index in transcript order rather than appending again. If messages.id already belongs to another session, the host remaps to {sessionId}:{id} before any JSONL write; a replay of the original id is a no-op against that remapped row. The outbox treats UNIQUE constraint failed: messages.id as an ack and keeps draining (D444). A permanently rejected append whose host error carries a PERMISSION_DENIED: prefix is likewise dropped so the FIFO can continue; PLUGIN_PERMISSION_DENIED and other host failures still pause while their retry budget lasts (D597). A failure the outbox cannot classify is retried within the same host generation on a bounded backoff (5s doubling to 60s) and quarantined after five consecutive failures into session-message-outbox.dead-letter.json (newest 256 appends, oldest discarded first), carrying its payload, session id, timestamp, and host error, so one unclassifiable head cannot starve every session's writes (D656). A restarted host is a new generation and retries the head at once. Quarantined appends are never replayed automatically, and session.delete drops that session's quarantined appends too. When the 1024-entry cap remains full after a flush attempt, enqueue logs the rejected key/session and rejects; it does not report that entry as queued. Generic foreign-key errors are not treated as acknowledgements. Steering into a claimed collaboration delivery turn is extra human input: it must target that delivery's session, is exempt from the delivery content/attachment contract, does not inherit the delivery origin, and has any client-supplied session_message stripped. No schema migration is required.
12. Native Pi session authority (ADR 0254)
Native Pi v3 sessions are a second, explicitly source-discriminated transcript authority owned by the Node agent sidecar. They are never inserted into SQLite and never copied to the Desktop transcript directory. session.list merges their projections with Rust-owned Desktop summaries, and session.get routes by the opaque native-pi: id.
Discovery always walks the Pi agent session root recursively (<agentDir>/sessions, default ~/.pi/agent/sessions). It also scans, without recursion, each sessionDir the pinned coding-agent SettingsManager resolves: an absolute or ~ value from global settings, and the merged global-plus-project setting for each known local project. Session files in a subdirectory of a configured sessionDir are ignored by that scan. A relative path resolves from that project's working directory, not from the Desktop process cwd. Remote project ids are skipped, and at most 500 local project paths are considered. A configured directory whose real path is the default root, or is inside it, is not scanned again. A file is listed only when its real path stays inside the default root or one of those directories. A symlink that escapes is omitted, and an in-directory symlink alias of a session file is not a second row. A nested file under a custom sessionDir is omitted when that directory is outside the default root. A sessionDir inside the default root is not scanned again, and the recursive default walk can still list nested JSONL there. Detail, prompt, and fork authorize a file against the default root, the session directories for the current project list, and any session directory that already admitted a file in this process, so a later empty project list does not revoke an open of a session that was already discovered. A child fork is still published beside its parent. A project Desktop has not yet recorded does not contribute a relative sessionDir. An absolute global sessionDir is scanned even when no project is known.
Discovery deduplicates native files that share the same JSONL header.id, keeping the projection with the newest transcript updatedAt. The selected file retains its path-derived opaque session id; duplicate files are not rewritten or deleted, and their paths are omitted from the in-memory lookup map for the current scan.
Detail reads take an immutable byte snapshot, parse it into an in-memory SessionManager, and follow the current native branch. They must not call persistent SessionManager.open, because that API may repair a missing newline or rewrite an older format. Unknown/custom entries and unknown fields remain in the source bytes; context-bearing custom messages and native compaction/tree semantics are resolved by the pinned coding-agent SDK.
A native prompt opens the original file only after exact-v3, newline, cwd, trust, saved-provider/auth, canonical-path, identity, and lease checks pass. AgentSession and SessionManager append the native entries. Desktop host turn and transcript append APIs are not invoked. Rename, delete, project move, revision, Plan/Goal, collaboration, and queue operations remain unsupported for native sessions in this slice. Forking is supported as described here and in the runtime spec.
The 0.87.1 context_edit entry is part of the native v3 JSONL branch. It changes only the SDK-built model projection by omitting or replacing a target message; the original line and renderer history remain intact. It is not copied into the Desktop transcript or SQLite, and needs no Desktop schema migration. The lease guard covers SessionManager.appendContextEdit alongside the other native append methods.
A native fork writes exactly one new v3 JSONL child in the parent's session directory. Branch extraction runs against an in-memory manager over the parent snapshot, then child title/parent saved model/thinking fallbacks are appended in memory. Publication is a full write to an exclusive non-jsonl temporary file in the same directory, followed by a same-directory hardlink to the final <timestamp>_<session-id>.jsonl name. The staged file must still match the captured device/inode/size/hash before the link, and the published child must match that same identity and hash before the child detail is projected or registered; a mismatch fails closed without returning a child. Cleanup removes only files whose device/inode and content still match what this fork wrote (complete files by size+hash, partial staging writes by byte prefix); foreign files after a failed no-clobber link are never removed. The parent file, its leaf, and any live runtime are never modified. The child header carries parentSession with the canonical source path; that path stays inside the sidecar.
The first slice has no projection cache or async scan bound; every list still reads/parses complete files. Caching by canonical path/file identity/size/mtime and bounded asynchronous scanning remain deferred performance work.
Provider display order
kv(ns="app", key="providers.order") stores an ordered array of provider IDs. Host-core owns updates through providers.reorder; missing metadata preserves creation order, new IDs follow saved IDs, and deleted IDs are ignored. This preference does not rewrite provider configuration or require a schema migration.
Scheduled calendar provenance
The optional config_json.calendarConfigured boolean records explicit calendar intent separately from the schedule object required by Hourly intervals. Legacy Daily/Weekly rows with a saved schedule infer calendar intent; legacy Hourly rows retain their fields but require explicit calendar confirmation when converted. Known intent survives cadence changes and database reopen. This additive JSON key needs no table or schema-version migration. Older versions ignore the key and cannot enforce the new conversion guard.
Physical operation usage ledger
Optional operation ID, origin, physical account/model and cost status augment existing message/turn usage. session.recordUsage merges identities into the existing turn usage_json; no schema migration or historical rewrite is needed. Identified records are idempotent across event replay, outbox retries, tool results and parent/subagent rollups. Legacy token-only rows remain readable and additive. An unknown price is distinct from a known zero price; partial known costs remain on the individual operations. Late usage targets its captured turn and does not revive it or debit the currently active turn. Immediate nested parent and owning Task remain separate optional transcript/event fields.