AI & Software Solution
Back to All Articles
Operations

Inventory Management: From Spreadsheets to Automation

GE

Guvantic Engineering

Guvantic Technical Publication

14 min
Inventory Management: From Spreadsheets to Automation
Executive Architectural Overview (AEO Direct Answer)

Complete guide to inventory management: real-time stock tracking, automated reorder alerts, multi-location visibility, and integration with sales channels. Production-ready code examples.

Spreadsheets break at scale. This guide shows you how to build or implement an inventory management system that prevents stockouts, reduces waste, and saves hours of manual work.

The Problem: Manual Inventory Breaks at Scale

Architectural Topology Specification
Spreadsheet Failure Points — Shows:
Human error: Mistyped numbers, missed updates
Version conflicts: Multiple copies, outdated data
No real-time visibility: Stock counts are always wrong
No alerts: Stockouts happen without warning
Cost: 8-12% of revenue lost to poor inventory management

Common Scenarios:

1.🚫 Out of stock on website → Lost sales → Angry customers → Competitor wins
2.📦 Overstock → Tied-up capital → Storage costs → Dead stock write-offs
3.📊 Manual counts → 3 days of work → Still inaccurate

Solution: Real-Time Inventory System

Architectural Topology Specification
Modern Inventory Management Architecture — Shows:
E-Commerce Storefront → Inventory API
POS System → Inventory API
Marketplace Sync → Inventory API
Inventory API → Database (real-time stock)
Inventory API → Reorder Engine → Supplier alerts
Warehouse Management → Barcode scanning → Inventory updates

Core Data Model:

sqlProduction snippet
-- Products
CREATE TABLE products (
  id UUID PRIMARY KEY,
  sku VARCHAR(50) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10,2),
  cost DECIMAL(10,2),
  category VARCHAR(100),
  weight DECIMAL(8,2),
  created_at TIMESTAMP DEFAULT NOW()
);

-- Inventory
CREATE TABLE inventory (
  id UUID PRIMARY KEY,
  product_id UUID REFERENCES products(id),
  location_id UUID REFERENCES locations(id),
  quantity INT NOT NULL DEFAULT 0,
  reserved_quantity INT NOT NULL DEFAULT 0,
  available_quantity INT GENERATED ALWAYS AS (quantity - reserved_quantity) STORED,
  reorder_point INT DEFAULT 5,
  reorder_quantity INT DEFAULT 20,
  last_updated TIMESTAMP DEFAULT NOW()
);

