Site Chemical Quantity Aggregation Implementation Guide

Overview

This document provides a comprehensive implementation plan for calculating and tracking chemical quantities at the site level. This feature supports:

EPCRA Tier II Reporting - Annual hazardous chemical inventory reports due March 1
Fire Permit Compliance - Fire departments require maximum chemical quantities on-site
Emergency Planning - LEPC (Local Emergency Planning Committee) needs chemical inventory data
Insurance Requirements - Underwriters often require chemical inventory summaries

Problem Statement
Regulatory Background
Data Flow Architecture
Database Schema
Trigger Points
Calculation Logic
Service Implementation
API Endpoints
Background Jobs
Frontend Integration
Testing Strategy

Problem Statement

Current Limitations

The current inventory system tracks:

Individual container quantities (container_size, size_unit, quantity)
Product-level information via chemiq_company_product_catalog
SDS composition data with chemical percentages

What's Missing:

No site-level aggregation - No pre-computed totals for all chemicals at a site
No actual chemical quantity calculation - Products are mixtures; we need individual chemical amounts
No unit conversion - Units stored as strings (oz, gal, L, kg) without standardization
No threshold tracking - No regulatory threshold alerts for Tier II or fire permits
No hazard category summaries - No aggregation by GHS hazard class at site level

Business Requirements

Stakeholder	Need
Fire Department	Maximum quantities of flammable/combustible liquids, gases, oxidizers
LEPC/SERC	Annual Tier II report with daily max, daily avg, and storage locations
Insurance	Total hazardous chemical inventory for risk assessment
EHS Manager	Dashboard showing chemical quantities approaching thresholds
Operations	Track chemical usage across sites for procurement planning

Regulatory Background

EPCRA Tier II Reporting (SARA Title III, Section 312)

Who Must Report:

Facilities that store hazardous chemicals above Threshold Planning Quantities (TPQ)
Most chemicals: 10,000 lbs (extremely hazardous substances have lower thresholds)

What's Reported:

Data Point	Description
Chemical Name	Common name from SDS
CAS Number	Chemical Abstracts Service registry number
Physical/Health Hazards	From GHS classification
Max Daily Amount	Highest quantity on-site during year (lbs)
Average Daily Amount	Average quantity over the year (lbs)
Days On-Site	Number of days chemical was present
Storage Locations	Building, room, or area descriptions
Storage Codes	Pressure, temperature, and container type codes

Deadline: March 1 annually (for previous calendar year)

Fire Code Requirements (NFPA 1, IFC)

Fire departments require quantity limits for:

Hazard Class	Typical Indoor Limit	Notes
Flammable Liquids (Class I)	120 gallons	Without special storage
Combustible Liquids (Class II)	120 gallons	Without special storage
Oxidizers (Class 1)	4,000 lbs	Higher classes have lower limits
Compressed Gases (Flammable)	500 cu ft	Total aggregate
Corrosives	500 gallons	Both acids and bases

Exceeding limits requires:

Permit from fire marshal
Additional fire suppression
Special storage cabinets
Annual inspection

Data Flow Architecture

Chemical Quantity Calculation Flow

┌─────────────────────────────────────────────────────────────────────────────────┐
│                    SITE CHEMICAL QUANTITY AGGREGATION FLOW                       │
└─────────────────────────────────────────────────────────────────────────────────┘

                                    INPUTS
  ┌──────────────┐     ┌──────────────┐     ┌──────────────┐
  │   Inventory  │     │     SDS      │     │    Unit      │
  │    Items     │     │  Composition │     │  Conversion  │
  │              │     │              │     │   Reference  │
  │ • site_id    │     │ • cas_number │     │              │
  │ • product_id │     │ • conc_%     │     │ oz → lbs     │
  │ • container  │     │ • is_hazard  │     │ gal → lbs    │
  │ • quantity   │     │              │     │ L → kg       │
  └──────┬───────┘     └──────┬───────┘     └──────┬───────┘
         │                    │                    │
         └────────────────────┼────────────────────┘
                              │
                              ▼
              ┌───────────────────────────────────┐
              │     AGGREGATION CALCULATION       │
              │                                   │
              │  For each (site, chemical):       │
              │  1. Sum all inventory items       │
              │  2. Convert to standard unit      │
              │  3. Apply composition %           │
              │  4. Calculate total chemical qty  │
              └───────────────┬───────────────────┘
                              │
                              ▼
              ┌───────────────────────────────────┐
              │      STORAGE (3 Tables)           │
              │                                   │
              │  1. chemiq_site_chemical_summary  │
              │     (per chemical per site)       │
              │                                   │
              │  2. chemiq_site_hazard_summary    │
              │     (per hazard class per site)   │
              │                                   │
              │  3. chemiq_site_quantity_history  │
              │     (daily snapshots for Tier II) │
              └───────────────┬───────────────────┘
                              │
                              ▼
                           OUTPUTS
  ┌──────────────┐     ┌──────────────┐     ┌──────────────┐
  │   Tier II    │     │ Fire Permit  │     │  Dashboard   │
  │   Report     │     │   Report     │     │   Widgets    │
  │              │     │              │     │              │
  │ • Max daily  │     │ • By hazard  │     │ • Threshold  │
  │ • Avg daily  │     │ • Total qty  │     │   alerts     │
  │ • Days onsite│     │ • Locations  │     │ • Trends     │
  └──────────────┘     └──────────────┘     └──────────────┘

Trigger Points Overview

┌─────────────────────────────────────────────────────────────────────────────────┐
│                     QUANTITY AGGREGATION TRIGGER POINTS                          │
└─────────────────────────────────────────────────────────────────────────────────┘

  ┌──────────────┐     ┌──────────────┐     ┌──────────────┐
  │  TRIGGER #1  │     │  TRIGGER #2  │     │  TRIGGER #3  │
  │  Inventory   │     │  SDS/Product │     │  Daily Cron  │
  │  Change      │     │  Update      │     │  Snapshot    │
  └──────┬───────┘     └──────┬───────┘     └──────┬───────┘
         │                    │                    │
         │ • Add item        │ • SDS parsed       │ • Record daily
         │ • Update qty      │ • Composition      │   max/current
         │ • Delete item     │   changed          │ • Archive to
         │ • Move to site    │ • Product linked   │   history table
         │                    │   to new SDS      │
         │                    │                    │
         ▼                    ▼                    ▼
  ┌─────────────────────────────────────────────────────────────────────────┐
  │                    RECALCULATE SITE SUMMARIES                            │
  │                                                                          │
  │  • chemiq_site_chemical_summary (incremental or full recalc)            │
  │  • chemiq_site_hazard_summary (aggregated from chemical summary)         │
  └─────────────────────────────────────────────────────────────────────────┘
         │
         ▼
  ┌──────────────┐
  │  TRIGGER #4  │
  │  Threshold   │
  │  Check       │
  └──────────────┘
         │
         │ If quantity > threshold:
         │ • Create notification
         │ • Update compliance status
         │ • Flag for dashboard
         │
         ▼

