# Product Architecture Comparison: Catalog vs Sales Products

This document provides a comprehensive review of the current product architecture, comparing the legacy **Catalog Products/Variants** system with the unified **Sales Products** system.

---

## Executive Summary

The system is currently in a **hybrid state** where both the legacy catalog product hierarchy and the new unified sales products model coexist. The migration strategy involves "flattening" the hierarchical catalog structure into individual sales product records.

### Key Differences

| Aspect | Legacy Catalog System | Unified Sales Products |
|--------|----------------------|------------------------|
| **Table Structure** | `catalog_products` → `catalog_product_variants` | `sales_products` (single table) |
| **Hierarchy** | Parent-child product-variant relationship | Flat structure with `section` attribute |
| **Inventory Tracking** | Via `variant_id` | Via `sales_product_id` |
| **Domain Scope** | Catalog domain only | Cross-domain (Accounting, Procurement, Catalog) |
| **Product Sections** | N/A | `accounting`, `procurement`, `catalog` |

---

## Architecture Diagrams

### 1. Legacy Catalog Product System

```mermaid
erDiagram
    CATALOG_PRODUCTS ||--o{ CATALOG_PRODUCT_VARIANTS : "has variants"
    CATALOG_PRODUCTS ||--o{ CATALOG_PRODUCT_ALIASES : "has aliases"
    CATALOG_PRODUCTS ||--o{ CATALOG_PRODUCT_IMAGES : "has images"
    CATALOG_PRODUCTS }o--|| CATALOG_CATEGORY_ITEMS : "belongs to"
    
    CATALOG_PRODUCT_VARIANTS ||--o{ CATALOG_INVENTORIES : "tracks inventory"
    CATALOG_PRODUCT_VARIANTS ||--o{ CATALOG_INVENTORY_TRANSACTIONS : "has transactions"
    CATALOG_PRODUCT_VARIANTS ||--o{ PRODUCT_VARIANT_UNITS : "has units"
    CATALOG_PRODUCT_VARIANTS ||--o{ PRODUCT_VARIANT_MATERIALS : "has materials"
    CATALOG_PRODUCT_VARIANTS ||--o{ INVENTORY_BATCHES : "has batches"
    CATALOG_PRODUCT_VARIANTS ||--o{ INVENTORY_SERIALS : "has serials"
    
    CATALOG_PRODUCTS {
        int id PK
        int catalog_item_id FK
        int parent_id FK "self-reference"
        json canonical_name
        json description
        string brand
        string manufacturer
        enum product_type
        enum status
        int revenue_account_id FK
        int expense_account_id FK
        int inventory_account_id FK
        int cogs_account_id FK
    }
    
    CATALOG_PRODUCT_VARIANTS {
        int id PK
        int product_id FK
        int variant_group_id FK
        string sku
        string barcode
        string origin_country
        enum quality_grade
        string manufacturer_location
        string certification
        string warranty_terms
        boolean is_preferred
        text notes
    }
    
    CATALOG_INVENTORIES {
        int id PK
        int variant_id FK "LEGACY"
        int warehouse_id FK
        int inventory_type_id FK
        decimal quantity
    }
```

### 2. Unified Sales Products System

```mermaid
erDiagram
    SALES_PRODUCTS ||--o{ CATALOG_INVENTORIES : "tracks inventory"
    SALES_PRODUCTS ||--o{ CATALOG_INVENTORY_TRANSACTIONS : "has transactions"
    SALES_PRODUCTS ||--o{ CATALOG_INVENTORY_RESERVATIONS : "has reservations"
    SALES_PRODUCTS ||--o{ PRODUCT_STORES : "stored in"
    SALES_PRODUCTS ||--o{ PURCHASE_PRODUCTS : "purchased as"
    SALES_PRODUCTS ||--o{ SALES_INVOICE_PRODUCTS : "sold as"
    SALES_PRODUCTS ||--o{ PRODUCT_PRICES : "has prices"
    SALES_PRODUCTS ||--o{ CLIENT_PRODUCTS : "client-specific"
    SALES_PRODUCTS }o--|| CATALOG_CATEGORY_ITEMS : "belongs to"
    SALES_PRODUCTS }o--|| CATEGORIES : "categorized by"
    
    SALES_PRODUCTS {
        int id PK
        int company_id FK
        int catalog_item_id FK
        int category_id FK
        string name
        text description
        string code "auto-generated"
        string sku
        string brand
        enum section "accounting|procurement|catalog"
        enum type "product|service"
        enum product_type "physical|spare_part|consumable"
        enum status
        string quality_grade
        string origin_country
        string manufacturer_location
        string certification
        string warranty_terms
        boolean is_preferred
        int purchase_account_id FK
        int purchase_tax_id FK
        int cost_center_id FK
        json purchasing_prices
    }
    
    CATALOG_INVENTORIES {
        int id PK
        int variant_id FK "LEGACY - being phased out"
        int sales_product_id FK "NEW - unified reference"
        int warehouse_id FK
        int inventory_type_id FK
        decimal quantity
    }
    
    CATALOG_INVENTORY_TRANSACTIONS {
        int id PK
        int inventory_id FK
        int sales_product_id FK "NEW"
        string transaction_type
        decimal quantity
        datetime transaction_date
    }
    
    CATALOG_INVENTORY_RESERVATIONS {
        int id PK
        int inventory_serial_id FK
        int sales_product_id FK "NEW"
        decimal quantity
        datetime reserved_until
    }
```

