Reference Schema

Purpose: Global lookup tables and catalog data shared across all tenants Tenant Scoped: No RLS Enabled: No

Overview

The reference schema contains platform-managed data that is shared globally. This includes medical coding systems, drug catalogs, geographic data, and facility type definitions.

No tables in this schema have a tenant_id column - all data is available to all tenants.

Entities

Medical Coding

Entity: IcdCode

Purpose: International Classification of Diseases codes for diagnosis coding

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
code	String	Yes	ICD code (e.g., "E11.9")
version	String	Yes	ICD version (ICD-10, ICD-11)
description	Text	Yes	Code description
category	String	No	Grouping category
is_active	Boolean	Yes	Active status

Relationships:

Referenced by: clinical.conditions

Entity: SnomedConcept

Purpose: SNOMED CT concepts for clinical terminology

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
concept_id	BigInt	Yes	SNOMED concept ID
term	Text	Yes	Preferred term
semantic_tag	String	No	Semantic category
is_active	Boolean	Yes	Active status

Relationships:

Referenced by: clinical.conditions, clinical.observations

Drug Catalog System

The drug catalog is a comprehensive system supporting multi-country formularies, drug interactions, alternatives, and AI-powered clinical decision support.

Entity: Drug

Purpose: Central drug catalog for prescriptions with multi-country support

Attributes:

Core Identity:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
name	String	Yes	Brand/trade name
trade_name	String	No	Alternative trade name
generic_name	String	No	Generic/salt composition
manufacturer_id	UUID	No	Reference to DrugManufacturer
manufacturer_name	String	No	Denormalized manufacturer name

Country and Regulatory:

Attribute	Type	Required	Description
alpha_2_code	String	Yes	Country code (IN, US, AE)
prescription_type	Enum	No	prescription, otc, controlled
schedule	String	No	Drug schedule (H, H1, X for India)
dea_schedule	String	No	DEA schedule (I-V for US)
is_controlled	Boolean	Yes	Controlled substance flag
is_discontinued	Boolean	Yes	Discontinued status
discontinued_date	Date	No	When discontinued
country_regulatory_data	JSONB	No	Country-specific regulatory info

Classification:

Attribute	Type	Required	Description
med_type	String	No	Medication type
category	String	No	Drug category
therapeutic_class	String	No	Therapeutic classification
pharmacological_class	String	No	Pharmacological classification
primary_use	String	No	Primary indication

Dosage Information:

Attribute	Type	Required	Description
dosage_form	String	No	tablet, capsule, syrup, injection
strength	String	No	Strength (e.g., "500mg")
unit	String	No	Unit of measure
route_of_administration	String	No	oral, iv, im, topical
pack_size_label	String	No	Package size description
package_unit	String	No	Package unit

Medical Coding:

Attribute	Type	Required	Description
snomed_code	String	No	SNOMED CT code
rxcui	String	No	RxNorm CUI (US)
ndc_code	String	No	National Drug Code (US)
atc_code	String	No	ATC classification code
country_specific_code	String	No	CDSCO (IN), DHA (AE)
external_id	String	No	External system ID

Safety Information:

Attribute	Type	Required	Description
warnings	Text	No	Drug warnings
precautions	Text	No	Usage precautions
contraindications	Text	No	Contraindication list
black_box_warning	Text	No	FDA black box warning
common_side_effects	Text	No	Common side effects
food_interactions	Text	No	Food interaction info
alcohol_interaction	String	No	Alcohol interaction level
pregnancy_interaction	String	No	Pregnancy category
pregnancy_category	String	No	FDA pregnancy category (A-X)
lactation_interaction	String	No	Lactation safety
driving_interaction	String	No	Driving impairment
kidney_interaction	String	No	Renal dosing notes
liver_interaction	String	No	Hepatic dosing notes
pediatric_use	Text	No	Pediatric safety info
geriatric_use	Text	No	Geriatric considerations

Clinical Guidance:

