Skip to main content

​
Columns

ColumnDescriptionAPI Field Name
medical_record_idPrimary key and medical record unique identifier.id
createdThe timestamp when the medical record was created.
modifiedThe timestamp when the medical record was last modified.
is_deletedIndicates whether the medical record is deleted.
clinic_patient_idForeign key to the patient & client_enriched table. The identifier of the patient associated with the medical record.clinic_patient_id
clinic_client_idForeign key to the client & client_enriched table. The identifier of the client associated with the medical record.clinic_client_id
clinic_idForeign key to the clinic & clinic_enriched table. The identifier of the clinic associated with the medical record.clinic_id
clinic_nameThe name of the clinic associated with the medical record.
appointment_idForeign key to the appointment & appointment_enriched table. The identifier of the appointment associated with the medical record, if applicable.appointment_id
invoice_idForeign key to the invoice & invoice_enriched table. The identifier of the invoice associated with the medical record, if applicable.invoice_id
subjectiveSubjective notes recorded in the medical record.subjective
objectiveObjective observations recorded in the medical record.objective
planThe plan or treatment outlined in the medical record.plan
assessment_idsArray of assessment IDs linked to this medical record.assessment_ids
date_postedThe timestamp when the medical record was posted.date_posted
finalized_atThe timestamp when the medical record was finalized.finalized_at
followup_appointment_idsArray of appointment IDs for follow-up appointments associated with this medical record.followup_appointment_ids
last_updatedThe timestamp when the medical record was last updated in the source table. This is the most recent timestamp from all related tables (medical_record, clinic, client, followup_appointment_ids, assessments). Note: the client table is joined for last_updated tracking only; no client columns are selected.
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 medical record data
    pre-joined for easy consumption, mirroring the medical records API.
*/

"
    )
}}

with medical_records as (
    select * from {{ ref('medical_record_v2') }}
)

-- Assessment IDs by medical record
, assessments as (
    select
        medical_record_id
        , max(last_updated) as last_updated
        , array_agg(id) within group (
            order by id
        ) as assessment_ids
    from {{ ref('assessment') }}
    where
        medical_record_id is not null
        and is_deleted = false
    group by medical_record_id
)

, clinics as (
    select * from {{ ref('clinic') }}
)

-- Followup appointment IDs by medical record
, followup_appointment_ids as (
    select
        followup_for_medical_record_id as medical_record_id
        , max(last_updated) as last_updated
        , array_agg(id) within group (
            order by id
        ) as followup_appointment_ids
    from {{ ref('appointment') }}
    where
        followup_for_medical_record_id is not null
        and is_deleted = false
    group by followup_for_medical_record_id
)

select
    medical_records.id as medical_record_id
    , medical_records.created
    , medical_records.modified
    , medical_records.is_deleted
    , medical_records.patient_id as clinic_patient_id
    , medical_records.client_id as clinic_client_id
    , medical_records.clinic_id
    , clinics.name as clinic_name
    , medical_records.appointment_id
    , medical_records.invoice_id
    , medical_records.subjective
    , medical_records.objective
    , medical_records.plan
    , coalesce(
        assessments.assessment_ids
        , array_construct()
    ) as assessment_ids
    , medical_records.date_posted
    , medical_records.finalized_at
    , coalesce(
        followup_appointment_ids.followup_appointment_ids
        , array_construct()
    ) as followup_appointment_ids
    -- Last updated & corporate ID
    , greatest(
        coalesce(medical_records.last_updated, '1900-01-01')
        , coalesce(clinics.last_updated, '1900-01-01')
        , coalesce(followup_appointment_ids.last_updated, '1900-01-01')
        , coalesce(assessments.last_updated, '1900-01-01')
    ) as last_updated
    , medical_records.vetcove_corporate_id
from medical_records
left join assessments
    on medical_records.id = assessments.medical_record_id
left join clinics
    on medical_records.clinic_id = clinics.id
left join followup_appointment_ids
    on medical_records.id = followup_appointment_ids.medical_record_id