spb/cancerindex

1import { pgTable, text, integer, bigserial, index, uniqueIndex, boolean, jsonb, real } from 'drizzle-orm/pg-core';2import { ciId, createdAt, updatedAt } from './_common.js';34/** Publications (CLAUDE.md §10.6, §342): one entity per paper across PubMed/DOI/Europe PMC. */5export const publications = pgTable(6  'publications',7  {8    id: ciId().primaryKey(), // CI-PUB-…9    pmid: text('pmid'),10    doi: text('doi'),11    pmcid: text('pmcid'),12    title: text('title').notNull(),13    abstract: text('abstract'), // stored only where NLM terms permit (abstract text may carry publisher copyright; we store it for indexing, display truncated with link)14    journal: text('journal'),15    journalIso: text('journal_iso'),16    pubDate: text('pub_date'),17    pubYear: integer('pub_year'),18    publicationTypes: text('publication_types').array().notNull().default([]),19    meshTerms: jsonb('mesh_terms').$type<Array<{ descriptor: string; ui?: string; major: boolean; qualifiers?: string[] }>>().notNull().default([]),20    authors: jsonb('authors').$type<Array<{ name: string; affiliation?: string; orcid?: string }>>().notNull().default([]),21    language: text('language'),22    isPreprint: boolean('is_preprint').notNull().default(false),23    retracted: boolean('retracted').notNull().default(false),24    retractionNotice: text('retraction_notice'),25    nctIds: text('nct_ids').array().notNull().default([]),26    citedByCount: integer('cited_by_count'),27    sourceRecordId: integer('source_record_id'),28    ingestRunId: text('ingest_run_id'),29    createdAt: createdAt(),30    updatedAt: updatedAt(),31  },32  (t) => [uniqueIndex('publications_pmid_uq').on(t.pmid), index('publications_doi_idx').on(t.doi), index('publications_year_idx').on(t.pubYear), index('publications_retracted_idx').on(t.retracted)],33);3435/** publication → entity links (CLAUDE.md §165-166, §271): extraction method + status preserved. */36export const publicationEntityEdges = pgTable(37  'publication_entity_edges',38  {39    id: bigserial('id', { mode: 'number' }).primaryKey(),40    publicationId: ciId('publication_id').notNull(),41    entityType: text('entity_type').notNull(), // cancer | gene | variant | drug | biomarker | trial42    entityId: text('entity_id').notNull(),43    method: text('method').notNull(), // mesh | dictionary | registry_reference | civic_curation | ner | llm | curator44    confidence: real('confidence'),45    status: text('status').notNull().default('candidate'), // candidate | validated | rejected46    sourceId: ciId('source_id'),47    ingestRunId: text('ingest_run_id'),48    createdAt: createdAt(),49  },50  (t) => [uniqueIndex('pub_entity_edges_uq').on(t.publicationId, t.entityType, t.entityId, t.method), index('pub_entity_edges_entity_idx').on(t.entityType, t.entityId)],51);5253/** Research activity counts per cancer from PubMed queries — the formula (query string) is stored (CLAUDE.md §251). */54export const literatureCounts = pgTable(55  'literature_counts',56  {57    id: bigserial('id', { mode: 'number' }).primaryKey(),58    cancerId: ciId('cancer_id').notNull(),59    windowKey: text('window_key').notNull(), // all | 12m | 5y | 10y | y2015 …60    windowStart: text('window_start'),61    windowEnd: text('window_end'),62    query: text('query').notNull(), // exact PubMed query used63    count: integer('count').notNull(),64    provenanceId: integer('provenance_id').notNull(),65    computedAt: updatedAt(),66  },67  (t) => [uniqueIndex('literature_counts_uq').on(t.cancerId, t.windowKey), index('literature_counts_window_idx').on(t.windowKey, t.count)],68);69