Skip to main content

​
Columns

ColumnDescriptionAPI Field Name
clinic_patient_idPrimary key and clinic patient unique identifier.id
createdThe timestamp when the patient record was created.
modifiedThe timestamp when the patient record was last modified.
is_deletedIndicates whether the patient record is deleted.
nameThe name of the patient.name
clinic_clientsArray of objects representing the client relationships for this patient, including clinic_client_id and is_active.clinic_clients
speciesThe species of the patient.species
weightThe weight of the patient.weight
weight_in_unitsThe weight of the patient in units.weight_in_units
weight_unitThe unit of measure for the patient’s weight.weight_unit
sexThe sex of the patient.sex
birth_monthThe birth month of the patient.birth_month
birth_dayThe birth day of the patient. Null if birth month or year is unknown.birth_day
birth_yearThe birth year of the patient.birth_year
organization_patient_idGlobal identifier that represents a unique patient across all clinics in the system.organization_patient_id
insuranceJSON object containing insurance details (policy_number, provider_id, provider_name).insurance
insurance_custom_provider_nameThe custom insurance provider name for the patient.insurance.provider_name
insurance_policy_numberThe insurance policy number for the patient.insurance.policy_number
insurance_provider_idThe insurance provider identifier for the patient.insurance.provider_id
primary_breedThe primary breed of the patient.primary_breed
secondary_breedsArray of secondary breeds for the patient.secondary_breeds
custom_breedThe custom breed of the patient, if applicable.custom_breed
allergiesThe allergies recorded for the patient.allergies
is_alteredIndicates whether the patient has been spayed or neutered.is_altered
is_aliveIndicates whether the patient is alive.is_alive
is_surrenderedIndicates whether the patient has been surrendered.is_surrendered
is_deactivatedIndicates whether the patient is deactivated.is_deactivated
is_legacy_deactivatedIndicates whether the patient was deactivated via a legacy process.is_legacy_deactivated
microchip_numberThe microchip number of the patient.microchip_number
primary_clinic_client_idThe identifier of the primary client associated with the patient.primary_clinic_client_id
clinic_idForeign key to the clinic & clinic_enriched table. The identifier of the clinic associated with the patient.clinic_id
custom_speciesThe custom species of the patient, if applicable.custom_species
colorThe color of the patient.color
deceased_dateThe date the patient was marked as deceased, if applicable.deceased_date
surrendered_dateThe date the patient was surrendered, if applicable.surrendered_date
deactivated_atThe timestamp when the patient was deactivated, if applicable.deactivated_at
first_visit_dateThe date of the patient’s first visit.first_visit_date
last_visit_dateThe date of the patient’s most recent visit.last_visit_date
patient_notesNotes recorded on the patient.patient_notes
resuscitate_statusThe resuscitation status of the patient.resuscitate_status
auto_rabies_tagThe auto-assigned rabies tag for the patient.auto_rabies_tag
reissued_rabies_tagThe reissued rabies tag for the patient.reissued_rabies_tag
is_activeIndicates whether the patient is currently active.is_active
date_of_birthThe date of birth of the patient, derived from birth_year, birth_month, and birth_day.date_of_birth
metadataAdditional metadata recorded on the patient.metadata
pim_idThe ID of the patient in the legacy PIMS system, if imported from there.
last_updatedThe timestamp when the patient record was last updated. This is the most recent timestamp from all related tables.
vetcove_corporate_idThe unique identifier of the corporate group associated with this record.

​
Relationships

​
SQL Definition

/*
    This model provides a business-ready, denormalized view of patient data
    pre-joined for easy consumption, mirroring patient API.
*/

"
    )
}}

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

, patient_insurance as (
    select * from {{ ref('patient_insurance') }}
    where is_deleted = false
)

, client_relationships as (
    select
        patient_id
        , array_agg(
            object_construct_keep_null(
                'clinic_client_id', client_id
                , 'is_active', is_active
            )
        ) within group (
            order by client_id
        ) as clinic_clients
        , max(last_updated) as last_updated
    from {{ ref('client_patient_client_relationship') }}
    where
        is_deleted = false
    group by patient_id
)

select
    patients.id as clinic_patient_id
    , patients.created
    , patients.modified
    , patients.is_deleted
    , patients.name
    , coalesce(
        client_relationships.clinic_clients, array_construct()
    ) as clinic_clients
    , patients.species
    , patients.weight
    , patients.weight_in_units
    , patients.weight_unit_of_measure as weight_unit
    , patients.sex
    , patients.birth_month
    , case
        when patients.birth_month is null or patients.birth_year is null
            then null
        else patients.birth_day
    end as birth_day
    , patients.birth_year
    , patients.organization_patient_id
    , object_construct_keep_null(
        'policy_number', patient_insurance.policy_number
        , 'provider_id', patient_insurance.provider_id
        , 'provider_name', patient_insurance.custom_provider_name
    ) as insurance
    , patient_insurance.custom_provider_name as insurance_custom_provider_name
    , patient_insurance.policy_number as insurance_policy_number
    , patient_insurance.provider_id as insurance_provider_id
    , patients.primary_breed
    , patients.secondary_breeds
    , patients.custom_breed
    , patients.allergies
    , patients.is_altered
    , patients.is_alive
    , patients.is_surrendered
    , case
        when
            patients.is_active = false then true
        when
            patients.is_active = true
            and patients.is_deleted = true then true
        else false
    end as is_deactivated
    , patients.is_legacy_deactivated
    , patients.microchip_number
    , patients.client_id as primary_clinic_client_id
    , patients.clinic_id
    , patients.custom_species
    , patients.color
    , patients.deceased_date
    , patients.surrendered_date
    , patients.deactivated_at
    , patients.first_visit_date
    , patients.last_visit_date
    , patients.patient_notes
    , patients.resuscitate_status
    , patients.auto_rabies_tag
    , patients.reissued_rabies_tag
    , patients.is_active
    , case
        when patients.birth_month is null or patients.birth_year is null
            then null
        else date_from_parts(
            patients.birth_year::int
            , patients.birth_month::int
            , coalesce(patients.birth_day, 1)::int
        )
    end as date_of_birth
    , patients.patient_clinic_group_metadata as metadata
    , patients.pim_id
    , greatest(
        coalesce(patients.last_updated, '1900-01-01')
        , coalesce(patient_insurance.last_updated, '1900-01-01')
        , coalesce(client_relationships.last_updated, '1900-01-01')
    ) as last_updated
    , patients.vetcove_corporate_id
from patients
left join patient_insurance
    on patients.id = patient_insurance.patient_id
left join client_relationships
    on patients.id = client_relationships.patient_id