Attribute	Type	Required	Description
description	Text	No	Drug description
introduction	Text	No	Introduction text
benefits	Text	No	Therapeutic benefits
how_to_use	Text	No	Usage instructions
how_it_works	Text	No	Mechanism of action
storage_instructions	Text	No	Storage requirements
storage_temperature	String	No	Temperature range
overdosage	Text	No	Overdose management

Pricing (Optional):

Attribute	Type	Required	Description
price	Decimal	No	MRP/retail price
price_per_unit	Decimal	No	Price per unit
purchase_price	Decimal	No	Wholesale price
currency	String	No	Currency code (INR, USD, AED)

Data Quality and Sync:

Attribute	Type	Required	Description
coding_status	Enum	No	pending, coded, verified, failed
coding_confidence	Float	No	AI coding confidence (0-1)
last_coded_at	Timestamp	No	Last coding timestamp
coding_source	String	No	Coding source (manual, ai, api)
data_source	String	No	Primary data source
external_api_id	String	No	External API identifier
last_synced_at	Timestamp	No	Last API sync
sync_active	Boolean	No	Active sync status

AI and Analytics Fields (Future):

Attribute	Type	Required	Description
embedding_vector	Vector	No	Semantic search embedding
ai_summary	Text	No	AI-generated drug summary
ai_summary_updated_at	Timestamp	No	When AI summary was generated
prescription_frequency_rank	Integer	No	Popularity ranking
clinical_effectiveness_score	Float	No	Efficacy score (0-1)
cost_effectiveness_tier	String	No	Cost-effectiveness category

Pharma Integration Fields (Future):

Attribute	Type	Required	Description
pharma_company_id	UUID	No	Link to pharma_db company
is_promoted	Boolean	No	Currently being promoted
promotion_priority	Integer	No	Promotion display priority
sample_available	Boolean	No	Sample availability
detailing_enabled	Boolean	No	Digital detailing enabled
clinical_trial_ids	JSONB	No	Associated clinical trials

Timestamps:

Attribute	Type	Required	Description
created_at	Timestamp	Yes	Record creation
updated_at	Timestamp	Yes	Last update

Unique Constraint: (name, manufacturer_name, med_type, alpha_2_code)

Indexes:

name (text search)
generic_name (text search)
alpha_2_code
manufacturer_id
snomed_code
rxcui
ndc_code
atc_code
therapeutic_class
(alpha_2_code, is_discontinued)
(coding_status, alpha_2_code)
embedding_vector (vector similarity search)

Relationships:

Belongs to: DrugManufacturer (optional)
Has many: DrugInteractions, DrugAlternatives
Referenced by: clinical.medication_requests

Entity: DrugManufacturer

Purpose: Drug manufacturer/pharmaceutical company registry

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
name	String	Yes	Company name
country_code	String	Yes	Primary country (IN, US, AE)
address	Text	No	Corporate address
phone	String	No	Contact phone
email	String	No	Contact email
website	String	No	Company website
license_number	String	No	Manufacturing license
license_expiry	Date	No	License expiration
is_active	Boolean	Yes	Active status
regulatory_info	JSONB	No	Country-specific regulatory data
pharma_company_id	UUID	No	Link to pharma_db (future)
created_at	Timestamp	Yes	Record creation
updated_at	Timestamp	Yes	Last update

Unique Constraint: (name, country_code)

Indexes:

name
country_code
is_active

Relationships:

Has many: Drugs

Entity: DrugInteraction

Purpose: Drug-drug interaction database for safety alerts

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
drug_id	UUID	Yes	Primary drug
interacting_drug_id	UUID	Yes	Interacting drug
interaction_type	Enum	No	pharmacokinetic, pharmacodynamic, combined
severity_level	Enum	Yes	contraindicated, major, moderate, minor
description	Text	No	Interaction description
clinical_effects	Text	No	Clinical manifestation
management	Text	No	Management recommendations
mechanism	Text	No	Interaction mechanism
onset	String	No	rapid, delayed, variable
evidence_level	Enum	No	established, probable, suspected, theoretical
documentation_level	String	No	excellent, good, fair, poor
data_source	String	No	Reference source
ai_verified	Boolean	No	AI verification flag
ai_confidence	Float	No	AI confidence score
created_at	Timestamp	Yes	Record creation
updated_at	Timestamp	Yes	Last update

