spb/cancerindex

1import { sql } from 'drizzle-orm';2import type { Database } from '@cancerindex/database';34const ACTIVE_STATUSES = ['RECRUITING', 'NOT_YET_RECRUITING', 'ENROLLING_BY_INVITATION', 'ACTIVE_NOT_RECRUITING'];5// Inline literal array: drizzle expands a JS array parameter into a ($1,$2,…) tuple, which breaks ANY().6const ACTIVE_STATUSES_SQL = `ARRAY[${ACTIVE_STATUSES.map((s) => `'${s}'`).join(',')}]::text[]`;78/**9 * Recompute entity_counters for every active cancer from canonical relations (CLAUDE.md §287).10 * Trial / evidence / cohort counts aggregate over the NCIt-hierarchy descendants of each cancer11 * (a lung-cancer trial mapped to "Lung Adenocarcinoma" counts for "Lung Cancer" too).12 * Deterministic: a full rebuild in one SQL transaction.13 */14export async function refreshCounters(db: Database): Promise<number> {15  return db.transaction(async (tx) => {16    await tx.execute(sql`CREATE TEMP TABLE _desc (ancestor varchar(32), descendant varchar(32)) ON COMMIT DROP`);17    await tx.execute(sql`18      INSERT INTO _desc19      WITH RECURSIVE d AS (20        SELECT id AS ancestor, id AS descendant, 0 AS depth FROM cancers WHERE status = 'active'21        UNION22        SELECT d.ancestor, h.child_id, d.depth + 1 FROM d JOIN cancer_hierarchy h ON h.parent_id = d.descendant23        WHERE d.depth < 1224      )25      SELECT DISTINCT ancestor, descendant FROM d26    `);27    await tx.execute(sql`CREATE INDEX ON _desc (descendant)`);28    await tx.execute(sql`CREATE INDEX ON _desc (ancestor)`);29    const rows = await tx.execute<{ n: string }>(sql`30      WITH trial_map AS (31        SELECT DISTINCT d.ancestor AS cancer_id, t.id AS trial_id, t.overall_status, t.phases, t.study_type32        FROM trial_conditions tc JOIN _desc d ON d.descendant = tc.cancer_id JOIN clinical_trials t ON t.id = tc.trial_id33        WHERE tc.cancer_id IS NOT NULL34      ),35      trials AS (36        SELECT cancer_id,37          count(*) AS trial_count,38          count(*) FILTER (WHERE study_type = 'INTERVENTIONAL' AND overall_status = ANY(${sql.raw(ACTIVE_STATUSES_SQL)})) AS active_trial_count,39          count(*) FILTER (WHERE study_type = 'INTERVENTIONAL' AND overall_status = 'RECRUITING') AS recruiting_trial_count,40          count(*) FILTER (WHERE study_type = 'INTERVENTIONAL' AND overall_status = ANY(${sql.raw(ACTIVE_STATUSES_SQL)}) AND 'PHASE3' = ANY(phases)) AS phase3_trial_count41        FROM trial_map GROUP BY cancer_id42      ),43      pubs AS (44        SELECT cancer_id,45          max(count) FILTER (WHERE window_key = 'all') AS publication_count,46          max(count) FILTER (WHERE window_key = '5y') AS publication_count_5y,47          max(count) FILTER (WHERE window_key = '12m') AS publication_count_12m48        FROM literature_counts GROUP BY cancer_id49      ),50      evid AS (51        SELECT d.ancestor AS cancer_id, count(DISTINCT e.civic_id) AS evidence_count,52          count(DISTINCT g) FILTER (WHERE g IS NOT NULL) AS civic_gene_count,53          count(DISTINCT v) FILTER (WHERE v IS NOT NULL) AS variant_count,54          count(DISTINCT t) FILTER (WHERE t IS NOT NULL) AS drug_count55        FROM civic_evidence_items e JOIN _desc d ON d.descendant = e.cancer_id56        LEFT JOIN LATERAL unnest(e.gene_ids) g ON true57        LEFT JOIN LATERAL unnest(e.variant_ids) v ON true58        LEFT JOIN LATERAL unnest(e.therapy_ids) t ON true59        WHERE e.status = 'ACCEPTED' GROUP BY d.ancestor60      ),61      cohorts AS (62        SELECT d.ancestor AS cancer_id, count(DISTINCT c.id) AS cohort_count,63          count(DISTINCT f.gene_symbol) FILTER (WHERE f.frequency >= 0.05 AND f.cases_affected >= 20) AS gdc_gene_count64        FROM genomic_cohorts c JOIN _desc d ON d.descendant = c.cancer_id65        LEFT JOIN cancer_gene_frequencies f ON f.cohort_id = c.id66        GROUP BY d.ancestor67      ),68      appr AS (69        SELECT d.ancestor AS cancer_id, count(DISTINCT a.drug_id) AS approved_drug_count70        FROM drug_approvals a JOIN _desc d ON d.descendant = a.cancer_id WHERE a.status IN ('approved','accelerated','conditional') GROUP BY d.ancestor71      ),72      epi AS (73        SELECT cancer_id, count(*) AS n FROM epidemiology_observations GROUP BY cancer_id74      ),75      surv AS (76        SELECT cancer_id, count(*) AS n FROM survival_observations GROUP BY cancer_id77      ),78      tree AS (79        SELECT ancestor AS cancer_id, count(*) - 1 AS descendant_count FROM _desc GROUP BY ancestor80      ),81      kids AS (82        SELECT parent_id AS cancer_id, count(DISTINCT child_id) AS subtype_count FROM cancer_hierarchy GROUP BY parent_id83      ),84      ins AS (85        INSERT INTO entity_counters (entity_type, entity_id, trial_count, active_trial_count, recruiting_trial_count, phase3_trial_count,86          publication_count, publication_count_5y, publication_count_12m, gene_count, variant_count, drug_count, approved_drug_count,87          evidence_count, cohort_count, subtype_count, descendant_count, epidemiology_obs_count, survival_obs_count, completeness, updated_at)88        SELECT 'cancer', c.id,89          COALESCE(t.trial_count,0), COALESCE(t.active_trial_count,0), COALESCE(t.recruiting_trial_count,0), COALESCE(t.phase3_trial_count,0),90          COALESCE(p.publication_count,0), COALESCE(p.publication_count_5y,0), COALESCE(p.publication_count_12m,0),91          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),92          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),93          jsonb_build_object(94            'epidemiology', CASE WHEN COALESCE(ep.n,0) > 0 THEN 1 ELSE 0 END,95            'survival', CASE WHEN COALESCE(sv.n,0) > 0 THEN 1 ELSE 0 END,96            'trials', CASE WHEN COALESCE(t.trial_count,0) > 0 THEN 1 ELSE 0 END,97            'literature', CASE WHEN COALESCE(p.publication_count,0) > 0 THEN 1 ELSE 0 END,98            'genomics', CASE WHEN COALESCE(g.cohort_count,0) > 0 THEN 1 ELSE 0 END,99            'evidence', CASE WHEN COALESCE(e.evidence_count,0) > 0 THEN 1 ELSE 0 END,100            'therapies', CASE WHEN COALESCE(a.approved_drug_count,0) > 0 THEN 1 ELSE 0 END101          ), now()102        FROM cancers c103        LEFT JOIN trials t ON t.cancer_id = c.id104        LEFT JOIN pubs p ON p.cancer_id = c.id105        LEFT JOIN evid e ON e.cancer_id = c.id106        LEFT JOIN cohorts g ON g.cancer_id = c.id107        LEFT JOIN appr a ON a.cancer_id = c.id108        LEFT JOIN epi ep ON ep.cancer_id = c.id109        LEFT JOIN surv sv ON sv.cancer_id = c.id110        LEFT JOIN tree tr ON tr.cancer_id = c.id111        LEFT JOIN kids k ON k.cancer_id = c.id112        WHERE c.status = 'active'113        ON CONFLICT (entity_type, entity_id) DO UPDATE SET114          trial_count = EXCLUDED.trial_count, active_trial_count = EXCLUDED.active_trial_count, recruiting_trial_count = EXCLUDED.recruiting_trial_count,115          phase3_trial_count = EXCLUDED.phase3_trial_count, publication_count = EXCLUDED.publication_count, publication_count_5y = EXCLUDED.publication_count_5y,116          publication_count_12m = EXCLUDED.publication_count_12m, gene_count = EXCLUDED.gene_count, variant_count = EXCLUDED.variant_count, drug_count = EXCLUDED.drug_count,117          approved_drug_count = EXCLUDED.approved_drug_count, evidence_count = EXCLUDED.evidence_count, cohort_count = EXCLUDED.cohort_count, subtype_count = EXCLUDED.subtype_count,118          descendant_count = EXCLUDED.descendant_count, epidemiology_obs_count = EXCLUDED.epidemiology_obs_count, survival_obs_count = EXCLUDED.survival_obs_count,119          completeness = EXCLUDED.completeness, updated_at = now()120        RETURNING 1121      )122      SELECT count(*)::text AS n FROM ins123    `);124    // Gene-level counters (cancers linked, evidence items) for gene pages.125    // `e.gene_ids @> ARRAY[g.id]::text[]` ≡ `g.id = ANY(e.gene_ids)` but uses the GIN index126    // civic_evidence_gene_ids_gin (migrate.ts); the ANY form forced one sequential scan per gene.127    await tx.execute(sql`128      INSERT INTO entity_counters (entity_type, entity_id, evidence_count, variant_count, drug_count, trial_count, updated_at)129      SELECT 'gene', g.id,130        (SELECT count(*) FROM civic_evidence_items e WHERE e.status = 'ACCEPTED' AND e.gene_ids @> ARRAY[g.id]::text[]),131        (SELECT count(*) FROM variants v WHERE v.gene_id = g.id),132        (SELECT count(DISTINCT t) FROM civic_evidence_items e, unnest(e.therapy_ids) t WHERE e.status = 'ACCEPTED' AND e.gene_ids @> ARRAY[g.id]::text[]),133        0, now()134      FROM genes g135      WHERE EXISTS (SELECT 1 FROM civic_evidence_items e WHERE e.gene_ids @> ARRAY[g.id]::text[]) 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)136      ON CONFLICT (entity_type, entity_id) DO UPDATE SET evidence_count = EXCLUDED.evidence_count, variant_count = EXCLUDED.variant_count, drug_count = EXCLUDED.drug_count, updated_at = now()137    `);138    await tx.execute(sql`UPDATE genes g SET is_cancer_gene = EXISTS (SELECT 1 FROM civic_evidence_items e WHERE e.status = 'ACCEPTED' AND e.gene_ids @> ARRAY[g.id]::text[]) 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)`);139    return Number(rows[0]?.n ?? 0);140  });141}142