### 3. Migration Flow: Catalog → Sales Products

```mermaid
flowchart TD
    subgraph Legacy["Legacy Catalog System"]
        CP[Catalog Product]
        PV1[Product Variant 1]
        PV2[Product Variant 2]
        PV3[Product Variant 3]
        
        CP --> PV1
        CP --> PV2
        CP --> PV3
    end
    
    subgraph Migration["Migration Process"]
        MAP[catalog_to_sales_product_map]
        FLATTEN[Flatten Hierarchy]
    end
    
    subgraph Unified["Unified Sales Products"]
        SP1["SalesProduct
        section: 'catalog'
        (from CatalogProduct)"]
        SP2["SalesProduct
        section: 'catalog'
        (from Variant 1)"]
        SP3["SalesProduct
        section: 'catalog'
        (from Variant 2)"]
        SP4["SalesProduct
        section: 'catalog'
        (from Variant 3)"]
    end
    
    CP -->|migrate| FLATTEN
    PV1 -->|flatten| FLATTEN
    PV2 -->|flatten| FLATTEN
    PV3 -->|flatten| FLATTEN
    
    FLATTEN --> MAP
    MAP --> SP1
    MAP --> SP2
    MAP --> SP3
    MAP --> SP4
    
    SP2 -.->|updates| INV1[Inventory Record 1]
    SP3 -.->|updates| INV2[Inventory Record 2]
    SP4 -.->|updates| INV3[Inventory Record 3]
    
    style Legacy fill:#ffe6e6
    style Unified fill:#e6f3ff
    style Migration fill:#fff4e6
```

### 4. Section-Based Product Organization

```mermaid
graph TB
    subgraph SalesProducts["sales_products Table"]
        direction TB
        
        subgraph Accounting["section = 'accounting'"]
            ACC1[Retail Products]
            ACC2[Service Items]
            ACC3[Billable Items]
        end
        
        subgraph Procurement["section = 'procurement'"]
            PROC1[Purchasable Products]
            PROC2[Raw Materials]
            PROC3[Spare Parts]
        end
        
        subgraph Catalog["section = 'catalog'"]
            CAT1[Migrated Catalog Products]
            CAT2[Migrated Variants]
            CAT3[Legacy Items]
        end
    end
    
    subgraph Domains["Domain Access"]
        ACCD[Accounting Domain]
        PROCD[Procurement Domain]
        CATD[Catalog Domain]
    end
    
    ACCD -.->|queries| Accounting
    PROCD -.->|queries| Procurement
    CATD -.->|queries| Catalog
    
    style Accounting fill:#d4edda
    style Procurement fill:#cce5ff
    style Catalog fill:#fff3cd
```

### 5. Inventory Tracking: Dual Reference System

```mermaid
graph LR
    subgraph Current["Current Hybrid State"]
        INV[catalog_inventories]
        
        INV -->|variant_id| LEGACY[Legacy Reference]
        INV -->|sales_product_id| NEW[New Reference]
    end
    
    subgraph Legacy_System["Legacy System"]
        LEGACY --> PV[catalog_product_variants]
        PV --> CP[catalog_products]
    end
    
    subgraph Unified_System["Unified System"]
        NEW --> SP[sales_products]
    end
    
    subgraph Migration_Mapping["Migration Tracking"]
        MAP[catalog_to_sales_product_map]
        MAP -->|catalog_variant_id| PV
        MAP -->|sales_product_id| SP
    end
    
    style Current fill:#fff4e6
    style Legacy_System fill:#ffe6e6
    style Unified_System fill:#e6f3ff
    style Migration_Mapping fill:#f0e6ff
```

---

## Detailed Comparison

### Legacy Catalog Products Model

**Table: `catalog_products`**
- Hierarchical structure with parent-child relationships
- Supports product variants through separate `catalog_product_variants` table
- Translatable fields (`canonical_name`, `description`)
- Accounting integration via account mappings
- Domain: **Catalog only**

