A sanitized write-up of the warehouse and shop-floor half of a production internal operations platform I designed and built as the sole developer for a manufacturing and e-commerce company. It runs on the same database as the order-to-shipment half, which has its own write-up. Company names, product details, and proprietary code are omitted. It describes the architecture and engineering decisions only.
Manufacturing operations needed accurate, real-time inventory that the storefront and accounting tools could not provide. Stock counts drifted, and there was no reliable record of who pulled what against which work order. The materials themselves also varied widely, from items that record cleanly by discrete lot to harder-to-measure stock such as roll goods and liquids.
As the sole developer, I built the warehouse side of the platform in two parts. The first is a comprehensive tracking application covering everyday stock, receiving, builds, waste, and physical counts. The second is a dedicated subsystem for one category of materials that records cleanly by lot number and exact quantity; it is a ground-up rebuild of an older system that had become unreliable and hard to maintain. Both run on the same internal Ubuntu Linux server as the rest of the platform, and the main one reads and writes the platform's own schema directly rather than syncing across an API.
Two vanilla-PHP applications backed by Microsoft SQL Server, each a single-file-per-feature design where every page is either a server-rendered UI or a JSON endpoint. The main inventory system shares its database directly with the ERP, while the lot-tracked-materials subsystem has its own database on the same server.
Why two systems rather than one? The main system grew alongside the ERP and shares its tables, so reads and writes are immediate with no API layer. The lot-tracked-materials subsystem was rebuilt from the ground up as a clean, separate system because that data requires tighter access control, lot-level precision, and its own alerting; isolating it also allowed me to replace the legacy version without destabilizing the live inventory system.
Two operators pulling against the same work order simultaneously would corrupt stock counts, and the ERP could also check out the same order. Scanning a work order acquires a short-lived lock in a shared table. The save releases it, navigating away from the page releases it, and a scheduled job clears any lock older than five minutes. Because the lock table is shared, the ERP refuses to check out a work order that inventory has locked, which resolves the conflict across both systems.
Operators work by scanning rather than typing. A scanned badge identifies the operator, a scanned work order validates and locks it, and scanned codes identify SKUs and material lots. This supports both a hardware barcode scanner, which enters values directly into the focused field, and camera-based scanning in the browser. It keeps data entry fast and accurate in a warehouse environment where typing is error-prone.
Usage of these lot-tracked materials can be recorded from two places: the materials subsystem and the build-itemization flow in the main system. Rather than have each write to the other automatically, I kept a single owner for the actual quantity deduction and treated the other as read-only reference, matched on a normalized work-order-and-lot key. Reconciliation reports surface rows that exist in one source but not the other. Automatic dual writes were deliberately avoided: without a clear owner, they conceal quantity drift rather than correcting it, so any future synchronization requires an explicit reviewer decision.
When a physical count does not match the database, the SKU is flagged and pulling against it is blocked until the variance is resolved. This prevents a known-incorrect quantity from drifting further while it is investigated, and it notifies the admins automatically when the discrepancy is filed.
Every state-changing operation records who performed it, what changed, and the before and after values in one audit table. A scheduled job also snapshots all stock levels at a fixed interval into a separate table, which powers inventory trend charts without slowing down live operations.