Warehouse & Inventory Subsystem (WMS)

Context

Manufacturing needed real-time inventory that the storefront and accounting tools could not provide. Counts drifted, and nothing recorded who pulled what against which work order. The materials themselves vary widely: some track cleanly by discrete lot, others are roll goods and liquids that resist exact measurement.

As the sole developer, I built this half in two parts. The first tracks everyday stock, receiving, builds, waste, and physical counts. The second handles the one category of material that does track by lot number and exact quantity, 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.

Scope

• Stock and SKUs: real-time quantities, pulling against work orders, and manual adjustments
• Receiving: purchase orders moving through pending, partially arrived, and arrived, feeding stock on arrival
• Builds and bill-of-materials: assembly tracking and per-build material consumption
• Physical counts: QR-scanned counts compared against the database, with discrepancy reporting
• Waste: scrap recorded against stock, with its own audit trail
• Lot-tracked materials subsystem: lot-level inventory, weekly snapshots, and threshold alerts
• Reporting and analytics: stock trends, purchase-versus-usage, low-stock and reconciliation reports
• QR codes: bulk generation, plus shop-floor scanning for stock, lots, and operator badges

Architecture

Two server-rendered PHP applications on Microsoft SQL Server, where every page is either a screen or a JSON endpoint. The main system shares the ERP's database; the lot-tracked-materials subsystem has its own on the same server.

Why two systems rather than one? The main one grew alongside the ERP and shares its tables, so reads and writes are immediate and there is no API layer to keep in sync. The materials subsystem got its own database because that data needs tighter access control, lot-level precision, and separate alerting. Isolating it also let me retire the legacy version without putting live inventory at risk.

Neither application uses a framework. With one person maintaining both, a page that is a single file is the fastest thing to open, understand, and change months later, and deploying stays a file copy onto the server already running the ERP. The cost is that auth and input validation live in shared includes rather than middleware, which is the first thing I would change if a second maintainer joined.

Scale

• 2,000+ SKUs tracked across stock, receiving, builds, and waste
• 20 to 30 internal users across the office and the shop floor at the same time
• Stock lookups and inventory reports return in under a second

Engineering decisions

1. Cross-system work order locking

Two operators pulling against the same work order would corrupt stock counts, and the ERP could check that order out at the same time. Scanning a work order takes a short-lived lock in a shared table. Saving releases it, leaving the page releases it, and a scheduled job clears anything older than five minutes. Because the table is shared, the ERP refuses to check out a work order the shop floor has locked, so the conflict is settled between the two systems rather than inside either one.

2. Shop-floor workflows driven by scanning

Operators scan instead of typing. A badge identifies the operator, a work order is validated and locked on scan, and SKUs and material lots are identified the same way. Both input paths work: a hardware barcode gun types into the focused field, and the browser scans through the camera when no gun is at hand. On a shop floor, typing is where the errors come from.

3. Two material sources, reconciled rather than auto-synced

Usage of these lot-tracked materials gets recorded in two places: the materials subsystem and the build-itemization flow in the main system. Only one of them owns the actual quantity deduction; the other is read-only reference, matched on a normalized work-order-and-lot key. Reconciliation reports list the rows that exist in one source but not the other. I left automatic dual writes out on purpose, because without a clear owner they hide quantity drift instead of correcting it. Closing a gap stays a decision someone makes, not something the system does quietly.

4. Discrepancy lock to stop drift

When a physical count does not match the database, the SKU is flagged and pulling against it is blocked until the variance is resolved, and filing the discrepancy notifies the admins. A quantity already known to be wrong stops moving while someone looks into it.

5. Audit trail and historical snapshots

Every state-changing operation records who did it, what changed, and the before and after values, all in one audit table. A scheduled job snapshots stock levels into a separate table on a fixed interval, and the trend charts read that snapshot, so reporting never competes with live operations.

Results

• Replaced drifting spreadsheet counts with real-time inventory across stock, receiving, builds, waste, and lot-tracked materials
• Gave the shop floor a scan-driven workflow for pulling, receiving, and counting, with every movement on the audit trail
• Prevented concurrent-edit and cross-system conflicts through work-order locking shared with the ERP
• Built and shipped both systems single-handedly, and kept them running in production