Skip to main content

​
Columns

ColumnDescriptionAPI Field Name
clinic_client_idPrimary key and client unique identifier.id
createdThe timestamp when the client record was created.
modifiedThe timestamp when the client record was last modified.
is_deletedIndicates whether the client record is deleted.
clinic_idForeign key to the clinic & clinic_enriched tables. The identifier of the clinic associated with the client.clinic_id
first_nameThe first name of the client.first_name
last_nameThe last name of the client.last_name
emailThe email address of the client.email
phone_numberThe phone number of the client.phone_number
primary_address_idForeign key to the address table. The identifier of the primary address associated with the client.primary_address.id
primary_address_nameThe name associated with the primary address (e.g., clinic name or client name).primary_address.name
primary_address_address_oneThe first line of the primary address (e.g., street address).primary_address.address_one
primary_address_address_twoThe second line of the primary address (e.g., apartment or suite number).primary_address.address_two
primary_address_cityThe city of the primary address.primary_address.city
primary_address_stateThe state of the primary address.primary_address.state
primary_address_zipcodeThe postal code associated with the primary address.primary_address.zipcode
primary_address_countryThe country of the primary address.primary_address.country
primary_address_countyThe county of the primary address.primary_address.county
primary_address_phone_numberThe phone number associated with the primary address.primary_address.phone_number
primary_addressJSON object containing all primary address fields (name, address_one, address_two, city, state, county, zipcode, country, phone_number, id).
primary_payment_method_idForeign key to the payment_payment_method table. The identifier of the primary payment method associated with the client.primary_payment_method_id
organization_client_idGlobal identifier that represents a unique client across all clinics in the system.organization_client_id
is_email_unsubscribedIndicates whether the client’s email is unsubscribed.is_email_unsubscribed
is_activeIndicates whether the client is currently active.is_active
can_be_calledIndicates whether the client can be called.can_be_called
is_subscribedIndicates whether the client is subscribed to receive info from the clinic.is_subscribed
has_unread_messages_for_clinicIndicates whether there are unread inbound messages from the client.has_unread_messages_for_clinic
last_visit_dateThe date of the client’s most recent appointment or invoice.last_visit_date
client_notesAny notes set on the client via the PIMS calendar.client_notes
has_multiple_usersIndicates whether the client has more than one active (non-deleted) client user.has_multiple_users
primary_client_user_idForeign key to the client_user table. The identifier of the primary client user associated with the client.primary_client_user_id
pim_idThe ID of the client in the legacy PIMS system, if imported from there.
last_updatedThe timestamp when the client record was last updated in the source table. 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 client data
    pre-joined for easy consumption, mirroring client API.
*/

"
    )
}}

with clients as (
    select * from {{ ref('client') }}
)

, primary_addresses as (
    select * from {{ ref('address') }}
)

, client_users as (
    select * from {{ ref('client_user') }}
)

, has_multiple_users as (
    select
        client_users.client_id
        , max(client_users.last_updated) as last_updated
        , case
            when count(client_users.id) > 1 then true
            else false
        end as has_multiple_users
    from client_users
    where
        client_users.is_deleted = false
    group by client_users.client_id
)

select
    clients.id as clinic_client_id
    , clients.created
    , clients.modified
    , clients.is_deleted
    , clients.clinic_id
    , clients.first_name
    , clients.last_name
    , clients.email_address as email
    , clients.phone_number
    , clients.primary_address_id
    , primary_addresses.name as primary_address_name
    , primary_addresses.address_one as primary_address_address_one
    , primary_addresses.address_two as primary_address_address_two
    , primary_addresses.city as primary_address_city
    , primary_addresses.state as primary_address_state
    , primary_addresses.zipcode as primary_address_zipcode
    , primary_addresses.country as primary_address_country
    , primary_addresses.county as primary_address_county
    , primary_addresses.phone_number as primary_address_phone_number
    , object_construct_keep_null(
        'name', primary_addresses.name
        , 'address_one', primary_addresses.address_one
        , 'address_two', primary_addresses.address_two
        , 'city', primary_addresses.city
        , 'state', primary_addresses.state
        , 'county', primary_addresses.county
        , 'zipcode', primary_addresses.zipcode
        , 'country', primary_addresses.country
        , 'phone_number', primary_addresses.phone_number
        , 'id', clients.primary_address_id
    ) as primary_address
    , clients.primary_payment_method_id
    , clients.organization_client_id
    , clients.is_email_unsubscribed
    , clients.is_active
    , clients.can_be_called
    , clients.is_subscribed
    , clients.has_unread_messages_for_clinic
    , clients.last_visit_date
    , clients.client_notes
    , case
        when has_multiple_users.client_id is null then false
        else has_multiple_users.has_multiple_users
    end as has_multiple_users
    , clients.primary_client_user_id
    , clients.pim_id
    , greatest(
        coalesce(clients.last_updated, '1900-01-01')
        , coalesce(primary_addresses.last_updated, '1900-01-01')
        , coalesce(has_multiple_users.last_updated, '1900-01-01')
    ) as last_updated
    , clients.vetcove_corporate_id
from clients
left join primary_addresses
    on clients.primary_address_id = primary_addresses.id
left join has_multiple_users
    on clients.id = has_multiple_users.client_id