CA Food Facility Inspections - San Francisco Restaurant History

San Francisco's Department of Public Health publishes its food-facility inspection history as open data (city Socrata, free public data). The catch: the city has published it in three successive datasets with different schemas, so a naive lookup only ever sees one slice. This actor merges all three into one queryable, charged-per-record API - the full SF restaurant inspection history from 2016 to today.

Built for: restaurant / ghost-kitchen / franchise compliance & closure diligence, supply-chain vetting, insurance, and food-safety research.

What it covers

San Francisco food-facility inspections, 2016 -> present (~126k records across three eras):

Era	Rows	Granularity	What it records
current (2024-present)	~22k	per inspection	Pass / Conditional Pass / Closure, violation codes + code text + violation count, exact permit number, DBA, neighborhood, inspector
y2020_2023	~50k	per violation	PASS / CONDITIONAL PASS / CLOSURE, violation severity (Minor / Major / OUT...), description
y2016_2019	~54k	per violation	inspection score (0-100), risk category (High / Moderate / Low), violation description

Why three eras? SF changed systems in 2016, 2020 and 2024 - the old tables froze and a new one started, with a ~2023-08 -> 2024-01 gap between eras. There is no exact key linking a business across all three, so cross-era history is matched on business name (generation: all). Within the current era, permit_number is the stable per-business key.

Note on era-specific filters: only the eras that actually record a field honor a filter on it. A rating / closure filter runs against the current and 2020-2023 eras only (the 2016-2019 era has no rating column - it records a score instead), so "show every closure across SF locations" returns closures from 2020 onward. Use risk / minScore for the 2016-2019 era.

Typical questions

• "Show every Closure a restaurant chain has had across its SF locations."
• "What violations does this food facility (by permit number) have on its latest inspections?"
• "Which violations mention rodents / vermin / improper cooling - current and historical?"
• "Find high-risk (or low-score) inspections from the 2016-2019 era for this business."
• "How many Conditional Pass results did restaurants in the Mission get last year?"

Results vs. violations - row semantics

Each era has its own row granularity, and the generation field tells you which:

• current = one row per inspection (violation text is the full violation_codes blob; violationCount = how many).
• y2020_2023 / y2016_2019 = one row per violation (a clean inspection still emits one row with empty violation text).

Inputs

Input	What it does
generation	current (default) / y2020_2023 / y2016_2019 / all (full history by name)
business	business-name substring
permit	exact permit number (current era only - the stable store key)
dateFrom / dateTo	inspection date window
rating	pass / conditional / closure
keyword	violation-text substring
risk	High / Moderate / Low (2016-2019 era)
severity	violation severity text (2020-2023 era)
minScore	only inspections scoring at least this (2016-2019 era)
aggregate / groupBy	one row per group (rating / neighborhood / inspectionType / severity / risk) with a count
maxResults	cap records (default 200)

Default run = the ~200 most recent SF inspections (2024-present era) - fast enough for the daily auto-test, and a live look at what SF inspectors are finding right now.

Low cost

From $0.001 per record - billed only for the rows you use, with a per-run charge cap so a broad query can't surprise-bill.

Behind this is an awkward source, unified. SF has published its food-inspection history as three successive datasets with different schemas (2016-2019 carries a 0-100 score, 2020-2023 carries a Pass/Conditional/Closure rating, 2024+ is per-inspection with violation codes) - and there is no key linking a restaurant across them; even the rating enum was once mistyped by the city (CONDITIIONAL). We normalize all three onto one schema, one rating vocabulary, and one cross-era store history matched on name - with the future-dated garbage rows SF lets into its tables clamped out so a date filter can never surface a 2031 inspection. You query one clean history; the schema archaeology stays on our side.

Source

• SF Health Inspections 2024-Present - official.
• SF Restaurant Scores 2020-2023 - official.
• SF Restaurant Scores 2016-2019 - official.

Note: inspection results are SF's own public health ratings. A "Closure" result means the facility was closed at the time of that inspection; reasons are recorded in free-text violation notes rather than a structured field.