lumnik. / scenarios All scenarios · Français

Three systems, one receipt date: the 00:30 batch and the plant's day

Goal Fuse into one entity a batch's arrival date as the LIMS, the ERP and the carrier give it, and keep in dispute only the real disagreements.

A plant receives its batches of raw material. Three systems say when: the LIMS scans the sample (an instant), the ERP books the receipt (a date), the carrier writes "Livré le" in a CSV (text).

plant.sql
CREATE SCHEMA lims;
CREATE SCHEMA erp;
CREATE TABLE lims.sample (sample_id text PRIMARY KEY, batch_no text NOT NULL, received_at timestamptz NOT NULL);
CREATE TABLE erp.goods_receipt (batch_no text PRIMARY KEY, supplier text NOT NULL, receipt_date date NOT NULL);
INSERT INTO lims.sample VALUES ('E-26-4410', 'L-0101', '2026-11-05T08:12:00+01:00');
INSERT INTO erp.goods_receipt VALUES ('L-0101', 'Chimie du Rhône', '2026-11-05');
INSERT INTO lims.sample VALUES ('E-26-4411', 'L-0102', '2026-11-05T10:47:00+01:00');
INSERT INTO erp.goods_receipt VALUES ('L-0102', 'Solvants de l''Est', '2026-11-05');
INSERT INTO lims.sample VALUES ('E-26-4412', 'L-0103', '2026-11-05T14:20:00+01:00');
INSERT INTO erp.goods_receipt VALUES ('L-0103', 'Arômes Provence', '2026-11-05');
INSERT INTO lims.sample VALUES ('E-26-4413', 'L-0104', '2026-11-06T00:30:00+01:00');
INSERT INTO erp.goods_receipt VALUES ('L-0104', 'Polymères Atlantique', '2026-11-06');
INSERT INTO lims.sample VALUES ('E-26-4414', 'L-0105', '2026-11-05T16:05:00+01:00');
INSERT INTO erp.goods_receipt VALUES ('L-0105', 'Chimie du Rhône', '2026-11-05');
INSERT INTO lims.sample VALUES ('E-26-4415', 'L-0106', '2026-11-05T23:40:00+01:00');
INSERT INTO erp.goods_receipt VALUES ('L-0106', 'Solvants de l''Est', '2026-11-05');
INSERT INTO lims.sample VALUES ('E-26-4416', 'L-0107', '2026-11-06T07:55:00+01:00');
INSERT INTO erp.goods_receipt VALUES ('L-0107', 'Arômes Provence', '2026-11-06');
INSERT INTO lims.sample VALUES ('E-26-4417', 'L-0108', '2026-11-06T09:31:00+01:00');
INSERT INTO erp.goods_receipt VALUES ('L-0108', 'Polymères Atlantique', '2026-11-06');

… 8 more lines

