Expired calibrations: the results measured on an out-of-date instrument

Goal Flag every lab result measured outside its instrument's calibration validity (ISO 17025), from the register and the LIMS as they export.

A metrology register and a LIMS, two CSV exports: 6 instruments, 144 results.

an Entity joins the register onto every result;
a Process compares two columns of the joined row: measured_at after valid_until;
the register changes: the results already there are judged again.

lumnik Open
Laboratory
ISO 17025
Metrology
Connector
Entity
Process

instruments.csv

instrument_id,label,calibrated_on,valid_until,certificate
BAL-01,Analytical balance QC bench 1,2026-03-02,2027-03-01,CAL-2026-0112
BAL-03,Analytical balance QC bench 3,2025-11-03,2026-11-02,CAL-2025-0871
HPLC-01,HPLC assay line,2026-05-12,2027-05-11,CAL-2026-0348
HPLC-02,HPLC impurities line,2026-01-20,2027-01-19,CAL-2026-0041
PH-02,pH meter buffers,2026-09-01,2026-12-01,CAL-2026-0655
UV-01,UV-Vis spectrophotometer,2026-02-16,2027-02-15,CAL-2026-0090

results-2026-10.csv

result_id,sample,analyte,value,unit,instrument_id,measured_at
R-00001,S-2026-1000,mass,100.0,mg,BAL-03,2026-10-12T08:37:00Z
R-00002,S-2026-1000,assay,99.396,%,HPLC-01,2026-10-23T09:14:00Z
R-00003,S-2026-1001,impurity B,0.0799,%,HPLC-02,2026-10-03T09:51:00Z
R-00004,S-2026-1001,pH,6.8204,,PH-02,2026-10-14T10:28:00Z
R-00005,S-2026-1001,absorbance,0.511,AU,UV-01,2026-10-25T11:05:00Z
R-00006,S-2026-1002,mass,250.5,mg,BAL-01,2026-10-05T11:42:00Z
R-00007,S-2026-1002,mass,99.7,mg,BAL-03,2026-10-16T12:19:00Z
R-00008,S-2026-1002,assay,99.099,%,HPLC-01,2026-10-27T12:56:00Z
R-00009,S-2026-1003,impurity B,0.0797,%,HPLC-02,2026-10-07T13:33:00Z
R-00010,S-2026-1003,pH,6.8,,PH-02,2026-10-18T14:10:00Z
R-00011,S-2026-1003,absorbance,0.514,AU,UV-01,2026-10-29T14:47:00Z
R-00012,S-2026-1004,mass,249.75,mg,BAL-01,2026-10-09T15:24:00Z
R-00013,S-2026-1004,mass,100.3,mg,BAL-03,2026-10-20T08:01:00Z
R-00014,S-2026-1004,assay,98.802,%,HPLC-01,2026-10-31T08:38:00Z
R-00015,S-2026-1005,impurity B,0.0802,%,HPLC-02,2026-10-11T09:15:00Z
R-00016,S-2026-1005,pH,6.7796,,PH-02,2026-10-22T09:52:00Z
R-00017,S-2026-1005,absorbance,0.5125,AU,UV-01,2026-10-02T10:29:00Z
R-00018,S-2026-1006,mass,249.0,mg,BAL-01,2026-10-13T11:06:00Z
R-00019,S-2026-1006,mass,100.0,mg,BAL-03,2026-10-24T11:43:00Z

… 101 more lines

manifests/10-instrument-register.yaml

apiVersion: connectors.lumnik.io/v1kind: Connectormetadata:  name: scenario-calib-reg-a  connector_type: csv-file  scopes: [qc-lab-a]spec:  transport:    kind: local    path: /tmp/lumnik-scenario/lims-expired-calibrations/a/register/instruments.csv   # the metrology register's export  after_process: none

manifests/11-lims-results.yaml

apiVersion: connectors.lumnik.io/v1kind: Connectormetadata:  name: scenario-calib-res-a  connector_type: csv-file  scopes: [qc-lab-a]spec:  transport:    kind: local    path: /tmp/lumnik-scenario/lims-expired-calibrations/a/results/results-*.csv   # the LIMS drops one file a month  after_process: none

$ lm apply -f manifests/10-instrument-register.yaml
Applied connector "scenario-calib-reg-a" — 1 endpoint

$ lm apply -f manifests/11-lims-results.yaml
Applied connector "scenario-calib-res-a" — 1 endpoint

$ lm connector run scenario-calib-reg-a
run scheduled: abdc8200-229f-4060-bfe0-2b46951da224

$ lm run get abdc8200-229f-4060-bfe0-2b46951da224
ID        CONNECTOR             ENDPOINT  STATUS     IN  OUT  SKIPPED  DEAD-LETTERS  STARTED           ENDED             EVALUATED
abdc8200  scenario-calib-reg-a  default   Completed  6   6    0        0             2026-11-06 14:49  2026-11-06 14:49  yes

$ lm connector run scenario-calib-res-a
run scheduled: a8d9bf7e-6948-486a-b362-05942470315e

