Medical
Medical data centers on the SOAP record (new_file_cases), which is the bridge between a patient's clinical history and the financial transaction it generated. See Database Overview for how this fits with pet and sale.
new_file_cases — the SOAP record
- pet_id → pet.id
- sale_id → sale.id
- appointment_id → appointment.id
- vet_id — assigned vet; responsible_id — the starred/favorite SOAP vet (can differ from vet_id)
- status — 1 = draft, 2 = saved, 3 = closed, 4 = hospitalized
- diagnosis, presumptive_diagnosis, treatment, recommendations, comments — free-text clinical fields
- reason_visit_type_id — links to visit type (see Operational for available_services)
- opened_at, closed_at, follow_up_date
diagnostics — diagnosis reference list
Not previously documented here.
- clinic_id, name, description, recommendation
- added_by — vet who added the custom diagnosis to the clinic's list
- This is the clinic's configurable list of diagnoses, not a per-patient record — a per-patient diagnosis is captured as free text on new_file_cases.diagnosis, not as a foreign key into this table.
prescriptions
Not previously documented here (previous list had this misspelled as "presrciptions").
- record_id → new_file_cases.id, pet_id → pet.id, vet_id, clinic_id
- product_id → products.id
- quantity, max_refills, prescribed_at, expires_at
- status, delivery_status, delivery_provider — relevant if the clinic uses e-prescription/delivery integrations
Custom checkup fields — new_checkup_groups, new_fields, new_field_values, new_field_options, new_group_rows
Not previously documented here. This is a small metadata system that lets each clinic define custom structured fields on the SOAP record (beyond the fixed diagnosis/treatment/comments text fields) — e.g. a custom vitals table or a checklist.
- new_checkup_groups — a named group of fields, e.g. "Vitals"
- new_fields — an individual field within a group (row_id groups fields into a row layout); settings holds field-type-specific config as JSON
- new_field_options — for dropdown/select-type fields, the list of selectable values
- new_field_values — the actual value entered for a given field, tied to pet_id, file_id (→ new_file_cases.id), and field_id; monitorable_type/monitorable_id allow the same field system to attach to record types beyond the SOAP record
- new_group_rows — layout ordering for rows within a group
If a clinic has heavily customized their checkup templates, most of the "interesting" structured clinical data (vitals, custom checklists) will live in new_field_values, not in new_file_cases itself.
Supporting joins
- vets — id referenced by both new_file_cases.vet_id and .responsible_id
- pet — id = new_file_cases.pet_id
- sale — id = new_file_cases.sale_id
- appointment — id = new_file_cases.appointment_id