Drug Database Schema Diagram

OverviewCopy Anchor Link

This document provides a comprehensive schema diagram for the drug database system in the Kulcare application. The system supports multiple countries (IN, US, AE) and maintains backward compatibility with the legacy basic_medicine system.

Core Drug System ArchitectureCopy Anchor Link

Detailed Entity-Relationship DiagramCopy Anchor Link

Core Drug TablesCopy Anchor Link

1. Drugs Table (drugs)Copy Anchor Link

Primary Key: id (bigint)

Purpose: Central table for storing comprehensive drug information across all supported countries.

Unique Constraint: name + manufacturer + med_type + alpha_2_code

Indexes:

• name (for searching by drug name)
• alpha_2_code (for country filtering)
• manufacturer (for manufacturer filtering)
• snomed_code (for SNOMED CT coding)
• country_specific_code (for country-specific codes)
• ndc_code (for US NDC codes)
• atc_code (for ATC classification)
• therapeutic_class (for therapeutic category filtering)
• coding_status + alpha_2_code (for coding status by country)
• alpha_2_code + is_discontinued (for active drugs by country)
• Composite unique: name + manufacturer + med_type + alpha_2_code

---

2. Drug Manufacturers Table (drug_manufacturers)Copy Anchor Link

Primary Key: id (bigint)

Purpose: Stores detailed information about drug manufacturers with country-specific regulatory data.

Unique Constraint: name + country_code

Indexes:

• name (for manufacturer lookup)
• country_code (for country filtering)
• name + country_code (unique composite)
• is_active (for active manufacturers)

---

3. Drug Interactions Table (drug_interactions)Copy Anchor Link

Primary Key: id (bigint)

Purpose: Records interactions between drugs with severity levels and clinical management guidance.

Unique Constraint: drug_id + interacting_drug_id

Relationships:

• drug_id → drugs.id (primary drug)
• interacting_drug_id → drugs.id (interacting drug - self-reference)

Indexes:

• drug_id (for finding interactions of a drug)
• interacting_drug_id (for reverse lookup)
• interaction_type (for filtering by interaction type)
• severity_level (for filtering by severity)
• evidence_level (for filtering by evidence quality)
• Composite unique: drug_id + interacting_drug_id

---

4. Drug Alternatives Table (drug_alternatives)Copy Anchor Link

Primary Key: id (bigint)

Purpose: Maintains alternative/substitute drug relationships with preference levels.

Unique Constraint: primary_drug_id + alternative_drug_id

Relationships:

• primary_drug_id → drugs.id (primary drug)
• alternative_drug_id → drugs.id (alternative drug)

Indexes:

• primary_drug_id (for finding alternatives of a drug)
• alternative_drug_id (for reverse lookup)
• alternative_type (for filtering by alternative type)
• substitution_level (for filtering by substitution level)
• is_preferred (for finding preferred alternatives)
• is_active (for active alternatives)
• Composite unique: primary_drug_id + alternative_drug_id

---

5. Drug Formularies Table (drug_formularies)Copy Anchor Link

Primary Key: id (bigint)

Purpose: Manages insurance coverage and formulary status for drugs across different insurance providers.

Unique Constraint: drug_id + insurance_provider_id

Relationships:

• drug_id → drugs.id (foreign key to drugs)
• insurance_provider_id → insurance_providers.id (foreign key to insurance providers)

Indexes:

• drug_id (for finding formularies for a drug)
• insurance_provider_id (for finding drugs in a formulary)
• formulary_tier (for filtering by tier)
• is_covered (for filtering covered drugs)
• is_active (for active formularies)
• effective_date (for time-based queries)
• Composite unique: drug_id + insurance_provider_id

---

Tables with Drug AssociationsCopy Anchor Link

The following tables reference the drugs table through drug_id and maintain backward compatibility with basic_medicine_id:

Medicine Reference TablesCopy Anchor Link

1. doctor_medicines - Doctor-specific medicine preferences and templates
2. patient_medicines - Medications prescribed to patients with dosage schedules
3. prescription_medicines - Medicines in specific prescriptions
4. prescription_template_medicines - Reusable prescription templates
5. surgery_case_file_medicines - Medications for surgery cases
6. pharmacy_order_medicines - Medicines in pharmacy orders

Common Pattern: All these tables support dual references:

• drug_id → drugs.id (new system)
• basic_medicine_id → basic_medicines.id (legacy system)
• Priority: drug_id takes precedence over basic_medicine_id

---

Complete Entity-Relationship DiagramCopy Anchor Link

---

Migration Path & Backward CompatibilityCopy Anchor Link

Dual Reference SystemCopy Anchor Link

The database supports a dual reference system to ensure smooth migration from basic_medicine_id to drug_id:

Medicine Reference Tables:Copy Anchor Link

• doctor_medicines
• patient_medicines
• prescription_medicines
• prescription_template_medicines
• surgery_case_file_medicines
• pharmacy_order_medicines

