Skip to main content

chemical-inventory

Here is a clean, modern, developer-ready Markdown rewrite of your entire Chemical Inventory (ChemIQ) Developer Checklist, preserving every requirement but organizing it for engineers, QA, and product in a highly structured, readable format.

🔥 Module 4 — Chemical Inventory (Inventory Management + SDS Maintenance)

Developer Implementation Checklist (Markdown Edition)​

Applies to CHEMIQ module across Starter / Standard / Pro tiers

0. Foundations (Blockers — must be completed first)

Feature Flags​

inventory.barcode  
inventory.ocr  
inventory.bulk_upload  
inventory.ai_sds_match  
inventory.fifra_ppls  
inventory.reviews  
inventory.offline_sync     (Pro)

Service Integrations​

  • OCR provider (Vision API or Tesseract)
  • GS1 / Product DB lookup key
  • SDS web search connectors
  • EPA PPLS API (FIFRA)
  • Object storage for PDFs/images (SDS, label images)
  • Background queue (Sentinel Workers)

Database Migrations​

  • chemicals
  • chemical_attributes
  • sds_documents
  • sds_sections
  • pending_sds_reviews
  • inventory_uploads
  • fifra_labels
  • label_images
  • site_location_index

Object Storage Buckets​

  • sds-pdf
  • label-images
  • inventory-imports With: malware scan + MIME type validation.

Access Control​

  • RLS by company_id and site_id
  • RBAC gates for create/edit/delete
  • Audit on SDS attach/replace

4.1 Purpose (Analytics + Compliance Hooks)

  • Emit events:

    • chemical_created, chemical_updated
    • sds_attached, sds_missing
    • sds_revision_updated
    • bulk_import_started|completed
    • inventory_review_due
    • fifra_label_attached

  • Persist and index: site_id, location_id

  • Ensure SDS is accessible/downloadable per site for OSHA Audits.

Acceptance:

  • All inventory actions appear in analytics.
  • Each chemical is site/location-scoped.

4.2 Site & Location Context

4.2.1 Selection & Filters​

  • User must select Site and optional Location.

  • Filters:

    • All
    • Missing SDS
    • Expiring SDS (≥3 years or per policy)
    • Hazard categories

  • Location hazard summary based on SDS Section 2.

Acceptance:

  • Filtering p95 < 300ms for ≤ 5k chemicals.
  • Hazard summary = SDS data.

4.3 Add Chemical

4.3.1 Manual Entry (Starter)​

Recommended Minimal Fields:

  • Product Name
  • Manufacturer
  • Container Size
  • Quantity (with unit)
  • Notes (optional)
  • Optional SDS Upload (PDF)

Duplicate detection:

  • Same Product + Manufacturer (case/space-insensitive) within the same site.

Tests:

  • Duplicate → inline warning + “view existing”.
  • SDS optional; CAS/Product Code no longer required in UI.

Acceptance:

  • Chemical created correctly, linked to site/location.

4.3.2 Barcode Scan (Starter + Standard)​

  • Accept camera or HID scanner input.

  • Parse UPC/EAN/GS1.

  • Lookup GS1/Product DB → prefill:

    • Product Name
    • Manufacturer
    • Category (if supported)

  • Allow overrides & graceful fallback.

Tests:

  • Unknown barcode → fallback to manual, event: barcode_not_found.
  • Partial data → allow save with warnings.

Acceptance:

  • Prefill works; unknown codes do not block creation.

4.3.3 Picture Label OCR (Standard + Pro)​

  • Upload image → OCR extracts:

    • Product Name
    • Manufacturer
    • Signal Word
    • GHS Pictograms

  • Show confidence scores.

  • Store image in label_images.

Tests:

  • Low-quality → “needs review” + create pending_sds_reviews if SDS missing.
  • Pictograms mapped to standard GHS icons.

Acceptance:

  • OCR prefill works; user edits override; confidences stored.

4.3.4 Additional Attributes​

  • Size/Unit
  • Batch/Lot
  • Manufacture/Expiration Date
  • Storage Conditions

Validation: Compare with SDS Section 1 (name/manufacturer) after SDS attachment.

Acceptance:

  • Block or warn depending on identifier mismatch policy.

4.3.5 Confirm & Save​

  • Create chemical
  • Link to site/location
  • Add to “Recent Additions”
  • Use idempotency key to avoid double creation.

Acceptance:

  • Exactly one record added.

4.4 SDS Attachment & Association

4.4.1 Starter — Web Search​

  • Query external SDS sources using name + manufacturer.
  • Download & store PDF with checksum dedupe.

Acceptance:

  • SDS visible in viewer; source logged.

4.4.2 Standard/Pro — AI SDS Suggestion​

  • Search internal SDS DB + web sources.
  • Rank top 3 with confidence scoring.
  • Display manufacturer snippet + revision date.

Acceptance:

  • Suggestions appear with confidence; user selects or uploads.

4.4.3 Preview / Accept / Upload​

Preview shows:

  • Product Identifier
  • Revision Date
  • Hazard Classification
  • Signal Word
  • Pictograms

User accepts/overrides/upload new SDS.

Acceptance:

  • Attachment completes; version preserved.

4.4.4 SDS Metadata Extraction​