Severity Levels:

Level	Description	Action Required
`contraindicated`	Never use together	Block prescription
`major`	Significant risk	Require override with reason
`moderate`	Monitor closely	Show warning
`minor`	Minimal risk	Informational alert

Unique Constraint: (drug_id, interacting_drug_id)

Indexes:

drug_id
interacting_drug_id
severity_level
(drug_id, severity_level)

Relationships:

Belongs to: Drug (both drug_id and interacting_drug_id)

Entity: DrugAlternative

Purpose: Therapeutic alternatives and generic substitutions

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
primary_drug_id	UUID	Yes	Original drug
alternative_drug_id	UUID	Yes	Alternative drug
alternative_type	Enum	Yes	generic, therapeutic, brand
substitution_level	Enum	No	equivalent, similar, related
is_preferred	Boolean	No	Preferred alternative
is_bioequivalent	Boolean	No	Bioequivalence confirmed
cost_comparison	String	No	cheaper, similar, expensive
notes	Text	No	Substitution notes
is_active	Boolean	Yes	Active relationship
ai_recommended	Boolean	No	AI-suggested alternative
ai_confidence	Float	No	AI recommendation confidence
created_at	Timestamp	Yes	Record creation
updated_at	Timestamp	Yes	Last update

Alternative Types:

Type	Description
`generic`	Generic equivalent (same salt)
`therapeutic`	Same therapeutic class
`brand`	Different brand, same formulation

Unique Constraint: (primary_drug_id, alternative_drug_id)

Indexes:

primary_drug_id
alternative_drug_id
alternative_type
is_preferred

Relationships:

Belongs to: Drug (both primary and alternative)

Entity: DrugConditionAssociation (Future/AI)

Purpose: AI-powered drug-condition associations for clinical decision support

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
drug_id	UUID	Yes	Drug reference
condition_code	String	Yes	ICD/SNOMED code
condition_name	String	Yes	Condition name
association_type	Enum	Yes	indication, contraindication, off_label
evidence_level	Enum	No	strong, moderate, weak, anecdotal
first_line	Boolean	No	First-line treatment
dosage_guidance	JSONB	No	Condition-specific dosing
efficacy_score	Float	No	Effectiveness score (0-1)
source	String	No	Guidelines/evidence source
ai_generated	Boolean	No	AI-derived association
ai_confidence	Float	No	AI confidence score
last_reviewed_at	Timestamp	No	Last clinical review
created_at	Timestamp	Yes	Record creation
updated_at	Timestamp	Yes	Last update

Dosage Guidance Structure:

{
  "adult": {
    "initial": "500mg once daily",
    "maintenance": "500-1000mg daily",
    "max_daily": "2000mg"
  },
  "pediatric": {
    "weight_based": "10-15mg/kg/day",
    "max_daily": "500mg"
  },
  "renal_adjustment": {
    "mild": "No adjustment",
    "moderate": "Reduce by 50%",
    "severe": "Avoid use"
  }
}

Relationships:

Belongs to: Drug
References: IcdCode, SnomedConcept

Entity: DrugAllergyGroup (Future/AI)

Purpose: Cross-reactivity groups for allergy checking

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
group_name	String	Yes	Allergy group name
group_type	Enum	Yes	class, structure, ingredient
description	Text	No	Group description
cross_reactivity_risk	Enum	No	high, moderate, low
member_drug_ids	UUID[]	No	Drugs in this group
is_active	Boolean	Yes	Active status
created_at	Timestamp	Yes	Record creation
updated_at	Timestamp	Yes	Last update

Example Groups:

Penicillins (class)
Sulfonamides (structure)
Beta-lactams (class with cross-reactivity to cephalosporins)

Relationships:

Has many: Drugs (via member_drug_ids)

Other Catalogs

Entity: LabTest

