spb/cancerindex

1import { pgTable, text, integer, bigint, bigserial, boolean, jsonb, timestamp, index, uniqueIndex, real, varchar } from 'drizzle-orm/pg-core';2import { ciId, createdAt, updatedAt } from './_common.js';34/** Per-namespace counters for stable public IDs (never exposed, never reused). */5export const idSequences = pgTable('id_sequences', {6  namespace: varchar('namespace', { length: 16 }).primaryKey(),7  next: bigint('next', { mode: 'number' }).notNull().default(1),8});910/** Source registry + license registry (CLAUDE.md §9, §95, §142). Seeded from connector manifests. */11export const sources = pgTable(12  'sources',13  {14    id: ciId().primaryKey(), // CI-SOURCE-…15    slug: text('slug').notNull(), // connector id, e.g. "clinicaltrials"16    name: text('name').notNull(),17    organization: text('organization'),18    category: text('category').notNull(), // terminology | genomics | epidemiology | trials | literature | variants | drugs | regulatory19    description: text('description'),20    homepage: text('homepage'),21    docsUrl: text('docs_url'),22    termsUrl: text('terms_url'),23    accessType: text('access_type').notNull(), // api|bulk|rss|ftp|graphql|rest|scrape|manual24    accessAuth: text('access_auth').notNull(), // none|api_key|oauth|account|controlled25    license: text('license'),26    licenseStatus: text('license_status').notNull().default('review'), // review | approved | restricted | blocked27    commercialUse: text('commercial_use').notNull().default('unknown'), // allowed | restricted | prohibited | unknown28    redistribution: text('redistribution').notNull().default('unknown'), // allowed | attribution | restricted | prohibited | unknown29    attribution: text('attribution'),30    licenseReviewedAt: timestamp('license_reviewed_at', { withTimezone: true }),31    approvedForProduction: boolean('approved_for_production').notNull().default(false),32    updateFrequency: text('update_frequency'),33    supportsIncremental: boolean('supports_incremental').notNull().default(false),34    entities: text('entities').array().notNull().default([]),35    metrics: text('metrics').array().notNull().default([]),36    rateLimit: text('rate_limit'),37    status: text('status').notNull().default('planned'), // planned | active | paused | degraded | awaiting_credentials | review | retired38    tier: integer('tier').notNull().default(0),39    manifest: jsonb('manifest').$type<Record<string, unknown>>().notNull().default({}),40    createdAt: createdAt(),41    updatedAt: updatedAt(),42  },43  (t) => [uniqueIndex('sources_slug_uq').on(t.slug)],44);4546/** Every connector execution (CLAUDE.md §24, §176). */47export const ingestRuns = pgTable(48  'ingest_runs',49  {50    id: text('id').primaryKey(), // ING-CLINICALTRIALS-20260908-00001951    connectorId: text('connector_id').notNull(),52    sourceId: ciId('source_id').notNull(),53    mode: text('mode').notNull().default('incremental'), // full | incremental | backfill | dry_run | probe54    status: text('status').notNull().default('running'), // running | succeeded | failed | partial | aborted55    startedAt: timestamp('started_at', { withTimezone: true }).notNull().defaultNow(),56    finishedAt: timestamp('finished_at', { withTimezone: true }),57    durationMs: integer('duration_ms'),58    recordsFetched: integer('records_fetched').notNull().default(0),59    recordsCreated: integer('records_created').notNull().default(0),60    recordsUpdated: integer('records_updated').notNull().default(0),61    recordsUnchanged: integer('records_unchanged').notNull().default(0),62    recordsRejected: integer('records_rejected').notNull().default(0),63    httpRequests: integer('http_requests').notNull().default(0),64    httpFailures: integer('http_failures').notNull().default(0),65    rateLimitEvents: integer('rate_limit_events').notNull().default(0),66    validationFailures: integer('validation_failures').notNull().default(0),67    schemaDrift: jsonb('schema_drift').$type<unknown[]>().notNull().default([]),68    cursorBefore: jsonb('cursor_before'),69    cursorAfter: jsonb('cursor_after'),70    error: text('error'),71    log: jsonb('log').$type<Array<{ t: string; level: string; msg: string }>>().notNull().default([]),72    datasetVersion: text('dataset_version'),73    anomaly: text('anomaly'), // set when a destructive update was refused (CLAUDE.md §171)74  },75  (t) => [index('ingest_runs_connector_idx').on(t.connectorId, t.startedAt)],76);7778export const connectorCursors = pgTable('connector_cursors', {79  connectorId: text('connector_id').primaryKey(),80  cursor: jsonb('cursor').$type<Record<string, unknown>>().notNull().default({}),81  lastSuccessAt: timestamp('last_success_at', { withTimezone: true }),82  lastAttemptAt: timestamp('last_attempt_at', { withTimezone: true }),83  paused: boolean('paused').notNull().default(false),84  health: text('health').notNull().default('unknown'), // healthy | degraded | failing | review | awaiting_credentials | unknown85  healthDetail: text('health_detail'),86  updatedAt: updatedAt(),87});8889/** Schema-drift detection: observed field names/types per connector entity (CLAUDE.md §25). */90export const connectorFieldStats = pgTable(91  'connector_field_stats',92  {93    id: bigserial('id', { mode: 'number' }).primaryKey(),94    connectorId: text('connector_id').notNull(),95    entity: text('entity').notNull(),96    field: text('field').notNull(),97    types: text('types').array().notNull().default([]),98    seenCount: integer('seen_count').notNull().default(0),99    nullCount: integer('null_count').notNull().default(0),100    firstSeenRun: text('first_seen_run'),101    lastSeenRun: text('last_seen_run'),102    updatedAt: updatedAt(),103  },104  (t) => [uniqueIndex('connector_field_stats_uq').on(t.connectorId, t.entity, t.field)],105);106107/** Source-native records (idempotency key = source + entity + source record id; CLAUDE.md §91-92). */108export const sourceRecords = pgTable(109  'source_records',110  {111    id: bigserial('id', { mode: 'number' }).primaryKey(),112    sourceId: ciId('source_id').notNull(),113    entityKind: text('entity_kind').notNull(),114    sourceRecordId: text('source_record_id').notNull(),115    payloadHash: text('payload_hash').notNull(),116    rawPath: text('raw_path'), // data lake path /raw/{source}/{date}/{entity}/{id}.json.gz117    status: text('status').notNull().default('active'), // active | deprecated | retracted | withdrawn | source_missing118    firstSeenRun: text('first_seen_run'),119    lastSeenRun: text('last_seen_run'),120    retrievedAt: timestamp('retrieved_at', { withTimezone: true }).notNull().defaultNow(),121    sourceUpdatedAt: timestamp('source_updated_at', { withTimezone: true }),122    canonicalType: text('canonical_type'),123    canonicalId: text('canonical_id'),124    createdAt: createdAt(),125    updatedAt: updatedAt(),126  },127  (t) => [128    uniqueIndex('source_records_uq').on(t.sourceId, t.entityKind, t.sourceRecordId),129    index('source_records_canonical_idx').on(t.canonicalType, t.canonicalId),130  ],131);132133/** Provenance record (CLAUDE.md §2). Referenced by observations, edges, descriptions, rankings. */134export const provenance = pgTable(135  'provenance',136  {137    id: bigserial('id', { mode: 'number' }).primaryKey(),138    publicId: ciId('public_id'), // CI-PROV-… (minted lazily when exposed)139    sourceId: ciId('source_id').notNull(),140    sourceRecordId: text('source_record_id'),141    sourceUrl: text('source_url'),142    dataset: text('dataset'),143    datasetVersion: text('dataset_version'),144    publicationId: ciId('publication_id'),145    pmid: text('pmid'),146    doi: text('doi'),147    retrievedAt: timestamp('retrieved_at', { withTimezone: true }).notNull(),148    publishedAt: text('published_at'),149    updatedAtSource: text('updated_at_source'),150    geography: text('geography'),151    population: text('population'),152    cohortSize: integer('cohort_size'),153    methodology: text('methodology'),154    evidenceType: text('evidence_type').notNull(),155    accessLevel: text('access_level').notNull().default('open'),156    confidence: real('confidence'),157    license: text('license'),158    ingestRunId: text('ingest_run_id'),159    createdAt: createdAt(),160  },161  (t) => [index('provenance_source_idx').on(t.sourceId, t.sourceRecordId), index('provenance_pmid_idx').on(t.pmid)],162);163164/** Unknown disease/drug labels are never discarded (CLAUDE.md §222). */165export const unresolvedLabels = pgTable(166  'unresolved_labels',167  {168    id: bigserial('id', { mode: 'number' }).primaryKey(),169    sourceId: ciId('source_id').notNull(),170    entityKind: text('entity_kind').notNull(), // cancer | drug | gene | biomarker171    sourceText: text('source_text').notNull(),172    normalized: text('normalized').notNull(),173    context: jsonb('context').$type<Record<string, unknown>>().notNull().default({}),174    count: integer('count').notNull().default(1),175    status: text('status').notNull().default('open'), // open | mapped | rejected | ignored176    suggestedId: text('suggested_id'),177    suggestedMatchType: text('suggested_match_type'),178    suggestedScore: real('suggested_score'),179    resolvedId: text('resolved_id'),180    resolvedBy: text('resolved_by'),181    createdAt: createdAt(),182    updatedAt: updatedAt(),183  },184  (t) => [uniqueIndex('unresolved_labels_uq').on(t.sourceId, t.entityKind, t.normalized), index('unresolved_labels_count_idx').on(t.status, t.count)],185);186187/** Entity change history (CLAUDE.md §96). */188export const changeEvents = pgTable(189  'change_events',190  {191    id: bigserial('id', { mode: 'number' }).primaryKey(),192    entityType: text('entity_type').notNull(),193    entityId: text('entity_id').notNull(),194    kind: text('kind').notNull(), // created | updated | trial_added | approval_added | ranking_changed | merged | deprecated …195    summary: text('summary').notNull(),196    before: jsonb('before'),197    after: jsonb('after'),198    ingestRunId: text('ingest_run_id'),199    createdAt: createdAt(),200  },201  (t) => [index('change_events_entity_idx').on(t.entityType, t.entityId, t.createdAt)],202);203204/** Curator/admin audit log (CLAUDE.md §348). */205export const auditLog = pgTable('audit_log', {206  id: bigserial('id', { mode: 'number' }).primaryKey(),207  actor: text('actor').notNull(),208  action: text('action').notNull(),209  entityType: text('entity_type'),210  entityId: text('entity_id'),211  before: jsonb('before'),212  after: jsonb('after'),213  reason: text('reason'),214  createdAt: createdAt(),215});216217/** Entity merge queue + audit (CLAUDE.md §70). Reversible. */218export const entityMerges = pgTable('entity_merges', {219  id: bigserial('id', { mode: 'number' }).primaryKey(),220  entityType: text('entity_type').notNull(),221  keepId: text('keep_id').notNull(),222  mergeId: text('merge_id').notNull(),223  evidence: jsonb('evidence').$type<Record<string, unknown>>().notNull().default({}),224  status: text('status').notNull().default('proposed'), // proposed | merged | kept_separate | reverted225  decidedBy: text('decided_by'),226  decidedAt: timestamp('decided_at', { withTimezone: true }),227  createdAt: createdAt(),228});229