IDEXX Cornerstone migration: a vet clinic rebuild playbook

The on-prem Cornerstone server hummed in a closet behind the cattery. It ran on a Dell tower from 2017, a Sybase SQL Anywhere database in the basement of a veterinary clinic in Leiden, and the UPS in front of it was blinking amber three times a minute. Eleven years of patient records lived on that disk. So did every controlled-substance entry the practice was legally obliged to keep.

The chain had grown to 22 staff across three locations. The vendor had moved on. The on-prem build of IDEXX Cornerstone the clinic was running had reached end-of-life on their hardware, and the cloud version did not match the workflow they had spent a decade tuning. So we built them a new one.

This is the playbook. It is opinionated, it is boring on purpose, and it is the only kind of work that does not end with a lawyer in the room.

The constraint nobody wrote down

A vet clinic is not a CRM. The chart is not the most important thing on disk. The medication logbook is. Under Dutch veterinary medicine rules, every administration and dispensation of a prescription-only veterinary medicine has to be recorded with date, animal, owner, product, batch, quantity, and prescriber. Those records have to survive an inspection by the NVWA going back at least five years, and they cannot be backdated.

Cornerstone enforced this implicitly. The medication module was append-only in practice, even if the underlying SQL Anywhere database technically allowed updates. The clinic had never lost an entry. We needed the new system to be more obviously safe, not less.

Before we touched code, we wrote one sentence on a whiteboard: "the medication ledger is the spine; everything else hangs off it." Every architectural decision in this post comes back to that line.

What we were actually replacing

The footprint was larger than the procurement spreadsheet suggested. Cornerstone wasn't one application. It was patient and client records with multi-pet households and chained insurance billing. An appointment book linked to three calendars and a paper backup at each reception desk. Visit notes including SOAP charts, weights, and structured vaccine entries. The medication ledger, plus integration with the controlled-substance cabinet's printed log. Inbound lab results over a local IDEXX VetLab integration. And invoicing, with a quirky pro-rata insurance discount that nobody could explain but every receptionist applied correctly.

The destination stack was deliberately small. A Next.js front-end, a Supabase project (Postgres, Auth, Storage, Edge Functions), and a voice agent layer over Twilio for after-hours triage. No Kubernetes. No microservices. Two schemas inside the Supabase project: one live application schema, one append-only audit schema with its own role.

The architecture, drawn small

Three things move data in this system: the staff app, the IDEXX lab webhook, and the voice agent. They each write to the same Postgres database, but under different roles, and only one of them can touch the medication ledger.

[staff browser]  --->  Next.js (Vercel)   --->  Supabase Postgres
[IDEXX VetLab]   --->  Edge Function       --->  (live schema)
[Twilio call]    --->  Edge Function       --->  voice_request queue
                                                  |
                                                  v
                                            audit schema
                                            (append-only,
                                             hash-chained)

The split between schemas matters. The live schema is normal: patients, owners, visits, invoices. It gets updated, corrected, soft-deleted. The audit schema is paranoid: rows are inserted by a single trigger, never updated, never deleted, and each row carries a SHA-256 of the previous row plus its own content. If anyone tampered with row 401, the hash on row 402 stops matching, and the daily reconciliation job will notice within hours.

The medication ledger

This is the part the inspector will care about, so it was the part we built first. The ledger table looks roughly like this:

create schema audit;

create table audit.medication_log (
  id            bigserial primary key,
  recorded_at   timestamptz not null default now(),
  patient_id    uuid not null,
  product_code  text not null,
  batch         text not null,
  quantity_ml   numeric(8,2) not null,
  prescriber    text not null,
  reason        text not null,
  prev_hash     bytea,
  row_hash      bytea not null
);

revoke update, delete on audit.medication_log from public, app_user;
grant insert, select on audit.medication_log to app_user;

The hash is computed in a BEFORE INSERT trigger. It concatenates the canonical JSON of the new row's fields with the row_hash of the previous row (ordered by id) and runs SHA-256 over the result. A nightly job re-walks the chain and writes a signed daily anchor into a separate table. We never delete a wrong entry; we insert a correcting entry with a reason of "correction of row 1402, see chart note 1408", and the inspector sees both.