Parse SDS PDF → extract at minimum:

  • Section 1:

    • Product identifiers
    • Manufacturer details
    • Emergency phone

  • Section 2:

    • Hazard Statements (H)
    • Precautionary (P)
    • Signal Word
    • Pictograms

Persist to sds_sections JSON.

Acceptance:

  • Parsed fields visible in UI; usable by Copilot & Insights.

4.5 FIFRA (Pesticides)

4.5.1 Starter — Manual EPA Entry​

  • Input EPA Reg. No.
  • Manual label entry
  • Optional attach label PDF.

Acceptance:

  • EPA Reg. + actives stored.

4.5.2 Standard/Pro — EPA PPLS Integration​

  • Fetch actives, %, signal word, label PDF from EPA.
  • Daily sync job with expiry alerts.

Acceptance:

  • Auto-populated; sync logs; Pro alerts fire.

4.6 Bulk Upload (Standard + Pro)

4.6.1 Upload File​

  • Accept CSV or invoice PDF → AI converts to CSV.
  • Mapping UI to match columns to fields.
  • Validate duplicates and required fields.

Acceptance:

  • Invalid rows flagged clearly.

4.6.2 Review & Validation​

  • Inline edits allowed in preview table.
  • SDS gaps highlighted.

Acceptance:

  • User can correct rows before commit.

4.6.3 SDS Association​

  • Auto-match SDS (internal + web).
  • Unresolved → pending_sds_reviews.

Acceptance:

  • Unresolved count shown.

4.6.4 Accept & Commit​

  • Transactional write to DB.
  • Summary in inventory_uploads.
  • Trigger Sentinel “Missing SDS Alert”.

Acceptance:

  • Either full commit or rollback (except fix-and-retry mode optional).

4.7 Inventory Maintenance & Review

4.7.1 Scheduled Reviews​

  • Annual inventory review (per site).
  • SDS review when SDS ≥ policy age.
  • Inventory Drift detection (12+ months inactivity).

Acceptance:

  • Reminders and dashboards show due items.

4.7.2 Editing & Deactivation​

  • Soft-delete obsolete chemicals.
  • SDS version history maintained.

Acceptance:

  • Historical links preserved.

4.7.3 Change Audit Trail​

  • Log add/edit/deactivate with diffs (Pro).
  • Exportable by site/date.

Acceptance:

  • Admin can export change logs.

4.8 Tier Enforcement

FeatureStarterStandardPro
Manual Entry✅✅✅
Barcode Scan✅✅Enhanced
Web SDS Search✅✅Enhanced
OCR Label❌✅Enhanced
Bulk CSV Import❌✅Enhanced
Internal SDS DB Match❌✅Enhanced
AI Hazard Parsing❌❌✅
Invoice OCR Import❌❌✅
Offline Sync❌❌✅
Sentinel AlertsBasic EmailIn-appMulti-channel

Acceptance:

  • Tier changes immediately update UI and disable/enable features.

4.9 Notifications & Alerts

Alert TypeStarterStandardPro
New Chemical AddedEmailEmail + In-appEmail + Webhook
SDS MissingEmailEmail + In-appEmail + Slack/Webhook
SDS ExpiringEmailEmail + In-appMulti-channel
Inventory Review DueEmailReminder UIAI Insight Email
FIFRA Label Expiry—EmailEmail + EPA Sync Alert

Acceptance:

  • No duplicate notifications
  • Configurable per site.

🔐 Security Checklist

  • Enforce RLS on all tables using company_id, site_id
  • Malware scan + MIME restrict SDS & image uploads
  • SDS text may contain PII — limit indexing, redact in broad views
  • SDS parsing sandboxed in worker with time/memory caps
  • Strict input validation (EPA Reg format, CAS if ever surfaced, etc.)
  • Idempotent create/update endpoints
  • Audit logs for SDS attach/detach with version diffs
  • Secret rotation + API quota monitoring

📊 QA Test Matrix

Happy Paths​

  • Manual add + SDS upload → SDS Section 1/2 visible
  • Barcode scan → prefill → SDS suggest → attach
  • OCR label → pictograms detected → SDS mapped
  • Bulk CSV 100 rows → fix inline → commit → unresolved SDS in queue

Edge Cases​

  • Duplicate detection works
  • Corrupted SDS → graceful failure
  • Conflicting identifiers → warning or block (per policy)
  • Unknown barcode → manual fallback
  • PPLS outage → manual entry
  • Invalid site/location in import → inline map
  • SDS > 20MB → queue parse job

Cross-Browser & Devices​

  • Barcode camera scan on mobile
  • Responsive lists
  • Pro offline: capture then sync

Performance​

  • Inventory list p95 < 500ms (5k rows)
  • SDS parse p95 < 30s
  • Bulk import 1k rows < 90s

🧭 Observability & Alerts

  • Dashboards: SDS parse success rate, OCR confidence, import failures, PPLS health
  • Alerts: spikes in failures, quota nearing, pending reviews > N days

📦 Deliverables (Definition of Done)

  • Figma: Add Chemical, SDS Attach, Bulk Import, Review Screens, Errors
  • OpenAPI specs for CRUD, SDS attach/preview, imports, PPLS sync
  • Localization (EN/ES)
  • E2E tests for all flows
  • Admin tools: re-run parser, re-link SDS, resolve pending reviews
  • Rollback: disable OCR/AI/PPLS via feature flags; manual flows unaffected