-- Inventory Transactions
CREATE TABLE inventory_transactions (
  id UUID PRIMARY KEY,
  product_id UUID REFERENCES products(id),
  location_id UUID REFERENCES locations(id),
  quantity_change INT NOT NULL,
  transaction_type VARCHAR(50) NOT NULL, -- SALE, RESTOCK, RETURN, ADJUSTMENT
  reference_id VARCHAR(100), -- Order ID, Purchase Order ID
  note TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Locations (Warehouses, Stores)
CREATE TABLE locations (
  id UUID PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  type VARCHAR(50) NOT NULL, -- WAREHOUSE, STORE, DC
  address TEXT,
  is_active BOOLEAN DEFAULT TRUE
);

Inventory API Implementation

Check Stock API

typescriptProduction snippet
// api/inventory/stock.ts
import { prisma } from "@/lib/db";

export async function getProductStock(productId: string) {
  const inventory = await prisma.inventory.findMany({
    where: { productId },
    include: { location: true },
  });

  const totalStock = inventory.reduce(
    (sum, item) => sum + item.quantity, 0
  );

  const availableStock = inventory.reduce(
    (sum, item) => sum + (item.quantity - item.reservedQuantity), 0
  );

  return {
    productId,
    totalStock,
    availableStock,
    byLocation: inventory.map((item) => ({
      location: item.location.name,
      quantity: item.quantity,
      available: item.quantity - item.reservedQuantity,
    })),
  };
}

Reserve Stock (for Orders)

typescriptProduction snippet
// api/inventory/reserve.ts
import { prisma } from "@/lib/db";
import { Prisma } from "@prisma/client";

export async function reserveStock(
  productId: string,
  quantity: number,
  orderId: string
) {
  return await prisma.$transaction(async (tx) => {
    const inventory = await tx.inventory.findFirst({
      where: { productId },
      orderBy: { availableQuantity: "desc" },
    });

    if (!inventory) {
      throw new Error("Product not found in inventory");
    }

    const available = inventory.quantity - inventory.reservedQuantity;
    if (available < quantity) {
      throw new Error(`Insufficient stock. Available: ${available}, Requested: ${quantity}`);
    }

    // Reserve the stock
    await tx.inventory.update({
      where: { id: inventory.id },
      data: {
        reservedQuantity: { increment: quantity },
      },
    });

    // Create transaction record
    await tx.inventoryTransaction.create({
      data: {
        productId,
        locationId: inventory.locationId,
        quantityChange: -quantity,
        transactionType: "RESERVED",
        referenceId: orderId,
        note: `Reserved for order ${orderId}`,
      },
    });

    return { success: true, reserved: quantity };
  });
}

Release Stock (After Fulfillment)

typescriptProduction snippet
// api/inventory/release.ts
export async function releaseStock(
  productId: string,
  quantity: number,
  orderId: string
) {
  return await prisma.$transaction(async (tx) => {
    const inventory = await tx.inventory.findFirst({
      where: { productId },
    });

    if (!inventory) {
      throw new Error("Product not found in inventory");
    }

    // Convert reserved to actual stock reduction
    await tx.inventory.update({
      where: { id: inventory.id },
      data: {
        quantity: { decrement: quantity },
        reservedQuantity: { decrement: quantity },
      },
    });

    await tx.inventoryTransaction.create({
      data: {
        productId,
        locationId: inventory.locationId,
        quantityChange: -quantity,
        transactionType: "SALE",
        referenceId: orderId,
        note: `Fulfilled order ${orderId}`,
      },
    });

    return { success: true };
  });
}

Automated Reorder System

Reorder Engine

typescriptProduction snippet
// services/reorder.ts
import { prisma } from "@/lib/db";

export async function checkReorderPoints() {
  const inventoryItems = await prisma.inventory.findMany({
    where: {
      quantity: {
        lte: prisma.inventory.fields.reorderPoint,
      },
    },
    include: {
      product: true,
      location: true,
    },
  });

  const reorderItems: any[] = [];

  for (const item of inventoryItems) {
    const { product, quantity, reorderQuantity, reorderPoint, location } = item;

    // Don't reorder if there are pending orders
    const pendingOrders = await getPendingPurchaseOrders(product.id);
    if (pendingOrders > 0) {
      continue;
    }

    const quantityNeeded = reorderQuantity - (reorderPoint - quantity);

    reorderItems.push({
      product,
      location,
      currentStock: quantity,
      reorderPoint,
      quantityNeeded,
    });
  }

  // Send reorder notifications
  for (const item of reorderItems) {
    await createPurchaseOrder(item);
    await sendReorderAlert(item);
  }

  return reorderItems;
}

async function createPurchaseOrder(item: any) {
  // Create purchase order in database
  await prisma.purchaseOrder.create({
    data: {
      productId: item.product.id,
      supplierId: item.product.supplierId,
      quantity: item.quantityNeeded,
      status: "PENDING",
      notes: `Auto-generated reorder - stock below ${item.reorderPoint}`,
    },
  });
}

// Schedule reorder check every hour
import cron from "node-cron";
cron.schedule("0 * * * *", checkReorderPoints);

Lead Time Based Reordering

typescriptProduction snippet
export async function checkLeadTimeReorder() {
  const products = await prisma.product.findMany({
    where: { supplierLeadTime: { gt: 0 } },
    include: { inventory: true },
  });

  for (const product of products) {
    const totalStock = product.inventory.reduce(
      (sum, inv) => sum + inv.quantity, 0
    );

    const dailySales = await getAverageDailySales(product.id);
    const leadTimeDays = product.supplierLeadTime;
    const bufferDays = 3; // Safety buffer

    const minStock = dailySales * (leadTimeDays + bufferDays);

    if (totalStock < minStock) {
      const requiredQty = (minStock - totalStock) * 1.5;
      await createPurchaseOrder({
        product,
        quantityNeeded: Math.ceil(requiredQty),
      });
    }
  }
}

Multi-Location Synchronization

Architectural Topology Specification
Multi-Location Inventory Sync — Shows:
Warehouse A → Inventory Service
Warehouse B → Inventory Service
Store A → Inventory Service
Store B → Inventory Service
Inventory Service → Unified view → E-commerce → Customer

Finding Nearest Stock

typescriptProduction snippet
// api/inventory/nearest.ts
export async function findNearestLocation(
  productId: string,
  userLat: number,
  userLng: number,
) {
  const locations = await prisma.$queryRaw`
    SELECT
      l.id,
      l.name,
      l.latitude,
      l.longitude,
      i.quantity,
      i.available_quantity,
      (
        6371 * acos(
          cos(radians(${userLat})) *
          cos(radians(l.latitude)) *
          cos(radians(l.longitude) - radians(${userLng})) +
          sin(radians(${userLat})) *
          sin(radians(l.latitude))
        )
      ) AS distance_km
    FROM locations l
    JOIN inventory i ON i.location_id = l.id
    WHERE i.product_id = ${productId}
      AND i.available_quantity > 0
    ORDER BY distance_km ASC
    LIMIT 1
  `;

  return locations[0] || null;
}

Integration with Sales Channels

Webhook for Real-Time Sync

typescriptProduction snippet
// api/webhooks/sale.ts
export async function handleSaleWebhook(req: Request) {
  const { productId, quantity, channel } = await req.json();

  // Update inventory in real-time
  await prisma.inventory.update({
    where: { productId },
    data: {
      quantity: { decrement: quantity },
    },
  });

  // Sync to all channels
  await syncToChannels({ productId, quantity, channel });

  return Response.json({ success: true });
}

async function syncToChannels(data: any) {
  const channels = await getActiveChannels();
  for (const channel of channels) {
    try {
      await fetch(`${channel.webhookUrl}/inventory`, {
        method: "POST",
        headers: { Authorization: `Bearer ${channel.apiKey}` },
        body: JSON.stringify(data),
      });
    } catch (error) {
      console.error(`Failed to sync to ${channel.name}:`, error);
    }
  }
}
Keywords:#inventory management#stock control#warehouse software#SKU tracking#reorder alerts#automation
GE

Guvantic Engineering

Engineering & System Architecture Division

Engineering teams at Guvantic deliver mission-critical software, high-concurrency microservices, and international organic positioning strategies for fast-growing companies in USA, UK, and India.

Related Engineering Insights