Inventory & Warehouse Management

Dokumentasi lengkap sistem inventory dan warehouse management di MStore Backend dengan support multi-warehouse, batch tracking, dan real-time stock movement.

🎯 Overview

Sistem inventory MStore dirancang dengan prinsip:

✅ Multi-Warehouse: Support distribution center & warehouse per branch
✅ Merchant-Scoped: Isolasi data per merchant
✅ Movement-Based: Stock derived dari inventory movements
✅ Batch Tracking: FIFO/FEFO untuk lot & expiry management
✅ Reserved Quantity: Alokasi untuk sales order
✅ Real-time Sync: Automatic stock update dari transactions
✅ Transfer Management: Inter-warehouse transfer dengan approval

🏗️ Architecture

📊 Data Model

1. Inventory Master

CREATE TABLE inventory (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    merchant_id BIGINT UNSIGNED NOT NULL,
    merchant_code VARCHAR(64),
    inventory_sku VARCHAR(255) NOT NULL,
    inventory_name VARCHAR(255) NOT NULL,
    inventory_type VARCHAR(32),  -- raw_material, finished_good, service
    category_id BIGINT UNSIGNED,
    unit VARCHAR(50) NOT NULL,
    cost DECIMAL(18,2),
    currency VARCHAR(10) DEFAULT 'IDR',
    barcode VARCHAR(64),
    description TEXT,
    active TINYINT DEFAULT 1,
    img_url VARCHAR(255),
    
    UNIQUE KEY uq_merchant_sku (merchant_id, inventory_sku),
    INDEX idx_merchant (merchant_id),
    INDEX idx_category (category_id)
);

Key Points:

❌ Kolom stock DIHAPUS - stok dihitung dari warehouse_stocks
✅ Merchant-scoped - unique SKU per merchant
✅ Multi-type - raw material, finished good, service

2. Warehouses

CREATE TABLE warehouses (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    merchant_id BIGINT UNSIGNED NOT NULL,
    warehouse_code VARCHAR(64) NOT NULL,
    warehouse_name VARCHAR(255) NOT NULL,
    warehouse_type ENUM('distribution_center', 'warehouse') NOT NULL,
    branch_id BIGINT UNSIGNED,
    address TEXT,
    is_active TINYINT DEFAULT 1,
    
    UNIQUE KEY uq_merchant_warehouse (merchant_id, warehouse_code),
    INDEX idx_merchant (merchant_id),
    INDEX idx_branch (branch_id)
);

Warehouse Types:

Distribution Center (DC): Pusat distribusi, supply ke warehouse lain
Warehouse: Gudang per branch untuk operasional

3. Warehouse Stocks

CREATE TABLE warehouse_stocks (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    merchant_id BIGINT UNSIGNED NOT NULL,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    inventory_id BIGINT UNSIGNED NOT NULL,
    inventory_batch_id BIGINT UNSIGNED NULL,
    qty_on_hand DECIMAL(18,3) DEFAULT 0,
    qty_reserved DECIMAL(18,3) DEFAULT 0,
    unit VARCHAR(50) NOT NULL,
    
    UNIQUE KEY uq_warehouse_inventory_batch (
        warehouse_id, 
        inventory_id, 
        COALESCE(inventory_batch_id, 0)
    ),
    INDEX idx_merchant (merchant_id),
    INDEX idx_warehouse (warehouse_id),
    INDEX idx_inventory (inventory_id)
);

Key Concepts:

qty_on_hand: Stok fisik tersedia
qty_reserved: Stok dialokasikan (SO pick/packing)
Available Stock = qty_on_hand - qty_reserved

4. Inventory Movements

