Barcode & Pesticide Identification Implementation Plan

Document Version: 1.0 Created: 2026-01-25 Status: Draft

Overview

This document outlines the implementation plan for two related enhancements:

1. Barcode Field Improvements - Make barcode optional, add source tracking, and introduce internal SKU field
2. Pesticide Identification - Auto-detect pesticides from label images and enable EPA label search

---

Problem Statement

Barcode Issues

• Current UI requires barcode as a mandatory field
• Many products (bulk chemicals, lab reagents, imported products) don't have barcodes
• Users are forced to enter dummy/fake barcodes, polluting the product catalog
• No distinction between scanned barcodes (reliable) vs manually entered (potentially incorrect)
• No field for company's internal product reference codes

Pesticide Identification Issues

• No way to identify if a product is a pesticide
• EPA label search not triggered for pesticide products
• Signal words (DANGER/WARNING/CAUTION) from labels not captured
• EPA Registration Numbers not extracted from label images

---

Solution Design

1. Schema Changes

1.1 New Fields for chemiq_company_product_catalog

Field	Type	Nullable	Default	Purpose
barcode_source	VARCHAR(20)	Yes	NULL	How barcode was obtained
internal_sku	VARCHAR(100)	Yes	NULL	Company's internal product code
is_pesticide	BOOLEAN	No	FALSE	Flag for pesticide products
epa_registration_number	VARCHAR(50)	Yes	NULL	EPA Reg. No. (e.g., 1234-567)
signal_word	VARCHAR(20)	Yes	NULL	DANGER/WARNING/CAUTION

1.2 Barcode Source Values

Value	Description	Trust Level
scanned	Barcode physically scanned via device camera	High
api_validated	Validated via external barcode API (GS1, UPC Database)	High
label_extracted	Extracted from label image via Vision LLM	Medium
manual	User typed the barcode manually	Low
NULL	No barcode provided	N/A

1.3 Product Matching Logic Changes

Barcode Source	Use for Global Catalog Matching	Use for Company Matching
scanned	Yes	Yes
api_validated	Yes	Yes
label_extracted	Yes (with lower confidence)	Yes
manual	No	Yes
NULL	N/A	N/A

---

2. Backend Changes

2.1 Database Migration

File: tellus-ehs-hazcom-service/alembic/versions/XXXX_add_barcode_pesticide_fields.py

"""Add barcode source, internal SKU, and pesticide fields

Revision ID: XXXX
Revises: [previous_revision]
Create Date: 2026-01-25
"""

from alembic import op
import sqlalchemy as sa

def upgrade():
    # Add new columns to chemiq_company_product_catalog
    op.add_column('chemiq_company_product_catalog',
        sa.Column('barcode_source', sa.String(20), nullable=True))
    op.add_column('chemiq_company_product_catalog',
        sa.Column('internal_sku', sa.String(100), nullable=True))
    op.add_column('chemiq_company_product_catalog',
        sa.Column('is_pesticide', sa.Boolean(), nullable=False, server_default='false'))
    op.add_column('chemiq_company_product_catalog',
        sa.Column('epa_registration_number', sa.String(50), nullable=True))
    op.add_column('chemiq_company_product_catalog',
        sa.Column('signal_word', sa.String(20), nullable=True))

    # Create indexes
    op.create_index('idx_company_product_catalog_internal_sku',
        'chemiq_company_product_catalog', ['internal_sku'],
        postgresql_where=sa.text('internal_sku IS NOT NULL'))
    op.create_index('idx_company_product_catalog_epa_reg',
        'chemiq_company_product_catalog', ['epa_registration_number'],
        postgresql_where=sa.text('epa_registration_number IS NOT NULL'))
    op.create_index('idx_company_product_catalog_is_pesticide',
        'chemiq_company_product_catalog', ['is_pesticide'],
        postgresql_where=sa.text('is_pesticide = true'))

def downgrade():
    op.drop_index('idx_company_product_catalog_is_pesticide')
    op.drop_index('idx_company_product_catalog_epa_reg')
    op.drop_index('idx_company_product_catalog_internal_sku')
    op.drop_column('chemiq_company_product_catalog', 'signal_word')
    op.drop_column('chemiq_company_product_catalog', 'epa_registration_number')
    op.drop_column('chemiq_company_product_catalog', 'is_pesticide')
    op.drop_column('chemiq_company_product_catalog', 'internal_sku')
    op.drop_column('chemiq_company_product_catalog', 'barcode_source')

