@arnilo/prism-session-store-postgres

Optional PostgreSQL adapter implementing Prism SessionStore, RunLedger, ProductionPersistenceStore, owned RunFeedbackStore, generic CheckpointStore, and atomic LeaseStore over pg.

Install

npm install @arnilo/prism-session-store-postgres @arnilo/prism pg

Usage

import { Pool } from "pg";
import { createAgentSession } from "@arnilo/prism";
import { createPostgresPersistence } from "@arnilo/prism-session-store-postgres";

const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 10 });
const persistence = await createPostgresPersistence({ pool, schema: "prism" });

const session = createAgentSession({
  sessionStore: persistence,
  runLedger: persistence,
  // ...
});

// When finished:
await persistence.close();
// pool.end() if you own the pool

The same object satisfies session/run/query contracts and exposes persistence.checkpoints for versioned durable state and persistence.leases for database-clock claims with monotonic fencing. Workflow hosts pass that capability to createWorkflowCheckpoints({ store }).

Open holds its existing per-schema advisory transaction lock while validating ordered SHA-256 migration history and complete schema-v3 catalog shape before runtime writes. Complete legacy v0.0.5 NULL checksums are shape-verified then backfilled transactionally; altered, partial, unknown, or mismatched history fails closed.

Options

| Field | Default | Purpose | | --- | --- | --- | | pool | — | Existing pg Pool (caller owns lifecycle) | | connectionString | — | Create an adapter-owned pool when pool is omitted | | schema | "prism" | PostgreSQL schema for Prism tables (validated/quoted) | | poolMax | 10 | Maximum pool size when creating a pool from connectionString | | poolConfig | — | Additional pg pool options (TLS, idle timeout, etc.) |

Conformance

Offline unit tests run in the default npm test suite. Full session-store and run-ledger conformance against a live PostgreSQL instance runs when PRISM_TEST_POSTGRES_URL is set:

PRISM_TEST_POSTGRES_URL="postgres://user:pass@localhost:5432/prism_test" npm run test:postgres

Shared suites:

• @arnilo/prism/testing/session-store-conformance — append/idempotency/conflict/branch/reopen
• @arnilo/prism/testing/run-ledger-conformance — run/event/tool/usage durability
• @arnilo/prism/testing/persistence-schema — pagination, tenant isolation, migration checksums, and normalized full-schema shape fixtures

Security

• Schema/table identifiers are validated and double-quoted; values are always bound as query parameters.
• Hosts own TLS configuration, credentials, and pool sizing via pg Pool / PoolConfig.
• Redact secrets before append / ledger writes; the adapter stores rows as provided.
• Migrations use pg_advisory_xact_lock to prevent concurrent setup races. Catalog reads cover metadata only, never application rows; repair schema/history drift through reviewed DDL or restore, not checksum edits.

See PostgreSQL persistence and Database persistence.