CompoundIQ

503B Outsourcing Facility Registration & Regulatory Action Tracking

Back to Case Studies

503B facility lifecycle tracking from a single FDA spreadsheet, modeled into history you can monitor, query, and trust.

Overview

CompoundIQ is a regulatory intelligence platform that transforms the FDA's weekly 503B outsourcing facility registration spreadsheet into a longitudinal data model. While the FDA publishes only a static, point-in-time Excel file, CompoundIQ captures each release, preserves history, and models facilities, parent pharmacies, inspections, regulatory actions, bulk API intent, recalls, and registration status over time.

The result is a system that makes change visible where none existed before — enabling monitoring, trend analysis, and defensible decision-making in a highly regulated pharmacy market.

Context / Risk

FDA 503B outsourcing facilities play a critical role in the sterile drug supply chain, especially during shortages. Health systems, clinics, telehealth organizations, and GPOs rely on these facilities, yet the regulatory signals that indicate supplier risk are fragmented and difficult to interpret.

The FDA's 503B registration list represents the authoritative public record, but it is not designed for operational or strategic use. Each weekly update overwrites the past, eliminating the ability to answer basic questions about how the supplier landscape evolves over time.

The Problem

The core challenge was not visualization — it was the absence of time and identity in the source data.

  • Data is released as a raw Excel spreadsheet with inconsistent formatting.
  • Weekly updates overwrite prior states, eliminating historical context.
  • Facilities and parent pharmacies lack stable identifiers.
  • Names and attributes shift subtly across releases.
  • Inspections, post-inspection actions, and recalls are not modeled as time-aware events.

Without re-engineering the dataset, longitudinal monitoring and reliable analytics are not possible.

Why Existing Approaches Failed

In practice, teams attempt to manage this data through manual spreadsheet comparisons or snapshot-based BI tools. These approaches fail quickly:

  • Manual comparisons do not scale and are not auditable.
  • Snapshot dashboards show "today" but cannot explain "what changed."
  • Without stable entity resolution, facility history becomes unreliable.

CompoundIQ was designed to solve what was missing: time, identity, and structure.

System Design

CompoundIQ treats each FDA release as a point-in-time observation rather than a replacement. Weekly spreadsheets are automatically collected, normalized, and preserved as history using Slowly Changing Dimensions (Type 2).

What the System Models

  • Facilities and parent pharmacies
  • Registration status over time (added, removed, re-registered)
  • Inspection events and inspection cadence
  • Post-inspection actions (Untitled Letters, FMD-145 notices, Warning Letters)
  • Bulk API compounding intent
  • FDA recall associations
  • High-level KPIs summarizing activity and change

Entity Resolution

The FDA source data contains significant text inconsistencies. CompoundIQ applies aggressive normalization and deterministic ID generation to ensure that the same real-world facility remains identifiable across time.

  • Standardization of casing, punctuation, spacing, and corporate suffixes
  • Stable facility and parent IDs generated via deterministic hashing
  • Explicit handling of edge cases such as "AMERICA LLC" vs "AMERICALLC"

Data Quality & Observability

  • 37 automated dbt tests validating schema integrity and relationships
  • Freshness monitoring to ensure weekly captures are complete
  • Administrative interface for pipeline monitoring and debugging

Product Views

The following views illustrate how CompoundIQ surfaces change, risk signals, and operational visibility from the FDA's 503B data.

CompoundIQ landing page with KPI cards and week-over-week changes

High-level KPI cards summarizing active facilities, recent registration changes, inspections, and post-inspection actions across the 503B landscape, with week-over-week change indicators.

Current 503B facility registration table

Interactive, cleaned view of the current 503B registration list with filtering, sorting, and export for downstream analysis and reporting.

Filtered 503B facility registration table

Filtered view of the registration list, demonstrating the ability to narrow results by registration status, inspection history, or other attributes.

Decisions Enabled

  • Identify facilities entering or exiting the 503B landscape
  • Track inspection cadence and outcomes over time
  • Monitor post-inspection actions accumulation
  • Understand shifts in bulk API compounding intent
  • Surface recall-associated facilities
  • Support sourcing and risk discussions with historical evidence

Who This Was For

Pharmacy procurement leaders, supply chain analytics teams, and GPO stakeholders who depend on 503B outsourcing facilities and need a reliable view of how the supplier landscape changes over time.

Why This Matters

The FDA spreadsheet is public, but history is not. CompoundIQ creates the missing layer by transforming a static disclosure into a durable, time-aware model of reality. It demonstrates how messy, stateless regulatory data can be re-engineered into structured facts and dimensions that support real decisions in a complex healthcare market.