Skip to main content

Persistent PostgreSQL records

@kavtuai/guildgate/postgres provides the official store bundle and transaction adapter over one connection pool.

import { Pool } from "pg";
import { createPostgresAdapter } from "@kavtuai/guildgate/postgres";

const postgres = createPostgresAdapter({
  pool: new Pool({ connectionString: process.env.DATABASE_URL }),
  prefix: "guildgate",
});

await postgres.migrate();

const gate = createGuildGate({
  // app, security, owners
  stores: postgres.stores,
  transactions: postgres.transactions,
});

Migration SQL​

Review SQL without opening a connection:

npx guildgate-migration --prefix guildgate

Before production execution, review transaction behavior, lock time, indexes, and rollback planning. Give the application user only the schema privileges it needs.

Transaction finality​

In 1.1.0, PostgreSQL COMMIT is separated from post-commit callbacks. After a successful COMMIT:

  • the domain write is final
  • a callback failure does not issue rollback
  • a callback failure does not repeat domain work
  • the issue is observable through TransactionPostCommitError or action metadata

Nested transactions​

A new run() inside the same transaction context uses a savepoint:

SAVEPOINT ...
-- nested work
RELEASE SAVEPOINT ...

A nested error runs ROLLBACK TO SAVEPOINT. The inner scope cannot change isolation or read-only mode. Its afterCommit callbacks join the parent only after savepoint release.

Rate limits​

The PostgreSQL rate-limit store takes a transaction-scoped advisory lock for each bucket. The first insert and later updates use the same serialized decision path, preventing two workers from accepting requests beyond the limit together.

Idempotency​

The reservation ID is stored in the table. Complete, renew, and fail queries include reservation_id and state='inflight' predicates. No affected row produces idempotency_reservation_lost.

Audit paging​

Audit ordering is (created_at DESC, id DESC). The opaque cursor contains those two values and becomes the predicate for the next query. Avoid offset pagination because concurrent inserts can cause skipped or repeated records.

Outbox​

An outbox worker operates with:

  • bounded limit
  • worker identity
  • claim lease duration
  • bounded concurrency

On success it calls markPublished; on failure it stores a sanitized, short error through markFailed. Delivery is at least once, so consumers must deduplicate by event ID.

Sessions and credentials​

The per-user session cap is applied inside the store transaction. OAuth credentials are ciphertext; keep encryption keys in a separate secret system so a database backup alone cannot reveal tokens.

Production settings​

  • calculate connection-pool size with worker count
  • configure statement and lock timeouts
  • monitor slow queries and deadlocks
  • separate migration and runtime database users
  • plan retention and index maintenance for audit and outbox growth
  • practice restore procedures
Last updated on