Database

SaaS Starter uses Drizzle ORM on PostgreSQL 16 with the pgvector extension for AI embeddings.

Migrations

# Generate migration from schema changes
pnpm --filter "@app/db" run generate

# Apply pending migrations
pnpm --filter "@app/db" run migrate

# Seed initial data
pnpm --filter "@app/db" run seed

Journal (migrations/meta/_journal.json)

Drizzle’s migrator uses packages/db/migrations/meta/_journal.json together with the ordered SQL files under packages/db/migrations/. Each journal entry’s tag must match the migration folder/file prefix (e.g. 0008_saas_metrics_daily).

Operator recovery (partial or out-of-band applies)

If a database was migrated manually (SQL pasted in psql) without updating Drizzle’s __drizzle_migrations table, the migrator may try to re-apply a file and fail (e.g. “relation already exists”). Safe recovery:

1. Identify which migration files truly ran against that environment.
2. If the schema already matches a pending file, insert the matching row into __drizzle_migrations only after verifying checksums against a staging clone, or restore from backup and re-run pnpm --filter @app/db run migrate on a clean database.
3. Prefer never editing applied SQL in place; add a forward fix-up migration instead.

When in doubt, restore from snapshot and run migrations on an empty database—faster than reconciling drift in production.

Schema overview

All tables are in packages/db/src/schema/. Import anywhere:

import { organization, subscription, aiUsage, documentChunk } from '@app/db'

Core tables

| Table | Description | |---|---| | user | Better Auth users | | session | Auth sessions | | organization | Tenant organizations | | member | Org membership + roles | | subscription | Billing subscriptions | | ai_usage | Per-org AI token usage | | org_credits | AI credit balance |

Feature tables

| Table | Description | |---|---| | document_chunk | pgvector embeddings (RAG) | | document_source | RAG document sources | | referrals | Referral tracking | | waitlist_entries | Email waitlist | | roadmap_items | Feature requests | | roadmap_votes | User votes | | webhook_endpoints | Outbound webhook targets | | webhook_deliveries | Delivery log | | announcements | In-app announcements | | feature_flags | Org-level feature flags | | audit_log | Security audit trail |

pgvector setup

SaaS Starter uses pgvector for semantic search. Enable it once:

CREATE EXTENSION IF NOT EXISTS vector;

The document_chunk.embedding column stores 1536-dimensional vectors (OpenAI text-embedding-3-small).

Connection pooling

Production uses pg-pool with the following defaults (configurable via env):

DATABASE_POOL_MIN=2
DATABASE_POOL_MAX=10
DATABASE_POOL_IDLE_TIMEOUT=30000