Schema Architecture Overview

Purpose: Visual architecture and relationships between database schemas

Schema Landscape

+-----------------------------------------------------------------------------+
|                            KULCARE DATABASE                                 |
+-----------------------------------------------------------------------------+
|                                                                             |
|  +-----------------------------------------------------------------------+  |
|  |  REFERENCE SCHEMA                                                    |  |
|  |  (Global - No tenant_id)                                             |  |
|  |                                                                       |  |
|  |  +-------------+  +-------------+  +-------------+  +-------------+  |  |
|  |  | specialties |  | drugs       |  | lab_tests   |  | procedures  |  |  |
|  |  +-------------+  +-------------+  +-------------+  +-------------+  |  |
|  |  +-------------+  +-------------+  +-------------+  +-------------+  |  |
|  |  | drug_       |  | drug_       |  | drug_       |  | drug_cond_  |  |  |
|  |  | manufacturers| | interactions|  | alternatives|  | associations|  |  |
|  |  +-------------+  +-------------+  +-------------+  +-------------+  |  |
|  |  +-------------+  +-------------+  +-------------+  +-------------+  |  |
|  |  | icd_codes   |  | snomed_     |  | countries   |  | facility_   |  |  |
|  |  |             |  | concepts    |  | states      |  | types       |  |  |
|  |  +-------------+  +-------------+  +-------------+  +-------------+  |  |
|  +-----------------------------------------------------------------------+  |
|                                      |                                      |
|                                      v                                      |
|  +-----------------------------------------------------------------------+  |
|  |  IDENTITY SCHEMA                                                     |  |
|  |  (Who provides what, where)                                         |  |
|  |                                                                       |  |
|  |  +-------------+       +----------------+       +---------------+    |  |
|  |  |   tenants   |------>| organizations  |------>|  facilities   |    |  |
|  |  +-------------+       +----------------+       +---------------+    |  |
|  |         |                      |                       |             |  |
|  |         v                      v                       v             |  |
|  |  +-------------+       +----------------+       +---------------+    |  |
|  |  |    users    |------>| practitioners  |------>| practitioner_ |    |  |
|  |  +-------------+       +----------------+       | roles         |    |  |
|  |                                                 +---------------+    |  |
|  |  +----------------+    +----------------+       +---------------+    |  |
|  |  | vendors        |    | healthcare_    |       | service_      |    |  |
|  |  | (unlicensed)   |    | services       |       | packages      |    |  |
|  |  +----------------+    +----------------+       +---------------+    |  |
|  +-----------------------------------------------------------------------+  |
|           |                       |                       |                 |
|           |                       |                       |                 |
|           v                       v                       v                 |
|  +---------------+  +---------------+  +---------------+  +---------------+ |
|  |   CLINICAL    |  |    BILLING    |  |    GROWTH     |  |    CONNECT    | |
|  |    SCHEMA     |  |    SCHEMA     |  |    SCHEMA     |  |    SCHEMA     | |
|  +---------------+  +---------------+  +---------------+  +---------------+ |
|  | patients      |  | invoices      |  | campaigns     |  | professional_ | |
|  | encounters    |  | invoice_items |  | leads         |  | profiles      | |
|  | episodes_of_  |  | payments      |  | communications|  | connections   | |
|  | care          |  | subscriptions |  |               |  | job_postings  | |
|  | conditions    |  | insurance_    |  |               |  | applications  | |
|  | observations  |  | payers        |  |               |  | messages      | |
|  | medication_   |  | patient_      |  |               |  | posts         | |
|  | requests      |  | policies      |  |               |  |               | |
|  | care_plans    |  | insurance_    |  |               |  |               | |
|  | goals         |  | claims        |  |               |  |               | |
|  | transcription_|  |               |  |               |  |               | |
|  | sessions      |  |               |  |               |  |               | |
|  | ai_suggestions|  |               |  |               |  |               | |
|  +---------------+  +---------------+  +---------------+  +---------------+ |
|           |                       |                       |                 |
|           +-------------------------------------------+   |                 |
|                                   |                   |   |                 |
|                                   v                   v   v                 |
|  +-----------------------------------------------------------------------+  |
|  |  ENTERPRISE SCHEMA (Cross-tenant)   |  INTEGRATION  |  AUDIT SCHEMA  |  |
|  +-------------------------------------+---------------+----------------+  |
|  | employers                           | abdm_records  | activity_logs  |  |
|  | employer_employees                  | webhook_      | phi_access_    |  |
|  | benefit_plans                       | events        | logs           |  |
|  | employee_enrollments                | api_logs      | llm_call_logs  |  |
|  | empaneled_providers                 |               | document_audit |  |
|  | wellness_programs                   |               | _logs          |  |
|  +-------------------------------------+---------------+----------------+  |
|                                                                             |
+-----------------------------------------------------------------------------+

