Patient Access Data Specifications

CREATE TABLE eligibility_checks (
    check_id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id         BIGINT NOT NULL,
    payer_id           BIGINT NOT NULL,
    plan_id            BIGINT NULL,
    encounter_id       BIGINT NULL,
    appointment_id     BIGINT NULL,
    facility_id        BIGINT NOT NULL,
    check_datetime     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    check_type         VARCHAR(30) NOT NULL DEFAULT 'real_time',
    request_source     VARCHAR(30) NOT NULL DEFAULT 'registration',
    member_id          VARCHAR(50),
    group_id           VARCHAR(50),
    emirate_code       VARCHAR(20),
    status             VARCHAR(20) NOT NULL DEFAULT 'pending',
    is_batch           BOOLEAN NOT NULL DEFAULT FALSE,
    correlation_id     VARCHAR(100),
    raw_request        JSONB,
    error_message      VARCHAR(500),
    created_by         BIGINT,
    created_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_eligibility_check_type
        CHECK (check_type IN ('real_time','batch','pre_service','day_of_service')),
    CONSTRAINT chk_eligibility_request_source
        CHECK (request_source IN ('registration','scheduling','billing','portal','batch_job')),
    CONSTRAINT chk_eligibility_status
        CHECK (status IN ('pending','success','failed','no_response')),
    CONSTRAINT chk_eligibility_emirate
        CHECK (emirate_code IS NULL OR emirate_code IN ('AD','DXB','SHJ','AJM','UAQ','RAK','FUJ'))
);

COMMENT ON TABLE eligibility_checks IS 'Tracks insurance eligibility verification requests (DHA eClaimLink, DOH eClaims, direct payer APIs) for Patient Access module.';
COMMENT ON COLUMN eligibility_checks.emirate_code IS 'Two/three-letter code for UAE emirate where service is rendered or coverage applies.';

