Healthcare EMR: Patient Record Loading Optimized from 2 Min to 2 Sec

CREATE INDEX idx_patient_composite ON patients (patient_id, mrn, date_of_birth);
CREATE INDEX idx_medical_history ON medical_history (patient_id, visit_date DESC, department_id);
CREATE INDEX idx_lab_results ON lab_results (patient_id, test_date DESC, test_type);
CREATE INDEX idx_medications ON medications (patient_id, prescription_date DESC, drug_name);
CREATE INDEX idx_allergies ON allergies (patient_id, allergy_type, severity_level);

SELECT DISTINCT 
    p.patient_id,
    p.first_name,
    p.last_name,
    p.date_of_birth,
    mh.chief_complaint,
    mh.diagnosis,
    lr.test_name,
    lr.result_value,
    m.drug_name,
    m.dosage,
    a.allergen_name
FROM patients p
LEFT JOIN medical_history mh ON p.patient_id = mh.patient_id
LEFT JOIN lab_results lr ON p.patient_id = lr.patient_id
LEFT JOIN medications m ON p.patient_id = m.patient_id
LEFT JOIN allergies a ON p.patient_id = a.patient_id
WHERE p.mrn = ? 
AND mh.visit_date >= DATE_SUB(NOW(), INTERVAL 2 YEAR)
ORDER BY mh.visit_date DESC, lr.test_date DESC;

ALTER TABLE medical_history PARTITION BY RANGE (YEAR(visit_date)) (
    PARTITION p_2020 VALUES LESS THAN (2021),
    PARTITION p_2021 VALUES LESS THAN (2022),
    PARTITION p_2022 VALUES LESS THAN (2023),
    PARTITION p_2023 VALUES LESS THAN (2024),
    PARTITION p_2024 VALUES LESS THAN (2025),
    PARTITION p_current VALUES LESS THAN MAXVALUE
);

ALTER TABLE lab_results PARTITION BY RANGE (YEAR(test_date)) (
    PARTITION lab_2022 VALUES LESS THAN (2023),
    PARTITION lab_2023 VALUES LESS THAN (2024),
    PARTITION lab_2024 VALUES LESS THAN (2025),
    PARTITION lab_current VALUES LESS THAN MAXVALUE
);

CREATE TABLE medical_imaging_archive AS 
SELECT * FROM medical_imaging 
WHERE image_date < DATE_SUB(NOW(), INTERVAL 5 YEAR);

DELETE FROM medical_imaging 
WHERE image_date < DATE_SUB(NOW(), INTERVAL 5 YEAR);

import redis
import json
import hashlib
from cryptography.fernet import Fernet

class HIPAACompliantCache:
    def __init__(self):
        self.redis_client = redis.Redis(
            host='localhost',
            port=6379,
            db=0,
            ssl=True,
            ssl_cert_reqs='required'
        )
        self.encryption_key = Fernet.generate_key()
        self.cipher = Fernet(self.encryption_key)
    
    def cache_patient_data(self, patient_id, data, ttl=600):
        cache_key = f"patient:{patient_id}"
        
        encrypted_data = self.cipher.encrypt(json.dumps(data).encode())
        
        self.redis_client.setex(
            cache_key, 
            ttl, 
            encrypted_data
        )
        
        self.log_cache_access(patient_id, 'WRITE', cache_key)
    
    def get_patient_data(self, patient_id, user_id):
        cache_key = f"patient:{patient_id}"
        
        if not self.verify_access_permissions(user_id, patient_id):
            raise PermissionError("Access denied to patient data")
        
        encrypted_data = self.redis_client.get(cache_key)
        
        if encrypted_data:
            decrypted_data = self.cipher.decrypt(encrypted_data)
            self.log_cache_access(patient_id, 'READ', cache_key, user_id)
            return json.loads(decrypted_data.decode())
        
        return None
    
    def invalidate_patient_cache(self, patient_id, reason="DATA_UPDATE"):
        cache_key = f"patient:{patient_id}"
        self.redis_client.delete(cache_key)
        self.log_cache_access(patient_id, 'INVALIDATE', cache_key, reason=reason)

const healthcareServices = {
  patientService: {
    endpoint: 'https://api.hospital.com/patients',
    timeout: 500,
    retries: 3,
    fallback: 'cached_patient_data'
  },
  labService: {
    endpoint: 'https://api.hospital.com/lab-results',
    timeout: 300,
    retries: 2,
    fallback: 'recent_lab_cache'
  },
  medicationService: {
    endpoint: 'https://api.hospital.com/medications',
    timeout: 200,
    retries: 2,
    cache_ttl: 3600
  },
  imagingService: {
    endpoint: 'https://api.hospital.com/imaging',
    timeout: 1000,
    compression: true,
    lazy_load: true
  }
};

async function loadPatientRecord(patientId, userId) {
  const startTime = Date.now();
  
  try {
    const patientDataPromises = [
      fetchPatientBasics(patientId),
      fetchLabResults(patientId),
      fetchMedications(patientId),
      fetchAllergies(patientId),
      fetchVitalSigns(patientId)
    ];
    
    const [
      patient,
      labResults,
      medications,
      allergies,
      vitals
    ] = await Promise.all(patientDataPromises);
    
    const loadTime = Date.now() - startTime;
    
    logPerformanceMetric({
      patient_id: patientId,
      user_id: userId,
      load_time: loadTime,
      services_called: patientDataPromises.length,
      timestamp: new Date()
    });
    
    return {
      patient,
      labResults,
      medications,
      allergies,
      vitals,
      metadata: {
        loadTime,
        cacheHits: getCacheHitCount(),
        timestamp: new Date()
      }
    };
    
  } catch (error) {
    logError('Patient record loading failed', error, { patientId, userId });
    throw new Error('Unable to load patient records');
  }
}

class HealthcarePerformanceMonitor {
  constructor() {
    this.metrics = new Map();
    this.alertThresholds = {
      patientLoadTime: 3000,
      emergencyLoadTime: 2000,
      imageLoadTime: 5000,
      labSyncDelay: 1000
    };
  }
  
  async trackPatientRecordLoad(patientId, userId, startTime) {
    const endTime = Date.now();
    const loadTime = endTime - startTime;
    
    const metric = {
      patient_id: patientId,
      user_id: userId,
      load_time: loadTime,
      timestamp: new Date(),
      department: await getUserDepartment(userId),
      cache_hit: await getCacheStatus(patientId),
      services_called: this.getServicesUsed()
    };
    
    await this.storeMetric('patient_load_time', metric);
    
    if (loadTime > this.alertThresholds.patientLoadTime) {
      await this.triggerPerformanceAlert({
        type: 'SLOW_PATIENT_LOAD',
        severity: loadTime > 10000 ? 'CRITICAL' : 'WARNING',
        details: metric
      });
    }
    
    await this.updateDashboard(metric);
  }
  
  async generateHIPAACompliantReport(startDate, endDate) {
    const metrics = await this.getMetrics(startDate, endDate);
    
    const report = {
      summary: {
        avgLoadTime: this.calculateAverage(metrics, 'load_time'),
        totalRequests: metrics.length,
        emergencyRequests: metrics.filter(m => m.department === 'EMERGENCY').length,
        performanceImprovements: this.calculateImprovement(metrics)
      },
      compliance: {
        auditTrailComplete: true,
        dataEncrypted: true,
        accessLogged: true,
        retentionCompliant: true
      },
      recommendations: this.generateOptimizationRecommendations(metrics)
    };
    
    return this.encryptReport(report);
  }
}