+-----------------------------------------------------------------------------+
|                       PHARMA DATABASE (Separate)                            |
+-----------------------------------------------------------------------------+
|                                                                             |
|  +-----------------------------------------------------------------------+  |
|  |  PHARMA SCHEMA                |  TRIALS SCHEMA    |  COMPLIANCE       |  |
|  +-------------------------------+-------------------+-------------------+  |
|  | pharma_companies              | clinical_trials   | disclosure_       |  |
|  | pharma_users                  | trial_sites       | records           |  |
|  | medical_reps                  | trial_            | gift_tracking     |  |
|  | campaigns                     | investigators     | pharma_audit_     |  |
|  | campaign_targets              | trial_            | logs              |  |
|  | drug_promotions               | enrollments       |                   |  |
|  | doctor_engagements            |                   |                   |  |
|  +-------------------------------+-------------------+-------------------+  |
|                                                                             |
|  Integration: API-based sync with Kulcare DB (practitioners, drugs)         |
|                                                                             |
+-----------------------------------------------------------------------------+

Schema Purposes and Boundaries

Reference Schema

Purpose: Global lookup tables shared across all tenants

Characteristic	Value
Tenant Scoped	No
RLS Enabled	No
Data Source	Platform-managed, external standards
Update Frequency	Rare (catalog updates, code updates)

Contains:

Medical coding (ICD, SNOMED, LOINC)
Drug system (comprehensive):
- Drug catalog with multi-country support (IN, US, AE)
- DrugManufacturer (manufacturer registry)
- DrugInteraction (drug-drug interaction database)
- DrugAlternative (therapeutic substitutes)
- DrugConditionAssociation (AI: drug-condition mapping)
- DrugAllergyGroup (AI: cross-reactivity patterns)
Procedure catalog
Specialty hierarchy
Geographic data
Facility type definitions

Identity Schema

Purpose: Who provides what, where -- organizational foundation for tenants, providers, facilities, and their service offerings

Characteristic	Value
Tenant Scoped	Partial (some global like users)
RLS Enabled	Yes (for tenant-scoped tables)
Core Entity	Facility (replaces Clinic)
Domain Model	Maps to FHIR Administration module

Contains:

Who: Users, Practitioners (licensed providers), Vendors (unlicensed providers)
Where: Tenants, Organizations, Facilities, Facility Locations
What: Healthcare Services, Service Packages, Package Components
Relationships: Practitioner Roles, Facility-Vendor contracts, User Memberships
Configuration: AI Settings (facility and practitioner level)

Key Relationships:

Tenant
  |
  +-- Organizations (1:N)
        |
        +-- Facilities (1:N)
              |
              +-- Locations (1:N)
              +-- Practitioner Roles (N:M with Practitioners)
              +-- Healthcare Services (1:N)

Clinical Schema

Purpose: Patient care, clinical documentation, AI-assisted care

Characteristic	Value
Tenant Scoped	Yes (all tables)
RLS Enabled	Yes
FHIR Aligned	Yes (core resources)

