Clinical Trials + PubMed — Usage Guide

TL;DR — Where to look first

The two new warehouse tables

clinical_trials

Pulled from ClinicalTrials.gov v2 API. Filtered to studies whose intervention type is DEVICE, DIAGNOSTIC_TEST, or COMBINATION_PRODUCT. Watermarked on lastUpdatePostDate with a 14-day lookback, so updates to a trial's status (e.g. RECRUITING → COMPLETED) are picked up on the next refresh, not just new trials.

pubmed_papers

Pulled from NCBI E-utilities (esearch + efetch). Two query strategies running in series each refresh:

• Sponsor-affiliation queries — for the top ~500 sponsors by combined 510(k) + PMA + trial activity, fetches all papers where any author affiliation contains the sponsor's normalized name ("medtronic"[Affiliation]).
• Curated keyword queries — 22 medtech keyword expressions (dental imaging + AI, wearable biosensor, point-of-care dx, surgical robotics, SaMD, neurostimulation, etc.). Tag attached so each paper records which keyword matched.

What each briefing now shows

5 strategic questions you can answer now

Query helpers (analysis layer)

Both query modules are pure-read: they take a DuckDB connection + intent, return list-of-dicts ready for any briefing template or notebook. They tolerate missing tables (return [] if you query before the first ingest finishes) — so you can wire them into a briefing draft without breaking the render.

analysis/trials.py

from fda_radar.analysis import trials as trial_q

# All trials sponsored by a sponsor cohort (normalized names)
trials = trial_q.trials_for_sponsors(con, ["medtronic", "boston scientific"], limit=50)

# Only active (RECRUITING / NOT_YET_RECRUITING / ACTIVE_NOT_RECRUITING / ENROLLING_BY_INVITATION)
active = trial_q.trials_for_sponsors(con, ["medtronic"], active_only=True)

# Trials with at least one location in one of several countries
eu = trial_q.trials_by_country(con, ["Germany", "France", "Italy"], limit=40)

# Trials with conditions matching ANY of several substrings
dental = trial_q.trials_for_conditions(con, ["dental", "caries", "oral health"], limit=30)

# Aggregate summary for a peer cohort — counts by status, top conditions, top phases
summary = trial_q.cohort_trial_summary(con, ["medtronic", "abbott medical", "boston scientific"])
# returns: {total, active, by_status, top_conditions, top_phases}

analysis/pubmed.py

from fda_radar.analysis import pubmed as pub_q

# Papers whose affiliation_sponsors column matches a known sponsor cohort
papers = pub_q.papers_for_sponsors(con, ["medtronic", "boston scientific"], limit=30)

# Papers whose keyword_matches OR title OR mesh_terms contains a substring (any)
dental_ai = pub_q.papers_for_keywords(con, ["intraoral camera", "dental AI"], limit=30)

# Papers whose author affiliations contain a country/city substring
in_papers = pub_q.papers_by_country(con, ["India", "Mumbai", "Bengaluru"], limit=25)

# Monthly publication count per sponsor cohort (last N months) — "scientific velocity" signal
vel = pub_q.sponsor_publication_velocity(con, ["medtronic"], months_back=24)

# Top journals overall or within a topical slice
top = pub_q.top_journals(con, keyword_substrings=["wearable", "biosensor"], limit=15)

cd "Chrysalis Intelligence" && export UV_PROJECT_ENVIRONMENT=$HOME/fda-radar-local/venv
uv run python
>>> import duckdb
>>> con = duckdb.connect('/Users/michaelsappington/fda-radar-local/data/fda.duckdb', read_only=True)
>>> from fda_radar.analysis import trials, pubmed
>>> trials.trials_for_sponsors(con, ["dexcom"], active_only=True)

Refreshing the data

Both ingesters are wired into the standard pipeline. The daily fda-radar refresh picks them up automatically — no separate command needed.

Ad-hoc single-endpoint refresh

# Trials only, narrow window
uv run fda-radar ingest --endpoint clinical_trials --start 2026-05-01 --end 2026-05-31

# PubMed only, narrow window
uv run fda-radar ingest --endpoint pubmed --start 2026-05-01 --end 2026-05-31

# Full incremental refresh (uses watermark - 14 day lookback)
uv run fda-radar ingest --endpoint clinical_trials
uv run fda-radar ingest --endpoint pubmed

First-time full 5y backfill

The next time you run fda-radar refresh after this change, both endpoints will do a 5y backfill (no watermark exists yet). Realistic times:

• clinical_trials — ~50-100k records, 30-60 min at default page size.
• pubmed — depends heavily on whether NCBI_API_KEY is set: 2-4 hours with the key, 8-12 hours without. Sponsor-affiliation fan-out (500 esearch + N efetch per sponsor) dominates the runtime.

The launchd daily refresh at 6:15 AM may extend by these amounts the first day. Subsequent runs are 14-day incrementals — back to ~10 min for trials, ~30 min for PubMed.

Coverage gaps + what NOT to conclude

The sponsor-name join is fuzzy

Both ingesters normalize sponsor names with the same normalize_applicant() the FDA tables use (lowercase, strip Inc/LLC/Ltd/Corp/GmbH/etc., collapse whitespace). That works for ~70-80% of obvious cases. It will miss:

• Subsidiary mismatches ("Medtronic plc" vs "Medtronic Vascular" vs "Medtronic Ireland")
• Recent corporate renames (the trial was registered under old name, the FDA clearance was filed under new name)
• Trials run via academic medical centers on behalf of a sponsor (no corporate name appears in lead_sponsor)
• Papers whose authors list institutional rather than corporate affiliation

When a firm appears empty in the trials/pubmed section but you know it's active, the name-match probably missed. Try the live ClinicalTrials.gov or PubMed UI to confirm.

Keyword scope is curated, not exhaustive

• etl/ingest_pubmed.py:MEDTECH_KEYWORDS for PubMed search expansion
• The relevant briefing's condition list (e.g. WHITESPACE_CONDITIONS in research/whitespace.py)

The next refresh picks the new keywords up; existing data isn't re-tagged automatically.

Future-dated PubMed records are real

PubMed's pub_date reflects the print issue date, which can be months in the future for accepted-but-not-yet-printed papers. You'll see records with pub_date in 2026-12 or even 2027. They're not corrupt — they're "in press". Treat them as current.

What this data does NOT tell you

• Trial outcomes — CT.gov records the protocol + status, not the results. Result reporting is incomplete (legally required but inconsistently enforced).
• Funding source for academic publications — disclosure varies; the affiliation match shows where authors work, not who paid.
• Pre-print activity — bioRxiv / medRxiv aren't indexed by PubMed for most papers until peer review completes. Useful signals there are missed.
• FDA review process detail — for that you still need to read the 510(k) Summary PDFs (the predicate_citations table).

Future extensions worth scoping