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 const ACTIVE_STATUSES = ['RECRUITING', 'NOT_YET_RECRUITING', 'ENROLLING_BY_INVITATION', 'ACTIVE_NOT_RECRUITING'];56export interface TrialRow {7 id: string;8 nct_id: string;9 brief_title: string;10 official_title: string | null;11 acronym: string | null;12 study_type: string | null;13 phases: string[];14 overall_status: string | null;15 why_stopped: string | null;16 start_date: string | null;17 primary_completion_date: string | null;18 completion_date: string | null;19 first_posted_date: string | null;20 last_update_posted_date: string | null;21 results_first_posted_date: string | null;22 has_results: boolean;23 enrollment_count: number | null;24 enrollment_type: string | null;25 lead_sponsor: string | null;26 lead_sponsor_class: string | null;27 collaborators: string[];28 conditions: string[];29 keywords: string[];30 interventions: Array<{ type: string; name: string; description?: string; otherNames?: string[] }>;31 arms: Array<Record<string, unknown>>;32 primary_outcomes: Array<Record<string, unknown>>;33 secondary_outcomes: Array<Record<string, unknown>>;34 eligibility: Record<string, unknown>;35 sex: string | null;36 minimum_age: string | null;37 maximum_age: string | null;38 countries: string[];39 locations_count: number;40 references: Array<{ pmid?: string; type?: string; citation?: string }>;41 brief_summary: string | null;42 is_oncology: boolean;43 source_record_id: number | null;44 ingest_run_id: string | null;45 created_at: Date;46 updated_at: Date;47}4849export interface TrialFilters {50 q: string;51 status: string;52 phase: string;53 country: string;54 cancerIds: string[] | null; // null = no cancer filter55 page: number;56 pageSize: number;57}5859function trialWhere(f: TrialFilters) {60 const parts = [sql`true`];61 if (f.q) parts.push(sql`(t.nct_id ILIKE ${f.q + '%'} OR t.brief_title ILIKE ${'%' + f.q + '%'} OR t.acronym ILIKE ${f.q} OR t.lead_sponsor ILIKE ${'%' + f.q + '%'})`);62 if (f.status === 'active') parts.push(sql`t.overall_status = ANY(${sql.param(ACTIVE_STATUSES)}::text[])`);63 else if (f.status) parts.push(sql`t.overall_status = ${f.status}`);64 if (f.phase) parts.push(sql`${f.phase} = ANY(t.phases)`);65 if (f.country) parts.push(sql`${f.country} = ANY(t.countries)`);66 if (f.cancerIds) parts.push(sql`EXISTS (SELECT 1 FROM trial_conditions tc WHERE tc.trial_id = t.id AND tc.cancer_id IN (${sql.join(f.cancerIds.map((i) => sql`${i}`), sql`, `)}))`);67 return sql.join(parts, sql` AND `);68}6970export async function listTrials(f: TrialFilters): Promise<{ rows: TrialRow[]; total: number }> {71 if (f.cancerIds && f.cancerIds.length === 0) return { rows: [], total: 0 };72 const total = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM clinical_trials t WHERE ${trialWhere(f)}`), [{ n: '0' }]);73 const rows = await safe(74 () => run<TrialRow>(sql`SELECT t.* FROM clinical_trials t WHERE ${trialWhere(f)} ORDER BY t.last_update_posted_date DESC NULLS LAST, t.nct_id LIMIT ${f.pageSize} OFFSET ${(f.page - 1) * f.pageSize}`),75 [] as TrialRow[],76 );77 return { rows, total: Number(total[0]?.n ?? 0) };78}7980export async function trialFacets(cancerIds: string[] | null): Promise<{ statuses: Array<{ k: string; n: number }>; phases: Array<{ k: string; n: number }>; countries: Array<{ k: string; n: number }> }> {81 const scope = cancerIds && cancerIds.length ? sql`WHERE EXISTS (SELECT 1 FROM trial_conditions tc WHERE tc.trial_id = t.id AND tc.cancer_id IN (${sql.join(cancerIds.map((i) => sql`${i}`), sql`, `)}))` : sql``;82 const statuses = await safe(() => run<{ k: string; n: string }>(sql`SELECT overall_status AS k, count(*) AS n FROM clinical_trials t ${scope} GROUP BY 1 ORDER BY n DESC`), [] as Array<{ k: string; n: string }>);83 const phases = await safe(() => run<{ k: string; n: string }>(sql`SELECT p AS k, count(*) AS n FROM clinical_trials t, unnest(t.phases) p ${scope} GROUP BY 1 ORDER BY 1`), [] as Array<{ k: string; n: string }>);84 const countries = await safe(() => run<{ k: string; n: string }>(sql`SELECT c AS k, count(*) AS n FROM clinical_trials t, unnest(t.countries) c ${scope} GROUP BY 1 ORDER BY n DESC LIMIT 60`), [] as Array<{ k: string; n: string }>);85 const num = (a: Array<{ k: string; n: string }>) => a.filter((x) => x.k).map((x) => ({ k: x.k, n: Number(x.n) }));86 return { statuses: num(statuses), phases: num(phases), countries: num(countries) };87}8889export async function getTrialByNct(nct: string): Promise<TrialRow | null> {90 const rows = await safe(() => run<TrialRow>(sql`SELECT * FROM clinical_trials WHERE upper(nct_id) = upper(${nct}) LIMIT 1`), [] as TrialRow[]);91 return rows[0] ?? null;92}9394export interface TrialCondition {95 condition_text: string;96 normalized: string;97 cancer_id: string | null;98 cancer_slug: string | null;99 cancer_name: string | null;100 match_type: string;101 confidence: number | null;102}103export async function trialConditionsFor(trialId: string): Promise<TrialCondition[]> {104 return safe(() => run<TrialCondition>(sql`SELECT tc.condition_text, tc.normalized, tc.cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name, tc.match_type, tc.confidence FROM trial_conditions tc LEFT JOIN cancers c ON c.id = tc.cancer_id WHERE tc.trial_id = ${trialId} ORDER BY tc.condition_text`), [] as TrialCondition[]);105}106107export interface TrialIntervention {108 name: string;109 intervention_type: string | null;110 drug_id: string | null;111 drug_slug: string | null;112 drug_name: string | null;113 match_type: string;114}115export async function trialInterventionsFor(trialId: string): Promise<TrialIntervention[]> {116 return safe(() => run<TrialIntervention>(sql`SELECT ti.name, ti.intervention_type, ti.drug_id, d.slug AS drug_slug, d.name AS drug_name, ti.match_type FROM trial_interventions ti LEFT JOIN drugs d ON d.id = ti.drug_id WHERE ti.trial_id = ${trialId} ORDER BY ti.name`), [] as TrialIntervention[]);117}118119export async function trialLocationsByCountry(trialId: string): Promise<Array<{ country: string; n: number; recruiting: number }>> {120 const rows = await safe(() => run<{ country: string; n: string; recruiting: string }>(sql`SELECT coalesce(country, 'Unknown') AS country, count(*) AS n, count(*) FILTER (WHERE status = 'RECRUITING') AS recruiting FROM trial_locations WHERE trial_id = ${trialId} GROUP BY 1 ORDER BY n DESC`), [] as Array<{ country: string; n: string; recruiting: string }>);121 return rows.map((r) => ({ country: r.country, n: Number(r.n), recruiting: Number(r.recruiting) }));122}123124export const TRIAL_PAGE_SIZE = 50;125126/** Columns the trial tables actually render (the full row carries arms, outcomes, eligibility JSON…). */127export type TrialListRow = Pick<TrialRow, 'id' | 'nct_id' | 'brief_title' | 'acronym' | 'phases' | 'overall_status' | 'enrollment_count' | 'lead_sponsor' | 'lead_sponsor_class' | 'countries' | 'last_update_posted_date' | 'updated_at'>;128const 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`;129130export async function listTrialRows(f: TrialFilters): Promise<{ rows: TrialListRow[]; total: number }> {131 if (f.cancerIds && f.cancerIds.length === 0) return { rows: [], total: 0 };132 const [total, rows] = await Promise.all([133 safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM clinical_trials t WHERE ${trialWhere(f)}`), [{ n: '0' }]),134 safe(() => run<TrialListRow>(sql`SELECT ${LIST_COLUMNS} FROM clinical_trials t WHERE ${trialWhere(f)} ORDER BY t.last_update_posted_date DESC NULLS LAST, t.nct_id LIMIT ${f.pageSize} OFFSET ${(Math.max(1, f.page) - 1) * f.pageSize}`), [] as TrialListRow[]),135 ]);136 return { rows, total: Number(total[0]?.n ?? 0) };137}138139const whereDrug = (drugId: string) => sql`EXISTS (SELECT 1 FROM trial_interventions ti WHERE ti.trial_id = t.id AND ti.drug_id = ${drugId})`;140141export async function trialsForDrug(drugId: string, p: { page: number; pageSize: number } = { page: 1, pageSize: TRIAL_PAGE_SIZE }): Promise<TrialListRow[]> {142 return safe(() => run<TrialListRow>(sql`SELECT ${LIST_COLUMNS} FROM clinical_trials t WHERE ${whereDrug(drugId)} 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}`), [] as TrialListRow[]);143}144export async function trialsForDrugCount(drugId: string): Promise<number> {145 const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM clinical_trials t WHERE ${whereDrug(drugId)}`), [{ n: '0' }]);146 return Number(r[0]?.n ?? 0);147}148149/** Aggregate view for the home "Most active clinical research" module. */150export async function mostActiveResearch(limit = 10): Promise<Array<{ slug: string; canonical_name: string; active_trial_count: number; recruiting_trial_count: number; computed_at: Date }>> {151 return safe(152 () =>153 run<{ slug: string; canonical_name: string; active_trial_count: number; recruiting_trial_count: number; computed_at: Date }>(sql`154 SELECT c.slug, c.canonical_name, ec.active_trial_count, ec.recruiting_trial_count, ec.updated_at AS computed_at FROM entity_counters ec JOIN cancers c ON c.id = ec.entity_id155 WHERE ec.entity_type = 'cancer' AND c.status = 'active' AND c.malignant AND ec.active_trial_count > 0 ORDER BY ec.active_trial_count DESC, c.canonical_name LIMIT ${limit}`),156 [],157 );158}159160export async function mostCuratedEvidence(limit = 10): Promise<Array<{ slug: string; canonical_name: string; evidence_count: number; gene_count: number; computed_at: Date }>> {161 return safe(162 () =>163 run<{ slug: string; canonical_name: string; evidence_count: number; gene_count: number; computed_at: Date }>(sql`164 SELECT c.slug, c.canonical_name, ec.evidence_count, ec.gene_count, ec.updated_at AS computed_at FROM entity_counters ec JOIN cancers c ON c.id = ec.entity_id165 WHERE ec.entity_type = 'cancer' AND c.status = 'active' AND c.malignant AND ec.evidence_count > 0 ORDER BY ec.evidence_count DESC, c.canonical_name LIMIT ${limit}`),166 [],167 );168}169170export async function trialNctForSitemap(offset: number, limit: number): Promise<Array<{ nct_id: string; updated_at: Date }>> {171 return safe(() => run<{ nct_id: string; updated_at: Date }>(sql`SELECT nct_id, updated_at FROM clinical_trials ORDER BY id LIMIT ${limit} OFFSET ${offset}`), []);172}173export async function countTrials(): Promise<number> {174 const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM clinical_trials`), [{ n: '0' }]);175 return Number(r[0]?.n ?? 0);176}177