Golden path: add a database migration

When to use

Use this path for a durable Postgres schema, constraint, index, enum, or data-shape change required by application behavior. Do not create a migration for an application-only refactor.

Before the first commercial release tag, the single baseline may be corrected only under docs/decisions/0002-pre-release-better-auth-schema-baseline.md. After that tag, shipped files are immutable and every change uses a new timestamped dbmate migration.

Files and boundaries

apps/server/migrations/*.sql: dbmate-formatted schema history and the only migration authority.
apps/server/src/domains/*/repository.ts: application SQL that consumes the schema.
apps/server/src/domains/*/*.integration.test.ts: real Postgres behavior.
packages/contracts: wire-shape changes only; database rows are not public contracts by default.
.github/workflows/ci.yml: full-chain reversal and DB-enabled verification.
docs/decisions: required when the change establishes a lasting support or upgrade policy.

Better Auth CLI output may be inspected to discover required columns, but it must be translated into a reviewed dbmate migration. Routes do not contain SQL, and migrations do not hide application backfills in startup code.

Procedure

Read the applicable AGENTS.md, the current schema, repository queries, integration tests, and recent migration history. Confirm whether the first commercial release has been tagged.
From apps/server, create a new file after release:
```
pnpm db:new add_resource_owner
```
Write both -- migrate:up and -- migrate:down. Prefer transactional DDL. Put foreign keys, uniqueness, checks, and ownership rules in the database when they are invariants.
For a data migration, define the affected row count, batching/restart behavior, lock impact, and compatibility window. Separate a large backfill from a blocking constraint when needed.
Update repository queries, types, contracts, and tests in the same change. Keep old and new application versions compatible during a rolling deployment, or state why the supported one-replica deployment makes an ordered stop/migrate/start safe.

Start Postgres and create a disposable, explicitly named proof database:

docker compose up -d postgres
dropdb --if-exists --force app_migration_proof
createdb app_migration_proof

Prove the complete fresh chain, the new down section, and reapplication:

DATABASE_URL='postgres://localhost:5432/app_migration_proof?sslmode=disable' \
  dbmate --migrations-dir apps/server/migrations --no-dump-schema up
DATABASE_URL='postgres://localhost:5432/app_migration_proof?sslmode=disable' \
  dbmate --migrations-dir apps/server/migrations --no-dump-schema down
DATABASE_URL='postgres://localhost:5432/app_migration_proof?sslmode=disable' \
  dbmate --migrations-dir apps/server/migrations --no-dump-schema up

One down reverses one migration. CI loops over the migration count when it verifies an entire multi-file history.

Assert the resulting columns, constraints, indexes, and absence of removed objects with psql or an integration test. Do not treat dbmate exit zero as proof that the application can use the result.
Run the DB-enabled server suite and repository-wide verification.
Remove the exact disposable database when finished:
```
dropdb --if-exists --force app_migration_proof
```

Verification

vp check
vp run --filter @app/server typecheck
RUN_DB_INTEGRATION_TESTS=1 \
  DATABASE_URL='postgres://localhost:5432/app_migration_proof?sslmode=disable' \
  bun test --cwd apps/server
vp run -r test
vp run -r build

For buyer-facing changes, also generate a custom-scope/custom-brand application and repeat frozen install, migration up → down → up, DB tests, and its compiled-server smoke from the generated repository root.

Security constraints

Never paste production URLs, credentials, customer rows, tokens, or database dumps into commands, tests, documentation, logs, or issue comments.
Use a disposable database whose exact name you control. Never point down, dropdb, destructive fixtures, or manual repair SQL at a shared or production database.
Review foreign-key delete behavior, uniqueness under concurrency, tenant/resource ownership, defaults, nullability, and index/lock cost.
Backups and a tested restore path are prerequisites for destructive production DDL; a down section is not a backup.

Failure modes

The migration works on an existing developer database but fails from empty because it relies on untracked manual schema.
down succeeds syntactically but loses data or cannot restore the old application contract.
A new non-null column fails on existing rows or creates a long table rewrite/lock.
Application queries deploy before the required schema or stop working after rollback.
A migration version is edited after release, so buyer databases with the same recorded version have different schemas.
Better Auth or another tool applies an untracked second migration history.

Rollback and diagnosis

If verification fails, stop and follow Diagnose or recover a failed migration. Determine whether the failure is connection, lock, transactional DDL, or application compatibility before changing SQL. Use down only when its data and compatibility effects are understood; otherwise ship a forward repair.

Acceptance criteria

A new post-release timestamped migration exists, or the recorded pre-release baseline exception applies.
Fresh up, one down, and re-up pass on an exact disposable database.
Schema assertions and DB integration tests prove the application invariant.
Old/new application compatibility and rollback behavior are explicit.
Root checks, tests, builds, and generated-buyer verification pass.
No secret, production URL, customer data, or untracked manual schema is introduced.

Agent prohibitions

Do not edit or reorder a migration included in a commercial release.
Do not delete or forge schema_migrations rows to make a deployment look green.
Do not run destructive verification against an ambient DATABASE_URL.
Do not add schema mutation to application startup outside the reviewed migration runner.
Do not use the Better Auth CLI or an ORM as a second schema authority.
Do not mark the path complete from SQL review alone.

Add a database migration