spb/cancerindex

1import { sql } from 'drizzle-orm';2import type { FastifyPluginAsyncZod } from 'fastify-type-provider-zod';3import { z } from 'zod';4import { normalizeLabel } from '@cancerindex/shared';5import { paginate } from '../lib/envelope.js';6import { ancestorChain, descendantIds } from '../lib/descendants.js';7import { boolQuery, pageQuery } from '../lib/pagination.js';8import { resolveCancer } from '../lib/resolve.js';9import { AnyList, AnyRecord, camel, camelRows, num, ok, respond } from '../lib/respond.js';10import { cancerStructuralSourceIds, pluck } from '../lib/sources.js';1112const idParam = z.object({ id: z.string().min(1).describe('CI-CAN-… identifier or slug') });1314const listQuery = z.object({15  q: z.string().trim().min(1).max(200).optional().describe('Name/alias prefix filter'),16  level: z.enum(['top', 'all']).default('all').describe('top = the mutually exclusive global ranking set (§247)'),17  type: z.string().optional().describe('entity_type filter (cancer, subtype, hematologic_malignancy, …)'),18  malignant: boolQuery,19  hematologic: boolQuery,20  pediatric: boolQuery,21  rare: boolQuery,22  sort: z.enum(['name', 'active_trials', 'publications_5y']).default('name'),23  ...pageQuery,24});2526/** Column list shared by list + detail: entity + entity_counters. */27const CANCER_COLS = sql`28  c.id, c.slug, c.canonical_name, c.short_name, c.entity_type, c.malignant, c.solid_tumor, c.hematologic, c.pediatric_relevant,29  c.rare_cancer, c.top_level, c.depth, c.primary_ncit_code, c.primary_oncotree_code, c.status, c.classification_version, c.semantic_types, c.updated_at,30  ec.trial_count, ec.active_trial_count, ec.recruiting_trial_count, ec.phase3_trial_count, ec.publication_count, ec.publication_count_5y,31  ec.publication_count_12m, ec.gene_count, ec.variant_count, ec.drug_count, ec.approved_drug_count, ec.evidence_count, ec.cohort_count,32  ec.subtype_count, ec.descendant_count, ec.epidemiology_obs_count, ec.survival_obs_count, ec.completeness, ec.updated_at AS counters_computed_at`;3334function shapeCancer(row: Record<string, unknown>) {35  const r = camel<Record<string, unknown>>(row);36  const counters = r.countersComputedAt37    ? {38        trials: num(r.trialCount),39        activeTrials: num(r.activeTrialCount),40        recruitingTrials: num(r.recruitingTrialCount),41        phase3Trials: num(r.phase3TrialCount),42        publications: num(r.publicationCount),43        publications5y: num(r.publicationCount5y),44        publications12m: num(r.publicationCount12m),45        genes: num(r.geneCount),46        variants: num(r.variantCount),47        drugs: num(r.drugCount),48        approvedDrugs: num(r.approvedDrugCount),49        evidenceItems: num(r.evidenceCount),50        cohorts: num(r.cohortCount),51        subtypes: num(r.subtypeCount),52        descendants: num(r.descendantCount),53        epidemiologyObservations: num(r.epidemiologyObsCount),54        survivalObservations: num(r.survivalObsCount),55        computedAt: r.countersComputedAt,56      }57    : null; // counters not yet computed → "Data not yet available", never zeros (CLAUDE.md §281)58  return {59    id: r.id,60    slug: r.slug,61    name: r.canonicalName,62    shortName: r.shortName,63    entityType: r.entityType,64    malignant: r.malignant,65    solidTumor: r.solidTumor,66    hematologic: r.hematologic,67    pediatricRelevant: r.pediatricRelevant,68    rareCancer: r.rareCancer,69    topLevel: r.topLevel,70    depth: r.depth,71    primaryNcitCode: r.primaryNcitCode,72    primaryOncotreeCode: r.primaryOncotreeCode,73    status: r.status,74    classificationVersion: r.classificationVersion,75    semanticTypes: r.semanticTypes,76    updatedAt: r.updatedAt,77    counters,78    completeness: (r.completeness as Record<string, number> | null) ?? null,79  };80}8182export const cancerRoutes: FastifyPluginAsyncZod = async (app) => {83  app.get('/cancers', { schema: { tags: ['cancers'], summary: 'List cancer entities with counters', querystring: listQuery, response: ok(AnyList, true) } }, async (req) => {84    const q = req.query;85    const conds = [sql`c.status = 'active'`];86    if (q.level === 'top') conds.push(sql`c.top_level`);87    if (q.type) conds.push(sql`c.entity_type = ${q.type}`);88    if (q.malignant !== undefined) conds.push(sql`c.malignant = ${q.malignant}`);89    if (q.hematologic !== undefined) conds.push(sql`c.hematologic = ${q.hematologic}`);90    if (q.pediatric !== undefined) conds.push(sql`c.pediatric_relevant = ${q.pediatric}`);91    if (q.rare !== undefined) conds.push(sql`c.rare_cancer = ${q.rare}`);92    if (q.q) {93      const norm = normalizeLabel(q.q);94      conds.push(sql`EXISTS (SELECT 1 FROM cancer_aliases a WHERE a.cancer_id = c.id AND (a.normalized = ${norm} OR a.normalized LIKE ${norm + '%'} OR a.normalized LIKE ${'% ' + norm + '%'}))`);95    }96    const where = sql.join(conds, sql` AND `);97    const order =98      q.sort === 'active_trials' ? sql`ec.active_trial_count DESC NULLS LAST, c.canonical_name` : q.sort === 'publications_5y' ? sql`ec.publication_count_5y DESC NULLS LAST, c.canonical_name` : sql`c.canonical_name`;99    const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`100      SELECT ${CANCER_COLS}, count(*) OVER() AS total101      FROM cancers c LEFT JOIN entity_counters ec ON ec.entity_type = 'cancer' AND ec.entity_id = c.id102      WHERE ${where} ORDER BY ${order} LIMIT ${q.limit} OFFSET ${q.offset}`);103    const total = rows.length ? num(rows[0]!.total) : 0;104    const data = rows.map(shapeCancer);105    const sources = await cancerStructuralSourceIds(106      app.db,107      data.map((d) => d.id as string),108    );109    return respond(app, data, sources, paginate(total, q.limit, q.offset));110  });111112  app.get('/cancers/:id', { schema: { tags: ['cancers'], summary: 'Cancer entity: aliases, codes, hierarchy, anatomy, counters, current rankings, change history', params: idParam, response: ok(AnyRecord) } }, async (req) => {113    const { id } = await resolveCancer(app.db, req.params.id);114    const db = app.db;115    const [entityRows, aliases, codes, parents, children, anatomy, rankings, changes, breadcrumbs] = await Promise.all([116      db.execute<Record<string, unknown>>(sql`SELECT ${CANCER_COLS}, c.description, c.description_provenance_id, c.merged_into, c.deprecated_reason117        FROM cancers c LEFT JOIN entity_counters ec ON ec.entity_type = 'cancer' AND ec.entity_id = c.id WHERE c.id = ${id}`),118      db.execute<Record<string, unknown>>(sql`SELECT alias, alias_type, source_id, source_terminology, language FROM cancer_aliases WHERE cancer_id = ${id} ORDER BY CASE alias_type WHEN 'preferred' THEN 0 WHEN 'display' THEN 1 WHEN 'abbreviation' THEN 2 ELSE 3 END, alias`),119      db.execute<Record<string, unknown>>(sql`SELECT system, code, match_type, source_id, valid_from, valid_to FROM cancer_codes WHERE cancer_id = ${id} ORDER BY system, code`),120      db.execute<Record<string, unknown>>(sql`SELECT h.parent_id AS id, c.slug, c.canonical_name AS name, c.entity_type, h.hierarchy_type, h.source_id FROM cancer_hierarchy h JOIN cancers c ON c.id = h.parent_id WHERE h.child_id = ${id} ORDER BY h.hierarchy_type, c.canonical_name`),121      db.execute<Record<string, unknown>>(sql`SELECT h.child_id AS id, c.slug, c.canonical_name AS name, c.entity_type, c.top_level, h.hierarchy_type, h.source_id, ec.active_trial_count122        FROM cancer_hierarchy h JOIN cancers c ON c.id = h.child_id LEFT JOIN entity_counters ec ON ec.entity_type = 'cancer' AND ec.entity_id = c.id123        WHERE h.parent_id = ${id} AND c.status = 'active' ORDER BY h.hierarchy_type, c.canonical_name LIMIT 500`),124      db.execute<Record<string, unknown>>(sql`SELECT s.id, s.slug, s.name, s.system, s.ncit_code, s.uberon_id, a.relation, a.source_id FROM cancer_anatomy a JOIN anatomical_sites s ON s.id = a.site_id WHERE a.cancer_id = ${id}`),125      db.execute<Record<string, unknown>>(sql`SELECT r.id AS ranking_id, r.metric_slug, m.name AS metric_name, m.unit, m.formula_version, m.category, r.rank, r.previous_rank, r.eligible_entities, r.percentile, r.value, r.confidence,126          s.scope_key, s.geography, s.sex, s.age_group, s.year, s.entity_level, s.inputs_hash, s.generated_at, s.source_ids127        FROM rankings r JOIN ranking_snapshots s ON s.id = r.snapshot_id JOIN metric_definitions m ON m.slug = r.metric_slug128        WHERE r.cancer_id = ${id} AND s.is_current ORDER BY s.entity_level, m.category, r.metric_slug, s.scope_key`),129      db.execute<Record<string, unknown>>(sql`SELECT id, kind, summary, before, after, ingest_run_id, created_at FROM change_events WHERE entity_type = 'cancer' AND entity_id = ${id} ORDER BY created_at DESC LIMIT 20`),130      ancestorChain(db, id, 'ncit'),131    ]);132    const entityRow = entityRows[0]!;133    const entity = shapeCancer(entityRow);134    let descriptionProvenance: Record<string, unknown> | null = null;135    if (entityRow.description_provenance_id) {136      const p = await db.execute<Record<string, unknown>>(sql`SELECT p.id, p.source_id, s.slug AS source_slug, p.source_url, p.dataset, p.dataset_version, p.retrieved_at, p.evidence_type, p.license FROM provenance p JOIN sources s ON s.id = p.source_id WHERE p.id = ${Number(entityRow.description_provenance_id)}`);137      descriptionProvenance = p[0] ? camel(p[0]) : null;138    }139    const rankingRows = camelRows(rankings);140    const data = {141      ...entity,142      description: entityRow.description ?? null,143      descriptionProvenance,144      mergedInto: entityRow.merged_into ?? null,145      deprecatedReason: entityRow.deprecated_reason ?? null,146      aliases: camelRows(aliases),147      codes: camelRows(codes),148      hierarchy: { parents: camelRows(parents), children: camelRows(children), breadcrumbs },149      anatomy: camelRows(anatomy),150      rankings: rankingRows,151      changes: camelRows(changes),152    };153    const sourceRefs = [154      ...(await cancerStructuralSourceIds(db, [id])),155      ...pluck(anatomy, 'source_id'),156      ...rankings.flatMap((r) => (r.source_ids as string[]) ?? []),157      ...(descriptionProvenance ? [descriptionProvenance.sourceId as string] : []),158    ];159    return respond(app, data, sourceRefs);160  });161162  app.get('/cancers/:id/statistics', { schema: { tags: ['cancers'], summary: 'Epidemiology observations (time-aware) with per-row provenance', params: idParam, querystring: z.object({ metric: z.string().optional(), geography: z.string().optional().describe('geography slug or ISO3'), sex: z.enum(['all', 'male', 'female']).optional(), ...pageQuery }), response: ok(AnyRecord, true) } }, async (req) => {163    const { id } = await resolveCancer(app.db, req.params.id);164    const q = req.query;165    const conds = [sql`o.cancer_id = ${id}`];166    if (q.metric) conds.push(sql`o.metric = ${q.metric}`);167    if (q.sex) conds.push(sql`o.sex = ${q.sex}`);168    if (q.geography) conds.push(sql`(g.slug = ${q.geography.toLowerCase()} OR g.iso3 = ${q.geography.toUpperCase()})`);169    const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`170      SELECT o.id, o.metric, o.year, o.year_end, o.sex, o.age_group, o.value, o.unit, o.lower_ci, o.upper_ci, o.standard_population, o.estimate_type, o.site_definition,171        g.id AS geography_id, g.slug AS geography_slug, g.name AS geography_name, g.iso3, g.kind AS geography_kind,172        o.source_id, s.slug AS source_slug, s.name AS source_name, p.id AS provenance_id, p.dataset, p.dataset_version, p.retrieved_at, p.source_url, p.methodology, p.population, o.ingest_run_id,173        count(*) OVER() AS total174      FROM epidemiology_observations o JOIN geographies g ON g.id = o.geography_id JOIN sources s ON s.id = o.source_id LEFT JOIN provenance p ON p.id = o.provenance_id175      WHERE ${sql.join(conds, sql` AND `)}176      ORDER BY o.metric, g.name, o.sex, o.age_group, o.year LIMIT ${q.limit} OFFSET ${q.offset}`);177    const total = rows.length ? num(rows[0]!.total) : 0;178    const observations = rows.map((r) => ({179      id: r.id,180      metric: r.metric,181      year: r.year,182      yearEnd: r.year_end,183      sex: r.sex,184      ageGroup: r.age_group,185      value: r.value,186      unit: r.unit,187      lowerCi: r.lower_ci,188      upperCi: r.upper_ci,189      standardPopulation: r.standard_population,190      estimateType: r.estimate_type,191      siteDefinition: r.site_definition,192      geography: { id: r.geography_id, slug: r.geography_slug, name: r.geography_name, iso3: r.iso3, kind: r.geography_kind },193      provenance: { sourceId: r.source_id, sourceSlug: r.source_slug, sourceName: r.source_name, provenanceId: r.provenance_id, dataset: r.dataset, datasetVersion: r.dataset_version, retrievedAt: r.retrieved_at, url: r.source_url, methodology: r.methodology, population: r.population, ingestRunId: r.ingest_run_id },194    }));195    // Series grouped by metric × geography × sex × age group (points ordered by year) for charting.196    const series = new Map<string, { metric: unknown; unit: unknown; geography: unknown; sex: unknown; ageGroup: unknown; sourceSlug: unknown; points: Array<Record<string, unknown>> }>();197    for (const o of observations) {198      const key = `${o.metric}|${o.geography.id}|${o.sex}|${o.ageGroup}|${o.provenance.sourceSlug}`;199      if (!series.has(key)) series.set(key, { metric: o.metric, unit: o.unit, geography: o.geography, sex: o.sex, ageGroup: o.ageGroup, sourceSlug: o.provenance.sourceSlug, points: [] });200      series.get(key)!.points.push({ year: o.year, yearEnd: o.yearEnd, value: o.value, lowerCi: o.lowerCi, upperCi: o.upperCi, estimateType: o.estimateType, observationId: o.id });201    }202    return respond(app, { cancerId: id, observations, series: [...series.values()] }, pluck(rows, 'source_id'), paginate(total, q.limit, q.offset));203  });204205  app.get('/cancers/:id/survival', { schema: { tags: ['cancers'], summary: 'Survival observations (population statistics, not individual prognosis) with provenance', params: idParam, querystring: z.object(pageQuery), response: ok(AnyRecord, true) } }, async (req) => {206    const { id } = await resolveCancer(app.db, req.params.id);207    const q = req.query;208    const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`209      SELECT o.id, o.stage, o.staging_system, o.sex, o.age_group, o.diagnosis_period, o.survival_type, o.duration_months, o.probability, o.median_months, o.cohort_size, o.lower_ci, o.upper_ci, o.method,210        g.id AS geography_id, g.slug AS geography_slug, g.name AS geography_name, g.iso3,211        o.source_id, s.slug AS source_slug, s.name AS source_name, p.id AS provenance_id, p.dataset, p.dataset_version, p.retrieved_at, p.source_url, p.methodology, p.population, o.ingest_run_id,212        count(*) OVER() AS total213      FROM survival_observations o LEFT JOIN geographies g ON g.id = o.geography_id JOIN sources s ON s.id = o.source_id LEFT JOIN provenance p ON p.id = o.provenance_id214      WHERE o.cancer_id = ${id}215      ORDER BY o.survival_type, o.stage NULLS FIRST, o.duration_months, o.diagnosis_period LIMIT ${q.limit} OFFSET ${q.offset}`);216    const total = rows.length ? num(rows[0]!.total) : 0;217    const observations = rows.map((r) => ({218      id: r.id,219      survivalType: r.survival_type,220      durationMonths: r.duration_months,221      probability: r.probability,222      medianMonths: r.median_months,223      cohortSize: r.cohort_size,224      lowerCi: r.lower_ci,225      upperCi: r.upper_ci,226      stage: r.stage,227      stagingSystem: r.staging_system,228      sex: r.sex,229      ageGroup: r.age_group,230      diagnosisPeriod: r.diagnosis_period,231      method: r.method,232      geography: r.geography_id ? { id: r.geography_id, slug: r.geography_slug, name: r.geography_name, iso3: r.iso3 } : null,233      provenance: { sourceId: r.source_id, sourceSlug: r.source_slug, sourceName: r.source_name, provenanceId: r.provenance_id, dataset: r.dataset, datasetVersion: r.dataset_version, retrievedAt: r.retrieved_at, url: r.source_url, methodology: r.methodology, population: r.population, ingestRunId: r.ingest_run_id },234    }));235    return respond(app, { cancerId: id, observations, disclaimer: 'Population survival statistics describe groups of patients diagnosed in the past; they do not predict an individual outcome.' }, pluck(rows, 'source_id'), paginate(total, q.limit, q.offset));236  });237238  app.get('/cancers/:id/genes', { schema: { tags: ['cancers'], summary: 'Genes: cohort alteration frequencies (with denominators) and curated CIViC evidence, for the cancer and its descendants', params: idParam, querystring: z.object({ minFrequency: z.coerce.number().min(0).max(1).optional(), ...pageQuery }), response: ok(AnyRecord, true) } }, async (req) => {239    const { id } = await resolveCancer(app.db, req.params.id);240    const ids = await descendantIds(app.db, id);241    const q = req.query;242    const [freq, civic] = await Promise.all([243      app.db.execute<Record<string, unknown> & { total: string }>(sql`244        SELECT f.id, f.gene_id, f.gene_symbol, g.name AS gene_name, f.alteration_type, f.cases_affected, f.cases_profiled, f.frequency, f.rank, f.data_release, f.cancer_id,245          c.id AS cohort_id, c.study_id, c.name AS cohort_name, c.program, c.case_count, c.cases_with_ssm, c.url AS cohort_url, c.source_id, c.cancer_match_type,246          p.id AS provenance_id, p.dataset, p.dataset_version, p.retrieved_at, p.source_url, count(*) OVER() AS total247        FROM cancer_gene_frequencies f JOIN genomic_cohorts c ON c.id = f.cohort_id LEFT JOIN genes g ON g.id = f.gene_id LEFT JOIN provenance p ON p.id = f.provenance_id248        WHERE f.cancer_id = ANY(${sql.param(ids)}::text[]) ${q.minFrequency !== undefined ? sql`AND f.frequency >= ${q.minFrequency}` : sql``}249        ORDER BY f.frequency DESC, f.gene_symbol LIMIT ${q.limit} OFFSET ${q.offset}`),250      app.db.execute<Record<string, unknown>>(sql`251        SELECT gs AS gene_symbol, g.id AS gene_id, g.name AS gene_name, count(*) AS evidence_items,252          count(*) FILTER (WHERE e.evidence_level = 'A') AS level_a, count(*) FILTER (WHERE e.evidence_level = 'B') AS level_b, count(*) FILTER (WHERE e.evidence_level = 'C') AS level_c,253          count(*) FILTER (WHERE e.evidence_type = 'PREDICTIVE') AS predictive, count(*) FILTER (WHERE e.evidence_type = 'PROGNOSTIC') AS prognostic, count(*) FILTER (WHERE e.evidence_type = 'DIAGNOSTIC') AS diagnostic, count(*) FILTER (WHERE e.evidence_type = 'PREDISPOSING') AS predisposing,254          array_agg(DISTINCT e.cancer_id) AS cancer_ids, min(p.source_id) AS source_id, max(p.retrieved_at) AS retrieved_at, array_agg(DISTINCT e.civic_id ORDER BY e.civic_id) AS civic_ids255        FROM civic_evidence_items e CROSS JOIN LATERAL unnest(e.gene_symbols) gs LEFT JOIN genes g ON upper(g.symbol) = upper(gs) LEFT JOIN provenance p ON p.id = e.provenance_id256        WHERE e.status = 'ACCEPTED' AND e.cancer_id = ANY(${sql.param(ids)}::text[])257        GROUP BY gs, g.id, g.name ORDER BY evidence_items DESC, gs LIMIT 500`),258    ]);259    const total = freq.length ? num(freq[0]!.total) : 0;260    const frequencies = freq.map((r) => ({261      id: r.id,262      gene: { id: r.gene_id, symbol: r.gene_symbol, name: r.gene_name },263      alterationType: r.alteration_type,264      casesAffected: r.cases_affected,265      casesProfiled: r.cases_profiled,266      frequency: r.frequency,267      rank: r.rank,268      cancerId: r.cancer_id,269      cohort: { id: r.cohort_id, studyId: r.study_id, name: r.cohort_name, program: r.program, caseCount: r.case_count, casesWithSsm: r.cases_with_ssm, url: r.cohort_url, cancerMatchType: r.cancer_match_type },270      provenance: { sourceId: r.source_id, provenanceId: r.provenance_id, dataset: r.dataset, datasetVersion: r.data_release ?? r.dataset_version, retrievedAt: r.retrieved_at, url: r.source_url },271    }));272    const curated = civic.map((r) => ({273      gene: { id: r.gene_id, symbol: r.gene_symbol, name: r.gene_name },274      evidenceItems: num(r.evidence_items),275      byLevel: { A: num(r.level_a), B: num(r.level_b), C: num(r.level_c) },276      byType: { predictive: num(r.predictive), prognostic: num(r.prognostic), diagnostic: num(r.diagnostic), predisposing: num(r.predisposing) },277      cancerIds: r.cancer_ids,278      civicEvidenceIds: r.civic_ids,279      provenance: { sourceId: r.source_id, retrievedAt: r.retrieved_at, category: 'curated_evidence' },280    }));281    return respond(app, { cancerId: id, includesDescendants: ids.length - 1, cohortFrequencies: frequencies, curatedEvidence: curated }, [...pluck(freq, 'source_id'), ...pluck(civic, 'source_id')], paginate(total, q.limit, q.offset));282  });283284  app.get('/cancers/:id/variants', { schema: { tags: ['cancers'], summary: 'Variants with curated evidence counts for the cancer and its descendants', params: idParam, querystring: z.object(pageQuery), response: ok(AnyList, true) } }, async (req) => {285    const { id } = await resolveCancer(app.db, req.params.id);286    const ids = await descendantIds(app.db, id);287    const q = req.query;288    const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`289      SELECT v.id, v.slug, v.name, v.gene_symbol, v.gene_id, v.variant_type, v.hgvs_p, v.hgvs_c, v.clinvar_variation_id, v.civic_variant_id,290        count(*) AS evidence_items,291        count(*) FILTER (WHERE e.evidence_level = 'A') AS level_a, count(*) FILTER (WHERE e.evidence_level = 'B') AS level_b, count(*) FILTER (WHERE e.evidence_level = 'C') AS level_c, count(*) FILTER (WHERE e.evidence_level IN ('D','E')) AS level_de,292        count(*) FILTER (WHERE e.evidence_direction = 'SUPPORTS') AS supports, count(*) FILTER (WHERE e.evidence_direction = 'DOES_NOT_SUPPORT') AS does_not_support,293        count(*) FILTER (WHERE e.evidence_type = 'PREDICTIVE') AS predictive, count(*) FILTER (WHERE e.evidence_type = 'PROGNOSTIC') AS prognostic, count(*) FILTER (WHERE e.evidence_type = 'DIAGNOSTIC') AS diagnostic,294        array_agg(DISTINCT e.cancer_id) AS cancer_ids, min(p.source_id) AS source_id, count(*) OVER() AS total295      FROM civic_evidence_items e CROSS JOIN LATERAL unnest(e.variant_ids) vid JOIN variants v ON v.id = vid LEFT JOIN provenance p ON p.id = e.provenance_id296      WHERE e.status = 'ACCEPTED' AND e.cancer_id = ANY(${sql.param(ids)}::text[])297      GROUP BY v.id ORDER BY evidence_items DESC, v.gene_symbol, v.name LIMIT ${q.limit} OFFSET ${q.offset}`);298    const total = rows.length ? num(rows[0]!.total) : 0;299    const data = rows.map((r) => ({300      id: r.id,301      slug: r.slug,302      name: r.name,303      gene: { id: r.gene_id, symbol: r.gene_symbol },304      variantType: r.variant_type,305      hgvsP: r.hgvs_p,306      hgvsC: r.hgvs_c,307      clinvarVariationId: r.clinvar_variation_id,308      civicVariantId: r.civic_variant_id,309      evidence: { items: num(r.evidence_items), byLevel: { A: num(r.level_a), B: num(r.level_b), C: num(r.level_c), DE: num(r.level_de) }, byDirection: { supports: num(r.supports), doesNotSupport: num(r.does_not_support) }, byType: { predictive: num(r.predictive), prognostic: num(r.prognostic), diagnostic: num(r.diagnostic) }, category: 'curated_evidence' },310      cancerIds: r.cancer_ids,311    }));312    return respond(app, data, pluck(rows, 'source_id'), paginate(total, q.limit, q.offset));313  });314315  app.get('/cancers/:id/drugs', { schema: { tags: ['cancers'], summary: 'Therapies: CIViC evidence counts (level/direction) and jurisdiction-aware approvals — never a bare "approved" flag (§13)', params: idParam, querystring: z.object(pageQuery), response: ok(AnyRecord, true) } }, async (req) => {316    const { id } = await resolveCancer(app.db, req.params.id);317    const ids = await descendantIds(app.db, id);318    const q = req.query;319    const [evidence, approvals] = await Promise.all([320      app.db.execute<Record<string, unknown> & { total: string }>(sql`321        SELECT d.id, d.slug, d.name, d.kind, d.ncit_code, d.chembl_id, d.mechanism,322          count(*) AS evidence_items,323          count(*) FILTER (WHERE e.evidence_level = 'A') AS level_a, count(*) FILTER (WHERE e.evidence_level = 'B') AS level_b, count(*) FILTER (WHERE e.evidence_level = 'C') AS level_c, count(*) FILTER (WHERE e.evidence_level IN ('D','E')) AS level_de,324          count(*) FILTER (WHERE e.evidence_direction = 'SUPPORTS') AS supports, count(*) FILTER (WHERE e.evidence_direction = 'DOES_NOT_SUPPORT') AS does_not_support,325          count(*) FILTER (WHERE e.significance = 'SENSITIVITYRESPONSE') AS sensitivity, count(*) FILTER (WHERE e.significance = 'RESISTANCE') AS resistance,326          array_agg(DISTINCT e.cancer_id) AS cancer_ids, array_agg(DISTINCT gs) FILTER (WHERE gs IS NOT NULL) AS gene_symbols, min(p.source_id) AS source_id, count(*) OVER() AS total327        FROM civic_evidence_items e CROSS JOIN LATERAL unnest(e.therapy_ids) tid JOIN drugs d ON d.id = tid LEFT JOIN LATERAL unnest(e.gene_symbols) gs ON true LEFT JOIN provenance p ON p.id = e.provenance_id328        WHERE e.status = 'ACCEPTED' AND e.cancer_id = ANY(${sql.param(ids)}::text[])329        GROUP BY d.id ORDER BY evidence_items DESC, d.name LIMIT ${q.limit} OFFSET ${q.offset}`),330      app.db.execute<Record<string, unknown>>(sql`331        SELECT a.id, a.drug_id, d.slug AS drug_slug, d.name AS drug_name, a.cancer_id, a.biomarker_ids, a.tumor_agnostic, a.jurisdiction, a.authority, a.indication, a.line_of_therapy, a.disease_stage, a.approval_type, a.accelerated, a.conditional,332          a.approval_date, a.withdrawal_date, a.status, a.application_number, a.source_id, p.source_url, p.retrieved_at, p.dataset_version333        FROM drug_approvals a JOIN drugs d ON d.id = a.drug_id LEFT JOIN provenance p ON p.id = a.provenance_id334        WHERE a.cancer_id = ANY(${sql.param(ids)}::text[]) OR a.tumor_agnostic335        ORDER BY a.approval_date DESC NULLS LAST, d.name LIMIT 500`),336    ]);337    const total = evidence.length ? num(evidence[0]!.total) : 0;338    const data = {339      cancerId: id,340      includesDescendants: ids.length - 1,341      evidenceByDrug: evidence.map((r) => ({342        drug: { id: r.id, slug: r.slug, name: r.name, kind: r.kind, ncitCode: r.ncit_code, chemblId: r.chembl_id, mechanism: r.mechanism },343        evidence: { items: num(r.evidence_items), byLevel: { A: num(r.level_a), B: num(r.level_b), C: num(r.level_c), DE: num(r.level_de) }, byDirection: { supports: num(r.supports), doesNotSupport: num(r.does_not_support) }, bySignificance: { sensitivity: num(r.sensitivity), resistance: num(r.resistance) }, category: 'curated_evidence' },344        geneSymbols: r.gene_symbols ?? [],345        cancerIds: r.cancer_ids,346      })),347      approvals: approvals.map((r) => ({348        id: r.id,349        drug: { id: r.drug_id, slug: r.drug_slug, name: r.drug_name },350        cancerId: r.cancer_id,351        tumorAgnostic: r.tumor_agnostic,352        biomarkerIds: r.biomarker_ids,353        jurisdiction: r.jurisdiction,354        authority: r.authority,355        indication: r.indication,356        lineOfTherapy: r.line_of_therapy,357        diseaseStage: r.disease_stage,358        approvalType: r.approval_type,359        accelerated: r.accelerated,360        conditional: r.conditional,361        approvalDate: r.approval_date,362        withdrawalDate: r.withdrawal_date,363        status: r.status,364        applicationNumber: r.application_number,365        provenance: { sourceId: r.source_id, url: r.source_url, retrievedAt: r.retrieved_at, datasetVersion: r.dataset_version, category: 'regulatory_status' },366      })),367    };368    return respond(app, data, [...pluck(evidence, 'source_id'), ...pluck(approvals, 'source_id')], paginate(total, q.limit, q.offset));369  });370371  app.get('/cancers/:id/trials', { schema: { tags: ['cancers'], summary: 'Clinical trials mapped to the cancer or any descendant (recursive hierarchy, depth ≤ 12)', params: idParam, querystring: z.object({ status: z.string().optional().describe('ClinicalTrials.gov overall status, e.g. RECRUITING'), phase: z.string().optional().describe('PHASE1 | PHASE2 | PHASE3 | PHASE4 | EARLY_PHASE1 | NA'), interventionalOnly: boolQuery, ...pageQuery }), response: ok(AnyList, true) } }, async (req) => {372    const { id } = await resolveCancer(app.db, req.params.id);373    const ids = await descendantIds(app.db, id);374    const q = req.query;375    const conds = [sql`tc.cancer_id = ANY(${sql.param(ids)}::text[])`];376    if (q.status) conds.push(sql`t.overall_status = ${q.status.toUpperCase()}`);377    if (q.phase) conds.push(sql`${q.phase.toUpperCase()} = ANY(t.phases)`);378    if (q.interventionalOnly) conds.push(sql`t.study_type = 'INTERVENTIONAL'`);379    const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`380      SELECT t.id, t.nct_id, t.brief_title, t.acronym, t.study_type, t.phases, t.overall_status, t.start_date, t.primary_completion_date, t.last_update_posted_date, t.has_results, t.enrollment_count, t.lead_sponsor, t.lead_sponsor_class, t.countries, t.locations_count,381        array_agg(DISTINCT jsonb_build_object('cancerId', tc.cancer_id, 'condition', tc.condition_text, 'matchType', tc.match_type)) AS mapped_conditions,382        sr.source_id, count(*) OVER() AS total383      FROM clinical_trials t JOIN trial_conditions tc ON tc.trial_id = t.id LEFT JOIN source_records sr ON sr.id = t.source_record_id384      WHERE ${sql.join(conds, sql` AND `)}385      GROUP BY t.id, sr.source_id386      ORDER BY t.last_update_posted_date DESC NULLS LAST, t.nct_id LIMIT ${q.limit} OFFSET ${q.offset}`);387    const total = rows.length ? num(rows[0]!.total) : 0;388    const data = rows.map((r) => {389      const { total: _t, source_id: _s, ...rest } = r;390      return camel(rest);391    });392    const srcs = pluck(rows, 'source_id');393    return respond(app, data, srcs.length ? srcs : ['clinicaltrials'], paginate(total, q.limit, q.offset));394  });395396  app.get('/cancers/:id/publications', { schema: { tags: ['cancers'], summary: 'Linked publications (entity edges with extraction method) and literature count windows (query stored verbatim)', params: idParam, querystring: z.object({ status: z.enum(['candidate', 'validated', 'all']).default('all'), ...pageQuery }), response: ok(AnyRecord, true) } }, async (req) => {397    const { id } = await resolveCancer(app.db, req.params.id);398    const q = req.query;399    const [pubs, counts] = await Promise.all([400      app.db.execute<Record<string, unknown> & { total: string }>(sql`401        SELECT p.id, p.pmid, p.doi, p.pmcid, p.title, p.journal, p.pub_date, p.pub_year, p.publication_types, p.is_preprint, p.retracted, p.nct_ids, p.cited_by_count,402          e.method, e.confidence, e.status AS edge_status, e.source_id, count(*) OVER() AS total403        FROM publication_entity_edges e JOIN publications p ON p.id = e.publication_id404        WHERE e.entity_type = 'cancer' AND e.entity_id = ${id} AND e.status <> 'rejected' ${q.status !== 'all' ? sql`AND e.status = ${q.status}` : sql``}405        ORDER BY p.pub_year DESC NULLS LAST, p.pub_date DESC NULLS LAST, p.pmid LIMIT ${q.limit} OFFSET ${q.offset}`),406      app.db.execute<Record<string, unknown>>(sql`407        SELECT l.id, l.window_key, l.window_start, l.window_end, l.query, l.count, l.updated_at AS computed_at, p.source_id, p.retrieved_at, p.source_url408        FROM literature_counts l LEFT JOIN provenance p ON p.id = l.provenance_id WHERE l.cancer_id = ${id} ORDER BY l.window_key`),409    ]);410    const total = pubs.length ? num(pubs[0]!.total) : 0;411    const data = {412      cancerId: id,413      literatureCounts: counts.map((r) => ({ id: r.id, windowKey: r.window_key, windowStart: r.window_start, windowEnd: r.window_end, query: r.query, count: r.count, computedAt: r.computed_at, provenance: { sourceId: r.source_id, retrievedAt: r.retrieved_at, url: r.source_url, category: 'computed_metric' } })),414      publications: pubs.map((r) => {415        const { total: _t, source_id, method, confidence, edge_status, ...rest } = r;416        return { ...camel(rest), edge: { method, confidence, status: edge_status, sourceId: source_id } };417      }),418    };419    return respond(app, data, [...pluck(pubs, 'source_id'), ...pluck(counts, 'source_id')], paginate(total, q.limit, q.offset));420  });421};422