import { pgTable, text, integer, bigserial, index, uniqueIndex, real, jsonb, doublePrecision, boolean } from 'drizzle-orm/pg-core'; import { ciId, updatedAt } from './_common.js'; /** * Derived layer — "intelligence" tables (SPEC §10, §15, §33-34, §115). Every row is recomputed * deterministically from canonical relations, carries a `formula_version` and keeps its inputs so a * number can be traced ("Why 542 active trials?"). Nothing here is an observation: the claim * category of every value is `computed_metric`. */ /** Per-cancer clinical-trial intelligence (SPEC §10): counts, growth, sponsor & geographic concentration, failures. */ export const trialIntelligence = pgTable( 'trial_intelligence', { id: bigserial('id', { mode: 'number' }).primaryKey(), cancerId: ciId('cancer_id').notNull(), /** top = mutually exclusive registry set (§246-247); all = every active malignant entity. */ entityLevel: text('entity_level').notNull().default('all'), // --- counts (interventional studies over the entity and its descendants) --- totalTrials: integer('total_trials').notNull().default(0), activeTrials: integer('active_trials').notNull().default(0), recruitingTrials: integer('recruiting_trials').notNull().default(0), phase1Active: integer('phase1_active').notNull().default(0), phase2Active: integer('phase2_active').notNull().default(0), phase3Active: integer('phase3_active').notNull().default(0), phase3Recruiting: integer('phase3_recruiting').notNull().default(0), phase4Active: integer('phase4_active').notNull().default(0), completedTrials: integer('completed_trials').notNull().default(0), terminatedTrials: integer('terminated_trials').notNull().default(0), withdrawnTrials: integer('withdrawn_trials').notNull().default(0), suspendedTrials: integer('suspended_trials').notNull().default(0), withResults: integer('with_results').notNull().default(0), // --- growth (registration date = first_posted_date) --- newTrials12m: integer('new_trials_12m').notNull().default(0), newTrialsPrior12m: integer('new_trials_prior_12m').notNull().default(0), trialGrowthYoy: real('trial_growth_yoy'), // (new_12m − prior_12m) / prior_12m; null when prior < threshold // --- enrollment --- avgEnrollment: real('avg_enrollment'), medianEnrollment: real('median_enrollment'), totalEnrollmentActive: integer('total_enrollment_active'), // --- sponsors --- distinctSponsors: integer('distinct_sponsors').notNull().default(0), industryShare: real('industry_share'), // share of active trials with lead_sponsor_class = INDUSTRY sponsorHhi: real('sponsor_hhi'), // Herfindahl–Hirschman index of lead sponsors over active trials (0..1) topSponsor: text('top_sponsor'), topSponsorShare: real('top_sponsor_share'), // --- geography --- distinctCountries: integer('distinct_countries').notNull().default(0), usShare: real('us_share'), // share of active trials with ≥ 1 US site topCountry: text('top_country'), topCountryShare: real('top_country_share'), countryHhi: real('country_hhi'), // --- failures --- terminationShare: real('termination_share'), // (terminated + withdrawn) / (completed + terminated + withdrawn), studies first posted ≥ 2010 whyStoppedBreakdown: jsonb('why_stopped_breakdown').$type>().notNull().default({}), // --- burden-normalized (US, latest year with both counts) --- trialsPer1000Deaths: real('trials_per_1000_deaths'), trialsPer100kCases: real('trials_per_100k_cases'), burdenGeography: text('burden_geography'), burdenYear: integer('burden_year'), burdenSourceId: ciId('burden_source_id'), formulaVersion: text('formula_version').notNull(), inputs: jsonb('inputs').$type>().notNull().default({}), computedAt: updatedAt(), }, (t) => [uniqueIndex('trial_intelligence_uq').on(t.cancerId, t.entityLevel), index('trial_intelligence_active_idx').on(t.entityLevel, t.activeTrials)], ); /** * Country-level trial site aggregates for the trial map (SPEC §11). `cancer_id` null = all oncology * trials; otherwise a top-level cancer (descendants included). `phase` null = any phase. */ export const trialSiteCountryCounts = pgTable( 'trial_site_country_counts', { id: bigserial('id', { mode: 'number' }).primaryKey(), cancerId: ciId('cancer_id'), phase: text('phase'), recruitingOnly: boolean('recruiting_only').notNull().default(false), country: text('country').notNull(), iso3: text('iso3'), sites: integer('sites').notNull(), trials: integer('trials').notNull(), formulaVersion: text('formula_version').notNull(), computedAt: updatedAt(), }, (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)], ); /** * Drug development pipeline (SPEC §15, §114, §119): per drug (and optionally per cancer) the highest * development stage supported by registered trials and jurisdiction-aware approvals. */ export const drugPipeline = pgTable( 'drug_pipeline', { id: bigserial('id', { mode: 'number' }).primaryKey(), drugId: ciId('drug_id').notNull(), cancerId: ciId('cancer_id'), // null = across all cancers /** preclinical | phase1 | phase2 | phase3 | phase4 | approved | withdrawn */ stage: text('stage').notNull(), maxPhase: text('max_phase'), // highest phase among interventional trials (PHASE1..PHASE4) activeTrials: integer('active_trials').notNull().default(0), recruitingTrials: integer('recruiting_trials').notNull().default(0), phase3Trials: integer('phase3_trials').notNull().default(0), totalTrials: integer('total_trials').notNull().default(0), approvals: integer('approvals').notNull().default(0), jurisdictions: text('jurisdictions').array().notNull().default([]), firstApprovalDate: text('first_approval_date'), latestApprovalDate: text('latest_approval_date'), firstTrialDate: text('first_trial_date'), formulaVersion: text('formula_version').notNull(), inputs: jsonb('inputs').$type>().notNull().default({}), computedAt: updatedAt(), }, (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)], ); /** Cross-reference codes for drugs (CLAUDE.md §347): ATC, UNII, RxCUI, DIN, ChEMBL… searchable, never buried in JSON. */ export const drugCodes = pgTable( 'drug_codes', { id: bigserial('id', { mode: 'number' }).primaryKey(), drugId: ciId('drug_id').notNull(), system: text('system').notNull(), // atc | unii | rxcui | din | chembl | drugbank | pubchem_cid | ncit | civic_therapy | hc_drug_code | ema_product | mhra | tga code: text('code').notNull(), label: text('label'), // e.g. ATC class name, brand for a DIN matchType: text('match_type').notNull().default('EXACT_IDENTIFIER'), sourceId: ciId('source_id'), }, (t) => [uniqueIndex('drug_codes_uq').on(t.drugId, t.system, t.code), index('drug_codes_lookup_idx').on(t.system, t.code)], ); /** Research-gap components per cancer and burden scope (SPEC §34, §113): shares and log-ratios with their inputs. */ export const researchGapComponents = pgTable( 'research_gap_components', { id: bigserial('id', { mode: 'number' }).primaryKey(), cancerId: ciId('cancer_id').notNull(), geography: text('geography').notNull(), // ISO3 or WORLD year: integer('year').notNull(), sex: text('sex').notNull().default('all'), burdenSourceId: ciId('burden_source_id').notNull(), deaths: doublePrecision('deaths'), incidence: doublePrecision('incidence'), activeTrials: integer('active_trials').notNull().default(0), phase3Trials: integer('phase3_trials').notNull().default(0), publications5y: integer('publications_5y').notNull().default(0), approvedDrugs: integer('approved_drugs').notNull().default(0), deathShare: real('death_share'), trialShare: real('trial_share'), publicationShare: real('publication_share'), trialGapRatio: real('trial_gap_ratio'), // log2(deathShare / trialShare) researchGapRatio: real('research_gap_ratio'), // log2(deathShare / publicationShare) trialsPer1000Deaths: real('trials_per_1000_deaths'), publicationsPer1000Deaths: real('publications_per_1000_deaths'), eligible: boolean('eligible').notNull().default(true), ineligibleReason: text('ineligible_reason'), formulaVersion: text('formula_version').notNull(), inputs: jsonb('inputs').$type>().notNull().default({}), computedAt: updatedAt(), }, (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)], );