A lab platform whose numbers have to survive an audit
An internal application for high-throughput experiments in process development: raw data in, analysis in the browser, robot-ready worklists and reports out. It runs entirely inside the client's own infrastructure.
Starting point
Process development runs its experiments at high throughput: many parallel runs, with instruments writing out raw data as text files, alongside planning tables in Excel and external analytical results. All of that has to turn into a decision about which fractions to carry forward, and from there into an instruction a pipetting robot can execute.
Excel can do this, but it does not scale and it is hard to audit. In a regulated environment it is not enough for a number to be right. How it came about has to be traceable.
Goal
Capture experiments reproducibly, do the analysis in the application, generate the robot worklists automatically, and store all of it so that it stays auditable later.
The constraints come with the regulated setting. The application is custom-built, which puts it under documented verification. That means every calculation that influences an experiment result has to be reviewed, and the review has to be documented. On top of that: an audit trail, versioned deployments, and a change process with an approval attached to it. Hosting exclusively on the client's own premises.
Solution
A dashboard that covers the whole lifecycle of an experiment. Users upload the planning table and the raw data; the application checks the structure and assigns the measurements to individual fractions. The overview overlays the chromatogram on the plate map, so you can see where each fraction sits and how the curve runs.
Then the real work: combining fractions into pools by criteria of the user's choosing, or cherry-picking individual ones. The assignment shows up directly on the plate map.
From that selection the app produces the transfer instruction for the robot, plus transfer sheets and a consolidated report as a PDF. At the end something physical happens: the robot works through the list.
Outcome
“Stefan Linner's implementation has clearly improved what we can do with high-throughput biotech data. Our lab staff can work in a visually appealing interface straight after their experiments, with no time-consuming formatting first. Thanks to Stefan's deep knowledge of R and R Shiny, we were able to automate complex processes and special cases efficiently as well. He picks up our business processes quickly, which lets him contribute new ideas without a long ramp-up.”
Where it got tricky
Rounding by lab procedure instead of R's default
Rounding follows a written lab procedure. R's defaults do something else:
round() rounds halfway cases to even instead of up, and it also carries
floating-point error out of intermediate results, so the outcome can deviate from the procedure.
The uncomfortable part: there is no single correct implementation. For certain edge cases two
plausible variants give different results, each defensible. Those cases had to be decided
against the binding procedure and documented, rather than quietly buried in the code.
When two calculation paths have to produce the same number
Yield is computed in R and also written into the exported workbook as an Excel formula, so the file cross-checks itself. A check column asserts that both values agree. The original comparison tested for exact equality and regularly reported differences that were not differences: R and Excel diverge in the last bits. The classic float-comparison trap, made worse here by the fact that two deliberately independent calculation paths are supposed to produce the same result.
Raw instrument data is not a clean format
The instruments write tab-separated text. When a measurement exceeds the range, the cell holds the
word “Overflow” instead of a number, or its German equivalent on instruments set to German. A
single such cell turns the whole column into text, and decimal separators may be commas. An
unguarded as.numeric() quietly turns “Overflow” into a missing value and destroys a
distinction that matters in the lab: saturated is not the same as not measured. The calculation
also has to catch physically impossible inputs, such as negative readings.
Technical implementation
Every feature is a module with a fixed convention: UI, server and data logic are separate, and the pure calculation logic always lives in the helper file. That is the only way the calculations that feed into results can be tested at all without starting the app. The module tree is read automatically at startup, so a new module never has to be registered anywhere.
Everything persistent sits in a relational database behind a connection pool, so concurrent sessions do not block each other. All writes go through one central layer that also records an audit entry and only touches the rows that actually changed. Access rights come from central group membership and are checked server-side.
The rounding functions live in a small package of their own that is installed into the image. That sounds like overkill for two functions, but that is the point: they are versioned, documented and tested, instead of sitting somewhere in the code as a helper.
Reports and transfer sheets are produced with R Markdown. The templates ship inside the image and are copied into a temporary directory at runtime and rendered there, so the template itself stays untouched and concurrent runs do not collide.
Hosting and ongoing work
The deployment consists of three images: a base image with the pinned packages, the application image on top, and a test image on the same base. On every push the pipeline builds the test image first, runs the suite inside it and stops as soon as a test fails. Only then does it build the application image, tag it with the commit and roll it out. Production and development environments are separate; the deploy to production is its own step, tied to an approved verification document.
The tests are integration tests and run against a database. They cover the paths that influence results: reading the planning files and raw data, saving and loading experiments and pooling decisions, the database layer including the audit log, and the data preparation behind the analysis.