CREATE TABLE inventory_movements (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    merchant_id BIGINT UNSIGNED NOT NULL,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    inventory_id BIGINT UNSIGNED NOT NULL,
    inventory_batch_id BIGINT UNSIGNED NULL,
    movement_type ENUM(
        'purchase_receipt',    -- IN: Penerimaan pembelian
        'sales_issue',         -- OUT: Pengeluaran penjualan
        'transfer_out',        -- OUT: Transfer keluar
        'transfer_in',         -- IN: Transfer masuk
        'production_issue',    -- OUT: Bahan baku produksi
        'production_receipt',  -- IN: Hasil produksi
        'return_in',           -- IN: Retur masuk
        'return_out',          -- OUT: Retur keluar
        'adjustment',          -- IN/OUT: Penyesuaian manual
        'stock_opname_adj'     -- IN/OUT: Adjustment stock opname
    ) NOT NULL,
    quantity DECIMAL(18,3) NOT NULL,  -- Positif=IN, Negatif=OUT
    unit VARCHAR(50) NOT NULL,
    reference_type VARCHAR(50),  -- transaction, purchase_order, transfer, etc
    reference_id BIGINT UNSIGNED,
    reference_code VARCHAR(255),
    notes TEXT,
    movement_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_merchant_warehouse (merchant_id, warehouse_id, inventory_id),
    INDEX idx_reference (reference_type, reference_id),
    INDEX idx_movement_date (movement_date)
);

Movement Rules:

Positif (+): Stock IN (purchase, transfer in, production receipt)
Negatif (-): Stock OUT (sales, transfer out, production issue)
Atomic: Setiap movement WAJIB update warehouse_stocks

5. Inventory Batches

CREATE TABLE inventory_batches (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    inventory_id BIGINT UNSIGNED NOT NULL,
    batch_code VARCHAR(64) NOT NULL,
    lot_number VARCHAR(64),
    manufacture_date DATE,
    expiry_date DATE,
    is_active TINYINT DEFAULT 1,
    
    UNIQUE KEY uq_warehouse_inventory_batch (
        warehouse_id, 
        inventory_id, 
        batch_code
    ),
    INDEX idx_expiry (expiry_date)
);

Batch Policies:

FIFO: First In First Out (default)
FEFO: First Expired First Out (untuk item dengan expiry)

6. Inventory Transfers

CREATE TABLE inventory_transfers (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    merchant_id BIGINT UNSIGNED NOT NULL,
    transfer_code VARCHAR(255) NOT NULL,
    from_warehouse_id BIGINT UNSIGNED NOT NULL,
    to_warehouse_id BIGINT UNSIGNED NOT NULL,
    status ENUM(
        'draft',
        'requested',
        'approved',
        'in_transit',
        'received',
        'cancelled'
    ) DEFAULT 'draft',
    requested_by BIGINT UNSIGNED,
    approved_by BIGINT UNSIGNED,
    shipped_by BIGINT UNSIGNED,
    received_by BIGINT UNSIGNED,
    approval_request_id BIGINT UNSIGNED,
    notes TEXT,
    requested_at DATETIME,
    approved_at DATETIME,
    shipped_at DATETIME,
    received_at DATETIME,
    
    UNIQUE KEY uq_merchant_transfer (merchant_id, transfer_code),
    INDEX idx_merchant_status (merchant_id, status),
    INDEX idx_from_warehouse (from_warehouse_id),
    INDEX idx_to_warehouse (to_warehouse_id),
    
    CONSTRAINT ck_transfers_distinct_warehouses 
        CHECK (from_warehouse_id != to_warehouse_id)
);

CREATE TABLE inventory_transfer_lines (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    transfer_id BIGINT UNSIGNED NOT NULL,
    inventory_id BIGINT UNSIGNED NOT NULL,
    inventory_batch_id BIGINT UNSIGNED NULL,
    quantity_requested DECIMAL(18,3) NOT NULL,
    quantity_shipped DECIMAL(18,3),
    quantity_received DECIMAL(18,3),
    unit VARCHAR(50) NOT NULL,
    notes TEXT,
    
    INDEX idx_transfer (transfer_id),
    INDEX idx_inventory (inventory_id)
);

🔄 Movement Flow

Purchase Receipt (Penerimaan Pembelian)

Code Example:

