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 { BadRequest } from '../lib/errors.js';7import { boolQuery, pageQuery } from '../lib/pagination.js';8import { resolveCancer } from '../lib/resolve.js';9import { AnyList, num, ok, respond } from '../lib/respond.js';1011const PHASES = ['PHASE1', 'PHASE2', 'PHASE3', 'PHASE4'] as const;12const FORMULA = 'ci-trial-sites-v1';1314/**15 * GET /trials/sites — trial site aggregates for the map (SPEC §11).16 *   level=country (default): from the derived table trial_site_country_counts (recomputed by `cix intel`),17 *     cancer = top-level cancer id/slug (descendants included) or omitted for every oncology trial.18 *   level=city: live aggregate of trial_locations (registrant city/state, mean lat/lng); requires19 *     `cancer` or `recruiting=true` (the unfiltered registry-wide aggregate is too slow per request).20 * Every row is a computed_metric with its formula version; sites are registrant-entered locations.21 * NOT registered in app.ts by this module — the integrator adds `await app.register(trialSiteRoutes)`.22 */23export const trialSiteRoutes: FastifyPluginAsyncZod = async (app) => {24  app.get(25    '/trials/sites',26    {27      schema: {28        tags: ['trials'],29        summary: 'Trial sites per country (precomputed) or per city (live) — the trial map data',30        querystring: z.object({31          cancer: z.string().optional().describe('Top-level cancer id/slug (country level) or any cancer id/slug (city level); descendants included'),32          phase: z.enum(PHASES).optional().describe('PHASE1 includes EARLY_PHASE1; omitted = any phase'),33          recruiting: boolQuery.describe('true = location status RECRUITING (or study RECRUITING when the location has no status)'),34          level: z.enum(['country', 'city']).default('country'),35          ...pageQuery,36        }),37        response: ok(AnyList, true),38      },39    },40    async (req) => {41      const q = req.query;42      const recruiting = q.recruiting ?? false;43      const phase = q.phase ?? null;44      const cancer = q.cancer ? await resolveCancer(app.db, q.cancer) : null;4546      if (q.level === 'country') {47        const rows = await app.db.execute<{ country: string; iso3: string | null; sites: number; trials: number; formula_version: string; updated_at: string; total: string }>(sql`48          SELECT s.country, s.iso3, s.sites, s.trials, s.formula_version, s.updated_at, count(*) OVER() AS total49          FROM trial_site_country_counts s50          WHERE s.cancer_id IS NOT DISTINCT FROM ${cancer?.id ?? null} AND s.phase IS NOT DISTINCT FROM ${phase} AND s.recruiting_only = ${recruiting}51          ORDER BY s.sites DESC, s.country LIMIT ${q.limit} OFFSET ${q.offset}`);52        if (cancer && rows.length === 0 && q.offset === 0) {53          const top = await app.db.execute<{ top_level: boolean }>(sql`SELECT top_level FROM cancers WHERE id = ${cancer.id}`);54          if (top[0] && !top[0].top_level) throw new BadRequest(`Country aggregates are precomputed for top-level cancers only; ${cancer.slug} is not top-level. Use level=city or a top-level ancestor.`);55        }56        const total = rows.length ? num(rows[0]!.total) : 0;57        const data = rows.map((r) => ({58          level: 'country' as const,59          country: r.country,60          iso3: r.iso3,61          sites: num(r.sites),62          trials: num(r.trials),63          cancer: cancer ? { id: cancer.id, slug: cancer.slug } : null,64          phase,65          recruitingOnly: recruiting,66          claim: 'computed_metric',67          formulaVersion: r.formula_version,68          computedAt: r.updated_at,69        }));70        return respond(app, data, data.length ? ['clinicaltrials'] : [], paginate(total, q.limit, q.offset));71      }7273      // level=city — live aggregate.74      if (!cancer && !recruiting) throw new BadRequest('level=city requires a cancer filter or recruiting=true (the registry-wide city aggregate is not served per request).');75      const conds = [sql`l.country IS NOT NULL AND l.country <> '' AND l.lat IS NOT NULL AND l.lng IS NOT NULL AND l.city IS NOT NULL`];76      if (phase) conds.push(phase === 'PHASE1' ? sql`(t.phases && ARRAY['PHASE1','EARLY_PHASE1']::text[])` : sql`${phase} = ANY(t.phases)`);77      if (recruiting) conds.push(sql`coalesce(l.status, t.overall_status) = 'RECRUITING'`);78      if (cancer) {79        const ids = await descendantIds(app.db, cancer.id);80        conds.push(sql`EXISTS (SELECT 1 FROM trial_conditions tc WHERE tc.trial_id = l.trial_id AND tc.cancer_id = ANY(${sql.param(ids)}::text[]))`);81      }82      const rows = await app.db.execute<{ country: string; city: string; state: string | null; lat: number; lng: number; sites: string; trials: string; total: string }>(sql`83        SELECT l.country, l.city, l.state, avg(l.lat)::float8 AS lat, avg(l.lng)::float8 AS lng, count(*) AS sites, count(DISTINCT l.trial_id) AS trials, count(*) OVER() AS total84        FROM trial_locations l JOIN clinical_trials t ON t.id = l.trial_id85        WHERE ${sql.join(conds, sql` AND `)}86        GROUP BY l.country, l.city, l.state87        ORDER BY sites DESC, trials DESC, l.country, l.city LIMIT ${q.limit} OFFSET ${q.offset}`);88      const total = rows.length ? num(rows[0]!.total) : 0;89      const data = rows.map((r) => ({90        level: 'city' as const,91        country: r.country,92        city: r.city,93        state: r.state,94        lat: Number(r.lat),95        lng: Number(r.lng),96        sites: num(r.sites),97        trials: num(r.trials),98        cancer: cancer ? { id: cancer.id, slug: cancer.slug } : null,99        phase,100        recruitingOnly: recruiting,101        claim: 'computed_metric',102        formulaVersion: FORMULA,103        computedAt: new Date().toISOString(),104      }));105      return respond(app, data, data.length ? ['clinicaltrials'] : [], paginate(total, q.limit, q.offset));106    },107  );108};109