Building a go-to-market brain on the FDA's own data

Public FDA data is a goldmine if you can stand to clean it. This is the story of turning establishment registries, inspection records, and warning letters into a single queryable picture of who needs help and why.

Some context first. The client is a consultancy that sells manufacturing and quality systems to small FDA-regulated manufacturers—the kind of company running batch records on spreadsheets and hoping the next inspection goes well. Its founder brings thirty-five years in regulated life sciences. The go-to-market problem was the classic one for a small firm selling into a regulated niche: researching a single prospect—who they are, what they make, what the FDA has said about them lately—took three to four hours of manual digging across government websites. At that rate you don't have a pipeline. You have a hobby.

But here's the thing about selling compliance help: the regulator publishes your lead list. Every warning letter is a company being told, in public and in writing, that its quality system is failing. Every inspection outcome is a temperature reading. Every establishment registration says who exists, where they operate, and what they do there. The demand signal is sitting on fda.gov, updated monthly, free.

The only problem is the shape it arrives in.

Four Sources, Four Personalities

There is no single FDA API. There's a family of overlapping publications, each with its own format, auth scheme, and temperament, and you need all of them.

The registry download. The Drug Registration and Listing System publishes a bulk file—drls_reg.txt, a tab-separated dump of every registered drug establishment. Ours had 10,363 rows. This is the spine of the whole system: firm names, addresses, the operations each facility performs (MANUFACTURE, API MANUFACTURE, ANALYSIS, and so on), and the corporate parent each facility belongs to. It refreshes monthly. You don't query it; you load the whole thing and treat it as the census.

openFDA. api.fda.gov is the modern, friendly one—documented JSON endpoints, and a free API key that gets you 240 requests a minute with a 40,000-per-day ceiling. It covers enforcement actions: recalls, with their classifications (Class I is "this can kill you," Class III is "the label is smudged").

The Data Dashboard API. api-datadashboard.fda.gov serves inspections, citations, and warning-letter metadata. It's clearly the backend of an internal BI tool that happens to be exposed publicly, and it behaves like one: authentication happens through custom Authorization-User and Authorization-Key headers, and the rate limits are undocumented. You throttle yourself and hope.

The HTML you have to scrape anyway. Here's the punchline: the actual text of a warning letter—the paragraphs where the FDA describes, in specific regulatory language, exactly what a company got wrong—exists only as a web page. The APIs give you a date, a subject line, and a URL. So the pipeline ends the way these pipelines always end, with requests and BeautifulSoup: fetch the page, look for #main-content or fall back to the article tag, strip out the text. One trap worth documenting: the FDA's bot detection doesn't return a 403. It 200-redirects you to an apology page. You have to check whether abuse-detection-apology appears in the final URL, because the status code will happily tell you everything went fine.

Why bother with the full text? Because it's the single most valuable field in the entire system. Downstream, outreach emails quote the specific citations a company received. "I saw you had an inspection" is spam; naming the exact clause of 21 CFR they were cited under is a colleague who did the reading.

What makes the four sources joinable at all is a pair of identifiers: the FEI number, which identifies a facility, and the DUNS number, which identifies a corporate parent. Every source speaks at least one of the two. Those became the primary keys, and everything else became an exercise in hanging data off them.

Normalizing into SQLite

The database is SQLite, and I want to defend that choice, because the instinct is always to reach for Postgres. One analyst, one machine, ten thousand establishments: a database that is a file beats a database that is a service on every axis that matters here. No server to run, trivially backed up, and queryable from anything. The performance question doesn't survive contact with the numbers—10K establishments and 50K contacts is nothing.

The schema decision that made everything else easy was mirroring the FDA's own structure instead of flattening it:

registrants (DUNS)  ←→  establishments (FEI)

Parent companies and facilities are different tables with different keys, linked one-to-many. This matters because the FDA acts at the facility level—a warning letter goes to a plant—but sales happen at the corporate level. Keep the two levels distinct and both queries are natural: "show me this facility's history" and "roll up compliance trouble across everything Pfizer owns."

The 10,363 registry rows normalized into 6,701 parent companies and 9,833 establishments. The registry also carries official contacts, which meant the system started life with 8,215 people in it before any enrichment—pre-loaded from the FDA's own filings.

Each type of compliance action got its own table—warning_letters, inspections, citations, enforcement_actions—all keyed on FEI, rather than one generic "events" table. Different action types have genuinely different fields (an inspection has a classification code; a recall has a product and a distribution pattern), and separate tables match the FDA's structure instead of producing a colander of nulls. Inspections keep their FDA classification codes: NAI (no action indicated), VAI (voluntary action), OAI (official action—the bad one). People connect to both facilities and parents through junction tables carrying a role, because the same quality director shows up at three sites, and knowing that is exactly the kind of thing a good sales conversation is made of.

All told: 21 tables and 5 views. The views encode the recurring questions—compliance overview per facility, contacts per establishment—so the everyday queries stay one line long. And the payoff for all this normalization is that questions which used to be an afternoon of manual research became joins:

SELECT p.*, e.firm_name, i.classification_code
FROM people p
JOIN people_establishments pe ON p.id = pe.person_id
JOIN establishments e ON pe.fei_number = e.fei_number
JOIN inspections i ON e.fei_number = i.fei_number
WHERE i.classification_code = 'OAI'
AND pe.role LIKE '%quality%';

"Every quality contact at every facility that just failed an inspection." That query is the business.