func (s *inventoryService) ReceivePurchase(ctx context.Context, req ReceivePurchaseRequest) error {
    // 1. Create movement
    movement := InventoryMovement{
        MerchantID:     req.MerchantID,
        WarehouseID:    req.WarehouseID,
        InventoryID:    req.InventoryID,
        MovementType:   "purchase_receipt",
        Quantity:       req.Quantity,  // Positif
        Unit:           req.Unit,
        ReferenceType:  "purchase_order",
        ReferenceID:    req.PurchaseOrderID,
        ReferenceCode:  req.POCode,
    }
    
    // 2. Update warehouse stock (atomic transaction)
    return s.repo.CreateMovementAndUpdateStock(ctx, movement)
}

Sales Issue (Pengeluaran Penjualan)

Code Example:

func (s *inventoryService) IssueSales(ctx context.Context, req IssueSalesRequest) error {
    // 1. Check stock availability
    stock, err := s.repo.GetWarehouseStock(ctx, req.WarehouseID, req.InventoryID, nil)
    if err != nil {
        return err
    }
    
    available := stock.QtyOnHand - stock.QtyReserved
    if available < req.Quantity {
        return errors.New("insufficient stock")
    }
    
    // 2. Create movement (negative quantity)
    movement := InventoryMovement{
        MerchantID:     req.MerchantID,
        WarehouseID:    req.WarehouseID,
        InventoryID:    req.InventoryID,
        MovementType:   "sales_issue",
        Quantity:       -req.Quantity,  // Negatif
        Unit:           req.Unit,
        ReferenceType:  "transaction",
        ReferenceID:    req.TransactionID,
        ReferenceCode:  req.TransactionCode,
    }
    
    // 3. Update stock (atomic)
    return s.repo.CreateMovementAndUpdateStock(ctx, movement)
}

Inter-Warehouse Transfer

Transfer Flow:

Draft → Requested: User membuat transfer request
Requested → Approved: Manager/Admin approve
Approved → In Transit:
- Create movement OUT (transfer_out, qty negatif) di source warehouse
- Reduce qty_on_hand di source
In Transit → Received:
- Create movement IN (transfer_in, qty positif) di destination warehouse
- Increase qty_on_hand di destination

Code Example:

func (s *inventoryService) ShipTransfer(ctx context.Context, transferID uint64) error {
    // 1. Get transfer with lines
    transfer, err := s.repo.GetTransferWithLines(ctx, transferID)
    if err != nil {
        return err
    }
    
    if transfer.Status != "approved" {
        return errors.New("transfer not approved")
    }
    
    // 2. Begin transaction
    return s.repo.Transaction(ctx, func(tx *gorm.DB) error {
        // 3. For each line, create movement OUT
        for _, line := range transfer.Lines {
            movement := InventoryMovement{
                MerchantID:     transfer.MerchantID,
                WarehouseID:    transfer.FromWarehouseID,
                InventoryID:    line.InventoryID,
                InventoryBatchID: line.InventoryBatchID,
                MovementType:   "transfer_out",
                Quantity:       -line.QuantityShipped,  // Negatif
                Unit:           line.Unit,
                ReferenceType:  "inventory_transfer",
                ReferenceID:    transfer.ID,
                ReferenceCode:  transfer.TransferCode,
            }
            
            if err := s.repo.CreateMovementAndUpdateStock(ctx, movement); err != nil {
                return err
            }
        }
        
        // 4. Update transfer status
        return s.repo.UpdateTransferStatus(ctx, transferID, "in_transit")
    })
}

func (s *inventoryService) ReceiveTransfer(ctx context.Context, transferID uint64) error {
    transfer, err := s.repo.GetTransferWithLines(ctx, transferID)
    if err != nil {
        return err
    }
    
    if transfer.Status != "in_transit" {
        return errors.New("transfer not in transit")
    }
    
    return s.repo.Transaction(ctx, func(tx *gorm.DB) error {
        for _, line := range transfer.Lines {
            movement := InventoryMovement{
                MerchantID:     transfer.MerchantID,
                WarehouseID:    transfer.ToWarehouseID,
                InventoryID:    line.InventoryID,
                InventoryBatchID: line.InventoryBatchID,
                MovementType:   "transfer_in",
                Quantity:       line.QuantityReceived,  // Positif
                Unit:           line.Unit,
                ReferenceType:  "inventory_transfer",
                ReferenceID:    transfer.ID,
                ReferenceCode:  transfer.TransferCode,
            }
            
            if err := s.repo.CreateMovementAndUpdateStock(ctx, movement); err != nil {
                return err
            }
        }
        
        return s.repo.UpdateTransferStatus(ctx, transferID, "received")
    })
}

