Site Quantities — Calculation & Determination Logic

This document explains how every field on the /chemiq/site-quantities screen is calculated, including the Tier II threshold, Fire Code threshold, and every column in the Hazard Summary tab.

---

1. High-Level Architecture

Inventory Items (chemiq_inventory)
       │
       ▼
  Background Service (SQS message: quantity_recalculation)
       │
       ├─ Step 1: Load reference data (units, densities, fire thresholds)
       ├─ Step 2: Aggregate inventory by CAS number
       ├─ Step 3: Upsert chemiq_site_chemical_summary (Chemical Summary tab)
       ├─ Step 4: Aggregate by hazard class → chemiq_site_hazard_summary (Hazard Summary tab)
       └─ Step 5: Check thresholds, generate alerts
       │
       ▼
  Frontend reads from the two summary tables via API

Trigger: The recalculation is triggered by clicking the "Recalculate" button on the Site Quantities page, which dispatches an SQS message to the background service (tellus-ehs-background-service). The service class is QuantityAggregationService in app/services/quantity_aggregation/service.py.

---

2. Data Sources (Reference Tables)

All three reference tables are static seed data populated via SQL INSERT statements defined in docs/features/site_chemical_quantity_aggregation.md. They are not user-editable and do not change during normal application use.

2.1 Unit Conversions (chemiq_unit_conversions)

All inventory quantities are normalized to pounds (lbs) for weight and gallons (gal) for volume using conversion factors:

Unit	Type	Factor	Standard Unit
lbs / lb	weight	1.0	lbs
oz	weight	0.0625	lbs
kg	weight	2.2046	lbs
g	weight	0.0022	lbs
mg	weight	0.0000022	lbs
ton	weight	2000.0	lbs
gal	volume	1.0	gal
L	volume	0.2642	gal
qt	volume	0.25	gal
pt	volume	0.125	gal
fl_oz	volume	0.0078	gal
mL	volume	0.000264	gal
cu_ft	volume	7.4805	gal

Data source: Standard scientific unit conversion factors. Seeded via SQL in docs/features/site_chemical_quantity_aggregation.md (lines 231–248). These are universal constants that do not require updates.

2.2 Product Densities (chemiq_product_density)

Used to convert between weight and volume. Stores density_lbs_per_gal per CAS number. Default fallback: 8.34 lbs/gal (water density).

Data source: Seeded with a single baseline record — water (CAS 7732-18-5, 8.34 lbs/gal). Additional densities can be populated from SDS data, PubChem, or manual entry (tracked by the data_source column: 'SDS', 'PubChem', 'Reference', or 'Manual'). If no density record exists for a CAS number, the background service falls back to the water density constant (DEFAULT_DENSITY = 8.34) hardcoded in QuantityAggregationService.

Seed SQL: docs/features/site_chemical_quantity_aggregation.md (lines 283–284).

2.3 Fire Code Thresholds (chemiq_fire_code_thresholds)

Reference data based on International Fire Code (IFC) Table 5003.1.1(1) — Maximum Allowable Quantities (MAQs) per control area for indoor storage:

Hazard Category	Class	Storage Type	Max Qty (gal)	Max Qty (lbs)	Notes
Flammable Liquid	IA	indoor_unprotected	30	—	Flash point < 73°F, BP < 100°F
Flammable Liquid	IA	indoor_cabinet	60	—	Approved flammable cabinet
Flammable Liquid	IB	indoor_unprotected	120	—	Flash point < 73°F, BP >= 100°F
Flammable Liquid	IB	indoor_cabinet	120	—	Approved flammable cabinet
Flammable Liquid	IC	indoor_unprotected	120	—	Flash point 73–100°F
Flammable Liquid	IC	indoor_cabinet	120	—	Approved flammable cabinet
Combustible Liquid	II	indoor_unprotected	120	—	Flash point 100–140°F
Combustible Liquid	IIIA	indoor_unprotected	330	—	Flash point 140–200°F
Combustible Liquid	IIIB	indoor_unprotected	13,200	—	Flash point >= 200°F
Oxidizer	1	indoor_unprotected	—	4,000	Class 1 oxidizer
Oxidizer	2	indoor_unprotected	—	250	Class 2 oxidizer
Oxidizer	3	indoor_unprotected	—	10	Class 3 oxidizer
Oxidizer	4	indoor_unprotected	—	1	Class 4 oxidizer — any amount