Contains:

Patients
Patient-Facility links
Episodes of Care
Encounters
Encounter Participants
Conditions
Observations
Medication Requests
Care Plans
Goals
Service Requests
Diagnostic Reports
Appointments
Transcription Sessions
AI Suggestions

Key Relationships:

Patient
  |
  +-- Episodes of Care (1:N)
  |     |
  |     +-- Encounters (1:N)
  |           |
  |           +-- Participants (N:M with Practitioners)
  |           +-- Observations (1:N)
  |           +-- Medication Requests (1:N)
  |           +-- AI Suggestions (1:N)
  |
  +-- Conditions (1:N)
  +-- Care Plans (1:N)
  +-- Goals (1:N)

Billing Schema

Purpose: Financial transactions, subscriptions, insurance, drug formularies

Characteristic	Value
Tenant Scoped	Yes
RLS Enabled	Yes
Currency Support	Multi-currency (INR, USD, AED)

Contains:

Invoices
Invoice Items
Payments
Subscriptions
Insurance Payers
Patient Policies
Insurance Claims
Claim Line Items
Prior Authorizations
Drug Formularies (tenant-scoped insurance coverage for drugs)

Growth Schema

Purpose: Marketing, lead management, communications

Characteristic	Value
Tenant Scoped	Yes
RLS Enabled	Yes

Contains:

Campaigns
Leads
Communications (email, SMS, WhatsApp)

Connect Schema

Purpose: Professional networking, job board

Characteristic	Value
Tenant Scoped	No (cross-tenant by design)
RLS Enabled	No
Visibility	Profile-level permissions

Contains:

Professional Profiles
Experiences, Education, Certifications
Skills, Publications, Awards
Connections, Follows
Recommendations, Endorsements
Job Postings, Applications
Conversations, Messages
Posts, Comments

Key Relationships:

Practitioner (identity schema)
  |
  +-- Professional Profile (1:1)
        |
        +-- Experiences (1:N)
        +-- Connections (N:M)
        +-- Job Applications (1:N)
        +-- Posts (1:N)

Integration Schema

Purpose: External service integrations

Characteristic	Value
Tenant Scoped	Yes
RLS Enabled	Yes

Contains:

ABDM Records (India health data exchange)
Webhook Events
API Logs

Audit Schema

Purpose: Compliance, activity tracking, AI audit

Characteristic	Value
Tenant Scoped	Yes
RLS Enabled	Yes
Retention	Long-term (regulatory requirements)

Contains:

Activity Logs (general actions)
PHI Access Logs (HIPAA compliance)
LLM Call Logs (AI audit trail)
Document Audit Logs (clinical document changes)

Enterprise Schema

Purpose: Employer healthcare benefits, ICHRA plans, empanelment

Characteristic	Value
Tenant Scoped	No (cross-tenant by design)
RLS Enabled	No
Visibility	Employer-level permissions

Enterprises are B2B customers of healthcare facilities. They provide benefits to employees who are patients at those facilities. This schema operates cross-tenant, similar to Connect.

Contains:

Employers (company entities)
Employer Employees (links to clinical.patients)
Employer Facilities (contracted facilities)
Benefit Plans (ICHRA-style plans)
Employee Enrollments
Empaneled Providers (negotiated rates)
Wellness Programs
Program Registrations

Key Relationships:

Employer (cross-tenant)
  |
  +-- Employer Facilities (N:M with identity.facilities)
  |
  +-- Employer Employees (1:N)
  |     |
  |     +-- Patient Link (1:1 with clinical.patients, cross-tenant)
  |     +-- Enrollments (1:N with Benefit Plans)
  |
  +-- Benefit Plans (1:N)
  |
  +-- Empaneled Providers (N:M with identity.practitioners)
  |
  +-- Wellness Programs (1:N)

Pharma Database (Separate)

Purpose: Pharmaceutical company relations, campaigns, clinical trials

