Water analysis control system

About the project

For one of the largest drinking water companies in the Netherlands, I contributed to the development of a new internal system that safeguards the reliability and integrity of water quality measurements. The company provides clean drinking water to millions of residents every day and also performs independent analyses for a large number of external customers. The accuracy and reliability of this data are therefore crucial.

In the first phase, the project aimed to replace a large number of existing Excel processes with complex macros by a central, controllable and reproducible system. The result is an application that analyzes complete measurement runs, applies corrections based on internal standards and automatically checks whether first line control samples containing known substances are correctly detected. Deviations are explicitly flagged, after which analysts can review and release the results in a controlled way towards the LIMS system. This improved not only the efficiency of the process, but also its reliability and traceability.

After the successful completion and production release of the first phase, the system was extended with a second phase. This phase focused on Non Target Screening, where much larger and more complex datasets from analytical instruments are processed. While the first phase mainly focused on safeguarding and releasing known measurement results, the second phase focused on analyzing, correcting, recognizing and assessing unknown or suspect components in water samples.

The team, my role and the collaboration

The team consisted of a senior lead developer, a second developer, a tester and a product owner from the customer organization. I joined the project full time approximately three months after it started. The active development phase of the first phase then continued for about a year.

My role went beyond development alone. In close collaboration with analysts, we often used existing Excel screens and macros as a starting point. We did not simply convert them one to one, but critically analyzed and redesigned them with the goal of optimizing the underlying business processes. It was not just about digitization, but about structuring, simplifying and making a critical work process future proof. This meant we were closely involved in the functional design, workflow and user experience, with a lot of trust and freedom to contribute substantively.

After the senior lead developer left the project, I took over responsibility towards the end of the first phase, shortly before the production release. Shortly afterwards, the system was released to production in phases, which was completed in a controlled way and without significant disruptions.

For the second phase, I was given responsibility for the entire trajectory. This meant that I was not only involved in the technical implementation, but also in translating the analytical processes into a workable application structure. Together with the analysts, I investigated, designed and translated the existing Non Target Screening workflow into domain logic, screens, validations and processing steps. It was important to implement the scientific corrections and checks reliably, while also keeping them transparent and understandable for the users.

How the system works

The system supports the full analysis process of laboratory runs and safeguards the quality and integrity of measurement results from various instruments and methods. A run consists of a series of samples that are processed together on an analytical instrument. Within such a run, there are different types of samples, each with a specific role in quality assurance.

In addition to customer samples, a run can contain first line control samples, blank samples and samples with internal standards. These are not only assessed individually, but always in relation to the complete run. The quality of a single measurement does not stand on its own. Reliability is determined by the complete picture.

First line control samples contain known substances and are used to verify whether the instrument, method and processing steps correctly detect and quantify what is expected. The system automatically checks whether these known components are found within the required limits. Analysts can then review the outcome and decide whether the run is acceptable. This creates an important safeguard before customer results are released.

Based on internal standards, correction factors are calculated and applied to the measured values. Blank samples are used to detect possible contamination or carry over. The system then performs automated validations at both sample level and run level. This includes checks against acceptance limits, consistency checks and deviations from historical or statistical expectations.

When a run does not meet the required criteria, this is explicitly signaled. The analyst is given insight into the deviations and can make a substantiated decision to release, correct or reject the results. Only after this validation step are the first line control samples processed into the ELC charts and the final results written to the LIMS system.

The system also supports periodic reports and annual evaluations, making trends and structural deviations visible. As a result, it supports not only the daily operation, but also long term quality monitoring.

In the second phase, a Non Target Screening extension was added. This extension processes measurement data from specialized analysis software and supports the assessment of large numbers of features found in water samples. Among other things, internal standards are detected, retention times are corrected, mass deviations are calculated and corrections are applied based on regression lines. Deviations are also automatically flagged when corrections fall outside the defined limits.

This extension makes it possible to convert complex raw measurement data into usable information for analysts in a reproducible way. The system supports the recognition of known components, the assessment of new or suspect features and the registration of statistics per sample and run. This creates a more controllable process for a type of analysis that is inherently much more complex and less predictable than regular target analysis.

The strength of the system lies in the combination of automated validation, transparent decision making and human control. This ensures that measurement results are not only technically correct, but also explainable, reproducible and demonstrably reliable.

Architecture and technical choices

Because of the complexity of the domain logic, we deliberately chose an architecture that enforces separation of concerns and supports long term maintainability. The application was built according to Clean Architecture, where the core of the system, the validation and correction logic, is fully separated from infrastructure and presentation. This keeps the domain logic leading and testable, independent of technical implementation details.

In the application layer, we used a CQRS approach in combination with MediatR. Commands and queries are explicitly separated, keeping use cases clearly defined and making the processing steps of a run transparent. This fits well with a system where traceability and control are essential.

For the data layer, Entity Framework Core was used, with careful modeling of runs, samples, measurement results and features, where consistency and performance were important considerations. The front end was built with Blazor WebAssembly and the application is hosted in IIS, supported by a CI/CD pipeline for controlled and reproducible deployments, including database migrations.

For the second phase, the existing architecture was further used and extended to support the more complex Non Target Screening processes. It was important to keep import logic, correction calculations, validations, statistics and user interaction clearly separated. This allowed the complex calculations around internal standards, mass correction and retention time correction to be isolated, tested and maintained without making the rest of the system unnecessarily complex.

The chosen architecture and stack support what matters most for this system: reliability, testability and long term maintainability of complex domain logic.

Reflection

For me, this has been a very special project. The complex subject matter, strong collaboration and active involvement in the functional design made it both technically and substantively challenging. Throughout the project, we were given a lot of trust and freedom to carefully develop the system, which resulted in a successful phased production release and a stable end result.

The fact that I was given responsibility at the end of the first phase, shortly before the production release, and then full responsibility for the second phase, made the project especially valuable to me. It gave me the opportunity not only to contribute technically, but also to help shape the further development of a critical system within a complex domain.

The second phase made the project even more interesting from a technical and domain perspective. Non Target Screening requires a combination of software development, domain knowledge, statistics, correction logic and close collaboration with analysts. That combination of technical depth and direct impact on an important quality process makes this one of the most meaningful and valuable projects I have had the opportunity to work on.