I build the data platform a clinical lab runs on.

The problem

The lab's inventory lives in an electronic lab notebook. Our database needs to reflect it closely enough that a scientist can trust a query instead of walking to a freezer. The vendor offers webhooks, and the obvious design is to receive each event and write it down.

That design fails quietly. In production, a measurable share of webhook deliveries never arrive. Nothing errors. The database simply drifts from reality, and the first symptom is somebody being told a sample exists when it does not. A system that is wrong but confident is worse than one that is visibly down.

What I decided, and why

Treat webhook delivery as a hint, not as the source of truth

The receiver's only job is to accept the payload, put it on a queue, and return immediately. It does no parsing and no business logic, so a malformed or surprising payload cannot cost us the event. Correctness moved out of the request path entirely, which also means the vendor's retry behaviour stops being our problem.

Reconcile on a schedule instead of hoping for completeness

Because delivery cannot be relied on, a scheduled job walks the notebook's API over a look-back window and compares it against what we hold. Anything missing is pulled in. This is the part that turns completeness from a hope into a property: the steady state is correct even if every webhook in a window is lost.

A second scheduled job does a different thing, and keeping them separate matters. Reconciliation asks “is anything missing?”. Change detection asks “has anything I already hold been edited?”. Those questions have different look-back windows and different costs, and a single job doing both ends up doing neither well.

Detect change by hashing fields, not by trusting timestamps

Reconciliation naively implemented would rewrite everything on every pass, which destroys any useful audit history and makes real changes impossible to see. Each record's meaningful fields are hashed, so a pass can tell the difference between “seen again” and “actually changed”, and only genuine changes are written.

Give every event a receipt, and make the receipt the source of safety

Each event gets a row recording where it is: queued, processing, or finished. That row is what makes reprocessing safe, because the same event arriving twice finds its own receipt rather than creating a second sample.

The subtle case is a crash halfway through. The queue makes the message visible again after a timeout, and the receipt is still sitting in processing. Matching that state, rather than only matching untouched work, is what lets the retry resume instead of quietly discarding the message. Treating a half-finished receipt as if it were finished is the bug that would be hardest to notice.

Normal run The function crashes The queue redelivers

Archive the raw payload before interpreting it

Every payload lands in object storage exactly as received, before any parsing. When a mapping turns out to be wrong months later, the original is still there, and there is an endpoint that feeds archived payloads back through the same processing path to rebuild the history. Parsing logic is the thing most likely to be wrong; the raw event is the thing least likely to be.

What this guarantees

• Inventory converges on the notebook even during a total webhook outage.
• A lost message delays a row. It does not silently lose one.
• Audit history shows real changes, not reconciliation noise.
• A parsing mistake is recoverable, because the raw event was never discarded.

What I would tell someone starting this

The instinct is to make the receiver clever: validate, deduplicate, retry. That is effort spent in the one place that cannot be made reliable, because you do not control the sender. Put the cleverness in reconciliation, keep the receiver boring, and the whole system gets easier to reason about.

The problem

Clinical and research workflows had grown up with separate identifier schemes. Some entities had no identifier at all: a processed spectrum, for instance, could not be named in a way that distinguished it from the raw acquisition it came from.

Where identifiers were missing, names filled the gap. Names are typed by people, under time pressure, in several systems, with no enforcement. So meaning migrated into the one field guaranteed to be inconsistent. This is a slow failure: nothing crashes, workarounds become standard practice, and traceability quietly becomes partial instead of guaranteed.

What I decided, and why

Identifiers are the system of record; names are a convenience

Names stay, because people need to read something human. But nothing downstream is allowed to depend on them. Every relationship that matters is expressed as a key and a row, which means traceability stops degrading as volume grows and as more people touch the data.

A parent-and-child graph, not a linear chain

The tempting model is a chain: patient, order, sample, aliquot. It breaks on the first pooled sample, and again on the first research sample with no patient, and again on a rerun. A parent-and-child relationship at the inventory level absorbs pools, splits, reruns and collaborator material without asking the lab to change how they work. The model bends so that the people do not have to.

Design the whole architecture, then deliver it in phases

It would have been faster to ship an identifier registry alone. It would also have guaranteed drift: samples handled physically before an identifier exists, aliquots with no durable parent, corrections needing human interpretation. So the full entity model was designed up front and delivery was phased against it. Phasing the delivery is fine. Phasing the design is how you get a system that has to be replaced.

Give spectra and processed spectra real identifiers

The gap that started this project was the most valuable thing to close. Once a processed spectrum has its own identifier and a recorded parent, the chain from a patient's sample through acquisition and processing to a reported result can be walked in either direction, by a person or a query.

