A hospital record is a clinical timeline, not a generic customer ledger. Start with patient identity, encounters, orders, medication dispensing and the staff permissions that determine who may access each record. This blueprint describes engineering choices for review with clinical and security owners; it does not certify regulatory compliance.
Operating workflow and system boundaries
Registration → patient identity resolution → clinical encounter → reviewed orders → pharmacy dispensation → discharge summary. A separate integration worker validates inbound FHIR messages and records acknowledgements without allowing an external payload to silently overwrite a signed clinical note.
Domain-specific PostgreSQL schema
Illustrative design excerpt. Final tables, constraints and security policies depend on discovery and review.
-- Illustrative PostgreSQL model; use synthetic data during development.
CREATE TABLE patients (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
facility_id bigint NOT NULL,
medical_record_number text NOT NULL,
identity_status text NOT NULL CHECK (identity_status IN ('unverified','verified','merged')),
UNIQUE (facility_id, medical_record_number)
);
CREATE TABLE clinical_encounters (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
patient_id bigint NOT NULL REFERENCES patients(id),
clinician_id bigint NOT NULL,
status text NOT NULL CHECK (status IN ('scheduled','in_progress','signed','amended')),
started_at timestamptz NOT NULL,
signed_at timestamptz
);
CREATE TABLE hl7_fhir_messages (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
source_system text NOT NULL,
external_message_id text NOT NULL,
encounter_id bigint REFERENCES clinical_encounters(id),
resource_type text NOT NULL,
validation_status text NOT NULL,
payload_reference text NOT NULL,
UNIQUE (source_system, external_message_id)
);
CREATE TABLE pharmacy_dispensations (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
encounter_id bigint NOT NULL REFERENCES clinical_encounters(id),
prescription_reference text NOT NULL,
medicine_batch text NOT NULL,
quantity numeric(12,3) NOT NULL CHECK (quantity > 0),
pharmacist_id bigint NOT NULL,
dispensed_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE audit_trail (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
actor_id bigint NOT NULL,
actor_role text NOT NULL,
patient_id bigint NOT NULL REFERENCES patients(id),
action text NOT NULL,
purpose_of_access text NOT NULL,
occurred_at timestamptz NOT NULL DEFAULT now()
);Keep sensitive demographics and clinical documents in separately controlled storage; a record identifier is not an access token. Application roles should not update or delete audit events. Record reads, exports, changes, failed access and emergency access with an accountable actor. Facility scoping must be enforced on every query, including background exports. A foreign key alone does not enforce facility isolation.
Failure scenarios and acceptance evidence
- Two registrations with similar names must enter an identity-review queue rather than auto-merge.
- A repeated inbound message must not create a second encounter or prescription.
- Signed notes require an amendment trail; do not replace the original author and signature time.
- A nurse, clinician, pharmacist and billing operator must pass separate positive and negative access tests.
- Rehearse backup restoration and message replay with synthetic patient data before live cutover.
Operational economics with explicit assumptions
Measure registration time, missing-record incidents and pharmacy reconciliation effort against your own baseline. Example planning model: 400 encounters a month × 3 minutes saved = 20 staff hours. At an assumed loaded cost of $12/hour that is $240/month in time capacity, not guaranteed cash savings. Subtract hosting, clinical validation, security operations, support and interface maintenance before calculating payback.
Migration and release review
Import a representative sample into staging and reconcile source identifiers, record counts and exceptions. Run a supervised pilot, record sign-off criteria, rehearse restoration, and agree the cutover owner. The final migration and rollback procedure must fit this operating workflow.
Technical references and project scope
Primary technical reference. Review the relevant software scope or plan an indicative budget. Fixed-milestone billing uses direct milestone invoicing, with optional third-party escrow by agreement for international contracts. Hosting, API fees and ongoing maintenance are scoped separately.