Database & schema

The system of record is a single PostgreSQL 16 database. The schema is defined once in api/app/models.py and applied through Alembic migrations. This page describes the tables, how they relate, and how to inspect them directly.

Connecting

The Postgres container publishes port 5432 on the host. Credentials come from deploy/.env (defaults shown — change them for a real deployment).

Setting	Default
Host	localhost
Port	5432
Database	mdms
User	mdms
Password	change-me

Terminal, via the container (no local client needed):

docker exec -it deploy-postgres-1 psql -U mdms -d mdms

Terminal, with a local psql, or any GUI (DBeaver / TablePlus / Postgres.app / pgAdmin):

postgresql://mdms:change-me@localhost:5432/mdms

Useful psql commands: \dt (tables), \d sample (one table), \dv (views), \q (quit).

Entity overview

patient ──1:1── patient_pii            (identifiers, separated for access control)
   │
   │ 1:N
   ▼
 sample ──self─┐                        (parent_sample_id: derivatives)
   │           │
   │ 1:N       └── derivatives
   ▼
observation ──N:1── result_type         (what kind of result this is)
   │
   └──────────N:1── ingest_dataset      (which upload it came from — provenance)

result_type   catalog of result kinds (hr_status, somatic_mutation, …)
ingest_dataset one row per uploaded CSV (deduplicated by sha256)

Two convenience views roll observations up into availability: sample_data_catalog and patient_data_catalog.

Core tables

patient

The cohort roster. One row per study subject.

Column	Type	Notes
id	bigint PK	running integer
study_id	text, unique	human-readable study identifier (e.g. HGSC0001)
created_at	timestamptz

patient_pii

Patient identifiers, kept in a separate table so access can be gated by role. 1:1 with patient. Empty in synthetic datasets.

Column	Type	Notes
patient_id	bigint PK/FK → patient.id
hospital_id, full_name, dob	text / date	only visible to superuser / clinician

sample

A biological sample. Dual identity: a running id and a human-readable name (patientid_phase_site). Samples may be derivatives of other samples (self-reference).

Column	Type	Notes
id	bigint PK
name	text, unique	HGSC0001_diagnosis_ovary
patient_id	bigint FK → patient.id	owner
sample_type	text	fresh_frozen, ffpe, organoid, ascites, plasma
timepoint	text	diagnosis / interval / relapse
anatomical_site, collection_site	text
parent_sample_id	bigint FK → sample.id	set for derivatives
created_at	timestamptz

result_type

Catalog of the kinds of results the system knows about. New codes are auto-registered on first upload.

Column	Type	Notes
id	bigint PK
code	text, unique	hr_status, somatic_mutation, seq_logistics, …
label, category	text	category groups the UI (molecular / qc / logistics / clinical)
json_schema	jsonb	reserved, not currently used
typed_table	text	for the 4 promoted result kinds (see "Slice-4 tables" below), names the typed table that holds them (e.g. somatic_mutation → somatic_variant); NULL for ad-hoc types that only live in observation

observation

The Slice-1 results surface — one row per landed measurement. Carries a scalar value and the full source row as payload (nothing is dropped).

Column	Type	Notes
id	bigint PK
sample_id	bigint FK → sample.id	XOR with patient_id
patient_id	bigint FK → patient.id	subject is a sample or a patient, not both
result_type_id	bigint FK → result_type.id	not null
value_num / value_text / value_bool	—	the primary value (typed)
payload	jsonb	the entire source CSV row
unit, observed_at	text / timestamptz
ingest_dataset_id	bigint FK → ingest_dataset.id	provenance
recorded_at	timestamptz	when it landed

Subject XOR

A CHECK constraint (observation_subject_xor) enforces exactly one of sample_id / patient_id.

ingest_dataset

Provenance: one row per uploaded CSV, deduplicated by sha256 (re-uploading the same file is a no-op).

Column	Type	Notes
id	bigint PK
filename, sha256	text	sha256 is unique
storage_pointer	text	local path or host:path to the raw file
uploaded_by, uploaded_at	text / timestamptz
row_count, mapping, status	int / jsonb / text	the mapping used to interpret the file

Availability views

• sample_data_catalog — per (sample, result_type): count and latest date.
• patient_data_catalog — the same rolled up to the patient (via the sample, or a direct patient observation).

These power the "what data is available?" tables in the UI and the SDK available() call.

Reserved / unused tables

assay, result, artifact were reserved for an assay/result/artifact-linked model that Slice 4 ended up not needing — the four typed tables (somatic_variant, germline_variant, copy_number_segment, hrd_score; see "Slice-4 tables" below) each reference sample and ingest_dataset directly instead. These three tables remain empty and unused; they may be removed or repurposed in a later slice.

Auth tables (managed by Better Auth)

Ovcahub's user, session, account, verification are created and owned by the web app's auth library in the same database. FastAPI reads session + user to validate web sessions and derive roles; it does not write them.

Slice-1.5 tables

publication

Papers linked to cohorts, runs, and projects.

Column	Type	Notes
id	bigint PK
title	text, not null
journal, authors	text
doi, pubmed_id	text	rendered as external links
year	int
readme	text	markdown
owner_id	text → user.id
created_at, updated_at	timestamptz

project

Research projects grouping runs, cohorts, and publications.

Column	Type	Notes
id	bigint PK
code	text, unique	slug (e.g. evolution)
name, description, readme	text
owner_id	text → user.id

cohort

Manual patient groupings. Many-to-many with patient via cohort_member.

Column	Type	Notes
id	bigint PK
name, description, readme	text
owner_id	text → user.id