2.2 Model Changes

File: tellus-ehs-hazcom-service/app/db/models/chemiq_product_catalog.py

Add to CompanyProductCatalog class:

# Barcode source tracking
barcode_source = Column(String(20), nullable=True)  # scanned, label_extracted, manual, api_validated

# Internal reference (company-specific, not UPC)
internal_sku = Column(String(100), nullable=True, index=True)

# Pesticide identification
is_pesticide = Column(Boolean, nullable=False, default=False)
epa_registration_number = Column(String(50), nullable=True, index=True)
signal_word = Column(String(20), nullable=True)  # DANGER, WARNING, CAUTION

2.3 Schema Changes

File: tellus-ehs-hazcom-service/app/schemas/chemiq/inventory.py

Update ChemicalInventoryCreate:

class ChemicalInventoryCreate(BaseModel):
    site_id: UUID
    product_name: str
    manufacturer: str
    product_code: Optional[str] = None
    cas_number: Optional[str] = None
    barcode_upc: Optional[str] = None  # Now optional
    barcode_source: Optional[str] = None  # NEW: scanned, label_extracted, manual, api_validated
    internal_sku: Optional[str] = None  # NEW: Company's internal product code
    container_size: float
    size_unit: str = "L"
    quantity: int = 1
    location_id: Optional[UUID] = None

    # Pesticide fields (NEW)
    is_pesticide: Optional[bool] = False
    epa_registration_number: Optional[str] = None
    signal_word: Optional[str] = None  # DANGER, WARNING, CAUTION

    # Data source tracking (existing)
    data_source: Optional[str] = None
    extraction_confidence: Optional[float] = None

2.4 Service Changes

File: tellus-ehs-hazcom-service/app/services/chemiq/chemiq_service.py

Update create_chemical() to include new fields:

# In create_chemical() method, update CompanyProductCatalog creation:
company_product = CompanyProductCatalog(
    company_id=company_id,
    product_name=chemical_data.product_name,
    manufacturer=chemical_data.manufacturer,
    product_code=chemical_data.product_code,
    cas_number=chemical_data.cas_number,
    barcode_upc=chemical_data.barcode_upc,
    barcode_source=getattr(chemical_data, 'barcode_source', None),  # NEW
    internal_sku=getattr(chemical_data, 'internal_sku', None),  # NEW
    is_pesticide=getattr(chemical_data, 'is_pesticide', False),  # NEW
    epa_registration_number=getattr(chemical_data, 'epa_registration_number', None),  # NEW
    signal_word=getattr(chemical_data, 'signal_word', None),  # NEW
    sds_missing=True,
    data_source=data_source,
    confidence_score=confidence_score,
    verification_status='unverified',
    is_active=True,
    created_by_user_id=user_id,
    created_at=datetime.utcnow()
)

2.5 API Endpoint Changes

File: tellus-ehs-hazcom-service/app/api/v1/chemiq/inventory.py

Update create_chemical() to dispatch EPA label search for pesticides:

# After existing SDS search dispatch:
if is_new_product and chemical.company_product:
    # Dispatch SDS search for products without SDS
    if chemical.company_product.sds_missing:
        await job_dispatcher.dispatch_sds_search(...)

    # NEW: Dispatch EPA label search for pesticides
    if chemical.company_product.is_pesticide and chemical.company_product.epa_registration_number:
        try:
            await job_dispatcher.dispatch_epa_label_search(
                company_product_id=str(chemical.company_product.company_product_id),
                epa_registration_number=chemical.company_product.epa_registration_number,
                product_name=chemical.company_product.product_name,
                manufacturer=chemical.company_product.manufacturer,
                company_id=str(ctx.company_id),
                user_id=str(ctx.user_id),
            )
        except Exception as e:
            logger.warning(f"Failed to dispatch EPA label search: {e}")

2.6 Job Dispatcher Changes

File: tellus-ehs-hazcom-service/app/services/job_dispatcher.py

Add new dispatch method:

