Medical Record

Medical record enriched model providing medical record, clinic, client, appointment, and invoice data.

• Each row represents a medical record.
• Compatible with the Vetcove API endpoint(s): /medical-records/
• API endpoint(s): https://connect.vetcove.com/ext/v2/medical-records/
• Snowflake view: phd_enriched.medical_record_enriched

Columns

Column	Description	API Field Name
medical_record_id	Primary key and medical record unique identifier.	id
created	The timestamp when the medical record was created.
modified	The timestamp when the medical record was last modified.
is_deleted	Indicates whether the medical record is deleted.
clinic_patient_id	Foreign key to the patient & client_enriched table. The identifier of the patient associated with the medical record.	clinic_patient_id
clinic_client_id	Foreign key to the client & client_enriched table. The identifier of the client associated with the medical record.	clinic_client_id
clinic_id	Foreign key to the clinic & clinic_enriched table. The identifier of the clinic associated with the medical record.	clinic_id
clinic_name	The name of the clinic associated with the medical record.
appointment_id	Foreign key to the appointment & appointment_enriched table. The identifier of the appointment associated with the medical record, if applicable.	appointment_id
invoice_id	Foreign key to the invoice & invoice_enriched table. The identifier of the invoice associated with the medical record, if applicable.	invoice_id
subjective	Subjective notes recorded in the medical record.	subjective
objective	Objective observations recorded in the medical record.	objective
plan	The plan or treatment outlined in the medical record.	plan
assessment_ids	Array of assessment IDs linked to this medical record.	assessment_ids
date_posted	The timestamp when the medical record was posted.	date_posted
finalized_at	The timestamp when the medical record was finalized.	finalized_at
followup_appointment_ids	Array of appointment IDs for follow-up appointments associated with this medical record.	followup_appointment_ids
last_updated	The 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_id	The unique identifier of the corporate group associated with this record.

Relationships

• Client via clinic_patient_id
• Clinic via clinic_id
• Appointment via appointment_id
• Invoice via invoice_id

SQL Definition

View SQL

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