PostgreSQL Storage Adapter (@invariant-tech/postgres)

The official persistent PostgreSQL storage adapter. It supplies atomic commits, durable command intent, OCC, and lease primitives; portable multi-worker recovery orchestration is not included in the public Beta. See the canonical Recovery Contract Matrix.

---

1. Installation

npm install @invariant-tech/sdk@beta @invariant-tech/postgres@beta pg
npm install --save-dev @types/pg

---

2. Quick Setup & Schema Bootstrapping

import { invariant } from "@invariant-tech/sdk";
import { PostgresRuntimeStore, postgres } from "@invariant-tech/postgres";
import { Pool } from "pg";

// 1. Configure standard PostgreSQL connection pool
const pool = new Pool({
  connectionString: process.env.DATABASE_URL || "postgres://postgres:postgrespassword@localhost:5432/invariant",
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
  ssl: process.env.NODE_ENV === "production" ? { rejectUnauthorized: false } : false,
});

// 2. Initialize the Postgres Runtime Store
export const store = new PostgresRuntimeStore({ pool });

// 3. Programmatic schema bootstrap on application startup
await store.initializeSchema();

// 4. Initialize Invariant with PostgreSQL storage
export const app = invariant({
  storage: store,
});

// The helper is an equivalent construction when the application owns no Pool.
void postgres;

Direct Connection String Option: You can also pass a connection string directly: new PostgresRuntimeStore({ connectionString: process.env.DATABASE_URL }) or use the helper postgres({ connectionString: process.env.DATABASE_URL }).

---

3. Versioned Database Schema

initializeSchema() creates the invariant_schema_migrations ledger and atomically applies each unapplied built-in migration. Migration v1 provisions the five logical runtime tables and their indexes; migration v2 adds durable loop_state to materialized executions so bounded .repeat() progress survives reload:

• workflow_executions — materialized state and OCC revision.
• workflow_events — immutable, ordered execution facts.
• outbox_commands — durable environmental-work intent and acknowledgement.
• execution_leases — time-bounded run ownership.
• sessions — durable application Session context.

Use Database Schema & Data Lifecycle as the canonical contract for columns, ownership, write timing, physical SQLite/PostgreSQL differences, migrations, backup, restore, and retention.

If a DBA-owned pipeline controls DDL, execute the versioned SQL shipped in the package instead of calling initializeSchema():

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 \
  -f node_modules/@invariant-tech/postgres/dist/schema.sql

Choose exactly one schema owner per environment; do not run application bootstrap and a separate migration pipeline concurrently.

---

4. Operational Durability Guarantees

Atomic OCC Transitions (commitTransition)

Every state transition evaluated by the Kernel commits atomically inside a single PostgreSQL transaction:

$$\text{workflow_executions (revision + durable event count)} + \text{workflow_events (seq)} + \text{outbox_commands} + \text{command acknowledgements}$$

If a single transaction appends sequences 8 through 10 from expectedRevision = 7, it stores revision 10. Revision counts durable event progression, not PostgreSQL transactions.

If two workers attempt to commit transitions against the same revision simultaneously, PostgreSQL row-level locks abort the stale writer with STALE_TRANSITION, ensuring zero split-brain execution.

Worker Lease Exclusivity (acquireLease)

Host code can coordinate ownership of a runId using durable leases stored in execution_leases. The adapter supplies lease primitives and runnable-execution discovery; these do not provide pending-command claim, arbitrary-runId runnable-work attachment, or a packaged background recovery worker. Session restoration can independently reconstruct a registered active .wait() boundary.

Verification levels

The default package suite uses an in-memory PostgreSQL-compatible SQL engine to verify transaction, rollback, OCC, outbox, and lease behavior quickly. It is a contract suite, not evidence from a PostgreSQL server.

For a real PostgreSQL instance, run the opt-in integration gate:

INVARIANT_TEST_POSTGRES_URL=postgres://postgres:postgres@localhost:5432/invariant_test \
  npm --prefix packages/postgres run test:integration:real

That gate initializes the schema, verifies session persistence, atomic state/event/outbox commits, rollback on constraint failure, stale-revision rejection, and lease exclusivity. It uses unique row identifiers and removes only the rows it creates. Without INVARIANT_TEST_POSTGRES_URL, the suite is skipped rather than silently substituting a mock.

---

5. Next Steps

• Database Schema & Data Lifecycle — Review the canonical persistence and migration contract.
• Self-Hosting & Operations — Deploy Invariant with Docker Compose and production worker topologies.
• Custom Storage Adapters — Build custom adapters implementing the RuntimeStore contract.
• Durability Guarantees — Review the 8 core operational guarantees.