Methodology, Reconciliation Pipeline & Official .gov Source Inventory

Cliff Ledger deterministically reconciles 17 primary federal and California regulatory feeds across FDA Orange Book patent/exclusivity files, Drugs@FDA approval tables, CMS Medicare Part D and Medicaid spending dashboards, California HCAI SB 17 WAC transparency disclosures, and openFDA enforcement recalls into 5,728 drug profiles and 600 consolidated biopharma sponsors.

Publication c7aa149ae887ad06ebb0 17 Official FDA, CMS & HCAI Feeds 26,275 Reconciled Records $399.94B Clean 2024 CMS Spend

1. Core Reconciliation & Deduplication Architecture

Public pharmaceutical datasets suffer from four severe join hazards if queried without deterministic normalization: CMS spending files include both Overall summary rows and individual manufacturer rows (which double-count spending to $799.89B if summed naively), Orange Book lists pediatric extensions as duplicate patent numbers carrying a *PED suffix, California HCAI SB 17 reports 11-digit package NDCs rather than FDA NDA/BLA application numbers, and biopharma M&A fragments subsidiary applicants across dozens of legacy shell names. Table 1 documents each reconciliation stage in publication c7aa149ae887ad06ebb0.

Reconciliation Stage Verified Volume Deterministic Normalization Rule
CMS Part D & Medicaid Deduplication (2020 to 2024) $399.94B Eliminated $399.94B in naive Overall/manufacturer double-counting
FDA Orange Book Patent & *PED Resolution 22,438 rows (2,337 *PED) Separated core drug-substance LOE from outer formulation/method-of-use and pediatric extension dates
CalHHS HCAI SB 17 11-Digit NDC Crosswalk 5,355 WAC / 2,161 launch Normalized 9/10/11-digit NDCs via openFDA NDC directory onto 1,345 parent FDA molecules
Biopharma M&A Sponsor Consolidation 600 sponsors (25 CA) Rolled subsidiary NDA/BLA holders (e.g., Celgene, Kite, Immunomedics, Pharmacyclics) onto ultimate corporate parents
openFDA Drug Recall Classification Join 18,110 recall rows Linked Class I, II, and III enforcement actions to parent molecules and manufacturing sponsors

2. Automated Quality Gate Verification

Quality Gate Status Observed Verification Detail
CMS Part D Overall vs. Manufacturer Reconciliation cms_partd_overall_vs_manufacturer_reconciliation PASS Actual: 0 (Expected: 0)
CMS Medicaid Overall vs. Manufacturer Reconciliation cms_medicaid_overall_vs_manufacturer_reconciliation PASS Actual: 0 (Expected: 0)
Zero CMS Overall Double Counting In Mart Entity zero_cms_overall_double_counting_in_mart_entity PASS Actual: 0 (Expected: 0)
Unique Entity Primary Keys unique_entity_primary_keys PASS Actual: 0 (Expected: 0)
Canonical FDA Application Keys canonical_fda_application_keys PASS Actual: 0 (Expected: 0)
Non Negative Drug And Trend Spending non_negative_drug_and_trend_spending PASS Actual: 0 (Expected: 0)
California Biopharma Portfolio Coverage california_biopharma_portfolio_coverage PASS Actual: 0 (Expected: 0)
Patent Cliffs And Sponsors Populated patent_cliffs_and_sponsors_populated PASS Actual: 0 (Expected: 0)
Non Negative SB 17 WAC Amounts non_negative_sb17_wac_amounts PASS Actual: 0 (Expected: 0)
California SB 17 Transparency Filings Reconciled california_sb17_transparency_filings_reconciled PASS Actual: 0 (Expected: 0)

3. Complete Official .gov & .ca.gov Source Inventory (17 Feeds)

