spb/cancerindex

1import { pgTable, text, integer, bigserial, index, uniqueIndex, real, jsonb, boolean, timestamp, doublePrecision } from 'drizzle-orm/pg-core';2import { ciId, createdAt, updatedAt } from './_common.js';34/** Metric catalog (CLAUDE.md §250-251): every derived number has a versioned formula. */5export const metricDefinitions = pgTable(6  'metric_definitions',7  {8    id: ciId().primaryKey(), // CI-METRIC-…9    slug: text('slug').notNull(), // active_trials | mortality_incidence_ratio | …10    name: text('name').notNull(),11    description: text('description').notNull(),12    formula: text('formula').notNull(),13    formulaVersion: text('formula_version').notNull(), // ci-mir-v114    unit: text('unit').notNull(),15    higherIsWorse: boolean('higher_is_worse'),16    aggregation: text('aggregation'),17    validDimensions: text('valid_dimensions').array().notNull().default([]),18    sourceSlugs: text('source_slugs').array().notNull().default([]),19    category: text('category').notNull(), // burden | lethality | trend | rarity | treatment | trials | research | molecular | unmet_need | composite20    eligibility: jsonb('eligibility').$type<Record<string, unknown>>().notNull().default({}),21    experimental: boolean('experimental').notNull().default(false),22    createdAt: createdAt(),23    updatedAt: updatedAt(),24  },25  (t) => [uniqueIndex('metric_definitions_slug_uq').on(t.slug)],26);2728/** Ranking snapshot = one metric × one scope × one formula version at one time (CLAUDE.md §33, §184). */29export const rankingSnapshots = pgTable(30  'ranking_snapshots',31  {32    id: bigserial('id', { mode: 'number' }).primaryKey(),33    metricId: ciId('metric_id').notNull(),34    metricSlug: text('metric_slug').notNull(),35    scopeKey: text('scope_key').notNull(), // e.g. "geo=world|sex=all|age=all|year=2022|level=top"36    geography: text('geography').notNull().default('WORLD'),37    sex: text('sex').notNull().default('all'),38    ageGroup: text('age_group').notNull().default('all'),39    year: integer('year'),40    entityLevel: text('entity_level').notNull().default('top'), // top | histology | subtype | rare | all41    formulaVersion: text('formula_version').notNull(),42    eligibleEntities: integer('eligible_entities').notNull(),43    inputsHash: text('inputs_hash').notNull(),44    sourceIds: text('source_ids').array().notNull().default([]),45    isCurrent: boolean('is_current').notNull().default(true),46    generatedAt: timestamp('generated_at', { withTimezone: true }).notNull().defaultNow(),47  },48  (t) => [index('ranking_snapshots_lookup_idx').on(t.metricSlug, t.scopeKey, t.isCurrent)],49);5051export const rankings = pgTable(52  'rankings',53  {54    id: bigserial('id', { mode: 'number' }).primaryKey(),55    snapshotId: integer('snapshot_id').notNull(),56    metricSlug: text('metric_slug').notNull(),57    scopeKey: text('scope_key').notNull(),58    cancerId: ciId('cancer_id').notNull(),59    rank: integer('rank').notNull(),60    eligibleEntities: integer('eligible_entities').notNull(),61    percentile: real('percentile').notNull(),62    value: doublePrecision('value').notNull(),63    unit: text('unit').notNull(),64    confidence: text('confidence').notNull().default('MEDIUM'), // HIGH | MEDIUM | LOW | INSUFFICIENT_DATA65    inputs: jsonb('inputs').$type<Record<string, unknown>>().notNull().default({}), // lineage: observation ids, counts, formula inputs66    breakdown: jsonb('breakdown').$type<Record<string, number>>(), // composite components67    previousRank: integer('previous_rank'),68    generatedAt: timestamp('generated_at', { withTimezone: true }).notNull().defaultNow(),69  },70  (t) => [uniqueIndex('rankings_uq').on(t.snapshotId, t.cancerId), index('rankings_cancer_idx').on(t.cancerId, t.metricSlug), index('rankings_lookup_idx').on(t.metricSlug, t.scopeKey, t.rank)],71);7273/** Precomputed counters per entity, refreshed deterministically from canonical relations (CLAUDE.md §287). */74export const entityCounters = pgTable(75  'entity_counters',76  {77    id: bigserial('id', { mode: 'number' }).primaryKey(),78    entityType: text('entity_type').notNull(),79    entityId: text('entity_id').notNull(),80    trialCount: integer('trial_count').notNull().default(0),81    activeTrialCount: integer('active_trial_count').notNull().default(0),82    recruitingTrialCount: integer('recruiting_trial_count').notNull().default(0),83    phase3TrialCount: integer('phase3_trial_count').notNull().default(0),84    publicationCount: integer('publication_count').notNull().default(0),85    publicationCount5y: integer('publication_count_5y').notNull().default(0),86    publicationCount12m: integer('publication_count_12m').notNull().default(0),87    geneCount: integer('gene_count').notNull().default(0),88    variantCount: integer('variant_count').notNull().default(0),89    drugCount: integer('drug_count').notNull().default(0),90    approvedDrugCount: integer('approved_drug_count').notNull().default(0),91    evidenceCount: integer('evidence_count').notNull().default(0),92    cohortCount: integer('cohort_count').notNull().default(0),93    subtypeCount: integer('subtype_count').notNull().default(0),94    descendantCount: integer('descendant_count').notNull().default(0),95    epidemiologyObsCount: integer('epidemiology_obs_count').notNull().default(0),96    survivalObsCount: integer('survival_obs_count').notNull().default(0),97    completeness: jsonb('completeness').$type<Record<string, number>>().notNull().default({}),98    computedAt: updatedAt(),99  },100  (t) => [uniqueIndex('entity_counters_uq').on(t.entityType, t.entityId), index('entity_counters_trials_idx').on(t.entityType, t.activeTrialCount)],101);102103/** Public API keys (CLAUDE.md §190-191). */104export const apiKeys = pgTable(105  'api_keys',106  {107    id: bigserial('id', { mode: 'number' }).primaryKey(),108    keyHash: text('key_hash').notNull(),109    prefix: text('prefix').notNull(),110    label: text('label'),111    ownerEmail: text('owner_email'),112    tier: text('tier').notNull().default('free'), // free | research | pro | institutional113    rateLimitPerMinute: integer('rate_limit_per_minute').notNull().default(60),114    active: boolean('active').notNull().default(true),115    lastUsedAt: timestamp('last_used_at', { withTimezone: true }),116    createdAt: createdAt(),117  },118  (t) => [uniqueIndex('api_keys_hash_uq').on(t.keyHash)],119);120121/** Cached AI syntheses (CLAUDE.md §44, §322) — model, prompt version and record snapshot recorded. */122export const aiAnswers = pgTable(123  'ai_answers',124  {125    id: bigserial('id', { mode: 'number' }).primaryKey(),126    kind: text('kind').notNull(), // cancer_summary | ask127    subjectId: text('subject_id'),128    questionHash: text('question_hash').notNull(),129    question: text('question'),130    answer: jsonb('answer').$type<Record<string, unknown>>().notNull(),131    model: text('model').notNull(),132    promptVersion: text('prompt_version').notNull(),133    sourceSnapshot: jsonb('source_snapshot').$type<Record<string, unknown>>().notNull().default({}),134    dataAsOf: timestamp('data_as_of', { withTimezone: true }).notNull(),135    createdAt: createdAt(),136  },137  (t) => [uniqueIndex('ai_answers_uq').on(t.kind, t.questionHash, t.promptVersion)],138);139