Data source: Values come from IFC Table 5003.1.1(1) (International Fire Code, published by the International Code Council). The code_reference column in every row stores 'IFC Table 5003.1.1(1)'. These values may also align with NFPA 1 (Fire Code) and NFPA 30 (Flammable and Combustible Liquids Code). The jurisdiction column defaults to 'general' but can be overridden for state/city-specific limits.

Seed SQL: docs/features/site_chemical_quantity_aggregation.md (lines 491–512).

2.4 Tier II Thresholds

Unlike fire code thresholds, Tier II thresholds are not stored in a database table. They are hardcoded constants in the background service:

TIER2_HAZARDOUS_THRESHOLD = 10000   # 10,000 lbs — EPCRA Section 312
TIER2_EHS_THRESHOLD = 500           # 500 lbs — for Extremely Hazardous Substances (not yet implemented)

Data source: EPA EPCRA (Emergency Planning and Community Right-to-Know Act), SARA Title III, Section 312. Facilities must file annual Tier II reports for any hazardous chemical stored above the threshold planning quantity (TPQ). The general threshold is 10,000 lbs; Extremely Hazardous Substances (EHS) listed under EPCRA Section 302 have a lower threshold of 500 lbs (or their specific TPQ).

---

3. Step-by-Step Aggregation Process

Step 1: Load Reference Data

The service loads three reference datasets:

• _load_unit_conversions() → Dict of unit_code → {type, factor, standard_unit}
• _load_density_data() → Dict of CAS → density_lbs_per_gal
• _load_fire_code_thresholds() → Dict of "category:class:storage_type" → {max_gal, max_lbs}

Step 2: Aggregate Inventory by CAS Number

Source query joins:

• chemiq_inventory (active items at the site)
• chemiq_company_product_catalog (product details, active products)
• chemiq_sds_composition (chemical components of each product, via current_sds_id)
• core_data_site_locations (storage location names)

Key behavior: Each inventory item expands to multiple rows — one per chemical component in its SDS composition. This means a single product with 3 chemical components generates 3 rows.

Quantity calculation per component:

item_total = container_size × quantity (number of containers)

concentration = COALESCE(concentration_exact, concentration_max, concentration_min) / 100
    (defaults to 1.0 if no concentration data — i.e., treat as 100%)

If size_unit is a WEIGHT unit:
    lbs = item_total × conversion_factor × concentration
    gal = lbs ÷ density_lbs_per_gal

If size_unit is a VOLUME unit:
    gal = item_total × conversion_factor × concentration
    lbs = gal × density_lbs_per_gal

If size_unit is unknown:
    lbs = item_total × concentration

All component quantities for the same CAS number are summed.

Container counting: Containers are only counted once per inventory item (not once per component), using a seen_chemical_ids set to deduplicate.

Step 3: Determine Hazard Classification

For each CAS group, the service looks up hazard information from chemiq_sds_hazard_info (the parsed GHS section of the SDS). This provides:

• hazard_classes (e.g., [\{"hazard_class": "Flammable liquid", "hazard_category": "Category 3"\}])
• signal_word
• hazard_statements

is_hazardous = true if any hazard classes are present.

Fire code classification is determined by the _determine_fire_code_class() method which maps GHS hazard classes to IFC categories:

GHS Hazard Class	GHS Category	→ IFC Fire Code Category	→ IFC Class
Flammable liquid	Category 1	Flammable Liquid	IA
Flammable liquid	Category 2	Flammable Liquid	IB
Flammable liquid	Category 3	Flammable Liquid	IC
Flammable liquid	Category 4	Combustible Liquid	II
Oxidizer	Category 1	Oxidizer	4
Oxidizer	Category 2	Oxidizer	3
Oxidizer	Category 3	Oxidizer	2
Corrosive	(any)	Corrosive	—
Toxic / Acute Toxicity	(any)	Toxic	—

Note: The method returns the first match — so if a chemical has both "Flammable liquid Category 3" and "Oxidizer Category 1", only the first (flammable) is used.

---

4. Chemical Summary Tab — Column Definitions

