An Italian supplier of footwear materials asked us to rebuild its e-commerce platform because stock availability updated only twice a day. We reviewed the platform and proposed a stock integration. The ERP and storefront remained in place. Updates reached the site in milliseconds under optimal conditions, at roughly 70% less than the cost of the proposed rebuild.
The existing stock system
The supplier serves independent cobblers and shoe manufacturers. Stock moves in and out of its warehouses throughout the day, and customers use the website to check availability before ordering.
With only two stock refreshes a day, the website could show materials as available hours after they had sold out. The client requested a new e-commerce platform to address this delay.
Other suppliers had reviewed the problem, and at least one had attempted an implementation without resolving it. The ERP was central to the business. Replacing it was either impractical or too costly for the stock problem the client needed to solve.
Integration constraints
Stock records were held in SQL Server, behind the ERP. The website read from the main database through a VPN. We needed to extract stock movements continuously and reliably while protecting the production system.
The rest of the storefront was meeting the business’s needs. Our assessment narrowed the work to the integration between the ERP and the site, rather than replacing the platform. The new integration had to work with the existing ERP and support frequent warehouse movements.
We had implemented stock synchronisation in other environments with frequent warehouse movements and used that experience to plan the integration.
Implementation
Scope. We reviewed the existing functionality with the client and recommended retaining the platform. The agreed work focused on getting current stock data to the storefront.
ERP access. We worked with the ERP vendor to establish direct read access to SQL Server. The integration was designed to avoid additional load and operational risk for the ERP.
A dedicated read store. We built components to capture stock movements and replicate them through a distributed messaging system into a separate read store for e-commerce. The storefront reads from this store instead of connecting to the production database through the VPN. This separates storefront queries from warehouse operations and gives the site a store designed for fast reads.
Retries and recovery. The pipeline uses deduplication and retries to process each stock movement once and avoid losing updates. During a connection failure, changes accumulate. When the connection returns, parallel processing brings the site back up to date quickly.
Update latency and capacity. Under optimal conditions, availability updates reach the site in the order of milliseconds. The pipeline handles current traffic and has capacity for further growth.
Results
- Stock availability moved from two updates a day to near real time, with updates in the order of milliseconds under optimal conditions.
- 95% fewer orders cancelled or amended because an item was out of stock, following the introduction of more current availability data.
- Roughly 70% lower cost than the proposed full e-commerce rewrite, with the work limited to the stock integration.
- The stock integration was completed while retaining the existing ERP. A previous implementation attempt had not resolved the problem.
- The pipeline supports frequent warehouse movements and recovers from connection failures through deduplication, retries and parallel processing of accumulated changes.
- The separate read store provides capacity for traffic growth.
Operating limits
The millisecond update timing applies under optimal conditions. During a connection outage, stock changes wait for the connection to return before they can reach the site. The implementation retains the existing ERP and storefront, with the messaging pipeline and read store handling stock synchronisation.