# 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:

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.
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.
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
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.

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