📦 Product Catalog System

System Overview

Key Features

System Architecture Diagram

Technology Stack

Database Architecture

9 Core Tables

3 Database Views

2 Stored Procedures

Relationships

Brands (1) → (M) Products
Categories (1) → (M) Products (hierarchical, self-referencing)
Products (1) → (M) ProductVariants
Products (1) → (M) ProductImages
ProductVariants (1) → (M) Inventory
InventoryLocations (1) → (M) Inventory
Products (1) → (M) ShopifySyncLog
Products (1) → (M) ProductPricingHistory

Brand-Store Mappings

Each brand has a dedicated Shopify store with unique credentials. Products are filtered by BrandID to ensure proper routing.

Environment Variables Required (Per Brand)

# CellJourney Example
SHOPIFY_CJ_ACCESS_TOKEN=shpat_xxxxxxxxxxxxxxxxxxxxx
SHOPIFY_CJ_STORE_URL=celljourney.myshopify.com
SHOPIFY_CJ_LOCATION_ID=12345678901  # Primary fulfillment location ID

# Repeat for each brand:
SHOPIFY_EY_ACCESS_TOKEN=...
SHOPIFY_QS_ACCESS_TOKEN=...
SHOPIFY_NX_ACCESS_TOKEN=...
SHOPIFY_LF_ACCESS_TOKEN=...
SHOPIFY_SM_ACCESS_TOKEN=...
SHOPIFY_5W_ACCESS_TOKEN=...
SHOPIFY_DT_ACCESS_TOKEN=...

API Reference

Base URL: https://www.drantech.ai/api/products

Authentication: Azure AD tenant authentication required (Authorization header with JWT token)

Product Endpoints

GET /api/products

List all products with optional filters.

GET /api/products?brandId=1&categoryId=5&page=1&pageSize=20

Response:
{
  "products": [
    {
      "productId": 1,
      "sku": "CJ-SUP-1732022400",
      "name": "CellOptimize Pro",
      "basePrice": 49.99,
      "brand": "CellJourney",
      "category": "Supplements",
      "shopifySync": true,
      "shopifyProductId": 7891234567890,
      "variantCount": 3,
      "primaryImageUrl": "https://..."
    }
  ],
  "total": 45,
  "page": 1,
  "pageSize": 20,
  "totalPages": 3
}

POST /api/products

Create new product with optional Shopify sync.

POST /api/products
Content-Type: application/json
Authorization: Bearer {token}

{
  "name": "CellOptimize Pro",
  "description": "Advanced cellular optimization supplement",
  "brandId": 1,
  "categoryId": 5,
  "basePrice": 49.99,
  "tags": ["supplement", "wellness", "cellular"],
  "shopifySync": true,
  "seoTitle": "CellOptimize Pro - Cellular Health Supplement",
  "seoDescription": "Scientifically formulated for optimal cellular health"
}

Response (201 Created):
{
  "success": true,
  "productId": 123,
  "sku": "CJ-SUP-1732022400",
  "shopifyProductId": 7891234567890
}

GET /api/products/{productId}

Get detailed product information including variants and images.

GET /api/products/123

Response:
{
  "productId": 123,
  "sku": "CJ-SUP-1732022400",
  "name": "CellOptimize Pro",
  "description": "...",
  "brand": {
    "id": 1,
    "name": "CellJourney",
    "code": "CJ",
    "shopifyStoreUrl": "celljourney.myshopify.com"
  },
  "category": {
    "id": 5,
    "name": "Supplements"
  },
  "variants": [
    {
      "variantId": 456,
      "sku": "CJ-SUP-1732022400-30CT",
      "name": "30 capsules",
      "price": 49.99,
      "inventoryQuantity": 150
    }
  ],
  "images": [...]
}

PUT /api/products/{productId}

Update product details (partial updates supported).

DELETE /api/products/{productId}

Soft delete product (sets IsActive = 0).

Inventory Endpoints

GET /api/products/variants/{variantId}/inventory

Get inventory levels across all locations.

POST /api/products/variants/{variantId}/inventory/adjust

Adjust inventory quantity.

POST /api/products/variants/456/inventory/adjust

{
  "locationId": 1,
  "adjustment": -5,
  "reason": "Order fulfillment"
}

Response:
{
  "success": true,
  "newQuantity": 145
}

