Systems Architecture

The landscape

Orders arrive on two channels. The warehouse, the shop floor, and procurement all sit downstream of the platform that records them.

Two boundaries on this diagram are manual. Storefront orders and customer purchase orders are keyed in rather than fed through an integration, and a low-stock condition sends an email notification that a person acts on instead of raising a purchase order on its own.

Three stores of record, not one

The platform is not a single database with everything in it. It is three separate stores, each with a deliberate boundary around it.

• The platform schema: one Microsoft SQL Server database of 50+ tables covering orders, inventory, materials, builds, and shipments, on the company's own internal Ubuntu Linux server. The ERP and the main warehouse application both read and write it directly.
• The lot-tracked materials subsystem: its own database on that same internal server. It was separated because that data needs tighter access control, lot-level precision, and its own alerting, and because isolating it allowed the legacy version to be replaced without destabilizing live inventory.
• The storefront: OpenCart 3 on PHP 7.4 backed by MySQL, on an external cPanel/WHM VPS, separate from the internal server entirely.

Where the boundaries are, and why

1. Between the ERP and the warehouse application: no boundary at all

They share the same tables. A stock write is visible to an order the moment it happens, with no API layer, no sync job, and nothing that can fall out of step. The cost is that two applications can act on the same work order at the same time, so both honor a shared lock table: a work order locked on the shop floor cannot be checked out in the office, and a scheduled job clears any lock left behind by an abandoned session. The conflict is resolved between the two applications rather than inside either one.

2. Between the main system and the materials subsystem: one owner, one reference

Usage of lot-tracked materials can be recorded from either side. Rather than have each write to the other automatically, one side owns the actual quantity deduction and the other is read-only reference. The two are reconciled by report rather than kept in lockstep by a sync, because a two-way automatic sync between separate databases fails quietly and a reconciliation report fails loudly.

3. Between the storefront and the platform: a person

The storefront is public and the platform is not. They share no network, no server, and no database. Orders cross that line as data a person enters, which is also the point at which pricing and specification are checked against what the shop floor can actually build. What the two sides do share is the SKU vocabulary: the same part numbers identify the same products in both catalogs. The boundary is manual by choice, not because the two systems disagree about what a product is.

What keeps the data honest

Operational data errors compound quietly, so the platform is built to catch them where they enter rather than to trust that they never happen.

• One audit table across every module records who acted, what changed, and the before and after values. It is the largest table in the schema, at over 1,000,000 rows.
• A physical count that does not match the database flags the SKU and blocks pulling against it until the variance is resolved, so a known-wrong quantity cannot drift further while it is investigated.
• Lot-number duplicate detection, negative-stock alerts, and reconciliation reports across materials and stock catch the errors that a workflow alone would let through.
• A scheduled job snapshots stock levels at a fixed interval into a separate table, so trend charts do not compete with live operations.
• Queries are parameterized throughout rather than assembled from concatenated strings, in a schema where every table is business-critical.

What this shape costs

Every decision above was made for a specific scale: 20 to 30 concurrent internal users, one developer, and no strict uptime SLA. These are the places it will break first as that changes.

• A single audit table will eventually bottleneck on write volume. The path is asynchronous audit writes or monthly partitioning.
• Reports query live operational data, so heavy reports compete with live operations. The path is a read replica and asynchronous generation for the heaviest ones.
• Two roles, admin and normal user, cannot express per-record rules. The path is attribute-based access control with a centralized session store for a multi-server deployment.
• Neither internal application sits on a framework, so cross-cutting concerns such as auth and input validation live in shared includes rather than in middleware. That is the first thing that would need to change if a second maintainer joined.