Systems of record at operational scale with unit-level traceability

Over six years, I replaced a manufacturing operation built around spreadsheets with a production platform that became the system of record for inventory, manufacturing, shipping, and engineering data. As the sole developer and IT infrastructure owner, I evolved it in place through a live factory operation, using the system to enforce manufacturing procedures, establish unit-level traceability, and support the company’s ISO 9001 certification.

The starting point was an electronics manufacturer whose operational history was spread across spreadsheets. By the end, forty people were using the platform every day across thousands of items, with functionality covering item master data and inventory, the manufacturing floor and its procedures, outbound shipping, and version-controlled engineering packages. The company was not ISO 9001 certified when the project began; the system helped achieve certification by putting those procedures into the production process, preserving complete traceability for every unit, and providing the documentation needed to support them.

The six-year timescale was not simply a reflection of the amount of functionality involved. The factory had to keep operating throughout the build, so there was no clean migration window and no opportunity for a wholesale rewrite. Each capability had to be introduced into an existing production environment without disrupting the operation, which made the safety and maintainability of every change part of the engineering problem.

Unit-level traceability became the clearest example. A barcode scan needed to reconstruct an individual unit’s history, tracing its components back to their sources, identifying the build they entered, and following the stations, procedures, and production events they passed through. That relationship crossed around fifteen tables, and the initial query took close to two minutes.

Because rebuilding the system was not an option, I tackled the problem at the query layer. Common table expressions allowed the traceability chain to be constructed once, while indexes were designed around the path the lookup actually followed. Typical lookups fell to around half a second, while more complex views covering hundreds of items completed in roughly three seconds.

The same emphasis on control and history shaped the engineering package system. BoMs, schematics, and configuration files were versioned per module, access was controlled by department and category, changes notified dependent users, and production was protected from superseded revisions. Across the platform, the guiding principle was the same: in a live manufacturing environment, a solution was only successful if it could be introduced safely and then maintained without creating operational risk.

My manager provided the operational direction and feature requirements, while a CS designer helped shape the UI from the halfway point, but I owned the technical design, implementation, and day-to-day evolution of the system. After six years of incremental development, the platform remains in production to this day.