Database Schema

1. Unit Conversion Reference Table

-- Reference table for unit conversions
-- Normalizes all quantities to standard units (lbs for weight, gallons for volume)
CREATE TABLE chemiq_unit_conversions (
    conversion_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Unit identification
    unit_code VARCHAR(20) NOT NULL,       -- 'oz', 'gal', 'L', 'kg', 'mL', 'lb'
    unit_name VARCHAR(50) NOT NULL,       -- 'Ounce', 'Gallon', 'Liter', etc.
    unit_type VARCHAR(20) NOT NULL,       -- 'weight', 'volume'

    -- Conversion factors to standard units
    -- Weight: convert to pounds (lbs)
    -- Volume: convert to gallons (gal)
    to_standard_factor DECIMAL(15,10) NOT NULL,  -- multiply by this to get standard unit
    standard_unit VARCHAR(20) NOT NULL,          -- 'lbs' or 'gal'

    -- Display
    abbreviation VARCHAR(10) NOT NULL,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    UNIQUE(unit_code)
);

-- Seed common units
INSERT INTO chemiq_unit_conversions (unit_code, unit_name, unit_type, to_standard_factor, standard_unit, abbreviation) VALUES
-- Weight conversions (to lbs)
('lb', 'Pound', 'weight', 1.0, 'lbs', 'lb'),
('lbs', 'Pounds', 'weight', 1.0, 'lbs', 'lbs'),
('oz', 'Ounce', 'weight', 0.0625, 'lbs', 'oz'),
('kg', 'Kilogram', 'weight', 2.20462, 'lbs', 'kg'),
('g', 'Gram', 'weight', 0.00220462, 'lbs', 'g'),
('mg', 'Milligram', 'weight', 0.00000220462, 'lbs', 'mg'),
('ton', 'Ton (US)', 'weight', 2000.0, 'lbs', 'ton'),

-- Volume conversions (to gallons)
('gal', 'Gallon', 'volume', 1.0, 'gal', 'gal'),
('qt', 'Quart', 'volume', 0.25, 'gal', 'qt'),
('pt', 'Pint', 'volume', 0.125, 'gal', 'pt'),
('fl_oz', 'Fluid Ounce', 'volume', 0.0078125, 'gal', 'fl oz'),
('L', 'Liter', 'volume', 0.264172, 'gal', 'L'),
('mL', 'Milliliter', 'volume', 0.000264172, 'gal', 'mL'),
('cu_ft', 'Cubic Foot', 'volume', 7.48052, 'gal', 'cu ft');

CREATE INDEX idx_unit_conversions_code ON chemiq_unit_conversions(unit_code);

2. Product Density Reference Table

-- Density reference for volume-to-weight conversions
-- Required for Tier II reporting (quantities in lbs)
CREATE TABLE chemiq_product_density (
    density_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Product identification (can be by CAS or product)
    cas_number VARCHAR(20),
    product_id UUID REFERENCES chemiq_company_product_catalog(product_id),

    -- Density data
    density_value DECIMAL(10,4) NOT NULL,  -- e.g., 8.34 for water
    density_unit VARCHAR(20) NOT NULL,     -- 'lbs_per_gal', 'kg_per_L', 'g_per_mL'

    -- Standard density in lbs/gal
    density_lbs_per_gal DECIMAL(10,4) NOT NULL,

    -- Source
    data_source VARCHAR(100),  -- 'SDS', 'PubChem', 'Manual'

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    UNIQUE(cas_number),
    UNIQUE(product_id)
);

-- Water baseline (most common)
INSERT INTO chemiq_product_density (cas_number, density_value, density_unit, density_lbs_per_gal, data_source)
VALUES ('7732-18-5', 8.34, 'lbs_per_gal', 8.34, 'Reference');

CREATE INDEX idx_product_density_cas ON chemiq_product_density(cas_number);
CREATE INDEX idx_product_density_product ON chemiq_product_density(product_id);

3. Site Chemical Summary Table (Main Aggregation)

-- Pre-computed chemical quantities per site
-- Updated on inventory changes; used for dashboards and reports
CREATE TABLE chemiq_site_chemical_summary (
    summary_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Scope
    company_id UUID NOT NULL REFERENCES core_companies(company_id),
    site_id UUID NOT NULL REFERENCES core_company_sites(site_id),

    -- Chemical identification
    cas_number VARCHAR(20) NOT NULL,
    chemical_name VARCHAR(255) NOT NULL,

    -- Aggregated quantities (in standard units)
    total_quantity_lbs DECIMAL(15,4),       -- Total weight in pounds
    total_quantity_gal DECIMAL(15,4),       -- Total volume in gallons (if liquid)

    -- Container counts
    total_containers INT DEFAULT 0,          -- Number of containers

    -- Products contributing to this chemical
    product_count INT DEFAULT 0,             -- Distinct products containing this chemical
    contributing_products JSONB,             -- Array of {product_id, product_name, contribution_lbs}

    -- Hazard classification (from SDS or regulatory lists)
    is_hazardous BOOLEAN DEFAULT FALSE,
    hazard_classes JSONB,                    -- GHS hazard classes array
    physical_state VARCHAR(20),              -- 'solid', 'liquid', 'gas'

    -- Tier II specific fields
    max_daily_quantity_lbs DECIMAL(15,4),    -- Highest point during year
    avg_daily_quantity_lbs DECIMAL(15,4),    -- Average over days present
    days_on_site INT DEFAULT 0,              -- Days chemical was present (for avg calc)

    -- Fire code classification
    fire_code_category VARCHAR(50),          -- 'flammable_liquid', 'oxidizer', etc.
    fire_code_class VARCHAR(20),             -- 'I', 'II', 'III' for liquids

    -- Storage locations (for Tier II)
    storage_locations JSONB,                 -- Array of {location_id, location_name, quantity_lbs}

    -- Regulatory thresholds
    tier2_threshold_lbs INT,                 -- EPCRA threshold (usually 10,000)
    fire_permit_threshold DECIMAL(15,4),     -- Fire code limit
    exceeds_tier2_threshold BOOLEAN DEFAULT FALSE,
    exceeds_fire_threshold BOOLEAN DEFAULT FALSE,

    -- Metadata
    last_calculated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    calculation_method VARCHAR(50) DEFAULT 'incremental', -- 'incremental', 'full_recalc'

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    -- Unique constraint: one record per chemical per site
    UNIQUE(site_id, cas_number)
);

