---
title: Medical
slug: redshift/medical
icon: {"lucideIcon":"Stethoscope"}
docTags: 
createdAt: 2026-10-01T12:31:18.006Z
---

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](docId:1MynhjoeVtcGFfGuXZQyq) 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](docId\:ihfOVSdsrTypRZRuq0DWW) 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`