When building internal enterprise tools or bespoke erp systems, high-volume catalog data is quick to unveil architectural bottlenecks. If your application needs to batch process feeds of millions of sku records from various supplier formats while maintaining multi-channel stock synchronization in near real-time, HTTP synchronous processing is out of the question.
Here is the architectural breakdown of the engineering decisions behind STK - our bespoke operations platform for a UK wholesale distributor of millions of cosmetic and fragrance SKUs.
1. Ingestion Bottlenecks: Memory Allocation vs Stream Chunking
Catalogue feeds from suppliers usually come in CSV or XML files containing tens/hundreds of thousands of lines. Trying to parse these feeds within regular web server request lifecycles will cause execution timeouts and memory limit exhaustion.
The Architectural Pattern:
- Decouple upload from processing: The web application accepts the file, validates headers, and writes it to private storage with a queued task record in the database. From there, scheduled background cron jobs execute isolated PHP CLI workers, outside of the web server process.
- CLI Background Workers: Scheduled background cron jobs run isolated PHP CLI workers outside the web server process.
- Stream-Based Chunking: By reading line-by-line using file streams rather than loading entire files into memory, datasets with millions of rows can be processed efficiently within strict memory limits, running in manageable database batches.
2. Database Indexing for Sub-Second SKU Lookups
When a database table crosses the threshold of millions of rows, standard search queries can become slow if compound indexing is not setup correctly
- Covering indexes: Queries executed by the buying team (checking margin rates against supplier price lists, etc.) make use of compound composite indexes spanning SKU, supplier identifiers, and update timestamps.
- Data segregation: Instead of altering the main product records when a catalogue is ingested, supplier offerings are stored in a separate offer-comparison relational table. This avoids table lock contention on live inventory records during huge feed updates.
3. Bi-Directional REST Integration with External Platforms
STK is not a standalone system - it orchestrates live data between Unleashed (warehouse inventory management) and Selro (multi-channel marketplace distributor).
To preserve state without hammering rate-limited external APIs:
- Delta Synchronization: Delta synchronization. Only modified records (identified by updated timestamps + checksum hashing) are sent through the REST API connectors.
- Queue retries with exponential backoff. All external API requests are wrapped in an asynchronous outbox queue. If a remote third-party API returns a rate-limit notice (429) or server downtime (503), the local task will retry automatically, without interrupting active users.
Conclusion & Architectural Takeaway
Scaling enterprise tools do not require the addition of microservice complexity where it is not needed. A well-indexed relational schema, background cli worker processes, and clean REST adapters built on a stable framework like CodeIgniter provide low maintenance overhead and sub-second performance even when spanning millions of data rows.
Explore the complete case study and functional breakdown:
https://thefoxlab.com/case-study/ace-beauty-group-bespoke-erp-platform
Top comments (0)