PROJECT

City of Chicago - SCSP

The City of Chicago is upgrading its technology system by system. In this engagement, we tackled a database that specifically manages all city-shared sidewalk projects. The intent was not to reinvent CDOT's workflow, but rather recreate and modernize their process by way of a source-of-truth application based on their current spreadsheet.

the PROJECT

Building a new database for construction data management.

What is Shared-Cost? CDOT's Shared-Cost Sidewalk Program is a yearly event where CDOT opens up applications for citizens that wish to pay directly for specific work to be done to sidewalks on their property. The city dedicates a fund to this program to pay for half of the work done for each citizen request that is approved. Only roughly 2500 applicants make the cut (with only a 12 hour window to apply!)

The Problem

The City of Chicago is using one massive spreadsheet to handle all of their shared-cost sidewalk projects from start to finish - including budgeting and quantity management.

The Goal

A dedicated interface to handle all of SCSP’s yearly data.

Create a new database to easily manage the construction, costs, and status of each shared cost project.

The Research

Untangling the web of data

The majority of research and discovery for this project was simply untangling the web of processes that make up their shared-cost program. Local government is especially tricky because of the myriad of departments and legal obstacles that makes everything move slower.

Because this was part of a larger engagement, we had already spent a significant amount of time understanding the larger scope of how work is managed. So, this time we spent 2 days talking directly to the teams involved with shared cost. We wanted to know exactly how they do their work, what tools they use to do it, where everything is tracked - and of course, all the pain points they were currently experiencing.

We met with the following departments:

Resident Engineers

Administration

Department of Finance

Construction CIC (Contractors)

Survey Team

Capturing the Current State

We gathered heaps of data from the 5 groups above - enough to last us weeks as we parsed through their process and made sense of it. First things first we needed to visually understand what exactly was going on behind the scenes of shared-cost.

My goal was to start at the journey level and work our way towards the granular step-by-step flow.

A web of databases and workflows

After understanding the groups involved and the data being sent back and forth, I found it necessary to refer to a database diagram that I created in our previous engagement with CDOT. We first started working with CDOT in 2018, and at the time had an elaborate workflow diagram capturing nearly all of their processes and workflows. Needless to say by the thumbnail below, it was an absolute behemoth. A portion of this flow is where our journey would begin. In addition to the image below, we captured the following current state documentation (that will be concealed on CDOT's behalf):

  1. Complete Current State Workflow
  2. Current State Database Mapping
  3. Current State Logistical Documents
  4. Many, many individual workflows

I'm using an intentionally low-res image to obscure important process - however, I think this picture gets the point across.

Envisioning the Future State

Our target workflow

All this data was helping us form the vision of the future SCSP database. In theory, it should be straightforward - collect all of the project requests in one place and track their status.

The new workflow would need to incorporate all of those disparate departments regardless. After all, we were NOT revolutionizing how they did their work. They simply wanted reliable technology to make it less redundant and fragile.

Instead, most departments would now funnel their information directly into the new database, manually, by import, or through API connections to other databases. If not that, then at least the data could get into the right hands and be imported directly.

There were a number of following documents that I'll conceal which detailed the future vision workflow:

  1. Future State Database Diagram
  2. Future State Complete Workflow
  3. Various feature workflows
  4. Future State Site Map

a Glimpse Into the Final Product

While I cannot share most of the details of the SCSP project due to it being a very important part of Chicago's local government, I'd love to share a small vision of what we ended up working towards.

At a key level, the vast and expansive sea of Excel spreadsheets were the core root of why their current state was dysfunctional. Paired with paper processes anda multitude of departments, the pain points were exacerbated.

At its core, the new database would capture the data of those many excel spreadsheets into one unified workflow. No more digging around and adjusting logic at a page to page level - you can do it all from their new portal.