spb/cancerindex
Public
1import { sql } from 'drizzle-orm';2import type { Database } from '@cancerindex/database';34const ACTIVE_STATUSES = ['RECRUITING', 'NOT_YET_RECRUITING', 'ENROLLING_BY_INVITATION', 'ACTIVE_NOT_RECRUITING'];56/**7 * Recompute entity_counters for every active cancer from canonical relations (CLAUDE.md §287).8 * Trial / evidence / cohort counts aggregate over the NCIt-hierarchy descendants of each cancer9 * (a lung-cancer trial mapped to "Lung Adenocarcinoma" counts for "Lung Cancer" too).10 * Deterministic: a full rebuild in one SQL transaction.11 */12export async function refreshCounters(db: Database): Promise<number> {13 await db.execute(sql`14 CREATE TEMP TABLE IF NOT EXISTS _desc (ancestor varchar(32), descendant varchar(32)) ON COMMIT DROP;15 `);16 return db.transaction(async (tx) => {17 await tx.execute(sql`CREATE TEMP TABLE _desc (ancestor varchar(32), descendant varchar(32)) ON COMMIT DROP`);18 await tx.execute(sql`19 INSERT INTO _desc20 WITH RECURSIVE d AS (21 SELECT id AS ancestor, id AS descendant, 0 AS depth FROM cancers WHERE status = 'active'22 UNION23 SELECT d.ancestor, h.child_id, d.depth + 1 FROM d JOIN cancer_hierarchy h ON h.parent_id = d.descendant24 WHERE d.depth < 1225 )26 SELECT DISTINCT ancestor, descendant FROM d27 `);28 await tx.execute(sql`CREATE INDEX ON _desc (descendant)`);29 await tx.execute(sql`CREATE INDEX ON _desc (ancestor)`);30 const rows = await tx.execute<{ n: string }>(sql`31 WITH trial_map AS (32 SELECT DISTINCT d.ancestor AS cancer_id, t.id AS trial_id, t.overall_status, t.phases, t.study_type33 FROM trial_conditions tc JOIN _desc d ON d.descendant = tc.cancer_id JOIN clinical_trials t ON t.id = tc.trial_id34 WHERE tc.cancer_id IS NOT NULL35 ),36 trials AS (37 SELECT cancer_id,38 count(*) AS trial_count,39 count(*) FILTER (WHERE study_type = 'INTERVENTIONAL' AND overall_status = ANY(${ACTIVE_STATUSES}::text[])) AS active_trial_count,40 count(*) FILTER (WHERE study_type = 'INTERVENTIONAL' AND overall_status = 'RECRUITING') AS recruiting_trial_count,41 count(*) FILTER (WHERE study_type = 'INTERVENTIONAL' AND overall_status = ANY(${ACTIVE_STATUSES}::text[]) AND 'PHASE3' = ANY(phases)) AS phase3_trial_count42 FROM trial_map GROUP BY cancer_id43 ),44 pubs AS (45 SELECT cancer_id,46 max(count) FILTER (WHERE window_key = 'all') AS publication_count,47 max(count) FILTER (WHERE window_key = '5y') AS publication_count_5y,48 max(count) FILTER (WHERE window_key = '12m') AS publication_count_12m49 FROM literature_counts GROUP BY cancer_id50 ),51 evid AS (52 SELECT d.ancestor AS cancer_id, count(DISTINCT e.civic_id) AS evidence_count,53 count(DISTINCT g) FILTER (WHERE g IS NOT NULL) AS civic_gene_count,54 count(DISTINCT v) FILTER (WHERE v IS NOT NULL) AS variant_count,55 count(DISTINCT t) FILTER (WHERE t IS NOT NULL) AS drug_count56 FROM civic_evidence_items e JOIN _desc d ON d.descendant = e.cancer_id57 LEFT JOIN LATERAL unnest(e.gene_ids) g ON true58 LEFT JOIN LATERAL unnest(e.variant_ids) v ON true59 LEFT JOIN LATERAL unnest(e.therapy_ids) t ON true60 WHERE e.status = 'ACCEPTED' GROUP BY d.ancestor61 ),62 cohorts AS (63 SELECT d.ancestor AS cancer_id, count(DISTINCT c.id) AS cohort_count,64 count(DISTINCT f.gene_symbol) FILTER (WHERE f.frequency >= 0.05 AND f.cases_affected >= 20) AS gdc_gene_count65 FROM genomic_cohorts c JOIN _desc d ON d.descendant = c.cancer_id66 LEFT JOIN cancer_gene_frequencies f ON f.cohort_id = c.id67 GROUP BY d.ancestor68 ),69 appr AS (70 SELECT d.ancestor AS cancer_id, count(DISTINCT a.drug_id) AS approved_drug_count71 FROM drug_approvals a JOIN _desc d ON d.descendant = a.cancer_id WHERE a.status IN ('approved','accelerated','conditional') GROUP BY d.ancestor72 ),73 epi AS (74 SELECT cancer_id, count(*) AS n FROM epidemiology_observations GROUP BY cancer_id75 ),76 surv AS (77 SELECT cancer_id, count(*) AS n FROM survival_observations GROUP BY cancer_id78 ),79 tree AS (80 SELECT ancestor AS cancer_id, count(*) - 1 AS descendant_count FROM _desc GROUP BY ancestor81 ),82 kids AS (83 SELECT parent_id AS cancer_id, count(DISTINCT child_id) AS subtype_count FROM cancer_hierarchy GROUP BY parent_id84 ),85 ins AS (86 INSERT INTO entity_counters (entity_type, entity_id, trial_count, active_trial_count, recruiting_trial_count, phase3_trial_count,87 publication_count, publication_count_5y, publication_count_12m, gene_count, variant_count, drug_count, approved_drug_count,88 evidence_count, cohort_count, subtype_count, descendant_count, epidemiology_obs_count, survival_obs_count, completeness, computed_at)89 SELECT 'cancer', c.id,90 COALESCE(t.trial_count,0), COALESCE(t.active_trial_count,0), COALESCE(t.recruiting_trial_count,0), COALESCE(t.phase3_trial_count,0),91 COALESCE(p.publication_count,0), COALESCE(p.publication_count_5y,0), COALESCE(p.publication_count_12m,0),92 GREATEST(COALESCE(e.civic_gene_count,0), COALESCE(g.gdc_gene_count,0)), COALESCE(e.variant_count,0), COALESCE(e.drug_count,0), COALESCE(a.approved_drug_count,0),93 COALESCE(e.evidence_count,0), COALESCE(g.cohort_count,0), COALESCE(k.subtype_count,0), COALESCE(tr.descendant_count,0), COALESCE(ep.n,0), COALESCE(sv.n,0),94 jsonb_build_object(95 'epidemiology', CASE WHEN COALESCE(ep.n,0) > 0 THEN 1 ELSE 0 END,96 'survival', CASE WHEN COALESCE(sv.n,0) > 0 THEN 1 ELSE 0 END,97 'trials', CASE WHEN COALESCE(t.trial_count,0) > 0 THEN 1 ELSE 0 END,98 'literature', CASE WHEN COALESCE(p.publication_count,0) > 0 THEN 1 ELSE 0 END,99 'genomics', CASE WHEN COALESCE(g.cohort_count,0) > 0 THEN 1 ELSE 0 END,100 'evidence', CASE WHEN COALESCE(e.evidence_count,0) > 0 THEN 1 ELSE 0 END,101 'therapies', CASE WHEN COALESCE(a.approved_drug_count,0) > 0 THEN 1 ELSE 0 END102 ), now()103 FROM cancers c104 LEFT JOIN trials t ON t.cancer_id = c.id105 LEFT JOIN pubs p ON p.cancer_id = c.id106 LEFT JOIN evid e ON e.cancer_id = c.id107 LEFT JOIN cohorts g ON g.cancer_id = c.id108 LEFT JOIN appr a ON a.cancer_id = c.id109 LEFT JOIN epi ep ON ep.cancer_id = c.id110 LEFT JOIN surv sv ON sv.cancer_id = c.id111 LEFT JOIN tree tr ON tr.cancer_id = c.id112 LEFT JOIN kids k ON k.cancer_id = c.id113 WHERE c.status = 'active'114 ON CONFLICT (entity_type, entity_id) DO UPDATE SET115 trial_count = EXCLUDED.trial_count, active_trial_count = EXCLUDED.active_trial_count, recruiting_trial_count = EXCLUDED.recruiting_trial_count,116 phase3_trial_count = EXCLUDED.phase3_trial_count, publication_count = EXCLUDED.publication_count, publication_count_5y = EXCLUDED.publication_count_5y,117 publication_count_12m = EXCLUDED.publication_count_12m, gene_count = EXCLUDED.gene_count, variant_count = EXCLUDED.variant_count, drug_count = EXCLUDED.drug_count,118 approved_drug_count = EXCLUDED.approved_drug_count, evidence_count = EXCLUDED.evidence_count, cohort_count = EXCLUDED.cohort_count, subtype_count = EXCLUDED.subtype_count,119 descendant_count = EXCLUDED.descendant_count, epidemiology_obs_count = EXCLUDED.epidemiology_obs_count, survival_obs_count = EXCLUDED.survival_obs_count,120 completeness = EXCLUDED.completeness, computed_at = now()121 RETURNING 1122 )123 SELECT count(*)::text AS n FROM ins124 `);125 // Gene-level counters (cancers linked, evidence items) for gene pages.126 await tx.execute(sql`127 INSERT INTO entity_counters (entity_type, entity_id, evidence_count, variant_count, drug_count, trial_count, computed_at)128 SELECT 'gene', g.id,129 (SELECT count(*) FROM civic_evidence_items e WHERE e.status = 'ACCEPTED' AND g.id = ANY(e.gene_ids)),130 (SELECT count(*) FROM variants v WHERE v.gene_id = g.id),131 (SELECT count(DISTINCT t) FROM civic_evidence_items e, unnest(e.therapy_ids) t WHERE e.status = 'ACCEPTED' AND g.id = ANY(e.gene_ids)),132 0, now()133 FROM genes g134 WHERE EXISTS (SELECT 1 FROM civic_evidence_items e WHERE g.id = ANY(e.gene_ids)) OR EXISTS (SELECT 1 FROM variants v WHERE v.gene_id = g.id) OR EXISTS (SELECT 1 FROM cancer_gene_frequencies f WHERE f.gene_id = g.id)135 ON CONFLICT (entity_type, entity_id) DO UPDATE SET evidence_count = EXCLUDED.evidence_count, variant_count = EXCLUDED.variant_count, drug_count = EXCLUDED.drug_count, computed_at = now()136 `);137 await tx.execute(sql`UPDATE genes g SET is_cancer_gene = EXISTS (SELECT 1 FROM civic_evidence_items e WHERE e.status = 'ACCEPTED' AND g.id = ANY(e.gene_ids)) OR EXISTS (SELECT 1 FROM cancer_gene_frequencies f WHERE f.gene_id = g.id AND f.frequency >= 0.05 AND f.cases_affected >= 20)`);138 return Number(rows[0]?.n ?? 0);139 });140}141