spb/cancerindex
Public
TypeScript 97.2%
SQL 1.5%
CSS 0.6%
JavaScript 0.5%
1import 'server-only';2import { run, sql, safe } from '@/lib/db';3import type { ComparableObs } from '@/lib/explorer-series';4import { SEX_ANY } from '@/lib/explorer-params';56/**7 * Read helpers of the Data explorer (/explore, CSV export, home module). Everything is read from8 * `epidemiology_observations` joined to its cancer, geography, source and provenance rows; nothing is9 * estimated, summed across sites or extrapolated. Options (metrics, geographies, sexes, age groups,10 * years, sources) are the distinct values actually present, so the UI never advertises data it lacks.11 */1213export interface MetricOption {14 metric: string;15 unit: string;16 n: number;17 n_cancers: number;18 year_min: number;19 year_max: number;20}2122export interface GeographyOption {23 id: string;24 slug: string;25 name: string;26 iso3: string | null;27 kind: string;28 n: number;29 year_min: number;30 year_max: number;31}3233export interface SourceOption {34 id: string;35 slug: string;36 name: string;37 license: string | null;38 license_status: string | null;39 homepage: string | null;40 attribution: string | null;41 n: number;42 retrieved_at: Date | string | null; // latest provenance retrieval behind its observations43}4445export interface ExplorerOptions {46 metrics: MetricOption[];47 geographies: GeographyOption[];48 sexes: string[];49 age_groups: string[];50 year_min: number | null;51 year_max: number | null;52 sources: SourceOption[];53 n_obs: number;54}5556export async function explorerOptions(): Promise<ExplorerOptions> {57 const [metrics, geographies, sexes, ages, years, sources] = await Promise.all([58 safe(59 () =>60 run<MetricOption>(sql`61 SELECT metric, min(unit) AS unit, count(*)::int AS n, count(DISTINCT cancer_id)::int AS n_cancers, min(year)::int AS year_min, max(coalesce(year_end, year))::int AS year_max62 FROM epidemiology_observations GROUP BY metric ORDER BY metric`),63 [] as MetricOption[],64 ),65 safe(66 () =>67 run<GeographyOption>(sql`68 SELECT g.id, g.slug, g.name, g.iso3, g.kind, a.n::int AS n, a.year_min::int AS year_min, a.year_max::int AS year_max69 FROM geographies g JOIN (SELECT geography_id, count(*) AS n, min(year) AS year_min, max(coalesce(year_end, year)) AS year_max FROM epidemiology_observations GROUP BY geography_id) a ON a.geography_id = g.id70 ORDER BY (g.kind = 'world') DESC, (g.kind = 'country') DESC, g.name`),71 [] as GeographyOption[],72 ),73 safe(() => run<{ sex: string }>(sql`SELECT sex FROM epidemiology_observations GROUP BY sex ORDER BY (sex = 'all') DESC, sex`), [] as Array<{ sex: string }>),74 safe(() => run<{ age_group: string }>(sql`SELECT age_group FROM epidemiology_observations GROUP BY age_group ORDER BY (age_group = 'all') DESC, age_group`), [] as Array<{ age_group: string }>),75 safe(() => run<{ y0: number | null; y1: number | null }>(sql`SELECT min(year)::int AS y0, max(coalesce(year_end, year))::int AS y1 FROM epidemiology_observations`), [] as Array<{ y0: number | null; y1: number | null }>),76 safe(77 () =>78 run<SourceOption>(sql`79 SELECT s.id, s.slug, s.name, s.license, s.license_status, s.homepage, s.attribution, a.n::int AS n,80 (SELECT max(p.retrieved_at) FROM provenance p WHERE p.id IN (SELECT DISTINCT o2.provenance_id FROM epidemiology_observations o2 WHERE o2.source_id = s.id)) AS retrieved_at81 FROM sources s JOIN (SELECT source_id, count(*) AS n FROM epidemiology_observations GROUP BY source_id) a ON a.source_id = s.id82 ORDER BY s.slug`),83 [] as SourceOption[],84 ),85 ]);86 return {87 metrics,88 geographies,89 sexes: sexes.map((r) => r.sex),90 age_groups: ages.map((r) => r.age_group),91 year_min: years[0]?.y0 ?? null,92 year_max: years[0]?.y1 ?? null,93 sources,94 n_obs: metrics.reduce((n, m) => n + m.n, 0),95 };96}9798export interface GeographyRef {99 id: string;100 slug: string;101 name: string;102 iso3: string | null;103 kind: string;104}105106/** Geography by slug or ISO3 (case-insensitive). */107export async function resolveGeographyRef(ref: string): Promise<GeographyRef | null> {108 const r = ref.trim();109 if (!r) return null;110 const rows = await safe(111 () => run<GeographyRef>(sql`SELECT id, slug, name, iso3, kind FROM geographies WHERE slug = ${r.toLowerCase()} OR upper(iso3) = ${r.toUpperCase()} OR id = ${r} ORDER BY (slug = ${r.toLowerCase()}) DESC LIMIT 1`),112 [] as GeographyRef[],113 );114 return rows[0] ?? null;115}116117export interface CancerRef {118 id: string;119 slug: string;120 canonical_name: string;121 top_level: boolean;122 status: string;123}124125/** Cancers by slug or CI-CAN id, in the order requested; unknown references are dropped (reported by the caller). */126export async function resolveCancerRefs(refs: readonly string[]): Promise<CancerRef[]> {127 if (refs.length === 0) return [];128 const rows = await safe(129 () =>130 run<CancerRef>(sql`131 SELECT c.id, c.slug, c.canonical_name, c.top_level, c.status FROM cancers c132 WHERE c.slug = ANY(${sql.param(refs.map((r) => r.toLowerCase()))}::text[]) OR c.id = ANY(${sql.param(refs)}::text[])`),133 [] as CancerRef[],134 );135 const bySlug = new Map(rows.map((r) => [r.slug, r]));136 const byId = new Map(rows.map((r) => [r.id, r]));137 const out: CancerRef[] = [];138 for (const r of refs) {139 const hit = bySlug.get(r.toLowerCase()) ?? byId.get(r);140 if (hit && !out.some((o) => o.id === hit.id)) out.push(hit);141 }142 return out;143}144145export interface CancerChoice {146 id: string;147 slug: string;148 canonical_name: string;149 n_obs: number; // observations for the selected metric × geography (0 = no data for this selection)150}151152/** Top-level cancers (checkbox list), with their observation count for a metric × geography so the form can flag gaps. */153export async function topLevelCancerChoices(metric: string, geographyId: string | null): Promise<CancerChoice[]> {154 return safe(155 () =>156 run<CancerChoice>(sql`157 SELECT c.id, c.slug, c.canonical_name,158 (SELECT count(*) FROM epidemiology_observations o WHERE o.cancer_id = c.id AND o.metric = ${metric} AND (${geographyId}::text IS NULL OR o.geography_id = ${geographyId}))::int AS n_obs159 FROM cancers c WHERE c.top_level AND c.status = 'active' ORDER BY c.canonical_name`),160 [] as CancerChoice[],161 );162}163164export interface TopByLatest {165 year: number | null;166 cancers: Array<{ id: string; slug: string; canonical_name: string; value: number; unit: string; source_slug: string }>;167}168169/**170 * Default cancer selection: the N top-level cancers with the highest value in the latest year of the171 * metric for this geography/sex/age. One row per cancer (when two sources publish the same year the172 * larger observation is kept — the selection is a convenience, every value is then shown per source).173 */174export async function topCancersByLatest(metric: string, geographyId: string, sex: string, age: string, n = 5): Promise<TopByLatest> {175 const sexCond = sex === SEX_ANY ? sql`true` : sql`o.sex = ${sex}`;176 const rows = await safe(177 () =>178 run<{ id: string; slug: string; canonical_name: string; value: number; unit: string; source_slug: string; year: number }>(sql`179 WITH latest AS (180 SELECT max(o.year) AS year FROM epidemiology_observations o JOIN cancers c ON c.id = o.cancer_id181 WHERE o.metric = ${metric} AND o.geography_id = ${geographyId} AND ${sexCond} AND o.age_group = ${age} AND c.top_level AND c.status = 'active'182 )183 SELECT DISTINCT ON (c.id) c.id, c.slug, c.canonical_name, o.value, o.unit, s.slug AS source_slug, o.year184 FROM epidemiology_observations o JOIN cancers c ON c.id = o.cancer_id JOIN sources s ON s.id = o.source_id, latest185 WHERE o.metric = ${metric} AND o.geography_id = ${geographyId} AND ${sexCond} AND o.age_group = ${age} AND o.year = latest.year AND c.top_level AND c.status = 'active'186 ORDER BY c.id, o.value DESC`),187 [] as Array<{ id: string; slug: string; canonical_name: string; value: number; unit: string; source_slug: string; year: number }>,188 );189 const top = rows.sort((a, b) => Number(b.value) - Number(a.value)).slice(0, n);190 return { year: top[0]?.year ?? null, cancers: top.map(({ year: _y, ...r }) => ({ ...r, value: Number(r.value) })) };191}192193/** Year span of a metric for a geography (null when nothing exists). */194export async function yearRangeFor(metric: string, geographyId: string | null): Promise<{ min: number; max: number } | null> {195 const rows = await safe(196 () => run<{ y0: number | null; y1: number | null }>(sql`SELECT min(year)::int AS y0, max(coalesce(year_end, year))::int AS y1 FROM epidemiology_observations WHERE metric = ${metric} AND (${geographyId}::text IS NULL OR geography_id = ${geographyId})`),197 [] as Array<{ y0: number | null; y1: number | null }>,198 );199 const r = rows[0];200 return r && r.y0 != null && r.y1 != null ? { min: r.y0, max: r.y1 } : null;201}202203export interface ExplorerObsRow extends ComparableObs {204 id: number;205 cancer_id: string;206 geography_id: string;207 iso3: string | null;208 year_end: number | null;209 lower_ci: number | null;210 upper_ci: number | null;211 site_definition: string | null;212 source_id: string;213 source_name: string;214 source_license: string | null;215 provenance_id: number;216 dataset: string | null;217 dataset_version: string | null;218 source_url: string | null;219 retrieved_at: Date | string | null;220 updated_at: Date | string;221}222223export interface ExplorerSelection {224 metric: string;225 cancerIds: readonly string[];226 geographyId: string;227 sex: string; // 'any' = no filter228 age: string;229 from: number | null;230 to: number | null;231 limit?: number;232}233234/** Observations for a selection, sorted by cancer, geography, sex, year (then source) — the order the table and CSV use. */235export async function explorerObservations(sel: ExplorerSelection): Promise<ExplorerObsRow[]> {236 if (sel.cancerIds.length === 0) return [];237 const limit = Math.max(1, Math.min(sel.limit ?? 20_000, 50_000));238 const conds = [sql`o.metric = ${sel.metric}`, sql`o.geography_id = ${sel.geographyId}`, sql`o.cancer_id = ANY(${sql.param([...sel.cancerIds])}::text[])`, sql`o.age_group = ${sel.age}`];239 if (sel.sex !== SEX_ANY) conds.push(sql`o.sex = ${sel.sex}`);240 if (sel.from != null) conds.push(sql`coalesce(o.year_end, o.year) >= ${sel.from}`);241 if (sel.to != null) conds.push(sql`o.year <= ${sel.to}`);242 const rows = await safe(243 () =>244 run<ExplorerObsRow>(sql`245 SELECT o.id, o.cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name,246 o.geography_id, g.slug AS geography_slug, g.name AS geography_name, g.iso3,247 o.year, o.year_end, o.sex, o.age_group, o.metric, o.value, o.unit, o.lower_ci, o.upper_ci, o.standard_population, o.estimate_type, o.site_definition,248 o.source_id, s.slug AS source_slug, s.name AS source_name, s.license AS source_license,249 o.provenance_id, p.dataset, p.dataset_version, p.source_url, p.retrieved_at, o.updated_at250 FROM epidemiology_observations o251 JOIN cancers c ON c.id = o.cancer_id252 JOIN geographies g ON g.id = o.geography_id253 JOIN sources s ON s.id = o.source_id254 LEFT JOIN provenance p ON p.id = o.provenance_id255 WHERE ${sql.join(conds, sql` AND `)}256 ORDER BY c.canonical_name, g.name, o.sex, o.year, s.slug, o.site_definition LIMIT ${limit}`),257 [] as ExplorerObsRow[],258 );259 return rows.map((r) => ({ ...r, value: Number(r.value), lower_ci: r.lower_ci == null ? null : Number(r.lower_ci), upper_ci: r.upper_ci == null ? null : Number(r.upper_ci), year: Number(r.year), year_end: r.year_end == null ? null : Number(r.year_end), provenance_id: Number(r.provenance_id) }));260}261262export interface PendingSource {263 slug: string;264 name: string;265 license_status: string;266 status: string;267}268269/** Epidemiology sources registered in the catalogue that have not contributed a single observation yet (license review, credentials…). */270export async function pendingEpidemiologySources(): Promise<PendingSource[]> {271 return safe(272 () =>273 run<PendingSource>(sql`274 SELECT s.slug, s.name, s.license_status, s.status FROM sources s275 WHERE s.category = 'epidemiology' AND NOT EXISTS (SELECT 1 FROM epidemiology_observations o WHERE o.source_id = s.id)276 ORDER BY s.slug`),277 [] as PendingSource[],278 );279}280281export interface CoverageMatrixRow {282 metric: string;283 unit: string;284 geography_id: string;285 geography_slug: string;286 geography_name: string;287 iso3: string | null;288 sex: string;289 age_group: string;290 source_slug: string;291 source_name: string;292 standard_population: string | null;293 estimate_types: string[];294 year_from: number;295 year_to: number;296 years: number;297 observations: number;298 n_cancers: number;299 last_updated: Date | string;300}301302/** Coverage matrix: what exists, per metric × geography × sex × age × source × standard population. */303export async function coverageMatrix(f: { cancerIds?: readonly string[]; geographyId?: string | null; metric?: string | null } = {}): Promise<CoverageMatrixRow[]> {304 const conds = [sql`true`];305 if (f.cancerIds && f.cancerIds.length > 0) conds.push(sql`o.cancer_id = ANY(${sql.param([...f.cancerIds])}::text[])`);306 if (f.geographyId) conds.push(sql`o.geography_id = ${f.geographyId}`);307 if (f.metric) conds.push(sql`o.metric = ${f.metric}`);308 return safe(309 () =>310 run<CoverageMatrixRow>(sql`311 SELECT o.metric, min(o.unit) AS unit, g.id AS geography_id, g.slug AS geography_slug, g.name AS geography_name, g.iso3, o.sex, o.age_group,312 s.slug AS source_slug, s.name AS source_name, o.standard_population, array_agg(DISTINCT o.estimate_type) AS estimate_types,313 min(o.year)::int AS year_from, max(coalesce(o.year_end, o.year))::int AS year_to, count(DISTINCT o.year)::int AS years, count(*)::int AS observations,314 count(DISTINCT o.cancer_id)::int AS n_cancers, max(o.updated_at) AS last_updated315 FROM epidemiology_observations o JOIN geographies g ON g.id = o.geography_id JOIN sources s ON s.id = o.source_id316 WHERE ${sql.join(conds, sql` AND `)}317 GROUP BY o.metric, g.id, g.slug, g.name, g.iso3, o.sex, o.age_group, s.slug, s.name, o.standard_population318 ORDER BY o.metric, g.name, (o.sex = 'all') DESC, o.sex, (o.age_group = 'all') DESC, o.age_group, s.slug, o.standard_population`),319 [] as CoverageMatrixRow[],320 );321}322