spb/cancerindex

1import { pgTable, text, integer, boolean, bigserial, index, uniqueIndex, real, jsonb } from 'drizzle-orm/pg-core';2import { ciId, createdAt, updatedAt } from './_common.js';34/** Canonical cancer entity (CLAUDE.md §5). One row per recognized disease concept, never per alias. */5export const cancers = pgTable(6  'cancers',7  {8    id: ciId().primaryKey(), // CI-CAN-…9    slug: text('slug').notNull(),10    canonicalName: text('canonical_name').notNull(),11    shortName: text('short_name'),12    entityType: text('entity_type').notNull().default('cancer'), // cancer | cancer_family | histology | subtype | molecular_subtype | hematologic_malignancy | precursor_condition | other13    malignant: boolean('malignant').notNull().default(true),14    solidTumor: boolean('solid_tumor').notNull().default(true),15    hematologic: boolean('hematologic').notNull().default(false),16    pediatricRelevant: boolean('pediatric_relevant').notNull().default(false),17    rareCancer: boolean('rare_cancer'), // null = unknown (no incidence data yet)18    topLevel: boolean('top_level').notNull().default(false), // member of the mutually exclusive global ranking set (§247)19    description: text('description'),20    descriptionProvenanceId: integer('description_provenance_id'),21    primaryNcitCode: text('primary_ncit_code'),22    primaryOncotreeCode: text('primary_oncotree_code'),23    depth: integer('depth').notNull().default(0), // depth in the NCIt-derived hierarchy from root "Neoplasm"24    status: text('status').notNull().default('active'), // active | deprecated | merged25    mergedInto: ciId('merged_into'),26    deprecatedReason: text('deprecated_reason'),27    classificationVersion: text('classification_version'),28    semanticTypes: text('semantic_types').array().notNull().default([]),29    createdAt: createdAt(),30    updatedAt: updatedAt(),31  },32  (t) => [33    uniqueIndex('cancers_slug_uq').on(t.slug),34    uniqueIndex('cancers_ncit_uq').on(t.primaryNcitCode),35    index('cancers_name_idx').on(t.canonicalName),36    index('cancers_type_idx').on(t.entityType, t.malignant, t.topLevel),37  ],38);3940export const cancerAliases = pgTable(41  'cancer_aliases',42  {43    id: bigserial('id', { mode: 'number' }).primaryKey(),44    cancerId: ciId('cancer_id').notNull(),45    alias: text('alias').notNull(),46    normalized: text('normalized').notNull(),47    aliasType: text('alias_type').notNull().default('synonym'), // preferred | synonym | abbreviation | historical | deprecated | display48    sourceId: ciId('source_id'),49    sourceTerminology: text('source_terminology'), // NCIt synonym source (e.g. CTRP, caDSR) or connector id50    language: text('language').notNull().default('en'),51  },52  (t) => [uniqueIndex('cancer_aliases_uq').on(t.cancerId, t.normalized, t.aliasType), index('cancer_aliases_norm_idx').on(t.normalized)],53);5455/** Multi-dimensional hierarchy (CLAUDE.md §4): several trees coexist. */56export const cancerHierarchy = pgTable(57  'cancer_hierarchy',58  {59    id: bigserial('id', { mode: 'number' }).primaryKey(),60    parentId: ciId('parent_id').notNull(),61    childId: ciId('child_id').notNull(),62    hierarchyType: text('hierarchy_type').notNull(), // ncit | oncotree | anatomical | histological | molecular | who | icd | seer63    sourceId: ciId('source_id'),64  },65  (t) => [uniqueIndex('cancer_hierarchy_uq').on(t.parentId, t.childId, t.hierarchyType), index('cancer_hierarchy_child_idx').on(t.childId)],66);6768/** Cross-reference codes (CLAUDE.md §220-221, §347): searchable, never buried in JSON. */69export const cancerCodes = pgTable(70  'cancer_codes',71  {72    id: bigserial('id', { mode: 'number' }).primaryKey(),73    cancerId: ciId('cancer_id').notNull(),74    system: text('system').notNull(), // ncit | icd10 | icd10cm | icdo_topography | icdo_morphology | doid | oncotree | umls | mesh | mondo | seer_site | efo | orphanet | gdc_project75    code: text('code').notNull(),76    matchType: text('match_type').notNull().default('EXACT_IDENTIFIER'),77    sourceId: ciId('source_id'),78    validFrom: text('valid_from'),79    validTo: text('valid_to'),80  },81  (t) => [uniqueIndex('cancer_codes_uq').on(t.cancerId, t.system, t.code), index('cancer_codes_lookup_idx').on(t.system, t.code)],82);8384export const anatomicalSites = pgTable(85  'anatomical_sites',86  {87    id: ciId().primaryKey(), // CI-ANAT-…88    name: text('name').notNull(),89    slug: text('slug').notNull(),90    ncitCode: text('ncit_code'),91    uberonId: text('uberon_id'),92    parentId: ciId('parent_id'),93    system: text('system'), // e.g. Digestive, Respiratory, Hematopoietic94  },95  (t) => [uniqueIndex('anatomical_sites_slug_uq').on(t.slug)],96);9798export const cancerAnatomy = pgTable(99  'cancer_anatomy',100  {101    id: bigserial('id', { mode: 'number' }).primaryKey(),102    cancerId: ciId('cancer_id').notNull(),103    siteId: ciId('site_id').notNull(),104    relation: text('relation').notNull().default('primary'), // primary | metastatic105    sourceId: ciId('source_id'),106  },107  (t) => [uniqueIndex('cancer_anatomy_uq').on(t.cancerId, t.siteId, t.relation)],108);109110/** Canonical geography (CLAUDE.md §117). */111export const geographies = pgTable(112  'geographies',113  {114    id: ciId().primaryKey(), // CI-GEO-…115    slug: text('slug').notNull(),116    name: text('name').notNull(),117    kind: text('kind').notNull(), // world | region | who_region | country | subdivision118    iso2: text('iso2'),119    iso3: text('iso3'),120    parentId: ciId('parent_id'),121    whoRegion: text('who_region'),122    population: integer('population'),123    populationYear: integer('population_year'),124  },125  (t) => [uniqueIndex('geographies_slug_uq').on(t.slug), index('geographies_iso3_idx').on(t.iso3)],126);127128/** Cohort definitions (CLAUDE.md §218): attribute combinations that are not taxonomy nodes. */129export const cohortDefinitions = pgTable('cohort_definitions', {130  id: bigserial('id', { mode: 'number' }).primaryKey(),131  name: text('name').notNull(),132  cancerId: ciId('cancer_id').notNull(),133  biomarkerIds: text('biomarker_ids').array().notNull().default([]),134  variantIds: text('variant_ids').array().notNull().default([]),135  stage: text('stage'),136  attributes: jsonb('attributes').$type<Record<string, unknown>>().notNull().default({}),137  confidence: real('confidence'),138  createdAt: createdAt(),139});140