SDK, connection pool

The SDK uses postgres-js (the postgres npm package) as its database client. One pool per app process; shared across modules.

Pool configuration

Default settings in src/db.ts:

const db = postgres(process.env.DATABASE_URL!, {
  max: 10,                  // max connections per process
  idle_timeout: 30,         // close idle connections after 30s
  connect_timeout: 10,      // fail if connection takes >10s
  prepare: true,            // use prepared statements
  ssl: getSslOptions(),     // verify-full in prod
});

max: 10 means each container holds up to 10 concurrent connections. Across Athena + Hera + Site in both domains, plus background processes, total connection count to the olympus database is up to ~60.

Per-database vs shared

The SDK has one pool to the olympus database. The Ory services (Kratos, Hydra) have their own connections to their own databases, those don't share the pool.

SSL

In production, sslmode=verify-full is required (ADR 0013). The SDK reads:

function getSslOptions() {
  if (process.env.DATABASE_URL?.includes("sslmode=verify-full")) {
    return {
      ca: readFileSync(process.env.PGSSLROOTCERT ?? "/etc/ssl/certs/postgres-ca.crt"),
      rejectUnauthorized: true,
    };
  }
  return process.env.NODE_ENV === "production" ? "require" : false;
}

Dev: SSL off. Prod: full verification with explicit CA bundle.

Auto-migration

On import, db.ts runs:

for (const migration of MIGRATIONS) {
  await db.unsafe(migration);
}

MIGRATIONS is an append-only array of CREATE TABLE IF NOT EXISTS and CREATE INDEX IF NOT EXISTS statements. New SDK versions append new entries.

This means: starting any SDK-importing app with a fresh olympus database results in a fully-migrated schema. No external migration tool needed.

The trade-off: schema migrations that require data transformation (not just structure) aren't well-handled by this approach. For those, ship a script alongside (like migrate-encryption-key.ts).

Connection leak detection

postgres-js doesn't ship with leak detection. If a connection is checked out and never returned (a await db.begin(...) with a throw that didn't release), eventually the pool exhausts.

Symptoms:

All API requests start failing with "no available connections" after some uptime.
Restart fixes temporarily.

Investigation: capture stack traces around each db.begin() call to identify where the leak is. The SDK doesn't currently do this; a planned feature is a DEBUG_DB_LEAKS=true env var.

Transactions

await db.begin(async tx => {
  await tx`UPDATE settings SET value = ${newValue} WHERE key = ${key}`;
  await tx`INSERT INTO audit_trail (...) VALUES (...)`;
});

Either both succeed or both fail. The SDK's batchSetSettings uses this pattern.

Read replicas (not currently used)

postgres-js supports a replica pool for reads. Olympus's scale doesn't currently warrant it, the olympus database is small. If you need to scale reads:

const db = postgres(masterUrl, { ... });
const dbRead = postgres(replicaUrl, { ... });

Then route reads through dbRead. Not currently shipped.

Connection-string parsing

DATABASE_URL follows the standard PostgreSQL URL form:

postgres://user:password@host:5432/database?sslmode=verify-full&sslrootcert=/path/to/ca.crt

The SDK passes the URL string through to postgres-js, which handles parsing.

SDK, connection pool

On this page