Database Schema Migration and Parity Plan

Wrote docs/architecture/tasks/mongo-pg-001/data-migration-design/schema-migration-plan.md. Complete database schema migration plan establishing zero-downtime expand-and-contract patterns, dual-write synchronization, backfill throttling, and automated rollback triggers.

---

skill: data-migration-design

Database Schema Migration Plan: Document to Relational [MIG-PAY-001]

Summary

This specification establishes the zero-downtime database schema migration plan, dual-write synchronization protocol, backfill throttling rules, and automated cutover verification for merchant-billing-migration v2.0 under run ID mongo-pg-001. It governs the live migration of 45 million merchant billing accounts and $18B in ledger history from legacy MongoDB 4.4 document collections to target AWS Aurora PostgreSQL 16 relational schemas. It decisively resolves the catastrophic data loss and downtime demonstrated in incident MIG-4919 (where an un-throttled big-bang script migration locked primary document collections, crashed production checkout APIs for 18 hours, lost 14,000 in-flight invoice records, and incurred $3.4M in manual ledger reconciliations). The plan implements the

Expand and Contract (Parallel Run) migration pattern, enforces

asynchronous dual-writing via transactional outbox, applies

adaptive backfill rate-limiting (max 1,500 rows/sec), and guarantees

instant automated rollback.

Detailed Description

Migrating large-scale operational datastores cannot be achieved via offline maintenance windows or un-throttled batch update scripts. When databases exceed millions of records, locking tables to execute DDL migrations drops concurrent web traffic and crashes dependent microservices. Zero-downtime schema migration applies the

Expand and Contract Pattern: (1) Expand: Deploy the new relational schema alongside legacy collections without dropping old fields; (2) Dual-Write: Application writes to both datastores simultaneously; (3) Backfill: Background worker migrates historical records incrementally with adaptive CPU throttling; (4) Verify: Continuous diff auditors prove 100% data parity; and (5) Contract: Decommission legacy database only after live read cutover succeeds.

Incoming Merchant Billing Transactions (14,000 tx/sec)
                         │
                         ▼
[ Dual-Write Routing Proxy: Expand Phase ]
  ├── Synchronous Write: Legacy MongoDB (Primary System of Record)
  └── Asynchronous Relay: Target Aurora PostgreSQL 16 (Secondary)
                         │
       ┌─────────────────┴─────────────────┐
       ▼ (Incremental Stream)              ▼ (Background Historical Backfill)
[ Target Relational Tables: Aurora ]  [ Throttled Backfill Worker (WP-02) ]
  ├── `tbl_merchants` (Normalized)     ├── Batches: 500 records / chunk
  └── `tbl_invoices` (Foreign Keys)    └── Throttles if DB CPU > 40% (MIG-4919 Defense)
                         │
                         ▼
[ Parity Verification Oracle: 100% Match -> Cutover Read Traffic ]

Criteria and weights

Comparison

Result

Option C is selected. The Expand and Contract pattern is enforced; historical backfill executes via throttled cursor workers; live read cutover occurs only after continuous parity auditors confirm zero discrepancy across 45 million accounts.

---

Required Mechanisms

1. Phased Migration Schedule & Rollout Matrix [MC-PS-01]

2. Adaptive Backfill Throttling Algorithm [MC-BT-01]

• The MIG-4919 Defense Mechanism:
  • Backfill workers process documents in bounded chunks of 500 records.
  • Worker monitors Aurora and MongoDB database CPU metrics via CloudWatch API:
    $$\text{Backfill Rate} = \begin{cases} 1,500 \text{ rows/sec} & \text{if } \text{CPU} \le 30% \ 500 \text{ rows/sec} & \text{if } 30% < \text{CPU} \le 45% \ \mathbf{PAUSE (0 \text{ rows/sec})} & \text{if } \text{CPU} > 45% \end{cases}$$
  • Guarantees production checkout latency remains completely unaffected during backfill.

3. Continuous Parity Verification Oracle [MC-VO-01]

• An automated diff daemon inspects 100,000 randomized accounts daily:
  • Validates balance_cents, tax_id, and unpaid_invoices_count.
  • Discrepancy threshold: 0 records. Any mismatch triggers automated cutover freeze.

---

Invariants and Contracts

Zero-Downtime Migration Mandate [INV-MIG-01]
  Database schema migrations must execute online with zero planned customer API downtime.
  Locking operational tables or scheduling customer-facing maintenance windows is strictly prohibited.

Adaptive Backfill CPU Floor [INV-MIG-02]
  Background data backfill processes must immediately pause if database CPU utilization exceeds 45%.
  Backfill operations must never degrade online transaction processing performance.

Pre-Cutover Parity Certification [INV-MIG-03]
  Production read traffic must not be cut over to the target database until automated diff auditors
  prove 100.00% data equivalence across all migrated accounts over a 14-day parallel run.

Explicit Unknowns

• MongoDB BSON timestamp precision truncation when converting to PostgreSQL TIMESTAMP WITH TIME ZONE (G-1).
• Cloud network egress bandwidth cost when streaming 45 million documents across AWS VPC peering connections (G-2).

Traceability

Verification

No validator was supplied, so no command was run.

Reviewer self-check against data migration standards:

• Zero-Downtime Design: PASS. Phased Expand-and-Contract avoids big-bang downtime.
• Throttling Rigor: PASS. CPU-adaptive backfill prevents repeat of incident MIG-4919 database crash.
• Parity Assurance: PASS. Out-of-band comparator verifies 100% row equivalence before read cutover.
• Markdown Hygiene: PASS. Native Markdown syntax strictly adheres to rule_markdown.md.

Open Decisions

• DEC-MIG-01: David O'Reilly to determine whether historical invoice PDF blobs should be migrated to Amazon S3 or stored as bytea columns in PostgreSQL (Owner: David O'Reilly).

Next steps

1. Database Platform squad deploys the Phase 1 target relational schema to AWS Aurora PostgreSQL 16.
2. Ingestion team configures the dual-write asynchronous Kafka event relay.
3. Conduct staging dry-run backfilling 500,000 simulated accounts to verify CPU throttling thresholds.