Inventory Management Systems That Scale — From Spreadsheets to Real-Time

The Flash Sale That Broke Everything

A DTC brand called us on a Monday morning in full panic mode. They'd run a flash sale over the weekend — 40% off their best seller. It went great. Too great. They oversold 340 units of a product they had 200 of.

The fallout: $12,000 in refunds, 340 angry customers, a spike in 1-star reviews on Amazon, and a Shopify trust score that took three months to recover. All because their inventory system was a Google Sheet that someone updated every morning at 9 AM.

We see this exact story at least once a month. The details change — different brand, different channel, different product — but the root cause is always the same: inventory management that worked at $500K/year breaks catastrophically at $5M/year.

What "Inventory" Actually Means (It's Not One Number)

This is the first thing we fix with every client. They think inventory is a single number: "we have 200 units." It's not. It's five numbers, and confusing them is how you oversell:

On Hand:    200  ← Physically sitting in your warehouse
Reserved:    45  ← Allocated to orders not yet shipped
Committed:   12  ← Currently being picked/packed
In Transit:  80  ← On a truck from your supplier
Available:  143  ← What you can actually sell (200 - 45 - 12)

Every brand we audit is tracking On Hand and calling it Available. That's a 28% error in this example. During a sale, when Reserved spikes, that error becomes catastrophic.

The One Thing You Cannot Get Wrong

Atomic reservation. This is the single most important piece of code in your entire e-commerce stack, and most teams get it wrong. Here's why: two customers click "Buy" at the same time for the last unit. Without proper locking, both orders succeed. One customer gets a refund and a bad experience.

async function reserveInventory(
  sku: string,
  quantity: number,
  orderId: string,
  locationId: string
): Promise<ReservationResult> {
  return await db.$transaction(async (tx) => {
    // FOR UPDATE locks this row — nobody else can read it
    // until we're done. This is the whole trick.
    const inventory = await tx.$queryRaw<InventoryRecord[]>`
      SELECT * FROM inventory
      WHERE sku = ${sku} AND location_id = ${locationId}
      FOR UPDATE
    `;
 
    if (!inventory[0]) {
      return { success: false, reason: "SKU not found" };
    }
 
    const available = inventory[0].on_hand
      - inventory[0].reserved
      - inventory[0].committed;
 
    if (available < quantity) {
      return {
        success: false,
        reason: "Insufficient stock",
        available,
        requested: quantity,
      };
    }
 
    // Reserve it
    await tx.inventory.update({
      where: { sku_locationId: { sku, locationId } },
      data: { reserved: { increment: quantity } },
    });
 
    // Track the reservation (with an expiry — more on this below)
    await tx.reservations.create({
      data: {
        sku, orderId, quantity, locationId,
        status: "active",
        expiresAt: addMinutes(new Date(), 30)
      },
    });
 
    return { success: true };
  });
}

FOR UPDATE is doing all the heavy lifting here. It's a database-level lock that prevents two transactions from reading the same row simultaneously. Without it, you're relying on application-level checks that will fail under concurrent load. We've seen it happen on every platform — Shopify, custom builds, headless setups. If your reservation isn't atomic at the database level, it's broken. You just haven't hit enough traffic to notice yet.

Multi-Channel Sync: Where Everyone Lies to Themselves

Here's a conversation we have with every multi-channel brand:

Us: "How do you sync inventory between Shopify and Amazon?" Them: "We use [app name], it syncs every 15 minutes." Us: "What happens when you sell 10 units on Shopify in those 15 minutes?" Them: "..."

Fifteen-minute sync intervals are fine at low volume. At 500+ orders/day across channels, you're guaranteed to oversell. And Amazon doesn't forgive overselling — they'll suppress your listings.

Here's how we build it:

async function syncChannels(sku: string) {
  const inventory = await getAggregatedInventory(sku);
 
  // Channel-specific buffers — this is where experience matters
  const channels = [
    { name: "shopify", buffer: 0.9 },   // 10% safety buffer
    { name: "amazon", buffer: 0.85 },    // Amazon penalizes hard — be conservative
    { name: "wholesale", buffer: 0.7 },  // Reserve 30% for higher-margin DTC
  ];
 
  await Promise.all(channels.map(async (channel) => {
    const channelAvailable = Math.floor(
      inventory.available * channel.buffer
    );
    await updateChannelInventory(channel.name, sku, channelAvailable);
  }));
}
 
// Event-driven, not cron-driven. Every inventory change triggers a sync.
eventBus.on("inventory.changed", async (event) => {
  await syncChannels(event.sku);
});

Two things to notice:

1. Event-driven sync, not scheduled. Every inventory change triggers an immediate sync. No 15-minute gaps.
2. Channel buffers are different. Amazon gets 85% of available because the penalty for overselling is brutal. Wholesale gets 70% because you want to protect DTC margin. These numbers aren't arbitrary — we've tuned them across dozens of brands.

The Reorder Problem Nobody Solves Well

Every brand we work with has the same story: "We ran out of our best seller for 3 weeks because nobody noticed inventory was low." Then they overcorrect and tie up $200K in excess inventory that sits in a warehouse for 6 months.

Automated reordering isn't glamorous, but it's the difference between brands that scale smoothly and brands that lurch from stockout to overstock:

async function checkReorderPoints() {
  const lowStockItems = await db.inventory.findMany({
    where: {
      available: { lte: db.raw("reorder_point") },
    },
    include: { supplier: true },
  });
 
  // Group by supplier — one PO per supplier, not per SKU
  const bySupplier = groupBy(lowStockItems, "supplier.id");
 
  for (const [supplierId, items] of Object.entries(bySupplier)) {
    await createPurchaseOrder({
      supplierId,
      items: items.map(item => ({
        sku: item.sku,
        quantity: item.reorderQuantity,
        currentStock: item.available,
      })),
    });
 
    await notifications.send({
      channel: "procurement",
      message: `Auto PO for ${items.length} SKUs from ${items[0].supplier.name}`,
    });
  }
}

The reorder point calculation is where most teams phone it in. They set a static number — "reorder when we hit 50 units" — and never update it. That's fine for stable products. For anything seasonal or trending, you need dynamic reorder points based on velocity. A product selling 20/day needs a very different reorder point than one selling 2/day, especially when your supplier has a 3-week lead time.

What Doesn't Work (And We've Tried)

