How Flexport Tracks Warehouse Turnaround Time

At Flexport, warehouses (also known as Container Freight Stations, or CFS for short) are vitally important to the freight forwarding services we offer; in many cases one of our CFSs — generally in Chicago or LA — is the last stop for a freight shipment before it reaches our customers. Moreover, our CFSs are the portion of a shipment’s journey where Flexport asserts the most physical control; when the cargo is under Flexport’s roof, our operations team and warehouse associates can move freight around in a way they see fit. This stands in stark contrast to other parts of the shipment’s journey — like the voyage at sea or in the air — where we are, to some extent, at the mercy of the carrier.

It’s important to note that Flexport CFSs are distinct from client warehouses. In particular, client warehouses are designed to store inventory and fulfill customer orders; CFSs, on the other hand, generally perform two main functions. First, they receive cargo on trucks which are unloaded by CFS associates — we call this an inbound delivery. Second, once the cargo has been organized and processed in the system, cargo leaves the CFS on a truck, an event known as an outbound delivery. The time difference between when cargo first arrives at the CFS on an inbound delivery and when it departs on an outbound delivery is known as the turnaround time.

We want to turn around freight quickly because the faster it arrives to our customers, the better! However, Flexport’s Warehouse App has not displayed the turnaround time… until now. As a (virtual) summer intern on the warehousing engineering team, my first project was to build a way to track the turnaround time of freight in our warehouses.

Understanding the Domain

Before jumping into the code, I had to understand how the turnaround time for a particular shipment would be calculated. Let’s use the following diagram of an ocean shipment containing multiple customers’ cargo — aka an LCL shipment— traveling to a destination CFS as a guide. Note that this diagram was created from the perspective of a Flexport customer.

A shipment generally passes through two CFSs during its lifecycle: one at “origin” and another at “destination”. These terms are relative to a specific shipment’s journey — for example, if a customer places an order to move freight from their factory in China to their warehouse in the US, the associated shipment may have an origin CFS in Shenzhen and a destination CFS in LA.

A customer’s freight first travels from some port to our CFS. The freight then spends some time there before being trucked to its final destination, which is most often that customer’s warehouse. Hence, the calculation is as follows:

Turnaround Time = 
      Departure time (from the CFS to the client) -
      Arrival time (at the CFS from the port)

If the cargo has not yet departed from our CFS, then there’s a slightly different calculation:

Turnaround Time = 
      Current time -
      Arrival time (at the CFS from the port)

Armed with the requisite domain knowledge, I was ready to build the feature.

Building the Feature

At a basic level, the Warehouse App is designed to provide a granular view of all the cargo coming in and going out of a particular warehouse.

The main design objective coming into the project was to make it easier for our CFSs to prioritize work and, therefore, meet our 24 hour turnaround time SLA (Service Level Agreement).

It was clear from the beginning that we’d need to add another column to display the current turnaround time for each delivery; the only question was, how could we present the information in the most intuitive way possible?

Ultimately, we decided to build a sortable and filterable column, with indicators attached to large turnaround times so CFS associates can easily identify cargo that is nearing or has exceeded the 24 hour SLA. If cargo has already left the CFS, however, we hide the indicator entirely to avoid confusion.

Engineering Work

The warehousing team’s stack is Flow-typed React, a Ruby on Rails backend, a GraphQL API layer, and PostgreSQL for our DBMS. Since I already had experience with React, I chose to implement the backend first to derisk the more unfamiliar parts of the project.

First, I created a new GraphQL field and implemented its resolver to calculate the turnaround time (in hours). Here’s the pseudocode:

When it came time to implement sorting/filtering, I found myself stuck; the turnaround time calculation dealt with ever-changing time-series data, so if the outbound delivery hadn’t left the CFS (and thus there was no recorded departure time), we’d need to subtract the current time from the inbound delivery’s arrival time to get the current turnaround time.