async def dispatch_epa_label_search(
    self,
    company_product_id: str,
    epa_registration_number: str,
    product_name: str,
    manufacturer: str | None = None,
    company_id: str | None = None,
    user_id: str | None = None,
    priority: MessagePriority = MessagePriority.NORMAL
) -> str:
    """Dispatch an EPA label search job to the background service."""
    payload = {
        "company_product_id": company_product_id,
        "epa_registration_number": epa_registration_number,
        "product_name": product_name,
        "manufacturer": manufacturer,
        "company_id": company_id,
        "user_id": user_id,
    }

    return await self._dispatch(
        message_type=MessageType.EPA_LABEL_SEARCH,
        payload=payload,
        priority=priority
    )

---

3. Frontend Changes

3.1 Type Definitions

File: tellus-ehs-hazcom-ui/src/types/index.ts

export interface ChemicalInventoryCreate {
  site_id: string;
  product_name: string;
  manufacturer: string;
  product_code?: string;
  cas_number?: string;
  barcode_upc?: string;  // Now optional
  barcode_source?: 'scanned' | 'label_extracted' | 'manual' | 'api_validated';  // NEW
  internal_sku?: string;  // NEW
  container_size: number;
  size_unit?: string;
  quantity: number;
  location_id?: string;

  // Pesticide fields (NEW)
  is_pesticide?: boolean;
  epa_registration_number?: string;
  signal_word?: 'DANGER' | 'WARNING' | 'CAUTION';

  // Data source tracking
  data_source?: string;
  extraction_confidence?: number;
}

3.2 Add Chemical Page Changes

File: tellus-ehs-hazcom-ui/src/pages/chemiq/inventory/AddChemicalPage.tsx

3.2.1 Form State Updates

const [formData, setFormData] = useState<ChemicalInventoryCreate>({
  site_id: currentSite?.site_id || '',
  product_name: '',
  manufacturer: '',
  product_code: '',
  cas_number: '',
  container_size: undefined,
  size_unit: 'L',
  quantity: 1,
  barcode_upc: '',  // Now optional
  barcode_source: undefined,  // NEW
  internal_sku: '',  // NEW
  location_id: undefined,
  // Pesticide fields (NEW)
  is_pesticide: false,
  epa_registration_number: '',
  signal_word: undefined,
});

3.2.2 Validation Updates

Remove barcode from required fields:

const validateForm = (): boolean => {
  const newErrors: Record<string, string> = {};

  // Required fields: Product Name, Manufacturer, Container Size, Quantity
  if (!formData.product_name.trim()) {
    newErrors.product_name = 'Product name is required';
  }
  if (!formData.manufacturer.trim()) {
    newErrors.manufacturer = 'Manufacturer is required';
  }
  // REMOVED: barcode_upc validation - now optional
  if (!formData.container_size || formData.container_size <= 0) {
    newErrors.container_size = 'Container size is required';
  }
  if (!formData.quantity || formData.quantity <= 0) {
    newErrors.quantity = 'Quantity must be greater than 0';
  }

  setErrors(newErrors);
  return Object.keys(newErrors).length === 0;
};

3.2.3 UI Changes - Barcode Field

Replace the existing Barcode/UPC field with:

{/* Barcode/UPC - Now Optional */}
<div>
  <label className="block text-sm font-medium text-text-main mb-1">
    Barcode/UPC
  </label>
  <input
    type="text"
    value={formData.barcode_upc}
    onChange={(e) => handleInputChange('barcode_upc', e.target.value)}
    className="w-full px-4 py-2 border border-border-light rounded-lg bg-surface-light text-text-main placeholder-text-muted focus:outline-none focus:ring-2 focus:ring-accent-purple-500"
    placeholder="e.g., 012345678905"
  />
  <p className="mt-1 text-sm text-text-muted">
    Optional - enter if available on product packaging
  </p>
</div>

{/* Internal SKU - NEW */}
<div>
  <label className="block text-sm font-medium text-text-main mb-1">
    Internal SKU / Reference
  </label>
  <input
    type="text"
    value={formData.internal_sku}
    onChange={(e) => handleInputChange('internal_sku', e.target.value)}
    className="w-full px-4 py-2 border border-border-light rounded-lg bg-surface-light text-text-main placeholder-text-muted focus:outline-none focus:ring-2 focus:ring-accent-purple-500"
    placeholder="e.g., CHEM-001, ABC-123"
  />
  <p className="mt-1 text-sm text-text-muted">
    Your company's internal product code (optional)
  </p>
