Inventory Management - KAIU Natural Living

Overview

KAIU’s inventory management system provides real-time stock tracking, product catalog management, and an admin interface for managing the entire product database. It includes a sophisticated stock reservation system to prevent overselling.

Product Data Model

Database Schema

Product structure in Prisma:

interface Product {
  id: string; // UUID
  sku: string; // Unique identifier (e.g., "KAIU-LAV-10ML")
  name: string; // Product name (e.g., "Aceite Esencial de Lavanda")
  slug: string; // URL-friendly name
  
  // Variant info
  variantName?: string; // Size/format (e.g., "10ml", "30ml")
  
  // Description
  description: string;
  benefits?: string; // Comma-separated (e.g., "Relajación, Sueño")
  category: string; // e.g., "Aceites Esenciales"
  
  // Pricing & Stock
  price: number; // In Colombian Pesos (COP)
  stock: number; // Available units
  reserved: number; // Units reserved for pending orders
  
  // Physical properties
  weight: number; // Kilograms
  width: number; // Centimeters
  height: number;
  length: number;
  
  // Images
  images: string[]; // Array of image URLs
  
  // Status
  isActive: boolean; // Visible to customers
  
  // Timestamps
  createdAt: DateTime;
  updatedAt: DateTime;
}

Product Variants

Products with multiple sizes/formats are stored as separate records:

Same Product, Different Sizes
Frontend Display

Example: Lavender Essential Oil

[
  {
    "sku": "KAIU-LAV-10",
    "name": "Aceite Esencial de Lavanda",
    "variantName": "10ml",
    "price": 45000,
    "stock": 50
  },
  {
    "sku": "KAIU-LAV-30",
    "name": "Aceite Esencial de Lavanda",
    "variantName": "30ml",
    "price": 120000,
    "stock": 30
  },
  {
    "sku": "KAIU-LAV-50",
    "name": "Aceite Esencial de Lavanda",
    "variantName": "50ml",
    "price": 180000,
    "stock": 20
  }
]

Each variant has independent stock levels and pricing.

Variants grouped in product card:

// Group products by name
const productGroups = products.reduce((acc, product) => {
  if (!acc[product.name]) acc[product.name] = [];
  acc[product.name].push(product);
  return acc;
}, {});

// Show variant selector
<Select>
  {variants.map(v => (
    <option value={v.sku}>
      {v.variantName} - ${v.price.toLocaleString()}
    </option>
  ))}
</Select>

Stock Management

Reservation System

To prevent overselling during checkout process, system uses two-tier stock tracking:

Available Stock

stock fieldTotal units physically in warehouse.Updated when:

Receiving new inventory
Completing sales (COD immediate, Online after payment)
Cancelling orders (stock released)

Reserved Stock

reserved fieldUnits temporarily held for pending orders.Updated when:

Order created (reserves)
Order confirmed (converts to sale)
Order cancelled (releases)

Actual available units for new orders:

const availableForSale = product.stock - product.reserved;

Inventory Service

Backend service handles all stock operations (backend/services/inventory/InventoryService.js):

reserveStock()

Reserve units for new orderCalled during order creation:

class InventoryService {
  static async reserveStock(lineItems) {
    for (const item of lineItems) {
      const product = await prisma.product.findUnique({
        where: { sku: item.sku }
      });
      
      if (!product) {
        throw new Error(`Product ${item.sku} not found`);
      }
      
      const available = product.stock - product.reserved;
      if (available < item.quantity) {
        throw new Error(
          `Insufficient stock for ${product.name}. ` +
          `Available: ${available}, Requested: ${item.quantity}`
        );
      }
      
      // Increment reserved counter
      await prisma.product.update({
        where: { sku: item.sku },
        data: {
          reserved: product.reserved + item.quantity
        }
      });
    }
  }
}

Throws error if insufficient stock, allowing order creation to fail cleanly before any database writes.

