Why Health Data is Different
Under GDPR, most personal data processing can rely on six legal bases (Article 6). Health data is different. Article 9 prohibits processing special category data entirely — unless one of ten specific exceptions applies. The most common exceptions for healthcare are:
- Explicit consent — not just a checkbox, but informed, specific, documented consent
- Healthcare provision (Article 9(2)(h)) — processing necessary for medical treatment
- Public health (Article 9(2)(i)) — processing in the public interest for health security
- Research (Article 9(2)(j)) — scientific or statistical research with safeguards
Each exception has requirements that must be implemented at the database level.
Health Data Schema Patterns
Healthcare databases typically contain these PII-heavy structures:
-- Patient demographics
CREATE TABLE patients (
patient_id UUID PRIMARY KEY,
name TEXT, -- PII: person_name
date_of_birth DATE, -- PII: date_of_birth (special category when combined with health data)
insurance_id TEXT, -- PII: government_id
email TEXT, -- PII: email
phone TEXT -- PII: phone
);
-- Clinical records (Article 9 special category)
CREATE TABLE medical_records (
record_id UUID PRIMARY KEY,
patient_id UUID REFERENCES patients,
diagnosis_code TEXT, -- ICD-10 code — this is health data
treatment TEXT, -- Health data
prescriptions JSONB, -- Health data
lab_results JSONB, -- Health data
attending_physician TEXT,
recorded_at TIMESTAMPTZ
);
-- Consent tracking (critical for Article 9)
CREATE TABLE patient_consent (
patient_id UUID,
purpose TEXT,
consent_type TEXT, -- Must be 'explicit' for health data
evidence TEXT, -- Link to signed consent form
granted_at TIMESTAMPTZ
);
Implementation with pgcomply
Step 1: Classify and Register
-- Install and auto-detect
SELECT pgcomply.quick_setup();
-- Register health-specific PII that auto-detect might miss
SELECT pgcomply.register_pii('medical_records', 'diagnosis_code', 'health_data', 'patient_id');
SELECT pgcomply.register_pii('medical_records', 'treatment', 'health_data', 'patient_id');
SELECT pgcomply.register_pii('medical_records', 'prescriptions', 'health_data', 'patient_id');
SELECT pgcomply.register_pii('medical_records', 'lab_results', 'health_data', 'patient_id');
-- Force classification to restricted (highest level)
SELECT pgcomply.classify_data('medical_records', 'restricted');
SELECT pgcomply.classify_data('patient_consent', 'restricted');
Step 2: Enforce Access Control
-- RLS: Patients only see their own records
SELECT pgcomply.enable_rls('medical_records', 'patient_id');
SELECT pgcomply.enable_rls('patients', 'patient_id');
-- Masking: Administrative staff sees masked clinical data
SELECT pgcomply.mask('medical_records', 'diagnosis_code', 'full', ARRAY['doctor', 'nurse', 'postgres']);
SELECT pgcomply.mask('patients', 'insurance_id', 'partial', ARRAY['billing', 'doctor', 'postgres']);
SELECT pgcomply.mask('patients', 'date_of_birth', 'full', ARRAY['doctor', 'nurse', 'postgres']);
Step 3: Track Consent with Evidence
Health data requires explicit consent with documentary evidence:
SELECT pgcomply.define_purpose('treatment', 'Medical treatment and care', 'healthcare_provision');
SELECT pgcomply.define_purpose('research', 'Clinical research participation', 'explicit_consent');
SELECT pgcomply.define_purpose('insurance', 'Insurance claims processing', 'contract');
-- Record explicit consent with evidence reference
SELECT pgcomply.grant_consent('patient-456', 'research',
source := 'consent_form_v3',
evidence := 'Signed paper form CF-2026-0089, witnessed by Dr. Mueller, scanned to DMS'
);
Step 4: Row-Level Audit Logging
Every access to health data should be traceable — not just changes, but reads:
-- Track all operations including SELECT (via application logging)
SELECT pgcomply.watch('medical_records', ARRAY['INSERT', 'UPDATE', 'DELETE']);
SELECT pgcomply.watch('patients', ARRAY['INSERT', 'UPDATE', 'DELETE']);
For SELECT auditing (reads), configure PostgreSQL logging:
ALTER SYSTEM SET log_statement = 'all';
-- Or use pg_audit for more granular control
Step 5: Retention Policies
German medical record retention requirements:
-- Patient records: 10 years after last treatment (Berufsordnung §10)
SELECT pgcomply.retain('medical_records', 'recorded_at', '3650 days');
-- Radiology records: 10 years
-- Pediatric records: until patient turns 28 (varies)
-- Research data: per study protocol
-- Administrative data: shorter retention
SELECT pgcomply.retain('sessions', 'created_at', '30 days');
Step 6: Verify Protection
-- Ensure all restricted tables have adequate protection
SELECT table_name, level, has_masking, has_rls, has_retention
FROM pgcomply.classification_map()
WHERE level = 'restricted';
Expected output for a compliant system:
table_name | level | has_masking | has_rls | has_retention
-----------------+------------+------------+---------+--------------
medical_records | restricted | true | true | true
patients | restricted | true | true | true
patient_consent | restricted | false | true | true
Every restricted table should show true for masking, RLS, and retention. If any shows false, that is an audit finding.
The Erasure Challenge in Healthcare
GDPR Article 17 (right to erasure) has explicit exceptions for healthcare: data necessary for public health, archiving in the public interest, or scientific research can be retained. But:
-- Patient requests deletion: anonymize clinical data, keep for required retention
SELECT pgcomply.register_pii('medical_records', 'diagnosis_code', 'health_data', 'patient_id', 'anonymize');
-- forget() will anonymize (not delete) clinical records, but delete sessions and marketing data
SELECT pgcomply.forget('patient-456');
Summary
Healthcare data in PostgreSQL requires the highest level of GDPR protection. pgcomply classifies health data as restricted, enforces masking and RLS, tracks explicit consent with evidence, and logs every access. Combined with sector-specific retention policies and careful erasure strategies, this meets the enhanced requirements of Article 9 without changing your database architecture.