livraisons.csv
N° lot;Fournisseur;Livré le;Quai
L-0101;Chimie du Rhône;05/11/2026;Q1
L-0102;Solvants de l'Est;05/11/2026;Q2
L-0103;Arômes Provence;05/11/2026;Q3
L-0104;Polymères Atlantique;06/11/2026;Q1
L-0105;Chimie du Rhône;05/11/2026;Q2
L-0106;Solvants de l'Est;05/11/2026;Q3
L-0107;Arômes Provence;06/11/2026;Q1
L-0108;Polymères Atlantique;06/11/2026;Q2
L-0109;Chimie du Rhône;06/11/2026;Q3
L-0110;Solvants de l'Est;06/11/2026;Q1
L-0111;Arômes Provence;06/11/2026;Q2
L-0112;Polymères Atlantique;06/11/2026;Q3
manifests/10-plant-db.yaml
apiVersion: connectors.lumnik.io/v1kind: Connectormetadata:  name: scenario-dates-plant-a  connector_type: jdbc-generic  scopes: [receiving-a]spec:  connection:    url: jdbc:postgresql://dates-plant-db:5432/plant    user: hub_reader    password_env: SCENARIO_DATES_PASSWORD_A    dialect: postgres  source:    tables:      - name: sample                 # the LIMS: received_at timestamptz, the instant the lab scanned the sample        schema: lims        mode: full        pk_columns: [sample_id]        target: scenario_dates_lims_a      - name: goods_receipt          # the ERP: receipt_date date, typed by the warehouse clerk        schema: erp        mode: full        pk_columns: [batch_no]        target: scenario_dates_erp_a
manifests/20-carrier.yaml
apiVersion: connectors.lumnik.io/v1kind: Connectormetadata:  name: scenario-dates-carrier-a  connector_type: csv-file  scopes: [receiving-a]spec:  transport:    kind: local    path: /tmp/lumnik-scenario/dates-across-sources/a/livraisons.csv   # the carrier's delivery report  parser:    delimiter: ";"    encoding: ISO-8859-1    has_header: false    columns: [batch_no, supplier, delivered_on, dock]  after_process: none  mapping:    dataset:      - filter-where: { column: batch_no, regex: "L-[0-9]+" }    # the header line
manifests/30-batch.yaml
kind: Entityname: ScenarioReceivedBatchAdescription: "One batch of raw material as three systems saw it arrive: the lab's scan, the ERP's goods receipt, the carrier."scopes: [receiving-a]sources:  - table: t_scenario_dates_lims_a    map: { batch_no: batch_no, received_at: received }  - table: t_scenario_dates_erp_a    map: { batch_no: batch_no, receipt_date: received, supplier: supplier }  - table: t_scenario_dates_carrier_a    map: { batch_no: batch_no, delivered_on: received, dock: dock }match: [batch_no]
manifests/21-carrier-typed.yaml
apiVersion: connectors.lumnik.io/v1kind: Connectormetadata:  name: scenario-dates-carrier-a  connector_type: csv-file  scopes: [receiving-a]spec:  transport:    kind: local    path: /tmp/lumnik-scenario/dates-across-sources/a/livraisons.csv   # the carrier's delivery report  parser:    delimiter: ";"    encoding: ISO-8859-1    has_header: false    columns: [batch_no, supplier, delivered_on, dock]  after_process: none  mapping:    dataset:      - filter-where: { column: batch_no, regex: "L-[0-9]+" }    # the header line    cell:      - parse-date: { column: delivered_on, formats: ["dd/MM/yyyy"] }   # a day, no time: a date
manifests/31-batch-zone.yaml
kind: Entityname: ScenarioReceivedBatchAdescription: "One batch of raw material as three systems saw it arrive: the lab's scan, the ERP's goods receipt, the carrier."scopes: [receiving-a]zone: Europe/Paris                # the plant's day: a date meets a time on the day it has in Parissources:  - table: t_scenario_dates_lims_a    map: { batch_no: batch_no, received_at: received }  - table: t_scenario_dates_erp_a    map: { batch_no: batch_no, receipt_date: received, supplier: supplier }  - table: t_scenario_dates_carrier_a    map: { batch_no: batch_no, delivered_on: received, dock: dock }match: [batch_no]

Limits

  • The zone holds for the whole entity: a plant across two time zones needs two entities.
  • The carrier's table is not retyped in place: it is created again, and its lines read again.
  • The hub compares at the day; it does not say which of the three systems is right.
  • Batches, suppliers and times synthetic.

Receiving · three systems

A batch arrives. Three systems say so.

The LIMS: an instant. The ERP: a date. The carrier: text.

00:30 · 2026-11-06 · 06/11/2026

The problem

The 00:30 batch was in dispute.

  • In Paris it is the 6th. In UTC it is still the 5th.
  • The ERP says the 6th: the hub compared at the UTC day.

What is not changed

The three systems stay as they are.

  • The LIMS keeps its instant, the ERP its date.
  • The carrier's CSV is not touched.

What had to be written

zone: Europe/Paris

One line in the entity.

  • A date falls at midnight in Paris.
  • A time falls on its Paris day.

What was fixed

The entity declares the plant's day (#1881).

  • A parse-date added later is said at the apply (#1882).
  • One dispute left: L-0109, the 4th instead of the 6th.

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 →