Dentolize · Regional DB Split Walkthrough
On this pageBusiness viewTechnical view

Moving a Company Between Regions

Business view

Splitting the database by region only matters if clinics can actually end up in the right one. Existing clinics are all sitting in the current (soon-to-be-EU-1) database; over time, some of them — say, a Saudi clinic that should really be on ME-1 — need to be moved, and new regions may come online that absorb clinics from an older one. This PR ships the tool that does that move: a command-line migration script, run by operators from the global Auth Server, that relocates one company's entire dataset — every database row, every uploaded file, every routing entry — from a source region to a target region.

The tool is built around a few hard guarantees that matter to the business, not just to engineers:

  • It doesn't touch anything until an operator explicitly confirms, and the confirmation string has to include the exact company id and both region codes, so a copy-paste mistake can't silently migrate the wrong clinic.
  • A "dry run" always comes first in practice. It scans the company's data and reports exactly how many rows, files, and bytes would move, without changing anything — so an operator can schedule a realistic downtime window instead of guessing.
  • It refuses to run while the clinic has work in flight — a pending Excel import, an unpaid online payment, an e-invoice mid-submission — because copying data out from under an active transaction would produce a target region that's silently missing something.
  • Nothing is deleted automatically. After a successful move, the old region's copy of the clinic's data is disabled but left in place as a backup, until an operator explicitly runs a separate cleanup step.
  • It can resume. If a migration is interrupted — network blip, process restart — it can pick back up from where it left off instead of starting over or leaving the clinic half-migrated.

Technical view

Full operator documentation lives in packages/auth-server/src/scripts/companyRegionMigration/README.md (421 lines) — this section summarizes the mechanics and points at the code.

Entry point and modules

Run from the Auth Server host: yarn migrate:company-region --help (packages/auth-server/src/scripts/migrateCompanyRegion.js). The implementation is split across single-purpose modules in packages/auth-server/src/scripts/companyRegionMigration/, orchestrated by migrationFlow.js (541 lines) — its own top comment: "Owns the migration timeline; detailed mechanics live in sibling modules." Notable modules: controlPlane.js (ledger CRUD, company locking), blockers.js (active-work checks), fencing.js (Redis write fences), globalState.js (global routing mapping updates), schemaPlanning.js (Prisma table-intersection between source/target schemas), postgresCopy.js (1,015 lines — low-level batched row copy), streamingCopy.js / streamingEstimate.js (real-run vs. dry-run streaming), storageAdapters.js (S3/GCS file copy), redisInvalidation.js (cache/session cleanup), finalizeCutover.js.

What it moves

Tenant-owned rows for the company, uploaded files referenced by those rows (with storage references rewritten for the target provider), and — only at the final cutover step — the global routing rows (CompanyRegion, CompanyLoginNameMapping, UserRegionMapping, CompanyPayment). It works for regional↔regional, monolith→regional, and regional→monolith pairings by computing the Prisma-owned table intersection between the two schemas.

Required configuration

Env vars on the Auth Server: GLOBAL_DATABASE_URL, REDIS_URL, REGIONAL_DATABASE_URLS_JSON, REGIONAL_REDIS_URLS_JSON, REGIONAL_STORAGE_CONFIG_JSON (per-region S3 or GCS config, preferring *Env fields so secrets stay out of the JSON itself), and optionally REGIONAL_WHATSAPP_URLS_JSON plus FACEBOOK_CALLBACK_TOKEN/FACEBOOK_JWT_SECRET if migrated WhatsApp accounts need callback updates.

Active blockers

The script checks for in-flight work before it will freeze the source company, and again immediately after freezing (to catch anything that started in the gap): CompanyUpload rows with pending/in-progress status, OnlinePayment rows CREATED/PENDING, EInvoiceBatch rows IN_PROGRESS, EInvoiceSubmission rows PENDING/IN_PROGRESS, plus Bull e-invoice queue jobs and the e-invoice batch lock key in Redis. A blocker failure never cuts over global routing.

Dry run vs. real migration

Dry run (--dry-run) creates an ESTIMATE ledger, scans source rows in batches, validates that referenced storage objects exist, estimates object count/bytes, and detects target conflicts — without freezing the source, copying anything, or touching global routing. Estimate ledgers expire after 24 hours.

A real migration requires --confirm <companyId>:<sourceRegion>:<targetRegion> exactly. It automatically looks for a recent matching dry-run estimate; if none exists (or it's expired) it prompts for explicit confirmation to continue anyway. During the run it: acquires a global per-company lock, sets Redis write-fences in both regions, disables the source company and deletes its sessions, streams rows in bounded batches (default 5,000), copies storage objects with bounded concurrency (default 8), validates row counts against the frozen plan, updates global routing tables in one transaction, invalidates Redis caches, updates WhatsApp callbacks, and marks the ledger COMPLETED.

Resume, cleanup, and the ledger

--resume <ledgerId> continues an interrupted run from wherever it stopped — before cutover it restores the source and clears fences; mid-copy it can skip already-copied identical rows; after cutover it only re-runs finalization steps. --cleanup-source (after a completed migration, once routing no longer points at the source) deletes the old region's now-orphaned rows and files — this step is destructive and is the final removal of the disabled backup. --cleanup-target removes a failed pre-cutover attempt's partial target data before a fresh retry.

The control-plane ledger (CompanyRegionMigration + its TableProgress/StorageProgress/CompanyLock tables, all in the global schema) tracks status (ESTIMATE → PREFLIGHT → FENCED → FROZEN → COPYING → CUTOVER_DONE → COMPLETED, or ERRORED), row/byte estimates, per-table and per-storage progress, and the last error — giving operators a full audit trail of exactly what happened to a specific clinic's move.