Laboratory Information System Data Specifications
Home / Clinical Modules / Laboratory Information System / Data Specifications

Laboratory Information System Data Specifications

Shared Entity References

This module references the following shared entities defined in their owning modules. LIS does not redefine these tables — it uses foreign-key references only.

Shared Entity Owning Module Table(s) FK Used in LIS
Patients ehr-patient-mgmt patients patients.patient_id
Providers ehr-patient-mgmt providers providers.provider_id
Encounters scheduling encounters encounters.encounter_id
Users & Auth ehr-patient-mgmt users, roles, permissions users.user_id
Facilities ehr-patient-mgmt facilities, departments facilities.facility_id, departments.department_id
Payers policy-contract-mgmt payers, insurance_plans payers.payer_id

Entity Relationship Diagram

erDiagram patients ||--o{ lab_orders : "has" encounters ||--o{ lab_orders : "context for" providers ||--o{ lab_orders : "orders" facilities ||--o{ lab_orders : "performed at" departments ||--o{ lab_orders : "performed in" lab_orders ||--o{ lab_order_tests : "contains" lab_orders ||--o{ lab_specimens : "fulfilled by" lab_order_tests ||--o{ lab_results : "produces" lab_results ||--o{ lab_result_components : "has" lab_order_tests ||--o{ lab_sendout_orders : "may create" lab_order_tests ||--o{ lab_micro_cultures : "for micro" lab_order_tests ||--o{ lab_pathology_cases : "for pathology" lab_specimens ||--o{ lab_micro_cultures : "source" lab_micro_cultures ||--o{ lab_micro_sensitivities : "has" lab_results ||--o{ lab_critical_notifications : "triggers" lab_panels ||--o{ lab_panel_components : "contains" lab_analyzers ||--o{ lab_analyzer_interfaces : "configured by" lab_analyzers ||--o{ lab_qc_records : "QC on" lab_reference_ranges ||--o{ lab_results : "applied to" lab_critical_values ||--o{ lab_results : "evaluated for" lab_orders { bigint order_id PK bigint patient_id FK bigint encounter_id FK bigint ordering_provider_id FK bigint facility_id FK bigint department_id FK } lab_order_tests { bigint order_test_id PK bigint order_id FK bigint result_id FK } lab_specimens { bigint specimen_id PK bigint order_id FK bigint patient_id FK bigint collector_id FK } lab_results { bigint result_id PK bigint order_test_id FK bigint patient_id FK bigint verified_by FK } lab_result_components { bigint component_id PK bigint result_id FK } lab_panels { bigint panel_id PK } lab_panel_components { bigint panel_id FK } lab_analyzers { bigint analyzer_id PK bigint facility_id FK } lab_analyzer_interfaces { bigint interface_id PK bigint analyzer_id FK } lab_qc_records { bigint qc_id PK bigint analyzer_id FK bigint technologist_id FK } lab_reference_ranges { bigint range_id PK } lab_critical_values { bigint critical_id PK } lab_critical_notifications { bigint notification_id PK bigint result_id FK bigint notifying_tech_id FK bigint target_provider_id FK bigint acknowledged_by FK } lab_sendout_orders { bigint sendout_id PK bigint order_test_id FK } lab_micro_cultures { bigint culture_id PK bigint specimen_id FK bigint order_test_id FK } lab_micro_sensitivities { bigint sensitivity_id PK bigint culture_id FK } lab_pathology_cases { bigint case_id PK bigint specimen_id FK bigint order_test_id FK bigint pathologist_id FK }

Table Definitions

Note: All timestamps are stored in UTC with application-level conversion to local UAE time zones where needed. All patient-identifying data must comply with UAE PDPL (Federal Decree-Law No. 45/2021).

1. lab_orders

Purpose
Stores lab order headers received from CPOE, including patient/encounter context, accessioning, and high-level status. One lab_orders record may contain multiple lab_order_tests.

Field Specifications

Field Type Nullable Default Constraint Description
order_id BIGINT NO IDENTITY PK Internal lab order identifier
patient_id BIGINT NO — FK → patients.patient_id Patient for whom tests are ordered
encounter_id BIGINT NO — FK → encounters.encounter_id Encounter context (visit/admission)
ordering_provider_id BIGINT NO — FK → providers.provider_id Ordering clinician
facility_id BIGINT NO — FK → facilities.facility_id Performing facility (e.g., Dubai main lab)
department_id BIGINT YES — FK → departments.department_id Lab department/section owning the order
accession_number VARCHAR(30) NO — UNIQUE Accession ID (section-specific format, barcode printed)
order_datetime TIMESTAMP NO CURRENT_TIMESTAMP — When order was received in LIS
order_status VARCHAR(20) NO 'received' IN ('received','in_progress','completed','cancelled') Overall order status
priority VARCHAR(20) NO 'routine' IN ('stat','urgent','routine','scheduled') Priority from CPOE
clinical_indication TEXT YES — — Free-text clinical indication
diagnosis_code_icd10 VARCHAR(10) YES — ICD-10-AM format Primary diagnosis driving order
fasting_status VARCHAR(20) YES — IN ('fasting','non_fasting','unknown') Fasting status at collection
uae_emirate_code VARCHAR(2) YES — IN ('AD','DU','SH','AJ','UA','RK','FU') Emirate of ordering facility
created_by BIGINT NO — FK → users.user_id User that created the order record (interface user for HL7)
updated_by BIGINT YES — FK → users.user_id Last user to update
created_at TIMESTAMP NO CURRENT_TIMESTAMP — Creation timestamp
updated_at TIMESTAMP NO CURRENT_TIMESTAMP — Last update timestamp

SQL DDL

SQL
CREATE TABLE lab_orders (
    order_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id            BIGINT NOT NULL,
    encounter_id          BIGINT NOT NULL,
    ordering_provider_id  BIGINT NOT NULL,
    facility_id           BIGINT NOT NULL,
    department_id         BIGINT NULL,
    accession_number      VARCHAR(30) NOT NULL,
    order_datetime        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    order_status          VARCHAR(20) NOT NULL DEFAULT 'received',
    priority              VARCHAR(20) NOT NULL DEFAULT 'routine',
    clinical_indication   TEXT NULL,
    diagnosis_code_icd10  VARCHAR(10) NULL,
    fasting_status        VARCHAR(20) NULL,
    uae_emirate_code      VARCHAR(2) NULL,
    created_by            BIGINT NOT NULL,
    updated_by            BIGINT NULL,
    created_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_lab_orders_accession UNIQUE (accession_number),

    CONSTRAINT fk_lab_orders_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_lab_orders_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_lab_orders_provider
        FOREIGN KEY (ordering_provider_id) REFERENCES providers(provider_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_lab_orders_facility
        FOREIGN KEY (facility_id) REFERENCES facilities(facility_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_lab_orders_department
        FOREIGN KEY (department_id) REFERENCES departments(department_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_lab_orders_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_lab_orders_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT chk_lab_orders_status
        CHECK (order_status IN ('received','in_progress','completed','cancelled')),
    CONSTRAINT chk_lab_orders_priority
        CHECK (priority IN ('stat','urgent','routine','scheduled')),
    CONSTRAINT chk_lab_orders_fasting
        CHECK (fasting_status IS NULL OR fasting_status IN ('fasting','non_fasting','unknown')),
    CONSTRAINT chk_lab_orders_emirate
        CHECK (uae_emirate_code IS NULL OR uae_emirate_code IN ('AD','DU','SH','AJ','UA','RK','FU'))
);

CREATE INDEX idx_lab_orders_patient ON lab_orders(patient_id);
CREATE INDEX idx_lab_orders_encounter ON lab_orders(encounter_id);
CREATE INDEX idx_lab_orders_accession ON lab_orders(accession_number);
CREATE INDEX idx_lab_orders_status ON lab_orders(order_status);
CREATE INDEX idx_lab_orders_priority ON lab_orders(priority);
CREATE INDEX idx_lab_orders_facility_datetime ON lab_orders(facility_id, order_datetime);

Terminology Bindings

Field Terminology Example Value
diagnosis_code_icd10 ICD-10-AM E11.9
uae_emirate_code Local Emirate Code Set DU (Dubai)

FHIR Resource Mapping

Table Field FHIR Resource FHIR Path
order_id ServiceRequest ServiceRequest.id
patient_id ServiceRequest ServiceRequest.subject.reference (Patient/{id})
encounter_id ServiceRequest ServiceRequest.encounter.reference
ordering_provider_id ServiceRequest ServiceRequest.requester.reference
facility_id ServiceRequest ServiceRequest.locationReference (Organization/Location)
accession_number ServiceRequest ServiceRequest.identifier (type = accession)
order_datetime ServiceRequest ServiceRequest.authoredOn
order_status ServiceRequest ServiceRequest.status
priority ServiceRequest ServiceRequest.priority
clinical_indication ServiceRequest ServiceRequest.reasonCode.text
diagnosis_code_icd10 ServiceRequest ServiceRequest.reasonCode.coding (ICD-10-AM)

2. lab_order_tests

Purpose
Stores individual ordered tests within a lab order, including LOINC code, section, and per-test status. One lab_orders record may have many lab_order_tests.

Field Specifications

Field Type Nullable Default Constraint Description
order_test_id BIGINT NO IDENTITY PK Unique test instance within an order
order_id BIGINT NO — FK → lab_orders.order_id Parent lab order
test_code_loinc VARCHAR(20) NO — — LOINC code for the test
test_name VARCHAR(100) NO — — Human-readable test name
lab_section VARCHAR(30) NO — IN ('chemistry','hematology','microbiology','pathology','immunology','coagulation','blood_bank','poct','sendout') Section performing the test
specimen_type_required VARCHAR(30) NO — — Required specimen type (e.g., serum, EDTA blood)
status VARCHAR(20) NO 'ordered' IN ('ordered','in_progress','completed','cancelled','rejected') Test-level status
result_id BIGINT YES — FK → lab_results.result_id Linked result header when available
priority VARCHAR(20) NO 'routine' IN ('stat','urgent','routine') Copied from order or overridden
is_panel_component BOOLEAN NO FALSE — True if created as part of a panel
panel_id BIGINT YES — FK → lab_panels.panel_id Panel definition if applicable
expected_completion_datetime TIMESTAMP YES — — Calculated expected completion time (for TAT)
performed_analyzer_id BIGINT YES — FK → lab_analyzers.analyzer_id Analyzer used, if automated
notes TEXT YES — — Internal lab notes
created_at TIMESTAMP NO CURRENT_TIMESTAMP — Creation timestamp
updated_at TIMESTAMP NO CURRENT_TIMESTAMP — Last update timestamp

SQL DDL

SQL
CREATE TABLE lab_order_tests (
    order_test_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id                   BIGINT NOT NULL,
    test_code_loinc            VARCHAR(20) NOT NULL,
    test_name                  VARCHAR(100) NOT NULL,
    lab_section                VARCHAR(30) NOT NULL,
    specimen_type_required     VARCHAR(30) NOT NULL,
    status                     VARCHAR(20) NOT NULL DEFAULT 'ordered',
    result_id                  BIGINT NULL,
    priority                   VARCHAR(20) NOT NULL DEFAULT 'routine',
    is_panel_component         BOOLEAN NOT NULL DEFAULT FALSE,
    panel_id                   BIGINT NULL,
    expected_completion_datetime TIMESTAMP NULL,
    performed_analyzer_id      BIGINT NULL,
    notes                      TEXT NULL,
    created_at                 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at                 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_lab_order_tests_order
        FOREIGN KEY (order_id) REFERENCES lab_orders(order_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_lab_order_tests_result
        FOREIGN KEY (result_id) REFERENCES lab_results(result_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_lab_order_tests_panel
        FOREIGN KEY (panel_id) REFERENCES lab_panels(panel_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_lab_order_tests_analyzer
        FOREIGN KEY (performed_analyzer_id) REFERENCES lab_analyzers(analyzer_id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT chk_lab_order_tests_status
        CHECK (status IN ('ordered','in_progress','completed','cancelled','rejected')),
    CONSTRAINT chk_lab_order_tests_priority
        CHECK (priority IN ('stat','urgent','routine')),
    CONSTRAINT chk_lab_order_tests_section
        CHECK (lab_section IN ('chemistry','hematology','microbiology','pathology','immunology','coagulation','blood_bank','poct','sendout'))
);

CREATE INDEX idx_lab_order_tests_order ON lab_order_tests(order_id);
CREATE INDEX idx_lab_order_tests_status ON lab_order_tests(status);
CREATE INDEX idx_lab_order_tests_section ON lab_order_tests(lab_section, status);
CREATE INDEX idx_lab_order_tests_loinc ON lab_order_tests(test_code_loinc);
CREATE INDEX idx_lab_order_tests_analyzer ON lab_order_tests(performed_analyzer_id);

Terminology Bindings

Field Terminology Example Value
test_code_loinc LOINC 718-7 (Hemoglobin [Mass/volume] in Blood)
lab_section Local Lab Section Code Set chemistry
specimen_type_required Local Specimen Type Code Set / SNOMED CT where applicable serum

FHIR Resource Mapping

Table Field FHIR Resource FHIR Path
order_test_id ServiceRequest ServiceRequest.identifier (type = local-test-id)
order_id ServiceRequest ServiceRequest.basedOn (reference to parent ServiceRequest if modeled)
test_code_loinc ServiceRequest ServiceRequest.code.coding (system = http://loinc.org)
test_name ServiceRequest ServiceRequest.code.text
lab_section ServiceRequest ServiceRequest.category (local code)
priority ServiceRequest ServiceRequest.priority
status ServiceRequest ServiceRequest.status

3. lab_specimens

Purpose
Tracks physical specimens from collection through receipt, storage, and disposal, including chain-of-custody and condition. Supports phlebotomy workflows and specimen rejection metrics.

Field Specifications

Field Type Nullable Default Constraint Description
specimen_id BIGINT NO IDENTITY PK Unique specimen identifier
order_id BIGINT NO — FK → lab_orders.order_id Related lab order
patient_id BIGINT NO — FK → patients.patient_id Patient from whom specimen collected
accession_number VARCHAR(30) NO — — Accession number printed on label (matches order or aliquot)
specimen_type VARCHAR(30) NO — — Actual specimen type collected
tube_type VARCHAR(30) NO — — Tube/container type (e.g., EDTA, SST)
collection_datetime TIMESTAMP YES — — When specimen was collected
collector_id BIGINT YES — FK → users.user_id Phlebotomist/collector
collection_location VARCHAR(100) YES — — Ward/clinic where collected
received_datetime TIMESTAMP YES — — When specimen received in lab
received_by BIGINT YES — FK → users.user_id Lab staff receiving specimen
condition VARCHAR(20) YES — IN ('acceptable','hemolyzed','clotted','insufficient','leaked','mislabelled','other') Condition on receipt
rejection_reason_code VARCHAR(20) YES — — Code from Specimen Rejection Reason master
rejection_comment TEXT YES — — Free-text explanation if rejected
storage_location VARCHAR(100) YES — — Rack/freezer/room location
storage_temperature_c NUMERIC(5,2) YES — — Storage temperature in °C
disposal_datetime TIMESTAMP YES — — When specimen disposed
chain_of_custody_json JSONB YES — — JSON array of custody events (scanner, user, timestamp, location)
is_sendout BOOLEAN NO FALSE — True if specimen sent to reference lab
created_at TIMESTAMP NO CURRENT_TIMESTAMP — Creation timestamp
updated_at TIMESTAMP NO CURRENT_TIMESTAMP — Last update timestamp

SQL DDL

SQL
CREATE TABLE lab_specimens (
    specimen_id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id              BIGINT NOT NULL,
    patient_id            BIGINT NOT NULL,
    accession_number      VARCHAR(30) NOT NULL,
    specimen_type         VARCHAR(30) NOT NULL,
    tube_type             VARCHAR(30) NOT NULL,
    collection_datetime   TIMESTAMP NULL,
    collector_id          BIGINT NULL,
    collection_location   VARCHAR(100) NULL,
    received_datetime     TIMESTAMP NULL,
    received_by           BIGINT NULL,
    condition             VARCHAR(20) NULL,
    rejection_reason_code VARCHAR(20) NULL,
    rejection_comment     TEXT NULL,
    storage_location      VARCHAR(100) NULL,
    storage_temperature_c NUMERIC(5,2) NULL,
    disposal_datetime     TIMESTAMP NULL,
    chain_of_custody_json JSONB NULL,
    is_sendout            BOOLEAN NOT NULL DEFAULT FALSE,
    created_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_lab_specimens_order
        FOREIGN KEY (order_id) REFERENCES lab_orders(order_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_lab_specimens_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_lab_specimens_collector
        FOREIGN KEY (collector_id) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_lab_specimens_received_by
        FOREIGN KEY (received_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT chk_lab_specimens_condition
        CHECK (condition IS NULL OR condition IN ('acceptable','hemolyzed','clotted','insufficient','leaked','mislabelled','other'))
);

CREATE INDEX idx_lab_specimens_order ON lab_specimens(order_id);
CREATE INDEX idx_lab_specimens_patient ON lab_specimens(patient_id);
CREATE INDEX idx_lab_specimens_accession ON lab_specimens(accession_number);
CREATE INDEX idx_lab_specimens_condition ON lab_specimens(condition);
CREATE INDEX idx_lab_specimens_collection_dt ON lab_specimens(collection_datetime);

Terminology Bindings

Field Terminology Example Value
specimen_type SNOMED CT / Local venous blood
tube_type Local Tube Type Code Set EDTA
rejection_reason_code Specimen Rejection Reason Master HEMOLYZED

FHIR Resource Mapping

Table Field FHIR Resource FHIR Path
specimen_id Specimen Specimen.id
patient_id Specimen Specimen.subject.reference
accession_number Specimen Specimen.accessionIdentifier.value
specimen_type Specimen Specimen.type
collection_datetime Specimen Specimen.collection.collectedDateTime
collector_id Specimen Specimen.collection.collector.reference
received_datetime Specimen Specimen.receivedTime
condition Specimen Specimen.condition (extension/local)
storage_location Specimen Specimen.container.location (extension/local)

4. lab_results

Purpose
Result header table: one record per ordered test (lab_order_tests) capturing overall status, verification, and criticality. Individual analytes/components are stored in lab_result_components.

Field Specifications

Field Type Nullable Default Constraint Description
result_id BIGINT NO IDENTITY PK Result header identifier
order_test_id BIGINT NO — FK → lab_order_tests.order_test_id Associated ordered test
patient_id BIGINT NO — FK → patients.patient_id Patient
test_code_loinc VARCHAR(20) NO — — LOINC code for the test
result_status VARCHAR(20) NO 'preliminary' IN ('registered','preliminary','final','amended','corrected','cancelled') Result status
result_type VARCHAR(20) NO 'numeric' IN ('numeric','text','microbiology','pathology') Type of result
reported_datetime TIMESTAMP YES — — When result first reported (prelim or final)
verified_by BIGINT YES — FK → users.user_id Verifying technologist/pathologist
verified_datetime TIMESTAMP YES — — When verification occurred
is_critical BOOLEAN NO FALSE — True if any component meets critical threshold
delta_check_flag BOOLEAN NO FALSE — True if delta check failed
auto_verified BOOLEAN NO FALSE — True if passed auto-verification rules
qc_status_at_run VARCHAR(20) YES — IN ('in_control','out_of_control','not_applicable') QC status at time of analysis
performing_analyzer_id BIGINT YES — FK → lab_analyzers.analyzer_id Analyzer used
performing_facility_id BIGINT YES — FK → facilities.facility_id Facility performing test
performing_department_id BIGINT YES — FK → departments.department_id Department performing test
comment TEXT YES — — General result comment
hie_submission_status VARCHAR(20) YES 'pending' IN ('pending','sent','accepted','rejected','not_required') NABIDH/Malaffi submission status
hie_last_submission_datetime TIMESTAMP YES — — Last time result sent to HIE
created_at TIMESTAMP NO CURRENT_TIMESTAMP — Creation timestamp
updated_at TIMESTAMP NO CURRENT_TIMESTAMP — Last update timestamp

SQL DDL

SQL
CREATE TABLE lab_results (
    result_id                    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_test_id                BIGINT NOT NULL,
    patient_id                   BIGINT NOT NULL,
    test_code_loinc              VARCHAR(20) NOT NULL,
    result_status                VARCHAR(20) NOT NULL DEFAULT 'preliminary',
    result_type                  VARCHAR(20) NOT NULL DEFAULT 'numeric',
    reported_datetime            TIMESTAMP NULL,
    verified_by                  BIGINT NULL,
    verified_datetime            TIMESTAMP NULL,
    is_critical                  BOOLEAN NOT NULL DEFAULT FALSE,
    delta_check_flag             BOOLEAN NOT NULL DEFAULT FALSE,
    auto_verified                BOOLEAN NOT NULL DEFAULT FALSE,
    qc_status_at_run             VARCHAR(20) NULL,
    performing_analyzer_id       BIGINT NULL,
    performing_facility_id       BIGINT NULL,
    performing_department_id     BIGINT NULL,
    comment                      TEXT NULL,
    hie_submission_status        VARCHAR(20) NULL DEFAULT 'pending',
    hie_last_submission_datetime TIMESTAMP NULL,
    created_at                   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at                   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_lab_results_order_test
        FOREIGN KEY (order_test_id) REFERENCES lab_order_tests(order_test_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_lab_results_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_lab_results_verified_by
        FOREIGN KEY (verified_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_lab_results_analyzer
        FOREIGN KEY (performing_analyzer_id) REFERENCES lab_analyzers(analyzer_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_lab_results_facility
        FOREIGN KEY (performing_facility_id) REFERENCES facilities(facility_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_lab_results_department
        FOREIGN KEY (performing_department_id) REFERENCES departments(department_id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT chk_lab_results_status
        CHECK (result_status IN ('registered','preliminary','final','amended','corrected','cancelled')),
    CONSTRAINT chk_lab_results_type
        CHECK (result_type IN ('numeric','text','microbiology','pathology')),
    CONSTRAINT chk_lab_results_qc_status
        CHECK (qc_status_at_run IS NULL OR qc_status_at_run IN ('in_control','out_of_control','not_applicable')),
    CONSTRAINT chk_lab_results_hie_status
        CHECK (hie_submission_status IS NULL OR hie_submission_status IN ('pending','sent','accepted','rejected','not_required'))
);

CREATE INDEX idx_lab_results_patient ON lab_results(patient_id);
CREATE INDEX idx_lab_results_order_test ON lab_results(order_test_id);
CREATE INDEX idx_lab_results_status ON lab_results(result_status);
CREATE INDEX idx_lab_results_critical ON lab_results(is_critical) WHERE is_critical = TRUE;
CREATE INDEX idx_lab_results_hie_status ON lab_results(hie_submission_status);
CREATE INDEX idx_lab_results_reported_dt ON lab_results(reported_datetime);

Terminology Bindings

Field Terminology Example Value
test_code_loinc LOINC 4548-4 (Hematocrit [Volume Fraction] of Blood)
qc_status_at_run Local QC Status Code Set in_control

FHIR Resource Mapping

Table Field FHIR Resource FHIR Path
result_id DiagnosticReport DiagnosticReport.id
order_test_id DiagnosticReport DiagnosticReport.basedOn (ServiceRequest reference)
patient_id DiagnosticReport DiagnosticReport.subject.reference
test_code_loinc DiagnosticReport DiagnosticReport.code.coding
result_status DiagnosticReport DiagnosticReport.status
reported_datetime DiagnosticReport DiagnosticReport.issued
verified_by DiagnosticReport DiagnosticReport.resultsInterpreter
performing_facility_id DiagnosticReport DiagnosticReport.performer
comment DiagnosticReport DiagnosticReport.conclusion / DiagnosticReport.note
hie_submission_status DiagnosticReport Extension for HIE submission status

For individual analytes, lab_result_components map to FHIR Observation resources linked via DiagnosticReport.result.

5. lab_result_components

Purpose
Stores individual result values (components) for a given lab_results header. Supports numeric, text, and coded values, reference ranges, and abnormal flags.

Field Specifications

Field Type Nullable Default Constraint Description
component_id BIGINT NO IDENTITY PK Component identifier
result_id BIGINT NO — FK → lab_results.result_id Parent result header
component_code_loinc VARCHAR(20) YES — — LOINC code for component (if different from parent)
component_name VARCHAR(100) NO — — Display name (e.g., “Hemoglobin”)
value_numeric NUMERIC(18,6) YES — — Numeric value, if applicable
value_text VARCHAR(255) YES — — Textual value (e.g., “Positive”)
value_coded VARCHAR(50) YES — — Coded value (e.g., SNOMED code)
unit VARCHAR(30) YES — — Unit of measure (UCUM where possible)
reference_range_low NUMERIC(18,6) YES — — Lower normal limit
reference_range_high NUMERIC(18,6) YES — — Upper normal limit
abnormal_flag VARCHAR(5) YES — IN ('L','H','LL','HH','N','A') Abnormality indicator
interpretation VARCHAR(255) YES — — Narrative interpretation
delta_from_previous NUMERIC(18,6) YES — — Difference from previous numeric result
delta_flag VARCHAR(10) YES — IN ('none','significant') Delta check outcome
sort_order INT YES — — Display order within panel
created_at TIMESTAMP NO CURRENT_TIMESTAMP — Creation timestamp

SQL DDL

SQL
CREATE TABLE lab_result_components (
    component_id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    result_id             BIGINT NOT NULL,
    component_code_loinc  VARCHAR(20) NULL,
    component_name        VARCHAR(100) NOT NULL,
    value_numeric         NUMERIC(18,6) NULL,
    value_text            VARCHAR(255) NULL,
    value_coded           VARCHAR(50) NULL,
    unit                  VARCHAR(30) NULL,
    reference_range_low   NUMERIC(18,6) NULL,
    reference_range_high  NUMERIC(18,6) NULL,
    abnormal_flag         VARCHAR(5) NULL,
    interpretation        VARCHAR(255) NULL,
    delta_from_previous   NUMERIC(18,6) NULL,
    delta_flag            VARCHAR(10) NULL,
    sort_order            INT NULL,
    created_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_lab_result_components_result
        FOREIGN KEY (result_id) REFERENCES lab_results(result_id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT chk_lab_result_components_abnormal
        CHECK (abnormal_flag IS NULL OR abnormal_flag IN ('L','H','LL','HH','N','A')),
    CONSTRAINT chk_lab_result_components_delta
        CHECK (delta_flag IS NULL OR delta_flag IN ('none','significant'))
);

CREATE INDEX idx_lab_result_components_result ON lab_result_components(result_id);
CREATE INDEX idx_lab_result_components_loinc ON lab_result_components(component_code_loinc);
CREATE INDEX idx_lab_result_components_abnormal ON lab_result_components(abnormal_flag);

Terminology Bindings

Field Terminology Example Value
component_code_loinc LOINC 718-7
value_coded SNOMED CT / Local 10828004 (Positive)
unit UCUM g/dL

FHIR Resource Mapping

Each component typically maps to an Observation:

Table Field FHIR Resource FHIR Path
component_id Observation Observation.id (or component-level if grouped)
result_id Observation Observation.partOf (DiagnosticReport)
component_code_loinc Observation Observation.code.coding
component_name Observation Observation.code.text
value_numeric Observation Observation.valueQuantity.value
value_text Observation Observation.valueString
value_coded Observation Observation.valueCodeableConcept.coding
unit Observation Observation.valueQuantity.unit
reference_range_low/high Observation Observation.referenceRange.low/high
abnormal_flag Observation Observation.interpretation

6. lab_panels

Purpose
Defines lab panels/profiles (e.g., CBC, BMP) with LOINC codes and lab section. Used to expand panel orders into component tests.

Field Specifications

Field Type Nullable Default Constraint Description
panel_id BIGINT NO IDENTITY PK Panel identifier
panel_code_loinc VARCHAR(20) YES — — LOINC code for the panel
panel_name VARCHAR(100) NO — UNIQUE Panel name (e.g., “CBC”)
lab_section VARCHAR(30) NO — Same set as lab_order_tests.lab_section Section owning the panel
specimen_type VARCHAR(30) NO — — Required specimen type
is_active BOOLEAN NO TRUE — Active flag
effective_from DATE YES — — Start date for panel definition
effective_to DATE YES — — End date (for versioning)
created_at TIMESTAMP NO CURRENT_TIMESTAMP — Creation timestamp
updated_at TIMESTAMP NO CURRENT_TIMESTAMP — Last update timestamp

SQL DDL

SQL
CREATE TABLE lab_panels (
    panel_id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    panel_code_loinc VARCHAR(20) NULL,
    panel_name       VARCHAR(100) NOT NULL,
    lab_section      VARCHAR(30) NOT NULL,
    specimen_type    VARCHAR(30) NOT NULL,
    is_active        BOOLEAN NOT NULL DEFAULT TRUE,
    effective_from   DATE NULL,
    effective_to     DATE NULL,
    created_at       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_lab_panels_name UNIQUE (panel_name),
    CONSTRAINT chk_lab_panels_section
        CHECK (lab_section IN ('chemistry','hematology','microbiology','pathology','immunology','coagulation','blood_bank','poct','sendout'))
);

CREATE INDEX idx_lab_panels_loinc ON lab_panels(panel_code_loinc);
CREATE INDEX idx_lab_panels_active ON lab_panels(is_active);

Terminology Bindings

Field Terminology Example Value
panel_code_loinc LOINC 57021-8 (CBC panel)
lab_section Local Lab Section Code Set hematology

FHIR Resource Mapping

Panels are typically represented as ServiceRequest with multiple Observation members:

Table Field FHIR Resource FHIR Path
panel_code_loinc ServiceRequest ServiceRequest.code.coding
panel_name ServiceRequest ServiceRequest.code.text

7. lab_panel_components

Purpose
Defines which tests belong to each panel and their display order and requirement status.

Field Specifications

Field Type Nullable Default Constraint Description
panel_id BIGINT NO — FK → lab_panels.panel_id Parent panel
component_test_code VARCHAR(30) NO — — Test code (usually LOINC or local test code)
component_name VARCHAR(100) NO — — Component test name
display_order INT NO 0 — Order within panel
is_required BOOLEAN NO TRUE — If false, optional component
default_included BOOLEAN NO TRUE — Included by default when panel ordered
created_at TIMESTAMP NO CURRENT_TIMESTAMP — Creation timestamp

Composite PK: (panel_id,component_test_code).

SQL DDL

SQL
CREATE TABLE lab_panel_components (
    panel_id           BIGINT NOT NULL,
    component_test_code VARCHAR(30) NOT NULL,
    component_name     VARCHAR(100) NOT NULL,
    display_order      INT NOT NULL DEFAULT 0,
    is_required        BOOLEAN NOT NULL DEFAULT TRUE,
    default_included   BOOLEAN NOT NULL DEFAULT TRUE,
    created_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT pk_lab_panel_components PRIMARY KEY (panel_id, component_test_code),

    CONSTRAINT fk_lab_panel_components_panel
        FOREIGN KEY (panel_id) REFERENCES lab_panels(panel_id)
        ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE INDEX idx_lab_panel_components_panel ON lab_panel_components(panel_id);

Terminology Bindings

Field Terminology Example Value
component_test_code LOINC / Local Test Catalog 718-7

FHIR Resource Mapping

Panel composition is usually implicit; no direct FHIR mapping beyond using panel LOINC and member Observations.

8. lab_analyzers

Purpose
Registry of lab analyzers/instruments, including facility, section, and connectivity details. Used for interface routing and QC association.

Field Specifications

Field Type Nullable Default Constraint Description
analyzer_id BIGINT NO IDENTITY PK Analyzer identifier
facility_id BIGINT NO — FK → facilities.facility_id Facility where analyzer is installed
lab_section VARCHAR(30) NO — Same set as above Section (chemistry, etc.)
analyzer_name VARCHAR(100) NO — — Friendly name (e.g., “Cobas 8000”)
manufacturer VARCHAR(100) NO — — Manufacturer
model VARCHAR(50) NO — — Model
serial_number VARCHAR(50) NO — UNIQUE Serial number
ip_address VARCHAR(45) YES — — IPv4/IPv6 address
interface_type VARCHAR(30) NO — IN ('ASTM','HL7','POCT1-A','file','manual') Interface type
is_active BOOLEAN NO TRUE — Active flag
last_maintenance_date DATE YES — — Last preventive maintenance
next_maintenance_due DATE YES — — Next scheduled maintenance
location_description VARCHAR(100) YES — — Room/bench location
created_at TIMESTAMP NO CURRENT_TIMESTAMP — Creation timestamp

SQL DDL

SQL
CREATE TABLE lab_analyzers (
    analyzer_id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    facility_id           BIGINT NOT NULL,
    lab_section           VARCHAR(30) NOT NULL,
    analyzer_name         VARCHAR(100) NOT NULL,
    manufacturer          VARCHAR(100) NOT NULL,
    model                 VARCHAR(50) NOT NULL,
    serial_number         VARCHAR(50) NOT NULL,
    ip_address            VARCHAR(45) NULL,
    interface_type        VARCHAR(30) NOT NULL,
    is_active             BOOLEAN NOT NULL DEFAULT TRUE,
    last_maintenance_date DATE NULL,
    next_maintenance_due  DATE NULL,
    location_description  VARCHAR(100) NULL,
    created_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_lab_analyzers_serial UNIQUE (serial_number),
    CONSTRAINT fk_lab_analyzers_facility
        FOREIGN KEY (facility_id) REFERENCES facilities(facility_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_lab_analyzers_section
        CHECK (lab_section IN ('chemistry','hematology','microbiology','pathology','immunology','coagulation','blood_bank','poct','sendout')),
    CONSTRAINT chk_lab_analyzers_interface
        CHECK (interface_type IN ('ASTM','HL7','POCT1-A','file','manual'))
);

CREATE INDEX idx_lab_analyzers_facility_section ON lab_analyzers(facility_id, lab_section);

FHIR Resource Mapping

Table Field FHIR Resource FHIR Path
analyzer_id Device Device.id
analyzer_name Device Device.deviceName.name
manufacturer Device Device.manufacturer
model Device Device.modelNumber
serial_number Device Device.serialNumber
facility_id Device Device.owner (Organization)

9. lab_analyzer_interfaces

Purpose
Configuration for analyzer interfaces: protocol, host, port, and field mappings for ASTM/HL7 messages.

Field Specifications

Field Type Nullable Default Constraint Description
interface_id BIGINT NO IDENTITY PK Interface identifier
analyzer_id BIGINT NO — FK → lab_analyzers.analyzer_id Linked analyzer
protocol_type VARCHAR(30) NO — IN ('ASTM','HL7v2','file') Protocol
host_ip VARCHAR(45) YES — — Host IP (for TCP)
port INT YES — — TCP port
message_format VARCHAR(20) NO — IN ('E1381','E1394','HL7_2.5.1','CSV') Message format
field_mapping_json JSONB YES — — JSON mapping analyzer fields → LIS fields
is_active BOOLEAN NO TRUE — Active flag
last_connection_datetime TIMESTAMP YES — — Last successful connection
created_at TIMESTAMP NO CURRENT_TIMESTAMP — Creation timestamp

SQL DDL

SQL
CREATE TABLE lab_analyzer_interfaces (
    interface_id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    analyzer_id            BIGINT NOT NULL,
    protocol_type          VARCHAR(30) NOT NULL,
    host_ip                VARCHAR(45) NULL,
    port                   INT NULL,
    message_format         VARCHAR(20) NOT NULL,
    field_mapping_json     JSONB NULL,
    is_active              BOOLEAN NOT NULL DEFAULT TRUE,
    last_connection_datetime TIMESTAMP NULL,
    created_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_lab_analyzer_interfaces_analyzer
        FOREIGN KEY (analyzer_id) REFERENCES lab_analyzers(analyzer_id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT chk_lab_analyzer_interfaces_protocol
        CHECK (protocol_type IN ('ASTM','HL7v2','file')),
    CONSTRAINT chk_lab_analyzer_interfaces_format
        CHECK (message_format IN ('E1381','E1394','HL7_2.5.1','CSV'))
);

CREATE INDEX idx_lab_analyzer_interfaces_analyzer ON lab_analyzer_interfaces(analyzer_id);
CREATE INDEX idx_lab_analyzer_interfaces_active ON lab_analyzer_interfaces(is_active);

10. lab_qc_records

Purpose
Stores quality control (QC) runs for analyzers, including Westgard rule evaluation and corrective actions. Supports QC dashboards and regulatory audits.

Field Specifications

Field Type Nullable Default Constraint Description
qc_id BIGINT NO IDENTITY PK QC run identifier
analyzer_id BIGINT NO — FK → lab_analyzers.analyzer_id Analyzer
test_code VARCHAR(30) NO — — Test code (LOINC or local)
control_level VARCHAR(10) NO — — Level (e.g., L1, L2, L3)
control_lot VARCHAR(50) NO — — Control lot number
observed_value NUMERIC(18,6) NO — — Observed QC value
expected_mean NUMERIC(18,6) NO — — Expected mean
expected_sd NUMERIC(18,6) NO — — Expected standard deviation
westgard_rule_violated VARCHAR(50) YES — — Rule violated (e.g., “1-3s”)
qc_status VARCHAR(20) NO 'in_control' IN ('in_control','out_of_control') QC outcome
run_datetime TIMESTAMP NO CURRENT_TIMESTAMP — When QC was run
technologist_id BIGINT NO — FK → users.user_id Technologist performing QC
corrective_action TEXT YES — — Description of corrective action
comment TEXT YES — — Additional notes

SQL DDL

SQL
CREATE TABLE lab_qc_records (
    qc_id                 BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    analyzer_id           BIGINT NOT NULL,
    test_code             VARCHAR(30) NOT NULL,
    control_level         VARCHAR(10) NOT NULL,
    control_lot           VARCHAR(50) NOT NULL,
    observed_value        NUMERIC(18,6) NOT NULL,
    expected_mean         NUMERIC(18,6) NOT NULL,
    expected_sd           NUMERIC(18,6) NOT NULL,
    westgard_rule_violated VARCHAR(50) NULL,
    qc_status             VARCHAR(20) NOT NULL DEFAULT 'in_control',
    run_datetime          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    technologist_id       BIGINT NOT NULL,
    corrective_action     TEXT NULL,
    comment               TEXT NULL,

    CONSTRAINT fk_lab_qc_records_analyzer
        FOREIGN KEY (analyzer_id) REFERENCES lab_analyzers(analyzer_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_lab_qc_records_technologist
        FOREIGN KEY (technologist_id) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT chk_lab_qc_records_status
        CHECK (qc_status IN ('in_control','out_of_control'))
);

CREATE INDEX idx_lab_qc_records_analyzer ON lab_qc_records(analyzer_id, test_code);
CREATE INDEX idx_lab_qc_records_run_datetime ON lab_qc_records(run_datetime);
CREATE INDEX idx_lab_qc_records_status ON lab_qc_records(qc_status);

11. lab_reference_ranges

Purpose
Stores reference ranges and critical thresholds per test, age band, gender, and unit. Used for flagging abnormal and critical results.

Field Specifications

Field Type Nullable Default Constraint Description
range_id BIGINT NO IDENTITY PK Range identifier
test_code_loinc VARCHAR(20) NO — — Test LOINC code
age_min INT NO 0 — Minimum age in years (inclusive)
age_max INT NO 200 — Maximum age in years (exclusive)
gender VARCHAR(10) NO 'any' IN ('male','female','any') Gender applicability
low_value NUMERIC(18,6) YES — — Lower reference limit
high_value NUMERIC(18,6) YES — — Upper reference limit
critical_low NUMERIC(18,6) YES — — Critical low threshold
critical_high NUMERIC(18,6) YES — — Critical high threshold
unit VARCHAR(30) NO — — Unit of measure
effective_date DATE NO CURRENT_DATE — Date from which range is effective
inactive_date DATE YES — — Date after which range is inactive
created_by BIGINT NO — FK → users.user_id User who configured range

Unique constraint on (test_code_loinc,age_min,age_max,gender,unit,effective_date).

SQL DDL

SQL
CREATE TABLE lab_reference_ranges (
    range_id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    test_code_loinc VARCHAR(20) NOT NULL,
    age_min        INT NOT NULL DEFAULT 0,
    age_max        INT NOT NULL DEFAULT 200,
    gender         VARCHAR(10) NOT NULL DEFAULT 'any',
    low_value      NUMERIC(18,6) NULL,
    high_value     NUMERIC(18,6) NULL,
    critical_low   NUMERIC(18,6) NULL,
    critical_high  NUMERIC(18,6) NULL,
    unit           VARCHAR(30) NOT NULL,
    effective_date DATE NOT NULL DEFAULT CURRENT_DATE,
    inactive_date  DATE NULL,
    created_by     BIGINT NOT NULL,

    CONSTRAINT fk_lab_reference_ranges_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_lab_reference_ranges_gender
        CHECK (gender IN ('male','female','any')),
    CONSTRAINT uq_lab_reference_ranges UNIQUE (test_code_loinc, age_min, age_max, gender, unit, effective_date)
);

CREATE INDEX idx_lab_reference_ranges_test ON lab_reference_ranges(test_code_loinc, gender, age_min, age_max);

Terminology Bindings

Field Terminology Example Value
test_code_loinc LOINC 718-7
gender Administrative Gender male

FHIR Resource Mapping

Reference ranges are mapped into Observation.referenceRange when constructing FHIR Observations.

12. lab_critical_values

Purpose
Defines critical value thresholds and notification requirements per test and age group. Drives WF-LIS-005 (Critical Value Notification).

Field Specifications

Field Type Nullable Default Constraint Description
critical_id BIGINT NO IDENTITY PK Critical threshold identifier
test_code_loinc VARCHAR(20) NO — — Test LOINC code
age_group VARCHAR(30) NO — — Age group label (e.g., “adult”, “neonate”)
critical_low NUMERIC(18,6) YES — — Critical low
critical_high NUMERIC(18,6) YES — — Critical high
notification_required BOOLEAN NO TRUE — Whether notification is required
escalation_minutes INT NO 30 — Minutes to escalate if unacknowledged
created_by BIGINT NO — FK → users.user_id Configuring user
effective_date DATE NO CURRENT_DATE — Effective date

SQL DDL

SQL
CREATE TABLE lab_critical_values (
    critical_id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    test_code_loinc      VARCHAR(20) NOT NULL,
    age_group            VARCHAR(30) NOT NULL,
    critical_low         NUMERIC(18,6) NULL,
    critical_high        NUMERIC(18,6) NULL,
    notification_required BOOLEAN NOT NULL DEFAULT TRUE,
    escalation_minutes   INT NOT NULL DEFAULT 30,
    created_by           BIGINT NOT NULL,
    effective_date       DATE NOT NULL DEFAULT CURRENT_DATE,

    CONSTRAINT fk_lab_critical_values_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE INDEX idx_lab_critical_values_test ON lab_critical_values(test_code_loinc, age_group);

13. lab_critical_notifications

Purpose
Tracks critical value notifications from identification through acknowledgment and escalation, supporting UAE regulatory and accreditation requirements.

Field Specifications

Field Type Nullable Default Constraint Description
notification_id BIGINT NO IDENTITY PK Notification identifier
result_id BIGINT NO — FK → lab_results.result_id Critical result
critical_value NUMERIC(18,6) YES — — Critical numeric value (if applicable)
notifying_tech_id BIGINT NO — FK → users.user_id Lab staff initiating notification
target_provider_id BIGINT NO — FK → providers.provider_id Intended recipient provider
notification_method VARCHAR(20) NO — IN ('in_app','phone','sms','pager') Primary method used
sent_datetime TIMESTAMP NO CURRENT_TIMESTAMP — When notification sent
acknowledged_datetime TIMESTAMP YES — — When acknowledged
acknowledged_by BIGINT YES — FK → users.user_id User acknowledging (may be nurse)
escalation_level INT NO 0 — 0 = initial, 1 = first escalation, etc.
read_back_confirmed BOOLEAN NO FALSE — True if read-back documented
read_back_text TEXT YES — — Optional read-back text
status VARCHAR(20) NO 'pending' IN ('pending','acknowledged','escalated','closed') Notification status
created_at TIMESTAMP NO CURRENT_TIMESTAMP — Creation timestamp

SQL DDL

SQL
CREATE TABLE lab_critical_notifications (
    notification_id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    result_id             BIGINT NOT NULL,
    critical_value        NUMERIC(18,6) NULL,
    notifying_tech_id     BIGINT NOT NULL,
    target_provider_id    BIGINT NOT NULL,
    notification_method   VARCHAR(20) NOT NULL,
    sent_datetime         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    acknowledged_datetime TIMESTAMP NULL,
    acknowledged_by       BIGINT NULL,
    escalation_level      INT NOT NULL DEFAULT 0,
    read_back_confirmed   BOOLEAN NOT NULL DEFAULT FALSE,
    read_back_text        TEXT NULL,
    status                VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_lab_critical_notifications_result
        FOREIGN KEY (result_id) REFERENCES lab_results(result_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_lab_critical_notifications_tech
        FOREIGN KEY (notifying_tech_id) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_lab_critical_notifications_target_provider
        FOREIGN KEY (target_provider_id) REFERENCES providers(provider_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_lab_critical_notifications_ack_by
        FOREIGN KEY (acknowledged_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT chk_lab_critical_notifications_method
        CHECK (notification_method IN ('in_app','phone','sms','pager')),
    CONSTRAINT chk_lab_critical_notifications_status
        CHECK (status IN ('pending','acknowledged','escalated','closed'))
);

CREATE INDEX idx_lab_critical_notifications_result ON lab_critical_notifications(result_id);
CREATE INDEX idx_lab_critical_notifications_status ON lab_critical_notifications(status);
CREATE INDEX idx_lab_critical_notifications_sent ON lab_critical_notifications(sent_datetime);

14. lab_sendout_orders

Purpose
Tracks tests sent to external reference laboratories, including shipping details, TAT, and result receipt.

Field Specifications

Field Type Nullable Default Constraint Description
sendout_id BIGINT NO IDENTITY PK Send-out identifier
order_test_id BIGINT NO — FK → lab_order_tests.order_test_id Associated test
reference_lab_id BIGINT YES — — Reference lab master ID (if maintained elsewhere)
reference_lab_name VARCHAR(100) NO — — Reference lab name
shipped_datetime TIMESTAMP YES — — When shipped
tracking_number VARCHAR(50) YES — — Courier tracking number
expected_tat_days INT YES — — Expected TAT in days
result_received_datetime TIMESTAMP YES — — When result received
status VARCHAR(20) NO 'pending_shipment' IN ('pending_shipment','shipped','received','cancelled') Send-out status
shipping_conditions VARCHAR(100) YES — — e.g., “Frozen, dry ice”
comment TEXT YES — — Notes
created_at TIMESTAMP NO CURRENT_TIMESTAMP — Creation timestamp

SQL DDL

SQL
CREATE TABLE lab_sendout_orders (
    sendout_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_test_id           BIGINT NOT NULL,
    reference_lab_id        BIGINT NULL,
    reference_lab_name      VARCHAR(100) NOT NULL,
    shipped_datetime        TIMESTAMP NULL,
    tracking_number         VARCHAR(50) NULL,
    expected_tat_days       INT NULL,
    result_received_datetime TIMESTAMP NULL,
    status                  VARCHAR(20) NOT NULL DEFAULT 'pending_shipment',
    shipping_conditions     VARCHAR(100) NULL,
    comment                 TEXT NULL,
    created_at              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_lab_sendout_orders_order_test
        FOREIGN KEY (order_test_id) REFERENCES lab_order_tests(order_test_id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT chk_lab_sendout_orders_status
        CHECK (status IN ('pending_shipment','shipped','received','cancelled'))
);

CREATE INDEX idx_lab_sendout_orders_status ON lab_sendout_orders(status);
CREATE INDEX idx_lab_sendout_orders_order_test ON lab_sendout_orders(order_test_id);

15. lab_micro_cultures

Purpose
Tracks microbiology culture processing for a specimen and ordered test, including plating, incubation, growth, and organism identification.

Field Specifications

Field Type Nullable Default Constraint Description
culture_id BIGINT NO IDENTITY PK Culture identifier
specimen_id BIGINT NO — FK → lab_specimens.specimen_id Source specimen
order_test_id BIGINT NO — FK → lab_order_tests.order_test_id Associated test
plating_datetime TIMESTAMP YES — — When plated
media_type VARCHAR(100) YES — — Media used
incubation_conditions VARCHAR(100) YES — — Time/temp/atmosphere
growth_status VARCHAR(30) YES — IN ('no_growth','growth','contaminant','mixed_flora','pending') Growth status
organism_code_snomed VARCHAR(20) YES — — SNOMED CT organism code
organism_name VARCHAR(255) YES — — Organism name
colony_count INT YES — — Colony count (CFU/mL)
identification_method VARCHAR(50) YES — — e.g., MALDI-TOF
identified_by BIGINT YES — FK → users.user_id Microbiologist/technologist
identified_datetime TIMESTAMP YES — — When organism identified
comment TEXT YES — — Notes

SQL DDL

SQL
CREATE TABLE lab_micro_cultures (
    culture_id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    specimen_id           BIGINT NOT NULL,
    order_test_id         BIGINT NOT NULL,
    plating_datetime      TIMESTAMP NULL,
    media_type            VARCHAR(100) NULL,
    incubation_conditions VARCHAR(100) NULL,
    growth_status         VARCHAR(30) NULL,
    organism_code_snomed  VARCHAR(20) NULL,
    organism_name         VARCHAR(255) NULL,
    colony_count          INT NULL,
    identification_method VARCHAR(50) NULL,
    identified_by         BIGINT NULL,
    identified_datetime   TIMESTAMP NULL,
    comment               TEXT NULL,

    CONSTRAINT fk_lab_micro_cultures_specimen
        FOREIGN KEY (specimen_id) REFERENCES lab_specimens(specimen_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_lab_micro_cultures_order_test
        FOREIGN KEY (order_test_id) REFERENCES lab_order_tests(order_test_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_lab_micro_cultures_identified_by
        FOREIGN KEY (identified_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT chk_lab_micro_cultures_growth_status
        CHECK (growth_status IS NULL OR growth_status IN ('no_growth','growth','contaminant','mixed_flora','pending'))
);

CREATE INDEX idx_lab_micro_cultures_specimen ON lab_micro_cultures(specimen_id);
CREATE INDEX idx_lab_micro_cultures_order_test ON lab_micro_cultures(order_test_id);
CREATE INDEX idx_lab_micro_cultures_organism ON lab_micro_cultures(organism_code_snomed);

Terminology Bindings

Field Terminology Example Value
organism_code_snomed SNOMED CT Organism 112283007 (Escherichia coli)

FHIR Resource Mapping

Microbiology results can be represented as Observation with category laboratory and code representing culture; organism details may use Observation.component or DiagnosticReport narrative.

16. lab_micro_sensitivities

Purpose
Stores antibiotic susceptibility results for a given culture, including MIC and interpretation (S/I/R) and cascade reporting level.

Field Specifications

Field Type Nullable Default Constraint Description
sensitivity_id BIGINT NO IDENTITY PK Sensitivity identifier
culture_id BIGINT NO — FK → lab_micro_cultures.culture_id Parent culture
antibiotic_code VARCHAR(20) NO — — Antibiotic code (RxNorm/local)
antibiotic_name VARCHAR(100) NO — — Antibiotic name
mic_value NUMERIC(18,6) YES — — MIC value
interpretation VARCHAR(5) NO — IN ('S','I','R') Susceptibility interpretation
testing_method VARCHAR(50) YES — — e.g., “E-test”, “Broth microdilution”
cascade_level INT YES — — Cascade reporting level
is_reportable BOOLEAN NO TRUE — Whether reported to clinician
comment TEXT YES — — Notes

SQL DDL

SQL
CREATE TABLE lab_micro_sensitivities (
    sensitivity_id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    culture_id       BIGINT NOT NULL,
    antibiotic_code  VARCHAR(20) NOT NULL,
    antibiotic_name  VARCHAR(100) NOT NULL,
    mic_value        NUMERIC(18,6) NULL,
    interpretation   VARCHAR(5) NOT NULL,
    testing_method   VARCHAR(50) NULL,
    cascade_level    INT NULL,
    is_reportable    BOOLEAN NOT NULL DEFAULT TRUE,
    comment          TEXT NULL,

    CONSTRAINT fk_lab_micro_sensitivities_culture
        FOREIGN KEY (culture_id) REFERENCES lab_micro_cultures(culture_id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT chk_lab_micro_sensitivities_interp
        CHECK (interpretation IN ('S','I','R'))
);

CREATE INDEX idx_lab_micro_sensitivities_culture ON lab_micro_sensitivities(culture_id);
CREATE INDEX idx_lab_micro_sensitivities_antibiotic ON lab_micro_sensitivities(antibiotic_code);

Terminology Bindings

Field Terminology Example Value
antibiotic_code RxNorm / Local Antibiotic Master 36567 (Ciprofloxacin)
interpretation CLSI/EUCAST S/I/R S

17. lab_pathology_cases

Purpose
Tracks surgical pathology and cytology cases, including gross and microscopic descriptions, SNOMED diagnosis, ICD-10-AM coding, and sign-out details.

Field Specifications

Field Type Nullable Default Constraint Description
case_id BIGINT NO IDENTITY PK Pathology case identifier
specimen_id BIGINT NO — FK → lab_specimens.specimen_id Source specimen
order_test_id BIGINT NO — FK → lab_order_tests.order_test_id Associated order test
case_number VARCHAR(50) NO — UNIQUE Case number (e.g., “SP-2026-000123”)
case_type VARCHAR(30) NO — IN ('surgical','cytology','autopsy') Case type
gross_description TEXT YES — — Gross description
microscopic_description TEXT YES — — Microscopic description
diagnosis_text TEXT YES — — Narrative diagnosis
diagnosis_code_snomed VARCHAR(20) YES — — SNOMED CT morphology/diagnosis code
icd10_code VARCHAR(10) YES — ICD-10-AM ICD-10-AM diagnosis code
pathologist_id BIGINT YES — FK → providers.provider_id Signing pathologist
signed_datetime TIMESTAMP YES — — When signed out
status VARCHAR(20) NO 'in_progress' IN ('in_progress','pending_review','final','amended') Case status
synoptic_report_json JSONB YES — — Structured synoptic report (e.g., CAP templates)
created_at TIMESTAMP NO CURRENT_TIMESTAMP — Creation timestamp
updated_at TIMESTAMP NO CURRENT_TIMESTAMP — Last update timestamp

SQL DDL

SQL
CREATE TABLE lab_pathology_cases (
    case_id               BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    specimen_id           BIGINT NOT NULL,
    order_test_id         BIGINT NOT NULL,
    case_number           VARCHAR(50) NOT NULL,
    case_type             VARCHAR(30) NOT NULL,
    gross_description     TEXT NULL,
    microscopic_description TEXT NULL,
    diagnosis_text        TEXT NULL,
    diagnosis_code_snomed VARCHAR(20) NULL,
    icd10_code            VARCHAR(10) NULL,
    pathologist_id        BIGINT NULL,
    signed_datetime       TIMESTAMP NULL,
    status                VARCHAR(20) NOT NULL DEFAULT 'in_progress',
    synoptic_report_json  JSONB NULL,
    created_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_lab_pathology_cases_number UNIQUE (case_number),

    CONSTRAINT fk_lab_pathology_cases_specimen
        FOREIGN KEY (specimen_id) REFERENCES lab_specimens(specimen_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_lab_pathology_cases_order_test
        FOREIGN KEY (order_test_id) REFERENCES lab_order_tests(order_test_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_lab_pathology_cases_pathologist
        FOREIGN KEY (pathologist_id) REFERENCES providers(provider_id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT chk_lab_pathology_cases_type
        CHECK (case_type IN ('surgical','cytology','autopsy')),
    CONSTRAINT chk_lab_pathology_cases_status
        CHECK (status IN ('in_progress','pending_review','final','amended'))
);

CREATE INDEX idx_lab_pathology_cases_case_number ON lab_pathology_cases(case_number);
CREATE INDEX idx_lab_pathology_cases_status ON lab_pathology_cases(status);
CREATE INDEX idx_lab_pathology_cases_icd10 ON lab_pathology_cases(icd10_code);

Terminology Bindings

Field Terminology Example Value
diagnosis_code_snomed SNOMED CT 254837009 (Adenocarcinoma)
icd10_code ICD-10-AM C18.7

FHIR Resource Mapping

Pathology cases can be represented as DiagnosticReport with category pathology, with narrative and structured data in DiagnosticReport.conclusion and DiagnosticReport.presentedForm.

Data Volume Estimates

Approximate volumes for a medium-to-large UAE hospital (per facility):

Table Estimated Rows (Current) Annual Growth Notes
lab_orders 1,000,000 ~10–15% One per order; driven by outpatient + inpatient volume
lab_order_tests 4,000,000 ~10–15% Average 4 tests per order
lab_specimens 2,500,000 ~10–15% Multiple specimens per order (tubes, aliquots)
lab_results 4,000,000 ~10–15% One per ordered test
lab_result_components 20,000,000 ~10–15% Panels expand into multiple components
lab_panels 100 Low Stable master data
lab_panel_components 2,000 Low Stable; changes with test menu
lab_analyzers 50 Very low Limited by equipment count
lab_analyzer_interfaces 50 Very low One or more per analyzer
lab_qc_records 500,000 ~10% Multiple QC runs per day per analyzer/test
lab_reference_ranges 3,000 Low Changes with validation studies
lab_critical_values 200 Low Changes infrequently
lab_critical_notifications 50,000 ~10% Depends on critical result frequency
lab_sendout_orders 20,000 ~5–10% Esoteric tests only
lab_micro_cultures 150,000 ~10% Microbiology workload
lab_micro_sensitivities 1,000,000 ~10% Multiple antibiotics per culture
lab_pathology_cases 25,000 ~5–10% Surgical + cytology cases

These estimates inform index design and partitioning (e.g., yearly partitioning for lab_result_components and lab_results).

Data Retention Policy

Retention must comply with UAE federal and emirate-level regulations (MOH, DOH, DHA) and UAE PDPL. Typical practice is minimum 10 years for clinical records, with longer retention for pathology and pediatric records as per facility policy.

Table Recommended Retention Rationale / Notes
lab_orders ≥ 10 years from order date Clinical and medico-legal record of ordered tests
lab_order_tests ≥ 10 years Linked to results and billing; required for audit trails
lab_specimens ≥ 10 years for metadata; physical specimen retention per test type (e.g., days–weeks) Metadata supports chain-of-custody and incident investigations
lab_results ≥ 10 years; consider 15–20 years for oncology and chronic disease Critical for longitudinal care and HIE submissions (NABIDH/Malaffi)
lab_result_components Same as lab_results Part of clinical record
lab_panels Retain all versions indefinitely or ≥ 15 years Needed for historical interpretation of results
lab_panel_components Same as lab_panels Panel composition affects interpretation
lab_analyzers Life of device + 10 years Supports traceability of results to devices
lab_analyzer_interfaces Life of interface + 10 years For integration audits and incident review
lab_qc_records ≥ 5–10 years QC documentation required for accreditation and legal defense
lab_reference_ranges Indefinite or ≥ 15 years Historical ranges required to interpret past results
lab_critical_values Indefinite or ≥ 10 years Policy history for critical thresholds
lab_critical_notifications ≥ 10 years Demonstrates compliance with critical value communication requirements
lab_sendout_orders ≥ 10 years Traceability with external labs and payers
lab_micro_cultures ≥ 10 years Infection control, antimicrobial stewardship, and medico-legal needs
lab_micro_sensitivities ≥ 10 years Supports antibiogram generation and stewardship analytics
lab_pathology_cases ≥ 20 years (or per facility policy, often lifetime) High medico-legal value; supports cancer registries

Archival strategies (e.g., partitioning, cold storage) must ensure data remains accessible for clinical care, audits, and legal requests while respecting UAE PDPL requirements for security, access control, and eventual deletion when legally permissible.

← Previous Laboratory Information System User Roles & Permissions Next → Laboratory Information System Screen Requirements
content/clinical/lis/03-data-specifications.md Generated 2026-02-20 22:54