Clinical data engineering · case study
Every value knowswhere it came from.
Ingests from source systems that were never designed to agree, standardises them, finds what is wrong, and produces submission-ready datasets in which every row can be traced back to what it was built from. Built solo, with no third-party runtime dependencies.
Measured, not estimated
What it does to real files
Four source systems, none of them written for this platform, each with different column names and conventions for the same clinical concept. The figures below are produced by the running engine on every deployment, not recorded from a good run.
The sample files are deliberately hostile: inconsistent units, ambiguous dates, duplicate extracts distinguished only by a timestamp, and two systems reporting the same measurement differently. A pipeline that has only run against clean data has not been tested, so the test corpus is the messy version.
| Source | Shape | Reads | Facts |
|---|---|---|---|
| REDCAP | Wide | Electronic data capture, all sites | 4,741 |
| CENTRAL_LAB | Long | HL7 feed, one row per analyte | 3,864 |
| EHR | Wide | Hospital registry, carries identifiers | 1,469 |
| SITE_AE_LOG | Wide | Coordinator’s adverse-event log | 653 |
One generic reader handles both shapes. Which columns mean what is a TOML file per source, so onboarding a study is configuration rather than a release.
Seven stages, in order
The pipeline
- 01ConnectFour source shapes read by one configuration-driven reader
- 02MapColumns matched to canonical fields on clinical evidence — units, ranges, codelists, vocabulary
- 03DeriveComputed fields, versioned and unit-checked, recording the exact inputs used
- 04ValidateRules bound per study; findings recomputed on every request, never stored
- 05CorrectSupersede rather than edit, then recompute everything downstream
- 06TraceLineage as a walk over the facts themselves — no separate log to keep honest
- 07ExportSDTM domains, ADaM, Define-XML, with a traceability companion
The operation worth watching
A correction is reviewed before it is made
Changing one collected value changes everything computed from it. So a correction is planned first: the system reports what the change would do — every downstream value it would move, what each would become, and anything it would block — and writes nothing. Only then is it committed, by someone holding a different permission from the person who proposed it.
Nothing is overwritten. A superseded value stays available with the reason it was replaced and the authenticated person who replaced it, because 21 CFR 11.10(e) asks for an audit trail identifying who changed a record — and a trail the actor writes for themselves records nothing.
Three that were nearly missed
Defects worth writing down
Each of these passed the tests that existed at the time. They are here because what a system does when it is wrong is more informative than what it does when it works.
A query plan that got 40× slower after ANALYZE
liveness check: 145 ms → 5,872 ms, with no code change
Finding which facts are currently in force was a NOT EXISTS subquery.
Once the table had statistics, SQLite’s planner re-chose the join order and the
query fell off a cliff — in the direction that only appears once there is
production-sized data.
Rewritten as a left-join anti-join, which the planner cannot reinterpret.
The regression test asserts on the query plan, not the wall clock. A timing assertion on shared CI hardware is a coin flip; the plan is the thing that actually changed.
An identifier riding along on a non-identifier
source_record_ref = “MRN=MRN-100001;row=136”
Fields marked as protected health information were correctly withheld. But every fact also carries a pointer back to the source record it came from, and for one source that pointer was keyed by medical record number — so a caller reading ordinary blood pressures received the patient’s MRN attached to each one.
Withholding the fields was not enough. Which reference components carry an identifier is now read from the source definition, and those are redacted while the row pointer survives, so a reference still locates the record without disclosing whose it is.
A privacy control that covers the obvious surface and not the adjacent one is worse than none, because it is believed.
Concurrency that returned plausible wrong rows
grant role: None · issued_at: not a string
A WSGI application is served by threads it did not create, so the first symptom was every request failing on a thread-bound SQLite connection. Lifting that guard where the runtime reports serialised access fixed the crash — and was only half the answer.
Under sixteen-way load, the token store began returning malformed rows. Several methods issue more than one statement to answer one question, and two threads interleaving on one connection read rows from the wrong statement.
It never raised a lock error. It returned a plausible wrong row, which is invisible to a test that checks status codes — so the regression test asserts on response bodies under concurrent load.
Natural language, without a new trust boundary
The assistant is a client, not an insider
It answers questions by calling the same API every other client uses, as whoever is operating it. A site coordinator’s assistant sees a site coordinator’s subjects. Someone who may propose a correction but not commit one has an assistant that can do exactly that and no more. Every call it makes lands in the audit trail attributed to a person.
It cannot decide what it is allowed to see. Whether identifiers may leave the deployment is a deployment setting rather than a tool argument, so no phrasing of a question can talk it into them, and it writes nothing without a person confirming. Where a value is withheld it is marked as withheld rather than omitted — a model handed a blank reports the value as missing, and someone acts on a record they believe is incomplete.
Stated plainly
What is not built
A case study that lists only what works is a sales page. These are the things that would have to exist before anyone ran a real study on it.
- Tenant isolation — one deployment currently means one customer
- Encryption at rest and in transit — not implemented
- De-identification layer — the PHI flags exist to enable it; it does not yet exist
- Long-format inference — HL7-style feeds still need their key mappings authored by hand
- Two placeholder derivations — transparent point scores, labelled as such, to give the cascade a three-deep chain; not validated clinical instruments
- No lab collection date — it travels in the record reference rather than the schema
Constraints held throughout
How it is built
| Constraint | Why | Held at |
|---|---|---|
| Zero third-party runtime dependencies | A clinical system inherits the vulnerabilities of everything it imports; CSV is read with the standard library | asserted in CI |
| Corrections never overwrite | Enforced by the database rather than by convention, so it cannot be bypassed in code review | enforced |
| Studies are configuration | Schema, derivations, mappings, rules and export all in per-study TOML, validated at load | 2 studies, no Python |
| Plan and apply are separate permissions | So review can be a second pair of hands, and survives the network | across 2 requests |
| Findings are recomputed | A stored finding outlives the data that justified it | every request |
| Test suite runs on the real files | A configuration change that breaks the engine fails every test | 677 tests |