Database architecture

PostgreSQL 16 with the pgvector extension. Drizzle ORM in @taikan/db (libs/db). Single physical database, single schema. Multi-tenancy via organization_id on every domain table.

Quick reference

Schema organization

Each domain has its own file in libs/db/src/lib/schema/. All are re-exported from index.ts. Cross-file foreign keys are explicit imports.

Multi-tenant isolation

Every domain table carries organization_id uuid NOT NULL REFERENCES organizations(id) with an index. Some tables intentionally don’t — e.g. users (global identity), aiCache (cross-org dedupe), legalDocuments (platform-wide versioned docs).

Services always include organization_id in WHERE clauses. There’s no row-level security at the Postgres layer — isolation is enforced in application code. Tests assert this.

Connection pool

apps/api/src/database/database.module.ts builds a pg Pool with knobs read from env.

Read this before touching any of them. Production PGBouncer runs PGBOUNCER_POOL_MODE=session, so every pooled client connection pins a Postgres backend for its whole lifetime. An idle connection in the app’s pool is not free — it is a server connection no other process can have. The binding limit is therefore PGBOUNCER_DEFAULT_POOL_SIZE (60, shared by every process on this user+database), not PGBOUNCER_MAX_CLIENT_CONN (120) and not Postgres max_connections (500, which has plenty of room). The sizing rule is:

processes x DB_POOL_MAX x 2  <=  PGBOUNCER_DEFAULT_POOL_SIZE
                          ^-- a rolling deploy runs old and new containers together

Today that is 3 x 8 x 2 = 48 against 60 — it fits, with 12 spare for the preDeployCommand migration job and ad-hoc clients. It did not fit until 2026-09-03, when the pool size was raised from 20 after eager warming exhausted it and failed a deploy (see below). Re-check this line whenever a replica is added or DB_POOL_MAX is raised — 4 replicas at 8 would need 80.

Why not transaction pooling

Transaction mode would make idle app connections free and delete this whole class of problem. It is not a config flip: the session-scoped advisory locks in apps/api/src/common/advisory-lock.ts and subscriptions/plan-change.service.ts take pg_try_advisory_lock and release it in a later statement. Under transaction pooling those two statements can land on different backends, so the lock would never be released — it would leak silently until PGBouncer recycled the connection. Moving them to pg_advisory_xact_lock (as platform-billing.service.ts already does) is the prerequisite.

Raising PGBOUNCER_DEFAULT_POOL_SIZE is the cheap alternative, and is what was done on 2026-09-03 (20 → 60): Postgres max_connections is 500, so the pooler’s cap has no database constraint behind it. That buys headroom, not correctness — under session pooling the pool size still has to track processes x DB_POOL_MAX x 2, so it is a number to revisit on every replica or DB_POOL_MAX change rather than a fix.

DIRECT_DATABASE_URL

Migrations bypass PgBouncer so a long ALTER TABLE neither occupies one of the 20 pooled server connections for its whole duration nor competes with the app for them — which matters because preDeployCommand = "pnpm db:migrate" runs while the outgoing containers still hold theirs. (An earlier version of this line blamed transaction pooling; the pooler is in session mode, so that was never the reason.) libs/db/drizzle.config.ts reads DIRECT_DATABASE_URL when present and falls back to DATABASE_URL. Set DIRECT_DATABASE_URL to the unpooled connection before running pnpm db:migrate.

pgvector

Custom Drizzle column type at libs/db/src/lib/schema/_pgvector.ts. Defaults to 1024 dimensions (Voyage v3-multilingual).

Tables using vector columns:

• exercises — exercise embeddings for semantic search.
• workouts — workout-level embeddings for similar-workout discovery.
• programs — program embeddings.
• member_profiles — member-profile embeddings used by Spotter RAG.
• agent.aiConversations and related agent tables — embeddings for retrieval.

The pgvector extension is preinstalled in the pgvector/pgvector:pg16 Docker image used in docker-compose.yml and docker-compose.test.yml. Migration 0037 runs CREATE EXTENSION vector. Stock postgres:16 will fail this migration.

Encryption at rest (application-level)

Two AES-256-GCM keys, both 32-byte hex:

Generate keys with node -e "console.log(require('crypto').randomBytes(32).toString('hex'))". Never commit. Dev defaults to a 64-zero string in Makefile and .env.example; prod must override.

Soft delete

Convention: deleted_at timestamptz on tables that need it, with reads filtered via WHERE deleted_at IS NULL. Used today on:

• users (user removed themselves)
• organizations (org closed)
• progressPhotos
• announcements
• paymentProviderConfigs and paymentTransactions (history-preserving)
• program_templates

Hard delete is reserved for transient records (e.g. unverified invitations).

Migration policy

Source of truth for this section: CLAUDE.md “Database Policy”. Keep both in sync.

Workflow

1. Edit the schema file under libs/db/src/lib/schema/.
2. pnpm db:generate — Drizzle-kit diffs the schema, writes a numbered migration to libs/db/drizzle/ and updates libs/db/drizzle/meta/_journal.json.
3. Review the generated SQL. Catch destructive renames, missing indexes, unintended type changes.
4. pnpm db:migrate — applies pending migrations. Requires explicit user approval every time. Dev and prod both hold real data.

Never use pnpm db:push (auto-apply without migration files). It bypasses the journal and creates drift.

The monotonic when rule

Drizzle-kit’s migrator filters journal entries by when > max(__drizzle_migrations.created_at). Any entry whose when is smaller than the last applied migration’s is silently skipped — the CLI reports “migrations applied successfully” with nothing actually run.

This caused a production outage on 2026-04-18 when a branch merged with a journal entry timestamped before main’s own newer migration.

Rule: when merging a branch that added migrations before main got its own, regenerate the stale ones so their when bumps. Verify by reading _journal.json after merge — the when field on each entry must be strictly monotonic.

Direct-write conventions

• Use db.transaction(async (tx) => { ... }) whenever a logical change spans multiple tables.
• Prefer Drizzle’s relational query builder (db.query.users.findFirst({ with: { ... } })) over hand-built joins.
• For bulk operations use db.execute(sql\…`)` with parameterized templates — never string-interpolate user input.
• Log slow queries — the Sentry beforeSendTransaction in apps/api/src/instrument.ts tags transactions with slow_db: true when any db span exceeds 1s.

Drizzle Studio

pnpm db:studio

Web GUI for browsing + editing data. Defaults to https://local.drizzle.studio. Reads DATABASE_URL from the api app’s .env. Useful for ad-hoc data inspection — avoid using it to modify prod data (use a SQL session and an audit-logged path instead).

Reference: env vars touching the DB

DATABASE_URL                  required, prod points at managed Postgres (Railway)
DIRECT_DATABASE_URL           optional, used by migrations to bypass PgBouncer
DB_POOL_MAX                   default 20
DB_POOL_IDLE_TIMEOUT          default 600000 (ms)
DB_POOL_CONNECT_TIMEOUT       default 15000 (ms)
DB_POOL_WARM                  default 0 (off; session pooling — see sizing rule)
DB_POOL_KEEPALIVE             default true
DB_POOL_KEEPALIVE_INITIAL_DELAY  default 10000 (ms)
DB_LOGGING                    default false; true dumps SQL
PAYMENT_CREDENTIALS_ENCRYPTION_KEY  32-byte hex, AES-256-GCM
NATIONAL_ID_ENCRYPTION_KEY    32-byte hex, AES-256-GCM