spb/cancerindex
Public
TypeScript 97.2%
SQL 1.5%
CSS 0.6%
JavaScript 0.5%
1import { sql, type SQL } from 'drizzle-orm';2import type { FastifyPluginAsyncZod } from 'fastify-type-provider-zod';3import { z } from 'zod';4import { normalizeLabel } from '@cancerindex/shared';5import { descendantIds } from '../lib/descendants.js';6import { paginate } from '../lib/envelope.js';7import { pageQuery } from '../lib/pagination.js';8import { resolveCancer, resolveDrug } from '../lib/resolve.js';9import { AnyList, AnyRecord, camel, num, ok, respond } from '../lib/respond.js';10import { pluck } from '../lib/sources.js';1112const PIPELINE_STAGES = ['phase_not_stated', 'phase1', 'phase2', 'phase3', 'phase4', 'approved', 'withdrawn'] as const;13const STAGE_RANK_SQL = sql.raw(`CASE p.stage WHEN 'approved' THEN 6 WHEN 'phase4' THEN 5 WHEN 'phase3' THEN 4 WHEN 'phase2' THEN 3 WHEN 'phase1' THEN 2 WHEN 'phase_not_stated' THEN 1 WHEN 'withdrawn' THEN 0 ELSE -1 END`);1415/** "2024" → "2024-01-01" (from) / "2024-12-31" (to); full ISO dates pass through. */16function dateBound(v: string | undefined, edge: 'from' | 'to'): string | undefined {17 if (!v) return undefined;18 if (/^\d{4}$/.test(v)) return edge === 'from' ? `${v}-01-01` : `${v}-12-31`;19 return v;20}2122const approvalSelect = (where: SQL, orderLimit: SQL) => sql`23 SELECT a.id, a.drug_id, a.cancer_id, a.biomarker_ids, a.tumor_agnostic, a.jurisdiction, a.authority, a.indication, a.line_of_therapy, a.disease_stage,24 a.approval_type, a.accelerated, a.conditional, a.approval_date, a.withdrawal_date, a.status, a.application_number, a.source_id, a.updated_at,25 a.raw->>'dpdStatus' AS source_status,26 d.slug AS drug_slug, d.name AS drug_name, c.slug AS cancer_slug, c.canonical_name AS cancer_name,27 p.source_url, p.retrieved_at, p.dataset, p.dataset_version, count(*) OVER() AS total28 FROM drug_approvals a JOIN drugs d ON d.id = a.drug_id LEFT JOIN cancers c ON c.id = a.cancer_id LEFT JOIN provenance p ON p.id = a.provenance_id29 WHERE ${where} ${orderLimit}`;3031function shapeApproval(r: Record<string, unknown>): Record<string, unknown> {32 const { total: _t, drug_slug, drug_name, cancer_slug, cancer_name, source_url, retrieved_at, dataset, dataset_version, source_status, ...rest } = r;33 return {34 ...camel(rest),35 sourceStatus: source_status ?? null,36 drug: { id: r.drug_id, slug: drug_slug, name: drug_name },37 cancer: r.cancer_id ? { id: r.cancer_id, slug: cancer_slug, name: cancer_name } : null,38 provenance: { sourceId: r.source_id, url: source_url, retrievedAt: retrieved_at, dataset, datasetVersion: dataset_version, category: 'regulatory_status' },39 };40}4142/**43 * Regulatory routes (SPEC §13-15, §63): jurisdiction-aware, dated approval records (one record = one44 * authority's decision for one application / DIN — never a bare "approved"), a recent feed grouped45 * by month, and the derived drug development pipeline (`drug_pipeline`, formula-versioned).46 */47export const approvalRoutes: FastifyPluginAsyncZod = async (app) => {48 app.get(49 '/approvals',50 {51 schema: {52 tags: ['regulatory'],53 summary: 'Approval records (jurisdiction-aware, dated), filterable by authority, jurisdiction, cancer (with descendants), drug, status, date range, text',54 querystring: z.object({55 authority: z.string().trim().min(1).max(60).optional().describe('e.g. FDA, Health Canada'),56 jurisdiction: z.string().trim().min(2).max(8).optional().describe('e.g. US, CA'),57 cancer: z.string().trim().min(1).optional().describe('CI-CAN id or slug; descendants included'),58 drug: z.string().trim().min(1).optional().describe('CI-DRUG id or slug'),59 status: z.enum(['approved', 'accelerated', 'conditional', 'withdrawn', 'superseded']).optional(),60 from: z.string().regex(/^\d{4}(-\d{2}-\d{2})?$/).optional().describe('approval_date ≥ (YYYY or YYYY-MM-DD)'),61 to: z.string().regex(/^\d{4}(-\d{2}-\d{2})?$/).optional().describe('approval_date ≤ (YYYY or YYYY-MM-DD)'),62 q: z.string().trim().min(1).max(100).optional().describe('drug name/alias or indication text'),63 ...pageQuery,64 }),65 response: ok(AnyList, true),66 },67 },68 async (req) => {69 const q = req.query;70 const conds: SQL[] = [sql`true`];71 if (q.authority) conds.push(sql`lower(a.authority) = lower(${q.authority})`);72 if (q.jurisdiction) conds.push(sql`upper(a.jurisdiction) = upper(${q.jurisdiction})`);73 if (q.status) conds.push(sql`a.status = ${q.status}`);74 const from = dateBound(q.from, 'from');75 const to = dateBound(q.to, 'to');76 if (from) conds.push(sql`a.approval_date >= ${from}`);77 if (to) conds.push(sql`a.approval_date <= ${to}`);78 if (q.cancer) {79 const c = await resolveCancer(app.db, q.cancer);80 const ids = await descendantIds(app.db, c.id);81 conds.push(sql`a.cancer_id = ANY(${sql.param(ids)}::text[])`);82 }83 if (q.drug) {84 const d = await resolveDrug(app.db, q.drug);85 conds.push(sql`a.drug_id = ${d.id}`);86 }87 if (q.q) {88 const norm = normalizeLabel(q.q);89 conds.push(sql`(EXISTS (SELECT 1 FROM drug_aliases al WHERE al.drug_id = a.drug_id AND (al.normalized = ${norm} OR al.normalized LIKE ${norm + '%'})) OR a.indication ILIKE ${'%' + q.q + '%'})`);90 }91 const rows = await app.db.execute<Record<string, unknown> & { total: string }>(approvalSelect(sql.join(conds, sql` AND `), sql`ORDER BY a.approval_date DESC NULLS LAST, a.id DESC LIMIT ${q.limit} OFFSET ${q.offset}`));92 const total = rows.length ? num(rows[0]!.total) : 0;93 return respond(app, rows.map(shapeApproval), pluck(rows, 'source_id'), paginate(total, q.limit, q.offset));94 },95 );9697 app.get(98 '/approvals/recent',99 {100 schema: {101 tags: ['regulatory'],102 summary: 'Latest dated approval records across authorities, grouped by month (data.months)',103 querystring: z.object({ days: z.coerce.number().int().min(1).max(3650).default(90), limit: z.coerce.number().int().min(1).max(500).default(100), authority: z.string().trim().min(1).max(60).optional() }),104 response: ok(AnyRecord),105 },106 },107 async (req) => {108 const { days, limit, authority } = req.query;109 const since = new Date(Date.now() - days * 86_400_000).toISOString().slice(0, 10);110 const conds: SQL[] = [sql`a.approval_date IS NOT NULL`, sql`a.approval_date >= ${since}`, sql`a.approval_date <= to_char(now(), 'YYYY-MM-DD')`];111 if (authority) conds.push(sql`lower(a.authority) = lower(${authority})`);112 const rows = await app.db.execute<Record<string, unknown> & { total: string }>(approvalSelect(sql.join(conds, sql` AND `), sql`ORDER BY a.approval_date DESC, a.id DESC LIMIT ${limit}`));113 const total = rows.length ? num(rows[0]!.total) : 0;114 const months = new Map<string, Record<string, unknown>[]>();115 for (const r of rows) {116 const m = String(r.approval_date).slice(0, 7);117 months.set(m, [...(months.get(m) ?? []), shapeApproval(r)]);118 }119 const data = {120 since,121 days,122 total,123 returned: rows.length,124 months: [...months.entries()].map(([month, approvals]) => ({ month, count: approvals.length, approvals })),125 };126 return respond(app, data, pluck(rows, 'source_id'));127 },128 );129130 app.get(131 '/pipeline',132 {133 schema: {134 tags: ['regulatory'],135 summary: 'Drug development pipeline rows (derived): stage per drug, or per drug × top-level cancer',136 querystring: z.object({137 cancer: z.string().trim().min(1).optional().describe('Top-level cancer (CI-CAN id or slug): drug × cancer rows'),138 stage: z.enum(PIPELINE_STAGES).optional(),139 drug: z.string().trim().min(1).optional().describe('CI-DRUG id or slug'),140 scope: z.enum(['drug', 'cancer']).default('drug').describe('Without `cancer`: drug = across-all-cancers rows (default), cancer = every drug × top-level cancer row'),141 ...pageQuery,142 }),143 response: ok(AnyList, true),144 },145 },146 async (req) => {147 const q = req.query;148 const conds: SQL[] = [];149 if (q.cancer) {150 const c = await resolveCancer(app.db, q.cancer);151 conds.push(sql`p.cancer_id = ${c.id}`);152 } else conds.push(q.scope === 'cancer' ? sql`p.cancer_id IS NOT NULL` : sql`p.cancer_id IS NULL`);153 if (q.stage) conds.push(sql`p.stage = ${q.stage}`);154 if (q.drug) {155 const d = await resolveDrug(app.db, q.drug);156 conds.push(sql`p.drug_id = ${d.id}`);157 }158 const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`159 SELECT p.id, p.drug_id, p.cancer_id, p.stage, p.max_phase, p.active_trials, p.recruiting_trials, p.phase3_trials, p.total_trials, p.approvals, p.jurisdictions,160 p.first_approval_date, p.latest_approval_date, p.first_trial_date, p.formula_version, p.inputs, p.updated_at AS computed_at,161 d.slug AS drug_slug, d.name AS drug_name, c.slug AS cancer_slug, c.canonical_name AS cancer_name, count(*) OVER() AS total162 FROM drug_pipeline p JOIN drugs d ON d.id = p.drug_id LEFT JOIN cancers c ON c.id = p.cancer_id163 WHERE ${sql.join(conds, sql` AND `)}164 ORDER BY ${STAGE_RANK_SQL} DESC, p.active_trials DESC, p.total_trials DESC, d.name LIMIT ${q.limit} OFFSET ${q.offset}`);165 const total = rows.length ? num(rows[0]!.total) : 0;166 const data = rows.map((r) => {167 const { total: _t, drug_slug, drug_name, cancer_slug, cancer_name, ...rest } = r;168 return { ...camel(rest), drug: { id: r.drug_id, slug: drug_slug, name: drug_name }, cancer: r.cancer_id ? { id: r.cancer_id, slug: cancer_slug, name: cancer_name } : null, category: 'computed_metric' };169 });170 return respond(app, data, await pipelineSources(rows.map((r) => r.drug_id as string)), paginate(total, q.limit, q.offset));171 },172 );173174 app.get(175 '/pipeline/summary',176 {177 schema: {178 tags: ['regulatory'],179 summary: 'Drug counts per development stage (derived), globally or for one top-level cancer',180 querystring: z.object({ cancer: z.string().trim().min(1).optional() }),181 response: ok(AnyRecord),182 },183 },184 async (req) => {185 let cancer: { id: string; slug: string } | null = null;186 if (req.query.cancer) cancer = await resolveCancer(app.db, req.query.cancer);187 const where = cancer ? sql`p.cancer_id = ${cancer.id}` : sql`p.cancer_id IS NULL`;188 const rows = await app.db.execute<{ stage: string; drugs: string; active_trials: string; total_trials: string }>(sql`189 SELECT p.stage, count(*) AS drugs, coalesce(sum(p.active_trials), 0) AS active_trials, coalesce(sum(p.total_trials), 0) AS total_trials190 FROM drug_pipeline p WHERE ${where} GROUP BY p.stage`);191 const meta = await app.db.execute<{ formula_version: string | null; computed_at: Date | null; rows: string }>(sql`SELECT max(formula_version) AS formula_version, max(updated_at) AS computed_at, count(*) AS rows FROM drug_pipeline`);192 const byStage = Object.fromEntries(PIPELINE_STAGES.map((s) => [s, { drugs: 0, activeTrials: 0, totalTrials: 0 }]));193 for (const r of rows) byStage[r.stage] = { drugs: num(r.drugs), activeTrials: num(r.active_trials), totalTrials: num(r.total_trials) };194 const data = {195 cancer: cancer ? { id: cancer.id, slug: cancer.slug } : null,196 stages: PIPELINE_STAGES.map((stage) => ({ stage, ...byStage[stage]! })),197 drugs: rows.reduce((s, r) => s + num(r.drugs), 0),198 formulaVersion: meta[0]?.formula_version ?? null,199 computedAt: meta[0]?.computed_at ?? null,200 category: 'computed_metric',201 };202 return respond(app, data, await pipelineSources([]));203 },204 );205206 /** Sources behind derived pipeline rows: the trial registry plus the regulatory sources of the drugs' approvals. */207 async function pipelineSources(drugIds: string[]): Promise<string[]> {208 const regs = drugIds.length209 ? await app.db.execute<{ source_id: string }>(sql`SELECT DISTINCT source_id FROM drug_approvals WHERE drug_id = ANY(${sql.param(drugIds)}::text[])`)210 : await app.db.execute<{ source_id: string }>(sql`SELECT DISTINCT source_id FROM drug_approvals`);211 return ['clinicaltrials', ...pluck(regs, 'source_id')];212 }213};214