case study · 2026

Line-of-business app / Traceability
Ortho Control

all projects
Ortho Control

01

the challenge

The orthodontic laboratory ran its production on Monday.com. As a board it worked, but it carried three problems no amount of configuration fixes: it is billed per user, per month, indefinitely; the data, tied to patients, lives on someone else's server; and nothing specific to the trade exists in a generic tool. Material batches, expiry dates, medical barcode scanning, material consumed per job: either you bolt them on with fragile automations, or they are not there. A legal obligation ran in parallel. A laboratory making custom-made medical devices must, under the European MDR, be able to link every device delivered to the exact batch of material it was made from: if a batch of resin turns out defective, you need to know which patients received it. That was kept in a spreadsheet and in the memory of whoever did the making. So the brief had two halves: replace the subscription with something of their own, and make that something solve what the subscription could not.

02

the solution

A desktop application designed, built and deployed from scratch, installed on the clinic's workstations. The product decision that shapes the whole interface: deliberately copy Monday's ergonomics (boards, status columns with coloured labels, inline editing) so that nobody had to relearn how to work. Changing tools was not supposed to feel like changing tools. Underneath, though, everything that was missing: batches and expiry dates, GS1 DataMatrix scanning that brings code, batch and expiry in a single trigger pull, automatic stock deduction, timestamped manufacturing stages, and the batch → consumption → case chain closed without extra manual work. The structural decision: the core of the application never talks to a database, it talks to a single interface with two interchangeable implementations: SQLite locally, PostgreSQL when shared. That let the app start on one workstation and move months later to a shared database without being rewritten. Scaling up is written down, not improvised: PostgreSQL in Docker on the practice's NAS and deliberately not on the domain server, workstations pointing at a DNS name and never at an IP address, per-user installation with no administrator rights, history migrated in a single verified transaction with rollback, and a commissioning manual in French covering hosting and fallbacks, network and firewall, backups and a restore test, and common symptoms.

03

the result

In production, six workstations, a shared database and live updates between them. The subscription is gone and the clinic owns the software it uses; patient data stayed in-house; the regulatory requirement is closed: from a defective batch you reach the patients, and from a patient their batches. To date: 293 lab cases traced, 1,738 timestamped production stages, 8 tables and 20,807 lines of TypeScript. What I take from it fits into three failures. Stock dropping to −6 because two workstations read before writing, fixed with row locking and verified with two instances against a real PostgreSQL. A network drop that closed the entire application instead of degrading it, turned into a backing-off reconnection that resynchronises on recovery. And absurd stock-out forecasts, such as nineteen years of runway on one sheet of plastic, born of the same naive formula copied three times and drifted apart. Since then, when the basis for a calculation is too thin, the panels refuse to answer and say why: an invented number someone is about to order material on is worse than a dash.

the brief

leaving monday without anyone noticing

before · Monday.com

after · the app

Column for column, it is the same board, and the likeness is deliberate: changing tools was not supposed to feel like changing tools. What changed is not visible here: under the table on the right sit batches, expiry dates, consumption per job and medical barcode scanning. Patient names pixelated; the Monday capture keeps its real structure.

what it changes

recurring cost

Gone. No per-user licences and no annual renewal: the practice owns the software it uses.

the data

At the client. The database lives on their own NAS, not on a third party’s server. With patient-linked information, that stops being a detail.

features

Made to measure. GS1 scanning, batch → case traceability, timestamped manufacturing stages, wear alerts. None of it fitted in the generic tool.

scale

the project in figures

20 807

lines of TypeScript / TSX in src/

2 334

lines of scripts: AutoHotkey, PowerShell, Node

98

commits in 3 months

8

tables, with 2 interchangeable backends

293

lab cases traced

1 738

timestamped production stage events

stack

what is underneath

Runtime / UI

Electron 39 · React 19 · TypeScript 5.9 in strict mode · electron-vite 5 with HMR · Vibe, Monday’s design system (@vibe/core 4)

Data

SQLite (better-sqlite3 12) · PostgreSQL (pg 8) · real time over LISTEN/NOTIFY · own idempotent migrations · hand-written SVG visualisations

Integrations

