Clinic Care Plan

Clinic-specific care plan enriched model providing care plans with their applicable pricing per clinic, based on each clinic’s assigned care pricing group.

• Each row represents a care plan & clinic.
• Compatible with the Vetcove API endpoint(s): /clinic-care-plans/
• API endpoint(s): https://connect.vetcove.com/ext/v2/clinic-care-plans/
• Snowflake view: phd_enriched.clinic_care_plan_enriched

Columns

Column	Description	API Field Name
care_plan_id	Foreign key to the care_plan table. The identifier of the care plan.	id
created	The timestamp when the care plan record was created.
modified	The timestamp when the care plan record was last modified.
is_deleted	Indicates whether the care plan record is deleted.
name	The name of the care plan.	name
status	The current status of the care plan (e.g., ACTIVE, INACTIVE).	status
kind	The 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
species	The species this care plan is designed for (e.g., CANINE, FELINE).	species
min_age_value	The minimum patient age required to enroll in this care plan.	min_age_value
min_age_unit	The unit of measure for min_age_value (e.g., DAY, WEEK, MONTH, YEAR).	min_age_unit
max_age_value	The maximum patient age allowed for enrollment in this care plan.	max_age_value
max_age_unit	The unit of measure for max_age_value (e.g., DAY, WEEK, MONTH, YEAR).	max_age_unit
default_duration_value	The default duration value for this care plan.	default_duration_value
default_duration_unit	The unit of measure for default_duration_value (e.g., DAY, WEEK, MONTH, YEAR).	default_duration_unit
next_plan_id	Foreign 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
price	The 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_fee	The 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_id	Foreign key to the clinic table. The identifier of the clinic for which the pricing applies.
last_updated	The timestamp when the record was last updated across all joined tables. This is the most recent timestamp from all related tables.
vetcove_corporate_id	The identifier of the corporate group associated with the care plan.

Relationships

• Care Plan via care_plan_id
• Clinic via clinic_id

SQL Definition

View SQL

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