Source map
Where the data actually lives
14 source systems behind Cascadia Health Plan, holding 759 tables, with 6 reporting tools built on top.
0 of 1,065
Zero enforced foreign keys across the entire estate.
All 1,065 cross-table links — 426 of them spanning different applications —
are by naming convention only. Nothing in the databases guarantees a row points at a real parent.
Every join in every report is a handshake, not a contract.
Operational systems
systems of record · these hold the 759 tablesCascadia EDW & BI
Conformed enterprise data warehouse and executive BI layer: dimensional models, conformed marts, and the bulk of board/executive reporting that recomputes contested KPIs (MLR, Membership, Revenue) from conformed data — often re-derived manually in Excel.
snapshot_membership_daily2,100,000,000 bridge_claim_diagnosis1,100,000,000 fact_claim_line920,000,000 fact_eob_remittance680,000,000 stg_claims_raw380,000,000 fact_claim340,000,000 stg_pharmacy_raw220,000,000 fact_pharmacy_claim210,000,000 stg_premium_raw90,000,000 fact_premium86,000,000 stg_eligibility_raw52,000,000 fact_quality_measure48,000,000 fact_premium_billing42,000,000 fact_cob_detail42,000,000 fact_edit_override14,000,000 fact_capitation12,000,000 fact_authorization9,800,000 fact_risk_score7,200,000 ref_fee_schedule4,800,000 fact_er_visit4,200,000 fact_enrollment_span4,100,000 bridge_member_plan3,900,000 stg_gl_raw3,800,000 fact_care_gap3,800,000 +59 more
ClaimCore Adjudication
Institutional and professional claims adjudication system of record for Cascadia Health Plan — ingests 837I/837P claims, applies benefit and edit logic, adjudicates lines, coordinates benefits, generates EOBs/remittance, and issues provider payments and capitation.
claim_status_history380,000,000 adjudication_message310,000,000 gl_posting240,000,000 adjudication_result225,000,000 pricing_audit225,000,000 claim_line210,000,000 eob_line_detail205,000,000 remittance_line195,000,000 claim_line_payment_allocation195,000,000 member_cost_share_detail195,000,000 claim_diagnosis150,000,000 risk_adjustment_claim_extract110,000,000 claim_edit95,000,000 claim_line_modifier68,000,000 claim_note62,000,000 encounter_submission58,000,000 duplicate_claim_check52,000,000 edi_837_transaction49,000,000 claim_header48,000,000 claim_geo_attribution48,000,000 timely_filing_check48,000,000 broker_attribution48,000,000 claim_payment_summary48,000,000 claim_sla_tracking48,000,000 +53 more
Atlas Finance (Oracle GL ERP)
System of record for the financial close: chart of accounts, general ledger, AP, journal entries, cost centers, allocations, fixed assets, IBNR reserve postings, statutory/STAT reporting, and the financials the board sees. Computes MLR and premium revenue on a GL/earned-incurred basis that diverges from Claims and Actuarial.
gl_chart_of_accounts0 gl_cost_center_master0 gl_segment_values0 gl_code_combinations0 gl_ledger_definition0 gl_journal_header0 gl_journal_line0 gl_balances0 gl_accounting_periods0 gl_daily_rates0 gl_journal_import_interface0 gl_allocation_rules0 gl_allocation_runs0 fund_master0 ap_supplier_master0 ap_supplier_site0 ap_invoice_header0 ap_invoice_line0 ap_payment0 ap_payment_application0 ap_claims_disbursement_feed0 ap_hold0 ap_1099_detail0 ar_cash_receipt0 +39 more
MemberConnect Enrollment & Eligibility
System of record for member master, enrollment spans, eligibility determination, group/employer structure, benefit plan assignment, benefit accumulators, dependents, ID cards, terminations, and retro-enrollment for Cascadia Health Plan's ~600k members.
audit_trail42,000,000 member_month_summary21,600,000 accumulator_transaction18,400,000 member_premium9,800,000 eligibility_verification_log8,900,000 enrollment_834_inbound7,200,000 member_communication_log6,400,000 enrollment_transaction5,600,000 eligibility_history4,200,000 medicaid_eligibility_feed3,100,000 capitation_eligibility_roster2,900,000 cms_membership_reconciliation2,400,000 benefit_accumulator2,100,000 eligibility1,620,000 enrollment_span1,480,000 member_xref1,480,000 member_address1,340,000 id_card_print_queue1,340,000 eligibility_response_cache1,200,000 id_card1,180,000 member_contact980,000 termination740,000 coverage_tier_assignment640,000 member_master612,000 +33 more
Provider360 Network Management
System of record for the provider network: provider master data, NPI registry, credentialing, contracts, fee schedules, network affiliation, par/non-par status, and the public provider directory. Feeds allowed-amount logic to claims adjudication.
provider_audit_trail2,900,000 fee_schedule_line2,400,000 medicare_fee_reference480,000 contract_rate_schedule380,000 provider_credentialing_status_history340,000 directory_verification_log310,000 provider_par_status210,000 primary_source_verification210,000 provider_quality_score188,000 network_affiliation156,000 contract_line_item142,000 provider_outreach140,000 network_tier_assignment118,000 facility_provider_link98,000 procedure_code_reference92,000 provider_efficiency_metric92,000 provider_data_change_request89,000 provider_contract_term76,000 provider_specialty74,000 provider_education71,000 provider_license67,000 provider_tin_link63,000 provider_location62,000 provider_geo_coordinate62,000 +32 more
Workforce HRIS
System of record for all Cascadia Health Plan employees: org hierarchy, positions, payroll, compensation, benefits administration, time & attendance, recruiting/ATS, and performance. Supplies the canonical employee_id used across the enterprise and is the source of the people who could own data assets but mostly do not.
employee_master4,200 employee_demographic0 employee_contact0 employee_address0 emergency_contact0 employment_event0 termination0 position0 job_code0 job_family0 department0 org_unit0 location0 supervisory_org0 compensation0 compensation_history0 pay_grade0 salary_survey_market0 bonus_plan0 bonus_award0 payroll_run0 payroll_detail0 payroll_earning0 payroll_deduction0 +30 more
CareTrak Utilization & Case Management
System of record for prior authorization, utilization management, concurrent review, case & disease management, clinical assessments, care plans, clinical notes, referrals, and pre-service/medical-necessity denials for Cascadia Health Plan's ~600k members.
um_audit_log58,000,000 assessment_question24,000,000 clinical_note12,500,000 auth_status_history9,700,000 member_contact_log8,900,000 auth_note7,300,000 auth_line6,100,000 member_medication5,400,000 auth_procedure5,200,000 auth_diagnosis4,800,000 program_metric_result4,600,000 um_work_queue_item4,200,000 auth_attachment4,100,000 referral_status_history3,900,000 auth_claim_link3,800,000 auth_determination3,400,000 sla_event3,400,000 authorization_letter3,100,000 auth_request2,900,000 fax_inbound2,700,000 auth_policy_link2,400,000 care_plan_intervention2,300,000 member_condition2,200,000 auth_clinical_criteria2,100,000 +28 more
RxGateway PBM
Adjudicates pharmacy (Rx) claims in real time, maintains the NDC drug master, formulary and tiering, prior authorization, MAC/AWP pricing, GPI therapeutic classification, the pharmacy network, rebate accruals, and Drug Utilization Review (DUR) for Cascadia Health Plan's ~600k members.
pharmacy_claim_line44,000,000 pricing_adjudication_result44,000,000 pharmacy_claim_header42,000,000 rebate_accrual21,000,000 cms_pde_record9,800,000 ncpdp_reject_code7,800,000 awp_price_history6,200,000 medication_adherence_metric4,200,000 controlled_substance_log4,100,000 pharmacy_benefit_accumulator3,600,000 dur_alert3,100,000 cob_pharmacy_detail2,900,000 mac_price_detail2,800,000 member_pharmacy_eligibility2,400,000 dir_fee1,600,000 formulary_drug1,400,000 edit_override920,000 pharmacy_remittance820,000 prior_auth_request680,000 vaccine_administration640,000 pharmacy_network_affiliation540,000 compound_ingredient510,000 dur_interaction_master420,000 copay_card_offset380,000 +25 more
PremiumBill Billing & AR
System of record for premium billing, invoicing, premium receipt/cash application, accounts receivable, aging, write-offs, group and individual billing, binder payments, and delinquency/grace-period management across all lines of business.
billing_audit_log210,000,000 ar_transaction120,000,000 invoice_line62,000,000 premium_calculation58,000,000 gl_posting48,000,000 cash_application34,000,000 billing_member_coverage24,000,000 ar_aging_snapshot18,000,000 ar_balance15,000,000 group_billing_roster14,000,000 lockbox_transaction11,000,000 premium_revenue_recognition9,600,000 premium_receipt9,200,000 delinquency_status7,400,000 cms_payment_reconciliation7,100,000 subsidy_aptc_detail6,800,000 adjustment_transaction5,800,000 invoice_delivery4,900,000 invoice_header4,800,000 medicare_premium_billing4,200,000 dunning_event3,200,000 ssa_premium_withhold3,100,000 broker_commission_billing2,100,000 membership_count_snapshot1,900,000 +25 more
ActuaryWorks Risk & Underwriting
System of record for IBNR reserves, completion factors, rating, underwriting, risk adjustment (HCC/RAF), trend, reserve development, and experience studies for Cascadia Health Plan
member_month_actuarial21,600,000 claim_lag_snapshot18,400,000 hcc_capture3,100,000 risk_score1,240,000 raf_reconciliation960,000 chart_review_finding680,000 reserve_run_detail520,000 raf_gap_opportunity420,000 pmpm_actual410,000 completion_factor_triangle340,000 ibnr_by_provider290,000 capitation_actuarial210,000 pharmacy_trend_actuarial96,000 reserve_development86,000 loss_ratio_summary86,000 underwriting_audit_log86,000 actuarial_gl_bridge62,000 reserve_variance_analysis58,000 membership_projection54,000 medical_underwriting_assessment48,000 rating_factor42,000 actuarial_data_load_log42,000 provider_risk_pool38,000 manual_rate18,600 +25 more
StarsQuality HEDIS & STARS
Computes HEDIS measure rates, gaps in care, CAHPS/HOS survey results, and the CMS Star Rating rollup; manages supplemental data ingestion and NCQA/CMS regulatory submissions. Member-month denominators are sourced from Membership (contested).
measure_master0 measure_version_history0 value_set0 value_set_code0 measure_eligible_population0 measure_denominator0 measure_numerator0 measure_exclusion0 measure_rate_result0 hybrid_sample0 medical_record_review0 chart_chase0 supplemental_data_source0 supplemental_data_record0 lab_result_feed0 gap_in_care0 gap_closure_activity0 member_gap_summary0 provider_measure_attribution0 provider_quality_scorecard0 star_cut_point0 measure_star_assignment0 contract_star_rating0 improvement_measure_calc0 +22 more
BrokerHub CRM & Sales
System of record for broker channel, agency hierarchy, sales pipeline (leads, quotes, applications), commission accounting, member CRM, marketing campaigns, retention, and NPS for Cascadia Health Plan's distribution arm. Treats membership on a SOLD/CRM basis (book-of-business), which diverges from enrollment- and finance-system definitions.
crm_audit_log58,000,000 email_engagement42,000,000 campaign_member18,000,000 commission_line8,900,000 churn_prediction7,600,000 sold_membership_snapshot7,200,000 member_interaction5,800,000 campaign_response4,100,000 document_attachment3,200,000 lead_activity2,400,000 application_status_history2,100,000 campaign_attribution1,900,000 quote_line1,600,000 broker_performance_snapshot840,000 nps_survey820,000 book_of_business640,000 member_crm_profile640,000 member_consent640,000 application540,000 commission_statement520,000 quote480,000 broker_payment480,000 override_commission410,000 renewal410,000 +21 more
Sentinel FWA / SIU
Detects, scores, investigates, and recovers improper payments across medical, pharmacy, and provider claims; manages SIU case lifecycle, provider/member flags, aberrancy analytics, law-enforcement referrals, and recovery accounting that forks into the claims COB/recovery ledger.
flagged_claim_line5,240,000 rule_hit4,120,000 score_feature2,980,000 audit_log2,840,000 flagged_claim1,860,000 flagged_pharmacy_claim742,000 aberrancy_score486,000 network_link_analysis412,000 prepay_edit318,000 billing_pattern_profile262,000 sanction_screening196,000 case_note148,000 recovery_transaction134,500 case_evidence98,300 case_status_history92,500 sla_tracking76,400 recovery71,800 overpayment67,300 medical_record_request58,700 recoupment_letter52,600 vendor_recovery_assignment48,900 chart_review44,100 investigation_finding41,200 controlled_substance_pattern38,400 +20 more
Resolve Appeals & Grievances
System of record for member and provider appeals, grievances, and complaints across all lines of business (Medicare Advantage, Medicaid, Commercial/ACA). Tracks intake, classification into CMS/state categories, clinical and administrative review, determinations and overturns, and regulatory timeliness against CMS and state thresholds. Feeds STARS, compliance, and CMS Part C/D universe reporting.
appeal_audit_log4,200,000 case_note1,840,000 case_document920,000 member_demographic604,000 complaint_intake310,000 case_assignment276,000 timeliness_clock244,000 cms_universe_export244,000 acknowledgement_letter231,000 appeal_level_history214,000 resolution_letter205,000 appeal_claim_link192,000 determination151,000 appeal_case148,000 appeal_classification148,000 appeal_provider_link138,000 clinical_review119,000 appeal_auth_link104,000 grievance_case96,000 timeliness_pause88,000 overturn_reason61,000 member_representative52,000 appeal_pharmacy_link47,000 category_ii_complaint_link15,400 +11 more
Reporting & BI tools
where the numbers get recomputed · 264 reports across 6 toolsPower BI
Excel
Tableau
SSRS
Cognos
Oracle FSG