Master database — data model

The master database (rcm_master) holds cross-tenant registries: tenant metadata, platform users, payer / trading-partner / companion-guide registries, and shared reference data (NPI provider registry, X12 code sets). One master DB per environment, replicated for read scale.

Diagrams are grouped by Postgres schema. Each diagram shows table boxes with primary keys + foreign keys + status / type / code columns. Full column inventories follow each diagram. Tables outside the diagrams (e.g. NPI staging) are intentionally omitted as ETL-internal.

Table of contents

• identity + security
• rcm_master — payer and EDI registry
• rcm_reference — code sets
• npi — NPPES provider registry

identity + security

Tenant lifecycle, DB-server placement, platform users and roles.

The ER diagram below shows the core tables. Several auth-related tables added in ADM_02 (security.platform_user_mfa_factor, security.platform_user_recovery_code, security.platform_webauthn_credential) and ADM_03 (security.federation_partner, security.federation_key, security.federation_partner_tenant, security.federation_nonce, security.revoked_session) are omitted from the diagram to keep it readable — they are documented in the Federation and Security & Compliance chapters.

Column inventory

identity.db_server

Column	Type	Null	Notes
db_server_id	uuid	N	PK
name	varchar(100)	N
pgbouncer_host	varchar(255)	N
pgbouncer_port	integer	N
admin_secret_ref	varchar(200)	N
region	varchar(50)	Y
capacity_hint	integer	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N

identity.dr_drill

Column	Type	Null	Notes
drill_id	uuid	N	PK
tenant_id	uuid	Y	FK
environment	varchar(16)	N
source_db_server_id	varchar(128)	N
restored_db_server_id	varchar(128)	N
target_pitr_at	timestamptz	N
started_at	timestamptz	N
completed_at	timestamptz	N
rto_seconds	integer	N
rpo_seconds	integer	N
post_rotation_phi_dek_kid	smallint	Y
outcome	varchar(16)	N
details	jsonb	N
initiated_by	varchar(128)	N
recorded_at	timestamptz	N

identity.migration_cohort_run

Column	Type	Null	Notes
run_id	uuid	N	PK
started_at	timestamptz	N
finished_at	timestamptz	Y
total_tenants	integer	N
cohort_size	integer	N
stagger_ms	integer	N
succeeded_count	integer	N
failed_count	integer	N
last_completed_cohort	integer	N
aborted_at	timestamptz	Y
abort_reason	text	Y
operator	varchar(200)	Y
migration_args	jsonb	N
report	jsonb	Y

identity.platform_job

Column	Type	Null	Notes
job_id	uuid	N	PK
kind	varchar(32)	N
status	varchar(16)	N
started_by	uuid	Y	FK
tenant_id	uuid	Y	FK
payload	jsonb	N
result	jsonb	N
error_code	varchar(64)	Y
error_message	text	Y
started_at	timestamptz	N
finished_at	timestamptz	Y

identity.tenant

Column	Type	Null	Notes
tenant_id	uuid	N	PK
slug	varchar(64)	N
name	varchar(200)	N
db_server_ref	uuid	N	FK
db_config_ref	varchar(200)	N
status	enum	N
provisioned_at	timestamptz	Y
status_changed_at	timestamptz	Y
offboarded_at	timestamptz	Y
created_at	timestamptz	N
updated_at	timestamptz	N
preferred_region	varchar(50)	Y
phi_dek_kid	smallint	N

identity.tenant_audit

Column	Type	Null	Notes
audit_id	uuid	N	PK
event_type	varchar(64)	N
tenant_id	uuid	Y	FK
platform_user_id	uuid	Y	FK
details	jsonb	N
ip_address	inet	Y
user_agent	text	Y
occurred_at	timestamptz	N

identity.worker_lease

Column	Type	Null	Notes
worker_id	varchar(200)	N	PK
hostname	varchar(255)	N
pid	integer	N
started_at	timestamptz	N
last_heartbeat_at	timestamptz	N
generation	integer	N
metadata	jsonb	Y

security.platform_role

Column	Type	Null	Notes
platform_role_id	uuid	N	PK
code	varchar(50)	N
description	text	N
created_at	timestamptz	N

security.platform_role_assignment

Column	Type	Null	Notes
platform_user_id	uuid	N	PK, FK
platform_role_id	uuid	N	PK, FK
assigned_at	timestamptz	N
assigned_by	uuid	Y	FK