Characteristic	Value
Database	Separate PostgreSQL database
Multi-Tenancy	Pharma company as tenant
Integration	API-based sync with Kulcare DB

The Pharma database is intentionally separate due to:

Different tenant model (pharma companies, not facilities)
B2B marketing focus vs patient care
Clinical trial isolation (FDA/DCGI compliance)
Conflict of interest tracking (Sunshine Act)

Contains:

Pharma Schema: Companies, users, medical reps, campaigns, drug promotions
Trials Schema: Clinical trials, sites, investigators, enrollments (anonymized)
Compliance Schema: Disclosure records, gift tracking, audit logs

Integration Points:

Kulcare DB                    Pharma DB
-----------                   ---------
identity.practitioners -----> practitioners_sync (read-only)
reference.drugs -----------> drugs_sync (read-only)

See Pharma Database for detailed documentation.

Cross-Schema Relationships

Identity to Clinical

identity.practitioners ----> clinical.encounter_participants
identity.facilities -------> clinical.patients (via patient_facilities)
identity.healthcare_services -> clinical.appointments

Clinical to Billing

clinical.patients ---------> billing.invoices
clinical.encounters -------> billing.invoice_items

Identity to Connect

identity.practitioners ----> connect.professional_profiles (1:1)
identity.facilities -------> connect.job_postings

Clinical to Audit

clinical.encounters -------> audit.activity_logs
clinical.patients ---------> audit.phi_access_logs
clinical.ai_suggestions ---> audit.llm_call_logs

Enterprise to Clinical/Identity

enterprise.employer_employees ----> clinical.patients (cross-tenant link)
enterprise.employer_facilities ---> identity.facilities (N:M)
enterprise.empaneled_providers ---> identity.practitioners (N:M)

Billing to Enterprise

billing.insurance_claims ------> enterprise.employee_enrollments
billing.patient_policies ------> enterprise.benefit_plans

Row-Level Security (RLS) Implementation

See docs/guides/developer/row-level-security.md for the full RLS model, including the GUC lifecycle, the RlsContext API, the table-classification rule, and case studies.

Tenant Context Pattern

-- Set tenant context on each request (session-scoped: local=false)
SELECT set_config('app.current_tenant_id', 'tenant-uuid', false);

-- RLS policy on tenant-scoped tables — safe form
CREATE POLICY tenant_isolation ON clinical.patients
  FOR ALL
  USING (
    tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid
  )
  WITH CHECK (
    tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid
  );

The , true second arg to current_setting makes the lookup missing-OK; the NULLIF(..., '') turns the empty-string default into NULL so tenant_id = NULL evaluates to NULL (not true), filtering all rows safely. Without these, an unset GUC raises unrecognized configuration parameter and a missing-context read becomes a 500 instead of a zero-row result. Every tenant policy in this codebase should use this exact shape.

Migrations enabling RLS should always pair ENABLE with FORCE:

ALTER TABLE identity.example ENABLE ROW LEVEL SECURITY;
ALTER TABLE identity.example FORCE ROW LEVEL SECURITY;

ENABLE alone leaves the table owner bypassing RLS, which is invisible until table ownership shifts. See row-level-security.md §2.5.

RLS Coverage

Schema / Table	RLS Enabled	Posture
reference	No	Global lookup data
identity.tenants	No	IS the tenant
identity.users	No	Cross-tenant entity
identity.practitioners	No	Cross-tenant entity
identity.user_memberships, sessions, profile_switches, refresh_tokens, devices	No	User↔tenant bridges — see row-level-security.md §2.4
identity.organizations, facilities, facility_locations, practitioner_roles	Yes (forced)	Tenant resources
identity.facility_settings, facility_ai_settings, practitioner_ai_settings	Yes (forced)	Tenant resources
clinical.*	Yes	Tenant isolation
billing.*	Yes	Tenant isolation
growth.*	Yes	Tenant isolation
connect.*	No	Cross-tenant by design — app-layer Pundit
enterprise.*	No	Employer-based access
integration.*	Yes	Tenant isolation
audit.*	Yes	Tenant isolation
federation.*	No	Facility-based access
notifications.*	No	Cross-tenant user inbox — app-layer scope by `recipient_user_id`

