Reverse-engineering a 16-year-old behavioral-health EHR.
Some software has been in production longer than the team maintaining it. We embedded into a sixteen-year-old electronic health record platform, built on a proprietary language with no public documentation, to fix deep stored-procedure bugs and ship the clinical, billing and scheduling features behavioral-health agencies run every day.
Sixteen years of software, zero Stack Overflow answers
The platform is a comprehensive behavioral-health EHR, sixteen years of evolution shaped by the direct input of providers, county-based networks and managed-care authorities. It is exactly the kind of system that runs quietly under thousands of clinicians and is far too entangled to rip out and replace.
The client, a US-based IT partner to health-services providers and organisations, brought us in to extend and harden it. The catch: a major part of its business logic runs on a proprietary language you cannot Google, and the rest lives in SQL stored procedures, hundreds of lines deep.
One system for everything an agency does
EHR adoption is effectively mandatory in the US, where CMS led the transition with incentives. Behavioral-health agencies needed the dozen things they do every day centralised into a single platform.
- Schedule patients with the right therapist and send email / text reminders.
- Capture every session’s progress in one place: notes, diagnoses, treatment plans, forms.
- Store billing against current authorisation criteria, ready to claim.
- Order labs electronically and route the results back to the clinician’s dashboard.
The modules we worked across
A complex, role-aware system (Supervisor, LCSW, MD and more) where authorisations drive what can be claimed.
Clinical
Charting, progress notes, treatment plans with goals & objectives, diagnoses.
Billing
Claim authorisation, claim creation & submission, procedure code sets.
Scheduling
Appointments driven by therapist work schedules and service types.
Reports
Custom clinical and billing reports, printable for hand-off.
Medication management
Allergy classification and medications prescribed to patients.
eLabs ordering
Electronic lab orders with result sets: BP, height, weight and more.
Learning a language the internet had never heard of
The platform’s display and business logic (active-client lists, remaining authorisations, units, durations, unsigned-note counts) is written in a proprietary, object-oriented language: inheritance, classes, method calls by value and reference, composing new classes from old. The rest sits in SQL stored procedures reached through the database.
There is no public documentation. No articles, no Stack Overflow, nothing on the web. Our entire knowledge base was two internal PDFs and a patient US team willing to answer questions. So we did what you do with any black box: read it, instrument it, and rebuild the mental model from first principles, with daily standups and weekly deep-dives to compare notes.
The lab-routing bug: 700 lines of SQL
When we picked it up, electronic lab orders were created fine, but the results never made it back to the clinician's dashboard.
The routing lived in a single ~700-line stored procedure. It took us three days, as a team, to map the data flow (what the proc was doing and where) before we could even name the bug. The root cause: the result set came back in HL7 format, and the procedure matched it against the dashboard using the wrong XML structure, so nothing routed.
We aligned the matching to the current HL7 standard, corrected the result set’s XML shape, then repositioned the query and added a ROW_NUMBER() OVER (PARTITION BY …) window to split the incoming results correctly (one clean row per order), which fixed the routing and optimised the query at the same time.
1-- The lab result came back as HL7, but the proc matched it against the2-- dashboard with the wrong XML structure, so nothing routed. We aligned the3-- match to the current HL7 standard and split the set with a window function.4;WITH parsed AS (5 SELECT obr.value('(@orderId)[1]', 'int') AS order_id,6 obx.value('(loinc/text())[1]', 'varchar(20)') AS loinc,7 obx.value('(value/text())[1]', 'varchar(64)') AS result,8 ROW_NUMBER() OVER (9 PARTITION BY obr.value('(@orderId)[1]', 'int')10 ORDER BY obx.value('(@seq)[1]', 'int')11 ) AS rn12 FROM @resultXml.nodes('//OBR') AS o(obr)13 CROSS APPLY obr.nodes('OBX') AS x(obx) -- HL7-correct shape14)15INSERT INTO dashboard_result (order_id, loinc, result)16SELECT order_id, loinc, result17FROM parsed18WHERE rn = 1; -- exactly one row per order, correctly partitioned| Before | After | |
|---|---|---|
| HL7 match | Wrong XML structure, so no rows matched | Aligned to the HL7 standard |
| Result set | Duplicated / mis-ordered rows | One row per order via window partition |
| Outcome | Results never reached the dashboard | Routed correctly, and faster |
What we shipped into it
Once the model clicked, the features came, including a few the team had been putting off.
A 20-window mega-form
A single form composed of 18-20 sub-windows: the deep end. Slow to build, but it burned the platform’s syntax and structure into muscle memory and made everything after it faster.
Authorisation calculations
Progress-note authorisation math that honours every billing scenario: the one feature that uses class variables so it can compute from anything the window inherits.
Coverage / maintenance window
When a therapist is on leave or moves on, reassign their tasks, appointments, notes-for-review, unsigned forms and billing matrix to other staff, so no work falls through the cracks.
A faster scrum loop
We helped the scrum master add a process-improvement step to the project lifecycle that lifted developer experience and cycle time across the team.
What it runs on
A deliberately old-school stack, which is exactly why reading it well mattered more than rewriting it.
Proprietary platform language
The OOP-style language the EHR’s screens and business logic are written in, learned from the inside.
SQL Server + stored procedures
Hundreds of lines of business logic per proc, where most of the real behaviour (and the bugs) lived.
RedGate
Database development, comparison and source control across schema and procedures.
CI/CD + Octopus Deploy
Repeatable build-and-release into the platform’s environments.
A legacy platform, modernised from the inside
Legacy EHR codebase we embedded into and extended
Lines of SQL reverse-engineered to fix the lab-routing bug
EHR modules we worked across or extended
- A comprehensive EHR that streamlines enrolment and treatment for behavioral-health agencies.
- One stop to schedule, document, add guarantors and file claims against authorisations, diagnoses and medications.
- Electronic patient detail with text / SMS appointment reminders.
- Report generation that lets agencies print and hand off the right patient information.
Legacy isn't a liability if you're willing to read the code
The instinct with a sixteen-year-old, undocumented system is to quarantine it. But the fastest path to value was the unglamorous one: read it carefully, instrument it, learn its language on its own terms, and fix the real root cause, not paper over it. Done that way, a legacy platform keeps doing its job, only better.
Sitting on a system everyone’s afraid to touch? We’re comfortable in the old code as well as the new.
Talk to engineering