# Database and migrations

Drizzle manages one Postgres schema through reviewed, journaled SQL migrations.

## Connection ownership [#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 [#change-the-schema]

```sh
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 [#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 [#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](/docs/adr/0009-row-level-security).

```sh
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 [#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.

```sh
# 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 [#verify-against-real-postgres]

```sh
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.

## Key files [#key-files]

- [`packages/db/src/tenancy.ts`](https://github.com/CarlosZiegler/template-cloudflare/blob/main/packages/db/src/tenancy.ts)
- [`packages/db/src/schema/tenancy.ts`](https://github.com/CarlosZiegler/template-cloudflare/blob/main/packages/db/src/schema/tenancy.ts)
- [`packages/db/src/connection.ts`](https://github.com/CarlosZiegler/template-cloudflare/blob/main/packages/db/src/connection.ts)
- [`packages/db/src/job-db.ts`](https://github.com/CarlosZiegler/template-cloudflare/blob/main/packages/db/src/job-db.ts)
- [`packages/db/scripts/apply-pending.mjs`](https://github.com/CarlosZiegler/template-cloudflare/blob/main/packages/db/scripts/apply-pending.mjs)
- [`packages/db/scripts/migration-runner.mjs`](https://github.com/CarlosZiegler/template-cloudflare/blob/main/packages/db/scripts/migration-runner.mjs)
- [`scripts/test-local-postgres.mjs`](https://github.com/CarlosZiegler/template-cloudflare/blob/main/scripts/test-local-postgres.mjs)