security.platform_user

Column	Type	Null	Notes
platform_user_id	uuid	N	PK
email	enum	N
password_hash	varchar(255)	N
full_name	varchar(200)	Y
is_active	boolean	N
last_login_at	timestamptz	Y
created_at	timestamptz	N
updated_at	timestamptz	N

rcm_master — payer and EDI registry

Payers, programs and program rules, aliases, trading partners, companion guides, submission routing.

Column inventory

rcm_master.companion_guide

Column	Type	Null	Notes
companion_guide_id	uuid	N	PK
trading_partner_id	uuid	N	FK
tx_type	varchar(10)	N
guide_version	varchar(50)	N
description	text	Y
effective_from	date	Y
effective_to	date	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N

rcm_master.companion_guide_rule

Column	Type	Null	Notes
rule_id	uuid	N	PK
companion_guide_id	uuid	N	FK
loop_id	varchar(20)	Y
segment_id	varchar(10)	N
element_position	integer	Y
component_position	integer	Y
rule_type	varchar(20)	N
condition	jsonb	Y
value_override	text	Y
value_source	varchar(100)	Y
validation_regex	varchar(255)	Y
max_length	integer	Y
notes	text	Y
sort_order	integer	N
created_at	timestamptz	N

rcm_master.payer

Column	Type	Null	Notes
payer_id	uuid	N	PK
name	varchar(255)	N
payer_code	varchar(50)	Y
payer_type	varchar(50)	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N
short_name	varchar(100)	Y
state	varchar(2)	Y
address_line1	varchar(255)	Y
address_line2	varchar(255)	Y
city	varchar(100)	Y
state_addr	varchar(2)	Y
zip	varchar(10)	Y
phone	varchar(20)	Y
website	varchar(255)	Y
electronic_payer_id	varchar(50)	Y
clearinghouse_payer_id	varchar(50)	Y

rcm_master.payer_alias

Column	Type	Null	Notes
alias_id	uuid	N	PK
payer_id	uuid	N	FK
alias_name	varchar(255)	N
alias_context	varchar(100)	Y
is_active	boolean	N
created_at	timestamptz	N

rcm_master.payer_lob

Column	Type	Null	Notes
lob_id	uuid	N	PK
payer_id	uuid	N	FK
lob_code	varchar(50)	N
lob_name	varchar(255)	N
state	varchar(2)	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N

rcm_master.payer_plan

Column	Type	Null	Notes
plan_id	uuid	N	PK
lob_id	uuid	N	FK
plan_code	varchar(50)	N
plan_name	varchar(255)	N
plan_type	varchar(50)	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N

rcm_master.payer_submission_capability

Column	Type	Null	Notes
capability_id	uuid	N	PK
payer_id	uuid	N	FK
claim_type	varchar(1)	N
submission_method	varchar(20)	N
trading_partner_id	uuid	Y	FK
accepts_secondary	boolean	N
accepts_corrected	boolean	N
timely_filing_days	integer	Y
notes	text	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N

rcm_master.payer_trading_partner_id

Column	Type	Null	Notes
ptp_id	uuid	N	PK
payer_id	uuid	N	FK
trading_partner_id	uuid	N	FK
external_payer_id	varchar(50)	N
is_active	boolean	N
created_at	timestamptz	N

rcm_master.program