-- Indexes for common queries
CREATE INDEX idx_site_chem_summary_company ON chemiq_site_chemical_summary(company_id);
CREATE INDEX idx_site_chem_summary_site ON chemiq_site_chemical_summary(site_id);
CREATE INDEX idx_site_chem_summary_cas ON chemiq_site_chemical_summary(cas_number);
CREATE INDEX idx_site_chem_summary_hazardous ON chemiq_site_chemical_summary(site_id, is_hazardous)
    WHERE is_hazardous = TRUE;
CREATE INDEX idx_site_chem_summary_exceeds ON chemiq_site_chemical_summary(site_id, exceeds_tier2_threshold)
    WHERE exceeds_tier2_threshold = TRUE;
CREATE INDEX idx_site_chem_summary_fire ON chemiq_site_chemical_summary(site_id, exceeds_fire_threshold)
    WHERE exceeds_fire_threshold = TRUE;

4. Site Hazard Summary Table (Hazard Class Aggregation)

-- Aggregated quantities by hazard class per site
-- Used for fire permit calculations and hazard overview dashboards
CREATE TABLE chemiq_site_hazard_summary (
    summary_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Scope
    company_id UUID NOT NULL REFERENCES core_companies(company_id),
    site_id UUID NOT NULL REFERENCES core_company_sites(site_id),

    -- Hazard classification
    hazard_category VARCHAR(50) NOT NULL,    -- 'health', 'physical', 'environmental'
    hazard_class VARCHAR(100) NOT NULL,      -- 'Flammable liquid', 'Acute toxicity', etc.
    ghs_code VARCHAR(20),                    -- 'GHS02', 'GHS06', etc.

    -- Fire code classification (for physical hazards)
    fire_code_category VARCHAR(50),          -- 'flammable_liquid', 'combustible_liquid', etc.
    fire_code_class VARCHAR(20),             -- 'IA', 'IB', 'IC', 'II', 'IIIA', 'IIIB'

    -- Aggregated quantities
    total_quantity_lbs DECIMAL(15,4),
    total_quantity_gal DECIMAL(15,4),
    total_containers INT DEFAULT 0,
    chemical_count INT DEFAULT 0,            -- Distinct chemicals in this class

    -- Chemicals contributing to this hazard class
    chemicals JSONB,                         -- Array of {cas_number, name, quantity_lbs}

    -- Threshold tracking
    fire_permit_limit DECIMAL(15,4),         -- From fire code
    exceeds_permit_limit BOOLEAN DEFAULT FALSE,
    percent_of_limit DECIMAL(5,2),           -- e.g., 85.5% of limit

    -- Metadata
    last_calculated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    UNIQUE(site_id, hazard_class, fire_code_class)
);

CREATE INDEX idx_site_hazard_summary_site ON chemiq_site_hazard_summary(site_id);
CREATE INDEX idx_site_hazard_summary_category ON chemiq_site_hazard_summary(site_id, hazard_category);
CREATE INDEX idx_site_hazard_summary_exceeds ON chemiq_site_hazard_summary(site_id, exceeds_permit_limit)
    WHERE exceeds_permit_limit = TRUE;

5. Site Quantity History Table (Daily Snapshots for Tier II)

-- Daily snapshots of site chemical quantities
-- Required for Tier II "Max Daily" and "Average Daily" calculations
CREATE TABLE chemiq_site_quantity_history (
    history_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Scope
    company_id UUID NOT NULL REFERENCES core_companies(company_id),
    site_id UUID NOT NULL REFERENCES core_company_sites(site_id),

    -- Chemical identification
    cas_number VARCHAR(20) NOT NULL,
    chemical_name VARCHAR(255) NOT NULL,

    -- Snapshot date
    snapshot_date DATE NOT NULL,

    -- Quantities on this date
    quantity_lbs DECIMAL(15,4),
    quantity_gal DECIMAL(15,4),
    container_count INT,

    -- Whether chemical was present
    is_present BOOLEAN DEFAULT TRUE,

    -- Source record
    source VARCHAR(50) DEFAULT 'daily_snapshot', -- 'daily_snapshot', 'inventory_change', 'manual'

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    -- Unique: one record per chemical per site per day
    UNIQUE(site_id, cas_number, snapshot_date)
);

-- Indexes for Tier II report generation
CREATE INDEX idx_site_qty_history_site_date ON chemiq_site_quantity_history(site_id, snapshot_date);
CREATE INDEX idx_site_qty_history_cas ON chemiq_site_quantity_history(cas_number);
CREATE INDEX idx_site_qty_history_year ON chemiq_site_quantity_history(site_id, cas_number, snapshot_date);

-- Partitioning by year recommended for large datasets
-- ALTER TABLE chemiq_site_quantity_history PARTITION BY RANGE (snapshot_date);

6. Fire Code Threshold Reference Table

-- Reference table for fire code quantity limits
-- Based on IFC/NFPA 1 storage limits (varies by jurisdiction)
CREATE TABLE chemiq_fire_code_thresholds (
    threshold_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Classification
    hazard_category VARCHAR(50) NOT NULL,    -- 'flammable_liquid', 'oxidizer', etc.
    hazard_class VARCHAR(20),                -- 'IA', 'IB', 'IC', 'II', etc.

    -- Storage type
    storage_type VARCHAR(50) NOT NULL,       -- 'indoor_unprotected', 'indoor_cabinet', 'outdoor'

    -- Quantity limits
    max_quantity_gal DECIMAL(10,2),          -- For liquids
    max_quantity_lbs DECIMAL(10,2),          -- For solids
    max_quantity_cu_ft DECIMAL(10,2),        -- For gases

    -- Source
    code_reference VARCHAR(100),             -- 'IFC Table 5003.1.1(1)', 'NFPA 30 Table 9.5.1'
    jurisdiction VARCHAR(50) DEFAULT 'general', -- Can customize per state/city

    -- Notes
    notes TEXT,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    UNIQUE(hazard_category, hazard_class, storage_type, jurisdiction)
);

-- Seed common fire code limits (IFC defaults)
INSERT INTO chemiq_fire_code_thresholds
(hazard_category, hazard_class, storage_type, max_quantity_gal, code_reference, notes) VALUES
-- Flammable Liquids
('flammable_liquid', 'IA', 'indoor_unprotected', 30, 'IFC Table 5003.1.1(1)', 'Flash point < 73°F, BP < 100°F'),
('flammable_liquid', 'IB', 'indoor_unprotected', 120, 'IFC Table 5003.1.1(1)', 'Flash point < 73°F, BP >= 100°F'),
('flammable_liquid', 'IC', 'indoor_unprotected', 120, 'IFC Table 5003.1.1(1)', 'Flash point 73-100°F'),
('flammable_liquid', 'IA', 'indoor_cabinet', 60, 'IFC Table 5003.1.1(1)', 'Approved flammable cabinet'),
('flammable_liquid', 'IB', 'indoor_cabinet', 120, 'IFC Table 5003.1.1(1)', 'Approved flammable cabinet'),
('flammable_liquid', 'IC', 'indoor_cabinet', 120, 'IFC Table 5003.1.1(1)', 'Approved flammable cabinet'),