confirmSale()

Convert reservation to actual saleCalled when:

COD order created (immediate)
Online payment confirmed (webhook)

static async confirmSale(lineItems) {
  for (const item of lineItems) {
    const product = await prisma.product.findUnique({
      where: { sku: item.sku }
    });
    
    // Deduct from both stock and reserved
    await prisma.product.update({
      where: { sku: item.sku },
      data: {
        stock: product.stock - item.quantity,
        reserved: product.reserved - item.quantity
      }
    });
  }
}

Effect:

stock decreases (physical inventory reduced)
reserved decreases (no longer held)
Net effect: available = stock - reserved stays same

releaseReserve()

Return reserved units to poolCalled when:

Order cancelled
Order creation fails (rollback)
Online payment fails/expires

static async releaseReserve(lineItems) {
  for (const item of lineItems) {
    const product = await prisma.product.findUnique({
      where: { sku: item.sku }
    });
    
    // Only decrease reserved counter
    await prisma.product.update({
      where: { sku: item.sku },
      data: {
        reserved: Math.max(0, product.reserved - item.quantity)
      }
    });
  }
}

Uses Math.max(0, ...) to prevent reserved from going negative in edge cases.

Stock Flow Examples

COD Order
Online Payment
Order Cancellation

Cash on Delivery FlowInitial state:

{ "sku": "KAIU-LAV-10", "stock": 50, "reserved": 0 }

Customer orders 2 units:

Reserve stock:
```
{ "stock": 50, "reserved": 2 }
```
Available: 50 - 2 = 48
Confirm sale (immediate):
```
{ "stock": 48, "reserved": 0 }
```
Available: 48 - 0 = 48

COD orders confirm immediately because payment is guaranteed at delivery.

Wompi Online Payment FlowInitial state:

{ "sku": "KAIU-LAV-10", "stock": 50, "reserved": 0 }

Customer orders 2 units:

Reserve stock:
```
{ "stock": 50, "reserved": 2 }
```
Available: 50 - 2 = 48
Wait for payment… Stock stays reserved while customer completes payment.

3a. Payment confirmed (webhook):

{ "stock": 48, "reserved": 0 }

Available: 48 - 0 = 483b. Payment failed/cancelled:

{ "stock": 50, "reserved": 0 }

Available: 50 - 0 = 50 (released)

Cancel Before ShipmentOrder exists with:

{ "stock": 48, "reserved": 0 }

Admin cancels order:

{ "stock": 50, "reserved": 0 }

Stock returned to pool (both stock and available increase).

Admin Interface

Inventory Manager Component

Admin dashboard includes dedicated inventory tab (src/components/admin/InventoryManager.tsx - referenced in AdminDashboard.tsx:470). Features:

View all products in sortable table
Filter by category, status (active/inactive)
Search by name or SKU
Edit product details inline
Adjust stock levels
Upload/change images
Create new products
Bulk operations

API Endpoints

Inventory management API (backend/admin/inventory.js):

GET /api/admin/inventory
POST /api/admin/inventory
PUT /api/admin/inventory

Fetch all products

const products = await prisma.product.findMany({
  orderBy: [
    { name: 'asc' },
    { variantName: 'asc' }
  ]
});

return res.status(200).json(products);

Returns array of all products, sorted by name then variant.

Create new product

const { 
  sku, name, description, variantName, 
  category, price, stock, isActive, 
  benefits, weight, width, height, length, images 
} = req.body;

if (!name || price === undefined) {
  return res.status(400).json({ 
    error: 'Name and price are required' 
  });
}

const safeSku = sku || `KAIU-${Date.now().toString(36).toUpperCase()}`;
const slug = name.toLowerCase().replace(/[^a-z0-9]+/g, '-') + 
             '-' + safeSku.toLowerCase();

