Stop frustrating users with “Out of Stock” errors at checkout by architecting a location-aware inventory system that doesn’t melt your primary database.

Architecting Multi-Location Inventory: Filtering Products by Region Without Killing Your DB

I still wake up in a cold sweat thinking about Black Friday 2018. We were running a mid-sized e-commerce platform for a client selling heavy automotive parts. The client had three warehouses: one in New Jersey, one in Texas, and one in California. The logic seemed simple enough: if total_qty > 0, show the product.

The problem? A guy in Seattle would order a transmission. We had one left… in New Jersey. The shipping cost calculated at checkout was higher than the profit margin on the part. We ended up canceling hundreds of orders manually, the C-suite was screaming, and the support ticket queue looked like a stock market crash. That was the day I learned that “In Stock” is a relative term. If it’s not in a region where you can profitably ship it, it doesn’t exist.

The “Why”: It’s Not Just a Query Problem

The root cause here isn’t usually the code; it’s the schema. Most legacy monoliths (and even some modern headless setups) treat inventory as a global integer attached to a SKU. When you try to filter by city or region dynamically, you are asking your database to do a massive amount of heavy lifting on every single category page load.

If you try to run a JOIN across products, inventory_sources, and geo_zones for 50 products per page with 10,000 active concurrent users, prod-db-01 is going to lock up. I guarantee it. You cannot calculate geospatial availability in real-time on the main thread.

Solution 1: The Quick Fix (The “UI Facade”)

If you are bleeding money right now and need a fix by tomorrow morning, this is what you do. You don’t actually filter the database query. You filter the experience.

You load the products normally (global stock). Then, you use an asynchronous Javascript call to check the user’s detected ZIP code against the inventory source. If the item isn’t in their region, you greyscale the image or add a “Not available in your area” badge.

Pro Tip: This is a hack. It saves server load because you can cache the initial HTML, but it frustrates users because they see the item before realizing they can’t have it. Use this only while building Solution 2.

// The 'Band-Aid' approach
// 1. Load page with all products
// 2. Client-side fetch
const userRegion = getUserRegion(); // 'US-EAST'

fetch(`/api/inventory/batch?region=${userRegion}&skus=${visibleSkus}`)
  .then(data => {
      data.forEach(item => {
          if (!item.available) {
              document.getElementById(`product-${item.sku}`).classList.add('out-of-area');
          }
      });
  });

Solution 2: The Permanent Fix (Index-Based Faceting)

This is how the big players do it. You stop asking your SQL database to do math and you start utilizing your search engine (Elasticsearch, Algolia, Solr).

You need to denormalize your data. Instead of storing inventory as a number, you store availability as a tag array on the product document itself. When the inventory level changes in your ERP or WMS, you push an update to the search index.

Your indexed document for a “Red T-Shirt” should look like this:

{
  "sku": "TSHIRT-RED-L",
  "name": "Red T-Shirt Large",
  "global_qty": 150,
  "availability_zones": [
    "US-NY-METRO",
    "US-TX-ALL",
    "US-CA-SOCAL"
  ]
}

Now, when a user visits the site, you map their IP or selected ZIP code to a Zone ID (e.g., US-NY-METRO). Your query becomes a simple filter, which Search Engines are incredibly fast at handling.

Solution 3: The ‘Nuclear’ Option (Edge Computing)

If you are running a flash sale site or high-velocity retail (think ticket sales or limited sneaker drops), even the search index might be too slow. This is where we get aggressive.

We move the logic to the Edge (Cloudflare Workers or AWS Lambda@Edge). We store regional inventory counts in a geo-replicated Redis instance (like AWS Global Datastore).

Before the request even hits your backend application, the Edge Worker intercepts it:

1. Determines user location from the request headers.
2. Checks Redis for that specific region’s inventory key.
3. Modifies the downstream request to strictly include only available IDs.

// Pseudo-code for Cloudflare Worker
const region = request.cf.regionCode; // e.g., "TX"
const inventoryKey = `inv:${region}:available_skus`;

// Fetch available SKUs from Edge KV or Redis
const availableSkus = await KV.get(inventoryKey);

// Rewrite the request to the origin to include a filter
const newUrl = new URL(request.url);
newUrl.searchParams.set('allowed_ids', availableSkus);

return fetch(newUrl, request);

This is complex to maintain. I only recommend this if you have a dedicated DevOps team. For 95% of you, Solution 2 is the sweet spot where performance meets sanity.

Darian Vance

Lead Cloud Architect & DevOps Strategist

With over 12 years in system architecture and automation, Darian specializes in simplifying complex cloud infrastructures. An advocate for open-source solutions, he founded TechResolve to provide engineers with actionable, battle-tested troubleshooting guides and robust software alternatives.

🤖 Frequently Asked Questions