$ lm run get a8d9bf7e-6948-486a-b362-05942470315e
ID        CONNECTOR             ENDPOINT  STATUS     IN   OUT  SKIPPED  DEAD-LETTERS  STARTED           ENDED             EVALUATED
a8d9bf7e  scenario-calib-res-a  default   Completed  120  120  0        0             2026-11-06 14:49  2026-11-06 14:49  yes

manifests/20-calibrated-result.yaml

kind: Entityname: calib_result_amatch: [result_id]scopes: [qc-lab-a]sources:  - table: t_scenario_calib_res_a    map: { result_id: result_id, sample: sample, analyte: analyte, value: value, unit: unit,           instrument_id: instrument_id, measured_at: measured_at }joins:  - table: t_scenario_calib_reg_a      # per instrument, not per result    from: instrument_id    to: instrument_id    map: { calibrated_on: calibrated_on, valid_until: valid_until, certificate: certificate }

$ lm entity apply -f manifests/20-calibrated-result.yaml
Applied entity "calib_result_a" → connector."v_calib_result_a"

manifests/30-out-of-calibration.yaml

apiVersion: apps.lumnik.io/v1kind: Processmetadata:  name: scenario-calib-expired-aspec:  scope: qc-lab-a  on: { event: data.row.changed, table: connector.v_calib_result_a }  when:    - "measured_at>{valid_until}"      # measured after the instrument's certificate ran out  emit:    type: decision.out-of-calibration-a    message: "{result_id} ({sample}, {analyte}) measured on {instrument_id} at {measured_at}: certificate {certificate} valid until {valid_until}"    key: result_id

$ lm process apply -f manifests/30-out-of-calibration.yaml
Applied process "scenario-calib-expired-a" → table connector.v_calib_result_a, emits decision.out-of-calibration-a

results-2026-11.csv

result_id,sample,analyte,value,unit,instrument_id,measured_at
R-00121,S-2026-1040,mass,250.75,mg,BAL-01,2026-11-02T10:37:00Z
R-00122,S-2026-1040,mass,99.8,mg,BAL-03,2026-11-03T11:14:00Z
R-00123,S-2026-1041,assay,99.198,%,HPLC-01,2026-11-04T11:51:00Z
R-00124,S-2026-1041,impurity B,0.0798,%,HPLC-02,2026-11-05T12:28:00Z
R-00125,S-2026-1041,pH,6.8068,,PH-02,2026-11-02T13:05:00Z
R-00126,S-2026-1042,absorbance,0.51,AU,UV-01,2026-11-03T13:42:00Z
R-00127,S-2026-1042,mass,250.0,mg,BAL-01,2026-11-04T14:19:00Z
R-00128,S-2026-1042,mass,100.4,mg,BAL-03,2026-11-04T14:56:00Z
R-00129,S-2026-1043,assay,98.901,%,HPLC-01,2026-11-02T15:33:00Z
R-00130,S-2026-1043,impurity B,0.0802,%,HPLC-02,2026-11-03T08:10:00Z
R-00131,S-2026-1043,pH,6.7864,,PH-02,2026-11-04T08:47:00Z
R-00132,S-2026-1044,absorbance,0.513,AU,UV-01,2026-11-05T09:24:00Z
R-00133,S-2026-1044,mass,249.25,mg,BAL-01,2026-11-02T10:01:00Z
R-00134,S-2026-1044,mass,100.1,mg,BAL-03,2026-11-05T10:38:00Z
R-00135,S-2026-1045,assay,98.604,%,HPLC-01,2026-11-04T11:15:00Z
R-00136,S-2026-1045,impurity B,0.08,%,HPLC-02,2026-11-05T11:52:00Z
R-00137,S-2026-1045,pH,6.8272,,PH-02,2026-11-02T12:29:00Z
R-00138,S-2026-1046,absorbance,0.5115,AU,UV-01,2026-11-03T13:06:00Z
R-00139,S-2026-1046,mass,250.75,mg,BAL-01,2026-11-04T13:43:00Z

… 5 more lines

$ lm connector run scenario-calib-res-a
run scheduled: 423ca3c4-e5fb-4472-8ed2-1e5737a8a1d3

$ lm run get 423ca3c4-e5fb-4472-8ed2-1e5737a8a1d3
ID        CONNECTOR             ENDPOINT  STATUS     IN  OUT  SKIPPED  DEAD-LETTERS  STARTED           ENDED             EVALUATED
423ca3c4  scenario-calib-res-a  default   Completed  24  24   0        0             2026-11-06 14:49  2026-11-06 14:49  yes
• 1 file(s) unchanged since they were last read — not read again

$ lm inbox --process scenario-calib-expired-a
ID     WHEN      PROCESS                   MESSAGE                                                                                                                   ENTITY_KEY
24274  just now  scenario-calib-expired-a  R-00140 (S-2026-1046, mass) measured on BAL-03 at 2026-11-03T14:20:00Z: certificate CAL-2025-0871 valid until 2026-11-02  R-00140
24273  just now  scenario-calib-expired-a  R-00134 (S-2026-1044, mass) measured on BAL-03 at 2026-11-05T10:38:00Z: certificate CAL-2025-0871 valid until 2026-11-02  R-00134
24272  just now  scenario-calib-expired-a  R-00128 (S-2026-1042, mass) measured on BAL-03 at 2026-11-04T14:56:00Z: certificate CAL-2025-0871 valid until 2026-11-02  R-00128
24271  just now  scenario-calib-expired-a  R-00122 (S-2026-1040, mass) measured on BAL-03 at 2026-11-03T11:14:00Z: certificate CAL-2025-0871 valid until 2026-11-02  R-00122

