Patient Portal & Mobile App Data Specifications

CREATE TABLE portal_accounts (
    account_id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id           BIGINT NOT NULL,
    user_id              BIGINT NOT NULL,
    email                VARCHAR(254) NOT NULL,
    phone                VARCHAR(20),
    activation_status    VARCHAR(20) NOT NULL DEFAULT 'pending',
    activation_date      TIMESTAMP,
    last_login           TIMESTAMP,
    mfa_method           VARCHAR(30),
    uae_pass_linked      BOOLEAN NOT NULL DEFAULT FALSE,
    uae_pass_sub         VARCHAR(100),
    language_preference  VARCHAR(10) NOT NULL DEFAULT 'en',
    time_zone            VARCHAR(50) NOT NULL DEFAULT 'Asia/Dubai',
    is_active            BOOLEAN NOT NULL DEFAULT TRUE,
    created_at           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by           BIGINT,
    updated_at           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by           BIGINT,
    CONSTRAINT uq_portal_accounts_email UNIQUE (email),
    CONSTRAINT fk_portal_accounts_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_portal_accounts_user
        FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_portal_accounts_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_portal_accounts_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_portal_accounts_activation_status
        CHECK (activation_status IN ('pending','active','locked','disabled')),
    CONSTRAINT chk_portal_accounts_language
        CHECK (language_preference IN ('en','ar')),
    CONSTRAINT chk_portal_accounts_mfa_method
        CHECK (mfa_method IS NULL OR mfa_method IN ('sms_otp','email_otp','auth_app','biometric'))
);

CREATE INDEX idx_portal_accounts_patient
    ON portal_accounts(patient_id);

CREATE INDEX idx_portal_accounts_user
    ON portal_accounts(user_id);

CREATE INDEX idx_portal_accounts_status
    ON portal_accounts(activation_status)
    WHERE is_active = TRUE;

COMMENT ON TABLE portal_accounts IS 'Patient portal user accounts for web/mobile access (UAE PDPL-compliant).';
COMMENT ON COLUMN portal_accounts.uae_pass_sub IS 'Opaque subject identifier from UAE Pass (OAuth2/OIDC).';

CREATE TABLE 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,
    device_type      VARCHAR(30),
    device_os        VARCHAR(100),
    ip_address       INET,
    auth_method      VARCHAR(30),
    mfa_used         BOOLEAN NOT NULL DEFAULT FALSE,
    created_at       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_portal_sessions_account
        FOREIGN KEY (account_id) REFERENCES portal_accounts(account_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT chk_portal_sessions_device_type
        CHECK (device_type IS NULL OR device_type IN ('web','ios','android','other')),
    CONSTRAINT chk_portal_sessions_auth_method
        CHECK (auth_method IS NULL OR auth_method IN ('password','uae_pass','biometric')),
    CONSTRAINT chk_portal_sessions_logout_after_login
        CHECK (logout_datetime IS NULL OR logout_datetime >= login_datetime)
);

CREATE INDEX idx_portal_sessions_account
    ON portal_sessions(account_id);

CREATE INDEX idx_portal_sessions_login_datetime
    ON portal_sessions(login_datetime);

CREATE INDEX idx_portal_sessions_recent_active
    ON portal_sessions(account_id, login_datetime)
    WHERE logout_datetime IS NULL;

[
  {
    "document_id": 12345,
    "file_name": "bp_readings.pdf",
    "content_type": "application/pdf",
    "size_bytes": 45231
  }
]

