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        // Count metrics rank only entities with at least one observed item: a shared rank of 1 among60        // hundreds of zeros carries no information (CLAUDE.md §245 eligibility rules).61        .filter((i) => i.value > 0);62      if (items.length < 3) continue;63      const snap = await persistSnapshot(db, def, scope, items, { descending: true, sourceIds: def.sourceSlugs });64      results.push(snap);65    }66    // Publication growth (derived from two windows).67    const growthDef = byslug.get('publication_growth');68    if (growthDef) {69      const rows = await db.execute<{ cancer_id: string; m12: number; y5: number }>(sql`70        SELECT cancer_id, max(count) FILTER (WHERE window_key = '12m') AS m12, max(count) FILTER (WHERE window_key = '5y_prior') AS y571        FROM literature_counts GROUP BY cancer_id`);72      const ids = new Set(counters.map((c) => c.id));73      const items: RankInput[] = rows74        .filter((r) => ids.has(r.cancer_id) && r.y5 != null && r.m12 != null && Number(r.y5) >= 50)75        .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 } }));76      if (items.length) results.push(await persistSnapshot(db, growthDef, scope, items, { descending: true, sourceIds: ['pubmed'] }));77    }78  }7980  // Burden / lethality / gaps per geography-year where observations exist (top level only, §246-247).81  const scopes = await db.execute<{ geography_id: string; slug: string; iso3: string | null; year: number; sex: string; source_id: string; n: number }>(sql`82    SELECT o.geography_id, g.slug, g.iso3, o.year, o.sex, o.source_id, count(*) AS n83    FROM epidemiology_observations o JOIN geographies g ON g.id = o.geography_id JOIN cancers c ON c.id = o.cancer_id84    WHERE c.top_level AND o.age_group = 'all' AND o.metric IN ('incidence_count','mortality_count','as_incidence_rate','as_mortality_rate')85    GROUP BY o.geography_id, g.slug, g.iso3, o.year, o.sex, o.source_id HAVING count(*) >= 10`);86  for (const s of scopes) {87    const scope: Scope = { geography: s.iso3 ?? s.slug.toUpperCase(), sex: s.sex as Scope['sex'], ageGroup: 'all', year: Number(s.year), entityLevel: 'top' };88    const obs = await db.execute<{ cancer_id: string; metric: string; value: number; id: number; estimate_type: string; site_definition: string | null }>(sql`89      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_id90      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}`);91    const byMetric = new Map<string, Map<string, { value: number; id: number; estimate_type: string; site: string | null }>>();92    for (const o of obs) {93      if (!byMetric.has(o.metric)) byMetric.set(o.metric, new Map());94      byMetric.get(o.metric)!.set(o.cancer_id, { value: Number(o.value), id: o.id, estimate_type: o.estimate_type, site: o.site_definition });95    }96    for (const metric of ['incidence_count', 'mortality_count', 'as_incidence_rate', 'as_mortality_rate']) {97      const def = byslug.get(metric);98      const m = byMetric.get(metric);99      if (!def || !m) continue;100      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 } }));101      results.push(await persistSnapshot(db, def, scope, items, { descending: true, sourceIds: [s.source_id] }));102    }103    // Mortality-to-incidence ratio104    const mirDef = byslug.get('mortality_incidence_ratio');105    const inc = byMetric.get('incidence_count');106    const mort = byMetric.get('mortality_count');107    if (mirDef && inc && mort) {108      const items: RankInput[] = [];109      for (const [id, i] of inc) {110        const d = mort.get(id);111        if (!d || i.value < 100) continue;112        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 } });113      }114      if (items.length) results.push(await persistSnapshot(db, mirDef, scope, items, { descending: true, sourceIds: [s.source_id] }));115    }116    // Gap indexes: burden percentile − activity percentile117    if (mort) {118      const burdenPct = percentiles([...mort.entries()].map(([id, v]) => ({ id, value: v.value })));119      const counters = await db.select({ id: entityCounters.entityId, trials: entityCounters.activeTrialCount, pubs: entityCounters.publicationCount5y }).from(entityCounters).where(eq(entityCounters.entityType, 'cancer'));120      const cmap = new Map(counters.map((c) => [c.id, c]));121      for (const [slug, field] of [122        ['trial_gap', 'trials'],123        ['research_gap', 'pubs'],124      ] as const) {125        const def = byslug.get(slug);126        if (!def) continue;127        const ids = [...mort.keys()].filter((id) => cmap.has(id) && (field === 'trials' || (cmap.get(id)!.pubs ?? 0) > 0));128        if (ids.length < 5) continue;129        const actPct = percentiles(ids.map((id) => ({ id, value: Number(cmap.get(id)![field] ?? 0) })));130        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 } }));131        results.push(await persistSnapshot(db, def, scope, items, { descending: true, sourceIds: [s.source_id, ...def.sourceSlugs] }));132      }133    }134  }135  return results;136}137138export async function persistSnapshot(db: Database, def: typeof metricDefinitions.$inferSelect, scope: Scope, items: RankInput[], opts: { descending: boolean; sourceIds: string[] }): Promise<RankingResult> {139  const key = scopeKey(scope);140  const ranked = rankEntities(items, { descending: def.higherIsWorse === false ? opts.descending : opts.descending });141  const hash = await inputsHash(items);142  return db.transaction(async (tx) => {143    // Previous rank for change explanation (§311)144    const prev = await tx.execute<{ cancer_id: string; rank: number }>(sql`145      SELECT r.cancer_id, r.rank FROM rankings r JOIN ranking_snapshots s ON s.id = r.snapshot_id146      WHERE s.metric_slug = ${def.slug} AND s.scope_key = ${key} AND s.is_current`);147    const prevMap = new Map(prev.map((p) => [p.cancer_id, Number(p.rank)]));148    await tx.update(rankingSnapshots).set({ isCurrent: false }).where(and(eq(rankingSnapshots.metricSlug, def.slug), eq(rankingSnapshots.scopeKey, key), eq(rankingSnapshots.isCurrent, true)));149    const [snap] = await tx150      .insert(rankingSnapshots)151      .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 })152      .returning({ id: rankingSnapshots.id });153    const snapshotId = snap!.id;154    const chunk = 500;155    for (let i = 0; i < ranked.length; i += chunk) {156      await tx.insert(rankings).values(157        ranked.slice(i, i + chunk).map((r) => ({158          snapshotId,159          metricSlug: def.slug,160          scopeKey: key,161          cancerId: r.id,162          rank: r.rank,163          eligibleEntities: r.eligible,164          percentile: r.percentile,165          value: r.value,166          unit: def.unit,167          confidence: r.confidence ?? 'MEDIUM',168          inputs: r.inputs ?? {},169          previousRank: prevMap.get(r.id) ?? null,170        })),171      );172    }173    return { metricSlug: def.slug, scopeKey: key, eligible: ranked.length, snapshotId };174  });175}176