📦 Batch Management (FIFO/FEFO)

FIFO Selection

func (s *inventoryService) SelectBatchFIFO(ctx context.Context, warehouseID, inventoryID uint64, qtyNeeded float64) ([]BatchAllocation, error) {
    // Get batches ordered by creation date (FIFO)
    batches, err := s.repo.GetWarehouseStocksByInventory(ctx, warehouseID, inventoryID)
    if err != nil {
        return nil, err
    }
    
    var allocations []BatchAllocation
    remaining := qtyNeeded
    
    for _, batch := range batches {
        available := batch.QtyOnHand - batch.QtyReserved
        if available <= 0 {
            continue
        }
        
        allocQty := math.Min(available, remaining)
        allocations = append(allocations, BatchAllocation{
            BatchID:  batch.InventoryBatchID,
            Quantity: allocQty,
        })
        
        remaining -= allocQty
        if remaining <= 0 {
            break
        }
    }
    
    if remaining > 0 {
        return nil, errors.New("insufficient stock across all batches")
    }
    
    return allocations, nil
}

FEFO Selection (First Expired First Out)

func (s *inventoryService) SelectBatchFEFO(ctx context.Context, warehouseID, inventoryID uint64, qtyNeeded float64) ([]BatchAllocation, error) {
    // Get batches ordered by expiry date (FEFO)
    batches, err := s.repo.GetWarehouseStocksByInventoryOrderByExpiry(ctx, warehouseID, inventoryID)
    if err != nil {
        return nil, err
    }
    
    // Same allocation logic as FIFO
    // ...
}

📊 Stock Queries

Get Available Stock per Warehouse

SELECT 
    w.warehouse_code,
    w.warehouse_name,
    i.inventory_sku,
    i.inventory_name,
    ws.qty_on_hand,
    ws.qty_reserved,
    (ws.qty_on_hand - ws.qty_reserved) AS qty_available,
    ws.unit
FROM warehouse_stocks ws
JOIN warehouses w ON ws.warehouse_id = w.id
JOIN inventory i ON ws.inventory_id = i.id
WHERE ws.merchant_id = ?
  AND ws.inventory_id = ?
  AND ws.deleted_at IS NULL;

Get Stock per Branch (Aggregate)

SELECT 
    b.branch_code,
    b.branch_name,
    i.inventory_sku,
    i.inventory_name,
    SUM(ws.qty_on_hand) AS total_on_hand,
    SUM(ws.qty_reserved) AS total_reserved,
    SUM(ws.qty_on_hand - ws.qty_reserved) AS total_available
FROM warehouse_stocks ws
JOIN warehouses w ON ws.warehouse_id = w.id
JOIN branches b ON w.branch_id = b.id
JOIN inventory i ON ws.inventory_id = i.id
WHERE ws.merchant_id = ?
  AND b.branch_id = ?
  AND ws.deleted_at IS NULL
GROUP BY b.branch_code, b.branch_name, i.inventory_sku, i.inventory_name;

Movement History

SELECT 
    im.movement_date,
    im.movement_type,
    im.quantity,
    im.unit,
    im.reference_type,
    im.reference_code,
    w.warehouse_name,
    i.inventory_name,
    ib.batch_code
FROM inventory_movements im
JOIN warehouses w ON im.warehouse_id = w.id
JOIN inventory i ON im.inventory_id = i.id
LEFT JOIN inventory_batches ib ON im.inventory_batch_id = ib.id
WHERE im.merchant_id = ?
  AND im.inventory_id = ?
ORDER BY im.movement_date DESC
LIMIT 100;

🧪 Testing Scenarios

Scenario 1: Purchase Receipt

