spb/cancerindex

1import { sql } from 'drizzle-orm';2import type { FastifyPluginAsyncZod } from 'fastify-type-provider-zod';3import { z } from 'zod';4import { paginate } from '../lib/envelope.js';5import { descendantIds } from '../lib/descendants.js';6import { pageQuery } from '../lib/pagination.js';7import { resolveCancer, resolveTrial } from '../lib/resolve.js';8import { AnyList, AnyRecord, camel, camelRows, num, ok, respond } from '../lib/respond.js';9import { pluck } from '../lib/sources.js';1011export const trialRoutes: FastifyPluginAsyncZod = async (app) => {12  app.get('/trials', { schema: { tags: ['trials'], summary: 'Search clinical trials', querystring: z.object({ q: z.string().trim().min(1).max(200).optional().describe('NCT id, acronym or title words'), status: z.string().optional(), phase: z.string().optional(), cancer: z.string().optional().describe('Cancer id/slug — includes descendants'), country: z.string().optional(), studyType: z.string().optional(), ...pageQuery }), response: ok(AnyList, true) } }, async (req) => {13    const q = req.query;14    const conds = [sql`true`];15    if (q.status) conds.push(sql`t.overall_status = ${q.status.toUpperCase()}`);16    if (q.phase) conds.push(sql`${q.phase.toUpperCase()} = ANY(t.phases)`);17    if (q.studyType) conds.push(sql`t.study_type = ${q.studyType.toUpperCase()}`);18    if (q.country) conds.push(sql`${q.country} = ANY(t.countries)`);19    if (q.q) {20      const raw = q.q.trim();21      if (/^NCT\d+$/i.test(raw)) conds.push(sql`t.nct_id LIKE ${raw.toUpperCase() + '%'}`);22      else conds.push(sql`(upper(coalesce(t.acronym,'')) = ${raw.toUpperCase()} OR to_tsvector('english', t.brief_title || ' ' || coalesce(t.official_title,'')) @@ plainto_tsquery('english', ${raw}))`);23    }24    if (q.cancer) {25      const { id } = await resolveCancer(app.db, q.cancer);26      const ids = await descendantIds(app.db, id);27      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[]))`);28    }29    const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`30      SELECT t.id, t.nct_id, t.brief_title, t.acronym, t.study_type, t.phases, t.overall_status, t.start_date, t.primary_completion_date, t.last_update_posted_date, t.has_results, t.enrollment_count, t.lead_sponsor, t.lead_sponsor_class, t.conditions, t.countries, t.locations_count,31        count(*) OVER() AS total32      FROM clinical_trials t WHERE ${sql.join(conds, sql` AND `)}33      ORDER BY t.last_update_posted_date DESC NULLS LAST, t.nct_id LIMIT ${q.limit} OFFSET ${q.offset}`);34    const total = rows.length ? num(rows[0]!.total) : 0;35    const data = rows.map((r) => {36      const { total: _t, ...rest } = r;37      return camel(rest);38    });39    return respond(app, data, data.length ? ['clinicaltrials'] : [], paginate(total, q.limit, q.offset));40  });4142  app.get('/trials/:nct', { schema: { tags: ['trials'], summary: 'Trial: conditions with cancer mapping (match type), interventions with drug mapping, locations (first 200), linked publications', params: z.object({ nct: z.string().min(1).describe('NCT id or CI-TRIAL-… id') }), response: ok(AnyRecord) } }, async (req) => {43    const { id } = await resolveTrial(app.db, req.params.nct);44    const db = app.db;45    const [trial, conditions, interventions, locations, pubs, source] = await Promise.all([46      db.execute<Record<string, unknown>>(sql`SELECT * FROM clinical_trials WHERE id = ${id}`),47      db.execute<Record<string, unknown>>(sql`SELECT tc.id, tc.condition_text, tc.normalized, tc.cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name, tc.match_type, tc.confidence FROM trial_conditions tc LEFT JOIN cancers c ON c.id = tc.cancer_id WHERE tc.trial_id = ${id} ORDER BY tc.id`),48      db.execute<Record<string, unknown>>(sql`SELECT ti.id, ti.name, ti.intervention_type, ti.drug_id, d.slug AS drug_slug, d.name AS drug_name, ti.match_type FROM trial_interventions ti LEFT JOIN drugs d ON d.id = ti.drug_id WHERE ti.trial_id = ${id} ORDER BY ti.id`),49      db.execute<Record<string, unknown>>(sql`SELECT facility, city, state, zip, country, status, lat, lng FROM trial_locations WHERE trial_id = ${id} ORDER BY country, city LIMIT 200`),50      db.execute<Record<string, unknown>>(sql`51        SELECT p.id, p.pmid, p.doi, p.title, p.journal, p.pub_year, p.retracted, e.method, e.status AS edge_status, e.source_id52        FROM publication_entity_edges e JOIN publications p ON p.id = e.publication_id WHERE e.entity_type = 'trial' AND e.entity_id = ${id} AND e.status <> 'rejected' ORDER BY p.pub_year DESC NULLS LAST LIMIT 100`),53      db.execute<{ source_id: string; retrieved_at: string; raw_path: string | null }>(sql`SELECT sr.source_id, sr.retrieved_at, sr.raw_path FROM clinical_trials t JOIN source_records sr ON sr.id = t.source_record_id WHERE t.id = ${id}`),54    ]);55    const t = camel<Record<string, unknown>>(trial[0]!);56    const data = {57      ...t,58      provenance: source[0] ? { sourceId: source[0].source_id, retrievedAt: source[0].retrieved_at, url: `https://clinicaltrials.gov/study/${t.nctId as string}`, category: 'observed_data' } : { sourceSlug: 'clinicaltrials', url: `https://clinicaltrials.gov/study/${t.nctId as string}` },59      conditionMappings: conditions.map((r) => ({ id: r.id, conditionText: r.condition_text, normalized: r.normalized, cancer: r.cancer_id ? { id: r.cancer_id, slug: r.cancer_slug, name: r.cancer_name } : null, matchType: r.match_type, confidence: r.confidence })),60      interventionMappings: interventions.map((r) => ({ id: r.id, name: r.name, interventionType: r.intervention_type, drug: r.drug_id ? { id: r.drug_id, slug: r.drug_slug, name: r.drug_name } : null, matchType: r.match_type })),61      locations: camelRows(locations),62      locationsShown: locations.length,63      publications: pubs.map((r) => {64        const { method, edge_status, source_id, ...rest } = r;65        return { ...camel(rest), edge: { method, status: edge_status, sourceId: source_id } };66      }),67    };68    return respond(app, data, [source[0]?.source_id ?? 'clinicaltrials', ...pluck(pubs, 'source_id')]);69  });70};71