spb/cancerindex

1# CancerIndex — Data model23Source of truth: `packages/database/src/schema/*.ts` (Drizzle, `casing: 'snake_case'`). This document4explains the purpose of each table, its key columns and the modelling rules behind it. Column names5below are the database names (snake_case); the API returns camelCase.67## 1. Identifier namespaces89Public identifiers are `CI-<NS>-00000001` (8+ digits, zero-padded), minted from per-namespace10counters in `id_sequences` by `mintId()` — never reused, never database integers (CLAUDE.md §6).1112| Namespace | Entity | Table |13|---|---|---|14| `CI-CAN` | cancer entity | `cancers` |15| `CI-GENE` | gene | `genes` |16| `CI-VAR` | variant | `variants` |17| `CI-DRUG` | drug | `drugs` |18| `CI-TRIAL` | clinical trial | `clinical_trials` |19| `CI-PUB` | publication | `publications` |20| `CI-BIO` | biomarker | `biomarkers` |21| `CI-STUDY` | genomic cohort / study | `genomic_cohorts` |22| `CI-METRIC` | metric definition | `metric_definitions` |23| `CI-SOURCE` | source | `sources` |24| `CI-TRT` | treatment / regimen | `treatment_regimens` |25| `CI-ANAT` | anatomical site | `anatomical_sites` |26| `CI-GEO` | geography | `geographies` |27| `CI-PROV` | provenance (minted lazily when exposed) | `provenance.public_id` |28| `CI-ORG`, `CI-EDGE`, `CI-RANK` | reserved | — |2930Other public references: slugs (`cancers.slug`, `drugs.slug`, `variants.slug`), HGNC symbols,31`NCT` ids and PMIDs. Ingest runs are `ING-<CONNECTOR>-YYYYMMDD-000001`.3233## 2. Registry and operations3435| Table | Purpose | Key columns |36|---|---|---|37| `sources` | Source + license registry, seeded from connector manifests (`pnpm cix sources:sync`). Drives `/sources`. | `slug` (= connector id), `category`, `access_type`, `access_auth`, `license`, `license_status` (review/approved/restricted/blocked), `commercial_use`, `redistribution`, `attribution`, `approved_for_production`, `status` (planned/active/paused/degraded/awaiting_credentials/review/retired), `tier`, `manifest` (jsonb copy) |38| `ingest_runs` | One row per connector execution with counters, HTTP stats, schema drift, cursors, log and anomaly flag. | `id`, `connector_id`, `mode` (full/incremental/backfill/dry_run/probe), `status` (running/succeeded/failed/partial/aborted), `records_*`, `http_*`, `schema_drift`, `cursor_before/after`, `log`, `dataset_version`, `anomaly` |39| `connector_cursors` | Restart cursor and health per connector. | `connector_id`, `cursor` (jsonb), `last_success_at`, `paused`, `health` (healthy/degraded/failing/review/awaiting_credentials/unknown), `health_detail` |40| `connector_field_stats` | Observed field names/types per entity for schema-drift detection. | `connector_id`, `entity`, `field`, `types[]`, `seen_count`, `null_count` |41| `source_records` | RAW layer index: one row per source-native record, idempotency key `(source_id, entity_kind, source_record_id)`. | `payload_hash` (sha256), `raw_path` (lake file), `status` (active/deprecated/retracted/withdrawn/source_missing), `first_seen_run`, `last_seen_run`, `canonical_type`, `canonical_id` |42| `provenance` | The provenance record every scientific value points to. | `source_id`, `source_record_id`, `source_url`, `dataset`, `dataset_version`, `pmid`, `doi`, `retrieved_at`, `published_at`, `geography`, `population`, `cohort_size`, `methodology`, `evidence_type` (registry/clinical_trial/…/computed), `access_level`, `confidence`, `license`, `ingest_run_id` |43| `unresolved_labels` | Curation queue: labels no connector could reconcile (never dropped). | `source_id`, `entity_kind`, `source_text`, `normalized`, `count`, `status` (open/mapped/rejected/ignored), `suggested_id`, `suggested_match_type`, `suggested_score`, `resolved_id`, `resolved_by` |44| `change_events` | Entity change history feeding "What changed". | `entity_type`, `entity_id`, `kind` (created/updated/trial_added/approval_added/ranking_changed/merged/deprecated/alias_added), `summary`, `before`, `after`, `ingest_run_id` |45| `audit_log` | Every admin/curator mutation. | `actor`, `action`, `entity_type`, `entity_id`, `before`, `after`, `reason` |46| `entity_merges` | Reversible merge queue. | `entity_type`, `keep_id`, `merge_id`, `evidence`, `status` (proposed/merged/kept_separate/reverted) |47| `api_keys` | Public API keys: hash only. | `key_hash` (sha256), `prefix`, `tier`, `rate_limit_per_minute`, `active`, `last_used_at` |48| `id_sequences` | Per-namespace counters for `CI-*` ids. | `namespace`, `next` |4950## 3. Cancer ontology (canonical)5152| Table | Purpose | Key columns |53|---|---|---|54| `cancers` | One row per recognised disease concept (never per alias). | `id`, `slug`, `canonical_name`, `short_name`, `entity_type` (cancer/cancer_family/histology/subtype/molecular_subtype/hematologic_malignancy/precursor_condition/other), `malignant`, `solid_tumor`, `hematologic`, `pediatric_relevant`, `rare_cancer` (null = unknown), `top_level` (member of the mutually exclusive ranking set), `description` + `description_provenance_id`, `primary_ncit_code`, `primary_oncotree_code`, `depth`, `status` (active/deprecated/merged), `merged_into`, `classification_version`, `semantic_types[]` |55| `cancer_aliases` | Synonyms with normalised form used for reconciliation. | `cancer_id`, `alias`, `normalized`, `alias_type` (preferred/synonym/abbreviation/historical/deprecated/display), `source_id`, `source_terminology`, `language` |56| `cancer_hierarchy` | Multi-dimensional hierarchy: several trees coexist. | `parent_id`, `child_id`, `hierarchy_type` (ncit/oncotree/anatomical/histological/molecular/who/icd/seer), `source_id` |57| `cancer_codes` | Cross-reference codes, searchable (never buried in JSON). | `cancer_id`, `system` (ncit/icd10/icd10cm/icdo_topography/icdo_morphology/doid/oncotree/umls/mesh/mondo/seer_site/efo/orphanet/gdc_project), `code`, `match_type`, `source_id`, `valid_from`, `valid_to` |58| `anatomical_sites` / `cancer_anatomy` | Anatomy dimension (NCIt/UBERON) and cancer→site relations (`primary`/`metastatic`). | `ncit_code`, `uberon_id`, `parent_id`, `system`; `relation` |59| `geographies` | Canonical geography for observations and ranking scopes. | `slug`, `name`, `kind` (world/region/who_region/country/subdivision), `iso2`, `iso3`, `parent_id`, `who_region`, `population`, `population_year` |60| `cohort_definitions` | Attribute combinations that are not taxonomy nodes (stage, biomarkers). | `cancer_id`, `biomarker_ids[]`, `variant_ids[]`, `stage`, `attributes` |6162Qualified disease states (stage, recurrent, metastatic, laterality…) are not canonical cancers; the63rules are in `packages/ontology/src/qualifiers.ts`. The top-level ranking set64(`packages/ontology/src/top-level.ts`) maps GLOBOCAN/ICD-10 site groups to NCIt anchors.6566## 4. Genes, variants, biomarkers, cohorts6768| Table | Purpose | Key columns |69|---|---|---|70| `genes` | HGNC is authoritative for symbols. | `hgnc_id`, `symbol`, `name`, `locus_type`, `location`, `chromosome`, `ensembl_gene_id`, `ncbi_gene_id`, `omim_ids[]`, `uniprot_ids[]`, `prev_symbols[]`, `alias_symbols[]`, `status`, `is_cancer_gene` (derived), `civic_gene_id` |71| `gene_aliases` | Previous/alias symbols and names. | `alias`, `alias_type` (prev_symbol/alias_symbol/prev_name/alias_name) |72| `variants` | Coordinates always carry an assembly; original + normalised kept. | `slug`, `gene_id`, `gene_symbol`, `name`, `variant_type` (SO-style), `hgvs_g/c/p`, `assembly` (GRCh37/GRCh38), `chromosome`, `start`, `end`, `reference_bases`, `alternate_bases`, `coordinates` (per-assembly list), `clinvar_variation_id`, `civic_variant_id`, `dbsnp_ids[]`, `fusion_partners[]` |73| `variant_aliases` | Alternative names. | `alias`, `source_id` |74| `variant_clinical_significance` | ClinVar interpretations kept structured (never flattened). | `clinvar_variation_id`, `clinical_significance`, `review_status`, `star_rating`, `last_evaluated`, `conditions[]`, `condition_cancer_ids[]`, `origin_simple`, `number_submitters`, `provenance_id` |75| `biomarkers` | First-class biomarkers with assay/threshold metadata. | `kind` (gene_mutation/protein_expression/hormone_receptor/immune_marker/msi/tmb/hrd/ctdna/…), `gene_id`, `ncit_code`, `measurement` (jsonb) |76| `genomic_cohorts` | Studies (GDC projects…), original study ids preserved. | `source_id`, `study_id`, `name`, `program`, `primary_sites[]`, `disease_types[]`, `cancer_id`, `cancer_match_type`, `case_count`, `cases_with_ssm` (denominator), `data_release`, `access_level`, `provenance_id` |77| `cancer_gene_frequencies` | Alteration frequency per cohort — denominator mandatory. | `cohort_id`, `cancer_id`, `gene_id`, `gene_symbol`, `alteration_type` (ssm/cnv_gain/cnv_loss/fusion), `cases_affected`, `cases_profiled`, `frequency`, `rank`, `data_release`, `provenance_id` |78| `entity_embeddings` | Vectors with model + dimensions recorded (pgvector). | `entity_type`, `entity_id`, `model`, `dimensions`, `text_hash`, `embedding` |7980## 5. Therapies and regulatory status8182| Table | Purpose | Key columns |83|---|---|---|84| `drugs` | Generic/INN preferred; brands are aliases, never separate molecules. | `slug`, `name`, `kind`, `ncit_code`, `chembl_id`, `civic_therapy_id`, `drugbank_id`, `pubchem_cid`, `unii`, `mechanism`, `target_gene_ids[]`, `development_status` |85| `drug_aliases` | Generic/brand/development code/salt/synonym. | `alias`, `normalized`, `alias_type`, `source_id` |86| `treatment_regimens` | Combinations and modalities. | `component_drug_ids[]`, `modality` |87| `drug_approvals` | Country-aware regulatory status — never a bare `approved=true`. | `drug_id`, `cancer_id`, `biomarker_ids[]`, `tumor_agnostic`, `jurisdiction` (US/CA/EU/UK/AU/JP/CH/OTHER), `authority` (FDA/Health Canada/EMA/…), `indication`, `line_of_therapy`, `disease_stage`, `approval_type`, `accelerated`, `conditional`, `approval_date`, `withdrawal_date`, `status` (approved/conditional/accelerated/withdrawn/superseded), `application_number`, `source_id`, `provenance_id`, `raw` |8889## 6. Clinical trials9091| Table | Purpose | Key columns |92|---|---|---|93| `clinical_trials` | ClinicalTrials.gov studies (v2 API fields). | `nct_id`, `brief_title`, `official_title`, `acronym`, `study_type`, `phases[]`, `overall_status`, `why_stopped`, dates (`start_date`, `primary_completion_date`, `completion_date`, `first_posted_date`, `last_update_posted_date`, `results_first_posted_date`), `has_results`, `enrollment_count/type`, `lead_sponsor`, `lead_sponsor_class`, `collaborators[]`, `conditions[]` (raw), `keywords[]`, `interventions` (jsonb), `arms`, `primary/secondary_outcomes`, `eligibility`, `sex`, `minimum_age`, `maximum_age`, `countries[]`, `locations_count`, `references` (jsonb with PMIDs), `brief_summary`, `is_oncology`, `source_record_id`, `ingest_run_id` |94| `trial_conditions` | Free-text condition → cancer mapping with match type and confidence. | `trial_id`, `condition_text`, `normalized`, `cancer_id` (nullable), `match_type` (default `UNRESOLVED`), `confidence` |95| `trial_interventions` | Intervention → drug mapping. | `trial_id`, `name`, `normalized`, `intervention_type`, `drug_id`, `match_type` |96| `trial_locations` | Sites (facility, city, state, country, status, lat/lng). | `trial_id`, `country`, … |97| `trial_pulse` | Derived daily new-trial counts per cancer/phase. | `day`, `cancer_id`, `phase`, `new_trials` |9899Trials count for a cancer **and all its ancestors**: a trial mapped to "Lung Adenocarcinoma" also100counts for "Lung Cancer". Descendant traversal is a recursive CTE over `cancer_hierarchy`, depth ≤ 12,101identical in the API (`apps/api/src/lib/descendants.ts`) and the counters (`packages/ranking`).102103## 7. Literature104105| Table | Purpose | Key columns |106|---|---|---|107| `publications` | One entity per paper across PubMed/DOI/Europe PMC. | `pmid`, `doi`, `pmcid`, `title`, `abstract` (indexed, displayed truncated), `journal`, `journal_iso`, `pub_date`, `pub_year`, `publication_types[]`, `mesh_terms` (jsonb with major flag + qualifiers), `authors` (jsonb), `language`, `is_preprint`, `retracted`, `retraction_notice`, `nct_ids[]`, `cited_by_count` |108| `publication_entity_edges` | Publication → entity links with extraction method and status. | `publication_id`, `entity_type` (cancer/gene/variant/drug/biomarker/trial), `entity_id`, `method` (mesh/dictionary/registry_reference/civic_curation/ner/llm/curator), `confidence`, `status` (candidate/validated/rejected), `source_id` |109| `literature_counts` | Research activity per cancer per window — the exact PubMed query is stored with the count. | `cancer_id`, `window_key` (all/12m/5y/5y_prior/10y/y2015…), `window_start`, `window_end`, `query`, `count`, `provenance_id` |110111## 8. Evidence and knowledge graph112113| Table | Purpose | Key columns |114|---|---|---|115| `civic_evidence_items` | CIViC evidence items kept in their native structure. | `civic_id`, `molecular_profile_id/name`, `gene_symbols[]`, `gene_ids[]`, `variant_ids[]`, `civic_variant_ids[]`, `disease_name`, `doid`, `cancer_id`, `cancer_match_type`, `therapy_names[]`, `therapy_ids[]`, `therapy_interaction_type`, `evidence_type` (PREDICTIVE/PROGNOSTIC/DIAGNOSTIC/PREDISPOSING/ONCOGENIC/FUNCTIONAL), `evidence_level` (A–E), `evidence_direction` (SUPPORTS/DOES_NOT_SUPPORT), `significance`, `evidence_rating`, `status` (ACCEPTED/SUBMITTED/REJECTED), `pmid`, `source_citation`, `phenotypes[]`, `provenance_id` |116| `knowledge_edges` | Typed edges with mandatory context. | `source_entity_type/id`, `target_entity_type/id`, `relationship_type` (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_OF), `cancer_context_ids[]`, `predictive/prognostic/diagnostic/predisposing`, `direction`, `evidence_level` (source-native, never re-scaled), `evidence_score`, `evidence_category` (observed_data/published_evidence/curated_evidence/regulatory_status/clinical_guideline/computed_metric), `status`, `source_id`, `provenance_ids[]`, `support_count`, `first/last_seen_at` |117| `risk_factors` | Risk factors and carcinogens as first-class entities. | `slug`, `name`, `kind`, `classification_authority`, `classification` |118119Only accepted CIViC items (`status = 'ACCEPTED'`) feed counters and rankings; the API separates120evidence by cancer context and never pools levels across diseases.121122## 9. Time-aware observations123124| Table | Purpose | Key columns |125|---|---|---|126| `epidemiology_observations` | Incidence/mortality/prevalence per cancer × geography × year × sex × age group × metric × source × site definition. Unique on that key — a new year is a new row, never an overwrite. | `year`, `year_end` (multi-year aggregates), `sex` (all/male/female), `age_group`, `metric` (incidence_count/incidence_rate/as_incidence_rate/mortality_count/mortality_rate/as_mortality_rate/prevalence/prevalence_5y), `value`, `unit` (count/per_100k), `lower_ci`, `upper_ci`, `standard_population`, `estimate_type` (observed/estimated/projected), `site_definition` (source's own site label / ICD range), `source_id`, `provenance_id` |127| `survival_observations` | Survival with mandatory context. | `geography_id`, `stage`, `staging_system`, `sex`, `age_group`, `diagnosis_period`, `survival_type` (overall/relative/cause_specific/progression_free/disease_free/net), `duration_months`, `probability` (0..1), `median_months`, `cohort_size`, `lower_ci`, `upper_ci`, `method`, `source_id`, `provenance_id` |128129Rates standardised to different standard populations are never compared in one ranking; the130`standard_population` column is part of the scope.131132## 10. Derived counters and rankings133134| Table | Purpose | Key columns |135|---|---|---|136| `metric_definitions` | Versioned metric catalog (seeded from `packages/database/src/seed-data/metrics.ts`). | `slug`, `name`, `description`, `formula`, `formula_version`, `unit`, `higher_is_worse`, `aggregation`, `valid_dimensions[]`, `source_slugs[]`, `category`, `eligibility` (jsonb), `experimental` |137| `entity_counters` | Deterministic counters per entity, fully rebuilt by `refreshCounters()`. | `entity_type`, `entity_id`, `trial_count`, `active_trial_count`, `recruiting_trial_count`, `phase3_trial_count`, `publication_count(_5y/_12m)`, `gene_count`, `variant_count`, `drug_count`, `approved_drug_count`, `evidence_count`, `cohort_count`, `subtype_count`, `descendant_count`, `epidemiology_obs_count`, `survival_obs_count`, `completeness` (jsonb 0/1 per section), `updated_at` |138| `ranking_snapshots` | One metric × one scope × one formula version at one time. | `metric_id`, `metric_slug`, `scope_key` (`geo=…|sex=…|age=…|year=…|level=…`), `geography`, `sex`, `age_group`, `year`, `entity_level` (top/all), `formula_version`, `eligible_entities`, `inputs_hash`, `source_ids[]`, `is_current`, `generated_at` |139| `rankings` | Rows of a snapshot with lineage. | `snapshot_id`, `cancer_id`, `rank` (competition ranking 1,2,2,4), `eligible_entities`, `percentile`, `value`, `unit`, `confidence` (HIGH/MEDIUM/LOW/INSUFFICIENT_DATA), `inputs` (observation ids, counters, formula inputs), `breakdown` (composite components, unused in Phase 1), `previous_rank` |140| `ai_answers` | Cached AI syntheses (Phase 5). | `kind`, `subject_id`, `question_hash`, `answer`, `model`, `prompt_version`, `source_snapshot`, `data_as_of` |141142Note on column naming: `entity_counters.computed_at`, `literature_counts.computed_at` and143`trial_pulse.computed_at` are declared with the shared `updatedAt()` helper, so their **database144column is `updated_at`**. Raw SQL must use `updated_at`; the API exposes it as `computedAt`.145146## 11. Provenance model in one picture147148```149rankings.inputs.observationId ─▶ epidemiology_observations.provenance_id ─▶ provenance ─▶ source_records (payload_hash, raw_path) ─▶ data/raw/*.jsonl.gz150                                                                           └▶ sources (license, attribution)151```152153`traceValue(db, table, id)` (`packages/ranking/src/trace.ts`, exposed at154`GET /v1/admin/trace/:table/:id` and inside `GET /v1/rankings/:metric/:cancerId/explain`) returns155that chain for `rankings`, `epidemiology_observations`, `survival_observations`,156`cancer_gene_frequencies` and `literature_counts`.157