const newProduct = await prisma.product.create({
  data: {
    sku: safeSku,
    name: String(name),
    slug,
    description: String(description) || '',
    variantName: variantName ? String(variantName) : null,
    category: category || 'Otros',
    price: Number(price),
    stock: Number(stock) || 0,
    isActive: isActive !== undefined ? Boolean(isActive) : true,
    images: Array.isArray(images) ? images : [],
    benefits: benefits ? String(benefits) : null,
    weight: weight !== undefined ? Number(weight) : 0.2,
    width: width !== undefined ? Number(width) : 10,
    height: height !== undefined ? Number(height) : 10,
    length: length !== undefined ? Number(length) : 10
  }
});

return res.status(201).json({ success: true, product: newProduct });

If SKU not provided, auto-generates one using timestamp.

Update existing product

const { sku, updates } = req.body;

if (!sku || !updates) {
  return res.status(400).json({ 
    error: 'Missing SKU or updates' 
  });
}

const { 
  price, stock, isActive, name, description, 
  category, variantName, benefits, 
  weight, width, height, length, images 
} = updates;

const dataToUpdate = {};
if (typeof price !== 'undefined') dataToUpdate.price = Number(price);
if (typeof stock !== 'undefined') dataToUpdate.stock = Number(stock);
if (typeof isActive !== 'undefined') dataToUpdate.isActive = Boolean(isActive);
// ... more fields

const updatedProduct = await prisma.product.update({
  where: { sku: sku },
  data: dataToUpdate
});

return res.status(200).json({ success: true, product: updatedProduct });

Only provided fields are updated (partial update supported).

Authentication Required: All inventory endpoints verify admin JWT token:

const user = verifyAdminToken(req);
if (!user) {
  return res.status(401).json({ error: 'Unauthorized' });
}

Product Categories

Standard categories used in KAIU:

Aceites Esenciales

Essential oils extracted from plants

Aceites Portadores

Carrier oils for diluting essentials

Hidrolatos

Floral waters from distillation

Kits

Product bundles and gift sets

Difusores

Aromatherapy diffusers

Otros

Miscellaneous products

Stock Alerts

Low Stock Detection

System can be configured to alert when stock falls below threshold:

// Future enhancement
const LOW_STOCK_THRESHOLD = 5;

const lowStockProducts = await prisma.product.findMany({
  where: {
    stock: { lt: LOW_STOCK_THRESHOLD },
    isActive: true
  }
});

if (lowStockProducts.length > 0) {
  // Send notification to admin
  sendLowStockAlert(lowStockProducts);
}

Out of Stock Handling

Frontend
Cart
AI Chatbot

Product cards automatically show “Out of Stock” badge:

if (product.stock - product.reserved <= 0) {
  return (
    <Badge variant="destructive">Agotado</Badge>
    <Button disabled>No Disponible</Button>
  );
}

Cart validates stock before checkout:

const available = product.stock - product.reserved;
if (quantity > available) {
  toast({
    variant: "destructive",
    title: "Stock insuficiente",
    description: `Solo ${available} unidades disponibles`
  });
  return;
}

AI assistant queries real-time stock:

const products = await prisma.product.findMany({
  where: { name: { contains: query } },
  select: { stock: true, isActive: true }
});

if (product.stock === 0) {
  return "Este producto está temporalmente agotado.";
}

Image Management

Upload Flow

Admin Uploads

Admin selects image file in inventory manager

CDN/Storage

Image uploaded to cloud storage (e.g., AWS S3, Cloudinary)

URL Stored

Image URL saved in images array:

{
  "images": [
    "https://cdn.kaiu.com/products/lavanda-10ml.jpg",
    "https://cdn.kaiu.com/products/lavanda-10ml-2.jpg"
  ]
}

Display

Frontend loads images from URLs in product catalog

Multiple Images

Products support multiple images (array):

First image is primary (shown in catalog)
Additional images in product detail page gallery
AI chatbot uses first image when sending product photos

Data Integrity

SKU Uniqueness

