Duplicate Patient Alert

Duplicate Patient Alert

Supported from Tamanu v2.35.

The duplicate patient alert feature helps prevent the creation of duplicate patient records in Tamanu Desktop by detecting potential matches when creating a new patient record.

This feature is currently supported on desktop only, however there are future plans to support mobile.

How it works

When creating a new patient, Tamanu automatically checks for existing patients that might be the same person by comparing a range of different patient details fields such as first name, last name and DOB.

This is achieved using PostgreSQL's fuzzy string matching capabilities to ensure comprehensive duplicate detection. It is designed to catch duplicates even when there are:

• spelling errors (e.g. Jon vs John)
• phonetic variations (e.g. Smith vs Smyth)
• minor typing mistakes
• date and months swapped around for date of birth

Some known limitations where duplicates are not detected:

• phonetic difference in last name (e.g. Seymour vs xSeymour, Ioane vs Toane)
• significant difference in length of name (e.g. Peter Alexander vs Peter)

The duplicate patient alert has been built using a PostgresSQL function which can be updated as required. To view the latest version of the duplicate patient alert function please see Library of duplicate patient alert functions .

Refining the alert

The duplicate patient alert has been built using a PostgresSQL function which is a bit of SQL that checks for patient duplicates and produces a list of potential duplicate patients to show on the front end. Because the checking logic is contained within that function, the duplicate alert can be tailored per deployment, at any time outside of a version upgrade.

Function Library

A higher version number does not indicate a more superior algorithm, it is based on the order of request to refine the algorithm.

Execute the SQL script to create or replace the function on both Central and Facility servers.

v2 - Phonetic - last name, Levenshtein distance - name, month/date - DOB

Key target

• Soundex on last name
• Levenshtein distance on name and date of birth
• date and month swapped around for date of birth

Description

PostgreSQL's fuzzy string matching capabilities provides comprehensive duplicate detection. This version is designed to catch duplicates even when there are:

• spelling errors (e.g. Jon vs John)
• phonetic variations (e.g. Smith vs Smyth)
• minor typing mistakes
• date and months swapped around for date of birth

Some known limitations where duplicates are not detected:

• phonetic difference in last name (e.g. Seymour vs xSeymour, Ioane vs Toane)
• significant difference in length of name (e.g. Peter Alexander vs Peter)

SQL function

/* Setup */
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
CREATE INDEX IF NOT EXISTS patients_last_name_soundex_index ON patients(soundex(last_name));

/* These index will further improve performance */
CREATE INDEX IF NOT EXISTS patients_deleted_at_index ON patients(deleted_at) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS patients_date_of_birth_index ON patients(date_of_birth);

/* Function */
CREATE OR REPLACE FUNCTION public.find_potential_patient_duplicates(patient_data json)
  RETURNS SETOF patients
  LANGUAGE plpgsql
  STABLE PARALLEL SAFE
AS $function$
    BEGIN      
      RETURN QUERY
    SELECT 
      p.*
    FROM patients p
    WHERE p.deleted_at IS NULL
      AND soundex(p.last_name) = soundex(patient_data->>'lastName')
	  AND levenshtein(
        lower(concat(p.last_name, p.first_name)), 
        lower(concat(patient_data->>'lastName', patient_data->>'firstName'))
      ) <= 6
	  AND (levenshtein(
        p.date_of_birth, 
        (patient_data->>'dateOfBirth')
      ) <= 1
      OR p.date_of_birth = concat_ws('-', 
	  	substring(patient_data->>'dateOfBirth', 1, 4), 
	  	substring(patient_data->>'dateOfBirth', 9, 2), 
	  	substring(patient_data->>'dateOfBirth', 6, 2)))
	LIMIT 5;
    END;
    $function$
;

v1 - Phonetic - last name, Levenshtein distance - name

Key target

• Soundex on last name
• Levenshtein distance on name and date of birth

Description

PostgreSQL's fuzzy string matching capabilities provides comprehensive duplicate detection. This version is designed to catch duplicates even when there are:

• spelling errors (e.g. Jon vs John)
• phonetic variations (e.g. Smith vs Smyth)
• minor typing mistakes

Some known limitations where duplicates are not detected:

• phonetic difference in last name (e.g. Seymour vs xSeymour, Ioane vs Toane)
• significant difference in length of name (e.g. Peter Alexander vs Peter)

SQL function

/* Setup */
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
CREATE INDEX IF NOT EXISTS patients_last_name_soundex_index ON patients(soundex(last_name));

/* These index will further improve performance */
CREATE INDEX IF NOT EXISTS patients_deleted_at_index ON patients(deleted_at) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS patients_date_of_birth_index ON patients(date_of_birth);

/* Function */
CREATE OR REPLACE FUNCTION public.find_potential_patient_duplicates(patient_data json)
  RETURNS SETOF patients
  LANGUAGE plpgsql
  STABLE PARALLEL SAFE
AS $function$
    BEGIN      
      RETURN QUERY
    SELECT 
      p.*
    FROM patients p
    WHERE p.deleted_at IS NULL
      AND soundex(p.last_name) = soundex(patient_data->>'lastName')
	  AND levenshtein(
        lower(concat(p.last_name, p.first_name)), 
        lower(concat(patient_data->>'lastName', patient_data->>'firstName'))
      ) <= 6
	  AND levenshtein(
        p.date_of_birth, 
        (patient_data->>'dateOfBirth')
      ) <= 1
	LIMIT 5;
    END;
    $function$
;

Default - exact match - first name, last name, DOB

CREATE OR REPLACE FUNCTION public.find_potential_patient_duplicates(patient_data json)
  RETURNS SETOF patients
  LANGUAGE plpgsql
  STABLE PARALLEL SAFE
AS $function$
    BEGIN      
      RETURN QUERY
      SELECT 
        p.*
      FROM patients p
      WHERE
        lower(p.first_name) = lower(patient_data->>'firstName')
        AND lower(p.last_name) = lower(patient_data->>'lastName')
        AND p.date_of_birth = patient_data->>'dateOfBirth'
        AND p.deleted_at IS NULL;
    END;