Inventory / Stock Management Module

Complete enterprise-grade inventory tracking and stock movement management with real-time updates and complete audit trail.

Overview

The Inventory module provides comprehensive stock management capabilities including master data management, real-time quantity tracking, automated stock movements, and complete audit trails. All operations are tenant-isolated and integrated with other modules.

Features

Core Capabilities

Master Data Management: SKU, name, description, zone location
Real-Time Tracking: Live quantity updates with automatic synchronization
Stock Movements: Complete audit trail (inbound, outbound, adjustment, transfer)
Low Stock Alerts: Automated warnings when quantity falls below minimum
Zone Organization: Zone-based inventory location tracking
Barcode Integration: Quick item lookup via barcode scanning
Movement History: Complete audit trail with user tracking
Analytics: Stock movement reports and trends

Architecture

mermaid

graph LR
    A[Inventory Items] --> B[Stock Movements]
    A --> C[Zones]
    B --> D[Users]
    B --> E[Audit Trail]
    A --> F[Pick & Pack]
    A --> G[Slotting AI]
    
    style A fill:#3b82f6
    style B fill:#10b981
    style C fill:#f59e0b

Database Schema

`inventory_items` Table

Column	Type	Constraints	Description
`id`	uuid	PRIMARY KEY	Unique item identifier
`tenant_id`	uuid	NOT NULL, FK → tenants	Tenant isolation
`sku`	text	NOT NULL, UNIQUE	Stock Keeping Unit (unique)
`name`	text	NOT NULL	Item name
`description`	text	NULL	Optional description
`zone_id`	uuid	FK → zones, NULL	Zone location
`quantity`	integer	NOT NULL, >= 0	Current stock quantity
`min_quantity`	integer	NOT NULL, >= 0	Minimum stock level
`unit`	text	NOT NULL, DEFAULT 'pcs'	Unit of measurement
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`updated_at`	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp

Indexes:

idx_inventory_items_sku on sku
idx_inventory_items_zone on zone_id
idx_inventory_items_tenant on tenant_id
idx_inventory_items_low_stock on (zone_id, quantity, min_quantity) WHERE quantity < min_quantity

`stock_movements` Table

Column	Type	Constraints	Description
`id`	uuid	PRIMARY KEY	Unique movement identifier
`tenant_id`	uuid	NOT NULL, FK → tenants	Tenant isolation
`item_id`	uuid	NOT NULL, FK → inventory_items	Related item
`type`	text	NOT NULL, CHECK	Movement type: 'inbound', 'outbound', 'adjustment', 'transfer'
`qty`	integer	NOT NULL, != 0	Movement quantity (positive or negative)
`from_zone_id`	uuid	FK → zones, NULL	Source zone (for transfer)
`to_zone_id`	uuid	FK → zones, NULL	Destination zone (for transfer/inbound)
`user_id`	uuid	NOT NULL, FK → profiles	User who created movement
`reason`	text	NULL	Optional reason for movement
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Movement timestamp

Indexes:

idx_stock_movements_item on item_id
idx_stock_movements_type on type
idx_stock_movements_tenant on tenant_id
idx_stock_movements_created on created_at

API Functions

Create Item

typescript

import { createItem } from '@/lib/inventory';

const { data, error } = await createItem({
  sku: 'ITEM-001',
  name: 'Sample Product',
  description: 'Product description',
  zone_id: 'zone-uuid',
  min_quantity: 10,
  unit: 'pcs',
  tenant_id: 'tenant-uuid', // Optional, auto-set from context
});

Parameters:

sku (string, required): Unique Stock Keeping Unit
name (string, required): Item name
description (string, optional): Item description
zone_id (uuid, optional): Initial zone location
min_quantity (number, optional, default: 0): Minimum stock level
unit (string, optional, default: 'pcs'): Unit of measurement
tenant_id (uuid, optional): Tenant ID (auto-set from auth context)

