@paykernel/sql-foundation

@paykernel/sql-foundation is the shared relational foundation used by PostgreSQL, SQLite, Turso, D1, and Durable Object adapters. It is not a query builder or ORM.

Version 0.1.0 — published on npm as @paykernel/sql-foundation. Install: bun add @paykernel/sql-foundation.

Install

Usually pulled in by an adapter:

bun add @paykernel/store-postgres
# runtime graph includes @paykernel/sql-foundation + @paykernel/store-contracts

Direct install is valid if you are writing a relational adapter:

bun add @paykernel/sql-foundation

Redis (@paykernel/store-redis) does not depend on this package.

In scope vs out of scope

Consumers: @paykernel/store-postgres, store-sqlite, store-turso, store-d1, store-durable-objects.

Schema

Logical names in LOGICAL_TABLES are resolved through createSchemaNamespace / resolveTableName before any SQL is built.

Timestamps are ISO-8601 TEXT. Lease tokens and keys are opaque strings. payload_hash is TEXT. last_error is sanitized and capped (MAX_SANITIZED_ERROR_LENGTH = 512). Raw provider payloads and signatures are not stored by default.

CHECK statuses:

• Idempotency: reserved | completed | indeterminate | expired
• Webhook inbox: pending | claimed | completed | failed | dead_letter
• Reconciliation: scheduled | claimed | completed | failed | manual_review

Official adapters do not write every CHECK-legal status. Postgres never writes idempotency expired (reclaim uses lease_expires_at). Webhook fail writes pending / dead_letter, not failed.

Webhook columns gateway, provider_event_id, first_received_at, last_received_at exist for operator/index use. Store claim() does not populate them (ClaimWebhookInput has no gateway).

import {
  createSchemaNamespace,
  resolveTableName,
} from "@paykernel/sql-foundation";

const ns = createSchemaNamespace({
  tablePrefix: "pay_",
  sqlSchema: "payments",
  tenantColumn: true,
});
const table = resolveTableName("payment_idempotency", ns);
// "payments"."pay_payment_idempotency"

tablePrefix is [A-Za-z0-9_]+, max 36 (MAX_SAFE_TABLE_PREFIX_LENGTH). sqlSchema is a strict identifier (max 63). Invalid config throws SchemaNamespaceError.

Migrations are explicit

CURRENT_SCHEMA_VERSION is 2. SCHEMA_FAMILY is "payments-storage" (not an npm version).

import {
  migrate,
  verifySchema,
  createSchemaNamespace,
  type SqlExecutor,
} from "@paykernel/sql-foundation";

const executor: SqlExecutor = {
  async execute(sql, params) {
    /* prepared / bound params */
  },
  async query(sql, params) {
    /* optional; used to read applied versions */
  },
};

const ns = createSchemaNamespace({ tablePrefix: "pay_" });
const result = await migrate(executor, {
  dialect: "postgres", // | "sqlite" | "generic"
  namespace: ns,
});
// result.applied, result.alreadyApplied, result.currentVersion

const check = await verifySchema(executor, { dialect: "postgres", namespace: ns });
if (!check.ok) {
  throw new Error("schema invalid");
}

Rules:

1. Call migrate from ops / adapter helpers — never as a silent side effect.
2. Importing @paykernel/sql-foundation does not touch a database.
3. Creating a store adapter must not apply DDL.
4. When sqlSchema is set and dialect is "postgres", migrate() issues CREATE SCHEMA IF NOT EXISTS. SQLite adapters reject sqlSchema. Operators still need CREATE privilege.
5. migrate() does not acquire a portable advisory lock. Serialize migrate across hosts (one job). Concurrent runs can race on the version INSERT after multi-statement DDL.

Failures throw MigrationError (code: "migration_error"). Adapter wrappers map driver errors into StoreError.

Atomic claims

Forbidden: get-then-set across connections.

Required: engine-level conditional writes. Pure decisions (decideIdempotencyReserve, decideWebhookClaim, decideReconciliationClaim, evaluateClaim, decideLeaseMutation) perform no I/O. SQL templates are dialect-tagged (postgres | sqlite | generic).

import {
  createSchemaNamespace,
  idempotencyReserveTemplates,
  pickClaimTemplate,
} from "@paykernel/sql-foundation";

const ns = createSchemaNamespace({ tablePrefix: "pay_" });
const frag = pickClaimTemplate(idempotencyReserveTemplates(ns), "postgres");
// frag.sql, frag.params (names), frag.intent

User values are bound parameters. Table names come only from resolveTableName.

createMemoryRelationalStore is NON-PRODUCTION / NON-DISTRIBUTED (MEMORY_RELATIONAL_NON_PRODUCTION). Do not treat its always-ok executor as a production migrate signal.

Related

• Contracts: @paykernel/store-contracts
• Adapter picker: Stores · Adapter selection