spb/cancerindex

1import { sql } from 'drizzle-orm';2import type { FastifyPluginAsyncZod } from 'fastify-type-provider-zod';3import { z } from 'zod';4import { TtlCache } from '../lib/cache.js';5import { ok, respond } from '../lib/respond.js';67const Stats = z.object({8  cancers: z.number().describe('Active malignant canonical entities'),9  entities: z.number().describe('All active canonical entities (incl. precursor/benign)'),10  topLevel: z.number(),11  subtypes: z.number().describe('Hierarchy edges (child relationships)'),12  genes: z.number(),13  cancerGenes: z.number(),14  variants: z.number(),15  drugs: z.number(),16  trials: z.number(),17  activeTrials: z.number(),18  publications: z.number(),19  evidenceItems: z.number(),20  epidemiologyObservations: z.number(),21  survivalObservations: z.number(),22  countriesWithData: z.number(),23  sources: z.number(),24  activeSources: z.number(),25  rankingSnapshots: z.number(),26  unresolvedLabelsOpen: z.number(),27  lastIngestAt: z.string().nullable(),28  computedAt: z.string(),29});30type Stats = z.infer<typeof Stats>;3132const cache = new TtlCache<Stats>(60_000);3334export const statsRoutes: FastifyPluginAsyncZod = async (app) => {35  app.get('/stats', { schema: { tags: ['stats'], summary: 'Live counts for the homepage ticker (computed from the database, cached 60 s)', response: ok(Stats) } }, async () => {36    const data = await cache.getOrLoad('stats', async () => {37      const rows = await app.db.execute<Record<string, string | null>>(sql`38        SELECT39          (SELECT count(*) FROM cancers WHERE status = 'active' AND malignant) AS cancers,40          (SELECT count(*) FROM cancers WHERE status = 'active') AS entities,41          (SELECT count(*) FROM cancers WHERE status = 'active' AND top_level) AS top_level,42          (SELECT count(DISTINCT child_id) FROM cancer_hierarchy) AS subtypes,43          (SELECT count(*) FROM genes) AS genes,44          (SELECT count(*) FROM genes WHERE is_cancer_gene) AS cancer_genes,45          (SELECT count(*) FROM variants) AS variants,46          (SELECT count(*) FROM drugs) AS drugs,47          (SELECT count(*) FROM clinical_trials) AS trials,48          (SELECT count(*) FROM clinical_trials WHERE overall_status IN ('RECRUITING','NOT_YET_RECRUITING','ENROLLING_BY_INVITATION','ACTIVE_NOT_RECRUITING')) AS active_trials,49          (SELECT count(*) FROM publications) AS publications,50          (SELECT count(*) FROM civic_evidence_items WHERE status = 'ACCEPTED') AS evidence_items,51          (SELECT count(*) FROM epidemiology_observations) AS epidemiology_observations,52          (SELECT count(*) FROM survival_observations) AS survival_observations,53          (SELECT count(DISTINCT o.geography_id) FROM epidemiology_observations o JOIN geographies g ON g.id = o.geography_id WHERE g.kind = 'country') AS countries_with_data,54          (SELECT count(*) FROM sources) AS sources,55          (SELECT count(*) FROM sources WHERE status = 'active') AS active_sources,56          (SELECT count(*) FROM ranking_snapshots WHERE is_current) AS ranking_snapshots,57          (SELECT count(*) FROM unresolved_labels WHERE status = 'open') AS unresolved_labels_open,58          (SELECT max(finished_at)::text FROM ingest_runs WHERE status IN ('succeeded','partial')) AS last_ingest_at`);59      const r = rows[0]!;60      const n = (k: string) => Number(r[k] ?? 0);61      return {62        cancers: n('cancers'),63        entities: n('entities'),64        topLevel: n('top_level'),65        subtypes: n('subtypes'),66        genes: n('genes'),67        cancerGenes: n('cancer_genes'),68        variants: n('variants'),69        drugs: n('drugs'),70        trials: n('trials'),71        activeTrials: n('active_trials'),72        publications: n('publications'),73        evidenceItems: n('evidence_items'),74        epidemiologyObservations: n('epidemiology_observations'),75        survivalObservations: n('survival_observations'),76        countriesWithData: n('countries_with_data'),77        sources: n('sources'),78        activeSources: n('active_sources'),79        rankingSnapshots: n('ranking_snapshots'),80        unresolvedLabelsOpen: n('unresolved_labels_open'),81        lastIngestAt: r.last_ingest_at ? new Date(r.last_ingest_at).toISOString() : null,82        computedAt: new Date().toISOString(),83      };84    });85    const active = await app.db.execute<{ id: string }>(sql`SELECT id FROM sources WHERE status = 'active'`);86    return respond(87      app,88      data,89      active.map((s) => s.id),90    );91  });92};93