Entity Count by Schema

Schema	Estimated Tables	Notes
reference	17	+5 for expanded drug system (DrugManufacturer, DrugInteraction, DrugAlternative, DrugConditionAssociation, DrugAllergyGroup)
identity	14	+1 for FacilityOfflineSettings
clinical	22	+2 for OfflinePrescription, OfflineEncounter
billing	11	+1 for DrugFormulary (tenant-scoped insurance formularies)
growth	4
connect	15
enterprise	10
integration	4
audit	7	+3 for OfflineSyncLog, OfflineDevice, SyncBatch
federation	12	Master Patient Index, sync tracking, feature config
Kulcare DB Total	~116
Pharma DB	~20

Compare to current: ~501 tables

Offline mode adds 6 tables for sync tracking and device management. See Offline Mode.

Federation schema adds 12 tables for on-premise sync and patient identity. See Federated Deployment.

Schema Dependencies

reference (independent)
    |
    v
identity (depends on reference)
    |
    +---> clinical (depends on identity, reference)
    |
    +---> billing (depends on identity, clinical, enterprise)
    |
    +---> growth (depends on identity)
    |
    +---> connect (depends on identity)
    |
    +---> enterprise (depends on identity, clinical - cross-tenant)
    |
    +---> integration (depends on identity, clinical)
    |
    +---> audit (depends on all schemas - logging)

pharma_db (separate database)
    |
    +---> API sync from identity.practitioners
    +---> API sync from reference.drugs

Migration Sequence

If migrating from current to new schema:

reference - Create first (no dependencies)
identity - Create second (foundation)
clinical - Core clinical data
billing - Financial data
growth - Marketing data
connect - Professional networking
enterprise - Employer benefits (after clinical for patient linkage)
integration - External integrations
audit - Logging (can be parallel with others)
pharma_db - Separate database (can be parallel)

Schema Architecture Overview

Schema LandscapeCopy Anchor Link

Schema Purposes and BoundariesCopy Anchor Link

Reference SchemaCopy Anchor Link

Identity SchemaCopy Anchor Link

Clinical SchemaCopy Anchor Link

Billing SchemaCopy Anchor Link

Growth SchemaCopy Anchor Link

Connect SchemaCopy Anchor Link

Integration SchemaCopy Anchor Link

Audit SchemaCopy Anchor Link

Enterprise SchemaCopy Anchor Link

Pharma Database (Separate)Copy Anchor Link

Cross-Schema RelationshipsCopy Anchor Link

Identity to ClinicalCopy Anchor Link

Clinical to BillingCopy Anchor Link

Identity to ConnectCopy Anchor Link

Clinical to AuditCopy Anchor Link

Enterprise to Clinical/IdentityCopy Anchor Link

Billing to EnterpriseCopy Anchor Link

Row-Level Security (RLS) ImplementationCopy Anchor Link

Tenant Context PatternCopy Anchor Link

RLS CoverageCopy Anchor Link

Entity Count by SchemaCopy Anchor Link

Schema DependenciesCopy Anchor Link

Migration SequenceCopy Anchor Link

On this page

Schema Landscape

Schema Purposes and Boundaries

Reference Schema

Identity Schema

Clinical Schema

Billing Schema

Growth Schema

Connect Schema

Integration Schema

Audit Schema

Enterprise Schema

Pharma Database (Separate)

Cross-Schema Relationships

Identity to Clinical

Clinical to Billing

Identity to Connect

Clinical to Audit

Enterprise to Clinical/Identity

Billing to Enterprise

Row-Level Security (RLS) Implementation

Tenant Context Pattern

RLS Coverage

Entity Count by Schema

Schema Dependencies

Migration Sequence