Payer Plans Data Model

Overview

The payer_plans table stores insurance plans from payers (Cigna, Aetna, Medicare Advantage, Medicaid MCOs) with their coverage policies, appeal processes, and denial triggers. This data powers marketplace search, appeal strategy recommendation, and prevention rules engine gates.

Table Schema

CREATE TABLE payer_plans (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  payer_id UUID NOT NULL,
  
  -- Plan identity
  plan_name VARCHAR(255) NOT NULL,
  plan_code VARCHAR(50) UNIQUE NOT NULL, -- e.g., "CIGNA-EAP-2026-NY"
  plan_type TEXT NOT NULL, -- EAP, PPO, HMO, POS, Medicaid, Medicare Advantage
  state_code VARCHAR(2),
  effective_date DATE NOT NULL,
  expiration_date DATE,
  
  -- Coverage & policy
  coverage_data JSONB NOT NULL, -- see structure below
  network_status TEXT, -- in-network, out-of-network, hybrid
  
  -- Performance metrics
  approval_rate DECIMAL(5, 2), -- 0-100, historical approval percentage
  average_processing_days INT,
  appeal_overturn_rate DECIMAL(5, 2),
  
  -- Sourcing & maintenance
  data_source TEXT NOT NULL, -- payer_portal, regulatory_filing, partner_api, manual_entry
  last_verified_date DATE,
  verified_by_user_id UUID,
  update_frequency TEXT, -- monthly, quarterly, annual
  
  -- Metadata
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  visibility TEXT DEFAULT 'private', -- private, shared, public
  
  UNIQUE(tenant_id, payer_id, plan_code),
  FOREIGN KEY(tenant_id) REFERENCES tenants(id),
  FOREIGN KEY(payer_id) REFERENCES payer_registry(id),
  FOREIGN KEY(verified_by_user_id) REFERENCES users(id)
);

CREATE INDEX idx_payer_plans_by_payer ON payer_plans(payer_id);
CREATE INDEX idx_payer_plans_by_state ON payer_plans(state_code);
CREATE INDEX idx_payer_plans_by_plan_type ON payer_plans(plan_type);
CREATE INDEX idx_payer_plans_coverage_search ON payer_plans USING GIN(coverage_data);

coverage_data JSONB Structure

The coverage_data JSONB field stores plan-specific clinical, administrative, and appeal policy information:

{
  "behavioral_health": {
    "covered_services": [
      "individual_therapy",
      "group_therapy",
      "intensive_outpatient_program",
      "partial_hospitalization",
      "inpatient_psych",
      "medication_management"
    ],
    "medical_necessity_criteria": {
      "description": "Psychosocial stressors, functional impairment, treatment response",
      "evidence_types": ["DSM-5_diagnosis", "GAF_score", "treatment_history"],
      "citation": "42 CFR Part 2, SAMHSA"
    },
    "authorization_requirements": {
      "prior_auth_required": true,
      "emergency_care_exception": true,
      "max_concurrent_visits": 2,
      "frequency_limits": {
        "individual_therapy": "1x per week",
        "group_therapy": "unlimited",
        "iop": "5 days per week max"
      }
    },
    "exclusions": [
      "services_for_legal_issues",
      "educational_counseling",
      "marital_counseling_without_diagnosis"
    ],
    "coverage_limits": {
      "annual_visit_limit": null,
      "lifetime_limit": null,
      "day_limits": {
        "inpatient_psych": 30
      },
      "dollar_limits": {
        "annual_max": null
      }
    }
  },
  "appeal_process": {
    "levels": [
      {
        "level": 1,
        "name": "Internal Review",
        "timeframe_days": 30,
        "required_evidence": ["clinical_notes", "treatment_plan", "medical_records"],
        "contact": {
          "department": "Appeals",
          "phone": "1-800-CIGNA-XX",
          "fax": "212-555-1234",
          "email": "appeals@cigna.com",
          "address": "Cigna Appeals, 123 Main St, Hartford, CT 06101"
        }
      },
      {
        "level": 2,
        "name": "External Review",
        "timeframe_days": 60,
        "required_evidence": ["level_1_denial", "additional_clinical_evidence"],
        "contact": {
          "entity": "Connecticut Insurance Department",
          "phone": "1-860-297-3800"
        }
      }
    ],
    "expedited_available": true,
    "expedited_timeframe_days": 3,
    "success_factors": [
      "Recent treatment records within 30 days",
      "Specific functional impairment language (GAF scores, ADL impact)",
      "Peer-reviewed research supporting medical necessity",
      "Prior successful appeals for same clinical scenario"
    ],
    "common_denial_reasons": [
      {
        "code": "NOT_MEDICALLY_NECESSARY",
        "description": "Service not medically necessary per plan guidelines",
        "overturn_rate": 45,
        "evidence_helpful": ["clinical_deterioration", "treatment_efficacy_data"]
      },
      {
        "code": "FREQUENCY_EXCEEDS_PLAN_LIMITS",
        "description": "Requested frequency exceeds plan coverage limits",
        "overturn_rate": 62,
        "evidence_helpful": ["stepped_care_failure", "acute_crisis_documentation"]
      }
    ]
  },
  "sud_compliance": {
    "applies": true,
    "regulation": "42 CFR Part 2",
    "restrictions": [
      "Cannot access patient SUD treatment records without consent",
      "Cannot use SUD history in coverage determination",
      "Requires separate BAA for SUD data handling"
    ],
    "appeal_considerations": "SUD-related denials require extra confidentiality protections"
  },
  "specialty_considerations": {
    "telephony": {
      "covered": true,
      "reimbursement_parity": true,
      "note": "Telehealth reimbursed at same rate as in-person"
    },
    "group_therapy": {
      "covered": true,
      "modifiers": "Applies toward visit frequency limits"
    }
  },
  "metadata": {
    "last_updated": "2026-03-15",
    "updated_by_user_id": "user-456",
    "source_url": "https://cigna-benefits.com/plans/EAP-2026/",
    "verification_notes": "Updated per 2026 plan documents on payer website",
    "known_gaps": [
      "Telehealth prior auth requirements unconfirmed",
      "Group therapy pricing tier not documented"
    ]
  }
}

