ADR-001 — PostgreSQL as the canonical store
Status: accepted (2026-09-08)
Context
CancerIndex needs one system of record for a heterogeneous but strongly relational domain: a cancer
ontology with several hierarchies, cross-reference codes, time-aware observations keyed by six
dimensions, evidence edges with mandatory context, provenance for every value, and immutable ranking
snapshots. Requirements that drove the choice: strict integrity (unique keys such as
(cancer, geography, year, sex, age, metric, source, site)), recursive hierarchy queries,
aggregation with window functions, full-text and trigram search, vectors for future retrieval, and
an operational footprint a single node can carry.
Alternatives considered: a graph database (Neo4j) for the knowledge graph, a document store for raw payloads, DuckDB/Parquet for analytics.
Decision
PostgreSQL 17 is the canonical store for every layer except the raw lake files. Extensions:
pg_trgm (fuzzy search and curation suggestions), unaccent, vector (pgvector, embeddings with
the model recorded per row). The schema is managed with Drizzle (packages/database, snake_case
casing) and migrations; raw SQL through the sql template is used for aggregates and recursive CTEs.
The job queue (pg-boss) also lives in PostgreSQL (schema pgboss), so the whole platform needs a
single stateful service.
The knowledge graph is modelled relationally (knowledge_edges, cancer_hierarchy) with recursive
CTEs bounded at depth 12 rather than in a graph database: the graph is small (tens of thousands of
edges), the queries are shallow, and integrity constraints and provenance joins matter more than
traversal performance.
Consequences
- One backup, one connection string, transactional writes across layers (a ranking snapshot and its rows are inserted in one transaction).
- Analytical workloads (large VCF/HGVS normalisation, cohort-level recomputation) are out of scope for the canonical store and are planned as Python/DuckDB workers writing back summarised rows (ADR-002).
- Raw payloads are not stored in PostgreSQL (ADR-003); only their hash and path are.
- Operators must size the node for PostgreSQL first (memory, disk for indexes on trials and publications); the deploy manifest asks for ≥ 6 GB RAM and prefers a node with Postgres 17 already installed.