#118 [High] Add database migration rollback tooling with CI verification

[james2177]

Description: prisma/migrations/ currently holds only a forward 20240101000000_init migration; Prisma itself has no built-in automatic "down" migration generation, and this repo has no documented or tooled way to roll back a bad migration once db:migrate:prod (prisma migrate deploy) has applied it in production. Add a rollback tooling convention (hand-authored down-migration SQL per forward migration, stored alongside it) plus a CI job that verifies each migration's down-script actually reverses its up-script cleanly.

Problem Statement & Context: The backup/restore runbook automation in issues #30/#31 covers full-database disaster recovery, but a bad schema migration doesn't always warrant a full restore — a targeted rollback is faster and safer when caught quickly. Without any rollback tooling or verification, an operator's only recourse today for a bad migration is either a full backup restore or a risky manual DROP/ALTER under production pressure.

Scope & Acceptance Criteria:

• Define and document a convention for storing a down-migration alongside each forward migration in prisma/migrations/<timestamp>_<name>/ (e.g. a down.sql file, since Prisma doesn't generate these automatically).
• Add a CI job that, for every migration with a down.sql, applies the up-migration then the down-migration against a fresh CI Postgres and asserts the resulting schema matches the pre-migration state.
• Backfill a down.sql for the existing 20240101000000_init migration as the worked example.
• Out of scope: building an automated rollback execution tool for production (a script that runs down.sql against live prod) — this issue covers the tooling/convention and CI verification; production rollback execution should go through the same change-management process as forward migrations, documented separately.

Implementation Guidelines:

1. Key files: prisma/migrations/20240101000000_init/, prisma/schema.prisma, .github/workflows/ci.yml (reuse the Postgres service-container pattern already defined there).
2. Document the convention clearly in CONTRIBUTING.md (Add a startup self-check for empty contract-id env vars in non-dev environments #98) — every future PR adding a migration must also add its down.sql, and this should be checked by the new CI job (fail if a new migration lacks one).
3. Handle data-loss-risk migrations (e.g. dropping a column) explicitly in the documentation — a "down" migration that recreates a dropped column can't restore its data, and this must be called out rather than silently glossed over.
4. Testing: the CI job itself is the test — up → down → schema-diff-check must run and pass for every migration in the history; include a deliberately-broken down.sql in a draft PR to prove the job actually catches failures, then remove it before merging.

Definition of Done:

• Rollback convention documented, CI verification job added, 20240101000000_init's down.sql backfilled.
• Acceptance criteria met.
• PR passes CI, includes evidence the verification job catches a broken down-migration.
• Reviewed and approved.

Resources: prisma/migrations/, prisma/schema.prisma, .github/workflows/ci.yml, RUNBOOK_BACKUP_RESTORE.md

Complexity: High (200 points)