Each row in the Chemical Summary tab represents one CAS number aggregated across all inventory items at the site.

Column	Source	Calculation
CAS Number	chemiq_sds_composition.cas_number	Grouped by CAS
Chemical Name	chemiq_sds_composition.chemical_name	First match from SDS composition
Qty (lbs)	Aggregated	Sum of all lbs for this CAS (see Step 2 formula)
Qty (gal)	Aggregated	Sum of all gal for this CAS (see Step 2 formula)
Containers	chemiq_inventory.quantity	Count of containers (deduplicated per inventory item)
Products	Aggregated	Count of distinct company_product_id values contributing to this CAS
Hazardous	chemiq_sds_hazard_info	true if any GHS hazard classes are associated with the product's SDS
Tier II	Calculated	See Section 5 below
Fire Code	Calculated	See Section 6 below

---

5. Tier II Threshold — How It Is Determined

What is Tier II?

EPA EPCRA Section 312 requires facilities to report chemicals stored above certain thresholds. This is the "Tier II" report filed annually.

Threshold Values (Hardcoded Constants)

TIER2_HAZARDOUS_THRESHOLD = 10,000 lbs   # For most hazardous chemicals
TIER2_EHS_THRESHOLD = 500 lbs            # For Extremely Hazardous Substances (not yet implemented)

Determination Logic

IF chemical is_hazardous = true:
    tier2_threshold_lbs = 10,000
    exceeds_tier2_threshold = (total_quantity_lbs >= 10,000)
ELSE:
    tier2_threshold_lbs = NULL
    exceeds_tier2_threshold = false

In the UI: A green checkmark means the chemical is below threshold. A red X-circle means it exceeds.

Tier II Report Data (separate endpoint)

The /sites/\{site_id\}/tier2-report endpoint uses daily quantity snapshots (chemiq_site_quantity_history) to calculate:

• Max daily amount: MAX(quantity_lbs) across all snapshot dates in the year
• Average daily amount: AVG(quantity_lbs) across all snapshot dates
• Days on site: COUNT(DISTINCT snapshot_date) where is_present = true
• Exceeds threshold: max_daily_amount >= 10,000 lbs

---

6. Fire Code Threshold — How It Is Determined

What is Fire Code Compliance?

The International Fire Code (IFC) sets maximum allowable quantities (MAQs) per control area. Exceeding these requires a fire permit.

Determination Logic (Per Chemical)

1. Determine fire_code_category and fire_code_class from GHS hazard classes
   (using the mapping table in Section 3)

2. Look up threshold in chemiq_fire_code_thresholds:
   key = "{fire_code_category}:{fire_code_class}:inside_building"

3. Get permit limit:
   fire_permit_threshold = threshold.max_gal OR threshold.max_lbs

4. Compare:
   IF fire_permit_threshold exists AND total_quantity_gal >= fire_permit_threshold:
       exceeds_fire_threshold = true
   ELSE:
       exceeds_fire_threshold = false

In the UI: A green checkmark means the chemical is within fire code limits. A red X-circle means it exceeds.

Note: The comparison currently uses gallons (total_quantity_gal >= fire_permit_threshold) for liquid categories (flammable/combustible) where thresholds are defined in gallons. For oxidizers (thresholds in lbs), it falls back to max_lbs.

---

7. Hazard Summary Tab — Column Definitions

Each row in the Hazard Summary tab represents one hazard class group, aggregating all chemicals at the site that share the same fire code classification.

How Rows Are Created

Chemicals are grouped by fire_code_category:fire_code_class. Only chemicals with a fire_code_category (determined from their GHS hazard classes) appear in this table. Non-hazardous chemicals are excluded.

Column-by-Column Breakdown