Because of this, it was not trivial to store up-to-date turnaround times in the database, as we’d need a CRON-like solution to constantly update the tens of thousands of warehouse deliveries in our system. Moreover, it wasn’t feasible to load all the warehouse deliveries into memory and calculate the turnaround times whenever the user went to sort or filter.

We were able to do the calculation in Ruby for the resolver because of GraphQL pagination — we only ever needed to perform 25 or so calculations since that’s all that’s required for one request. When sorting or filtering, though, we needed to calculate and compare the turnaround times for all the warehouse deliveries accessible to the user, not just the ones currently displayed on the page.

All that to say, I couldn’t proceed with the same implementation I used for the resolver; instead, I had to tap into my database knowledge from a single university course and write some custom SQL. After much trial and error, sorting and filtering was working smoothly. Here’s the pseudocode for calculating the turnaround time, omitting any comparison operators for filtering/sorting:

For the curious SQL-goers, here is some documentation for the COALESCE and CURRENT_TIMESTAMP functions.

The frontend work consisted of fairly standard React and GraphQL, including some Flow annoyances. Especially as a new engineer, Latitude (Flexport’s design system) provided all the building blocks I needed for the UI, with mostly intuitive APIs. In particular, all I needed to use was the Text, Tooltip, and some custom styling; the final UI was pretty slick, if I do say so myself.

Shown above is the Warehouse App with the new turnaround time column (labeled Time in WH). A filter (shown at the top right) is being applied to look at deliveries with turnaround times past 24 hours, and the resulting warehouse deliveries are being sorted in ascending order by those times.

Visiting the Warehouse

I’d be remiss if I didn’t mention the highlight of my summer internship: visiting Flexport’s Chicago CFS! I was able to meet and talk with the CFS manager, Kevin, and some of the CFS associates, Ruben and Lizette, see the very cargo we were displaying in the Warehouse App, and witness deconsolidations and container unloadings that I’ve only ever talked about in a hand-wavy way.

Before my virtual internship ended, I was able to visit Flexport’s Chicago CFS with our team’s designer, Mark, and product manager, Matt. Pictured (left to right): Mark, myself, and Matt.

Being able to visit the CFS was emblematic of the main reason I chose to intern at Flexport: the software’s direct connection — and importance — to the real world. The majority of data at Flexport corresponds 1:1 with physical goods moving around the world. What’s more, for the last 6 months, a large number of those goods have been PPE, proving that, even as a software engineer, there’s potential for impact on some of the world’s most pressing problems.

Lessons Learned as an Intern

From an engineering perspective, my takeaways from Flexport are substantial. My team, and in particular my mentor, Andrew, taught me a great deal about Ruby on Rails. I was also able to learn a new type system in Flow. Furthermore, I significantly improved my ability to communicate with non-technical stakeholders like designers, product managers, and business leaders.

More importantly, though, I gained a better sense of what kind of real world impact I want to have with my career and the types of people I’d like to collaborate with along the way. My experience as an intern was overwhelmingly positive for many reasons, but especially because I validated my original reason for joining Flexport: unlike other software companies, where the focus largely boils down to moving bits, Flexport builds software to move atoms.

There are many individuals to thank for my internship. First, Moira Reilly and Lakshmi Gunanayagam for setting up an amazing virtual internship program, and my fellow interns for being so engaging and open with one another.

Next, to my ERG (Employee Resource Group) buddy throughout the summer, Pamela Yuan, for providing consistent support for all things Flexport and to the entire Warehousing team — Mark Phan, Matt Salefski, Shubham Bansal, Riley Klingler, Julia Xu, Aditya Gande, and Ethan Goldberg — for setting me up to succeed with impactful projects and giving me meaningful, constructive feedback every step of the way.

Finally, I want to thank my mentor for the summer, Andrew Paulson, who took my freight forwarding and Ruby on Rails questions in stride, and who continues to maintain the same light-hearted attitude he had when we first (virtually) met.