Skip to main content

​
Columns

ColumnDescriptionAPI Field Name
prescription_idPrimary key and prescription unique identifier.id
createdThe timestamp when the prescription record was created.
modifiedThe timestamp when the prescription record was last modified.
is_deletedIndicates whether the prescription record is deleted.
clinic_idForeign key to the clinic & clinic_enriched tables. The identifier of the clinic associated with the prescription.clinic_id
clinic_patient_idForeign key to the patient & client_enriched tables. The identifier of the patient associated with the prescription.clinic_patient_id
itemJSON object containing the prescribed item’s identifier and name (id, name).item
item_idForeign key to the item & item_enriched table. The identifier of the item prescribed.item_id
item_nameName of the item (denormalized from item).item_name
directionsThe directions for the prescription (instructions to the client).directions
client_notesAdditional notes to the client for the prescription.client_notes
allowed_usesThe number of fills allowed; each fill has quantity denoted by quantity.allowed_uses
quantityRecommended number of items per fill.quantity
days_supplyHow many days one unit of the prescription is intended to last.days_supply
expiration_dateWhen the prescription expires.expiration_date
statusThe status of the latest prescription fill (e.g., ACTIVE, DRAFT, DECLINED, IMPORTED).status
prescribed_atThe timestamp when the prescription fill was created (prescribed).prescribed_at
prescribed_by_doctor_idForeign key to the doctor table. The identifier of the doctor who prescribed.
prescribed_byFull name of the prescribing doctor (first_name + last_name, denormalized from doctor).prescribed_by
is_prnWhether the prescription has unlimited refills (as needed).is_prn
total_allowed_usesTotal allowed quantity for the prescription (allowed_uses * quantity). Null if there is no active fill or if the prescription is PRN.total_allowed_uses
total_usesTotal quantity of items dispensed (fill order count total_uses + external_uses).total_uses
remaining_fillsNumber of fills remaining on the prescription. Null for PRN (as-needed) prescriptions.remaining_fills
last_filled_atThe date and time when the last fill was dispensed (most recent order_placed_time across non-canceled order items).last_filled_at
latest_fill_idForeign key to the prescription_fill table. The identifier of the latest prescription fill for this prescription.
last_updatedThe timestamp when the prescription record was last updated in the source table. This is the most recent timestamp from all related tables (prescription, fill, item, doctor, fill_order_counts).
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 prescription data
    pre-joined with the latest prescription fill for easy consumption, mirroring
    the prescriptions API.
*/

"
    )
}}

with prescriptions as (
    select * from {{ ref('prescription') }}
)

, fills as (
    select * from {{ ref('prescription_fill') }}
)

, items as (
    select * from {{ ref('item') }}
)

, doctors as (
    select * from {{ ref('doctor') }}
)

-- Count non-canceled order items per fill, and last order date
, fill_order_counts as (
    select
        order_items.prescription_fill_id
        , count(*) as active_order_item_count
        , sum(order_items.quantity) as total_uses
        , max(orders.order_placed_time) as last_filled_at
        , greatest(
            coalesce(max(order_items.last_updated), '1900-01-01')
            , coalesce(max(orders.last_updated), '1900-01-01')
        ) as last_updated
    from {{ ref('order_item') }} as order_items
    inner join {{ ref('orders') }} as orders
        on order_items.order_id = orders.id
    where
        -- Filter out canceled statuses
        order_items.status not in (
            'CLINIC_CANCELED'
            , 'CLIENT_CANCELED'
            , 'VENDOR_CANCELED'
            , 'CLINIC_CANCELED_REFUNDED'
            , 'CLINIC_CANCELED_CREDITED'
        )
    group by order_items.prescription_fill_id
)

select
    prescriptions.id as prescription_id
    , prescriptions.created
    , prescriptions.modified
    , prescriptions.is_deleted
    , prescriptions.clinic_id
    , prescriptions.patient_id as clinic_patient_id
    , object_construct_keep_null(
        'id', prescriptions.item_id
        , 'name', items.name
    ) as item
    , prescriptions.item_id
    , items.name as item_name
    , fills.directions
    , fills.client_notes
    , fills.allowed_uses
    , fills.quantity
    , fills.days_supply
    , fills.expiration_date
    , fills.status
    , fills.created as prescribed_at
    , fills.prescribed_by_doctor_id
    , case
        when doctors.id is null then null
        when doctors.first_name = 'Your' and doctors.last_name = 'Veterinarian'
            then 'Your Veterinarian'
        when doctors.is_archived = true
            then trim(
                concat(coalesce(doctors.first_name, ''), ' ', coalesce(doctors.last_name, ''))
            )
        else 'Dr. ' || trim(
            concat(coalesce(doctors.first_name, ''), ' ', coalesce(doctors.last_name, ''))
        )
    end as prescribed_by
    , fills.is_prn
    , case
        when fills.id is null then null
        when coalesce(fills.is_deleted, false) then null
        when fills.is_prn then null
        else coalesce(fills.allowed_uses, 0) * coalesce(fills.quantity, 0)
    end as total_allowed_uses
    , coalesce(fill_order_counts.total_uses, 0)
    + coalesce(fills.external_uses, 0)
        as total_uses
    , case
        when fills.is_prn then 1
        else greatest(
            coalesce(fills.allowed_uses, 0)
            - coalesce(fill_order_counts.active_order_item_count, 0)
            - coalesce(fills.additional_order_item_count, 0)
            , 0
        )
    end as remaining_fills
    , coalesce(fill_order_counts.active_order_item_count, 0)
    + coalesce(fills.additional_order_item_count, 0)
        as total_order_item_count
    , fill_order_counts.last_filled_at
    , prescriptions.latest_fill_id
    , greatest(
        coalesce(prescriptions.last_updated, '1900-01-01')
        , coalesce(fills.last_updated, '1900-01-01')
        , coalesce(items.last_updated, '1900-01-01')
        , coalesce(doctors.last_updated, '1900-01-01')
        , coalesce(fill_order_counts.last_updated, '1900-01-01')
    ) as last_updated
    , prescriptions.vetcove_corporate_id
from prescriptions
left join fills
    on prescriptions.latest_fill_id = fills.id
left join items
    on prescriptions.item_id = items.id
left join doctors
    on fills.prescribed_by_doctor_id = doctors.id
left join fill_order_counts
    on prescriptions.latest_fill_id = fill_order_counts.prescription_fill_id