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 { EvidenceItem } from '@/lib/queries/evidence';4import { EVIDENCE_DESCRIPTION_CHARS } from '@/lib/queries/evidence';5import type { ApprovalRow } from '@/lib/queries/drugs';6import type { TrialListRow } from '@/lib/queries/trials';7import { ACTIVE_STATUSES } from '@/lib/queries/trials';89/**10 * Biomarkers (SPEC §17, §52, §121). The `biomarkers` table holds curated metadata only (identity,11 * verified NCIt concept, anchor gene, aliases, assay conventions — seeded from12 * packages/database/src/seed-data/biomarkers.ts). Every link to cancers, drugs, approvals, trials13 * and publications is DERIVED HERE at query time from source-native records, with these rules14 * (docs/methodology/biomarkers.md, formula `biomarker-links-v1`):15 *16 * scope gene_ids = anchor gene ∪ measurement.genes (HGNC symbols → genes.id);17 * variant_ids = measurement.variantSlugs → variants.id. When variant_ids is non-empty18 * the marker is molecular-level and evidence is restricted to those variants; otherwise19 * the whole gene(s).20 * evidence CIViC items with status ACCEPTED and type PREDICTIVE | PROGNOSTIC | DIAGNOSTIC whose21 * variant_ids (or gene_ids) overlap the scope. Levels A–E kept native.22 * cancers distinct mapped cancer_id of that evidence.23 * drugs therapy_ids of the PREDICTIVE evidence ∪ targets of active PREDICTS_RESPONSE_TO24 * knowledge edges whose source variant is in scope. Direction from the source.25 * approvals drug_approvals of those drugs (matched_by = drug), plus rows whose indication text26 * contains a curated `indicationTerms` phrase (matched_by = indication). tumor_agnostic27 * is the source flag, never inferred.28 * trials clinical_trials with an intervention mapped to a scope drug AND a condition mapped to29 * a scope cancer; "active" = ClinicalTrials.gov statuses in ACTIVE_STATUSES.30 * literature publication_entity_edges on the scope variants (molecular markers) or genes.31 */32export const BIOMARKER_LINKS_FORMULA = 'biomarker-links-v1';3334export const BIOMARKER_KINDS = ['gene_mutation', 'protein_expression', 'hormone_receptor', 'immune_marker', 'msi', 'tmb', 'hrd', 'ctdna', 'methylation', 'signature', 'cell_surface', 'other'] as const;35export type BiomarkerKind = (typeof BIOMARKER_KINDS)[number];36export const BIOMARKER_KIND_LABEL: Record<string, string> = {37 gene_mutation: 'Gene alteration',38 protein_expression: 'Protein expression',39 hormone_receptor: 'Hormone receptor',40 immune_marker: 'Immune marker',41 msi: 'MSI / MMR',42 tmb: 'Tumor mutational burden',43 hrd: 'HR deficiency',44 ctdna: 'Circulating tumor DNA',45 methylation: 'Methylation',46 signature: 'Signature',47 cell_surface: 'Cell-surface target',48 other: 'Other',49};50export function kindLabel(kind: string): string {51 return BIOMARKER_KIND_LABEL[kind] ?? kind.replace(/_/g, ' ');52}5354/** Mirror of the seed's `measurement` jsonb (written only by the seed). */55export interface BiomarkerMeasurement {56 assays?: string[];57 scoring?: string;58 notes?: string;59 sources?: Array<{ label: string; url: string }>;60 aliases?: string[];61 genes?: string[];62 variantSlugs?: string[];63 indicationTerms?: string[];64 tumorAgnostic?: boolean;65 ncit?: { code: string; name: string; conceptKind: string };66 verification?: { authority: string; endpoint: string; ncitVersion: string; verifiedAt: string };67}6869export interface BiomarkerRow {70 id: string;71 slug: string;72 name: string;73 kind: string;74 gene_id: string | null;75 ncit_code: string | null;76 description: string | null;77 measurement: BiomarkerMeasurement;78 updated_at: Date;79 /** Resolved scope (see module doc). */80 gene_ids: string[];81 variant_ids: string[];82 gene_symbols: string[];83}8485export interface BiomarkerListRow extends BiomarkerRow {86 cancers_n: number;87 drugs_n: number;88 approvals_n: number;89 tumor_agnostic_n: number;90 active_trials_n: number;91}9293/* ------------------------------------------------------------------------------------------------94 * Scope fragments — one CTE `b` carrying gene_ids / variant_ids, reused by every derived block.95 * ---------------------------------------------------------------------------------------------- */9697const SCOPE_COLUMNS = sql`98 b.id, b.slug, b.name, b.kind, b.gene_id, b.ncit_code, b.description, b.measurement, b.updated_at,99 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,100 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`;101102// MATERIALIZED: the scope arrays are sub-selects over 45k genes / 417k variants; if PostgreSQL inlines103// this single-reference CTE they are re-evaluated per joined evidence row (22 s for MSI-H, measured104// 2026-09-11) instead of once.105const scopeCte = (where: ReturnType<typeof sql>) => sql`WITH b AS MATERIALIZED (SELECT ${SCOPE_COLUMNS} FROM biomarkers b WHERE ${where})`;106const scopeById = (id: string) => scopeCte(sql`b.id = ${id}`);107108/** Evidence in scope (alias `e` = civic_evidence_items, `b` = scope CTE). */109const EV_SCOPE = sql`e.status = 'ACCEPTED' AND e.evidence_type IN ('PREDICTIVE', 'PROGNOSTIC', 'DIAGNOSTIC')110 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`;111112/** Variants in scope (alias `v`). */113const 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`;114115/** Derived drug set per biomarker: (bid, drug_id). */116const DSET = sql`dset AS (117 SELECT DISTINCT bid, drug_id FROM (118 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) t119 UNION120 SELECT b.id, k.target_entity_id FROM b JOIN variants v ON ${VAR_SCOPE}121 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'122 ) u)`;123/** Derived cancer set per biomarker: (bid, cancer_id). */124const 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)`;125126/**127 * Derived trial set per biomarker: (bid, trial_id) = trials with an intervention mapped to a scope128 * drug AND a condition mapped to a scope cancer. Plain hash joins, MATERIALIZED once: a LIMIT-ed129 * outer query must never walk clinical_trials in date order evaluating EXISTS per row (20 s for HER2130 * vs ~120 ms for this form, measured 2026-09-11).131 */132const TSET = sql`tset AS MATERIALIZED (133 SELECT DISTINCT d.bid, ti.trial_id FROM dset d JOIN trial_interventions ti ON ti.drug_id = d.drug_id134 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)`;135136/** Indication-text match on curated terms (alias `a` = drug_approvals). */137const TERM_MATCH = sql`EXISTS (SELECT 1 FROM jsonb_array_elements_text(coalesce(b.measurement->'indicationTerms', '[]'::jsonb)) term WHERE a.indication ILIKE '%' || term || '%')`;138139// sql.param binds the whole array as ONE parameter; interpolating a JS array directly expands to a140// ($1, $2, …) tuple, which is not a valid text[] cast.141const ACTIVE = sql`t.overall_status = ANY(${sql.param(ACTIVE_STATUSES)}::text[])`;142143/* ------------------------------------------------------------------------------------------------144 * List145 * ---------------------------------------------------------------------------------------------- */146147export async function biomarkerKindFacets(): Promise<Array<{ kind: string; n: number }>> {148 const rows = await safe(() => run<{ kind: string; n: string }>(sql`SELECT kind, count(*) AS n FROM biomarkers GROUP BY kind ORDER BY n DESC, kind`), []);149 return rows.map((r) => ({ kind: r.kind, n: Number(r.n) }));150}151152export async function listBiomarkers(opts: { kind?: string; q?: string } = {}): Promise<BiomarkerListRow[]> {153 const conds = [sql`true`];154 if (opts.kind) conds.push(sql`b.kind = ${opts.kind}`);155 if (opts.q) {156 const like = `%${opts.q}%`;157 conds.push(sql`(b.name ILIKE ${like} OR b.slug ILIKE ${like} OR b.ncit_code ILIKE ${opts.q + '%'}158 OR EXISTS (SELECT 1 FROM jsonb_array_elements_text(coalesce(b.measurement->'aliases', '[]'::jsonb)) a WHERE a ILIKE ${like})159 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 ${opts.q + '%'}))`);160 }161 const rows = await safe(162 () =>163 run<Omit<BiomarkerListRow, 'cancers_n' | 'drugs_n' | 'approvals_n' | 'tumor_agnostic_n' | 'active_trials_n'> & { cancers_n: string; drugs_n: string; approvals_n: string; tumor_agnostic_n: string; active_trials_n: string }>(sql`164 ${scopeCte(sql.join(conds, sql` AND `))}, ${DSET}, ${CSET}, ${TSET}165 SELECT b.*,166 coalesce((SELECT array_agg(g.symbol ORDER BY g.symbol) FROM genes g WHERE g.id = ANY(b.gene_ids)), '{}') AS gene_symbols,167 (SELECT count(*) FROM cset c WHERE c.bid = b.id) AS cancers_n,168 (SELECT count(*) FROM dset d WHERE d.bid = b.id) AS drugs_n,169 (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,170 (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,171 (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_n172 FROM b ORDER BY b.kind, b.name`),173 [],174 );175 return rows.map((r) => ({ ...r, cancers_n: Number(r.cancers_n), drugs_n: Number(r.drugs_n), approvals_n: Number(r.approvals_n), tumor_agnostic_n: Number(r.tumor_agnostic_n), active_trials_n: Number(r.active_trials_n) }));176}177178export async function countBiomarkers(): Promise<number> {179 const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM biomarkers`), [{ n: '0' }]);180 return Number(r[0]?.n ?? 0);181}182183export async function biomarkerSlugsForSitemap(): Promise<Array<{ slug: string; updated_at: Date }>> {184 return safe(() => run<{ slug: string; updated_at: Date }>(sql`SELECT slug, updated_at FROM biomarkers ORDER BY id`), []);185}186187/* ------------------------------------------------------------------------------------------------188 * Detail189 * ---------------------------------------------------------------------------------------------- */190191export interface BiomarkerGene {192 id: string;193 symbol: string;194 name: string | null;195 is_cancer_gene: boolean;196}197export interface BiomarkerVariant {198 id: string;199 slug: string;200 name: string;201 gene_symbol: string | null;202 variant_type: string | null;203}204205export async function getBiomarkerBySlug(slug: string): Promise<BiomarkerRow | null> {206 const rows = await safe(207 () =>208 run<BiomarkerRow>(sql`209 ${scopeCte(sql`b.slug = ${slug} OR b.id = ${slug}`)}210 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`),211 [] as BiomarkerRow[],212 );213 return rows[0] ?? null;214}215216export async function biomarkerGenes(b: BiomarkerRow): Promise<BiomarkerGene[]> {217 if (b.gene_ids.length === 0) return [];218 return safe(() => run<BiomarkerGene>(sql`SELECT g.id, g.symbol, g.name, g.is_cancer_gene FROM genes g WHERE g.id = ANY(${sql.param(b.gene_ids)}::text[]) ORDER BY g.symbol`), []);219}220221export async function biomarkerVariants(b: BiomarkerRow): Promise<BiomarkerVariant[]> {222 if (b.variant_ids.length === 0) return [];223 return safe(() => run<BiomarkerVariant>(sql`SELECT v.id, v.slug, v.name, v.gene_symbol, v.variant_type FROM variants v WHERE v.id = ANY(${sql.param(b.variant_ids)}::text[]) ORDER BY v.gene_symbol, v.name`), []);224}225226export interface BiomarkerCancerRow {227 cancer_id: string;228 slug: string;229 name: string;230 n: number;231 predictive: number;232 prognostic: number;233 diagnostic: number;234 level_a: number;235 level_b: number;236 level_c: number;237 level_d: number;238 level_e: number;239}240241/** Associated cancers = mapped cancers of the in-scope evidence, with counts by type and native level. */242export async function biomarkerCancers(b: BiomarkerRow): Promise<BiomarkerCancerRow[]> {243 const rows = await safe(244 () =>245 run<Record<keyof BiomarkerCancerRow, string>>(sql`246 ${scopeById(b.id)}247 SELECT c.id AS cancer_id, c.slug, c.canonical_name AS name, count(*) AS n,248 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,249 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,250 count(*) FILTER (WHERE e.evidence_level = 'D') AS level_d, count(*) FILTER (WHERE e.evidence_level = 'E') AS level_e251 FROM b JOIN civic_evidence_items e ON ${EV_SCOPE} JOIN cancers c ON c.id = e.cancer_id252 GROUP BY c.id, c.slug, c.canonical_name ORDER BY n DESC, c.canonical_name`),253 [],254 );255 return rows.map((r) => ({ cancer_id: r.cancer_id, slug: r.slug, name: r.name, n: Number(r.n), predictive: Number(r.predictive), prognostic: Number(r.prognostic), diagnostic: Number(r.diagnostic), level_a: Number(r.level_a), level_b: Number(r.level_b), level_c: Number(r.level_c), level_d: Number(r.level_d), level_e: Number(r.level_e) }));256}257258export interface BiomarkerDrugRow {259 drug_id: string;260 slug: string;261 name: string;262 kind: string | null;263 /** PREDICTIVE CIViC items in scope naming this therapy. */264 evidence_n: number;265 sensitivity: number;266 resistance: number;267 /** Best (lowest letter) native CIViC level among those items; null when only knowledge edges link the drug. */268 best_level: string | null;269 /** Active PREDICTS_RESPONSE_TO knowledge edges from in-scope variants to this drug. */270 edge_n: number;271 edge_sensitivity: number;272 edge_resistance: number;273 cancer_names: string[];274 cancer_slugs: string[];275}276277/** Drugs with predictive evidence: CIViC therapies ∪ knowledge-edge targets, with direction counts. */278export async function biomarkerDrugs(b: BiomarkerRow): Promise<BiomarkerDrugRow[]> {279 const rows = await safe(280 () =>281 run<Record<string, unknown>>(sql`282 ${scopeById(b.id)}, ${DSET},283 ev AS (284 SELECT t AS drug_id, e.significance, e.evidence_level, e.cancer_id285 FROM b JOIN civic_evidence_items e ON ${EV_SCOPE} AND e.evidence_type = 'PREDICTIVE' CROSS JOIN LATERAL unnest(e.therapy_ids) t),286 ke AS (287 SELECT k.target_entity_id AS drug_id, k.direction288 FROM b JOIN variants v ON ${VAR_SCOPE}289 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')290 SELECT d.id AS drug_id, d.slug, d.name, d.kind,291 (SELECT count(*) FROM ev WHERE ev.drug_id = d.id) AS evidence_n,292 (SELECT count(*) FROM ev WHERE ev.drug_id = d.id AND ev.significance ILIKE '%SENSITIV%') AS sensitivity,293 (SELECT count(*) FROM ev WHERE ev.drug_id = d.id AND ev.significance ILIKE '%RESIST%') AS resistance,294 (SELECT min(ev.evidence_level) FROM ev WHERE ev.drug_id = d.id) AS best_level,295 (SELECT count(*) FROM ke WHERE ke.drug_id = d.id) AS edge_n,296 (SELECT count(*) FROM ke WHERE ke.drug_id = d.id AND ke.direction = 'sensitivity') AS edge_sensitivity,297 (SELECT count(*) FROM ke WHERE ke.drug_id = d.id AND ke.direction = 'resistance') AS edge_resistance,298 coalesce((SELECT array_agg(DISTINCT c.canonical_name ORDER BY c.canonical_name) FROM ev JOIN cancers c ON c.id = ev.cancer_id WHERE ev.drug_id = d.id), '{}') AS cancer_names,299 coalesce((SELECT array_agg(DISTINCT c.slug ORDER BY c.slug) FROM ev JOIN cancers c ON c.id = ev.cancer_id WHERE ev.drug_id = d.id), '{}') AS cancer_slugs300 FROM dset JOIN drugs d ON d.id = dset.drug_id301 ORDER BY evidence_n DESC, edge_n DESC, d.name`),302 [],303 );304 return rows.map((r) => ({305 drug_id: r.drug_id as string,306 slug: r.slug as string,307 name: r.name as string,308 kind: (r.kind as string | null) ?? null,309 evidence_n: Number(r.evidence_n),310 sensitivity: Number(r.sensitivity),311 resistance: Number(r.resistance),312 best_level: (r.best_level as string | null) ?? null,313 edge_n: Number(r.edge_n),314 edge_sensitivity: Number(r.edge_sensitivity),315 edge_resistance: Number(r.edge_resistance),316 cancer_names: (r.cancer_names as string[]) ?? [],317 cancer_slugs: (r.cancer_slugs as string[]) ?? [],318 }));319}320321/* Evidence table rows — same shape as lib/queries/evidence.ts so <EvidenceTable> renders them. */322const EVIDENCE_SELECT = sql`323 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,324 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,325 e.pmid, e.source_citation, e.provenance_id, e.updated_at,326 c.slug AS cancer_slug, c.canonical_name AS cancer_name,327 (SELECT array_agg(v.slug ORDER BY v.slug) FROM variants v WHERE v.id = ANY(e.variant_ids)) AS variant_slugs,328 (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,329 (SELECT array_agg(d.slug ORDER BY d.slug) FROM drugs d WHERE d.id = ANY(e.therapy_ids)) AS therapy_slugs,330 (SELECT array_agg(d.name ORDER BY d.slug) FROM drugs d WHERE d.id = ANY(e.therapy_ids)) AS therapy_slug_names,331 left(e.description, ${EVIDENCE_DESCRIPTION_CHARS}) AS description332 FROM b JOIN civic_evidence_items e ON ${EV_SCOPE} LEFT JOIN cancers c ON c.id = e.cancer_id`;333334export type BiomarkerEvidenceType = 'PREDICTIVE' | 'PROGNOSTIC' | 'DIAGNOSTIC';335336export async function biomarkerEvidence(b: BiomarkerRow, opts: { type?: BiomarkerEvidenceType; page: number; pageSize: number }): Promise<EvidenceItem[]> {337 const typeFilter = opts.type ? sql`WHERE e.evidence_type = ${opts.type}` : sql``;338 return safe(339 () =>340 run<EvidenceItem>(sql`${scopeById(b.id)} ${EVIDENCE_SELECT} ${typeFilter}341 ORDER BY c.canonical_name NULLS LAST, e.disease_name NULLS LAST, array_to_string(e.therapy_names, '+'), e.evidence_level NULLS LAST, e.civic_id342 LIMIT ${opts.pageSize} OFFSET ${(Math.max(1, opts.page) - 1) * opts.pageSize}`),343 [] as EvidenceItem[],344 );345}346export async function biomarkerEvidenceCount(b: BiomarkerRow, type?: BiomarkerEvidenceType): Promise<number> {347 const typeFilter = type ? sql`AND e.evidence_type = ${type}` : sql``;348 const r = await safe(() => run<{ n: string }>(sql`${scopeById(b.id)} SELECT count(*) AS n FROM b JOIN civic_evidence_items e ON ${EV_SCOPE} ${typeFilter}`), [{ n: '0' }]);349 return Number(r[0]?.n ?? 0);350}351352export type BiomarkerApprovalRow = ApprovalRow & { matched_by: 'drug' | 'indication' | 'both' };353354/** Approvals of the scope drugs (matched_by = drug) and/or whose indication names the biomarker (matched_by = indication). */355export async function biomarkerApprovals(b: BiomarkerRow): Promise<BiomarkerApprovalRow[]> {356 return safe(357 () =>358 run<BiomarkerApprovalRow>(sql`359 ${scopeById(b.id)}, ${DSET}360 SELECT a.*, a.raw->>'dpdStatus' AS source_status, d.slug AS drug_slug, d.name AS drug_name, c.slug AS cancer_slug, c.canonical_name AS cancer_name, s.slug AS source_slug, s.name AS source_name,361 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_by362 FROM b, 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_id363 WHERE a.drug_id IN (SELECT d2.drug_id FROM dset d2) OR ${TERM_MATCH}364 ORDER BY a.tumor_agnostic DESC, d.name, a.jurisdiction, a.approval_date DESC NULLS LAST LIMIT 500`),365 [] as BiomarkerApprovalRow[],366 );367}368369export interface BiomarkerTrialCounts {370 total: number;371 active: number;372 recruiting: number;373 phase3: number;374}375376/** Trials of the scope drugs in the scope cancers (counts by status/phase). */377export async function biomarkerTrialCounts(b: BiomarkerRow): Promise<BiomarkerTrialCounts> {378 const r = await safe(379 () =>380 run<{ total: string; active: string; recruiting: string; phase3: string }>(sql`381 ${scopeById(b.id)}, ${DSET}, ${CSET}, ${TSET}382 SELECT count(*) AS total, count(*) FILTER (WHERE ${ACTIVE}) AS active, count(*) FILTER (WHERE t.overall_status = 'RECRUITING') AS recruiting,383 count(*) FILTER (WHERE ${ACTIVE} AND 'PHASE3' = ANY(t.phases)) AS phase3384 FROM tset x JOIN clinical_trials t ON t.id = x.trial_id`),385 [{ total: '0', active: '0', recruiting: '0', phase3: '0' }],386 );387 const x = r[0]!;388 return { total: Number(x.total), active: Number(x.active), recruiting: Number(x.recruiting), phase3: Number(x.phase3) };389}390391const TRIAL_LIST_COLUMNS = sql`t.id, t.nct_id, t.brief_title, t.acronym, t.phases, t.overall_status, t.enrollment_count, t.lead_sponsor, t.lead_sponsor_class, t.countries, t.last_update_posted_date, t.updated_at`;392393/** Active trials in scope, most recently updated first. */394export async function biomarkerActiveTrials(b: BiomarkerRow, p: { page: number; pageSize: number }): Promise<TrialListRow[]> {395 return safe(396 () =>397 run<TrialListRow>(sql`398 ${scopeById(b.id)}, ${DSET}, ${CSET}, ${TSET}399 SELECT ${TRIAL_LIST_COLUMNS} FROM tset x JOIN clinical_trials t ON t.id = x.trial_id WHERE ${ACTIVE}400 ORDER BY t.last_update_posted_date DESC NULLS LAST, t.nct_id LIMIT ${p.pageSize} OFFSET ${(Math.max(1, p.page) - 1) * p.pageSize}`),401 [] as TrialListRow[],402 );403}404405/** Which entity type / ids the literature block should query (variants for molecular markers, genes otherwise). */406export function literatureScope(b: BiomarkerRow): { entityType: 'variant' | 'gene'; ids: string[] } {407 return b.variant_ids.length ? { entityType: 'variant', ids: b.variant_ids } : { entityType: 'gene', ids: b.gene_ids };408}409410/** Why a derived block is empty for non-gene markers (rendered in the EmptyState). */411export function noScopeReason(b: BiomarkerRow): string | null {412 if (b.gene_ids.length || b.variant_ids.length) return null;413 return b.measurement.notes ?? 'This biomarker is not anchored to a gene or variant entity, so gene-derived links cannot be computed.';414}415