Calibrations expirées: les résultats mesurés sur un instrument hors validité

Objectif Signaler chaque résultat de labo mesuré hors de la validité de calibration de son instrument (ISO 17025), depuis le registre et le LIMS tels qu'ils exportent.

Un registre de métrologie et un LIMS, deux exports CSV: 6 instruments, 144 résultats.

une Entity joint le registre à chaque résultat;
un Process compare deux colonnes de la ligne jointe: measured_at après valid_until;
le registre change: les résultats déjà là sont jugés à nouveau.

lumnik Open
Laboratoire
ISO 17025
Métrologie
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 lignes de plus

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 lignes de plus

$ 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

Limites

Les conditions se combinent en ET: « mesuré avant la calibration » demande un second Process.
Une date seule vaut minuit UTC: un certificat valable jusqu'au 2 novembre se termine au début du 2.
Un résultat supprimé, ou dont le numéro change, n'est pas jugé.
Les résultats déjà là quand le Process est appliqué ne sont jugés que s'ils changent, ou si leur instrument change.
Instruments, certificats et résultats synthétiques.

Laboratoire · ISO 17025

Un résultat ne vaut que si son instrument était calibré.

Le registre et les résultats vivent dans deux systèmes.

6 instruments, 144 résultats, deux exports CSV

Le problème

Le certificat de BAL-03 a expiré le 2 novembre.

Elle a pesé les 3, 4 et 5.
Les deux exports se croisent à la main.

Ce qui n'est pas modifié

Le LIMS et le registre restent tels quels.

Leurs exports CSV habituels.
Aucun développement dans l'un ou l'autre.

Ce qu'il a fallu écrire

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

Une jointure, une condition entre deux colonnes.

Une Entity: la validité sur chaque résultat.
Un Process: une décision par résultat.

Le registre change

HPLC-01 échoue sa vérification du 27 octobre.

Sa validité est raccourcie dans le registre.
8 résultats déjà validés remontent.

Essayer

Le scénario se rejoue en local.

Chaque étape est publiée avec sa sortie.

Ce scénario tourne sur lumnik Open, gratuit et open source.

Essayer lumnik Open →