Skip to main content

​
Columns

ColumnDescriptionAPI Field Name
patient_one_off_reminder_idPrimary key and patient one-off reminder unique identifier.id
createdThe timestamp when the custom care reminder record was created.
modifiedThe timestamp when the custom care reminder record was last modified.
is_deletedIndicates whether the custom care reminder record is deleted.
statusThe current status of the reminder (e.g., ACTIVE, INACTIVE, NOT_MEDICALLY_NECESSARY, DECLINED).status
kindThe type of care reminder. Always ‘CUSTOM’ for one-off reminders created directly on a patient.kind
nameThe name of the custom care reminder, set directly on the reminder.name
organization_patient_idGlobal identifier that represents a unique patient across all clinics in the system (from patient table).organization_patient_id
last_fulfilled_dateThe date the reminder was last fulfilled, derived from the fulfilled_at timestamp.last_fulfilled_date
due_dateThe due date of the reminder.due_date
is_past_dueIndicates whether the reminder is past due (due date is before the current date).is_past_due
patient_standard_care_idForeign key to the patient_standard_care & patient_standard_care_enriched table. The identifier of the patient standard care association.patient_standard_care_id
last_updatedThe timestamp when the custom care reminder record was last updated in the source table. This is the most recent timestamp from all related tables (patient_one_off_reminder, patient).
vetcove_corporate_idThe unique identifier of the corporate group associated with this record.

​
SQL Definition

/*
    This model provides a business-ready, denormalized view of patient custom
    (one-off) care reminders pre-joined for easy consumption, mirroring
    the custom care reminders API.
*/

"
    )
}}

with patient_one_off_reminders as (
    select * from {{ ref('patient_one_off_reminder') }}
    -- Exclude legacy mapped reminders
    where
        is_legacy_mapped = false
)

, patients as (
    select * from {{ ref('patient') }}
)

select
    patient_one_off_reminders.id as patient_one_off_reminder_id
    , patient_one_off_reminders.created
    , patient_one_off_reminders.modified
    , patient_one_off_reminders.is_deleted
    , patient_one_off_reminders.status
    , 'CUSTOM' as kind
    , patient_one_off_reminders.name
    , patients.organization_patient_id
    , patient_one_off_reminders.fulfilled_at::date as last_fulfilled_date
    , patient_one_off_reminders.due_date
    , case
        when patient_one_off_reminders.due_date < current_date() then true
        else false
    end as is_past_due
    , patient_one_off_reminders.patient_standard_care_id
    , greatest(
        coalesce(patient_one_off_reminders.last_updated, '1900-01-01')
        , coalesce(patients.last_updated, '1900-01-01')
    ) as last_updated
    , patient_one_off_reminders.vetcove_corporate_id
from patient_one_off_reminders
left join patients
    on patient_one_off_reminders.patient_id = patients.id