# 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 @doro/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.

## 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 keeps tests away from a production Neon URL. Use additive migrations for deploys so rolling application code back does not require destructive schema rollback.


## Key files [#key-files]

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