🏷️ Dynamic Attributes Feature Guide

Overview

Features Implemented

1. Database Schema

• Added attributes JSONB field to inventory_items table
• Added attributes JSONB field to price_book_items table
• Created GIN indexes for efficient JSONB queries
• Supports unlimited custom attributes per item

2. Backend API Endpoints

Query Inventory by Attributes

GET /api/item-attributes/inventory
Query Parameters:
  - attributes: JSON object of key-value pairs (e.g., {"Color": "Red", "Size": "Large"})
  - matchAll: boolean (true = match ALL attributes, false = match ANY)
  - category: optional category filter
  - isActive: optional active status filter

Query Price Book by Attributes

GET /api/item-attributes/price-book
Query Parameters:
  - attributes: JSON object of key-value pairs
  - matchAll: boolean
  - category: optional category filter
  - itemType: optional item type filter
  - isActive: optional active status filter

Get Available Attributes

GET /api/item-attributes/available-attributes
Query Parameters:
  - type: 'inventory' | 'pricebook' | 'both' (default: 'both')

Returns: Array of unique attribute keys used across items

Get Attribute Values

GET /api/item-attributes/attribute-values
Query Parameters:
  - key: attribute key to get values for
  - type: 'inventory' | 'pricebook' | 'both' (default: 'both')

Returns: Array of unique values for the specified attribute key

3. Frontend Components

AttributesManager Component

• Add/edit/remove custom attributes
• Autocomplete for existing attribute keys
• Autocomplete for common attribute values
• Visual key-value pair management
• Helpful examples for new users

AttributesTestPage

• Test interface for querying items by attributes
• Switch between inventory and price book
• AND/OR logic toggle
• Real-time results display
• Shows available attributes and their values

4. Integration

• ✅ Inventory Item Create/Edit Form
• ✅ Price Book Item Create/Edit Form (ready to add)

Usage Examples

Example 1: Garage Door Parts

{
  "Color": "White",
  "Material": "Steel",
  "Size": "16x7",
  "Insulation": "R-16",
  "Brand": "Clopay"
}

Example 2: Paint Products

{
  "Color": "Eggshell White",
  "Finish": "Matte",
  "Brand": "Sherwin Williams",
  "Coverage": "400 sq ft",
  "Type": "Interior"
}

Example 3: HVAC Filters

{
  "Size": "16x20x1",
  "MERV Rating": "13",
  "Brand": "Filtrete",
  "Type": "Pleated"
}

Testing Instructions

1. Start the Development Server

cd HomeImprovment
npm run dev:web  # Start web frontend
npm run dev:api  # Start API backend (in another terminal)

2. Test Attributes on Inventory Items

1. Navigate to Inventory page
2. Create or edit an inventory item
3. Scroll to "Custom Attributes" section
4. Click "Add Attribute"
5. Enter attribute name (e.g., "Color") and value (e.g., "Red")
6. Click "Add" and save the item

3. Test Attributes Query

1. Navigate to /inventory/attributes-test (test page)
2. Select "Inventory Items" or "Price Book Items"
3. Add attributes to search for
4. Toggle "Match ALL" / "Match ANY"
5. Click "Search"
6. View results in the table

4. Test API Directly

# Get all inventory items with Color=Red
curl -H "Authorization: Bearer YOUR_TOKEN" \
  "http://localhost:3001/api/item-attributes/inventory?attributes={\"Color\":\"Red\"}"

# Get all price book items with Size=Large OR Material=Steel
curl -H "Authorization: Bearer YOUR_TOKEN" \
  "http://localhost:3001/api/item-attributes/price-book?attributes={\"Size\":\"Large\",\"Material\":\"Steel\"}&matchAll=false"

# Get all available attribute keys
curl -H "Authorization: Bearer YOUR_TOKEN" \
  "http://localhost:3001/api/item-attributes/available-attributes?type=inventory"

# Get all values for "Color" attribute
curl -H "Authorization: Bearer YOUR_TOKEN" \
  "http://localhost:3001/api/item-attributes/attribute-values?key=Color&type=inventory"

Common Use Cases

1. Product Variants

• Track different colors, sizes, or models of the same product
• Example: "Garage Door - 16x7 White" vs "Garage Door - 16x7 Brown"

2. Filtering & Search

• Find all items by brand
• Find all items with specific dimensions
• Find all items with certain certifications

3. Inventory Organization

• Group items by manufacturer
• Organize by material type
• Categorize by application

4. Reporting

• Generate reports by attribute
• Track inventory by custom categories
• Analyze sales by product characteristics

Performance Notes

• JSONB fields are indexed with GIN indexes for fast queries
• Queries support both exact matches and partial matches
• AND/OR logic allows flexible filtering
• Autocomplete reduces typing and ensures consistency

Future Enhancements

• Attribute templates by category
• Bulk attribute assignment
• Attribute-based pricing rules
• Export/import attributes
• Attribute validation rules
• Required vs optional attributes
• Attribute groups/categories

API Response Examples

Query Inventory Response

{
  "success": true,
  "data": [
    {
      "id": "item-123",
      "name": "Garage Door Panel",
      "sku": "GD-PANEL-001",
      "category": "Doors",
      "currentStock": 15,
      "attributes": {
        "Color": "White",
        "Size": "16x7",
        "Material": "Steel"
      },
      "priceBookItem": {
        "id": "pb-456",
        "name": "Garage Door Panel",
        "unitPrice": 299.99
      }
    }
  ],
  "count": 1
}

Available Attributes Response

{
  "success": true,
  "data": [
    "Color",
    "Size",
    "Material",
    "Brand",
    "Model"
  ]
}

Attribute Values Response

{
  "success": true,
  "data": [
    "White",
    "Brown",
    "Black",
    "Tan"
  ]
}

Troubleshooting

Attributes not saving

• Check that the attributes field is included in the form data
• Verify the API request includes the attributes in the body
• Check browser console for errors

Query returns no results

• Verify the attribute keys match exactly (case-sensitive)
• Try using matchAll=false for broader results
• Check that items actually have the attributes you're searching for

Autocomplete not working

• Ensure items with attributes exist in the database
• Check that the API endpoints are accessible
• Verify the Redux store is properly configured

Support

1. Check the browser console for errors
2. Check the API logs for backend errors
3. Verify database migrations ran successfully
4. Test with the AttributesTestPage first