Purpose: Laboratory test catalog

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
code	String	Yes	Test code
name	String	Yes	Test name
loinc_code	String	No	LOINC standard code
category	String	No	Test category (hematology, biochemistry)
specimen_type	String	No	Required specimen
unit	String	No	Result unit
normal_range	JSONB	No	Reference ranges by demographics
is_active	Boolean	Yes	Active in catalog

Normal Range Structure:

{
  "male": { "min": 4.5, "max": 5.5, "unit": "million/uL" },
  "female": { "min": 4.0, "max": 5.0, "unit": "million/uL" },
  "pediatric": { "min": 4.0, "max": 5.5, "unit": "million/uL" }
}

Relationships:

Referenced by: clinical.service_requests, clinical.diagnostic_reports

Entity: Procedure

Purpose: Medical procedure catalog

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
code	String	Yes	Procedure code
name	String	Yes	Procedure name
cpt_code	String	No	CPT code (US billing)
snomed_code	BigInt	No	SNOMED procedure code
specialty_id	UUID	No	Associated specialty
category	String	No	Procedure category
is_surgical	Boolean	Yes	Requires surgery
is_active	Boolean	Yes	Active in catalog

Relationships:

Belongs to: Specialty (optional)
Referenced by: clinical.service_requests

Specialties

Entity: Specialty

Purpose: Medical specialty hierarchy

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
code	String	Yes	Specialty code
name	String	Yes	Specialty name
name_local	JSONB	No	Localized names
parent_id	UUID	No	Parent specialty (for subspecialties)
snomed_code	BigInt	No	SNOMED specialty code
is_surgical	Boolean	Yes	Surgical specialty
is_active	Boolean	Yes	Active status

Localized Names Structure:

{
  "hi": "हृदय रोग",
  "ar": "طب القلب",
  "es": "Cardiología"
}

Relationships:

Self-referential: parent_id for hierarchy
Referenced by: identity.practitioner_roles, identity.healthcare_services

Facility Classification

Entity: FacilityType

Purpose: Predefined facility type classifications

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
code	String	Yes	Type code
name	String	Yes	Type name
name_local	JSONB	No	Localized names
fhir_code	String	No	HL7 v3-RoleCode mapping
service_level	String	No	Default service level
requires_beds	Boolean	Yes	Typically has beds
requires_ot	Boolean	Yes	Typically has OT
is_active	Boolean	Yes	Active status

Predefined Types:

Code	Name	FHIR Code
HOSP	Hospital	HOSP
CLINIC	Clinic	OF
SURG_CTR	Surgery Center	SU
DIAG_CTR	Diagnostic Center	DX
THERAPY	Therapy Center	RHAB
PHARMACY	Pharmacy	PHARM
LAB	Laboratory	HLAB
VIRTUAL	Virtual Care Site	VR

Relationships:

Referenced by: identity.facilities

Geographic Data

Entity: Country

Purpose: Country reference with compliance flags

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
code	String	Yes	ISO 3166-1 alpha-2
name	String	Yes	Country name
phone_code	String	No	International dialing code
currency_code	String	No	ISO 4217 currency code
compliance_flags	JSONB	No	Country-specific compliance

Compliance Flags Structure:

{
  "abdm_enabled": true,
  "hipaa_required": false,
  "gdpr_required": false,
  "dpdp_required": true,
  "vat_rate": 0,
  "gst_rate": 18
}

Relationships:

Has many: States

Entity: State

Purpose: State/province reference

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
country_id	UUID	Yes	Parent country
code	String	Yes	State code
name	String	Yes	State name

Relationships:

Belongs to: Country
Has many: Cities

Entity: City

Purpose: City reference for location searches

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
state_id	UUID	Yes	Parent state
name	String	Yes	City name
tier	String	No	City tier (metro, tier1, tier2, tier3)

Relationships:

Belongs to: State
Referenced by: identity.facility_locations

Identifier Systems

Entity: IdentifierSystem

Purpose: Registry of identifier systems by country

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
country_id	UUID	No	Country (null = global)
code	String	Yes	System code
name	String	Yes	System name
uri	String	No	FHIR system URI
entity_type	String	Yes	Applies to (practitioner, patient, organization)
validation_regex	String	No	Validation pattern
is_active	Boolean	Yes	Active status

