# 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.