spb/cancerindex

1import { sql } from 'drizzle-orm';2import type { FastifyPluginAsyncZod } from 'fastify-type-provider-zod';3import { z } from 'zod';4import { resolveVariant } from '../lib/resolve.js';5import { AnyRecord, camel, camelRows, ok, respond } from '../lib/respond.js';6import { pluck } from '../lib/sources.js';78export const variantRoutes: FastifyPluginAsyncZod = async (app) => {9  app.get('/variants/:id', { schema: { tags: ['variants'], summary: 'Variant: coordinates per assembly, ClinVar interpretations (structured), CIViC evidence grouped by cancer (§50), knowledge edges', params: z.object({ id: z.string().min(1).describe('CI-VAR-… id or slug') }), response: ok(AnyRecord) } }, async (req) => {10    const { id } = await resolveVariant(app.db, req.params.id);11    const db = app.db;12    const [variant, aliases, clinsig, evidence, edges, pubs] = await Promise.all([13      db.execute<Record<string, unknown>>(sql`SELECT v.*, g.name AS gene_name, g.hgnc_id FROM variants v LEFT JOIN genes g ON g.id = v.gene_id WHERE v.id = ${id}`),14      db.execute<Record<string, unknown>>(sql`SELECT alias, source_id FROM variant_aliases WHERE variant_id = ${id} ORDER BY alias`),15      db.execute<Record<string, unknown>>(sql`SELECT cs.*, p.source_id, p.source_url, p.retrieved_at, p.dataset_version FROM variant_clinical_significance cs LEFT JOIN provenance p ON p.id = cs.provenance_id WHERE cs.variant_id = ${id}`),16      db.execute<Record<string, unknown>>(sql`17        SELECT e.civic_id, e.name, e.molecular_profile_name, e.disease_name, e.doid, e.cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name, e.cancer_match_type,18          e.therapy_names, e.therapy_ids, e.therapy_interaction_type, e.evidence_type, e.evidence_level, e.evidence_direction, e.significance, e.evidence_rating, e.status, e.description, e.pmid, e.source_citation, e.phenotypes,19          p.source_id, p.source_url, p.retrieved_at20        FROM civic_evidence_items e LEFT JOIN cancers c ON c.id = e.cancer_id LEFT JOIN provenance p ON p.id = e.provenance_id21        WHERE ${id} = ANY(e.variant_ids) ORDER BY e.cancer_id NULLS LAST, e.evidence_level, e.civic_id`),22      db.execute<Record<string, unknown>>(sql`23        SELECT k.id, k.source_entity_type, k.source_entity_id, k.target_entity_type, k.target_entity_id, k.relationship_type, k.cancer_context_ids, k.direction, k.evidence_level, k.evidence_category, k.status, k.source_id, k.support_count, k.last_seen_at24        FROM knowledge_edges k WHERE (k.source_entity_type = 'variant' AND k.source_entity_id = ${id}) OR (k.target_entity_type = 'variant' AND k.target_entity_id = ${id}) ORDER BY k.relationship_type LIMIT 500`),25      db.execute<Record<string, unknown>>(sql`26        SELECT p.id, p.pmid, p.doi, p.title, p.journal, p.pub_year, p.retracted, e.method, e.status AS edge_status, e.source_id27        FROM publication_entity_edges e JOIN publications p ON p.id = e.publication_id WHERE e.entity_type = 'variant' AND e.entity_id = ${id} AND e.status <> 'rejected' ORDER BY p.pub_year DESC NULLS LAST LIMIT 100`),28    ]);29    // Evidence separated by cancer context — never pooled across diseases (CLAUDE.md §50).30    const byCancer = new Map<string, { cancer: Record<string, unknown> | null; items: Array<Record<string, unknown>>; accepted: number }>();31    for (const e of evidence) {32      const key = (e.cancer_id as string | null) ?? `unmapped:${e.disease_name ?? 'unknown'}`;33      if (!byCancer.has(key)) byCancer.set(key, { cancer: e.cancer_id ? { id: e.cancer_id, slug: e.cancer_slug, name: e.cancer_name, matchType: e.cancer_match_type } : null, items: [], accepted: 0 });34      const grp = byCancer.get(key)!;35      if (e.status === 'ACCEPTED') grp.accepted++;36      const { cancer_slug: _a, cancer_name: _b, cancer_match_type: _c, source_id, source_url, retrieved_at, ...rest } = e;37      grp.items.push({ ...camel(rest), provenance: { sourceId: source_id, url: source_url, retrievedAt: retrieved_at, category: 'curated_evidence' } });38    }39    const data = {40      ...camel(variant[0]!),41      aliases: camelRows(aliases),42      clinicalSignificance: clinsig.map((r) => {43        const { source_id, source_url, retrieved_at, dataset_version, provenance_id: _p, ...rest } = r;44        return { ...camel(rest), provenance: { sourceId: source_id, url: source_url, retrievedAt: retrieved_at, datasetVersion: dataset_version, category: 'curated_evidence' } };45      }),46      evidenceByCancer: [...byCancer.values()].map((g) => ({ cancer: g.cancer, acceptedItems: g.accepted, items: g.items })),47      edges: camelRows(edges),48      publications: pubs.map((r) => {49        const { method, edge_status, source_id, ...rest } = r;50        return { ...camel(rest), edge: { method, status: edge_status, sourceId: source_id } };51      }),52    };53    return respond(app, data, [...pluck(clinsig, 'source_id'), ...pluck(evidence, 'source_id'), ...pluck(edges, 'source_id'), ...pluck(pubs, 'source_id'), ...pluck(aliases, 'source_id')]);54  });55};56