spb/cancerindex

1import { and, eq, sql } from 'drizzle-orm';2import { type Database, metricDefinitions, rankingSnapshots, rankings, entityCounters, cancers } from '@cancerindex/database';3import { rankEntities, inputsHash, percentiles, type RankInput } from './rank.js';45export interface Scope {6  geography: string; // WORLD | ISO3 | slug7  sex: 'all' | 'male' | 'female';8  ageGroup: string;9  year: number | null;10  entityLevel: 'top' | 'all';11}1213export function scopeKey(s: Scope): string {14  return `geo=${s.geography}|sex=${s.sex}|age=${s.ageGroup}|year=${s.year ?? 'latest'}|level=${s.entityLevel}`;15}1617export interface RankingResult {18  metricSlug: string;19  scopeKey: string;20  eligible: number;21  snapshotId: number;22}2324/**25 * Ranking engine (CLAUDE.md §30-35, §245-247, §288). Every snapshot stores scope, formula version,26 * an inputs hash and per-row lineage (`inputs`) so "Why #4?" and TRACE can be answered.27 *28 * Phase 1 ships count-based metrics (trials, literature, molecular) for both entity levels and the29 * burden/lethality/gap metrics only where epidemiology observations exist for the scope.30 */31export async function computeAllRankings(db: Database): Promise<RankingResult[]> {32  const results: RankingResult[] = [];33  const defs = await db.select().from(metricDefinitions);34  const byslug = new Map(defs.map((d) => [d.slug, d]));3536  const countMetrics: Array<[string, keyof typeof entityCounters.$inferSelect]> = [37    ['active_trials', 'activeTrialCount'],38    ['recruiting_trials', 'recruitingTrialCount'],39    ['phase3_trials', 'phase3TrialCount'],40    ['publications_5y', 'publicationCount5y'],41    ['publications_12m', 'publicationCount12m'],42    ['curated_evidence_items', 'evidenceCount'],43    ['associated_genes', 'geneCount'],44    ['genomic_cohorts', 'cohortCount'],45  ];4647  for (const level of ['top', 'all'] as const) {48    const scope: Scope = { geography: 'WORLD', sex: 'all', ageGroup: 'all', year: null, entityLevel: level };49    const counters = await db50      .select({ id: cancers.id, c: entityCounters })51      .from(cancers)52      .innerJoin(entityCounters, and(eq(entityCounters.entityType, 'cancer'), eq(entityCounters.entityId, cancers.id)))53      .where(level === 'top' ? and(eq(cancers.status, 'active'), eq(cancers.topLevel, true)) : and(eq(cancers.status, 'active'), eq(cancers.malignant, true)));54    for (const [slug, field] of countMetrics) {55      const def = byslug.get(slug);56      if (!def) continue;57      const items: RankInput[] = counters58        .map((r) => ({ id: r.id, value: Number(r.c[field] ?? 0), inputs: { counter: field, computedAt: r.c.computedAt } }))59        .filter((i) => (slug.startsWith('publications') ? i.value > 0 : true)); // publications: only cancers with a stored PubMed query60      if (items.length === 0) continue;61      const snap = await persistSnapshot(db, def, scope, items, { descending: true, sourceIds: def.sourceSlugs });62      results.push(snap);63    }64    // Publication growth (derived from two windows).65    const growthDef = byslug.get('publication_growth');66    if (growthDef) {67      const rows = await db.execute<{ cancer_id: string; m12: number; y5: number }>(sql`68        SELECT cancer_id, max(count) FILTER (WHERE window_key = '12m') AS m12, max(count) FILTER (WHERE window_key = '5y_prior') AS y569        FROM literature_counts GROUP BY cancer_id`);70      const ids = new Set(counters.map((c) => c.id));71      const items: RankInput[] = rows72        .filter((r) => ids.has(r.cancer_id) && r.y5 != null && r.m12 != null && Number(r.y5) >= 50)73        .map((r) => ({ id: r.cancer_id, value: Number(r.m12) / (Number(r.y5) / 5), inputs: { publications_12m: Number(r.m12), publications_5y_prior: Number(r.y5), formula: growthDef.formula } }));74      if (items.length) results.push(await persistSnapshot(db, growthDef, scope, items, { descending: true, sourceIds: ['pubmed'] }));75    }76  }7778  // Burden / lethality / gaps per geography-year where observations exist (top level only, §246-247).79  const scopes = await db.execute<{ geography_id: string; slug: string; iso3: string | null; year: number; sex: string; source_id: string; n: number }>(sql`80    SELECT o.geography_id, g.slug, g.iso3, o.year, o.sex, o.source_id, count(*) AS n81    FROM epidemiology_observations o JOIN geographies g ON g.id = o.geography_id JOIN cancers c ON c.id = o.cancer_id82    WHERE c.top_level AND o.age_group = 'all' AND o.metric IN ('incidence_count','mortality_count','as_incidence_rate','as_mortality_rate')83    GROUP BY o.geography_id, g.slug, g.iso3, o.year, o.sex, o.source_id HAVING count(*) >= 10`);84  for (const s of scopes) {85    const scope: Scope = { geography: s.iso3 ?? s.slug.toUpperCase(), sex: s.sex as Scope['sex'], ageGroup: 'all', year: Number(s.year), entityLevel: 'top' };86    const obs = await db.execute<{ cancer_id: string; metric: string; value: number; id: number; estimate_type: string; site_definition: string | null }>(sql`87      SELECT o.cancer_id, o.metric, o.value, o.id, o.estimate_type, o.site_definition FROM epidemiology_observations o JOIN cancers c ON c.id = o.cancer_id88      WHERE c.top_level AND o.geography_id = ${s.geography_id} AND o.year = ${s.year} AND o.sex = ${s.sex} AND o.age_group = 'all' AND o.source_id = ${s.source_id}`);89    const byMetric = new Map<string, Map<string, { value: number; id: number; estimate_type: string; site: string | null }>>();90    for (const o of obs) {91      if (!byMetric.has(o.metric)) byMetric.set(o.metric, new Map());92      byMetric.get(o.metric)!.set(o.cancer_id, { value: Number(o.value), id: o.id, estimate_type: o.estimate_type, site: o.site_definition });93    }94    for (const metric of ['incidence_count', 'mortality_count', 'as_incidence_rate', 'as_mortality_rate']) {95      const def = byslug.get(metric);96      const m = byMetric.get(metric);97      if (!def || !m) continue;98      const items: RankInput[] = [...m.entries()].map(([id, v]) => ({ id, value: v.value, confidence: v.estimate_type === 'observed' ? 'HIGH' : 'MEDIUM', inputs: { observationId: v.id, estimateType: v.estimate_type, siteDefinition: v.site, sourceId: s.source_id } }));99      results.push(await persistSnapshot(db, def, scope, items, { descending: true, sourceIds: [s.source_id] }));100    }101    // Mortality-to-incidence ratio102    const mirDef = byslug.get('mortality_incidence_ratio');103    const inc = byMetric.get('incidence_count');104    const mort = byMetric.get('mortality_count');105    if (mirDef && inc && mort) {106      const items: RankInput[] = [];107      for (const [id, i] of inc) {108        const d = mort.get(id);109        if (!d || i.value < 100) continue;110        items.push({ id, value: d.value / i.value, confidence: i.value >= 1000 ? 'HIGH' : 'MEDIUM', inputs: { incidenceObservationId: i.id, mortalityObservationId: d.id, incidence: i.value, deaths: d.value, formula: mirDef.formula } });111      }112      if (items.length) results.push(await persistSnapshot(db, mirDef, scope, items, { descending: true, sourceIds: [s.source_id] }));113    }114    // Gap indexes: burden percentile − activity percentile115    if (mort) {116      const burdenPct = percentiles([...mort.entries()].map(([id, v]) => ({ id, value: v.value })));117      const counters = await db.select({ id: entityCounters.entityId, trials: entityCounters.activeTrialCount, pubs: entityCounters.publicationCount5y }).from(entityCounters).where(eq(entityCounters.entityType, 'cancer'));118      const cmap = new Map(counters.map((c) => [c.id, c]));119      for (const [slug, field] of [120        ['trial_gap', 'trials'],121        ['research_gap', 'pubs'],122      ] as const) {123        const def = byslug.get(slug);124        if (!def) continue;125        const ids = [...mort.keys()].filter((id) => cmap.has(id) && (field === 'trials' || (cmap.get(id)!.pubs ?? 0) > 0));126        if (ids.length < 5) continue;127        const actPct = percentiles(ids.map((id) => ({ id, value: Number(cmap.get(id)![field] ?? 0) })));128        const items: RankInput[] = ids.map((id) => ({ id, value: (burdenPct.get(id) ?? 0) - (actPct.get(id) ?? 0), inputs: { burdenPercentile: burdenPct.get(id), activityPercentile: actPct.get(id), deaths: mort.get(id)!.value, activity: Number(cmap.get(id)![field] ?? 0), formula: def.formula } }));129        results.push(await persistSnapshot(db, def, scope, items, { descending: true, sourceIds: [s.source_id, ...def.sourceSlugs] }));130      }131    }132  }133  return results;134}135136async function persistSnapshot(db: Database, def: typeof metricDefinitions.$inferSelect, scope: Scope, items: RankInput[], opts: { descending: boolean; sourceIds: string[] }): Promise<RankingResult> {137  const key = scopeKey(scope);138  const ranked = rankEntities(items, { descending: def.higherIsWorse === false ? opts.descending : opts.descending });139  const hash = await inputsHash(items);140  return db.transaction(async (tx) => {141    // Previous rank for change explanation (§311)142    const prev = await tx.execute<{ cancer_id: string; rank: number }>(sql`143      SELECT r.cancer_id, r.rank FROM rankings r JOIN ranking_snapshots s ON s.id = r.snapshot_id144      WHERE s.metric_slug = ${def.slug} AND s.scope_key = ${key} AND s.is_current`);145    const prevMap = new Map(prev.map((p) => [p.cancer_id, Number(p.rank)]));146    await tx.update(rankingSnapshots).set({ isCurrent: false }).where(and(eq(rankingSnapshots.metricSlug, def.slug), eq(rankingSnapshots.scopeKey, key), eq(rankingSnapshots.isCurrent, true)));147    const [snap] = await tx148      .insert(rankingSnapshots)149      .values({ metricId: def.id, metricSlug: def.slug, scopeKey: key, geography: scope.geography, sex: scope.sex, ageGroup: scope.ageGroup, year: scope.year, entityLevel: scope.entityLevel, formulaVersion: def.formulaVersion, eligibleEntities: ranked.length, inputsHash: hash, sourceIds: opts.sourceIds, isCurrent: true })150      .returning({ id: rankingSnapshots.id });151    const snapshotId = snap!.id;152    const chunk = 500;153    for (let i = 0; i < ranked.length; i += chunk) {154      await tx.insert(rankings).values(155        ranked.slice(i, i + chunk).map((r) => ({156          snapshotId,157          metricSlug: def.slug,158          scopeKey: key,159          cancerId: r.id,160          rank: r.rank,161          eligibleEntities: r.eligible,162          percentile: r.percentile,163          value: r.value,164          unit: def.unit,165          confidence: r.confidence ?? 'MEDIUM',166          inputs: r.inputs ?? {},167          previousRank: prevMap.get(r.id) ?? null,168        })),169      );170    }171    return { metricSlug: def.slug, scopeKey: key, eligible: ranked.length, snapshotId };172  });173}174