Chapter 17 · Session Persistence and SQLite Backend

Chapter goals: Use the SQLite session backend to persist the Agent's conversation history,实现 session recovery after process restart, and multi-session management.

17.1 Why Session Persistence Is Needed

So far, the Agent's messages have lived in memory — all lost on process exit. Real products need:

• Restart recovery: the user continues chatting after the service restarts for an upgrade;
• Multi-session: the service handles multiple users/tasks simultaneously, each with isolated context;
• Audit replay: look back at what decisions the Agent made afterward.

The Pi ecosystem's answer is a separate SQLite session backend package.

17.2 Installing the Independent Session Backend Package

For dependency minimization, the SQLite backend is not in the core package. Official explanation:

The SQLite session backend and the node:sqlite adapter live in a separate package, @earendil-works/pi-session-backend-sqlite-node, so the core package does not pull in runtime builtins or native SQLite dependencies by default.

npm install @earendil-works/pi-session-backend-sqlite-node

The benefit of this design: the core package does not carry Node built-in modules (node:sqlite) or native dependencies, so browser bundling and edge runtimes are unaffected.

17.3 The Backend's Adapter Design

This package's architecture is sophisticated: the backend accepts a runtime-specific SQLite factory rather than being bound to a specific implementation:

// The backend accepts a runtime-specific SQLite factory,
// leaving room for other forms of session backends in the future
import { createSqliteSessionBackend } from '@earendil-works/pi-session-backend-sqlite-node';

const backend = createSqliteSessionBackend({
  // Inject the node:sqlite factory implementation (built into Node 22+)
  sqliteFactory: /* node:sqlite adapter */,
  path: './sessions.db', // Session database file
});

Official extension point

The document explicitly mentions allowing other session backends to ship as their own packages in the future — you can implement your own backend (e.g. PostgreSQL) as long as it satisfies the同一 interface contract.

17.4 Managing Multiple Sessions with sessionId

Both Agent and pi-ai request options have a sessionId concept — it is the common key for provider cache affinity and session persistence:

import { Agent } from '@earendil-works/pi-agent-core';

const agent = new Agent({
  initialState: {
    systemPrompt: 'You are a project assistant.',
    model,
    tools,
    messages: [], // History messages can be loaded from the backend at startup
  },
  streamFn: models.streamSimple.bind(models),
  sessionId: 'session-123', // Used for provider caching and session association
});

// You can also switch sessions during runtime
agent.sessionId = 'session-456';

In a multi-user HTTP service, the typical pattern is "each request carries userId → look up or create that user's sessionId → restore messages from the backend → process → write back":

// Multi-user session management pseudocode
async function handleChat(userId: string, text: string) {
  const session = await backend.load(userId);       // Load history
  const agent = spawnAgent(session?.messages ?? []); // Cold start or hot recovery
  agent.sessionId = userId;

  await agent.prompt(text);
  await backend.save(userId, agent.state.messages);  // Persist
  return lastAssistantText(agent.state.messages);
}

Restoring history simply fills the message array back into initialState.messages, and the Agent continues from where it left off:

// Hot recovery: inject persisted history messages into initial state
const saved = await backend.load('session-123');
const agent = new Agent({
  initialState: {
    systemPrompt: 'You are a project assistant.',
    model,
    tools,
    messages: saved?.messages ?? [],   // Hot start with history if present; cold start with empty array if not
  },
  streamFn: models.streamSimple.bind(models),
});

17.5 Lightweight Alternative: JSON Serialization

For small scenarios that do not need a multi-session database, directly serializing context to JSON is sufficient (details expanded in the next chapter). Trade-offs between the two approaches:

Approach	Suitable Scenario	Capabilities
JSON file	Single user, low-frequency writes	Simplest, no dependencies
SQLite backend	Multi-user, high-frequency writes, need queries	Transaction-safe, indexable

Chapter Summary

• Session persistence solves three needs: restart recovery, multi-user isolation, audit replay;
• The SQLite backend lives in the独立 package @earendil-works/pi-session-backend-sqlite-node; the core package stays zero-native-dependency;
• The backend decouples via an adapter design that injects a SQLite factory; the官方预留s extension space for custom backends;
• sessionId is the unified identifier for cache affinity and session management; it can be assigned directly on the Agent;
• Small scenarios can use JSON serialization for lightweight persistence.

🧪 随堂测验

点击你认为正确的选项。答错时会展示正确答案与原因解析。

1. Why is the SQLite session backend split into an independent package rather than included in pi-agent-core?

2. What architecture design does pi's SQLite backend adopt?

3. What is the purpose of the sessionId property on an Agent instance?

4. For a single-user small tool that wants to save conversation history, what is the lightest approach?

🛠️ Hands-on Practice

1. Use the SQLite backend to retrofit the CLI from Chapter 19 (preview): after exiting and re-entering the program, conversation history is automatically restored.
2. Implement a listSessions() function: list all sessions in the database along with their message count and last active time.
3. Bench-mark comparison: measure read/write latency for a session with 1000 messages under both JSON file and SQLite approaches.

Next we dive deeper into context serialization details and browser-side execution.