func TestPurchaseReceipt(t *testing.T) {
    // 1. Initial stock = 0
    stock := getWarehouseStock(warehouseID, inventoryID)
    assert.Equal(t, 0.0, stock.QtyOnHand)
    
    // 2. Receive purchase (qty +10)
    err := service.ReceivePurchase(ctx, ReceivePurchaseRequest{
        WarehouseID: warehouseID,
        InventoryID: inventoryID,
        Quantity:    10.0,
        Unit:        "PCS",
    })
    assert.NoError(t, err)
    
    // 3. Verify stock updated
    stock = getWarehouseStock(warehouseID, inventoryID)
    assert.Equal(t, 10.0, stock.QtyOnHand)
    
    // 4. Verify movement created
    movements := getMovements(inventoryID)
    assert.Equal(t, 1, len(movements))
    assert.Equal(t, "purchase_receipt", movements[0].MovementType)
    assert.Equal(t, 10.0, movements[0].Quantity)
}

Scenario 2: Sales Issue with Insufficient Stock

func TestSalesIssueInsufficientStock(t *testing.T) {
    // 1. Stock = 5
    setupStock(warehouseID, inventoryID, 5.0)
    
    // 2. Try to issue 10 (should fail)
    err := service.IssueSales(ctx, IssueSalesRequest{
        WarehouseID: warehouseID,
        InventoryID: inventoryID,
        Quantity:    10.0,
    })
    
    assert.Error(t, err)
    assert.Contains(t, err.Error(), "insufficient stock")
    
    // 3. Stock should remain unchanged
    stock := getWarehouseStock(warehouseID, inventoryID)
    assert.Equal(t, 5.0, stock.QtyOnHand)
}

Scenario 3: Inter-Warehouse Transfer

func TestInterWarehouseTransfer(t *testing.T) {
    // 1. Setup: Warehouse A has 20, Warehouse B has 0
    setupStock(warehouseA, inventoryID, 20.0)
    setupStock(warehouseB, inventoryID, 0.0)
    
    // 2. Create transfer A -> B (qty 10)
    transfer := createTransfer(warehouseA, warehouseB, inventoryID, 10.0)
    
    // 3. Approve transfer
    approveTransfer(transfer.ID)
    
    // 4. Ship transfer
    shipTransfer(transfer.ID)
    
    // Verify: A reduced, B unchanged (in transit)
    stockA := getWarehouseStock(warehouseA, inventoryID)
    stockB := getWarehouseStock(warehouseB, inventoryID)
    assert.Equal(t, 10.0, stockA.QtyOnHand)  // 20 - 10
    assert.Equal(t, 0.0, stockB.QtyOnHand)   // Still 0
    
    // 5. Receive transfer
    receiveTransfer(transfer.ID)
    
    // Verify: B increased
    stockB = getWarehouseStock(warehouseB, inventoryID)
    assert.Equal(t, 10.0, stockB.QtyOnHand)  // 0 + 10
}

💡 Best Practices

DO ✅

Selalu gunakan transaction untuk movement + stock update
Implement FIFO/FEFO untuk batch selection
Check stock availability sebelum issue
Log semua movement dengan reference yang jelas
Use reserved quantity untuk sales order allocation
Validate warehouse type untuk transfer rules
Implement approval flow untuk transfer antar warehouse
Monitor stock level untuk reorder point

DON’T ❌

Jangan update warehouse_stocks tanpa create movement
Jangan skip stock availability check
Jangan allow transfer dari/ke warehouse yang sama
Jangan hardcode warehouse_id
Jangan ignore batch expiry date
Jangan skip transaction untuk atomic operations
Jangan allow negative stock (kecuali policy khusus)

🆘 Troubleshooting

Problem: Stock Mismatch

Symptoms: Stock di UI tidak match dengan fisik Solution:

-- Recalculate stock from movements
SELECT 
    warehouse_id,
    inventory_id,
    SUM(quantity) AS calculated_stock
FROM inventory_movements
WHERE merchant_id = ?
  AND inventory_id = ?
GROUP BY warehouse_id, inventory_id;

-- Compare with warehouse_stocks
SELECT * FROM warehouse_stocks 
WHERE merchant_id = ? AND inventory_id = ?;

