Mapping MIS Data onto the CoCoCo Data Model

Applies to CoCoCo platform v1.0.0-rc.31. Every statement below was checked on that version; the SQL Server snippet could not be run there (no SQL Server on the test tenant), and the note on fixed-width CHAR columns was not reproduced.

Reading rows out of a MIS database is the easy half. The real work is deciding which record in CoCoCo each row belongs to — every time the import runs, without creating duplicates and without a mapping table of your own.

CoCoCo has three mechanisms for exactly this. Learn them once and every integration afterwards looks the same.

1. externalId — the anchor

Customers, orders, jobs and operations each carry an externalId: the identifier the record has in your source system. Write it on creation and the source system’s numbering becomes the key you can look records up by.

Use a prefix so the origin stays visible, and keep the source number readable:

Source record	externalId
Customer 1042	MIS:K-001042
Order 26-04711	MIS:26-04711
Work step 20 of that order	MIS:26-04711:20

The import pattern is then always the same — look up first, create only when absent:

local ok, res = ctx.graphql.query([[
  query($e: String!) {
    listJobs(first: 1, filter: { externalId: { eq: $e } }) { edges { node { id } } }
  }]], { e = "MIS:26-04711" })
if not ok then error(res) end
local edge = res.data.listJobs.edges[1]   -- nil if the job does not exist yet

ctx.graphql.query returns two values: whether the call went through, and the result. Assigning it to a single variable keeps only the first — true — and the lookup never sees a job.

If the job comes back, use its id. If not, create it with that externalId. A second run of the same import then creates nothing at all, which is what you want when a timer fires every five minutes.

Trim your source values before using them as an id. Fixed-width CHAR columns arrive padded with spaces, and "26-04711 " is a different id from "26-04711".

2. externalRef — booking progress without knowing CoCoCo ids

Shop-floor feedback is the highest-volume data in any MIS integration, and it is the place where a mapping table hurts most. recordProgressPoints therefore accepts an externalRef instead of an operation id, and resolves the target through your own coordinates:

local ok, res = ctx.graphql.query([[
  mutation($i: RecordProgressPointsInput!) {
    recordProgressPoints(input: $i) { recorded errors { message } }
  }]], { i = { points = { {
    externalRef = {
      jobExternalId       = "MIS:26-04711",
      operationExternalId = "MIS:26-04711:20",
    },
    timestamp  = "2026-09-04T06:41:00Z",
    phase      = "RUNNING",
    goodCount  = 1420,
    wasteCount = 95,
    source     = "MANUAL",
  } } } })
if not ok then error(res) end
-- res.data.recordProgressPoints.errors lists the points that could not be resolved

No lookup, no id translation, and the reference resolves top-down (job → component → operation → item), so partial coordinates work too.

Set source honestly: MANUAL for a terminal entry by a person, JMF for a machine message. Anyone reading the data can then tell whether a number was measured or typed.

3. A watermark — knowing what is new

On every run, ask the database only for what changed. Which column to use depends on what the table offers:

Table has	Use as watermark
A change timestamp	The highest timestamp already imported
A sequential id, no timestamp	The highest id already imported
Neither	Re-read a fixed window (e.g. 48 hours). The externalId lookups absorb repeated customers, orders, jobs and operations — not progress points (see below)

Keep the watermark in ctx.cache — a pointer, never the data:

ctx.cache.set({ key = "mis:feedback:last", value = tostring(highest), ttl = 604800 })

And two rules that save real trouble:

• Advance the watermark only after an error-free run. For customers, orders, jobs and operations a repeated run is harmless: the externalId lookup finds what the last run created. Progress points have no such key — a point sent twice is stored twice and cannot be deleted. On a partial failure, send again only the feedback rows that were not recorded (recordProgressPoints answers with the number recorded and the errors).
• Survive a cold start. The cache can expire. If the pointer is missing, do not import everything from the beginning: re-read a fixed window for master data, and start feedback after the newest point already recorded for that operation.

Timestamps: assemble them in SQL

Many MIS schemas keep date and time in separate columns. Combine them in the query, not in your script — and convert to UTC before you append the Z. A shop-floor terminal writes local wall-clock time; labelled as UTC as it is, every point in Central Europe lands one or two hours in the future:

-- PostgreSQL: through the database's own time zone
to_char(((start_date + start_time::time) AT TIME ZONE current_setting('TimeZone')) AT TIME ZONE 'UTC',
        'YYYY-MM-DD"T"HH24:MI:SS') || 'Z'

-- SQL Server: name the time zone the MIS writes in
FORMAT((DATEADD(second, DATEDIFF(second, 0, start_time), CAST(start_date AS datetime2))
        AT TIME ZONE 'W. Europe Standard Time') AT TIME ZONE 'UTC', 'yyyy-MM-ddTHH:mm:ss') + 'Z'

If your MIS already stores UTC, leave the conversion out. Ask the vendor; the column names rarely say.

Two reasons for doing it in SQL. A DATE column arrives as a full timestamp (2026-09-02T00:00:00Z), not as 2026-09-02 — so appending a time produces a malformed string. And a wrong timestamp is expensive: progress points are append-only. A mis-dated production history cannot be corrected, only added to, and latestProgress reports the most recent point.

Codes and units

Status codes and cost centres need a translation, and it belongs in configuration. STATUS = 2 or KST-OFF means nothing to CoCoCo; you map it to CONFIRMED or OFFSET_PRINTING. Every customer’s codes differ, so keep the table in one place in your integration config rather than scattered through the code.

Units are typed. For sheets and impressions use countUnit: CUSTOM with customCountUnit: "SHEETS", spelled exactly as verticalProductionUnits lists it — the string is free text and a typo silently creates a second series.

A worked shape

For a print MIS the mapping usually comes out like this:

MIS	CoCoCo
Customer master	Customer (externalId, name, payment terms)
Order header	Order (orderNumber, externalId, dates, totals)
Order lines	Order lines; the production line carries jobId
Order + quantity + due date	Job (quantity, dueAt, jobNumber)
Work steps	Operations (kind from the cost centre, requestedWorkCenterId from the machine)
Shop-floor feedback	Progress points via externalRef

With the order line pointing at the job, orderLifecycle returns the order, its lines and the production job in a single call.

Where to go next

• Connecting an External Database (MIS/ERP) over SQL — getting the connection up in the first place
• What are Integrations? and How to Build an Integration — where this code belongs once it should run on a timer instead of by hand
• How to Use Scripts and Lua Playground: Getting Started — for trying the mapping out first
• How to Create and Manage API Tokens — if you drive the import from outside instead of from a script