GS1 DataMatrix codes, own parser · 2D scanner in HID mode (Inateck BCST-52) · .xlsx reading with no dependency · Brother QL-810W label printing · desktop automation in AutoHotkey

Build / deployment

electron-builder (NSIS, Windows) · ESLint and Prettier in flat config · per-user installer, no administrator · icon and AppUserModelID via rcedit and PowerShell · SQLite → PostgreSQL migration by transactional script

architecture

a pluggable data layer

The structural decision of the project: IPC never talks to a database, it talks to a DataStore interface. Two implementations honour the same contract, and switching between them needs no recompile: it is chosen by environment variable or by a config file. That is what allowed the app to start locally, on a single PC, and move months later to a shared database without being rewritten.

Renderer

React 19 · one component per board

Inline-editable tables, statistics panels, own SVG primitives. No data access at all.

↕ window.api, via contextBridge, no nodeIntegration

Main

Node process · IPC, printing, folder watching

Thin mapping over the store; scope of the settings (shared or per workstation); broadcasting changes to the windows.

↕ DataStore: one interface, one contract

Persistence

SqliteStore

Local, the default. A file in the user profile, WAL, additive migrations.

PgStore

Central PostgreSQL on the laboratory NAS. Loaded by dynamic import, transactions with row locking and real-time notifications.

The logic both sides share, such as the automatic stock deduction rule, lives in src/shared/ with its own structural types, so it depends neither on the renderer’s types nor on the store’s.

deployment

from one workstation to the whole practice

The application started on a single computer with a local database. As soon as it became the laboratory’s working tool, the brief grew: put it on every computer in the practice, against a single shared database, with live updates between them.

This is the part that separates a personal project from a system other people use, and the one that forced the most decisions to be written down before touching anything, on infrastructure already in production for something else.

  1. 01

    PostgreSQL in Docker, on the practice NAS

    And deliberately not on the main server, which is both domain controller and file server for the whole practice. If the database fails, the app stops, but not the sessions, the shares or the patient software. The NAS is also already in RAID, powered 24/7 and inside the backup plan.

  2. 02

    Workstations point at a DNS name, never an IP

    So the database can move machine the day it has to, without going round the practice computer by computer.

  3. 03

    Installation with no administrator rights

    The installer is per user and each workstation’s configuration is a file in its own profile: setting up a new computer needs neither domain credentials nor the registry. Switching from local to shared database, or back, means editing that file, with no recompile. The domain-policy option exists in the code, but it was dropped: it added nothing here and required an administrator.

  4. 04

    History migrated by a transactional script

    It keeps the original identifiers, since batches, consumption and events reference each other, and resets the sequences at the end; it converts dates to an explicit time zone, without which the whole history shifted by an hour and daily production moved cell. All in a single transaction, with the counts verified inside: if anything is off, rollback. It has a dry-run mode.

  5. 05

    What belongs to everyone and what belongs to each workstation

    Moving to a shared database, interface settings stopped being "mine". Status labels and their colours are shared, because everyone must see the same thing; column widths are not, because they depend on the screen in front of you and sharing them would move the other five people’s table every time someone drags an edge. The split is resolved on the server and the interface knows nothing about it.

  6. 06

    Commissioning is written down, not improvised

    A manual in French, with its PDF: hosting decision and fallbacks if the NAS will not do, pre-flight checks, network and firewall, backups and a restore test, common symptoms and a final checklist. It also says what is not touched: add a rule to the firewall, never enable it from scratch with a single rule, which would cut the rest of the practice’s services. Custom software with no operations manual is not a saving, it is a dependency in disguise.

features

three working surfaces

inventory

"Inventaire"
  • Entry by GS1 DataMatrix scan: a single trigger pull brings GTIN, batch and expiry, and the parser separates them. If the GTIN is already in the catalogue, the app recognises it and avoids the duplicate.
  • Everything is typed in packages, never in units. "I received 12 bottles", and the app multiplies by the contents. It is the rule that has prevented the most stock errors.
  • A journal per item: every entry, exit and correction leaves a dated line, tied to its batch. Product traceability reads in one go.
  • A statistics panel with three views: the essentials (what to order), the overview and the per-item sheet, with stock value, turnover, expiry dates and runway.