What this guarantees

• Spectra and processed spectra are addressable, which they were not before.
• Samples stay linked to patients across both product and research workflows.
• Pooled, split and rerun material is representable without a workaround.
• The path from sample to report can be traced in both directions.

What I would tell someone starting this

A rigid identifier scheme looks fine for about a year. Design for the exceptions you already know about, because they are not exceptions, they are the workload. And watch for meaning leaking into free-text fields; that is the earliest signal that the model is too tight.

The problem

NMR acquisition quality depends on parameters that drift: temperature, shimming, field stability. A long overnight run can degrade partway through, and if nobody finds out until the batch completes, the cost is instrument time that cannot be recovered and clinical reporting that slips.

What I decided, and why

Compare against reference rule sets, not a single threshold

Field drift tolerance and temperature tolerance have nothing to do with each other. One global threshold does both jobs badly: it fires constantly on parameters that vary normally, and stays silent on parameters where a small movement is meaningful. Each parameter gets its own reference range, which is more configuration and considerably less noise.

Spend the effort on alert fatigue, not on detection

Detection is the easy half. An alert the team learns to ignore is worse than no alert, because it also teaches them to distrust the next one. So there are cooldowns to survive transient spikes, and notifications are grouped rather than fired per reading. I spent as long on restraint as on the checks.

Log to a database, not to files

The first version wrote quality metrics to flat files, which answers “what is happening now” and nothing else. Questions worth asking are comparative: was this parameter stable across the batch, is this instrument worse than it was last month. Those are queries, so the metrics belong in PostgreSQL, indexed by session and parameter.

Alert where the team already is

The notification goes to Microsoft Teams, because that is where the lab already works. A dashboard nobody opens is not monitoring; it is a monitoring-shaped object.

What this guarantees

• Acquisition is checked against reference ranges on every run.
• Drift reaches a human while the instrument is still running.
• Quality history is queryable by session and by parameter, so trends are answerable.
• Alert volume stays low enough that alerts still get read.

What I would tell someone starting this

Build the thresholds with the people who run the instrument, not from the specification sheet. They already know which parameters wander harmlessly and which ones mean stop. That conversation is worth more than any amount of statistical tuning.

The problem

An order arriving from an external lab partner has to become a sample, a set of measurements, and eventually a clinical report. Every step has a regulatory expectation attached, and every manual handoff is somewhere a mistake can enter without a trace.

What I decided, and why

Put a gateway in front of everything

External traffic terminates at API Management, which owns authentication, routing and policy. The functions behind it do not each re-implement a security posture, and the boundary an auditor cares about is one component rather than a diagram full of arrows.

Model cancellation as a first-class path

Orders get cancelled and amended. If that is handled by someone editing a row, the audit trail is a story rather than a record. Verification and cancellation are explicit flows with their own states, so the unhappy paths are as well-defined as the happy one, and neither needs an operator to intervene.

Secrets in Key Vault, never in configuration

Unremarkable, and worth stating because the alternative is so common. Nothing in source control or app settings holds a credential, and rotation does not require a deployment.

Approvals through the tool people already use

Report approval runs through Power Automate into Teams, as an actionable card. The approval is recorded as a state change in the database rather than living in somebody's inbox, which is the difference between a workflow and a habit.

What this guarantees

• Order state is explicit at every point, including when an order is withdrawn.
• The security boundary is one reviewable component, not a property of many.
• Approvals leave a record in the database, not only in a chat history.
• Credential rotation is an operational task, not a code change.

The problem

Analytical code that influences a clinical result carries the same expectations as any other regulated artefact: you should be able to say what ran, when, on what input, and who reviewed it. Reproducibility by convention does not survive a busy quarter.

What I decided, and why

One deployment path for every application, no exceptions

Standardised pipelines mean a change reaches production the same way every time, through review and a recorded build. The value is not the automation, it is the absence of a second, undocumented route. A single exception makes the guarantee worthless.

A staging environment that actually resembles production

Refactoring a clinical application with no safe place to test it means the test is the release. Staging is unglamorous infrastructure that mostly pays off on the days you were going to make a mistake.

Write outputs once, immutably, and serialise them

Model and analysis outputs are stored so that they cannot be overwritten in place, with enough structure to be verified later. A result you can regenerate is useful; a result you can prove was the one used is what regulation is actually asking for.

The database schema is code as well

Application code being reviewed is only half of it, because a schema change can break a clinical report just as thoroughly. The logging schema is written declaratively, reviewed like any other change, and applied as ordered, checksummed migrations across more than one instance. Checksums are the part that matters: they turn “we think these databases match” into something a machine can verify.

