Appearance
Tier 4 — Internal Data Integrity
Last run: April 14, 2026 Duration: ~4 seconds (no CMS calls) Plans checked: 4,044 Zips checked: 28,196
Purpose
Pure database consistency checks — no CMS calls. Catches data quality issues that would surface as false negatives in Tiers 1-3.
Checks performed
| # | Check | What it catches |
|---|---|---|
| 1 | Plans have non-empty ageRatesByArea | Loading bugs |
| 2 | Plans have non-empty premiumsByRatingArea | Loading bugs |
| 3 | RAs in ageRatesByArea match premiumsByRatingArea | Inconsistent ingestion |
| 4 | Plans have non-zero rates in served RAs | Zero-rate bugs |
| 5 | Every countiesServed county has a usable RA in plan rates | Service area mismatches |
| 6 | partialCountyZips keys are subset of countiesServed | Partial county data integrity |
| 7 | All zips have a regionId set | Enrichment completeness |
| 8 | No orphan zips (zips for counties with no plans) | Dead data |
| 9 | Age curve sanity: age 35 / age 21 ratio is 1.15-1.30 | PUF data anomalies |
Results — April 14, 2026
| Check | Result |
|---|---|
Plans without ageRatesByArea | 0 ✅ |
Plans without premiumsByRatingArea | 0 ✅ |
| RA mismatch (age vs premium) | 0 ✅ |
| Plans with zero rates | 0 ✅ |
countiesServed not in any RA | 24 ⚠️ (cosmetic) |
partialCountyZips integrity | 0 ✅ |
Zips without regionId | 0 ✅ |
| Orphan zips | 0 ✅ |
| Age curve anomalies | 196 ⚠️ (PUF data) |
Issue 1: countsServed not in any RA (24 plans)
Affected: All 24 are FL plans listing Sumter County in countiesServed.
Root cause: PUF service area file lists these plans as serving Sumter County (FIPS 12119), but the plans don't have rates for Rating Area 36 (Sumter's RA). CMS doesn't return them for Sumter either, so they're correctly filtered at query time. The mismatch is purely cosmetic — the PUF service area data is slightly over-inclusive.
Action: No fix required. Plans are filtered correctly by the existing regionId lookup logic.
Issue 2: Age curve anomalies (196 plans)
Affected by issuer prefix:
| Issuer ID | Count | Issuer (likely) |
|---|---|---|
68781 | 87 | Select Health (UT) |
22013 | 40 | Regence BlueCross (UT) |
42261 | 36 | University of Utah Health Plans (UT) |
34541 | 15 | BridgeSpan Health (UT) |
98113 | 10 | Mountain Health Co-Op (UT) |
18167 | 8 | Molina Healthcare (UT) |
Pattern: All UT issuers. Age curve ratio is ~1.39 instead of the standard 1.222.
Sample: Plan 18167UT0010001 Rating Area 5: age 21 = $601.23, age 35 = $835.71, ratio = 1.39
Root cause: Utah uses a state-specific age curve that differs from the federal default. This is allowed under 45 CFR 147.102(e). The PUF rates are correct as-filed.
Action: No fix required. Update Tier 4 thresholds to allow UT-specific age curves (1.35-1.45 acceptable).
Resolution status
- Functional integrity: 100% clean
- Cosmetic warnings: 220 items documented above, do not affect query results
Related fixes during this audit
- 9,358 zips backfilled with
regionIdfrom sibling zips (enrichment had left them empty) - 66 cross-state border zips removed from
zip_county(CA, CO, NV, etc. — not in our service area) - FL Sumter RA confirmed correct (RA36, 6/10 plan votes)
