# Inventory Sync Issues - Root Cause Analysis

## Problem Identified

### Data Discrepancy
- **Product:** Summer Dress (ID: 3)
- **Inventory Table:** Shows quantity = 120 (variant_id = NULL) and 100 (variant_id = 1)
- **InventoryItems Table:** Shows 220 total items (120 with variant_id=NULL, 100 with variant_id=1)
- **Status:** All 220 items are IN_STOCK

### Critical Issues

1. **ACID Violations:**
   - `Inventory.quantity` is manually incremented/decremented
   - No atomic constraint ensuring `Inventory.quantity = COUNT(InventoryItems WHERE status = 'IN_STOCK')`
   - If transaction fails partway, the two systems can drift apart
   - No database triggers or constraints enforcing consistency

2. **Normalization Violations:**
   - `Inventory.quantity` is redundant - it's a computed value
   - Storing computed data violates 3NF (Third Normal Form)
   - Should be calculated on-the-fly or via a VIEW

3. **Missing Constraints:**
   - No CHECK constraint: `quantity = (SELECT COUNT(*) FROM inventory_items WHERE ...)`
   - No trigger to auto-update quantity when InventoryItem status changes
   - No validation before allowing quantity updates

4. **Transaction Issues:**
   - InventoryItems created but quantity not incremented (or vice versa)
   - Status changes not reflected in quantity
   - Sales can mark items SOLD without updating quantity

## Root Causes

### 1. Dual Inventory Model Without Enforcement
The system has two inventory layers but no mechanism ensuring they stay in sync:
- Layer 1: `Inventory.quantity` (quantity-based)
- Layer 2: `InventoryItem` count with status IN_STOCK (UID-based)

**Issue:** These should always match but there's no database-level enforcement.

### 2. Manual Quantity Updates
Every code path that modifies inventory must:
1. Create/update InventoryItem
2. Update Inventory.quantity
3. Create InventoryMovement

**Issue:** If any step fails or is missed, sync is broken.

### 3. No Computed Columns or Views
MySQL doesn't support computed columns in the same way, but we could:
- Use a VIEW to calculate quantity
- Use triggers to maintain quantity
- Remove quantity column and calculate on-the-fly

**Issue:** We're storing redundant data that can become stale.

### 4. Missing Status Change Handlers
When InventoryItem status changes:
- IN_STOCK → RESERVED: Should decrement quantity? (No, still in inventory)
- IN_STOCK → SOLD: Should decrement quantity (Yes!)
- RESERVED → SOLD: Should decrement quantity (Yes!)
- SOLD → RETURNED: Should increment quantity (Yes!)

**Issue:** Not all status changes update quantity correctly.

## Proposed Solutions

### Solution 1: Remove Quantity Column (Recommended)
- Remove `Inventory.quantity` column
- Calculate quantity on-the-fly: `COUNT(InventoryItems WHERE status = 'IN_STOCK')`
- Use database VIEW for reports
- Add index on `(product_id, variant_id, status)` for performance

**Pros:**
- Always accurate (no sync issues)
- Normalized (no redundancy)
- ACID compliant (no dual updates)

**Cons:**
- Performance impact (needs COUNT query)
- Requires code changes

### Solution 2: Database Triggers (Quick Fix)
- Create triggers to auto-update `Inventory.quantity` when:
  - InventoryItem created/deleted
  - InventoryItem status changes
- Add CHECK constraint (MySQL 8.0.16+)

**Pros:**
- Keeps existing structure
- Automatic sync
- Minimal code changes

**Cons:**
- Still redundant data
- Triggers can be complex
- Harder to debug

### Solution 3: Computed Column via VIEW (Hybrid)
- Keep `Inventory` table for metadata (reorder_level, etc.)
- Remove `quantity` from table
- Create VIEW: `inventory_with_quantity` that joins and calculates
- Use VIEW for all reads, table for writes

**Pros:**
- Best of both worlds
- No redundancy
- Performance with proper indexes

**Cons:**
- More complex queries
- Requires code refactoring

### Solution 4: Sync Service (Application-Level Fix)
- Create a sync service that:
  - Periodically recalculates quantity from InventoryItems
  - Fixes discrepancies
  - Runs as background job

**Pros:**
- No schema changes
- Can fix existing data

**Cons:**
- Not real-time (eventual consistency)
- Doesn't prevent issues, only fixes them
- Band-aid solution

## Recommended Approach

**Immediate Fix (Solution 2 + 4):**
1. Create database triggers to auto-sync quantity
2. Create sync service to fix existing discrepancies
3. Add validation to prevent quantity updates outside triggers

**Long-term Fix (Solution 1 or 3):**
1. Migrate to computed quantity (VIEW or on-the-fly calculation)
2. Remove quantity column from Inventory table
3. Update all code to use computed quantity

## Implementation Priority

1. **P0 (Critical):** Sync service to fix current discrepancies
2. **P1 (High):** Database triggers to prevent future issues
3. **P2 (Medium):** Refactor to computed quantity
4. **P3 (Low):** Remove quantity column entirely

