# jg-postgres

> node-postgres helpers: transactions that report an unknown commit, advisory locks, SQLSTATE checks, cursor pages, and ordered lists.

- **Version:** `0.3.0`
- **Runtimes:** Node.js
- **Install:** `npm install jg-postgres`
- **On npm:** [jg-postgres](https://www.npmjs.com/package/jg-postgres)
- **Source:** [packages/jg-postgres](https://github.com/jesusgraterol/jg-packages/tree/main/packages/jg-postgres)
- **Recipes:** [jg-postgres recipes](https://jg-packages.jesusgraterol.dev/packages/jg-postgres/recipes/index.md)

## Usage

Create one pool per database, with timeouts, and close it at shutdown:

```ts
import { createPostgres } from 'jg-postgres';

const postgres = createPostgres({
  host: 'db.internal',
  database: 'app',
  user: 'app',
  password: 'secret',
  max: 10,
  connectionTimeoutMillis: 30_000, // waiting for a connection
  statement_timeout: 60_000, // one statement, on the server
  lock_timeout: 30_000, // waiting for a row or table lock
  idle_in_transaction_session_timeout: 300_000, // a transaction left open
  onError: (error) => console.error('PostgreSQL', error),
});

const users = await postgres.query<{ id: string }>('SELECT id FROM user_account LIMIT $1;', [10]);
await postgres.close();
```

The pool reads `NUMERIC` as a number and `TIMESTAMPTZ` as an ISO 8601 UTC string, such as `'2026-10-17T08:30:00.000Z'`, and keeps `BIGINT` as a string, so no digit of a large amount is lost. The parsers are set on the pool, not node-postgres's global registry, so other pools in the program keep theirs. Errors no caller awaits, such as an idle connection the server closed, go to `onError`, `console.error` by default, instead of crashing the process.

### Transactions

```ts
import { createPostgres } from 'jg-postgres';

declare const postgres: ReturnType<typeof createPostgres>;

const orderId = await postgres.transaction(async (client) => {
  const { rows } = await client.query<{ id: string }>(
    'INSERT INTO purchase_order (user_account_id) VALUES ($1) RETURNING id;',
    ['42'],
  );
  await client.query('UPDATE user_account SET order_count = order_count + 1 WHERE id = $1;', [
    '42',
  ]);
  return rows[0]?.id;
});
```

`transaction` commits when the operation resolves, rolls back when it throws, and rejects with the operation's own error. It always returns its client to the pool, or destroys it when its state is unknown. When the connection breaks during `COMMIT`, nobody can tell whether the transaction committed, so it rejects with `TRANSACTION_OUTCOME_UNKNOWN`: check the data before retrying anything that isn't idempotent. Run work inside a transaction on its `client`, and never start a transaction inside another.

### Timeouts

A profile says how long a kind of work may run: a statement on the server, a wait for a lock, and the client's wait for the answer, which adds a grace, 15 seconds by default, so the server's own error arrives first. Give the pool the profile of everyday work, and a slow transaction one of its own:

```ts
import {
  createPostgres,
  defineTimeoutProfile,
  toPoolTimeouts,
  withQueryTimeout,
} from 'jg-postgres';

const STANDARD = defineTimeoutProfile({ statementTimeoutSeconds: 15, lockTimeoutSeconds: 5 });
const REPORT = defineTimeoutProfile({ statementTimeoutSeconds: 300, lockTimeoutSeconds: 30 });

const postgres = createPostgres({ connectionString, ...toPoolTimeouts(STANDARD) });

await postgres.transaction(
  (client) =>
    client.query(withQueryTimeout({ text: 'SELECT build_report($1);', values: [month] }, REPORT)),
  { timeouts: REPORT },
);
```

`timeouts` sets the statement and lock timeouts for that transaction only, so the client goes back to the pool with the pool's. The pool's `query_timeout` still applies to each query that doesn't set its own, so pass the transaction's queries through `withQueryTimeout` with the same profile. A query the client stops waiting for is rolled back: the client goes back to the pool once `ROLLBACK`, which has the pool's `query_timeout` too, confirms the connection, and is destroyed otherwise.

### Optional reads

`createOptionalReader` runs reads that are nice to have, such as a counter in a page's header, without ever delaying or crowding out essential work. A read is skipped, giving `null` at once, when the reader already runs `maxActiveReads` reads, a request waits for a client, or the pool can't give more clients than `reservedIdleClients`, counting idle ones and the room left to open new ones. It runs in a read-only transaction with short timeouts, gives `null` after `admissionSeconds`, and never throws:

```ts
import { createOptionalReader } from 'jg-postgres';

const counters = createOptionalReader({ postgres }); // 2 reads at most, 2 clients left free, 1 second

const unread = counters.read(
  `unread:${accountId}`, // callers of the same key share one read
  {
    text: 'SELECT count(*)::int AS count FROM notification WHERE account_id = $1;',
    values: [accountId],
  },
  (row) => (row as { count: number }).count,
);
const count = await unread.completion; // a number, or null
```

### Usage limits

`admitUsage` decides, atomically, whether an actor may do a rate-limited action now, such as sending an invitation, and records it when they may. It refuses with `cooldown` while the same action on the same scope was accepted less than `cooldownSeconds` ago, by any actor, and with `limit` once the actor did the action `limit` times within `windowSeconds`:

```ts
import { admitUsage } from 'jg-postgres';

const admission = await admitUsage(postgres, {
  actorId: userId,
  actionId: 'send-invitation',
  scopeKey: inviteeEmailHash, // the cooldown applies per invitee
  limit: 20,
  windowSeconds: 86_400,
  cooldownSeconds: 60,
});
// { outcome: 'accepted' | 'cooldown' | 'limit', retryAt: an ISO timestamp, or null }
```

Concurrent calls queue on transaction advisory locks, the scope's then the actor's, so two can never both pass, and every comparison uses the database's clock. The accepted actions go in a `usage_event` table:

```sql
CREATE TABLE usage_event (
  id bigint GENERATED ALWAYS AS IDENTITY,
  actor_id varchar(256) NOT NULL,
  action_id varchar(128) NOT NULL,
  scope_key varchar(256) NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT pk_ue__id PRIMARY KEY (id),
  CONSTRAINT chk_ue__actor_id_non_empty CHECK (actor_id <> ''),
  CONSTRAINT chk_ue__action_id_non_empty CHECK (action_id <> ''),
  CONSTRAINT chk_ue__scope_key_non_empty CHECK (scope_key <> '')
);
CREATE INDEX idx_ue__actor_id_action_id_created_at ON usage_event (actor_id, action_id, created_at);
CREATE INDEX idx_ue__action_id_scope_key_created_at_scoped
  ON usage_event (action_id, scope_key, created_at)
  WHERE scope_key IS NOT NULL;
CREATE INDEX idx_ue__created_at ON usage_event (created_at);
```

The table grows with every accepted action. Prune it on a schedule, deleting in batches the rows older than the longest window or cooldown, `$1` seconds, `$2` rows at a time, such as 1024, and running it again while it deletes a whole batch. It compares with `now()`, which in a statement of its own is the statement's start, because the index can bound a scan by `now()` but not by `clock_timestamp()`:

```sql
DELETE FROM usage_event
WHERE id IN (
  SELECT id
  FROM usage_event
  WHERE created_at < now() - make_interval(secs => $1)
  ORDER BY created_at
  LIMIT $2
);
```

### Verification challenges

`createVerificationChallenges` issues and checks short codes bound to an actor, an action, and a target, such as the code that confirms a new email address. Each action has a policy: how long a code is valid, how many wrong codes it takes, and how many codes an actor may be issued within a window, over every target:

```ts
import { createVerificationChallenges } from 'jg-postgres';

const challenges = createVerificationChallenges(postgres, {
  policies: {
    'verify-email': {
      validitySeconds: 600,
      maxFailedAttempts: 5,
      issueLimit: 5,
      issueWindowSeconds: 3_600,
    },
  },
  codeLength: 6, // the default
});

const binding = { actorId: userId, actionId: 'verify-email', targetId: newEmail };
const issued = await challenges.issue(binding, ({ code, challengeId }) =>
  mailer.send(newEmail, code, { idempotencyKey: challengeId }),
);
// { outcome: 'issued', challengeToken, expiresAt } or { outcome: 'limit', retryAt }

// later, with the token the client kept and the code the user typed
const isVerified = await challenges.consume({
  ...binding,
  challengeToken: savedToken,
  code: typedCode,
});
```

`issue` refuses with `limit` once the actor was issued `issueLimit` codes within the window, with `retryAt`, when the oldest of them leaves it. Otherwise it supersedes the binding's active challenge and commits the new one, then calls `deliver` with the code, the only time it's available. A delivery that throws makes `issue` throw `VERIFICATION_DELIVERY_FAILED`, with the code and the token redacted from it and its cause; the challenge still counts toward the limit. Concurrent issues for an actor and an action queue on a transaction advisory lock, and every comparison uses the database's clock.

`consume` resolves to `true` once, for the right code. A wrong code counts a failed attempt, and a challenge stops accepting codes after `maxFailedAttempts` of them, so the policy's current value applies. A used, superseded, or expired challenge, another binding's token, or a token or code of the wrong shape resolves to `false` without counting, and every `false` looks the same to the caller.

Only the SHA-256 hashes of the token and of the code, combined with its token, are stored, in a `verification_challenge` table:

```sql
CREATE TABLE verification_challenge (
  id bigint GENERATED ALWAYS AS IDENTITY,
  actor_id varchar(256) NOT NULL,
  action_id varchar(128) NOT NULL,
  target_id varchar(256) NOT NULL,
  token_hash text NOT NULL,
  code_hash text NOT NULL,
  failed_attempt_count smallint NOT NULL DEFAULT 0,
  expires_at timestamptz NOT NULL,
  used_at timestamptz NULL,
  superseded_at timestamptz NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT pk_vc__id PRIMARY KEY (id),
  CONSTRAINT uq_vc__token_hash UNIQUE (token_hash),
  CONSTRAINT chk_vc__actor_id_non_empty CHECK (actor_id <> ''),
  CONSTRAINT chk_vc__action_id_non_empty CHECK (action_id <> ''),
  CONSTRAINT chk_vc__target_id_non_empty CHECK (target_id <> ''),
  CONSTRAINT chk_vc__token_hash_sha256 CHECK (token_hash ~ '^[0-9a-f]{64}$'),
  CONSTRAINT chk_vc__code_hash_sha256 CHECK (code_hash ~ '^[0-9a-f]{64}$'),
  CONSTRAINT chk_vc__failed_attempt_count_non_negative CHECK (failed_attempt_count >= 0),
  CONSTRAINT chk_vc__expires_at_chronological CHECK (expires_at > created_at),
  CONSTRAINT chk_vc__used_at_superseded_at_mutually_exclusive
    CHECK (used_at IS NULL OR superseded_at IS NULL)
);
CREATE UNIQUE INDEX uidx_vc__actor_id_action_id_target_id_active
  ON verification_challenge (actor_id, action_id, target_id)
  WHERE used_at IS NULL AND superseded_at IS NULL;
CREATE INDEX idx_vc__actor_id_action_id_created_at
  ON verification_challenge (actor_id, action_id, created_at);
CREATE INDEX idx_vc__created_at ON verification_challenge (created_at);
```

Prune it on a schedule as `usage_event` is pruned, deleting in batches the rows older than the longest `validitySeconds` or `issueWindowSeconds` of any policy, `$1` seconds, `$2` rows at a time. A younger row may still be consumed, or count toward a limit:

```sql
DELETE FROM verification_challenge
WHERE id IN (
  SELECT id
  FROM verification_challenge
  WHERE created_at < now() - make_interval(secs => $1)
  ORDER BY created_at
  LIMIT $2
);
```

### Database summaries

`readDatabaseSummary` and `listTableSizes` read what an admin dashboard or a health check shows about the database, and `diffTables` compares the tables with the ones a migration should have created:

```ts
import { diffTables, listTableSizes, readDatabaseSummary } from 'jg-postgres';

const summary = await readDatabaseSummary(postgres.pool); // the public schema, or { schema }
// { name, serverVersion, serverVersionNumber, sizeBytes, activeConnections, maxConnections, tableCount }

const tables = await listTableSizes(postgres.pool); // [{ name, totalBytes }], sorted by name
const { missing, unexpected } = diffTables(
  tables.map(({ name }) => name),
  ['app_user', 'session'],
);
```

Sizes and counts are numbers, and sizes include indexes and TOAST data. `activeConnections` counts the connections to this database running a query. Without the `pg_read_all_stats` role, PostgreSQL shows a role only its own connections, so the count is lower than the server's.

### Counter tables

`defineCounterTable` builds the queries that change counter rows, such as an account's daily usage or a balance, quoting every name once and sending every value as a parameter. `applyCounterQueries` runs them in the caller's transaction, sorted by row, so two transactions that change the same rows lock them in the same order and never deadlock:

```ts
import { applyCounterQueries, defineCounterTable } from 'jg-postgres';

const dailyUsage = defineCounterTable({
  table: 'daily_usage',
  keyColumns: ['account_id', 'day'],
  counters: ['request_count', 'token_count'],
});

await postgres.transaction((client) =>
  applyCounterQueries(client, [
    // inserts the row, or adds to it
    dailyUsage.buildIncrement(
      { account_id: accountId, day },
      { request_count: 1, token_count: tokens },
    ),
    // adds to a row that must exist, or throws COUNTER_ROW_NOT_FOUND
    dailyUsage.buildAdjustment({ account_id: accountId, day }, { token_count: -refunded }),
  ]),
);
```

An increment is an `INSERT … ON CONFLICT (key columns) DO UPDATE`, so the table needs a unique constraint on exactly the key columns, and a default, such as 0, for every counter. Amounts are whole numbers or bigints within `BIGINT`: positive to increment, and non-zero to adjust. A counter that would leave `BIGINT`'s range fails with SQLSTATE 22003, which `isNumericValueOutOfRangeError` checks, and the transaction rolls back.

### Constraint errors

```ts
import { isUniqueViolationError } from 'jg-postgres';

declare const error: unknown;

if (isUniqueViolationError(error, 'uq_user_account__email')) {
  // the email is already registered
}
```

`isDatabaseError` and the checks for unique, foreign key, not-null, check, and restrict violations, string truncation, numeric overflow, invalid text, serialization failures, deadlocks, and canceled queries read the SQLSTATE code. The constraint checks take an optional constraint name.

### Advisory locks

`createAdvisoryLockKey` turns a name into the 64-bit key PostgreSQL's advisory locks take. `lockTransaction` holds one until the transaction ends, waiting while another holds it. `withSessionLock` holds one on a connection across several transactions, or skips the work when another session holds it:

```ts
import { createAdvisoryLockKey, createPostgres, withSessionLock } from 'jg-postgres';

declare const postgres: ReturnType<typeof createPostgres>;

const outcome = await withSessionLock(
  postgres,
  createAdvisoryLockKey('user-account-deletion:42'),
  async (lock) => {
    lock.assertActive(); // throws SESSION_LOCK_LOST once the lock's connection is gone
    return 'deleted';
  },
);
// { acquired: false } when another request holds the lock
```

### Cursor pages and ordered lists

`listByCursor` lists a page of rows after a cursor, and `buildCursorPage` turns a page listed with one extra row into `{ items, nextCursor }`. `moveUp` and `moveDown` swap a row's sort order with its neighbor's, in their own transaction under the list's lock, and `lockOrderedList` takes the same lock for an append. Every table and column name is quoted.

### A record store

`jg-postgres/record-store` keeps small JSON records, such as counters or a feature's settings, one row each in a `record_store` table, which the app creates:

```sql
CREATE TABLE IF NOT EXISTS record_store (
  id    varchar(512),
  value jsonb        NOT NULL,

  CONSTRAINT pk_rs__id PRIMARY KEY (id),
  CONSTRAINT chk_rs__id_non_empty CHECK (id <> ''),
  CONSTRAINT chk_rs__value_object CHECK (jsonb_typeof(value) = 'object')
);
```

```ts
import { createPostgres } from 'jg-postgres';
import { createRecordStore } from 'jg-postgres/record-store';
import { z } from 'zod';

declare const postgres: ReturnType<typeof createPostgres>;

const counters = createRecordStore({
  postgres,
  id: 'admin:unread-counts',
  defaults: { notifications: 0, reports: 0 },
  schema: z.object({ notifications: z.number(), reports: z.number() }),
});

await counters.incrementNumber('notifications', 1); // atomic, so concurrent increments all count
await counters.setNumber('reports', 0); // leaves the other fields as they are
await counters.get(); // { notifications: 1, reports: 0 }, checked with the schema
```

The defaults fill every missing or null field when the record is read, and are the only keys a write keeps. Every method takes an optional transaction's client, to run in that transaction without committing it.

### Errors

| Code                            | When                                                                                                                  |
| ------------------------------- | --------------------------------------------------------------------------------------------------------------------- |
| `TRANSACTION_OUTCOME_UNKNOWN`   | The connection was lost during `COMMIT`                                                                               |
| `SESSION_LOCK_LOST`             | The connection that held a session lock was lost                                                                      |
| `INVALID_IDENTIFIER`            | A name is empty, holds a null character, or is longer than 63 bytes                                                   |
| `ROW_NOT_FOUND`                 | The row to move doesn't exist in the ordered list                                                                     |
| `ROW_ALREADY_FIRST`             | The row to move up is already first                                                                                   |
| `ROW_ALREADY_LAST`              | The row to move down is already last                                                                                  |
| `MOVE_FAILED`                   | The move didn't update exactly two rows                                                                               |
| `INVALID_TIMEOUT_PROFILE`       | A timeout profile's seconds aren't positive and finite, or its lock timeout is longer than its statement timeout      |
| `INVALID_OPTIONAL_READ_OPTIONS` | An optional reader's database, limits, or timeouts aren't valid                                                       |
| `INVALID_USAGE_ADMISSION`       | A usage admission's ids, limit, or seconds aren't valid, or it has a cooldown without a scope                         |
| `INVALID_VERIFICATION_POLICY`   | The verification policies or the code length aren't valid                                                             |
| `INVALID_VERIFICATION_INPUT`    | A verification binding's ids aren't valid, its action has no policy, or issue has no delivery function                |
| `VERIFICATION_DELIVERY_FAILED`  | The delivery function threw, after the challenge was committed                                                        |
| `INVALID_COUNTER_TABLE`         | A counter table has no key column or no counter, or repeats a name                                                    |
| `INVALID_COUNTER_CHANGE`        | A counter row's key doesn't have exactly the key columns, or an amount isn't a whole, non-zero amount within `BIGINT` |
| `COUNTER_ROW_NOT_FOUND`         | A counter query changed no row, because the row to adjust doesn't exist                                               |

A query's own error, such as a constraint violation, is node-postgres's `DatabaseError`, unchanged.

## <a id="api-reference"></a>jg-postgres API reference

Every export of `jg-postgres@0.3.0`, 76 in all, with its signature, its errors, and an example. It's generated from the package's TSDoc.

### <a id="api-entry-main"></a>`jg-postgres`

- Functions: [`admitUsage`](#api-admitUsage), [`applyCounterQueries`](#api-applyCounterQueries), [`buildCursorPage`](#api-buildCursorPage), [`createAdvisoryLockKey`](#api-createAdvisoryLockKey), [`createOptionalReader`](#api-createOptionalReader), [`createPostgres`](#api-createPostgres), [`createVerificationChallenges`](#api-createVerificationChallenges), [`defineCounterTable`](#api-defineCounterTable), [`defineTimeoutProfile`](#api-defineTimeoutProfile), [`diffTables`](#api-diffTables), [`isCheckViolationError`](#api-isCheckViolationError), [`isDatabaseError`](#api-isDatabaseError), [`isDeadlockDetectedError`](#api-isDeadlockDetectedError), [`isForeignKeyViolationError`](#api-isForeignKeyViolationError), [`isInvalidTextRepresentationError`](#api-isInvalidTextRepresentationError), [`isNotNullViolationError`](#api-isNotNullViolationError), [`isNumericValueOutOfRangeError`](#api-isNumericValueOutOfRangeError), [`isQueryCanceledError`](#api-isQueryCanceledError), [`isRestrictViolationError`](#api-isRestrictViolationError), [`isSerializationFailureError`](#api-isSerializationFailureError), [`isStringDataRightTruncationError`](#api-isStringDataRightTruncationError), [`isUniqueViolationError`](#api-isUniqueViolationError), [`listByCursor`](#api-listByCursor), [`listTableSizes`](#api-listTableSizes), [`lockOrderedList`](#api-lockOrderedList), [`lockTransaction`](#api-lockTransaction), [`moveDown`](#api-moveDown), [`moveUp`](#api-moveUp), [`quoteIdentifier`](#api-quoteIdentifier), [`readDatabaseSummary`](#api-readDatabaseSummary), [`toPoolTimeouts`](#api-toPoolTimeouts), [`withQueryTimeout`](#api-withQueryTimeout), [`withSessionLock`](#api-withSessionLock)
- Constants: [`ERRORS`](#api-ERRORS), [`POSTGRES_BIGINT_MAX`](#api-POSTGRES_BIGINT_MAX), [`POSTGRES_BIGINT_MIN`](#api-POSTGRES_BIGINT_MIN)
- Types: [`IColumnFilter`](#api-IColumnFilter), [`ICounterChanges`](#api-ICounterChanges), [`ICounterKey`](#api-ICounterKey), [`ICounterQuery`](#api-ICounterQuery), [`ICounterTable`](#api-ICounterTable), [`ICounterTableDefinition`](#api-ICounterTableDefinition), [`ICursorListingOptions`](#api-ICursorListingOptions), [`ICursorPage`](#api-ICursorPage), [`IDatabaseSummary`](#api-IDatabaseSummary), [`IIntrospectionOptions`](#api-IIntrospectionOptions), [`IMoveOptions`](#api-IMoveOptions), [`IOptionalRead`](#api-IOptionalRead), [`IOptionalReader`](#api-IOptionalReader), [`IOptionalReaderOptions`](#api-IOptionalReaderOptions), [`IOrderedList`](#api-IOrderedList), [`IPostgres`](#api-IPostgres), [`IPostgresConfig`](#api-IPostgresConfig), [`IQueryExecutor`](#api-IQueryExecutor), [`IRowId`](#api-IRowId), [`ISessionLock`](#api-ISessionLock), [`ISessionLockResult`](#api-ISessionLockResult), [`ITableDiff`](#api-ITableDiff), [`ITableSize`](#api-ITableSize), [`ITimeoutProfile`](#api-ITimeoutProfile), [`ITransactionOptions`](#api-ITransactionOptions), [`IUsageAdmission`](#api-IUsageAdmission), [`IUsageAdmissionInput`](#api-IUsageAdmissionInput), [`IVerificationAttempt`](#api-IVerificationAttempt), [`IVerificationBinding`](#api-IVerificationBinding), [`IVerificationChallenges`](#api-IVerificationChallenges), [`IVerificationChallengesOptions`](#api-IVerificationChallengesOptions), [`IVerificationDeliver`](#api-IVerificationDeliver), [`IVerificationDelivery`](#api-IVerificationDelivery), [`IVerificationIssue`](#api-IVerificationIssue), [`IVerificationPolicy`](#api-IVerificationPolicy)

#### <a id="api-functions"></a>Functions in `jg-postgres`

##### <a id="api-admitUsage"></a>`admitUsage`

*Function*

Decides, atomically, whether an actor may do a rate-limited action now, such as sending an invitation, and records it when they may. It refuses with `cooldown` while the same action on the same scope was accepted less than `cooldownSeconds` ago, by any actor, and with `limit` once the actor did the action `limit` times within `windowSeconds`. Concurrent calls queue on transaction advisory locks, the scope's then the actor's, so they never both pass, and every comparison uses the database's clock. It needs the `usage_event` table the README defines.

```ts
admitUsage(
  postgres: IPostgres,
  input: IUsageAdmissionInput,
): Promise<IUsageAdmission>
```

**Parameters**

- `postgres` `IPostgres`: The database.
- `input` `IUsageAdmissionInput`: The actor, the action, the optional scope, the limit and its window, and the optional cooldown.

**Returns** `Promise<IUsageAdmission>`: A promise that resolves to the outcome, with `retryAt`, an ISO 8601 UTC timestamp: when the scope's cooldown ends, or null without one.

**Throws**

- `INVALID_USAGE_ADMISSION`: The usage admission needs an actor id of 1 to 256 characters, an action id of 1 to 128, a scope key of 1 to 256 when given, a whole positive limit, and positive seconds, with a cooldown only for a scope.

**Example**

```ts
import { admitUsage, createPostgres } from 'jg-postgres';

declare const postgres: ReturnType<typeof createPostgres>;

const admission = await admitUsage(postgres, {
  actorId: '42',
  actionId: 'send-invitation',
  scopeKey: 'invitee@example.com',
  limit: 20,
  windowSeconds: 86_400,
  cooldownSeconds: 60,
});
if (admission.outcome !== 'accepted') {
  // tell the user when to try again: admission.retryAt, or tomorrow for 'limit'
}
```

##### <a id="api-applyCounterQueries"></a>`applyCounterQueries`

*Function*

Runs counter queries one after another on a transaction's client, sorted by their lock keys, so two transactions changing the same rows lock them in the same order and never deadlock each other. Queries for the same row keep their order. Each must change exactly one row, so an adjustment of a row that doesn't exist throws, and the caller's transaction rolls back. A counter that would leave BIGINT's range fails with SQLSTATE 22003, as isNumericValueOutOfRangeError checks.

```ts
applyCounterQueries(
  client: PoolClient,
  queries: readonly ICounterQuery[],
): Promise<void>
```

**Parameters**

- `client` `PoolClient`: The client of the transaction to run them in.
- `queries` `readonly ICounterQuery[]`: The queries, from defineCounterTable's builders. The list isn't changed.

**Returns** `Promise<void>`: A promise that resolves once every query ran.

**Throws**

- `COUNTER_ROW_NOT_FOUND`: The counter query changed no row, because the row to adjust doesn't exist.

**Example**

```ts
import { applyCounterQueries, createPostgres, defineCounterTable } from 'jg-postgres';

declare const postgres: ReturnType<typeof createPostgres>;

const balances = defineCounterTable({
  table: 'account_balance',
  keyColumns: ['account_id'],
  counters: ['credit_count'],
});
await postgres.transaction((client) =>
  applyCounterQueries(client, [
    balances.buildAdjustment({ account_id: '42' }, { credit_count: -10 }),
    balances.buildIncrement({ account_id: '7' }, { credit_count: 10 }),
  ]),
);
```

##### <a id="api-buildCursorPage"></a>`buildCursorPage`

*Function*

Builds a page from rows listed with one more than the page's size, so the extra row tells whether another page follows without counting the table.

```ts
buildCursorPage<T>(
  rows: readonly T[],
  limit: number,
  toCursor: (row: T) => string,
): ICursorPage<T>
```

**Parameters**

- `rows` `readonly T[]`: The rows, listed with a limit of the page size plus one.
- `limit` `number`: The page size.
- `toCursor` `(row: T) => string`: Reads the cursor from a row, such as its ID.

**Returns** `ICursorPage<T>`: The page's rows, and the last one's cursor when more follow, otherwise null.

**Example**

```ts
import { buildCursorPage } from 'jg-postgres';

buildCursorPage([{ id: '3' }, { id: '2' }, { id: '1' }], 2, ({ id }) => id);
// => { items: [{ id: '3' }, { id: '2' }], nextCursor: '2' }
```

##### <a id="api-createAdvisoryLockKey"></a>`createAdvisoryLockKey`

*Function*

Turns a lock's name into the signed 64-bit key PostgreSQL's advisory locks take: the first 8 bytes of the name's SHA-256 hash. The same name always gives the same key, in every process.

```ts
createAdvisoryLockKey(name: string): string
```

**Parameters**

- `name` `string`: The lock's name, such as `user-account-deletion:42`.

**Returns** `string`: The key, as a string, such as `'7480330268761432310'`.

**Example**

```ts
import { createAdvisoryLockKey } from 'jg-postgres';

createAdvisoryLockKey('moldea'); // => '7480330268761432310'
```

##### <a id="api-createOptionalReader"></a>`createOptionalReader`

*Function*

Creates a reader for optional data, such as counters in a page's header, that must never delay or crowd out essential work. A read is skipped, giving null at once, when the reader already runs maxActiveReads reads, a request waits for a client, or the pool can't give more clients than reservedIdleClients, counting idle ones and the room left to open new ones. A running read gives null once admissionSeconds pass, and is shared by every caller of the same key until it ends. Each read runs in a read-only transaction with short timeouts of its own, and never throws.

```ts
createOptionalReader(options: IOptionalReaderOptions): IOptionalReader
```

**Parameters**

- `options` `IOptionalReaderOptions`: The database, and optionally the limits and the reads' timeouts.

**Returns** `IOptionalReader`: The reader.

**Throws**

- `INVALID_OPTIONAL_READ_OPTIONS`: The optional reader needs a database, whole positive maxActiveReads, whole reservedIdleClients from 0, positive admissionSeconds, and a valid timeout profile.

**Example**

```ts
import { createOptionalReader, createPostgres } from 'jg-postgres';

declare const postgres: ReturnType<typeof createPostgres>;
declare const accountId: string;

const counters = createOptionalReader({ postgres });

const unread = counters.read(
  `unread:${accountId}`,
  { text: 'SELECT count(*)::int AS count FROM notification WHERE account_id = $1 AND read_at IS NULL;', values: [accountId] },
  (row) => (row as { count: number }).count,
);
const count = await unread.completion; // a number, or null when the database is busy
```

##### <a id="api-createPostgres"></a>`createPostgres`

*Function*

Creates a PostgreSQL pool with the helpers every query, transaction, and lock uses. The pool parses NUMERIC as a number and TIMESTAMPTZ as an ISO 8601 UTC string, keeps BIGINT as a string, and sends errors no caller awaits, such as an idle connection the server closed, to onError instead of crashing the process.

```ts
createPostgres(config: IPostgresConfig): IPostgres
```

**Parameters**

- `config` `IPostgresConfig`: node-postgres's pool configuration, plus onError.

**Returns** `IPostgres`: The pool and its helpers.

**Example**

```ts
import { createPostgres } from 'jg-postgres';

const postgres = createPostgres({
  host: 'localhost',
  database: 'app',
  user: 'app',
  password: 'secret',
  max: 10,
  connectionTimeoutMillis: 30_000,
  statement_timeout: 60_000,
  lock_timeout: 30_000,
  idle_in_transaction_session_timeout: 300_000,
});

const users = await postgres.query<{ id: string }>('SELECT id FROM user_account LIMIT $1;', [10]);
await postgres.close();
```

##### <a id="api-createVerificationChallenges"></a>`createVerificationChallenges`

*Function*

Creates the functions that issue and consume verification challenges, such as the code that confirms a new email address. A challenge is bound to an actor, an action, and a target; only the SHA-256 hashes of its token and code are stored, in a `verification_challenge` table the README defines, and every comparison uses the database's clock.

`issue` refuses with `limit` once the actor was issued the action's `issueLimit` challenges within `issueWindowSeconds`, over every target, so concurrent issues queue on a transaction advisory lock. Otherwise it supersedes the binding's active challenge, commits the new one, and calls `deliver` with the code. `consume` uses the challenge when the code matches, or counts a failed attempt, and every failure resolves to `false` alike.

```ts
createVerificationChallenges(
  postgres: IPostgres,
  options: IVerificationChallengesOptions,
): IVerificationChallenges
```

**Parameters**

- `postgres` `IPostgres`: The database.
- `options` `IVerificationChallengesOptions`: Each action's policy, keyed by its action id, and the code's digits.

**Returns** `IVerificationChallenges`: The functions.

**Throws**

- `INVALID_VERIFICATION_POLICY`: The verification challenges need at least one policy, each for an action id of 1 to 128 characters, with whole validity and window seconds of at least 1, a whole issue limit of at least 1, and a whole failed-attempt limit from 1 to 32767, and a whole code length from 4 to 10.

**Example**

```ts
import { createPostgres, createVerificationChallenges } from 'jg-postgres';

declare const postgres: ReturnType<typeof createPostgres>;
declare const sendEmail: (to: string, text: string) => Promise<void>;

const challenges = createVerificationChallenges(postgres, {
  policies: {
    'verify-email': {
      validitySeconds: 600,
      maxFailedAttempts: 5,
      issueLimit: 5,
      issueWindowSeconds: 3_600,
    },
  },
});
const binding = { actorId: '42', actionId: 'verify-email', targetId: 'ada@example.com' };
const issued = await challenges.issue(binding, ({ code, targetId }) =>
  sendEmail(targetId, `Your code is ${code}`),
);
if (issued.outcome === 'issued') {
  // later, with the code the user typed
  const isVerified = await challenges.consume({
    ...binding,
    challengeToken: issued.challengeToken,
    code: '123456',
  });
}
```

##### <a id="api-defineCounterTable"></a>`defineCounterTable`

*Function*

Defines a table of counters, such as an account's daily usage, and gives the functions that build the queries changing its rows. Every name is quoted once, and every value is a parameter. The table needs a unique constraint on exactly the key columns, for the increments' ON CONFLICT, and a default for every counter. Run the queries with `applyCounterQueries`, which orders them so concurrent transactions lock rows in the same order.

```ts
defineCounterTable<K extends string, C extends string>(
  definition: ICounterTableDefinition<K, C>,
): ICounterTable<K, C>
```

**Parameters**

- `definition` `ICounterTableDefinition<K, C>`: The table, its key columns, and its counters.

**Returns** `ICounterTable<K, C>`: The query builders.

**Throws**

- `INVALID_COUNTER_TABLE`: The counter table needs at least one key column and one counter, every name distinct.
- `INVALID_IDENTIFIER`: The identifier is empty, holds a null character, or is longer than 63 bytes.

**Example**

```ts
import { defineCounterTable } from 'jg-postgres';

const dailyUsage = defineCounterTable({
  table: 'daily_usage',
  keyColumns: ['account_id', 'day'],
  counters: ['request_count', 'token_count'],
});
const increment = dailyUsage.buildIncrement(
  { account_id: '42', day: '2026-10-19' },
  { request_count: 1, token_count: 1_250n },
);
// increment.text: INSERT INTO "daily_usage" ("account_id", "day", "request_count", "token_count")
//   VALUES ($1, $2, $3, $4) ON CONFLICT ("account_id", "day") DO UPDATE SET ...
// increment.values: ['42', '2026-10-19', '1', '1250']
```

##### <a id="api-defineTimeoutProfile"></a>`defineTimeoutProfile`

*Function*

Defines how long a kind of work may run: a statement on the server, a wait for a lock, and the client's wait for the server's answer, which is the statement timeout plus a grace, so the server's own timeout error arrives first. A slow report and an interactive request each get one.

```ts
defineTimeoutProfile(
  options: { statementTimeoutSeconds: number; lockTimeoutSeconds: number; queryGraceSeconds?: number },
): ITimeoutProfile
```

**Parameters**

- `options` `{ statementTimeoutSeconds: number; lockTimeoutSeconds: number; queryGraceSeconds?: number }`: The statement and lock timeouts, and the client's grace, 15 seconds by default, all in seconds.

**Returns** `ITimeoutProfile`: The profile, frozen.

**Throws**

- `INVALID_TIMEOUT_PROFILE`: The timeout profile needs positive, finite seconds, with the lock timeout no longer than the statement timeout.

**Example**

```ts
import { defineTimeoutProfile } from 'jg-postgres';

const REPORT_TIMEOUTS = defineTimeoutProfile({ statementTimeoutSeconds: 120, lockTimeoutSeconds: 10 });
REPORT_TIMEOUTS.queryTimeoutSeconds; // 135
```

##### <a id="api-diffTables"></a>`diffTables`

*Function*

Compares the tables a schema has with the ones an app expects, such as in a health check after a migration. Names compare exactly, as PostgreSQL stores them.

```ts
diffTables(actual: readonly string[], expected: readonly string[]): ITableDiff
```

**Parameters**

- `actual` `readonly string[]`: The tables that exist, such as listTableSizes' names.
- `expected` `readonly string[]`: The tables the app expects.

**Returns** `ITableDiff`: The expected tables that are missing, and the tables that aren't expected, each sorted and without duplicates.

**Example**

```ts
import { diffTables } from 'jg-postgres';

diffTables(['app_user', 'session'], ['app_user', 'invitation']);
// => { missing: ['invitation'], unexpected: ['session'] }
```

##### <a id="api-isCheckViolationError"></a>`isCheckViolationError`

*Function*

Checks whether an error is a check violation (SQLSTATE 23514), optionally on one constraint.

```ts
isCheckViolationError(
  error: unknown,
  constraint?: string,
): error is DatabaseError
```

**Parameters**

- `error` `unknown`: The error.
- `constraint?` `string`: The check constraint's name, when it must match.

**Returns** `error is DatabaseError`: Whether the error matches.

**Example**

```ts
import { isCheckViolationError } from 'jg-postgres';

declare const error: unknown;
isCheckViolationError(error, 'ck_order__quantity');
```

##### <a id="api-isDatabaseError"></a>`isDatabaseError`

*Function*

Checks whether an error is one PostgreSQL returned, with its SQLSTATE code, as node-postgres reports it.

```ts
isDatabaseError(error: unknown): error is DatabaseError
```

**Parameters**

- `error` `unknown`: The error.

**Returns** `error is DatabaseError`: Whether it's a node-postgres DatabaseError.

**Example**

```ts
import { isDatabaseError } from 'jg-postgres';

isDatabaseError(new Error('Connection terminated')); // => false
```

##### <a id="api-isDeadlockDetectedError"></a>`isDeadlockDetectedError`

*Function*

Checks whether an error is a detected deadlock (SQLSTATE 40P01), which a retry of the whole transaction may fix.

```ts
isDeadlockDetectedError(error: unknown): error is DatabaseError
```

**Parameters**

- `error` `unknown`: The error.

**Returns** `error is DatabaseError`: Whether the error matches.

**Example**

```ts
import { isDeadlockDetectedError } from 'jg-postgres';

declare const error: unknown;
isDeadlockDetectedError(error);
```

##### <a id="api-isForeignKeyViolationError"></a>`isForeignKeyViolationError`

*Function*

Checks whether an error is a foreign key violation (SQLSTATE 23503), optionally on one constraint.

```ts
isForeignKeyViolationError(
  error: unknown,
  constraint?: string,
): error is DatabaseError
```

**Parameters**

- `error` `unknown`: The error.
- `constraint?` `string`: The foreign key's name, when it must match.

**Returns** `error is DatabaseError`: Whether the error matches.

**Example**

```ts
import { isForeignKeyViolationError } from 'jg-postgres';

declare const error: unknown;
isForeignKeyViolationError(error, 'fk_order__user_account_id');
```

##### <a id="api-isInvalidTextRepresentationError"></a>`isInvalidTextRepresentationError`

*Function*

Checks whether an error is an invalid text representation (SQLSTATE 22P02), such as text where a UUID belongs.

```ts
isInvalidTextRepresentationError(error: unknown): error is DatabaseError
```

**Parameters**

- `error` `unknown`: The error.

**Returns** `error is DatabaseError`: Whether the error matches.

**Example**

```ts
import { isInvalidTextRepresentationError } from 'jg-postgres';

declare const error: unknown;
isInvalidTextRepresentationError(error);
```

##### <a id="api-isNotNullViolationError"></a>`isNotNullViolationError`

*Function*

Checks whether an error is a not-null violation (SQLSTATE 23502).

```ts
isNotNullViolationError(error: unknown): error is DatabaseError
```

**Parameters**

- `error` `unknown`: The error.

**Returns** `error is DatabaseError`: Whether the error matches.

**Example**

```ts
import { isNotNullViolationError } from 'jg-postgres';

declare const error: unknown;
isNotNullViolationError(error);
```

##### <a id="api-isNumericValueOutOfRangeError"></a>`isNumericValueOutOfRangeError`

*Function*

Checks whether an error is a numeric value out of range (SQLSTATE 22003).

```ts
isNumericValueOutOfRangeError(error: unknown): error is DatabaseError
```

**Parameters**

- `error` `unknown`: The error.

**Returns** `error is DatabaseError`: Whether the error matches.

**Example**

```ts
import { isNumericValueOutOfRangeError } from 'jg-postgres';

declare const error: unknown;
isNumericValueOutOfRangeError(error);
```

##### <a id="api-isQueryCanceledError"></a>`isQueryCanceledError`

*Function*

Checks whether an error is a canceled query (SQLSTATE 57014), such as one that ran past statement_timeout.

```ts
isQueryCanceledError(error: unknown): error is DatabaseError
```

**Parameters**

- `error` `unknown`: The error.

**Returns** `error is DatabaseError`: Whether the error matches.

**Example**

```ts
import { isQueryCanceledError } from 'jg-postgres';

declare const error: unknown;
isQueryCanceledError(error);
```

##### <a id="api-isRestrictViolationError"></a>`isRestrictViolationError`

*Function*

Checks whether an error is a restrict violation (SQLSTATE 23001), optionally on one constraint.

```ts
isRestrictViolationError(
  error: unknown,
  constraint?: string,
): error is DatabaseError
```

**Parameters**

- `error` `unknown`: The error.
- `constraint?` `string`: The foreign key's name, when it must match.

**Returns** `error is DatabaseError`: Whether the error matches.

**Example**

```ts
import { isRestrictViolationError } from 'jg-postgres';

declare const error: unknown;
isRestrictViolationError(error);
```

##### <a id="api-isSerializationFailureError"></a>`isSerializationFailureError`

*Function*

Checks whether an error is a serialization failure (SQLSTATE 40001), which a retry of the whole transaction may fix.

```ts
isSerializationFailureError(error: unknown): error is DatabaseError
```

**Parameters**

- `error` `unknown`: The error.

**Returns** `error is DatabaseError`: Whether the error matches.

**Example**

```ts
import { isSerializationFailureError } from 'jg-postgres';

declare const error: unknown;
isSerializationFailureError(error);
```

##### <a id="api-isStringDataRightTruncationError"></a>`isStringDataRightTruncationError`

*Function*

Checks whether an error is a string data right truncation (SQLSTATE 22001): a value longer than its column allows.

```ts
isStringDataRightTruncationError(error: unknown): error is DatabaseError
```

**Parameters**

- `error` `unknown`: The error.

**Returns** `error is DatabaseError`: Whether the error matches.

**Example**

```ts
import { isStringDataRightTruncationError } from 'jg-postgres';

declare const error: unknown;
isStringDataRightTruncationError(error);
```

##### <a id="api-isUniqueViolationError"></a>`isUniqueViolationError`

*Function*

Checks whether an error is a unique violation (SQLSTATE 23505), optionally on one constraint.

```ts
isUniqueViolationError(
  error: unknown,
  constraint?: string,
): error is DatabaseError
```

**Parameters**

- `error` `unknown`: The error.
- `constraint?` `string`: The unique constraint's or index's name, when it must match.

**Returns** `error is DatabaseError`: Whether the error matches.

**Example**

```ts
import { isUniqueViolationError } from 'jg-postgres';

declare const error: unknown;
if (isUniqueViolationError(error, 'uq_user_account__email')) {
  // the email is taken
}
```

##### <a id="api-listByCursor"></a>`listByCursor`

*Function*

Lists a page of a table's rows after a cursor, ordered by the cursor column. Every name is quoted, and the order and the cursor comparison use the table's own column, so a selected column can't change them.

```ts
listByCursor<T extends QueryResultRow = QueryResultRow>(
  executor: IQueryExecutor,
  options: ICursorListingOptions,
): Promise<T[]>
```

**Parameters**

- `executor` `IQueryExecutor`: The pool, or a transaction's client.
- `options` `ICursorListingOptions`: The table, its columns, the cursor column and direction, filters, the previous page's last cursor, and the limit.

**Returns** `Promise<T[]>`: A promise that resolves to the rows.

**Throws**

- `INVALID_IDENTIFIER`: The identifier is empty, holds a null character, or is longer than 63 bytes.

**Example**

```ts
import { createPostgres, listByCursor } from 'jg-postgres';

declare const postgres: ReturnType<typeof createPostgres>;

const errors = await listByCursor<{ id: string; origin: string }>(postgres.pool, {
  table: 'api_error',
  columns: ['id', 'origin'],
  filters: [{ column: 'origin', value: 'api-admin' }],
  after: '120',
  limit: 20,
});
```

##### <a id="api-listTableSizes"></a>`listTableSizes`

*Function*

Lists the ordinary tables in a schema with their total size on disk, indexes and TOAST data included, sorted by name.

```ts
listTableSizes(
  executor: IQueryExecutor,
  options?: IIntrospectionOptions,
): Promise<ITableSize[]>
```

**Parameters**

- `executor` `IQueryExecutor`: The pool, or a client.
- `options?` `IIntrospectionOptions`: The schema. Defaults to `{}`.

**Returns** `Promise<ITableSize[]>`: A promise that resolves to the tables.

**Example**

```ts
import { createPostgres, listTableSizes } from 'jg-postgres';

declare const postgres: ReturnType<typeof createPostgres>;

const tables = await listTableSizes(postgres.pool);
// [{ name: 'app_user', totalBytes: 65_536 }, { name: 'session', totalBytes: 16_384 }]
```

##### <a id="api-lockOrderedList"></a>`lockOrderedList`

*Function*

Locks an ordered list for the rest of a transaction, so no move or append changes it meanwhile, such as before appending a row after the list's last sort order. moveUp and moveDown take the same lock.

```ts
lockOrderedList(client: PoolClient, list: IOrderedList): Promise<void>
```

**Parameters**

- `client` `PoolClient`: The transaction's client.
- `list` `IOrderedList`: The table, its sort order column, and the scope that picks one list.

**Returns** `Promise<void>`: A promise that resolves once the lock is held.

**Example**

```ts
import { createPostgres, lockOrderedList } from 'jg-postgres';

declare const postgres: ReturnType<typeof createPostgres>;

await postgres.transaction(async (client) => {
  await lockOrderedList(client, { table: 'category', scope: [{ column: 'group_id', value: 1 }] });
  await client.query(
    `INSERT INTO category (group_id, sort_order)
       SELECT 1, COALESCE(MAX(sort_order), 0) + 10 FROM category WHERE group_id = 1;`,
  );
});
```

##### <a id="api-lockTransaction"></a>`lockTransaction`

*Function*

Takes an advisory lock for the rest of a transaction, waiting while another transaction holds it. PostgreSQL releases it when the transaction commits or rolls back.

```ts
lockTransaction(client: PoolClient, key: string): Promise<void>
```

**Parameters**

- `client` `PoolClient`: The transaction's client.
- `key` `string`: The lock's key, from createAdvisoryLockKey.

**Returns** `Promise<void>`: A promise that resolves once the lock is held.

**Example**

```ts
import { createAdvisoryLockKey, createPostgres, lockTransaction } from 'jg-postgres';

declare const postgres: ReturnType<typeof createPostgres>;

await postgres.transaction(async (client) => {
  await lockTransaction(client, createAdvisoryLockKey('invoice-numbers'));
  // only one transaction at a time gets here
});
```

##### <a id="api-moveDown"></a>`moveDown`

*Function*

Moves a row one place down an ordered list, by swapping its sort order with the closest larger one, in its own transaction under the list's lock.

```ts
moveDown(
  postgres: IPostgres,
  rowId: IRowId,
  options: IMoveOptions,
): Promise<void>
```

**Parameters**

- `postgres` `IPostgres`: The database, from createPostgres.
- `rowId` `IRowId`: The row's identifier.
- `options` `IMoveOptions`: The table, its sort order constraint, and, when they differ from the defaults, its ID and sort order columns, and the scope that picks one list.

**Returns** `Promise<void>`: A promise that resolves once the move commits.

**Throws**

- `INVALID_IDENTIFIER`: The identifier is empty, holds a null character, or is longer than 63 bytes.
- `MOVE_FAILED`: The move did not update exactly two rows.
- `ROW_NOT_FOUND`: The row does not exist in the ordered list.
- `ROW_ALREADY_LAST`: The row is already last in the ordered list.
- `TRANSACTION_OUTCOME_UNKNOWN`: The connection was lost during COMMIT, so the transaction may or may not have committed.

**Example**

```ts
import { createPostgres, moveDown } from 'jg-postgres';

declare const postgres: ReturnType<typeof createPostgres>;

await moveDown(postgres, 42, { table: 'category', sortOrderConstraint: 'uq_category__sort_order' });
```

##### <a id="api-moveUp"></a>`moveUp`

*Function*

Moves a row one place up an ordered list, by swapping its sort order with the closest smaller one, in its own transaction under the list's lock.

```ts
moveUp(postgres: IPostgres, rowId: IRowId, options: IMoveOptions): Promise<void>
```

**Parameters**

- `postgres` `IPostgres`: The database, from createPostgres.
- `rowId` `IRowId`: The row's identifier.
- `options` `IMoveOptions`: The table, its sort order constraint, and, when they differ from the defaults, its ID and sort order columns, and the scope that picks one list.

**Returns** `Promise<void>`: A promise that resolves once the move commits.

**Throws**

- `INVALID_IDENTIFIER`: The identifier is empty, holds a null character, or is longer than 63 bytes.
- `MOVE_FAILED`: The move did not update exactly two rows.
- `ROW_NOT_FOUND`: The row does not exist in the ordered list.
- `ROW_ALREADY_FIRST`: The row is already first in the ordered list.
- `TRANSACTION_OUTCOME_UNKNOWN`: The connection was lost during COMMIT, so the transaction may or may not have committed.

**Example**

```ts
import { createPostgres, moveUp } from 'jg-postgres';

declare const postgres: ReturnType<typeof createPostgres>;

await moveUp(postgres, 42, { table: 'category', sortOrderConstraint: 'uq_category__sort_order' });
```

##### <a id="api-quoteIdentifier"></a>`quoteIdentifier`

*Function*

Quotes a table, column, or constraint name for SQL that can't take it as a parameter. A name PostgreSQL would cut short, an empty one, or one with a null character throws instead.

```ts
quoteIdentifier(name: string): string
```

**Parameters**

- `name` `string`: The name, such as `order` or `sort_order`.

**Returns** `string`: The quoted name, such as `"order"`.

**Throws**

- `INVALID_IDENTIFIER`: The identifier is empty, holds a null character, or is longer than 63 bytes.

**Example**

```ts
import { quoteIdentifier } from 'jg-postgres';

quoteIdentifier('order'); // => '"order"'
quoteIdentifier('say "hi"'); // => '"say ""hi"""'
```

##### <a id="api-readDatabaseSummary"></a>`readDatabaseSummary`

*Function*

Reads a summary of the database the executor is connected to, such as for an admin dashboard: its name, the server's version, its size, its active connections out of the server's maximum, and the ordinary tables in a schema. Without the pg_read_all_stats role, PostgreSQL shows only the role's own connections, so activeConnections undercounts.

```ts
readDatabaseSummary(
  executor: IQueryExecutor,
  options?: IIntrospectionOptions,
): Promise<IDatabaseSummary>
```

**Parameters**

- `executor` `IQueryExecutor`: The pool, or a client.
- `options?` `IIntrospectionOptions`: The schema whose tables are counted. Defaults to `{}`.

**Returns** `Promise<IDatabaseSummary>`: A promise that resolves to the summary.

**Example**

```ts
import { createPostgres, readDatabaseSummary } from 'jg-postgres';

declare const postgres: ReturnType<typeof createPostgres>;

const summary = await readDatabaseSummary(postgres.pool);
// { name: 'app', serverVersion: '18.1', serverVersionNumber: 180001, sizeBytes: 8_000_000,
//   activeConnections: 3, maxConnections: 100, tableCount: 12 }
```

##### <a id="api-toPoolTimeouts"></a>`toPoolTimeouts`

*Function*

Gives a profile's timeouts as createPostgres takes them, in milliseconds, for a pool whose every query follows the profile.

```ts
toPoolTimeouts(
  profile: ITimeoutProfile,
): { statement_timeout: number; lock_timeout: number; query_timeout: number }
```

**Parameters**

- `profile` `ITimeoutProfile`: The profile.

**Returns** `{ statement_timeout: number; lock_timeout: number; query_timeout: number }`: The pool's `statement_timeout`, `lock_timeout`, and `query_timeout`.

**Throws**

- `INVALID_TIMEOUT_PROFILE`: The timeout profile needs positive, finite seconds, with the lock timeout no longer than the statement timeout.

**Example**

```ts
import { createPostgres, defineTimeoutProfile, toPoolTimeouts } from 'jg-postgres';

const STANDARD = defineTimeoutProfile({ statementTimeoutSeconds: 30, lockTimeoutSeconds: 5 });
const postgres = createPostgres({ connectionString: process.env['DATABASE_URL'], ...toPoolTimeouts(STANDARD) });
```

##### <a id="api-withQueryTimeout"></a>`withQueryTimeout`

*Function*

Gives a query the client timeout of a profile, which node-postgres's pool otherwise sets for every query, so a query in a transaction with longer timeouts isn't cut off by the pool's.

```ts
withQueryTimeout(
  query: { text: string; values?: unknown[] },
  profile: ITimeoutProfile,
): QueryConfig<any[]> & { query_timeout: number }
```

**Parameters**

- `query` `{ text: string; values?: unknown[] }`: The query's text and values.
- `profile` `ITimeoutProfile`: The profile.

**Returns** `QueryConfig<any[]> & { query_timeout: number }`: The query, as node-postgres's query methods take it.

**Throws**

- `INVALID_TIMEOUT_PROFILE`: The timeout profile needs positive, finite seconds, with the lock timeout no longer than the statement timeout.

**Example**

```ts
import { createPostgres, defineTimeoutProfile, withQueryTimeout } from 'jg-postgres';

declare const postgres: ReturnType<typeof createPostgres>;

const REPORT_TIMEOUTS = defineTimeoutProfile({ statementTimeoutSeconds: 120, lockTimeoutSeconds: 10 });
await postgres.transaction(
  (client) =>
    client.query(withQueryTimeout({ text: 'SELECT build_report($1);', values: [2026] }, REPORT_TIMEOUTS)),
  { timeouts: REPORT_TIMEOUTS },
);
```

##### <a id="api-withSessionLock"></a>`withSessionLock`

*Function*

Runs an operation while holding an advisory lock on one connection, across as many transactions as it needs, or skips it when another session holds the lock. The lock lasts as long as its connection, so the operation can check it's still held. Afterwards the lock is released, and the client is destroyed instead of reused when the release fails or the connection was lost, which releases the lock too.

```ts
withSessionLock<T>(
  postgres: IPostgres,
  key: string,
  operation: (lock: ISessionLock) => Promise<T>,
): Promise<ISessionLockResult<T>>
```

**Parameters**

- `postgres` `IPostgres`: The database, from createPostgres.
- `key` `string`: The lock's key, from createAdvisoryLockKey.
- `operation` `(lock: ISessionLock) => Promise<T>`: The work, given the lock.

**Returns** `Promise<ISessionLockResult<T>>`: A promise that resolves to `{ acquired: false }` when another session holds the lock, or `{ acquired: true, result }` with what the operation resolved to.

**Throws**

- `SESSION_LOCK_LOST`: The connection that held the session lock was lost.

**Example**

```ts
import { createAdvisoryLockKey, createPostgres, withSessionLock } from 'jg-postgres';

declare const postgres: ReturnType<typeof createPostgres>;

const outcome = await withSessionLock(
  postgres,
  createAdvisoryLockKey('user-account-deletion:42'),
  async (lock) => {
    lock.assertActive();
    return 'deleted';
  },
);
if (!outcome.acquired) {
  // another request is already deleting the account
}
```

#### <a id="api-constants"></a>Constants in `jg-postgres`

##### <a id="api-ERRORS"></a>`ERRORS`

*Constant*

the codes for every error thrown by the package

```ts
const ERRORS: { INVALID_IDENTIFIER: "INVALID_IDENTIFIER"; TRANSACTION_OUTCOME_UNKNOWN: "TRANSACTION_OUTCOME_UNKNOWN"; SESSION_LOCK_LOST: "SESSION_LOCK_LOST"; ROW_NOT_FOUND: "ROW_NOT_FOUND"; ROW_ALREADY_FIRST: "ROW_ALREADY_FIRST"; ROW_ALREADY_LAST: "ROW_ALREADY_LAST"; MOVE_FAILED: "MOVE_FAILED"; INVALID_TIMEOUT_PROFILE: "INVALID_TIMEOUT_PROFILE"; INVALID_OPTIONAL_READ_OPTIONS: "INVALID_OPTIONAL_READ_OPTIONS"; INVALID_USAGE_ADMISSION: "INVALID_USAGE_ADMISSION"; INVALID_VERIFICATION_POLICY: "INVALID_VERIFICATION_POLICY"; INVALID_VERIFICATION_INPUT: "INVALID_VERIFICATION_INPUT"; VERIFICATION_DELIVERY_FAILED: "VERIFICATION_DELIVERY_FAILED"; INVALID_COUNTER_TABLE: "INVALID_COUNTER_TABLE"; INVALID_COUNTER_CHANGE: "INVALID_COUNTER_CHANGE"; COUNTER_ROW_NOT_FOUND: "COUNTER_ROW_NOT_FOUND" }
```

##### <a id="api-POSTGRES_BIGINT_MAX"></a>`POSTGRES_BIGINT_MAX`

*Constant*

the largest value a PostgreSQL BIGINT column holds

```ts
const POSTGRES_BIGINT_MAX: 9223372036854775807n
```

##### <a id="api-POSTGRES_BIGINT_MIN"></a>`POSTGRES_BIGINT_MIN`

*Constant*

the smallest value a PostgreSQL BIGINT column holds

```ts
const POSTGRES_BIGINT_MIN: -9223372036854775808n
```

#### <a id="api-types"></a>Types in `jg-postgres`

##### <a id="api-IColumnFilter"></a>`IColumnFilter`

*Type*

an equality condition on one column, such as { column: 'group_id', value: 1 }

```ts
type IColumnFilter = {
    column: string;
    value: unknown;
};
```

**Members**

- `column: string` (property): the column's name, quoted in the SQL
- `value: unknown` (property): the value it must equal, sent as a query parameter

##### <a id="api-ICounterChanges"></a>`ICounterChanges`

*Type*

the whole amounts to add to some of the counters

```ts
type ICounterChanges<C extends string> = Partial<Record<C, number | bigint>>;
```

##### <a id="api-ICounterKey"></a>`ICounterKey`

*Type*

a row's key: a value for every key column, none of them empty

```ts
type ICounterKey<K extends string> = Record<K, string | number | bigint>;
```

##### <a id="api-ICounterQuery"></a>`ICounterQuery`

*Type*

a query a counter table builds, with the key that orders it among a transaction's other queries

```ts
type ICounterQuery = {
    text: string;
    values: unknown[];
    lockKey: string;
};
```

**Members**

- `text: string` (property): the SQL, with every value as a parameter
- `values: unknown[]` (property): the parameters, with bigints as strings
- `lockKey: string` (property): the table and the row's key, so queries for the same rows run in the same order everywhere

##### <a id="api-ICounterTable"></a>`ICounterTable`

*Type*

builds the queries that change a counter table's rows

```ts
type ICounterTable<K extends string, C extends string> = {
    buildIncrement: (key: ICounterKey<K>, increments: ICounterChanges<C>) => ICounterQuery;
    buildAdjustment: (key: ICounterKey<K>, changes: ICounterChanges<C>) => ICounterQuery;
};
```

**Members**

- `buildIncrement: (key: ICounterKey<K>, increments: ICounterChanges<C>) => ICounterQuery` (property): inserts the row with the increments, or adds them to the row that exists
- `buildAdjustment: (key: ICounterKey<K>, changes: ICounterChanges<C>) => ICounterQuery` (property): adds the changes, which may be negative, to a row that must exist

##### <a id="api-ICounterTableDefinition"></a>`ICounterTableDefinition`

*Type*

a counter table's layout: its name, the columns that identify a row, and the counter columns

```ts
type ICounterTableDefinition<K extends string, C extends string> = {
    table: string;
    keyColumns: readonly K[];
    counters: readonly C[];
};
```

**Members**

- `table: string` (property): the table's name, quoted in the SQL
- `keyColumns: readonly K[]` (property): the columns that identify a row, which a unique constraint must cover exactly
- `counters: readonly C[]` (property): the BIGINT counter columns, each with a default, such as 0

##### <a id="api-ICursorListingOptions"></a>`ICursorListingOptions`

*Type*

what listByCursor lists

```ts
type ICursorListingOptions = {
    table: string;
    columns: readonly string[];
    cursorColumn?: string;
    sortDirection?: 'ASC' | 'DESC';
    filters?: readonly IColumnFilter[];
    after?: IRowId | undefined;
    limit: number;
};
```

**Members**

- `table: string` (property): the table's name, quoted in the SQL
- `columns: readonly string[]` (property): the columns to select, each quoted in the SQL
- `cursorColumn: string` (property): the unique column the pages follow, such as an ascending ID. Defaults to 'id'
- `sortDirection: "ASC" | "DESC"` (property): the order rows come in. Defaults to 'DESC', newest first for ascending IDs
- `filters: readonly IColumnFilter[]` (property): equality conditions every listed row meets
- `after: IRowId` (property): the cursor column's value on the last row of the previous page, or undefined for the first
- `limit: number` (property): the most rows to return

##### <a id="api-ICursorPage"></a>`ICursorPage`

*Type*

a page of rows, and the cursor of the next page, or null when this is the last

```ts
type ICursorPage<T> = {
    items: T[];
    nextCursor: string | null;
};
```

**Members**

- `items: T[]` (property)
- `nextCursor: string | null` (property)

##### <a id="api-IDatabaseSummary"></a>`IDatabaseSummary`

*Type*

the database a connection is on: its name, server, size, connections, and tables

```ts
type IDatabaseSummary = {
    name: string;
    serverVersion: string;
    serverVersionNumber: number;
    sizeBytes: number;
    activeConnections: number;
    maxConnections: number;
    tableCount: number;
};
```

**Members**

- `name: string` (property): the database's name
- `serverVersion: string` (property): the server's version, such as '18.1'
- `serverVersionNumber: number` (property): the server's version as a number, such as 180001, to compare versions
- `sizeBytes: number` (property): the database's size on disk, in bytes
- `activeConnections: number` (property): the connections to this database running a query now. Without pg_read_all_stats, only the role's own connections are counted
- `maxConnections: number` (property): the most connections the server accepts, over every database
- `tableCount: number` (property): the ordinary tables in the schema

##### <a id="api-IIntrospectionOptions"></a>`IIntrospectionOptions`

*Type*

which schema readDatabaseSummary and listTableSizes read the tables of

```ts
type IIntrospectionOptions = {
    schema?: string | undefined;
};
```

**Members**

- `schema: string` (property): the schema. Defaults to 'public'

##### <a id="api-IMoveOptions"></a>`IMoveOptions`

*Type*

what moveUp and moveDown move within

```ts
type IMoveOptions = IOrderedList & {
    sortOrderConstraint: string;
    idColumn?: string;
};
```

##### <a id="api-IOptionalRead"></a>`IOptionalRead`

*Type*

a read that may not run: its value once known, and a promise of it; null when it was skipped, ran out of time, or failed

```ts
type IOptionalRead<T> = {
    getSnapshot: () => T | null;
    completion: Promise<T | null>;
};
```

**Members**

- `getSnapshot: () => T | null` (property): the value, or null until the read finishes, and when it gives none
- `completion: Promise<T | null>` (property): resolves to the value, or null; it never rejects

##### <a id="api-IOptionalReader"></a>`IOptionalReader`

*Type*

runs optional reads, such as a counter in a page's header, that never delay or crowd out essential work

```ts
type IOptionalReader = {
    read: <T>(key: string, query: {
        text: string;
        values?: unknown[] | undefined;
    }, parse: (row: unknown) => T) => IOptionalRead<T>;
};
```

**Members**

- `read: (key: string, query: { text: string; values?: unknown[] }, parse: (row: unknown) => T) => IOptionalRead<T>` (property): starts a read of the query's first row, or joins the one running for the same key

##### <a id="api-IOptionalReaderOptions"></a>`IOptionalReaderOptions`

*Type*

how createOptionalReader admits and bounds reads

```ts
type IOptionalReaderOptions = {
    postgres: IPostgres;
    maxActiveReads?: number | undefined;
    reservedIdleClients?: number | undefined;
    admissionSeconds?: number | undefined;
    timeouts?: ITimeoutProfile | undefined;
};
```

**Members**

- `postgres: IPostgres` (property): the database the reads run on
- `maxActiveReads: number` (property): the most reads running at once. Defaults to 2
- `reservedIdleClients: number` (property): the clients a read leaves free for essential work: it's skipped unless the pool can give more than this many. Defaults to 2
- `admissionSeconds: number` (property): the seconds a read may take, waiting for a client included, before it gives null. Defaults to 1
- `timeouts: ITimeoutProfile` (property): each read's timeouts. Defaults to a 0.5-second statement timeout, a 0.1-second lock timeout, and a 0.75-second client timeout

##### <a id="api-IOrderedList"></a>`IOrderedList`

*Type*

an ordered list: the rows of a table, or of one group in it, ordered by a column

```ts
type IOrderedList = {
    table: string;
    sortOrderColumn?: string;
    scope?: readonly IColumnFilter[];
};
```

**Members**

- `table: string` (property): the table's name
- `sortOrderColumn: string` (property): the column the rows are ordered by. Defaults to 'sort_order'
- `scope: readonly IColumnFilter[]` (property): equality conditions that pick one ordered list when a table holds several, such as [{ column: 'group_id', value: 1 }]

##### <a id="api-IPostgres"></a>`IPostgres`

*Type*

a database: its pool, and the helpers that use it

```ts
type IPostgres = {
    readonly pool: Pool;
    query: <T extends QueryResultRow = QueryResultRow>(text: string, params?: readonly unknown[]) => Promise<T[]>;
    transaction: <T>(operation: (client: PoolClient) => Promise<T>, options?: ITransactionOptions) => Promise<T>;
    close: () => Promise<void>;
};
```

**Members**

- `pool: Pool` (property): the node-postgres pool, for what the helpers don't cover
- `query: (text: string, params?: readonly unknown[]) => Promise<T[]>` (property): runs a query on the pool and returns its rows
- `transaction: (operation: (client: PoolClient) => Promise<T>, options?: ITransactionOptions) => Promise<T>` (property): runs an operation in a transaction on one client, committing when it resolves and rolling back when it throws, optionally with timeouts of its own. Never start a transaction inside another: run the inner work on the client instead
- `close: () => Promise<void>` (property): ends every connection, once queries in flight finish

##### <a id="api-IPostgresConfig"></a>`IPostgresConfig`

*Type*

what createPostgres takes: node-postgres's pool configuration, such as the host, the pool size, and connectionTimeoutMillis, statement_timeout, lock_timeout, and idle_in_transaction_session_timeout, plus where errors outside any query go

```ts
type IPostgresConfig = Omit<PoolConfig, 'types'> & {
    onError?: ((error: unknown) => void) | undefined;
};
```

##### <a id="api-IQueryExecutor"></a>`IQueryExecutor`

*Type*

what runs a query: the pool, or a client checked out from it, such as a transaction's

```ts
type IQueryExecutor = Pool | PoolClient;
```

##### <a id="api-IRowId"></a>`IRowId`

*Type*

a row's identifier, as a cursor or a row to move

```ts
type IRowId = string | number | bigint;
```

##### <a id="api-ISessionLock"></a>`ISessionLock`

*Type*

what withSessionLock gives the operation while it holds the lock

```ts
type ISessionLock = {
    readonly client: PoolClient;
    isActive: () => boolean;
    assertActive: () => void;
    destroyClient: () => void;
};
```

**Members**

- `client: PoolClient` (property): the client that holds the lock, for work that must stop when the lock does
- `isActive: () => boolean` (property): whether the lock's connection is still alive, so the lock is still held
- `assertActive: () => void` (property): throws SESSION_LOCK_LOST when the lock's connection was lost
- `destroyClient: () => void` (property): marks the client as unsafe to reuse, such as after work on it ended in an unknown state, so it's destroyed instead of returned to the pool

##### <a id="api-ISessionLockResult"></a>`ISessionLockResult`

*Type*

what withSessionLock resolves to: whether it got the lock, and the operation's result when it did

```ts
type ISessionLockResult<T> = {
    acquired: false;
} | {
    acquired: true;
    result: T;
};
```

##### <a id="api-ITableDiff"></a>`ITableDiff`

*Type*

how a schema's tables differ from the ones expected

```ts
type ITableDiff = {
    missing: string[];
    unexpected: string[];
};
```

**Members**

- `missing: string[]` (property): the expected tables that don't exist
- `unexpected: string[]` (property): the tables that exist but aren't expected

##### <a id="api-ITableSize"></a>`ITableSize`

*Type*

a table and its size on disk, with its indexes and TOAST data

```ts
type ITableSize = {
    name: string;
    totalBytes: number;
};
```

**Members**

- `name: string` (property): the table's name
- `totalBytes: number` (property): its total size, in bytes

##### <a id="api-ITimeoutProfile"></a>`ITimeoutProfile`

*Type*

how long a query may run, in seconds: on the server, waiting for a lock, and on the client, which stops waiting for the server's answer. defineTimeoutProfile builds it

```ts
type ITimeoutProfile = Readonly<{
    statementTimeoutSeconds: number;
    lockTimeoutSeconds: number;
    queryTimeoutSeconds: number;
}>;
```

##### <a id="api-ITransactionOptions"></a>`ITransactionOptions`

*Type*

how a transaction runs

```ts
type ITransactionOptions = {
    timeouts?: ITimeoutProfile | undefined;
};
```

**Members**

- `timeouts: ITimeoutProfile` (property): the statement and lock timeouts for this transaction only, from defineTimeoutProfile. The pool's query_timeout still applies to each query that doesn't set its own, so pass the operation's queries through withQueryTimeout with the same profile. A query the client stops waiting for is rolled back: the client goes back to the pool once ROLLBACK, which waits for the server and has the pool's query_timeout too, confirms the connection, and is destroyed otherwise

##### <a id="api-IUsageAdmission"></a>`IUsageAdmission`

*Type*

what admitUsage decides, with when to try again: accepted, refused by the scope's cooldown, or refused because the actor reached the limit within the window

```ts
type IUsageAdmission = {
    outcome: 'accepted';
    retryAt: string | null;
} | {
    outcome: 'cooldown';
    retryAt: string;
} | {
    outcome: 'limit';
    retryAt: null;
};
```

##### <a id="api-IUsageAdmissionInput"></a>`IUsageAdmissionInput`

*Type*

what admitUsage counts and limits

```ts
type IUsageAdmissionInput = {
    actorId: string;
    actionId: string;
    scopeKey?: string | undefined;
    limit: number;
    windowSeconds: number;
    cooldownSeconds?: number | undefined;
};
```

**Members**

- `actorId: string` (property): who acts, such as a user's id: 1 to 256 characters
- `actionId: string` (property): what they do, such as `send-invitation`: 1 to 128 characters
- `scopeKey: string` (property): what they act on, such as the invited email's hash, which the cooldown applies to: 1 to 256 characters
- `limit: number` (property): the most times the actor may do the action within the window
- `windowSeconds: number` (property): the seconds the limit counts back from now
- `cooldownSeconds: number` (property): the seconds after an accepted action before the same action on the same scope is accepted again, by any actor. Only with a scopeKey

##### <a id="api-IVerificationAttempt"></a>`IVerificationAttempt`

*Type*

what consume checks: the binding the challenge was issued for, its token, and the code

```ts
type IVerificationAttempt = IVerificationBinding & {
    challengeToken: string;
    code: string;
};
```

##### <a id="api-IVerificationBinding"></a>`IVerificationBinding`

*Type*

who a challenge is for, what it verifies, and what on

```ts
type IVerificationBinding = {
    actorId: string;
    actionId: string;
    targetId: string;
};
```

**Members**

- `actorId: string` (property): who verifies, such as a user's id: 1 to 256 characters
- `actionId: string` (property): what they verify, such as `verify-email`, which must have a policy: 1 to 128 characters
- `targetId: string` (property): what they verify it on, such as the new email address: 1 to 256 characters

##### <a id="api-IVerificationChallenges"></a>`IVerificationChallenges`

*Type*

issues and consumes verification challenges with the policies they were created with

```ts
type IVerificationChallenges = {
    issue: (binding: IVerificationBinding, deliver: IVerificationDeliver) => Promise<IVerificationIssue>;
    consume: (attempt: IVerificationAttempt) => Promise<boolean>;
};
```

**Members**

- `issue: (binding: IVerificationBinding, deliver: IVerificationDeliver) => Promise<IVerificationIssue>` (property): issues a challenge for a binding, superseding its active one, and delivers its code once it's committed, unless the actor reached the issue limit
- `consume: (attempt: IVerificationAttempt) => Promise<boolean>` (property): uses a challenge when its code matches, or counts a failed attempt, and resolves to whether it matched

##### <a id="api-IVerificationChallengesOptions"></a>`IVerificationChallengesOptions`

*Type*

what createVerificationChallenges takes

```ts
type IVerificationChallengesOptions = {
    policies: Record<string, IVerificationPolicy>;
    codeLength?: number | undefined;
};
```

**Members**

- `policies: Record<string, IVerificationPolicy>` (property): each action's policy, keyed by its action id, such as `verify-email`
- `codeLength: number` (property): the code's digits: 4 to 10. Defaults to 6

##### <a id="api-IVerificationDeliver"></a>`IVerificationDeliver`

*Type*

sends a challenge's code to its actor, such as by email

```ts
type IVerificationDeliver = (delivery: IVerificationDelivery) => Promise<void> | void;
```

##### <a id="api-IVerificationDelivery"></a>`IVerificationDelivery`

*Type*

what deliver receives once the challenge is committed, to send the code to the actor

```ts
type IVerificationDelivery = IVerificationBinding & {
    code: string;
    challengeId: string;
    expiresAt: string;
    validitySeconds: number;
};
```

##### <a id="api-IVerificationIssue"></a>`IVerificationIssue`

*Type*

What issue resolves to: the challenge's token, which the client keeps and sends back with the code, and when it expires; or, when the actor reached the policy's issue limit, when they may be issued one again.

```ts
type IVerificationIssue = {
    outcome: 'issued';
    challengeToken: string;
    expiresAt: string;
} | {
    outcome: 'limit';
    retryAt: string;
};
```

##### <a id="api-IVerificationPolicy"></a>`IVerificationPolicy`

*Type*

how one action's challenges behave: how long they last, how many wrong codes they take, and how many an actor may be issued within a window

```ts
type IVerificationPolicy = {
    validitySeconds: number;
    maxFailedAttempts: number;
    issueLimit: number;
    issueWindowSeconds: number;
};
```

**Members**

- `validitySeconds: number` (property): the whole seconds a challenge stays valid after it's issued, at least 1
- `maxFailedAttempts: number` (property): the wrong codes a challenge takes before it stops accepting any: 1 to 32,767
- `issueLimit: number` (property): the most challenges an actor may be issued for the action within the window, over every target
- `issueWindowSeconds: number` (property): the whole seconds the issue limit counts back from now, at least 1

### <a id="api-entry-record-store"></a>`jg-postgres/record-store`

- Functions: [`createRecordStore`](#api-record-store-createRecordStore)
- Types: [`IRecordNumberKey`](#api-record-store-IRecordNumberKey), [`IRecordStore`](#api-record-store-IRecordStore), [`IRecordStoreOptions`](#api-record-store-IRecordStoreOptions), [`ISchema`](#api-record-store-ISchema)

#### <a id="api-record-store-functions"></a>Functions in `jg-postgres/record-store`

##### <a id="api-record-store-createRecordStore"></a>`createRecordStore`

*Function*

Creates a store for one JSON record, such as a set of counters or a feature's settings, kept in the record_store table the package's README describes, which the app creates. The defaults fill every missing field when it's read, and are the only keys a write keeps. Every read is checked with the schema. A method given a transaction's client runs in it, and never commits it.

```ts
createRecordStore<T extends Record<string, unknown>>(
  options: IRecordStoreOptions<T>,
): IRecordStore<T>
```

**Parameters**

- `options` `IRecordStoreOptions<T>`: The database, the record's id, its defaults, and its schema.

**Returns** `IRecordStore<T>`: The record's store.

**Example**

```ts
import { createPostgres } from 'jg-postgres';
import { createRecordStore } from 'jg-postgres/record-store';
import { z } from 'zod';

declare const postgres: ReturnType<typeof createPostgres>;

const counters = createRecordStore({
  postgres,
  id: 'admin:unread-counts',
  defaults: { notifications: 0, reports: 0 },
  schema: z.object({ notifications: z.number(), reports: z.number() }),
});

await counters.incrementNumber('notifications', 1);
await counters.get(); // => { notifications: 1, reports: 0 }
```

#### <a id="api-record-store-types"></a>Types in `jg-postgres/record-store`

##### <a id="api-record-store-IRecordNumberKey"></a>`IRecordNumberKey`

*Type*

the keys of a record whose values are numbers

```ts
type IRecordNumberKey<T extends Record<string, unknown>> = {
    [Key in keyof T]: T[Key] extends number ? Key : never;
}[keyof T] & string;
```

##### <a id="api-record-store-IRecordStore"></a>`IRecordStore`

*Type*

one record in the record_store table. Each method takes a transaction's client, to run in it without committing it, or runs on the pool

```ts
type IRecordStore<T extends Record<string, unknown>> = {
    get: (client?: PoolClient) => Promise<T>;
    set: (value: T, client?: PoolClient) => Promise<void>;
    incrementNumber: (field: IRecordNumberKey<T>, delta: number, client?: PoolClient) => Promise<void>;
    setNumber: (field: IRecordNumberKey<T>, value: number, client?: PoolClient) => Promise<void>;
};
```

**Members**

- `get: (client?: PoolClient) => Promise<T>` (property): reads the record, with the defaults filling every missing or null field
- `set: (value: T, client?: PoolClient) => Promise<void>` (property): replaces the record, with the defaults filling every undefined or null field
- `incrementNumber: (field: IRecordNumberKey<T>, delta: number, client?: PoolClient) => Promise<void>` (property): adds to one numeric field atomically, so concurrent increments all count
- `setNumber: (field: IRecordNumberKey<T>, value: number, client?: PoolClient) => Promise<void>` (property): replaces one numeric field, leaving the record's other fields as they are

##### <a id="api-record-store-IRecordStoreOptions"></a>`IRecordStoreOptions`

*Type*

what createRecordStore keeps

```ts
type IRecordStoreOptions<T extends Record<string, unknown>> = {
    postgres: IPostgres;
    id: string;
    defaults: T;
    schema: ISchema<T>;
};
```

**Members**

- `postgres: IPostgres` (property): the database whose record_store table holds the record
- `id: string` (property): the record's id in the table, such as 'notifications:unread-counts'
- `defaults: T` (property): the complete record: what get returns for a missing field, and the only keys a write keeps
- `schema: ISchema<T>` (property): checks the record get returns, defaults applied, such as a Zod schema. Its error propagates

##### <a id="api-record-store-ISchema"></a>`ISchema`

*Type*

validates data and returns it typed, which a Zod schema does

```ts
type ISchema<T> = {
    parse(value: unknown): T;
};
```

**Members**

- `parse(value: unknown): T` (method)