-- Combustible Liquids
('combustible_liquid', 'II', 'indoor_unprotected', 120, 'IFC Table 5003.1.1(1)', 'Flash point 100-140°F'),
('combustible_liquid', 'IIIA', 'indoor_unprotected', 330, 'IFC Table 5003.1.1(1)', 'Flash point 140-200°F'),
('combustible_liquid', 'IIIB', 'indoor_unprotected', 13200, 'IFC Table 5003.1.1(1)', 'Flash point >= 200°F');

-- Add oxidizers (lbs)
INSERT INTO chemiq_fire_code_thresholds
(hazard_category, hazard_class, storage_type, max_quantity_lbs, code_reference, notes) VALUES
('oxidizer', '1', 'indoor_unprotected', 4000, 'IFC Table 5003.1.1(1)', 'Class 1 oxidizer'),
('oxidizer', '2', 'indoor_unprotected', 250, 'IFC Table 5003.1.1(1)', 'Class 2 oxidizer'),
('oxidizer', '3', 'indoor_unprotected', 10, 'IFC Table 5003.1.1(1)', 'Class 3 oxidizer'),
('oxidizer', '4', 'indoor_unprotected', 1, 'IFC Table 5003.1.1(1)', 'Class 4 oxidizer - any amount');

CREATE INDEX idx_fire_thresholds_category ON chemiq_fire_code_thresholds(hazard_category, hazard_class);

Trigger Points

Trigger #1: Inventory Changes (Real-Time)

When:

Inventory item created
Inventory item updated (quantity, container_size, size_unit)
Inventory item deleted
Inventory item moved to different site

What Happens:

Identify affected site(s)
Get product's SDS composition (chemicals + percentages)
Calculate delta for each chemical
Update chemiq_site_chemical_summary incrementally
Recalculate chemiq_site_hazard_summary for affected hazard classes
Check thresholds and create notifications if exceeded

Implementation:

# app/services/site_quantity_service.py

from typing import Optional, List
from uuid import UUID
from sqlalchemy.orm import Session
from datetime import datetime, date

from app.db.models.chemiq_inventory import ChemIQInventory
from app.db.models.chemiq_sds import SDSComposition
from app.db.models.site_quantity import (
    ChemiqSiteChemicalSummary,
    ChemiqSiteHazardSummary,
    ChemiqSiteQuantityHistory
)


class SiteQuantityService:
    """Service for calculating and tracking site-level chemical quantities."""

    def __init__(self, db: Session):
        self.db = db

    async def on_inventory_change(
        self,
        inventory_item: ChemIQInventory,
        change_type: str,  # 'create', 'update', 'delete', 'move'
        old_values: Optional[dict] = None
    ):
        """
        TRIGGER #1: Handle inventory item changes.

        This is the primary trigger for quantity updates.
        Called from inventory service after any CRUD operation.
        """
        site_id = inventory_item.site_id
        company_id = inventory_item.company_id

        # Get product's SDS composition
        product = inventory_item.company_product
        if not product or not product.current_sds_id:
            return  # No SDS = no composition data

        composition = self.db.query(SDSComposition).filter(
            SDSComposition.sds_id == product.current_sds_id
        ).all()

        if not composition:
            return

        # Calculate quantity for this inventory item
        item_quantity_lbs = await self._calculate_item_quantity_lbs(inventory_item)

        # Calculate delta per chemical
        for component in composition:
            if not component.cas_number:
                continue

            concentration = self._get_concentration(component)
            chemical_quantity_lbs = item_quantity_lbs * (concentration / 100)

            if change_type == 'create':
                await self._add_chemical_quantity(
                    site_id, company_id, component, chemical_quantity_lbs
                )
            elif change_type == 'delete':
                await self._subtract_chemical_quantity(
                    site_id, component.cas_number, chemical_quantity_lbs
                )
            elif change_type == 'update':
                # Calculate old quantity and delta
                old_quantity_lbs = self._calculate_old_quantity_lbs(old_values)
                old_chemical_lbs = old_quantity_lbs * (concentration / 100)
                delta = chemical_quantity_lbs - old_chemical_lbs
                await self._update_chemical_quantity(
                    site_id, component.cas_number, delta
                )
            elif change_type == 'move':
                # Handle site change
                old_site_id = old_values.get('site_id')
                await self._subtract_chemical_quantity(
                    old_site_id, component.cas_number, chemical_quantity_lbs
                )
                await self._add_chemical_quantity(
                    site_id, company_id, component, chemical_quantity_lbs
                )

        # Update hazard summaries
        await self._recalculate_hazard_summaries(site_id)

        # Check thresholds
        await self._check_thresholds(site_id, company_id)

        # Record change in history (for intra-day tracking)
        await self._record_quantity_change(site_id, change_type)

Trigger #2: SDS/Product Updates

When:

New SDS is parsed and composition data is available
SDS is updated (new version with different composition)
Product is linked to a different SDS
Composition data is manually corrected

What Happens:

Identify all inventory items using this product/SDS
Full recalculation for affected sites (not incremental)
Update chemiq_site_chemical_summary with new composition
Recalculate hazard summaries

Implementation:

async def on_sds_composition_change(
    self,
    sds_id: UUID,
    company_id: UUID
):
    """
    TRIGGER #2: Handle SDS composition changes.

    Called when:
    - SDS parsing completes with new composition data
    - SDS composition is manually updated
    - Product is linked to a different SDS
    """
    # Find all products using this SDS
    products_with_sds = self.db.query(CompanyProductCatalog).filter(
        CompanyProductCatalog.current_sds_id == sds_id,
        CompanyProductCatalog.company_id == company_id
    ).all()

    if not products_with_sds:
        return

    product_ids = [p.product_id for p in products_with_sds]

    # Find all inventory items using these products
    inventory_items = self.db.query(ChemIQInventory).filter(
        ChemIQInventory.company_product_id.in_(product_ids)
    ).all()

    # Group by site
    sites_to_recalculate = set()
    for item in inventory_items:
        sites_to_recalculate.add(item.site_id)

    # Full recalculation for affected sites
    for site_id in sites_to_recalculate:
        await self.full_site_recalculation(site_id, company_id)

Trigger #3: Daily Snapshot (Scheduled Job)

When:

Daily at midnight (configurable)
End of business day for fire permit tracking

What Happens:

For each active site, snapshot current quantities
Store in chemiq_site_quantity_history
Update max_daily and avg_daily in summary table
Check for year-to-date max (for Tier II)

Implementation:

async def daily_quantity_snapshot(self):
    """
    TRIGGER #3: Daily snapshot for Tier II reporting.

    Run via cron at midnight local time per site.
    Records current quantities for max/avg calculations.
    """
    today = date.today()

    # Get all active site chemical summaries
    summaries = self.db.query(ChemiqSiteChemicalSummary).filter(
        ChemiqSiteChemicalSummary.total_quantity_lbs > 0
    ).all()

    for summary in summaries:
        # Record snapshot
        history_entry = ChemiqSiteQuantityHistory(
            company_id=summary.company_id,
            site_id=summary.site_id,
            cas_number=summary.cas_number,
            chemical_name=summary.chemical_name,
            snapshot_date=today,
            quantity_lbs=summary.total_quantity_lbs,
            quantity_gal=summary.total_quantity_gal,
            container_count=summary.total_containers,
            is_present=summary.total_quantity_lbs > 0
        )
        self.db.add(history_entry)

        # Update max daily if this is higher
        if summary.total_quantity_lbs > (summary.max_daily_quantity_lbs or 0):
            summary.max_daily_quantity_lbs = summary.total_quantity_lbs

        # Update days on site
        summary.days_on_site = (summary.days_on_site or 0) + 1

        # Recalculate average
        year_start = date(today.year, 1, 1)
        history_entries = self.db.query(ChemiqSiteQuantityHistory).filter(
            ChemiqSiteQuantityHistory.site_id == summary.site_id,
            ChemiqSiteQuantityHistory.cas_number == summary.cas_number,
            ChemiqSiteQuantityHistory.snapshot_date >= year_start,
            ChemiqSiteQuantityHistory.is_present == True
        ).all()

        if history_entries:
            total_qty = sum(h.quantity_lbs or 0 for h in history_entries)
            summary.avg_daily_quantity_lbs = total_qty / len(history_entries)

    self.db.commit()

Trigger #4: Threshold Alerts

When:

After any quantity update (from triggers #1, #2, or #3)

What Happens:

Compare quantities against Tier II thresholds (10,000 lbs default)
Compare against fire code limits (by hazard class)
Create notifications for:
- Threshold exceeded (first time)
- Approaching threshold (80%, 90%)
- Quarterly reminder for ongoing exceedance
Update compliance dashboard

Implementation:

async def _check_thresholds(
    self,
    site_id: UUID,
    company_id: UUID
):
    """
    TRIGGER #4: Check regulatory thresholds.

    Called after quantity updates to check:
    - EPCRA Tier II threshold (10,000 lbs)
    - Fire code limits by hazard class
    """
    # Get site summaries exceeding thresholds
    summaries = self.db.query(ChemiqSiteChemicalSummary).filter(
        ChemiqSiteChemicalSummary.site_id == site_id
    ).all()

    for summary in summaries:
        # Check Tier II threshold
        tier2_threshold = summary.tier2_threshold_lbs or 10000
        was_exceeding = summary.exceeds_tier2_threshold
        now_exceeding = (summary.total_quantity_lbs or 0) >= tier2_threshold

        if now_exceeding and not was_exceeding:
            # Newly exceeded - create notification
            await self._create_threshold_notification(
                company_id=company_id,
                site_id=site_id,
                chemical_name=summary.chemical_name,
                cas_number=summary.cas_number,
                threshold_type='tier2',
                current_quantity=summary.total_quantity_lbs,
                threshold_quantity=tier2_threshold
            )

        summary.exceeds_tier2_threshold = now_exceeding

        # Check fire permit threshold
        if summary.fire_permit_threshold:
            was_exceeding_fire = summary.exceeds_fire_threshold
            now_exceeding_fire = (summary.total_quantity_lbs or 0) >= summary.fire_permit_threshold

            if now_exceeding_fire and not was_exceeding_fire:
                await self._create_threshold_notification(
                    company_id=company_id,
                    site_id=site_id,
                    chemical_name=summary.chemical_name,
                    cas_number=summary.cas_number,
                    threshold_type='fire_permit',
                    current_quantity=summary.total_quantity_lbs,
                    threshold_quantity=summary.fire_permit_threshold
                )

            summary.exceeds_fire_threshold = now_exceeding_fire

        # Check approaching thresholds (80%, 90%)
        pct_of_tier2 = ((summary.total_quantity_lbs or 0) / tier2_threshold) * 100
        if 80 <= pct_of_tier2 < 90:
            await self._create_approaching_notification(
                company_id, site_id, summary, 80, 'tier2'
            )
        elif 90 <= pct_of_tier2 < 100:
            await self._create_approaching_notification(
                company_id, site_id, summary, 90, 'tier2'
            )

    self.db.commit()

Trigger Summary Table

Trigger	When	Calculation	Frequency	Priority
#1 Inventory Change	CRUD on inventory	Incremental delta	Real-time	High
#2 SDS/Composition	SDS parsed/updated	Full site recalc	Event-driven	High
#3 Daily Snapshot	Midnight cron	Record current qty	Daily	Medium
#4 Threshold Check	After #1, #2, #3	Compare vs limits	Real-time	High

Calculation Logic

Unit Conversion Algorithm

async def _calculate_item_quantity_lbs(
    self,
    inventory_item: ChemIQInventory
) -> float:
    """
    Convert inventory item quantity to pounds.

    Handles both weight and volume units.
    Volume requires density lookup for conversion.
    """
    container_size = float(inventory_item.container_size or 0)
    quantity = inventory_item.quantity or 1
    size_unit = (inventory_item.size_unit or '').lower()

    total_amount = container_size * quantity

    if total_amount == 0:
        return 0

    # Look up unit conversion
    conversion = self.db.query(ChemiqUnitConversion).filter(
        ChemiqUnitConversion.unit_code == size_unit
    ).first()

    if not conversion:
        # Default: assume already in lbs
        return total_amount

    if conversion.unit_type == 'weight':
        # Direct conversion to lbs
        return total_amount * float(conversion.to_standard_factor)

    elif conversion.unit_type == 'volume':
        # Convert to gallons first
        total_gallons = total_amount * float(conversion.to_standard_factor)

        # Then convert gallons to lbs using density
        density = await self._get_product_density(inventory_item.company_product_id)
        return total_gallons * density

    return total_amount


async def _get_product_density(self, product_id: UUID) -> float:
    """
    Get density for volume-to-weight conversion.

    Priority:
    1. Product-specific density
    2. CAS-based density from composition
    3. Default (water = 8.34 lbs/gal)
    """
    # Check product-specific density
    product_density = self.db.query(ChemiqProductDensity).filter(
        ChemiqProductDensity.product_id == product_id
    ).first()

    if product_density:
        return float(product_density.density_lbs_per_gal)

    # Check SDS for density info
    product = self.db.query(CompanyProductCatalog).filter(
        CompanyProductCatalog.product_id == product_id
    ).first()

    if product and product.current_sds_id:
        sds = self.db.query(SDSDocument).filter(
            SDSDocument.sds_id == product.current_sds_id
        ).first()

        if sds and sds.parsed_json:
            section9 = sds.parsed_json.get('section9_physical_chemical', {})
            density = section9.get('relative_density') or section9.get('specific_gravity')
            if density:
                # Convert specific gravity to lbs/gal (SG * 8.34)
                return float(density) * 8.34

    # Default: water density
    return 8.34

Chemical Quantity from Composition

def _get_concentration(self, component: SDSComposition) -> float:
    """
    Get concentration percentage for a composition component.

    Priority:
    1. concentration_exact
    2. Midpoint of concentration_min/max range
    3. Parse concentration_text
    4. Default to 100% (pure substance)
    """
    if component.concentration_exact:
        return float(component.concentration_exact)

    if component.concentration_min and component.concentration_max:
        return (float(component.concentration_min) + float(component.concentration_max)) / 2

    if component.concentration_min:
        return float(component.concentration_min)

    if component.concentration_max:
        return float(component.concentration_max)

    if component.concentration_text:
        # Parse text like "< 5%", "10-20%", "≤ 1%"
        return self._parse_concentration_text(component.concentration_text)

    # Default: assume 100% (single chemical product)
    return 100.0


def _parse_concentration_text(self, text: str) -> float:
    """Parse concentration from text representation."""
    import re

    text = text.strip().replace('%', '').replace(' ', '')

    # Handle ranges like "10-20"
    range_match = re.match(r'(\d+\.?\d*)-(\d+\.?\d*)', text)
    if range_match:
        min_val = float(range_match.group(1))
        max_val = float(range_match.group(2))
        return (min_val + max_val) / 2

    # Handle "< X" or "<X"
    lt_match = re.match(r'[<≤](\d+\.?\d*)', text)
    if lt_match:
        return float(lt_match.group(1)) / 2  # Conservative: use half

    # Handle "> X" or ">X"
    gt_match = re.match(r'[>≥](\d+\.?\d*)', text)
    if gt_match:
        return float(gt_match.group(1))  # Use minimum

    # Handle plain number
    num_match = re.match(r'(\d+\.?\d*)', text)
    if num_match:
        return float(num_match.group(1))

    return 0.0

API Endpoints

Site Chemical Summary Endpoints

# app/api/v1/chemiq/site_quantities.py

from fastapi import APIRouter, Depends, Query
from typing import Optional, List
from uuid import UUID

router = APIRouter()


@router.get("/sites/{site_id}/chemical-summary")
async def get_site_chemical_summary(
    site_id: UUID,
    hazardous_only: bool = Query(False, description="Filter to hazardous chemicals only"),
    exceeds_threshold: bool = Query(False, description="Filter to chemicals exceeding thresholds"),
    ctx: UserContext = Depends(get_user_context),
    db: Session = Depends(get_db)
):
    """
    Get chemical quantity summary for a site.

    Returns aggregated quantities for all chemicals at the site,
    including Tier II and fire permit threshold status.
    """
    query = db.query(ChemiqSiteChemicalSummary).filter(
        ChemiqSiteChemicalSummary.site_id == site_id,
        ChemiqSiteChemicalSummary.company_id == ctx.company_id
    )

    if hazardous_only:
        query = query.filter(ChemiqSiteChemicalSummary.is_hazardous == True)

    if exceeds_threshold:
        query = query.filter(
            db.or_(
                ChemiqSiteChemicalSummary.exceeds_tier2_threshold == True,
                ChemiqSiteChemicalSummary.exceeds_fire_threshold == True
            )
        )

    summaries = query.order_by(
        ChemiqSiteChemicalSummary.total_quantity_lbs.desc()
    ).all()

    return {
        'site_id': site_id,
        'total_chemicals': len(summaries),
        'chemicals_exceeding_tier2': sum(1 for s in summaries if s.exceeds_tier2_threshold),
        'chemicals_exceeding_fire': sum(1 for s in summaries if s.exceeds_fire_threshold),
        'chemicals': [SiteChemicalSummaryResponse.from_orm(s) for s in summaries]
    }


@router.get("/sites/{site_id}/hazard-summary")
async def get_site_hazard_summary(
    site_id: UUID,
    ctx: UserContext = Depends(get_user_context),
    db: Session = Depends(get_db)
):
    """
    Get hazard class summary for a site.

    Returns aggregated quantities by GHS hazard class,
    useful for fire permit applications.
    """
    summaries = db.query(ChemiqSiteHazardSummary).filter(
        ChemiqSiteHazardSummary.site_id == site_id,
        ChemiqSiteHazardSummary.company_id == ctx.company_id
    ).order_by(
        ChemiqSiteHazardSummary.hazard_category,
        ChemiqSiteHazardSummary.hazard_class
    ).all()

    return {
        'site_id': site_id,
        'hazard_summaries': [SiteHazardSummaryResponse.from_orm(s) for s in summaries]
    }


@router.get("/sites/{site_id}/tier2-report-data")
async def get_tier2_report_data(
    site_id: UUID,
    year: int = Query(..., description="Reporting year"),
    ctx: UserContext = Depends(get_user_context),
    db: Session = Depends(get_db)
):
    """
    Get Tier II report data for a site.

    Returns:
    - Chemical list with max daily, avg daily, days on site
    - Storage locations
    - Hazard classifications
    - Threshold status
    """
    service = SiteQuantityService(db)
    return await service.generate_tier2_report_data(
        site_id=site_id,
        company_id=ctx.company_id,
        year=year
    )


@router.post("/sites/{site_id}/recalculate")
async def recalculate_site_quantities(
    site_id: UUID,
    ctx: UserContext = Depends(get_user_context),
    db: Session = Depends(get_db)
):
    """
    Force full recalculation of site quantities.

    Use when:
    - Bulk inventory import completed
    - Composition data corrected
    - System inconsistency detected
    """
    service = SiteQuantityService(db)
    await service.full_site_recalculation(site_id, ctx.company_id)

    return {'success': True, 'message': 'Site quantities recalculated'}

Background Jobs

Daily Snapshot Job

# app/workers/site_quantity_snapshot_worker.py

class SiteQuantitySnapshotWorker:
    """
    Background worker for daily quantity snapshots.

    Runs at midnight to:
    1. Record current quantities for Tier II max/avg calculations
    2. Update year-to-date statistics
    3. Clean up old history (configurable retention)
    """

    RETENTION_YEARS = 7  # Keep 7 years for Tier II compliance

    async def run_daily_snapshot(self):
        """Main entry point - run for all sites."""
        db = next(get_db())

        try:
            service = SiteQuantityService(db)
            await service.daily_quantity_snapshot()

            logger.info("Daily quantity snapshot completed")

        except Exception as e:
            logger.error(f"Daily snapshot failed: {e}")
        finally:
            db.close()

    async def cleanup_old_history(self):
        """Remove history older than retention period."""
        db = next(get_db())

        cutoff_date = date.today() - timedelta(days=365 * self.RETENTION_YEARS)

        deleted = db.query(ChemiqSiteQuantityHistory).filter(
            ChemiqSiteQuantityHistory.snapshot_date < cutoff_date
        ).delete()

        db.commit()
        logger.info(f"Cleaned up {deleted} old history records")

Cron Schedule

# app/workers/scheduler.py

SITE_QUANTITY_SCHEDULE = {
    # Daily snapshot at midnight
    'site_quantity_daily_snapshot': {
        'cron': '0 0 * * *',  # Midnight daily
        'handler': 'site_quantity_snapshot_worker.run_daily_snapshot',
        'description': 'Record daily chemical quantities for Tier II reporting'
    },

    # Weekly full recalculation (safety net)
    'site_quantity_weekly_recalc': {
        'cron': '0 2 * * 0',  # 2 AM Sunday
        'handler': 'site_quantity_snapshot_worker.full_recalculation_all_sites',
        'description': 'Weekly full recalculation to catch any missed updates'
    },

    # Monthly history cleanup
    'site_quantity_history_cleanup': {
        'cron': '0 3 1 * *',  # 3 AM on 1st of month
        'handler': 'site_quantity_snapshot_worker.cleanup_old_history',
        'description': 'Clean up quantity history older than retention period'
    },

    # Tier II reminder - February
    'tier2_reminder': {
        'cron': '0 9 1 2 *',  # 9 AM on Feb 1
        'handler': 'compliance_notification_worker.send_tier2_reminder',
        'description': 'Send Tier II reporting deadline reminder (due March 1)'
    }
}

Frontend Integration

TypeScript Types

// src/types/site-quantities.ts

export interface SiteChemicalSummary {
  summary_id: string;
  site_id: string;
  cas_number: string;
  chemical_name: string;

  // Quantities
  total_quantity_lbs: number;
  total_quantity_gal: number | null;
  total_containers: number;

  // Hazard info
  is_hazardous: boolean;
  hazard_classes: string[];
  physical_state: 'solid' | 'liquid' | 'gas';

  // Tier II data
  max_daily_quantity_lbs: number | null;
  avg_daily_quantity_lbs: number | null;
  days_on_site: number;

  // Fire code
  fire_code_category: string | null;
  fire_code_class: string | null;

  // Threshold status
  tier2_threshold_lbs: number;
  fire_permit_threshold: number | null;
  exceeds_tier2_threshold: boolean;
  exceeds_fire_threshold: boolean;

  // Contributing products
  contributing_products: {
    product_id: string;
    product_name: string;
    contribution_lbs: number;
  }[];

  storage_locations: {
    location_id: string;
    location_name: string;
    quantity_lbs: number;
  }[];

  last_calculated_at: string;
}

export interface SiteHazardSummary {
  summary_id: string;
  site_id: string;
  hazard_category: 'health' | 'physical' | 'environmental';
  hazard_class: string;
  ghs_code: string;

  // Fire code
  fire_code_category: string | null;
  fire_code_class: string | null;

  // Quantities
  total_quantity_lbs: number;
  total_quantity_gal: number | null;
  total_containers: number;
  chemical_count: number;

  // Threshold
  fire_permit_limit: number | null;
  exceeds_permit_limit: boolean;
  percent_of_limit: number;

  // Chemicals in this class
  chemicals: {
    cas_number: string;
    chemical_name: string;
    quantity_lbs: number;
  }[];
}

export interface Tier2ReportData {
  site_id: string;
  site_name: string;
  report_year: number;

  facility_info: {
    name: string;
    address: string;
    city: string;
    state: string;
    zip: string;
    duns_number?: string;
    naics_codes?: string[];
    employee_count?: number;
  };

  chemicals: {
    cas_number: string;
    chemical_name: string;

    // Physical/health hazards (checkboxes on form)
    is_fire_hazard: boolean;
    is_sudden_release: boolean;
    is_reactive: boolean;
    is_immediate_health: boolean;
    is_delayed_health: boolean;

    // Quantities
    max_daily_lbs: number;
    avg_daily_lbs: number;
    days_on_site: number;

    // Storage
    storage_locations: {
      description: string;
      max_quantity_lbs: number;
      storage_type_code: string;  // 'A' tank, 'B' cylinder, etc.
      pressure_code: string;      // '1' ambient, '2' above ambient
      temperature_code: string;   // '4' ambient, '5' above ambient
    }[];

    // Optional: confidential location claimed
    confidential_location: boolean;
  }[];

  // Certification
  certifier_name?: string;
  certifier_title?: string;
  certification_date?: string;
}

Dashboard Component Example

// src/pages/chemiq/sites/components/SiteQuantitySummaryCard.tsx

import React from 'react';
import { AlertTriangle, Flame, FileText, TrendingUp } from 'lucide-react';
import { SiteChemicalSummary } from '../../../../types/site-quantities';

interface Props {
  siteId: string;
  summaries: SiteChemicalSummary[];
}

export const SiteQuantitySummaryCard: React.FC<Props> = ({ siteId, summaries }) => {
  const hazardousCount = summaries.filter(s => s.is_hazardous).length;
  const tier2ExceedCount = summaries.filter(s => s.exceeds_tier2_threshold).length;
  const fireExceedCount = summaries.filter(s => s.exceeds_fire_threshold).length;

  const totalQuantityLbs = summaries.reduce(
    (sum, s) => sum + (s.total_quantity_lbs || 0), 0
  );

  return (
    <div className="bg-white rounded-lg shadow p-6">
      <h3 className="text-lg font-semibold mb-4">Chemical Quantity Summary</h3>

      <div className="grid grid-cols-2 gap-4 mb-6">
        <div className="text-center p-4 bg-gray-50 rounded">
          <div className="text-3xl font-bold">{summaries.length}</div>
          <div className="text-sm text-gray-600">Total Chemicals</div>
        </div>
        <div className="text-center p-4 bg-gray-50 rounded">
          <div className="text-3xl font-bold">{hazardousCount}</div>
          <div className="text-sm text-gray-600">Hazardous</div>
        </div>
        <div className="text-center p-4 bg-gray-50 rounded">
          <div className="text-3xl font-bold">
            {totalQuantityLbs.toLocaleString()}
          </div>
          <div className="text-sm text-gray-600">Total (lbs)</div>
        </div>
        <div className="text-center p-4 bg-gray-50 rounded">
          <div className="text-3xl font-bold">{summaries.reduce(
            (sum, s) => sum + s.total_containers, 0
          )}</div>
          <div className="text-sm text-gray-600">Containers</div>
        </div>
      </div>

      {/* Threshold Alerts */}
      {(tier2ExceedCount > 0 || fireExceedCount > 0) && (
        <div className="space-y-3">
          {tier2ExceedCount > 0 && (
            <div className="flex items-center p-3 bg-yellow-50 border border-yellow-200 rounded">
              <FileText className="h-5 w-5 text-yellow-600 mr-3" />
              <div>
                <div className="font-medium text-yellow-800">
                  Tier II Reporting Required
                </div>
                <div className="text-sm text-yellow-700">
                  {tier2ExceedCount} chemical(s) exceed 10,000 lb threshold
                </div>
              </div>
            </div>
          )}

          {fireExceedCount > 0 && (
            <div className="flex items-center p-3 bg-red-50 border border-red-200 rounded">
              <Flame className="h-5 w-5 text-red-600 mr-3" />
              <div>
                <div className="font-medium text-red-800">
                  Fire Permit May Be Required
                </div>
                <div className="text-sm text-red-700">
                  {fireExceedCount} chemical(s) exceed fire code limits
                </div>
              </div>
            </div>
          )}
        </div>
      )}
    </div>
  );
};

Integration with Existing Systems

Integration with Compliance Checking Service

The site quantity aggregation feeds into the compliance checking service for Tier II deadline tracking:

# In compliance_checking_service.py

def _get_upcoming_deadlines():
    """Get upcoming regulatory deadlines."""
    from datetime import date

    today = date.today()
    year = today.year

    deadlines = []

    # Tier II due March 1
    tier2_deadline = date(year, 3, 1)
    if tier2_deadline > today:
        # Check if company has chemicals exceeding threshold
        has_tier2_chemicals = db.query(ChemiqSiteChemicalSummary).filter(
            ChemiqSiteChemicalSummary.company_id == company_id,
            ChemiqSiteChemicalSummary.exceeds_tier2_threshold == True
        ).first() is not None

        if has_tier2_chemicals:
            deadlines.append({
                'program': 'SARA Tier II',
                'deadline': tier2_deadline.isoformat(),
                'description': 'Emergency planning hazardous chemical inventory',
                'action_required': True
            })

    return deadlines

Integration with Inventory Service

The inventory service triggers quantity recalculation:

# In inventory_service.py

async def create_inventory_item(
    self,
    inventory_data: InventoryCreate,
    company_id: UUID,
    user_id: UUID
) -> ChemIQInventory:
    """Create inventory item and update site quantities."""

    # ... existing creation logic ...

    # INTEGRATION: Trigger site quantity update
    quantity_service = SiteQuantityService(self.db)
    await quantity_service.on_inventory_change(
        inventory_item=new_item,
        change_type='create'
    )

    return new_item

Summary

What This Implementation Provides

Real-time site-level chemical aggregation - Quantities updated on every inventory change
Tier II reporting support - Max daily, avg daily, days on-site tracked automatically
Fire permit compliance - Quantities tracked by hazard class with threshold alerts
Unit conversion - Automatic conversion to standard units (lbs) for reporting
Historical tracking - Daily snapshots for regulatory reporting accuracy
Threshold notifications - Alerts when quantities approach or exceed limits

Key Tables

Table	Purpose	Update Frequency
`chemiq_site_chemical_summary`	Per-chemical totals	Real-time (on inventory change)
`chemiq_site_hazard_summary`	Per-hazard-class totals	Real-time (derived from chemical summary)
`chemiq_site_quantity_history`	Daily snapshots	Daily (cron job)
`chemiq_unit_conversions`	Unit conversion factors	Static (seed data)
`chemiq_fire_code_thresholds`	Fire code limits	Static (seed data)

Trigger Summary

Trigger	Event	Action	Frequency
#1	Inventory CRUD	Incremental update	Real-time
#2	SDS/Composition change	Full site recalc	Event-driven
#3	Daily cron	Snapshot + max/avg	Daily
#4	After #1, #2, #3	Threshold check	Real-time

Regulatory Deadlines Supported

Program	Deadline	Data Needed
EPCRA Tier II	March 1	Max daily, avg daily, days on-site, storage locations
Fire Permit	Varies	Quantities by hazard class vs. code limits
TRI (Form R)	July 1	Annual usage by chemical (separate feature)

Overview​

Table of Contents​

Problem Statement​

Current Limitations​

Business Requirements​

Regulatory Background​

EPCRA Tier II Reporting (SARA Title III, Section 312)​

Fire Code Requirements (NFPA 1, IFC)​

Data Flow Architecture​

Chemical Quantity Calculation Flow​

Trigger Points Overview​

Database Schema​

1. Unit Conversion Reference Table​

2. Product Density Reference Table​

3. Site Chemical Summary Table (Main Aggregation)​

4. Site Hazard Summary Table (Hazard Class Aggregation)​

5. Site Quantity History Table (Daily Snapshots for Tier II)​

6. Fire Code Threshold Reference Table​

Trigger Points​

Trigger #1: Inventory Changes (Real-Time)​

Trigger #2: SDS/Product Updates​

Trigger #3: Daily Snapshot (Scheduled Job)​

Trigger #4: Threshold Alerts​

Trigger Summary Table​

Calculation Logic​

Unit Conversion Algorithm​

Chemical Quantity from Composition​

API Endpoints​

Site Chemical Summary Endpoints​

Background Jobs​

Daily Snapshot Job​

Cron Schedule​

Frontend Integration​

TypeScript Types​

Dashboard Component Example​

Integration with Existing Systems​

Integration with Compliance Checking Service​

Integration with Inventory Service​

Summary​

What This Implementation Provides​

Key Tables​

Trigger Summary​

Regulatory Deadlines Supported​

Overview

Table of Contents

Problem Statement

Current Limitations

Business Requirements

Regulatory Background

EPCRA Tier II Reporting (SARA Title III, Section 312)

Fire Code Requirements (NFPA 1, IFC)

Data Flow Architecture

Chemical Quantity Calculation Flow

Trigger Points Overview

Database Schema

1. Unit Conversion Reference Table

2. Product Density Reference Table

3. Site Chemical Summary Table (Main Aggregation)

4. Site Hazard Summary Table (Hazard Class Aggregation)

5. Site Quantity History Table (Daily Snapshots for Tier II)

6. Fire Code Threshold Reference Table

Trigger Points

Trigger #1: Inventory Changes (Real-Time)

Trigger #2: SDS/Product Updates

Trigger #3: Daily Snapshot (Scheduled Job)

Trigger #4: Threshold Alerts

Trigger Summary Table

Calculation Logic

Unit Conversion Algorithm

Chemical Quantity from Composition

API Endpoints

Site Chemical Summary Endpoints

Background Jobs

Daily Snapshot Job

Cron Schedule

Frontend Integration

TypeScript Types

Dashboard Component Example

Integration with Existing Systems

Integration with Compliance Checking Service

Integration with Inventory Service

Summary

What This Implementation Provides

Key Tables

Trigger Summary

Regulatory Deadlines Supported