Predefined Systems:

Code	Country	Entity	Example
ABHA	IN	patient	14-digit ABHA number
MCI	IN	practitioner	Medical Council registration
NPI	US	practitioner	10-digit NPI
DEA	US	practitioner	DEA number
EMIRATES_ID	AE	patient	Emirates ID
DHA_LICENSE	AE	practitioner	DHA license number

Relationships:

Belongs to: Country (optional)
Referenced by: identity.organization_identifiers, identity.practitioner_identifiers

Schema Diagram

+---------------+     +---------------+     +---------------+
|   icd_codes   |     |    drugs      |     |   lab_tests   |
+---------------+     +---------------+     +---------------+
                               |
                               v
+---------------+     +---------------+     +---------------+
|  procedures   |---->|  specialties  |<----|  snomed_      |
+---------------+     +---------------+     |  concepts     |
                            |               +---------------+
                            | (parent_id)
                            v
                      +---------------+
                      |  specialties  |
                      +---------------+

+---------------+     +---------------+     +---------------+
|   countries   |---->|    states     |---->|    cities     |
+---------------+     +---------------+     +---------------+
       |
       v
+---------------+     +---------------+
| identifier_   |     | facility_     |
| systems       |     | types         |
+---------------+     +---------------+

Data Governance

Update Frequency

Entity	Update Source	Frequency
icd_codes	WHO releases	Yearly
snomed_concepts	SNOMED International	Quarterly
drugs	National formulary	Monthly
lab_tests	Platform team	As needed
procedures	CPT/platform	Yearly
specialties	Platform team	Rare
countries	ISO updates	Rare
facility_types	Platform team	Rare
identifier_systems	Regulatory	As needed

Access Patterns

Read: All applications, all tenants
Write: Platform admin only
Cache: Heavily cached (rarely changes)

Reference Schema

OverviewCopy Anchor Link

EntitiesCopy Anchor Link

Medical CodingCopy Anchor Link

Entity: IcdCodeCopy Anchor Link

Entity: SnomedConceptCopy Anchor Link

Drug Catalog SystemCopy Anchor Link

Entity: DrugCopy Anchor Link

Entity: DrugManufacturerCopy Anchor Link

Entity: DrugInteractionCopy Anchor Link

Entity: DrugAlternativeCopy Anchor Link

Entity: DrugConditionAssociation (Future/AI)Copy Anchor Link

Entity: DrugAllergyGroup (Future/AI)Copy Anchor Link

Other CatalogsCopy Anchor Link

Entity: LabTestCopy Anchor Link

Entity: ProcedureCopy Anchor Link

SpecialtiesCopy Anchor Link

Entity: SpecialtyCopy Anchor Link

Facility ClassificationCopy Anchor Link

Entity: FacilityTypeCopy Anchor Link

Geographic DataCopy Anchor Link

Entity: CountryCopy Anchor Link

Entity: StateCopy Anchor Link

Entity: CityCopy Anchor Link

Identifier SystemsCopy Anchor Link

Entity: IdentifierSystemCopy Anchor Link

Schema DiagramCopy Anchor Link

Data GovernanceCopy Anchor Link

Update FrequencyCopy Anchor Link

Access PatternsCopy Anchor Link

On this page

Overview

Entities

Medical Coding

Entity: IcdCode

Entity: SnomedConcept

Drug Catalog System

Entity: Drug

Entity: DrugManufacturer

Entity: DrugInteraction

Entity: DrugAlternative

Entity: DrugConditionAssociation (Future/AI)

Entity: DrugAllergyGroup (Future/AI)

Other Catalogs

Entity: LabTest

Entity: Procedure

Specialties

Entity: Specialty

Facility Classification

Entity: FacilityType

Geographic Data

Entity: Country

Entity: State

Entity: City

Identifier Systems

Entity: IdentifierSystem

Schema Diagram

Data Governance

Update Frequency

Access Patterns