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 CancerCore {5 id: string;6 slug: string;7 canonical_name: string;8 short_name: string | null;9 entity_type: string;10 malignant: boolean;11 solid_tumor: boolean;12 hematologic: boolean;13 pediatric_relevant: boolean;14 rare_cancer: boolean | null;15 top_level: boolean;16 description: string | null;17 description_provenance_id: number | null;18 primary_ncit_code: string | null;19 primary_oncotree_code: string | null;20 depth: number;21 status: string;22 merged_into: string | null;23 deprecated_reason: string | null;24 classification_version: string | null;25 semantic_types: string[];26 created_at: Date;27 updated_at: Date;28}2930export interface Counters {31 trial_count: number;32 active_trial_count: number;33 recruiting_trial_count: number;34 phase3_trial_count: number;35 publication_count: number;36 publication_count_5y: number;37 publication_count_12m: number;38 gene_count: number;39 variant_count: number;40 drug_count: number;41 approved_drug_count: number;42 evidence_count: number;43 cohort_count: number;44 subtype_count: number;45 descendant_count: number;46 epidemiology_obs_count: number;47 survival_obs_count: number;48 completeness: Record<string, number>;49 updated_at: Date; // schema field `computedAt` is declared with the updatedAt() helper → column `updated_at`50}5152export const ENTITY_TYPES = ['cancer', 'cancer_family', 'histology', 'subtype', 'molecular_subtype', 'hematologic_malignancy', 'precursor_condition', 'other'] as const;53export const SORTS = ['name', 'trials', 'publications', 'evidence', 'descendants'] as const;54export type Sort = (typeof SORTS)[number];5556export interface ExplorerFilters {57 q: string;58 level: 'top' | 'all';59 entityType: string;60 malignant: boolean | null;61 hematologic: boolean | null;62 pediatric: boolean | null;63 site: string; // anatomical site slug64 sort: Sort;65 page: number;66 pageSize: number;67}6869export interface ExplorerRow extends CancerCore {70 parents: Array<{ slug: string; name: string }> | null;71 active_trial_count: number | null;72 publication_count_5y: number | null;73 evidence_count: number | null;74 descendant_count: number | null;75 epidemiology_obs_count: number | null;76 survival_obs_count: number | null;77 gene_count: number | null;78 drug_count: number | null;79 child_count: number;80}8182function explorerWhere(f: ExplorerFilters) {83 const parts = [sql`c.status = 'active'`];84 if (f.level === 'top') parts.push(sql`c.top_level`);85 if (f.q) {86 const q = f.q.trim();87 parts.push(sql`(c.canonical_name ILIKE ${'%' + q + '%'} OR c.slug ILIKE ${'%' + q + '%'} OR c.primary_oncotree_code ILIKE ${q} OR c.primary_ncit_code ILIKE ${q} OR c.id = ${q}88 OR EXISTS (SELECT 1 FROM cancer_aliases a WHERE a.cancer_id = c.id AND a.alias ILIKE ${'%' + q + '%'})89 OR EXISTS (SELECT 1 FROM cancer_codes k WHERE k.cancer_id = c.id AND k.code ILIKE ${q}))`);90 }91 if (f.entityType) parts.push(sql`c.entity_type = ${f.entityType}`);92 if (f.malignant != null) parts.push(sql`c.malignant = ${f.malignant}`);93 if (f.hematologic != null) parts.push(sql`c.hematologic = ${f.hematologic}`);94 if (f.pediatric != null) parts.push(sql`c.pediatric_relevant = ${f.pediatric}`);95 if (f.site) parts.push(sql`EXISTS (SELECT 1 FROM cancer_anatomy ca JOIN anatomical_sites s ON s.id = ca.site_id WHERE ca.cancer_id = c.id AND s.slug = ${f.site})`);96 return sql.join(parts, sql` AND `);97}9899export async function explorerCount(f: ExplorerFilters): Promise<number> {100 const rows = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM cancers c WHERE ${explorerWhere(f)}`), [{ n: '0' }]);101 return Number(rows[0]?.n ?? 0);102}103104export async function explorerRows(f: ExplorerFilters): Promise<ExplorerRow[]> {105 const order =106 f.sort === 'trials'107 ? sql`coalesce(ec.active_trial_count, 0) DESC, c.canonical_name`108 : f.sort === 'publications'109 ? sql`coalesce(ec.publication_count_5y, 0) DESC, c.canonical_name`110 : f.sort === 'evidence'111 ? sql`coalesce(ec.evidence_count, 0) DESC, c.canonical_name`112 : f.sort === 'descendants'113 ? sql`coalesce(ec.descendant_count, child.n) DESC, c.canonical_name`114 : sql`c.canonical_name`;115 return safe(116 () =>117 run<ExplorerRow>(sql`118 SELECT c.*, ec.active_trial_count, ec.publication_count_5y, ec.evidence_count, ec.descendant_count, ec.epidemiology_obs_count, ec.survival_obs_count, ec.gene_count, ec.drug_count,119 child.n AS child_count,120 (SELECT json_agg(json_build_object('slug', p.slug, 'name', p.canonical_name) ORDER BY p.canonical_name)121 FROM (SELECT DISTINCT pc.slug, pc.canonical_name FROM cancer_hierarchy h JOIN cancers pc ON pc.id = h.parent_id WHERE h.child_id = c.id LIMIT 3) p) AS parents122 FROM cancers c123 LEFT JOIN entity_counters ec ON ec.entity_type = 'cancer' AND ec.entity_id = c.id124 LEFT JOIN LATERAL (SELECT count(*) AS n FROM cancer_hierarchy h WHERE h.parent_id = c.id) child ON true125 WHERE ${explorerWhere(f)}126 ORDER BY ${order}127 LIMIT ${f.pageSize} OFFSET ${(f.page - 1) * f.pageSize}`),128 [] as ExplorerRow[],129 );130}131132export async function listAnatomicalSites(): Promise<Array<{ id: string; slug: string; name: string; system: string | null; n: number }>> {133 const rows = await safe(134 () => run<{ id: string; slug: string; name: string; system: string | null; n: string }>(sql`SELECT s.id, s.slug, s.name, s.system, (SELECT count(*) FROM cancer_anatomy ca WHERE ca.site_id = s.id) AS n FROM anatomical_sites s ORDER BY s.name`),135 [] as Array<{ id: string; slug: string; name: string; system: string | null; n: string }>,136 );137 return rows.map((r) => ({ ...r, n: Number(r.n) }));138}139140// ---------- Detail ----------141142export async function getCancerBySlug(slug: string): Promise<CancerCore | null> {143 const rows = await safe(() => run<CancerCore>(sql`SELECT * FROM cancers WHERE slug = ${slug} LIMIT 1`), [] as CancerCore[]);144 return rows[0] ?? null;145}146147export async function getCancerById(id: string): Promise<CancerCore | null> {148 const rows = await safe(() => run<CancerCore>(sql`SELECT * FROM cancers WHERE id = ${id} LIMIT 1`), [] as CancerCore[]);149 return rows[0] ?? null;150}151152export async function getCounters(cancerId: string): Promise<Counters | null> {153 const rows = await safe(() => run<Counters>(sql`SELECT * FROM entity_counters WHERE entity_type = 'cancer' AND entity_id = ${cancerId}`), [] as Counters[]);154 return rows[0] ?? null;155}156157export interface CodeRow {158 system: string;159 code: string;160 match_type: string;161 source_slug: string | null;162}163export async function getCodes(cancerId: string): Promise<CodeRow[]> {164 return safe(() => run<CodeRow>(sql`SELECT k.system, k.code, k.match_type, s.slug AS source_slug FROM cancer_codes k LEFT JOIN sources s ON s.id = k.source_id WHERE k.cancer_id = ${cancerId} ORDER BY k.system, k.code`), [] as CodeRow[]);165}166167export interface AliasRow {168 alias: string;169 alias_type: string;170 source_terminology: string | null;171 source_slug: string | null;172}173export async function getAliases(cancerId: string): Promise<AliasRow[]> {174 return safe(() => run<AliasRow>(sql`SELECT a.alias, a.alias_type, a.source_terminology, s.slug AS source_slug FROM cancer_aliases a LEFT JOIN sources s ON s.id = a.source_id WHERE a.cancer_id = ${cancerId} ORDER BY a.alias_type, a.alias`), [] as AliasRow[]);175}176177export interface RelRow {178 id: string;179 slug: string;180 canonical_name: string;181 entity_type: string;182 hierarchy_type: string;183 malignant: boolean;184 child_count: number;185}186export async function getParents(cancerId: string): Promise<RelRow[]> {187 return safe(188 () =>189 run<RelRow>(sql`190 SELECT p.id, p.slug, p.canonical_name, p.entity_type, h.hierarchy_type, p.malignant, (SELECT count(*) FROM cancer_hierarchy x WHERE x.parent_id = p.id)::int AS child_count191 FROM cancer_hierarchy h JOIN cancers p ON p.id = h.parent_id WHERE h.child_id = ${cancerId} ORDER BY h.hierarchy_type, p.canonical_name`),192 [] as RelRow[],193 );194}195export async function getChildren(cancerId: string, limit = 500): Promise<RelRow[]> {196 return safe(197 () =>198 run<RelRow>(sql`199 SELECT c.id, c.slug, c.canonical_name, c.entity_type, h.hierarchy_type, c.malignant, (SELECT count(*) FROM cancer_hierarchy x WHERE x.parent_id = c.id)::int AS child_count200 FROM cancer_hierarchy h JOIN cancers c ON c.id = h.child_id WHERE h.parent_id = ${cancerId} ORDER BY h.hierarchy_type, c.canonical_name LIMIT ${limit}`),201 [] as RelRow[],202 );203}204205/** Shortest path from a root (node without parent) to this cancer, preferring NCIt, then OncoTree. */206export async function getBreadcrumbPath(cancerId: string): Promise<Array<{ id: string; slug: string; canonical_name: string }>> {207 const rows = await safe(208 () =>209 run<{ path_ids: string[]; hierarchy_type: string }>(sql`210 WITH RECURSIVE up AS (211 SELECT c.id, ARRAY[c.id]::varchar[] AS path_ids, NULL::text AS hierarchy_type, 0 AS depth FROM cancers c WHERE c.id = ${cancerId}212 UNION ALL213 SELECT h.parent_id, h.parent_id || up.path_ids, coalesce(up.hierarchy_type, h.hierarchy_type), up.depth + 1214 FROM up JOIN cancer_hierarchy h ON h.child_id = up.id215 WHERE up.depth < 14 AND NOT (h.parent_id = ANY(up.path_ids)) AND (up.hierarchy_type IS NULL OR h.hierarchy_type = up.hierarchy_type)216 )217 SELECT path_ids, hierarchy_type FROM up218 WHERE NOT EXISTS (SELECT 1 FROM cancer_hierarchy h2 WHERE h2.child_id = up.id AND (up.hierarchy_type IS NULL OR h2.hierarchy_type = up.hierarchy_type))219 ORDER BY (hierarchy_type = 'ncit') DESC, array_length(path_ids, 1) ASC LIMIT 1`),220 [] as Array<{ path_ids: string[]; hierarchy_type: string }>,221 );222 const ids = rows[0]?.path_ids ?? [];223 if (ids.length <= 1) return [];224 const nodes = await safe(225 () => run<{ id: string; slug: string; canonical_name: string }>(sql`SELECT id, slug, canonical_name FROM cancers WHERE id IN (${sql.join(ids.map((i) => sql`${i}`), sql`, `)})`),226 [] as Array<{ id: string; slug: string; canonical_name: string }>,227 );228 const byId = new Map(nodes.map((n) => [n.id, n]));229 return ids.map((i) => byId.get(i)).filter((n): n is { id: string; slug: string; canonical_name: string } => !!n);230}231232/** All descendant ids (inclusive) across hierarchy types — used for trial/evidence roll-ups. */233export async function getDescendantIds(cancerId: string): Promise<string[]> {234 const rows = await safe(235 () =>236 run<{ id: string }>(sql`237 WITH RECURSIVE d AS (238 SELECT ${cancerId}::varchar AS id, 0 AS depth239 UNION240 SELECT h.child_id, d.depth + 1 FROM d JOIN cancer_hierarchy h ON h.parent_id = d.id WHERE d.depth < 12241 ) SELECT DISTINCT id FROM d`),242 [{ id: cancerId }],243 );244 return rows.map((r) => r.id);245}246247export interface AnatomyRow {248 site_id: string;249 slug: string;250 name: string;251 system: string | null;252 relation: string;253 ncit_code: string | null;254}255export async function getAnatomy(cancerId: string): Promise<AnatomyRow[]> {256 return safe(() => run<AnatomyRow>(sql`SELECT s.id AS site_id, s.slug, s.name, s.system, ca.relation, s.ncit_code FROM cancer_anatomy ca JOIN anatomical_sites s ON s.id = ca.site_id WHERE ca.cancer_id = ${cancerId} ORDER BY ca.relation, s.name`), [] as AnatomyRow[]);257}258259export interface ChangeEvent {260 id: number;261 kind: string;262 summary: string;263 before: unknown;264 after: unknown;265 ingest_run_id: string | null;266 created_at: Date;267}268export async function getChangeEvents(entityType: string, entityId: string, limit = 50): Promise<ChangeEvent[]> {269 return safe(() => run<ChangeEvent>(sql`SELECT id, kind, summary, before, after, ingest_run_id, created_at FROM change_events WHERE entity_type = ${entityType} AND entity_id = ${entityId} ORDER BY created_at DESC LIMIT ${limit}`), [] as ChangeEvent[]);270}271272/** Sources (with run ids) that contributed anything to this cancer — union across tables. */273export async function getContributingSources(cancerId: string, descendantIds: string[]): Promise<Array<{ slug: string; name: string; license_status: string; kinds: string[]; run_ids: string[]; last_retrieved: Date | null }>> {274 const ids = sql.join(descendantIds.map((i) => sql`${i}`), sql`, `);275 return safe(276 () =>277 run<{ slug: string; name: string; license_status: string; kinds: string[]; run_ids: string[]; last_retrieved: Date | null }>(sql`278 WITH contrib AS (279 SELECT k.source_id, 'codes' AS kind, NULL::text AS run_id, NULL::timestamptz AS at FROM cancer_codes k WHERE k.cancer_id = ${cancerId} AND k.source_id IS NOT NULL280 UNION ALL SELECT a.source_id, 'aliases', NULL, NULL FROM cancer_aliases a WHERE a.cancer_id = ${cancerId} AND a.source_id IS NOT NULL281 UNION ALL SELECT h.source_id, 'hierarchy', NULL, NULL FROM cancer_hierarchy h WHERE (h.child_id = ${cancerId} OR h.parent_id = ${cancerId}) AND h.source_id IS NOT NULL282 UNION ALL SELECT r.source_id, 'source_records', r.last_seen_run, r.retrieved_at FROM source_records r WHERE r.canonical_type = 'cancer' AND r.canonical_id = ${cancerId}283 UNION ALL SELECT o.source_id, 'epidemiology', o.ingest_run_id, o.updated_at FROM epidemiology_observations o WHERE o.cancer_id = ${cancerId}284 UNION ALL SELECT o.source_id, 'survival', o.ingest_run_id, o.updated_at FROM survival_observations o WHERE o.cancer_id = ${cancerId}285 UNION ALL SELECT p.source_id, 'evidence', e.ingest_run_id, e.updated_at FROM civic_evidence_items e JOIN provenance p ON p.id = e.provenance_id WHERE e.cancer_id IN (${ids})286 UNION ALL SELECT gc.source_id, 'genomic_cohorts', NULL, gc.updated_at FROM genomic_cohorts gc WHERE gc.cancer_id IN (${ids})287 UNION ALL SELECT p.source_id, 'literature_counts', p.ingest_run_id, lc.updated_at FROM literature_counts lc JOIN provenance p ON p.id = lc.provenance_id WHERE lc.cancer_id = ${cancerId}288 UNION ALL SELECT da.source_id, 'approvals', NULL, da.updated_at FROM drug_approvals da WHERE da.cancer_id IN (${ids})289 UNION ALL SELECT p.source_id, 'description', p.ingest_run_id, p.retrieved_at FROM cancers c JOIN provenance p ON p.id = c.description_provenance_id WHERE c.id = ${cancerId}290 )291 SELECT s.slug, s.name, s.license_status, array_agg(DISTINCT c.kind) AS kinds, array_remove(array_agg(DISTINCT c.run_id), NULL) AS run_ids, max(c.at) AS last_retrieved292 FROM contrib c JOIN sources s ON s.id = c.source_id GROUP BY s.id ORDER BY s.name`),293 [],294 );295}296297/** Top-level ranking set: TOP_LEVEL_CANCERS resolved through NCIt codes (or the top_level flag). */298export async function resolveTopLevel(ncitCodes: string[]): Promise<Map<string, { id: string; slug: string; canonical_name: string; descendant_count: number | null; active_trial_count: number | null; publication_count_5y: number | null }>> {299 if (ncitCodes.length === 0) return new Map();300 const rows = await safe(301 () =>302 run<{ ncit: string; id: string; slug: string; canonical_name: string; descendant_count: number | null; active_trial_count: number | null; publication_count_5y: number | null }>(sql`303 SELECT k.code AS ncit, c.id, c.slug, c.canonical_name, ec.descendant_count, ec.active_trial_count, ec.publication_count_5y304 FROM cancer_codes k JOIN cancers c ON c.id = k.cancer_id305 LEFT JOIN entity_counters ec ON ec.entity_type = 'cancer' AND ec.entity_id = c.id306 WHERE k.system = 'ncit' AND k.code IN (${sql.join(ncitCodes.map((c) => sql`${c}`), sql`, `)}) AND c.status = 'active'`),307 [],308 );309 return new Map(rows.map((r) => [r.ncit, r]));310}311312export async function cancerSlugsForSitemap(offset: number, limit: number): Promise<Array<{ slug: string; updated_at: Date }>> {313 return safe(() => run<{ slug: string; updated_at: Date }>(sql`SELECT slug, updated_at FROM cancers WHERE status = 'active' ORDER BY id LIMIT ${limit} OFFSET ${offset}`), []);314}315export async function countActiveCancers(): Promise<number> {316 const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM cancers WHERE status = 'active'`), [{ n: '0' }]);317 return Number(r[0]?.n ?? 0);318}319