spb/cancerindex

1import { pgTable, text, integer, bigserial, index, uniqueIndex, real, jsonb, doublePrecision, boolean } from 'drizzle-orm/pg-core';2import { ciId, updatedAt } from './_common.js';34/**5 * Derived layer — "intelligence" tables (SPEC §10, §15, §33-34, §115). Every row is recomputed6 * deterministically from canonical relations, carries a `formula_version` and keeps its inputs so a7 * number can be traced ("Why 542 active trials?"). Nothing here is an observation: the claim8 * category of every value is `computed_metric`.9 */1011/** Per-cancer clinical-trial intelligence (SPEC §10): counts, growth, sponsor & geographic concentration, failures. */12export const trialIntelligence = pgTable(13  'trial_intelligence',14  {15    id: bigserial('id', { mode: 'number' }).primaryKey(),16    cancerId: ciId('cancer_id').notNull(),17    /** top = mutually exclusive registry set (§246-247); all = every active malignant entity. */18    entityLevel: text('entity_level').notNull().default('all'),19    // --- counts (interventional studies over the entity and its descendants) ---20    totalTrials: integer('total_trials').notNull().default(0),21    activeTrials: integer('active_trials').notNull().default(0),22    recruitingTrials: integer('recruiting_trials').notNull().default(0),23    phase1Active: integer('phase1_active').notNull().default(0),24    phase2Active: integer('phase2_active').notNull().default(0),25    phase3Active: integer('phase3_active').notNull().default(0),26    phase3Recruiting: integer('phase3_recruiting').notNull().default(0),27    phase4Active: integer('phase4_active').notNull().default(0),28    completedTrials: integer('completed_trials').notNull().default(0),29    terminatedTrials: integer('terminated_trials').notNull().default(0),30    withdrawnTrials: integer('withdrawn_trials').notNull().default(0),31    suspendedTrials: integer('suspended_trials').notNull().default(0),32    withResults: integer('with_results').notNull().default(0),33    // --- growth (registration date = first_posted_date) ---34    newTrials12m: integer('new_trials_12m').notNull().default(0),35    newTrialsPrior12m: integer('new_trials_prior_12m').notNull().default(0),36    trialGrowthYoy: real('trial_growth_yoy'), // (new_12m − prior_12m) / prior_12m; null when prior < threshold37    // --- enrollment ---38    avgEnrollment: real('avg_enrollment'),39    medianEnrollment: real('median_enrollment'),40    totalEnrollmentActive: integer('total_enrollment_active'),41    // --- sponsors ---42    distinctSponsors: integer('distinct_sponsors').notNull().default(0),43    industryShare: real('industry_share'), // share of active trials with lead_sponsor_class = INDUSTRY44    sponsorHhi: real('sponsor_hhi'), // Herfindahl–Hirschman index of lead sponsors over active trials (0..1)45    topSponsor: text('top_sponsor'),46    topSponsorShare: real('top_sponsor_share'),47    // --- geography ---48    distinctCountries: integer('distinct_countries').notNull().default(0),49    usShare: real('us_share'), // share of active trials with ≥ 1 US site50    topCountry: text('top_country'),51    topCountryShare: real('top_country_share'),52    countryHhi: real('country_hhi'),53    // --- failures ---54    terminationShare: real('termination_share'), // (terminated + withdrawn) / (completed + terminated + withdrawn), studies first posted ≥ 201055    whyStoppedBreakdown: jsonb('why_stopped_breakdown').$type<Record<string, number>>().notNull().default({}),56    // --- burden-normalized (US, latest year with both counts) ---57    trialsPer1000Deaths: real('trials_per_1000_deaths'),58    trialsPer100kCases: real('trials_per_100k_cases'),59    burdenGeography: text('burden_geography'),60    burdenYear: integer('burden_year'),61    burdenSourceId: ciId('burden_source_id'),62    formulaVersion: text('formula_version').notNull(),63    inputs: jsonb('inputs').$type<Record<string, unknown>>().notNull().default({}),64    computedAt: updatedAt(),65  },66  (t) => [uniqueIndex('trial_intelligence_uq').on(t.cancerId, t.entityLevel), index('trial_intelligence_active_idx').on(t.entityLevel, t.activeTrials)],67);6869/**70 * Country-level trial site aggregates for the trial map (SPEC §11). `cancer_id` null = all oncology71 * trials; otherwise a top-level cancer (descendants included). `phase` null = any phase.72 */73export const trialSiteCountryCounts = pgTable(74  'trial_site_country_counts',75  {76    id: bigserial('id', { mode: 'number' }).primaryKey(),77    cancerId: ciId('cancer_id'),78    phase: text('phase'),79    recruitingOnly: boolean('recruiting_only').notNull().default(false),80    country: text('country').notNull(),81    iso3: text('iso3'),82    sites: integer('sites').notNull(),83    trials: integer('trials').notNull(),84    formulaVersion: text('formula_version').notNull(),85    computedAt: updatedAt(),86  },87  (t) => [uniqueIndex('trial_site_country_uq').on(t.cancerId, t.phase, t.recruitingOnly, t.country), index('trial_site_country_lookup_idx').on(t.cancerId, t.phase, t.recruitingOnly)],88);8990/**91 * Drug development pipeline (SPEC §15, §114, §119): per drug (and optionally per cancer) the highest92 * development stage supported by registered trials and jurisdiction-aware approvals.93 */94export const drugPipeline = pgTable(95  'drug_pipeline',96  {97    id: bigserial('id', { mode: 'number' }).primaryKey(),98    drugId: ciId('drug_id').notNull(),99    cancerId: ciId('cancer_id'), // null = across all cancers100    /** preclinical | phase1 | phase2 | phase3 | phase4 | approved | withdrawn */101    stage: text('stage').notNull(),102    maxPhase: text('max_phase'), // highest phase among interventional trials (PHASE1..PHASE4)103    activeTrials: integer('active_trials').notNull().default(0),104    recruitingTrials: integer('recruiting_trials').notNull().default(0),105    phase3Trials: integer('phase3_trials').notNull().default(0),106    totalTrials: integer('total_trials').notNull().default(0),107    approvals: integer('approvals').notNull().default(0),108    jurisdictions: text('jurisdictions').array().notNull().default([]),109    firstApprovalDate: text('first_approval_date'),110    latestApprovalDate: text('latest_approval_date'),111    firstTrialDate: text('first_trial_date'),112    formulaVersion: text('formula_version').notNull(),113    inputs: jsonb('inputs').$type<Record<string, unknown>>().notNull().default({}),114    computedAt: updatedAt(),115  },116  (t) => [uniqueIndex('drug_pipeline_uq').on(t.drugId, t.cancerId), index('drug_pipeline_stage_idx').on(t.stage), index('drug_pipeline_cancer_idx').on(t.cancerId, t.stage)],117);118119/** Cross-reference codes for drugs (CLAUDE.md §347): ATC, UNII, RxCUI, DIN, ChEMBL… searchable, never buried in JSON. */120export const drugCodes = pgTable(121  'drug_codes',122  {123    id: bigserial('id', { mode: 'number' }).primaryKey(),124    drugId: ciId('drug_id').notNull(),125    system: text('system').notNull(), // atc | unii | rxcui | din | chembl | drugbank | pubchem_cid | ncit | civic_therapy | hc_drug_code | ema_product | mhra | tga126    code: text('code').notNull(),127    label: text('label'), // e.g. ATC class name, brand for a DIN128    matchType: text('match_type').notNull().default('EXACT_IDENTIFIER'),129    sourceId: ciId('source_id'),130  },131  (t) => [uniqueIndex('drug_codes_uq').on(t.drugId, t.system, t.code), index('drug_codes_lookup_idx').on(t.system, t.code)],132);133134/** Research-gap components per cancer and burden scope (SPEC §34, §113): shares and log-ratios with their inputs. */135export const researchGapComponents = pgTable(136  'research_gap_components',137  {138    id: bigserial('id', { mode: 'number' }).primaryKey(),139    cancerId: ciId('cancer_id').notNull(),140    geography: text('geography').notNull(), // ISO3 or WORLD141    year: integer('year').notNull(),142    sex: text('sex').notNull().default('all'),143    burdenSourceId: ciId('burden_source_id').notNull(),144    deaths: doublePrecision('deaths'),145    incidence: doublePrecision('incidence'),146    activeTrials: integer('active_trials').notNull().default(0),147    phase3Trials: integer('phase3_trials').notNull().default(0),148    publications5y: integer('publications_5y').notNull().default(0),149    approvedDrugs: integer('approved_drugs').notNull().default(0),150    deathShare: real('death_share'),151    trialShare: real('trial_share'),152    publicationShare: real('publication_share'),153    trialGapRatio: real('trial_gap_ratio'), // log2(deathShare / trialShare)154    researchGapRatio: real('research_gap_ratio'), // log2(deathShare / publicationShare)155    trialsPer1000Deaths: real('trials_per_1000_deaths'),156    publicationsPer1000Deaths: real('publications_per_1000_deaths'),157    eligible: boolean('eligible').notNull().default(true),158    ineligibleReason: text('ineligible_reason'),159    formulaVersion: text('formula_version').notNull(),160    inputs: jsonb('inputs').$type<Record<string, unknown>>().notNull().default({}),161    computedAt: updatedAt(),162  },163  (t) => [uniqueIndex('research_gap_components_uq').on(t.cancerId, t.geography, t.year, t.sex, t.burdenSourceId), index('research_gap_scope_idx').on(t.geography, t.year, t.sex)],164);165