ADR 008: Database Platform & Strategy

Context

We need to select a database platform that must support:

• Strong transactional guarantees (ACID)
• Extensions for geospatial, vector search, and specialized metadata
• A clear path to enterprise security controls (HSMs, KMS, private networking)
• The ability to migrate to regulated cloud or on-premises environments
• Zero application-layer vendor lock-in
• Support for both development (rapid iteration) and production (security-focused) deployments
• Compatibility with our portability goals (Fastify + Drizzle stack)

Considered Options

Option A – PostgreSQL + Supabase (Initial) + Drizzle ORM (Chosen)

PostgreSQL database with Supabase as initial managed provider, using Drizzle ORM for abstraction.

Pros

• Portability: Standard SQL, works with any PostgreSQL host
• ACID transactions: Full support for transactional integrity
• Initial setup: Fast setup with Supabase for rapid iteration
• Production ready: Yes, suitable for production workloads
• Enterprise support: Available through Supabase and migration to cloud providers
• Migration path: Easy - only requires changing DATABASE_URL
• Zero vendor lock-in: Standard PostgreSQL, no proprietary APIs
• Security flexibility: Can migrate to environments with KMS, VPC, Cloud HSM
• Supabase benefits: Managed Postgres with backups, monitoring, branching for preview environments
• Local development: Supabase CLI for local development
• Postgres extensions: Support for PostGIS, vector, crypto, full-text search

Cons

• Slightly more setup than fully proprietary stacks
• Requires managing migrations and schema explicitly

Option B – PostgreSQL + Cloud Managed (RDS/Cloud SQL)

PostgreSQL database with AWS RDS or Google Cloud SQL as managed provider.

Pros

• Portability: Standard SQL
• ACID transactions: Full support
• Production ready: Yes
• Enterprise support: Available
• Migration path: Change DATABASE_URL
• Security controls: Native KMS, VPC isolation, IAM/Cloud IAM

Cons

• Initial setup: More configuration required than Supabase
• Less rapid iteration for development (no branching, more setup)

Option C – PostgreSQL + Self-hosted

Self-hosted PostgreSQL database.

Pros

• Portability: Standard SQL
• ACID transactions: Full support
• Production ready: Yes
• Enterprise support: Available
• Full control: Complete control over infrastructure

Cons

• Initial setup: Complex setup and maintenance
• Requires database administration expertise
• More operational overhead

Option D – Embedded Database (PGlite)

PostgreSQL embedded in the runtime.

Pros

• Initial setup: Very fast
• ACID transactions: Full support
• Good for local development and testing

Cons

• Production ready: No - not suitable for production
• Enterprise support: Limited
• Migration path: Rewrite needed for production
• Not suitable for concurrent workloads or multi-instance deployments

Option E – Other Databases (MongoDB, MySQL, etc.)

Alternative database engines.

Pros

• Some have good ecosystem support

Cons

• Portability: Vendor-specific, not standard SQL
• ACID transactions: Varies by database
• Migration path: Rewrite needed to switch
• Less compatible with our PostgreSQL-focused stack

Decision

We will use PostgreSQL as the single database engine with Supabase as the initial managed Postgres provider, using Drizzle ORM as the database abstraction.

TLDR: Comparison Table

Main reasons:

• PostgreSQL provides ACID guarantees, mature extensions, and enterprise acceptance
• Supabase accelerates initial development with managed Postgres, branching, and local development
• Drizzle ORM ensures zero vendor lock-in by generating plain SQL with no proprietary runtime
• Portability: Migration requires only changing DATABASE_URL - no code changes
• Clear migration path: Adopter may point DATABASE_URL at another Postgres host. Basilic ships Vercel + Postgres; GCP/AWS are not a Basilic destination.
• Complements Fastify: Both stay portable; Basilic’s shipped path is Vercel.

Migration Strategy

Database migrations follow a host-agnostic strategy that works across all deployment platforms (Docker, Railway, Fly.io, Render, localhost, etc.).

Migration Execution

PostgreSQL (Production):