The only program concept customers see (#5986 — was canonical_program; the old name was kept as a compatibility view until #5993 dropped it).

Column	Type	Null	Notes
program_id	uuid	N	PK
name	varchar(100)	N
service_lines	text[]	N	primary service line first
description	text	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N

rcm_master.program_rule

Rates and code rules for a program in a state (#5986 — was payer_program, kept as a compatibility view until #5993 dropped it). payer_id NULL is the standard rule set, set is a payer exception; state_code NULL is national. One active rule set per (program, state, payer, payer type) over its effective dates (excl_program_rule_one_per_scope). Its code-rule children — program_rule_procedure_code, _modifier_requirement, _pos, _tob, _revenue, _taxonomy — each carry program_rule_id and are documented in docs/database/master/.

Column	Type	Null	Notes
program_rule_id	uuid	N	PK
program_id	uuid	N	FK → program
state_code	varchar(2)	Y	FK → state; NULL = national
payer_id	uuid	Y	FK → payer; NULL = standard
rule_kind	varchar(20)	Y	generated: TYPED_STANDARD · STANDARD · EXCEPTION · NATIONAL · NATIONAL_EXCEPTION
name	varchar(200)	N
payer_type	varchar(50)	Y	a typed standard: applies only to payers of this payer.payer_type; NULL = any (#6275)
service_lines	text[]	N
external_program_id	varchar(100)	Y
effective_from	date	N
effective_to	date	Y
effective_range	daterange	Y	generated
requires_referring_provider	boolean	N
is_institutional	boolean	N
source_documents	jsonb	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N

rcm_master.schema_version

Column	Type	Null	Notes
version_id	uuid	N	PK
schema_name	varchar(50)	N
major_version	integer	N
minor_version	integer	N
patch_version	integer	N
description	text	Y
applied_at	timestamptz	N

| effective_range | daterange | Y | | | rate_amount_cents | integer | Y | |

rcm_master.submission_routing_rule

Column	Type	Null	Notes
routing_rule_id	uuid	N	PK
state	varchar(2)	Y
payer_id	uuid	Y
payer_type	varchar(50)	Y
service_line	varchar(50)	Y
claim_type	varchar(5)	N
tx_type	varchar(10)	N
trading_partner_id	uuid	N	FK
companion_guide_id	uuid	Y	FK
connection_config	jsonb	Y
priority	integer	N
effective_from	date	Y
effective_to	date	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N

rcm_master.trading_partner

Column	Type	Null	Notes
trading_partner_id	uuid	N	PK
name	varchar(255)	N
partner_type	varchar(50)	N
partner_code	varchar(50)	Y
connection_config	jsonb	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N
credential_last_rotated_at	timestamptz	Y
credential_last_rotated_by	varchar(100)	Y
credential_secret_names	ARRAY	N

rcm_reference — code sets

X12 code lists (CPT/HCPCS, ICD-10, modifiers, place of service, revenue, CARC/RARC), CCI/MUE edits, payer supervision rules.

Column inventory

rcm_reference.cci_edit_pair

Column	Type	Null	Notes
cci_edit_id	uuid	N	PK
column_1_code	varchar(10)	N
column_2_code	varchar(10)	N
modifier_indicator	varchar(1)	N
effective_from	date	N
effective_to	date	Y
ptp_edit_type	varchar(20)	N
source	varchar(20)	N
created_at	timestamptz	N

rcm_reference.code_set_adjustment_group

Column	Type	Null	Notes
code_id	uuid	N	PK
code	varchar(5)	N
description	text	N
is_active	boolean	N
created_at	timestamptz	N

rcm_reference.code_set_adjustment_reason

Column	Type	Null	Notes
code_id	uuid	N	PK
code	varchar(10)	N
description	text	N
effective_from	date	N
effective_to	date	Y
deactivated_date	date	Y
notes	text	Y
created_at	timestamptz	N

rcm_reference.code_set_icd10

Column	Type	Null	Notes
code_id	uuid	N	PK
code	varchar(10)	N
code_type	varchar(5)	N
short_description	text	Y
long_description	text	Y
effective_from	date	N
effective_to	date	Y
is_billable	boolean	N
chapter	varchar(10)	Y
created_at	timestamptz	N

rcm_reference.code_set_modifier

Column	Type	Null	Notes
code_id	uuid	N	PK
code	varchar(5)	N
description	text	Y
modifier_type	varchar(50)	Y
effective_from	date	N
effective_to	date	Y
is_active	boolean	N
created_at	timestamptz	N

rcm_reference.code_set_place_of_service

Column	Type	Null	Notes
code_id	uuid	N	PK
code	varchar(2)	N
name	varchar(100)	N
description	text	Y
effective_from	date	N
effective_to	date	Y
is_active	boolean	N
created_at	timestamptz	N

rcm_reference.code_set_procedure

Column	Type	Null	Notes
code_id	uuid	N	PK
code	varchar(10)	N
code_type	varchar(20)	N
short_description	text	Y
long_description	text	Y
effective_from	date	N
effective_to	date	Y
category	varchar(50)	Y
is_active	boolean	N
created_at	timestamptz	N

rcm_reference.code_set_remark

Column	Type	Null	Notes
code_id	uuid	N	PK
code	varchar(10)	N
description	text	N
effective_from	date	N
effective_to	date	Y
deactivated_date	date	Y
notes	text	Y
created_at	timestamptz	N

rcm_reference.code_set_revenue

Column	Type	Null	Notes
code_id	uuid	N	PK
code	varchar(4)	N
description	text	Y
category	varchar(100)	Y
effective_from	date	N
effective_to	date	Y
is_active	boolean	N
created_at	timestamptz	N
accommodation_subtype	varchar(20)	Y

rcm_reference.group_size_rate_adjustment

Column	Type	Null	Notes
adjustment_id	uuid	N	PK
state_code	varchar(2)	N
service_line	varchar(32)	N
payer_id	uuid	Y
min_group_size	integer	N
max_group_size	integer	Y
percent_multiplier	numeric	N
effective_from	date	N
effective_to	date	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N

rcm_reference.medicaid_non_covered_carc

Column	Type	Null	Notes
rule_id	uuid	N	PK
state_code	varchar(2)	Y
carc_code	varchar(5)	N
reason_description	text	N
effective_from	date	N
effective_to	date	Y
is_active	boolean	N
created_at	timestamptz	N

rcm_reference.medicare_primary_rule

Column	Type	Null	Notes
rule_id	uuid	N	PK
rule_code	varchar(40)	N
description	text	N
entitlement_reason	varchar(20)	Y
age_min	integer	Y
age_max	integer	Y
employer_size_min	integer	Y
esrd_coordination_active	boolean	Y
requires_black_lung	boolean	N
primary_payer_type	varchar(20)	N
evaluation_order	integer	N
effective_from	date	N
effective_to	date	Y
is_active	boolean	N
created_at	timestamptz	N

rcm_reference.modifier_injection_rule

Column	Type	Null	Notes
rule_id	uuid	N	PK
procedure_code	varchar(16)	N
service_line	varchar(32)	Y
state_code	varchar(2)	Y
payer_id	uuid	Y
trigger_type	varchar(32)	N
trigger_value	varchar(64)	N
modifier_code	varchar(8)	N
modifier_sequence	smallint	N
description	text	Y
effective_from	date	N
effective_to	date	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N

rcm_reference.mue_limit

Column	Type	Null	Notes
mue_limit_id	uuid	N	PK
procedure_code	varchar(10)	N
mue_value	integer	N
mai_indicator	varchar(1)	N
mue_rationale	varchar(1)	N
edit_type	varchar(20)	N
effective_from	date	N
effective_to	date	Y
source	varchar(20)	N
created_at	timestamptz	N

rcm_reference.payer_supervision_rule

Column	Type	Null	Notes
rule_id	uuid	N	PK
payer_id	uuid	Y
state_code	varchar(2)	Y
service_line	varchar(32)	N
supervisor_credential	varchar(16)	N
supervisee_credential	varchar(16)	N
max_ratio	integer	N
severity	varchar(16)	N
effective_from	date	N
effective_to	date	Y
is_active	boolean	N
created_at	timestamptz	N

rcm_reference.procedure_service_line

Column	Type	Null	Notes
procedure_service_line_id	uuid	N	PK
procedure_code	varchar(10)	N
service_line	varchar(20)	N
is_primary	boolean	N
typical_unit_type	varchar(20)	Y
notes	text	Y
effective_from	date	N
effective_to	date	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N
effective_range	daterange	Y

rcm_reference.ub04_code

Column	Type	Null	Notes
code_id	uuid	N	PK
kind	varchar(20)	N
code	varchar(10)	N
description	text	N
effective_from	date	N
effective_to	date	Y
is_active	boolean	N
created_at	timestamptz	N

npi — NPPES provider registry

Mirror of the CMS NPPES provider registry. Refreshed monthly.

Column inventory

npi.address

Column	Type	Null	Notes
address_id	uuid	N	PK
provider_id	uuid	N	FK
address_type	varchar(30)	N
sequence_no	integer	Y
first_line	varchar(200)	Y
second_line	varchar(200)	Y
city	varchar(100)	Y
state	varchar(50)	Y
postal_code	varchar(20)	Y
country_code	varchar(2)	Y
telephone_number	varchar(25)	Y
fax_number	varchar(25)	Y
telephone_extension	varchar(10)	Y
created_at	timestamptz	N

npi.endpoint

Column	Type	Null	Notes
endpoint_id	uuid	N	PK
provider_id	uuid	N	FK
endpoint_type	varchar(50)	Y
endpoint_type_description	varchar(200)	Y
endpoint	text	Y
affiliation	varchar(10)	Y
affiliation_legal_business_name	varchar(300)	Y
use_code	varchar(50)	Y
use_description	varchar(200)	Y
content_type	varchar(100)	Y
content_description	varchar(200)	Y
content_other_description	varchar(500)	Y
address_first_line	varchar(200)	Y
address_second_line	varchar(200)	Y
address_city	varchar(100)	Y
address_state	varchar(50)	Y
address_postal_code	varchar(20)	Y
address_country_code	varchar(2)	Y
created_at	timestamptz	N

npi.ingestion_history

Column	Type	Null	Notes
ingestion_id	uuid	N	PK
file_type	varchar(20)	N
file_name	varchar(500)	N
file_date	date	Y
status	varchar(20)	N
started_at	timestamptz	N
completed_at	timestamptz	Y
records_processed	integer	Y
records_inserted	integer	Y
records_updated	integer	Y
records_deactivated	integer	Y
records_failed	integer	Y
error_message	text	Y
details	jsonb	Y
rows_processed	jsonb	Y

npi.other_identifier

Column	Type	Null	Notes
other_identifier_id	uuid	N	PK
provider_id	uuid	N	FK
identifier	varchar(50)	N
identifier_type_code	varchar(10)	Y
identifier_state	varchar(2)	Y
identifier_issuer	varchar(100)	Y
sequence_no	integer	N
created_at	timestamptz	N

npi.other_name

Column	Type	Null	Notes
other_name_id	uuid	N	PK
provider_id	uuid	N	FK
other_name	varchar(300)	N
other_name_type_code	varchar(5)	Y
created_at	timestamptz	N

npi.practice_location

Column	Type	Null	Notes
practice_location_id	uuid	N	PK
provider_id	uuid	N	FK
address_line_1	text	Y
address_line_2	text	Y
city	text	Y
state	text	Y
postal_code	text	Y
country_code	text	Y
telephone	text	Y
telephone_extension	text	Y
fax	text	Y
created_at	timestamptz	N

npi.provider

Column	Type	Null	Notes
provider_id	uuid	N	PK
npi	varchar(10)	N
entity_type_code	smallint	Y
first_name	varchar(100)	Y
last_name	varchar(100)	Y
middle_name	varchar(100)	Y
name_prefix	varchar(10)	Y
name_suffix	varchar(10)	Y
credential	varchar(50)	Y
gender	varchar(1)	Y
organization_name	varchar(300)	Y
organization_subpart	boolean	Y
parent_organization_lbn	varchar(300)	Y
parent_organization_tin	varchar(15)	Y
authorized_official	jsonb	Y
enumeration_date	date	Y
last_update_date	date	Y
deactivation_date	date	Y
reactivation_date	date	Y
certification_date	date	Y
is_active	boolean	N
is_sole_proprietor	boolean	Y
created_at	timestamptz	N
updated_at	timestamptz	N
replacement_npi	varchar(10)	Y
ein	varchar(9)	Y
other_organization_name	text	Y
other_organization_name_type_code	text	Y
other_last_name	text	Y
other_first_name	text	Y
other_middle_name	text	Y
other_name_prefix	text	Y
other_name_suffix	text	Y
other_credential	text	Y
other_last_name_type_code	text	Y
deactivation_reason_code	varchar(2)	Y
gender_code	varchar(1)	Y
search_vector	tsvector	Y

npi.taxonomy

Column	Type	Null	Notes
taxonomy_id	uuid	N	PK
provider_id	uuid	N	FK
taxonomy_code	varchar(15)	N
license_number	varchar(50)	Y
license_state	varchar(2)	Y
is_primary	boolean	N
sequence_no	integer	N
created_at	timestamptz	N

npi.taxonomy_code

Column	Type	Null	Notes
code	varchar(10)	N	PK
grouping	varchar(100)	Y
classification	varchar(100)	Y
specialization	varchar(100)	Y
definition	text	Y
display_name	varchar(200)	Y
section	varchar(50)	Y
search_vector	tsvector	Y

npi.taxonomy_group

Column	Type	Null	Notes
taxonomy_group_id	uuid	N	PK
provider_id	uuid	N	FK
sequence_no	integer	N
taxonomy_group_code	text	Y
created_at	timestamptz	N