Migrations

The database is declarative: the schema in packages/database/prisma/schema.prisma is the source of truth, and every change reaches Postgres via a versioned migration generated by Prisma.

The loop

1. Edit the schema. Update packages/database/prisma/schema.prisma — add a model, add a field, add an index, etc.
2. Generate the migration. From the repo root:

   pnpm run db:migrate:dev

   (equivalent to npx prisma migrate dev in packages/database). This creates a new directory under packages/database/prisma/migrations/<YYYYMMDDHHmmss>_<snake_case>/ with the SQL.
3. Commit the migration. Both schema.prisma and the generated migration directory must land in the same PR.
4. Regenerate the clients.

   pnpm run db:generate

   This refreshes the Prisma client every app imports through @repo/database.
5. Update consumers in the same PR. If a field was renamed or removed, every consuming app — apps/api, apps/pool, apps/ops, apps/debug — must be updated before the PR can merge. Type errors at build time are the gate.

Non-interactive migration application

In CI and production environments, use:

pnpm run db:migrate

That maps to prisma migrate deploy under the hood — non-interactive, no schema regeneration prompts.

Naming convention

Prisma generates <UTC timestamp>_<snake_case_description>. Two soft rules apply:

• Keep the snake-case description short and intent-revealing: add_candidate_phone_country_code, drop_application_legacy_status, index_audit_log_target_type.
• Each migration does one logical thing. Splitting a rename into a multi-step migration (add column → backfill → drop old column) is encouraged when the change must be safe under live writes.

The hard rule

Never modify the database manually. No ALTER TABLE in psql, no prisma db push --accept-data-loss, no hot-fix migration files. The migration directory is append-only history; once a migration is committed, the next change is a new migration on top of it.

Renames and removals

When a column is renamed or removed, prefer a sequence of safe migrations over a single destructive one:

1. Add the new column.
2. Backfill old → new (in a separate migration or a one-shot script).
3. Update every consumer to read/write the new column.
4. Drop the old column in a follow-up migration once no consumer references it.

For an example, see the location → (locationCountryCode, locationState, locationCity) transition in CandidateProfile and JobPosting: the legacy location column is still present with a // Legacy free-form location string… comment until the cut-over completes.

Generated client and types

• The generated Prisma client is the only typed surface — apps import Prisma, PrismaClient, model types, and enum types from @repo/database.
• Do not hand-write TS interfaces that duplicate Prisma types.
• After a schema change, run pnpm run db:generate so downstream check-types sees the new shape.