@paykernel/store-postgres

PostgreSQL durable stores for @paykernel/core lease-aware idempotency, webhook inbox, and reconciliation contracts (Phase 9).

Phase 12 production adapter. Multi-host safe when pointed at a shared PostgreSQL cluster. Claims use engine-level conditional writes (INSERT … ON CONFLICT / UPDATE … RETURNING), not application get-then-set.

Install

bun add @paykernel/store-postgres
# optional drivers (pick one binding):
bun add pg
# or
bun add postgres

Quick start

import {
  createPostgresIdempotencyStore,
  migratePostgresAdapter,
  type PostgresExecutor,
} from "@paykernel/store-postgres";

// Build a narrow executor for your driver (or use a subpath binding):
const executor: PostgresExecutor = /* … */;

// Explicit migrate — NEVER automatic on import or factory construction.
await migratePostgresAdapter(executor);

const store = createPostgresIdempotencyStore({ executor });
const r = await store.reserve({
  key: "pay_123",
  fingerprint: "fp",
  owner: "worker-1",
  leaseMs: 30_000,
});

Driver subpaths

Root entry never statically imports optional drivers. Bindings live on isolated subpaths:

| Subpath | Package | |---------|---------| | @paykernel/store-postgres/pg | pg (node-postgres) | | @paykernel/store-postgres/postgres-js | postgres (postgres.js) | | @paykernel/store-postgres/bun-sql | Bun SQL (bun:sql) — runtime-provided | | @paykernel/store-postgres/drizzle | Notes + executor pass-through only. Phase 12.3 optional Drizzle schema exports were not shipped. |

Example with pg:

import { Pool } from "pg";
import {
  createPostgresStoresFromPg,
  createPgPostgresExecutor,
  migratePostgresAdapter,
} from "@paykernel/store-postgres/pg";

const pool = new Pool({
  connectionString: process.env.PAYMENTS_SDK_PG_URL ?? process.env.DATABASE_URL,
});
const executor = createPgPostgresExecutor(pool);
await migratePostgresAdapter(executor);
const stores = createPostgresStoresFromPg({ client: pool });

Example with postgres (postgres.js):

import postgres from "postgres";
import {
  createPostgresJsPostgresExecutor,
  createPostgresStoresFromPostgresJs,
  migratePostgresAdapter,
} from "@paykernel/store-postgres/postgres-js";

const sql = postgres(process.env.PAYMENTS_SDK_PG_URL!);
const executor = createPostgresJsPostgresExecutor(sql);
await migratePostgresAdapter(executor);
const stores = createPostgresStoresFromPostgresJs({ sql });

Full binding examples: docs/drivers.md.

Migrations

import {
  migratePostgresAdapter,
  verifyPostgresAdapterSchema,
} from "@paykernel/store-postgres";

await migratePostgresAdapter(executor);
const check = await verifyPostgresAdapterSchema(executor);
if (!check.ok) throw new Error(check.errors.join("; "));

• Migrations are opt-in and explicit.
• Factories do not migrate by default.
• Importing the package never touches the database.
• When sqlSchema is set, migratePostgresAdapter issues CREATE SCHEMA IF NOT EXISTS. Operators still need CREATE privilege.
• tenantColumn enables a nullable tenant_id column + index only. v1 DDL always emits that column and index (never a custom name). v1 does not isolate tenants, does not write tenant_id from stores, and does not use a custom column name in DDL (always tenant_id). PK remains key. Prefix keys or wait for a later schema if you need isolation.

See docs/migrations.md.

Timestamps

Foundation schema stores lease and audit timestamps as TEXT ISO-8601 strings (compatible with injectable FakeClock and lexical comparison). Lease reclaim predicates bind injectable now into SQL — they do not hard-depend on SQL NOW() for test paths.

Atomic claims

• Reserve/claim: single-statement Postgres templates from @paykernel/sql-foundation (INSERT ON CONFLICT DO UPDATE … WHERE … RETURNING / conditional UPDATE … RETURNING).
• Mutators (complete, fail, renew, …): conditional UPDATE … WHERE lease_token = $n — zero rows → StoreLeaseLostError.
• listDue soft-releases expired claimed rows then SELECTs due scheduled work. FOR UPDATE SKIP LOCKED is optional fairness and is not used on the default scan. Advisory locks are never the only durable record of work.
• Postgres never writes idempotency status expired (reclaim uses lease_expires_at). Webhook fail writes pending / dead_letter, not failed. expired / failed remain CHECK-legal for operator SQL and memory expire-on-read.
• Webhook columns gateway, provider_event_id, first_received_at, last_received_at exist for operator/index use; claim() does not populate them (ClaimWebhookInput has no gateway).

Manifest

import {
  POSTGRES_STORAGE_ADAPTER_MANIFEST,
  getPostgresStorageAdapterManifest,
} from "@paykernel/store-postgres";

| Field | Value | |-------|--------| | coordinationScope | multi-host (shared PG cluster) | | durability | durable | | consistency.claims | strong | | supportsLeases / Transactions / RetentionCleanup | true |

See docs/guarantees.md.

Documentation

See monorepo docs/adapter-selection.md for the Phase 18 capability matrix and decision tree.

| Doc | Topic | | --- | ----- | | docs/overview.md | Purpose, multi-process durability, boundaries | | docs/crash-boundaries.md | Crash before/after side effect vs complete | | docs/drivers.md | bun-sql / postgres-js / pg; /drizzle is notes + executor pass-through (no schema exports) | | docs/migrations.md | Explicit migrate / verify | | docs/testing.md | PAYMENTS_SDK_PG_URL, docker-compose, conformance | | docs/guarantees.md | Manifest honesty notes |

Testing

# unit / public-api / driver smoke (no live PG required)
bun test packages/store-postgres

# optional local Postgres (docker compose)
docker compose -f packages/store-postgres/docker-compose.yml up -d
export PAYMENTS_SDK_PG_URL=postgres://payments:[email protected]:54329/payments_sdk
# DATABASE_URL is also accepted when PAYMENTS_SDK_PG_URL is unset

# live PG (conformance × bindings, multi-connection, txn rollback, migrate)
bun test packages/store-postgres

When the URL is unset, integration/conformance tests skip cleanly (ok / green CI).

See docs/testing.md.

Non-goals

• This package does not implement Redis / SQLite / Turso / D1 / Durable Object adapters.
• Core and webhooks must not depend on this adapter; inject stores at the app layer.
• Does not publish or re-export private internal/sql-store as a public ORM.

Packaging / install graph

Published adapters depend on:

| Runtime dependency | Role | | --- | --- | | @paykernel/store-contracts | Lease-aware store interfaces + StoreError taxonomy + manifests | | @paykernel/sql-foundation | Shared relational schemas, codecs, migrations, claim SQL templates |

Decision (ship-blocker B8 option B): the former private monorepo package @paykernel/internal-sql-store is packaged as public @paykernel/sql-foundation. Adapters do not list private internal/* packages as runtime dependencies. @paykernel/testkit is a devDependency only (conformance + fake clocks); production install graphs do not pull mock gateways or NON_PRODUCTION memory factories.

See also docs/monorepo.md and docs/workspace-boundaries.md.