If you have never written one of these, the Postgres docs on CREATE TRIGGER and the Supabase guide to row-level security together give you everything you need. The trigger does the chaining. RLS keeps the wrong role from writing in the first place.

Eleven years of records, moved

SQL Anywhere is not as exotic as it looks. We connected to the Cornerstone export via ODBC from a small Python script running on a laptop on the clinic LAN, dumped each table to CSV with a stable ordering, and ran the same script three nights in a row to confirm row counts were stable. They were not, the first night. Cornerstone's audit table had a self-cleaning job that ran at 03:14. We rescheduled it and ran the dumps again.

The hard part was not the dump. It was the dedup. Eleven years of receptionists had created the same dog three times: "Bobby Jansen", "Bobby J.", "Bobby (Jansen)". We did not auto-merge. We built a small reviewer UI that put two candidate records side by side with their visit history, and a vet nurse spent four mornings clicking through. We logged the merge decisions to a fourth table so we could undo any of them later. Three of them, we did.

Radiographs and ultrasound stills lived in a shared SMB folder, not in the database. We hashed each file by content, stored a copy in Supabase Storage with the hash as the path, and pointed the new patient records at the storage URL. Duplicates collapsed automatically. The folder shrank from 412 GB to 188 GB.

The voice agent we do not trust

The clinic wanted an after-hours triage line. A pet owner calls at 22:47, the receptionists are home, and the on-call vet needs to be woken only for actual emergencies. So we built a voice agent on Twilio plus a language model, with one constraint that we wrote into the system prompt and enforced at the database layer: the voice agent cannot write a single character to the patient chart or the medication ledger.

What it can do is fill in a row in voice_request, a queue table in the live schema. The row contains the audio recording, a transcript, a structured triage score, and the caller's stated reason. In the morning, a vet opens the queue, listens to anything the on-call colleague flagged, and either closes the request or promotes it into a visit. Promotion writes the chart entry under the vet's account, not the agent's.

This sounds paranoid. It is. The recent run of headlines about AI agents going amok inside other people's production systems is the polite version of what happens when you let a language model touch a regulated record. In a veterinary clinic, "amok" means an incorrect dose entry that an inspector reads in February. We would rather a tired vet read a transcript in the morning than discover the agent autonomously logged Metacam against a cat with a noted NSAID allergy.

Cutover, by stopwatch

The clinic could not be down. Animals do not reschedule. We dual-ran the systems for fourteen days. The Cornerstone install remained authoritative. Every action staff took in the new app also created a shadow entry in Cornerstone via a small adapter we wrote against its export API. At the end of each day, a reconciliation script compared counts and flagged drift.

There was always drift. Most of it was timezone-related (Cornerstone stored local, we stored UTC). Some of it was real, and the staff caught two bugs we would have shipped otherwise. On the cutover Saturday, the clinic closed at 13:00, we cut traffic at 13:30, and the medication ledger went read-only on the old system at 13:45. We had paper forms at each reception desk in case the new system fell over. They were not used. We kept the Cornerstone server running, powered down, in the closet for ninety days, then took a disk image and shipped the drive to the clinic's accountant for cold storage.

What broke, and what we learned

Three things broke in week one. The IDEXX VetLab webhook started sending a new field nobody had documented, and our Edge Function rejected the payload until we relaxed the schema check. The receipt printer at the Voorschoten location refused to talk to anything that was not Cornerstone, because the driver was hard-coded to a specific Windows COM port. We bought a new printer for €184 and threw the old one out.

The third thing was the most expensive lesson. One of the senior vets had developed a Cornerstone muscle memory: Ctrl+Shift+M to open the medication module. We had not mapped it. She lost six minutes a day for the first week. We added the keymap on Monday of week two. Map the old shortcuts. Always.

The boundary that mattered

When we built this rebuild for the Leiden chain, the part that took the most thought was not the Next.js front-end or the voice triage. It was the boundary between the live schema and the audit schema, and we ship the same boundary on every legacy migration with a regulated record at its center.

If you run a clinic, a pharmacy, or any operation with an inspector-facing log, do this today. Open the relevant table in your current system, start a transaction, run an UPDATE against a row from last year, then ROLLBACK. If that UPDATE succeeded, your audit trail is a convention, not a guarantee. That five-minute test is the gap a real rebuild closes.