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 { boolQuery, pageQuery } from '../lib/pagination.js';6import { resolveGene } from '../lib/resolve.js';7import { AnyList, AnyRecord, camel, camelRows, num, ok, respond } from '../lib/respond.js';8import { pluck } from '../lib/sources.js';910export const geneRoutes: FastifyPluginAsyncZod = async (app) => {11  app.get('/genes', { schema: { tags: ['genes'], summary: 'List genes (HGNC symbols)', querystring: z.object({ q: z.string().trim().min(1).max(50).optional().describe('Symbol/alias prefix'), cancerOnly: boolQuery.describe('Only genes with curated or cohort cancer evidence'), sort: z.enum(['symbol', 'evidence']).default('symbol'), ...pageQuery }), response: ok(AnyList, true) } }, async (req) => {12    const q = req.query;13    const conds = [sql`g.status = 'Approved'`];14    if (q.cancerOnly) conds.push(sql`g.is_cancer_gene`);15    if (q.q) {16      const up = q.q.toUpperCase();17      conds.push(sql`(upper(g.symbol) LIKE ${up + '%'} OR EXISTS (SELECT 1 FROM gene_aliases a WHERE a.gene_id = g.id AND upper(a.alias) LIKE ${up + '%'}))`);18    }19    const order = q.sort === 'evidence' ? sql`ec.evidence_count DESC NULLS LAST, g.symbol` : sql`g.symbol`;20    const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`21      SELECT g.id, g.hgnc_id, g.symbol, g.name, g.locus_type, g.location, g.chromosome, g.ensembl_gene_id, g.ncbi_gene_id, g.is_cancer_gene, g.civic_gene_id,22        ec.evidence_count, ec.variant_count, ec.drug_count, ec.updated_at AS counters_computed_at, count(*) OVER() AS total23      FROM genes g LEFT JOIN entity_counters ec ON ec.entity_type = 'gene' AND ec.entity_id = g.id24      WHERE ${sql.join(conds, sql` AND `)} ORDER BY ${order} LIMIT ${q.limit} OFFSET ${q.offset}`);25    const total = rows.length ? num(rows[0]!.total) : 0;26    const data = rows.map((r) => {27      const { total: _t, ...rest } = r;28      return camel(rest);29    });30    return respond(app, data, ['hgnc'], paginate(total, q.limit, q.offset));31  });3233  app.get('/genes/:symbol', { schema: { tags: ['genes'], summary: 'Gene: aliases, counters, linked cancers (curated evidence + cohort frequencies), variants, drugs, literature edges', params: z.object({ symbol: z.string().min(1).describe('HGNC symbol, alias, HGNC:id or CI-GENE-… id') }), response: ok(AnyRecord) } }, async (req) => {34    const { id } = await resolveGene(app.db, req.params.symbol);35    const db = app.db;36    const [gene, aliases, civicCancers, cohortCancers, variants, drugs, pubs] = await Promise.all([37      db.execute<Record<string, unknown>>(sql`SELECT g.*, ec.evidence_count, ec.variant_count, ec.drug_count, ec.updated_at AS counters_computed_at38        FROM genes g LEFT JOIN entity_counters ec ON ec.entity_type = 'gene' AND ec.entity_id = g.id WHERE g.id = ${id}`),39      db.execute<Record<string, unknown>>(sql`SELECT alias, alias_type, source_id FROM gene_aliases WHERE gene_id = ${id} ORDER BY alias_type, alias`),40      db.execute<Record<string, unknown>>(sql`41        SELECT c.id, c.slug, c.canonical_name AS name, c.top_level, count(*) AS evidence_items,42          count(*) FILTER (WHERE e.evidence_type = 'PREDICTIVE') AS predictive, count(*) FILTER (WHERE e.evidence_type = 'PROGNOSTIC') AS prognostic,43          count(*) FILTER (WHERE e.evidence_type = 'DIAGNOSTIC') AS diagnostic, count(*) FILTER (WHERE e.evidence_type = 'PREDISPOSING') AS predisposing,44          count(*) FILTER (WHERE e.evidence_level IN ('A','B')) AS level_ab, min(p.source_id) AS source_id45        FROM civic_evidence_items e JOIN cancers c ON c.id = e.cancer_id LEFT JOIN provenance p ON p.id = e.provenance_id46        WHERE e.status = 'ACCEPTED' AND ${id} = ANY(e.gene_ids)47        GROUP BY c.id ORDER BY evidence_items DESC, c.canonical_name LIMIT 200`),48      db.execute<Record<string, unknown>>(sql`49        SELECT f.id, f.cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name, f.alteration_type, f.cases_affected, f.cases_profiled, f.frequency, f.rank,50          co.id AS cohort_id, co.study_id, co.name AS cohort_name, co.program, co.source_id, p.retrieved_at, p.source_url, p.dataset_version51        FROM cancer_gene_frequencies f JOIN genomic_cohorts co ON co.id = f.cohort_id LEFT JOIN cancers c ON c.id = f.cancer_id LEFT JOIN provenance p ON p.id = f.provenance_id52        WHERE f.gene_id = ${id} ORDER BY f.frequency DESC LIMIT 200`),53      db.execute<Record<string, unknown>>(sql`54        SELECT v.id, v.slug, v.name, v.variant_type, v.hgvs_p, v.hgvs_c, v.clinvar_variation_id, v.civic_variant_id,55          (SELECT count(*) FROM civic_evidence_items e WHERE e.status = 'ACCEPTED' AND v.id = ANY(e.variant_ids)) AS evidence_items,56          (SELECT cs.clinical_significance FROM variant_clinical_significance cs WHERE cs.variant_id = v.id LIMIT 1) AS clinical_significance57        FROM variants v WHERE v.gene_id = ${id} ORDER BY evidence_items DESC, v.name LIMIT 500`),58      db.execute<Record<string, unknown>>(sql`59        SELECT d.id, d.slug, d.name, d.kind, count(*) AS evidence_items,60          count(*) FILTER (WHERE e.significance = 'SENSITIVITYRESPONSE') AS sensitivity, count(*) FILTER (WHERE e.significance = 'RESISTANCE') AS resistance,61          array_agg(DISTINCT e.cancer_id) FILTER (WHERE e.cancer_id IS NOT NULL) AS cancer_ids, min(p.source_id) AS source_id62        FROM civic_evidence_items e CROSS JOIN LATERAL unnest(e.therapy_ids) tid JOIN drugs d ON d.id = tid LEFT JOIN provenance p ON p.id = e.provenance_id63        WHERE e.status = 'ACCEPTED' AND ${id} = ANY(e.gene_ids)64        GROUP BY d.id ORDER BY evidence_items DESC, d.name LIMIT 200`),65      db.execute<Record<string, unknown>>(sql`66        SELECT p.id, p.pmid, p.doi, p.title, p.journal, p.pub_year, p.retracted, e.method, e.confidence, e.status AS edge_status, e.source_id67        FROM publication_entity_edges e JOIN publications p ON p.id = e.publication_id68        WHERE e.entity_type = 'gene' AND e.entity_id = ${id} AND e.status <> 'rejected' ORDER BY p.pub_year DESC NULLS LAST LIMIT 100`),69    ]);70    const g = camel<Record<string, unknown>>(gene[0]!);71    const counters = g.countersComputedAt ? { evidenceItems: num(g.evidenceCount), variants: num(g.variantCount), drugs: num(g.drugCount), computedAt: g.countersComputedAt } : null;72    delete g.evidenceCount;73    delete g.variantCount;74    delete g.drugCount;75    delete g.countersComputedAt;76    const data = {77      ...g,78      counters,79      aliases: camelRows(aliases),80      cancers: {81        curatedEvidence: civicCancers.map((r) => ({ cancer: { id: r.id, slug: r.slug, name: r.name, topLevel: r.top_level }, evidenceItems: num(r.evidence_items), byType: { predictive: num(r.predictive), prognostic: num(r.prognostic), diagnostic: num(r.diagnostic), predisposing: num(r.predisposing) }, levelAB: num(r.level_ab), sourceId: r.source_id, category: 'curated_evidence' })),82        cohortFrequencies: cohortCancers.map((r) => ({ id: r.id, cancer: r.cancer_id ? { id: r.cancer_id, slug: r.cancer_slug, name: r.cancer_name } : null, alterationType: r.alteration_type, casesAffected: r.cases_affected, casesProfiled: r.cases_profiled, frequency: r.frequency, rank: r.rank, cohort: { id: r.cohort_id, studyId: r.study_id, name: r.cohort_name, program: r.program }, provenance: { sourceId: r.source_id, retrievedAt: r.retrieved_at, url: r.source_url, datasetVersion: r.dataset_version, category: 'observed_data' } })),83      },84      variants: camelRows(variants),85      drugs: drugs.map((r) => ({ drug: { id: r.id, slug: r.slug, name: r.name, kind: r.kind }, evidenceItems: num(r.evidence_items), sensitivity: num(r.sensitivity), resistance: num(r.resistance), cancerIds: r.cancer_ids ?? [], sourceId: r.source_id })),86      publications: pubs.map((r) => {87        const { method, confidence, edge_status, source_id, ...rest } = r;88        return { ...camel(rest), edge: { method, confidence, status: edge_status, sourceId: source_id } };89      }),90    };91    return respond(app, data, ['hgnc', ...pluck(civicCancers, 'source_id'), ...pluck(cohortCancers, 'source_id'), ...pluck(drugs, 'source_id'), ...pluck(pubs, 'source_id'), ...pluck(aliases, 'source_id')]);92  });93};94