Patient Custom Care Reminder

Patient custom (one-off) care reminder enriched model providing patient one-off reminder, patient, and patient standard care data.

• Each row represents a patient one-off reminder.
• Compatible with the Vetcove API endpoint(s): /standard-care-reminders/custom/
• API endpoint(s): https://connect.vetcove.com/ext/v2/standard-care-reminders/custom/
• Snowflake view: phd_enriched.patient_custom_care_reminder_enriched

Columns

Column	Description	API Field Name
patient_one_off_reminder_id	Primary key and patient one-off reminder unique identifier.	id
created	The timestamp when the custom care reminder record was created.
modified	The timestamp when the custom care reminder record was last modified.
is_deleted	Indicates whether the custom care reminder record is deleted.
status	The current status of the reminder (e.g., ACTIVE, INACTIVE, NOT_MEDICALLY_NECESSARY, DECLINED).	status
kind	The type of care reminder. Always ‘CUSTOM’ for one-off reminders created directly on a patient.	kind
name	The name of the custom care reminder, set directly on the reminder.	name
organization_patient_id	Global identifier that represents a unique patient across all clinics in the system (from patient table).	organization_patient_id
last_fulfilled_date	The date the reminder was last fulfilled, derived from the fulfilled_at timestamp.	last_fulfilled_date
due_date	The due date of the reminder.	due_date
is_past_due	Indicates whether the reminder is past due (due date is before the current date).	is_past_due
patient_standard_care_id	Foreign key to the patient_standard_care & patient_standard_care_enriched table. The identifier of the patient standard care association.	patient_standard_care_id
last_updated	The 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_id	The unique identifier of the corporate group associated with this record.

SQL Definition

View SQL

/*
    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