One more practical note on filling the tables. Syncing compliance history for all 10,363 facilities means four API calls each—over 41,000 requests, most of them wasted on facilities that would never be prospects. So the sync is targeted: filter to facilities whose operations are relevant to the product (here, operations subject to Part 11 electronic-records requirements—manufacturing, API production, laboratory analysis, sterilization), plus major pharma and US companies with multiple qualifying sites. That cut the target list to 2,137 facilities—a 76% reduction—which at four calls per facility and a respectful 1.5-second delay is about 8,500 requests and 3.6 hours. An overnight job, then a weekly top-up on Sunday at 2 AM, with every call logged to a sync_log table so failures can be re-run instead of re-discovered.

Scoring: Nine Signals, and Resisting the Urge to Over-Model

With the data in one place, the next question is ordering: of six thousand companies, who do you call first?

There are really two questions hiding in there. Who is in trouble? comes straight from the data, and the FDA has already done the modeling for you—an OAI inspection, a warning letter, or a Class I recall is high risk; VAI classifications and Class II recalls are medium; NAI is background noise. I did not invent a risk score. The agency's own classifications, counted and sorted by recency, are the risk score.

Who is likely to buy? is the more interesting question, and the place where the temptation to over-model is strongest. The fix was embarrassingly simple: nine signals, each worth a fixed number of points, added up.

Signal	Points
New VP Ops / Plant Manager	10
PE acquisition in the last 12 months	10
No ERP detected	8
Hiring for ops/quality roles	7
Already using the platform the product is built on	7
Facility expansion	6
20%+ employee growth	5
Inc. 5000 / growth-list appearance	4
Running ops on Excel/SharePoint	4

The sum lands each company in a tier: HOT at 15 points or more, WARM at 10, MONITOR at 5, COLD below that. Reaching HOT takes roughly two strong signals or three weaker ones, which matches the intuition it encodes: one signal is a coincidence, two is a company in motion.

Everything about this design is a refusal. No learned weights, no embeddings, no model. Partly that's honesty about the data—the client had a few dozen researched prospects and a single-digit number of closed deals; there is nothing to train on, and pretending otherwise produces confident noise. But mostly it's that the point values are the domain expertise. They came out of arguing with a founder who has watched thirty-five years of these deals about which signals had ever actually preceded one. A new VP of Operations is worth 10 points because a new VP of Ops is someone who was hired to change things and has about eighteen months to show results. That reasoning survives in the score.

And the legibility pays for itself twice. When a rep sees a HOT rating, the signals that produced it are right there—and they're the opening line of the outreach, because "congratulations on the expansion" is a better email than anything a gradient descent has to say. When the scoring is wrong, the fix is editing a constant, not retraining a model. For a system whose entire job is to rank a small firm's call list, auditable-and-adjustable beats optimal-and-opaque, and it isn't close.

From CLI to Service Layer Without a Rewrite

The whole thing started as a Typer CLI—list, search, profile <id>, write-emails <id>—because a CLI is the fastest way to find out whether a tool is useful. It was. And then came the familiar pull: a web UI would help, other people might use this, maybe it's a product. The traditional next move is The Rewrite, where the working tool is abandoned in favor of a webapp skeleton that takes three months to reach feature parity.

The actual move was smaller: notice that the CLI commands had accreted all the business logic, and evict it.

# Before: logic lives in the command
@app.command()
def profile(company_id: str):
    # fetch, join, format, print — all here

# After: logic lives in a service; the command is a shell
class CompanyService:
    def get_company_profile(self, company_id, include_compliance=True): ...
    def search_companies(self, query, limit=50): ...

@app.command()
def profile(company_id: str):
    service = CompanyService()
    typer.echo(render(service.get_company_profile(company_id)))

The service classes are pure: no Typer imports, no printing, just data in and data out. The service constructor takes a data-source parameter, which turned out to matter—during the transition the system ran in dual mode, reading from the legacy QuickBase CRM and the new SQLite database side by side, behind the same interface.

Once the logic lives in services, the "rewrite" to a web platform stops being a rewrite. A FastAPI endpoint is a different fifteen-line shell around the same method the CLI calls—GET /api/v1/companies/{id} wraps get_company_profile exactly the way the profile command does. The CLI kept working the entire time, which meant the tool never stopped earning its keep while the API grew next to it. Auth, multi-tenancy, a React frontend: all additive layers, none of them touching the logic that does the actual work. The unit tests target the services and don't care who's calling.

None of this is novel architecture—it's the hexagonal/clean-architecture insight at consulting-project scale. What I'd underline is the sequencing: extracting services from a working CLI, with its behavior as the spec, took days. Building "the platform" first and hoping it converged on usefulness would have taken the quarter.

What the Brain Actually Is

No single piece of this is clever. A TSV loader, a couple of API clients, a scraper with one weird redirect check, a normalized schema, an additive scoring function, a service layer. The value is entirely in the join: the registry says who exists and what they make, the inspection history says how much pressure they're under, the warning-letter text says—in the FDA's own words—precisely what's wrong, the people table says who to talk to, and the score says in what order. A research task that consumed a rep's afternoon became a SELECT, and the outreach it feeds can quote chapter and verse because the pipeline kept the prose, not just the metadata.

If you sell into a regulated market, some agency is publishing this about your prospects right now—inspection outcomes, enforcement actions, registrations, all of it public, most of it structured, none of it joined. The moat isn't access. It's the willingness to clean it, key it, and encode what your most experienced person knows into something as unfashionable as a points table.

The goldmine was never hidden. It's just messy.