SKU field has unique constraint:

model Product {
  sku String @unique
  // ...
}

Attempting to create duplicate SKU fails:

PrismaClientKnownRequestError: 
Unique constraint failed on the fields: (`sku`)

Stock Validation

Stock and reserved fields have checks:

// Prevent negative stock
if (newStock < 0) {
  throw new Error('Stock cannot be negative');
}

// Prevent reserved exceeding stock
if (reserved > stock) {
  throw new Error('Reserved cannot exceed total stock');
}

Reporting

Inventory Value

Calculate total inventory value:

const inventoryValue = await prisma.product.aggregate({
  where: { isActive: true },
  _sum: {
    // SQL: SUM(price * stock)
  }
});

// Using Prisma's raw query
const result = await prisma.$queryRaw`
  SELECT SUM(price * stock) as total_value
  FROM Product
  WHERE isActive = true
`;

Stock Movement Report

Track stock changes over time (future enhancement):

interface StockMovement {
  id: string;
  productId: string;
  type: 'PURCHASE' | 'SALE' | 'ADJUSTMENT' | 'RETURN';
  quantity: number; // Positive or negative
  previousStock: number;
  newStock: number;
  reason?: string;
  createdAt: DateTime;
}

Integration with AI Chatbot

The AI chatbot queries inventory in real-time:

// In backend/services/ai/Retriever.js
async function executeSearchInventory(query) {
  const products = await prisma.product.findMany({
    where: {
      OR: [
        { name: { contains: query, mode: 'insensitive' } },
        { category: { contains: query, mode: 'insensitive' } },
        { variantName: { contains: query, mode: 'insensitive' } }
      ]
    },
    select: {
      id: true,
      name: true,
      variantName: true,
      price: true,
      stock: true,
      isActive: true,
      category: true,
      description: true
    }
  });
  
  // Only return active products
  return JSON.stringify(products.filter(p => p.isActive));
}

See AI Chatbot for details.

Best Practices

Always Reserve First

Never deduct stock directly. Always:

Reserve
Process order
Confirm or release

Atomic Operations

Use database transactions for multi-product operations to ensure consistency.

Audit Trail

Log all stock changes with timestamp, reason, and admin user for accountability.

Regular Reconciliation

Periodically verify:

reserved matches pending orders
Physical count matches database
No orphaned reservations

Order Management

How orders consume inventory

Admin Dashboard

Inventory management interface

E-commerce

Product catalog and shopping

Getting Started

Core Features

Setup & Configuration

User Guides

Deployment

​Overview

​Product Data Model

​Database Schema

​Product Variants

​Stock Management

​Reservation System

Available Stock

Reserved Stock

​Inventory Service

​Stock Flow Examples

​Admin Interface

​Inventory Manager Component

​API Endpoints

​Product Categories

Aceites Esenciales

Aceites Portadores

Hidrolatos

Kits

Difusores

Otros

​Stock Alerts

​Low Stock Detection

​Out of Stock Handling

​Image Management

​Upload Flow

​Multiple Images

​Data Integrity

​SKU Uniqueness

​Stock Validation

​Reporting

​Inventory Value

​Stock Movement Report

​Integration with AI Chatbot

​Best Practices

Always Reserve First

Atomic Operations

Audit Trail

Regular Reconciliation

​Related Features

Order Management

Admin Dashboard

E-commerce

Build docs developers (and LLMs) love

Overview

Product Data Model

Database Schema

Product Variants

Stock Management

Reservation System

Inventory Service

Stock Flow Examples

Admin Interface

Inventory Manager Component

API Endpoints

Product Categories

Stock Alerts

Low Stock Detection

Out of Stock Handling

Image Management

Upload Flow

Multiple Images

Data Integrity

SKU Uniqueness

Stock Validation

Reporting

Inventory Value

Stock Movement Report

Integration with AI Chatbot

Best Practices

Related Features