spb/cancerindex

1# Connector: `clinicaltrials` — ClinicalTrials.gov API v223Tier 0 · category `trials` · code `packages/connectors/src/connectors/clinicaltrials/`4Documentation verified **2026-09-08** (API version 2.0.5, `dataTimestamp` 2026-09-04T09:00:06).56## Source78| | |9|---|---|10| Organization | U.S. National Library of Medicine (NIH) |11| Base URL | `https://clinicaltrials.gov/api/v2` |12| OpenAPI | `https://clinicaltrials.gov/api/oas/v2` (YAML, OpenAPI 3.0.3, info.version 2.0.5) |13| Auth | none |14| Update cadence | ClinicalTrials.gov is updated daily; connector schedule `30 2 * * *` (incremental) |1516### Verified endpoints and parameters1718Read from the OpenAPI document and exercised with `curl` on 2026-09-08:1920- `GET /studies` — `query.cond`, `query.term` (Essie syntax), `filter.overallStatus`, `filter.advanced`21  (Essie), `fields` (comma/pipe list of area/piece/field names; branch pieces expand to descendants),22  `pageSize` ("coerced down to 1,000, if greater than that"; default 10), `pageToken` (opaque; "Do not23  specify it for first page"), `countTotal` (returns `totalCount` with the first page only; "ignored for24  the subsequent pages"), `sort` ("only date and numeric fields are allowed for sorting"; `Field:asc|desc`;25  studies missing the sort field are last). Response: `{ totalCount?, studies[], nextPageToken? }`.26- `GET /studies/{nctId}` — single study (not used by the sync; kept for TRACE/repair).27- `GET /studies/enums` — enum values used by the validator (verified live):28  Phase `NA | EARLY_PHASE1 | PHASE1 | PHASE2 | PHASE3 | PHASE4`; Status `ACTIVE_NOT_RECRUITING, COMPLETED,29  ENROLLING_BY_INVITATION, NOT_YET_RECRUITING, RECRUITING, SUSPENDED, TERMINATED, WITHDRAWN, AVAILABLE,30  NO_LONGER_AVAILABLE, TEMPORARILY_NOT_AVAILABLE, APPROVED_FOR_MARKETING, WITHHELD, UNKNOWN`; StudyType31  `EXPANDED_ACCESS | INTERVENTIONAL | OBSERVATIONAL`; InterventionType `BEHAVIORAL, BIOLOGICAL,32  COMBINATION_PRODUCT, DEVICE, DIAGNOSTIC_TEST, DIETARY_SUPPLEMENT, DRUG, GENETIC, PROCEDURE, RADIATION,33  OTHER`; AgencyClass `NIH, FED, OTHER_GOV, INDIV, INDUSTRY, NETWORK, AMBIG, OTHER, UNKNOWN`.34- `GET /version` → `{ apiVersion: "2.0.5", dataTimestamp: "2026-09-04T09:00:06" }` — used by35  `healthCheck` and stored as `ingest_runs.dataset_version` (the "date the data were processed by36  ClinicalTrials.gov" that the terms ask us to display).3738Rate limits: none published; no `x-ratelimit-*` or `Retry-After` headers were observed (the connector39logs them if they ever appear). Self-imposed: **3 req/s, concurrency 1, pageSize 1000**. A 1,000-study40page with the field list below is ≈ 5.8 MB and answered in ≈ 0.9 s.4142### Terms and Conditions (read 2026-09-08, https://clinicaltrials.gov/about-site/terms-conditions)4344Verbatim excerpts:4546> "ClinicalTrials.gov data are available to all requesters, both within and outside the United States, at no charge."4748> "In any publication or distribution of these data, you should: Attribute the source of the data as ClinicalTrials.gov; Update the data such that they are current at all times; Clearly display the date the data were processed by ClinicalTrials.gov; State any modifications made to the content of the data, along with a complete description of the modifications."4950> "You shall not assert any proprietary rights to any portion of the database, or represent the database or any part thereof to anyone as other than a United States Government database. You shall not use any email addresses extracted from our database for marketing or other promotional purposes."5152> "The ClinicalTrials.gov data carry an international copyright outside the United States and its Territories or Possessions. Some ClinicalTrials.gov data may be subject to the copyright of third parties; you should consult these entities for any additional terms of use."5354Manifest decision: `licenseStatus: approved`, `commercialUse: allowed`, `redistribution: attribution`.55Obligations implemented: attribution string in the manifest; `dataTimestamp` recorded per run; the56modifications (field normalization, condition→cancer reconciliation) are described here; contacts57(e-mail addresses) are not requested at all (`contactsLocationsModule.locations` only).5859## Oncology scope6061`filter.advanced` expression (stored verbatim in the cursor as `expression`; changing it resets the cursor):6263```64AREA[ConditionSearch] (cancer OR neoplasm OR carcinoma OR tumor OR tumour OR lymphoma OR leukemia OR leukaemia OR sarcoma OR melanoma OR myeloma OR glioma OR glioblastoma OR mesothelioma OR blastoma OR malignan*)65```6667Live counts on 2026-09-08 (data 2026-09-04):6869| formulation | totalCount |70|---|---|71| expression above | **126,107** |72| `query.cond=cancer` (= `query.cond=neoplasms`; CT.gov expands synonyms) | 123,069 |73| expression + `metastatic OR metastases OR adenocarcinoma OR oncology OR malignancy…` | 130,667 (adds non-oncology "metastatic" noise) |74| `AREA[ConditionBrowseBranchAbbrev] BC04` | 0 — the MeSH branch is not a searchable area |7576`ConditionSearch` covers the free-text conditions, keywords and the derived MeSH condition terms and77ancestors, so studies whose only condition is e.g. "Glioblastoma" or "NSCLC" are included through the78MeSH ancestor "Neoplasms". Every ingested study is flagged `is_oncology = true`; conditions that are not79cancers (e.g. "HIV Infections" on a Kaposi sarcoma trial) simply stay `UNRESOLVED` in `trial_conditions`.8081## Fields requested8283```84protocolSection.identificationModule, protocolSection.statusModule, protocolSection.sponsorCollaboratorsModule,85protocolSection.descriptionModule.briefSummary, protocolSection.conditionsModule, protocolSection.designModule,86protocolSection.armsInterventionsModule, protocolSection.outcomesModule, protocolSection.eligibilityModule,87protocolSection.contactsLocationsModule.locations, protocolSection.referencesModule, hasResults,88derivedSection.conditionBrowseModule, derivedSection.interventionBrowseModule89```9091The trimmed payload (exactly these pieces) is what `ctx.upsertSourceRecord('study', nctId, payload)`92hashes and writes to the raw lake (`data/raw/clinicaltrials/<date>/study/`).9394## Crawl strategy (incremental, restartable)9596- `sort=LastUpdatePostDate:asc`; incremental runs add `AND AREA[LastUpdatePostDate]RANGE[<lastUpdatePostDate>,MAX]`.97- Cursor (`connector_cursors.cursor`, persisted **after every page**, also on failure):98  `{ expression, rangeStart, pageToken, lastUpdatePostDate, fullTotalCount, crawlTotalCount, pagesDone, completedAt, dataTimestamp }`.99  A run interrupted by the time budget (`--max-minutes`, `ctx.shouldStop()` between pages) or by100  `--max-records` resumes at `pageToken`; a rejected/expired token (HTTP 400) restarts from101  `RANGE[lastUpdatePostDate,MAX]` (idempotent re-processing, same day re-read).102- Anomaly guard: on a full crawl, if `totalCount` < 50 % of the previous complete full crawl103  (`fullTotalCount`, also recovered from `ingest_runs.cursor_after`) the run aborts before writing.104  Nothing is ever mass-deleted on a shrunken response; `source_records` only gain `last_seen_run`.105- Idempotency: when the trimmed payload hash is unchanged and the trial exists, the study is skipped106  (`unchanged` counter). Otherwise the `clinical_trials` row is upserted on `nct_id` and the child107  tables are replaced for that trial (delete + insert): `trial_locations` (≤ 500 rows, lat/lng from108  `geoPoint`), `trial_interventions` (one row per distinct normalized name, `drug_id` null,109  `match_type = UNRESOLVED` — drug reconciliation is Phase 2), `trial_conditions`.110- Validation: `validateTrial()` (NCT id format, phase and status enums) + title presence; rejected111  records increment `rejected`/`validationFailures` and are logged, never thrown.112- `mode=dry_run` fetches one page (or `--max-records`), normalizes, validates and reconciles in memory,113  writes nothing.114- `mode=backfill` (`pnpm cix run clinicaltrials --mode backfill --reset-cursor`) re-crawls everything and115  re-processes studies whose payload is unchanged (child tables rewritten) — the repair path after a116  reconciliation-rule change; ontology growth alone is covered by the end-of-run re-reconciliation.117- Change events: on incremental crawls each new trial gets a `change_events` row (`trial`, `created`);118  the initial bulk crawl skips them (volume).119120Smoke ladder on `cancerindex_b` (OncoTree-only ontology, 865 cancers), 2026-09-08:121122| run | studies | wall time |123|---|---|---|124| `--mode dry_run --max-records 5` | 5 | 0.9 s |125| `--max-records 10` | 10 | 1.7 s |126| `--max-records 1000` | 1,000 | 5.2 s |127| `--max-records 1000` (resume by token) | 1,000 | 5.2 s |128| full crawl `--max-minutes 30` | see run report at the end of this file | |129130## Condition reconciliation (`trial_conditions`)131132Implemented in `reconcile.ts`, using `CancerResolver` from `@cancerindex/ontology` only (no OncoTree133or NCIt specifics; the alias set grows as terminology connectors run):1341351. **Label candidates** per condition string, most specific first: the string itself; `baseDiseaseLabel()`136   (stage/recurrence/resectability qualifiers stripped); registry noise removed ("Patients With",137   "Histologically Confirmed", "Adult", …); comma forms uninverted ("Lymphoma, Non-Hodgkin" → "Non-Hodgkin138   Lymphoma") and truncated ("Breast Cancer, Metastatic" → "Breast Cancer"); parenthetical acronyms split139   ("Non-Small Cell Lung Cancer (NSCLC)"). Each candidate goes through `resolver.byLabel(c, { allowMeshInversion: true })`140   which itself handles "Cancer of the X" reorderings. A hit on a de-qualified candidate keeps the141   resolver's `match_type` with confidence capped at 0.85.1422. **Study-level MeSH fallback** (`derivedSection.conditionBrowseModule.meshes`): `resolver.byCode('mesh', id)`143   first (populated once a terminology connector maps MeSH UIs), then `byLabel(term, { allowMeshInversion: true })`.144   Because MeSH terms are attached to the study, not to one condition string, the inference is guarded145   (each guard was motivated by a wrong mapping observed in the first 1,000-study smoke run):146   the condition must look oncologic (`ONCOLOGY_LABEL_RE`; "Pain", "HIV Infections" never inherit) and not147   be a bare generic word ("Cancer", "Neoplasms" — those resolve by label to the generic NCIt entity or stay148   unresolved); every MeSH term of the study must resolve (or there is exactly one); exactly one MeSH cancer149   must remain after removing cancers already assigned to label-resolved conditions ("Breast Cancer" +150   "Lung Cancer" with MeSH Breast/Lung Neoplasms never maps Lung → Breast); and only one unresolved151   oncologic condition may claim it. The mapping is stored as `PROBABILISTIC` (confidence 0.7) unless the152   study has exactly one condition and one MeSH term (then the resolver's match type, confidence ≤ 0.9).153   MeSH *ancestors* are never used for mapping (too generic).1543. Otherwise `match_type = UNRESOLVED`, `cancer_id` null, and `ctx.recordUnresolved('cancer', text,155   normalized, { nctId, meshTerms })` once per occurrence (so `unresolved_labels.count` = number of156   trial-condition occurrences). Generic labels ("Cancer", "Neoplasms", "Solid Tumor") resolve to the157   corresponding generic NCIt entity when the ontology provides one — that is the intended behaviour.1584. End of run: `resolver.suggest()` (pg_trgm) attaches a suggestion to the **top 500** unresolved labels159   of the run (`suggested_id/match_type=PROBABILISTIC/score`) — curation aid only, never auto-accepted.1605. End of run: **re-reconciliation** of previously `UNRESOLVED` rows (≤ 50,000 distinct labels, label161   candidates only) against the freshly warmed resolver; hits update `trial_conditions` and mark the162   `unresolved_labels` row `mapped` with `resolved_by = clinicaltrials:rereconcile:<match_type>`. This is163   what makes the trial layer catch up automatically after the NCIt ontology lands.164165DRUG/BIOLOGICAL interventions are queued once per distinct normalized name per run in166`unresolved_labels` (`entity_kind = drug`, context `{ occurrences, sampleNctIds }`); other intervention167types are not queued.168169## Derived table `trial_pulse`170171`refreshTrialPulse()` recomputes (delete + insert, deterministic, formula `ci-trial-pulse-v1`) the last172400 days: `new_trials` per `day` (= `first_posted_date`, full `YYYY-MM-DD` dates only — `YYYY-MM` is never173guessed) × `cancer_id` (any `trial_conditions` row with a cancer, distinct per trial) × `phase` (each174value of `phases` plus the pseudo-phase `ALL`). Rows with `cancer_id NULL` are the all-oncology pulse.175176## Metrics fed177178`entity_counters.trial_count / active_trial_count / recruiting_trial_count / phase3_trial_count`179(`packages/ranking/src/counters.ts`, aggregated over NCIt descendants) → rankings `active_trials`,180`recruiting_trials`, `phase3_trials`, `trial_gap`.181182## Limitations / gotchas183184- Resolution rate depends entirely on the alias set. With OncoTree only (865 entities, no "Colorectal185  Cancer"/"Prostate Cancer"-style registry names) ≈ 40 % of condition occurrences resolve; the top186  unresolved labels are exactly the registry phrasings NCIt synonyms provide.187- `PROBABILISTIC` mappings count in `entity_counters` like any other mapping (the match type is stored on188  every row, so consumers can filter).189- Conditions of a non-oncology nature on oncology trials (HIV, anemia, pain…) stay unresolved by design;190  they will keep appearing in the unresolved queue until curators mark them `ignored`.191- `HttpClient` sends the plain product token `CancerIndex/0.1` (some WAFs reject UAs with URLs).192- ClinicalTrials.gov page tokens are tied to the query; a token from a different `pageSize` is accepted193  (page size may change between pages per the docs), a token for a different filter is not (→ 400 → restart).194- Locations are capped at 500 per trial (`locations_count` keeps the true number).195196## Run report (cancerindex_b, 2026-09-08, OncoTree-only ontology: 865 cancers / 865 aliases)197198| run | mode | studies | HTTP req. | wall time | notes |199|---|---|---|---|---|---|200| `ING-…-000001` | dry_run, 5 | 5 | 2 | 0.9 s | totalCount 126,107 |201| `ING-…-000002/3/4` | incremental, 10 / 1000 / 1000 | 2,010 | 4 | 1.7 s + 5.2 s + 5.2 s | resume by pageToken verified |202| `ING-…-000005` | incremental (rest of full crawl) | 124,097 created | 126 | **8 min 19 s** | 0 rejected, 0 validation failures, 0 rate-limit events |203| `ING-…-000006/7` | backfill `--reset-cursor` | 126,107 re-processed (all `unchanged`) | 128 | 8 min 34 s / 8 min 59 s | repair after tightening the MeSH fallback |204205Final state: `clinical_trials` 126,107 · `trial_conditions` 278,132 (ONTOLOGY_EXACT 50,890 · PROBABILISTIC 1,025 ·206UNRESOLVED 226,313 → **18.8 % of condition occurrences resolved**, 37,718 trials (29.9 %) with ≥ 1 cancer) ·207`trial_locations` 1,211,366 · `trial_interventions` 246,864 · `trial_pulse` 6,905 rows (400 days) ·208`source_records(study)` 126,107 · cursor `{ pageToken: null, lastUpdatePostDate: "2026-09-04", fullTotalCount: 126107, dataTimestamp: "2026-09-04T09:00:06" }`.209210Top 15 unresolved condition labels (occurrences in the last full pass): Prostate Cancer (4,498) · Cancer (4,030) ·211Breast Cancer (3,846) · Colorectal Cancer (3,508) · Lung Cancer (3,149) · Multiple Myeloma (2,350) · Lymphomas212(2,107) · Ovarian Cancer (2,082) · Pancreatic Cancer (1,949) · Solid Tumors (1,930) · Gastric Cancer (1,832) ·213Leukemia (1,686) · Head and Neck Cancer (1,658) · Advanced Solid Tumor (1,652) · Cervical Cancer (1,348) — all214registry phrasings that NCIt synonyms ("Breast Cancer" = Breast Carcinoma, "Malignant Solid Neoplasm", …) will215resolve; the end-of-run re-reconciliation then repairs the stored rows automatically.216217`pnpm cix counters` → 865 entities (Σ trial_count 70,211, Σ active_trial_count 17,896); `pnpm cix rank` →218`active_trials level=all` eligible 383. Top 10 active_trials (level=all): Breast Neoplasm, NOS 1,302 ·219Non-Small Cell Lung Cancer 1,128 · Lymphoid Neoplasm 1,123 · Non-Hodgkin Lymphoma 1,038 · Myeloid Neoplasm 935 ·220Mature B-Cell Neoplasms 733 · Acute Myeloid Leukemia 724 · Hepatocellular Carcinoma 668 · Melanoma 519 ·221Head and Neck Squamous Cell Carcinoma 334.222223Gotcha: `unresolved_labels.count` is incremented on every run that sees the occurrence, so backfill runs inflate224it (632,516 after one full crawl + two backfills for 34,808 distinct cancer labels); treat it as a popularity225signal, not an exact occurrence count — the exact number is `count(*) FROM trial_conditions WHERE normalized = …`.226