Implement Zero-Downtime Live Schema Migration Framework With Dual-Write Shadow Verification and Automated Cutover

[Emmyt24]

Description

Prisma migrations currently run as a single blocking step; for a large projection table, a schema-changing migration under live traffic risks locking writers for the duration or losing writes issued during the cutover window.

Requirements and Context

• Implement a dual-write shadow-table migration pattern: create the new schema shape alongside the old, dual-write both during a transition window, backfill historical rows, verify shadow-table parity, then cut reads over
• Cutover must be a single atomic, reversible step (a feature-flag or view swap), not a data-copy operation
• Verification must compare every row (not a sample) between old and shadow tables before allowing cutover
• Add an automated rollback path that reverts to old-schema reads if post-cutover error rates spike
• Proof of work (required): this is a hard/complex issue — the PR description must include (1) a screenshot of the full relevant test suite run showing all tests passing, and (2) a screenshot of a successful project/module build or compile (npm run build) with no errors. PRs missing either screenshot will not be reviewed.

Suggested Execution

Branch: feat/backend-zero-downtime-schema-migration

Implement Changes

• Add a MigrationOrchestrator that manages the dual-write / backfill / verify / cutover / cleanup lifecycle for a target table
• Wire dual-writes through the existing Prisma client layer transparently to callers (no per-call-site changes required)
• Implement full-table parity verification with a clear pass/fail report and a hard block on cutover if any row mismatches
• Add the automated rollback path

Test and Commit

Run npx vitest run src/__tests__/migrationOrchestrator.test.ts — dual-write, full parity verification, atomic cutover, and rollback-on-error-spike all pass. Then run npm run build and confirm it completes with no errors. Attach a screenshot of both the passing test run and the successful build to the PR description as proof of work.

Example Commit Message

feat(backend): zero-downtime live schema migration framework with dual-write verification

Closes #<issue>

Guidelines

• Branch from main, open a PR back to main
• All new code must have corresponding tests
• Run npm run lint and npm run test before pushing
• Follow existing naming conventions and file structure
• PR description must reference this issue number (e.g., Closes #<issue>)
• Keep commits atomic and use conventional commit format
• This is a hard/complex issue: PR must include a screenshot of the test suite passing and a screenshot of a successful build/compile — PRs without both screenshots will be marked incomplete and not merged