spb/cancerindex

1import { pgTable, text, integer, bigserial, index, uniqueIndex, real, doublePrecision } from 'drizzle-orm/pg-core';2import { ciId, updatedAt } from './_common.js';34/** Time-aware epidemiology observations (CLAUDE.md §71): never overwrite a year with another. */5export const epidemiologyObservations = pgTable(6  'epidemiology_observations',7  {8    id: bigserial('id', { mode: 'number' }).primaryKey(),9    cancerId: ciId('cancer_id').notNull(),10    geographyId: ciId('geography_id').notNull(),11    year: integer('year').notNull(),12    yearEnd: integer('year_end'), // for multi-year aggregates (e.g. 2018-2022)13    sex: text('sex').notNull().default('all'), // all | male | female14    ageGroup: text('age_group').notNull().default('all'),15    metric: text('metric').notNull(), // incidence_count | incidence_rate | as_incidence_rate | mortality_count | mortality_rate | as_mortality_rate | prevalence | prevalence_5y16    value: doublePrecision('value').notNull(),17    unit: text('unit').notNull(), // count | per_100k18    lowerCi: doublePrecision('lower_ci'),19    upperCi: doublePrecision('upper_ci'),20    standardPopulation: text('standard_population'), // e.g. "US 2000 standard", "World (Segi)"21    estimateType: text('estimate_type').notNull().default('observed'), // observed | estimated | projected22    siteDefinition: text('site_definition'), // source's own site label / ICD code range23    sourceId: ciId('source_id').notNull(),24    provenanceId: integer('provenance_id').notNull(),25    ingestRunId: text('ingest_run_id'),26    updatedAt: updatedAt(),27  },28  (t) => [29    uniqueIndex('epi_obs_uq').on(t.cancerId, t.geographyId, t.year, t.sex, t.ageGroup, t.metric, t.sourceId, t.siteDefinition),30    index('epi_obs_lookup_idx').on(t.metric, t.geographyId, t.year, t.sex),31    index('epi_obs_cancer_idx').on(t.cancerId, t.metric),32  ],33);3435/** Survival requires context (CLAUDE.md §72). */36export const survivalObservations = pgTable(37  'survival_observations',38  {39    id: bigserial('id', { mode: 'number' }).primaryKey(),40    cancerId: ciId('cancer_id').notNull(),41    geographyId: ciId('geography_id'),42    stage: text('stage'),43    stagingSystem: text('staging_system'),44    sex: text('sex').notNull().default('all'),45    ageGroup: text('age_group').notNull().default('all'),46    diagnosisPeriod: text('diagnosis_period'),47    survivalType: text('survival_type').notNull(), // overall | relative | cause_specific | progression_free | disease_free | net48    durationMonths: integer('duration_months').notNull(),49    probability: real('probability'), // 0..150    medianMonths: real('median_months'),51    cohortSize: integer('cohort_size'),52    lowerCi: real('lower_ci'),53    upperCi: real('upper_ci'),54    method: text('method'),55    sourceId: ciId('source_id').notNull(),56    provenanceId: integer('provenance_id').notNull(),57    ingestRunId: text('ingest_run_id'),58    updatedAt: updatedAt(),59  },60  (t) => [index('survival_obs_cancer_idx').on(t.cancerId, t.survivalType, t.durationMonths)],61);62