SQLite Transcript Store

Concurrent, indexed gateway session persistence — now the default

SqliteTranscriptStore keeps every gateway session as one row in a WAL-preferring SQLite database (falls back to DELETE on hostile filesystems) — the default backend whenever session.persist is on. See Hardened SQLite Storage for the fallback behaviour.

Nothing changes for a single-user Python Agent in a script — session APIs are identical. Only the gateway changes: with session.persist: true, transcripts now live in one sessions.db with concurrent readers and indexed routing instead of per-session JSON files guarded by a FileLock.

What changed by default

With session.persist: true, the gateway now builds SqliteTranscriptStore unless you opt out with session.store: file. And session.persist itself now defaults to true — see Gateway Session Persistence.

The change is backward-compatible for API callers (public methods are identical) but visible on disk. External tools pointed at *.json files should either read sessions.db or set session.store: file.

Quick Start

1. Default (YAML) — SQLite is on

   # gateway.yaml
   session:
     persist: true
     store: sqlite            # NEW — sqlite (default) | file
     persist_path: ~/.praisonai/sessions/

   session.persist: true is enough — store: sqlite is the default. Transcripts land in ~/.praisonai/sessions/sessions.db.

2. Keep the legacy JSON layout

   # gateway.yaml
   session:
     persist: true
     store: file              # restore pre-#3409 JSON-per-session behaviour
     persist_path: ~/.praisonai/sessions/

   Use store: file when external tooling grep-scans the individual *.json transcripts.

3. Python embedder using the store directly

   from praisonaiagents import Agent
   from praisonaiagents.session import SqliteTranscriptStore
   
   store = SqliteTranscriptStore(db_path="~/.praisonai/sessions/sessions.db")
   agent = Agent(name="Assistant", instructions="Be helpful", session_store=store)

   An injected store always wins over the session.store config field.

Agent scripts need no changes — the gateway persists sessions for you:

from praisonaiagents import Agent

agent = Agent(name="Assistant", instructions="Be helpful")
# The gateway persists this agent's session via SqliteTranscriptStore
# — no code change, no new imports required in agent scripts.

How It Works

Two gateway processes handling the same session serialise inside SQLite, not on a directory scan — the read-modify-write window the old FileLock could lose is closed by BEGIN IMMEDIATE.

Every read-modify-write (add_message and friends) runs its SELECT + INSERT OR REPLACE inside one BEGIN IMMEDIATE transaction, so a second gateway process cannot slip a read between this one’s read and its write.

Which backend to pick

Configuration Options

SqliteTranscriptStore constructor:

SessionConfig.store (gateway YAML):

Selection rules:

• session.persist: false → no store, regardless of store:.
• session.persist: true + store: sqlite (default) → SqliteTranscriptStore.
• session.persist: true + store: file → DefaultSessionStore.
• An injected session_store=… always wins — the field only applies when the gateway builds the store itself.
• If sqlite3 is unavailable, the gateway falls back to the JSON store and logs a warning.

PRAGMAs applied on first connect (you don’t need to touch these):

Schema (one row per session — inspect with the sqlite3 CLI if needed):

CREATE TABLE sessions (
    session_id         TEXT PRIMARY KEY,
    data               TEXT NOT NULL,       -- JSON payload (whole SessionData)
    agent_name         TEXT,
    gateway_session_id TEXT,
    agent_id           TEXT,
    updated_at         TEXT
);
CREATE INDEX idx_sessions_gateway    ON sessions(gateway_session_id);
CREATE INDEX idx_sessions_agent      ON sessions(agent_id);
CREATE INDEX idx_sessions_agent_name ON sessions(agent_name);
CREATE INDEX idx_sessions_updated    ON sessions(updated_at);

Portable export/import

SqliteTranscriptStore implements the same PortableSessionStoreProtocol as DefaultSessionStore and SqliteSessionStore, but every portable operation reads and writes database rows — never JSON sidecars.

from praisonaiagents.session import SqliteTranscriptStore

store = SqliteTranscriptStore(db_path="~/.praisonai/sessions/sessions.db")

payload = store.export_all()                                 # scans DB rows, not *.json
payload = store.export_session("s1", include_lineage=True)   # follows DB lineage
report  = store.import_sessions(payload, overwrite=True)     # writes a DB row

The restore path reuses the inherited _save_imported_session orchestration, so the no-window-truncation restore contract still holds: a destination configured with a small truncating active_window / retention="truncate" will not drop exported history on import.

See Gateway Session Portability for the CLI, Python helpers, lineage flags, and ImportReport schema — the same surface works unchanged against the SQLite transcript backend.

Common Patterns

Default gateway config — SQLite is on with just persist: true:

session:
  persist: true

Explicit legacy fallback — for tooling that scans JSON transcripts:

session:
  persist: true
  store: file

Custom db_path on a shared volume — WAL + BEGIN IMMEDIATE give cross-process append-safety on a real filesystem:

from praisonaiagents.session import SqliteTranscriptStore

store = SqliteTranscriptStore(db_path="/mnt/shared/praisonai/sessions.db")

Best Practices

Related