Returns: { data: InventoryItem | null, error: any }

Adjust Stock

typescript

import { adjustStock } from '@/lib/inventory';

// Increase stock by 10
const { data, error } = await adjustStock(
  'item-uuid',
  10,
  'Manual adjustment - stock correction'
);

// Decrease stock by 5
const { data, error } = await adjustStock(
  'item-uuid',
  -5,
  'Stock correction - found discrepancy'
);

Parameters:

itemId (string, required): Inventory item ID
adjustmentQty (number, required): Adjustment quantity (positive or negative)
reason (string, optional): Reason for adjustment

Returns: { data: StockMovement | null, error: any }

Note: Only admins can perform adjustments. Regular users should use inbound/outbound movements.

Inbound Movement

typescript

import { inbound } from '@/lib/inventory';

const { data, error } = await inbound({
  item_id: 'item-uuid',
  qty: 50,
  to_zone_id: 'zone-uuid',
  reason: 'Received from supplier',
});

Parameters:

item_id (string, required): Inventory item ID
qty (number, required, > 0): Inbound quantity
to_zone_id (uuid, optional): Destination zone
reason (string, optional): Reason for inbound

Returns: { data: StockMovement | null, error: any }

Effect: Increases item quantity by qty.

Outbound Movement

typescript

import { outbound } from '@/lib/inventory';

const { data, error } = await outbound({
  item_id: 'item-uuid',
  qty: 20,
  from_zone_id: 'zone-uuid',
  reason: 'Shipped to customer',
});

Parameters:

item_id (string, required): Inventory item ID
qty (number, required, > 0): Outbound quantity
from_zone_id (uuid, optional): Source zone
reason (string, optional): Reason for outbound

Returns: { data: StockMovement | null, error: any }

Effect: Decreases item quantity by qty.

Validation: Fails if quantity would go below 0.

Transfer Movement

typescript

import { transfer } from '@/lib/inventory';

const { data, error } = await transfer({
  item_id: 'item-uuid',
  qty: 15,
  from_zone_id: 'zone-a-uuid',
  to_zone_id: 'zone-b-uuid',
  reason: 'Reorganization - moving to faster zone',
});

Parameters:

item_id (string, required): Inventory item ID
qty (number, required, > 0): Transfer quantity
from_zone_id (uuid, required): Source zone
to_zone_id (uuid, required): Destination zone
reason (string, optional): Reason for transfer

Returns: { data: StockMovement | null, error: any }

Effect: Moves quantity from one zone to another. Total quantity unchanged.

Get Item with History

typescript

import { getItemWithMovementHistory } from '@/lib/inventory';

const { data, error } = await getItemWithMovementHistory('item-uuid');
// Returns: { item: InventoryItem, movements: StockMovement[] }

Returns: Complete item data with all stock movements in chronological order.

Get Low Stock Items

typescript

import { getLowStockItems } from '@/lib/inventory';

const { data, error } = await getLowStockItems();
// Returns: InventoryItem[] where quantity < min_quantity

Returns: Array of items where quantity < min_quantity.

Data Flow

mermaid

sequenceDiagram
    participant User
    participant UI
    participant API
    participant Database
    participant RLS
    
    User->>UI: Create Stock Movement
    UI->>API: inbound/outbound/transfer()
    API->>Database: Insert stock_movement
    Database->>RLS: Check tenant access
    RLS->>Database: Authorize
    Database->>Database: Update inventory_items.quantity
    Database->>API: Return movement record
    API->>UI: Update UI
    UI->>User: Show confirmation

Permissions

Role	Permissions
Admin	Full access: create items, all movement types, adjustments
Inventory Worker	Inbound, outbound, transfer (no adjustments, no item creation)
Driver	No access to inventory
Tenant Admin	Full access within tenant

Integration Points

Pick & Pack Integration

When items are picked in Pick & Pack module, outbound movements are automatically created:

typescript

// Automatic integration
// When pick list item is marked as "picked"
// → Creates outbound movement automatically

Slotting AI Integration

Slotting AI analyzes inventory velocity and turnover to recommend optimal zone placement:

typescript

// Slotting AI reads:
// - inventory_items (current locations)
// - stock_movements (movement frequency)
// → Generates zone recommendations

Warehouse Map Integration

Inventory items are displayed on the warehouse floor plan with zone locations:

typescript

// Floor plan shows:
// - Zone locations
// - Item counts per zone
// - Low stock indicators

Best Practices

1. Always Use Movements

Never update quantity directly. Always use movement functions:

typescript

// ❌ BAD
await supabase
  .from('inventory_items')
  .update({ quantity: newQuantity })
  .eq('id', itemId);

// ✅ GOOD
await inbound({ item_id: itemId, qty: difference });

2. Provide Reasons

Always include reasons for movements, especially adjustments:

typescript

await adjustStock(itemId, 10, 'Stock count correction - found 10 extra units');

3. Check Low Stock

Regularly check for low stock items:

typescript

const lowStockItems = await getLowStockItems();
if (lowStockItems.length > 0) {
  // Send alerts, trigger reordering, etc.
}

4. Use Zone Organization

Assign items to zones for better organization:

typescript

await createItem({
  sku: 'ITEM-001',
  name: 'Product',
  zone_id: 'fast-moving-zone-uuid', // Assign to appropriate zone
});

Troubleshooting

Issue: Quantity Mismatch

Symptom: Item quantity doesn't match sum of movements.

Solution:

Check all movements for the item
Verify no direct quantity updates
Use adjustment movement to correct

Issue: Low Stock Not Alerting

Symptom: Items below min_quantity not showing alerts.

Solution:

Verify min_quantity is set correctly
Check index exists: idx_inventory_items_low_stock
Refresh UI cache

Issue: Movement Fails with Negative Quantity

Symptom: Outbound movement fails when quantity would go negative.

Solution: This is expected behavior. Check:

Current quantity is sufficient
Consider using adjustment if correction needed
Verify item_id is correct

Pick & Pack Module - Uses inventory for picking
Slotting AI Module - Analyzes inventory for optimization
Warehouse Map Module - Visualizes inventory locations
Multi-Tenant Architecture - Tenant isolation

Inventory / Stock Management Module ​

Overview ​

Features ​

Core Capabilities ​

Architecture ​

Database Schema ​

inventory_items Table ​

stock_movements Table ​

API Functions ​

Create Item ​

Adjust Stock ​

Inbound Movement ​

Outbound Movement ​

Transfer Movement ​

Get Item with History ​

Get Low Stock Items ​

Data Flow ​

Permissions ​

Integration Points ​

Pick & Pack Integration ​

Slotting AI Integration ​

Warehouse Map Integration ​

Best Practices ​

1. Always Use Movements ​

2. Provide Reasons ​

3. Check Low Stock ​

4. Use Zone Organization ​

Troubleshooting ​

Issue: Quantity Mismatch ​

Issue: Low Stock Not Alerting ​

Issue: Movement Fails with Negative Quantity ​

Related Documentation ​

Inventory / Stock Management Module

Overview

Features

Core Capabilities

Architecture

Database Schema

`inventory_items` Table

`stock_movements` Table

API Functions

Create Item

Adjust Stock

Inbound Movement

Outbound Movement

Transfer Movement

Get Item with History

Get Low Stock Items

Data Flow

Permissions

Integration Points

Pick & Pack Integration

Slotting AI Integration

Warehouse Map Integration

Best Practices

1. Always Use Movements

2. Provide Reasons

3. Check Low Stock

4. Use Zone Organization

Troubleshooting

Issue: Quantity Mismatch

Issue: Low Stock Not Alerting

Issue: Movement Fails with Negative Quantity

Related Documentation