Background & Context

Purpose: Document the current state, limitations, and rationale for the new schema design

Current State AnalysisCopy Anchor Link

Database StatisticsCopy Anchor Link

Current Domain ModulesCopy Anchor Link

The backend uses a parts-based architecture with 13 domain modules:

backend/parts/
├── clinic/          # 66 tables - Clinic operations
├── patient/         # 25 tables - Patient health records
├── doctor/          # 25 tables - Practitioner profiles
├── billing/         # 20 tables - Payment processing
├── hospital/        # 12 tables - Hospital operations (overlaps with clinic)
├── lab/             # 6 tables - Laboratory services
├── pharmacy/        # 5 tables - Pharmacy operations
├── growth/          # 11 tables - Marketing/leads
├── care_coordinator/# 7 tables - Care coordination
├── abdm/            # 15 tables - India healthcare integration
├── auth/            # ~8 tables - Authentication
├── admin/           # ~2 tables - Administration
└── core/            # 46+ tables - Shared reference data

Naming Convention IssuesCopy Anchor Link

1. Duplicate/Conflicting NamesCopy Anchor Link

• careplans AND care_plans (both exist)
• pharmas AND pharmacies (inconsistent)
• goals exists standalone and in patient context

2. Inconsistent AbbreviationsCopy Anchor Link

• msg_* vs message_*
• fb_* vs facebook_*
• gmb_* (Google My Business)
• ip_encounters (IP = inpatient?)

3. Blurred Domain BoundariesCopy Anchor Link

• clinic_patient_* (clinic-scoped) vs patient_* (global)
• clinic_doctor_* vs doctor_*
• Unclear what belongs to which domain

4. Reference Data ScatteredCopy Anchor Link

• basic_* prefix for lookup tables
• Some reference data unprefixed
• No clear separation between global and tenant-specific

Why Change is NeededCopy Anchor Link

1. Facility Model LimitationsCopy Anchor Link

Problem: Everything is a "Clinic"

The current model uses clinics table for all facility types:

• Medical clinics (outpatient)
• Hospitals (inpatient)
• Surgery centers
• Therapy centers (talk therapy, physical therapy)
• Diagnostic centers
• Virtual practices

Limitation: A single is_hospital boolean cannot capture the complexity of facilities with:

• OPD (outpatient department)
• IPD (inpatient department)
• Single OT (operation theater)
• Doctor-owned specialty centers

Impact: UI confusion, reporting limitations, incorrect feature exposure

2. Multi-Tenancy is ImplicitCopy Anchor Link

Current State:

• Tenant isolation via clinic_id foreign keys
• No database-level enforcement
• Risk of cross-tenant data leakage through application bugs

Needed:

• Explicit tenant boundaries
• Row-Level Security (RLS) at PostgreSQL level
• Clear tenant context for every operation

3. FHIR Compliance GapsCopy Anchor Link

Current State:

• Partial FHIR alignment for ABDM (India)
• Organization resource partially mapped
• No formal Location, HealthcareService, PractitionerRole resources

Needed:

• Complete FHIR R4 resource mapping for core entities
• Standardized identifier management
• Cross-border healthcare data exchange capability

4. LHR Architecture Already DefinedCopy Anchor Link

The team has done significant work on Longitudinal Health Record architecture:

Implemented:

• Episode of Care model
• Care Plans with versioning
• Multi-practitioner encounters
• Treatment goals and progress notes
• Bridge pattern for encounter documents

Reference: See Clinical Data Hierarchy

The new schema should preserve and enhance this work.

5. AI-Assisted Care IntegrationCopy Anchor Link

Current State:

• AI Scribe with transcription sessions
• AI suggestions linked to appointments
• LLM call logging for audit

Needed:

• Proper FHIR linkage for AI suggestions
• Clear separation of clinical AI data vs audit logs
• Research/analytics data model

6. Multi-Country ExpansionCopy Anchor Link

Current Markets:

• India (IN) - ABDM/ABHA, DPDP, GST
• UAE (AE) - DHA, VAT
• USA (US) - HIPAA, insurance

Planned Expansion:

• Saudi Arabia (SA) - MOH, NPHIES
• Qatar (QA) - MOPH
• Kuwait (KW) - MOH

Needed:

• Country-agnostic core with country-specific compliance modules
• Flexible identifier systems
• Regional configuration patterns

FHIR R4 Alignment RequirementsCopy Anchor Link

Core Resources to MapCopy Anchor Link

FHIR Adoption MandatesCopy Anchor Link

Multi-Country Regulatory LandscapeCopy Anchor Link

India (IN)Copy Anchor Link

UAE (AE)Copy Anchor Link

USA (US)Copy Anchor Link

GCC ExpansionCopy Anchor Link

Existing LHR WorkCopy Anchor Link

The new schema must support the clinical architecture already designed:

Clinical Data HierarchyCopy Anchor Link

Patient
   |
   +-- LHR (Longitudinal Health Record) <-- Computed, not stored
   |       |
   |       +-- Problems, Medications, Vitals, Assessments
   |       +-- Encounter History (grouped by Episode)
   |       +-- Health Score (computed)
   |
   +-- Episodes of Care <-- Treatment program containers
   |       |
   |       +-- Episode: "Post-ACL Rehab"
   |       |       +-- Treatment Goals
   |       |       +-- Progress Notes
   |       |       +-- Encounters (sessions)
   |       |
   |       +-- Episode: "Anxiety Treatment"
   |               +-- Treatment Goals
   |               +-- Safety Plans
   |               +-- Encounters (therapy sessions)
   |
   +-- Standalone Encounters
           +-- One-off visits, acute care

Key Patterns to PreserveCopy Anchor Link

1. LHR as Computed View - Aggregated from FHIR resources, not stored
2. Episode of Care - FHIR-aligned treatment program container
3. Bridge Pattern - EncounterDocument links to CarePlan, Goal, EpisodeOfCare
4. Document Immutability - Signed documents are immutable, amendments chain
5. CarePlan Versioning - Versions track via replaces_id

Reference DocumentationCopy Anchor Link

• Clinical Data Hierarchy
• Multi-Practitioner Encounter Types
• Episode of Care
• LHR Components Audit

SummaryCopy Anchor Link

The new schema design addresses: