# Runbook: Migrate raw `pg.Pool` to `@oshun/database`

Owner: platform team Last reviewed: 2026-05-28

The audit of v1 identified ~30 services that instantiate `new Pool` /
`new pg.Pool` directly from the `pg` package instead of going through the
`@oshun/database` wrapper. This bypass costs us:

- **Health checks** — `@oshun/database` exposes `checkPostgresHealth` so
  `/health` endpoints land in a uniform shape across services. Direct `pg.Pool`
  users have to hand-roll the check (or skip it).
- **Connection-string normalization** — `parsePostgresConnectionString`
  - `maskPostgresConnectionString` in `@oshun/database` strip credentials before
    logs hit cloud-aggregated stdout. Raw pg users often log the unredacted DSN
    by accident.
- **Pool metrics** — `instrumentPostgresClient` ships acquire / wait / release
  timings to the metrics sink. Raw pools have no such hook.
- **Pluggable pool implementation** — when the platform switches pgbouncer →
  pg-native or adds the read-replica router, the `@oshun/database` boundary is
  the only place we have to change. Raw pools need each service to do its own
  swap.

This runbook documents the migration path. As of writing, ~30 sites still need
to move; the list is at the bottom.

## Pattern

### Before

```ts
import pg from 'pg';

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

await pool.query('SELECT 1');
```

### After

```ts
import { createPostgresClient } from '@oshun/database';

const client = createPostgresClient({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000,
});

await client.query('SELECT 1');
```

The `client` object exposes the same `query`, `connect`, and `end` methods as a
`pg.Pool`, so most call sites only change the constructor. If you depend on
`pool.totalCount` / `pool.idleCount` for ad-hoc metrics, use `client.stats()`
instead — it returns a fully-typed `PostgresPoolStats` object covering the same
fields plus acquire-wait p50/p95.

### Connection from env

If the service reads `DATABASE_URL` and applies no overrides,
`createPostgresClientFromEnv()` does the right thing:

```ts
import { createPostgresClientFromEnv } from '@oshun/database';

const client = createPostgresClientFromEnv(); // reads DATABASE_URL
```

### Transactions

```ts
import { withTransaction } from '@oshun/database';

await withTransaction(client, async (tx) => {
  await tx.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [
    amount,
    fromId,
  ]);
  await tx.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [
    amount,
    toId,
  ]);
});
```

`withReadOnlyTransaction` and `withSerializableTransaction` give you the
read-only / `SERIALIZABLE` isolation variants without each service
re-implementing the BEGIN/COMMIT/ROLLBACK boilerplate.

### Health check

```ts
import { checkPostgresHealth } from '@oshun/database';

const health = await checkPostgresHealth(client);
// → { ok: true, latencyMs: 4, poolStats: { ... } }
```

Wire this into the service's `/health` route so the response shape matches every
other Oshun service.

## Migration checklist (per service)

1. Add `"@oshun/database": "workspace:*"` to the service's `package.json`
   dependencies (most already have it transitively; confirm with
   `pnpm why @oshun/database` in the service dir).
2. Find the `new pg.Pool(...)` site:
   ```bash
   grep -rn "new pg\\.Pool\\|new Pool(" apps/<service>/src
   ```
3. Replace with `createPostgresClient(...)` (or `createPostgresClientFromEnv()`
   if applicable).
4. Remove the `import pg from 'pg'` / `import { Pool } from 'pg'` line if it's
   no longer used.
5. Adjust any `pool.totalCount` / `pool.idleCount` reads to `client.stats()`.
6. Wire `/health` through `checkPostgresHealth` if the service has a health
   endpoint that doesn't already use it.
7. Run the service's tests; verify nothing breaks. Watch for type errors on
   `pool.connect()` callbacks — `client.connect()` returns `Promise<PoolClient>`
   (same shape) but the type is exported from `@oshun/database` rather than
   `pg`.

## Tracked sites (as of 2026-05-28)

### Migrated (18 / 30)

- apps/arete/api/src/plugins/database.ts
- apps/demeter/api/src/plugins/database.ts
- apps/hestia/api/src/plugins/database.ts
- apps/lilith/svc-consent-management/src/app.ts
- apps/lilith/svc-settlement/src/storage/postgres-store.ts
- apps/lilith/svc-catalog/src/app.ts
- apps/nyx/api/src/middleware/api-key-auth.ts
- apps/nyx/pipelines/src/event-calculator.ts
- apps/nyx/pipelines/src/catalog-syncer.ts
- apps/nyx/pipelines/src/tle-updater.ts
- apps/nyx/pipelines/src/ephemeris-generator.ts
- apps/oshun/bff/scripts/backfill-stubs-to-canonical.ts
- apps/oshun/bff/src/routes/domain-stubs-postgres.ts
- apps/veritas/ai-workers/src/workers/base/pool.ts
- apps/veritas/api/src/infrastructure/postgres/createPool.ts
- apps/veritas/ingestion/src/db/pool.ts
- apps/yemaya/workers/src/workers/notification-worker.ts
- libs/yemaya/assets/src/database/lfs-quota-connection.ts

### Still to migrate (12 / 30) — each blocked on a specific concern

Multi-pool sites (one wrapper, multiple distinct pg.Pool instances — needs
per-pool care to wire the right config to each pool):

- apps/veritas/video/src/main.ts (4 pools)
- apps/veritas/audio/src/main.ts (4 pools)
- libs/saraswati/db/src/connection.ts (2 pools: primary + telemetry)
- libs/lakshmi/db/src/connection.ts (3 pools: primary + read replica + ingest)
- libs/lakshmi/db/src/migrations.ts (2 pools)

Nx layer-boundary blocked (the lib is tagged `layer:infra` which prevents
importing `@oshun/database`; needs lilith owners to retag or move the consumer):

- libs/lilith/common/audit-logger.ts
- libs/lilith/service-lib/database.ts

Pre-existing lint debt that surfaces when the file is touched (each needs the
file owner to clean up the unrelated warnings before the migration can land):

- libs/iris/memory/persistence/src/postgres-store.ts

Already-using a different abstraction (libs/asase/migrations uses its own
connection-pool wrapper; libs/cybele/db builds config but delegates
instantiation to the caller; libs/saraswati's telemetry pool sits in a
sub-module — needs per-lib decisions):

- libs/asase/migrations/src/connection-pool.ts
- libs/cybele/db/src/connection.ts

**Exempt from this migration:**

- `libs/shared/database/src/legacy-pool.ts` — the @oshun/database internal that
  exposes a raw-pool escape hatch for one-off scripts.
- `libs/shared/database/src/postgres-client.ts` — the @oshun/database internal
  that wraps pg.Pool. The whole point of the wrapper.
- Anything under `test/` or `__mocks__/` — test fixtures often use raw pg
  directly so they can poke at internals the wrapper hides.

## Enforcement

The pre-commit `stub-indicator-scan` does not currently flag raw `pg.Pool`
instantiations. A follow-up will add an ESLint rule (`@oshun/no-raw-pg-pool`)
that fails CI for any new `new Pool` from `'pg'` outside the exempt set above.
Until that lands, the audit / gaps doc remains the source of truth.