spb/cancerindex

1# Drug development pipeline — stage rules (`drug_pipeline`, formula `ci-drug-pipeline-v1`)23The pipeline is a **derived** layer (claim category `computed_metric`): for each drug, and for each4(drug, top-level cancer) pair, the most advanced development stage supported by two canonical5relations — registered **interventional** clinical trials (`trial_interventions` ×6`trial_conditions` × `clinical_trials`) and jurisdiction-aware **approval records**7(`drug_approvals`). It is recomputed by `pnpm cix intel` (`packages/ranking/src/drug-pipeline.ts`,8`computeDrugPipeline`) in one transaction, set-based SQL over temp tables; the stage itself is9decided by a pure, unit-tested function (`stageFor`, `drug-pipeline.test.ts`). Every row carries10`formula_version` and its `inputs` (scope, approval counts, phase rank, the status lists and the11rule text) so a stage can be reproduced.1213Code: `/Users/simon-pierreboucher/Desktop/Projets/apps-web/cancerindex/packages/ranking/src/drug-pipeline.ts`.14Pages: `/pipeline` (funnel + table, `?cancer=<top-level slug>&stage=`), drug page section15"Development pipeline"; API `GET /v1/pipeline`, `GET /v1/pipeline/summary`.1617## Rows1819| Row | `cancer_id` | Trials counted | Approvals counted |20|---|---|---|---|21| Drug, across all cancers | `NULL` | every interventional trial that lists the drug as an intervention (`trial_interventions.drug_id`) | every `drug_approvals` row of the drug, with or without a cancer |22| Drug × top-level cancer | the top-level cancer | trials above whose `trial_conditions.cancer_id` is the top-level cancer **or one of its descendants** | approvals whose `cancer_id` is the top-level cancer or one of its descendants |2324A row is written only when the scope has ≥ 1 trial or ≥ 1 approval (no "preclinical" rows are25inferred: absence of registered activity is not evidence of preclinical work).2627### Ancestor mapping2829Descendants are resolved with a recursive CTE over `cancer_hierarchy` (all hierarchy types —30NCIt and OncoTree — like `entity_counters` and `GET /cancers/:id/descendants`), starting from every31`cancers.top_level = true AND status = 'active'` concept, depth ≤ 12 (`PIPELINE_THRESHOLDS.32maxHierarchyDepth`). A concept with several top-level ancestors (e.g. a lymphoma subtype under33both "Lymphoma" and a hematologic family) feeds every one of them — a trial is never lost, and a34drug may legitimately appear under two top-level cancers. Trials are counted DISTINCT per scope;35a study with three mapped conditions under one top-level cancer counts once there.3637Approvals without a `cancer_id` (FDA supplements, label bullets naming 0 or ≥ 2 cancers, every38**Health Canada DPD** row — the DPD publishes no indications) feed only the across-all-cancers39row. They never place a drug at "approved" for a specific cancer.4041## Stage rule (`stageFor`)4243Evaluated in this order, on the counts of the scope:44451. **approved** — at least one approval record with `status ∈ {approved, accelerated,46   conditional}` (`PIPELINE_APPROVED_STATUSES`). Approval in **any ingested jurisdiction** (US/FDA,47   CA/Health Canada today) suffices; `jurisdictions` lists which.482. **withdrawn** — approval records exist but every one is `withdrawn` or `superseded`. For a DPD49   product this means every Canadian DIN of the molecule in scope is cancelled/dormant *and* no50   other jurisdiction has an in-force record.513. otherwise, by the **highest registry phase** among the scope's interventional trials52   (`maxPhase`, rank PHASE4 4 > PHASE3 3 > PHASE2 2 > PHASE1 = EARLY_PHASE1 1 > NA 0; a trial53   labelled `PHASE2, PHASE3` ranks 3, `PHASE1, PHASE2` ranks 2):54   - `PHASE4` → **phase4**55   - `PHASE3` → **phase3**56   - `PHASE2` → **phase2**57   - `PHASE1` or `EARLY_PHASE1` → **phase1**58   - only `NA` / empty phases → **phase_not_stated**594. no trials and no approvals → **no row**.6061`max_phase` stores the label (PHASE1 wins over EARLY_PHASE1 when both occur at rank 1) even when62the stage is `approved`, so "approved, still in phase 4 trials" stays visible.6364### Counts and dates6566| Column | Definition |67|---|---|68| `total_trials` | DISTINCT interventional trials in scope |69| `active_trials` | those with `overall_status ∈ {RECRUITING, NOT_YET_RECRUITING, ENROLLING_BY_INVITATION, ACTIVE_NOT_RECRUITING}` (same list as trial intelligence and counters) |70| `recruiting_trials` | `overall_status = RECRUITING` |71| `phase3_trials` | trials whose `phases` contains `PHASE3` (so `PHASE2, PHASE3` counts) |72| `approvals` | approval records in scope, all statuses |73| `jurisdictions` | DISTINCT `jurisdiction` of those records |74| `first_approval_date` / `latest_approval_date` | min / max `approval_date` over in-force records only |75| `first_trial_date` | min `start_date` (registry text `YYYY-MM-DD` or `YYYY-MM`; lexicographic min) |7677## Caveats7879- **Registry phases are declared by sponsors**; `NA` is common for device, behavioural or80  surgical arms that carry a drug intervention. The stage says nothing about efficacy or about81  the drug's role (experimental arm vs comparator vs background therapy): a phase 3 trial using82  cisplatin as backbone places cisplatin at phase 3 for that cancer.83- **Only ingested jurisdictions** can produce "approved". A molecule approved by the EMA only is84  shown at its trial phase until an EMA connector exists. Conversely a Canadian DIN (Health85  Canada) is a market authorization for a product with no stated indication: the unscoped row86  becomes "approved" while cancer-scoped rows still follow trial phases.87- **Trial ↔ drug linking** is by alias reconciliation of intervention names (`match_type ALIAS`;88  `pnpm cix reconcile-drugs`). Unresolved intervention names (queued in `unresolved_labels`) do not89  contribute; new drug entities (e.g. minted by the Health Canada connector) gain their trials after90  the next reconciliation.91- **Trial ↔ cancer linking** depends on `trial_conditions.cancer_id` (CancerResolver, EXACT or92  ALIAS matches); trials whose conditions stayed unresolved only feed the unscoped row.93- **Duplicate drug entities** (salt forms: "Imatinib" and "Imatinib Mesylate") each get their own94  rows until curators accept a merge. `computeDrugPipeline` ends by *proposing* such merges95  (`entity_merges`, status `proposed`, `packages/ranking/src/drug-duplicates.ts`: equal names after96  stripping salt tokens, or ≥ 2 shared generic/brand/development-code aliases; keep = INN-like,97  shortest name). Nothing is merged automatically.98- Rows are rebuilt from scratch on each run (`DELETE` + `INSERT` in one transaction); `computed_at`99  is the run time shown by the Freshness line.100