spb/cancerindex

1import { sql } from 'drizzle-orm';2import type { FastifyPluginAsyncZod } from 'fastify-type-provider-zod';3import { z } from 'zod';4import { paginate } from '../lib/envelope.js';5import { BadRequest, NotFound } from '../lib/errors.js';6import { pageQuery } from '../lib/pagination.js';7import { resolveCancer } from '../lib/resolve.js';8import { AnyList, num, ok, respond } from '../lib/respond.js';910const MAX_CANCERS = 8;11const METRIC_RE = /^[a-z][a-z0-9_]{1,63}$/;1213/** Labels shared with the web app (apps/web/src/lib/queries/epidemiology.ts EPI_METRIC_LABEL). */14const METRIC_LABEL: Record<string, string> = {15  incidence_count: 'New cases',16  incidence_rate: 'Incidence rate (crude)',17  as_incidence_rate: 'Incidence rate (age-standardized)',18  mortality_count: 'Deaths',19  mortality_rate: 'Mortality rate (crude)',20  as_mortality_rate: 'Mortality rate (age-standardized)',21  prevalence: 'Prevalence',22  prevalence_5y: '5-year prevalence',23};2425/** `cancer=a&cancer=b` or `cancer=a,b` (or both) → distinct trimmed refs, max MAX_CANCERS. */26export function splitRefs(v: string | string[] | undefined, max = MAX_CANCERS): string[] {27  const raw = (Array.isArray(v) ? v : v == null ? [] : [v]).flatMap((s) => String(s).split(','));28  const out: string[] = [];29  for (const t of raw.map((s) => s.trim()).filter(Boolean)) if (!out.includes(t)) out.push(t);30  if (out.length > max) throw new BadRequest(`at most ${max} cancers per request (got ${out.length})`);31  return out;32}3334const cancerParam = z.union([z.string(), z.array(z.string())]).optional().describe('Cancer id or slug — repeatable or comma-separated, max 8');35const geographyParam = z.string().trim().min(1).max(100).optional().describe('Geography slug or ISO3 code (e.g. united-states, USA)');3637type GeoRow = Record<string, unknown> & { id: string; slug: string; name: string; iso3: string | null };3839/**40 * Epidemiology routes (SPEC §20, §63, §110). Observations are returned exactly as stored — one row per41 * (cancer, geography, year, sex, age group, metric, source, site definition) — with the standard population,42 * estimate type and full provenance so consumers can apply the comparability rules43 * (docs/methodology/data-explorer.md): never overlay different standard populations, sources or age groups.44 */45export const epidemiologyRoutes: FastifyPluginAsyncZod = async (app) => {46  async function resolveGeography(ref: string): Promise<GeoRow> {47    const rows = await app.db.execute<GeoRow>(sql`SELECT id, slug, name, iso3 FROM geographies WHERE slug = ${ref.toLowerCase()} OR upper(iso3) = ${ref.toUpperCase()} OR id = ${ref} ORDER BY (slug = ${ref.toLowerCase()}) DESC LIMIT 1`);48    const g = rows[0];49    if (!g) throw new NotFound('geography', ref);50    return g;51  }5253  app.get(54    '/epidemiology',55    {56      schema: {57        tags: ['epidemiology'],58        summary: 'Epidemiology observations filtered by metric, cancer(s), geography, sex, age group, years, source and estimate type',59        querystring: z.object({60          metric: z.string().regex(METRIC_RE).optional().describe('incidence_count | as_incidence_rate | mortality_count | mortality_rate | as_mortality_rate | …'),61          cancer: cancerParam,62          geography: geographyParam,63          sex: z.enum(['all', 'male', 'female']).optional(),64          age: z.string().trim().max(24).optional().describe('Age group label as stored (default: any; "all" = all ages)'),65          from: z.coerce.number().int().min(1900).max(2100).optional().describe('First year (inclusive)'),66          to: z.coerce.number().int().min(1900).max(2100).optional().describe('Last year (inclusive)'),67          source: z.string().trim().max(64).optional().describe('Source slug or CI-SOURCE id'),68          estimateType: z.enum(['observed', 'estimated', 'projected']).optional(),69          ...pageQuery,70        }),71        response: ok(AnyList, true),72      },73    },74    async (req) => {75      const q = req.query;76      const conds = [sql`true`];77      if (q.metric) conds.push(sql`o.metric = ${q.metric}`);78      const refs = splitRefs(q.cancer);79      if (refs.length > 0) {80        const ids = await Promise.all(refs.map((r) => resolveCancer(app.db, r).then((c) => c.id)));81        conds.push(sql`o.cancer_id = ANY(${sql.param(ids)}::text[])`);82      }83      if (q.geography) {84        const g = await resolveGeography(q.geography);85        conds.push(sql`o.geography_id = ${g.id}`);86      }87      if (q.sex) conds.push(sql`o.sex = ${q.sex}`);88      if (q.age) conds.push(sql`o.age_group = ${q.age}`);89      if (q.from != null) conds.push(sql`coalesce(o.year_end, o.year) >= ${q.from}`);90      if (q.to != null) conds.push(sql`o.year <= ${q.to}`);91      if (q.source) conds.push(q.source.startsWith('CI-SOURCE-') ? sql`o.source_id = ${q.source}` : sql`s.slug = ${q.source.toLowerCase()}`);92      if (q.estimateType) conds.push(sql`o.estimate_type = ${q.estimateType}`);93      if (q.from != null && q.to != null && q.from > q.to) throw new BadRequest('`from` must not exceed `to`');9495      const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`96        SELECT o.id, o.cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name,97               o.geography_id, g.slug AS geography_slug, g.name AS geography_name, g.iso3,98               o.year, o.year_end, o.sex, o.age_group, o.metric, o.value, o.unit, o.lower_ci, o.upper_ci, o.standard_population, o.estimate_type, o.site_definition,99               o.source_id, s.slug AS source_slug, o.provenance_id, p.dataset, p.dataset_version, p.source_url, p.retrieved_at, o.updated_at,100               count(*) OVER() AS total101        FROM epidemiology_observations o102        JOIN cancers c ON c.id = o.cancer_id103        JOIN geographies g ON g.id = o.geography_id104        JOIN sources s ON s.id = o.source_id105        LEFT JOIN provenance p ON p.id = o.provenance_id106        WHERE ${sql.join(conds, sql` AND `)}107        ORDER BY c.canonical_name, c.id, g.name, o.sex, o.year, s.slug, o.site_definition108        LIMIT ${q.limit} OFFSET ${q.offset}`);109      const total = rows.length ? num(rows[0]!.total) : 0;110      const data = rows.map((r) => ({111        id: num(r.id),112        cancer: { id: r.cancer_id, slug: r.cancer_slug, name: r.cancer_name },113        geography: { id: r.geography_id, slug: r.geography_slug, name: r.geography_name, iso3: r.iso3 },114        year: num(r.year),115        yearEnd: r.year_end == null ? null : num(r.year_end),116        sex: r.sex,117        ageGroup: r.age_group,118        metric: r.metric,119        value: num(r.value),120        unit: r.unit,121        lowerCi: r.lower_ci == null ? null : num(r.lower_ci),122        upperCi: r.upper_ci == null ? null : num(r.upper_ci),123        standardPopulation: r.standard_population,124        estimateType: r.estimate_type,125        siteDefinition: r.site_definition,126        source: { id: r.source_id, slug: r.source_slug },127        provenance: { id: num(r.provenance_id), dataset: r.dataset, datasetVersion: r.dataset_version, sourceUrl: r.source_url, retrievedAt: r.retrieved_at },128        updatedAt: r.updated_at,129      }));130      return respond(app, data, data.map((d) => d.source.id as string), paginate(total, q.limit, q.offset));131    },132  );133134  app.get(135    '/epidemiology/coverage',136    {137      schema: {138        tags: ['epidemiology'],139        summary: 'Coverage matrix: metric × geography × sex × age group × source × standard population with year span and counts',140        querystring: z.object({ cancer: cancerParam, geography: geographyParam, metric: z.string().regex(METRIC_RE).optional() }),141        response: ok(AnyList),142      },143    },144    async (req) => {145      const q = req.query;146      const conds = [sql`true`];147      const refs = splitRefs(q.cancer);148      if (refs.length > 0) {149        const ids = await Promise.all(refs.map((r) => resolveCancer(app.db, r).then((c) => c.id)));150        conds.push(sql`o.cancer_id = ANY(${sql.param(ids)}::text[])`);151      }152      if (q.geography) {153        const g = await resolveGeography(q.geography);154        conds.push(sql`o.geography_id = ${g.id}`);155      }156      if (q.metric) conds.push(sql`o.metric = ${q.metric}`);157      const rows = await app.db.execute<Record<string, unknown>>(sql`158        SELECT o.metric, min(o.unit) AS unit, g.id AS geography_id, g.slug AS geography_slug, g.name AS geography_name, g.iso3, o.sex, o.age_group,159               o.source_id, s.slug AS source_slug, o.standard_population, array_agg(DISTINCT o.estimate_type) AS estimate_types,160               min(o.year) AS year_from, max(coalesce(o.year_end, o.year)) AS year_to, count(DISTINCT o.year) AS years, count(*) AS observations, count(DISTINCT o.cancer_id) AS cancers, max(o.updated_at) AS last_updated161        FROM epidemiology_observations o JOIN geographies g ON g.id = o.geography_id JOIN sources s ON s.id = o.source_id162        WHERE ${sql.join(conds, sql` AND `)}163        GROUP BY o.metric, g.id, g.slug, g.name, g.iso3, o.sex, o.age_group, o.source_id, s.slug, o.standard_population164        ORDER BY o.metric, g.name, (o.sex = 'all') DESC, o.sex, (o.age_group = 'all') DESC, o.age_group, s.slug, o.standard_population`);165      const data = rows.map((r) => ({166        metric: r.metric,167        unit: r.unit,168        geography: { id: r.geography_id, slug: r.geography_slug, name: r.geography_name, iso3: r.iso3 },169        sex: r.sex,170        ageGroup: r.age_group,171        source: { id: r.source_id, slug: r.source_slug },172        standardPopulation: r.standard_population,173        estimateTypes: r.estimate_types,174        yearFrom: num(r.year_from),175        yearTo: num(r.year_to),176        years: num(r.years),177        observations: num(r.observations),178        cancers: num(r.cancers),179        lastUpdated: r.last_updated,180      }));181      return respond(182        app,183        data,184        data.map((d) => d.source.id as string),185      );186    },187  );188189  app.get('/epidemiology/metrics', { schema: { tags: ['epidemiology'], summary: 'Distinct epidemiology metrics present, with unit, label, year span and counts', response: ok(AnyList) } }, async () => {190    const rows = await app.db.execute<Record<string, unknown>>(sql`191      SELECT o.metric, min(o.unit) AS unit, count(*) AS n, count(DISTINCT o.cancer_id) AS cancers, count(DISTINCT o.geography_id) AS geographies, min(o.year) AS year_from, max(coalesce(o.year_end, o.year)) AS year_to,192             array_agg(DISTINCT s.slug ORDER BY s.slug) AS sources, array_remove(array_agg(DISTINCT o.standard_population), NULL) AS standard_populations193      FROM epidemiology_observations o JOIN sources s ON s.id = o.source_id GROUP BY o.metric ORDER BY o.metric`);194    const data = rows.map((r) => ({195      metric: r.metric,196      label: METRIC_LABEL[r.metric as string] ?? String(r.metric).replace(/_/g, ' '),197      unit: r.unit,198      n: num(r.n),199      cancers: num(r.cancers),200      geographies: num(r.geographies),201      yearFrom: num(r.year_from),202      yearTo: num(r.year_to),203      sources: r.sources,204      standardPopulations: r.standard_populations,205    }));206    return respond(207      app,208      data,209      data.flatMap((d) => d.sources as string[]),210    );211  });212};213