Physician Portal & Mobile App Data Specifications

CREATE TABLE physician_portal_accounts (
    account_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    provider_id             BIGINT NOT NULL,
    user_id                 BIGINT NOT NULL,
    activation_status       VARCHAR(20) NOT NULL DEFAULT 'pending',
    activation_datetime     TIMESTAMP NULL,
    deactivation_datetime   TIMESTAMP NULL,
    last_login              TIMESTAMP NULL,
    mfa_method              VARCHAR(30) NOT NULL DEFAULT 'biometric',
    device_tokens           JSONB NULL,
    offline_access_enabled  BOOLEAN NOT NULL DEFAULT FALSE,
    is_active               BOOLEAN NOT NULL DEFAULT TRUE,
    preferred_language      VARCHAR(10) NULL,
    emirate_code            VARCHAR(3) NULL,
    created_at              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_phys_portal_accounts_provider UNIQUE (provider_id),
    CONSTRAINT uq_phys_portal_accounts_user UNIQUE (user_id),

    CONSTRAINT chk_phys_portal_activation_status
        CHECK (activation_status IN ('pending','active','suspended','revoked')),

    CONSTRAINT chk_phys_portal_mfa_method
        CHECK (mfa_method IN ('biometric','otp_sms','otp_email','auth_app','none')),

    CONSTRAINT chk_phys_portal_emirate_code
        CHECK (emirate_code IS NULL OR emirate_code IN ('DXB','AUH','SHJ','AJM','RAK','UAQ','Fuj')),

    CONSTRAINT fk_phys_portal_provider
        FOREIGN KEY (provider_id) REFERENCES providers(provider_id)
            ON UPDATE CASCADE ON DELETE RESTRICT,

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

CREATE INDEX idx_phys_portal_accounts_status
    ON physician_portal_accounts (activation_status, is_active);

CREATE INDEX idx_phys_portal_accounts_last_login
    ON physician_portal_accounts (last_login DESC);

CREATE INDEX idx_phys_portal_accounts_emirate
    ON physician_portal_accounts (emirate_code);

CREATE TABLE physician_portal_sessions (
    session_id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id             BIGINT NOT NULL,
    login_datetime         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    logout_datetime        TIMESTAMP NULL,
    device_type            VARCHAR(20) NOT NULL,
    device_os              VARCHAR(50) NULL,
    app_version            VARCHAR(20) NULL,
    ip_address             INET NULL,
    auth_method            VARCHAR(30) NOT NULL,
    is_mobile              BOOLEAN NOT NULL DEFAULT FALSE,
    session_status         VARCHAR(20) NOT NULL DEFAULT 'active',
    push_token_hash        VARCHAR(128) NULL,
    last_activity_datetime TIMESTAMP NULL,
    session_metadata       JSONB NULL,
    created_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_phys_sessions_account
        FOREIGN KEY (account_id) REFERENCES physician_portal_accounts(account_id)
            ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT chk_phys_sessions_device_type
        CHECK (device_type IN ('ios','android','web','tablet')),

    CONSTRAINT chk_phys_sessions_auth_method
        CHECK (auth_method IN ('password','biometric','sso','otp')),

    CONSTRAINT chk_phys_sessions_status
        CHECK (session_status IN ('active','terminated','expired')),

    CONSTRAINT chk_phys_sessions_logout_after_login
        CHECK (logout_datetime IS NULL OR logout_datetime >= login_datetime)
);

CREATE INDEX idx_phys_sessions_account
    ON physician_portal_sessions (account_id, login_datetime DESC);

CREATE INDEX idx_phys_sessions_mobile
    ON physician_portal_sessions (is_mobile, login_datetime DESC);

CREATE INDEX idx_phys_sessions_status
    ON physician_portal_sessions (session_status);

CREATE INDEX idx_phys_sessions_last_activity
    ON physician_portal_sessions (last_activity_datetime);

CREATE TABLE physician_inbox_items (
    item_id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    provider_id         BIGINT NOT NULL,
    item_type           VARCHAR(30) NOT NULL,
    source_module       VARCHAR(30) NOT NULL,
    source_id           BIGINT NULL,
    patient_id          BIGINT NULL,
    subject             VARCHAR(255) NOT NULL,
    preview_text        TEXT NULL,
    priority            VARCHAR(20) NOT NULL DEFAULT 'normal',
    status              VARCHAR(20) NOT NULL DEFAULT 'unread',
    received_datetime   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    read_datetime       TIMESTAMP NULL,
    actioned_datetime   TIMESTAMP NULL,
    action_taken        VARCHAR(100) NULL,
    is_critical         BOOLEAN NOT NULL DEFAULT FALSE,
    requires_ack        BOOLEAN NOT NULL DEFAULT FALSE,
    ack_datetime        TIMESTAMP NULL,
    ack_user_id         BIGINT NULL,
    routing_metadata    JSONB NULL,
    created_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_inbox_provider
        FOREIGN KEY (provider_id) REFERENCES providers(provider_id)
            ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_inbox_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
            ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_inbox_ack_user
        FOREIGN KEY (ack_user_id) REFERENCES users(user_id)
            ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT chk_inbox_item_type
        CHECK (item_type IN (
            'patient_message','provider_message','lab_result',
            'radiology_result','critical_result','system_alert','task'
        )),

    CONSTRAINT chk_inbox_priority
        CHECK (priority IN ('low','normal','high','critical')),

    CONSTRAINT chk_inbox_status
        CHECK (status IN ('unread','read','actioned','archived')),

    CONSTRAINT chk_inbox_read_after_received
        CHECK (read_datetime IS NULL OR read_datetime >= received_datetime),

    CONSTRAINT chk_inbox_action_after_received
        CHECK (actioned_datetime IS NULL OR actioned_datetime >= received_datetime),

    CONSTRAINT chk_inbox_ack_after_received
        CHECK (ack_datetime IS NULL OR ack_datetime >= received_datetime),

    CONSTRAINT chk_inbox_ack_required
        CHECK (requires_ack = FALSE OR is_critical = TRUE)
);

CREATE INDEX idx_inbox_provider_status
    ON physician_inbox_items (provider_id, status, priority DESC, received_datetime DESC);

CREATE INDEX idx_inbox_provider_type
    ON physician_inbox_items (provider_id, item_type, received_datetime DESC);

CREATE INDEX idx_inbox_patient
    ON physician_inbox_items (patient_id, received_datetime DESC);

CREATE INDEX idx_inbox_requires_ack
    ON physician_inbox_items (requires_ack, is_critical, received_datetime);

CREATE TABLE physician_preferences (
    preference_id                 BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id                    BIGINT NOT NULL,
    notification_critical_results BOOLEAN NOT NULL DEFAULT TRUE,
    notification_new_messages     BOOLEAN NOT NULL DEFAULT TRUE,
    notification_schedule_changes BOOLEAN NOT NULL DEFAULT TRUE,
    notification_abnormal_results BOOLEAN NOT NULL DEFAULT TRUE,
    notification_handoff          BOOLEAN NOT NULL DEFAULT TRUE,
    notification_task_updates     BOOLEAN NOT NULL DEFAULT TRUE,
    display_theme                 VARCHAR(20) NOT NULL DEFAULT 'light',
    default_patient_list          VARCHAR(50) NULL,
    result_sort_preference        VARCHAR(20) NOT NULL DEFAULT 'abnormal_first',
    language                      VARCHAR(10) NULL,
    time_zone                     VARCHAR(40) NULL DEFAULT 'Asia/Dubai',
    notification_quiet_hours      JSONB NULL,
    mobile_layout_settings        JSONB NULL,
    favorite_views                JSONB NULL,
    created_at                    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at                    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_phys_prefs_account
        FOREIGN KEY (account_id) REFERENCES physician_portal_accounts(account_id)
            ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT uq_phys_prefs_account UNIQUE (account_id),

    CONSTRAINT chk_phys_prefs_theme
        CHECK (display_theme IN ('light','dark','system')),

    CONSTRAINT chk_phys_prefs_result_sort
        CHECK (result_sort_preference IN ('abnormal_first','chronological','by_type'))
);

CREATE INDEX idx_phys_prefs_account
    ON physician_preferences (account_id);

CREATE TABLE physician_favorites (
    favorite_id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    provider_id       BIGINT NOT NULL,
    favorite_type     VARCHAR(30) NOT NULL,
    item_id           BIGINT NULL,
    item_name         VARCHAR(255) NOT NULL,
    item_code         VARCHAR(50) NULL,
    item_context      VARCHAR(50) NULL,
    display_order     INT NOT NULL DEFAULT 0,
    is_active         BOOLEAN NOT NULL DEFAULT TRUE,
    created_datetime  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_datetime  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_phys_fav_provider
        FOREIGN KEY (provider_id) REFERENCES providers(provider_id)
            ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT chk_phys_fav_type
        CHECK (favorite_type IN ('patient','order','order_set','note_template','diagnosis')),

    CONSTRAINT chk_phys_fav_display_order
        CHECK (display_order >= 0)
);

CREATE INDEX idx_phys_fav_provider_type
    ON physician_favorites (provider_id, favorite_type, is_active, display_order);

CREATE INDEX idx_phys_fav_item
    ON physician_favorites (favorite_type, item_context, item_id);

CREATE TABLE clinical_task_lists (
    list_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    provider_id          BIGINT NOT NULL,
    list_name            VARCHAR(100) NOT NULL,
    list_type            VARCHAR(30) NOT NULL DEFAULT 'personal',
    is_default           BOOLEAN NOT NULL DEFAULT FALSE,
    is_shared            BOOLEAN NOT NULL DEFAULT FALSE,
    owning_department_id BIGINT NULL,
    created_datetime     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_datetime     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_task_lists_provider
        FOREIGN KEY (provider_id) REFERENCES providers(provider_id)
            ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_task_lists_department
        FOREIGN KEY (owning_department_id) REFERENCES departments(department_id)
            ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT chk_task_lists_type
        CHECK (list_type IN ('personal','shared_department','on_call')),

    CONSTRAINT chk_task_lists_default
        CHECK (is_default = FALSE OR list_type = 'personal')
);

CREATE INDEX idx_task_lists_provider
    ON clinical_task_lists (provider_id, list_type);

CREATE INDEX idx_task_lists_department
    ON clinical_task_lists (owning_department_id);

CREATE TABLE clinical_task_items (
    task_item_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    list_id             BIGINT NOT NULL,
    patient_id          BIGINT NULL,
    encounter_id        BIGINT NULL,
    task_description    VARCHAR(500) NOT NULL,
    task_type           VARCHAR(50) NOT NULL,
    source_module       VARCHAR(30) NULL,
    source_id           BIGINT NULL,
    priority            VARCHAR(20) NOT NULL DEFAULT 'normal',
    due_datetime        TIMESTAMP NULL,
    status              VARCHAR(20) NOT NULL DEFAULT 'open',
    created_datetime    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_datetime  TIMESTAMP NULL,
    delegated_to        BIGINT NULL,
    notes               TEXT NULL,
    escalation_flag     BOOLEAN NOT NULL DEFAULT FALSE,
    escalated_datetime  TIMESTAMP NULL,
    task_metadata       JSONB NULL,

    CONSTRAINT fk_tasks_list
        FOREIGN KEY (list_id) REFERENCES clinical_task_lists(list_id)
            ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_tasks_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
            ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_tasks_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
            ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_tasks_delegated_to
        FOREIGN KEY (delegated_to) REFERENCES providers(provider_id)
            ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT chk_tasks_type
        CHECK (task_type IN ('result_review','order_sign','follow_up','message_reply','manual')),

    CONSTRAINT chk_tasks_priority
        CHECK (priority IN ('low','normal','high','critical')),

    CONSTRAINT chk_tasks_status
        CHECK (status IN ('open','in_progress','completed','cancelled','overdue')),

    CONSTRAINT chk_tasks_completed_after_created
        CHECK (completed_datetime IS NULL OR completed_datetime >= created_datetime)
);

CREATE INDEX idx_tasks_list_status
    ON clinical_task_items (list_id, status, priority DESC, due_datetime);

CREATE INDEX idx_tasks_patient
    ON clinical_task_items (patient_id, status, due_datetime);

CREATE INDEX idx_tasks_delegated
    ON clinical_task_items (delegated_to, status, priority DESC);

CREATE INDEX idx_tasks_source
    ON clinical_task_items (source_module, source_id);

CREATE TABLE handoff_communications (
    handoff_id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id             BIGINT NOT NULL,
    encounter_id           BIGINT NULL,
    outgoing_provider_id   BIGINT NOT NULL,
    incoming_provider_id   BIGINT NOT NULL,
    illness_severity       VARCHAR(20) NOT NULL,
    patient_summary        TEXT NOT NULL,
    action_list            TEXT NOT NULL,
    situation_awareness    TEXT NULL,
    contingency_plan       TEXT NULL,
    handoff_datetime       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    acknowledged_datetime  TIMESTAMP NULL,
    status                 VARCHAR(20) NOT NULL DEFAULT 'pending',
    includes_critical_items BOOLEAN NOT NULL DEFAULT FALSE,
    handoff_metadata       JSONB NULL,
    created_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_handoff_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
            ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_handoff_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
            ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_handoff_outgoing_provider
        FOREIGN KEY (outgoing_provider_id) REFERENCES providers(provider_id)
            ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT fk_handoff_incoming_provider
        FOREIGN KEY (incoming_provider_id) REFERENCES providers(provider_id)
            ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT chk_handoff_illness_severity
        CHECK (illness_severity IN ('stable','watcher','unstable')),

    CONSTRAINT chk_handoff_status
        CHECK (status IN ('pending','acknowledged','superseded','cancelled')),

    CONSTRAINT chk_handoff_ack_after_handoff
        CHECK (acknowledged_datetime IS NULL OR acknowledged_datetime >= handoff_datetime)
);

CREATE INDEX idx_handoff_patient
    ON handoff_communications (patient_id, handoff_datetime DESC);

CREATE INDEX idx_handoff_incoming_status
    ON handoff_communications (incoming_provider_id, status, handoff_datetime DESC);

CREATE INDEX idx_handoff_outgoing
    ON handoff_communications (outgoing_provider_id, handoff_datetime DESC);