• Migrations run at build time via pnpm db:migrate (Vercel: after turbo run build, skipped when VERCEL_ENV=preview unless RUN_PG_MIGRATE=true)
• Concurrent migrators serialize with a PostgreSQL advisory lock
• Migrations are skipped at runtime (already applied at build time)
• Faster startup (migrations already applied)
• Fails fast if migrations have issues
• Works with any deployment platform

PGLite (Preview/Development):

• Migrations skip at build time, run at runtime when instance is created
• PGLite instance doesn't exist at build time
• Migrations run during app initialization via runMigrations()

Environment Configuration

The system uses a PGLITE environment variable to control database selection:

• PGLITE=true: Uses in-memory PGLite database (useful for preview branches, local dev without PostgreSQL)
• PGLITE=false (default): Uses PostgreSQL via DATABASE_URL
• When PGLITE=true, DATABASE_URL is optional and defaults to PGLite URL
• When PGLITE=false, DATABASE_URL is required

Migration Flow

Deployment Platform Examples

All platforms use the same build command pattern:

• Docker: RUN pnpm build (OpenAPI + tsc; run pnpm db:migrate as a separate migrate phase)
• Railway/Fly.io/Render: build: pnpm build then a migrate command — not bundled into tsc
• Local dev (PostgreSQL): Migrations run via pnpm db:migrate, not when you run pnpm build / pnpm --filter @repo/api build alone. To wipe local Supabase and re-apply schema + seed, use pnpm reset (repo root) or pnpm reset in apps/api. PGLite: SQL assets must be present next to compiled migrate.js; empty list is a hard error when PGLITE=true or NODE_ENV=test

Local database reset (Supabase + Drizzle)

For local Postgres via Supabase CLI (project_id: basilic-api in apps/api/supabase/config.toml), use pnpm reset from the repo root (runs pnpm --filter @repo/api reset) or pnpm reset from apps/api. One command does:

1. supabase db reset — Drops and recreates the database. [db.seed] is off; no seed.sql.
2. Drizzle migrations — scripts/migrate.ts via pnpm db:migrate with RUN_PG_MIGRATE=true and the local Supabase DATABASE_URL.
3. Data seed — scripts/seed.ts (runSeed). Add idempotent inserts as needed. Runs only as part of pnpm reset, not on pnpm db:migrate or pnpm build alone. See apps/api/README.md.

Run pnpm db:stop before switching to another project's Supabase instance (e.g. different repo); only one Supabase runs per host.

Key Benefits

• Host-agnostic: No platform-specific code, standard npm scripts work everywhere
• Separate PostgreSQL migrations: run pnpm db:migrate as its own step (Docker migrate phase, Railway/Fly.io/Render, local). Not part of pnpm build / tsc
• Runtime migrations: PGLite migrations run when instance is created
• Flexible: Supports both PostgreSQL and PGLite based on environment configuration
• Test-friendly: Tests always use PGLite via NODE_ENV=test

Notes

• Strategic approach: Basilic ships Vercel + Postgres (DATABASE_URL). Other hosts (Supabase, GCP, AWS, on-prem) remain an adopter choice via the connection string, not a Basilic destination.
• Supabase used as "Postgres as a service", not as platform dependency
• No Supabase SDKs, auth, storage, or proprietary APIs used - only standard PostgreSQL connection
• PGlite suitable for local dev/testing and preview branches, not production
• Migration path designed for: Development (Supabase) → Production (GCP Cloud SQL / AWS RDS)
• Both Fastify and Drizzle chosen specifically to avoid vendor lock-in and enable this migration
• Security & Enterprise Portability: Architecture allows migration to:
  • AWS: RDS/Aurora PostgreSQL, KMS encryption, VPC isolation, IAM-based access control
  • Google Cloud: Cloud SQL, CMEK/Cloud KMS, VPC-SC, Private Service Connect
  • Private/On-prem: Self-hosted Postgres, Hardware Security Modules, air-gapped environments
• Because SQL is portable, migrations are explicit, no vendor SDKs are used, and no platform-coupled APIs exist

Related Documentation

• API Development - PostgreSQL and Drizzle ORM integration
• ADR 007: Backend ORM - Drizzle ORM selection decision
• Portability Strategy - Database migration paths and portability strategy
• Installation - Database configuration and connection setup