Denial Analysis Data Specifications
Home / Revenue Cycle Management / Denial Analysis / Data Specifications

Denial Analysis Data Specifications

Shared Entity References

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

Shared Entity Owning Module Table(s) FK Used Here
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 users.user_id
Facilities ehr-patient-mgmt facilities facilities.facility_id
Payers policy-contract-mgmt payers payers.payer_id
Claims billing-claims claims claims.claim_id
Claim Lines billing-claims claim_lines claim_lines.line_id

Entity Relationship Diagram

erDiagram patients ||--o{ denial_records : "has denials" payers ||--o{ denial_records : "denies claims" claims ||--o{ denial_records : "has" claim_lines ||--o{ denial_records : "line-level" users ||--o{ denial_records : "assigned analyst" denial_categories ||--o{ denial_records : "categorized as" denial_records ||--o{ denial_root_causes : "explained by" users ||--o{ denial_root_causes : "identified by" denial_records ||--o{ appeals : "appealed by" users ||--o{ appeals : "handled by" appeals ||--o{ appeal_outcomes : "results in" payers ||--o{ denial_trends : "performance" denial_categories ||--o{ denial_trends : "trend by" denial_categories ||--o{ denial_prevention_actions : "targets" payers ||--o{ denial_prevention_actions : "payer-specific" denial_prevention_actions ||--o{ denial_trends : "influences" payers ||--o{ payer_denial_scorecards : "scored" facilities ||--o{ denial_records : "origin facility" encounters ||--o{ denial_records : "encounter context" denial_records { bigint denial_id PK bigint claim_id FK bigint claim_line_id FK bigint patient_id FK bigint payer_id FK bigint facility_id FK bigint encounter_id FK bigint denial_category_id FK bigint root_cause_id FK bigint assigned_analyst_id FK bigint created_by FK bigint updated_by FK } denial_categories { bigint denial_category_id PK bigint created_by FK bigint updated_by FK } denial_root_causes { bigint root_cause_id PK bigint denial_id FK bigint identified_by FK bigint linked_action_id FK } appeals { bigint appeal_id PK bigint denial_id FK bigint assigned_to FK bigint created_by FK bigint updated_by FK } appeal_outcomes { bigint outcome_id PK bigint appeal_id FK bigint created_by FK bigint updated_by FK } denial_trends { bigint trend_id PK bigint payer_id FK bigint denial_category_id FK bigint provider_id FK } denial_prevention_actions { bigint action_id PK bigint denial_category_id FK bigint payer_id FK bigint created_by FK bigint updated_by FK } payer_denial_scorecards { bigint scorecard_id PK bigint payer_id FK }

Table Definitions

1. denial_records

Purpose
Stores individual denial instances linked to claims and claim lines. Used as the primary fact table for denial analysis, appeal tracking, KPIs, and UAE payer reporting (eClaimLink, DOH eClaims).

Field Specifications

Field Type Nullable Default Constraint Description
denial_id BIGINT NO IDENTITY PK Unique denial record ID
claim_id BIGINT NO — FK → claims.claim_id Parent claim
claim_line_id BIGINT YES — FK → claim_lines.line_id Line-level denial (NULL for header-level)
patient_id BIGINT NO — FK → patients.patient_id Patient whose claim was denied
payer_id BIGINT NO — FK → payers.payer_id Denying payer (THIQA, Daman, etc.)
facility_id BIGINT NO — FK → facilities.facility_id Originating facility (Dubai/Abu Dhabi, etc.)
encounter_id BIGINT YES — FK → encounters.encounter_id Encounter associated with claim
denial_date TIMESTAMP NO CURRENT_TIMESTAMP — Date/time denial received/posted
payer_denial_code VARCHAR(50) NO — — Payer-specific denial code (eClaimLink/DOH)
denial_description VARCHAR(255) YES — — Free-text description from payer
denial_category_id BIGINT YES — FK → denial_categories.denial_category_id Standardized denial category
denied_amount NUMERIC(14,2) NO 0.00 ≥ 0 Amount denied in AED
root_cause_id BIGINT YES — FK → denial_root_causes.root_cause_id Primary root cause (if identified)
is_preventable BOOLEAN NO FALSE — Flag if denial deemed preventable
assigned_analyst_id BIGINT YES — FK → users.user_id Denial analyst assigned
status VARCHAR(30) NO 'open' IN ('open','in_review','appealed','resolved','written_off') Current denial lifecycle status
resolution_date TIMESTAMP YES — — When denial was fully resolved
resolution_type VARCHAR(30) YES — IN ('paid','partial_paid','write_off','patient_billed','no_action') Final resolution classification
recovered_amount NUMERIC(14,2) YES 0.00 ≥ 0 Amount recovered after appeal/correction
appeal_status VARCHAR(30) NO 'not_applicable' IN ('not_applicable','pending','in_progress','closed') Appeal lifecycle status
days_to_appeal INTEGER YES — ≥ 0 Days between denial and first appeal submission
days_to_resolution INTEGER YES — ≥ 0 Days from denial to final resolution
created_at TIMESTAMP NO CURRENT_TIMESTAMP — Record creation timestamp
updated_at TIMESTAMP NO CURRENT_TIMESTAMP — Last update timestamp
created_by BIGINT YES — FK → users.user_id User who created record (system/user)
updated_by BIGINT YES — FK → users.user_id User who last updated record

SQL DDL

SQL
CREATE TABLE denial_records (
    denial_id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    claim_id            BIGINT NOT NULL,
    claim_line_id       BIGINT NULL,
    patient_id          BIGINT NOT NULL,
    payer_id            BIGINT NOT NULL,
    facility_id         BIGINT NOT NULL,
    encounter_id        BIGINT NULL,
    denial_date         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    payer_denial_code   VARCHAR(50) NOT NULL,
    denial_description  VARCHAR(255),
    denial_category_id  BIGINT NULL,
    denied_amount       NUMERIC(14,2) NOT NULL DEFAULT 0.00,
    root_cause_id       BIGINT NULL,
    is_preventable      BOOLEAN NOT NULL DEFAULT FALSE,
    assigned_analyst_id BIGINT NULL,
    status              VARCHAR(30) NOT NULL DEFAULT 'open',
    resolution_date     TIMESTAMP NULL,
    resolution_type     VARCHAR(30) NULL,
    recovered_amount    NUMERIC(14,2) NULL DEFAULT 0.00,
    appeal_status       VARCHAR(30) NOT NULL DEFAULT 'not_applicable',
    days_to_appeal      INTEGER NULL,
    days_to_resolution  INTEGER NULL,
    created_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by          BIGINT NULL,
    updated_by          BIGINT NULL,
    CONSTRAINT fk_denial_claim
        FOREIGN KEY (claim_id) REFERENCES claims (claim_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_denial_claim_line
        FOREIGN KEY (claim_line_id) REFERENCES claim_lines (line_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_denial_patient
        FOREIGN KEY (patient_id) REFERENCES patients (patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_denial_payer
        FOREIGN KEY (payer_id) REFERENCES payers (payer_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_denial_facility
        FOREIGN KEY (facility_id) REFERENCES facilities (facility_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_denial_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters (encounter_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_denial_category
        FOREIGN KEY (denial_category_id) REFERENCES denial_categories (denial_category_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_denial_root_cause
        FOREIGN KEY (root_cause_id) REFERENCES denial_root_causes (root_cause_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_denial_assigned_analyst
        FOREIGN KEY (assigned_analyst_id) REFERENCES users (user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_denial_created_by
        FOREIGN KEY (created_by) REFERENCES users (user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_denial_updated_by
        FOREIGN KEY (updated_by) REFERENCES users (user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_denial_status
        CHECK (status IN ('open','in_review','appealed','resolved','written_off')),
    CONSTRAINT chk_denial_resolution_type
        CHECK (resolution_type IS NULL OR resolution_type IN ('paid','partial_paid','write_off','patient_billed','no_action')),
    CONSTRAINT chk_denial_appeal_status
        CHECK (appeal_status IN ('not_applicable','pending','in_progress','closed')),
    CONSTRAINT chk_denied_amount_nonnegative
        CHECK (denied_amount >= 0),
    CONSTRAINT chk_recovered_amount_nonnegative
        CHECK (recovered_amount >= 0),
    CONSTRAINT chk_days_to_appeal_nonnegative
        CHECK (days_to_appeal IS NULL OR days_to_appeal >= 0),
    CONSTRAINT chk_days_to_resolution_nonnegative
        CHECK (days_to_resolution IS NULL OR days_to_resolution >= 0)
);

CREATE INDEX idx_denial_records_claim
    ON denial_records (claim_id);

CREATE INDEX idx_denial_records_patient
    ON denial_records (patient_id);

CREATE INDEX idx_denial_records_payer_date
    ON denial_records (payer_id, denial_date);

CREATE INDEX idx_denial_records_category_date
    ON denial_records (denial_category_id, denial_date);

CREATE INDEX idx_denial_records_status
    ON denial_records (status);

CREATE INDEX idx_denial_records_analyst_status
    ON denial_records (assigned_analyst_id, status);

CREATE INDEX idx_denial_records_facility_date
    ON denial_records (facility_id, denial_date);

CREATE INDEX idx_denial_records_preventable
    ON denial_records (is_preventable);

COMMENT ON TABLE denial_records IS 'Individual claim denial records for UAE payers, used for denial analysis, appeals, and KPIs.';
COMMENT ON COLUMN denial_records.payer_denial_code IS 'Payer-specific denial code as received from eClaimLink, DOH eClaims, or other UAE payer formats.';
COMMENT ON COLUMN denial_records.denied_amount IS 'Amount denied in AED.';
COMMENT ON COLUMN denial_records.is_preventable IS 'Flag indicating whether the denial is considered preventable based on root cause analysis.';

Terminology Bindings

Field Terminology Example Value
payer_denial_code DHA eClaimLink / DOH eClaims denial code sets PR-204 (Dubai payer-specific), MNEC (medical necessity)
denial_category_id Local Denial Category Taxonomy (master data) Category “ELIG” for eligibility
resolution_type Local value set (RCM policy) write_off
status Local denial status value set appealed

FHIR Resource Mapping

Denials are not first-class FHIR resources; they are typically represented using ClaimResponse and ExplanationOfBenefit.

Table Field FHIR Resource FHIR Path
claim_id ClaimResponse ClaimResponse.request.reference
payer_id ClaimResponse ClaimResponse.insurer.reference
denial_date ClaimResponse ClaimResponse.created
payer_denial_code ClaimResponse ClaimResponse.error.code (CodeableConcept.coding.code)
denial_description ClaimResponse ClaimResponse.error.code.text or ClaimResponse.error.detail
denied_amount ClaimResponse ClaimResponse.payment.amount (negative or separate adjudication item)
denial_category_id ExplanationOfBenefit ExplanationOfBenefit.item.adjudication.category
recovered_amount ExplanationOfBenefit ExplanationOfBenefit.payment.amount
status ClaimResponse ClaimResponse.outcome (mapped to queued, complete, etc.)

2. denial_categories

Purpose
Defines the standard denial category taxonomy used to normalize payer-specific denial codes into consistent analytical buckets (eligibility, authorization, coding, medical necessity, timely filing, duplicate, bundling, etc.).

Field Specifications

Field Type Nullable Default Constraint Description
denial_category_id BIGINT NO IDENTITY PK Unique category ID
category_code VARCHAR(50) NO — UNIQUE Short code (e.g., ELIG, AUTH)
category_name VARCHAR(100) NO — — Human-readable name
category_group VARCHAR(50) YES — — Higher-level grouping (e.g., front_end, mid_cycle, back_end)
description TEXT YES — — Detailed description
responsible_department VARCHAR(100) YES — — Primary department accountable (e.g., Patient Access)
is_active BOOLEAN NO TRUE — Active flag
display_order INTEGER YES — ≥ 0 UI ordering
created_at TIMESTAMP NO CURRENT_TIMESTAMP — Created timestamp
updated_at TIMESTAMP NO CURRENT_TIMESTAMP — Updated timestamp
created_by BIGINT YES — FK → users.user_id Created by
updated_by BIGINT YES — FK → users.user_id Updated by

SQL DDL

SQL
CREATE TABLE denial_categories (
    denial_category_id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    category_code          VARCHAR(50) NOT NULL,
    category_name          VARCHAR(100) NOT NULL,
    category_group         VARCHAR(50) NULL,
    description            TEXT NULL,
    responsible_department VARCHAR(100) NULL,
    is_active              BOOLEAN NOT NULL DEFAULT TRUE,
    display_order          INTEGER NULL,
    created_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by             BIGINT NULL,
    updated_by             BIGINT NULL,
    CONSTRAINT uq_denial_category_code UNIQUE (category_code),
    CONSTRAINT chk_denial_category_group
        CHECK (category_group IS NULL OR category_group IN ('front_end','mid_cycle','back_end')),
    CONSTRAINT chk_denial_category_display_order
        CHECK (display_order IS NULL OR display_order >= 0),
    CONSTRAINT fk_denial_category_created_by
        FOREIGN KEY (created_by) REFERENCES users (user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_denial_category_updated_by
        FOREIGN KEY (updated_by) REFERENCES users (user_id)
        ON UPDATE CASCADE ON DELETE SET NULL
);

CREATE INDEX idx_denial_categories_active
    ON denial_categories (is_active, display_order);

COMMENT ON TABLE denial_categories IS 'Standard denial category taxonomy used to normalize payer denial codes for UAE RCM analytics.';

Terminology Bindings

Field Terminology Example Value
category_code Local Denial Category Taxonomy ELIG, AUTH, CODING, MEDNEC, TIMELY
category_group Local value set front_end

FHIR Resource Mapping

Categories are internal reference data; they may be exposed via FHIR CodeSystem/ValueSet if needed.

Table Field FHIR Resource FHIR Path
category_code CodeSystem CodeSystem.concept.code
category_name CodeSystem CodeSystem.concept.display
description CodeSystem CodeSystem.concept.definition

3. denial_root_causes

Purpose
Captures root cause analysis for each denial, including process failure point, responsible department, and linkage to prevention actions. Supports identification of preventable denials and systemic issues.

Field Specifications

Field Type Nullable Default Constraint Description
root_cause_id BIGINT NO IDENTITY PK Root cause record ID
denial_id BIGINT NO — FK → denial_records.denial_id Associated denial
root_cause_category VARCHAR(50) NO — — Category (e.g., registration_error)
root_cause_code VARCHAR(100) YES — — Local code from root cause master
root_cause_detail TEXT YES — — Narrative description
process_failure_point VARCHAR(100) YES — — Process step where failure occurred
responsible_department VARCHAR(100) YES — — Department accountable
identified_by BIGINT NO — FK → users.user_id Analyst who identified root cause
identified_date TIMESTAMP NO CURRENT_TIMESTAMP — Date/time identified
systemic_flag BOOLEAN NO FALSE — Indicates systemic issue vs one-off
linked_action_id BIGINT YES — FK → denial_prevention_actions.action_id Related prevention action (if any)
created_at TIMESTAMP NO CURRENT_TIMESTAMP — Created timestamp
updated_at TIMESTAMP NO CURRENT_TIMESTAMP — Updated timestamp

SQL DDL

SQL
CREATE TABLE denial_root_causes (
    root_cause_id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    denial_id             BIGINT NOT NULL,
    root_cause_category   VARCHAR(50) NOT NULL,
    root_cause_code       VARCHAR(100) NULL,
    root_cause_detail     TEXT NULL,
    process_failure_point VARCHAR(100) NULL,
    responsible_department VARCHAR(100) NULL,
    identified_by         BIGINT NOT NULL,
    identified_date       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    systemic_flag         BOOLEAN NOT NULL DEFAULT FALSE,
    linked_action_id      BIGINT NULL,
    created_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_root_cause_denial
        FOREIGN KEY (denial_id) REFERENCES denial_records (denial_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_root_cause_identified_by
        FOREIGN KEY (identified_by) REFERENCES users (user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_root_cause_action
        FOREIGN KEY (linked_action_id) REFERENCES denial_prevention_actions (action_id)
        ON UPDATE CASCADE ON DELETE SET NULL
);

CREATE INDEX idx_root_causes_denial
    ON denial_root_causes (denial_id);

CREATE INDEX idx_root_causes_category
    ON denial_root_causes (root_cause_category, systemic_flag);

COMMENT ON TABLE denial_root_causes IS 'Root cause analysis records for denials, supporting prevention initiatives and UAE RCM quality programs.';

Terminology Bindings

Field Terminology Example Value
root_cause_category Local Root Cause Classification registration_error, eligibility_not_checked, coding_error
process_failure_point Local process map pre-registration, charge_entry, claim_submission

FHIR Resource Mapping

Root cause data is not directly represented in base FHIR; if exposed, it would typically be via Task or DetectedIssue.

Table Field FHIR Resource FHIR Path
denial_id Task Task.focus.reference (to a ClaimResponse)
root_cause_detail DetectedIssue DetectedIssue.detail
systemic_flag DetectedIssue DetectedIssue.severity (mapped)

4. appeals

Purpose
Tracks appeal submissions for denials, including level, submission method/channel (e.g., DHA eClaimLink, DOH eClaims), supporting documents, and follow-up.

Field Specifications

Field Type Nullable Default Constraint Description
appeal_id BIGINT NO IDENTITY PK Appeal record ID
denial_id BIGINT NO — FK → denial_records.denial_id Denial being appealed
appeal_level VARCHAR(20) NO — IN ('level1','level2','external') Appeal level
appeal_deadline_date DATE YES — — Last date to file appeal per contract
submission_date TIMESTAMP YES — — Date/time appeal submitted
submission_method VARCHAR(30) NO 'electronic' IN ('electronic','fax','mail','portal') Method of submission
submission_channel VARCHAR(30) NO 'eClaimLink' IN ('eClaimLink','DOH_eClaims','payer_portal','other') Channel/system used
supporting_docs TEXT YES — — JSON or delimited list of document IDs/paths
appeal_letter_path VARCHAR(255) YES — — Path/URI to stored appeal letter
payer_reference_number VARCHAR(100) YES — — Reference/tracking number from payer
follow_up_date TIMESTAMP YES — — Next follow-up date/time
status VARCHAR(30) NO 'draft' IN ('draft','submitted','under_review','closed') Appeal status
assigned_to BIGINT YES — FK → users.user_id Analyst handling appeal
is_escalated BOOLEAN NO FALSE — Escalation flag
notes TEXT YES — — Internal notes
created_at TIMESTAMP NO CURRENT_TIMESTAMP — Created timestamp
updated_at TIMESTAMP NO CURRENT_TIMESTAMP — Updated timestamp
created_by BIGINT YES — FK → users.user_id Created by
updated_by BIGINT YES — FK → users.user_id Updated by

SQL DDL

SQL
CREATE TABLE appeals (
    appeal_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    denial_id              BIGINT NOT NULL,
    appeal_level           VARCHAR(20) NOT NULL,
    appeal_deadline_date   DATE NULL,
    submission_date        TIMESTAMP NULL,
    submission_method      VARCHAR(30) NOT NULL DEFAULT 'electronic',
    submission_channel     VARCHAR(30) NOT NULL DEFAULT 'eClaimLink',
    supporting_docs        TEXT NULL,
    appeal_letter_path     VARCHAR(255) NULL,
    payer_reference_number VARCHAR(100) NULL,
    follow_up_date         TIMESTAMP NULL,
    status                 VARCHAR(30) NOT NULL DEFAULT 'draft',
    assigned_to            BIGINT NULL,
    is_escalated           BOOLEAN NOT NULL DEFAULT FALSE,
    notes                  TEXT NULL,
    created_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by             BIGINT NULL,
    updated_by             BIGINT NULL,
    CONSTRAINT fk_appeal_denial
        FOREIGN KEY (denial_id) REFERENCES denial_records (denial_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_appeal_assigned_to
        FOREIGN KEY (assigned_to) REFERENCES users (user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_appeal_created_by
        FOREIGN KEY (created_by) REFERENCES users (user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_appeal_updated_by
        FOREIGN KEY (updated_by) REFERENCES users (user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_appeal_level
        CHECK (appeal_level IN ('level1','level2','external')),
    CONSTRAINT chk_appeal_submission_method
        CHECK (submission_method IN ('electronic','fax','mail','portal')),
    CONSTRAINT chk_appeal_submission_channel
        CHECK (submission_channel IN ('eClaimLink','DOH_eClaims','payer_portal','other')),
    CONSTRAINT chk_appeal_status
        CHECK (status IN ('draft','submitted','under_review','closed'))
);

CREATE INDEX idx_appeals_denial
    ON appeals (denial_id);

CREATE INDEX idx_appeals_status
    ON appeals (status, submission_date);

CREATE INDEX idx_appeals_assigned
    ON appeals (assigned_to, status);

CREATE INDEX idx_appeals_follow_up
    ON appeals (follow_up_date);

COMMENT ON TABLE appeals IS 'Appeal submissions and tracking for denied claims, including UAE DHA eClaimLink and DOH eClaims channels.';

Terminology Bindings

Field Terminology Example Value
appeal_level Local appeal level set level1 (reconsideration), external
submission_channel Local + UAE eClaims eClaimLink, DOH_eClaims
status Local appeal status under_review

FHIR Resource Mapping

Appeals can be represented as Task resources linked to ClaimResponse.

Table Field FHIR Resource FHIR Path
appeal_id Task Task.id
denial_id Task Task.focus.reference (ClaimResponse)
appeal_level Task Task.priority or extension
submission_date Task Task.executionPeriod.start
status Task Task.status
assigned_to Task Task.owner.reference

5. appeal_outcomes

Purpose
Stores payer responses to appeals, including decision, recovered amount, adjustment codes, and whether further appeal is possible. Drives KPIs such as Appeal Success Rate and Denial Recovery Rate.

Field Specifications

Field Type Nullable Default Constraint Description
outcome_id BIGINT NO IDENTITY PK Outcome record ID
appeal_id BIGINT NO — FK → appeals.appeal_id Related appeal
response_date TIMESTAMP NO CURRENT_TIMESTAMP — Date/time payer responded
payer_decision VARCHAR(30) NO — IN ('overturned','upheld','partial','withdrawn') Decision outcome
recovered_amount NUMERIC(14,2) YES 0.00 ≥ 0 Amount recovered due to this appeal
adjustment_code VARCHAR(50) YES — — Payer adjustment code (e.g., CARC/RARC equivalent)
notes TEXT YES — — Additional details
final_resolution VARCHAR(30) YES — IN ('closed_paid','closed_unpaid','escalate') Internal final resolution
appeal_round INTEGER YES 1 ≥ 1 Round number (1st, 2nd appeal)
further_appeal_possible BOOLEAN NO TRUE — Whether further appeal is allowed
created_at TIMESTAMP NO CURRENT_TIMESTAMP — Created timestamp
updated_at TIMESTAMP NO CURRENT_TIMESTAMP — Updated timestamp
created_by BIGINT YES — FK → users.user_id Created by
updated_by BIGINT YES — FK → users.user_id Updated by

SQL DDL

SQL
CREATE TABLE appeal_outcomes (
    outcome_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    appeal_id               BIGINT NOT NULL,
    response_date           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    payer_decision          VARCHAR(30) NOT NULL,
    recovered_amount        NUMERIC(14,2) NULL DEFAULT 0.00,
    adjustment_code         VARCHAR(50) NULL,
    notes                   TEXT NULL,
    final_resolution        VARCHAR(30) NULL,
    appeal_round            INTEGER NULL DEFAULT 1,
    further_appeal_possible BOOLEAN NOT NULL DEFAULT TRUE,
    created_at              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by              BIGINT NULL,
    updated_by              BIGINT NULL,
    CONSTRAINT fk_appeal_outcome_appeal
        FOREIGN KEY (appeal_id) REFERENCES appeals (appeal_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_appeal_outcome_created_by
        FOREIGN KEY (created_by) REFERENCES users (user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_appeal_outcome_updated_by
        FOREIGN KEY (updated_by) REFERENCES users (user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_payer_decision
        CHECK (payer_decision IN ('overturned','upheld','partial','withdrawn')),
    CONSTRAINT chk_final_resolution
        CHECK (final_resolution IS NULL OR final_resolution IN ('closed_paid','closed_unpaid','escalate')),
    CONSTRAINT chk_appeal_round
        CHECK (appeal_round IS NULL OR appeal_round >= 1),
    CONSTRAINT chk_recovered_amount_nonnegative2
        CHECK (recovered_amount IS NULL OR recovered_amount >= 0)
);

CREATE INDEX idx_appeal_outcomes_appeal
    ON appeal_outcomes (appeal_id);

CREATE INDEX idx_appeal_outcomes_decision
    ON appeal_outcomes (payer_decision, response_date);

COMMENT ON TABLE appeal_outcomes IS 'Payer responses to appeals, including decision and recovered amounts, for UAE denial management.';

Terminology Bindings

Field Terminology Example Value
payer_decision Local appeal decision set overturned, upheld
adjustment_code DHA/DOH adjustment code sets CO-45 equivalent in UAE payer format

FHIR Resource Mapping

Mapped via ClaimResponse and ExplanationOfBenefit.

Table Field FHIR Resource FHIR Path
payer_decision ClaimResponse ClaimResponse.outcome
recovered_amount ExplanationOfBenefit ExplanationOfBenefit.payment.amount
response_date ClaimResponse ClaimResponse.created

Purpose
Stores pre-computed aggregated denial metrics by period, payer, category, department, and provider to support dashboards and KPIs (denial rate, recovery rate, etc.) without heavy runtime aggregation.

Field Specifications

Field Type Nullable Default Constraint Description
trend_id BIGINT NO IDENTITY PK Trend record ID
period_start DATE NO — — Start date of period (e.g., month)
period_end DATE NO — — End date of period
payer_id BIGINT YES — FK → payers.payer_id Payer (NULL for all payers)
denial_category_id BIGINT YES — FK → denial_categories.denial_category_id Category (NULL for all categories)
department VARCHAR(100) YES — — Department (e.g., Radiology)
provider_id BIGINT YES — FK → providers.provider_id Ordering/attending provider
denial_count INTEGER NO 0 ≥ 0 Number of denials
denied_amount NUMERIC(16,2) NO 0.00 ≥ 0 Total denied amount (AED)
appeal_count INTEGER NO 0 ≥ 0 Number of appeals filed
recovered_amount NUMERIC(16,2) NO 0.00 ≥ 0 Total recovered amount (AED)
prevention_actions_count INTEGER NO 0 ≥ 0 Count of actions linked to this slice
denial_rate NUMERIC(5,2) YES — 0–100 Denials as % of claims (if available)
recovery_rate NUMERIC(5,2) YES — 0–100 Recovered as % of denied amount
created_at TIMESTAMP NO CURRENT_TIMESTAMP — Created timestamp
updated_at TIMESTAMP NO CURRENT_TIMESTAMP — Updated timestamp

SQL DDL

SQL
CREATE TABLE denial_trends (
    trend_id                BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    period_start            DATE NOT NULL,
    period_end              DATE NOT NULL,
    payer_id                BIGINT NULL,
    denial_category_id      BIGINT NULL,
    department              VARCHAR(100) NULL,
    provider_id             BIGINT NULL,
    denial_count            INTEGER NOT NULL DEFAULT 0,
    denied_amount           NUMERIC(16,2) NOT NULL DEFAULT 0.00,
    appeal_count            INTEGER NOT NULL DEFAULT 0,
    recovered_amount        NUMERIC(16,2) NOT NULL DEFAULT 0.00,
    prevention_actions_count INTEGER NOT NULL DEFAULT 0,
    denial_rate             NUMERIC(5,2) NULL,
    recovery_rate           NUMERIC(5,2) NULL,
    created_at              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_trend_payer
        FOREIGN KEY (payer_id) REFERENCES payers (payer_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_trend_category
        FOREIGN KEY (denial_category_id) REFERENCES denial_categories (denial_category_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_trend_provider
        FOREIGN KEY (provider_id) REFERENCES providers (provider_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_trend_denial_rate
        CHECK (denial_rate IS NULL OR (denial_rate >= 0 AND denial_rate <= 100)),
    CONSTRAINT chk_trend_recovery_rate
        CHECK (recovery_rate IS NULL OR (recovery_rate >= 0 AND recovery_rate <= 100)),
    CONSTRAINT chk_trend_counts_nonnegative
        CHECK (denial_count >= 0 AND appeal_count >= 0 AND prevention_actions_count >= 0),
    CONSTRAINT chk_trend_amounts_nonnegative
        CHECK (denied_amount >= 0 AND recovered_amount >= 0)
);

CREATE INDEX idx_denial_trends_period_payer_cat
    ON denial_trends (period_start, period_end, payer_id, denial_category_id);

CREATE INDEX idx_denial_trends_department
    ON denial_trends (department);

CREATE INDEX idx_denial_trends_provider
    ON denial_trends (provider_id);

COMMENT ON TABLE denial_trends IS 'Pre-computed denial trend data for dashboards and KPIs (denial rate, recovery rate) in UAE RCM.';

Terminology Bindings

Field Terminology Example Value
department Local department master Radiology, Emergency

FHIR Resource Mapping

Trends are analytical aggregates; if exposed, they would typically be via FHIR Measure/MeasureReport.

Table Field FHIR Resource FHIR Path
denial_rate MeasureReport MeasureReport.group.measureScore
period_start/period_end MeasureReport MeasureReport.period.start / .end

7. denial_prevention_actions

Purpose
Tracks denial prevention initiatives (process changes, training, system configuration) with responsible parties, timelines, and ROI metrics. Supports Prevention Action ROI KPI.

Field Specifications

Field Type Nullable Default Constraint Description
action_id BIGINT NO IDENTITY PK Prevention action ID
denial_category_id BIGINT YES — FK → denial_categories.denial_category_id Targeted category
payer_id BIGINT YES — FK → payers.payer_id Targeted payer (NULL for all)
department VARCHAR(100) YES — — Targeted department
description TEXT NO — — Action description
action_type VARCHAR(50) NO — IN ('process_change','training','system_config','policy_change','other') Type of action
responsible_party VARCHAR(150) NO — — Name/role of responsible person/team
target_date DATE YES — — Planned completion date
completion_date DATE YES — — Actual completion date
status VARCHAR(30) NO 'planned' IN ('planned','in_progress','completed','cancelled') Action status
pre_intervention_rate NUMERIC(5,2) YES — 0–100 Denial rate before intervention
post_intervention_rate NUMERIC(5,2) YES — 0–100 Denial rate after intervention
implementation_cost NUMERIC(16,2) YES 0.00 ≥ 0 Cost of implementing action (AED)
estimated_savings NUMERIC(16,2) YES 0.00 ≥ 0 Estimated or measured savings (AED)
roi_percent NUMERIC(7,2) YES — — ROI percentage ((savings-cost)/cost*100)
created_at TIMESTAMP NO CURRENT_TIMESTAMP — Created timestamp
updated_at TIMESTAMP NO CURRENT_TIMESTAMP — Updated timestamp
created_by BIGINT YES — FK → users.user_id Created by
updated_by BIGINT YES — FK → users.user_id Updated by

SQL DDL

SQL
CREATE TABLE denial_prevention_actions (
    action_id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    denial_category_id    BIGINT NULL,
    payer_id              BIGINT NULL,
    department            VARCHAR(100) NULL,
    description           TEXT NOT NULL,
    action_type           VARCHAR(50) NOT NULL,
    responsible_party     VARCHAR(150) NOT NULL,
    target_date           DATE NULL,
    completion_date       DATE NULL,
    status                VARCHAR(30) NOT NULL DEFAULT 'planned',
    pre_intervention_rate NUMERIC(5,2) NULL,
    post_intervention_rate NUMERIC(5,2) NULL,
    implementation_cost   NUMERIC(16,2) NULL DEFAULT 0.00,
    estimated_savings     NUMERIC(16,2) NULL DEFAULT 0.00,
    roi_percent           NUMERIC(7,2) NULL,
    created_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by            BIGINT NULL,
    updated_by            BIGINT NULL,
    CONSTRAINT fk_action_category
        FOREIGN KEY (denial_category_id) REFERENCES denial_categories (denial_category_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_action_payer
        FOREIGN KEY (payer_id) REFERENCES payers (payer_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_action_created_by
        FOREIGN KEY (created_by) REFERENCES users (user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_action_updated_by
        FOREIGN KEY (updated_by) REFERENCES users (user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_action_type
        CHECK (action_type IN ('process_change','training','system_config','policy_change','other')),
    CONSTRAINT chk_action_status
        CHECK (status IN ('planned','in_progress','completed','cancelled')),
    CONSTRAINT chk_action_rates
        CHECK (
            (pre_intervention_rate IS NULL OR (pre_intervention_rate >= 0 AND pre_intervention_rate <= 100)) AND
            (post_intervention_rate IS NULL OR (post_intervention_rate >= 0 AND post_intervention_rate <= 100))
        ),
    CONSTRAINT chk_action_costs
        CHECK (
            (implementation_cost IS NULL OR implementation_cost >= 0) AND
            (estimated_savings IS NULL OR estimated_savings >= 0)
        )
);

CREATE INDEX idx_actions_status
    ON denial_prevention_actions (status, target_date);

CREATE INDEX idx_actions_category_payer
    ON denial_prevention_actions (denial_category_id, payer_id);

COMMENT ON TABLE denial_prevention_actions IS 'Denial prevention initiatives with ROI metrics for UAE revenue cycle improvement.';

Terminology Bindings

Field Terminology Example Value
action_type Local action type set training, system_config
status Local project status in_progress

FHIR Resource Mapping

If exposed, actions can be represented as PlanDefinition/ActivityDefinition or Task.

Table Field FHIR Resource FHIR Path
description PlanDefinition PlanDefinition.description
status PlanDefinition PlanDefinition.status
target_date Task Task.executionPeriod.end

8. payer_denial_scorecards

Purpose
Stores periodic payer performance metrics (denial rate, appeal success rate, recovery, rank) used for payer scorecards and contract negotiations in coordination with Policy & Contract Management.

Field Specifications

Field Type Nullable Default Constraint Description
scorecard_id BIGINT NO IDENTITY PK Scorecard record ID
payer_id BIGINT NO — FK → payers.payer_id Payer being scored
period_start DATE NO — — Start of reporting period
period_end DATE NO — — End of reporting period
total_claims INTEGER NO 0 ≥ 0 Total claims submitted
total_denials INTEGER NO 0 ≥ 0 Total denials
denial_rate NUMERIC(5,2) NO 0.00 0–100 Denials as % of claims
total_denied_amount NUMERIC(16,2) NO 0.00 ≥ 0 Total denied amount (AED)
total_recovered NUMERIC(16,2) NO 0.00 ≥ 0 Total recovered amount (AED)
appeal_success_rate NUMERIC(5,2) NO 0.00 0–100 % of appeals overturned/partial
avg_resolution_days NUMERIC(5,2) NO 0.00 ≥ 0 Average days to resolution
rank INTEGER YES — ≥ 1 Payer rank (1 = best)
preventable_denial_count INTEGER NO 0 ≥ 0 Count of preventable denials
preventable_denial_rate NUMERIC(5,2) NO 0.00 0–100 Preventable denials as % of total denials
created_at TIMESTAMP NO CURRENT_TIMESTAMP — Created timestamp
updated_at TIMESTAMP NO CURRENT_TIMESTAMP — Updated timestamp

SQL DDL

SQL
CREATE TABLE payer_denial_scorecards (
    scorecard_id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payer_id                 BIGINT NOT NULL,
    period_start             DATE NOT NULL,
    period_end               DATE NOT NULL,
    total_claims             INTEGER NOT NULL DEFAULT 0,
    total_denials            INTEGER NOT NULL DEFAULT 0,
    denial_rate              NUMERIC(5,2) NOT NULL DEFAULT 0.00,
    total_denied_amount      NUMERIC(16,2) NOT NULL DEFAULT 0.00,
    total_recovered          NUMERIC(16,2) NOT NULL DEFAULT 0.00,
    appeal_success_rate      NUMERIC(5,2) NOT NULL DEFAULT 0.00,
    avg_resolution_days      NUMERIC(5,2) NOT NULL DEFAULT 0.00,
    rank                     INTEGER NULL,
    preventable_denial_count INTEGER NOT NULL DEFAULT 0,
    preventable_denial_rate  NUMERIC(5,2) NOT NULL DEFAULT 0.00,
    created_at               TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at               TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_scorecard_payer
        FOREIGN KEY (payer_id) REFERENCES payers (payer_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_scorecard_rates
        CHECK (
            denial_rate >= 0 AND denial_rate <= 100 AND
            appeal_success_rate >= 0 AND appeal_success_rate <= 100 AND
            preventable_denial_rate >= 0 AND preventable_denial_rate <= 100
        ),
    CONSTRAINT chk_scorecard_counts
        CHECK (total_claims >= 0 AND total_denials >= 0 AND preventable_denial_count >= 0),
    CONSTRAINT chk_scorecard_amounts
        CHECK (total_denied_amount >= 0 AND total_recovered >= 0),
    CONSTRAINT chk_scorecard_rank
        CHECK (rank IS NULL OR rank >= 1)
);

CREATE INDEX idx_scorecards_payer_period
    ON payer_denial_scorecards (payer_id, period_start, period_end);

CREATE INDEX idx_scorecards_rank
    ON payer_denial_scorecards (period_start, period_end, rank);

COMMENT ON TABLE payer_denial_scorecards IS 'Payer performance scorecards for UAE RCM, used for contract negotiations and monitoring.';

Terminology Bindings

All coded fields here are internal metrics; no external terminology bindings.

FHIR Resource Mapping

If exposed, scorecards map to MeasureReport grouped by payer.

Table Field FHIR Resource FHIR Path
payer_id MeasureReport MeasureReport.subject (Organization)
denial_rate MeasureReport MeasureReport.group[denial].measureScore
appeal_success_rate MeasureReport MeasureReport.group[appeal].measureScore

Data Volume Estimates

Estimates assume a medium-to-large UAE hospital (300–500 beds) with multi-payer contracts.

Table Initial Rows (Year 1) Annual Growth Notes
denial_records 150,000–250,000 +15–25% 5–10% of all claims; includes line-level denials
denial_categories ~30 Low Master data; small, stable
denial_root_causes 80,000–150,000 +15–25% Not all denials analyzed; some multiple root causes per denial
appeals 60,000–100,000 +15–25% Typically 40–60% of denials appealed
appeal_outcomes 60,000–100,000 +15–25% Usually one per appeal; more if multiple rounds
denial_trends 5,000–20,000 +10–15% Depends on granularity (payer/category/department/provider/month)
denial_prevention_actions 100–300 +10–20% Management-level actions; low volume
payer_denial_scorecards 500–2,000 +10–15% Per payer per month/quarter

Storage and indexing should be planned for at least 7–10 years of history for denial_records, appeals, and appeal_outcomes, given UAE financial and audit requirements.

Data Retention Policy

Retention must comply with UAE regulations and facility policy, considering financial audit, payer dispute windows, and UAE PDPL (data minimization, purpose limitation).

Table Recommended Retention Rationale (UAE Context)
denial_records Minimum 10 years from resolution date Supports long-term financial audits, payer disputes, and trend analysis; aligns with common UAE hospital financial record practices.
denial_categories Retain indefinitely; soft-delete via is_active Master data; required to interpret historical denials.
denial_root_causes 10 years from associated denial resolution Needed for quality and compliance reviews; PDPL allows retention while necessary for legitimate business and legal purposes.
appeals 10 years from final outcome Supports payer disputes, DOH/DHA audits, and internal compliance.
appeal_outcomes 10 years from final outcome Financial impact and audit trail for recovered amounts.
denial_trends 7–10 years Historical analytics; can be recomputed from raw data if needed, but retention improves performance.
denial_prevention_actions 7–10 years after completion Evidence of quality improvement and governance; useful for accreditation and payer negotiations.
payer_denial_scorecards 7–10 years Longitudinal payer performance for contract renegotiation and regulatory reporting.

PDPL Considerations

  • All tables contain financial and potentially identifiable patient data via foreign keys. Access must be role-based (Denial Analyst, Manager, etc.) and logged.
  • When patient data is anonymized or deleted per PDPL requests, foreign keys (patient_id) should be handled according to enterprise policy (e.g., pseudonymization or retention under legal basis for financial records).
  • Any exports for payer negotiations or external reporting must be de-identified where possible, especially when shared outside the treating facility or payer.
← Previous Denial Analysis User Roles & Permissions Next → Denial Analysis Screen Requirements
content/rcm/denial-analysis/03-data-specifications.md Generated 2026-02-20 22:54