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';3import type { SQL } from 'drizzle-orm';4import { getDescendantIds } from '@/lib/queries/cancers';5import { ACTIVE_STATUSES } from '@/lib/queries/trials';6import { type CancerContext, type EdgeGroup, type FocusRef, type GraphEdge, type GraphNode, type Neighborhood, type NodeType, type PathChain, compareChains, nodeKey, sortGroups } from '@/lib/graph-model';78/**9 * Knowledge-graph neighbourhood queries. Two families of links:10 * - source-native `knowledge_edges` rows (CIViC, ChEMBL, openFDA…) — never inferred by CancerIndex,11 * aggregated for display per (neighbour, relationship, direction, evidence level, source);12 * - derived relational links computed at query time from registry tables (trial_conditions,13 * trial_interventions, cancer_gene_frequencies, drug_approvals, civic_evidence_items) — always14 * flagged `derived: true` with the count / measurement that backs them.15 * Schema is frozen: everything is derived at query time with per-group LIMITs.16 *17 * The public API (`apps/api/src/routes/graph.ts`) duplicates this SQL: this module is `server-only`18 * and depends on the web `@/lib/db` helpers, so it cannot be imported from the Fastify app.19 */2021export const DEFAULT_GROUP_LIMIT = 15;22export const EXPANDED_GROUP_LIMIT = 200;23export const TRIAL_GROUP_LIMIT = 10;24/** Cohort thresholds for the derived gene ↔ cancer link (frequency and cases affected). */25export const FREQ_MIN = 0.05;26export const CASES_MIN = 20;27export const PATHS_LIMIT = 8;28/** Cap on descendant ids rolled into a cancer focus (very broad families are truncated to their first N ids). */29export const MAX_DESCENDANTS = 600;3031const inList = (ids: string[]): SQL => sql`(${sql.join(ids.map((i) => sql`${i}`), sql`, `)})`;32const activeList = (): SQL => sql`(${sql.join(ACTIVE_STATUSES.map((s) => sql`${s}`), sql`, `)})`;33const n = (v: unknown): number => (v === null || v === undefined ? 0 : Number(v));34const groupLimit = (rel: string, more: string | null | undefined, base = DEFAULT_GROUP_LIMIT): number => (more && more.toUpperCase() === rel ? EXPANDED_GROUP_LIMIT : base);3536/** Native-scale rank used only for ORDER BY (never shown; the native level is what the UI displays). */37const LEVEL_RANK = sql`CASE upper(coalesce(ke.evidence_level, '')) WHEN 'A' THEN 0 WHEN 'FDA ORIG' THEN 0 WHEN 'B' THEN 1 WHEN '4' THEN 1 WHEN 'C' THEN 2 WHEN '3' THEN 2 WHEN 'D' THEN 3 WHEN '2' THEN 3 WHEN 'E' THEN 4 WHEN '1' THEN 4 WHEN '' THEN 99 ELSE 50 END`;38const CIVIC_LEVEL_RANK = sql`CASE e.evidence_level WHEN 'A' THEN 0 WHEN 'B' THEN 1 WHEN 'C' THEN 2 WHEN 'D' THEN 3 WHEN 'E' THEN 4 ELSE 99 END`;3940const hrefFor = (type: NodeType, ref: string): string => {41 switch (type) {42 case 'cancer':43 return `/cancer/${ref}`;44 case 'gene':45 return `/gene/${ref}`;46 case 'variant':47 return `/variant/${ref}`;48 case 'drug':49 return `/drug/${ref}`;50 case 'trial':51 return `/trial/${ref}`;52 default:53 return ref;54 }55};5657// ---------------------------------------------------------------------------------------------58// Focus resolution59// ---------------------------------------------------------------------------------------------6061export interface FocusNode extends GraphNode {62 type: Exclude<NodeType, 'approval'>;63 ref: string;64}6566export async function resolveFocus(f: FocusRef): Promise<FocusNode | null> {67 const ref = f.ref.trim();68 const isCi = /^CI-[A-Z]+-\d+$/i.test(ref);69 type Row = { id: string; ref: string; label: string; sublabel: string | null };70 let rows: Row[] = [];71 switch (f.type) {72 case 'cancer':73 rows = await safe(() => run<Row>(sql`SELECT id, slug AS ref, canonical_name AS label, entity_type AS sublabel FROM cancers WHERE ${isCi ? sql`id = ${ref.toUpperCase()}` : sql`slug = ${ref.toLowerCase()}`} LIMIT 1`), []);74 break;75 case 'gene':76 rows = await safe(77 () =>78 run<Row>(sql`SELECT g.id, g.symbol AS ref, g.symbol AS label, g.name AS sublabel FROM genes g WHERE ${isCi ? sql`g.id = ${ref.toUpperCase()}` : sql`upper(g.symbol) = upper(${ref}) OR g.hgnc_id = ${ref}`}79 UNION ALL SELECT g.id, g.symbol, g.symbol, g.name FROM genes g JOIN gene_aliases a ON a.gene_id = g.id WHERE upper(a.alias) = upper(${ref}) LIMIT 1`),80 [],81 );82 break;83 case 'variant':84 rows = await safe(() => run<Row>(sql`SELECT id, slug AS ref, coalesce(gene_symbol || ' ', '') || name AS label, variant_type AS sublabel FROM variants WHERE ${isCi ? sql`id = ${ref.toUpperCase()}` : sql`slug = ${ref.toLowerCase()}`} LIMIT 1`), []);85 break;86 case 'drug':87 rows = await safe(() => run<Row>(sql`SELECT id, slug AS ref, name AS label, kind AS sublabel FROM drugs WHERE ${isCi ? sql`id = ${ref.toUpperCase()}` : sql`slug = ${ref.toLowerCase()}`} LIMIT 1`), []);88 break;89 case 'trial':90 rows = await safe(() => run<Row>(sql`SELECT id, nct_id AS ref, brief_title AS label, overall_status AS sublabel FROM clinical_trials WHERE ${isCi ? sql`id = ${ref.toUpperCase()}` : sql`upper(nct_id) = upper(${ref})`} LIMIT 1`), []);91 break;92 }93 const r = rows[0];94 if (!r) return null;95 return { type: f.type, id: r.id, ref: r.ref, label: r.label, sublabel: r.sublabel, href: hrefFor(f.type, r.ref), degree: 0 };96}9798// ---------------------------------------------------------------------------------------------99// Source-native knowledge edges100// ---------------------------------------------------------------------------------------------101102interface KeRow {103 relationship_type: string;104 outgoing: boolean;105 ctx_only: boolean;106 n_type: NodeType;107 n_id: string;108 n_ref: string | null;109 n_label: string | null;110 n_sublabel: string | null;111 via_type: NodeType | null;112 via_id: string | null;113 via_ref: string | null;114 via_label: string | null;115 direction: string | null;116 evidence_level: string | null;117 evidence_category: string;118 source_id: string;119 source_slug: string;120 support: string;121 edge_ids: number[];122 provenance_ids: number[];123 context_ids: string[];124 last_seen: Date | null;125 total: string;126 rn: string;127}128129/**130 * Edges where the focus is the source or the target (both directions), plus — for a cancer focus —131 * edges where the cancer is only the *context* (variant → drug in this cancer), aggregated per132 * (neighbour, relationship, direction, level, source). Per-relationship LIMIT via row_number().133 */134async function knowledgeEdges(focus: FocusNode, more: string | null): Promise<KeRow[]> {135 const t = focus.type;136 const id = focus.id;137 const ctx = t === 'cancer' ? sql`OR (${id} = ANY(ke.cancer_context_ids) AND ke.source_entity_id <> ${id} AND ke.target_entity_id <> ${id})` : sql``;138 return safe(139 () =>140 run<KeRow>(sql`141 WITH e AS (142 SELECT ke.relationship_type,143 (ke.source_entity_type = ${t} AND ke.source_entity_id = ${id}) AS outgoing,144 (ke.source_entity_id <> ${id} AND ke.target_entity_id <> ${id}) AS ctx_only,145 CASE WHEN ke.source_entity_id = ${id} THEN ke.target_entity_type ELSE ke.source_entity_type END AS n_type,146 CASE WHEN ke.source_entity_id = ${id} THEN ke.target_entity_id ELSE ke.source_entity_id END AS n_id,147 CASE WHEN ke.source_entity_id <> ${id} AND ke.target_entity_id <> ${id} THEN ke.target_entity_type END AS via_type,148 CASE WHEN ke.source_entity_id <> ${id} AND ke.target_entity_id <> ${id} THEN ke.target_entity_id END AS via_id,149 ke.direction, ke.evidence_level, ke.evidence_category, ke.source_id, ke.id, ke.provenance_ids, ke.cancer_context_ids, ke.support_count, ke.last_seen_at,150 ${LEVEL_RANK} AS lvl151 FROM knowledge_edges ke152 WHERE ke.status = 'active' AND ((ke.source_entity_type = ${t} AND ke.source_entity_id = ${id}) OR (ke.target_entity_type = ${t} AND ke.target_entity_id = ${id}) ${ctx})153 ), a AS (154 SELECT relationship_type, outgoing, ctx_only, n_type, n_id, via_type, via_id, direction, evidence_level, evidence_category, source_id, min(lvl) AS lvl,155 sum(support_count) AS support, array_agg(id ORDER BY id) AS edge_ids,156 (SELECT array_agg(DISTINCT x::int ORDER BY x::int) FROM unnest(string_to_array(string_agg(array_to_string(provenance_ids, ','), ','), ',')) x WHERE x <> '') AS provenance_ids,157 (SELECT array_agg(DISTINCT x ORDER BY x) FROM unnest(string_to_array(string_agg(array_to_string(cancer_context_ids, ','), ','), ',')) x WHERE x <> '') AS context_ids,158 max(last_seen_at) AS last_seen159 FROM e GROUP BY 1,2,3,4,5,6,7,8,9,10,11160 ), r AS (161 SELECT a.*, row_number() OVER (PARTITION BY a.relationship_type ORDER BY a.lvl, a.support DESC, a.last_seen DESC NULLS LAST, a.n_id, a.via_id) AS rn,162 count(*) OVER (PARTITION BY a.relationship_type) AS total163 FROM a164 )165 SELECT r.relationship_type, r.outgoing, r.ctx_only, r.n_type, r.n_id, r.via_type, r.via_id, r.direction, r.evidence_level, r.evidence_category, r.source_id, s.slug AS source_slug,166 r.support, r.edge_ids, r.provenance_ids, r.context_ids, r.last_seen, r.total, r.rn,167 coalesce(c.slug, g.symbol, v.slug, d.slug) AS n_ref,168 coalesce(c.canonical_name, g.symbol, coalesce(v.gene_symbol || ' ', '') || v.name, d.name) AS n_label,169 coalesce(c.entity_type, g.name, v.variant_type, d.kind) AS n_sublabel,170 coalesce(vc.slug, vg.symbol, vv.slug, vd.slug) AS via_ref,171 coalesce(vc.canonical_name, vg.symbol, coalesce(vv.gene_symbol || ' ', '') || vv.name, vd.name) AS via_label172 FROM r173 JOIN sources s ON s.id = r.source_id174 LEFT JOIN cancers c ON r.n_type = 'cancer' AND c.id = r.n_id175 LEFT JOIN genes g ON r.n_type = 'gene' AND g.id = r.n_id176 LEFT JOIN variants v ON r.n_type = 'variant' AND v.id = r.n_id177 LEFT JOIN drugs d ON r.n_type = 'drug' AND d.id = r.n_id178 LEFT JOIN cancers vc ON r.via_type = 'cancer' AND vc.id = r.via_id179 LEFT JOIN genes vg ON r.via_type = 'gene' AND vg.id = r.via_id180 LEFT JOIN variants vv ON r.via_type = 'variant' AND vv.id = r.via_id181 LEFT JOIN drugs vd ON r.via_type = 'drug' AND vd.id = r.via_id182 WHERE r.rn <= CASE WHEN r.relationship_type = ${(more ?? '').toUpperCase()}::text THEN ${EXPANDED_GROUP_LIMIT}::int ELSE ${DEFAULT_GROUP_LIMIT}::int END183 ORDER BY r.relationship_type, r.rn`),184 [] as KeRow[],185 );186}187188// ---------------------------------------------------------------------------------------------189// Derived links (registry counts) — each returns ready-made edges + nodes190// ---------------------------------------------------------------------------------------------191192interface Derived {193 relationshipType: string;194 total: number;195 edges: GraphEdge[];196 nodes: GraphNode[];197}198199const SRC = { clinicaltrials: 'CI-SOURCE-00000004', civic: 'CI-SOURCE-00000006', gdc: 'CI-SOURCE-00000008', openfda: 'CI-SOURCE-00000016', cbioportal: 'CI-SOURCE-00000017' } as const;200201function pct(v: number): string {202 return `${(v * 100).toFixed(v >= 0.1 ? 0 : 1)} %`;203}204205/** cancer ⇄ gene through cohort alteration frequencies (largest cohort per pair — biggest denominator, not highest frequency; thresholds applied to that cohort row). */206async function frequencyLinks(side: 'cancer' | 'gene', focus: FocusNode, ids: string[], limit: number): Promise<Derived> {207 type Row = { gene_id: string; symbol: string; is_cancer_gene: boolean; cancer_id: string; cancer_slug: string; cancer_name: string; alteration_type: string; cases_affected: number; cases_profiled: number; frequency: number; study_id: string; source_id: string; source_slug: string; provenance_id: number; cohorts: string; total: string };208 const where = side === 'cancer' ? sql`f.cancer_id IN ${inList(ids)} AND f.gene_id IS NOT NULL` : sql`f.gene_id = ${focus.id} AND f.cancer_id IS NOT NULL`;209 const part = side === 'cancer' ? sql`f.gene_id` : sql`f.cancer_id`;210 const order = side === 'cancer' ? sql`g.is_cancer_gene DESC, f.frequency DESC, g.symbol` : sql`f.frequency DESC, c.canonical_name`;211 const rows = await safe(212 () =>213 run<Row>(sql`214 WITH f AS (215 SELECT f.gene_id, f.cancer_id, f.alteration_type, f.cases_affected, f.cases_profiled, f.frequency, f.provenance_id, co.study_id, co.source_id,216 count(*) OVER (PARTITION BY ${part}) AS cohorts,217 row_number() OVER (PARTITION BY ${part} ORDER BY f.cases_profiled DESC, f.frequency DESC, f.id) AS rn218 FROM cancer_gene_frequencies f JOIN genomic_cohorts co ON co.id = f.cohort_id219 WHERE ${where} AND f.frequency >= ${FREQ_MIN} AND f.cases_affected >= ${CASES_MIN}220 )221 SELECT f.*, g.symbol, g.is_cancer_gene, c.slug AS cancer_slug, c.canonical_name AS cancer_name, s.slug AS source_slug, count(*) OVER() AS total222 FROM f JOIN genes g ON g.id = f.gene_id JOIN cancers c ON c.id = f.cancer_id JOIN sources s ON s.id = f.source_id223 WHERE f.rn = 1 ORDER BY ${order} LIMIT ${limit}`),224 [] as Row[],225 );226 const edges: GraphEdge[] = [];227 const nodes: GraphNode[] = [];228 for (const r of rows) {229 const neighbor: GraphNode =230 side === 'cancer'231 ? { type: 'gene', id: r.gene_id, ref: r.symbol, label: r.symbol, sublabel: r.is_cancer_gene ? 'cancer gene' : null, href: hrefFor('gene', r.symbol), degree: 0 }232 : { type: 'cancer', id: r.cancer_id, ref: r.cancer_slug, label: r.cancer_name, sublabel: null, href: hrefFor('cancer', r.cancer_slug), degree: 0 };233 nodes.push(neighbor);234 edges.push({235 key: `dv:freq:${r.gene_id}:${r.cancer_id}`,236 relationshipType: 'ALTERED_IN',237 neighborKey: nodeKey(neighbor),238 outgoing: side === 'gene',239 direction: null,240 evidenceLevel: null,241 evidenceCategory: 'observed_data',242 cancerContext: [{ id: r.cancer_id, name: r.cancer_name, slug: r.cancer_slug }],243 supportCount: n(r.cohorts),244 sourceIds: [r.source_id],245 sourceSlugs: [r.source_slug],246 provenanceIds: [r.provenance_id],247 derived: true,248 detail: `${r.cases_affected.toLocaleString('en-US')} / ${r.cases_profiled.toLocaleString('en-US')} cases (${pct(r.frequency)}) · ${r.alteration_type} · ${r.study_id}${n(r.cohorts) > 1 ? ` · largest of ${n(r.cohorts)} cohorts` : ''}`,249 });250 }251 return { relationshipType: 'ALTERED_IN', total: rows.length ? n(rows[0]!.total) : 0, edges, nodes };252}253254/** cancer → trials (registry, deduplicated per trial; most recently updated first). */255async function cancerTrialLinks(focus: FocusNode, ids: string[], limit: number): Promise<Derived> {256 type Row = { id: string; nct_id: string; brief_title: string; overall_status: string | null; phases: string[]; last_update_posted_date: string | null; cancer_id: string; cancer_slug: string; cancer_name: string; match_type: string; total: string; active: string };257 const rows = await safe(258 () =>259 run<Row>(sql`260 WITH m AS (261 SELECT DISTINCT ON (tc.trial_id) tc.trial_id, tc.cancer_id, tc.match_type FROM trial_conditions tc WHERE tc.cancer_id IN ${inList(ids)} ORDER BY tc.trial_id, (tc.cancer_id = ${focus.id}) DESC, tc.id262 )263 SELECT t.id, t.nct_id, t.brief_title, t.overall_status, t.phases, t.last_update_posted_date, m.cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name, m.match_type,264 count(*) OVER() AS total, count(*) FILTER (WHERE t.overall_status IN ${activeList()}) OVER() AS active265 FROM m JOIN clinical_trials t ON t.id = m.trial_id JOIN cancers c ON c.id = m.cancer_id266 ORDER BY t.last_update_posted_date DESC NULLS LAST, t.nct_id LIMIT ${limit}`),267 [] as Row[],268 );269 const edges: GraphEdge[] = [];270 const nodes: GraphNode[] = [];271 for (const r of rows) {272 const node: GraphNode = { type: 'trial', id: r.id, ref: r.nct_id, label: r.nct_id, sublabel: [r.phases.map((p) => p.replace('PHASE', 'Phase ').replace('EARLY_', 'early ')).join('/'), r.overall_status?.toLowerCase().replace(/_/g, ' ')].filter(Boolean).join(' · ') || null, href: hrefFor('trial', r.nct_id), degree: 0 };273 nodes.push(node);274 edges.push({275 key: `dv:trial:${r.id}`,276 relationshipType: 'STUDIED_IN',277 neighborKey: nodeKey(node),278 outgoing: true,279 direction: null,280 evidenceLevel: null,281 evidenceCategory: 'observed_data',282 cancerContext: [{ id: r.cancer_id, name: r.cancer_name, slug: r.cancer_slug }],283 supportCount: 1,284 sourceIds: [SRC.clinicaltrials],285 sourceSlugs: ['clinicaltrials'],286 provenanceIds: [],287 derived: true,288 detail: `${r.brief_title} · condition mapped ${r.match_type}`,289 date: r.last_update_posted_date,290 });291 }292 const total = rows.length ? n(rows[0]!.total) : 0;293 const active = rows.length ? n(rows[0]!.active) : 0;294 for (const e of edges) e.detail = `${e.detail} · ${total.toLocaleString('en-US')} trials mapped (${active.toLocaleString('en-US')} active)`;295 return { relationshipType: 'STUDIED_IN', total, edges, nodes };296}297298/** cancer → drugs through registered trials (trial_interventions.drug_id × trial_conditions.cancer_id). */299async function cancerDrugTrialLinks(focus: FocusNode, ids: string[], limit: number): Promise<Derived> {300 type Row = { drug_id: string; slug: string; name: string; kind: string | null; trials: string; active: string; last: string | null; total: string };301 const rows = await safe(302 () =>303 run<Row>(sql`304 SELECT ti.drug_id, d.slug, d.name, d.kind, count(DISTINCT ti.trial_id) AS trials,305 count(DISTINCT ti.trial_id) FILTER (WHERE t.overall_status IN ${activeList()}) AS active, max(t.last_update_posted_date) AS last, count(*) OVER() AS total306 FROM trial_conditions tc JOIN trial_interventions ti ON ti.trial_id = tc.trial_id AND ti.drug_id IS NOT NULL307 JOIN clinical_trials t ON t.id = tc.trial_id JOIN drugs d ON d.id = ti.drug_id308 WHERE tc.cancer_id IN ${inList(ids)}309 GROUP BY ti.drug_id, d.slug, d.name, d.kind ORDER BY trials DESC, d.name LIMIT ${limit}`),310 [] as Row[],311 );312 return drugTrialRows(focus, rows, 'INVESTIGATED_IN_TRIALS', true);313}314315function drugTrialRows(focus: FocusNode, rows: Array<{ drug_id: string; slug: string; name: string; kind: string | null; trials: string; active: string; last: string | null; total: string }>, rel: string, outgoing: boolean): Derived {316 const edges: GraphEdge[] = [];317 const nodes: GraphNode[] = [];318 for (const r of rows) {319 const node: GraphNode = { type: 'drug', id: r.drug_id, ref: r.slug, label: r.name, sublabel: r.kind?.replace(/_/g, ' ') ?? null, href: hrefFor('drug', r.slug), degree: 0 };320 nodes.push(node);321 edges.push({322 key: `dv:drugtrials:${r.drug_id}:${focus.id}`,323 relationshipType: rel,324 neighborKey: nodeKey(node),325 outgoing,326 direction: null,327 evidenceLevel: null,328 evidenceCategory: 'observed_data',329 cancerContext: focus.type === 'cancer' ? [{ id: focus.id, name: focus.label, slug: focus.ref }] : [],330 supportCount: n(r.trials),331 sourceIds: [SRC.clinicaltrials],332 sourceSlugs: ['clinicaltrials'],333 provenanceIds: [],334 derived: true,335 detail: `${n(r.trials).toLocaleString('en-US')} trials (${n(r.active).toLocaleString('en-US')} active)${focus.type === 'cancer' ? ' · roll-up of the cancer and its descendants' : ''}`,336 date: r.last,337 });338 }339 return { relationshipType: rel, total: rows.length ? n(rows[0]!.total) : 0, edges, nodes };340}341342/** cancer ← drug through regulatory approvals (jurisdiction-aware, dated); rows already present as APPROVED_FOR knowledge edges are skipped. */343async function approvalLinks(side: 'cancer' | 'drug', focus: FocusNode, ids: string[], limit: number): Promise<Derived> {344 type Row = { id: number; drug_id: string; drug_slug: string; drug_name: string; kind: string | null; cancer_id: string | null; cancer_slug: string | null; cancer_name: string | null; tumor_agnostic: boolean; jurisdiction: string; authority: string; indication: string; approval_date: string | null; status: string; source_id: string; source_slug: string; provenance_id: number; total: string };345 const where = side === 'cancer' ? sql`a.cancer_id IN ${inList(ids)} AND NOT EXISTS (SELECT 1 FROM knowledge_edges ke WHERE ke.relationship_type = 'APPROVED_FOR' AND ke.source_entity_id = a.drug_id AND ke.target_entity_id = ${focus.id})` : sql`a.drug_id = ${focus.id} AND (a.cancer_id IS NULL OR NOT EXISTS (SELECT 1 FROM knowledge_edges ke WHERE ke.relationship_type = 'APPROVED_FOR' AND ke.source_entity_id = a.drug_id AND ke.target_entity_id = a.cancer_id))`;346 const rows = await safe(347 () =>348 run<Row>(sql`349 SELECT a.id, a.drug_id, d.slug AS drug_slug, d.name AS drug_name, d.kind, a.cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name, a.tumor_agnostic, a.jurisdiction, a.authority, a.indication, a.approval_date, a.status,350 a.source_id, s.slug AS source_slug, a.provenance_id, count(*) OVER() AS total351 FROM drug_approvals a JOIN drugs d ON d.id = a.drug_id LEFT JOIN cancers c ON c.id = a.cancer_id JOIN sources s ON s.id = a.source_id352 WHERE ${where} ORDER BY a.approval_date DESC NULLS LAST, a.id LIMIT ${limit}`),353 [] as Row[],354 );355 const edges: GraphEdge[] = [];356 const nodes: GraphNode[] = [];357 for (const r of rows) {358 let node: GraphNode;359 if (side === 'cancer') node = { type: 'drug', id: r.drug_id, ref: r.drug_slug, label: r.drug_name, sublabel: r.kind?.replace(/_/g, ' ') ?? null, href: hrefFor('drug', r.drug_slug), degree: 0 };360 else if (r.cancer_id && r.cancer_slug && r.cancer_name) node = { type: 'cancer', id: r.cancer_id, ref: r.cancer_slug, label: r.cancer_name, sublabel: null, href: hrefFor('cancer', r.cancer_slug), degree: 0 };361 else node = { type: 'approval', id: `approval:${r.id}`, ref: null, label: `${r.authority} · ${r.jurisdiction}${r.approval_date ? ` · ${r.approval_date.slice(0, 4)}` : ''}`, sublabel: r.indication, href: `/drug/${r.drug_slug}#approvals`, degree: 0 };362 nodes.push(node);363 edges.push({364 key: `dv:approval:${r.id}`,365 relationshipType: 'APPROVED_FOR',366 neighborKey: nodeKey(node),367 outgoing: side === 'drug',368 direction: null,369 evidenceLevel: r.status,370 evidenceCategory: 'regulatory_status',371 cancerContext: r.cancer_id && r.cancer_slug && r.cancer_name ? [{ id: r.cancer_id, name: r.cancer_name, slug: r.cancer_slug }] : [],372 supportCount: 1,373 sourceIds: [r.source_id],374 sourceSlugs: [r.source_slug],375 provenanceIds: [r.provenance_id],376 derived: true,377 detail: `${r.authority} (${r.jurisdiction}) · ${r.status}${r.tumor_agnostic ? ' · tumour-agnostic' : ''}${r.approval_date ? ` · ${r.approval_date}` : ' · date not published'} · ${r.indication.length > 140 ? `${r.indication.slice(0, 139)}…` : r.indication}`,378 date: r.approval_date,379 });380 }381 return { relationshipType: 'APPROVED_FOR', total: rows.length ? n(rows[0]!.total) : 0, edges, nodes };382}383384/** gene → variants (top by CIViC evidence count; the variant list itself is structural HGNC/ClinVar/CIViC data). */385async function geneVariantLinks(focus: FocusNode, limit: number): Promise<Derived> {386 type Row = { id: string; slug: string; name: string; variant_type: string | null; ev: string; context_ids: string[] | null; provenance_ids: number[] | null; total: string };387 const rows = await safe(388 () =>389 run<Row>(sql`390 WITH ev AS (391 SELECT vid, count(*) AS ev, (array_agg(DISTINCT e.cancer_id) FILTER (WHERE e.cancer_id IS NOT NULL))[1:5] AS context_ids, (array_agg(DISTINCT e.provenance_id))[1:20] AS provenance_ids392 FROM civic_evidence_items e CROSS JOIN LATERAL unnest(e.variant_ids) vid393 WHERE e.status = 'ACCEPTED' AND (${focus.id} = ANY(e.gene_ids) OR ${focus.label} = ANY(e.gene_symbols)) GROUP BY vid394 )395 SELECT v.id, v.slug, v.name, v.variant_type, coalesce(ev.ev, 0) AS ev, ev.context_ids, ev.provenance_ids, count(*) OVER() AS total396 FROM variants v LEFT JOIN ev ON ev.vid = v.id WHERE v.gene_id = ${focus.id}397 ORDER BY coalesce(ev.ev, 0) DESC, v.name LIMIT ${limit}`),398 [] as Row[],399 );400 const edges: GraphEdge[] = [];401 const nodes: GraphNode[] = [];402 for (const r of rows) {403 const node: GraphNode = { type: 'variant', id: r.id, ref: r.slug, label: r.name, sublabel: r.variant_type, href: hrefFor('variant', r.slug), degree: 0 };404 nodes.push(node);405 const ev = n(r.ev);406 edges.push({407 key: `dv:variant:${r.id}`,408 relationshipType: 'HAS_VARIANT',409 neighborKey: nodeKey(node),410 outgoing: true,411 direction: null,412 evidenceLevel: null,413 evidenceCategory: ev > 0 ? 'curated_evidence' : 'observed_data',414 cancerContext: (r.context_ids ?? []).map((id) => ({ id, name: id, slug: '' })),415 supportCount: ev,416 sourceIds: [SRC.civic],417 sourceSlugs: ['civic'],418 provenanceIds: r.provenance_ids ?? [],419 derived: true,420 detail: ev > 0 ? `${ev.toLocaleString('en-US')} accepted CIViC evidence items` : 'no accepted CIViC evidence item (variant record only)',421 });422 }423 return { relationshipType: 'HAS_VARIANT', total: rows.length ? n(rows[0]!.total) : 0, edges, nodes };424}425426/** variant → cancers with accepted CIViC evidence, aggregated by level (A–E) and direction. */427async function variantEvidenceLinks(focus: FocusNode, limit: number): Promise<Derived> {428 type Row = { cancer_id: string; slug: string; name: string; items: string; levels: string[]; best: string | null; sens: string; res: string; supports: string; does_not: string; provenance_ids: number[]; total: string };429 const rows = await safe(430 () =>431 run<Row>(sql`432 SELECT e.cancer_id, c.slug, c.canonical_name AS name, count(*) AS items,433 array_agg(DISTINCT e.evidence_level ORDER BY e.evidence_level) FILTER (WHERE e.evidence_level IS NOT NULL) AS levels,434 min(e.evidence_level) AS best,435 count(*) FILTER (WHERE e.significance ILIKE '%SENSITIV%') AS sens, count(*) FILTER (WHERE e.significance ILIKE '%RESIST%') AS res,436 count(*) FILTER (WHERE e.evidence_direction = 'SUPPORTS') AS supports, count(*) FILTER (WHERE e.evidence_direction = 'DOES_NOT_SUPPORT') AS does_not,437 (array_agg(DISTINCT e.provenance_id))[1:50] AS provenance_ids, count(*) OVER() AS total438 FROM civic_evidence_items e JOIN cancers c ON c.id = e.cancer_id439 WHERE e.status = 'ACCEPTED' AND ${focus.id} = ANY(e.variant_ids)440 GROUP BY e.cancer_id, c.slug, c.canonical_name ORDER BY min(${CIVIC_LEVEL_RANK}), items DESC, c.canonical_name LIMIT ${limit}`),441 [] as Row[],442 );443 const edges: GraphEdge[] = [];444 const nodes: GraphNode[] = [];445 for (const r of rows) {446 const node: GraphNode = { type: 'cancer', id: r.cancer_id, ref: r.slug, label: r.name, sublabel: null, href: hrefFor('cancer', r.slug), degree: 0 };447 nodes.push(node);448 const sens = n(r.sens);449 const res = n(r.res);450 edges.push({451 key: `dv:civic:${focus.id}:${r.cancer_id}`,452 relationshipType: 'HAS_EVIDENCE_IN',453 neighborKey: nodeKey(node),454 outgoing: true,455 direction: sens && res ? 'mixed' : sens ? 'sensitivity' : res ? 'resistance' : n(r.supports) && !n(r.does_not) ? 'supports' : n(r.does_not) ? 'does not support' : null,456 evidenceLevel: r.best,457 evidenceCategory: 'curated_evidence',458 cancerContext: [{ id: r.cancer_id, name: r.name, slug: r.slug }],459 supportCount: n(r.items),460 sourceIds: [SRC.civic],461 sourceSlugs: ['civic'],462 provenanceIds: r.provenance_ids ?? [],463 derived: true,464 detail: `${n(r.items).toLocaleString('en-US')} accepted items · levels ${(r.levels ?? []).join(', ') || '—'} · ${sens} sensitivity / ${res} resistance · ${n(r.supports)} supports / ${n(r.does_not)} does not support`,465 });466 }467 return { relationshipType: 'HAS_EVIDENCE_IN', total: rows.length ? n(rows[0]!.total) : 0, edges, nodes };468}469470/** variant → drugs from CIViC predictive items that have no PREDICTS_RESPONSE_TO knowledge edge yet (gap filler, flagged derived). */471async function variantDrugCivicLinks(focus: FocusNode, limit: number): Promise<Derived> {472 type Row = { drug_id: string; slug: string; name: string; kind: string | null; items: string; best: string | null; sens: string; res: string; context_ids: string[]; provenance_ids: number[]; total: string };473 const rows = await safe(474 () =>475 run<Row>(sql`476 SELECT tid AS drug_id, d.slug, d.name, d.kind, count(*) AS items, min(e.evidence_level) AS best,477 count(*) FILTER (WHERE e.significance ILIKE '%SENSITIV%') AS sens, count(*) FILTER (WHERE e.significance ILIKE '%RESIST%') AS res,478 (array_agg(DISTINCT e.cancer_id) FILTER (WHERE e.cancer_id IS NOT NULL))[1:5] AS context_ids, (array_agg(DISTINCT e.provenance_id))[1:50] AS provenance_ids, count(*) OVER() AS total479 FROM civic_evidence_items e CROSS JOIN LATERAL unnest(e.therapy_ids) tid JOIN drugs d ON d.id = tid480 WHERE e.status = 'ACCEPTED' AND e.evidence_type = 'PREDICTIVE' AND ${focus.id} = ANY(e.variant_ids)481 AND NOT EXISTS (SELECT 1 FROM knowledge_edges ke WHERE ke.relationship_type = 'PREDICTS_RESPONSE_TO' AND ke.source_entity_id = ${focus.id} AND ke.target_entity_id = tid)482 GROUP BY tid, d.slug, d.name, d.kind ORDER BY min(${CIVIC_LEVEL_RANK}), items DESC, d.name LIMIT ${limit}`),483 [] as Row[],484 );485 const edges: GraphEdge[] = [];486 const nodes: GraphNode[] = [];487 for (const r of rows) {488 const node: GraphNode = { type: 'drug', id: r.drug_id, ref: r.slug, label: r.name, sublabel: r.kind?.replace(/_/g, ' ') ?? null, href: hrefFor('drug', r.slug), degree: 0 };489 nodes.push(node);490 const sens = n(r.sens);491 const res = n(r.res);492 edges.push({493 key: `dv:civicdrug:${focus.id}:${r.drug_id}`,494 relationshipType: 'PREDICTS_RESPONSE_TO',495 neighborKey: nodeKey(node),496 outgoing: true,497 direction: sens && res ? 'mixed' : sens ? 'sensitivity' : res ? 'resistance' : null,498 evidenceLevel: r.best,499 evidenceCategory: 'curated_evidence',500 cancerContext: (r.context_ids ?? []).map((id) => ({ id, name: id, slug: '' })),501 supportCount: n(r.items),502 sourceIds: [SRC.civic],503 sourceSlugs: ['civic'],504 provenanceIds: r.provenance_ids ?? [],505 derived: true,506 detail: `${n(r.items)} accepted predictive items (aggregated from CIViC, no knowledge edge yet)`,507 });508 }509 return { relationshipType: 'PREDICTS_RESPONSE_TO', total: rows.length ? n(rows[0]!.total) : 0, edges, nodes };510}511512/** drug → trials (registry, most recently updated first) with the trial's mapped cancers as context. */513async function drugTrialLinks(focus: FocusNode, limit: number): Promise<Derived> {514 type Row = { id: string; nct_id: string; brief_title: string; overall_status: string | null; phases: string[]; last_update_posted_date: string | null; context_ids: string[] | null; total: string; active: string };515 const rows = await safe(516 () =>517 run<Row>(sql`518 SELECT t.id, t.nct_id, t.brief_title, t.overall_status, t.phases, t.last_update_posted_date,519 (SELECT (array_agg(DISTINCT tc.cancer_id))[1:5] FROM trial_conditions tc WHERE tc.trial_id = t.id AND tc.cancer_id IS NOT NULL) AS context_ids,520 count(*) OVER() AS total, count(*) FILTER (WHERE t.overall_status IN ${activeList()}) OVER() AS active521 FROM (SELECT DISTINCT trial_id FROM trial_interventions WHERE drug_id = ${focus.id}) ti JOIN clinical_trials t ON t.id = ti.trial_id522 ORDER BY t.last_update_posted_date DESC NULLS LAST, t.nct_id LIMIT ${limit}`),523 [] as Row[],524 );525 const edges: GraphEdge[] = [];526 const nodes: GraphNode[] = [];527 const total = rows.length ? n(rows[0]!.total) : 0;528 const active = rows.length ? n(rows[0]!.active) : 0;529 for (const r of rows) {530 const node: GraphNode = { type: 'trial', id: r.id, ref: r.nct_id, label: r.nct_id, sublabel: [r.phases.map((p) => p.replace('PHASE', 'Phase ').replace('EARLY_', 'early ')).join('/'), r.overall_status?.toLowerCase().replace(/_/g, ' ')].filter(Boolean).join(' · ') || null, href: hrefFor('trial', r.nct_id), degree: 0 };531 nodes.push(node);532 edges.push({533 key: `dv:drugtrial:${r.id}`,534 relationshipType: 'STUDIED_IN',535 neighborKey: nodeKey(node),536 outgoing: true,537 direction: null,538 evidenceLevel: null,539 evidenceCategory: 'observed_data',540 cancerContext: (r.context_ids ?? []).map((id) => ({ id, name: id, slug: '' })),541 supportCount: 1,542 sourceIds: [SRC.clinicaltrials],543 sourceSlugs: ['clinicaltrials'],544 provenanceIds: [],545 derived: true,546 detail: `${r.brief_title} · ${total.toLocaleString('en-US')} trials list this drug (${active.toLocaleString('en-US')} active)`,547 date: r.last_update_posted_date,548 });549 }550 return { relationshipType: 'STUDIED_IN', total, edges, nodes };551}552553/** drug → cancers through registered trials. */554async function drugCancerTrialLinks(focus: FocusNode, limit: number): Promise<Derived> {555 type Row = { cancer_id: string; slug: string; name: string; trials: string; active: string; last: string | null; total: string };556 const rows = await safe(557 () =>558 run<Row>(sql`559 SELECT tc.cancer_id, c.slug, c.canonical_name AS name, count(DISTINCT tc.trial_id) AS trials,560 count(DISTINCT tc.trial_id) FILTER (WHERE t.overall_status IN ${activeList()}) AS active, max(t.last_update_posted_date) AS last, count(*) OVER() AS total561 FROM trial_interventions ti JOIN trial_conditions tc ON tc.trial_id = ti.trial_id AND tc.cancer_id IS NOT NULL562 JOIN clinical_trials t ON t.id = ti.trial_id JOIN cancers c ON c.id = tc.cancer_id563 WHERE ti.drug_id = ${focus.id}564 GROUP BY tc.cancer_id, c.slug, c.canonical_name ORDER BY trials DESC, c.canonical_name LIMIT ${limit}`),565 [] as Row[],566 );567 const edges: GraphEdge[] = [];568 const nodes: GraphNode[] = [];569 for (const r of rows) {570 const node: GraphNode = { type: 'cancer', id: r.cancer_id, ref: r.slug, label: r.name, sublabel: null, href: hrefFor('cancer', r.slug), degree: 0 };571 nodes.push(node);572 edges.push({573 key: `dv:drugtrials:${focus.id}:${r.cancer_id}`,574 relationshipType: 'INVESTIGATED_IN_TRIALS',575 neighborKey: nodeKey(node),576 outgoing: true,577 direction: null,578 evidenceLevel: null,579 evidenceCategory: 'observed_data',580 cancerContext: [{ id: r.cancer_id, name: r.name, slug: r.slug }],581 supportCount: n(r.trials),582 sourceIds: [SRC.clinicaltrials],583 sourceSlugs: ['clinicaltrials'],584 provenanceIds: [],585 derived: true,586 detail: `${n(r.trials).toLocaleString('en-US')} trials (${n(r.active).toLocaleString('en-US')} active) · conditions mapped to this cancer only (no hierarchy roll-up)`,587 date: r.last,588 });589 }590 return { relationshipType: 'INVESTIGATED_IN_TRIALS', total: rows.length ? n(rows[0]!.total) : 0, edges, nodes };591}592593/** trial → mapped conditions (cancers) and interventions (drugs). */594async function trialLinks(focus: FocusNode, limit: number): Promise<Derived[]> {595 type CRow = { cancer_id: string; slug: string; name: string; match_type: string; condition_text: string; total: string };596 type DRow = { drug_id: string; slug: string; name: string; kind: string | null; match_type: string; intervention_type: string | null; iname: string; total: string };597 const [conds, ints] = await Promise.all([598 safe(() => run<CRow>(sql`SELECT tc.cancer_id, c.slug, c.canonical_name AS name, tc.match_type, tc.condition_text, count(*) OVER() AS total FROM trial_conditions tc JOIN cancers c ON c.id = tc.cancer_id WHERE tc.trial_id = ${focus.id} ORDER BY c.canonical_name LIMIT ${limit}`), [] as CRow[]),599 safe(() => run<DRow>(sql`SELECT ti.drug_id, d.slug, d.name, d.kind, ti.match_type, ti.intervention_type, ti.name AS iname, count(*) OVER() AS total FROM trial_interventions ti JOIN drugs d ON d.id = ti.drug_id WHERE ti.trial_id = ${focus.id} ORDER BY d.name LIMIT ${limit}`), [] as DRow[]),600 ]);601 const c: Derived = { relationshipType: 'CONDITION_OF', total: conds.length ? n(conds[0]!.total) : 0, edges: [], nodes: [] };602 for (const r of conds) {603 const node: GraphNode = { type: 'cancer', id: r.cancer_id, ref: r.slug, label: r.name, sublabel: null, href: hrefFor('cancer', r.slug), degree: 0 };604 c.nodes.push(node);605 c.edges.push({ key: `dv:cond:${focus.id}:${r.cancer_id}`, relationshipType: 'CONDITION_OF', neighborKey: nodeKey(node), outgoing: false, direction: null, evidenceLevel: null, evidenceCategory: 'observed_data', cancerContext: [{ id: r.cancer_id, name: r.name, slug: r.slug }], supportCount: 1, sourceIds: [SRC.clinicaltrials], sourceSlugs: ['clinicaltrials'], provenanceIds: [], derived: true, detail: `registry condition “${r.condition_text}” mapped ${r.match_type}` });606 }607 const d: Derived = { relationshipType: 'INTERVENTION_OF', total: ints.length ? n(ints[0]!.total) : 0, edges: [], nodes: [] };608 for (const r of ints) {609 const node: GraphNode = { type: 'drug', id: r.drug_id, ref: r.slug, label: r.name, sublabel: r.kind?.replace(/_/g, ' ') ?? null, href: hrefFor('drug', r.slug), degree: 0 };610 d.nodes.push(node);611 d.edges.push({ key: `dv:int:${focus.id}:${r.drug_id}`, relationshipType: 'INTERVENTION_OF', neighborKey: nodeKey(node), outgoing: false, direction: null, evidenceLevel: null, evidenceCategory: 'observed_data', cancerContext: [], supportCount: 1, sourceIds: [SRC.clinicaltrials], sourceSlugs: ['clinicaltrials'], provenanceIds: [], derived: true, detail: `registry intervention “${r.iname}” (${r.intervention_type ?? 'type not stated'}) mapped ${r.match_type}` });612 }613 return [c, d];614}615616// ---------------------------------------------------------------------------------------------617// Assembly618// ---------------------------------------------------------------------------------------------619620async function cancerNames(ids: Iterable<string>): Promise<Map<string, CancerContext>> {621 const uniq = [...new Set(ids)].filter(Boolean);622 if (uniq.length === 0) return new Map();623 const rows = await safe(() => run<{ id: string; slug: string; name: string }>(sql`SELECT id, slug, canonical_name AS name FROM cancers WHERE id IN ${inList(uniq)}`), [] as Array<{ id: string; slug: string; name: string }>);624 return new Map(rows.map((r) => [r.id, { id: r.id, slug: r.slug, name: r.name }]));625}626627/** Descendant ids (inclusive) for a cancer focus, capped so very broad families stay bounded. */628export async function focusCancerIds(focus: FocusNode): Promise<string[]> {629 if (focus.type !== 'cancer') return [];630 const ids = await getDescendantIds(focus.id);631 return ids.length > MAX_DESCENDANTS ? [focus.id, ...ids.filter((i) => i !== focus.id).slice(0, MAX_DESCENDANTS - 1)] : ids;632}633634export interface NeighborhoodOptions {635 /** Relationship type whose group is expanded to EXPANDED_GROUP_LIMIT. */636 more?: string | null;637 includeDerived?: boolean;638}639640export async function loadNeighborhood(focus: FocusNode, opts: NeighborhoodOptions = {}): Promise<Neighborhood & { cancerIds: string[] }> {641 const more = opts.more ?? null;642 const includeDerived = opts.includeDerived ?? true;643 const cancerIds = await focusCancerIds(focus);644 const lim = (rel: string, base = DEFAULT_GROUP_LIMIT) => groupLimit(rel, more, base);645646 const derivedTasks: Array<Promise<Derived | Derived[]>> = [];647 if (includeDerived) {648 switch (focus.type) {649 case 'cancer':650 derivedTasks.push(cancerTrialLinks(focus, cancerIds, lim('STUDIED_IN', TRIAL_GROUP_LIMIT)), frequencyLinks('cancer', focus, cancerIds, lim('ALTERED_IN')), approvalLinks('cancer', focus, cancerIds, lim('APPROVED_FOR')), cancerDrugTrialLinks(focus, cancerIds, lim('INVESTIGATED_IN_TRIALS')));651 break;652 case 'gene':653 derivedTasks.push(geneVariantLinks(focus, lim('HAS_VARIANT')), frequencyLinks('gene', focus, [], lim('ALTERED_IN')));654 break;655 case 'variant':656 derivedTasks.push(variantEvidenceLinks(focus, lim('HAS_EVIDENCE_IN')), variantDrugCivicLinks(focus, lim('PREDICTS_RESPONSE_TO')));657 break;658 case 'drug':659 derivedTasks.push(drugTrialLinks(focus, lim('STUDIED_IN', TRIAL_GROUP_LIMIT)), drugCancerTrialLinks(focus, lim('INVESTIGATED_IN_TRIALS')), approvalLinks('drug', focus, [], lim('APPROVED_FOR')));660 break;661 case 'trial':662 derivedTasks.push(trialLinks(focus, lim('CONDITION_OF')));663 break;664 }665 }666 const [ke, ...derivedRaw] = await Promise.all([focus.type === 'trial' ? Promise.resolve([] as KeRow[]) : knowledgeEdges(focus, more), ...derivedTasks]);667 const derived = derivedRaw.flat();668669 // Source-native edges → GraphEdge + nodes670 const nodes = new Map<string, GraphNode>();671 const groups = new Map<string, EdgeGroup>();672 const ctxIds = new Set<string>();673 for (const r of ke) for (const c of r.context_ids ?? []) ctxIds.add(c);674 for (const d of derived) for (const e of d.edges) for (const c of e.cancerContext) if (!c.slug) ctxIds.add(c.id);675 const names = await cancerNames(ctxIds);676 const ctx = (ids: string[] | null | undefined): CancerContext[] => (ids ?? []).map((id) => names.get(id) ?? { id, name: id, slug: '' }).sort((a, b) => a.name.localeCompare(b.name));677678 const addNode = (node: GraphNode) => {679 const k = nodeKey(node);680 const cur = nodes.get(k);681 if (cur) cur.degree += 1;682 else nodes.set(k, { ...node, degree: 1 });683 };684 const addEdge = (e: GraphEdge, total: number, derivedGroup: boolean) => {685 const g = groups.get(e.relationshipType) ?? { relationshipType: e.relationshipType, total: 0, edges: [], derived: derivedGroup };686 g.edges.push(e);687 g.total = Math.max(g.total, total);688 if (!derivedGroup) g.derived = false;689 groups.set(e.relationshipType, g);690 };691692 for (const r of ke) {693 if (!r.n_ref || !r.n_label) continue; // dangling target (entity not loaded on this environment)694 const node: GraphNode = { type: r.n_type, id: r.n_id, ref: r.n_ref, label: r.n_label, sublabel: r.n_sublabel, href: hrefFor(r.n_type, r.n_ref), degree: 0 };695 addNode(node);696 addEdge(697 {698 key: `ke:${(r.edge_ids ?? []).join('.')}`,699 relationshipType: r.relationship_type,700 neighborKey: nodeKey(node),701 outgoing: r.outgoing,702 direction: r.direction,703 evidenceLevel: r.evidence_level,704 evidenceCategory: r.evidence_category,705 cancerContext: ctx(r.context_ids),706 supportCount: n(r.support),707 sourceIds: [r.source_id],708 sourceSlugs: [r.source_slug],709 provenanceIds: (r.provenance_ids ?? []).map(Number),710 derived: false,711 detail: (r.edge_ids?.length ?? 1) > 1 ? `${r.edge_ids.length} source records aggregated` : null,712 date: r.last_seen ? new Date(r.last_seen).toISOString().slice(0, 10) : null,713 via: r.ctx_only && r.via_type && r.via_id && r.via_ref && r.via_label ? { type: r.via_type, id: r.via_id, label: r.via_label, href: hrefFor(r.via_type, r.via_ref) } : null,714 },715 n(r.total),716 false,717 );718 }719 for (const d of derived) {720 for (let i = 0; i < d.edges.length; i++) {721 const e = d.edges[i]!;722 const node = d.nodes[i]!;723 addNode(node);724 e.cancerContext = e.cancerContext.map((c) => (c.slug ? c : (names.get(c.id) ?? c)));725 addEdge(e, d.total, true);726 }727 }728729 const degreeByType: Record<NodeType, number> = { cancer: 0, gene: 0, variant: 0, drug: 0, trial: 0, approval: 0 };730 for (const node of nodes.values()) degreeByType[node.type] += 1;731 const focusOut: GraphNode = { ...focus, degree: [...groups.values()].reduce((a, g) => a + g.total, 0) };732 return { focus: focusOut, nodes: [...nodes.values()], groups: sortGroups([...groups.values()]), degreeByType, cancerIds };733}734735// ---------------------------------------------------------------------------------------------736// Paths (cancer focus)737// ---------------------------------------------------------------------------------------------738739/**740 * Strongest cancer → gene → variant → drug → approval → trials chains. The variant → drug hop is a741 * source-native PREDICTS_RESPONSE_TO edge (direction sensitivity) whose context includes the cancer742 * or one of its descendants; the gene hop is the variant's gene with its top cohort frequency in743 * the cancer — the cohort with the largest denominator (null when no cohort covers it — shown as "not yet available", never invented); the744 * approval hop is the earliest drug_approvals row in the cancer (or tumour-agnostic); the trial745 * hop counts registry trials listing the drug for the cancer. Ranked by level, then support.746 */747export async function loadPaths(focus: FocusNode, cancerIds: string[], limit = PATHS_LIMIT): Promise<PathChain[]> {748 if (focus.type !== 'cancer' || cancerIds.length === 0) return [];749 type Row = {750 variant_id: string; variant_slug: string; variant_name: string; gene_id: string; symbol: string; drug_id: string; drug_slug: string; drug_name: string;751 evidence_level: string | null; direction: string | null; support: string; source_ids: string[]; provenance_ids: number[]; context_ids: string[];752 frequency: number | null; cases_affected: number | null; cases_profiled: number | null; cohorts: string | null;753 approval_id: number | null; jurisdiction: string | null; authority: string | null; approval_date: string | null; approval_status: string | null; approval_cancer_id: string | null; approval_cancer_name: string | null; tumor_agnostic: boolean | null; approvals: string | null;754 trials: string | null; active_trials: string | null;755 };756 const rows = await safe(757 () =>758 run<Row>(sql`759 WITH ids AS (SELECT unnest(ARRAY[${sql.join(cancerIds.map((i) => sql`${i}`), sql`, `)}]::varchar[]) AS id),760 ed AS (761 SELECT ke.source_entity_id AS variant_id, ke.target_entity_id AS drug_id, min(${LEVEL_RANK}) AS lvl, min(ke.evidence_level) AS evidence_level, min(ke.direction) AS direction,762 sum(ke.support_count) AS support, array_agg(DISTINCT ke.source_id) AS source_ids,763 (SELECT (array_agg(DISTINCT x::int ORDER BY x::int))[1:50] FROM unnest(string_to_array(string_agg(array_to_string(ke.provenance_ids, ','), ','), ',')) x WHERE x <> '') AS provenance_ids,764 (SELECT array_agg(DISTINCT x ORDER BY x) FROM unnest(string_to_array(string_agg(array_to_string(ke.cancer_context_ids, ','), ','), ',')) x WHERE x <> '' AND x IN (SELECT id FROM ids)) AS context_ids765 FROM knowledge_edges ke766 WHERE ke.status = 'active' AND ke.relationship_type = 'PREDICTS_RESPONSE_TO' AND ke.direction = 'sensitivity' AND ke.source_entity_type = 'variant' AND ke.target_entity_type = 'drug'767 AND ke.cancer_context_ids && (SELECT array_agg(id)::text[] FROM ids)768 GROUP BY ke.source_entity_id, ke.target_entity_id769 ),770 fq AS (771 SELECT DISTINCT ON (f.gene_id) f.gene_id, f.frequency, f.cases_affected, f.cases_profiled, count(*) OVER (PARTITION BY f.gene_id) AS cohorts772 FROM cancer_gene_frequencies f WHERE f.cancer_id IN (SELECT id FROM ids) AND f.gene_id IS NOT NULL AND f.cases_affected >= ${CASES_MIN}773 ORDER BY f.gene_id, f.cases_profiled DESC, f.frequency DESC774 )775 SELECT ed.variant_id, v.slug AS variant_slug, v.name AS variant_name, g.id AS gene_id, g.symbol, ed.drug_id, d.slug AS drug_slug, d.name AS drug_name,776 ed.evidence_level, ed.direction, ed.support, ed.source_ids, ed.provenance_ids, ed.context_ids,777 fq.frequency, fq.cases_affected, fq.cases_profiled, fq.cohorts,778 ap.id AS approval_id, ap.jurisdiction, ap.authority, ap.approval_date, ap.status AS approval_status, ap.cancer_id AS approval_cancer_id, ac.canonical_name AS approval_cancer_name, ap.tumor_agnostic, ap.approvals,779 tr.trials, tr.active_trials780 FROM ed JOIN variants v ON v.id = ed.variant_id JOIN genes g ON g.id = v.gene_id JOIN drugs d ON d.id = ed.drug_id781 LEFT JOIN fq ON fq.gene_id = g.id782 LEFT JOIN LATERAL (783 SELECT a.id, a.jurisdiction, a.authority, a.approval_date, a.status, a.cancer_id, a.tumor_agnostic, count(*) OVER() AS approvals784 FROM drug_approvals a WHERE a.drug_id = ed.drug_id AND (a.cancer_id IN (SELECT id FROM ids) OR a.tumor_agnostic)785 ORDER BY (a.cancer_id IS NOT NULL) DESC, a.approval_date ASC NULLS LAST, a.id LIMIT 1786 ) ap ON true787 LEFT JOIN cancers ac ON ac.id = ap.cancer_id788 LEFT JOIN LATERAL (789 SELECT count(DISTINCT ti.trial_id) AS trials, count(DISTINCT ti.trial_id) FILTER (WHERE t.overall_status IN ${activeList()}) AS active_trials790 FROM trial_interventions ti JOIN trial_conditions tc ON tc.trial_id = ti.trial_id AND tc.cancer_id IN (SELECT id FROM ids) JOIN clinical_trials t ON t.id = ti.trial_id791 WHERE ti.drug_id = ed.drug_id792 ) tr ON true793 ORDER BY ed.lvl, ed.support DESC, fq.frequency DESC NULLS LAST, g.symbol, v.name, d.name794 LIMIT ${limit}`),795 [] as Row[],796 );797 const names = await cancerNames(rows.flatMap((r) => r.context_ids ?? []));798 const chains: PathChain[] = rows.map((r) => ({799 cancer: { id: focus.id, slug: focus.ref, name: focus.label },800 gene: { id: r.gene_id, symbol: r.symbol, frequency: r.frequency, casesAffected: r.cases_affected, casesProfiled: r.cases_profiled, cohorts: n(r.cohorts) },801 variant: { id: r.variant_id, slug: r.variant_slug, name: r.variant_name },802 drug: { id: r.drug_id, slug: r.drug_slug, name: r.drug_name },803 edge: { evidenceLevel: r.evidence_level, direction: r.direction, supportCount: n(r.support), sourceIds: r.source_ids ?? [], provenanceIds: (r.provenance_ids ?? []).map(Number), contextIds: r.context_ids ?? [], contextNames: (r.context_ids ?? []).map((id) => names.get(id)?.name ?? id) },804 approval: r.approval_id ? { id: Number(r.approval_id), jurisdiction: r.jurisdiction!, authority: r.authority!, approvalDate: r.approval_date, status: r.approval_status!, cancerId: r.approval_cancer_id, cancerName: r.approval_cancer_name, tumorAgnostic: !!r.tumor_agnostic, total: n(r.approvals) } : null,805 trials: r.trials !== null && r.trials !== undefined ? { total: n(r.trials), active: n(r.active_trials) } : null,806 }));807 return chains.sort(compareChains);808}809810// ---------------------------------------------------------------------------------------------811// Default focus + example foci (data-driven, never hardcoded)812// ---------------------------------------------------------------------------------------------813814export interface FocusSuggestion {815 type: Exclude<NodeType, 'approval' | 'trial' | 'variant'>;816 ref: string;817 label: string;818 edges: number;819}820821/** Most-connected cancers (2), genes (2) and drugs (2) by knowledge-edge count; the first cancer is the default focus. */822export async function suggestedFoci(): Promise<FocusSuggestion[]> {823 type Row = { type: FocusSuggestion['type']; ref: string; label: string; edges: string };824 const rows = await safe(825 () =>826 run<Row>(sql`827 WITH k AS (828 SELECT eid, count(*) AS n FROM (829 SELECT source_entity_id AS eid FROM knowledge_edges WHERE status = 'active'830 UNION ALL SELECT target_entity_id FROM knowledge_edges WHERE status = 'active'831 UNION ALL SELECT unnest(cancer_context_ids) FROM knowledge_edges WHERE status = 'active'832 ) x GROUP BY eid833 )834 (SELECT 'cancer' AS type, c.slug AS ref, c.canonical_name AS label, k.n AS edges FROM k JOIN cancers c ON c.id = k.eid WHERE c.status = 'active' ORDER BY k.n DESC, c.slug LIMIT 2)835 UNION ALL (SELECT 'gene', g.symbol, g.symbol, k.n FROM k JOIN genes g ON g.id = k.eid ORDER BY k.n DESC, g.symbol LIMIT 2)836 UNION ALL (SELECT 'drug', d.slug, d.name, k.n FROM k JOIN drugs d ON d.id = k.eid ORDER BY k.n DESC, d.slug LIMIT 2)`),837 [] as Row[],838 );839 return rows.map((r) => ({ type: r.type, ref: r.ref, label: r.label, edges: n(r.edges) }));840}841842export async function defaultFocus(): Promise<FocusRef | null> {843 const s = (await suggestedFoci()).find((x) => x.type === 'cancer');844 return s ? { type: 'cancer', ref: s.ref } : null;845}846847/** Freshness: latest last_seen_at across the focus' knowledge edges. */848export async function edgesFreshness(focus: FocusNode): Promise<Date | null> {849 const rows = await safe(() => run<{ t: Date | null }>(sql`SELECT max(last_seen_at) AS t FROM knowledge_edges WHERE source_entity_id = ${focus.id} OR target_entity_id = ${focus.id} OR ${focus.id} = ANY(cancer_context_ids)`), [{ t: null }]);850 return rows[0]?.t ?? null;851}852853