Inventory Management

The inventory system tracks stock levels, handles reservations during checkout, creates transaction audit trails, and sends email alerts for low-stock and out-of-stock conditions. All critical operations use Serializable transaction isolation to prevent race conditions.

Architecture

Stock Status

Products have a computed stockStatus field:

Where Available = inventory - stockReserved.

Core Operations

Adjust Inventory

adjustInventory() changes a product's stock level and creates a transaction record:

interface InventoryAdjustment {
  productId: string
  quantity: number    // Positive to add, negative to remove
  type: InventoryTransactionType
  reason?: string
  notes?: string
  orderId?: string
  userId?: string
}

The function validates that:

• New stock is not negative
• New stock does not drop below reserved stock

Reserve Inventory

reserveInventory() holds stock during checkout using Serializable transaction isolation:

export async function reserveInventory(reservation: InventoryReservation) {
  return withSerializableRetry(() =>
    prisma.$transaction(
      async (tx) => {
        // Validate available stock
        // Increment stockReserved
        // Create RESERVATION transaction
      },
      { isolationLevel: 'Serializable' }
    )
  )
}

Reserve Multiple Products

reserveMultipleProducts() atomically reserves stock for all items in a cart within a single Serializable transaction. If any product has insufficient stock, the entire reservation rolls back:

Release Inventory

releaseInventory() reverses a reservation (e.g., order cancellation) by decrementing stockReserved.

Deduct Reserved Inventory

deductReservedInventory() is called when an order is completed. It decreases both stockReserved and actual inventory, creating a SALE transaction record.

Serialization Conflict Retry

All reservation operations retry up to 3 times on Prisma error code P2034 (serialization conflict):

async function withSerializableRetry<T>(fn: () => Promise<T>, maxRetries = 3): Promise<T> {
  for (let attempt = 0; attempt < maxRetries; attempt++) {
    try {
      return await fn()
    } catch (error: any) {
      if (error?.code === 'P2034' && attempt < maxRetries - 1) continue
      throw error
    }
  }
}

Inventory Alerts

The checkAndUpdateAlerts() function runs after every stock change:

Email Notifications

New alerts trigger email notifications to addresses in INVENTORY_ALERT_EMAILS:

const adminEmails = process.env.INVENTORY_ALERT_EMAILS?.split(',') || []

Emails include product name, SKU, current stock level, and a link to the product in the admin panel.

Transaction History

Every stock change creates an InventoryTransaction record with:

• Transaction type (SALE, RESERVATION, RELEASE, ADJUSTMENT, etc.)
• Previous and new stock levels
• Reason and notes
• Associated order ID and user ID

The getInventoryHistory() function retrieves the last 50 transactions for a product.