spb/cancerindex
Public
TypeScript 97.2%
SQL 1.5%
CSS 0.6%
JavaScript 0.5%
1import { sql, type SQL } from 'drizzle-orm';2import type { FastifyPluginAsyncZod } from 'fastify-type-provider-zod';3import { z } from 'zod';4import { isCiId } from '@cancerindex/shared';5import { paginate } from '../lib/envelope.js';6import { NotFound } from '../lib/errors.js';7import { pageQuery } from '../lib/pagination.js';8import { AnyList, AnyRecord, camel, camelRows, num, ok, respond } from '../lib/respond.js';9import { pluck } from '../lib/sources.js';1011/**12 * Biomarkers (SPEC §17, §52, §121). Rows are curated metadata (identity, verified NCIt concept,13 * anchor gene, aliases, assay conventions); every link below is DERIVED at request time with the14 * rules of docs/methodology/biomarkers.md (formula `biomarker-links-v1`), mirroring15 * apps/web/src/lib/queries/biomarkers.ts:16 * scope gene_ids = anchor gene ∪ measurement.genes; variant_ids = measurement.variantSlugs.17 * evidence CIViC ACCEPTED, type PREDICTIVE | PROGNOSTIC | DIAGNOSTIC, variant (or gene) overlap.18 * cancers distinct mapped cancers of that evidence.19 * drugs PREDICTIVE therapies ∪ PREDICTS_RESPONSE_TO edge targets from in-scope variants.20 * approvals of those drugs (matchedBy = drug) or whose indication text names the marker (indication).21 * trials interventions ∩ scope drugs AND conditions ∩ scope cancers.22 * literature publication_entity_edges on scope variants (molecular) or genes.23 */24export const BIOMARKER_LINKS_FORMULA = 'biomarker-links-v1';25const ACTIVE_STATUSES = ['RECRUITING', 'NOT_YET_RECRUITING', 'ENROLLING_BY_INVITATION', 'ACTIVE_NOT_RECRUITING'];26const KINDS = ['gene_mutation', 'protein_expression', 'hormone_receptor', 'immune_marker', 'msi', 'tmb', 'hrd', 'ctdna', 'methylation', 'signature', 'cell_surface', 'other'] as const;2728const SCOPE_COLUMNS = sql`29 b.id, b.slug, b.name, b.kind, b.gene_id, b.ncit_code, b.description, b.measurement, b.created_at, b.updated_at,30 ARRAY(SELECT g.id FROM genes g WHERE g.id = b.gene_id OR g.symbol IN (SELECT jsonb_array_elements_text(coalesce(b.measurement->'genes', '[]'::jsonb))))::text[] AS gene_ids,31 ARRAY(SELECT v.id FROM variants v WHERE v.slug IN (SELECT jsonb_array_elements_text(coalesce(b.measurement->'variantSlugs', '[]'::jsonb))))::text[] AS variant_ids`;32// MATERIALIZED: otherwise the scope sub-selects (45k genes / 417k variants) are re-evaluated per joined row when the CTE is inlined (22 s for MSI-H, measured 2026-09-11).33const scopeCte = (where: SQL) => sql`WITH b AS MATERIALIZED (SELECT ${SCOPE_COLUMNS} FROM biomarkers b WHERE ${where})`;34const EV_SCOPE = sql`e.status = 'ACCEPTED' AND e.evidence_type IN ('PREDICTIVE', 'PROGNOSTIC', 'DIAGNOSTIC')35 AND CASE WHEN cardinality(b.variant_ids) > 0 THEN e.variant_ids && b.variant_ids ELSE cardinality(b.gene_ids) > 0 AND e.gene_ids && b.gene_ids END`;36const VAR_SCOPE = sql`CASE WHEN cardinality(b.variant_ids) > 0 THEN v.id = ANY(b.variant_ids) ELSE v.gene_id = ANY(b.gene_ids) END`;37const DSET = sql`dset AS (38 SELECT DISTINCT bid, drug_id FROM (39 SELECT b.id AS bid, t AS drug_id FROM b JOIN civic_evidence_items e ON ${EV_SCOPE} AND e.evidence_type = 'PREDICTIVE' CROSS JOIN LATERAL unnest(e.therapy_ids) t40 UNION41 SELECT b.id, k.target_entity_id FROM b JOIN variants v ON ${VAR_SCOPE}42 JOIN knowledge_edges k ON k.source_entity_type = 'variant' AND k.source_entity_id = v.id AND k.target_entity_type = 'drug' AND k.relationship_type = 'PREDICTS_RESPONSE_TO' AND k.status = 'active'43 ) u)`;44const CSET = sql`cset AS (SELECT DISTINCT b.id AS bid, e.cancer_id FROM b JOIN civic_evidence_items e ON ${EV_SCOPE} WHERE e.cancer_id IS NOT NULL)`;45const TERM_MATCH = sql`EXISTS (SELECT 1 FROM jsonb_array_elements_text(coalesce(b.measurement->'indicationTerms', '[]'::jsonb)) term WHERE a.indication ILIKE '%' || term || '%')`;46const ACTIVE = sql`t.overall_status = ANY(${sql.param(ACTIVE_STATUSES)}::text[])`;47/** (bid, trial_id) = intervention on a scope drug AND condition in a scope cancer; MATERIALIZED so LIMIT-ed queries never scan clinical_trials row by row. */48const TSET = sql`tset AS MATERIALIZED (49 SELECT DISTINCT d.bid, ti.trial_id FROM dset d JOIN trial_interventions ti ON ti.drug_id = d.drug_id50 JOIN trial_conditions tc ON tc.trial_id = ti.trial_id JOIN cset c ON c.bid = d.bid AND c.cancer_id = tc.cancer_id)`;5152const derived = { category: 'computed_metric', formulaVersion: BIOMARKER_LINKS_FORMULA, methodology: '/methodology#biomarkers', doc: 'docs/methodology/biomarkers.md' } as const;5354function shapeBiomarker(r: Record<string, unknown>) {55 const { gene_ids, variant_ids, measurement, ...rest } = r;56 const m = (measurement ?? {}) as Record<string, unknown>;57 return { ...camel(rest), aliases: (m.aliases as string[] | undefined) ?? [], tumorAgnosticCurated: m.tumorAgnostic === true, ncit: m.ncit ?? null, verification: m.verification ?? null, measurement: m, scope: { geneIds: gene_ids ?? [], variantIds: variant_ids ?? [] } };58}5960export const biomarkerRoutes: FastifyPluginAsyncZod = async (app) => {61 app.get(62 '/biomarkers',63 {64 schema: {65 tags: ['biomarkers'],66 summary: 'List canonical biomarkers with derived link counts (cancers, drugs, approvals, active trials)',67 querystring: z.object({ kind: z.enum(KINDS).optional().describe('Biomarker kind'), q: z.string().trim().min(1).max(80).optional().describe('Name, alias, gene symbol or NCIt code'), ...pageQuery }),68 response: ok(AnyList, true),69 },70 },71 async (req) => {72 const q = req.query;73 const conds = [sql`true`];74 if (q.kind) conds.push(sql`b.kind = ${q.kind}`);75 if (q.q) {76 const like = `%${q.q}%`;77 conds.push(sql`(b.name ILIKE ${like} OR b.slug ILIKE ${like} OR b.ncit_code ILIKE ${q.q + '%'}78 OR EXISTS (SELECT 1 FROM jsonb_array_elements_text(coalesce(b.measurement->'aliases', '[]'::jsonb)) a WHERE a ILIKE ${like})79 OR EXISTS (SELECT 1 FROM genes g WHERE (g.id = b.gene_id OR g.symbol IN (SELECT jsonb_array_elements_text(coalesce(b.measurement->'genes', '[]'::jsonb)))) AND g.symbol ILIKE ${q.q + '%'}))`);80 }81 const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`82 ${scopeCte(sql.join(conds, sql` AND `))}, ${DSET}, ${CSET}, ${TSET}83 SELECT b.*, count(*) OVER() AS total,84 coalesce((SELECT array_agg(g.symbol ORDER BY g.symbol) FROM genes g WHERE g.id = ANY(b.gene_ids)), '{}') AS gene_symbols,85 (SELECT count(*) FROM cset c WHERE c.bid = b.id) AS cancers_n,86 (SELECT count(*) FROM dset d WHERE d.bid = b.id) AS drugs_n,87 (SELECT count(*) FROM drug_approvals a WHERE a.drug_id IN (SELECT d.drug_id FROM dset d WHERE d.bid = b.id) OR ${TERM_MATCH}) AS approvals_n,88 (SELECT count(*) FROM drug_approvals a WHERE a.tumor_agnostic AND (a.drug_id IN (SELECT d.drug_id FROM dset d WHERE d.bid = b.id) OR ${TERM_MATCH})) AS tumor_agnostic_n,89 (SELECT count(*) FROM tset x JOIN clinical_trials t ON t.id = x.trial_id WHERE x.bid = b.id AND ${ACTIVE}) AS active_trials_n90 FROM b ORDER BY b.kind, b.name LIMIT ${q.limit} OFFSET ${q.offset}`);91 const total = rows.length ? num(rows[0]!.total) : 0;92 const data = rows.map((r) => {93 const { total: _t, gene_symbols, cancers_n, drugs_n, approvals_n, tumor_agnostic_n, active_trials_n, ...rest } = r;94 return {95 ...shapeBiomarker(rest),96 geneSymbols: gene_symbols ?? [],97 derived: { cancers: num(cancers_n), drugs: num(drugs_n), approvals: num(approvals_n), tumorAgnosticApprovals: num(tumor_agnostic_n), activeTrials: num(active_trials_n), ...derived },98 };99 });100 return respond(app, data, ['ncit-evs', 'hgnc', 'civic', 'clinicaltrials', 'openfda', 'health-canada-dpd'], paginate(total, q.limit, q.offset));101 },102 );103104 app.get(105 '/biomarkers/:slug',106 {107 schema: {108 tags: ['biomarkers'],109 summary: 'Biomarker: curated metadata + derived cancers, drugs (direction/level), approvals (tumor-agnostic flagged), trials and literature',110 params: z.object({ slug: z.string().min(1).describe('Biomarker slug or CI-BIO-… id') }),111 querystring: z.object({ trialLimit: z.coerce.number().int().min(0).max(200).default(50), publicationLimit: z.coerce.number().int().min(0).max(200).default(50) }),112 response: ok(AnyRecord),113 },114 },115 async (req) => {116 const ref = req.params.slug.trim();117 const where = isCiId(ref, 'BIO') ? sql`b.id = ${ref}` : sql`b.slug = ${ref.toLowerCase()}`;118 const head = await app.db.execute<Record<string, unknown>>(sql`${scopeCte(where)} SELECT b.*, coalesce((SELECT array_agg(g.symbol ORDER BY g.symbol) FROM genes g WHERE g.id = ANY(b.gene_ids)), '{}') AS gene_symbols FROM b LIMIT 1`);119 const row = head[0];120 if (!row) throw new NotFound('biomarker', ref);121 const id = row.id as string;122 const geneIds = (row.gene_ids as string[]) ?? [];123 const variantIds = (row.variant_ids as string[]) ?? [];124 const byId = scopeCte(sql`b.id = ${id}`);125 const db = app.db;126 const lit = variantIds.length ? { type: 'variant', ids: variantIds } : { type: 'gene', ids: geneIds };127128 const [genes, variants, cancers, drugs, approvals, trialCounts, trials, pubs] = await Promise.all([129 geneIds.length ? db.execute<Record<string, unknown>>(sql`SELECT id, symbol, name, hgnc_id, is_cancer_gene FROM genes WHERE id = ANY(${sql.param(geneIds)}::text[]) ORDER BY symbol`) : Promise.resolve([]),130 variantIds.length ? db.execute<Record<string, unknown>>(sql`SELECT id, slug, name, gene_symbol, variant_type, civic_variant_id FROM variants WHERE id = ANY(${sql.param(variantIds)}::text[]) ORDER BY gene_symbol, name`) : Promise.resolve([]),131 db.execute<Record<string, unknown>>(sql`132 ${byId}133 SELECT c.id, c.slug, c.canonical_name AS name, count(*) AS n,134 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,135 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,136 count(*) FILTER (WHERE e.evidence_level = 'D') AS level_d, count(*) FILTER (WHERE e.evidence_level = 'E') AS level_e, min(p.source_id) AS source_id137 FROM b JOIN civic_evidence_items e ON ${EV_SCOPE} JOIN cancers c ON c.id = e.cancer_id LEFT JOIN provenance p ON p.id = e.provenance_id138 GROUP BY c.id, c.slug, c.canonical_name ORDER BY n DESC, c.canonical_name LIMIT 200`),139 db.execute<Record<string, unknown>>(sql`140 ${byId}, ${DSET},141 ev AS (SELECT t AS drug_id, e.significance, e.evidence_level, e.cancer_id, e.civic_id FROM b JOIN civic_evidence_items e ON ${EV_SCOPE} AND e.evidence_type = 'PREDICTIVE' CROSS JOIN LATERAL unnest(e.therapy_ids) t),142 ke AS (SELECT k.target_entity_id AS drug_id, k.direction, k.evidence_level, k.cancer_context_ids FROM b JOIN variants v ON ${VAR_SCOPE}143 JOIN knowledge_edges k ON k.source_entity_type = 'variant' AND k.source_entity_id = v.id AND k.target_entity_type = 'drug' AND k.relationship_type = 'PREDICTS_RESPONSE_TO' AND k.status = 'active')144 SELECT d.id, d.slug, d.name, d.kind,145 (SELECT count(*) FROM ev WHERE ev.drug_id = d.id) AS evidence_n,146 (SELECT count(*) FROM ev WHERE ev.drug_id = d.id AND ev.significance = 'SENSITIVITYRESPONSE') AS sensitivity,147 (SELECT count(*) FROM ev WHERE ev.drug_id = d.id AND ev.significance = 'RESISTANCE') AS resistance,148 (SELECT min(ev.evidence_level) FROM ev WHERE ev.drug_id = d.id) AS best_level,149 (SELECT array_agg(DISTINCT ev.civic_id) FROM ev WHERE ev.drug_id = d.id) AS civic_ids,150 (SELECT count(*) FROM ke WHERE ke.drug_id = d.id) AS edge_n,151 (SELECT count(*) FROM ke WHERE ke.drug_id = d.id AND ke.direction = 'sensitivity') AS edge_sensitivity,152 (SELECT count(*) FROM ke WHERE ke.drug_id = d.id AND ke.direction = 'resistance') AS edge_resistance,153 coalesce((SELECT array_agg(DISTINCT ev.cancer_id) FILTER (WHERE ev.cancer_id IS NOT NULL) FROM ev WHERE ev.drug_id = d.id), '{}') AS cancer_ids154 FROM dset JOIN drugs d ON d.id = dset.drug_id ORDER BY evidence_n DESC, edge_n DESC, d.name`),155 db.execute<Record<string, unknown>>(sql`156 ${byId}, ${DSET}157 SELECT a.id, a.drug_id, d.slug AS drug_slug, d.name AS drug_name, a.cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name, a.tumor_agnostic, a.jurisdiction, a.authority, a.indication,158 a.line_of_therapy, a.disease_stage, a.approval_type, a.accelerated, a.conditional, a.approval_date, a.withdrawal_date, a.status, a.application_number, a.source_id, p.source_url, p.retrieved_at, p.dataset_version,159 CASE WHEN a.drug_id IN (SELECT d2.drug_id FROM dset d2) AND ${TERM_MATCH} THEN 'both' WHEN a.drug_id IN (SELECT d2.drug_id FROM dset d2) THEN 'drug' ELSE 'indication' END AS matched_by160 FROM b, drug_approvals a JOIN drugs d ON d.id = a.drug_id LEFT JOIN cancers c ON c.id = a.cancer_id LEFT JOIN provenance p ON p.id = a.provenance_id161 WHERE a.drug_id IN (SELECT d2.drug_id FROM dset d2) OR ${TERM_MATCH}162 ORDER BY a.tumor_agnostic DESC, d.name, a.jurisdiction, a.approval_date DESC NULLS LAST LIMIT 500`),163 db.execute<{ total: string; active: string; recruiting: string; phase3: string }>(sql`164 ${byId}, ${DSET}, ${CSET}, ${TSET}165 SELECT count(*) AS total, count(*) FILTER (WHERE ${ACTIVE}) AS active, count(*) FILTER (WHERE t.overall_status = 'RECRUITING') AS recruiting, count(*) FILTER (WHERE ${ACTIVE} AND 'PHASE3' = ANY(t.phases)) AS phase3166 FROM tset x JOIN clinical_trials t ON t.id = x.trial_id`),167 req.query.trialLimit168 ? db.execute<Record<string, unknown>>(sql`169 ${byId}, ${DSET}, ${CSET}, ${TSET}170 SELECT t.id, t.nct_id, t.brief_title, t.phases, t.overall_status, t.study_type, t.start_date, t.enrollment_count, t.lead_sponsor, t.countries, t.last_update_posted_date171 FROM tset x JOIN clinical_trials t ON t.id = x.trial_id WHERE ${ACTIVE} ORDER BY t.last_update_posted_date DESC NULLS LAST, t.nct_id LIMIT ${req.query.trialLimit}`)172 : Promise.resolve([]),173 req.query.publicationLimit && lit.ids.length174 ? db.execute<Record<string, unknown>>(sql`175 SELECT p.id, p.pmid, p.doi, p.title, p.journal, p.pub_year, p.retracted, x.method, x.confidence, x.edge_status, x.source_id176 FROM (SELECT DISTINCT ON (e.publication_id) e.publication_id, e.method, e.confidence, e.status AS edge_status, e.source_id FROM publication_entity_edges e177 WHERE e.entity_type = ${lit.type} AND e.entity_id = ANY(${sql.param(lit.ids)}::text[]) AND e.status <> 'rejected' ORDER BY e.publication_id, e.status) x178 JOIN publications p ON p.id = x.publication_id ORDER BY p.pub_year DESC NULLS LAST, p.id LIMIT ${req.query.publicationLimit}`)179 : Promise.resolve([]),180 ]);181182 const tc = trialCounts[0] ?? { total: '0', active: '0', recruiting: '0', phase3: '0' };183 const tumorAgnosticRows = approvals.filter((a) => a.tumor_agnostic === true);184 const { gene_symbols, ...restRow } = row;185 const data = {186 ...shapeBiomarker(restRow),187 geneSymbols: gene_symbols ?? [],188 genes: camelRows(genes),189 variants: camelRows(variants),190 noScopeReason: geneIds.length || variantIds.length ? null : (((row.measurement as Record<string, unknown> | null)?.notes as string | undefined) ?? 'Not anchored to a gene or variant entity; gene-derived links cannot be computed.'),191 tumorAgnostic: {192 curated: ((row.measurement as Record<string, unknown> | null)?.tumorAgnostic ?? false) === true,193 approvalRows: tumorAgnosticRows.map((a) => ({ id: a.id, drug: { id: a.drug_id, slug: a.drug_slug, name: a.drug_name }, jurisdiction: a.jurisdiction, authority: a.authority, approvalDate: a.approval_date, status: a.status, matchedBy: a.matched_by, category: 'regulatory_status' })),194 rule: 'Rows of drug_approvals with tumor_agnostic = true (source flag) reached through the derived drug set or an indication-text match; nothing is asserted without a row.',195 },196 cancers: cancers.map((r) => ({197 cancer: { id: r.id, slug: r.slug, name: r.name },198 evidenceItems: num(r.n),199 byType: { predictive: num(r.predictive), prognostic: num(r.prognostic), diagnostic: num(r.diagnostic) },200 byLevel: { A: num(r.level_a), B: num(r.level_b), C: num(r.level_c), D: num(r.level_d), E: num(r.level_e) },201 sourceId: r.source_id,202 category: 'curated_evidence',203 })),204 drugs: drugs.map((r) => ({205 drug: { id: r.id, slug: r.slug, name: r.name, kind: r.kind },206 civic: { evidenceItems: num(r.evidence_n), sensitivity: num(r.sensitivity), resistance: num(r.resistance), bestLevel: r.best_level ?? null, civicEvidenceIds: r.civic_ids ?? [] },207 knowledgeEdges: { edges: num(r.edge_n), sensitivity: num(r.edge_sensitivity), resistance: num(r.edge_resistance) },208 cancerIds: r.cancer_ids ?? [],209 category: 'curated_evidence',210 })),211 approvals: approvals.map((r) => {212 const { drug_slug, drug_name, cancer_slug, cancer_name, source_url, retrieved_at, dataset_version, source_id, matched_by, ...rest } = r;213 return { ...camel(rest), drug: { id: r.drug_id, slug: drug_slug, name: drug_name }, cancer: r.cancer_id ? { id: r.cancer_id, slug: cancer_slug, name: cancer_name } : null, matchedBy: matched_by, provenance: { sourceId: source_id, url: source_url, retrievedAt: retrieved_at, datasetVersion: dataset_version, category: 'regulatory_status' } };214 }),215 trials: {216 counts: { total: num(tc.total), active: num(tc.active), recruiting: num(tc.recruiting), phase3Active: num(tc.phase3) },217 activeStatuses: ACTIVE_STATUSES,218 rows: camelRows(trials),219 category: 'published_evidence',220 },221 publications: { entityType: lit.type, rows: pubs.map((r) => {222 const { method, confidence, edge_status, source_id, ...rest } = r;223 return { ...camel(rest), edge: { method, confidence, status: edge_status, sourceId: source_id } };224 }) },225 derived: { cancers: cancers.length, drugs: drugs.length, approvals: approvals.length, tumorAgnosticApprovals: tumorAgnosticRows.length, activeTrials: num(tc.active), ...derived },226 };227 return respond(app, data, ['ncit-evs', 'hgnc', 'civic', 'clinicaltrials', 'pubmed', ...pluck(cancers, 'source_id'), ...pluck(approvals, 'source_id'), ...pluck(pubs, 'source_id')]);228 },229 );230};231