ERP Connector for Vision Inspection

#At a glance

#Lines of code by language

Excluded: the vendored hls.js player (543 KB minified), Python bytecode caches, all runtime storage (dataset videos, model weights, heartbeat history, logs, state files), test model bundles, bitmap screenshots and the logo, and the .env file (not opened).

Not counted above: 1,628 lines of Markdown (7 files: a README and five contract documents for the Pi, server and training machines, plus a bug list) and 33 lines of JSON configuration (the SQL query file and the PWA manifest).

#Lines of code by module

The server is a single Python file. These modules are contiguous sections of it, plus the front-end files.

#The problem

Mursix put camera-based inspection on the shop floor using Raspberry Pi units with an AI accelerator hat. Each unit runs a small object detector that sorts parts into good and defect. That raised three problems the Pi could not solve by itself:

1. It needs to know what it is looking at. The right model depends on which part is running on the press or assembly cell right now, and that lives in the Plex ERP. The Plex ODBC driver is Windows-only and needs credentials, so it does not belong on every Pi.
2. Models need data, and the data needs order. Training clips have to land in one predictable place, tagged with workcenter, part and job, with held-out validation clips kept apart from training clips.
3. Models have to come back safely. A new model should only reach the floor after it has been checked, and a failed update must never leave a station without a model.

The ERP Connector is the one Windows PC that holds the ODBC driver and acts as the system of record. Pis, phones and the nightly training machine all talk to it over plain HTTP on the plant network.

                               ┌──────────────────────────────┐
  Raspberry Pi CV station      │        ERP Connector PC       │          Plex ERP
  (camera + Hailo NPU,         │                               │      (cloud, via ODBC)
   YOLOv8n good/defect)        │  ERP proxy ──── read-only SQL ├────────►  workcenters
        │                      │                               │           jobs, parts
        │ GET job/part context │  Upload intake ─► dataset/    │           part images
        ├─────────────────────►│     │   train | validation    │
        │ POST heartbeat (≈5 s)│     ▼                         │
        ├─────────────────────►│  Training queue (JSON)  ◄─────┼──── Nightly training PC
        │ POST video+metadata  │                               │     reads queue, trains,
        ├─────────────────────►│  Model registry               │     publishes candidate,
        │ GET latest model     │   candidates/ archive/ latest/│     promotes after eval
        │◄─────────────────────┤                               │
        │                      │  Fleet dashboard PWA  ◄───────┼──── phones / PCs
        │ HLS live preview ────┼──────────── (direct, not      │     (email sign-in)
        │                      │             proxied) ─────────┼───►
                               │  Watchdog (Task Scheduler)    │
                               └──────────────────────────────┘

#1. ERP proxy (Plex over ODBC)

The connector turns ERP lookups into simple JSON endpoints so the Pi never needs a database driver.

• Configured queries. Workcenters, workcenter groups and per-workcenter jobs come from SQL kept in a JSON config file, not in code, so they can be changed without a redeploy. The jobs query uses window functions (ROW_NUMBER() OVER (PARTITION BY …)) across job, operation and operation-status views to pick the previous, current and next job for a workcenter and the part key of the current one.
• Part enrichment. Each job row is enriched with part number, name, revision and the part's photos. The photo list is ordered default image first, then newest; the public image URL is built from the stored filename. A per-request cache stops the same part being looked up twice.
• Part search by number or name, with an optional revision filter, for when an operator needs to override the part the ERP reports.
• Schema discovery. Table and column search over the ODBC catalog, plus an ad-hoc query endpoint, were used to find the right Plex views while the queries were being written.

SQL safety. Every statement passes a read-only guard: it must start with SELECT or WITH, may not contain a semicolon, and may not contain DML/DDL keywords (INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, EXEC, MERGE, CREATE). Configured templates are checked before values are substituted and again after. Substituted values are SQL-quoted with doubled single quotes; part search uses true ? parameters. ODBC failures are returned as HTTP 502 so a client can tell "ERP is down" from "bad request".

#2. Upload intake and dataset builder

Captures arrive through a four-step upload API, so a half-sent video never reaches the dataset:

1. init creates an upload record and an intake folder per device.
2. video streams the raw bytes (capped at a configurable maximum, 2 GB by default) and writes them atomically (temp file, then rename).
3. metadata stores the sidecar JSON.
4. complete validates and ingests.

Validation on complete. The video exists and is non-empty, the metadata exists and has every required field (schema version, capture ID, device ID, recorded time, building, workcenter, part key, video filename, ERP snapshot), the building is the one supported site, the workcenter exists in the live ERP list, the video filename equals <capture_id>.mp4, the dataset role is train or validation, and the capture ID is not already in the dataset. Every path component is checked for /, \ and ...

On failure the whole upload folder moves to quarantine/, the upload record is marked quarantined, the error is logged, and the client gets a clear error message.

On success the files move into a canonical layout:

dataset/<building>/<workcenter_slug>/<part_key>/<train|validation>/raw/<YYYY>/<YYYY-MM-DD>/<capture_id>.mp4|.json

A per-role manifest records capture count and last capture time, and the training queue is updated. The workcenter slug is lowercase, with every run of non-alphanumeric characters turned into one underscore. All timestamps are normalized to UTC; a timestamp without a time zone is rejected.

The training identity is building + workcenter + part, not the job. The job number is kept for traceability only, because one workcenter runs many parts over time and one part runs on many jobs.

#3. Training queue

The server keeps an explicit queue in a JSON file instead of making the trainer scan folders.

• Upsert rule. A new capture joins the open queue item (ready, picked_up, training or trained) for the same building, workcenter and part. If there is none, a new item is created. Each item tracks all new capture IDs and also separate train and validation ID lists.
• State machine. The nightly trainer moves items through ready → picked_up → training → trained → candidate_ready → evaluating → promoted, or to evaluation_failed / failed, with an optional message. Unknown states are rejected.
• Validation clips are a first-class upload type. They are stored apart from training clips so the trainer can score the current model and the candidate on the same held-out clips, and promote only a candidate that beats the current model without losing defect recall. The contract documents spell out this acceptance rule. The evaluation itself runs on the training machine, which is not part of this project.

#4. Model registry and delivery

Models are stored per building / workcenter / part:

models/<building>/<workcenter_slug>/<part_key>/
    candidates/<version>/   staged by publish
    archive/<version>/      every version ever published
    latest/                 what the Pi downloads

• Publish copies a bundle from the server's staging area into both archive/ and candidates/ and marks the queue item candidate_ready. It does not replace latest unless the caller asks for auto-promotion. The source folder must resolve inside the server's storage root, so a caller cannot publish from an arbitrary path. A bundle must contain model.onnx, labels.json and model_metadata.json.
• Promote moves a candidate, or any archived version (which makes rollback one call), into latest. Promotion requires a compiled .hef file for the Pi's Hailo accelerator, so an ONNX-only model can never reach a station that cannot run it.
• Crash-safe swap. The new bundle is fully built in latest_new, the current latest is renamed to latest_old, latest_new is renamed to latest, and then latest_old is deleted. If the final rename fails, the old model is put back. At startup the server scans for leftover latest_old / latest_new folders and finishes or rolls back any promotion a crash interrupted. At no point is there a window with no latest model.
• Lookup by workcenter and part returns the metadata and a download URL for every file in latest. When a .hef is present it is marked as the preferred runtime artifact, and the reported format is changed to match. If no model exists for that exact part, the server returns 404. It never falls back to another part's model.

The model itself. The deployed bundle is a YOLOv8n detector at 320 × 320 input with two classes, good and defect. It was exported to ONNX (12.1 MB) and compiled with the Hailo Model Zoo toolchain into a 4.8 MB HEF with on-chip NMS. Quantization was calibrated on up to 1,024 sample images from both classes, and the compile took 2 min 34 s across three contexts. Training and compilation happen off this server; the connector stores, versions and serves the result.

#5. Station telemetry (heartbeats and per-part counts)

Each Pi posts a heartbeat every few seconds with its ERP context, UI state, camera list, inference status (FPS, target FPS, confidence threshold, top label and score, model status), recording state, upload queue depth, disk usage and session counters.

