1.4 Master DB initialization

Outcome

The master Postgres database has its schemas, tables, reference data, and the first db_server row registered. No tenants yet.

Prerequisites

• 1.3 Deploy services complete (services run but have no master schema).

What goes into master?

Master holds:

• Cross-tenant registries: identity.tenant, identity.tenant_audit.
• Platform staff: security.platform_user, security.platform_role, security.platform_role_assignment.
• DB server registry: identity.db_server rows that map regions to tenant DB instances.
• Reference data: payers, procedure codes, modifiers, CCI/MUE, fee schedules templates.

A tenant DB by contrast holds only that tenant's claims, members, eligibility, etc.

Steps

1. Create the master database

   The flexible server is empty after step 1.2. Create the database:

   psql "host=pg-rcm-master-prod.postgres.database.azure.com user=rcm_master_admin dbname=postgres sslmode=require" \
     -c "CREATE DATABASE rcm_master OWNER rcm_master_admin;"

2. Run master migrations

   From your workstation in the repo root:

   export MASTER_DATABASE_URL="postgresql://rcm_master_admin:<password>@pg-rcm-master-prod.postgres.database.azure.com:5432/rcm_master?sslmode=require"
   pnpm --filter @rcm/database run migrate:master

   This applies all packages/database/src/migrations/master/*.ts migrations in order (currently 50+ migrations covering schemas, tenant registry, platform users, payer registry, reference data, modifier rules, COB, institutional billing, etc.).

3. Verify schemas and seed data

   psql "$MASTER_DATABASE_URL" -c "
     SELECT table_schema, count(*) AS tables
     FROM information_schema.tables
     WHERE table_schema IN ('identity','security','rcm_master','reference')
     GROUP BY 1 ORDER BY 1;"

   Expected schemas: identity, security, rcm_master, plus reference tables seeded by migrations 003 / 011 / 012 / 020 / 021 / 023 / 025 / 026 / 030 / 031 / 032 / 040 / 041 / 042 / 043 / 045 / 046 (procedure codes, modifiers, CCI/MUE, group-size rules, Medicare-primary matrix, commercial payer stubs, institutional reference data, etc.).

4. Register the first db_server

   Tell master DB about the tenant DB server you provisioned:

   INSERT INTO identity.db_server (
     name, region, host, port, admin_user, status, capacity_tenants
   ) VALUES (
     'pg-rcm-tenant-eastus2-01',
     'eastus2',
     'pg-rcm-tenant-eastus2-01.postgres.database.azure.com',
     5432,
     'rcm_tenant_admin',
     'active',
     100
   );

   The admin password lives in Key Vault under pg-tenant-eastus2-01-admin-password; the provisioner reads it from there.

5. Confirm reference data sanity

   -- Payer registry seeded? (commercial stubs from migration 043)
   SELECT count(*) FROM rcm_master.payer;
   
   -- Procedure codes seeded across all verticals?
   SELECT vertical, count(*) FROM rcm_master.procedure_service_line GROUP BY 1;
   
   -- Modifier injection rules?
   SELECT count(*) FROM rcm_master.modifier_injection_rule;

   Counts should be non-zero for all three.

Validation

Check	Expected
Master schemas exist	identity, security, rcm_master, reference
Migration count	50+ rows in knex_migrations
identity.db_server	1 row with status active
Reference data	non-zero counts

Troubleshooting

Symptom	Likely cause	Fix
permission denied to create extension "citext"	Default Postgres user lacks CREATE EXTENSION	GRANT azure_pg_admin TO rcm_master_admin; then re-run.
Migration X fails with "relation already exists"	Partial prior run	Check knex_migrations and knex_migrations_lock; manually unlock and re-run.
Reference-data counts are zero	Seed migrations skipped	Re-run those specific migration files via pnpm --filter @rcm/database run migrate:master:up <name>.

Next

1.5 — First platform admin