spb/cancerindex
Public
TypeScript 97.2%
SQL 1.5%
CSS 0.6%
JavaScript 0.5%
1import { sql } from 'drizzle-orm';2import { type Database, metricDefinitions } from '@cancerindex/database';3import { persistSnapshot, type RankingResult, type Scope } from './engine.js';4import type { RankInput } from './rank.js';5import { STOP_REASON_RULES_VERSION, classifyStopReason } from './trial-stop-reasons.js';67// The stop-reason classifier is part of this work package's public surface (API + web reuse it).8export * from './trial-stop-reasons.js';910export const TRIAL_INTELLIGENCE_FORMULA_VERSION = 'ci-trial-intel-v1';1112/** Registry statuses counted as "active" (same list as counters.ts so numbers match entity_counters). */13export const TRIAL_INTEL_ACTIVE_STATUSES = ['RECRUITING', 'NOT_YET_RECRUITING', 'ENROLLING_BY_INVITATION', 'ACTIVE_NOT_RECRUITING'] as const;14// Inline literal array: drizzle expands a JS array parameter into a ($1,$2,…) tuple, which breaks ANY().15const ACTIVE_SQL = `ARRAY[${TRIAL_INTEL_ACTIVE_STATUSES.map((s) => `'${s}'`).join(',')}]::text[]`;1617/** Every threshold used by the formulas — persisted in `inputs.thresholds` of each row. */18export const TRIAL_INTEL_THRESHOLDS = {19 /** trial_growth_yoy is null when fewer studies were first posted in the prior 12-month window. */20 growthMinPriorTrials: 20,21 /** sponsor_hhi is null when fewer active interventional studies. */22 hhiMinActiveTrials: 10,23 /** termination_share is null when fewer terminal studies (completed + terminated + withdrawn). */24 terminationMinTerminalTrials: 30,25 /** termination_share only considers studies first posted on/after this date. */26 terminationSince: '2010-01-01',27 /** burden-normalized ratios require at least this many annual deaths. */28 burdenMinDeaths: 100,29 /** NCIt-hierarchy traversal depth for descendants (same as counters.ts). */30 maxHierarchyDepth: 12,31 /** Burden scope for trials_per_1000_deaths / trials_per_100k_cases. */32 burdenGeography: 'USA',33} as const;3435export interface TrialIntelligenceResult {36 rows: number;37 topRows: number;38 allRows: number;39 stopReasonsClassified: number;40 rankings: RankingResult[];41 ms: number;42}4344/**45 * Growth windows as ISO dates for a reference day (pure, unit-tested):46 * new_trials_12m ← first_posted_date ∈ [asOf − 12 months, asOf)47 * new_trials_prior_12m ← first_posted_date ∈ [asOf − 24 months, asOf − 12 months)48 */49export function growthWindows(asOf: string): { asOf: string; new12m: { from: string; to: string }; prior12m: { from: string; to: string } } {50 const d = new Date(`${asOf}T00:00:00Z`);51 if (!/^\d{4}-\d{2}-\d{2}$/.test(asOf) || Number.isNaN(d.getTime())) throw new Error(`growthWindows: invalid asOf date "${asOf}"`);52 const minus = (months: number) => {53 const x = new Date(d);54 const day = x.getUTCDate();55 x.setUTCDate(1);56 x.setUTCMonth(x.getUTCMonth() - months);57 const last = new Date(Date.UTC(x.getUTCFullYear(), x.getUTCMonth() + 1, 0)).getUTCDate();58 x.setUTCDate(Math.min(day, last));59 return x.toISOString().slice(0, 10);60 };61 const m12 = minus(12);62 const m24 = minus(24);63 return { asOf, new12m: { from: m12, to: asOf }, prior12m: { from: m24, to: m12 } };64}6566/** (new − prior) / prior, null under the eligibility threshold (pure, unit-tested). */67export function growthYoy(new12: number, prior12: number, minPrior: number = TRIAL_INTEL_THRESHOLDS.growthMinPriorTrials): number | null {68 if (!Number.isFinite(new12) || !Number.isFinite(prior12) || prior12 < minPrior || prior12 <= 0) return null;69 return (new12 - prior12) / prior12;70}7172/** Herfindahl–Hirschman index of a count distribution: Σ (nᵢ / Σn)² ∈ (0, 1]; null when empty (pure, unit-tested). */73export function hhi(counts: Iterable<number>): number | null {74 let total = 0;75 let sq = 0;76 for (const n of counts) {77 if (!Number.isFinite(n) || n < 0) continue;78 total += n;79 sq += n * n;80 }81 return total > 0 ? sq / (total * total) : null;82}8384/** (terminated + withdrawn) / (completed + terminated + withdrawn); null under the threshold (pure, unit-tested). */85export function terminationShare(completed: number, terminated: number, withdrawn: number, minTerminal: number = TRIAL_INTEL_THRESHOLDS.terminationMinTerminalTrials): number | null {86 const denom = completed + terminated + withdrawn;87 if (denom < minTerminal || denom <= 0) return null;88 return (terminated + withdrawn) / denom;89}9091/** active / (deaths / 1000) and active / (incidence / 100 000); null when deaths are under the threshold (pure, unit-tested). */92export function burdenNormalized(active: number, deaths: number | null, incidence: number | null, minDeaths: number = TRIAL_INTEL_THRESHOLDS.burdenMinDeaths): { per1000Deaths: number | null; per100kCases: number | null } {93 if (deaths == null || !Number.isFinite(deaths) || deaths < minDeaths) return { per1000Deaths: null, per100kCases: null };94 return {95 per1000Deaths: active / (deaths / 1000),96 per100kCases: incidence != null && Number.isFinite(incidence) && incidence > 0 ? active / (incidence / 100_000) : null,97 };98}99100/**101 * Recompute `trial_intelligence` for every top-level cancer (entity_level = 'top') and every active102 * malignant entity with ≥ 1 mapped trial (entity_level = 'all'). Trials attach to a cancer through103 * `trial_conditions.cancer_id` over its NCIt-hierarchy descendants (depth ≤ 12, DISTINCT trials) —104 * exactly like entity_counters. Counts are over interventional studies. Set-based SQL over temp105 * tables; one transaction; deterministic for a given `asOf` day. Rankings for the four106 * trial-intelligence metrics are refreshed at the end.107 */108export async function computeTrialIntelligence(db: Database, opts: { asOf?: string } = {}): Promise<TrialIntelligenceResult> {109 const t0 = Date.now();110 const asOf = opts.asOf ?? new Date().toISOString().slice(0, 10);111 const windows = growthWindows(asOf);112 const th = TRIAL_INTEL_THRESHOLDS;113114 // 1. Classify registrant-reported stop reasons in TypeScript (pure rules), to be joined in SQL.115 const stopped = await db.execute<{ id: string; why_stopped: string | null }>(sql`116 SELECT id, why_stopped FROM clinical_trials WHERE overall_status IN ('TERMINATED','WITHDRAWN','SUSPENDED')`);117 const stopIds: string[] = [];118 const stopCats: string[] = [];119 for (const r of stopped) {120 stopIds.push(r.id);121 stopCats.push(classifyStopReason(r.why_stopped).category);122 }123124 const { top, all } = await db.transaction(async (tx) => {125 await tx.execute(sql`CREATE TEMP TABLE _ti_desc (ancestor varchar(32), descendant varchar(32)) ON COMMIT DROP`);126 await tx.execute(sql`127 INSERT INTO _ti_desc128 WITH RECURSIVE d AS (129 SELECT id AS ancestor, id AS descendant, 0 AS depth FROM cancers WHERE status = 'active'130 UNION131 SELECT d.ancestor, h.child_id, d.depth + 1 FROM d JOIN cancer_hierarchy h ON h.parent_id = d.descendant132 WHERE d.depth < ${sql.raw(String(th.maxHierarchyDepth))}133 )134 SELECT DISTINCT ancestor, descendant FROM d`);135 await tx.execute(sql`CREATE INDEX ON _ti_desc (descendant)`);136137 await tx.execute(sql`CREATE TEMP TABLE _ti_stop (trial_id varchar(32) PRIMARY KEY, category text NOT NULL) ON COMMIT DROP`);138 const chunk = 20_000;139 for (let i = 0; i < stopIds.length; i += chunk) {140 await tx.execute(sql`INSERT INTO _ti_stop (trial_id, category) SELECT * FROM unnest(${sql.param(stopIds.slice(i, i + chunk))}::text[], ${sql.param(stopCats.slice(i, i + chunk))}::text[])`);141 }142143 // 2. cancer × trial map (DISTINCT over descendants) with the trial fields the formulas need.144 await tx.execute(sql`145 CREATE TEMP TABLE _ti_map ON COMMIT DROP AS146 SELECT m.cancer_id, t.id AS trial_id,147 t.study_type = 'INTERVENTIONAL' AS interventional,148 t.overall_status AS status,149 t.overall_status = ANY(${sql.raw(ACTIVE_SQL)}) AS active,150 t.phases, t.first_posted_date::date AS first_posted, t.enrollment_count, t.lead_sponsor, t.lead_sponsor_class, t.countries, t.has_results151 FROM (SELECT DISTINCT d.ancestor AS cancer_id, tc.trial_id FROM trial_conditions tc JOIN _ti_desc d ON d.descendant = tc.cancer_id WHERE tc.cancer_id IS NOT NULL) m152 JOIN clinical_trials t ON t.id = m.trial_id`);153 await tx.execute(sql`CREATE INDEX ON _ti_map (cancer_id)`);154155 // 3. Per-cancer aggregates (computed once, inserted for each entity level the cancer belongs to).156 await tx.execute(sql`157 CREATE TEMP TABLE _ti_agg ON COMMIT DROP AS158 WITH base AS (159 SELECT cancer_id,160 count(*) AS mapped_any_type,161 count(*) FILTER (WHERE interventional) AS total_trials,162 count(*) FILTER (WHERE interventional AND active) AS active_trials,163 count(*) FILTER (WHERE interventional AND status = 'RECRUITING') AS recruiting_trials,164 count(*) FILTER (WHERE interventional AND active AND 'PHASE1' = ANY(phases)) AS phase1_active,165 count(*) FILTER (WHERE interventional AND active AND 'PHASE2' = ANY(phases)) AS phase2_active,166 count(*) FILTER (WHERE interventional AND active AND 'PHASE3' = ANY(phases)) AS phase3_active,167 count(*) FILTER (WHERE interventional AND status = 'RECRUITING' AND 'PHASE3' = ANY(phases)) AS phase3_recruiting,168 count(*) FILTER (WHERE interventional AND active AND 'PHASE4' = ANY(phases)) AS phase4_active,169 count(*) FILTER (WHERE interventional AND status = 'COMPLETED') AS completed_trials,170 count(*) FILTER (WHERE interventional AND status = 'TERMINATED') AS terminated_trials,171 count(*) FILTER (WHERE interventional AND status = 'WITHDRAWN') AS withdrawn_trials,172 count(*) FILTER (WHERE interventional AND status = 'SUSPENDED') AS suspended_trials,173 count(*) FILTER (WHERE interventional AND has_results) AS with_results,174 count(*) FILTER (WHERE interventional AND first_posted >= ${windows.new12m.from}::date AND first_posted < ${windows.new12m.to}::date) AS new_trials_12m,175 count(*) FILTER (WHERE interventional AND first_posted >= ${windows.prior12m.from}::date AND first_posted < ${windows.prior12m.to}::date) AS new_trials_prior_12m,176 avg(enrollment_count) FILTER (WHERE interventional AND active) AS avg_enrollment,177 percentile_cont(0.5) WITHIN GROUP (ORDER BY enrollment_count) FILTER (WHERE interventional AND active AND enrollment_count IS NOT NULL) AS median_enrollment,178 sum(enrollment_count) FILTER (WHERE interventional AND active) AS total_enrollment_active,179 count(*) FILTER (WHERE interventional AND active AND lead_sponsor_class = 'INDUSTRY') AS industry_active,180 count(*) FILTER (WHERE interventional AND active AND 'United States' = ANY(countries)) AS us_active,181 count(*) FILTER (WHERE interventional AND first_posted >= ${th.terminationSince}::date AND status = 'COMPLETED') AS term_completed,182 count(*) FILTER (WHERE interventional AND first_posted >= ${th.terminationSince}::date AND status = 'TERMINATED') AS term_terminated,183 count(*) FILTER (WHERE interventional AND first_posted >= ${th.terminationSince}::date AND status = 'WITHDRAWN') AS term_withdrawn184 FROM _ti_map GROUP BY cancer_id185 ),186 sp AS (187 SELECT cancer_id, lead_sponsor, count(*) AS n FROM _ti_map WHERE interventional AND active AND lead_sponsor IS NOT NULL GROUP BY 1, 2188 ),189 spa AS (190 SELECT cancer_id, count(*) AS distinct_sponsors, sum(n) AS sponsor_denominator, sum(n * n)::double precision / (sum(n) * sum(n)) AS sponsor_hhi,191 (array_agg(lead_sponsor ORDER BY n DESC, lead_sponsor))[1] AS top_sponsor, max(n) AS top_sponsor_n192 FROM sp GROUP BY cancer_id193 ),194 co AS (195 SELECT cancer_id, c AS country, count(*) AS n FROM _ti_map, unnest(countries) c WHERE interventional AND active GROUP BY 1, 2196 ),197 coa AS (198 SELECT cancer_id, count(*) AS distinct_countries, sum(n) AS country_pairs, sum(n * n)::double precision / (sum(n) * sum(n)) AS country_hhi,199 (array_agg(country ORDER BY n DESC, country))[1] AS top_country, max(n) AS top_country_n200 FROM co GROUP BY cancer_id201 ),202 ws AS (203 SELECT m.cancer_id, s.category, count(*) AS n FROM _ti_map m JOIN _ti_stop s ON s.trial_id = m.trial_id204 WHERE m.interventional AND m.status IN ('TERMINATED','WITHDRAWN','SUSPENDED') GROUP BY 1, 2205 ),206 wsa AS (207 SELECT cancer_id, jsonb_object_agg(category, n ORDER BY category) AS why_stopped_breakdown FROM ws GROUP BY cancer_id208 ),209 bur AS (210 SELECT DISTINCT ON (x.cancer_id) x.* FROM (211 SELECT m.cancer_id, m.year, m.source_id, m.value AS deaths, i.value AS incidence, m.id AS mortality_obs_id, i.id AS incidence_obs_id212 FROM epidemiology_observations m213 JOIN geographies g ON g.id = m.geography_id AND g.iso3 = ${th.burdenGeography}214 JOIN epidemiology_observations i ON i.cancer_id = m.cancer_id AND i.geography_id = m.geography_id AND i.year = m.year AND i.source_id = m.source_id215 AND i.sex = m.sex AND i.age_group = m.age_group AND i.metric = 'incidence_count'216 WHERE m.metric = 'mortality_count' AND m.sex = 'all' AND m.age_group = 'all' AND m.value >= ${sql.raw(String(th.burdenMinDeaths))}217 ) x ORDER BY x.cancer_id, x.year DESC, x.source_id, x.mortality_obs_id, x.incidence_obs_id218 )219 SELECT c.id AS cancer_id, c.top_level, c.malignant,220 b.mapped_any_type, b.total_trials, b.active_trials, b.recruiting_trials, b.phase1_active, b.phase2_active, b.phase3_active, b.phase3_recruiting, b.phase4_active,221 b.completed_trials, b.terminated_trials, b.withdrawn_trials, b.suspended_trials, b.with_results,222 b.new_trials_12m, b.new_trials_prior_12m,223 CASE WHEN b.new_trials_prior_12m >= ${sql.raw(String(th.growthMinPriorTrials))} THEN (b.new_trials_12m - b.new_trials_prior_12m)::double precision / b.new_trials_prior_12m END AS trial_growth_yoy,224 b.avg_enrollment, b.median_enrollment, b.total_enrollment_active,225 COALESCE(s.distinct_sponsors, 0) AS distinct_sponsors,226 CASE WHEN b.active_trials > 0 THEN b.industry_active::double precision / b.active_trials END AS industry_share,227 CASE WHEN b.active_trials >= ${sql.raw(String(th.hhiMinActiveTrials))} THEN s.sponsor_hhi END AS sponsor_hhi,228 s.top_sponsor,229 CASE WHEN s.sponsor_denominator > 0 THEN s.top_sponsor_n::double precision / s.sponsor_denominator END AS top_sponsor_share,230 COALESCE(s.sponsor_denominator, 0) AS sponsor_denominator,231 COALESCE(g.distinct_countries, 0) AS distinct_countries,232 CASE WHEN b.active_trials > 0 THEN b.us_active::double precision / b.active_trials END AS us_share,233 g.top_country,234 CASE WHEN b.active_trials > 0 THEN g.top_country_n::double precision / b.active_trials END AS top_country_share,235 g.country_hhi,236 COALESCE(g.country_pairs, 0) AS country_pairs,237 b.term_completed, b.term_terminated, b.term_withdrawn,238 CASE WHEN (b.term_completed + b.term_terminated + b.term_withdrawn) >= ${sql.raw(String(th.terminationMinTerminalTrials))}239 THEN (b.term_terminated + b.term_withdrawn)::double precision / (b.term_completed + b.term_terminated + b.term_withdrawn) END AS termination_share,240 COALESCE(w.why_stopped_breakdown, '{}'::jsonb) AS why_stopped_breakdown,241 CASE WHEN c.top_level THEN b.active_trials::double precision / (r.deaths / 1000) END AS trials_per_1000_deaths,242 CASE WHEN c.top_level AND r.incidence > 0 THEN b.active_trials::double precision / (r.incidence / 100000) END AS trials_per_100k_cases,243 CASE WHEN c.top_level AND r.cancer_id IS NOT NULL THEN ${th.burdenGeography}::text END AS burden_geography,244 CASE WHEN c.top_level THEN r.year END AS burden_year,245 CASE WHEN c.top_level THEN r.source_id END AS burden_source_id,246 CASE WHEN c.top_level THEN r.deaths END AS burden_deaths,247 CASE WHEN c.top_level THEN r.incidence END AS burden_incidence,248 CASE WHEN c.top_level THEN r.mortality_obs_id END AS mortality_obs_id,249 CASE WHEN c.top_level THEN r.incidence_obs_id END AS incidence_obs_id250 FROM cancers c251 JOIN base b ON b.cancer_id = c.id252 LEFT JOIN spa s ON s.cancer_id = c.id253 LEFT JOIN coa g ON g.cancer_id = c.id254 LEFT JOIN wsa w ON w.cancer_id = c.id255 LEFT JOIN bur r ON r.cancer_id = c.id256 WHERE c.status = 'active'`);257258 const inputsJson = sql`jsonb_strip_nulls(jsonb_build_object(259 'asOf', ${asOf}::text,260 'windows', ${JSON.stringify({ new12m: windows.new12m, prior12m: windows.prior12m })}::jsonb,261 'thresholds', ${JSON.stringify(th)}::jsonb,262 'activeStatuses', ${JSON.stringify(TRIAL_INTEL_ACTIVE_STATUSES)}::jsonb,263 'stopReasonRulesVersion', ${STOP_REASON_RULES_VERSION}::text,264 'aggregation', 'descendants',265 'studyType', 'INTERVENTIONAL',266 'mappedTrialsAnyType', a.mapped_any_type,267 'denominators', jsonb_build_object('sponsor', a.sponsor_denominator, 'countryPairs', a.country_pairs, 'terminal', a.term_completed + a.term_terminated + a.term_withdrawn, 'terminalCompleted', a.term_completed, 'terminalTerminated', a.term_terminated, 'terminalWithdrawn', a.term_withdrawn),268 'burden', CASE WHEN a.burden_source_id IS NOT NULL THEN jsonb_build_object('geography', a.burden_geography, 'year', a.burden_year, 'sourceId', a.burden_source_id, 'deaths', a.burden_deaths, 'incidence', a.burden_incidence, 'mortalityObservationId', a.mortality_obs_id, 'incidenceObservationId', a.incidence_obs_id, 'sex', 'all', 'ageGroup', 'all') END269 ))`;270271 const insertFor = (level: 'top' | 'all') => sql`272 INSERT INTO trial_intelligence (cancer_id, entity_level, total_trials, active_trials, recruiting_trials, phase1_active, phase2_active, phase3_active, phase3_recruiting, phase4_active,273 completed_trials, terminated_trials, withdrawn_trials, suspended_trials, with_results, new_trials_12m, new_trials_prior_12m, trial_growth_yoy,274 avg_enrollment, median_enrollment, total_enrollment_active, distinct_sponsors, industry_share, sponsor_hhi, top_sponsor, top_sponsor_share,275 distinct_countries, us_share, top_country, top_country_share, country_hhi, termination_share, why_stopped_breakdown,276 trials_per_1000_deaths, trials_per_100k_cases, burden_geography, burden_year, burden_source_id, formula_version, inputs, updated_at)277 SELECT a.cancer_id, ${level}, a.total_trials, a.active_trials, a.recruiting_trials, a.phase1_active, a.phase2_active, a.phase3_active, a.phase3_recruiting, a.phase4_active,278 a.completed_trials, a.terminated_trials, a.withdrawn_trials, a.suspended_trials, a.with_results, a.new_trials_12m, a.new_trials_prior_12m, a.trial_growth_yoy,279 a.avg_enrollment, a.median_enrollment, a.total_enrollment_active, a.distinct_sponsors, a.industry_share, a.sponsor_hhi, a.top_sponsor, a.top_sponsor_share,280 a.distinct_countries, a.us_share, a.top_country, a.top_country_share, a.country_hhi, a.termination_share, a.why_stopped_breakdown,281 a.trials_per_1000_deaths, a.trials_per_100k_cases, a.burden_geography, a.burden_year, a.burden_source_id, ${TRIAL_INTELLIGENCE_FORMULA_VERSION}, ${inputsJson}, now()282 FROM _ti_agg a283 ${level === 'top' ? sql`WHERE a.top_level` : sql`WHERE a.malignant AND a.mapped_any_type > 0`}`;284285 await tx.execute(sql`DELETE FROM trial_intelligence`);286 // Top-level cancers without any mapped trial still get a row (true zeros) so the top-level table is complete.287 await tx.execute(sql`288 INSERT INTO _ti_agg (cancer_id, top_level, malignant, mapped_any_type, total_trials, active_trials, recruiting_trials, phase1_active, phase2_active, phase3_active, phase3_recruiting, phase4_active,289 completed_trials, terminated_trials, withdrawn_trials, suspended_trials, with_results, new_trials_12m, new_trials_prior_12m, distinct_sponsors, sponsor_denominator, distinct_countries, country_pairs,290 term_completed, term_terminated, term_withdrawn, why_stopped_breakdown)291 SELECT c.id, true, c.malignant, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '{}'::jsonb292 FROM cancers c WHERE c.status = 'active' AND c.top_level AND NOT EXISTS (SELECT 1 FROM _ti_agg a WHERE a.cancer_id = c.id)`);293 const topRows = await tx.execute<{ n: string }>(sql`WITH ins AS (${insertFor('top')} RETURNING 1) SELECT count(*)::text AS n FROM ins`);294 const allRows = await tx.execute<{ n: string }>(sql`WITH ins AS (${insertFor('all')} RETURNING 1) SELECT count(*)::text AS n FROM ins`);295 return { top: Number(topRows[0]?.n ?? 0), all: Number(allRows[0]?.n ?? 0) };296 });297298 const rankings = await rankTrialIntelligence(db);299 return { rows: top + all, topRows: top, allRows: all, stopReasonsClassified: stopIds.length, rankings, ms: Date.now() - t0 };300}301302interface IntelRow extends Record<string, unknown> {303 cancer_id: string;304 entity_level: 'top' | 'all';305 phase3_recruiting: number;306 active_trials: number;307 new_trials_12m: number;308 new_trials_prior_12m: number;309 trial_growth_yoy: number | null;310 termination_share: number | null;311 sponsor_hhi: number | null;312 distinct_sponsors: number;313 top_sponsor: string | null;314 top_sponsor_share: number | null;315 inputs: Record<string, unknown>;316}317318/**319 * Ranking snapshots for the four trial-intelligence metrics (WORLD, all sexes/ages, latest, per entity320 * level). Only eligible entities are ranked: non-null value, count metrics > 0. `trial_termination_share`321 * ranks descending too (rank 1 = highest share; `higher_is_worse` is display information).322 */323export async function rankTrialIntelligence(db: Database): Promise<RankingResult[]> {324 const slugs = ['phase3_recruiting_trials', 'trial_growth_yoy', 'trial_termination_share', 'sponsor_concentration'];325 const defs = await db.select().from(metricDefinitions);326 const byslug = new Map(defs.filter((d) => slugs.includes(d.slug)).map((d) => [d.slug, d]));327 const rows = await db.execute<IntelRow>(sql`328 SELECT ti.cancer_id, ti.entity_level, ti.phase3_recruiting, ti.active_trials, ti.new_trials_12m, ti.new_trials_prior_12m, ti.trial_growth_yoy, ti.termination_share, ti.sponsor_hhi,329 ti.distinct_sponsors, ti.top_sponsor, ti.top_sponsor_share, ti.inputs330 FROM trial_intelligence ti JOIN cancers c ON c.id = ti.cancer_id WHERE c.status = 'active' AND ti.formula_version = ${TRIAL_INTELLIGENCE_FORMULA_VERSION}`);331 const out: RankingResult[] = [];332 for (const level of ['top', 'all'] as const) {333 const scope: Scope = { geography: 'WORLD', sex: 'all', ageGroup: 'all', year: null, entityLevel: level };334 const lv = rows.filter((r) => r.entity_level === level);335 const common = (r: IntelRow) => ({ formulaVersion: TRIAL_INTELLIGENCE_FORMULA_VERSION, asOf: r.inputs.asOf, aggregation: 'descendants', studyType: 'INTERVENTIONAL', activeStatuses: r.inputs.activeStatuses });336 const metrics: Array<{ slug: string; items: RankInput[] }> = [337 {338 slug: 'phase3_recruiting_trials',339 items: lv.filter((r) => Number(r.phase3_recruiting) > 0).map((r) => ({ id: r.cancer_id, value: Number(r.phase3_recruiting), confidence: 'HIGH' as const, inputs: { ...common(r), phase3Recruiting: Number(r.phase3_recruiting), activeTrials: Number(r.active_trials) } })),340 },341 {342 slug: 'trial_growth_yoy',343 items: lv344 .filter((r) => r.trial_growth_yoy != null && Number.isFinite(Number(r.trial_growth_yoy)))345 .map((r) => ({ id: r.cancer_id, value: Number(r.trial_growth_yoy), confidence: (Number(r.new_trials_prior_12m) >= 100 ? 'HIGH' : 'MEDIUM') as 'HIGH' | 'MEDIUM', inputs: { ...common(r), newTrials12m: Number(r.new_trials_12m), newTrialsPrior12m: Number(r.new_trials_prior_12m), windows: r.inputs.windows, minPriorTrials: TRIAL_INTEL_THRESHOLDS.growthMinPriorTrials } })),346 },347 {348 slug: 'trial_termination_share',349 items: lv350 .filter((r) => r.termination_share != null && Number.isFinite(Number(r.termination_share)))351 .map((r) => {352 const d = (r.inputs.denominators ?? {}) as Record<string, number>;353 return { id: r.cancer_id, value: Number(r.termination_share), confidence: ((d.terminal ?? 0) >= 100 ? 'HIGH' : 'MEDIUM') as 'HIGH' | 'MEDIUM', inputs: { ...common(r), completed: d.terminalCompleted, terminated: d.terminalTerminated, withdrawn: d.terminalWithdrawn, terminal: d.terminal, since: TRIAL_INTEL_THRESHOLDS.terminationSince, minTerminalTrials: TRIAL_INTEL_THRESHOLDS.terminationMinTerminalTrials } };354 }),355 },356 {357 slug: 'sponsor_concentration',358 items: lv359 .filter((r) => r.sponsor_hhi != null && Number.isFinite(Number(r.sponsor_hhi)))360 .map((r) => {361 const d = (r.inputs.denominators ?? {}) as Record<string, number>;362 return { id: r.cancer_id, value: Number(r.sponsor_hhi), confidence: (Number(r.active_trials) >= 50 ? 'HIGH' : 'MEDIUM') as 'HIGH' | 'MEDIUM', inputs: { ...common(r), activeTrials: Number(r.active_trials), activeWithSponsor: d.sponsor, distinctSponsors: Number(r.distinct_sponsors), topSponsor: r.top_sponsor, topSponsorShare: r.top_sponsor_share == null ? null : Number(r.top_sponsor_share), minActiveTrials: TRIAL_INTEL_THRESHOLDS.hhiMinActiveTrials } };363 }),364 },365 ];366 for (const m of metrics) {367 const def = byslug.get(m.slug);368 if (!def || m.items.length < 3) continue;369 out.push(await persistSnapshot(db, def, scope, m.items, { descending: true, sourceIds: def.sourceSlugs.length ? def.sourceSlugs : ['clinicaltrials'] }));370 }371 }372 return out;373}374