# Drug development pipeline — stage rules (`drug_pipeline`, formula `ci-drug-pipeline-v1`)

The pipeline is a **derived** layer (claim category `computed_metric`): for each drug, and for each
(drug, top-level cancer) pair, the most advanced development stage supported by two canonical
relations — registered **interventional** clinical trials (`trial_interventions` ×
`trial_conditions` × `clinical_trials`) and jurisdiction-aware **approval records**
(`drug_approvals`). It is recomputed by `pnpm cix intel` (`packages/ranking/src/drug-pipeline.ts`,
`computeDrugPipeline`) in one transaction, set-based SQL over temp tables; the stage itself is
decided by a pure, unit-tested function (`stageFor`, `drug-pipeline.test.ts`). Every row carries
`formula_version` and its `inputs` (scope, approval counts, phase rank, the status lists and the
rule text) so a stage can be reproduced.

Code: `/Users/simon-pierreboucher/Desktop/Projets/apps-web/cancerindex/packages/ranking/src/drug-pipeline.ts`.
Pages: `/pipeline` (funnel + table, `?cancer=<top-level slug>&stage=`), drug page section
"Development pipeline"; API `GET /v1/pipeline`, `GET /v1/pipeline/summary`.

## Rows

| Row | `cancer_id` | Trials counted | Approvals counted |
|---|---|---|---|
| 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 |
| 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 |

A row is written only when the scope has ≥ 1 trial or ≥ 1 approval (no "preclinical" rows are
inferred: absence of registered activity is not evidence of preclinical work).

### Ancestor mapping

Descendants are resolved with a recursive CTE over `cancer_hierarchy` (all hierarchy types —
NCIt and OncoTree — like `entity_counters` and `GET /cancers/:id/descendants`), starting from every
`cancers.top_level = true AND status = 'active'` concept, depth ≤ 12 (`PIPELINE_THRESHOLDS.
maxHierarchyDepth`). A concept with several top-level ancestors (e.g. a lymphoma subtype under
both "Lymphoma" and a hematologic family) feeds every one of them — a trial is never lost, and a
drug may legitimately appear under two top-level cancers. Trials are counted DISTINCT per scope;
a study with three mapped conditions under one top-level cancer counts once there.

Approvals without a `cancer_id` (FDA supplements, label bullets naming 0 or ≥ 2 cancers, every
**Health Canada DPD** row — the DPD publishes no indications) feed only the across-all-cancers
row. They never place a drug at "approved" for a specific cancer.

## Stage rule (`stageFor`)

Evaluated in this order, on the counts of the scope:

1. **approved** — at least one approval record with `status ∈ {approved, accelerated,
   conditional}` (`PIPELINE_APPROVED_STATUSES`). Approval in **any ingested jurisdiction** (US/FDA,
   CA/Health Canada today) suffices; `jurisdictions` lists which.
2. **withdrawn** — approval records exist but every one is `withdrawn` or `superseded`. For a DPD
   product this means every Canadian DIN of the molecule in scope is cancelled/dormant *and* no
   other jurisdiction has an in-force record.
3. otherwise, by the **highest registry phase** among the scope's interventional trials
   (`maxPhase`, rank PHASE4 4 > PHASE3 3 > PHASE2 2 > PHASE1 = EARLY_PHASE1 1 > NA 0; a trial
   labelled `PHASE2, PHASE3` ranks 3, `PHASE1, PHASE2` ranks 2):
   - `PHASE4` → **phase4**
   - `PHASE3` → **phase3**
   - `PHASE2` → **phase2**
   - `PHASE1` or `EARLY_PHASE1` → **phase1**
   - only `NA` / empty phases → **phase_not_stated**
4. no trials and no approvals → **no row**.

`max_phase` stores the label (PHASE1 wins over EARLY_PHASE1 when both occur at rank 1) even when
the stage is `approved`, so "approved, still in phase 4 trials" stays visible.

### Counts and dates

| Column | Definition |
|---|---|
| `total_trials` | DISTINCT interventional trials in scope |
| `active_trials` | those with `overall_status ∈ {RECRUITING, NOT_YET_RECRUITING, ENROLLING_BY_INVITATION, ACTIVE_NOT_RECRUITING}` (same list as trial intelligence and counters) |
| `recruiting_trials` | `overall_status = RECRUITING` |
| `phase3_trials` | trials whose `phases` contains `PHASE3` (so `PHASE2, PHASE3` counts) |
| `approvals` | approval records in scope, all statuses |
| `jurisdictions` | DISTINCT `jurisdiction` of those records |
| `first_approval_date` / `latest_approval_date` | min / max `approval_date` over in-force records only |
| `first_trial_date` | min `start_date` (registry text `YYYY-MM-DD` or `YYYY-MM`; lexicographic min) |

## Caveats

- **Registry phases are declared by sponsors**; `NA` is common for device, behavioural or
  surgical arms that carry a drug intervention. The stage says nothing about efficacy or about
  the drug's role (experimental arm vs comparator vs background therapy): a phase 3 trial using
  cisplatin as backbone places cisplatin at phase 3 for that cancer.
- **Only ingested jurisdictions** can produce "approved". A molecule approved by the EMA only is
  shown at its trial phase until an EMA connector exists. Conversely a Canadian DIN (Health
  Canada) is a market authorization for a product with no stated indication: the unscoped row
  becomes "approved" while cancer-scoped rows still follow trial phases.
- **Trial ↔ drug linking** is by alias reconciliation of intervention names (`match_type ALIAS`;
  `pnpm cix reconcile-drugs`). Unresolved intervention names (queued in `unresolved_labels`) do not
  contribute; new drug entities (e.g. minted by the Health Canada connector) gain their trials after
  the next reconciliation.
- **Trial ↔ cancer linking** depends on `trial_conditions.cancer_id` (CancerResolver, EXACT or
  ALIAS matches); trials whose conditions stayed unresolved only feed the unscoped row.
- **Duplicate drug entities** (salt forms: "Imatinib" and "Imatinib Mesylate") each get their own
  rows until curators accept a merge. `computeDrugPipeline` ends by *proposing* such merges
  (`entity_merges`, status `proposed`, `packages/ranking/src/drug-duplicates.ts`: equal names after
  stripping salt tokens, or ≥ 2 shared generic/brand/development-code aliases; keep = INN-like,
  shortest name). Nothing is merged automatically.
- Rows are rebuilt from scratch on each run (`DELETE` + `INSERT` in one transaction); `computed_at`
  is the run time shown by the Freshness line.