from sqlalchemy import Column, Integer, BigInteger, String, DateTime, Text, Date, Float, JSON, UniqueConstraint, Index from db import Base from datetime import datetime class EpicAppConfig(Base): __tablename__ = "epic_app_configs" id = Column(Integer, primary_key=True, autoincrement=True) client_id = Column(String(64), nullable=False) epic_client_id = Column(String(255), nullable=False) private_key_pem = Column(Text, nullable=False) token_url = Column(String(512), nullable=False) scope = Column(String(255), nullable=False) fhir_base = Column(String(512), nullable=False) org_id = Column(String(100), nullable=True) class EpicToken(Base): __tablename__ = "epic_tokens" id = Column(Integer, primary_key=True, autoincrement=True) client_id = Column(String(100), nullable=True) refresh_token = Column(String(355), nullable=True) new_token = Column(String(300), nullable=True) fhir_base = Column(String(300), nullable=True) token_url = Column(String(300), nullable=True) org_id = Column(String(100), nullable=True) token_timestamp = Column(DateTime, default=datetime.now()) expires_in = Column(Integer, nullable=True) class EpicPatient(Base): __tablename__ = "epic_patients" id = Column(Integer, primary_key=True, autoincrement=True) # FHIR resource id (e.g. "e63wRTbPfr1p8UW81d8Seiw3") patient_id = Column(String(64), unique=True, nullable=False) # Common identifiers epic_id = Column(String(64), nullable=True) # identifier.type.text == "EPIC" epi_id = Column(String(64), nullable=True) # identifier.type.text == "EPI" ssn = Column(String(32), nullable=True) # system "urn:oid:2.16.840.1.113883.4.1" payer_member_id = Column(String(64), nullable=True) # first PayerMemberId ceid = Column(String(64), nullable=True) # type "CEID" mychart_login = Column(String(128), nullable=True) # type "MYCHARTLOGIN" # Demographics first_name = Column(String(100), nullable=True) last_name = Column(String(100), nullable=True) full_name = Column(String(255), nullable=True) gender = Column(String(32), nullable=True) birth_date = Column(Date, nullable=True) # Contact home_phone = Column(String(50), nullable=True) work_phone = Column(String(50), nullable=True) # Address (home) address_line = Column(String(255), nullable=True) city = Column(String(100), nullable=True) state = Column(String(50), nullable=True) postal_code = Column(String(20), nullable=True) country = Column(String(50), nullable=True) # Raw JSON raw_json = Column(Text, nullable=False) created_at = Column(DateTime, default=datetime.now) updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now) class LabObservation(Base): __tablename__ = "lab_observations" id = Column(BigInteger, primary_key=True, autoincrement=True) org_id = Column(String(64), nullable=False) fhir_id = Column(String(128), nullable=False) patient_id = Column(String(128), nullable=False) patient_display = Column(Text) encounter_id = Column(String(128)) encounter_display = Column(Text) status = Column(String(32)) category_code = Column(String(64)) category_display = Column(Text) test_code_system = Column(Text) test_code = Column(String(128)) test_display = Column(Text) effective_datetime = Column(DateTime(timezone=True)) issued_datetime = Column(DateTime(timezone=True)) value_number = Column(Float) value_unit = Column(String(64)) value_code = Column(String(64)) value_system = Column(Text) interpretation_code = Column(String(32)) interpretation_text = Column(Text) ref_low = Column(Float) ref_low_unit = Column(String(64)) ref_high = Column(Float) ref_high_unit = Column(String(64)) specimen_id = Column(String(128)) specimen_display = Column(Text) raw_fhir = Column(JSON, nullable=False) created_at = Column(DateTime, default=datetime.now()) class DiagnosticReport(Base): __tablename__ = "diagnostic_reports" id = Column(Integer, primary_key=True, index=True) # Org + FHIR identifiers org_id = Column(String(64), nullable=False, index=True) fhir_id = Column(String(128), nullable=False, index=True) # Patient info patient_id = Column(String(128), nullable=True, index=True) patient_display = Column(String(255), nullable=True) # Basic status / category status = Column(String(32), nullable=True) category_code = Column(String(64), nullable=True) category_display = Column(String(255), nullable=True) # Code for the diagnostic report itself (LOINC, etc.) code_system = Column(String(255), nullable=True) code_code = Column(String(64), nullable=True) code_display = Column(String(255), nullable=True) code_text = Column(String(255), nullable=True) # Timing effective_datetime = Column(DateTime(timezone=True), nullable=True) issued_datetime = Column(DateTime(timezone=True), nullable=True) # Performer performer_display = Column(String(255), nullable=True) performer_reference = Column(String(255), nullable=True) # Result references (Observation URLs) result_references = Column(JSON, nullable=True) # Raw FHIR payload for auditing / debugging raw_fhir = Column(JSON, nullable=False) __table_args__ = ( UniqueConstraint( "org_id", "fhir_id", "patient_id", name="uq_diagnostic_report_org_fhir_patient", ), ) class EpicObservation(Base): __tablename__ = "epic_observation" org_id = Column(String(64), nullable=False, index=True) fhir_id = Column(String(128), nullable=False) patient_id = Column(String(128), nullable=False) # FHIR Observation.id observation_id = Column(String(255), primary_key=True) # Common references (store just the referenced ids if you want; here we store full "Patient/xxx") patient_reference = Column(String(255), index=True) # e.g. "Patient/eUEKdn..." encounter_reference = Column(String(255), index=True) # e.g. "Encounter/eKvk..." specimen_reference = Column(String(255), index=True) # e.g. "Specimen/..." status = Column(String(64), index=True) # final, amended, etc. # "code" summary code_text = Column(String(512), index=True) # resource.code.text code_primary_system = Column(String(255), index=True) # e.g. "http://loinc.org" code_primary_code = Column(String(128), index=True) # e.g. "94500-6" code_primary_display = Column(String(512)) # display text # Timing effective_datetime = Column(DateTime(timezone=True), index=True) issued_datetime = Column(DateTime(timezone=True), index=True) # Value (support common value[x] shapes) value_type = Column(String(64), index=True) # "valueString", "valueCodeableConcept", ... value_string = Column(String(2048)) value_code = Column(String(128)) value_system = Column(String(255)) value_display = Column(String(512)) value_unit = Column(String(64)) value_number = Column(String(64)) # store numeric as string for portability # Other FHIR elements (store as JSON string blobs to avoid many join tables) category_json = Column(Text) # list of category codings/text interpretation_json = Column(Text) # list note_json = Column(Text) # list has_member_json = Column(Text) # list of references based_on_json = Column(Text) # list of references reference_range_json = Column(Text) # list # Full raw resource (always keep this) raw_json = Column(Text) __table_args__ = ( Index("ix_obs_patient_effective", "patient_reference", "effective_datetime"), Index("ix_obs_code", "code_primary_system", "code_primary_code"), )