Ten years in production for a city public works department

Context

A public works department knows an enormous amount about the city: which streets were swept, where the plows went, how many tons went over the scale, which routes changed this season. Almost none of it was usable, because it lived in dozens of systems bought separately, over two decades, from vendors who had no reason to talk to each other.

The way that normally gets solved is one interface at a time. A talks to B, so pay a vendor to build it. Then B needs C. Then A needs C. Each one is a separate procurement, each is only as good as whoever built it, each has its own documentation, and each one ties the department tighter to a system it can no longer afford to replace.

What came out the other end was spreadsheets. Excel, CSV, tab-delimited files. Some vendors handed over data, some sold a reporting module instead. There was no live view of the department's own operation anywhere.

The operational question, meanwhile, is simple and constant. Which streets have actually been done, right now.

What we built

An enterprise service bus, so that a system connects once rather than once per partner. The bus translates whatever each vendor emits into a common set of attributes and events, which means no system needs to learn another system's vocabulary, and replacing a vendor stops being a rewrite of everything attached to it.

Then a data warehouse behind it: one database, one data model, fast enough to answer an analytical question in seconds, and reachable from the tools the department already had rather than from a bespoke portal. Parking and towing arrive as enforcements. Fleet vehicles arrive as assets, alongside asset management. Service requests keep their own fields instead of inheriting the 311 system's.

One architectural rule did a lot of work: every DPW system reaches 311 through the bus, and none of them touch the 311 system directly. The department needs a handful of licenses on that side rather than one per integration, and when the 311 vendor changes, one connection changes.

On top of that, real-time operations tracking with crews entering work from the field, spatial areas recalculated as routes change, and feeds out to the department's own dashboards, so the people running the operation and the people reporting on it are reading the same numbers. Events can trigger work rather than just being recorded: boot and tow alerts run that way today.

Around that: automatic real-time calculation of sweep inspector areas, police service areas, trash and recycling routes; GIS integration with ArcGIS Online and Pro; mobile data entry for field crews doing mowing, leaf collection and street cleaning; and tonnage data linked back to routes through vehicle assignment.

Why it was hard

The hardest part was not technical. It was that vendors want you to come to their system to get your own data, and several had never been asked for an API at all. Every one of those was a conversation, and the ones that went badly went badly early. Part of the job became advising the department on procurement language, so the next contract required an interface instead of hoping for one.

The rest was semantics. Every source system had its own idea of what a route was, what a completion meant, and how fresh the data needed to be. Reconciling that is unglamorous and it is most of the work.

The other difficulty is duration. Ten years covers four administrations, several CIOs, a pandemic, and a full turnover of the people who originally specified the thing. A system that depends on institutional memory does not survive that. Documentation, design docs for every interface, and knowledge transfer to department staff were deliverables, not courtesies.

What shipped

Reporting that used to arrive weekly, as a spreadsheet, now lands in seconds to minutes. Parking enforcement updates in one to two seconds. Tows and fleet GPS every five minutes. Service requests and work orders every fifteen. Five core feeds, none of them slower than a coffee break.

By 2019 that meant 4.5 million parking tickets with about four thousand added a day, 37,000 tows, 426,000 service requests, 160,697 fleet work orders, 26 million GPS points off the vehicle location system, and 4,200 vehicles tracked as assets. It has kept accumulating since.

It is read by executive dashboards during snow events and inaugurations, by the performance team for ad-hoc analysis, and by other agencies: OCTO for GIS and Open Data, HSEMA, DDOT, and the department's own snow team. A data warehouse that has outlived most of the systems feeding it, and it is still running.

The department's own reference, from their Deputy CIO: requirements changed frequently, which meant frequent changes to the database, scripts and web services, and the contractor was flexible and knowledgeable about identifying when and where changes needed to occur.

What we learned

Start the vendor conversations on day one. That is the single biggest roadblock, and it is a commercial problem wearing a technical costume. No amount of engineering gets you data a vendor has decided to keep.

Long engagements are won on documentation, not on cleverness. Ten years covers four administrations, several CIOs, a pandemic, and a full turnover of the people who originally specified the thing. The reason this contract keeps renewing is that when a new CIO arrives, everything is written down and the department's own staff can operate it.

Have the freshness argument early. We spent a chunk of the first years persuading the department to move its expectation of vendor data from weekly to real time. Everything good downstream depends on having won that argument.

And the harder half of that is cultural rather than technical. Once real-time data is actually there, somebody has to work differently to benefit from it, and that does not happen on its own. The useful mental model turned out to be treating each transaction in a business process as an interchangeable block: fewer duplicate builds handed to vendors, and less retraining every time a system changes underneath.