Database providers

The app talks to its database only through the Database port (packages/ports/src/database.ts). The composition root (api/src/composition-root.ts) picks the concrete adapter from one env var, so the backend is a configuration choice — no domain or route code changes.

// api/src/env.ts
DATABASE_PROVIDER?: 'neon' | 'supabase' | 'd1';   // unset ⇒ 'neon' (Postgres)

Provider	Engine	Adapter	Tenant isolation	Use for
neon (default)	Postgres	makePostgresDatabase (neon.ts)	RLS (default-deny + FORCE)	production, staging
supabase	Postgres	makePostgresDatabase (same adapter)	RLS (default-deny + FORCE)	production, staging
d1	Cloudflare D1 (SQLite)	makeD1Database (d1.ts)	app-level only — no RLS	dev/test seam only

neon and supabase are the same code path — Supabase is plain Postgres, so the postgres.js adapter is reused verbatim and the connection string is the only difference. The flag exists for clarity and so docs/ops can reason about the target.

---

Neon (default)

The current production path. Reached via the Hyperdrive binding in the Worker, or DATABASE_URL locally. See Cloudflare topology and DEPLOYMENT.md §3. Nothing to set: unset DATABASE_PROVIDER is neon.

---

Supabase

Supabase is Postgres, so it runs on the existing adapter with full RLS. Setup is a connection-string swap plus the standard migration run.

1. Create the project and grab two connection strings from Project Settings → Database:
   • the pooler string (Supavisor, transaction mode, port 6543) — for the runtime Worker, and
   • the direct string (port 5432) — for migrations (DDL + role management need a direct, non-pooled connection).
2. Apply migrations against the direct connection — this also creates the app_tenant / app_admin roles and the RLS policies (migration 0002):

   DATABASE_URL="postgresql://postgres:…@db.<ref>.supabase.co:5432/postgres?sslmode=require" \
     npm run db:migrate --workspace db

3. Prove isolation (the release gate, provider-agnostic):

   DATABASE_URL="…:5432/…?sslmode=require" npm run db:verify-rls --workspace db

4. Wire the runtime: set DATABASE_PROVIDER=supabase and point the Worker at the pooler string — in production via a Hyperdrive config created over it, or locally via DATABASE_URL in api/.dev.vars. The adapter already uses prepare: false + fetch_types: false, which is required for Supavisor/PgBouncer transaction-mode pooling.

:::note Role membership SET LOCAL ROLE app_tenant (the RLS chokepoint) requires the runtime DB role to be a member of app_tenant. Migration 0002 grants this to whatever role runs the migrations (current_user), so run migrations and the runtime as the same role (Supabase's postgres). If you use a different runtime role and db:verify-rls fails on the role switch, grant it explicitly: GRANT app_tenant, app_admin TO <runtime_role>; :::

That's the whole change — no adapter or schema differences from Neon. Migrations are forward-only Postgres DDL and run identically.

---

Cloudflare D1 (SQLite) — dev/test seam

DATABASE_PROVIDER=d1 selects makeD1Database (packages/adapters/src/d1.ts). It is a first-class, config-selectable seam — not a working production backend. Two hard reasons:

1. No row-level security. SQLite has no RLS. Tenant isolation could only be enforced in application code (every query filtered by client_account_id), losing the database-level default-deny + FORCE net that is non-negotiable in production. Never point a real tenant deployment at D1.
2. A different SQL dialect. The Postgres adapter carries ~675 hand-written Postgres-dialect statements (jsonb, ::text::jsonb, to_tsvector/@@ full-text search, for update skip locked, lateral, window functions, current_setting(...) RLS reads). None run unchanged on SQLite, so a working D1 backend needs a parallel SQLite query + migration set — a multi-week effort and a permanent second dialect to maintain, which is why it is deliberately deferred.

What the seam gives you today: a real ping() (so wiring, /health/ready, and provider selection work end-to-end against a local D1) and a conformant Database shape. Every un-ported query path throws D1NotImplementedError with the method path in the message — it fails loudly, never with silent wrong or zero rows. Also note the Clerk identity adapter still uses Postgres for org→client_account resolution and API-key checks, independent of this port.

# api/wrangler.toml — uncomment + fill, then set DATABASE_PROVIDER = "d1"
# [[d1_databases]]
# binding = "DB"
# database_name = "voc-dev"
# database_id = "<d1-database-id>"   # wrangler d1 create voc-dev

Want a quick local database today?

Use Postgres — the app fully supports it and you keep RLS + dialect parity:

• Local Docker Postgres or a Neon/Supabase preview branch: point DATABASE_URL at it, run npm run db:migrate, done.
• PGlite (Postgres compiled to WASM, in-process): the genuinely-minimal "no external service" option that preserves RLS/jsonb/FTS, unlike D1. A good candidate if the goal is a zero-dependency local DB.

Growing the D1 seam into a real dev backend

If D1 specifically is wanted for local runs, implement the repository methods you exercise against d1.prepare(...).bind(...).all(), add a SQLite migration set (e.g. db/migrations-sqlite/, applied with wrangler d1 migrations apply), and inject the tenant filter per query. The Postgres → SQLite mapping:

Postgres	SQLite / D1
jsonb, ->/->>/#>>, ::text::jsonb	TEXT + json_extract() / json_set() (JSON1)
RLS (current_setting('app.client_account_id'))	none — add WHERE client_account_id = ? in app code
to_tsvector / websearch_to_tsquery / @@	FTS5 virtual tables (MATCH)
for update skip locked	app-level locking / a claimed_at column + conditional UPDATE
gen_random_uuid()	generate in app code (crypto.randomUUID())
ilike	like (SQLite LIKE is case-insensitive for ASCII)
lateral, percentile_cont, filter (where …)	rewrite as correlated subqueries / CASE aggregates