Healthcare (FHIR-style)
File: 05-healthcare-fhir.xdbml · Target: PostgreSQL
A healthcare records schema partially inspired by FHIR resource patterns. Demonstrates named reusable types, recursive types for organizational hierarchy, polymorphic observation values, business_term references to clinical vocabularies (LOINC, SNOMED, ICD-10), and compliance tags (HIPAA, PII).
Source
xdbml
xdbml: 0.3
Project healthcare {
targets: PostgreSQL
Note: '''
A healthcare records schema partially inspired by FHIR resource
patterns. Demonstrates named reusable types for clinical concepts
(CodedConcept, HumanName, ContactPoint), recursive types for
hierarchical structures (organizational units, observation
components), business_term references to clinical vocabularies
(LOINC, SNOMED, ICD-10), and synonyms for natural-language query
resolution.
This is a teaching example, not production-ready. Real clinical
systems require additional concerns out of xDBML scope: audit
trails compliant with HIPAA, fine-grained consent management,
immutable history of every value change, integration with the
full FHIR resource graph, and so on.
'''
}
// ============================================================
// Reusable clinical types
// ============================================================
Type CodedConcept {
Note: '''
A code drawn from a clinical vocabulary, with the vocabulary system
identified by URI and a human-readable display. Inspired by FHIR's
Coding datatype. Used for diagnosis codes, observation codes,
procedure codes, and similar references.
'''
system varchar [not null,
maxLength: 200,
note: 'URI identifying the vocabulary, e.g. "http://snomed.info/sct" for SNOMED CT']
code varchar [not null,
maxLength: 50,
note: 'Code value within the named system']
display varchar [maxLength: 200,
note: 'Human-readable description; redundant with code lookup but improves readability']
system_version varchar [maxLength: 50,
note: 'Optional version identifier of the vocabulary system; needed for time-bound code stability']
}
Type HumanName {
Note: 'A person name with structural components. Western-name-biased; non-Western names use the family/given fields creatively.'
use varchar [enum: ['official', 'usual', 'temp', 'nickname', 'anonymous', 'old', 'maiden'],
note: 'Context in which this name is used; a person may have multiple HumanName instances']
family varchar [maxLength: 100, note: 'Surname / last name']
given array [varchar [maxLength: 100]] [note: 'Given names in order; usually ["First", "Middle"]']
prefix array [varchar] [note: 'Titles preceding the name, e.g. ["Dr.", "Sir"]']
suffix array [varchar] [note: 'Suffixes following the name, e.g. ["MD", "PhD", "Jr."]']
}
Type ContactPoint {
Note: 'A contact method -- phone, email, address, etc. -- for a person or organization.'
system varchar [enum: ['phone', 'email', 'fax', 'pager', 'url', 'sms'],
note: 'Type of contact mechanism']
value varchar [not null,
maxLength: 200,
tags: ['pii', 'contact'],
note: 'The contact identifier; phone number, email address, URL, etc.']
use varchar [enum: ['home', 'work', 'temp', 'old', 'mobile'],
note: 'Context in which this contact is used']
rank int [minimum: 1,
note: 'Preference order when multiple contacts of the same system exist; 1 is preferred']
}
Type Address {
Note: 'Postal address. Internationally flexible; not all fields are populated for every address.'
use varchar [enum: ['home', 'work', 'temp', 'old', 'billing'], note: 'Context of the address']
line array [varchar] [maxItems: 4, note: 'Street address lines; usually 1-3 entries']
city varchar [maxLength: 100]
district varchar [maxLength: 100, note: 'County or equivalent administrative subdivision']
state varchar [maxLength: 100]
postal_code varchar [maxLength: 20]
country varchar [minLength: 2, maxLength: 2, note: 'ISO 3166-1 alpha-2 code']
}
Type OrganizationalUnit {
Note: '''
A unit within a healthcare organization. Recursive: units contain
sub-units (department > division > team). The root unit represents
the organization itself.
'''
id int [pk]
name varchar [not null, maxLength: 200]
unit_type varchar [enum: ['hospital', 'clinic', 'department', 'division', 'team'],
note: 'Hierarchical level of this unit']
sub_units array [child OrganizationalUnit] [note: 'Direct children in the org tree; recursion terminates at leaf units']
}
// ============================================================
// Containers
// ============================================================
Container clinical [type: schema] {
Note: 'Core clinical records: patients, observations, diagnoses, and the relationships between them.'
Entity patients {
Note: '''
A patient. Demographic record only; clinical data lives in
related entities (observations, diagnoses, etc.) keyed by
patient_id.
'''
id int [pk, increment]
medical_record_number varchar [unique,
not null,
maxLength: 50,
tags: ['pii', 'identifier', 'hipaa'],
business_term: 'Medical Record Number (MRN)',
note: 'Hospital-assigned patient identifier; unique within the organization']
names array [name HumanName] [
minItems: 1,
note: 'A patient may have multiple recorded names (official + maiden, for example)'
]
birth_date date [not null,
tags: ['pii', 'hipaa'],
business_term: 'Date of Birth',
note: 'Used for identity verification and clinical calculations like age-based dosing']
gender varchar [enum: ['male', 'female', 'other', 'unknown'],
note: 'Administrative gender; distinct from clinical sex and gender identity']
contact_points array [contact ContactPoint] [note: 'Phone numbers, emails, etc., used to reach the patient']
addresses array [address Address] [note: 'Patient addresses; usually one home address and optionally a work address']
deceased_date date [tags: ['lifecycle'],
note: 'Null for living patients; set on confirmed death']
indexes {
medical_record_number [unique]
birth_date
family_name_lookup [type: btree, name: 'idx_patient_family_name', note: 'Functional index for name search; created via SQL DDL']
}
}
Entity observations {
Note: '''
A clinical observation: lab result, vital sign measurement,
questionnaire response, imaging finding, etc. Each observation
has a code identifying what was measured and a value carrying
the measurement.
'''
id int [pk, increment]
patient_id int [not null,
note: 'References clinical.patients.id; the subject of the observation']
code CodedConcept [note: 'What was observed, e.g. LOINC code 8867-4 for heart rate or 718-7 for hemoglobin']
effective_at timestamp [not null,
granularity: minute,
synonyms: ['observation time', 'measurement time'],
note: 'When the observation was made or the sample was collected; not when entered into the system']
issued_at timestamp [granularity: minute,
note: 'When the result became available; differs from effective_at for lab tests']
status varchar [enum: ['registered', 'preliminary', 'final', 'amended', 'corrected', 'cancelled', 'entered-in-error'],
default: 'preliminary',
business_term: 'Observation Status',
note: 'Lifecycle stage; clinical decisions should be based on "final" or "amended" observations only']
value oneOf {
quantity object {
Note: 'A measured numeric quantity with units, e.g. 72 bpm for heart rate'
value decimal(15,4) [not null, note: 'The numeric measurement']
unit varchar [not null, maxLength: 30, note: 'Unit of measurement, often a UCUM code']
unit_code CodedConcept [note: 'Optional structured reference to the unit in UCUM or similar vocabulary']
}
coded object {
Note: 'A categorical observation, e.g. blood type "A+" or pathogen identification'
concept CodedConcept [not null, note: 'The coded answer']
}
string_value object {
Note: 'A free-text observation, e.g. radiology report narrative or note from clinician'
text varchar [not null, maxLength: 100000, note: 'Free text content; may be very long for narrative reports']
}
boolean_value object {
Note: 'A true/false observation, e.g. "pregnant: yes"'
value boolean [not null]
}
} [discriminator: value_type,
note: 'Different observation types carry different value shapes; the value_type discriminator carries the kind']
indexes {
(patient_id, effective_at)
patient_id
effective_at [note: 'Used by population-level queries that span all patients']
}
}
Entity diagnoses {
Note: 'A diagnosis or condition recorded for a patient. May be active, resolved, or historical.'
id int [pk, increment]
patient_id int [not null, note: 'References clinical.patients.id']
condition_code CodedConcept [note: 'Diagnosis code, typically ICD-10 or SNOMED CT']
onset_date date [note: 'When the condition began; may be approximate or unknown for chronic conditions']
resolved_date date [note: 'When the condition resolved; null for ongoing or chronic conditions']
severity varchar [enum: ['mild', 'moderate', 'severe', 'unknown'],
note: 'Clinical severity assessment; subjective and clinician-dependent']
clinical_status varchar [enum: ['active', 'recurrence', 'relapse', 'inactive', 'remission', 'resolved'],
default: 'active',
business_term: 'Clinical Status']
notes varchar [maxLength: 5000,
tags: ['phi', 'hipaa'],
note: 'Clinician notes about the diagnosis; protected health information']
indexes {
patient_id
(patient_id, clinical_status)
}
}
}
Container administrative [type: schema] {
Note: 'Non-clinical entities: organizations, providers, practitioners, departments, scheduling.'
Entity organizations {
Note: 'Healthcare organizations: hospitals, clinics, practices. The root of the organizational tree.'
id int [pk, increment]
name varchar [not null, maxLength: 200]
npi varchar [unique,
pattern: '^[0-9]{10}$',
tags: ['identifier'],
business_term: 'National Provider Identifier',
note: 'NPI number; required for billing in the United States']
org_tree OrganizationalUnit [note: 'Recursive organizational hierarchy starting at this organization']
primary_address Address
}
Entity practitioners {
Note: 'Individual healthcare providers: physicians, nurses, technicians.'
id int [pk, increment]
organization_id int [not null, note: 'References administrative.organizations.id']
names array [name HumanName] [minItems: 1]
npi varchar [unique,
pattern: '^[0-9]{10}$',
tags: ['identifier'],
business_term: 'National Provider Identifier']
contact_points array [contact ContactPoint]
role varchar [enum: ['physician', 'nurse', 'tech', 'therapist', 'pharmacist', 'admin'],
note: 'Primary role; some practitioners have multiple roles, handled via a separate practitioner_roles table']
}
}
Ref: clinical.observations.patient_id > clinical.patients.id [source: '0..*', target: '1..1', delete: restrict]
Ref: clinical.diagnoses.patient_id > clinical.patients.id [source: '0..*', target: '1..1', delete: restrict]
Ref: administrative.practitioners.organization_id > administrative.organizations.id [source: '1..*', target: '1..1']