A production-control system for a prefabricated HVAC duct factory, built alongside the factory’s ERP (SAP), its cutting software and the Smartsheet workspace its staff already used. It went from first commit to launch in 75 days, after the production manager, the production engineer and supervisors tested it on real cutting files.
The study started on the factory floor: how the production engineer and supervisors worked, how cutting plans were produced, how stores issued material, how dispatch happened and where billing could lose track.
Four risks came out of it. A delivery could leave the gate without a delivery order behind it. Delivered work could wait weeks for an invoice, because customers were billed fortnightly or monthly while material costs were booked as the material was used. Stock could read three ways, in the spreadsheets, in SAP and in the bin. And a job could be cut beyond what the customer had ordered.
The gaps sat in the hand-offs between order entry, engineering, stores and billing, so the fix had to follow one job through all four. Before this project, control depended on Excel, paper, email and local experience, and a problem could stay hidden until the next reconciliation.
Each leak got a check at the hand-off where it happened, and the calls that involved money stayed with people.
| Hand-off | Before | With the system |
|---|---|---|
| Order → cutting | Cutting could run past what the customer ordered | A job that would take the order past its remaining balance was refused, with the reason |
| Engineering → floor | The cutting plan would have been typed in again | The cutting software’s file was read directly, with nothing re-typed |
| Stores → floor | Material was pulled as needed and reconciled later | Material was reserved for each job and shift, with a stock check; actual use was recorded against the job and over-use was flagged |
| Floor → delivery | Margin was a planning figure, corrected after the fact | The production manager approved each job’s margin from actual use before a delivery order |
| Delivery → invoice | Unbilled work surfaced when the books were reconciled | Each delivery order was logged against its job with the SAP number and the signed delivery note, so billing could see what had left the gate |
These materials have more than one SAP code in the catalogue. Choose the code to use for order ORD-0412.
Margin approved. Create the delivery order in SAP with these details, then submit the SAP DO number through the form.
I’d start by tracing one order through engineering, stores, production, dispatch and billing.
That shows where information is typed twice, which decisions need a check, and what each person needs to see to act. The first version goes on the tools your team already uses; a proper database comes in when the workarounds start to cost more than they save. The scope follows your actual process and constraints.
Discuss a system like thisThe launched system kept Smartsheet as the screen factory staff already knew and moved every rule into 22 Python Azure Functions. Three of them form a webhook adapter that queues each change on Azure Service Bus, and a dispatcher routes it to the function that handles it.
A tag, the record for one production job, came in against its customer order and was refused if it would take the order past its remaining balance. The nesting file was uploaded against the tag, and a parser turned the cutting software’s spreadsheet export into material lines mapped to SAP codes, so nobody re-typed a bill of materials. Material was then reserved for a date and a shift, consumption was recorded against the reservation, and the production manager approved the margin before a delivery order was generated.
SAP stayed the accounting record throughout. The factory had legacy SAP project orders still open alongside production orders introduced around December 2025, so the design had to handle both.
Meeting staff in the tool they knew meant the code supplied what a spreadsheet lacks. Smartsheet has no transactions, no foreign keys and no row locks, so the code brought its own equivalents: a manifest that maps logical sheet names to fixed IDs, deterministic keys for safe retries, type coercion for numbers and a queue-based lock around consumption submissions and approvals.
The nesting software already knew what each tag needed. Its spreadsheet export has merged cells and floating headers, so the parser finds labelled anchors, reads each component and material line from fixed offsets, and maps each line to a canonical material and an SAP code, sending any it cannot match to review.
In May 2026 I rebuilt the same model on PostgreSQL as its next version, so its controls would rest on database guarantees once the spreadsheet workarounds began to cost more than they saved.
The rewrite carried the launched system’s rules across, each one kept, changed or dropped with a logged reason, and replaced the workarounds with database guarantees: transactions, foreign keys, row locks and constraints. Its core modules were built through invoicing, with 929 tests passing in the recorded run of 6 May 2026; the section below uses synthetic data.
No tag is accepted without cover. A tag that would take its order line past the remaining balance, beyond a small tolerance, is refused with the reason, so the fourth leak is controlled at intake.
The launched system’s parser, already used on real nesting exports, is wrapped in its own FastAPI service. One real nesting file parses to 44 piece lines, 25 of them non-straight, and tests pin both counts; the materials derived from those pieces are mapped to canonical materials and SAP codes.
Every resolved code writes an ALLOCATE row to the ledger: quantity signed negative, actor stamped, reserved for 24 hours. Running allocation twice changes nothing, because a UNIQUE index makes it idempotent.
The storekeeper’s pick writes a PICK row. The consumption form opens prefilled with what was allocated; the supervisor edits only the differences, variance bands flag what drifted, and the actual quantity lands as an ISSUE row. The manager then reviews the margin, with an adjustment spread over non-straight pieces only, and approves, rejects or holds. A delivery order can only be raised against an approved margin, and it then needs a gate pass, a dispatch scan and a proof-of-delivery photo.
Suppose a bin recount finds three square metres fewer than the ledger shows. In a spreadsheet someone would edit the cell. Here UPDATE and DELETE are revoked at the database role, so a correction can only arrive as a new signed ADJUST row, and the balance is derived from the rows again. The application cannot rewrite history.
Twelve modules, each owning its Postgres schema. A module’s public surface is its api.ts and its Zod contract, other modules may not import its internals, and durable messages go through an outbox.
The app connects as a role that can write business rows but can never UPDATE or DELETE the ledger, and cannot change the schema. Migrations, analytics and backups each have their own role and login.
Each row is a problem caught in testing or review, in one of the two builds, and settled at its cause.
In the launched system the fixes hardened the event path around the platform. In the rewrite, acceptance testing and audit sweeps found the rest, and most became standing checks: a parity audit, tests that pin band edges and concurrency tests. Each row traces to a commit or a logged decision.
Most of these became checks that no longer depend on anyone remembering to look. Sixty-three logged decisions and 46 documented gotchas keep each later change safe.
The launched system ran in the Smartsheet workspace the factory already used, with every rule in code, and the people who ran the floor tested it end to end on real cutting files.
Its PostgreSQL version was built through invoicing as the next step, carrying the same rules onto database guarantees. A version for your operation starts from the same method, a check with an owner wherever money can leak, and adds what your floor and your finance team need.
If your team finds the gaps at month end, in stock that counts three ways or in deliveries the paperwork never saw, the fix starts with an accurate drawing of the process you already run. Bring it.