Medical Necessity Criteria Structure

Each plan's medical necessity criteria field follows this pattern for consistency:

{
  "description": "Plain-language summary of medical necessity standard",
  "clinical_indicators": [
    "Diagnosis code: F32.x (Depression) or F41.x (Anxiety)",
    "Functional impairment: ADL disruption documented in notes",
    "Treatment response: Failed trial of lower-level care",
    "Acuity: GAF score ≤50 indicates moderate-severe impairment"
  ],
  "documentation_requirements": {
    "required_fields": [
      "Current DSM-5 diagnosis with Z-codes for psychosocial stressors",
      "Global Assessment of Functioning (GAF) score",
      "Treatment history (prior attempts, current medications)",
      "Functional impact statement (work, relationships, self-care)"
    ],
    "must_have_within": "30 days of request",
    "sufficient_without": [
      "Court orders alone",
      "Family member requests without clinical assessment"
    ]
  },
  "evidence_types_that_strengthen_appeal": [
    "Peer-reviewed research on diagnosis/treatment",
    "Prior authorizations for same service (shows covered pattern)",
    "Treatment response data (symptom improvement on current therapy)",
    "Risk escalation docs (suicide risk, functional decline)"
  ],
  "regulatory_basis": "42 CFR Part 2 (SUD), 42 CFR §438.210 (Medicaid managed care standards)"
}

Performance Metrics

The approval_rate, average_processing_days, and appeal_overturn_rate fields are denormalized from historical appeal data to support fast filtering:

-- Update payer plan metrics nightly
UPDATE payer_plans pp SET
  approval_rate = (
    SELECT COUNT() FILTER(WHERE state = 'approved')  100.0 / COUNT()
    FROM appeals a
    WHERE a.payer_id = pp.payer_id AND a.plan_id = pp.id
      AND a.state_changed_at > NOW() - INTERVAL '12 months'
  ),
  average_processing_days = (
    SELECT AVG(
      EXTRACT(DAY FROM (a.state_changed_at - a.created_at))
    )
    FROM appeals a
    WHERE a.payer_id = pp.payer_id AND a.plan_id = pp.id
      AND a.state_changed_at > NOW() - INTERVAL '12 months'
  ),
  appeal_overturn_rate = (
    SELECT COUNT() FILTER(WHERE state = 'approved')  100.0 / COUNT()
    FROM appeals a
    WHERE a.payer_id = pp.payer_id AND a.plan_id = pp.id
      AND a.created_at > a.state_changed_at - INTERVAL '120 days'
  ),
  updated_at = NOW()
WHERE updated_at < NOW() - INTERVAL '1 day';

Query Patterns

Find Highest-Approval Plans for Clinical Scenario

SELECT 
  pp.plan_code,
  pp.plan_name,
  pp.approval_rate,
  pp.appeal_overturn_rate,
  pp.coverage_data -> 'behavioral_health' -> 'medical_necessity_criteria' as med_nec
FROM payer_plans pp
WHERE pp.state_code = $1 -- CA
  AND pp.plan_type IN ('EAP', 'PPO') 
  AND pp.coverage_data -> 'behavioral_health' ->> 'medical_necessity_criteria' ILIKE '%functional impairment%'
  AND pp.approval_rate > 75
ORDER BY pp.approval_rate DESC, pp.appeal_overturn_rate DESC;

Check Plan Coverage for Service

SELECT 
  pp.plan_code,
  pp.plan_type,
  CASE 
    WHEN pp.coverage_data -> 'behavioral_health' -> 'covered_services' @> '"intensive_outpatient_program"'
      THEN 'covered'
    WHEN pp.coverage_data -> 'behavioral_health' -> 'exclusions' @> '"intensive_outpatient_program"'
      THEN 'excluded'
    ELSE 'unspecified'
  END as iop_coverage_status
FROM payer_plans pp
WHERE pp.plan_code = $1;

Extract Appeal Contact Information

SELECT 
  pp.plan_code,
  pp.coverage_data -> 'appeal_process' -> 'levels' -> 0 -> 'contact' as first_level_contact,
  pp.coverage_data -> 'appeal_process' ->> 'expedited_available' as expedited_option
FROM payer_plans pp
WHERE pp.payer_id = $1;

Data Sourcing and Maintenance

Payer plans data comes from three sources:

Payer Portals (60% of data)

Direct subscription to payer plan documents
Updated quarterly or as payer publishes new plans
Example: Cigna Broker Portal, Aetna Plan Search
Regulatory Filings (30% of data)
State insurance department plan filings
Medicaid managed care rate certifications
Medicare Advantage plan details (CMS)
Manual Verification (10% of data)
Direct payer outreach for clarifications
Partner submissions with domain expertise
Internal appeals analyst notes

Verification Protocol

Every 90 days: Re-validate last_verified_date against payer documents
If expired: Mark visibility = 'private' until reverified
If plan discontinued: Set expiration_date retroactively

Marketplace Integration

Plan Search — Users filter by state, plan type, coverage specifics
Appeal Strategy — System recommends precedents matching plan coverage/appeal process
Prevention Rules — Gates check plan approval rates, appeal overturn likelihood
Pricing — Market query costs vary by appeal complexity (see ENDPOINTS-MARKETPLACE.md)