database-migration.md

Database migration operations

AgoraHub v0.2 uses checked-in Prisma migrations. Production startup runs prisma migrate deploy and exits if a migration fails. Never use db push on a production database.

Normal upgrade

1. Create and verify a PostgreSQL backup.
2. Set DATABASE_URL to the target database.
3. Run pnpm exec prisma migrate status and confirm that it lists only the reviewed release migrations as pending. Prisma exits non-zero while migrations are pending; any failed or divergent history must be resolved before continuing.
4. Run pnpm exec prisma migrate deploy during the maintenance window.
5. Run ./scripts/verify-migrations.sh, then verify /api/health. This strict check is intentionally post-deploy: it requires no pending migrations and zero drift from prisma/schema.prisma.

The catch-up migration preserves legacy plaintext API keys by hashing each key with SHA-256 and retaining its first 12 characters for display before dropping the plaintext column. Existing keys remain valid.

Adopt migration history for the final v0.1 db push schema

This procedure adopts migration history; it does not perform a schema transition. Use it only for a database that was last synchronized by the final v0.1 container's prisma db push and already matches prisma/schema.prisma exactly. The v0.2 release datamodel is identical to that final v0.1 datamodel; v0.2 only moves its database management from db push to checked-in migrations.

Do not use this procedure for a database that still has a plaintext ApiKey.key column, lacks ApiKey.keyHash/ApiKey.keyPrefix, or has any other drift. Such a database fails the mandatory diff below and must receive a separately reviewed forward migration before v0.2 is deployed.

1. Take a restorable backup and record representative row counts.

2. Confirm that _prisma_migrations is absent or empty.

3. Prove that the live schema already equals the release datamodel. The command must print No difference detected. and exit 0:

   pnpm exec prisma migrate diff \
     --exit-code \
     --from-url "$DATABASE_URL" \
     --to-schema-datamodel prisma/schema.prisma

   Exit 2 means drift: stop without resolving either migration. In particular, never use migrate resolve to conceal a plaintext-key or missing-table diff.

4. Mark both existing migrations as applied, in order. This records SQL that the final v0.1 db push schema has already materialized; it does not execute or skip an outstanding schema change:

   pnpm exec prisma migrate resolve --applied 20260205174650_init
   pnpm exec prisma migrate resolve --applied 20260810052000_catch_up_current_schema

5. Run pnpm exec prisma migrate status, pnpm exec prisma migrate deploy, and ./scripts/verify-migrations.sh.

6. Recheck representative rows and API-key authentication.

The release gate exercises both paths: migrations on an empty PostgreSQL 16 database and history adoption on an exact db push schema. Do not baseline a database with any schema difference. Resolve unexpected drift from a reviewed backup or forward migration first.

Recovery

Prisma migrations are forward-only. If deployment validation fails, stop the new application, preserve logs, and restore the pre-deployment database backup. Do not improvise a destructive down migration against production data.