Shopify Sync Endpoints

POST /api/products/{productId}/sync-shopify

Manually trigger Shopify sync.

Analytics Endpoints

GET /api/products/analytics/low-stock

Get products below reorder point.

GET /api/products/analytics/brand-summary

Product count summary by brand.

Helper Endpoints

GET /api/products/brands

List all active brands.

GET /api/products/categories

List all categories (hierarchical).

Shopify Integration

Integration Flow

Shopify API Client Usage

from shopify_sync_service import ShopifyClient

# Initialize client for CellJourney
client = ShopifyClient('CJ')

# Create product
shopify_product_id = client.create_product({
    "title": "CellOptimize Pro",
    "body_html": "Advanced supplement...",
    "vendor": "CellJourney",
    "product_type": "Supplements",
    "tags": ["supplement", "wellness"],
    "variants": [
        {
            "sku": "CJ-SUP-1732022400",
            "price": "49.99",
            "option1": "30 capsules"
        }
    ]
})

# Update product
client.update_product(shopify_product_id, {
    "title": "CellOptimize Pro (Updated)"
})

# Sync inventory
client.update_inventory(
    shopify_inventory_item_id=12345,
    shopify_location_id=67890,
    quantity=150
)

Webhook Configuration

How-To Guides

How to Add a New Product

1. Navigate to Product SKU Entry: Go to /products/product_sku_entry.html
2. Select Brand: Choose the brand from dropdown (e.g., CellJourney)
3. Select Category: Pick category (e.g., Supplements → Cellular Health)
4. Enter Product Details:
   • Product Name
   • Description
   • Base Price
   • Cost Price (optional)
   • Compare At Price (for discounts)
   • Tags (comma-separated)
5. Add Variants (if applicable):
   • Size (e.g., 30 capsules, 60 capsules)
   • Color
   • Material
   • Price per variant
6. Upload Images: Drag-drop product images (auto-uploads to Azure Blob Storage)
7. Set Initial Inventory: Enter quantity for each warehouse location
8. Enable Shopify Sync: Toggle "Sync to Shopify" if you want product in Shopify store immediately
9. Click "Create Product": System will:
   • Auto-generate SKU
   • Save to Nova_StaX_DB
   • Create Shopify product (if sync enabled)
   • Upload images to Azure Blob
   • Log sync attempt to ShopifySyncLog

How to Update Inventory

1. Go to Inventory Dashboard: Access from Product SKU Entry page
2. Find Product/Variant: Search by SKU or product name
3. Select Location: Choose warehouse (East Coast, West Coast, etc.)
4. Enter Adjustment:
   • Positive number = Add inventory (e.g., +100 for new shipment)
   • Negative number = Remove inventory (e.g., -5 for manual order)
5. Add Reason: Document adjustment (e.g., "Received shipment", "Damaged goods")
6. Submit: System will:
   • Update Inventory table
   • Sync to Shopify (if product is synced)
   • Trigger low-stock alert if below reorder point

How to Sync Product to Shopify

1. Automatic Sync (Recommended):
   • Enable "Sync to Shopify" when creating product
   • System automatically creates Shopify product
2. Manual Sync:
   • Navigate to product details
   • Click "Sync to Shopify Now" button
   • View sync status in ShopifySyncLog table
3. API Sync:

   POST /api/products/123/sync-shopify

How to Configure Shopify API Credentials

1. Create Shopify Private App:
   • Log into Shopify admin (e.g., celljourney.myshopify.com/admin)
   • Go to Settings → Apps and sales channels → Develop apps
   • Click "Create an app"
   • Name: "DranTechAI Product Sync"
   • Configure Admin API scopes:
     • read_products, write_products
     • read_inventory, write_inventory
     • read_orders
   • Install app and copy Admin API access token
2. Store in Azure Key Vault:
   • Open Azure Portal → Key Vault (qi3ci-keyvault)
   • Secrets → Generate/Import
   • Name: SHOPIFY-CJ-ACCESS-TOKEN
   • Value: shpat_xxxxxxxxxxxxx
   • Repeat for each brand
