@pai/storage-sqlite

Package: @pai/storage-sqlite

Persists PAI thread state in a local SQLite database. This provider is intended for durable local applications, scripts, and single-host services that need stronger transactional coordination than JSON files without operating a networked database.

The package requires Node 24 and uses the built-in node:sqlite driver, so it has no native npm dependency or postinstall build.

Quick start

import { createAgentRuntime } from "@pai/core";
import { createSQLiteThreadStore } from "@pai/storage-sqlite";

const storage = createSQLiteThreadStore({
  filename: "./.pai/threads.db",
});

const runtime = createAgentRuntime({
  agent,
  scopeKey: (identity) => identity.workspaceId,
  storage,
});

// During application shutdown:
await storage.close();

The factory opens and initializes the database immediately. Missing parent directories are created automatically. Use :memory: for an ephemeral SQLite database.

Options

type SQLiteThreadStoreOptions = {
  /** SQLite file path, file URL, or `:memory:`. */
  filename: string | URL;

  /** Time to wait for another SQLite writer. Defaults to 5,000 ms. */
  busyTimeoutMs?: number;

  /** Override the provider clock in tests. */
  now?: () => Date;

  /** Persistent database journal mode. Omit to retain the current mode. */
  journalMode?: "delete" | "wal";
};

The store owns its connection. close() is safe to call repeatedly, and all later store operations reject after it has closed.

Storage model

The provider owns a normalized schema with one metadata row and separate tables for thread roots, runs, queued items, and messages. Each logical record keeps a strict JSON payload alongside checked scalar columns used for keys, ordering, filters, visibility, and relationships. Transcript and queued messages share a table, which preserves message-id uniqueness across both locations. Foreign keys keep child records attached to their thread, run, or queued item.

The tables are SQLite STRICT and WITHOUT ROWID, with indexes for thread listing, bounded child pages, stable-id reads, and relationship validation. Opaque PAI string keys are encoded without imposing application-specific syntax.

The current physical schema version is 1. On open, the provider creates its namespaced objects only when none exist, then validates the complete owned schema and metadata. It refuses partial, unversioned, colliding, or altered provider objects and does not currently migrate other schema versions.

Thread listing reads only indexed root candidates. A selected thread read runs in one read transaction and queries only the requested IDs or a bounded page of the requested child families. It does not materialize unrelated transcript, run, or queue records.

Transactions and concurrency

Every commit starts a SQLite BEGIN IMMEDIATE transaction, reads the current root under the write lock, compares the complete ThreadVersion, and applies the write set atomically. Updates load and mutate the explicitly affected child rows plus the indexed relationship projections needed to validate the result; they do not load or rewrite the complete aggregate. Truncation work is proportional to the affected suffix. Root-only updates and thread deletion do not read child history.

Separate connections and processes using the same database file therefore cannot both accept the same observed version. SQLite still serializes writers.

Version and lease-precondition races return the ordinary conflict result and write nothing. Invalid logical changes roll back the transaction. Commit times are advanced past a transactionally maintained high-water mark while the write lock is held, so already-open provider instances retain monotonic ordering, including after deletion.

Page tokens are stateless, query-bound, and scope-bound. They remain usable after closing and reopening the provider against the same database.

Journal mode and maintenance

When journalMode is omitted, the provider leaves the database's current journal mode unchanged. Setting it to "wal" or "delete" requests that mode only after schema ownership and compatibility have been established. SQLite stores this setting for the database, so it affects later connections as well as the one opened by this provider. WAL can improve reader/writer concurrency for a dedicated local database, but it does not permit concurrent writers.

Deleting records normally leaves reusable pages in the database file. Call await storage.compact() during a quiescent maintenance window to run SQLite VACUUM and return free pages to the filesystem. Do not call it concurrently with application traffic.

Deployment boundary

node:sqlite exposes synchronous database calls. Those calls can block the Node.js event loop while SQLite performs I/O or waits up to busyTimeoutMs for another writer. Use a networked transactional provider for multi-host deployments, high write contention, or latency-sensitive servers where embedded database work must not share the application event loop.

Do not place the database on a network filesystem unless its locking and durability behavior is known to satisfy SQLite's requirements.

Conformance

createSQLiteThreadStore passes the complete @pai/storage/test conformance suite, plus persistence, cross-connection and cross-process compare-and-swap, restart-safe page tokens, bounded-query, schema integrity, journal-mode, compaction, rollback, and lifecycle tests.