• Validation. Required sections must be present and must be objects. The workcenter must exist in the ERP.
• Online status. A station is online when

$$t_\text{now} - t_\text{received} \le 30\ \text{s}$$

(configurable). The dashboard sorts online stations first. A heartbeat file that fails to parse is logged as an error instead of silently disappearing from the dashboard.

• Per-part inspection report. Stations send running session counters (inspected, good, defect). The server turns them into durable totals per part and per station using deltas against the previous heartbeat:

$$\Delta_k = \begin{cases} c_k & \text{if the part changed, no report exists, or any } c_j < c_j^{\text{prev}} \ (\text{station restarted}) \\ c_k - c_k^{\text{prev}} & \text{otherwise} \end{cases}$$

$$\text{pass rate} = 100 \cdot \frac{\sum \text{good}}{\sum \text{inspected}}$$

Running totals survive Pi restarts and part changeovers without double counting.

• Concurrency. Read-previous / update-report / write-latest runs under an exclusive OS file lock (msvcrt on Windows, fcntl elsewhere), so overlapping heartbeats from threads or processes cannot corrupt the totals.

#6. Fleet dashboard (installable PWA)

A dark, responsive web app served by the connector and installable on phones, with a service worker that caches the app shell and always goes to the network for API calls.

• Sign-in. Company-domain email plus a 6-digit one-time code sent over SMTP (STARTTLS or SSL). Codes come from secrets.randbelow and expire after 10 minutes. Sessions use a 32-byte URL-safe token in an HttpOnly, SameSite=Lax cookie and last 12 hours. Expired codes and sessions are purged on every auth call. Email sign-in can be switched off for local use.
• Plant level. Workcenter count, online and offline stations, groups with at least one station online, a bar per workcenter group, and a "needs attention" list.
• Group level. A station table for every workcenter in a Plex workcenter group.
• Workcenter level.
  • The current ERP part, with its photo, previous/current/next job and revision.
  • A live view when a CV unit is assigned: an HLS stream played natively on iOS or through hls.js elsewhere, with automatic recovery from network and media errors. When there is no stream, the Plex part photo is shown instead. If several units share a workcenter, the one with a live heartbeat wins.
  • Phone capture upload. Record or pick a video, choose training or validation, add notes, and send it through the same four-step upload API as the Pi. The metadata carries an ERP snapshot and the uploader's identity.
  • Manual part override by Plex part search, for when the ERP's current job is not what is on the line.
• Live refresh. Heartbeats are polled every 5 s. When the station reports a different part than the one on screen, the ERP context reloads by itself.

#Setup console (admin only)

• CV unit registry. Display name, device ID (matched against heartbeats), assigned workcenter (or none), camera count, active flag, and stream URL / format / size. A workcenter can have zero, one or several units.
• Workcenter coverage across the plant.
• Training dashboard. Queue totals by status and captures by role.
• Training-set browser. Search and filter every capture by role, workcenter, part, device or job, and play it in the browser. Videos not already in H.264 are transcoded once with FFmpeg (libx264, CRF 23, +faststart) into a cached preview. A lock prevents duplicate transcodes, and the cache is rebuilt when the source is newer. Playback supports RFC 7233 byte ranges, so scrubbing works on phones.

#7. API

All routes return JSON with an ok flag. Errors map to status codes: 400 for validation errors, 401 for auth, 404 for missing items, 501 for unconfigured queries, 502 for ERP/ODBC failures. Every /api/... route is also reachable as /app/api/... behind a session, and setup / admin routes additionally require the admin role.

Request bodies are bounded: JSON at 1 MB, video at the upload cap. A missing or non-numeric Content-Length, malformed JSON, or a JSON value that is not an object all return 400. Static files are served only from inside the static folder.

#8. Reliability engineering

