spb/cancerindex
Public
TypeScript 97.2%
SQL 1.5%
CSS 0.6%
JavaScript 0.5%
1import 'server-only';2import { run, sql, safe } from '@/lib/db';34export interface EvidenceItem {5 id: number;6 civic_id: number;7 name: string | null;8 molecular_profile_id: number | null;9 molecular_profile_name: string | null;10 gene_symbols: string[];11 gene_ids: string[];12 variant_ids: string[];13 disease_name: string | null;14 cancer_id: string | null;15 cancer_slug: string | null;16 cancer_name: string | null;17 cancer_match_type: string | null;18 therapy_names: string[];19 therapy_ids: string[];20 therapy_interaction_type: string | null;21 evidence_type: string | null;22 evidence_level: string | null;23 evidence_direction: string | null;24 significance: string | null;25 evidence_rating: number | null;26 status: string | null;27 /** Excerpt (EVIDENCE_DESCRIPTION_CHARS), never the full text. */28 description: string | null;29 pmid: string | null;30 source_citation: string | null;31 provenance_id: number;32 updated_at: Date;33 variant_slugs: string[] | null;34 variant_names: string[] | null;35 therapy_slugs: string[] | null;36 /** Drug names aligned with therapy_slugs (same ORDER BY) — never zip with therapy_names, whose order is CIViC's. */37 therapy_slug_names: string[] | null;38}3940/** Description excerpt length shipped per row (the full text is one click away at CIViC). */41export const EVIDENCE_DESCRIPTION_CHARS = 200;4243const SELECT = sql`44 SELECT e.id, e.civic_id, e.name, e.molecular_profile_id, e.molecular_profile_name, e.gene_symbols, e.gene_ids, e.variant_ids, e.disease_name, e.cancer_id, e.cancer_match_type,45 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,46 e.pmid, e.source_citation, e.provenance_id, e.updated_at,47 c.slug AS cancer_slug, c.canonical_name AS cancer_name,48 (SELECT array_agg(v.slug ORDER BY v.slug) FROM variants v WHERE v.id = ANY(e.variant_ids)) AS variant_slugs,49 (SELECT array_agg(coalesce(v.gene_symbol || ' ', '') || v.name ORDER BY v.slug) FROM variants v WHERE v.id = ANY(e.variant_ids)) AS variant_names,50 (SELECT array_agg(d.slug ORDER BY d.slug) FROM drugs d WHERE d.id = ANY(e.therapy_ids)) AS therapy_slugs,51 (SELECT array_agg(d.name ORDER BY d.slug) FROM drugs d WHERE d.id = ANY(e.therapy_ids)) AS therapy_slug_names,52 left(e.description, ${EVIDENCE_DESCRIPTION_CHARS}) AS description53 FROM civic_evidence_items e LEFT JOIN cancers c ON c.id = e.cancer_id`;5455export const EVIDENCE_PAGE_SIZE = 50;5657export interface Page {58 page: number;59 pageSize: number;60}6162/**63 * Stable, group-preserving order so server-side pages never split a (profile → therapy) group at64 * random: molecular profile, then therapy, then level, then the CIViC id. `showCancer` contexts65 * (gene / drug / publication pages) sort by cancer first so the per-cancer grouping stays contiguous.66 */67const ORDER_BY_PROFILE = sql`ORDER BY coalesce(e.molecular_profile_name, e.name) NULLS LAST, array_to_string(e.therapy_names, '+'), e.evidence_level NULLS LAST, e.evidence_rating DESC NULLS LAST, e.civic_id`;68const ORDER_BY_CANCER = sql`ORDER BY c.canonical_name NULLS LAST, e.disease_name NULLS LAST, coalesce(e.molecular_profile_name, e.name) NULLS LAST, array_to_string(e.therapy_names, '+'), e.evidence_level NULLS LAST, e.civic_id`;6970function pageClause(p: Page) {71 return sql`LIMIT ${p.pageSize} OFFSET ${(Math.max(1, p.page) - 1) * p.pageSize}`;72}7374async function count(where: ReturnType<typeof sql>): Promise<number> {75 const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM civic_evidence_items e WHERE ${where}`), [{ n: '0' }]);76 return Number(r[0]?.n ?? 0);77}7879const whereCancer = (cancerIds: string[]) => sql`e.cancer_id IN (${sql.join(cancerIds.map((i) => sql`${i}`), sql`, `)})`;80const whereVariant = (variantId: string) => sql`${variantId} = ANY(e.variant_ids)`;81const whereGene = (geneId: string, symbol: string) => sql`(${geneId} = ANY(e.gene_ids) OR ${symbol} = ANY(e.gene_symbols))`;82const whereDrug = (drugId: string) => sql`${drugId} = ANY(e.therapy_ids)`;83const wherePmid = (pmid: string) => sql`e.pmid = ${pmid}`;8485export async function evidenceForCancer(cancerIds: string[], p: Page = { page: 1, pageSize: EVIDENCE_PAGE_SIZE }): Promise<EvidenceItem[]> {86 if (cancerIds.length === 0) return [];87 return safe(() => run<EvidenceItem>(sql`${SELECT} WHERE ${whereCancer(cancerIds)} ${ORDER_BY_PROFILE} ${pageClause(p)}`), [] as EvidenceItem[]);88}89export async function evidenceForCancerCount(cancerIds: string[]): Promise<number> {90 if (cancerIds.length === 0) return 0;91 return count(whereCancer(cancerIds));92}9394/** Lightweight rows for the Drugs tab "therapies in evidence" aggregate: no description, no joins. */95export async function therapyMentionsForCancer(cancerIds: string[]): Promise<Array<{ slug: string; name: string; n: number; sensitivity: number; resistance: number }>> {96 if (cancerIds.length === 0) return [];97 const rows = await safe(98 () =>99 run<{ slug: string; name: string; n: string; sensitivity: string; resistance: string }>(sql`100 SELECT d.slug, d.name, count(*) AS n,101 count(*) FILTER (WHERE e.significance ILIKE '%SENSITIV%') AS sensitivity,102 count(*) FILTER (WHERE e.significance ILIKE '%RESIST%') AS resistance103 FROM civic_evidence_items e JOIN drugs d ON d.id = ANY(e.therapy_ids)104 WHERE ${whereCancer(cancerIds)} GROUP BY d.slug, d.name ORDER BY n DESC, d.name`),105 [],106 );107 return rows.map((r) => ({ slug: r.slug, name: r.name, n: Number(r.n), sensitivity: Number(r.sensitivity), resistance: Number(r.resistance) }));108}109110export async function evidenceForVariant(variantId: string, p: Page = { page: 1, pageSize: EVIDENCE_PAGE_SIZE }): Promise<EvidenceItem[]> {111 return safe(() => run<EvidenceItem>(sql`${SELECT} WHERE ${whereVariant(variantId)} ${ORDER_BY_CANCER} ${pageClause(p)}`), [] as EvidenceItem[]);112}113export async function evidenceForVariantCount(variantId: string): Promise<number> {114 return count(whereVariant(variantId));115}116117export async function evidenceForGene(geneId: string, symbol: string, p: Page = { page: 1, pageSize: EVIDENCE_PAGE_SIZE }): Promise<EvidenceItem[]> {118 return safe(() => run<EvidenceItem>(sql`${SELECT} WHERE ${whereGene(geneId, symbol)} ${ORDER_BY_PROFILE} ${pageClause(p)}`), [] as EvidenceItem[]);119}120export async function evidenceForGeneCount(geneId: string, symbol: string): Promise<number> {121 return count(whereGene(geneId, symbol));122}123/** Cancers appearing in a gene's evidence with item counts (facet chips above the paginated table). */124export async function evidenceCancersForGene(geneId: string, symbol: string, limit = 40): Promise<Array<{ slug: string; name: string; n: number }>> {125 const rows = await safe(126 () => run<{ slug: string; name: string; n: string }>(sql`SELECT c.slug, c.canonical_name AS name, count(*) AS n FROM civic_evidence_items e JOIN cancers c ON c.id = e.cancer_id WHERE ${whereGene(geneId, symbol)} GROUP BY c.slug, c.canonical_name ORDER BY n DESC, c.canonical_name LIMIT ${limit}`),127 [],128 );129 return rows.map((r) => ({ slug: r.slug, name: r.name, n: Number(r.n) }));130}131132export async function evidenceForDrug(drugId: string, p: Page = { page: 1, pageSize: EVIDENCE_PAGE_SIZE }): Promise<EvidenceItem[]> {133 return safe(() => run<EvidenceItem>(sql`${SELECT} WHERE ${whereDrug(drugId)} ${ORDER_BY_CANCER} ${pageClause(p)}`), [] as EvidenceItem[]);134}135export async function evidenceForDrugCount(drugId: string): Promise<number> {136 return count(whereDrug(drugId));137}138139export async function evidenceForPublication(pmid: string, p: Page = { page: 1, pageSize: EVIDENCE_PAGE_SIZE }): Promise<EvidenceItem[]> {140 return safe(() => run<EvidenceItem>(sql`${SELECT} WHERE ${wherePmid(pmid)} ${ORDER_BY_CANCER} ${pageClause(p)}`), [] as EvidenceItem[]);141}142export async function evidenceForPublicationCount(pmid: string): Promise<number> {143 return count(wherePmid(pmid));144}145146export const EVIDENCE_LEVEL_LABEL: Record<string, string> = {147 A: 'A — Validated association',148 B: 'B — Clinical evidence',149 C: 'C — Case study',150 D: 'D — Preclinical evidence',151 E: 'E — Inferential association',152};153154export type EvidenceGroupBy = 'variant' | 'cancer' | 'none';155156export interface EvidenceGroup {157 key: string;158 label: string;159 href: string | null;160 genes: string[];161 unmapped: boolean;162 items: EvidenceItem[];163}164165/** Therapy key used to collapse consecutive rows of the same therapy inside a group (〃 marker). */166export function therapyKey(e: EvidenceItem): string {167 return e.therapy_names.length ? e.therapy_names.join(' + ') : e.evidence_type === 'PREDICTIVE' ? 'Unspecified therapy' : `(${(e.evidence_type ?? 'evidence').toLowerCase()})`;168}169170/**171 * Group evidence items (never collapsed to works/doesn't work). Groups and rows keep the incoming172 * (SQL) order so a paginated table reads the same way as the query orders it; within a variant173 * group, rows of the same therapy stay adjacent because the query orders by therapy.174 */175export function groupEvidence(items: EvidenceItem[], by: EvidenceGroupBy = 'variant'): EvidenceGroup[] {176 if (by === 'none') return items.length ? [{ key: 'all', label: '', href: null, genes: [], unmapped: false, items }] : [];177 const groups = new Map<string, EvidenceGroup>();178 for (const e of items) {179 let key: string;180 let g: EvidenceGroup | undefined;181 if (by === 'cancer') {182 key = e.cancer_id ?? `unmapped:${e.disease_name ?? 'unknown'}`;183 g = groups.get(key);184 if (!g) g = { key, label: e.cancer_name ?? e.disease_name ?? 'Unmapped disease', href: e.cancer_slug ? `/cancer/${e.cancer_slug}/evidence` : null, genes: [], unmapped: !e.cancer_slug, items: [] };185 } else {186 key = e.variant_ids.length ? e.variant_ids.join('+') : `mp-${e.molecular_profile_id ?? e.civic_id}`;187 g = groups.get(key);188 if (!g) g = { key, label: e.variant_names?.join(' + ') ?? e.molecular_profile_name ?? e.name ?? `CIViC EID${e.civic_id}`, href: e.variant_slugs?.length === 1 ? `/variant/${e.variant_slugs[0]!}` : null, genes: e.gene_symbols, unmapped: false, items: [] };189 }190 groups.set(key, g);191 g.items.push(e);192 }193 return [...groups.values()];194}195