cohort_member

Patient membership in a cohort. Unique (cohort_id, patient_id).

resource

External file catalogue — reference genomes, annotation databases, etc.

Column	Type	Notes
id	bigint PK
key	text, unique	SDK handle (e.g. hg38)
description, path, sha256	text
filesize	bigint	bytes
owner_id	text → user.id

entity_link

Free cross-links between any two entities. Canonical ordering (alphabetical type) prevents duplicate A↔B / B↔A rows.

Column	Type	Notes
id	bigint PK
src_type, dst_type	text	one of: publication, project, cohort, run, patient, sample
src_id, dst_id	bigint	numeric id in the respective table
created_at	timestamptz

No FK integrity

entity_link uses polymorphic ids — there is no database-level foreign key. Existence of both endpoints is validated at the API layer on insert.

Slice-5: discovery

discovery_index

A denormalized search index over the generic observation registry. Materialized view on Postgres (production), refreshed after every ingest; plain view on SQLite (dev/tests, no FTS/trigram — substring search only).

Column	Type	Notes
id	bigint	synthetic row id (row_number())
subject_type	text	sample or patient
subject_id, subject_label	bigint, text	the sample/patient id and its display name
patient_id, study_id	bigint, text	owning patient (present even for sample-level rows)
sample_name, sample_type, timepoint, anatomical_site	text	null for patient-level rows
result_type_code, result_type_label, category	text	from result_type
source_dataset_id, source_filename	bigint, text	provenance — the ingest_dataset this came from
n	bigint	count of observations of this type for this subject
latest	timestamptz	most recent recorded_at
search_text	text	concatenated human-readable fields + (Postgres only) top-level payload strings, used for FTS

Indexes (Postgres): a GIN index on to_tsvector('english', search_text) for full-text search, a GIN trigram index (pg_trgm) on search_text for fuzzy/substring matching, a unique index on id (required for REFRESH MATERIALIZED VIEW CONCURRENTLY), plus plain indexes on result_type_code, sample_type, timepoint.

Scope

discovery_index indexes the generic observation table only — not any typed result tables a later slice may add. This keeps the view simple; a future slice can extend it if the corpus needs typed-table search.

Refresh: refresh_discovery_index() (in api/app/db.py) runs REFRESH MATERIALIZED VIEW CONCURRENTLY discovery_index on Postgres (no-op on SQLite). It's called automatically after POST /ingest/csv (wrapped in try/except — a refresh failure never breaks an ingest), and can be triggered manually via POST /discover/refresh (superuser only).

Audit slice: application access log

access_log

One row per HTTP request, written by a FastAPI middleware. Complements pgaudit (a Postgres-level extension that logs writes to the server's text log files, not to a table — see the Audit logging page).

Column	Type	Notes
id	bigint PK
ts	timestamptz	when the request happened
user_id, user_email, user_role	text	resolved from the bearer token; null if unauthenticated
via	text	session / apikey / dev / guest
method, path, query	text	the request
status_code	int	the response
ip	text	client IP (X-Forwarded-For or socket address)
entity_type, entity_id	text	best-effort, parsed from the path (e.g. /patients/HGSC0001 → patient, HGSC0001)
pii_access	boolean	true when the request actually returned PII (set by GET /patients/{id})
duration_ms	int	request duration

Indexes on ts, user_id, pii_access, status_code support the audit dashboard's filters.

Pruned to a 90-day retention window via prune_access_log() / POST /audit/prune.

Slice-4 tables: typed rich results + verification/versioning

Four typed result tables (in addition to the generic observation registry). Every one of them, plus observation, carries the same verification columns (verification_status, verified_by, verified_at, created_by) and the same versioning columns (result_uid, version, is_current, superseded_by) — see Verification workflow for the semantics.

somatic_variant

Column	Type	Notes
id	bigint PK
sample_id	bigint FK → sample.id, NOT NULL
result_type_id	bigint FK → result_type.id	catalog code somatic_mutation
ingest_dataset_id	bigint FK → ingest_dataset.id	provenance
gene, hgvs_c, hgvs_p	text
chrom, pos, ref, alt	text/bigint	genomic coordinates
vaf	float	variant allele fraction 0–1
depth	int	read depth
caller	text	e.g. Mutect2
payload	jsonb	any extra columns from the source

germline_variant

Same shape as somatic_variant, plus:

Column	Type	Notes
classification	text	pathogenic / likely_pathogenic / VUS / benign
zygosity	text	het / hom

Somatic and germline variants are separate tables — different clinical meaning, different classification vocabularies.

copy_number_segment

Column	Type	Notes
id	bigint PK
sample_id	bigint FK → sample.id, NOT NULL
chrom, start_pos, end_pos	text/bigint	segment coordinates
gene	text	optional gene label for the segment
copy_number	float	absolute or log2 ratio
cn_type	text	gain / loss / neutral / amplification / deletion

hrd_score

Column	Type	Notes
id	bigint PK
sample_id	bigint FK → sample.id, NOT NULL
score	float	numeric HRD score
status	text	HRD / HRP
method	text	e.g. genomic_scar

observation (extended)

The generic Slice-1 registry gains the same verification + versioning columns described above, so ad-hoc CSV results also participate in the verification workflow (existing rows default to verification_status='draft', version=1, is_current=true, result_uid=NULL).

Verifiable table allow-list

POST /verify only accepts a hardcoded set of table names (somatic_variant, germline_variant, copy_number_segment, hrd_score, observation) — never an arbitrary string — to prevent SQL injection via the table name.