Architecting Care Pathway Digitization Software on FHIR R5 and PostgreSQL
Most clinical software projects start with an application-specific relational schema, a set of hand-written REST endpoints for appointments and notes, and a promise to add standards-based interoperability later. Teams usually hit a wall as soon as a clinic needs to connect online booking, pre-visit intake, multidisciplinary encounters, diagnostic results, and between-visit home plans into one longitudinal record, because every new module invents its own identifiers, authorization checks, and export scripts. In this article we lay out the technical architecture we built for Innovi Care on PostgreSQL 18, HL7 FHIR R5, and graphpg, and explain how to design care pathway digitization software that stays fast to build without sacrificing data integrity or portability.
Regulatory timelines in Europe make that architectural choice harder to postpone. Regulation (EU) 2025/327, which establishes the European Health Data Space (EHDS), begins general application in March 2027, with mandatory cross-border exchange for patient summaries and ePrescriptions starting in 2029 followed by laboratory results, imaging, and discharge reports. Designing the internal data model around open healthcare resources from day one costs far less than translating a bespoke schema into standard clinical bundles five years later.
What care pathway digitization means at the data-model layer
From a software architect's point of view, a care pathway is a state machine linked to a single patient identity across time, locations, and clinical roles. When the pathway breaks, it is usually because the booking table, the questionnaire form store, the clinical note document, and the home exercise app do not share foreign keys or consistent lifecycle states.
Modeling the pathway directly on HL7 FHIR R5 resources gives each clinical step a clear, standard entity:
| Pathway stage | Core FHIR R5 resources | Operational invariants to enforce |
|---|---|---|
| Practitioner availability & booking | PractitionerRole, Location, Schedule, Slot, Appointment | No overlapping active bookings per practitioner or room; idempotent slot transitions |
| Pre-visit mobile intake | Questionnaire, QuestionnaireResponse | Versioned form definitions linked to the patient and upcoming appointment |
| Clinical consultation | Encounter, Procedure, Observation, ClinicalImpression | Consistent patient-to-encounter ownership; idempotent visit start from arrival |
| Orders & diagnostic results | ServiceRequest, Specimen, DiagnosticReport, Binary | Results trace back to originating orders; concurrent note saves do not overwrite newer edits |
| Home care & longitudinal follow-up | CarePlan, Goal, Task, Consent | Patient-visible home activities linked to the encounter; revocable per-relationship access |
Keeping these resources inside one transactional PostgreSQL database means starting a visit from a booked appointment, recording arrival, creating the Encounter, and linking follow-up CarePlan activities happen in atomic database transactions rather than fragile cross-service webhooks.
Hybrid PostgreSQL storage: full FHIR JSONB plus promoted columns
A common objection to storing FHIR resources directly in a relational database is that pure JSONB queries become slow or messy, while fully normalizing every nested FHIR extension into hundreds of join tables turns simple writes into maintenance nightmares.
The pattern we use in Innovi Care, powered by the open-source graphpg library, combines both approaches inside PostgreSQL 18:
- Full resource payload in JSONB. Each resource table keeps the complete FHIR R5 resource in a
payloadJSONB column, checked structurally with thepg_jsonschemaextension. - Promoted columns for hot queries and constraints. Fields the application filters, joins, or constrains frequently, such as
active,gender,birth_date,practitioner_id,patient_id,status, and time ranges, sit as native typed PostgreSQL columns right next topayload. - Database triggers keep both copies synchronized. Updates compare old and new values so changing either the promoted column or the JSONB payload updates the other copy, while conflicting edits in the same write roll back immediately.
For dates, the application also enforces a clear policy: even though FHIR permits partial dates like YYYY or YYYY-MM, storing a native PostgreSQL DATE column normalized to YYYY-MM-DD lets age sorting, recall scheduling, and range indexes run cleanly without string-parsing hacks.
Putting scheduling integrity and privacy rules inside PostgreSQL
Application middleware is a risky place to enforce clinical invariants, because every new endpoint, background job, or coding agent can accidentally bypass a check that lives only in TypeScript or Python. Moving the two highest-risk rules into PostgreSQL closes that gap across every client.
Preventing double bookings with GiST exclusion constraints
In a practice that mixes studio rooms, video consultations, and home visits, two patients can easily submit booking requests for the same window within milliseconds of each other. Instead of relying on a read-then-write check in application code, PostgreSQL's btree_gist exclusion constraint rejects overlapping periods atomically:
ALTER TABLE appointments ADD CONSTRAINT appointments_practitioner_no_overlap
EXCLUDE USING gist (practitioner_id WITH =, period WITH &&)
WHERE (status NOT IN ('cancelled', 'entered-in-error', 'noshow'));
When two requests race for the same slot, PostgreSQL commits the first and raises a constraint violation for the second, which the API maps to a typed SLOT_UNAVAILABLE error so the UI can show the next open window cleanly.
Isolating clinical roles and consent with Row-Level Security
A digitized care pathway connects receptionists, several clinical specialties, patients, and delegated family members to the same platform, which makes authorization boundaries critical.
In our portal architecture, an OIDC identity provider (ZITADEL) issues short-lived JWTs carrying the user's organization and subject claims. The web app acts as a backend-for-frontend (BFF) and forwards the signed-in token to the API engine, which verifies the signature and executes every query inside PostgreSQL under the authenticated role with request.jwt.claims populated.
Row-Level Security (RLS) policies then govern every table directly:
- Signed-out visitors only reach explicit public directory projections (
care_directoryand moderatedpractitioner_reviews) through ananonrole that has zero select permissions onpatients,encounters, orclinical_notes. - Patients read and update their own
Patientresource, view their ownCarePlanactivities and appointments, and manage access permissions, while profile photos and attachments live as FHIRBinaryrecords scoped bysecurityContext. - Reception staff manage schedules and arrivals without reading clinical notes.
- Clinicians open a patient's chart only when an active care relationship or explicit patient approval exists; confidential notes such as psychotherapy records remain isolated to the treating clinician.
These database controls support European GDPR access-minimization requirements and HIPAA technical safeguards, though software controls alone never replace an organization's legal, hosting, and operational compliance program.
Removing CRUD plumbing without losing type safety
A FHIR R5 model spanning more than 170 tables normally forces a small engineering team to spend months writing pagination, filtering, nested resolvers, and N+1 query fixes before a clinician ever sees a screen.
Because graphpg introspects the PostgreSQL catalog directly and serves both GraphQL and OpenAPI REST from one compiled SQL query plan, adding a column or table in a Drizzle migration automatically updates the API schema and the generated TypeScript operations. A nested patient timeline fetching Patient, recent Encounter rows, Observation trends, and active CarePlan items compiles into one SQL statement using lateral joins instead of dozens of network round trips.
We covered the fast verification loop that pairs with this setup in our write-up on building FHIR software with graphpg and coding agents. Running the full migration chain and more than 40 database security and scheduling suites on PGlite (PostgreSQL compiled to WebAssembly) gives engineers and coding agents feedback in seconds during CI.
Drawing an honest boundary for external hospital exchange
One architectural mistake we see often in healthcare consulting is trying to make a clinic's internal product database act directly as a hospital-grade HL7 interface engine.
An internal FHIR R5 PostgreSQL model with JSON Schema checks gives you clean structure, fast UI queries, and straightforward data export. It is not by itself a certified, conformant FHIR server, and HL7 does not endorse it. When a clinic or health system needs live hospital exchange, national switch connectivity, external Laboratory Information System (LIS) result transport, or PACS/DICOMweb archive integration, treat that boundary as a dedicated integration tier:
- Legacy hospital feeds (HL7 v2 over MLLP, ADT/ORM/ORU messages): Place a dedicated integration engine such as Mirth Connect (or the community-maintained Open Integration Engine) at the perimeter to normalize, route, audit, and retry hospital traffic before mapping into your core database.
- Conformant FHIR exchange and EHDS gateways: Keep profile validation, national terminology bindings, and audit logging in an explicit exchange adapter rather than leaking regional quirks into every product screen.
- AI and automation workflows: Keep LLMs and agentic helpers behind typed, read-scoped or propose-only boundaries so an AI step never writes directly to clinical or billing records without deterministic validation and clinician review.
For the business and clinical workflow side of this architecture, see our companion guide on care pathway digitization software on Innovi Care. For a first-person look at how ten years across radiology HL7 integrations, enterprise patient portals, and pharma AI shaped these decisions, read Mihai Farcas's essay on mihai.ltd.
Plan your healthcare architecture with Innovi Pro
If you are building a clinical platform, modernizing a legacy EHR, or preparing a European health product for FHIR and EHDS interoperability, getting the database boundaries right early prevents years of rework.
At Innovi Pro we start with a focused software architecture audit or a fixed-fee one-to-two-week scoping engagement that delivers a written architecture blueprint, security and RLS model, integration risk map, and costed roadmap. Book a discovery call to walk through your clinical workflow and technical constraints.