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 GeneRow {5 id: string;6 hgnc_id: string | null;7 symbol: string;8 name: string | null;9 locus_type: string | null;10 locus_group: string | null;11 location: string | null;12 chromosome: string | null;13 ensembl_gene_id: string | null;14 ncbi_gene_id: string | null;15 omim_ids: string[];16 uniprot_ids: string[];17 refseq_accession: string | null;18 prev_symbols: string[];19 alias_symbols: string[];20 gene_families: string[];21 status: string;22 is_cancer_gene: boolean;23 civic_gene_id: number | null;24 description: string | null;25 updated_at: Date;26 evidence_count?: number;27 variant_count?: number;28 cohort_count?: number;29}3031export async function getGeneBySymbol(symbol: string): Promise<GeneRow | null> {32 const rows = await safe(33 () =>34 run<GeneRow>(sql`35 SELECT g.*, (SELECT count(*) FROM civic_evidence_items e WHERE g.id = ANY(e.gene_ids) OR g.symbol = ANY(e.gene_symbols))::int AS evidence_count,36 (SELECT count(*) FROM variants v WHERE v.gene_id = g.id)::int AS variant_count,37 (SELECT count(DISTINCT f.cohort_id) FROM cancer_gene_frequencies f WHERE f.gene_id = g.id OR f.gene_symbol = g.symbol)::int AS cohort_count38 FROM genes g WHERE upper(g.symbol) = upper(${symbol}) LIMIT 1`),39 [] as GeneRow[],40 );41 return rows[0] ?? null;42}4344export async function listGenes(opts: { q: string; cancerOnly: boolean; page: number; pageSize: number }): Promise<{ rows: GeneRow[]; total: number }> {45 const where = sql`${opts.cancerOnly ? sql`g.is_cancer_gene` : sql`true`} AND ${opts.q ? sql`(g.symbol ILIKE ${opts.q + '%'} OR g.name ILIKE ${'%' + opts.q + '%'} OR ${opts.q.toUpperCase()} = ANY(g.alias_symbols) OR ${opts.q.toUpperCase()} = ANY(g.prev_symbols))` : sql`true`}`;46 const total = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM genes g WHERE ${where}`), [{ n: '0' }]);47 const rows = await safe(48 () =>49 run<GeneRow>(sql`50 SELECT g.*, (SELECT count(*) FROM civic_evidence_items e WHERE g.id = ANY(e.gene_ids))::int AS evidence_count,51 (SELECT count(*) FROM variants v WHERE v.gene_id = g.id)::int AS variant_count52 FROM genes g WHERE ${where} ORDER BY g.is_cancer_gene DESC, g.symbol LIMIT ${opts.pageSize} OFFSET ${(opts.page - 1) * opts.pageSize}`),53 [] as GeneRow[],54 );55 return { rows, total: Number(total[0]?.n ?? 0) };56}5758export interface FreqRow {59 id: number;60 cohort_id: string;61 study_id: string;62 cohort_name: string;63 program: string | null;64 data_release: string | null;65 cases_with_ssm: number | null;66 case_count: number | null;67 cancer_id: string | null;68 cancer_slug: string | null;69 cancer_name: string | null;70 cancer_match_type: string | null;71 gene_id: string | null;72 gene_symbol: string;73 alteration_type: string;74 cases_affected: number;75 cases_profiled: number;76 frequency: number;77 rank: number | null;78 provenance_id: number;79 source_slug: string;80 source_name: string;81 updated_at: Date;82}8384export interface CohortSummary {85 cohort_id: string;86 study_id: string;87 cohort_name: string;88 program: string | null;89 data_release: string | null;90 cases_with_ssm: number | null;91 case_count: number | null;92 cancer_id: string | null;93 cancer_slug: string | null;94 cancer_name: string | null;95 cancer_match_type: string | null;96 /** Largest denominator among the cohort's frequency rows — drives the default cohort choice. */97 cases_profiled: number;98 frequency_rows: number;99}100101/** Cohorts mapped to a cancer (and descendants), largest denominator first (§260-261). */102export async function cohortsForCancer(cancerIds: string[]): Promise<CohortSummary[]> {103 if (cancerIds.length === 0) return [];104 const rows = await safe(105 () =>106 run<CohortSummary & { cases_profiled: string; frequency_rows: string }>(sql`107 SELECT gc.id AS cohort_id, gc.study_id, gc.name AS cohort_name, gc.program, gc.data_release, gc.cases_with_ssm, gc.case_count, gc.cancer_id, gc.cancer_match_type,108 c.slug AS cancer_slug, c.canonical_name AS cancer_name,109 coalesce(max(f.cases_profiled), 0) AS cases_profiled, count(f.id) AS frequency_rows110 FROM genomic_cohorts gc LEFT JOIN cancers c ON c.id = gc.cancer_id LEFT JOIN cancer_gene_frequencies f ON f.cohort_id = gc.id111 WHERE gc.cancer_id IN (${sql.join(cancerIds.map((i) => sql`${i}`), sql`, `)})112 GROUP BY gc.id, c.slug, c.canonical_name113 ORDER BY cases_profiled DESC, frequency_rows DESC, gc.name`),114 [],115 );116 return rows.map((r) => ({ ...r, cases_profiled: Number(r.cases_profiled), frequency_rows: Number(r.frequency_rows) }));117}118119/**120 * Gene frequencies for a cancer (and descendants), grouped per cohort by the caller (§260-261).121 * `cohortId` restricts to one cohort (the default UI renders the largest cohort only).122 */123export async function frequenciesForCancer(cancerIds: string[], opts: { cohortId?: string | null; limitPerCohort?: number } = {}): Promise<FreqRow[]> {124 if (cancerIds.length === 0) return [];125 const limitPerCohort = opts.limitPerCohort ?? 30;126 const cohortFilter = opts.cohortId ? sql`AND gc.id = ${opts.cohortId}` : sql``;127 return safe(128 () =>129 run<FreqRow>(sql`130 SELECT * FROM (131 SELECT f.*, gc.study_id, gc.name AS cohort_name, gc.program, gc.cases_with_ssm, gc.case_count, gc.cancer_match_type,132 c.slug AS cancer_slug, c.canonical_name AS cancer_name, s.slug AS source_slug, s.name AS source_name,133 row_number() OVER (PARTITION BY f.cohort_id, f.alteration_type ORDER BY f.frequency DESC) AS rn134 FROM cancer_gene_frequencies f JOIN genomic_cohorts gc ON gc.id = f.cohort_id JOIN sources s ON s.id = gc.source_id135 LEFT JOIN cancers c ON c.id = gc.cancer_id136 WHERE gc.cancer_id IN (${sql.join(cancerIds.map((i) => sql`${i}`), sql`, `)}) ${cohortFilter}137 ) x WHERE rn <= ${limitPerCohort} ORDER BY cohort_name, alteration_type, frequency DESC`),138 [] as FreqRow[],139 );140}141142export async function frequenciesForGene(geneId: string, symbol: string): Promise<FreqRow[]> {143 return safe(144 () =>145 run<FreqRow>(sql`146 SELECT f.*, gc.study_id, gc.name AS cohort_name, gc.program, gc.cases_with_ssm, gc.case_count, gc.cancer_match_type,147 c.slug AS cancer_slug, c.canonical_name AS cancer_name, s.slug AS source_slug, s.name AS source_name148 FROM cancer_gene_frequencies f JOIN genomic_cohorts gc ON gc.id = f.cohort_id JOIN sources s ON s.id = gc.source_id149 LEFT JOIN cancers c ON c.id = gc.cancer_id150 WHERE f.gene_id = ${geneId} OR f.gene_symbol = ${symbol} ORDER BY f.frequency DESC LIMIT 200`),151 [] as FreqRow[],152 );153}154155export interface VariantRow {156 id: string;157 slug: string;158 gene_id: string | null;159 gene_symbol: string | null;160 name: string;161 variant_type: string | null;162 hgvs_g: string | null;163 hgvs_c: string | null;164 hgvs_p: string | null;165 assembly: string | null;166 chromosome: string | null;167 start: number | null;168 end: number | null;169 reference_bases: string | null;170 alternate_bases: string | null;171 coordinates: Array<Record<string, unknown>>;172 clinvar_variation_id: string | null;173 civic_variant_id: number | null;174 dbsnp_ids: string[];175 fusion_partners: string[];176 updated_at: Date;177 evidence_count?: number;178}179180export async function getVariantBySlug(slug: string): Promise<VariantRow | null> {181 const rows = await safe(() => run<VariantRow>(sql`SELECT v.*, (SELECT count(*) FROM civic_evidence_items e WHERE v.id = ANY(e.variant_ids))::int AS evidence_count FROM variants v WHERE v.slug = ${slug} LIMIT 1`), [] as VariantRow[]);182 return rows[0] ?? null;183}184185export const VARIANT_PAGE_SIZE = 50;186187/** Compact variant rows for the gene page list (no coordinates JSON, no HGVS strings). */188export type VariantListRow = Pick<VariantRow, 'id' | 'slug' | 'name' | 'variant_type' | 'evidence_count'>;189190export async function variantsForGene(geneId: string, p: { page: number; pageSize: number } = { page: 1, pageSize: VARIANT_PAGE_SIZE }): Promise<VariantListRow[]> {191 return safe(192 () =>193 run<VariantListRow>(sql`194 SELECT v.id, v.slug, v.name, v.variant_type, (SELECT count(*) FROM civic_evidence_items e WHERE v.id = ANY(e.variant_ids))::int AS evidence_count195 FROM variants v WHERE v.gene_id = ${geneId} ORDER BY evidence_count DESC, v.name LIMIT ${p.pageSize} OFFSET ${(Math.max(1, p.page) - 1) * p.pageSize}`),196 [] as VariantListRow[],197 );198}199export async function variantsForGeneCount(geneId: string): Promise<number> {200 const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM variants v WHERE v.gene_id = ${geneId}`), [{ n: '0' }]);201 return Number(r[0]?.n ?? 0);202}203204export async function variantAliases(variantId: string): Promise<string[]> {205 const rows = await safe(() => run<{ alias: string }>(sql`SELECT alias FROM variant_aliases WHERE variant_id = ${variantId} ORDER BY alias`), []);206 return rows.map((r) => r.alias);207}208209export interface ClinSigRow {210 id: number;211 clinvar_variation_id: string;212 clinical_significance: string;213 review_status: string | null;214 star_rating: number | null;215 last_evaluated: string | null;216 conditions: string[];217 condition_cancer_ids: string[];218 origin_simple: string | null;219 number_submitters: number | null;220 provenance_id: number;221 updated_at: Date;222}223export async function clinicalSignificanceFor(variantId: string): Promise<ClinSigRow[]> {224 return safe(() => run<ClinSigRow>(sql`SELECT * FROM variant_clinical_significance WHERE variant_id = ${variantId} ORDER BY last_evaluated DESC NULLS LAST`), [] as ClinSigRow[]);225}226227export async function geneSymbolsForSitemap(offset: number, limit: number): Promise<Array<{ symbol: string; updated_at: Date }>> {228 return safe(() => run<{ symbol: string; updated_at: Date }>(sql`SELECT symbol, updated_at FROM genes ORDER BY id LIMIT ${limit} OFFSET ${offset}`), []);229}230export async function countGenes(): Promise<number> {231 const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM genes`), [{ n: '0' }]);232 return Number(r[0]?.n ?? 0);233}234