Identifying the Correct Unique Patient Identifier After Exporting Data from PostgreSQL

Technical Support
,
1

Identifying the Correct Unique Patient Identifier After Exporting Data from PostgreSQL

I exported patient-related data from a PostgreSQL database and am trying to determine the correct unique identifier for each patient.

The issue is that many records have blank or missing IDs. In addition, I am seeing cases where the same patient appears multiple times with:

  • The same name

  • The same mobile number

  • Different patient_id values

This makes it difficult to determine which field should be treated as the true unique identifier.

For example:

Name Mobile Number patient_id
John Doe 9800000000 P001
John Doe 9800000000 P002

I also have separate patient information and form/submission data tables, but many of the ID fields are blank in the exported data.

My questions are:

  1. Which field is typically considered the actual unique identifier in this type of system?

  2. If patient_id values can differ for the same person, what is the best way to identify duplicate patient records?

  3. Are there database-level IDs (e.g., _id, UUID, primary key) that I should be looking for instead of patient_id?

  4. What queries or checks would you recommend to determine which identifier is truly unique and reliable?

Any guidance on how to verify the correct patient identifier and safely merge patient records would be greatly appreciated.

2

Hello @Sanjit7

The answer to this question starts before PostgreSQL.

The Key Point

In a CHT implementation, the “correct patient identifier” is usually defined by the application configuration, forms, and workflows — not by PostgreSQL itself.

PostgreSQL (via CHT Sync / couch2pg) simply contains what was captured and synchronized from CHT.

Before asking:

Which field in PostgreSQL is the true patient identifier?

it is important to first understand:

How was patient identity designed and configured in the source CHT application?

1. The Identifier Is Defined Upstream

Different CHT implementations use different identifiers, for example:

  • patient_id
  • national_id
  • medical_record_number
  • Generated codes
  • Phone numbers
  • Contact UUIDs

The CHT platform does not enforce a universal patient identifier.

Whether any of these fields are unique depends entirely on:

  • Contact configuration
  • Registration forms
  • Workflow design
  • Validation rules

Relevant documentation:

2. Different Patient IDs Do Not Necessarily Mean the Identifier Is Wrong

You mentioned seeing:

  • Same name
  • Same phone number
  • Different patient_id values

If patient_id was intended to uniquely identify patients, then both IDs may still be valid.

The more important question becomes:

How were two patient records created for what appears to be the same individual?

This is usually a workflow or data quality question rather than a PostgreSQL question.

For example, two users may independently register the same person, resulting in two separate patient records with different identifiers.

3. Understanding _id

Every CouchDB document contains an _id field.

{
  "_id": "..."
}

This value is always unique.

However, _id identifies a document, not necessarily a patient.

For example:

  • A patient contact has its own _id
  • A report submission has its own _id
  • A task has its own _id

Therefore:

_id is a reliable document identifier, but it should not automatically be treated as the patient identifier.

4. Blank Identifier Fields May Be Expected

You mentioned that some patient or submission tables contain blank ID fields.

In CHT, patient identifiers are not automatically copied into report submissions.

If a project wants fields such as:

  • patient_id
  • patient_uuid
  • contact_uuid

to appear in reports, those values must be explicitly included in the form configuration.

Relevant documentation:

As a result:

Blank values in PostgreSQL often mean the identifier was never captured in the source form.

5. PostgreSQL Only Contains What Was Submitted

CHT Sync replicates data from CouchDB into PostgreSQL.

It does not generate missing identifiers or reconstruct relationships that were not captured.

If an identifier is missing in PostgreSQL, the first thing to check is whether it exists in the original document.

Relevant documentation:

6. There Is No Universal Answer to “Which Field Is Truly Unique?”

The answer depends on how the implementation was designed.

In one project, patient_id may be the authoritative identifier.

In another, it may be a contact UUID, national ID, or another field entirely.

Without reviewing the application’s configuration and forms, it is difficult to determine which field was intended to uniquely identify patients.

7. What Should Be Investigated First?

Before attempting deduplication or record merging, I would recommend understanding:

  • Which form creates patient records?
  • How is patient_id generated?
  • Is uniqueness enforced during registration?
  • Are duplicate checks implemented?
  • Which field was intended to uniquely identify a patient?

These answers are usually found in the CHT configuration rather than the PostgreSQL database.

The practical question is often:

Can I determine the correct patient identifier purely from PostgreSQL exports?

In most CHT implementations, the answer is:

Not reliably.

PostgreSQL can help identify potential duplicates and assess data quality, but determining the intended patient identifier usually requires reviewing:

  • Contact configuration
  • Registration workflows
  • Form design
  • Identifier generation logic

within the CHT application itself.

Only then can you confidently determine whether patient_id, a contact UUID, a national identifier, or another field was intended to be the authoritative patient identifier.