What this guarantees

• Every change to a clinical application is reviewed and recorded before release.
• Schema changes are versioned and checksummed, so two instances can be proven to agree.
• There is no informal path to production for anyone, including me.
• A past result can be produced as evidence, not reconstructed from memory.

The problem

A raw acquisition is not a usable spectrum. It needs phase correction, baseline correction and calibration against a known reference before anything downstream can read it. Done by hand in the instrument software, that is several minutes of a trained person’s attention per sample, and two analysts will not produce byte-identical output from the same input.

For a research question that is tolerable. For a clinical workflow it is a reproducibility problem, because the processing itself becomes an undocumented step between the instrument and the result.

What I decided, and why

Bridge the instrument software rather than reimplement it

The instrument vendor’s software is the authority on how a spectrum is processed, and it scripts in Jython. So the automation is a thin Jython layer that drives the vendor tooling and hands off to Python 3 for the analysis, rather than a from-scratch reimplementation of well-understood spectroscopy. Writing my own phasing algorithm would have been more fun and much harder to defend.

Calibrate against a known standard, not against the tallest peak

Chemical shift referencing needs a fixed point. Calibrating to a reference compound added to the sample gives one that does not move with biology, and the processing detects the reference and navigator peaks rather than assuming where they will be.

One task per spectrum, on a pool that scales to the run

Processing is embarrassingly parallel: spectra do not depend on each other. So the work runs as a task per spectrum across a batch pool, and the same code runs locally or on the pool depending on a single mode switch. That keeps development honest, because the thing you debug on your laptop is the thing that runs on the cluster.

Every run leaves an audit record next to its output

Each run gets its own directory keyed by the ticket, the date and the operator, and writes both a human-readable log and a structured record beside the processed data. The point is not tidiness. It is that six months later you can say which code, which inputs and which person produced a given spectrum, without relying on anybody’s memory.

What this guarantees

• The same acquisition produces the same processed spectrum every time.
• Processing scales with the size of the run rather than the number of analysts.
• Every processed spectrum has a record of the code, inputs and operator behind it.
• The local and cluster paths run identical code, so one cannot drift from the other.

What I would tell someone starting this

Automate the boring, well-specified steps and leave the judgement calls alone. Phasing and baseline correction have right answers. Deciding whether a spectrum is fit to report does not, which is why that stayed a separate model with a human in the loop.

The problem

Metabolite lookup was exact-match. Scientific naming does not cooperate with that: one compound carries synonyms, abbreviations and structural aliases, so a correct query returns nothing and the analyst has to guess again. A silent dead end is the worst possible search result, because it looks like an answer.

What I decided, and why

Embeddings computed and indexed in-tenant

A hosted vector service would have been quicker to stand up and would have sent compound identifiers and query patterns outside the compliance boundary. A local model and a local index keep both inside it. The constraint chose the architecture, and the architecture turned out to be cheaper anyway.

Diversify results before showing them

Ranking by similarity alone fills the first page with near-duplicates of the same compound. Re-ranking for relevance and diversity together means the first screen offers chemically distinct options, which is what the analyst was actually asking for.

What this guarantees

• Synonyms and spelling variants resolve to the same compound.
• No embedding or query leaves the tenant.
• The first page of results is chemically distinct, not the same molecule eight times.

The problem

Spectra carry noise, shimming artefacts and baseline distortion. Expert review catches these and is also the bottleneck. Automating the judgement is attractive and dangerous in equal measure, because a bad spectrum passed off as good is a clinical problem, not an accuracy statistic.

What I decided, and why

Treat the spectrum as a sequence

A tree-based model sees each frequency bin as independent, which throws away the fact that an artefact at one shift distorts its neighbours. A one-dimensional convolution over the spectrum keeps that locality, which is the entire signal here.

Calibrate the threshold well above the default

The decision boundary is not a modelling choice, it is a clinical one. A false pass is far more costly than a false flag, so the operating point is set to push uncertain spectra to a person. The model's job is to be confident only when it has earned it.

Generate failures from domain knowledge

Failing spectra are rare, so the classes are imbalanced. Oversampling the real failures risks leaking them into validation. Synthesising artefacts from known shimming error patterns produced a balanced training set while keeping validation honest.

What this guarantees

• Low-confidence spectra go to a human instead of through the pipeline.
• The same spectrum receives the same verdict on any day, from any reviewer.
• Reported confidence is calibrated, so the number means something.

What I would tell someone starting this

Most of the work is not the model. It is deciding what happens when the model is unsure, and being willing to make the system slower in exchange for being right.