CREATE TABLE portal_messages (
    message_id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    thread_id             BIGINT NOT NULL,
    sender_account_id     BIGINT,
    sender_type           VARCHAR(20) NOT NULL,
    recipient_provider_id BIGINT,
    recipient_account_id  BIGINT,
    recipient_type        VARCHAR(20) NOT NULL,
    subject               VARCHAR(255) NOT NULL,
    body                  TEXT NOT NULL,
    attachments           JSONB,
    sent_datetime         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    read_datetime         TIMESTAMP,
    is_urgent             BOOLEAN NOT NULL DEFAULT FALSE,
    encounter_id          BIGINT,
    created_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_portal_messages_sender_account
        FOREIGN KEY (sender_account_id) REFERENCES portal_accounts(account_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_portal_messages_recipient_account
        FOREIGN KEY (recipient_account_id) REFERENCES portal_accounts(account_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_portal_messages_recipient_provider
        FOREIGN KEY (recipient_provider_id) REFERENCES providers(provider_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_portal_messages_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_portal_messages_sender_type
        CHECK (sender_type IN ('patient','provider','system')),
    CONSTRAINT chk_portal_messages_recipient_type
        CHECK (recipient_type IN ('patient','provider','team')),
    CONSTRAINT chk_portal_messages_read_after_sent
        CHECK (read_datetime IS NULL OR read_datetime >= sent_datetime),
    CONSTRAINT chk_portal_messages_sender_recipient
        CHECK (
            -- at least one recipient
            (recipient_provider_id IS NOT NULL OR recipient_account_id IS NOT NULL)
        )
);

CREATE INDEX idx_portal_messages_thread
    ON portal_messages(thread_id, sent_datetime);

CREATE INDEX idx_portal_messages_recipient_provider_unread
    ON portal_messages(recipient_provider_id, sent_datetime)
    WHERE read_datetime IS NULL;

CREATE INDEX idx_portal_messages_recipient_account_unread
    ON portal_messages(recipient_account_id, sent_datetime)
    WHERE read_datetime IS NULL;

CREATE INDEX idx_portal_messages_encounter
    ON portal_messages(encounter_id);

CREATE TABLE portal_notifications (
    notification_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id         BIGINT NOT NULL,
    notification_type  VARCHAR(50) NOT NULL,
    channel            VARCHAR(20) NOT NULL,
    subject            VARCHAR(255) NOT NULL,
    body               TEXT NOT NULL,
    payload            JSONB,
    sent_datetime      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    read_datetime      TIMESTAMP,
    action_url         VARCHAR(500),
    status             VARCHAR(20) NOT NULL DEFAULT 'pending',
    error_message      VARCHAR(500),
    CONSTRAINT fk_portal_notifications_account
        FOREIGN KEY (account_id) REFERENCES portal_accounts(account_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT chk_portal_notifications_channel
        CHECK (channel IN ('in_app','email','sms','push')),
    CONSTRAINT chk_portal_notifications_status
        CHECK (status IN ('pending','sent','failed','read')),
    CONSTRAINT chk_portal_notifications_read_after_sent
        CHECK (read_datetime IS NULL OR read_datetime >= sent_datetime)
);

CREATE INDEX idx_portal_notifications_account
    ON portal_notifications(account_id, sent_datetime);

CREATE INDEX idx_portal_notifications_status
    ON portal_notifications(status);

CREATE INDEX idx_portal_notifications_unread_inapp
    ON portal_notifications(account_id, sent_datetime)
    WHERE channel = 'in_app' AND read_datetime IS NULL;

CREATE TABLE portal_preferences (
    preference_id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id                BIGINT NOT NULL UNIQUE,
    notification_email        BOOLEAN NOT NULL DEFAULT TRUE,
    notification_sms          BOOLEAN NOT NULL DEFAULT FALSE,
    notification_push         BOOLEAN NOT NULL DEFAULT TRUE,
    language                  VARCHAR(10) NOT NULL DEFAULT 'en',
    display_theme             VARCHAR(20) NOT NULL DEFAULT 'light',
    result_release_preference VARCHAR(20),
    marketing_opt_in          BOOLEAN NOT NULL DEFAULT FALSE,
    created_at                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_portal_preferences_account
        FOREIGN KEY (account_id) REFERENCES portal_accounts(account_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT chk_portal_preferences_language
        CHECK (language IN ('en','ar')),
    CONSTRAINT chk_portal_preferences_theme
        CHECK (display_theme IN ('light','dark','system')),
    CONSTRAINT chk_portal_preferences_result_release
        CHECK (result_release_preference IS NULL OR result_release_preference IN ('immediate','delayed'))
);

CREATE INDEX idx_portal_preferences_language
    ON portal_preferences(language);

CREATE TABLE telehealth_sessions (
    session_id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    appointment_id         BIGINT NOT NULL,
    patient_account_id     BIGINT NOT NULL,
    provider_id            BIGINT NOT NULL,
    scheduled_datetime     TIMESTAMP NOT NULL,
    join_datetime_patient  TIMESTAMP,
    join_datetime_provider TIMESTAMP,
    end_datetime           TIMESTAMP,
    duration_minutes       INT,
    connection_quality     VARCHAR(20),
    platform               VARCHAR(30) NOT NULL,
    recording_consent      BOOLEAN NOT NULL DEFAULT FALSE,
    recording_stored       BOOLEAN NOT NULL DEFAULT FALSE,
    encounter_id           BIGINT,
    created_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_telehealth_sessions_appointment
        FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_telehealth_sessions_patient_account
        FOREIGN KEY (patient_account_id) REFERENCES portal_accounts(account_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_telehealth_sessions_provider
        FOREIGN KEY (provider_id) REFERENCES providers(provider_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_telehealth_sessions_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_telehealth_sessions_platform
        CHECK (platform IN ('webrtc','native_ios','native_android','other')),
    CONSTRAINT chk_telehealth_sessions_quality
        CHECK (connection_quality IS NULL OR connection_quality IN ('excellent','good','fair','poor')),
    CONSTRAINT chk_telehealth_sessions_times
        CHECK (
            (join_datetime_patient IS NULL OR join_datetime_patient >= scheduled_datetime) AND
            (join_datetime_provider IS NULL OR join_datetime_provider >= scheduled_datetime) AND
            (end_datetime IS NULL OR
                (join_datetime_patient IS NULL OR end_datetime >= join_datetime_patient) AND
                (join_datetime_provider IS NULL OR end_datetime >= join_datetime_provider)
            )
        ),
    CONSTRAINT chk_telehealth_sessions_duration
        CHECK (duration_minutes IS NULL OR duration_minutes >= 0)
);

CREATE INDEX idx_telehealth_sessions_appointment
    ON telehealth_sessions(appointment_id);

CREATE INDEX idx_telehealth_sessions_provider
    ON telehealth_sessions(provider_id, scheduled_datetime);

CREATE INDEX idx_telehealth_sessions_patient
    ON telehealth_sessions(patient_account_id, scheduled_datetime);

CREATE TABLE patient_submitted_forms (
    form_id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id          BIGINT NOT NULL,
    patient_id          BIGINT NOT NULL,
    form_type           VARCHAR(100) NOT NULL,
    form_version        VARCHAR(20),
    form_data_json      JSONB NOT NULL,
    submitted_datetime  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    processed           BOOLEAN NOT NULL DEFAULT FALSE,
    processed_datetime  TIMESTAMP,
    processed_by        BIGINT,
    target_module       VARCHAR(50) NOT NULL,
    source_ip           INET,
    user_agent          VARCHAR(255),
    CONSTRAINT fk_patient_forms_account
        FOREIGN KEY (account_id) REFERENCES portal_accounts(account_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_patient_forms_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_patient_forms_processed_by
        FOREIGN KEY (processed_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_patient_forms_processed_time
        CHECK (processed_datetime IS NULL OR processed_datetime >= submitted_datetime)
);

CREATE INDEX idx_patient_forms_patient
    ON patient_submitted_forms(patient_id, submitted_datetime);

CREATE INDEX idx_patient_forms_type
    ON patient_submitted_forms(form_type, submitted_datetime);

CREATE INDEX idx_patient_forms_processed
    ON patient_submitted_forms(processed, target_module);

CREATE TABLE portal_feedback (
    feedback_id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id          BIGINT NOT NULL,
    feedback_type       VARCHAR(50) NOT NULL,
    encounter_id        BIGINT,
    rating              INT NOT NULL,
    comments            TEXT,
    submitted_datetime  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    follow_up_required  BOOLEAN NOT NULL DEFAULT FALSE,
    created_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_portal_feedback_account
        FOREIGN KEY (account_id) REFERENCES portal_accounts(account_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_portal_feedback_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_portal_feedback_type
        CHECK (feedback_type IN ('portal','telehealth','visit','billing')),
    CONSTRAINT chk_portal_feedback_rating
        CHECK (rating BETWEEN 1 AND 5)
);

CREATE INDEX idx_portal_feedback_account
    ON portal_feedback(account_id, submitted_datetime);

CREATE INDEX idx_portal_feedback_type
    ON portal_feedback(feedback_type, submitted_datetime);

CREATE TABLE proxy_access_grants (
    grant_id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    grantor_patient_id   BIGINT NOT NULL,
    proxy_account_id     BIGINT NOT NULL,
    dependent_patient_id BIGINT NOT NULL,
    relationship         VARCHAR(50) NOT NULL,
    access_level         VARCHAR(20) NOT NULL DEFAULT 'full',
    granted_datetime     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expiry_datetime      TIMESTAMP,
    is_active            BOOLEAN NOT NULL DEFAULT TRUE,
    created_by           BIGINT,
    created_at           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    revoked_datetime     TIMESTAMP,
    revoked_reason       VARCHAR(255),
    CONSTRAINT fk_proxy_grants_grantor_patient
        FOREIGN KEY (grantor_patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_proxy_grants_proxy_account
        FOREIGN KEY (proxy_account_id) REFERENCES portal_accounts(account_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_proxy_grants_dependent_patient
        FOREIGN KEY (dependent_patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_proxy_grants_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_proxy_grants_access_level
        CHECK (access_level IN ('full','limited','appointments_only')),
    CONSTRAINT chk_proxy_grants_expiry
        CHECK (expiry_datetime IS NULL OR expiry_datetime >= granted_datetime),
    CONSTRAINT chk_proxy_grants_revoked_time
        CHECK (revoked_datetime IS NULL OR revoked_datetime >= granted_datetime),
    CONSTRAINT uq_proxy_grants_unique
        UNIQUE (proxy_account_id, dependent_patient_id, access_level)
);

CREATE INDEX idx_proxy_grants_proxy
    ON proxy_access_grants(proxy_account_id, is_active);

CREATE INDEX idx_proxy_grants_dependent
    ON proxy_access_grants(dependent_patient_id, is_active);