Data Model¶

CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    domain VARCHAR(255),
    logo_url TEXT,
    primary_color VARCHAR(7) DEFAULT '#008B8B',
    accent_color VARCHAR(7) DEFAULT '#FF7F50',
    flagsmith_environment_key TEXT,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE patients (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    clerk_user_id VARCHAR(255),
    email VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    date_of_birth DATE,
    gender VARCHAR(20),
    country_of_residence VARCHAR(3),  -- ISO 3166-1 alpha-3
    preferred_language VARCHAR(10) DEFAULT 'en',
    phone VARCHAR(20),
    timezone VARCHAR(50),
    medical_history JSONB DEFAULT '{}',
    preferences JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(tenant_id, email)
);

CREATE TABLE providers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    country VARCHAR(3) NOT NULL,
    city VARCHAR(100) NOT NULL,
    provider_type VARCHAR(50) DEFAULT 'hospital',
    accreditations JSONB DEFAULT '[]',
    specialties JSONB DEFAULT '[]',
    language_services JSONB DEFAULT '{}',
    facilities JSONB DEFAULT '{}',
    pricing_tier VARCHAR(20),
    website_url TEXT,
    is_active BOOLEAN DEFAULT true,
    neo4j_node_id VARCHAR(100),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(tenant_id, slug)
);

CREATE TABLE doctors (
    -- Identity
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    provider_id UUID NOT NULL REFERENCES providers(id),
    external_id VARCHAR(100),
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    title VARCHAR(50),                    -- Dr., Prof., etc.
    gender VARCHAR(20),
    photo_url TEXT,

    -- Professional
    specialty VARCHAR(100) NOT NULL,
    sub_specialties JSONB DEFAULT '[]',
    medical_license_number VARCHAR(100),
    license_country VARCHAR(3),
    years_of_experience INTEGER,
    education JSONB DEFAULT '[]',         -- [{institution, degree, year}]
    certifications JSONB DEFAULT '[]',

    -- Languages
    languages JSONB DEFAULT '[]',         -- [{language, proficiency}]
    primary_language VARCHAR(50),

    -- Clinical Volume & Outcomes
    annual_procedure_volume INTEGER,
    total_procedures_performed INTEGER,
    overall_success_rate DECIMAL(5,2),
    complication_rate_30day DECIMAL(5,2),
    patient_satisfaction_score DECIMAL(3,2),
    mortality_rate DECIMAL(5,4),

    -- Operational
    consultation_fee_usd DECIMAL(10,2),
    accepts_international_patients BOOLEAN DEFAULT true,
    available_for_video_consult BOOLEAN DEFAULT true,
    average_response_time_hours INTEGER,
    next_available_date DATE,

    -- Social Proof
    publications_count INTEGER DEFAULT 0,
    notable_achievements JSONB DEFAULT '[]',
    patient_testimonials_count INTEGER DEFAULT 0,

    -- Metadata
    data_completeness JSONB DEFAULT '{}',
    badges JSONB DEFAULT '[]',
    is_active BOOLEAN DEFAULT true,
    neo4j_node_id VARCHAR(100),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

{
  "identity": {"score": 0.80, "missing": ["photo_url"]},
  "languages": {"score": 1.00, "missing": []},
  "credentials": {"score": 0.75, "missing": ["certifications"]},
  "volume": {"score": 1.00, "missing": []},
  "outcomes": {"score": 1.00, "missing": []},
  "operational": {"score": 0.50, "missing": ["next_available_date", "average_response_time_hours"]},
  "social_proof": {"score": 0.33, "missing": ["notable_achievements", "patient_testimonials_count"]},
  "overall": 0.82
}

CREATE TABLE doctor_procedures (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    doctor_id UUID NOT NULL REFERENCES doctors(id),
    procedure_code VARCHAR(20) NOT NULL,     -- CPT code
    procedure_name VARCHAR(255) NOT NULL,
    annual_volume INTEGER,
    success_rate DECIMAL(5,2),
    complication_rate_30day DECIMAL(5,2),
    average_los_days DECIMAL(4,1),           -- Length of stay
    proms_scores JSONB DEFAULT '{}',         -- Patient-Reported Outcome Measures
    technique VARCHAR(100),                  -- e.g., "robotic", "laparoscopic", "open"
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(tenant_id, doctor_id, procedure_code)
);

(provider:Provider)-[:OFFERS {
    cost_usd: 12500,
    cost_currency: "USD",
    annual_volume: 450,
    success_rate: 97.2,
    average_los_days: 5,
    wait_time_weeks: 2,
    package_includes: ["surgery", "hospital_stay", "physiotherapy"],
    last_updated: datetime()
}]->(procedure:Procedure)

(doctor:Doctor)-[:PERFORMS {
    annual_volume: 120,
    success_rate: 98.1,
    technique: "robotic-assisted",
    since_year: 2018
}]->(procedure:Procedure)

MATCH (c:Condition {code: $condition_code})-[:REQUIRES]->(p:Procedure)
      <-[:OFFERS]-(prov:Provider)-[:LOCATED_IN]->(loc:Location)
WHERE prov.tenant_id = $tenant_id AND prov.is_active = true
RETURN prov, p, loc
ORDER BY prov.offers_success_rate DESC

MATCH (d:Doctor)-[:AFFILIATED_WITH]->(prov:Provider),
      (d)-[:PERFORMS]->(proc:Procedure {code: $procedure_code}),
      (d)-[:SPEAKS]->(lang:Language {code: $patient_language})
WHERE prov.tenant_id = $tenant_id
RETURN d, prov, proc, lang

provider_text = f"""
{provider.name} is a {provider.provider_type} located in {provider.city}, {provider.country}.
Specialties: {', '.join(provider.specialties)}.
Accreditations: {', '.join(provider.accreditations)}.
Language services: {json.dumps(provider.language_services)}.
"""
embedding = voyage_client.embed(provider_text, model="voyage-3.5-lite")

# Example: "Recent blood work within 30 days showing CBC, metabolic panel, and coagulation studies"
# This enables matching uploaded documents to requirements via cosine similarity

CREATE TABLE fhir_resources (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    patient_id UUID NOT NULL REFERENCES patients(id),
    case_id UUID REFERENCES cases(id),
    resource_type VARCHAR(50) NOT NULL,
    resource_id VARCHAR(255) NOT NULL,    -- FHIR resource ID
    resource JSONB NOT NULL,              -- Full FHIR R4 JSON
    version INTEGER DEFAULT 1,
    is_current BOOLEAN DEFAULT true,
    source VARCHAR(50),                   -- 'agent', 'manual', 'import'
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(tenant_id, resource_type, resource_id, version)
);

CREATE INDEX idx_fhir_patient ON fhir_resources(tenant_id, patient_id, resource_type);
CREATE INDEX idx_fhir_resource ON fhir_resources USING GIN(resource);

from fhir.resources.condition import Condition

def validate_fhir_condition(resource_json: dict) -> Condition:
    """Validate and return a typed FHIR Condition resource."""
    return Condition.model_validate(resource_json)