Ambiguity in Determining Current State for Completed Programs Due to DATE-only Fields

Software Bahmni
,
1

When working with OpenMRS patient program workflow states, we’ve encountered a fundamental data model limitation in determining which state represents the “current” or “final” state of a completed program enrollment.

The patient_state table structure:

  +-------------------------+--------------+------+-----+---------+----------------+
  | Field                   | Type         | Null | Key | Default | Extra          |
  +-------------------------+--------------+------+-----+---------+----------------+
  | patient_state_id        | int          | NO   | PRI | NULL    | auto_increment |
  | patient_program_id      | int          | NO   | MUL | 0       |                |
  | state                   | int          | NO   | MUL | 0       |                |
  | start_date              | date         | YES  |     | NULL    |                |
  | end_date                | date         | YES  |     | NULL    |                |
  | creator                 | int          | NO   | MUL | 0       |                |
  | date_created            | datetime     | NO   |     | NULL    |                |
  | changed_by              | int          | YES  | MUL | NULL    |                |
  | date_changed            | datetime     | YES  |     | NULL    |                |
  +-------------------------+--------------+------+-----+---------+----------------+

Key Observation: start_date and end_date are DATE fields (not DATETIME).

For active program enrollments, determining the current state is straightforward:

  • Find the state where end_date IS NULL
  • This works correctly as per OpenMRS design

However, for completed program enrollments where all states have end_date IS NOT NULL, determining the “most recent” or “final” state becomes impossible to determine accurately when multiple state transitions occur on the same calendar day.

Real-world example

A TB patient’s treatment program on June 15, 2024:

  • 09:00 AM: Patient enters “Initial Treatment” state
  • 11:30 AM: Clinician updates to “Intensive Phase”
  • 02:45 PM: Patient moved to “Continuation Phase”
  • Program completes December 31, 2024

Database records:

  • State A: start_date = ‘2024-06-15’, end_date = ‘2024-06-15’ (Initial Treatment)
  • State B: start_date = ‘2024-06-15’, end_date = ‘2024-06-15’ (Intensive Phase)
  • State C: start_date = ‘2024-06-15’, end_date = ‘2024-12-31’ (Continuation Phase)

Now when querying for the “most recent state” on 2024-06-15, there is no way to determine the correct order because:

  1. All three states have start_date = ‘2024-06-15’
  2. States A and B both have end_date = ‘2024-06-15’
  3. The DATE type fields contain no time information

Sorting by end_date alone would show State C as most recent, but if a clinician corrected an error and updated State A or B later that same day, we have no field in the data model that captures the temporal order of these transitions within the same day.

A potential solution is to change start_date and end_date to DATETIME, modifying the schema to capture time precision.

We’d love to hear from the community on how best this can be handled and how we can help out in here.

cc: @angshuonline, @mohant, @ibacher, @grace, @raff, @burke, @dkayiwa, @mseaton

2

I am not sure why this should not be a DATETIME, and just DATE!! But state changes within the day is entirely possible depending on workflow. Maybe not that common in typical programs like MNIC, TB, HIV which is probably the historical usage of Program. Although, depending on how the program workflow is setup, state change within the date is entirely possible, we must not assume that only DATE is applicable.

@dkayiwa @mseaton thoughts?

3

The request of changing these fields to DATETIME makes sense to me. :+1:

4

Totally agree @angshuonline . This has come up more than once in the past, but I can’t seem to find any reference to it in JIRA or Talk. Would be happy to see this change.

5

Yeah, this seems like a net improvement. I wonder if it also makes sense to add a column to patient_state to track the previous patient_state_id. DATETIME resolution is probably close enough, but we could just eliminate any potential ambiguity in ordering that way (and we do something similar for orders and obs).