spb/cancerindex

1import { sql } from 'drizzle-orm';2import type { FastifyPluginAsyncZod } from 'fastify-type-provider-zod';3import { z } from 'zod';4import { RESEARCH_GAP_FORMULA_VERSION, RESEARCH_GAP_THRESHOLDS } from '@cancerindex/ranking';5import { NotFound } from '../lib/errors.js';6import { AnyRecord, AnyList, camelRows, num, ok, respond } from '../lib/respond.js';78const scopeQuery = z.object({9  geography: z.string().default('USA').describe('ISO3 of the burden scope (WORLD once licensed global estimates exist)'),10  year: z.coerce.number().int().optional().describe('Burden year; omitted = latest year available for the geography and sex'),11  sex: z.enum(['all', 'male', 'female']).default('all'),12  source: z.string().optional().describe('Burden source (slug or CI-SOURCE id); omitted = the source with the most component rows in the scope'),13});1415type ScopeRow = {16  geography: string;17  year: number;18  sex: string;19  burden_source_id: string;20  source_slug: string;21  source_name: string;22  n_total: number;23  n_eligible: number;24  formula_version: string;25  computed_at: Date | string;26};2728const SCOPES_SQL = sql`29  SELECT r.geography, r.year, r.sex, r.burden_source_id, s.slug AS source_slug, s.name AS source_name,30         count(*)::int AS n_total, count(*) FILTER (WHERE r.eligible)::int AS n_eligible,31         max(r.formula_version) AS formula_version, max(r.updated_at) AS computed_at32  FROM research_gap_components r JOIN sources s ON s.id = r.burden_source_id33  GROUP BY r.geography, r.year, r.sex, r.burden_source_id, s.slug, s.name`;3435/**36 * Research Gap routes (SPEC §34, §113): `GET /research-gap` → components (deaths, trials,37 * publications, shares, log-ratios, per-1,000 values) per top-level cancer for one burden scope;38 * `GET /research-gap/scopes` → the scopes with computed components. Everything here is a39 * computed metric (formula version on every row); it is a signal about burden vs registered40 * research activity, not a judgement of research quality or funding.41 */42export const researchGapRoutes: FastifyPluginAsyncZod = async (app) => {43  app.get('/research-gap/scopes', { schema: { tags: ['intelligence'], summary: 'Burden scopes (geography, year, sex, source) with research-gap components and their eligible counts', response: ok(AnyList) } }, async () => {44    const rows = await app.db.execute<ScopeRow>(sql`${SCOPES_SQL} ORDER BY r.geography, r.year DESC, r.sex, s.slug`);45    const data = camelRows(rows).map((r) => ({ ...r, scopeKey: `geo=${r.geography as string}|sex=${r.sex as string}|age=all|year=${r.year as number}|level=top` }));46    return respond(app, data, rows.map((r) => r.burden_source_id));47  });4849  app.get('/research-gap', { schema: { tags: ['intelligence'], summary: 'Research-gap components for one burden scope: deaths, active trials, publications (5 y), shares over the eligible set, log2 gap ratios and per-1,000-deaths intensities per top-level cancer', querystring: scopeQuery, response: ok(AnyRecord) } }, async (req) => {50    const q = req.query;51    const geography = q.geography.toUpperCase();52    const scopes = await app.db.execute<ScopeRow>(sql`${SCOPES_SQL} HAVING upper(r.geography) = ${geography} AND r.sex = ${q.sex}53      ${q.year !== undefined ? sql`AND r.year = ${q.year}` : sql``}54      ${q.source ? sql`AND (s.slug = ${q.source} OR r.burden_source_id = ${q.source})` : sql``}55      ORDER BY r.year DESC, count(*) DESC, s.slug LIMIT 1`);56    const scope = scopes[0];57    if (!scope) {58      const any = await app.db.execute<{ n: string }>(sql`SELECT count(*)::text AS n FROM research_gap_components`);59      if (num(any[0]?.n) === 0) return respond(app, { scope: null, rows: [], status: 'not_available', message: 'Research-gap components have not been computed yet (run `pnpm cix intel`).' }, []);60      throw new NotFound('research-gap scope', `geography=${geography} sex=${q.sex} year=${q.year ?? 'latest'} source=${q.source ?? 'any'}`);61    }62    const scopeKey = `geo=${scope.geography}|sex=${scope.sex}|age=all|year=${scope.year}|level=top`;63    const rows = await app.db.execute<Record<string, unknown>>(sql`64      SELECT r.id AS component_id, r.cancer_id, c.slug, c.canonical_name AS name, c.short_name, c.hematologic,65             r.deaths, r.incidence, r.active_trials, r.phase3_trials, r.publications_5y, r.approved_drugs,66             r.death_share, r.trial_share, r.publication_share, r.trial_gap_ratio, r.research_gap_ratio,67             r.trials_per_1000_deaths, r.publications_per_1000_deaths, r.eligible, r.ineligible_reason,68             r.formula_version, r.inputs, r.updated_at AS computed_at,69             tg.rank AS trial_gap_rank, rg.rank AS research_gap_rank70      FROM research_gap_components r JOIN cancers c ON c.id = r.cancer_id71      LEFT JOIN rankings tg ON tg.cancer_id = r.cancer_id AND tg.metric_slug = 'trial_gap_ratio' AND tg.scope_key = ${scopeKey}72        AND tg.snapshot_id = (SELECT id FROM ranking_snapshots WHERE metric_slug = 'trial_gap_ratio' AND scope_key = ${scopeKey} AND is_current AND ${scope.burden_source_id} = ANY(source_ids) LIMIT 1)73      LEFT JOIN rankings rg ON rg.cancer_id = r.cancer_id AND rg.metric_slug = 'research_gap_ratio' AND rg.scope_key = ${scopeKey}74        AND rg.snapshot_id = (SELECT id FROM ranking_snapshots WHERE metric_slug = 'research_gap_ratio' AND scope_key = ${scopeKey} AND is_current AND ${scope.burden_source_id} = ANY(source_ids) LIMIT 1)75      WHERE r.geography = ${scope.geography} AND r.year = ${scope.year} AND r.sex = ${scope.sex} AND r.burden_source_id = ${scope.burden_source_id}76      ORDER BY r.eligible DESC, r.research_gap_ratio DESC NULLS LAST, c.canonical_name`);77    const eligible = rows.filter((r) => r.eligible === true);78    const sums = {79      deaths: eligible.reduce((s, r) => s + num(r.deaths), 0),80      activeTrials: eligible.reduce((s, r) => s + num(r.active_trials), 0),81      publications5y: eligible.reduce((s, r) => s + num(r.publications_5y), 0),82      eligible: eligible.length,83    };84    const metrics = await app.db.execute<{ slug: string; name: string; formula: string; formula_version: string; unit: string }>(sql`85      SELECT slug, name, formula, formula_version, unit FROM metric_definitions WHERE slug IN ('trial_gap_ratio','research_gap_ratio','trials_per_1000_deaths','publications_per_1000_deaths') ORDER BY slug`);86    const data = {87      scope: {88        geography: scope.geography,89        year: Number(scope.year),90        sex: scope.sex,91        ageGroup: 'all',92        entityLevel: 'top',93        scopeKey,94        burdenSource: { id: scope.burden_source_id, slug: scope.source_slug, name: scope.source_name },95        activitySources: { activeTrials: 'clinicaltrials', publications5y: 'pubmed' },96        formulaVersion: scope.formula_version ?? RESEARCH_GAP_FORMULA_VERSION,97        computedAt: scope.computed_at,98        totalEntities: Number(scope.n_total),99        eligibleEntities: Number(scope.n_eligible),100        sums,101      },102      thresholds: { minDeaths: RESEARCH_GAP_THRESHOLDS.minDeaths, minActivity: RESEARCH_GAP_THRESHOLDS.minActivity, highConfidenceDeaths: RESEARCH_GAP_THRESHOLDS.highConfidenceDeaths },103      metrics: camelRows(metrics).map((m) => ({ ...m, ranking: `/v1/rankings?metric=${m.slug as string}&geography=${scope.geography}&sex=${scope.sex}&year=${scope.year}&level=top` })),104      claim: 'computed_metric',105      notes: [106        'Shares are computed over the eligible top-level cancers of the scope (deaths ≥ minDeaths); a log2 ratio of +1 means the cancer has twice the share of deaths that its share of trials (or publications) would suggest, −1 half.',107        'Trial counts aggregate a cancer and its NCIt descendants; literature counts are query-based per entity and not aggregated over descendants. Burden depends on the epidemiology source of the scope. A gap is a quantitative signal, not an accusation.',108      ],109      rows: rows.map((r) => ({110        componentId: r.component_id,111        cancer: { id: r.cancer_id, slug: r.slug, name: r.name, shortName: r.short_name, hematologic: r.hematologic },112        deaths: r.deaths,113        incidence: r.incidence,114        activeTrials: r.active_trials,115        phase3Trials: r.phase3_trials,116        publications5y: r.publications_5y,117        approvedDrugs: r.approved_drugs,118        deathShare: r.death_share,119        trialShare: r.trial_share,120        publicationShare: r.publication_share,121        trialGapRatio: r.trial_gap_ratio,122        researchGapRatio: r.research_gap_ratio,123        trialsPer1000Deaths: r.trials_per_1000_deaths,124        publicationsPer1000Deaths: r.publications_per_1000_deaths,125        ranks: { trialGapRatio: r.trial_gap_rank, researchGapRatio: r.research_gap_rank },126        eligible: r.eligible,127        ineligibleReason: r.ineligible_reason,128        formulaVersion: r.formula_version,129        inputs: r.inputs,130        computedAt: r.computed_at,131      })),132    };133    return respond(app, data, [scope.burden_source_id, 'clinicaltrials', 'pubmed']);134  });135};136