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

> Understand PI-Desktop's data persistence. Explore its SQLite and JSONL architecture for secure storage of conversations, settings, and credentials. Learn how it protects sensitive data.

- Repository: [Lan/PI-Desktop](https://github.com/vastsa/PI-Desktop)
- Tags: architecture
- Published: 2026-09-12

---

**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`](https://github.com/vastsa/PI-Desktop/blob/main/crates/host-core/src/db.rs) as `SCHEMA_VERSION = 15`, indicating an actively maintained migration history.

The `Database` struct exposed from [`db.rs`](https://github.com/vastsa/PI-Desktop/blob/main/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`](https://github.com/vastsa/PI-Desktop/blob/main/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`](https://github.com/vastsa/PI-Desktop/blob/main/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:

```rust
// 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:

```rust
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`](https://github.com/vastsa/PI-Desktop/blob/main/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`](https://github.com/vastsa/PI-Desktop/blob/main/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:

```rust
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`](https://github.com/vastsa/PI-Desktop/blob/main/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`](https://github.com/vastsa/PI-Desktop/blob/main/db.rs).
- **JSONL transcripts** (`<data_dir>/sessions/<session_id>.jsonl`) store conversation history in append-only files handled by [`transcripts.rs`](https://github.com/vastsa/PI-Desktop/blob/main/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`](https://github.com/vastsa/PI-Desktop/blob/main/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`](https://github.com/vastsa/PI-Desktop/blob/main/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`](https://github.com/vastsa/PI-Desktop/blob/main/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`](https://github.com/vastsa/PI-Desktop/blob/main/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`](https://github.com/vastsa/PI-Desktop/blob/main/transcripts.rs), advanced users can read or archive these files directly from the filesystem for backup or analysis purposes.