PostgreSQL

Install the direct PostgreSQL adapter, apply its migrations, configure node-postgres safely, and understand its transaction guarantees.

@ownfold/postgres stores vault coordination metadata in PostgreSQL without requiring an ORM. Use it when the application already uses pg, when another ORM owns application records, or when you want the smallest database integration surface.

Ownfold does not store encrypted application records in these tables. Your notes, documents, or other records remain in your application tables as versioned encrypted envelopes.

Install

pnpm add @ownfold/postgres @ownfold/server pg
pnpm add -D @types/pg

pg is a peer dependency. Ownfold does not create or own the connection pool.

Apply the migrations

The CLI can generate and apply the complete ordered set:

pnpm add -D @ownfold/cli
pnpm ownfold init --database postgres --identity custom
pnpm ownfold migrate --database-url "$DATABASE_URL"

It records applied files in ownfold_migrations and wraps each pending migration in a transaction. Use pnpm ownfold generate when your application migration system owns deployment.

The package publishes ordered SQL files under @ownfold/postgres/migrations:

0000_ownfold_vaults.sql
0001_ownfold_devices.sql
0002_ownfold_pairings.sql
0003_ownfold_rotations.sql
0004_ownfold_device_last_active.sql

Apply every file exactly once, in numeric order, through your existing migration system. Do this before deploying application code that can call the adapter. Do not run migrations from a request handler.

Create the pool and adapter

import { Pool } from "pg"
import { nodePostgresVaultAdapter } from "@ownfold/postgres/node-postgres"

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  connectionTimeoutMillis: 5_000,
  idleTimeoutMillis: 30_000,
})

pool.on("error", (error) => {
  console.error("An idle PostgreSQL connection failed", error)
})

export const vaultAdapter = nodePostgresVaultAdapter(pool)

import { createVaultServer } from "@ownfold/server"
import { vaultAdapter } from "./database"

export const vaultServer = createVaultServer({
  adapter: vaultAdapter,
  getUserId: async ({ request }) => {
    const session = await readApplicationSession(request)
    return session?.user.id ?? null
  },
})

The node-postgres entry point checks out one client for each transaction, runs BEGIN, and always releases the client after COMMIT or ROLLBACK. This is required for pairing approval and rotation cutover; those operations cannot be split across pooled connections.

Custom PostgreSQL drivers

If your driver is not pg, adapt it to PostgresExecutor:

import { postgresVaultAdapter, type PostgresExecutor } from "@ownfold/postgres"

const executor: PostgresExecutor = {
  async query(text, values = []) {
    const result = await driver.query(text, values)
    return { rows: result.rows, rowCount: result.rowCount }
  },
  async transaction(operation) {
    return driver.transaction(async (transaction) =>
      operation(createOwnfoldExecutor(transaction)),
    )
  },
}

export const adapter = postgresVaultAdapter(executor)

The transaction callback must use the same database transaction for every nested query. It must roll back when operation throws. Do not emulate this contract with unrelated autocommit queries.

What the tables contain

Application ciphertext belongs in your own tables. See Private encrypted records.

Concurrency and atomicity

Every security-sensitive update includes the expected revision. A stale request returns a typed ConflictError instead of overwriting newer metadata.

The adapter guarantees:

• idempotent vault and device creation when the same identity is retried;
• revision-checked recovery verification and Recovery Kit replacement;
• atomic pairing approval plus device registration;
• atomic root-key rotation cutover across the vault, active device envelopes, Recovery Kit ID, and completed rotation record;
• rollback of the complete operation when any expected device revision is stale; and
• validation of every row before it enters server domain logic.

touchDevice updates last-active metadata without changing the security revision. Activity writes therefore do not create false conflicts with revocation or rotation.

TLS and least privilege

Use TLS according to your PostgreSQL provider’s certificate policy. Do not disable certificate verification merely to make a hosted connection succeed. Prefer a dedicated application role with only the privileges needed to read and mutate the four Ownfold tables, and a separate migration role for DDL.

Database backups contain encrypted envelopes and visible coordination metadata. Protect them as sensitive user data even though they do not contain record plaintext or usable recovery secrets.

Graceful shutdown

Drain the pool when the process terminates:

const shutdown = async (): Promise<void> => {
  await pool.end()
}

process.once("SIGTERM", () => void shutdown())
process.once("SIGINT", () => void shutdown())

Coordinate shutdown with your HTTP server so new requests stop before the pool drains.

Validate an integration

All official database adapters run the shared compliance suite against a real PostgreSQL server. Custom adapters should run it too:

import { checkVaultRotationAdapterCompliance } from "@ownfold/testing"

const result = await checkVaultRotationAdapterCompliance(adapter)
if (result.status === "error") throw result.error

Use an empty, disposable database. The suite creates fixed compliance records and exercises stale writes, revocation, pairing, resumable rotation, and atomic cutover behavior.

Troubleshooting

relation "ownfold_vaults" does not exist

The package migrations have not run in the database used by the application process. Check the resolved connection URL and migration history; do not create only the first table by hand.

Rotation completion returns a conflict

A vault or device revision changed after rotation began. Reload remote state and resume from the stored checkpoint. Do not retry cutover with guessed revisions.

The process hangs after tests

Call await pool.end() in teardown. A live pg pool retains clients and timers.

The adapter returns POSTGRES_VAULT_STORAGE_FAILED

The adapter intentionally does not expose driver text or SQL details to the browser. Inspect server-side database logs and the original operational error without returning it to the client.

Production checklist

• Run all numbered migrations before serving the new release.
• Use TLS with certificate verification appropriate to the provider.
• Set finite connection and query timeouts at the deployment boundary.
• Keep the pool instance process-wide; do not create one per request.
• Grant least privilege to the runtime role.
• Back up the database and test restoration.
• Run the adapter compliance suite against the same PostgreSQL major version used in production.
• Monitor conflicts and storage failures without logging encrypted payloads or secrets.