3. Configure App Service:
   • Azure Portal → App Service (cell-journet or Dranschak-East2-App)
   • Configuration → Application settings
   • Add references:

     SHOPIFY_CJ_ACCESS_TOKEN = @Microsoft.KeyVault(SecretUri=https://qi3ci-keyvault.vault.azure.net/secrets/SHOPIFY-CJ-ACCESS-TOKEN)

Troubleshooting

Common Issues

❌ "Product failed to sync to Shopify"

Symptoms: Product created in database but not appearing in Shopify store.

Diagnosis:

1. Check ShopifySyncLog table:

   SELECT TOP 10 * FROM ShopifySyncLog
   WHERE SyncStatus = 'Failed'
   ORDER BY SyncedAt DESC;

2. Review ErrorMessage column for API errors

Solutions:

• 401 Unauthorized: Check Shopify access token in Key Vault
• 403 Forbidden: Verify API scopes (read_products, write_products)
• 422 Unprocessable Entity: Check required fields (title, variants)
• Timeout: Network issue - retry sync manually

❌ "SKU already exists"

Cause: Duplicate SKU in Products table.

Solution:

1. Check existing SKUs:

   SELECT SKU, ProductName FROM Products WHERE SKU = 'CJ-SUP-1732022400';

2. Modify SKU or update existing product instead of creating new one

❌ "Inventory not syncing to Shopify"

Diagnosis:

1. Verify variant has ShopifyVariantID:

   SELECT VariantSKU, ShopifyVariantID FROM ProductVariants WHERE VariantID = 456;

2. Check Shopify location ID configured:

   echo $env:SHOPIFY_CJ_LOCATION_ID

Solution: Ensure variant is synced to Shopify first, then inventory sync will work.

❌ "Database connection failed"

Symptoms: API returns "Failed to fetch products".

Solution:

1. Check connection string in App Service Configuration:

   AZURE_SQL_CONNECTIONSTRING

2. Verify Azure SQL firewall allows App Service IP
3. Test connection:

   sqlcmd -S dranschak-family-server.database.windows.net -d Nova_StaX_DB -U {user} -P {pass}

❌ "Images not uploading to Azure Blob"

Diagnosis:

1. Check Blob Storage container exists: product-images
2. Verify CORS configured for frontend domain
3. Check connection string in Key Vault: AZURE_STORAGE_CONNECTIONSTRING

Debugging Tools

Deployment Checklist

Pre-Deployment

Deployment Steps

1. Deploy Backend Code:

   cd backend/blueprints/ecomm_flows
   git add product_catalog_api.py shopify_sync_service.py __init__.py
   git commit -m "Add Product Catalog API with Shopify integration"
   git push origin main

2. Configure App Service:
   • Add environment variables from Key Vault
   • Restart app service
3. Register Blueprints:
   • Update main app.py to import register_ecomm_blueprints
   • Call registration function
4. Deploy Frontend:
   • Update Product SKU Entry page
   • Test image upload to Blob Storage
5. Configure Webhooks:
   • For each Shopify store, add webhooks:
     • orders/create → https://www.drantech.ai/webhooks/shopify/orders/create
     • orders/cancelled → https://www.drantech.ai/webhooks/shopify/orders/cancelled
     • products/update → https://www.drantech.ai/webhooks/shopify/products/update

Post-Deployment Verification

1. Test API Endpoints:

   # List brands
   curl https://www.drantech.ai/api/products/brands
   
   # Create test product
   curl -X POST https://www.drantech.ai/api/products \
     -H "Authorization: Bearer {token}" \
     -H "Content-Type: application/json" \
     -d '{"name": "Test Product", "brandId": 1, "categoryId": 5, "basePrice": 19.99}'

2. Verify Shopify Sync:
   • Check Shopify admin for test product
   • Verify ShopifySyncLog for successful sync
3. Test Inventory Update:
   • Adjust inventory via API
   • Verify Shopify inventory updated
4. Test Webhooks:
   • Place test order in Shopify
   • Verify inventory reduced in database

Additional Resources

• Implementation Plan: apps/production_ready/drantechai_developer_flaskapp/docs/PRODUCT_CATALOG_DATABASE_PLAN.md
• Database Schema: apps/production_ready/drantechai_developer_flaskapp/database/schemas/product_catalog_schema.sql
• API Code: backend/blueprints/ecomm_flows/product_catalog_api.py
• Shopify Service: backend/blueprints/ecomm_flows/shopify_sync_service.py
• Shopify API Docs: Shopify Admin REST API
• Azure SQL Docs: Azure SQL Documentation