@psci-labs/chat-persistence-postgres

Postgres-backed PersistenceAdapter for @psci-labs/chat-runtime. Durable chat history with a bring-your-own-table (BYOT) column contract — this package documents the schema and runs all reads/writes; the host application owns DDL, migrations, and the pg.Pool.

Install

pnpm add @psci-labs/chat-persistence-postgres pg

pg is a peer dependency: pg >= 8.0.0.

Usage

import { Pool } from 'pg';
import { createAgentRunner } from '@psci-labs/chat-runtime';
import { PostgresPersistence } from '@psci-labs/chat-persistence-postgres';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

const persistence = new PostgresPersistence({ pool });

// Recommended: fail fast on schema drift at boot.
await persistence.validateSchema();

const runner = createAgentRunner({
  apiKey: process.env.ANTHROPIC_API_KEY!,
  model: 'claude-sonnet-4-6',
  persistence,
  // ...
});

Custom table names

const persistence = new PostgresPersistence({
  pool,
  tables: {
    messages: 'app_chat_messages',
    sessions: 'app_chat_sessions',
  },
});

Column contract

The host must create two tables conforming to the columns below. Indexes are optional but recommended for production workloads.

create table chat_messages (
  thread_id     text        not null,
  message_id    text        not null,
  session_id    text,
  user_id       text,
  role          text        not null,
  created_at    timestamptz not null default now(),
  parts_json    jsonb       not null,
  primary key (thread_id, message_id)
);
create index on chat_messages (thread_id, created_at);
create index on chat_messages (session_id);

create table chat_sessions (
  session_id    text        primary key,
  thread_id     text        not null,
  sdk_version   text        not null,
  state_json    jsonb,
  metadata_json jsonb,
  created_at    timestamptz not null default now(),
  updated_at    timestamptz not null default now(),
  archived_at   timestamptz
);
create index on chat_sessions (thread_id);

Notes:

• user_id is reserved for #40 — the v1 adapter writes null.
• parts_json stores the MessagePart[] array — the same wire shape as ChatMessage.content from @psci-labs/chat-protocol.
• archived_at is NULL for live sessions. archiveSession() upserts a row with archived_at = now(); the default listMessages(threadId) excludes messages whose session is archived. Passing an explicit sessionId bypasses the archive filter — that path is for archived-session browsers.

Drizzle schema example

import { jsonb, pgTable, primaryKey, text, timestamp } from 'drizzle-orm/pg-core';

export const chatMessages = pgTable(
  'chat_messages',
  {
    threadId: text('thread_id').notNull(),
    messageId: text('message_id').notNull(),
    sessionId: text('session_id'),
    userId: text('user_id'),
    role: text('role').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    partsJson: jsonb('parts_json').notNull(),
  },
  (t) => ({ pk: primaryKey({ columns: [t.threadId, t.messageId] }) }),
);

export const chatSessions = pgTable('chat_sessions', {
  sessionId: text('session_id').primaryKey(),
  threadId: text('thread_id').notNull(),
  sdkVersion: text('sdk_version').notNull(),
  stateJson: jsonb('state_json'),
  metadataJson: jsonb('metadata_json'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  archivedAt: timestamp('archived_at', { withTimezone: true }),
});

Schema validation

adapter.validateSchema() runs a one-shot information_schema.columns probe against both tables and throws a clear error listing missing columns. Call it once on application boot:

const persistence = new PostgresPersistence({ pool });
await persistence.validateSchema();

The probe checks column presence, not types — type mismatches still surface at query time. For stronger guarantees, run schema diff in CI against the DDL above.

Contract test

Like every PersistenceAdapter, this package passes runPersistenceAdapterContract from @psci-labs/chat-runtime/testing against a real Postgres 16 instance (testcontainers in CI / local Docker).

License

MIT