CancerIndex — Data model
Source of truth: packages/database/src/schema/*.ts (Drizzle, casing: 'snake_case'). This document
explains the purpose of each table, its key columns and the modelling rules behind it. Column names
below are the database names (snake_case); the API returns camelCase.
1. Identifier namespaces
Public identifiers are CI-<NS>-00000001 (8+ digits, zero-padded), minted from per-namespace
counters in id_sequences by mintId() — never reused, never database integers (CLAUDE.md §6).
| Namespace | Entity | Table |
|---|---|---|
CI-CAN |
cancer entity | cancers |
CI-GENE |
gene | genes |
CI-VAR |
variant | variants |
CI-DRUG |
drug | drugs |
CI-TRIAL |
clinical trial | clinical_trials |
CI-PUB |
publication | publications |
CI-BIO |
biomarker | biomarkers |
CI-STUDY |
genomic cohort / study | genomic_cohorts |
CI-METRIC |
metric definition | metric_definitions |
CI-SOURCE |
source | sources |
CI-TRT |
treatment / regimen | treatment_regimens |
CI-ANAT |
anatomical site | anatomical_sites |
CI-GEO |
geography | geographies |
CI-PROV |
provenance (minted lazily when exposed) | provenance.public_id |
CI-ORG, CI-EDGE, CI-RANK |
reserved | — |
Other public references: slugs (cancers.slug, drugs.slug, variants.slug), HGNC symbols,
NCT ids and PMIDs. Ingest runs are ING-<CONNECTOR>-YYYYMMDD-000001.
2. Registry and operations
| Table | Purpose | Key columns |
|---|---|---|
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) |
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 |
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 |
connector_field_stats |
Observed field names/types per entity for schema-drift detection. | connector_id, entity, field, types[], seen_count, null_count |
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 |
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 |
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 |
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 |
audit_log |
Every admin/curator mutation. | actor, action, entity_type, entity_id, before, after, reason |
entity_merges |
Reversible merge queue. | entity_type, keep_id, merge_id, evidence, status (proposed/merged/kept_separate/reverted) |
api_keys |
Public API keys: hash only. | key_hash (sha256), prefix, tier, rate_limit_per_minute, active, last_used_at |
id_sequences |
Per-namespace counters for CI-* ids. |
namespace, next |
3. Cancer ontology (canonical)
| Table | Purpose | Key columns |
|---|---|---|
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[] |
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 |
cancer_hierarchy |
Multi-dimensional hierarchy: several trees coexist. | parent_id, child_id, hierarchy_type (ncit/oncotree/anatomical/histological/molecular/who/icd/seer), source_id |
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 |
anatomical_sites / cancer_anatomy |
Anatomy dimension (NCIt/UBERON) and cancer→site relations (primary/metastatic). |
ncit_code, uberon_id, parent_id, system; relation |
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 |
cohort_definitions |
Attribute combinations that are not taxonomy nodes (stage, biomarkers). | cancer_id, biomarker_ids[], variant_ids[], stage, attributes |
Qualified disease states (stage, recurrent, metastatic, laterality…) are not canonical cancers; the
rules are in packages/ontology/src/qualifiers.ts. The top-level ranking set
(packages/ontology/src/top-level.ts) maps GLOBOCAN/ICD-10 site groups to NCIt anchors.
4. Genes, variants, biomarkers, cohorts
| Table | Purpose | Key columns |
|---|---|---|
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 |
gene_aliases |
Previous/alias symbols and names. | alias, alias_type (prev_symbol/alias_symbol/prev_name/alias_name) |
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[] |
variant_aliases |
Alternative names. | alias, source_id |
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 |
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) |
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 |
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 |
entity_embeddings |
Vectors with model + dimensions recorded (pgvector). | entity_type, entity_id, model, dimensions, text_hash, embedding |
5. Therapies and regulatory status
| Table | Purpose | Key columns |
|---|---|---|
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 |
drug_aliases |
Generic/brand/development code/salt/synonym. | alias, normalized, alias_type, source_id |
treatment_regimens |
Combinations and modalities. | component_drug_ids[], modality |
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 |
6. Clinical trials
| Table | Purpose | Key columns |
|---|---|---|
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 |
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 |
trial_interventions |
Intervention → drug mapping. | trial_id, name, normalized, intervention_type, drug_id, match_type |
trial_locations |
Sites (facility, city, state, country, status, lat/lng). | trial_id, country, … |
trial_pulse |
Derived daily new-trial counts per cancer/phase. | day, cancer_id, phase, new_trials |
Trials count for a cancer and all its ancestors: a trial mapped to "Lung Adenocarcinoma" also
counts for "Lung Cancer". Descendant traversal is a recursive CTE over cancer_hierarchy, depth ≤ 12,
identical in the API (apps/api/src/lib/descendants.ts) and the counters (packages/ranking).
7. Literature
| Table | Purpose | Key columns |
|---|---|---|
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 |
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 |
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 |
8. Evidence and knowledge graph
| Table | Purpose | Key columns |
|---|---|---|
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 |
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 |
risk_factors |
Risk factors and carcinogens as first-class entities. | slug, name, kind, classification_authority, classification |
Only accepted CIViC items (status = 'ACCEPTED') feed counters and rankings; the API separates
evidence by cancer context and never pools levels across diseases.
9. Time-aware observations
| Table | Purpose | Key columns |
|---|---|---|
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 |
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 |
Rates standardised to different standard populations are never compared in one ranking; the
standard_population column is part of the scope.
10. Derived counters and rankings
| Table | Purpose | Key columns |
|---|---|---|
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 |
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 |
ranking_snapshots |
One metric × one scope × one formula version at one time. | metric_id, metric_slug, scope_key (`geo=… |
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 |
ai_answers |
Cached AI syntheses (Phase 5). | kind, subject_id, question_hash, answer, model, prompt_version, source_snapshot, data_as_of |
Note on column naming: entity_counters.computed_at, literature_counts.computed_at and
trial_pulse.computed_at are declared with the shared updatedAt() helper, so their database
column is updated_at. Raw SQL must use updated_at; the API exposes it as computedAt.
11. Provenance model in one picture
text
rankings.inputs.observationId ─▶ epidemiology_observations.provenance_id ─▶ provenance ─▶ source_records (payload_hash, raw_path) ─▶ data/raw/*.jsonl.gz
└▶ sources (license, attribution)traceValue(db, table, id) (packages/ranking/src/trace.ts, exposed at
GET /v1/admin/trace/:table/:id and inside GET /v1/rankings/:metric/:cancerId/explain) returns
that chain for rankings, epidemiology_observations, survival_observations,
cancer_gene_frequencies and literature_counts.