$ lm connector run scenario-calib-reg-a
run scheduled: 5712b74e-c1df-4d5a-8ca9-142ab9683af7

$ lm run get 5712b74e-c1df-4d5a-8ca9-142ab9683af7
ID        CONNECTOR             ENDPOINT  STATUS     IN  OUT  SKIPPED  DEAD-LETTERS  STARTED           ENDED             EVALUATED
5712b74e  scenario-calib-reg-a  default   Completed  6   6    0        0             2026-11-06 14:49  2026-11-06 14:49  yes

$ lm inbox --process scenario-calib-expired-a
ID     WHEN      PROCESS                   MESSAGE                                                                                                                     ENTITY_KEY
24285  just now  scenario-calib-expired-a  R-00141 (S-2026-1047, assay) measured on HPLC-01 at 2026-11-02T14:57:00Z: certificate CAL-2026-0348 valid until 2026-10-27  R-00141
24284  just now  scenario-calib-expired-a  R-00135 (S-2026-1045, assay) measured on HPLC-01 at 2026-11-04T11:15:00Z: certificate CAL-2026-0348 valid until 2026-10-27  R-00135
24283  just now  scenario-calib-expired-a  R-00129 (S-2026-1043, assay) measured on HPLC-01 at 2026-11-02T15:33:00Z: certificate CAL-2026-0348 valid until 2026-10-27  R-00129
24282  just now  scenario-calib-expired-a  R-00123 (S-2026-1041, assay) measured on HPLC-01 at 2026-11-04T11:51:00Z: certificate CAL-2026-0348 valid until 2026-10-27  R-00123
24281  just now  scenario-calib-expired-a  R-00104 (S-2026-1034, assay) measured on HPLC-01 at 2026-10-29T08:08:00Z: certificate CAL-2026-0348 valid until 2026-10-27  R-00104
24280  just now  scenario-calib-expired-a  R-00056 (S-2026-1018, assay) measured on HPLC-01 at 2026-10-28T10:32:00Z: certificate CAL-2026-0348 valid until 2026-10-27  R-00056
24279  just now  scenario-calib-expired-a  R-00014 (S-2026-1004, assay) measured on HPLC-01 at 2026-10-31T08:38:00Z: certificate CAL-2026-0348 valid until 2026-10-27  R-00014
24278  just now  scenario-calib-expired-a  R-00008 (S-2026-1002, assay) measured on HPLC-01 at 2026-10-27T12:56:00Z: certificate CAL-2026-0348 valid until 2026-10-27  R-00008
24274  just now  scenario-calib-expired-a  R-00140 (S-2026-1046, mass) measured on BAL-03 at 2026-11-03T14:20:00Z: certificate CAL-2025-0871 valid until 2026-11-02    R-00140
24273  just now  scenario-calib-expired-a  R-00134 (S-2026-1044, mass) measured on BAL-03 at 2026-11-05T10:38:00Z: certificate CAL-2025-0871 valid until 2026-11-02    R-00134
24272  just now  scenario-calib-expired-a  R-00128 (S-2026-1042, mass) measured on BAL-03 at 2026-11-04T14:56:00Z: certificate CAL-2025-0871 valid until 2026-11-02    R-00128
24271  just now  scenario-calib-expired-a  R-00122 (S-2026-1040, mass) measured on BAL-03 at 2026-11-03T11:14:00Z: certificate CAL-2025-0871 valid until 2026-11-02    R-00122

Limits

The conditions are ANDed: 'measured before the calibration' needs a second Process.
A date alone is midnight UTC: a certificate valid until 2 November ends at the start of the 2nd.
A deleted result, or one whose number changes, is not judged.
The results already there when the Process is applied are judged only if they or their instrument change.
Synthetic instruments, certificates and results.

Laboratory · ISO 17025

A result counts only if its instrument was calibrated.

The register and the results live in two systems.

6 instruments, 144 results, two CSV exports

The problem

BAL-03's certificate ran out on 2 November.

It kept weighing on the 3rd, 4th and 5th.
The two exports are compared by hand.

What is not changed

The LIMS and the register stay as they are.

Their usual CSV exports.
No development in either system.

What had to be written

joins: [{ table: register, from: instrument_id }]
when: ["measured_at>{valid_until}"]

One join, one condition between two columns.

An Entity: the validity on every result.
A Process: one decision per result.

The register changes

HPLC-01 fails its check of 27 October.

Its validity is shortened in the register.
8 results already validated come back up.

Try it

The scenario replays locally.

Every step is published with its output.

This scenario runs on lumnik Open, free and open source.

Try lumnik Open →