spb/cancerindex
Public
TypeScript 97.2%
SQL 1.5%
CSS 0.6%
JavaScript 0.5%
1import { and, eq, sql } from 'drizzle-orm';2import { type Database, metricDefinitions, researchGapComponents } from '@cancerindex/database';3import { persistSnapshot, type Scope } from './engine.js';4import type { RankInput } from './rank.js';56export const RESEARCH_GAP_FORMULA_VERSION = 'ci-research-gap-components-v1';78/** Eligibility thresholds (metric_definitions eligibility.minDeaths + activity ≥ 1 per ratio). */9export const RESEARCH_GAP_THRESHOLDS = {10 /** Minimum annual deaths in the scope for a cancer to enter the eligible set. */11 minDeaths: 100,12 /** Minimum activity (trials or publications) for the corresponding log-ratio to be defined. */13 minActivity: 1,14 /** Minimum number of top-level cancers with a mortality_count observation for a scope to exist. */15 minCancersPerScope: 10,16 /** Deaths above which a ranking row gets HIGH confidence (else MEDIUM). */17 highConfidenceDeaths: 1000,18} as const;1920export type ResearchGapThresholds = { minDeaths: number; minActivity: number };2122export interface ResearchGapResult {23 rows: number;24 scopes: number;25}2627/** Raw inputs for one cancer in one burden scope. */28export interface ComponentInput {29 id: string;30 deaths: number | null;31 activeTrials: number;32 publications5y: number;33}3435/** Eligibility verdict for one cancer (pure). */36export interface Eligibility {37 eligible: boolean;38 reason: string | null;39 /** Whether the trial-based ratio can be computed (eligible AND active_trials ≥ minActivity). */40 trialRatio: boolean;41 publicationRatio: boolean;42}4344/** Computed shares and ratios for one cancer (pure; nulls where undefined). */45export interface ComponentShares extends Eligibility {46 id: string;47 deathShare: number | null;48 trialShare: number | null;49 publicationShare: number | null;50 trialGapRatio: number | null;51 researchGapRatio: number | null;52 trialsPer1000Deaths: number | null;53 publicationsPer1000Deaths: number | null;54}5556export interface SharesResult {57 rows: ComponentShares[];58 /** Sums over the eligible set (deaths ≥ minDeaths). */59 sums: { deaths: number; activeTrials: number; publications5y: number; eligible: number };60}6162const round = (v: number, digits = 6): number => {63 const f = 10 ** digits;64 return Math.round(v * f) / f;65};6667/**68 * Eligibility rule (metric_definitions eligibility: deaths ≥ minDeaths; each ratio also needs69 * activity ≥ minActivity so that log2 is defined). A cancer with enough deaths but no registered70 * trials still belongs to the eligible set (it contributes 0 to Σ trials) — only its ratio is null.71 */72export function eligibility(input: Pick<ComponentInput, 'deaths' | 'activeTrials' | 'publications5y'>, t: ResearchGapThresholds = RESEARCH_GAP_THRESHOLDS): Eligibility {73 if (input.deaths == null || !Number.isFinite(input.deaths)) return { eligible: false, reason: 'no_mortality_observation', trialRatio: false, publicationRatio: false };74 if (input.deaths < t.minDeaths) return { eligible: false, reason: `deaths_below_threshold (${input.deaths} < ${t.minDeaths})`, trialRatio: false, publicationRatio: false };75 return { eligible: true, reason: null, trialRatio: input.activeTrials >= t.minActivity, publicationRatio: input.publications5y >= t.minActivity };76}7778/** log2(a / b), or null when either side is not a positive finite number. */79export function log2Ratio(a: number | null | undefined, b: number | null | undefined): number | null {80 if (a == null || b == null || !Number.isFinite(a) || !Number.isFinite(b) || a <= 0 || b <= 0) return null;81 return round(Math.log2(a / b));82}8384/** activity per 1,000 deaths, or null when deaths are not positive. */85export function per1000Deaths(activity: number, deaths: number | null | undefined): number | null {86 if (deaths == null || !Number.isFinite(deaths) || deaths <= 0 || !Number.isFinite(activity)) return null;87 return round(activity / (deaths / 1000), 4);88}8990/**91 * Shares and log-ratios over the eligible set of one scope (pure, deterministic).92 * death_share = deaths / Σ deaths; trial_share = active_trials / Σ active_trials;93 * publication_share = publications_5y / Σ publications_5y — all sums over eligible cancers only.94 * Shares are rounded to 6 decimals; per-1,000 values to 4.95 */96export function computeShares(inputs: ComponentInput[], t: ResearchGapThresholds = RESEARCH_GAP_THRESHOLDS): SharesResult {97 const verdicts = new Map(inputs.map((i) => [i.id, eligibility(i, t)]));98 const eligible = inputs.filter((i) => verdicts.get(i.id)!.eligible);99 const sums = {100 deaths: eligible.reduce((s, i) => s + (i.deaths ?? 0), 0),101 activeTrials: eligible.reduce((s, i) => s + i.activeTrials, 0),102 publications5y: eligible.reduce((s, i) => s + i.publications5y, 0),103 eligible: eligible.length,104 };105 const share = (v: number, total: number): number | null => (total > 0 ? round(v / total) : null);106 const rows: ComponentShares[] = inputs.map((i) => {107 const e = verdicts.get(i.id)!;108 if (!e.eligible) {109 return { id: i.id, ...e, deathShare: null, trialShare: null, publicationShare: null, trialGapRatio: null, researchGapRatio: null, trialsPer1000Deaths: per1000Deaths(i.activeTrials, i.deaths), publicationsPer1000Deaths: per1000Deaths(i.publications5y, i.deaths) };110 }111 const deathShare = share(i.deaths!, sums.deaths);112 const trialShare = share(i.activeTrials, sums.activeTrials);113 const publicationShare = share(i.publications5y, sums.publications5y);114 return {115 id: i.id,116 ...e,117 deathShare,118 trialShare,119 publicationShare,120 // Ratios use unrounded shares (equivalently deaths·Σtrials / (trials·Σdeaths)) so rounding of shares never leaks into the index.121 trialGapRatio: e.trialRatio ? log2Ratio(i.deaths! / sums.deaths, i.activeTrials / sums.activeTrials) : null,122 researchGapRatio: e.publicationRatio ? log2Ratio(i.deaths! / sums.deaths, i.publications5y / sums.publications5y) : null,123 trialsPer1000Deaths: per1000Deaths(i.activeTrials, i.deaths),124 publicationsPer1000Deaths: per1000Deaths(i.publications5y, i.deaths),125 };126 });127 return { rows, sums };128}129130type ScopeRow = {131 geography_id: string;132 slug: string;133 iso3: string | null;134 year: number;135 sex: string;136 source_id: string;137 n: number;138};139140type ObsRow = {141 cancer_id: string;142 metric: string;143 value: number;144 id: number;145 estimate_type: string;146 site_definition: string | null;147};148149type CounterRow = {150 entity_id: string;151 active_trial_count: number;152 phase3_trial_count: number;153 publication_count_5y: number;154 approved_drug_count: number;155 updated_at: Date | string;156};157158type LitRow = {159 cancer_id: string;160 count: number;161 id: number;162 query: string;163 updated_at: Date | string;164};165166/**167 * Research Gap Index (SPEC §34, §113): burden vs research activity per top-level cancer and burden168 * scope. Scope discovery follows the ranking engine (every geography/year/sex/source with ≥ 10169 * top-level cancers carrying a `mortality_count` observation, age group "all"). For each scope the170 * component rows are rebuilt in one transaction, then four ranking snapshots are persisted through171 * the shared engine so `/rankings/<slug>` pages, "Why this rank?" and TRACE work unchanged.172 */173export async function computeResearchGap(db: Database): Promise<ResearchGapResult> {174 const t = RESEARCH_GAP_THRESHOLDS;175 const defs = await db.select().from(metricDefinitions);176 const byslug = new Map(defs.map((d) => [d.slug, d]));177 const activitySources = await db.execute<{ id: string; slug: string }>(sql`SELECT id, slug FROM sources WHERE slug IN ('clinicaltrials', 'pubmed')`);178 const srcId = (slug: string) => activitySources.find((s) => s.slug === slug)?.id ?? slug;179 const trialsSourceId = srcId('clinicaltrials');180 const pubmedSourceId = srcId('pubmed');181182 const [counters, lit] = await Promise.all([183 db.execute<CounterRow>(sql`SELECT entity_id, active_trial_count, phase3_trial_count, publication_count_5y, approved_drug_count, updated_at FROM entity_counters WHERE entity_type = 'cancer'`),184 db.execute<LitRow>(sql`SELECT cancer_id, count, id, query, updated_at FROM literature_counts WHERE window_key = '5y'`),185 ]);186 const cmap = new Map(counters.map((c) => [c.entity_id, c]));187 const lmap = new Map(lit.map((l) => [l.cancer_id, l]));188189 const scopes = await db.execute<ScopeRow>(sql`190 SELECT o.geography_id, g.slug, g.iso3, o.year, o.sex, o.source_id, count(DISTINCT o.cancer_id) AS n191 FROM epidemiology_observations o JOIN geographies g ON g.id = o.geography_id JOIN cancers c ON c.id = o.cancer_id192 WHERE c.top_level AND c.status = 'active' AND o.age_group = 'all' AND o.metric = 'mortality_count'193 GROUP BY o.geography_id, g.slug, g.iso3, o.year, o.sex, o.source_id HAVING count(DISTINCT o.cancer_id) >= ${t.minCancersPerScope}194 ORDER BY o.year, o.sex, o.source_id`);195196 // Ranking snapshots are keyed by (geography, sex, age, year, level) without the burden source, so197 // two sources for the same population would overwrite each other's "current" snapshot. Components198 // are stored for every source; snapshots only for the preferred one per scope key (most cancers199 // with a mortality observation, ties broken by source id) — the same source the web page defaults to.200 const preferred = new Map<string, ScopeRow>();201 for (const s of scopes) {202 const key = `${s.iso3 ?? s.slug.toUpperCase()}|${s.year}|${s.sex}`;203 const cur = preferred.get(key);204 if (!cur || Number(s.n) > Number(cur.n) || (Number(s.n) === Number(cur.n) && s.source_id < cur.source_id)) preferred.set(key, s);205 }206207 let rows = 0;208 let nScopes = 0;209 for (const s of scopes) {210 const geography = s.iso3 ?? s.slug.toUpperCase();211 const year = Number(s.year);212 const obs = await db.execute<ObsRow>(sql`213 SELECT o.cancer_id, o.metric, o.value, o.id, o.estimate_type, o.site_definition214 FROM epidemiology_observations o JOIN cancers c ON c.id = o.cancer_id215 WHERE c.top_level AND c.status = 'active' AND o.geography_id = ${s.geography_id} AND o.year = ${year} AND o.sex = ${s.sex} AND o.age_group = 'all'216 AND o.source_id = ${s.source_id} AND o.metric IN ('mortality_count', 'incidence_count')217 ORDER BY o.id`);218 // First observation per (cancer, metric) is used; any duplicates (other site definitions) are kept in inputs.219 const mort = new Map<string, ObsRow & { otherIds: number[] }>();220 const inc = new Map<string, ObsRow & { otherIds: number[] }>();221 for (const o of obs) {222 const m = o.metric === 'mortality_count' ? mort : inc;223 const cur = m.get(o.cancer_id);224 if (cur) cur.otherIds.push(o.id);225 else m.set(o.cancer_id, { ...o, value: Number(o.value), otherIds: [] });226 }227 if (mort.size < t.minCancersPerScope) continue;228229 const inputs: ComponentInput[] = [...mort.keys()].sort().map((id) => {230 const c = cmap.get(id);231 const l = lmap.get(id);232 const fromCounter = Number(c?.publication_count_5y ?? 0);233 return { id, deaths: mort.get(id)!.value, activeTrials: Number(c?.active_trial_count ?? 0), publications5y: fromCounter > 0 ? fromCounter : Number(l?.count ?? 0) };234 });235 const { rows: shares, sums } = computeShares(inputs, t);236 const byId = new Map(shares.map((r) => [r.id, r]));237238 const values = inputs.map((i) => {239 const r = byId.get(i.id)!;240 const m = mort.get(i.id)!;241 const ic = inc.get(i.id);242 const c = cmap.get(i.id);243 const l = lmap.get(i.id);244 const pubsFrom = Number(c?.publication_count_5y ?? 0) > 0 ? 'entity_counters.publication_count_5y' : l ? 'literature_counts[5y]' : 'none';245 return {246 cancerId: i.id,247 geography,248 year,249 sex: s.sex,250 burdenSourceId: s.source_id,251 deaths: m.value,252 incidence: ic ? ic.value : null,253 activeTrials: i.activeTrials,254 phase3Trials: Number(c?.phase3_trial_count ?? 0),255 publications5y: i.publications5y,256 approvedDrugs: Number(c?.approved_drug_count ?? 0),257 deathShare: r.deathShare,258 trialShare: r.trialShare,259 publicationShare: r.publicationShare,260 trialGapRatio: r.trialGapRatio,261 researchGapRatio: r.researchGapRatio,262 trialsPer1000Deaths: r.trialsPer1000Deaths,263 publicationsPer1000Deaths: r.publicationsPer1000Deaths,264 eligible: r.eligible,265 ineligibleReason: r.reason,266 formulaVersion: RESEARCH_GAP_FORMULA_VERSION,267 inputs: {268 mortalityObservationId: m.id,269 mortalityOtherObservationIds: m.otherIds,270 mortalityEstimateType: m.estimate_type,271 mortalitySiteDefinition: m.site_definition,272 incidenceObservationId: ic?.id ?? null,273 countersComputedAt: c?.updated_at ?? null,274 publicationsSource: pubsFrom,275 literatureCountId: l?.id ?? null,276 literatureQuery: pubsFrom === 'literature_counts[5y]' ? l?.query : undefined,277 thresholds: { minDeaths: t.minDeaths, minActivity: t.minActivity },278 ratioEligibility: { trialGapRatio: r.trialRatio, researchGapRatio: r.publicationRatio },279 sums,280 activitySources: { activeTrials: trialsSourceId, publications5y: pubmedSourceId },281 } as Record<string, unknown>,282 };283 });284285 await db.transaction(async (tx) => {286 await tx.delete(researchGapComponents).where(and(eq(researchGapComponents.geography, geography), eq(researchGapComponents.year, year), eq(researchGapComponents.sex, s.sex), eq(researchGapComponents.burdenSourceId, s.source_id)));287 for (let i = 0; i < values.length; i += 200) await tx.insert(researchGapComponents).values(values.slice(i, i + 200));288 });289 rows += values.length;290 nScopes += 1;291292 // Ranking snapshots (same scope shape as the engine so /rankings/<slug>?scope=… resolves).293 if (preferred.get(`${geography}|${s.year}|${s.sex}`)?.source_id !== s.source_id) continue;294 const scope: Scope = { geography, sex: s.sex as Scope['sex'], ageGroup: 'all', year, entityLevel: 'top' };295 const confidence = (deaths: number) => (deaths >= t.highConfidenceDeaths ? 'HIGH' : 'MEDIUM') as 'HIGH' | 'MEDIUM';296 const base = (v: (typeof values)[number]) => ({297 deaths: v.deaths,298 mortalityObservationId: (v.inputs as { mortalityObservationId: number }).mortalityObservationId,299 burdenSourceId: s.source_id,300 countersComputedAt: (v.inputs as { countersComputedAt: unknown }).countersComputedAt,301 componentsFormulaVersion: RESEARCH_GAP_FORMULA_VERSION,302 thresholds: { minDeaths: t.minDeaths, minActivity: t.minActivity },303 });304 const eligibleRows = values.filter((v) => v.eligible);305 const metricItems: Array<[string, RankInput[], string[]]> = [306 ['trials_per_1000_deaths', eligibleRows.filter((v) => v.trialsPer1000Deaths != null).map((v) => ({ id: v.cancerId, value: v.trialsPer1000Deaths!, confidence: confidence(v.deaths), inputs: { ...base(v), activeTrials: v.activeTrials, formula: byslug.get('trials_per_1000_deaths')?.formula } })), [s.source_id, trialsSourceId]],307 ['publications_per_1000_deaths', eligibleRows.filter((v) => v.publicationsPer1000Deaths != null).map((v) => ({ id: v.cancerId, value: v.publicationsPer1000Deaths!, confidence: confidence(v.deaths), inputs: { ...base(v), publications5y: v.publications5y, formula: byslug.get('publications_per_1000_deaths')?.formula } })), [s.source_id, pubmedSourceId]],308 ['trial_gap_ratio', eligibleRows.filter((v) => v.trialGapRatio != null).map((v) => ({ id: v.cancerId, value: v.trialGapRatio!, confidence: confidence(v.deaths), inputs: { ...base(v), activeTrials: v.activeTrials, deathShare: v.deathShare, trialShare: v.trialShare, sumDeaths: sums.deaths, sumActiveTrials: sums.activeTrials, eligibleEntities: sums.eligible, formula: byslug.get('trial_gap_ratio')?.formula } })), [s.source_id, trialsSourceId]],309 ['research_gap_ratio', eligibleRows.filter((v) => v.researchGapRatio != null).map((v) => ({ id: v.cancerId, value: v.researchGapRatio!, confidence: confidence(v.deaths), inputs: { ...base(v), publications5y: v.publications5y, deathShare: v.deathShare, publicationShare: v.publicationShare, sumDeaths: sums.deaths, sumPublications5y: sums.publications5y, eligibleEntities: sums.eligible, formula: byslug.get('research_gap_ratio')?.formula } })), [s.source_id, pubmedSourceId]],310 ];311 for (const [slug, items, sourceIds] of metricItems) {312 const def = byslug.get(slug);313 if (!def || items.length < 3) continue;314 await persistSnapshot(db, def, scope, items, { descending: true, sourceIds });315 }316 }317 return { rows, scopes: nScopes };318}319