Database and migrations

Connection ownership

createDb uses node-postgres and Drizzle relations. Workerd resolves the Hyperdrive binding; Node uses DATABASE_URL. Disable Hyperdrive query caching so a recently changed Session, membership or record is not answered from stale query results. Scheduled and streaming work use explicitly owned bounded pools.

Change the schema

pnpm db:generate
# Review the generated SQL and snapshot before applying it.
pnpm db:migrate
# CI and explicit automation use the same journaled runner:
pnpm --filter @template-cloudflare/db db:apply-pending

The migration scripts read apps/web/.env unless a named environment variable is supplied. They compare the migration history and apply pending SQL. Never use db:push; the script deliberately refuses it. Do not edit a migration that has already been applied.

An existing database without matching history requires an explicit schema comparison before baseline. --baseline <migration-folder> is an operator decision after that comparison, not a generic repair for a failed migration.

Domain and contracts

Organization-owned tables carry organizationId. Auth tables are owned by Better Auth. Domain enum files are client-safe leaves; Drizzle table definitions are server-only. Add relations to the sanctioned schema aggregate only when the relation builder needs them.

Use a transaction for changes that must commit together, including domain writes with outbox/audit records. A retry after an uncertain response needs an idempotency key or a durable result; blindly replaying a write is unsafe.

Tenant isolation

Every table has row-level security. Procedures, assistant and MCP tools and the files gateway receive context.db as the app_tenant role for the session's User and Active Organization; each statement runs in a short transaction with app.user_id and app.org_id set locally, and app.org_id() re-checks membership on every statement. Better Auth, the cron tick, billing webhooks and admin run as the connection role through asSystem(db, reason, fn), which is listed in packages/db/src/system-call-sites.txt and checked by pnpm lint.

A new table uses pgTable.withRLS with tenantPolicy(...) helpers from schema/tenancy.ts, plus a GRANT to app_tenant in a custom migration. rls-catalog.test.ts fails for a table without RLS, a grant without a policy, a policy for another role, or FORCE ROW LEVEL SECURITY (which would hide every row from a connection role without BYPASSRLS). PostgreSQL 16 or newer is required. The decision, the supported connection roles and the rejected alternatives are in ADR-0009.

pnpm --filter @template-cloudflare/db test:rls-matrix

The matrix starts a Docker PostgreSQL 16 and 18 for each connection role (superuser, owner with BYPASSRLS, plain owner, owner without CREATEROLE), applies migrations and runs the catalog and isolation suites.

Restore or switch providers

The migration journal travels with a dump, but roles do not, and pg_restore --no-acl drops grants (and every policy when app_tenant is missing on the target). db:apply-pending notices: it ends with db:verify-rls, repairs the idempotent part (role, app.session_roles, functions, grants, missing policies) and fails with the steps to take when it cannot.

# On the target, if the connection role cannot create roles, an administrator runs:
#   CREATE ROLE app_tenant NOLOGIN NOINHERIT NOBYPASSRLS;
#   GRANT app_tenant TO <connection role> WITH INHERIT FALSE, SET TRUE;
pg_restore --no-owner --no-acl -d "$TARGET_URL" dump.fc
TARGET_URL="$TARGET_URL" pnpm --filter @template-cloudflare/db db:apply-pending TARGET_URL
TARGET_URL="$TARGET_URL" pnpm --filter @template-cloudflare/db db:verify-rls TARGET_URL

If the Worker connects with a different role than the one that ran the migrations, add it to app.session_roles as the migration role; until then tenant requests see no rows.

Verify against real Postgres

node scripts/test-local-postgres.mjs exec pnpm test:integration

The wrapper creates a disposable local cluster, applies migrations twice and stops its own database. It connects as a plain table owner (TEMPLATE_TEST_DB_PROFILE=superuser|bypassrls|owner picks another) and keeps tests away from any hosted database. Use additive migrations for deploys so rolling application code back does not require destructive schema rollback.