Skip to main content

​
Columns

ColumnDescriptionAPI Field Name
care_plan_idForeign key to the care_plan table. The identifier of the care plan.id
createdThe timestamp when the care plan record was created.
modifiedThe timestamp when the care plan record was last modified.
is_deletedIndicates whether the care plan record is deleted.
nameThe name of the care plan.name
statusThe current status of the care plan (e.g., ACTIVE, INACTIVE).status
kindThe type of care plan (e.g., CORE, ADD_ON, SERVICE_PACKAGE), indicating whether it is a base plan or an add-on to another plan.kind
speciesThe species this care plan is designed for (e.g., CANINE, FELINE).species
min_age_valueThe minimum patient age required to enroll in this care plan.min_age_value
min_age_unitThe unit of measure for min_age_value (e.g., DAY, WEEK, MONTH, YEAR).min_age_unit
max_age_valueThe maximum patient age allowed for enrollment in this care plan.max_age_value
max_age_unitThe unit of measure for max_age_value (e.g., DAY, WEEK, MONTH, YEAR).max_age_unit
default_duration_valueThe default duration value for this care plan.default_duration_value
default_duration_unitThe unit of measure for default_duration_value (e.g., DAY, WEEK, MONTH, YEAR).default_duration_unit
next_plan_idForeign key to the care_plan & care_plan_enriched table. The identifier of the next care plan that a patient is automatically transitioned to after this plan ends.next_plan_id
priceThe plan price in dollars for this care plan at this clinic, based on the clinic’s assigned care pricing group. Null if no pricing exists for this clinic’s pricing group. Snowflake displays dollars, API returns cents.price
enrollment_feeThe enrollment fee in dollars for this care plan at this clinic, based on the clinic’s assigned care pricing group. Null if no pricing exists for this clinic’s pricing group. Snowflake displays dollars, API returns cents.enrollment_fee
clinic_idForeign key to the clinic table. The identifier of the clinic for which the pricing applies.
last_updatedThe timestamp when the record was last updated across all joined tables. This is the most recent timestamp from all related tables.
vetcove_corporate_idThe identifier of the corporate group associated with the care plan.

​
Relationships

​
SQL Definition

/*
    This model provides a clinic-specific view of care plan pricing,
    mirroring the /clinic-care-plans/ API endpoint. One row per care plan
    per clinic, showing each clinic's applicable price and enrollment fee
    based on their assigned care pricing group.

    Tag filtering mirrors the API logic: care plans with no tags are visible
    to all clinics in the group; care plans with tags are visible only to
    clinics that belong to at least one of those tags.
*/

"
    )
}}

with care_plans as (
    select * from {{ ref('care_plan') }}
)

, care_clinics as (
    select * from {{ ref('care_plan_care_clinic') }}
)

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

, care_plan_tags as (
    select * from {{ ref('care_plan_careplan_tags') }}
)

, clinic_tag_clinics as (
    select * from {{ ref('clinic_tag_clinic') }}
)

-- care_plan/clinic_id pairs where the care plan is tagged AND the clinic has a matching tag
, tagged_care_plan_clinics as (
    select
        care_plan_tags.care_plan_id
        , clinic_tag_clinics.clinic_id
        , max(greatest(
            coalesce(care_plan_tags.last_updated, '1900-01-01')
            , coalesce(clinic_tag_clinics.last_updated, '1900-01-01')
        )) as last_updated
    from care_plan_tags
    inner join clinic_tag_clinics
        on care_plan_tags.clinic_tag_id = clinic_tag_clinics.clinic_tag_id
    group by
        care_plan_tags.care_plan_id
        , clinic_tag_clinics.clinic_id
)

-- care plan IDs that have at least one tag (tagged care plans are restricted to specific clinics)
, care_plans_with_tags as (
    select
        care_plan_id
        , max(last_updated) as last_updated
    from care_plan_tags
    where is_deleted = false
    group by care_plan_id
)

select
    -- Identifiers
    care_plans.id as care_plan_id
    , care_plans.created
    , care_plans.modified
    , care_plans.is_deleted
    , care_plans.name
    , care_plans.status
    , care_plans.kind
    , care_plans.species
    , care_plans.min_age_value
    , care_plans.min_age_unit
    , care_plans.max_age_value
    , care_plans.max_age_unit
    , care_plans.default_duration_value
    , care_plans.default_duration_unit
    , care_plans.next_plan_id
    , care_plan_prices.price
    , care_plan_prices.enrollment_fee
    , care_clinics.clinic_id
    , greatest(
        coalesce(care_plans.last_updated, '1900-01-01')
        , coalesce(care_clinics.last_updated, '1900-01-01')
        , coalesce(care_plan_prices.last_updated, '1900-01-01')
        , coalesce(tagged_care_plan_clinics.last_updated, '1900-01-01')
        , coalesce(care_plans_with_tags.last_updated, '1900-01-01')
    ) as last_updated
    , care_plans.vetcove_corporate_id
from care_plans
inner join care_clinics
    on care_plans.vetcove_corporate_id = care_clinics.vetcove_corporate_id
left join tagged_care_plan_clinics
    on
        care_plans.id = tagged_care_plan_clinics.care_plan_id
        and care_clinics.clinic_id = tagged_care_plan_clinics.clinic_id
left join care_plans_with_tags
    on care_plans.id = care_plans_with_tags.care_plan_id
left join care_plan_prices
    on
        care_plans.id = care_plan_prices.care_plan_id
        and care_clinics.care_pricing_group_id = care_plan_prices.care_pricing_group_id
-- Tag filter: visible if tagged for this clinic OR has no tags (applies to all clinics)
where (
    tagged_care_plan_clinics.clinic_id is not null
    or care_plans_with_tags.care_plan_id is null
)