FHIR database schema
How to store FHIR resources: normalised tables vs JSON with search indexes vs graph, the PostgreSQL schema Anpheros runs (resources, history, typed indexes, projections) and the rules that matter.
A FHIR database stores HL7 FHIR resources so they can be read, searched and versioned through the FHIR API; the most common schema keeps each resource as JSON and fills separate index tables for the search parameters, instead of spreading every element across normalised relational tables. This page explains the three usual designs, the schema Anpheros runs in PostgreSQL, and when you should not build one yourself.
Three ways to store FHIR
| Normalised tables | JSON + search indexes | Graph database | |
|---|---|---|---|
| Stores any valid resource unchanged | — | ✓ | ✓ |
| Standard FHIR search on any parameter | ◐ | ✓ | ◐ |
| New fields need a schema migration | ✓ | — | — |
| Easy SQL reporting | ✓ | ◐ | — |
| Used by mainstream FHIR servers | ◐ | ✓ | — |
◐ = possible with extra work. HAPI FHIR and Anpheros both keep the resource as JSON and index search parameters separately.
FHIR resources are deep, optional and extensible: an Observation alone has dozens of elements, choice types (value[x]), repeating codings and extensions. Normalising that into relational columns means a migration whenever a profile adds a field. Keeping the resource as JSON stores anything valid unchanged; the index tables make it searchable.
The Anpheros schema
- APIFHIR R4 REST and the simpler /v1 REST API
- Current resourcesOne row per resource: type, id, patient, version, the full FHIR JSON (JSONB)
- Search indexesTyped tables filled on every write: token, string, date, reference, quantity
- HistoryEvery version of every resource, never overwritten
- ProjectionsFlat tables for the common REST views: observations, conditions, medications
- PostgreSQLOne database per environment; sandbox and production are separate
| Table | One row per | Key columns |
|---|---|---|
fhir_resources |
current version of a resource | resource_type, id, patient_id, version, json (the full resource), last_updated, deleted, plus provenance columns |
fhir_history |
every version ever written | resource_type, id, version, json, last_updated |
idx_token |
coded or identifier search value | system, code — for code, category, identifier, status |
idx_string |
text search value | normalised text — for names and free text |
idx_date |
date search value | start and end of the range — for date, onset-date, birthdate |
idx_reference |
reference search value | target type and id — for patient, subject, encounter |
idx_quantity |
quantity search value | value, system, unit — for value-quantity |
proj_observations, proj_conditions, proj_medications |
resource, flattened | the columns the simple REST API filters on |
On every write the platform validates the resource, writes the new version to fhir_resources and fhir_history, replaces its index rows and projections, and records Provenance. A search such as Observation?patient=…&code=http://loinc.org|4548-4&date=ge2026-01-01 becomes joins on idx_reference, idx_token and idx_date; the result returns the stored JSON as it was written.
Design rules that matter
- Index only declared search parameters, and refuse unknown ones with
400rather than silently ignoring them. - Never update in place without history: corrections must keep what was known before.
- Soft delete: a deleted resource disappears from reads and searches but its versions stay for audit.
- Patient compartment column: storing
patient_idon every resource makes$everything, consent filters and per-patient isolation cheap. - Validate before storing: invalid JSON in the store breaks every reader later.
Frequently asked questions
What is the best database for FHIR?
Most production FHIR servers use a relational database such as PostgreSQL with the resource stored as JSON (JSONB) and search parameters indexed in separate tables. It combines flexible storage with fast, standard search.
Should I normalise FHIR resources into relational tables?
Usually not for the primary store. Normalised tables suit reporting; keep them as projections built from the stored resources, as Anpheros does for its simpler REST API.
How does HAPI FHIR store resources?
HAPI FHIR's JPA server also stores each resource version as serialised JSON and keeps search parameters in separate index tables. The Anpheros design follows the same principle.
Do I need to build a FHIR database myself?
Only if you need full control of profiles and resource types. Otherwise a managed FHIR platform gives you storage, search, history, consent and audit without running the schema.