</div>

3.2.4 UI Changes - Pesticide Section

Add new collapsible section for pesticide products:

{/* Pesticide Information - Collapsible */}
<div className="border border-border-light rounded-lg">
  <button
    type="button"
    onClick={() => setShowPesticideInfo(!showPesticideInfo)}
    className="w-full flex items-center justify-between px-4 py-3 text-left hover:bg-surface-light transition-colors"
  >
    <div className="flex items-center gap-2">
      <span className="text-sm font-medium text-text-main">
        Pesticide Information
      </span>
      {formData.is_pesticide && (
        <span className="px-2 py-0.5 text-xs font-medium bg-amber-100 text-amber-700 rounded">
          Pesticide
        </span>
      )}
    </div>
    <ChevronDown
      className={`h-5 w-5 text-text-muted transition-transform ${
        showPesticideInfo ? 'transform rotate-180' : ''
      }`}
    />
  </button>
  {showPesticideInfo && (
    <div className="px-4 pb-4 space-y-4 border-t border-border-light">
      {/* Is Pesticide Toggle */}
      <div className="flex items-center gap-3 pt-4">
        <input
          type="checkbox"
          id="is_pesticide"
          checked={formData.is_pesticide}
          onChange={(e) => handleInputChange('is_pesticide', e.target.checked)}
          className="h-4 w-4 text-amber-600 focus:ring-amber-500 border-gray-300 rounded"
        />
        <label htmlFor="is_pesticide" className="text-sm text-text-main">
          This product is a pesticide (requires EPA registration)
        </label>
      </div>

      {formData.is_pesticide && (
        <>
          {/* EPA Registration Number */}
          <div>
            <label className="block text-sm font-medium text-text-main mb-1">
              EPA Registration Number
            </label>
            <input
              type="text"
              value={formData.epa_registration_number}
              onChange={(e) => handleInputChange('epa_registration_number', e.target.value)}
              className="w-full px-4 py-2 border border-border-light rounded-lg bg-surface-light text-text-main placeholder-text-muted focus:outline-none focus:ring-2 focus:ring-amber-500"
              placeholder="e.g., 1234-567 or 1234-567-8901"
            />
            <p className="mt-1 text-sm text-text-muted">
              Found on pesticide label as "EPA Reg. No."
            </p>
          </div>

          {/* Signal Word */}
          <div>
            <label className="block text-sm font-medium text-text-main mb-1">
              Signal Word
            </label>
            <select
              value={formData.signal_word || ''}
              onChange={(e) => handleInputChange('signal_word', e.target.value || undefined)}
              className="w-full px-4 py-2 border border-border-light rounded-lg bg-surface-light text-text-main focus:outline-none focus:ring-2 focus:ring-amber-500"
            >
              <option value="">Select signal word...</option>
              <option value="DANGER">DANGER (Highest toxicity)</option>
              <option value="WARNING">WARNING (Moderate toxicity)</option>
              <option value="CAUTION">CAUTION (Lowest toxicity)</option>
            </select>
            <p className="mt-1 text-sm text-text-muted">
              Required warning level on all pesticide labels
            </p>
          </div>
        </>
      )}
    </div>
  )}
</div>

3.3 Vision LLM Prompt Updates

File: tellus-ehs-hazcom-service/app/services/chemiq/vision_extraction_service.py

Update the extraction prompt to include pesticide detection:

EXTRACTION_PROMPT = """
Analyze this product label image and extract the following information:

**Required Fields:**
- product_name: The full product name
- manufacturer: The company/brand name
- net_contents: Size/volume with unit (e.g., "4L", "1 GAL")

**Optional Fields:**
- product_code: Manufacturer's product/catalog number
- barcode: UPC/EAN barcode number (if visible)
- cas_number: CAS registry number (if present)

**Pesticide Detection (IMPORTANT):**
- is_pesticide: true/false - Check for ANY of these indicators:
  1. "EPA Reg. No." or "EPA Registration Number" text
  2. Signal words: DANGER, WARNING, or CAUTION (prominently displayed)
  3. "Keep Out of Reach of Children" statement
  4. Product type keywords: insecticide, herbicide, fungicide, rodenticide,
     disinfectant, sanitizer, antimicrobial, pesticide
- epa_registration_number: Extract the EPA Reg. No. if present (format: XXXXX-XX or XXXXX-XX-XXXXX)
- signal_word: Extract DANGER, WARNING, or CAUTION if present

Return JSON format:
{
  "product_name": "...",
  "manufacturer": "...",
  "net_contents": "...",
  "product_code": "..." or null,
  "barcode": "..." or null,
  "cas_number": "..." or null,
  "is_pesticide": true/false,
  "epa_registration_number": "..." or null,
  "signal_word": "DANGER" | "WARNING" | "CAUTION" | null,
  "confidence": 0.0-1.0
}
"""

3.4 Label Capture Section Updates

File: tellus-ehs-hazcom-ui/src/pages/chemiq/inventory/components/LabelCaptureSection.tsx

Update to pass pesticide fields back to parent:

// In onDataExtracted callback
onDataExtracted({
  productName: data.product_name,
  manufacturer: data.manufacturer,
  productCode: data.product_code,
  barcode: data.barcode,
  netContents: data.net_contents,
  confidence: data.confidence,
  // Pesticide fields (NEW)
  isPesticide: data.is_pesticide || false,
  epaRegistrationNumber: data.epa_registration_number,
  signalWord: data.signal_word,
}, images, matches, bestMatch);

Update parent handler in AddChemicalPage.tsx:

onDataExtracted={(data, images, matches, best) => {
  // ... existing parsing logic ...

  setFormData((prev) => ({
    ...prev,
    product_name: data.productName || prev.product_name,
    manufacturer: data.manufacturer || prev.manufacturer,
    // ... other fields ...

    // Pesticide fields (NEW)
    is_pesticide: data.isPesticide || prev.is_pesticide,
    epa_registration_number: data.epaRegistrationNumber || prev.epa_registration_number,
    signal_word: data.signalWord || prev.signal_word,
  }));

  // Auto-expand pesticide section if detected
  if (data.isPesticide) {
    setShowPesticideInfo(true);
  }
}}

3.5 Barcode Search Section Updates

File: tellus-ehs-hazcom-ui/src/pages/chemiq/inventory/components/BarcodeSearchSection.tsx

Update to set barcode_source:

onProductFound={(data) => {
  setFormData((prev) => ({
    ...prev,
    product_name: data.productName,
    manufacturer: data.manufacturer,
    barcode_upc: data.barcode,
    barcode_source: 'scanned',  // NEW: Mark as scanned
  }));
}}

---

4. Background Service Changes

4.1 Message Types

File: tellus-ehs-hazcom-service/app/core/sqs_config.py

Add new message type:

class MessageType(str, Enum):
    SDS_PARSE = "sds_parse"
    SDS_SEARCH = "sds_search"
    EPA_LABEL_SEARCH = "epa_label_search"  # NEW
    REPORT_GENERATE = "report_generate"
    EMAIL_SEND = "email_send"
    BULK_IMPORT = "bulk_import"
    CLEANUP = "cleanup"
    NOTIFICATION_SEND = "notification_send"

4.2 EPA Label Search Handler

File: tellus-ehs-background-service/app/services/sqs_consumer/handlers.py

Add new handler:

async def handle_epa_label_search(payload: Dict[str, Any], metadata: Dict[str, Any]) -> None:
    """Handle EPA label search job from queue."""
    company_product_id = payload.get("company_product_id")
    epa_registration_number = payload.get("epa_registration_number")
    product_name = payload.get("product_name")
    manufacturer = payload.get("manufacturer")
    company_id = payload.get("company_id")

    logger.info(f"Processing EPA label search for product {company_product_id}, EPA Reg: {epa_registration_number}")

    try:
        epa_search_service = EPALabelSearchService()
        await epa_search_service.search_for_product(
            company_product_id=company_product_id,
            epa_registration_number=epa_registration_number,
            product_name=product_name,
            manufacturer=manufacturer,
            company_id=company_id,
        )
    except Exception as e:
        logger.error(f"EPA label search failed for {company_product_id}: {e}")
        raise

4.3 EPA Label Search Service

File: tellus-ehs-background-service/app/services/epa_label_search/service.py

class EPALabelSearchService:
    """Service for searching and retrieving EPA pesticide labels."""

    async def search_for_product(
        self,
        company_product_id: str,
        epa_registration_number: str,
        product_name: str,
        manufacturer: str | None = None,
        company_id: str | None = None,
    ) -> None:
        """Search for EPA label by registration number."""

        # 1. Check if label already exists in company's library
        existing_label = await self._find_existing_label(
            company_id=company_id,
            epa_registration_number=epa_registration_number
        )

        if existing_label:
            # Link existing label to product
            await self._link_label_to_product(
                company_product_id=company_product_id,
                label_id=existing_label.label_id
            )
            return

        # 2. Search EPA PPLS database
        ppls_result = await self._search_ppls_api(epa_registration_number)

        if ppls_result:
            # Download and store the label
            label = await self._download_and_store_label(ppls_result)
            await self._link_label_to_product(
                company_product_id=company_product_id,
                label_id=label.label_id
            )
            return

        # 3. Mark product as needing manual label upload
        await self._mark_label_not_found(company_product_id)

---

5. Implementation Phases

Phase 1: Backend Schema & API (Day 1-2)

1. Create Alembic migration for new columns
2. Update CompanyProductCatalog model
3. Update ChemicalInventoryCreate schema
4. Update chemiq_service.py to handle new fields
5. Run migration on dev database
6. Test API with new fields via Swagger

Phase 2: Frontend UI Changes (Day 2-3)

1. Update TypeScript types
2. Remove barcode from required validation
3. Add Internal SKU field to form
4. Add Pesticide Information collapsible section
5. Update form submission to include new fields
6. Test manual entry flow

Phase 3: Vision LLM Integration (Day 3-4)

1. Update Vision LLM prompt for pesticide detection
2. Update LabelCaptureSection to pass pesticide fields
3. Update AddChemicalPage to handle pesticide data
4. Auto-expand pesticide section when detected
5. Test label capture with pesticide products

Phase 4: Background Service (Day 4-5)

1. Add EPA_LABEL_SEARCH message type
2. Add dispatch_epa_label_search() to job dispatcher
3. Create EPA label search handler
4. Create EPA PPLS search service
5. Update inventory creation to dispatch EPA label search
6. Test end-to-end flow

Phase 5: Testing & QA (Day 5-6)

1. Test barcode optional flow (no barcode entered)
2. Test internal SKU field
3. Test pesticide detection via label capture
4. Test EPA label search dispatch
5. Test product matching with different barcode sources
6. Regression testing on existing functionality

---

6. Testing Checklist

6.1 Barcode Changes

• Create product without barcode - succeeds
• Create product with scanned barcode - barcode_source = scanned
• Create product with manually entered barcode - barcode_source = manual
• Create product with AI-extracted barcode - barcode_source = label_extracted
• Internal SKU field saves correctly
• Product matching only uses trusted barcode sources for global matching

6.2 Pesticide Changes

• Label capture detects pesticide from EPA Reg. No.
• Label capture detects pesticide from signal word
• Pesticide section auto-expands when detected
• EPA Registration Number saves correctly
• Signal word dropdown works correctly
• EPA label search job dispatched for new pesticide products
• Non-pesticide products don't trigger EPA label search

6.3 Regression Tests

• Existing barcode scan flow still works
• Existing label capture flow still works
• Existing SDS search dispatch still works
• Product creation with all fields works
• Product update works
• Inventory list displays correctly

---

7. Rollback Plan

If issues are discovered after deployment:

1. Database: Migration includes downgrade() function
2. Backend: Revert to previous commit
3. Frontend: Revert to previous build
4. Feature Flags: Consider adding feature flags for gradual rollout

---

8. Future Enhancements

1. Barcode API Integration: Validate barcodes via GS1/UPC database API
2. EPA PPLS API Integration: Auto-fetch labels from EPA Pesticide Product Label System
3. Bulk Pesticide Import: Import pesticide inventory from spreadsheet
4. Pesticide Compliance Reports: Track pesticide usage and certifications
5. Applicator Training Integration: Link pesticide products to required training