cGxP Directory · Data Platform
How a record gets into the directory
Two real companies, ten real feeds, and every table the data touches on its way from an HTTP response to a profile page. Press Generate data and the pipeline narrates itself — including the two records it refuses to trust.
How to read the values on this page
Live means the value was pulled from the public API during preparation of this page and can be re-fetched with the curl shown beside it.
Curated means a hand-entered demo value standing in for what a curator or a commercial feed would supply. Nothing on this page is invented and presented as regulatory fact.
The sample
Two companies, chosen to break in different places
One small-molecule drug and one biologic. They exercise opposite halves of the schema: an NDA routes through Drugs@FDA and the Orange Book with patents and exclusivity; a BLA routes through the Purple Book and never appears in Drugs@FDA at all. Their names also fail to match in three different ways, which is the more interesting half of the demo.
Pfizer Inc.
companies.company_slug = pfizer-inc
- Product
- XELJANZ (tofacitinib) Live
- Route
- NDA → Orange Book
- Application
- NDA203214 Live
- NDC
- 0069-1002 Live
- UNII
- O1FF4DIV0D Live
- Labeler
- 0069
- Trial
- NCT02092467 Live
- Paper
- PMID 35081280 Live
- Exchange
- NYSE : PFE Curated
Moderna, Inc.
companies.company_slug = moderna-inc
- Product
- mRESVIA (mRNA-1345) Live
- Route
- BLA → Purple Book
- Application
- BLA125796 Live
- NDC
- 80777-345 Live
- Substance
- RNA-100-AR02 Live
ingredient name — the feed carries no UNII - Labeler
- 80777
- Trial
- NCT05127434 Live
- Paper
- PMID 38091530 Live
- Exchange
- NASDAQ : MRNA Curated
Neither company's name arrives from a feed the way it is stored. openFDA calls Pfizer Pfizer Laboratories Div Pfizer Inc; Drugs@FDA lists the NDA203214 sponsor as PF PRISM CV; ClinicalTrials.gov says just Pfizer; the shortage feed says Pfizer Inc.. Moderna arrives as Moderna US, INC. and ModernaTX, Inc.. Five spellings, one company — and one of them scores so low that no fuzzy matcher will ever find it.
Step one
Where the data comes from
Every feed is a row in data_sources. The row is not a label — trust_rank is the number the survivorship function sorts on when two sources disagree, and is_licensed is what keeps MedDRA terms out of a public export.
| Source | Endpoint | Tier | Trust | Access | Cadence | Feeds tables |
|---|
Trust rank is deliberately an integer rather than an ordering derived from tier: two feeds in the same tier still need a tiebreak, and Drugs@FDA must beat an EMA scrape.
The demo
Run the pipeline
Three presses, in order. The first lands the data and holds what it cannot verify. The second is a human clearing the hold. The third is the next morning's scheduled run, which is where the design earns its keep.
Rows written, by layer
Tables written by the run will appear here, newest first.
In run 1: one reject (a malformed record refused rather than coerced to NULL), four rows held at Review with their foreign key left NULL, and one row that matches exactly and auto-confirms. In run 3: content_hash short-circuits four of the five records, so the run writes almost nothing — and the alias a human taught it in step 2 now matches at 1.00 without asking again.
Reference
The seven layers, and what each one refuses to do
| Layer | Tables | Written by | The rule it enforces |
|---|---|---|---|
| CTL Control plane | data_sources, ingest_runs, source_watermarks, ingest_rejects | Every loader | Which run wrote this row, where the feed got to, and what it refused. A bad run is revertible because every staging row carries its ingest_run_id. |
| L0 Landing | raw_source_payload | Loader, before anything is derived | Append-only. Never updated, never read by the API. A mapping bug becomes a re-transform instead of a re-download — which matters because shortages and CT.gov publish current state only. |
| L1 Staging | ndc_products, fda_applications, clinical_trials, drug_labels, drug_shortages, adverse_event_reports, publications, orange_book_products, purple_book_products | *-mapper.ts | Typed, keyed on the source's own record id. Uniqueness is per source, not global, so two feeds publishing the same NDC do not overwrite each other. |
| L2 Resolution | entity_xrefs, company_aliases | *-matcher.service.ts | A staging foreign key is written only from an xref at Confirmed. Anything less confident leaves the FK NULL. An unattributed record is recoverable; a wrongly attributed one quietly poisons every derived trend. |
| CORE Canonical | companies, products, people, facilities, deals, and their satellites | Curators & the write-back job | One row per real-world thing. This is what the API serves and what a slug points at. |
| L3 Provenance | fact_claims, field_overrides | Loaders & the governance workflow | Source URL, as-of date and confidence on every contested fact. Losing claims are never deleted — "three sources disagree with this value" is a feature of the profile. |
| REF Vocabulary | taxonomy_terms, taxonomy_codes, taxonomy_crosswalks, ingredients, routes | Reference loaders | One vocabulary, many code systems. MeSH, ICD-10, SNOMED CT, ATC and MedDRA all hang off the same term, so "trials for this product's indication" is a join. |
Survivorship: which value the profile page actually shows
Defined once, in SQL, by resolve_entity() — not in application code, because the write-back job needs the same ordering without going through Node.
field_overrides— a human decided. Nothing outranks it, permanently.data_sources.trust_rankdescending — regulatory authority over secondary source.fact_claims.observed_atdescending — the source's own as-of date, not when we fetched it.fact_claims.confidencedescending — primary over inferred over crowdsourced.
Reference
Industrial identifier for every table
All 95 tables. For each: the primary key the database uses, the natural key that makes a re-ingest an upsert rather than an append, and the identifier the outside industry would use to name the same thing. Where there is no external identifier the cell says so — a blank cell invites someone to invent one.
Reading the Example column. Values in accent colour were fetched from the live API while this page was written and can be looked up as printed. Values in grey with a dotted underline are format illustrations showing the shape of the identifier — they were not fetched and should not be quoted as records. Where a value is part-verified the note says which half.
| Table | Layer | Primary key | Natural key in this DB | Industrial identifier | Issuing authority | Example |
|---|
ndc_products.product_ndc, fda_applications.application_number, clinical_trials.nct_id and drug_labels.(spl_set_id, spl_version) were all demoted from @unique. The per-source partial index on (data_source_id, source_record_id) says one row per source per record; a global unique on the business key says one row full stop. With twenty-plus feeds those two statements contradict each other the moment a second source publishes the same key, and the loser is an ON CONFLICT that silently overwrites another source's row. Uniqueness of the real-world thing lives on the core entity and is reached through entity_xrefs. This is a design decision, not a missing constraint.
LEI, DUNS and SEC CIK for companies; ORCID for people; FDA recall, Warning Letter and Form 483 reference numbers for regulatory_events; GS1 GLN for company_locations. Each can be carried today as an entity_xrefs row keyed by source system, which is the intended route — but none of them has a first-class column, and no constraint currently enforces their formats. Flagged here rather than left blank.
Honesty
The three empty states
Some tables stay empty for these two companies, and the reason differs each time. One NULL cannot encode three different reasons, so field_coverage carries the distinction at section grain. A clean compliance panel is only a finding if the reader knows the platform actually looked.
We looked; the source published nothing. A genuine finding. Example on this run: company_awards — no awards feed returned a row for either company.
Nobody has looked yet. Absence of evidence, not evidence of absence. Example: facilities — the FDA establishment registration feed is not wired, so both companies show zero sites and no FEI numbers.
Structurally irrelevant to this company's vertical. Example: vendors and vendor_services — both companies are manufacturers, not service vendors, so the Vendors panel is not rendered at all.