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_catalogbybarcode_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%
- Search
-
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 |
Navigation Structure
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_sizefield 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_batchesrecords
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" |