**Table: `catalog_product_variants`**
- Child records of `catalog_products`
- Contains SKU, barcode, quality grades, certifications
- Direct relationship with inventory tracking
- Each variant is a distinct inventory-trackable item

**Key Characteristics:**
- ✅ Clear hierarchical organization
- ✅ Separation of product definition from variants
- ❌ Limited to Catalog domain
- ❌ Complex queries across product-variant relationships
- ❌ Difficult to share across domains

### Unified Sales Products Model

**Table: `sales_products`**
- Flat structure with all product data in one table
- Uses `section` attribute to differentiate domain usage
- Combines features from both products and variants
- Cross-domain accessibility
- Auto-generated product codes

**Key Characteristics:**
- ✅ Single source of truth across domains
- ✅ Simplified queries and relationships
- ✅ Section-based access control
- ✅ Flexible for multi-domain usage
- ❌ Loss of explicit hierarchical structure
- ❌ Potential data duplication

---

## Migration Strategy

### Phase 1: Schema Alignment ✅ Complete
- Added `sales_product_id` to `catalog_inventories`
- Added `sales_product_id` to `catalog_inventory_transactions`
- Added `sales_product_id` to `catalog_inventory_reservations`
- Created mapping table `catalog_to_sales_product_map`

### Phase 2: Data Migration 🔄 In Progress
- Migrate `catalog_products` → `sales_products` (section: 'catalog')
- Flatten `catalog_product_variants` → individual `sales_products` (section: 'catalog')
- Update inventory references from `variant_id` to `sales_product_id`
- Maintain mapping for rollback capability

### Phase 3: Service Layer Updates 🔄 In Progress
- Update inventory services to use `sales_product_id`
- Update supply domain services
- Update pricing services
- Update allocation services

### Phase 4: Deprecation ⏳ Pending
- Mark legacy models as `@deprecated`
- Remove `variant_id` foreign keys
- Archive legacy tables
- Remove mapping table

---

## Current Hybrid State Risks

> [!WARNING]
> **Dual Reference System**
> 
> The `catalog_inventories` table currently has BOTH `variant_id` and `sales_product_id` columns. This creates potential for:
> - Data inconsistency if both are populated differently
> - Logic errors if services expect one but receive the other
> - Confusion during development

> [!IMPORTANT]
> **Migration Tracking**
> 
> The `catalog_to_sales_product_map` table is critical for:
> - Tracking which catalog products/variants have been migrated
> - Enabling rollback if needed
> - Debugging migration issues
> - Should be maintained until full deprecation

---

## Relationships with Other Domains

### Catalog Domain
- **CategoryItem**: Both systems link to `catalog_category_items`
- **Inventory**: Transitioning from `variant_id` to `sales_product_id`
- **Pricing**: Legacy uses `ClientProductPrice`, new uses `ProductPrice`

### Accounting Domain
- **Sales Products**: Native table owner
- **Purchase Products**: References `sales_products`
- **Product Stores**: References `sales_products`
- **Invoices**: Uses `sales_products` for line items

### Supply Domain
- **Purchase Orders**: Being updated to use `sales_product_id`
- **Goods Receipts**: Being updated to use `sales_product_id`
- **Supplier Catalog**: Links to `catalog_item_id`

---

## Recommendations

### Immediate Actions
1. **Complete Supply Domain Migration**: Ensure all `PurchaseOrderDTO` and `GoodsReceiptDTO` use `sales_product_id`
2. **Update Pricing Services**: Migrate `ClientProductPricingService` to use unified products
3. **Implement Validation**: Add checks to prevent dual-population of `variant_id` and `sales_product_id`

### Medium-term Actions
1. **Deprecate Legacy Models**: Mark `Product` and `ProductVariant` in Catalog domain as deprecated
2. **Update Frontend**: Ensure all UI components use new product endpoints
3. **Data Cleanup**: Verify all inventory records have `sales_product_id` populated

### Long-term Actions
1. **Remove Legacy Tables**: After verification period, drop `catalog_products` and `catalog_product_variants`
2. **Remove Mapping Table**: Once migration is stable and verified
3. **Optimize Queries**: Remove dual-reference checks and simplify inventory queries

---

## Conclusion

The system is transitioning from a hierarchical catalog-specific product model to a unified, flat, cross-domain sales products model. The migration strategy preserves data integrity through mapping tables while enabling gradual service layer updates. The hybrid state introduces complexity but provides a safe migration path with rollback capability.

**Current Status**: 🔄 **In Progress** - Schema aligned, data migration active, service layer partially updated