Column	Source	Calculation
Hazard Category	_determine_fire_code_class()	The IFC fire code category derived from GHS class (e.g., "Flammable Liquid", "Oxidizer", "Corrosive", "Toxic")
Hazard Class	_determine_fire_code_class()	The IFC sub-class (e.g., "IA", "IB", "IC", "II", "2", "3", "4") or "General" if none
Qty (lbs)	Aggregated	SUM(total_quantity_lbs) for all chemicals in this hazard group
Qty (gal)	Aggregated	SUM(total_quantity_gal) for all chemicals in this hazard group
Chemicals	Aggregated	COUNT of distinct CAS numbers in this hazard group
Fire Permit Limit	chemiq_fire_code_thresholds	The MAQ from the fire code threshold table for this category:class:inside_building combination. NULL if no threshold exists for this hazard type
% of Limit	Calculated	(total_qty / fire_permit_limit) × 100. Uses total_quantity_gal if the threshold is defined in gallons, total_quantity_lbs if defined in lbs. NULL if no permit limit exists
Exceeds	Calculated	true if total_qty >= fire_permit_limit. Green check = within limits, Red X = exceeds

Detailed Calculation for % of Limit and Exceeds

# From _aggregate_by_hazard_class():

threshold_key = f"{fire_code_category}:{fire_code_class}:inside_building"
fire_threshold = fire_thresholds.get(threshold_key, {})
permit_limit = fire_threshold.get("max_gal") or fire_threshold.get("max_lbs")

exceeds_limit = False
percent_of_limit = None

if permit_limit:
    # Use gallons if threshold is in gallons, otherwise use lbs
    total = total_quantity_gal if fire_threshold.get("max_gal") else total_quantity_lbs

    if total >= permit_limit:
        exceeds_limit = True

    percent_of_limit = (total / permit_limit) * 100

---

8. Stat Cards on the Page Header

Card	Value	Source
Total Chemicals	Count of all CAS rows	COUNT(*) from chemiq_site_chemical_summary
Hazardous	Count of hazardous CAS rows	SUM(CASE WHEN is_hazardous THEN 1 ELSE 0 END)
Exceeds Tier II	Count of chemicals exceeding Tier II	SUM(CASE WHEN exceeds_tier2_threshold THEN 1 ELSE 0 END)
Exceeds Fire Code	Count of chemicals exceeding fire code	SUM(CASE WHEN exceeds_fire_threshold THEN 1 ELSE 0 END)

---

9. Key Files

Layer	File	Purpose
Background Service	tellus-ehs-background-service/app/services/quantity_aggregation/service.py	Core aggregation logic, threshold calculations
API Endpoints	tellus-ehs-hazcom-service/app/api/v1/chemiq/site_quantities.py	REST endpoints for chemical/hazard summaries
DB Models	tellus-ehs-hazcom-service/app/db/models/site_quantity.py	SQLAlchemy models for all 6 tables
Pydantic Schemas	tellus-ehs-hazcom-service/app/schemas/chemiq/site_quantity.py	Request/response validation
Frontend Page	tellus-ehs-hazcom-ui/src/pages/chemiq/site-quantities/index.tsx	React page with tabs
Frontend Types	tellus-ehs-hazcom-ui/src/types/siteQuantity.ts	TypeScript type definitions
API Client	tellus-ehs-hazcom-ui/src/services/api/chemiq.api.ts	Frontend API call functions

---

10. Database Tables

Table	Purpose
chemiq_site_chemical_summary	Pre-computed per-CAS aggregation per site (Chemical Summary tab)
chemiq_site_hazard_summary	Per-hazard-class aggregation per site (Hazard Summary tab)
chemiq_site_quantity_history	Daily quantity snapshots for Tier II max/avg calculations
chemiq_unit_conversions	Reference: unit conversion factors
chemiq_product_density	Reference: chemical densities for weight/volume conversion
chemiq_fire_code_thresholds	Reference: IFC maximum allowable quantities

---

11. Current Limitations

1. EHS threshold (500 lbs) is defined as a constant but not yet implemented in the determination logic — only the 10,000 lbs general hazardous threshold is used.
2. Fire code classification uses the first matching GHS class only. A chemical with multiple hazard classes (e.g., flammable AND oxidizer) only gets classified under the first match.
3. Storage type defaults to inside_building when looking up fire code thresholds. Cabinet storage (indoor_cabinet) thresholds exist but are not dynamically selected based on actual storage type.
4. Concentration fallback: If no concentration data exists in SDS composition (concentration_exact, concentration_max, concentration_min all NULL), the chemical is treated as 100% concentration.
5. Daily snapshots must be created separately (via create_daily_snapshot()) to populate Tier II report historical data. Without snapshots, the Tier II report endpoint returns empty results.