spb/cancerindex

1import { pgTable, text, integer, bigserial, index, uniqueIndex, boolean, jsonb, real, date } from 'drizzle-orm/pg-core';2import { ciId, createdAt, updatedAt } from './_common.js';34/** ClinicalTrials.gov studies (CLAUDE.md §10.5). */5export const clinicalTrials = pgTable(6  'clinical_trials',7  {8    id: ciId().primaryKey(), // CI-TRIAL-…9    nctId: text('nct_id').notNull(),10    briefTitle: text('brief_title').notNull(),11    officialTitle: text('official_title'),12    acronym: text('acronym'),13    studyType: text('study_type'), // INTERVENTIONAL | OBSERVATIONAL | EXPANDED_ACCESS14    phases: text('phases').array().notNull().default([]), // EARLY_PHASE1 | PHASE1 | PHASE2 | PHASE3 | PHASE4 | NA15    overallStatus: text('overall_status'), // RECRUITING | ACTIVE_NOT_RECRUITING | COMPLETED | …16    whyStopped: text('why_stopped'),17    startDate: text('start_date'),18    primaryCompletionDate: text('primary_completion_date'),19    completionDate: text('completion_date'),20    firstPostedDate: text('first_posted_date'),21    lastUpdatePostedDate: text('last_update_posted_date'),22    resultsFirstPostedDate: text('results_first_posted_date'),23    hasResults: boolean('has_results').notNull().default(false),24    enrollmentCount: integer('enrollment_count'),25    enrollmentType: text('enrollment_type'),26    leadSponsor: text('lead_sponsor'),27    leadSponsorClass: text('lead_sponsor_class'),28    collaborators: text('collaborators').array().notNull().default([]),29    conditions: text('conditions').array().notNull().default([]), // raw free text30    keywords: text('keywords').array().notNull().default([]),31    interventions: jsonb('interventions').$type<Array<{ type: string; name: string; description?: string; otherNames?: string[] }>>().notNull().default([]),32    arms: jsonb('arms').$type<Array<Record<string, unknown>>>().notNull().default([]),33    primaryOutcomes: jsonb('primary_outcomes').$type<Array<Record<string, unknown>>>().notNull().default([]),34    secondaryOutcomes: jsonb('secondary_outcomes').$type<Array<Record<string, unknown>>>().notNull().default([]),35    eligibility: jsonb('eligibility').$type<Record<string, unknown>>().notNull().default({}),36    sex: text('sex'),37    minimumAge: text('minimum_age'),38    maximumAge: text('maximum_age'),39    countries: text('countries').array().notNull().default([]),40    locationsCount: integer('locations_count').notNull().default(0),41    references: jsonb('references').$type<Array<{ pmid?: string; type?: string; citation?: string }>>().notNull().default([]),42    briefSummary: text('brief_summary'),43    isOncology: boolean('is_oncology').notNull().default(true),44    sourceRecordId: integer('source_record_id'),45    ingestRunId: text('ingest_run_id'),46    createdAt: createdAt(),47    updatedAt: updatedAt(),48  },49  (t) => [50    uniqueIndex('clinical_trials_nct_uq').on(t.nctId),51    index('clinical_trials_status_idx').on(t.overallStatus),52    index('clinical_trials_updated_idx').on(t.lastUpdatePostedDate),53    index('clinical_trials_sponsor_idx').on(t.leadSponsor),54  ],55);5657/** trial → cancer mapping from free-text conditions (CLAUDE.md §10.5, §221). */58export const trialConditions = pgTable(59  'trial_conditions',60  {61    id: bigserial('id', { mode: 'number' }).primaryKey(),62    trialId: ciId('trial_id').notNull(),63    conditionText: text('condition_text').notNull(),64    normalized: text('normalized').notNull(),65    cancerId: ciId('cancer_id'),66    matchType: text('match_type').notNull().default('UNRESOLVED'),67    confidence: real('confidence'),68  },69  (t) => [uniqueIndex('trial_conditions_uq').on(t.trialId, t.normalized), index('trial_conditions_cancer_idx').on(t.cancerId), index('trial_conditions_norm_idx').on(t.normalized)],70);7172export const trialInterventions = pgTable(73  'trial_interventions',74  {75    id: bigserial('id', { mode: 'number' }).primaryKey(),76    trialId: ciId('trial_id').notNull(),77    name: text('name').notNull(),78    normalized: text('normalized').notNull(),79    interventionType: text('intervention_type'), // DRUG | BIOLOGICAL | DEVICE | PROCEDURE | RADIATION | BEHAVIORAL | OTHER80    drugId: ciId('drug_id'),81    matchType: text('match_type').notNull().default('UNRESOLVED'),82  },83  (t) => [uniqueIndex('trial_interventions_uq').on(t.trialId, t.normalized), index('trial_interventions_drug_idx').on(t.drugId)],84);8586export const trialLocations = pgTable(87  'trial_locations',88  {89    id: bigserial('id', { mode: 'number' }).primaryKey(),90    trialId: ciId('trial_id').notNull(),91    facility: text('facility'),92    city: text('city'),93    state: text('state'),94    zip: text('zip'),95    country: text('country'),96    status: text('status'),97    lat: real('lat'),98    lng: real('lng'),99  },100  (t) => [index('trial_locations_trial_idx').on(t.trialId), index('trial_locations_country_idx').on(t.country)],101);102103/** Daily trial-creation pulse (derived; CLAUDE.md §332). */104export const trialPulse = pgTable(105  'trial_pulse',106  {107    id: bigserial('id', { mode: 'number' }).primaryKey(),108    day: date('day').notNull(),109    cancerId: ciId('cancer_id'),110    phase: text('phase'),111    newTrials: integer('new_trials').notNull(),112    computedAt: updatedAt(),113  },114  (t) => [uniqueIndex('trial_pulse_uq').on(t.day, t.cancerId, t.phase)],115);116