Context
A WooCommerce merchant sold across two regions. Warehouse stock lived in a 3PL system updated by their logistics partner. The store showed "in stock" while the warehouse was empty-oversells happened twice before they called for help.
They did not need a new storefront. They needed truth between two systems that would never merge.
What I built
- Nightly full sync: SKU map, quantity available, backorder allowed flag
- Incremental webhook listener for high-turnover SKUs (optional path after week two)
- Admin page listing last sync time, rows changed, and last error
- Email alert to merchant + me when a run fails or SKU mapping is missing
SKU mapping table maintained by the merchant-aliases for renamed products, not hardcoded in code.
Technical approach
Idempotent upserts keyed on warehouse SKU → WooCommerce product ID. Conflicts (same SKU mapped twice) fail the run loudly instead of picking a winner.
Rate limits respected with backoff. Dry-run mode on staging with fake inventory swings so the merchant could see behavior before Black Friday.
Documented replay: how to re-run last night's job without duplicating adjustments.
Outcomes
- Oversell incidents stopped after mapping cleanup in week one
- Sync still running on the same schedule years later (client renewed hosting, not the integration)
- Merchant learned to treat SKU map as inventory-not a one-time setup task
Stack
WooCommerce REST API · Node.js · Cron · PostgreSQL job log · SMTP alerts