spb/cancerindex

1import { pgTable, text, integer, bigserial, index, uniqueIndex, real, jsonb, boolean, timestamp } from 'drizzle-orm/pg-core';2import { ciId, createdAt, updatedAt } from './_common.js';34/** Genes — HGNC is authoritative for symbols (CLAUDE.md §11, §143). */5export const genes = pgTable(6  'genes',7  {8    id: ciId().primaryKey(), // CI-GENE-…9    hgncId: text('hgnc_id'), // HGNC:1199810    symbol: text('symbol').notNull(),11    name: text('name'),12    locusType: text('locus_type'),13    locusGroup: text('locus_group'),14    location: text('location'),15    chromosome: text('chromosome'),16    ensemblGeneId: text('ensembl_gene_id'),17    ncbiGeneId: text('ncbi_gene_id'),18    omimIds: text('omim_ids').array().notNull().default([]),19    uniprotIds: text('uniprot_ids').array().notNull().default([]),20    refseqAccession: text('refseq_accession'),21    prevSymbols: text('prev_symbols').array().notNull().default([]),22    aliasSymbols: text('alias_symbols').array().notNull().default([]),23    geneFamilies: text('gene_families').array().notNull().default([]),24    status: text('status').notNull().default('Approved'),25    isCancerGene: boolean('is_cancer_gene').notNull().default(false), // has ≥1 curated cancer edge (derived)26    civicGeneId: integer('civic_gene_id'),27    description: text('description'),28    createdAt: createdAt(),29    updatedAt: updatedAt(),30  },31  (t) => [uniqueIndex('genes_symbol_uq').on(t.symbol), uniqueIndex('genes_hgnc_uq').on(t.hgncId), index('genes_ensembl_idx').on(t.ensemblGeneId), index('genes_ncbi_idx').on(t.ncbiGeneId)],32);3334export const geneAliases = pgTable(35  'gene_aliases',36  {37    id: bigserial('id', { mode: 'number' }).primaryKey(),38    geneId: ciId('gene_id').notNull(),39    alias: text('alias').notNull(),40    aliasType: text('alias_type').notNull(), // prev_symbol | alias_symbol | prev_name | alias_name41    sourceId: ciId('source_id'),42  },43  (t) => [uniqueIndex('gene_aliases_uq').on(t.geneId, t.alias, t.aliasType), index('gene_aliases_alias_idx').on(t.alias)],44);4546/** Variants (CLAUDE.md §235-239): coordinates always carry assembly; original + normalized kept. */47export const variants = pgTable(48  'variants',49  {50    id: ciId().primaryKey(), // CI-VAR-…51    slug: text('slug').notNull(),52    geneId: ciId('gene_id'),53    geneSymbol: text('gene_symbol'),54    name: text('name').notNull(), // e.g. "V600E", "Exon 19 Deletion", "Amplification"55    variantType: text('variant_type'), // SO-style: SNV | MNV | insertion | deletion | indel | fusion | amplification | deletion_cna | loss_of_heterozygosity | promoter_mutation | splice | expression | epigenetic | structural | other56    hgvsG: text('hgvs_g'),57    hgvsC: text('hgvs_c'),58    hgvsP: text('hgvs_p'),59    assembly: text('assembly'), // GRCh37 | GRCh3860    chromosome: text('chromosome'),61    start: integer('start'),62    end: integer('end'),63    referenceBases: text('reference_bases'),64    alternateBases: text('alternate_bases'),65    coordinates: jsonb('coordinates').$type<Array<Record<string, unknown>>>().notNull().default([]), // per-assembly list66    clinvarVariationId: text('clinvar_variation_id'),67    civicVariantId: integer('civic_variant_id'),68    dbsnpIds: text('dbsnp_ids').array().notNull().default([]),69    fusionPartners: text('fusion_partners').array().notNull().default([]), // [5' gene, 3' gene]70    createdAt: createdAt(),71    updatedAt: updatedAt(),72  },73  (t) => [uniqueIndex('variants_slug_uq').on(t.slug), index('variants_gene_idx').on(t.geneId), index('variants_clinvar_idx').on(t.clinvarVariationId), index('variants_civic_idx').on(t.civicVariantId)],74);7576export const variantAliases = pgTable(77  'variant_aliases',78  {79    id: bigserial('id', { mode: 'number' }).primaryKey(),80    variantId: ciId('variant_id').notNull(),81    alias: text('alias').notNull(),82    sourceId: ciId('source_id'),83  },84  (t) => [uniqueIndex('variant_aliases_uq').on(t.variantId, t.alias)],85);8687/** ClinVar interpretations (CLAUDE.md §10.7): structured, never flattened. */88export const variantClinicalSignificance = pgTable(89  'variant_clinical_significance',90  {91    id: bigserial('id', { mode: 'number' }).primaryKey(),92    variantId: ciId('variant_id').notNull(),93    clinvarVariationId: text('clinvar_variation_id').notNull(),94    clinicalSignificance: text('clinical_significance').notNull(),95    reviewStatus: text('review_status'),96    starRating: integer('star_rating'),97    lastEvaluated: text('last_evaluated'),98    conditions: text('conditions').array().notNull().default([]),99    conditionCancerIds: text('condition_cancer_ids').array().notNull().default([]),100    originSimple: text('origin_simple'),101    numberSubmitters: integer('number_submitters'),102    provenanceId: integer('provenance_id').notNull(),103    ingestRunId: text('ingest_run_id'),104    updatedAt: updatedAt(),105  },106  (t) => [uniqueIndex('variant_clinsig_uq').on(t.clinvarVariationId)],107);108109export const biomarkers = pgTable(110  'biomarkers',111  {112    id: ciId().primaryKey(), // CI-BIO-…113    slug: text('slug').notNull(),114    name: text('name').notNull(),115    kind: text('kind').notNull(), // gene_mutation | protein_expression | hormone_receptor | immune_marker | msi | tmb | hrd | ctdna | methylation | signature | cell_surface | other116    geneId: ciId('gene_id'),117    ncitCode: text('ncit_code'),118    description: text('description'),119    measurement: jsonb('measurement').$type<Record<string, unknown>>().notNull().default({}), // assay/clone/scoring/thresholds (§241-243)120    createdAt: createdAt(),121    updatedAt: updatedAt(),122  },123  (t) => [uniqueIndex('biomarkers_slug_uq').on(t.slug)],124);125126/** Genomic studies / cohorts (GDC projects, cBioPortal studies…) — original study IDs preserved. */127export const genomicCohorts = pgTable(128  'genomic_cohorts',129  {130    id: ciId().primaryKey(), // CI-STUDY-…131    sourceId: ciId('source_id').notNull(),132    studyId: text('study_id').notNull(), // TCGA-PAAD133    name: text('name').notNull(),134    program: text('program'),135    primarySites: text('primary_sites').array().notNull().default([]),136    diseaseTypes: text('disease_types').array().notNull().default([]),137    cancerId: ciId('cancer_id'),138    cancerMatchType: text('cancer_match_type'),139    caseCount: integer('case_count'),140    casesWithSsm: integer('cases_with_ssm'), // denominator for mutation frequencies141    dataRelease: text('data_release'),142    accessLevel: text('access_level').notNull().default('open'),143    url: text('url'),144    provenanceId: integer('provenance_id'),145    updatedAt: updatedAt(),146  },147  (t) => [uniqueIndex('genomic_cohorts_uq').on(t.sourceId, t.studyId), index('genomic_cohorts_cancer_idx').on(t.cancerId)],148);149150/** Gene alteration frequency per cohort — denominator is mandatory (CLAUDE.md §261-262). */151export const cancerGeneFrequencies = pgTable(152  'cancer_gene_frequencies',153  {154    id: bigserial('id', { mode: 'number' }).primaryKey(),155    cohortId: ciId('cohort_id').notNull(),156    cancerId: ciId('cancer_id'),157    geneId: ciId('gene_id'),158    geneSymbol: text('gene_symbol').notNull(),159    alterationType: text('alteration_type').notNull().default('ssm'), // ssm | cnv_gain | cnv_loss | fusion160    casesAffected: integer('cases_affected').notNull(),161    casesProfiled: integer('cases_profiled').notNull(),162    frequency: real('frequency').notNull(),163    rank: integer('rank'),164    dataRelease: text('data_release'),165    provenanceId: integer('provenance_id').notNull(),166    updatedAt: updatedAt(),167  },168  (t) => [uniqueIndex('cancer_gene_freq_uq').on(t.cohortId, t.geneSymbol, t.alterationType), index('cancer_gene_freq_cancer_idx').on(t.cancerId, t.frequency), index('cancer_gene_freq_gene_idx').on(t.geneId)],169);170171export const entityEmbeddings = pgTable(172  'entity_embeddings',173  {174    id: bigserial('id', { mode: 'number' }).primaryKey(),175    entityType: text('entity_type').notNull(),176    entityId: text('entity_id').notNull(),177    model: text('model').notNull(),178    dimensions: integer('dimensions').notNull(),179    textHash: text('text_hash').notNull(),180    embedding: text('embedding'), // stored via raw SQL cast to vector(n); model recorded on the row181    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),182  },183  (t) => [uniqueIndex('entity_embeddings_uq').on(t.entityType, t.entityId, t.model)],184);185