How Data Persistence Works in PI-Desktop: SQLite and JSONL Architecture

PI-Desktop uses a dual-layer persistence system where structured data lives in an SQLite database managed by the Rust host-core, while conversation history is stored in append-only JSON Lines files, ensuring the Electron renderer never touches sensitive data directly.

The vastsa/PI-Desktop repository implements a frozen architecture that strictly separates data persistence concerns between the UI layer and the core engine. Understanding how this open-source AI desktop client manages data persistence reveals a security-first design that protects credentials and conversation history through careful architectural boundaries.

The Dual-Layer Persistence Architecture

PI-Desktop splits persistent data across two distinct storage mechanisms based on access patterns and sensitivity requirements. This separation ensures that structured configuration data receives ACID guarantees while conversation transcripts remain portable and append-optimized.

SQLite Database for Structured Data

All cross-session structured data resides in a single SQLite file owned exclusively by the Rust host-core component. The database schema version is defined in crates/host-core/src/db.rs as SCHEMA_VERSION = 15, indicating an actively maintained migration history.

The Database struct exposed from db.rs provides high-level access helpers including get_setting and set_setting for arbitrary JSON-encoded configuration, and upsert_project_row for project metadata management. Provider credentials—including API keys and OAuth tokens—live in dedicated providers and credentials tables, accessed via the get_secret_for_provider function in crates/host-core/src/providers/credentials.rs. This design keeps secrets entirely out of the UI layer; the renderer process never accesses the SQLite file directly.

JSONL Files for Conversation History

Conversation sessions utilize an append-only JSON Lines format stored in <data_dir>/sessions/<session_id>.jsonl. As implemented in crates/host-core/src/transcripts.rs, each transcript file begins with a header line containing session metadata ({"type":"session",…}), followed by one line per MessageRecord containing messages, tool results, and checkpoint metadata.

The transcripts module exposes append_message for atomic writes, read_transcript for windowed reads, and write_transcript for compaction operations. When a session ends, remove_session_transcripts handles cleanup of the JSONL file and temporary leftovers, ensuring no orphaned data remains on disk.

How Settings and Credentials Are Stored

The SQLite layer handles all mutable configuration state through the Rust host-core API. Settings are stored as JSON blobs accessed via key-based lookups, while credentials receive additional isolation through dedicated tables.

Reading and Updating Application Settings

The following pattern demonstrates how to retrieve and modify settings such as the default command shell:

// Get the current app settings from the DB.
let settings = db.get_setting("app")?;
// Modify the JSON value.
let mut json = settings.unwrap_or_default();
json["defaultCommandShell"] = json!("zsh");

// Persist the new settings atomically.
db.set_setting("app", &json)?;

Secure Credential Retrieval

API keys and provider secrets never traverse the IPC boundary to the renderer. Instead, the host-core exposes controlled access functions:

use pi_desktop::providers::get_secret_for_provider;

let secret = get_secret_for_provider(&db, "openai")?;
println!("Stored API key: {}", secret.api_key);

This implementation in crates/host-core/src/providers/credentials.rs ensures that sensitive tokens remain encrypted at rest and accessible only through audited Rust code paths.

How Conversation History Is Managed

The transcript system in crates/host-core/src/transcripts.rs optimizes for write-append semantics and efficient windowed reading. Unlike the SQLite database, which handles random-access configuration data, transcripts grow monotonically and support compaction to control file size.

Appending Messages to Sessions

When the AI assistant generates a response, the host-core appends it to the session transcript using atomic file operations:

use pi_desktop::transcripts::{append_message, MessageRecord};

let msg = MessageRecord {
    id: uuid::Uuid::new_v4().to_string(),
    role: "assistant".into(),
    content: "Sure, here's the code you asked for.".into(),
    // …other fields…
};
append_message(db.data_dir(), "session-1234", &msg)?;

The append_message function handles the JSON serialization and atomic write, ensuring that conversation history survives crashes without corruption. The read_transcript function supports pagination parameters for the UI to load conversation windows efficiently, while background compaction prevents unbounded file growth.

Security and Architectural Boundaries

PI-Desktop enforces a strict process model that preserves data safety: Renderer → Preload IPC → Electron Main → Rust Host Core. Because the SQLite database at <data_dir>/pi-desktop.db is accessible only to the Rust host-core, all persistence operations must traverse this boundary. This isolation prevents the Electron renderer—which executes untrusted JavaScript—from accessing credentials or directly manipulating conversation metadata.

The global state orchestration in crates/host-core/src/state.rs manages settings merging and synchronization between these layers, ensuring the UI receives consistent views of persisted data without exposing raw database handles.

Summary

• SQLite database (<data_dir>/pi-desktop.db) stores settings, project metadata, and provider credentials with ACID guarantees, managed exclusively by the Rust host-core via db.rs.
• JSONL transcripts (<data_dir>/sessions/<session_id>.jsonl) store conversation history in append-only files handled by transcripts.rs, supporting atomic appends and compaction.
• Security isolation ensures the Electron renderer never touches the SQLite database directly; all data flows through the host-core process boundary.
• Credential protection places API keys in dedicated tables accessed only through get_secret_for_provider, keeping secrets out of the JavaScript execution context.

Frequently Asked Questions

Where does PI-Desktop store conversation history?

Conversation history is stored in JSON Lines format at <data_dir>/sessions/<session_id>.jsonl, where <data_dir> represents the application's data directory. The transcripts.rs module in the Rust host-core manages these files using append-only writes and supports compaction to manage file size over long sessions.

How are API keys and credentials secured in PI-Desktop?

API keys and OAuth tokens live in dedicated SQLite tables (providers and credentials) accessed exclusively through the Rust host-core. The get_secret_for_provider function in crates/host-core/src/providers/credentials.rs provides the only code path for retrieving these values, ensuring credentials never enter the Electron renderer process or JavaScript execution context.

What database does PI-Desktop use for settings?

PI-Desktop uses SQLite for all structured data persistence, with the schema version currently set to 15 in crates/host-core/src/db.rs. The Database struct exposes get_setting and set_setting methods for JSON-encoded configuration storage, while crates/host-core/src/state.rs handles settings merging and global state orchestration.

Can PI-Desktop conversation transcripts be accessed externally?

Yes, the JSONL transcript files stored in <data_dir>/sessions/ are standard text files containing one JSON object per line, starting with a session header followed by message records. While the application manages these through append_message and read_transcript functions in transcripts.rs, advanced users can read or archive these files directly from the filesystem for backup or analysis purposes.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}