Data · Engineering
Boston civic data
Which city departments are falling behind on 311 requests — and is the problem getting worse? A staffing intelligence dashboard built from public data.
Problem
The weekly operations review surfaces the same question week after week: is this department struggling, or is it just noise? Boston's 311 data is public and continuously updated — but raw case counts don't tell you whether a backlog is building or easing. By the time the signal is obvious, a council office has usually gotten there first.
Loading department data…
How it works
Boston publishes every 311 service request — every pothole report, every missed trash pickup, every noise complaint — as open data. The dataset updates continuously and goes back years. This dashboard tracks weekly open vs. closed ticket counts and median closure time for each of the nine city departments that handle 311 work. When both metrics worsen for two or more consecutive weeks, the department is flagged — giving an operations manager a clear signal before a backlog becomes a crisis.
The city's data lives on the Analyze Boston CKAN platform, split into per-year CSV resources. A nightly pipeline fetches new cases and upserts them into a case_events table in Neon Postgres — one row per 311 case, keyed on the city's case ID. A second pass aggregates case data into ISO-week buckets per department, which the API route serves to the dashboard.
A few deliberate choices in the methodology:
- Median, not mean. A single outlier case open for two years drags the mean. Median gives a better read on what a typical resident actually experiences.
- Both conditions must hold. A department is only flagged when backlog is growing (opened > closed) AND median closure time is increasing — a single bad metric in isolation is noise, not signal.
- Two-week minimum for red. A single stressed week earns a yellow watch flag. It takes two consecutive stressed weeks to turn red. A calm week resets the counter entirely.
Stack
What's next
The current dashboard serves one decision well: an operations manager needs a signal, before a weekly review, about which departments are showing signs of stress. Extending it to serve adjacent decisions requires a few specific additions.
AI narrative summaries are already in progress. When a department is flagged, the next version will generate a plain-English paragraph — what changed, over how many weeks, how severe — that an ops manager can bring directly into a staffing conversation without reformatting the data.
Per-district breakdowns would unlock a different user: a district chief who needs to know whether a backlog is concentrated in their geography or spread citywide. Department flags give you the what; district-level data gives you the where.
Seasonality-adjusted flagging would separate staffing problems from volume spikes. More pothole reports in April isn't a PWD failure — it's spring. Accounting for year-over-year volume patterns would make red flags meaningfully rarer and more credible.
A cost-per-case layer would make this useful in a budget conversation. Right now the dashboard shows operational stress; it doesn't translate that into dollars. Adding median cost-per-case by department turns a backlog trend into the beginning of a headcount justification.