Claims Adjudication Guide

-- Claim Adjudication
CREATE TABLE claim_adjudication (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clm_id              UUID NOT NULL REFERENCES claim(id),
  clm_line_id         UUID REFERENCES claim_line(id),
  
  -- Adjudication Status
  adjudication_status   VARCHAR(50),              -- approved, denied, pended
  adjudication_dt     TIMESTAMP NOT NULL DEFAULT NOW(),
  
  -- Amounts
  billed_amt         DECIMAL(10,2) NOT NULL,
  allowed_amt        DECIMAL(10,2),
  deductible_amt     DECIMAL(10,2) DEFAULT 0,
  copay_amt          DECIMAL(10,2) DEFAULT 0,
  coinsurance_amt    DECIMAL(10,2) DEFAULT 0,
  cob_amt            DECIMAL(10,2) DEFAULT 0,   -- Paid by other insurance
  paid_amt           DECIMAL(10,2),
  patient_responsibility DECIMAL(10,2),
  
  -- Adjustments
  adjustment_amt     DECIMAL(10,2) DEFAULT 0,
  adjustment_reason     VARCHAR(10),               -- CARC code
  
  -- Denial
  denial_code           VARCHAR(10),               -- CARC code
  denial_reason         TEXT,
  remark_code           VARCHAR(10),               -- RARC code
  
  -- Pricing
  contract_rate         DECIMAL(10,2),
  fee_schedule_rate     DECIMAL(10,2),
  pricing_method        VARCHAR(50),               -- contracted, fee_schedule, UCR
  
  -- Processing
  auto_adjudicated      BOOLEAN DEFAULT FALSE,
  reviewer_id           UUID,
  review_notes          TEXT,
  
  created_at            TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_adj_claim ON claim_adjudication(clm_id);
CREATE INDEX idx_adj_status ON claim_adjudication(adjudication_status);

-- Payment Calculation Rules
CREATE TABLE adjudication_rule (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  rule_nm             VARCHAR(255) NOT NULL,
  rule_type             VARCHAR(50),               -- eligibility, pricing, medical_necessity
  priority              INTEGER,
  
  -- Conditions (JSON for flexibility)
  conditions            JSONB,                     -- e.g., {"service_type": "surgery", "place_of_service": "11"}
  
  -- Actions
  action_type           VARCHAR(50),               -- approve, deny, pend, adjust
  action_config         JSONB,                     -- Action parameters
  
  -- Status
  active                BOOLEAN DEFAULT TRUE,
  effective_dt        DATE,
  termination_dt      DATE,
  
  created_at            TIMESTAMP DEFAULT NOW()
);

-- Denial Codes Reference
CREATE TABLE denial_code (
  code                  VARCHAR(10) PRIMARY KEY,
  code_type             VARCHAR(10),               -- CARC, RARC
  description           TEXT,
  category              VARCHAR(100),              -- CO, OA, PR, PI
  patient_responsibility BOOLEAN,
  
  created_at            TIMESTAMP DEFAULT NOW()
);

-- Coordination of Benefits
CREATE TABLE cob_calculation (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clm_id              UUID NOT NULL REFERENCES claim(id),
  
  -- Primary Insurance
  primary_payer_id      UUID,
  primary_allowed       DECIMAL(10,2),
  primary_paid          DECIMAL(10,2),
  
  -- Secondary Insurance
  secondary_payer_id    UUID,
  secondary_allowed     DECIMAL(10,2),
  secondary_paid        DECIMAL(10,2),
  
  -- Tertiary (if applicable)
  tertiary_payer_id     UUID,
  tertiary_paid         DECIMAL(10,2),
  
  -- Total
  total_paid            DECIMAL(10,2),
  patient_balance       DECIMAL(10,2),
  
  calculated_dt       TIMESTAMP DEFAULT NOW()
);