spb/cancerindex

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