Stock reads in both units at once: "123.3 sachets / 1225 u", "3.2 boîtes / 1520 u". It is the rule that stopped "12 bottles" from being entered as 144 ml again.

production tracking

"Suivi Labo"
  • A Monday-style table: inline editing, resizable and renamable columns, status columns with user-created labels and colours, automatic grouping by status.
  • Timestamped manufacturing stages: every stage change writes a dated event, which turns a coloured label into measurable history (durations, bottlenecks, weekly load).
  • A finished task locks. Batch traceability only holds if one task equals one delivered job; the locking warning, besides forbidding, opens the patient’s next pass.
  • An active wear alert for the cutting bur, derived from an event log rather than a stored counter, so that six workstations adding at once do not tread on each other.
The manufacturing sub-process of a finished job: six stages timestamped to the minute, 11.5 days in total, the slowest stage flagged in orange and the comparison against the other 124 tasks of the same treatment. This is where the production median comes from.

appointment intake bridge

"Ortholeader"
  • The laboratory’s patient software is closed and has no API. The route chosen is legitimate by design: it uses the software’s own export of the table to Excel. No reading its database, no intercepting network traffic, no screen scraping.
  • The only piece of data crossing the two systems is the order number, the shared key. Minimal, stable coupling.
  • Automatic capture with AutoHotkey: it exports every two hours during working hours, only if the workstation has been idle for 90 seconds, and puts the mouse and the active window back where they were.
  • Nothing reaches production without human approval, and the bridge clears itself of appointments the tracking board already covers; its job is to verify that none slips through.
Two text columns that never mix: what the practice writes when exporting the appointment, and what the laboratory notes. Only the second one moves into the task. Each row is sent or dismissed by hand.
The thesis of the project, in one table. The material that left stock for this specific job, batch by batch, with its real batch number and its date. This is the regulatory requirement solved: from a defective batch you reach the patients, and from a patient their batches.

engineering

five real problems and how they were closed

They were found against the laboratory’s real data and materials, not in a test environment: some in daily use, others reproduced with two workstations working at the same time on the shared database. It is the part of the project I learned the most from, so I document it with the measured symptom in front of me.

a batch with negative stock

concurrency
symptom
With two workstations working at once, two exits of 8 units on a batch of 10 left it at −6.
cause
Both workstations read 10, both concluded there was enough, and since the UPDATE was relative both subtractions applied. With one PC this does not exist; with six, every "read then write" is a race.
fix
Every read-then-write operation moved into a transaction with SELECT … FOR UPDATE. Verified with two simultaneous instances against a real PostgreSQL.

an idle connection that killed the whole application

resilience
symptom
When the connection to the server dropped (a nightly reboot, the network falling over, a firewall cutting on idleness), the application did not degrade: it closed entirely, with no warning, on every connected workstation.
cause
An error on an idle pool connection is emitted as an event; with no pool.on('error') listener, Node turns it into an uncaught exception.
fix
A handler on the pool, and the notification listener hooks its error before connecting. Reconnection always retries with a growing back-off (2 s → 30 s) and re-synchronises on recovery: whatever happened during the outage left no notification for that workstation.

"stock-out in 2 days" on a material nobody had touched

calculation model
symptom
The panel announced false stock-outs, and the same resin gave 26 days of runway under one filter and 316 under another. One sheet reached 7,171 days, or nineteen years.
cause
Three copies of the same naive formula (exits ÷ days), wrong for three different reasons: it counted inventory corrections as consumption, divided by the filter’s nominal duration instead of the real history’s, and treated a single movement as if it were a rhythm.
fix
One single definition, with a weighted average of 30-day half-life (days with no exit count in the divisor, which is what tells "12 exits in 12 days" from "12 in 12 months"). Below 7 observed days and 2 exits the panel refuses to answer and says why, rather than giving a number someone would end up buying on.

search stopped accepting keystrokes, with nothing to look at