Source ID & Agency Dataset Title Rows SHA-256 Prefix & Official Endpoint
fda_orange_book U.S. Food and Drug Administration (FDA CDER) Approved Drug Products with Therapeutic Equivalence Evaluations (Orange Book) 73,724 c2088c7a36db1930...
https://www.fda.gov/media/76860/download
fda_drugsatfda U.S. Food and Drug Administration (FDA CDER) Drugs@FDA: FDA-Approved Drugs Regulatory Approvals & Submissions 29,453 306d3de76a95a43e...
https://www.fda.gov/media/89850/download
cms_partd_spend Centers for Medicare & Medicaid Services (CMS) Medicare Part D Spending by Drug (2020-2024) 14,536 5dcc9d7bd7d7f88d...
https://data.cms.gov/sites/default/files/2026-06/98218f98-166c-4723-...
cms_medicaid_spend Centers for Medicare & Medicaid Services (CMS) Medicaid Spending by Drug (2020-2024) 18,511 29294c174ee7ad6c...
https://data.cms.gov/sites/default/files/2026-06/ae1c5e06-957d-42dd-...
openfda_drug_recalls U.S. Food and Drug Administration (openFDA) openFDA Pharmaceutical Enforcement Reports (Class I/II/III Drug Recalls) 18,110 76e8c96a1c665872...
https://download.open.fda.gov/drug/enforcement/drug-enforcement-0001...
hcai_sb17_wac_increases_current California Department of Health Care Access and Information (HCAI) SB 17 Prescription Drug Wholesale Acquisition Cost (WAC) Increases (Latest Monthly CSV) 828 d556ab0491a9bdeb...
https://data.chhs.ca.gov/dataset/0c693b50-6d23-46a0-a1ae-7c320fe23df...
hcai_sb17_wac_increases_2025 California Department of Health Care Access and Information (HCAI) SB 17 Prescription Drug Wholesale Acquisition Cost (WAC) Increases (Q1-Q4 2025) 1,344 29901a25067e651a...
https://data.chhs.ca.gov/dataset/0c693b50-6d23-46a0-a1ae-7c320fe23df...
hcai_sb17_wac_increases_2024 California Department of Health Care Access and Information (HCAI) SB 17 Prescription Drug Wholesale Acquisition Cost (WAC) Increases (Q1-Q4 2024) 1,632 e096fde24ff0e7e8...
https://data.chhs.ca.gov/dataset/0c693b50-6d23-46a0-a1ae-7c320fe23df...
hcai_sb17_wac_increases_2023 California Department of Health Care Access and Information (HCAI) SB 17 Prescription Drug Wholesale Acquisition Cost (WAC) Increases (Q1-Q4 2023) 1,553 069840ef39f2beaf...
https://data.chhs.ca.gov/dataset/0c693b50-6d23-46a0-a1ae-7c320fe23df...
hcai_sb17_wac_history_5yr California Department of Health Care Access and Information (HCAI) SB 17 Prescription Drug WAC Increases 5-Year History (Latest Monthly CSV) 4,503 3fbd8cb6f6f4de3a...
https://data.chhs.ca.gov/dataset/0c693b50-6d23-46a0-a1ae-7c320fe23df...
hcai_sb17_wac_history_2025 California Department of Health Care Access and Information (HCAI) SB 17 Prescription Drug WAC Increases 5-Year History (Q1-Q4 2025) 7,147 3f157858861f6467...
https://data.chhs.ca.gov/dataset/0c693b50-6d23-46a0-a1ae-7c320fe23df...
hcai_sb17_wac_history_2024 California Department of Health Care Access and Information (HCAI) SB 17 Prescription Drug WAC Increases 5-Year History (Q1-Q4 2024) 8,602 4bd6e415ad1e8ee1...
https://data.chhs.ca.gov/dataset/0c693b50-6d23-46a0-a1ae-7c320fe23df...
hcai_sb17_wac_history_2023 California Department of Health Care Access and Information (HCAI) SB 17 Prescription Drug WAC Increases 5-Year History (Q1-Q4 2023) 7,967 8d50b66ca55e0fa7...
https://data.chhs.ca.gov/dataset/0c693b50-6d23-46a0-a1ae-7c320fe23df...
hcai_sb17_new_drugs_current California Department of Health Care Access and Information (HCAI) SB 17 New Prescription Drugs Introduced to Market (Latest Monthly CSV) 511 9d220a594a5ac635...
https://data.chhs.ca.gov/dataset/e54d331c-65d3-4c6e-b4ba-390bd702424...
hcai_sb17_new_drugs_2025 California Department of Health Care Access and Information (HCAI) SB 17 New Prescription Drugs Introduced to Market (Q1-Q4 2025) 559 a044ef5df97582b4...
https://data.chhs.ca.gov/dataset/e54d331c-65d3-4c6e-b4ba-390bd702424...
hcai_sb17_new_drugs_2024 California Department of Health Care Access and Information (HCAI) SB 17 New Prescription Drugs Introduced to Market (Q1-Q4 2024) 465 b35c78651703773b...
https://data.chhs.ca.gov/dataset/e54d331c-65d3-4c6e-b4ba-390bd702424...
hcai_sb17_new_drugs_2023 California Department of Health Care Access and Information (HCAI) SB 17 New Prescription Drugs Introduced to Market (Q1-Q4 2023) 626 2c5b448e8533b159...
https://data.chhs.ca.gov/dataset/e54d331c-65d3-4c6e-b4ba-390bd702424...

4. Update Cadence, Known Limitations & Citation Format

Update Cadence: Cliff Ledger refreshes monthly for FDA Orange Book patent/exclusivity supplements and openFDA enforcement recalls, quarterly for CalHHS HCAI SB 17 WAC filings and new-drug disclosures, and annually upon CMS Medicare Part D and Medicaid Drug Spending public-use releases.

Known Limitations: CMS Part D and Medicaid figures reflect gross pre-rebate spending; HCAI SB 17 filings reflect undiscounted manufacturer Wholesale Acquisition Cost; and confidential Hatch-Waxman Paragraph IV settlement agreements or Purple Book BPCIA patent settlements may allow licensed generic or biosimilar entry earlier than the latest listed patent date.

5. Public-Data Architecture, Institutional Partnerships & Cross-Agency Reference Notes

Cliff Ledger shares deterministic entity-resolution and cryptographic provenance infrastructure with sibling Meru AI healthcare, regulatory, and financial ledgers: