Quick Inventory Import - Requirements Document

Overview

Problem Statement

When companies onboard to the Tellus EHS platform, they face significant friction in populating their chemical inventory. The current "Add Chemical" flow adds one item at a time, requiring detailed information entry on the warehouse floor. This creates several issues:

Time-consuming: Manual entry of hundreds of items is impractical
Low motivation: Junior employees tasked with data entry lack incentive for accuracy
Data quality: Information entered under time pressure is often incomplete or incorrect
Non-productive: For business owners, this is overhead that doesn't directly generate value
Delayed value realization: Users can't experience platform benefits until inventory is populated

Solution Overview

Implement a staged inventory import system that:

Provides multiple low-friction methods to capture inventory data
Separates "quick capture" (field workers) from "review & approve" (supervisors)
Uses a staging area for pending items before final inventory creation
Leverages AI/OCR to auto-populate fields where possible
Allows inventory creation without requiring an SDS attachment

User Personas

Field Worker (Capture Role)

Junior employee, warehouse staff, or intern
Limited product knowledge
Using mobile device on warehouse floor
Goal: Capture items quickly with minimal friction
Not responsible for data accuracy

Supervisor (Review Role)

Manager, safety coordinator, or business owner
Product and compliance knowledge
Using desktop at their desk
Goal: Validate, correct, and approve staged items
Responsible for final data quality

Import Methods

1. Photo Capture (Mobile-First)

Use Case: Walking the warehouse floor to document existing inventory

User Flow:

Select site and location (persists for session)
Take photo(s) of product label (up to 3 per item)
Enter quantity and unit
Add optional notes
Tap "Add Another" to continue or "Done" to finish session

Data Captured:

Field	Source	Required
Product images	Camera (up to 5)	Yes (at least 2)
Site	User selection	Yes
Location	User selection	Yes
Quantity	User input	Yes
Unit	User selection	Yes
Notes	User input	No

Background Processing:

OCR extracts text from product label images
Barcode detection attempts to read UPC/EAN codes
Auto-matching against existing product catalog by barcode
Auto-matching against SDS library by product name similarity

UI Mockup:

┌─────────────────────────────────────┐
│  Quick Capture                      │
├─────────────────────────────────────┤
│  [Site: ▼ Main Warehouse  ]         │
│  [Location: ▼ Shelf A-1   ]         │
├─────────────────────────────────────┤
│  Photos:                            │
│  ┌─────┐ ┌─────┐ ┌─────────────┐   │
│  │ img │ │ img │ │  + Add      │   │
│  │  ✕  │ │  ✕  │ │   Photo     │   │
│  └─────┘ └─────┘ └─────────────┘   │
│  2 of 5 photos                      │
│                                     │
│  Quantity: [ 5    ] [ bottles ▼]    │
│                                     │
│  Notes (optional):                  │
│  ┌─────────────────────────────┐    │
│  │ Almost empty, reorder soon  │    │
│  └─────────────────────────────┘    │
│                                     │
│  [ + Add Another ] [ Done (12) ]    │
└─────────────────────────────────────┘

2. Barcode Scan (Mobile-First)

Use Case: Quick inventory count of products with readable barcodes

Design Principle: Non-blocking capture - User is never waiting for server response. All lookups happen asynchronously in the background after item is submitted.

User Flow:

Select site and location (persists for session)
Point camera at barcode
Barcode detected → immediately show quantity entry form
Enter quantity and optional notes
Tap "Add Another" to continue or "Done" to finish session
Background: Server lookup and product matching happens async

Data Captured:

Field	Source	Required
Barcode	Camera scan	Yes
Site	User selection	Yes
Location	User selection	Yes
Quantity	User input (default: 1)	Yes
Unit	User selection	Yes
Notes	User input	No

Background Processing (async, after item submitted):

Search chemiq_company_product_catalog by barcode_upc
If found: populate matched_company_product_id, matched_sds_id
If not found: mark as "no match" for manual review
Update staging item with match results and confidence score

Why Non-Blocking:

User can scan 50+ items without network delays
Better offline support potential
Matching results visible during supervisor review
Same user action regardless of match result

UI Mockup:

┌─────────────────────────────────────┐
│  Barcode Scan                       │
├─────────────────────────────────────┤
│  [Site: ▼ Main Warehouse  ]         │
│  [Location: ▼ Shelf A-1   ]         │
├─────────────────────────────────────┤
│  ┌─────────────────────────────┐    │
│  │                             │    │
│  │    Point at barcode         │    │
│  │    ▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓        │    │
│  │                             │    │
│  └─────────────────────────────┘    │
│                                     │
│  ✓ Scanned: 096619204205           │
│    Qty: [1] [-] [+]  [each ▼]      │
│    Notes: [___________________]     │
│                                     │
│  Session: 5 items                   │
│  [ + Add Another ]    [ Done ]      │
└─────────────────────────────────────┘

Review Queue Shows Match Results (after background processing):

┌────────────────────────────────────────────────────────────────────┐
│ Barcode Scan - Dec 25, 3:15 PM - 12 items                         │
├────────────────────────────────────────────────────────────────────┤
│ │ Barcode        │ Product (matched)    │ Qty │ Location │ Status │
│ ├────────────────┼──────────────────────┼─────┼──────────┼────────│
│ │ 096619204205   │ ✓ Clorox Bleach      │ 5   │ Shelf A  │ 🟢     │
│ │ 079567490012   │ ✓ WD-40 Multi-Use    │ 3   │ Shelf A  │ 🟢     │
│ │ 012345678901   │ ⚠ No match found     │ 2   │ Shelf B  │ 🟡     │
│ │ 098765432109   │ ⏳ Processing...     │ 1   │ Shelf B  │ ⏳     │
└────────────────────────────────────────────────────────────────────┘

3. Excel/CSV Import

Use Case: Migrating from existing spreadsheets or other systems

User Flow:

Download template (optional)
Upload Excel/CSV file
Map columns to fields (if not using template)
Preview data and validation errors
Submit for staging

Template Columns:

Column	Required	Description
Product Name	Yes	Name of the chemical product
Quantity	Yes	Numeric quantity
Unit	No	Unit of measure (default: "each")
Manufacturer	No	Product manufacturer
Barcode/UPC	No	Product barcode for matching
Site Name	No	Must match existing site
Location Name	No	Must match existing location within site
Notes	No	Any additional notes

Validation Rules:

Product Name is required and non-empty
Quantity must be a positive number
Site Name (if provided) must exist in company's sites
Location Name (if provided) must exist within the specified site
Duplicate rows are flagged but allowed

UI Mockup:

┌─────────────────────────────────────────────┐
│  Import from Excel                          │
├─────────────────────────────────────────────┤
│  [ Download Template ]                      │
│                                             │
│  Required columns:                          │
│  • Product Name                             │
│  • Quantity                                 │
│                                             │
│  Optional columns:                          │
│  • Manufacturer, Barcode/UPC, Unit          │
│  • Site Name, Location Name, Notes          │
│                                             │
│  ┌─────────────────────────────────────┐   │
│  │  Drop Excel/CSV file here           │   │
│  │  or click to browse                 │   │
│  └─────────────────────────────────────┘   │
│                                             │
│  [ Cancel ]              [ Upload ]         │
└─────────────────────────────────────────────┘

Post-Upload Preview:

┌─────────────────────────────────────────────────────────────┐
│  Import Preview - inventory_list.xlsx                       │
├─────────────────────────────────────────────────────────────┤
│  45 rows found • 3 warnings • 0 errors                      │
│                                                             │
│  │ Product Name      │ Qty │ Site      │ Status           │ │
│  ├───────────────────┼─────┼───────────┼──────────────────┤ │
│  │ Clorox Bleach     │ 5   │ Warehouse │ ✓ Ready          │ │
│  │ Unknown Product   │ 3   │ -         │ ⚠ No site       │ │
│  │ WD-40             │ 12  │ Warehouse │ ✓ Ready          │ │
│                                                             │
│  [ Cancel ]                    [ Import 45 Items ]          │
└─────────────────────────────────────────────────────────────┘

4. Invoice/PO Upload

Use Case: Adding inventory from new purchases/deliveries

User Flow:

Upload invoice PDF or image
Select default site/location (optional, can assign during review)
System extracts line items using AI/OCR
Items go to staging for review

Data Extracted:

Field	Extraction Method
Product Name	OCR + AI parsing
Manufacturer	OCR + AI parsing
Quantity	OCR + AI parsing
Unit	OCR + AI parsing
Unit Price	OCR (informational only)

Supported Formats:

PDF invoices
Image files (JPG, PNG)
Maximum file size: 20MB

UI Mockup:

┌─────────────────────────────────────────────┐
│  Import from Invoice                        │
├─────────────────────────────────────────────┤
│  Upload a PDF or image of your invoice.     │
│  We'll extract product information          │
│  automatically using AI.                    │
│                                             │
│  ┌─────────────────────────────────────┐   │
│  │  Drop invoice here or browse        │   │
│  └─────────────────────────────────────┘   │
│                                             │
│  Default location for extracted items:      │
│  [Site: ▼ Optional ] [Location: ▼ ]        │
│                                             │
│  You can assign locations during review.    │
│                                             │
│  [ Cancel ]              [ Upload ]         │
└─────────────────────────────────────────────┘

5. Add from SDS Library

Use Case: Creating inventory entries for products whose SDS is already uploaded

User Flow:

Search/browse SDS library
Select one or more SDS documents
For each selected SDS, enter quantity and location
Items go to staging (or directly to inventory if all info provided)

Data Pre-populated from SDS:

Product Name
Manufacturer
SDS attachment (automatic)
Hazard information (from parsed SDS)

User Provides:

Site and Location
Quantity and Unit
Notes (optional)

UI Mockup:

┌─────────────────────────────────────────────────────┐
│  Add from SDS Library                               │
├─────────────────────────────────────────────────────┤
│  Search: [ clorox                   ] [Search]     │
│                                                     │
│  ┌─────────────────────────────────────────────┐   │
│  │ ☑ Clorox Disinfecting Bleach    │ Clorox    │   │
│  │   Parsed ✓  Signal: Danger                  │   │
│  ├─────────────────────────────────────────────┤   │
│  │ ☐ Clorox Clean-Up Cleaner       │ Clorox    │   │
│  │   Parsed ✓  Signal: Warning                 │   │
│  └─────────────────────────────────────────────┘   │
│                                                     │
│  Selected: 1 SDS                                    │
│                                                     │
│  [ Cancel ]              [ Continue → ]             │
└─────────────────────────────────────────────────────┘

        ↓ After clicking Continue ↓

┌─────────────────────────────────────────────────────┐
│  Add Inventory Details                              │
├─────────────────────────────────────────────────────┤
│  Clorox Disinfecting Bleach                         │
│  Manufacturer: Clorox | SDS: Attached ✓            │
│                                                     │
│  Site:     [ ▼ Main Warehouse        ]              │
│  Location: [ ▼ Cleaning Supplies     ]              │
│  Quantity: [ 5        ] [ gallons ▼  ]              │
│  Notes:    [ ________________________ ]             │
│                                                     │
│  [ ← Back ]               [ Add to Inventory ]      │
└─────────────────────────────────────────────────────┘

6. Copy from Another Site

Use Case: Multi-location companies setting up new sites with similar inventory

User Flow:

Select source site
Select target site
Choose items to copy
Select copy or transfer mode
Adjust quantities if needed
Submit

Options:

Copy: Creates duplicate entries at new site (original remains)
Transfer: Moves inventory to new site (original marked inactive)

UI Mockup:

┌─────────────────────────────────────────────────────┐
│  Copy Inventory from Another Site                   │
├─────────────────────────────────────────────────────┤
│  Source Site: [ ▼ Warehouse A        ]              │
│  Target Site: [ ▼ New Warehouse B    ]              │
│                                                     │
│  ☑ Select All (45 items)                           │
│  ┌─────────────────────────────────────────────┐   │
│  │ ☑ Clorox Bleach           │ 5 gal │ Shelf 1 │   │
│  │ ☑ Simple Green            │ 3 btl │ Shelf 1 │   │
│  │ ☑ WD-40                   │ 12    │ Cabinet │   │
│  └─────────────────────────────────────────────┘   │
│                                                     │
│  Mode:                                              │
│  ○ Copy (keep at both sites)                        │
│  ○ Transfer (move to new site)                      │
│                                                     │
│  [ Cancel ]              [ Copy 45 Items ]          │
└─────────────────────────────────────────────────────┘

7. Distributor Catalog Import (Future)

Use Case: Companies regularly ordering from known distributors

User Flow:

Select or add distributor
Upload distributor order history or product list
System maps distributor SKUs to known products
Matched items go to staging

Supported Distributors (future integration):

Grainger
Uline
Fisher Scientific
CHEMTREC partners
Custom distributor mapping

Value Proposition:

One-time SKU mapping per distributor
Recurring orders become one-click imports
Pre-matched SDS from distributor partnerships

8. Product Database Search (Future)

Use Case: Finding products in external databases when SDS is not available

User Flow:

Search by product name, manufacturer, or UPC
View results from public SDS databases
Select product to add
System fetches SDS automatically
Item goes to staging with SDS attached

Data Sources (future integration):

Manufacturer SDS portals
OSHA SDS database
Chemical supplier databases
UPC/barcode databases

Staging Workflow

Staging Area Concept

All import methods (except direct "Add from SDS Library" with complete info) create items in a staging area before final inventory creation. This allows:

Batch review: Supervisor reviews multiple items at once
Error correction: Fix OCR mistakes, missing data
Product matching: Link to existing catalog or create new entries
SDS attachment: Find and attach SDS documents
Quality control: Reject invalid or duplicate entries

Staging Item States

Status	Description	Actions Available
`pending_review`	Awaiting supervisor review	Edit, Approve, Reject
`needs_attention`	Low confidence match or missing required data	Edit, Approve, Reject
`approved`	Ready to create inventory	Create Inventory
`rejected`	Will not be added to inventory	Delete, Restore
`completed`	Inventory created successfully	View Inventory

Auto-Matching Logic

When items are added to staging, background processing attempts to match:

Barcode Match (highest confidence)
- Search chemiq_company_product_catalog.barcode_upc
- If exact match: auto-populate product info and SDS
- Confidence: 100%
Product Name Match (medium confidence)
- Fuzzy search against existing product catalog
- If similarity > 85%: suggest match
- Confidence: 70-95% based on similarity
OCR Text Match (lower confidence)
- Extract text from product images
- Search SDS library by extracted text
- Confidence: 50-80% based on match quality
No Match Found
- Mark as "New Product"
- Supervisor must enter product details manually
- Confidence: 0%

Supervisor Review Dashboard

┌─────────────────────────────────────────────────────────────────┐
│  Pending Inventory Review                              [Refresh]│
├─────────────────────────────────────────────────────────────────┤
│  Filter: [All Methods ▼] [All Statuses ▼]   Sort: [Newest ▼]  │
├─────────────────────────────────────────────────────────────────┤
│  📦 Photo Upload - Dec 25, 2:30 PM - John D. (8 items)    [→]  │
│  ├─ 🟢 5 high confidence matches                               │
│  ├─ 🟡 2 need attention                                        │
│  └─ 🔴 1 no match found                                        │
│                                                                 │
│  📊 Excel Import - Dec 25, 10:00 AM - Sarah M. (45 items) [→]  │
│  ├─ 🟢 40 ready to approve                                     │
│  └─ 🟡 5 missing location                                      │
│                                                                 │
│  🧾 Invoice #INV-2024-001 - Dec 24 - Auto (12 items)      [→]  │
│  └─ 🟢 All items matched                                       │
└─────────────────────────────────────────────────────────────────┘

Batch Review Screen

┌────────────────────────────────────────────────────────────────────────┐
│ ← Back    Photo Upload - Dec 25, 2:30 PM                               │
│           Captured by: John D.                                         │
├────────────────────────────────────────────────────────────────────────┤
│ [☑ Select All] [Approve Selected] [Reject Selected]  Filter: [All ▼] │
├────────────────────────────────────────────────────────────────────────┤
│ ☑ │ 📷 │ Product              │ Match      │ Qty  │ Location  │ 💬│ St │
├───┼────┼──────────────────────┼────────────┼──────┼───────────┼───┼────┤
│ ☑ │ 🖼 │ Clorox Bleach        │ ✓ 98%      │ 5 gal│ Shelf A-1 │ 💬│ 🟢 │
│ ☑ │ 🖼 │ Simple Green         │ ✓ 95%      │ 3 btl│ Shelf A-1 │   │ 🟢 │
│ ☑ │ 🖼 │ [Unknown Product]    │ ⚠ New      │ 2 cs │ Shelf A-2 │ 💬│ 🟡 │
│   │    │ └─ OCR: "ACME Wax"   │ [Edit]     │      │           │   │    │
│ ☑ │ 🖼 │ WD-40                │ ✓ Barcode  │ 12   │ Cabinet B │   │ 🟢 │
└────────────────────────────────────────────────────────────────────────┘
│  💬 = Has field notes (click to view)                                  │
│                                                                        │
│ [Reject 0 Items]                    [Approve 4 Items → Create Inventory]│
└────────────────────────────────────────────────────────────────────────┘

Single Item Review/Edit

┌──────────────────────────────────────────────────────────────────────────┐
│ Edit Staging Item                                                  [×]  │
├──────────────────────────────────────────────────────────────────────────┤
│  ┌────────┐ ┌────────┐ ┌────────┐                                       │
│  │  📷 1  │ │  📷 2  │ │  📷 3  │  (Click to enlarge)                   │
│  └────────┘ └────────┘ └────────┘                                       │
│                                                                          │
│  Product Name: [ Clorox Disinfecting Bleach___________ ]                │
│  Manufacturer: [ The Clorox Company___________________ ]                │
│                                                                          │
│  Match Status: ✓ Matched to existing product (98% confidence)           │
│  [View Matched Product] [Search for Different Product]                   │
│                                                                          │
│  SDS: ✓ Attached - Clorox Bleach SDS (Rev. 2024-01-15)                  │
│  [View SDS] [Change SDS] [Remove SDS]                                    │
│                                                                          │
│  Site:     [ ▼ Main Warehouse        ]                                   │
│  Location: [ ▼ Shelf A-1             ]                                   │
│  Quantity: [ 5        ] [ gallons ▼  ]                                   │
│                                                                          │
│  💬 Field Notes (by John D., Dec 25 2:30 PM):                           │
│  ┌────────────────────────────────────────────────────────────────────┐ │
│  │ "Almost empty, needs reorder. Label damaged on one side."         │ │
│  └────────────────────────────────────────────────────────────────────┘ │
│                                                                          │
│  📝 Reviewer Notes:                                                      │
│  ┌────────────────────────────────────────────────────────────────────┐ │
│  │ Added to reorder list.                                            │ │
│  └────────────────────────────────────────────────────────────────────┘ │
│                                                                          │
│  [ Reject ]            [ Save Changes ]            [ Approve ]           │
└──────────────────────────────────────────────────────────────────────────┘

Data Model

New Tables

`chemiq_inventory_upload_batches`

Tracks each upload session/batch.

Column	Type	Description
batch_id	UUID	Primary key
company_id	UUID	FK to companies
upload_method	VARCHAR(50)	'photo', 'barcode', 'excel', 'invoice', 'sds_library', 'site_copy'
source_filename	VARCHAR(255)	Original filename for file uploads
total_items	INT	Total items in batch
pending_count	INT	Items pending review
approved_count	INT	Items approved
rejected_count	INT	Items rejected
completed_count	INT	Items converted to inventory
processing_status	VARCHAR(30)	'pending', 'processing', 'ready_for_review', 'completed'
processing_error	TEXT	Error message if processing failed
created_by	UUID	FK to users (uploader)
created_at	TIMESTAMPTZ	Upload timestamp
completed_at	TIMESTAMPTZ	When all items processed

`chemiq_inventory_staging`

Individual items pending review.

Column	Type	Description
staging_id	UUID	Primary key
company_id	UUID	FK to companies
upload_batch_id	UUID	FK to upload batches
upload_method	VARCHAR(50)	Method used for this item
Raw Captured Data
raw_product_name	VARCHAR(500)	Product name as captured
raw_manufacturer	VARCHAR(255)	Manufacturer as captured
raw_barcode	VARCHAR(100)	Barcode if scanned
raw_quantity	DECIMAL(10,2)	Quantity as entered
raw_unit	VARCHAR(50)	Unit as entered
raw_location_text	VARCHAR(255)	Free text location if not selected
product_image_urls	JSONB	Array of S3 URLs (up to 3)
Location
site_id	UUID	FK to company_sites (nullable)
location_id	UUID	FK to site_locations (nullable)
Auto-Matching Results
matched_company_product_id	UUID	FK to product catalog (nullable)
matched_sds_id	UUID	FK to SDS documents (nullable)
match_confidence	DECIMAL(3,2)	0.00 to 1.00
match_method	VARCHAR(50)	'barcode_exact', 'name_fuzzy', 'ocr_text', null
ocr_extracted_text	TEXT	Raw OCR output from images
Review Workflow
status	VARCHAR(30)	Status enum (see above)
Approved Values
approved_product_name	VARCHAR(500)	Supervisor-approved name
approved_manufacturer	VARCHAR(255)	Supervisor-approved manufacturer
approved_quantity	DECIMAL(10,2)	Supervisor-approved quantity
approved_unit	VARCHAR(50)	Supervisor-approved unit
Notes
capture_notes	TEXT	Notes from field worker
reviewer_notes	TEXT	Notes from supervisor
Result
created_chemical_id	UUID	FK to inventory (after approval)
Audit
created_by	UUID	FK to users (capturer)
created_at	TIMESTAMPTZ	Capture timestamp
reviewed_by	UUID	FK to users (reviewer)
reviewed_at	TIMESTAMPTZ	Review timestamp

Indexes

CREATE INDEX idx_staging_company_status ON chemiq_inventory_staging(company_id, status);
CREATE INDEX idx_staging_batch ON chemiq_inventory_staging(upload_batch_id);
CREATE INDEX idx_staging_barcode ON chemiq_inventory_staging(company_id, raw_barcode)
    WHERE raw_barcode IS NOT NULL;
CREATE INDEX idx_batches_company ON chemiq_inventory_upload_batches(company_id, processing_status);

API Endpoints

Upload/Capture Endpoints

Method	Endpoint	Description
POST	`/api/v1/chemiq/inventory/staging/photo`	Submit photo capture item
POST	`/api/v1/chemiq/inventory/staging/barcode`	Submit barcode scan item
POST	`/api/v1/chemiq/inventory/staging/excel`	Upload Excel file
POST	`/api/v1/chemiq/inventory/staging/invoice`	Upload invoice for parsing
POST	`/api/v1/chemiq/inventory/staging/from-sds`	Add items from SDS library
POST	`/api/v1/chemiq/inventory/staging/copy-site`	Copy inventory from another site

Batch Management

Method	Endpoint	Description
GET	`/api/v1/chemiq/inventory/staging/batches`	List upload batches
GET	`/api/v1/chemiq/inventory/staging/batches/{id}`	Get batch details
DELETE	`/api/v1/chemiq/inventory/staging/batches/{id}`	Delete entire batch

Staging Item Management

Method	Endpoint	Description
GET	`/api/v1/chemiq/inventory/staging`	List staging items (with filters)
GET	`/api/v1/chemiq/inventory/staging/{id}`	Get staging item details
PUT	`/api/v1/chemiq/inventory/staging/{id}`	Update staging item
POST	`/api/v1/chemiq/inventory/staging/{id}/approve`	Approve single item
POST	`/api/v1/chemiq/inventory/staging/{id}/reject`	Reject single item
POST	`/api/v1/chemiq/inventory/staging/bulk-approve`	Approve multiple items
POST	`/api/v1/chemiq/inventory/staging/bulk-reject`	Reject multiple items

Image Upload

Method	Endpoint	Description
POST	`/api/v1/chemiq/inventory/staging/upload-image`	Upload product image to S3

ChemIQ
├── Inventory
│   ├── All Items (existing)
│   ├── + Add Chemical (existing - single item flow)
│   └── 📥 Quick Import (NEW)
│       ├── 📸 Photo Capture
│       ├── 📱 Barcode Scan
│       ├── 📊 Excel Import
│       ├── 🧾 Invoice Upload
│       ├── 🔗 Add from SDS Library
│       ├── 🔄 Copy from Site
│       └── 📋 Review Queue (badge showing pending count)

Business Rules

Inventory Creation Without SDS

Items CAN be added to final inventory without an SDS attached
Items without SDS are flagged as sds_missing = true
Dashboard shows warning for items missing SDS
Compliance reports highlight items without SDS

Duplicate Handling

Same barcode at different locations: Create separate inventory entries
Same barcode at same location: Merge quantities (or warn and let user decide)
Same product name without barcode: Flag for manual review

Quantity and Units

Store quantity and unit as entered by user
No automatic unit conversion
Optional pack_size field on product catalog for informational conversion display

Data Retention

Staging items: Retained for 90 days after completion/rejection
Rejected items: Can be restored within 30 days
Upload batches: Retained indefinitely for audit trail

Success Metrics

Metric	Target	Measurement
Time to add 100 items	< 30 minutes	Track batch completion time
Items requiring manual correction	< 20%	Track items edited before approval
Auto-match success rate	> 70%	Track items matched automatically
User adoption	> 50% of new companies	Track usage within first 30 days
Completion rate	> 90%	Staging items that become inventory

Service Architecture

Service Responsibilities

This feature spans multiple services in the Tellus EHS platform:

`tellus-ehs-hazcom-service` (Main API Service)

Handles all synchronous HTTP requests:

Upload Endpoints: Receive photos, barcodes, Excel files, invoices
CRUD Operations: Create/read/update staging items and batches
Review Workflows: Approve, reject, bulk operations
Image Upload: Pre-signed S3 URL generation
Immediate Validations: File type, size, required fields
Staging Item Creation: Insert raw captured data into chemiq_inventory_staging
Batch Creation: Create chemiq_inventory_upload_batches records

`tellus-ehs-background-service` (Async Job Processor)

Handles all asynchronous processing that shouldn't block users:

OCR Processing: Extract text from product images (Tesseract/Cloud Vision)
Barcode Lookup: Search product catalog by barcode after item submitted
Product Matching: Fuzzy name matching, confidence scoring
SDS Matching: Link staging items to existing SDS documents
Invoice Parsing: AI extraction of line items from invoice PDFs
Excel Processing: Parse large Excel files, validate rows, create staging items
Batch Status Updates: Update counts and processing status on batches

Processing Flow

┌─────────────────────────────────────────────────────────────────────────────┐
│                           tellus-ehs-hazcom-service                         │
├─────────────────────────────────────────────────────────────────────────────┤
│  User Action                                                                │
│      │                                                                      │
│      ▼                                                                      │
│  API Endpoint (e.g., POST /staging/barcode)                                 │
│      │                                                                      │
│      ├─► Validate request                                                   │
│      ├─► Upload images to S3 (if any)                                       │
│      ├─► Create staging item (status: 'pending_processing')                 │
│      ├─► Create/update batch record                                         │
│      ├─► Queue background job ──────────────────────────────────────────┐   │
│      │                                                                  │   │
│      ▼                                                                  │   │
│  Return 202 Accepted (item_id, batch_id)                                │   │
│                                                                         │   │
└─────────────────────────────────────────────────────────────────────────│───┘
                                                                          │
                                                                          ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                        tellus-ehs-background-service                        │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                             │
│  Job Queue (polls pending jobs)                                             │
│      │                                                                      │
│      ├─► InventoryMatchingJob                                               │
│      │   ├─► Search product catalog by barcode                              │
│      │   ├─► Fuzzy match product name                                       │
│      │   ├─► Search SDS library                                             │
│      │   ├─► Calculate confidence score                                     │
│      │   └─► Update staging item with match results                         │
│      │                                                                      │
│      ├─► OCRExtractionJob                                                   │
│      │   ├─► Download images from S3                                        │
│      │   ├─► Run OCR (Tesseract/Cloud Vision)                               │
│      │   ├─► Extract barcode from image                                     │
│      │   └─► Update staging item with extracted text                        │
│      │                                                                      │
│      ├─► InvoiceParsingJob                                                  │
│      │   ├─► Download invoice PDF/image                                     │
│      │   ├─► Call LLM for line item extraction                              │
│      │   ├─► Create multiple staging items                                  │
│      │   └─► Update batch with extracted items                              │
│      │                                                                      │
│      └─► ExcelProcessingJob                                                 │
│          ├─► Parse Excel/CSV file                                           │
│          ├─► Validate each row                                              │
│          ├─► Create staging items                                           │
│          └─► Update batch status                                            │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────┘

Job Queue Design

Background jobs are stored in a database table (similar to existing sds_parse_jobs):

`chemiq_inventory_processing_jobs`

Column	Type	Description
job_id	UUID	Primary key
company_id	UUID	FK to companies
job_type	VARCHAR(50)	'ocr_extraction', 'product_matching', 'invoice_parsing', 'excel_processing'
staging_id	UUID	FK to staging item (nullable for batch jobs)
batch_id	UUID	FK to batch (nullable for single items)
status	VARCHAR(30)	'pending', 'processing', 'completed', 'failed'
priority	INT	Job priority (higher = process first)
retry_count	INT	Number of retry attempts
error_message	TEXT	Error details if failed
created_at	TIMESTAMPTZ	When job was queued
started_at	TIMESTAMPTZ	When processing started
completed_at	TIMESTAMPTZ	When processing finished

Why This Split?

User Experience: Field workers adding items via barcode/photo get instant feedback
Scalability: Heavy processing (OCR, LLM) doesn't block API requests
Reliability: Failed background jobs can retry without losing user data
Cost Optimization: Batch similar operations (e.g., process all OCR jobs together)
Existing Pattern: Follows the same pattern as SDS parsing (sds_parse_jobs)

Implementation Phases

Phase 1: Core Infrastructure

Staging tables and API
Photo capture with single image
Excel import with template
Basic review dashboard
Background job infrastructure for matching

Phase 2: Enhanced Capture

Multi-photo support (up to 3)
Barcode scanning
OCR text extraction
Auto-matching logic

Phase 3: Advanced Features

Invoice parsing with AI
Add from SDS library
Copy from site
Batch approve/reject

Phase 4: Future Enhancements

Distributor catalog import
Product database search
Mobile-optimized PWA
Offline capture mode

Appendix: Field Notes Examples

Scenario	Example Note
Product condition	"Container is leaking", "Label faded/unreadable"
Quantity uncertainty	"Approx 5 gallons remaining", "Half full"
Location context	"Behind the red cabinet", "Top shelf, hard to reach"
Identification help	"Same as blue cleaner we use in kitchen"
Safety concern	"Stored next to incompatible chemical"
Reorder	"Running low, need to reorder soon"
Verification needed	"Not sure if this is the 32oz or 64oz version"

Overview​

Problem Statement​

Solution Overview​

User Personas​

Field Worker (Capture Role)​

Supervisor (Review Role)​

Import Methods​

1. Photo Capture (Mobile-First)​

2. Barcode Scan (Mobile-First)​

3. Excel/CSV Import​

4. Invoice/PO Upload​

5. Add from SDS Library​

6. Copy from Another Site​

7. Distributor Catalog Import (Future)​

8. Product Database Search (Future)​

Staging Workflow​

Staging Area Concept​

Staging Item States​

Auto-Matching Logic​

Supervisor Review Dashboard​

Batch Review Screen​

Single Item Review/Edit​

Data Model​

New Tables​

chemiq_inventory_upload_batches​

chemiq_inventory_staging​

Indexes​

API Endpoints​

Upload/Capture Endpoints​

Batch Management​

Staging Item Management​

Image Upload​

Navigation Structure​

Business Rules​

Inventory Creation Without SDS​

Duplicate Handling​

Quantity and Units​

Data Retention​

Success Metrics​

Service Architecture​

Service Responsibilities​

tellus-ehs-hazcom-service (Main API Service)​

tellus-ehs-background-service (Async Job Processor)​

Processing Flow​

Job Queue Design​

chemiq_inventory_processing_jobs​

Why This Split?​

Implementation Phases​

Phase 1: Core Infrastructure​

Phase 2: Enhanced Capture​

Phase 3: Advanced Features​

Phase 4: Future Enhancements​

Appendix: Field Notes Examples​

Overview

Problem Statement

Solution Overview

User Personas

Field Worker (Capture Role)

Supervisor (Review Role)

Import Methods

1. Photo Capture (Mobile-First)

2. Barcode Scan (Mobile-First)

3. Excel/CSV Import

4. Invoice/PO Upload

5. Add from SDS Library

6. Copy from Another Site

7. Distributor Catalog Import (Future)

8. Product Database Search (Future)

Staging Workflow

Staging Area Concept

Staging Item States

Auto-Matching Logic

Supervisor Review Dashboard

Batch Review Screen

Single Item Review/Edit

Data Model

New Tables

`chemiq_inventory_upload_batches`

`chemiq_inventory_staging`

Indexes

API Endpoints

Upload/Capture Endpoints

Batch Management

Staging Item Management

Image Upload

Navigation Structure

Business Rules

Inventory Creation Without SDS

Duplicate Handling

Quantity and Units

Data Retention

Success Metrics

Service Architecture

Service Responsibilities

`tellus-ehs-hazcom-service` (Main API Service)

`tellus-ehs-background-service` (Async Job Processor)

Processing Flow

Job Queue Design

`chemiq_inventory_processing_jobs`

Why This Split?

Implementation Phases

Phase 1: Core Infrastructure

Phase 2: Enhanced Capture

Phase 3: Advanced Features

Phase 4: Future Enhancements

Appendix: Field Notes Examples