How we ingest, standardize, and serve millions of 311 records across the country for $0/month.
The foundation of this index relies on the Socrata Open Data API (SODA). Many of the largest municipalities in the United States (including NYC, Chicago, SF, and Austin) use Socrata to host their civic datasets.
While there are comprehensive lists of civic datasets (such as Andrew Friedman's JKAN repository), they reveal a deeply fragmented ecosystem. Some cities use CKAN, others use ArcGIS, and some simply host static CSV files. By strictly targeting the Socrata SODA API, our Python pipeline can intelligently paginate through massive historical datasets using uniform $where and $limit parameters, ensuring we never duplicate records or "double dip".
Not all cities host their data the same way, even when using Socrata. While cities like New York maintain a single, massive endpoint for all historical data, cities like Los Angeles "chunk" their data, creating a brand new API endpoint every single year (e.g., MyLA311-2024, MyLA311-2025).
Our pipeline is architected to natively handle endpoint chunking. The ETL script accepts arrays of API endpoints for a single city. When the automated nightly sync runs, it iterates through all active chunks, querying them for new records created since the last successful sync. If a chunk returns zero new records, it instantly skips it, making the process highly efficient.
Every city uses a proprietary taxonomy for their data. Dallas might use lat_location as a string (32.8, -96.6), while Oakland uses srx and sry. Our pipeline utilizes the Polars Dataframe Engine to dynamically inspect the schema of every incoming payload.
Using regex and column introspection, it coerces unpredictable schemas into a uniform master format: [id, city, category, created_at, closed_at, lat, lon, resolution_days]. It categorizes thousands of hyper-specific civic complaint types (e.g., "Missed Trash Collection" vs "Overflowing Garbage Cart") into 8 shared categories.
Every request lives in a MotherDuck (hosted DuckDB) warehouse. A nightly job asks each city's portal only for records that changed since the last run, and upserts them. Alongside the raw requests it keeps a daily rollup: counts and resolution times per day, city, category, status and agency. Every chart reads that rollup, so a ten-year range costs about as much to answer as a single week.
Queries run on the server. Your browser only receives small, chart-sized results, however large the date range. The data changes once a day, so identical questions are answered from a short-lived cache.
Plotting millions of 311 requests on a map at once hides geographic patterns and slows the browser down. To keep the map stable and flexible, this platform uses a Dynamic Query Budget.
Every time you change a filter (city, date, category), the server first reads how many requests match from the daily rollup. If there are fewer than 10,000, the map shows each one at its exact location.
Above that (for example, three years of New York City), the server groups coordinates into spatial bins using ROUND(lat, 3) and counts each bin. Mapbox draws the bins as a Heatmap, and a message tells you when this happens. The charts and totals are unaffected; only the map is aggregated.
The data is not in UTC. City portals publish each request's time as the local clock time in that city, with no time zone attached. A request stamped 11:08 PM in New York happened at 11:08 PM Eastern; one stamped 11:08 PM in Los Angeles happened three hours later. We keep those times exactly as published. You can see it in the data: in every city, requests bottom out around 2–4 AM local time and peak around 8–10 AM local time.
A date means that calendar day in each city's own time zone. Choosing Sep 22 selects Sep 22 in New York for New York's requests, Sep 22 in Chicago for Chicago's, and Sep 22 in Los Angeles for Los Angeles's. Daily charts, totals and the map all use the same rule, so they always agree. It also keeps comparisons fair: "Monday" is Monday in every city, not a window that starts at 9 PM for one city and midnight for another.
We deliberately don't convert to UTC. A UTC day starts at 5 PM in Los Angeles and 8 PM in New York (8 PM and 11 PM in winter), which would split every American evening across two dates.
| Cities | Their clock |
|---|---|
| New York City | Eastern (America/New_York) |
| Chicago, Austin, Dallas, New Orleans, Baton Rouge | Central (America/Chicago) |
| Los Angeles, San Francisco, Oakland | Pacific (America/Los_Angeles) |
The "Times in" setting next to the date range controls your own clock. It decides what "Today" and the quick ranges mean, and how refresh times are shown. It never changes which requests a date selects. The newest date you can pick is the newest date any city has reached, which is usually New York's.
Cities don't all publish on the same schedule or with the same history. The Data Coverage timeline shows, for each city, what we hold and why anything is missing:
By default the dashboard opens on the last 30 days that every city has fully published, so the first view compares like with like. The notes under the date picker flag it whenever your range includes days still arriving, a feed that stopped, or months that are thin or not downloaded.