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';34/**5 * Geography (country) queries for /countries and /country/[slug] (SPEC §47).6 * Everything is read straight from epidemiology_observations; nothing is estimated or extrapolated.7 * Only geographies with at least one observation are listed.8 */910export const SEXES = ['all', 'male', 'female'] as const;11export type Sex = (typeof SEXES)[number];1213export const BURDEN_METRICS = ['mortality_count', 'incidence_count', 'as_mortality_rate', 'as_incidence_rate'] as const;14export type BurdenMetric = (typeof BURDEN_METRICS)[number];1516export interface GeographyRow {17 id: string;18 slug: string;19 name: string;20 kind: string;21 iso2: string | null;22 iso3: string | null;23 who_region: string | null;24 population: number | null;25 population_year: number | null;26 parent_slug: string | null;27 parent_name: string | null;28}2930export interface CountryListRow extends GeographyRow {31 n_obs: number;32 n_cancers: number;33 min_year: number;34 max_year: number;35 sources: Array<{ slug: string; name: string }>;36 last_updated: Date | string;37}3839export const WHO_REGION_LABEL: Record<string, string> = {40 'who-afro': 'WHO African Region',41 'who-amro': 'WHO Region of the Americas',42 'who-searo': 'WHO South-East Asia Region',43 'who-euro': 'WHO European Region',44 'who-emro': 'WHO Eastern Mediterranean Region',45 'who-wpro': 'WHO Western Pacific Region',46};4748/** Geographies of any kind that carry at least one epidemiology observation. */49export async function listGeographiesWithObservations(): Promise<CountryListRow[]> {50 return safe(51 () =>52 run<CountryListRow>(sql`53 SELECT g.id, g.slug, g.name, g.kind, g.iso2, g.iso3, g.who_region, g.population, g.population_year,54 pg.slug AS parent_slug, pg.name AS parent_name,55 a.n_obs::int AS n_obs, a.n_cancers::int AS n_cancers, a.min_year::int AS min_year, a.max_year::int AS max_year, a.last_updated,56 (SELECT json_agg(json_build_object('slug', s.slug, 'name', s.name) ORDER BY s.name)57 FROM sources s WHERE s.id IN (SELECT DISTINCT o2.source_id FROM epidemiology_observations o2 WHERE o2.geography_id = g.id)) AS sources58 FROM geographies g59 LEFT JOIN geographies pg ON pg.id = g.parent_id60 JOIN (61 SELECT geography_id, count(*) AS n_obs, count(DISTINCT cancer_id) AS n_cancers, min(year) AS min_year, max(coalesce(year_end, year)) AS max_year, max(updated_at) AS last_updated62 FROM epidemiology_observations GROUP BY geography_id63 ) a ON a.geography_id = g.id64 ORDER BY (g.kind = 'world') DESC, (g.kind = 'country') DESC, g.name`),65 [] as CountryListRow[],66 );67}6869export async function getGeographyBySlug(slug: string): Promise<GeographyRow | null> {70 const rows = await safe(71 () =>72 run<GeographyRow>(sql`73 SELECT g.id, g.slug, g.name, g.kind, g.iso2, g.iso3, g.who_region, g.population, g.population_year, pg.slug AS parent_slug, pg.name AS parent_name74 FROM geographies g LEFT JOIN geographies pg ON pg.id = g.parent_id WHERE g.slug = ${slug} LIMIT 1`),75 [] as GeographyRow[],76 );77 return rows[0] ?? null;78}7980/** Scope key used by the ranking engine for a geography (ISO3, else upper-cased slug). */81export function geographyScopeCode(g: { iso3: string | null; slug: string }): string {82 return g.iso3 ?? g.slug.toUpperCase();83}8485export interface CoverageRow {86 metric: string;87 sex: string;88 min_year: number;89 max_year: number;90 n_years: number;91 n_cancers: number;92 source_slug: string;93 source_name: string;94 estimate_types: string[];95 standard_population: string | null;96 last_updated: Date | string;97}9899/** What the source covers for this geography: per metric × sex, the year span and entity count. */100export async function coverageFor(geographyId: string): Promise<CoverageRow[]> {101 return safe(102 () =>103 run<CoverageRow>(sql`104 SELECT o.metric, o.sex, min(o.year)::int AS min_year, max(o.year)::int AS max_year, count(DISTINCT o.year)::int AS n_years, count(DISTINCT o.cancer_id)::int AS n_cancers,105 s.slug AS source_slug, s.name AS source_name, array_agg(DISTINCT o.estimate_type) AS estimate_types, max(o.standard_population) AS standard_population, max(o.updated_at) AS last_updated106 FROM epidemiology_observations o JOIN sources s ON s.id = o.source_id107 WHERE o.geography_id = ${geographyId} AND o.age_group = 'all'108 GROUP BY o.metric, o.sex, s.slug, s.name ORDER BY o.metric, o.sex`),109 [] as CoverageRow[],110 );111}112113/** Distinct years with any observation for the geography (descending). */114export async function yearsFor(geographyId: string): Promise<number[]> {115 const rows = await safe(() => run<{ year: number }>(sql`SELECT DISTINCT year FROM epidemiology_observations WHERE geography_id = ${geographyId} ORDER BY year DESC`), [] as Array<{ year: number }>);116 return rows.map((r) => Number(r.year));117}118119/**120 * "All cancer sites" observation, if the source publishes one (e.g. USCS "All Cancer Sites Combined").121 * Detected from the site definition or a cancer entity flagged as the all-sites aggregate; null when absent —122 * the page then says so instead of summing per-site rows (sites overlap and sources differ in inclusion).123 */124export interface AllSitesObs {125 metric: string;126 year: number;127 sex: string;128 value: number;129 unit: string;130 estimate_type: string;131 site_definition: string | null;132 source_slug: string;133 source_name: string;134 provenance_id: number;135 cancer_slug: string;136 cancer_name: string;137}138export async function allSitesObservations(geographyId: string, year: number, sex: Sex): Promise<AllSitesObs[]> {139 return safe(140 () =>141 run<AllSitesObs>(sql`142 SELECT o.metric, o.year, o.sex, o.value, o.unit, o.estimate_type, o.site_definition, s.slug AS source_slug, s.name AS source_name, o.provenance_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name143 FROM epidemiology_observations o JOIN sources s ON s.id = o.source_id JOIN cancers c ON c.id = o.cancer_id144 WHERE o.geography_id = ${geographyId} AND o.year = ${year} AND o.sex = ${sex} AND o.age_group = 'all'145 AND (o.site_definition ILIKE '%all cancer sites%' OR o.site_definition ILIKE '%all sites combined%' OR o.site_definition ILIKE '%all malignant neoplasms%')146 ORDER BY o.metric`),147 [] as AllSitesObs[],148 );149}150151export interface TopCancerRow {152 cancer_id: string;153 slug: string;154 canonical_name: string;155 entity_type: string;156 value: number;157 unit: string;158 lower_ci: number | null;159 upper_ci: number | null;160 estimate_type: string;161 site_definition: string | null;162 standard_population: string | null;163 year: number;164 year_end: number | null;165 source_slug: string;166 source_name: string;167 provenance_id: number;168 updated_at: Date | string;169 rank: number | null;170 eligible_entities: number | null;171 rank_scope_key: string | null;172}173174export interface TopCancersResult {175 metric: BurdenMetric;176 requestedYear: number;177 year: number | null; // actual year used (latest ≤ requested with data for this metric/sex), null if none178 rows: TopCancerRow[];179}180181/**182 * Top cancers for one metric in a geography/year/sex. If the requested year has no data for this metric183 * (e.g. incidence lags mortality by a year), the latest earlier year is used and reported in `year`.184 * Ranks come from the current ranking snapshot whose scope matches (metric_slug + scope_key), when one exists.185 */186export async function topCancersFor(geo: { id: string; iso3: string | null; slug: string }, metric: BurdenMetric, requestedYear: number, sex: Sex, limit = 40): Promise<TopCancersResult> {187 const yr = await safe(188 () => run<{ y: number | null }>(sql`SELECT max(year) AS y FROM epidemiology_observations WHERE geography_id = ${geo.id} AND metric = ${metric} AND sex = ${sex} AND age_group = 'all' AND year <= ${requestedYear}`),189 [{ y: null }],190 );191 const year = yr[0]?.y == null ? null : Number(yr[0].y);192 if (year == null) return { metric, requestedYear, year: null, rows: [] };193 const scopeKey = `geo=${geographyScopeCode(geo)}|sex=${sex}|age=all|year=${year}|level=top`;194 const rows = await safe(195 () =>196 run<TopCancerRow>(sql`197 SELECT DISTINCT ON (o.cancer_id) o.cancer_id, c.slug, c.canonical_name, c.entity_type, o.value, o.unit, o.lower_ci, o.upper_ci, o.estimate_type, o.site_definition, o.standard_population,198 o.year, o.year_end, s.slug AS source_slug, s.name AS source_name, o.provenance_id, o.updated_at,199 r.rank, r.eligible_entities, r.scope_key AS rank_scope_key200 FROM epidemiology_observations o201 JOIN cancers c ON c.id = o.cancer_id202 JOIN sources s ON s.id = o.source_id203 LEFT JOIN rankings r ON r.cancer_id = o.cancer_id AND r.metric_slug = ${metric} AND r.scope_key = ${scopeKey}204 AND r.snapshot_id = (SELECT id FROM ranking_snapshots rs WHERE rs.metric_slug = ${metric} AND rs.scope_key = ${scopeKey} AND rs.is_current ORDER BY rs.generated_at DESC LIMIT 1)205 WHERE o.geography_id = ${geo.id} AND o.metric = ${metric} AND o.sex = ${sex} AND o.age_group = 'all' AND o.year = ${year}206 ORDER BY o.cancer_id, (c.top_level) DESC, o.value DESC`),207 [] as TopCancerRow[],208 );209 rows.sort((a, b) => Number(b.value) - Number(a.value) || a.canonical_name.localeCompare(b.canonical_name));210 return { metric, requestedYear, year, rows: rows.slice(0, limit) };211}212213export interface TrendPoint {214 cancer_id: string;215 slug: string;216 canonical_name: string;217 year: number;218 value: number;219 lower_ci: number | null;220 upper_ci: number | null;221 estimate_type: string;222 unit: string;223 standard_population: string | null;224 source_slug: string;225}226227/** Full yearly series of one metric for a set of cancers (all years available in the geography). */228export async function trendFor(geographyId: string, metric: BurdenMetric, sex: Sex, cancerIds: string[]): Promise<TrendPoint[]> {229 if (cancerIds.length === 0) return [];230 return safe(231 () =>232 run<TrendPoint>(sql`233 SELECT o.cancer_id, c.slug, c.canonical_name, o.year, o.value, o.lower_ci, o.upper_ci, o.estimate_type, o.unit, o.standard_population, s.slug AS source_slug234 FROM epidemiology_observations o JOIN cancers c ON c.id = o.cancer_id JOIN sources s ON s.id = o.source_id235 WHERE o.geography_id = ${geographyId} AND o.metric = ${metric} AND o.sex = ${sex} AND o.age_group = 'all'236 AND o.cancer_id IN (${sql.join(cancerIds.map((i) => sql`${i}`), sql`, `)})237 ORDER BY c.canonical_name, o.year`),238 [] as TrendPoint[],239 );240}241242export interface CagrRow {243 cancer_id: string;244 slug: string;245 canonical_name: string;246 start_year: number;247 end_year: number;248 start_value: number;249 end_value: number;250 n_years: number;251 cagr: number; // fraction per year, e.g. 0.021 = +2.1 %/yr252 unit: string;253 estimate_types: string[];254 source_slug: string;255 standard_population: string | null;256 start_provenance_id: number;257 end_provenance_id: number;258 updated_at: Date | string;259}260261export const CAGR_FORMULA = 'CAGR = (value[end_year] / value[start_year])^(1 / (end_year − start_year)) − 1';262export const CAGR_FORMULA_VERSION = 'ci-asir-cagr-10y-v1 (derived on the fly, not a stored metric)';263264/**265 * Compound annual growth rate of a metric over the last `window` available years, per cancer, computed on the266 * fly from observations (derived value; formula shown on the page). Returns nothing unless at least `window`267 * distinct years exist for the geography. Cancers with fewer than `window` points in the window are skipped.268 */269export async function cagrFor(geographyId: string, metric: BurdenMetric, sex: Sex, window = 10): Promise<{ rows: CagrRow[]; startYear: number; endYear: number; yearsAvailable: number } | null> {270 const years = await safe(271 () => run<{ year: number }>(sql`SELECT DISTINCT year FROM epidemiology_observations WHERE geography_id = ${geographyId} AND metric = ${metric} AND sex = ${sex} AND age_group = 'all' ORDER BY year DESC`),272 [] as Array<{ year: number }>,273 );274 const ys = years.map((r) => Number(r.year));275 if (ys.length < window) return null;276 const endYear = ys[0]!;277 const startYear = ys[window - 1]!;278 const rows = await safe(279 () =>280 run<CagrRow>(sql`281 WITH w AS (282 SELECT o.cancer_id, o.year, o.value, o.unit, o.estimate_type, o.provenance_id, o.standard_population, o.source_id, o.updated_at283 FROM epidemiology_observations o284 WHERE o.geography_id = ${geographyId} AND o.metric = ${metric} AND o.sex = ${sex} AND o.age_group = 'all' AND o.year BETWEEN ${startYear} AND ${endYear}285 ), agg AS (286 SELECT cancer_id, count(DISTINCT year) AS n_years, min(year) AS y0, max(year) AS y1, array_agg(DISTINCT estimate_type) AS estimate_types, max(unit) AS unit, max(standard_population) AS standard_population, max(source_id) AS source_id, max(updated_at) AS updated_at287 FROM w GROUP BY cancer_id288 )289 SELECT a.cancer_id, c.slug, c.canonical_name, a.y0::int AS start_year, a.y1::int AS end_year, w0.value AS start_value, w1.value AS end_value, a.n_years::int AS n_years,290 CASE WHEN w0.value > 0 AND a.y1 > a.y0 THEN power(w1.value / w0.value, 1.0 / (a.y1 - a.y0)) - 1 ELSE NULL END AS cagr,291 a.unit, a.estimate_types, s.slug AS source_slug, a.standard_population, w0.provenance_id AS start_provenance_id, w1.provenance_id AS end_provenance_id, a.updated_at292 FROM agg a293 JOIN cancers c ON c.id = a.cancer_id294 JOIN sources s ON s.id = a.source_id295 JOIN w w0 ON w0.cancer_id = a.cancer_id AND w0.year = a.y0296 JOIN w w1 ON w1.cancer_id = a.cancer_id AND w1.year = a.y1297 WHERE a.n_years >= ${window} AND a.y0 = ${startYear} AND a.y1 = ${endYear} AND w0.value > 0298 ORDER BY cagr DESC NULLS LAST`),299 [] as CagrRow[],300 );301 return { rows: rows.filter((r) => r.cagr != null && Number.isFinite(Number(r.cagr))).map((r) => ({ ...r, cagr: Number(r.cagr), start_value: Number(r.start_value), end_value: Number(r.end_value) })), startYear, endYear, yearsAvailable: ys.length };302}303304/** Geography slugs for the sitemap chunk (only those with observations). */305export async function geographySlugsForSitemap(): Promise<Array<{ slug: string; updated_at: Date | string }>> {306 return safe(307 () => run<{ slug: string; updated_at: Date | string }>(sql`SELECT g.slug, max(o.updated_at) AS updated_at FROM geographies g JOIN epidemiology_observations o ON o.geography_id = g.id GROUP BY g.slug ORDER BY g.slug`),308 [] as Array<{ slug: string; updated_at: Date | string }>,309 );310}311