Database architecture

PostgreSQL 16 with the pgvector extension. Drizzle ORM in @fitkit/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:

DIRECT_DATABASE_URL

Migrations bypass PgBouncer because long-running ALTER TABLE statements + transaction-pooling don’t mix. 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 on Neon/Supabase 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 (Neon/Supabase/Railway DB)
DIRECT_DATABASE_URL           optional, used by migrations to bypass PgBouncer
DB_POOL_MAX                   default 20
DB_POOL_IDLE_TIMEOUT          default 30000 (ms)
DB_POOL_CONNECT_TIMEOUT       default 5000 (ms)
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