Internal Operations Platform (ERP / WMS / MES)

Context

The company already had an internal system, but it was slow: common lookups and reports took several seconds or worse. It also covered only part of the workflow, so the gaps were filled with spreadsheets and manual entry, and nothing reliably recorded who changed what.

As the sole developer, I designed and built the replacement: more complete, and faster to query.

Scope

• Order management: order entry, line items, and a status flow from open to pulled to built to shipped
• Inventory: finished-goods stock levels, valuation, and manual adjustments, on the same schema the warehouse subsystem writes to
• Materials: raw-material procurement, lot-number tracking, consumption logging, and allocation by order
• Builds and work orders: production fulfillment tied to order line items
• Shipments: carrier and tracking, packing slips, commercial invoices, and customs documents
• Reporting and analytics: 40+ reports across orders, inventory, materials, shipping, and sales
• Admin: user and role management, bulk imports, audit log, and work-in-progress tracking

Architecture

A monolith, on purpose, in three tiers. PHP renders the UI and holds the business logic, SQL Server is the single source of truth, and attachments sit on the local filesystem.

Why a monolith rather than microservices? A small internal user base, a one-person team, and no uptime SLA to hit. At that scale, microservices buy operational overhead and little else. I optimized for how fast one person can build and maintain the thing, while staying clear on which parts would have to change first if that stopped being true.

Scale

• One SQL Server database of 50+ tables covering orders, inventory, materials, builds, and shipments, shared directly by the ERP and the warehouse application; the lot-tracked materials subsystem keeps its own database alongside it
• The audit table is the largest in the schema, past 1,000,000 rows
• 20 to 30 internal users working in the system at the same time
• Any one work order is edited by a single person at a time, enforced by the lock table rather than left to convention
• Reports query live operational data and return in under a second

Engineering decisions

1. Concurrency control via pessimistic locking

Two operators editing the same work order would silently overwrite each other. Opening a work order now checks the lock table and takes the lock if it is free; saving releases it. A scheduled job clears anything older than five minutes, so an abandoned session cannot sit on a work order indefinitely. The warehouse subsystem reads and writes this same table, so an order locked on the shop floor cannot be checked out here, and the conflict is settled between the two applications rather than inside either one. Pessimistic locking fits because conflicts here are expensive; optimistic version numbers suit a higher-throughput system where they are rare.

2. Unified audit trail

Every create, update, and delete writes one row to a single audit table: actor, timestamp, object type, object ID, and a readable note. Keeping all modules in one table is what makes "who changed this record" answerable in a single query. The cost is that every operational write drags a synchronous audit write along with it, so the pressure is write concurrency rather than row count. If that becomes the bottleneck, the fix is to move the audit write off the request path, asynchronously or onto its own filegroup. Partitioning would help the archival queries, not the write path.

3. Real-time reporting without extra infrastructure

Reports query SQL Server directly and draw their charts in the browser from server-emitted JSON. Nothing is precomputed, so nothing is ever stale, and there is no extra infrastructure to run. The cost is that a heavy report competes with live operations; the way out is a read replica for reporting and asynchronous generation for the worst offenders.

4. Role-based access control

Two roles, admin and normal user, checked at the top of every page against a server-side session. It is simple and fast to reason about, and it cannot express per-record rules. The day it needs to, the path is attribute-based access control, plus a shared session store if this ever runs on more than one server.

5. Data-integrity discipline

Bad operational data compounds quietly, so the platform catches it at the entry points: duplicate lot-number detection, negative-stock alerts, and reconciliation reports across materials and stock. Queries are parameterized throughout rather than assembled from concatenated strings. In a schema where every table is business-critical, that is not something to leave to discipline.

6. Two item types, one lot-tracked

The catalog splits in two, and the schema follows. Stock items are bought and resold as they are: one quantity, no production history. Built items are assembled in house and itemized against the raw materials they consume, and those materials are lot-tracked, so a finished unit traces back to the work order and the lots it drew on. The split is per line item, not per order, so one work order routinely carries both and each line takes its own path to ready. Lot precision is therefore paid for on one side only. It costs real effort to maintain and it buys traceability only where something was actually assembled, which is why it lives in a subsystem of its own rather than as a column every stock row would carry.

Results

• Replaced a slow, incomplete legacy system with one platform covering the full order-to-shipment lifecycle: orders, inventory, materials, builds, and shipments
• Designed the schema and query layer so reporting runs against live operational data, with no separate analytics stack to build or keep in sync
• Gave the business end-to-end order traceability and an audit trail it had never had
• Built, maintained, and extended the platform single-handedly in production