When dealing with Protected Health Information (PHI) under HIPAA, or personal medical details under the GDPR, database design is the first line of defense. Standard database conventions fail to satisfy security audits, requiring a shift in how we isolate, encrypt, and audit patient data.
This article outlines the core database patterns utilized at Turntiles to meet strict healthcare governance rules without compromising execution speeds or database performance.
1. Separation of PHI/PII and Clinical Telemetry
The most fundamental pattern is data segregation. Never store patient identifiers (names, emails, SSNs) in the same table or document as clinical telemetry (imaging metadata, diagnosis log metrics, lab values). Instead, utilize pseudonymized identifiers.
-- Schema Pattern
CREATE TABLE patients_pii (
patient_id UUID PRIMARY KEY,
first_name BYTEA NOT NULL, -- Encrypted at Rest
email_address BYTEA NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE clinical_records (
record_id UUID PRIMARY KEY,
patient_id UUID NOT NULL, -- Pseudonymized reference
diagnosis_code VARCHAR(12) NOT NULL,
systolic_pressure INT,
diastolic_pressure INT
);2. Encryption at Rest & In-Flight (Envelope Encryption)
Under HIPAA, data must be encrypted in transit and at rest. Under GDPR, encryption is a key technical measure to achieve pseudonymization.
We implement **Envelope Encryption** for sensitive fields:
- Generate a unique **Data Encryption Key (DEK)** for each patient record.
- Encrypt patient PII columns using the DEK with AES-256-GCM.
- Encrypt the DEK using a **Key Encryption Key (KEK)** managed in a secure cloud HSM (like AWS KMS or GCP KMS).
3. Automated Audit Trails & Database Access Logs
Every read and write access to database records containing PHI must be logged. Logs must be tamper-proof, detailing who accessed the record, when, and what query was executed. Storing these logs in CloudWatch/Stackdriver with write-once-read-many (WORM) policies is standard practice.