Definitions / duplicate_patient
Duplicate patient record
A record registered in the last 30 days with the same first name, family name, date of birth and sex as an older record that has not been merged. Phone and postcode matches are evidence, not part of the rule: namesakes exist.
Unit: patients · parameters: recent days 30 · also said as: duplicate patient, duplicate record, same patient twice, registered twice, duplicate registration
Records
Records manager (records) · version 1, changed 2026-11-02
Both records registered by now and neither merged by now; the newer one registered within recent_days. Names compare without case.
First version.
WITH live AS (
SELECT * FROM patients
WHERE registered_at <= {now} AND NOT (merged_into IS NOT NULL AND merged_at <= {now})
)
SELECT 'patient' AS subject_type, n.id AS subject_id, n.id AS patient_id, o.id AS other_patient_id,
n.home_clinic_id AS clinic_id, n.registered_at AS started_at, 'medium' AS severity,
json_object(
'name', n.first_name || ' ' || n.last_name, 'dob', n.dob, 'sex', n.sex, 'mrn', n.mrn,
'other_mrn', o.mrn, 'other_registered_at', o.registered_at,
'phone_match', n.phone = o.phone, 'postcode_match', n.postcode = o.postcode,
'same_region', n.region_id = o.region_id,
'patient_ids', [n.id, o.id],
'bookings_on_new_record', (SELECT count(*) FROM appointments a WHERE a.patient_id = n.id
AND a.booked_at <= {now})
) AS evidence
FROM live n
JOIN live o ON lower(o.first_name) = lower(n.first_name) AND lower(o.last_name) = lower(n.last_name)
AND o.dob = n.dob AND o.sex = n.sex AND o.id <> n.id
AND (o.registered_at < n.registered_at OR (o.registered_at = n.registered_at AND o.id < n.id))
WHERE n.registered_at >= {now} - INTERVAL ({recent_days}) DAY
ORDER BY started_at, n.id
Tests: count_between, unique