Reference Logic:Copy Anchor Link

1. New System: Use drug_id (references drugs.id)
2. Legacy System: Use basic_medicine_id (references basic_medicines.id)
3. Priority: drug_id takes precedence over basic_medicine_id
4. Migration: Gradual migration from basic_medicine_id to drug_id

Interactor Pattern:Copy Anchor Link

All business logic interactors handle both reference types:

• Check for drug_id first
• Fall back to basic_medicine_id if drug_id is null
• Maintain data consistency during migration period

---

Index StrategyCopy Anchor Link

Performance-Critical IndexesCopy Anchor Link

1. Drug Search:

   • drugs.name (primary search field)
   • drugs.alpha_2_code (country filtering)
   • drugs.manufacturer (manufacturer filtering)

2. Drug Coding:

   • drugs.snomed_code (SNOMED CT codes)
   • drugs.country_specific_code (country-specific regulatory codes)
   • drugs.ndc_code (US National Drug Codes)
   • drugs.atc_code (Anatomical Therapeutic Chemical classification)

3. Formulary Management:

   • drug_formularies.drug_id (find coverage for a drug)
   • drug_formularies.insurance_provider_id (find drugs in a formulary)
   • drug_formularies.formulary_tier (filter by tier)

4. Drug Interactions:

   • drug_interactions.drug_id (find interactions for a drug)
   • drug_interactions.severity_level (filter by severity)

5. Medicine References:

   • All medicine tables have drug_id indexes for JOIN operations

---

Data Integrity ConstraintsCopy Anchor Link

Unique ConstraintsCopy Anchor Link

1. Drugs: (name, manufacturer, med_type, alpha_2_code) - Ensures no duplicate drugs per country
2. Drug Interactions: (drug_id, interacting_drug_id) - Prevents duplicate interaction records
3. Drug Alternatives: (primary_drug_id, alternative_drug_id) - Prevents duplicate alternative relationships
4. Drug Formularies: (drug_id, insurance_provider_id) - One formulary entry per drug per insurer
5. Drug Manufacturers: (name, country_code) - Unique manufacturer per country

Foreign Key RelationshipsCopy Anchor Link

• All drug_id fields reference drugs.id
• All basic_medicine_id fields reference basic_medicines.id (legacy)
• Cascade delete configured for related records
• Null allowed for backward compatibility

---

Multi-Country SupportCopy Anchor Link

Country-Specific DataCopy Anchor Link

• alpha_2_code: 'IN' (India), 'US' (United States), 'AE' (UAE)
• country_regulatory_data: JSONB field for country-specific regulatory information
• coding_status + alpha_2_code: Track terminology coding progress by country

Country-Specific IndexesCopy Anchor Link

• drugs.alpha_2_code for filtering by country
• drugs.therapeutic_class + alpha_2_code for therapeutic categories by country
• drug_manufacturers.country_code for manufacturers by country

---

Elasticsearch IntegrationCopy Anchor Link

Searchable Drug AttributesCopy Anchor Link

The drugs table is indexed in Elasticsearch for fast search:

• Name (autocomplete, fuzzy search)
• Manufacturer (exact match, filtering)
• Therapeutic class (faceted search)
• Salt composition (composition matching)
• SNOMED codes (medical terminology search)
• Country-specific codes (regulatory search)

Reindexing StrategyCopy Anchor Link

• Full reindex: rake es:reindex_drugs
• Incremental updates via DrugSyncJob
• Real-time sync on drug creation/update

---

Summary StatisticsCopy Anchor Link

• Core Drug Tables: 5 tables (drugs, drug_manufacturers, drug_interactions, drug_alternatives, drug_formularies)
• Tables with Drug Associations: 6 medicine tables reference the drugs system
• Total System Tables: 11 tables (5 core + 6 associated)
• Primary Drug Fields: 80+ fields in drugs table covering comprehensive drug information
• Indexes: 30+ indexes for performance optimization across all drug tables
• Relationships: Self-referencing (interactions, alternatives) and cross-domain (medicine tables)
• Countries Supported: 3 (IN, US, AE) with country-specific regulatory data
• Backward Compatibility: Dual reference system with legacy basic_medicine support

---

Maintenance NotesCopy Anchor Link

Regular TasksCopy Anchor Link

1. Terminology Coding: Assign SNOMED/RxNorm codes to new drugs
2. Interaction Updates: Refresh drug interaction database quarterly
3. Formulary Updates: Update insurance formularies monthly
4. Data Sync: Sync with external APIs (FDA, CDSCO, MOHAP)
5. Index Maintenance: Rebuild Elasticsearch indexes after bulk imports

Data QualityCopy Anchor Link

• Monitor coding_status for incomplete terminology coding
• Track coding_confidence scores for data quality assessment
• Clean up discontinued drugs quarterly
• Archive old interaction data annually