General
Kulcare Next-Generation Database Schema
Status: Analysis & Design Phase Created: 2025-12-28 Purpose: Greenfield database architecture for multi-tenant, FHIR-compliant healthcare platform
Executive Summary
This documentation describes a proposed greenfield database architecture that addresses the limitations of the current "clinic-centric" data model. The new schema is designed to support:
- Multi-tenancy with proper isolation using PostgreSQL Row-Level Security (RLS)
- Domain-aware organization using PostgreSQL schemas for logical separation
- FHIR R4 compliance for healthcare data interoperability
- Longitudinal Health Record (LHR) with Episode of Care and coordinated care
- AI-assisted care with clinical decision support integration
- Multi-country compliance for India, UAE, USA, and GCC region
- Enterprise healthcare benefits with ICHRA support and provider empanelment
- Pharmaceutical relations in isolated database for compliance
- Offline mode support with sync tracking and conflict resolution
- Federated deployment with on-premise installations syncing to central cloud
Problem Statement
Current Limitations
The existing database has evolved organically with several architectural challenges:
| Issue | Impact |
|---|---|
| Clinic-centric model | All facilities are "clinics" even when they're hospitals, therapy centers, or surgery centers |
| 501 tables in single schema | No logical domain separation, difficult to reason about |
| Inconsistent naming | Mix of prefixes (basic_*, clinic_*), unprefixed tables, duplicate names |
| Implicit multi-tenancy | Tenant isolation via clinic_id without formal enforcement |
| Limited facility classification | Boolean is_hospital flag cannot capture OPD+IPD+OT specialty centers |
Target State
A clean, domain-organized architecture where:
- Facilities are properly classified by service level, ownership, and specialty scope
- Clinical data follows FHIR R4 resource patterns
- Domains are separated into PostgreSQL schemas
- Multi-tenancy is enforced at the database level
- AI-assisted care integrates seamlessly with clinical workflows
Design Principles
- FHIR-First: Core clinical entities map to FHIR R4 resources
- Domain Boundaries: Clear separation between clinical, billing, growth, and identity domains
- Tenant Isolation: Row-Level Security for data protection
- Reference Data Sharing: Global lookup tables without tenant scope
- LHR as Computed View: Longitudinal Health Record aggregated from FHIR resources, not stored
- Extensibility: Support for new countries, facility types, and clinical workflows
Document Index
Foundation Documents
| Document | Description |
|---|---|
| Background | Current state analysis, why change is needed |
| Architectural Decisions | Key design decisions in ADR format |
| Schema Overview | Visual architecture and schema relationships |
Schema Definitions
| Schema | Document | Purpose |
|---|---|---|
reference | Reference Schema | Global lookup tables, medical coding |
identity | Identity Schema | Who provides what, where: tenants, organizations, facilities, providers, services, packages |
clinical | Clinical Schema | Patients, encounters, care plans, AI suggestions |
billing | Billing Schema | Invoices, payments, subscriptions, insurance claims |
growth | Growth Schema | Campaigns, leads, communications |
connect | Connect Schema | Professional networking, job board (cross-tenant) |
enterprise | Enterprise Schema | Employer benefits, empanelment, wellness (cross-tenant) |
integration | Integration Schema | ABDM, webhooks, API logs |
audit | Audit Schema | Activity logs, PHI access, LLM logs |
federation | Federation Schema | On-premise sync, patient identity federation |
Separate Databases
| Database | Document | Purpose |
|---|---|---|
pharma_db | Pharma Database | Pharmaceutical relations, campaigns, clinical trials |
Feature Documentation
| Document | Description |
|---|---|
| Clinical Features | LHR, Episode of Care, Coordinated Care, AI-Assisted Care |
| Internationalization | Multi-country compliance (IN, AE, US, GCC) |
| Offline Mode | Offline sync schema design, conflict resolution |
| Federated Deployment | On-premise installations, patient portal integration |
| Notifications | Shared notification system (SMS, push, WhatsApp, Slack) |
| Teleconsultation | Video sessions, participant management, teleconsultation notifications |
| Migration Considerations | High-level migration strategy |
Patient Self-Service
| Document | Description |
|---|---|
| Patient Self-Service Signup | Architecture, API contracts, patient-clinic linking, and Identity integration |
| Patient Signup Testing Guide | Testing guide for registration, authentication, and clinic linking |
Migration Execution
| Document | Description |
|---|---|
| Legacy Prescription Cleanup | Removal of old appointment-based prescription feature from frontend CMS |
Schema Architecture Overview
+-------------------------------------------------------------------------+
| KULCARE DATABASE |
+-------------------------------------------------------------------------+
| |
| +-------------------------------------------------------------------+ |
| | REFERENCE SCHEMA (Global - No tenant_id) | |
| | specialties, drugs, lab_tests, procedures, icd_codes, countries | |
| +-------------------------------------------------------------------+ |
| | |
| v |
| +-------------------------------------------------------------------+ |
| | IDENTITY SCHEMA (Who provides what, where) | |
| | tenants, organizations, facilities, practitioners, | |
| | vendors, healthcare_services, service_packages | |
| +-------------------------------------------------------------------+ |
| | | | | |
| v v v v |
| +-------------+ +-------------+ +-------------+ +-------------+ |
| | CLINICAL | | BILLING | | GROWTH | | CONNECT | |
| | SCHEMA | | SCHEMA | | SCHEMA | | SCHEMA | |
| +-------------+ +-------------+ +-------------+ +-------------+ |
| | patients | | invoices | | campaigns | | profiles | |
| | encounters | | payments | | leads | | jobs | |
| | care_plans | | insurance | | communic- | | connections | |
| | medications | | claims | | ations | | messages | |
| +-------------+ +-------------+ +-------------+ +-------------+ |
| |
| +-------------------------------------------------------------------+ |
| | ENTERPRISE SCHEMA (Cross-tenant) | |
| | employers, employees, benefit_plans, empaneled_providers | |
| +-------------------------------------------------------------------+ |
| |
| +-------------------------------------------------------------------+ |
| | INTEGRATION SCHEMA | AUDIT SCHEMA | |
| | abdm_records, webhooks | activity_logs, phi_access, llm_logs| |
| +-------------------------------------------------------------------+ |
| |
+-------------------------------------------------------------------------+
+-------------------------------------------------------------------------+
| PHARMA DATABASE (Separate) |
+-------------------------------------------------------------------------+
| pharma_companies, campaigns, doctor_engagements, clinical_trials, |
| disclosure_records, gift_tracking (API sync with Kulcare) |
+-------------------------------------------------------------------------+Related Documentation
- LHR Architecture - Clinical data hierarchy
- Episode of Care - Treatment program design
- Multi-Practitioner Encounters - Encounter document model
Terminology
| Term | Definition |
|---|---|
| Tenant | Top-level isolation boundary (usually an organization) |
| Organization | Legal entity that provides healthcare (FHIR: Organization) |
| Facility | Physical location where services are delivered (replaces "Clinic") |
| Practitioner | Licensed healthcare provider (doctor, nurse, therapist) |
| Vendor | Unlicensed service provider (yoga instructor, lamaze educator) |
| LHR | Longitudinal Health Record - computed aggregate view of patient health |
| Episode of Care | Treatment program container spanning multiple visits |
| Encounter | Single visit/session between patient and practitioner |
| Employer | Company/organization providing healthcare benefits (B2B customer) |
| ICHRA | Individual Coverage Health Reimbursement Arrangement |
| Empanelment | Contracted provider relationship with an employer |
| Service Package | Bundled offering of healthcare and vendor services |
| RLS | Row-Level Security - PostgreSQL feature for tenant isolation |
| Cross-tenant | Data accessible across multiple tenants (Connect, Enterprise schemas) |
| Pharma DB | Separate database for pharmaceutical company relations |