We've implemented inventory systems on every major platform. Here's what we've learned the hard way:

The sweet spot for most DTC brands doing $5M–$50M? A lightweight custom inventory service that handles reservation and sync, connected to your existing Shopify/platform via webhooks. It's a 4-6 week build, not a 6-month ERP implementation.

Cycle Counting: The Boring Thing That Saves You

Your system count will drift from physical reality. It always does. Warehouse staff makes mistakes, returns get miscounted, shrinkage happens. The question isn't whether your counts are wrong — it's how wrong and how fast you catch it.

Full physical counts are expensive and disruptive. Cycle counting is the answer:

A items (top 20% by revenue):  Count weekly
B items (next 30%):            Count monthly
C items (bottom 50%):          Count quarterly

Discrepancy > 5%? → Investigation required
Discrepancy > 10%? → Stop selling that SKU until resolved

We had a client discover through cycle counting that a warehouse employee was stealing high-value items — the system showed 50 units, the shelf had 38. Without regular counts, they'd have found out when a customer ordered something that wasn't there.

The Spreadsheet Got You Here. It Won't Get You There.

Every successful DTC brand started with a spreadsheet. There's no shame in it. But the spreadsheet that got you to $1M in revenue will actively prevent you from reaching $10M. The overselling, the stockouts, the manual sync — these aren't inconveniences at scale. They're revenue killers.

Get the reservation logic right. Make it atomic. Sync channels in real-time, not on a timer. Automate reordering before you run out, not after. And count your inventory regularly, because the system is always lying to you a little bit.

The spreadsheet got you to $1M. Proper inventory systems get you to $100M. The brands that figure this out early spend their time growing. The ones that don't spend their time apologizing to customers.