Doctors Schema (Session 26)¶
Tables¶
doctors¶
Individual doctors/surgeons affiliated with healthcare providers.
| Column | Type | Nullable | Description |
|---|---|---|---|
| id | UUID (PK) | NO | |
| tenant_id | UUID (FK→tenants) | NO | Multi-tenancy |
| provider_id | UUID (FK→providers) | NO | Affiliated provider |
| external_id | VARCHAR(100) | YES | External system ID |
| slug | VARCHAR(200) UNIQUE | NO | URL-safe identifier |
| full_name | VARCHAR(200) | NO | |
| name_translations | JSONB | YES | {"ar": "..."} |
| title | VARCHAR(300) | YES | Role/position |
| gender | VARCHAR(20) | NO | male/female |
| photo_url | VARCHAR(500) | YES | |
| bio | TEXT | YES | |
| primary_specialty | VARCHAR(100) | NO | orthopedics, cardiology, etc. |
| sub_specialties | JSONB | YES | ["joint_replacement"] |
| languages_spoken | JSONB | NO | ["en", "hi", "ar"] |
| consultation_languages | JSONB | YES | Languages used in consultations |
| qualifications | JSONB | YES | [{"degree": "...", "institution": "...", "country": "..."}] |
| board_certifications | JSONB | YES | [{"board": "...", "specialty": "..."}] |
| fellowship_training | JSONB | YES | [{"institution": "...", "focus": "..."}] |
| years_of_experience | INTEGER | YES | |
| total_procedures_performed | INTEGER | YES | Lifetime total |
| annual_procedure_volume | INTEGER | YES | |
| technology_proficiencies | JSONB | YES | ["robotic_surgery"] |
| communication_style | VARCHAR(50) | YES | Default: patient_directed |
| telemedicine_capable | BOOLEAN | YES | Default: false |
| patient_satisfaction_score | DECIMAL(3,2) | YES | 0.00-5.00 |
| total_reviews | INTEGER | YES | Default: 0 |
| outcome_data | JSONB | YES | Hospital-level outcome data |
| publications_count | INTEGER | YES | |
| data_completeness | JSONB | YES | {"overall_score": 0.92, "badge": "verified", ...} |
| is_active | BOOLEAN | NO | Default: true |
| is_verified | BOOLEAN | NO | Default: false |
| metadata | JSONB | YES | |
| created_at | TIMESTAMPTZ | NO | |
| updated_at | TIMESTAMPTZ | NO |
doctor_procedures¶
Join table linking doctors to procedures they perform, with outcome data.
| Column | Type | Nullable | Description |
|---|---|---|---|
| id | UUID (PK) | NO | |
| doctor_id | UUID (FK→doctors, CASCADE) | NO | |
| procedure_code | VARCHAR(20) | NO | CPT code |
| procedure_name | VARCHAR(200) | NO | |
| is_primary_surgeon | BOOLEAN | YES | Default: true |
| annual_volume | INTEGER | NO | Cases per year |
| lifetime_volume | INTEGER | YES | Total career cases |
| complexity_tiers_handled | JSONB | YES | ["routine", "complex", "revision"] |
| success_rate | DECIMAL(5,4) | YES | 0.0000-1.0000 |
| complication_rate_30day | DECIMAL(5,4) | YES | |
| readmission_rate_30day | DECIMAL(5,4) | YES | |
| infection_rate | DECIMAL(5,4) | YES | |
| average_recovery_days | INTEGER | YES | |
| proms_scores | JSONB | YES | {"KOOS": 88.5, "Oxford_Knee": 42.1} |
| technique | VARCHAR(50) | YES | robotic_assisted, conventional, etc. |
| implant_brands_used | JSONB | YES | |
| cost_range_usd_cents | JSONB | YES | {"min": 500000, "max": 800000} |
| is_active | BOOLEAN | YES | Default: true |
| UNIQUE(doctor_id, procedure_code) |
Modified Columns¶
providers.language_services— JSONB, nullable. Interpreter, coordinator, and document language data.provider_procedures.lead_surgeon_id— UUID, nullable. FK reference to lead doctor.
Data Completeness Scoring¶
7 weighted categories: - identity (0.15): Always 1.0 if profile exists - languages (0.10): languages_spoken + consultation_languages - credentials (0.15): qualifications + board_certs + fellowship - volume (0.20): annual_volume + lifetime_volume from doctor_procedures - outcomes (0.25): success_rate + complication_rate + readmission_rate + PROMs - operational (0.10): technique + technology + telemedicine + response_time - social_proof (0.05): satisfaction_score + reviews + publications
Badges: >= 0.80 "verified" | 0.55-0.79 "complete" | < 0.55 "basic"
Seed Data (8 Doctors)¶
| Doctor | Provider | Specialty | Completeness | Badge |
|---|---|---|---|---|
| Dr. Rajesh Patel | Apollo Chennai | orthopedics | ~0.92 | verified |
| Dr. Somchai Rattanapong | Bumrungrad Bangkok | orthopedics | ~0.88 | verified |
| Dr. Fatima Al-Rashidi | Cleveland Clinic Abu Dhabi | orthopedics | ~0.75 | complete |
| Dr. Mehmet Yilmaz | Medicana Ankara | orthopedics | ~0.72 | complete |
| Dr. Arun Krishnamurthy | Apollo Chennai | cardiology | ~0.68 | complete |
| Dr. Priya Sharma | Apollo Chennai | orthopedics | ~0.65 | complete |
| Dr. Vikram Singh | Fortis Gurgaon | orthopedics | ~0.48 | basic |
| Dr. Carmen Vidal | Quironsalud Barcelona | oncology | ~0.42 | basic |