spb/cancerindex

1import { sql } from 'drizzle-orm';2import type { FastifyPluginAsyncZod } from 'fastify-type-provider-zod';3import { z } from 'zod';4import { traceValue } from '@cancerindex/ranking';5import { NotFound } from '../lib/errors.js';6import { paginate } from '../lib/envelope.js';7import { pageQuery } from '../lib/pagination.js';8import { resolveCancer } from '../lib/resolve.js';9import { AnyList, AnyRecord, camel, camelRows, num, ok, respond } from '../lib/respond.js';1011const scopeQuery = {12  geography: z.string().default('WORLD').describe('WORLD or ISO3 / geography slug (upper-cased)'),13  sex: z.enum(['all', 'male', 'female']).default('all'),14  age: z.string().default('all').describe('Age group key (all)'),15  year: z.coerce.number().int().optional().describe('Reference year; omitted = latest available (count metrics have no year)'),16  level: z.enum(['top', 'all']).default('top').describe('top = mutually exclusive ranking set (§247); all = every active malignant entity'),17};1819/** Pick the current snapshot for a metric + scope; without `year` prefer the most recent year, then year-less snapshots. */20async function findSnapshot(app: Parameters<FastifyPluginAsyncZod>[0], metric: string, s: { geography: string; sex: string; age: string; year?: number; level: string }) {21  const rows = await app.db.execute<Record<string, unknown>>(sql`22    SELECT s.*, m.name AS metric_name, m.description AS metric_description, m.formula, m.unit, m.higher_is_worse, m.category, m.aggregation, m.eligibility, m.experimental, m.source_slugs23    FROM ranking_snapshots s JOIN metric_definitions m ON m.slug = s.metric_slug24    WHERE s.metric_slug = ${metric} AND s.is_current AND upper(s.geography) = ${s.geography.toUpperCase()} AND s.sex = ${s.sex} AND s.age_group = ${s.age} AND s.entity_level = ${s.level}25      ${s.year !== undefined ? sql`AND s.year = ${s.year}` : sql``}26    ORDER BY s.year DESC NULLS LAST, s.generated_at DESC LIMIT 1`);27  return rows[0] ?? null;28}2930export const rankingRoutes: FastifyPluginAsyncZod = async (app) => {31  app.get('/rankings/metrics', { schema: { tags: ['rankings'], summary: 'Metric catalog: formula, version, unit, eligibility and the scopes with a current snapshot', response: ok(AnyList) } }, async () => {32    const rows = await app.db.execute<Record<string, unknown>>(sql`33      SELECT m.*, coalesce(json_agg(json_build_object('scopeKey', s.scope_key, 'geography', s.geography, 'sex', s.sex, 'ageGroup', s.age_group, 'year', s.year, 'entityLevel', s.entity_level, 'eligibleEntities', s.eligible_entities, 'generatedAt', s.generated_at, 'inputsHash', s.inputs_hash) ORDER BY s.entity_level, s.geography, s.year DESC) FILTER (WHERE s.id IS NOT NULL), '[]'::json) AS scopes34      FROM metric_definitions m LEFT JOIN ranking_snapshots s ON s.metric_id = m.id AND s.is_current35      GROUP BY m.id ORDER BY m.category, m.slug`);36    const data = camelRows(rows);37    return respond(app, data, rows.flatMap((r) => (r.source_slugs as string[]) ?? []));38  });3940  app.get('/rankings', { schema: { tags: ['rankings'], summary: 'Current ranking snapshot for a metric and scope, with snapshot metadata and the metric definition (§33, §179)', querystring: z.object({ metric: z.string().default('active_trials'), ...scopeQuery, ...pageQuery }), response: ok(AnyRecord, true) } }, async (req) => {41    const q = req.query;42    const snap = await findSnapshot(app, q.metric, q);43    if (!snap) {44      const def = await app.db.execute<Record<string, unknown>>(sql`SELECT * FROM metric_definitions WHERE slug = ${q.metric}`);45      if (!def[0]) throw new NotFound('metric', q.metric);46      // Metric exists but no snapshot for this scope: "Data not yet available" (never an empty fake ranking).47      return respond(app, { metric: camel(def[0]), snapshot: null, rows: [], status: 'not_available', message: `No current ranking for ${q.metric} in scope geo=${q.geography} sex=${q.sex} age=${q.age} year=${q.year ?? 'latest'} level=${q.level}.` }, (def[0].source_slugs as string[]) ?? [], paginate(0, q.limit, q.offset));48    }49    const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`50      SELECT r.id AS ranking_id, r.rank, r.previous_rank, r.percentile, r.value, r.unit, r.confidence, r.eligible_entities, r.inputs, r.breakdown,51        c.id AS cancer_id, c.slug, c.canonical_name AS name, c.short_name, c.entity_type, c.hematologic, c.top_level, count(*) OVER() AS total52      FROM rankings r JOIN cancers c ON c.id = r.cancer_id WHERE r.snapshot_id = ${Number(snap.id)}53      ORDER BY r.rank, c.canonical_name LIMIT ${q.limit} OFFSET ${q.offset}`);54    const total = rows.length ? num(rows[0]!.total) : 0;55    const { metric_name, metric_description, formula, unit, higher_is_worse, category, aggregation, eligibility, experimental, source_slugs, ...snapshot } = snap;56    const data = {57      metric: { slug: snap.metric_slug, name: metric_name, description: metric_description, formula, formulaVersion: snap.formula_version, unit, higherIsWorse: higher_is_worse, category, aggregation, eligibility, experimental, sourceSlugs: source_slugs },58      snapshot: camel(snapshot),59      rows: rows.map((r) => ({60        rankingId: r.ranking_id,61        rank: r.rank,62        previousRank: r.previous_rank,63        rankChange: r.previous_rank == null ? null : num(r.previous_rank) - num(r.rank),64        percentile: r.percentile,65        value: r.value,66        unit: r.unit,67        confidence: r.confidence,68        eligibleEntities: r.eligible_entities,69        cancer: { id: r.cancer_id, slug: r.slug, name: r.name, shortName: r.short_name, entityType: r.entity_type, hematologic: r.hematologic, topLevel: r.top_level },70        inputs: r.inputs,71        breakdown: r.breakdown,72        explain: `/v1/rankings/${snap.metric_slug as string}/${r.cancer_id as string}/explain?geography=${encodeURIComponent(q.geography)}&sex=${q.sex}&age=${q.age}&level=${q.level}${snap.year ? `&year=${snap.year as number}` : ''}`,73      })),74    };75    return respond(app, data, (snap.source_ids as string[]) ?? [], paginate(total, q.limit, q.offset));76  });7778  app.get('/rankings/:metric/:cancerId/explain', { schema: { tags: ['rankings'], summary: '"Why this rank?" — the row inputs, previous rank, snapshot metadata and a lineage trace down to provenance and raw records (§183, §252)', params: z.object({ metric: z.string(), cancerId: z.string() }), querystring: z.object(scopeQuery), response: ok(AnyRecord) } }, async (req) => {79    const { id } = await resolveCancer(app.db, req.params.cancerId);80    const snap = await findSnapshot(app, req.params.metric, req.query);81    if (!snap) throw new NotFound('ranking snapshot', `${req.params.metric} in the requested scope`);82    const rows = await app.db.execute<Record<string, unknown>>(sql`83      SELECT r.*, c.slug, c.canonical_name AS name FROM rankings r JOIN cancers c ON c.id = r.cancer_id WHERE r.snapshot_id = ${Number(snap.id)} AND r.cancer_id = ${id}`);84    const row = rows[0];85    if (!row) throw new NotFound('ranking row', `${req.params.metric}/${id} (not eligible in this scope)`);86    const [trace, neighbours] = await Promise.all([87      traceValue(app.db, 'rankings', String(row.id)),88      app.db.execute<Record<string, unknown>>(sql`SELECT r.rank, r.value, c.id, c.slug, c.canonical_name AS name FROM rankings r JOIN cancers c ON c.id = r.cancer_id WHERE r.snapshot_id = ${Number(snap.id)} AND r.rank BETWEEN ${num(row.rank) - 2} AND ${num(row.rank) + 2} ORDER BY r.rank`),89    ]);90    const { metric_name, metric_description, formula, unit, higher_is_worse, category, aggregation, eligibility, experimental, source_slugs, ...snapshot } = snap;91    const data = {92      cancer: { id, slug: row.slug, name: row.name },93      metric: { slug: snap.metric_slug, name: metric_name, description: metric_description, formula, formulaVersion: snap.formula_version, unit, higherIsWorse: higher_is_worse, category, aggregation, eligibility, experimental, sourceSlugs: source_slugs },94      snapshot: camel(snapshot),95      rank: row.rank,96      previousRank: row.previous_rank,97      rankChange: row.previous_rank == null ? null : num(row.previous_rank) - num(row.rank),98      percentile: row.percentile,99      value: row.value,100      unit: row.unit,101      confidence: row.confidence,102      eligibleEntities: row.eligible_entities,103      inputs: row.inputs,104      breakdown: row.breakdown,105      neighbours: camelRows(neighbours),106      trace,107      reproduce: `pnpm cix rank  # recompute; compare ranking_snapshots.inputs_hash = ${snap.inputs_hash as string}`,108    };109    return respond(app, data, (snap.source_ids as string[]) ?? []);110  });111};112