-- Adjustment if needed
INSERT INTO inventory_movements (
    merchant_id, warehouse_id, inventory_id,
    movement_type, quantity, unit,
    reference_type, notes
) VALUES (
    ?, ?, ?,
    'adjustment', ?, 'PCS',
    'stock_opname', 'Adjustment from physical count'
);

Problem: Transfer Stuck in In Transit

Symptoms: Transfer tidak bisa di-receive Solution:

# Check transfer status
curl -X GET /api/v1/inventory/transfers/{id}

# Manual receive (admin only)
curl -X POST /api/v1/inventory/transfers/{id}/receive \
  -d '{"received_by": 5}'

Transaction Flow

Integrasi inventory dengan transaksi POS

Approval Flow

Approval untuk transfer & adjustment

Database Schema

ERD lengkap inventory tables

Need Help? Contact backend team atau check GitHub Issues

Business Features

Offline-First Architecture

Real-time Sync

Inventory & Warehouse Management

Inventory & Warehouse Management

🎯 Overview

🏗️ Architecture

📊 Data Model

1. Inventory Master

2. Warehouses

3. Warehouse Stocks

4. Inventory Movements

5. Inventory Batches

6. Inventory Transfers

🔄 Movement Flow

Purchase Receipt (Penerimaan Pembelian)

Sales Issue (Pengeluaran Penjualan)

Inter-Warehouse Transfer

📦 Batch Management (FIFO/FEFO)

FIFO Selection

FEFO Selection (First Expired First Out)

📊 Stock Queries

Get Available Stock per Warehouse

Get Stock per Branch (Aggregate)

Movement History

🧪 Testing Scenarios

Scenario 1: Purchase Receipt

Scenario 2: Sales Issue with Insufficient Stock

Scenario 3: Inter-Warehouse Transfer

💡 Best Practices

DO ✅

DON’T ❌

🆘 Troubleshooting

Problem: Stock Mismatch

Problem: Transfer Stuck in In Transit

Transaction Flow

Approval Flow

Database Schema

​Inventory & Warehouse Management

​🎯 Overview

​🏗️ Architecture

​📊 Data Model

​1. Inventory Master

​2. Warehouses

​3. Warehouse Stocks

​4. Inventory Movements

​5. Inventory Batches

​6. Inventory Transfers

​🔄 Movement Flow

​Purchase Receipt (Penerimaan Pembelian)

​Sales Issue (Pengeluaran Penjualan)

​Inter-Warehouse Transfer

​📦 Batch Management (FIFO/FEFO)

​FIFO Selection

​FEFO Selection (First Expired First Out)

​📊 Stock Queries

​Get Available Stock per Warehouse

​Get Stock per Branch (Aggregate)

​Movement History

​🧪 Testing Scenarios

​Scenario 1: Purchase Receipt

​Scenario 2: Sales Issue with Insufficient Stock

​Scenario 3: Inter-Warehouse Transfer

​💡 Best Practices

​DO ✅

​DON’T ❌

​🆘 Troubleshooting

​Problem: Stock Mismatch

​Problem: Transfer Stuck in In Transit

​📚 Related Documentation

Transaction Flow

Approval Flow

Database Schema

Inventory & Warehouse Management

🎯 Overview

🏗️ Architecture

📊 Data Model

1. Inventory Master

2. Warehouses

3. Warehouse Stocks

4. Inventory Movements

5. Inventory Batches

6. Inventory Transfers

🔄 Movement Flow

Purchase Receipt (Penerimaan Pembelian)

Sales Issue (Pengeluaran Penjualan)

Inter-Warehouse Transfer

📦 Batch Management (FIFO/FEFO)

FIFO Selection

FEFO Selection (First Expired First Out)

📊 Stock Queries

Get Available Stock per Warehouse

Get Stock per Branch (Aggregate)

Movement History

🧪 Testing Scenarios

Scenario 1: Purchase Receipt

Scenario 2: Sales Issue with Insufficient Stock

Scenario 3: Inter-Warehouse Transfer

💡 Best Practices

DO ✅

DON’T ❌

🆘 Troubleshooting

Problem: Stock Mismatch

Problem: Transfer Stuck in In Transit

📚 Related Documentation