diagnosis
symptom
Intermittent, every few days: the search field stopped writing and the application had to be closed. On screen everything looked normal.
cause
Impossible to reproduce on demand, so I built a probe inside the app (a shortcut that dumps the DOM state to the database, never recording what is typed, since patient names go through that field). The first dump ruled out the obvious hypotheses. The second was conclusive: the keystrokes were reaching the DOM, uncancelled, with the field empty: the content had lost Chromium’s "page focus", a state in which keys are delivered but text is not inserted.
fix
The only thing that repairs it is webContents.focus() from the main process; from the page there is no way. It now fires by itself as soon as a keystroke arrives without focus, so the user never notices.

12 bottles entered as 144 ml

interface design
symptom
Typing "12" into a resin entry gave 144 ml instead of 12,000. Stock barely moved and it looked as if the panel was not refreshing.
cause
The field asked for packages, which was right, but opened with a default content of 1 unit. Asking for the right unit is not enough if the default value is the wrong one. The "minimum stock" had the same underlying fault: it read in millilitres while the user thought in bottles, so the alert never fired.
fix
Every quantity field opens with the item’s reference package and shows the equivalent in both units at once ("12 bottles = 12,000 ml") so a wrong value is obvious before confirming. Package arithmetic was unified into a shared module.
The outcome of the third incident. The app does not just give a number of days: it says what it calculated it on: "136 j · rupture vers le 31/01/27 · estimation fiable sur 24 j (40 sorties)". When it has no sufficient basis, it writes a dash and the reason instead of a figure.

code

where the complexity lives

SuiviLaboBoard.tsx
5 295
InventaireBoard.tsx
3 020
InventaireStats.tsx
1 595
SuiviLaboStats.tsx
1 492
store/sqlite.ts
1 274
store/postgres.ts
1 247
lib/viz.tsx
781
store/types.ts
758
main/print.ts
600
lib/fraise.ts
463
27 remaining files
4 282

Lines per file in src/, out of 20,807 in total. The two boards hold 40 % of the code and are the declared target of the next refactor: they grew by accumulating columns and modals, and that is where it is hardest to get in. The two backends weigh almost the same (1,274 and 1,247), a sign that the DataStore contract has stayed symmetrical.

screens

the rest of the application

Production: five counters on a common axis, comparison with the reference period and the state of the board.
Inventory overview. Only euros and movements are summed: adding millilitres to sheets would mean nothing.
Per-item sheet: stock curve reconstructed backwards, state of each batch and weighted consumption rate.
The wear alert: counter since the last change, laboratory total and the lifespan of previous burs.
Finished tasks lock. One task equals one delivered job, and rewriting it would falsify its traceability.
Creating an item by scan: one trigger pull brings code, batch and expiry in a single two-dimensional code.
An item’s journal: batch entries, exits and corrections mixed by date, each tied to its batch.
Bulk actions. It is one of the board’s four write paths, which is why it also goes through the locking.
Shared settings: colours and labels look the same to everyone; column width belongs to each workstation.

All captures come from a copy of the real database with patient names replaced by fictional ones. The volumes, batches, dates and stages are the laboratory’s.

judgement

what I take from it

refusing to answer is a feature

An invented number someone is about to order material on is worse than a dash and a reason. Several parts of the app say "I have no sufficient basis" and explain why.

the legitimate route is also the robust one

Reading the closed software’s database was dropped in favour of its own export. Besides respecting the licence, it is the only thing that does not break when the vendor ships an update.

instrument rather than guess

The focus fault went weeks without an explanation. Two dumps from a probe written for the occasion closed it; no amount of reading the code would have.

one definition per concept

Almost every calculation error came from the same formula copied three times and drifted apart. Now each concept (runway, stock state, number of sheets) has one single place.

And one piece of honesty the project forces me to add: leaving a subscription is not free, it moves the bill elsewhere. The practice no longer pays a monthly fee, but it does depend on this staying maintained; hence the step-by-step commissioning manual, the installation that asks for no administrator and the two backends sharing one contract. Custom software only its author can operate is not a saving, it is another dependency. Proprietary software, private repository; the client’s infrastructure details are left out.

Batch traceability GS1 scanning Real-time shared database Custom software Subscription eliminated
stack

Electron·React·TypeScript·SQLite·PostgreSQL·Docker·electron-vite·AutoHotkey

a similar
project?

let's talk