ALTER TABLE eligibility_checks
    ADD CONSTRAINT fk_elig_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_elig_payer
        FOREIGN KEY (payer_id) REFERENCES payers(payer_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_elig_plan
        FOREIGN KEY (plan_id) REFERENCES insurance_plans(plan_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_elig_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_elig_appointment
        FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_elig_facility
        FOREIGN KEY (facility_id) REFERENCES facilities(facility_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_elig_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL;

CREATE INDEX idx_elig_checks_patient ON eligibility_checks(patient_id);
CREATE INDEX idx_elig_checks_payer ON eligibility_checks(payer_id, plan_id);
CREATE INDEX idx_elig_checks_datetime ON eligibility_checks(check_datetime);
CREATE INDEX idx_elig_checks_status ON eligibility_checks(status);
CREATE INDEX idx_elig_checks_encounter ON eligibility_checks(encounter_id);
CREATE INDEX idx_elig_checks_appointment ON eligibility_checks(appointment_id);
CREATE INDEX idx_elig_checks_batch ON eligibility_checks(is_batch) WHERE is_batch = TRUE;

CREATE TABLE eligibility_responses (
    response_id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    check_id              BIGINT NOT NULL,
    is_eligible           BOOLEAN NOT NULL DEFAULT FALSE,
    effective_date        DATE,
    termination_date      DATE,
    copay_amount          NUMERIC(12,2) DEFAULT 0.00,
    copay_percentage      NUMERIC(5,2)  DEFAULT 0.00,
    deductible_total      NUMERIC(12,2) DEFAULT 0.00,
    deductible_met        NUMERIC(12,2) DEFAULT 0.00,
    coinsurance           NUMERIC(5,2)  DEFAULT 0.00,
    annual_max            NUMERIC(12,2) DEFAULT 0.00,
    annual_max_remaining  NUMERIC(12,2) DEFAULT 0.00,
    payer_response_code   VARCHAR(50),
    response_raw          JSONB,
    response_datetime     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    network_status        VARCHAR(20),
    plan_name             VARCHAR(200),
    coverage_summary      VARCHAR(500),

    CONSTRAINT fk_elig_response_check
        FOREIGN KEY (check_id) REFERENCES eligibility_checks(check_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT chk_elig_resp_copay_amt
        CHECK (copay_amount >= 0),
    CONSTRAINT chk_elig_resp_deductible
        CHECK (deductible_total >= 0 AND deductible_met >= 0),
    CONSTRAINT chk_elig_resp_coinsurance
        CHECK (coinsurance >= 0 AND coinsurance <= 100),
    CONSTRAINT chk_elig_resp_annual_max
        CHECK (annual_max >= 0 AND annual_max_remaining >= 0),
    CONSTRAINT chk_elig_resp_network
        CHECK (network_status IS NULL OR network_status IN ('in_network','out_of_network','unknown'))
);

CREATE INDEX idx_elig_responses_check ON eligibility_responses(check_id);
CREATE INDEX idx_elig_responses_eligible ON eligibility_responses(is_eligible);
CREATE INDEX idx_elig_responses_datetime ON eligibility_responses(response_datetime);

CREATE TABLE prior_authorizations (
    auth_id               BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id            BIGINT NOT NULL,
    encounter_id          BIGINT NULL,
    payer_id              BIGINT NOT NULL,
    requesting_provider_id BIGINT NOT NULL,
    facility_id           BIGINT NOT NULL,
    auth_number           VARCHAR(50),
    service_codes         TEXT NOT NULL,
    icd10_codes           TEXT NOT NULL,
    auth_status           VARCHAR(30) NOT NULL DEFAULT 'pending',
    requested_date        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    approved_date         TIMESTAMP NULL,
    denied_date           TIMESTAMP NULL,
    valid_from            DATE NULL,
    valid_to              DATE NULL,
    approved_units        INTEGER NULL,
    used_units            INTEGER DEFAULT 0,
    is_urgent             BOOLEAN NOT NULL DEFAULT FALSE,
    denial_reason         TEXT,
    notes                 TEXT,
    last_status_update    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by            BIGINT,
    updated_by            BIGINT,
    created_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_auth_number UNIQUE (auth_number),
    CONSTRAINT chk_auth_status
        CHECK (auth_status IN ('draft','pending','under_review','approved','partially_approved','denied','cancelled','expired')),
    CONSTRAINT chk_auth_units
        CHECK ((approved_units IS NULL OR approved_units >= 0) AND used_units >= 0),
    CONSTRAINT chk_auth_dates
        CHECK (valid_to IS NULL OR valid_from IS NULL OR valid_to >= valid_from)
);

ALTER TABLE prior_authorizations
    ADD CONSTRAINT fk_auth_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_auth_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_auth_payer
        FOREIGN KEY (payer_id) REFERENCES payers(payer_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_auth_provider
        FOREIGN KEY (requesting_provider_id) REFERENCES providers(provider_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_auth_facility
        FOREIGN KEY (facility_id) REFERENCES facilities(facility_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_auth_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_auth_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL;

CREATE INDEX idx_auth_patient ON prior_authorizations(patient_id);
CREATE INDEX idx_auth_payer_status ON prior_authorizations(payer_id, auth_status);
CREATE INDEX idx_auth_encounter ON prior_authorizations(encounter_id);
CREATE INDEX idx_auth_number ON prior_authorizations(auth_number);
CREATE INDEX idx_auth_validity ON prior_authorizations(valid_from, valid_to);
CREATE INDEX idx_auth_status ON prior_authorizations(auth_status);

CREATE TABLE prior_auth_requests (
    request_id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    auth_id               BIGINT NOT NULL,
    submission_datetime   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    submission_channel    VARCHAR(30) NOT NULL DEFAULT 'eClaimLink',
    clinical_documentation TEXT,
    payer_response        TEXT,
    response_datetime     TIMESTAMP NULL,
    reviewer_name         VARCHAR(200),
    peer_review_scheduled BOOLEAN NOT NULL DEFAULT FALSE,
    peer_review_datetime  TIMESTAMP NULL,
    peer_review_reference VARCHAR(50),
    attachments_metadata  TEXT,
    submitted_by          BIGINT,
    created_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_auth_request_channel
        CHECK (submission_channel IN ('eClaimLink','DOH_eClaims','portal','phone','fax','email')),
    CONSTRAINT chk_peer_review
        CHECK (peer_review_scheduled = FALSE OR peer_review_datetime IS NOT NULL)
);

ALTER TABLE prior_auth_requests
    ADD CONSTRAINT fk_auth_req_auth
        FOREIGN KEY (auth_id) REFERENCES prior_authorizations(auth_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    ADD CONSTRAINT fk_auth_req_user
        FOREIGN KEY (submitted_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL;

CREATE INDEX idx_auth_requests_auth ON prior_auth_requests(auth_id);
CREATE INDEX idx_auth_requests_channel ON prior_auth_requests(submission_channel);
CREATE INDEX idx_auth_requests_submitted ON prior_auth_requests(submission_datetime);

CREATE TABLE referrals (
    referral_id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id             BIGINT NOT NULL,
    referring_provider_id  BIGINT NOT NULL,
    specialist_provider_id BIGINT NULL,
    payer_id               BIGINT NULL,
    facility_id            BIGINT NOT NULL,
    referral_number        VARCHAR(50),
    service_type           VARCHAR(50) NOT NULL,
    authorized_visits      INTEGER NULL,
    used_visits            INTEGER DEFAULT 0,
    valid_from             DATE NULL,
    valid_to               DATE NULL,
    status                 VARCHAR(30) NOT NULL DEFAULT 'active',
    icd10_code             VARCHAR(10),
    notes                  TEXT,
    created_by             BIGINT,
    created_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_referral_visits
        CHECK ((authorized_visits IS NULL OR authorized_visits >= 0) AND used_visits >= 0),
    CONSTRAINT chk_referral_dates
        CHECK (valid_to IS NULL OR valid_from IS NULL OR valid_to >= valid_from),
    CONSTRAINT chk_referral_status
        CHECK (status IN ('draft','pending_payer','active','expired','closed','cancelled'))
);

ALTER TABLE referrals
    ADD CONSTRAINT fk_referral_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_referral_ref_provider
        FOREIGN KEY (referring_provider_id) REFERENCES providers(provider_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_referral_spec_provider
        FOREIGN KEY (specialist_provider_id) REFERENCES providers(provider_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_referral_payer
        FOREIGN KEY (payer_id) REFERENCES payers(payer_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_referral_facility
        FOREIGN KEY (facility_id) REFERENCES facilities(facility_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_referral_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL;

CREATE INDEX idx_referrals_patient ON referrals(patient_id);
CREATE INDEX idx_referrals_provider ON referrals(referring_provider_id);
CREATE INDEX idx_referrals_specialist ON referrals(specialist_provider_id);
CREATE INDEX idx_referrals_status ON referrals(status);
CREATE INDEX idx_referrals_validity ON referrals(valid_from, valid_to);

CREATE TABLE cost_estimates (
    estimate_id                    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id                     BIGINT NOT NULL,
    encounter_id                   BIGINT NULL,
    payer_id                       BIGINT NULL,
    plan_id                        BIGINT NULL,
    facility_id                    BIGINT NOT NULL,
    planned_cpt_codes              TEXT NOT NULL,
    estimated_charges              NUMERIC(12,2) NOT NULL DEFAULT 0.00,
    estimated_payer_payment        NUMERIC(12,2) DEFAULT 0.00,
    estimated_patient_responsibility NUMERIC(12,2) DEFAULT 0.00,
    deductible_applied             NUMERIC(12,2) DEFAULT 0.00,
    copay_applied                  NUMERIC(12,2) DEFAULT 0.00,
    coinsurance_applied            NUMERIC(12,2) DEFAULT 0.00,
    estimate_datetime              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    acknowledged_by_patient        BOOLEAN NOT NULL DEFAULT FALSE,
    acknowledged_datetime          TIMESTAMP NULL,
    valid_until                    DATE NULL,
    status                         VARCHAR(30) NOT NULL DEFAULT 'draft',
    created_by                     BIGINT,
    updated_by                     BIGINT,
    created_at                     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at                     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_estimate_amounts
        CHECK (estimated_charges >= 0
           AND estimated_payer_payment >= 0
           AND estimated_patient_responsibility >= 0
           AND deductible_applied >= 0
           AND copay_applied >= 0
           AND coinsurance_applied >= 0),
    CONSTRAINT chk_estimate_status
        CHECK (status IN ('draft','final','superseded','cancelled'))
);

ALTER TABLE cost_estimates
    ADD CONSTRAINT fk_estimate_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_estimate_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_estimate_payer
        FOREIGN KEY (payer_id) REFERENCES payers(payer_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_estimate_plan
        FOREIGN KEY (plan_id) REFERENCES insurance_plans(plan_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_estimate_facility
        FOREIGN KEY (facility_id) REFERENCES facilities(facility_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_estimate_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_estimate_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL;

CREATE INDEX idx_estimates_patient ON cost_estimates(patient_id);
CREATE INDEX idx_estimates_encounter ON cost_estimates(encounter_id);
CREATE INDEX idx_estimates_status ON cost_estimates(status);
CREATE INDEX idx_estimates_datetime ON cost_estimates(estimate_datetime);

CREATE TABLE financial_counseling_records (
    record_id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id           BIGINT NOT NULL,
    counselor_id         BIGINT NULL,
    counselor_user_id    BIGINT NULL,
    facility_id          BIGINT NOT NULL,
    session_datetime     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    counseling_type      VARCHAR(30) NOT NULL DEFAULT 'general',
    patient_situation    TEXT,
    options_presented    TEXT,
    patient_decision     TEXT,
    payment_plan_id      BIGINT,
    charity_application_id BIGINT,
    notes                TEXT,
    follow_up_required   BOOLEAN NOT NULL DEFAULT FALSE,
    follow_up_due_date   TIMESTAMP NULL,
    outcome_status       VARCHAR(30),

    created_at           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_counseling_type
        CHECK (counseling_type IN ('general','payment_plan','charity_care','government_assistance','follow_up')),
    CONSTRAINT chk_counseling_outcome
        CHECK (outcome_status IS NULL OR outcome_status IN ('open','in_progress','completed','declined')),
    CONSTRAINT chk_follow_up
        CHECK (follow_up_required = FALSE OR follow_up_due_date IS NOT NULL)
);

ALTER TABLE financial_counseling_records
    ADD CONSTRAINT fk_counseling_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_counseling_counselor
        FOREIGN KEY (counselor_id) REFERENCES providers(provider_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_counseling_user
        FOREIGN KEY (counselor_user_id) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_counseling_facility
        FOREIGN KEY (facility_id) REFERENCES facilities(facility_id)
        ON UPDATE CASCADE ON DELETE RESTRICT;

CREATE INDEX idx_counseling_patient ON financial_counseling_records(patient_id);
CREATE INDEX idx_counseling_facility ON financial_counseling_records(facility_id);
CREATE INDEX idx_counseling_session ON financial_counseling_records(session_datetime);
CREATE INDEX idx_counseling_outcome ON financial_counseling_records(outcome_status);

CREATE TABLE pre_registration_records (
    prereg_id                BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id               BIGINT NOT NULL,
    appointment_id           BIGINT NOT NULL,
    facility_id              BIGINT NOT NULL,
    invitation_sent_datetime TIMESTAMP NULL,
    completed_datetime       TIMESTAMP NULL,
    demographics_complete    BOOLEAN NOT NULL DEFAULT FALSE,
    insurance_verified       BOOLEAN NOT NULL DEFAULT FALSE,
    auth_verified            BOOLEAN NOT NULL DEFAULT FALSE,
    consent_obtained         BOOLEAN NOT NULL DEFAULT FALSE,
    status                   VARCHAR(30) NOT NULL DEFAULT 'pending',
    channel                  VARCHAR(30),
    incomplete_items         TEXT,
    created_by               BIGINT,
    created_at               TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at               TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_prereg_status
        CHECK (status IN ('pending','in_progress','completed','cancelled')),
    CONSTRAINT chk_prereg_channel
        CHECK (channel IS NULL OR channel IN ('portal','phone','onsite','kiosk'))
);

ALTER TABLE pre_registration_records
    ADD CONSTRAINT fk_prereg_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_prereg_appointment
        FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    ADD CONSTRAINT fk_prereg_facility
        FOREIGN KEY (facility_id) REFERENCES facilities(facility_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_prereg_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL;

CREATE INDEX idx_prereg_patient ON pre_registration_records(patient_id);
CREATE INDEX idx_prereg_appointment ON pre_registration_records(appointment_id);
CREATE INDEX idx_prereg_status ON pre_registration_records(status);
CREATE INDEX idx_prereg_facility ON pre_registration_records(facility_id);