OSHA Establishments

Core schema, browse surfaces, aggregate functions, and research-oriented querying patterns for the OSHA dataset.

The OSHA dataset is designed for business research on establishment safety and compliance history, not just raw inspection reporting.

The serving layer is built around four main objects:

osha_inspections: lean inspection-level records
osha_violations: citation and penalty details
osha_related_activities: lightweight links between related inspections
osha_establishment_master: the main entity-centric browse table

For most product and API use cases, osha_establishment_master should be the default table.

Source

Property	Value
Source	U.S. Department of Labor OSHA Data Catalog
Total source rows profiled	~32.8 million across 7 source exports
V1 serving focus	inspections + violations + related activities
Refresh strategy	batch reload followed by serving-layer refresh

Why This Dataset Exists

This dataset is meant to support workflows like:

finding establishments in a target NAICS segment
screening companies by geography and OSHA history
researching high-penalty or high-violation businesses
building city, state, and industry browse pages
supporting diligence and prospecting workflows on NewBizBot

Main Browse Table: `osha_establishment_master`

This is the main public-serving table because OSHA source data is inspection-centric, while business research is establishment-centric.

Core Columns

Column	Type	Description
`establishment_key`	text	Stable derived identifier for a normalized establishment
`estab_name_display`	text	Best display name for the establishment
`site_address`	text	Site street address
`site_city`	text	Site city
`site_state`	text	Site state
`site_zip`	text	Site ZIP
`mail_street`	text	Mailing street
`mail_city`	text	Mailing city
`mail_state`	text	Mailing state
`mail_zip`	text	Mailing ZIP
`owner_type`	text	Ownership/business type code from OSHA
`naics_code`	text	Primary industry code for research filtering
`sic_code`	text	Legacy industry code for additional segmentation
`nr_in_estab_latest`	integer	Latest establishment-size proxy from OSHA
`latest_open_date`	date	Latest inspection open date
`latest_close_case_date`	date	Latest case close date
`inspection_count`	bigint	Count of inspections tied to the establishment
`violation_count`	bigint	Count of citations across inspections
`serious_violation_count`	bigint	Count of serious violations
`repeat_violation_count`	bigint	Count of repeat violations
`willful_violation_count`	bigint	Count of willful violations
`total_initial_penalty`	numeric	Sum of initial penalties
`total_current_penalty`	numeric	Sum of current penalties
`latest_citation_date`	date	Latest citation issuance date
`has_related_activity`	boolean	Whether related inspection activity exists

Search

Column	Type	Description
`estab_name_search`	tsvector	Full-text search vector built from establishment and address fields

Supporting Tables

`osha_inspections`

Lean inspection-level records with:

establishment name
site and mailing addresses
NAICS and SIC
inspection type/scope
employee count proxy
core inspection dates

`osha_violations`

Citation-level records with:

citation identifier
OSHA standard
violation type
issuance and abatement dates
penalty amounts
gravity and instance counts

`osha_related_activities`

Links between inspections. Useful for establishing that two activities belong to the same broader enforcement chain.

Aggregate Materialized Views

These views are intended for browse pages and summary endpoints:

`osha_stats_by_state_mv`

Returns one row per state with:

total establishments
total inspections
total violations
serious/repeat/willful counts
penalty totals
latest inspection date

`osha_stats_by_city_mv`

Returns one row per (state, city) with:

total establishments
total inspections
total violations
serious violation counts
total current penalty
latest inspection date

`osha_stats_by_state_industry_mv`

Returns one row per (state, naics_code, sic_code) with:

total establishments
total inspections
total violations
serious violation counts
total current penalty
latest inspection date

`osha_city_slug_lookup_mv`

Maps (state, city_slug) back to the original city name for route resolution.

Fast-Lookup Columns

These columns are expected to be the main indexed filter paths:

`osha_establishment_master`

establishment_key
site_state
site_city
naics_code
sic_code
latest_open_date
latest_close_case_date
total_current_penalty
violation_count
inspection_count
estab_name_search

`osha_inspections`

activity_nr
reporting_id
site_state
site_city
site_zip
naics_code
sic_code
open_date
close_case_date
estab_name_search

`osha_violations`

activity_nr
citation_id
viol_type
issuance_date
abate_date
current_penalty
initial_penalty
gravity

At least one indexed filter should be present in table queries. For city lookups, pair city with state whenever possible.

Aggregate RPCs

`osha_stats_by_state`

Endpoint: GET /data/rpc/osha_stats_by_state

Use this for:

state leaderboard pages
high-level research overviews
state summary cards

Example:

curl "https://newbizbot.ai/api/data/rpc/osha_stats_by_state?p_state=TX" \
  -H "Authorization: Bearer YOUR_API_KEY"

`osha_stats_by_city`

Endpoint: GET /data/rpc/osha_stats_by_city

Use this for:

state-to-city drill-down
city browse pages

Example:

curl "https://newbizbot.ai/api/data/rpc/osha_stats_by_city?p_state=TX" \
  -H "Authorization: Bearer YOUR_API_KEY"

`osha_stats_by_industry`

Endpoint: GET /data/rpc/osha_stats_by_industry

Use this for:

state + NAICS research
broad industry scans
workflows like utility or contractor discovery

Example:

curl "https://newbizbot.ai/api/data/rpc/osha_stats_by_industry?p_state=TX&p_naics_prefix=237130" \
  -H "Authorization: Bearer YOUR_API_KEY"