Dentolize · Regional DB Split Walkthrough
On this pageBusiness viewTechnical view

Global vs. Regional Split

Business view

Today, every Dentolize clinic — wherever it is in the world — has its data stored in the same single database. This PR splits that into two kinds of database:

  • A global database, holding the handful of things that genuinely need to be shared everywhere: the list of Dentolize admins, referral partners, which region each clinic and user lives in, and a few shared catalogs (like the master medication and condition lists every clinic can search).
  • A regional database per region (starting with EU-1 for existing European/general clients and ME-1 for Saudi Arabia), each holding everything else: the clinics themselves, their patients, appointments, invoices, inventory — the full clinical and financial record.

A new, small Auth Server sits in front of both. When someone tries to log in, the Auth Server's only job is to say "this email belongs to region EU-1" (or ME-1, or wherever) so the client can talk to the right regional server. The regional servers never talk to each other directly, and a regional server can only read the global database — it can never write to it.

The practical effect: a clinic's data physically lives in one region, one country even, and stays there. If Saudi Arabia requires clinic data to remain on Saudi soil, ME-1 can be hosted there while EU-1 continues serving everyone else, with no code fork — same application, same features, different database per region.

Technical view

The split

  • Global schema: packages/prisma/schema.global.prisma (320 lines). Header comment states it's used by the Auth Server (auth.dentolize.com) and read-only by regional servers. Models: Admin, Referral (system users), CompanyPayment (kept global because it references Admin), Region, CompanyRegion, CompanyRegionMigration + its progress/lock tables, UserRegionMapping, CompanyLoginNameMapping, RegistrationWorkflow, FeatureFlag, and the global catalogs GlobalInventoryItem, GlobalMedication, GlobalCondition.
  • Regional schema: packages/prisma/schema.regional.prisma (7,103 lines, 136 models — Company, User, Patient, Appointment, Invoice, etc.). This file is auto-generated, not hand-edited — its header explicitly says so.
  • Generator: packages/prisma/generate-regional-schema.js (185 lines) reads the original monolith schema.prisma (still present, kept for backward compatibility), strips a hardcoded GLOBAL_MODELS list (generate-regional-schema.js:22-30: Admin, Referral, CompanyPayment, GlobalInventoryItem, GlobalMedication, GlobalCondition, FeatureFlag), converts three hardcoded CROSS_SCHEMA_RELATIONS (:33-40: Company→Referral, Company→CompanyPayment, Operation→GlobalCondition) into "soft references" — the foreign-key column stays, but the Prisma @relation is dropped, since Prisma can't do relations across two separate databases — and writes the result to schema.regional.prisma. The global schema's own extra models (Region, CompanyRegion*, mapping tables) are not part of this generation step, meaning schema.global.prisma is maintained by hand.
  • Yarn commands (packages/prisma/package.json, packages/prisma/scripts/generate-client.js): generate:global and generate:regional produce two independent Prisma clients (generated/global-client, generated/regional-client) from two independent configs (prisma.config.global.ts, prisma.config.regional.ts); generate:split runs both. The legacy monolith commands (prisma:generate, prisma:migrate:dev, etc.) still exist for backward compatibility.

The Auth Server

packages/auth-server is a new standalone service (src/index.js, 403 lines; entrypoint src/bootstrap.js). It:

  • Exposes REST lookup endpoints (/api/lookup, /api/lookup-company, /api/lookup-public-route, /api/lookup-reset-password-token, /api/lookup-verification-token) that map an email/company/public-entity id to a region API URL. See Region-Aware Login & Multi-Account for how these are used.
  • Exposes internal sync webhooks (/internal/user-mapping, /internal/company-mapping) that regional servers call whenever a user or company is created/updated/deleted, keeping the global routing tables in sync.
  • Runs its own Apollo GraphQL server at /graphql (index.js:317-362), merging schema fragments from @dentolize/server with its own local .graphql files and resolvers — this is where admin login, cross-region registration, and the admin dashboard's regional queries/mutations live.
  • Connects to the global database always, and optionally to a local regional database too (index.js:88-167) — this matches the EU-1 production topology, where the Auth Server and the EU-1 regional API run as sibling PM2 processes that can share a host.

Runtime cross-region database access

Two registries let a server reach a database that isn't its own:

  • packages/server/src/db/globalClient.js — every regional server gets a lazily-cached, read-only connection to the global database via getGlobalPrisma(). Its own doc comment is explicit: regional servers "should NEVER write to the global database directly — all writes go through the Auth Server's internal API."
  • packages/server/src/db/regionalPrismaRegistry.js and its auth-server counterpart packages/auth-server/src/utils/regionalPrismaRegistry.js — getRegionalPrisma(regionCode, ...) looks up REGIONAL_DATABASE_URLS_JSON and opens/caches a pooled Prisma client for any region on demand. This is what lets the Auth Server, the admin dashboard, and the company-region migration tool reach a specific region's data.

Key env vars (.env.example): REGION_CODE (this server's own region, e.g. EU-1), DATABASE_URL (this region's database), GLOBAL_DATABASE_URL (the shared global database), REGIONAL_DATABASE_URLS_JSON (a JSON map of region code → connection string, needed by anything that must reach other regions).

Shared infrastructure: server-common

packages/server-common is a new internal package (@dentolize/server-common) that centralizes code previously duplicated per-service: Redis client creation and rate limiting (redis.js, rateLimit.js), password hashing (password.js), DB connection-string/SSL handling (db.js), CORS whitelisting (cors.js), structured logging and the Apollo logging plugin (logging/), and OpenTelemetry tracing/metrics bootstrap (tracing/, metrics/metricsSDK.js). It's consumed by both packages/server (18 files) and packages/auth-server (7 files); packages/whatsapp-official does not use it yet. packages/server/package.json's prebuild step runs yarn workspace @dentolize/server-common run build first, since both server and auth-server compile against its build output.