• Atomic writes everywhere. All JSON state (queue, uploads, CV units, sessions, manifests, reports) and uploaded video bytes are written to a temp file and renamed into place.
• Crash-safe model promotion with startup recovery (section 4).
• Quarantine for bad uploads instead of deleting them.
• Structured logs. Every request goes to a JSON-lines access log. Every error goes to an error log with status, type, message and full traceback.
• Watchdog (PowerShell, installed as a Windows scheduled task):
  • A named mutex ensures only one watchdog runs.
  • It adopts an already-healthy server instead of starting a duplicate.
  • It starts the server hidden and waits up to 30 s for /health.
  • It probes /health every 15 s and kills the process tree after 4 straight failures.
  • It restarts the server 5 s after a crash, or 10 s after a failed start.
  • It archives stdout and stderr logs with a timestamp on every restart.
• Task Scheduler settings:
  • Starts at sign-in, and optionally at machine boot before anyone signs in (S4U logon, so no stored password).
  • A repair trigger fires every 5 minutes for ten years.
  • Restarts up to 999 times at 1-minute intervals, with no execution time limit, and ignores duplicate instances.
• Restart script. Frees the port by killing whatever listens on it, byte-compiles the server as a syntax check (a broken build leaves the server stopped instead of half-running), starts one instance, and waits for a healthy /health.
• Fail-fast startup. A missing or invalid query config gives a clear message and exit code 2 instead of a stack trace.

#9. The multi-machine contract

Half of the project is the written contract that lets three machines be built separately:

• Pi: deterministic capture IDs (cap_<UTC date>_<UTC time>_<workcenter_slug>_<device>_<seq>), a metadata sidecar schema, upload with retry and a persistent pending queue (never silently drop a capture), and a local model cache keyed by workcenter and part.
• Live stream: H.264 at 640 × 480, 5–15 FPS, about 0.5–2 Mbit/s, over HLS for iOS compatibility. The stream goes straight from the Pi to the viewer and is never proxied, so total bandwidth ≈ bitrate × concurrent viewers lands on the LAN rather than on the connector.
• Server: naming rules, folder layout, validation and quarantine rules, the manifest and queue formats, the model-metadata schema, and "never return another part's model".
• Nightly trainer: read the queue, train per building + workcenter + part, version models as <YYYY-MM-DD>_<HHMMSS>_utc, copy the bundle into the server's staging area, publish, evaluate against validation clips, and promote. If training fails, latest is left alone.

#Testing and measured results

There is no automated test suite. Verification was done by hand against the running server, guided by a written bug list with explicit checks (missing config, Content-Length: abc, an oversized JSON body, malformed JSON, a corrupt heartbeat file, publishing from outside the storage root, and killing the server mid-promotion). The server's own error log shows those checks being run and handled: 400 for the bad Content-Length, the oversized body and the malformed JSON, and a rejection for the outside-root publish. Every item on that list is fixed in the current code.

Figures below come from the server's logs and state files:

The pass and defect counts are the detector's own calls. No ground-truth labelling of those parts exists here, so they show the pipeline working, not classifier accuracy. The model metadata has no validation-accuracy figure recorded.

#Honest status

• Working in production: the ERP proxy, heartbeats and per-part counters, model lookup and download to a live Pi station, the fleet dashboard with live HLS view, the watchdog, and crash-safe promotion. A real station downloaded and ran a Hailo-compiled model served by this connector.
• Built but lightly used: the upload → dataset → queue → publish → promote loop works end to end, but it has been exercised with only a handful of test captures (3 videos in the dataset, 3 queue items). The automated nightly trainer and the candidate-vs-current evaluation step described in the contracts are not part of this project and are not yet running; the deployed model was imported by hand.
• Security hardening is on the roadmap. The connector is built for a closed plant network today; device authentication and transport encryption are planned before any wider exposure.
  • The admin role is a single configured account.
  • It is fine behind the plant firewall. It needs per-device keys, TLS and real role checks before wider exposure.
• Simple storage. State lives in JSON files that are rewritten whole. That is fine at fleet scale (tens of stations), but only heartbeat aggregation is locked across processes.
• One file. The server is a single 2,700-line Python file on the standard-library HTTP server. That keeps deployment trivial (two pip packages), but it is overdue for splitting into modules.
• Drift. An earlier version wrote a per-heartbeat history archive; the current code keeps only the latest heartbeat per station plus the per-part reports. The telemetry figures above come from that older archive.
• No tests, no git history. Changes were made and verified by hand.