spb/cancerindex

1import { pgTable, text, integer, bigserial, index, uniqueIndex, boolean, jsonb, real, timestamp } from 'drizzle-orm/pg-core';2import { ciId, createdAt, updatedAt } from './_common.js';34/** Knowledge graph edge with mandatory context and provenance (CLAUDE.md §28-29, §244). */5export const knowledgeEdges = pgTable(6  'knowledge_edges',7  {8    id: bigserial('id', { mode: 'number' }).primaryKey(),9    sourceEntityType: text('source_entity_type').notNull(), // cancer | gene | variant | drug | biomarker | trial | publication | pathway | risk_factor10    sourceEntityId: text('source_entity_id').notNull(),11    targetEntityType: text('target_entity_type').notNull(),12    targetEntityId: text('target_entity_id').notNull(),13    relationshipType: text('relationship_type').notNull(), // HAS_SUBTYPE | OCCURS_IN | ASSOCIATED_WITH | HAS_VARIANT | HAS_BIOMARKER | TREATED_BY | STUDIED_IN | DESCRIBED_BY | TARGETS | APPROVED_FOR | INVESTIGATED_FOR | PREDICTS_RESPONSE_TO | CONFERS_RESISTANCE_TO | PREDISPOSES_TO | PROGNOSTIC_IN | DIAGNOSTIC_OF14    cancerContextIds: text('cancer_context_ids').array().notNull().default([]),15    predictive: boolean('predictive'),16    prognostic: boolean('prognostic'),17    diagnostic: boolean('diagnostic'),18    predisposing: boolean('predisposing'),19    direction: text('direction'), // supports | resistance | sensitivity | neutral | unknown20    evidenceLevel: text('evidence_level'), // source-native level (e.g. CIViC A-E) — never re-scaled silently21    evidenceScore: real('evidence_score'),22    evidenceCategory: text('evidence_category').notNull().default('curated_evidence'), // observed_data | published_evidence | curated_evidence | regulatory_status | clinical_guideline | computed_metric23    status: text('status').notNull().default('active'), // active | candidate | superseded | retracted_basis24    sourceId: ciId('source_id').notNull(),25    sourceRecordId: text('source_record_id'),26    provenanceIds: integer('provenance_ids').array().notNull().default([]),27    supportCount: integer('support_count').notNull().default(1),28    firstSeenAt: timestamp('first_seen_at', { withTimezone: true }).notNull().defaultNow(),29    lastSeenAt: timestamp('last_seen_at', { withTimezone: true }).notNull().defaultNow(),30  },31  (t) => [32    uniqueIndex('knowledge_edges_uq').on(t.sourceEntityType, t.sourceEntityId, t.targetEntityType, t.targetEntityId, t.relationshipType, t.sourceId, t.sourceRecordId),33    index('knowledge_edges_source_idx').on(t.sourceEntityType, t.sourceEntityId, t.relationshipType),34    index('knowledge_edges_target_idx').on(t.targetEntityType, t.targetEntityId, t.relationshipType),35  ],36);3738/** CIViC evidence items kept in their native structure (CLAUDE.md §10.9). */39export const civicEvidenceItems = pgTable(40  'civic_evidence_items',41  {42    id: bigserial('id', { mode: 'number' }).primaryKey(),43    civicId: integer('civic_id').notNull(),44    name: text('name'),45    molecularProfileId: integer('molecular_profile_id'),46    molecularProfileName: text('molecular_profile_name'),47    geneSymbols: text('gene_symbols').array().notNull().default([]),48    geneIds: text('gene_ids').array().notNull().default([]),49    variantIds: text('variant_ids').array().notNull().default([]),50    civicVariantIds: integer('civic_variant_ids').array().notNull().default([]),51    diseaseName: text('disease_name'),52    doid: text('doid'),53    cancerId: ciId('cancer_id'),54    cancerMatchType: text('cancer_match_type'),55    therapyNames: text('therapy_names').array().notNull().default([]),56    therapyIds: text('therapy_ids').array().notNull().default([]), // CI-DRUG-…57    therapyInteractionType: text('therapy_interaction_type'),58    evidenceType: text('evidence_type'), // PREDICTIVE | PROGNOSTIC | DIAGNOSTIC | PREDISPOSING | ONCOGENIC | FUNCTIONAL59    evidenceLevel: text('evidence_level'), // A-E60    evidenceDirection: text('evidence_direction'), // SUPPORTS | DOES_NOT_SUPPORT61    significance: text('significance'), // SENSITIVITYRESPONSE | RESISTANCE | …62    evidenceRating: integer('evidence_rating'),63    status: text('status'), // ACCEPTED | SUBMITTED | REJECTED64    description: text('description'),65    pmid: text('pmid'),66    sourceCitation: text('source_citation'),67    phenotypes: text('phenotypes').array().notNull().default([]),68    provenanceId: integer('provenance_id').notNull(),69    ingestRunId: text('ingest_run_id'),70    updatedAt: updatedAt(),71  },72  (t) => [uniqueIndex('civic_evidence_uq').on(t.civicId), index('civic_evidence_cancer_idx').on(t.cancerId), index('civic_evidence_gene_idx').on(t.geneSymbols)],73);7475/** Risk factors / carcinogens as first-class entities (CLAUDE.md §74, §133). */76export const riskFactors = pgTable(77  'risk_factors',78  {79    id: bigserial('id', { mode: 'number' }).primaryKey(),80    slug: text('slug').notNull(),81    name: text('name').notNull(),82    kind: text('kind').notNull(), // behavioral | infectious | environmental | occupational | genetic | hormonal | demographic | carcinogen83    classificationAuthority: text('classification_authority'),84    classification: text('classification'),85    description: text('description'),86    createdAt: createdAt(),87  },88  (t) => [uniqueIndex('risk_factors_slug_uq').on(t.slug)],89);90