spb/cancerindex

1import { sql } from 'drizzle-orm';2import type { FastifyPluginAsyncZod } from 'fastify-type-provider-zod';3import { z } from 'zod';4import { STOP_REASON_CATEGORIES, STOP_REASON_RULES, STOP_REASON_RULES_VERSION, TRIAL_INTELLIGENCE_FORMULA_VERSION, TRIAL_INTEL_THRESHOLDS, classifyStopReason, type StopReasonCategory } from '@cancerindex/ranking';5import { paginate } from '../lib/envelope.js';6import { descendantIds } from '../lib/descendants.js';7import { pageQuery } from '../lib/pagination.js';8import { resolveCancer } from '../lib/resolve.js';9import { AnyList, AnyRecord, camel, num, ok, respond } from '../lib/respond.js';1011/** Sortable columns of GET /trials/intelligence (whitelist → physical column). */12const SORTABLE = {13  active: 'ti.active_trials',14  total: 'ti.total_trials',15  recruiting: 'ti.recruiting_trials',16  phase3Active: 'ti.phase3_active',17  phase3Recruiting: 'ti.phase3_recruiting',18  growth: 'ti.trial_growth_yoy',19  new12m: 'ti.new_trials_12m',20  avgEnrollment: 'ti.avg_enrollment',21  medianEnrollment: 'ti.median_enrollment',22  industryShare: 'ti.industry_share',23  sponsorHhi: 'ti.sponsor_hhi',24  distinctSponsors: 'ti.distinct_sponsors',25  distinctCountries: 'ti.distinct_countries',26  usShare: 'ti.us_share',27  countryHhi: 'ti.country_hhi',28  terminationShare: 'ti.termination_share',29  trialsPer1000Deaths: 'ti.trials_per_1000_deaths',30  trialsPer100kCases: 'ti.trials_per_100k_cases',31  completed: 'ti.completed_trials',32  terminated: 'ti.terminated_trials',33  withResults: 'ti.with_results',34  name: 'c.canonical_name',35} as const;36type SortKey = keyof typeof SORTABLE;37const sortKeys = Object.keys(SORTABLE) as [SortKey, ...SortKey[]];3839const INTEL_COLUMNS = sql`ti.cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name, c.top_level, ti.entity_level,40  ti.total_trials, ti.active_trials, ti.recruiting_trials, ti.phase1_active, ti.phase2_active, ti.phase3_active, ti.phase3_recruiting, ti.phase4_active,41  ti.completed_trials, ti.terminated_trials, ti.withdrawn_trials, ti.suspended_trials, ti.with_results,42  ti.new_trials_12m, ti.new_trials_prior_12m, ti.trial_growth_yoy, ti.avg_enrollment, ti.median_enrollment, ti.total_enrollment_active,43  ti.distinct_sponsors, ti.industry_share, ti.sponsor_hhi, ti.top_sponsor, ti.top_sponsor_share,44  ti.distinct_countries, ti.us_share, ti.top_country, ti.top_country_share, ti.country_hhi,45  ti.termination_share, ti.why_stopped_breakdown, ti.trials_per_1000_deaths, ti.trials_per_100k_cases, ti.burden_geography, ti.burden_year, ti.burden_source_id,46  ti.formula_version, ti.inputs, ti.updated_at AS computed_at`;4748const STOPPED_STATUSES = ['TERMINATED', 'WITHDRAWN', 'SUSPENDED'];4950/**51 * Clinical-trial intelligence (SPEC §10): per-cancer derived metrics with formula version and inputs,52 * and registrant-reported stop reasons classified by explicit keyword rules (never inferred).53 * `GET /trials/sites` (trial map) is added by the Trial Map work package.54 */55export const intelligenceRoutes: FastifyPluginAsyncZod = async (app) => {56  app.get(57    '/trials/intelligence',58    {59      schema: {60        tags: ['trials'],61        summary: 'Trial intelligence per cancer: counts, growth, enrollment, sponsor and country concentration, termination share, burden-normalized intensity (computed, formula-versioned)',62        querystring: z.object({63          level: z.enum(['top', 'all']).default('top').describe('top = mutually exclusive top-level cancers; all = every malignant entity with ≥ 1 mapped trial'),64          sort: z.enum(sortKeys).default('active'),65          order: z.enum(['asc', 'desc']).default('desc'),66          minActive: z.coerce.number().int().min(0).optional().describe('Only rows with at least this many active interventional studies'),67          ...pageQuery,68        }),69        response: ok(AnyList, true),70      },71    },72    async (req) => {73      const q = req.query;74      const conds = [sql`ti.entity_level = ${q.level}`, sql`c.status = 'active'`];75      if (q.minActive != null) conds.push(sql`ti.active_trials >= ${q.minActive}`);76      const col = SORTABLE[q.sort];77      const dir = q.order === 'asc' ? sql.raw('ASC NULLS LAST') : sql.raw('DESC NULLS LAST');78      const rows = await app.db.execute<Record<string, unknown> & { total: string; burden_source_id: string | null }>(sql`79        SELECT ${INTEL_COLUMNS}, count(*) OVER() AS total80        FROM trial_intelligence ti JOIN cancers c ON c.id = ti.cancer_id81        WHERE ${sql.join(conds, sql` AND `)}82        ORDER BY ${sql.raw(col)} ${dir}, c.canonical_name ASC LIMIT ${q.limit} OFFSET ${q.offset}`);83      const total = rows.length ? num(rows[0]!.total) : 0;84      const data = rows.map((r) => {85        const { total: _t, ...rest } = r;86        return camel(rest);87      });88      const burdenSources = new Set(rows.map((r) => r.burden_source_id).filter((s): s is string => !!s));89      return respond(app, data, data.length ? ['clinicaltrials', ...burdenSources] : [], paginate(total, q.limit, q.offset));90    },91  );9293  app.get(94    '/trials/intelligence/:cancer',95    {96      schema: {97        tags: ['trials'],98        summary: 'Trial intelligence for one cancer (both entity levels when present) with the registrant-reported stop-reason breakdown and the classification rules',99        params: z.object({ cancer: z.string().min(1).describe('CI-CAN-… id or slug') }),100        response: ok(AnyRecord),101      },102    },103    async (req) => {104      const { id, slug } = await resolveCancer(app.db, req.params.cancer);105      const rows = await app.db.execute<Record<string, unknown> & { entity_level: string; burden_source_id: string | null; why_stopped_breakdown: Record<string, number> }>(sql`106        SELECT ${INTEL_COLUMNS} FROM trial_intelligence ti JOIN cancers c ON c.id = ti.cancer_id WHERE ti.cancer_id = ${id} ORDER BY ti.entity_level`);107      const levels: Record<string, unknown> = {};108      for (const r of rows) levels[r.entity_level] = camel(r);109      const primary = rows.find((r) => r.entity_level === 'all') ?? rows[0];110      const data = {111        cancer: { id, slug, name: (primary?.cancer_name as string | undefined) ?? null },112        available: rows.length > 0,113        formulaVersion: TRIAL_INTELLIGENCE_FORMULA_VERSION,114        levels,115        whyStoppedBreakdown: primary?.why_stopped_breakdown ?? null,116        stopReasonRules: { version: STOP_REASON_RULES_VERSION, categories: STOP_REASON_CATEGORIES, rules: STOP_REASON_RULES.map((r) => ({ category: r.category, keywords: r.keywords })), note: 'Reasons are as posted by the registrant on ClinicalTrials.gov; a category is assigned only when an explicit keyword matches, otherwise other_stated / not_stated.' },117        thresholds: TRIAL_INTEL_THRESHOLDS,118      };119      const burdenSources = rows.map((r) => r.burden_source_id).filter((s): s is string => !!s);120      return respond(app, data, rows.length ? ['clinicaltrials', ...burdenSources] : []);121    },122  );123124  app.get(125    '/trials/terminated',126    {127      schema: {128        tags: ['trials'],129        summary: 'Terminated, withdrawn and suspended studies with the registrant-reported reason (raw) and its keyword-rule category; breakdown over the filtered set',130        querystring: z.object({131          cancer: z.string().optional().describe('Cancer id/slug — includes descendants'),132          reason: z.enum(STOP_REASON_CATEGORIES).optional().describe('Stop-reason category (keyword rules)'),133          status: z.enum(['TERMINATED', 'WITHDRAWN', 'SUSPENDED']).optional(),134          since: z.coerce.number().int().min(1990).max(2100).optional().describe('First posted in this year or later'),135          studyType: z.string().optional().describe('INTERVENTIONAL | OBSERVATIONAL | EXPANDED_ACCESS'),136          ...pageQuery,137        }),138        response: ok(AnyRecord, true),139      },140    },141    async (req) => {142      const q = req.query;143      const conds = [sql`t.overall_status = ANY(${sql.param(STOPPED_STATUSES)}::text[])`];144      if (q.status) conds.push(sql`t.overall_status = ${q.status}`);145      if (q.since) conds.push(sql`t.first_posted_date >= ${`${q.since}-01-01`}`);146      if (q.studyType) conds.push(sql`t.study_type = ${q.studyType.toUpperCase()}`);147      let cancer: { id: string; slug: string } | null = null;148      if (q.cancer) {149        cancer = await resolveCancer(app.db, q.cancer);150        const ids = await descendantIds(app.db, cancer.id);151        conds.push(sql`EXISTS (SELECT 1 FROM trial_conditions tc WHERE tc.trial_id = t.id AND tc.cancer_id = ANY(${sql.param(ids)}::text[]))`);152      }153      // 1) light pass over the whole filtered set: classify every reason (the category is not stored), build the breakdown.154      const light = await app.db.execute<{ id: string; why_stopped: string | null; last_update_posted_date: string | null; nct_id: string }>(sql`155        SELECT t.id, t.why_stopped, t.last_update_posted_date, t.nct_id FROM clinical_trials t WHERE ${sql.join(conds, sql` AND `)}156        ORDER BY t.last_update_posted_date DESC NULLS LAST, t.nct_id`);157      const breakdown = Object.fromEntries(STOP_REASON_CATEGORIES.map((c) => [c, 0])) as Record<StopReasonCategory, number>;158      const classified = light.map((r) => {159        const c = classifyStopReason(r.why_stopped);160        breakdown[c.category] += 1;161        return { id: r.id, category: c.category, matched: c.matched };162      });163      const filtered = q.reason ? classified.filter((r) => r.category === q.reason) : classified;164      const page = filtered.slice(q.offset, q.offset + q.limit);165      const byId = new Map(page.map((p) => [p.id, p]));166      // 2) full columns for the page only.167      const rows = page.length168        ? await app.db.execute<Record<string, unknown> & { id: string }>(sql`169            SELECT t.id, t.nct_id, t.brief_title, t.acronym, t.study_type, t.phases, t.overall_status, t.why_stopped, t.first_posted_date, t.last_update_posted_date, t.enrollment_count, t.lead_sponsor, t.lead_sponsor_class, t.countries170            FROM clinical_trials t WHERE t.id = ANY(${sql.param(page.map((p) => p.id))}::text[])`)171        : [];172      const order = new Map(page.map((p, i) => [p.id, i]));173      const trials = rows174        .sort((a, b) => (order.get(a.id) ?? 0) - (order.get(b.id) ?? 0))175        .map((r) => {176          const c = byId.get(r.id)!;177          return { ...camel(r), reasonCategory: c.category, reasonMatches: c.matched };178        });179      const data = {180        filters: { cancer: cancer ? { id: cancer.id, slug: cancer.slug, includesDescendants: true } : null, reason: q.reason ?? null, status: q.status ?? null, since: q.since ?? null, studyType: q.studyType?.toUpperCase() ?? null },181        total: light.length,182        breakdown,183        rules: { version: STOP_REASON_RULES_VERSION, categories: STOP_REASON_CATEGORIES, rules: STOP_REASON_RULES.map((r) => ({ category: r.category, keywords: r.keywords })), note: 'Reasons are registrant-reported free text; categories come from explicit keyword matches only and are never inferred.' },184        trials,185      };186      return respond(app, data, light.length ? ['clinicaltrials'] : [], paginate(filtered.length, q.limit, q.offset));187    },188  );189};190