PostgreSQL adapter

The PostgreSQL adapter implements SessionStore using an application-owned Knex client. Install the optional knex and pg peer dependencies:

yarn add knex pg

import knex from 'knex';
import { SessionTether, SessionStatus } from 'session-tether';
import { PostgresSessionStore, createPostgresSessionTables } from 'session-tether/postgres';

const db = knex({
  client: 'pg',
  connection: process.env.DATABASE_URL,
});

// Run during setup, before serving requests.
await createPostgresSessionTables(db);

const sessions = new SessionTether({
  store: new PostgresSessionStore(db),
  expiryTTL: 30 * 24 * 60 * 60 * 1000,
  reuseGraceTTL: 30_000,
});

const refreshToken = await sessions.createSession({
  userId: '00000000-0000-4000-8000-000000000001',
  sessionName: 'Laptop',
});
const result = await sessions.rotateRefreshToken(refreshToken);
if (result.status === SessionStatus.ROTATED) {
  // Deliver result.refreshToken as the client's replacement credential.
}

// On application shutdown:
await db.destroy();

Schema and timestamps

createPostgresSessionTables() creates missing session_families and refresh_tokens tables in the client's search path. It leaves existing tables unchanged; use application migrations for upgrades. Run setup once before serving traffic rather than concurrently from application workers.

Both SQL adapters share the same column layout: text token hashes, user identifiers and session names, nullable BIGINT epoch milliseconds for revocation and consumption, and ISO text for expiration. refresh_tokens.family_id references session_families.token_hash. User tables remain application-owned.

The adapter converts returned BIGINT timestamps to safe JavaScript integers, so consumed-token grace works with the default pg parsers. No global driver-parser changes are needed.

Transactions and concurrency

Rotation first conditionally updates the predecessor with WHERE token_hash = ? AND "consumedAt" IS NULL, then reads its family and inserts a successor only when exactly one row was updated, all in one transaction. A failed insert rolls back consumption. Isolation is left at the database default, normally PostgreSQL's READ COMMITTED; application or server overrides are respected. Cleanup also deletes tokens and families in one transaction, with batches of 500 identifiers.

Competing rotations cannot insert multiple successors through this operation. A losing update raises TokenAlreadyConsumedError, which Core handles by rechecking token state and applying grace/reuse policy. Other database errors propagate. Rotation acquires a write lock on the active family before consumption, ordering it with family and user-wide revocation. A family revoked first raises SessionRevokedError, returned as revoked by the core. Expiration is rechecked under the family lock using a fresh application-clock timestamp as the consumption decision point. At or after expiry, TokenExpiredError maps to expired without consuming the token or creating a successor. No expiration grace applies; expiry after the decision does not abort commit. See Policies & Guarantees.

Session creation inserts the family and initial token in one transaction. If either insert fails, neither record is committed. The adapter does not close the supplied Knex client or schedule cleanup.

Integration tests

Tests require a PostgreSQL database where the test user can create schemas. Each test creates a uniquely named schema and drops only that schema afterward. Without the environment variable, PostgreSQL tests are skipped by the default test command.

$env:SESSION_TETHER_POSTGRES_TEST_URL = 'postgresql://user:password@localhost:5432/session_tether_test'
yarn test