In March we released version 4.16 of CxAlloy TQ. It was a moderately sized release in terms of new features, but behind the scenes it was one of the more extensive updates we’ve ever done. I’d like to give you a peek into the engineering that went into this update.
One of the marquee features of CxAlloy TQ is that we track every change to your data. Any time an issue status is changed, a checklist line is answered, a meeting is created, or anything else happens, we record the change and who made it.
Having a full history of every data change in the system has proven incredibly useful. We use it to power the syncing in our mobile apps and to dictate what emails we need to send (and the content of those emails). We’ve used it to rebuild the statistics for every project in the system (twice). And of course we use it to show you what has been happening in your projects.
Unfortunately, the code to get at all this wonderful data was… not great. In fact it was kind of terrible. It was hard to understand. Because it was hard to understand, it was difficult to update or add to. History code quickly got a reputation among the team. For years there was a comment in the primary file: “HERE BE DRAGONS!!!”.
Worst of all, it was slow. I’m sure if you ever spent time viewing project or item history you dealt with long waits. And that wasn’t just bad for you – because history didn’t work with the database in an efficient way, it meant that the database spent a lot of time getting history data. That could potentially delay other requests and slow things down for everybody.
Given all this we knew we needed a new approach. The issue is that it’s an inherently difficult undertaking. Our history table is massive, sitting today at over 125 million rows. That creates challenges for making efficient database queries. And each history item could potentially need information from any other table in the database. Add to that the need to convert raw change data into nice English sentences like “Jacob Terry changed the status of Checklist Section Controls from Not Started to Started in Checklist AHU-1” and you’ve got a pretty complex project.
Learning from History
After a lot of experimentation and false starts we landed on an approach that divided the code into three discrete tasks:
- Finding the set of related items.
- Finding the history for those items.
- Converting those history records into standardized “Events” with all the necessary information for displaying them.
Now when you load the history for an issue we go through these three steps. First, we find all the items that are related to that issue. That may include issue comments and uploaded files. Then, we get the history records not only for the issue itself, but for those related items as well. Finally, we convert those history records into standard Events that contain all the information we need to show a nice sentence like “Jacob Terry changed the status of this issue from Open to Closed”.
By separating the history into these three tasks we’ve been able to optimize each part so that it runs much faster. It’s also made the code far easier to understand so that it is easy to add new functionality or improve existing functionality.
Events, Compounded
The real meat of our new structure is the concept of an Event. An Event is a standardized representation of a data change. It contains detailed data on the state of the record before the change and after the change. It contains the ID and name of any related items (i.e. issue “RVR-7-2”; checklist “AHU-2”). It also contains components of the sentence we show, such as the verb (“uploaded”, “added”, “edited”), preposition (“from Open to Closed”), subject (“Jane Doe”), and so forth.
Some of this information (such as the name of an item) requires database lookups. We use fast, efficient queries against primary keys to get that information, and we cache the results so repeated requests don’t require a second trip to the database.
We also created a second type of event, a Compound Event. Compound Events are what you see when you view Project Activity. Compound Events represent a group of similar Events. We group Compound Events not only on the nature of the event (adding an issue comment vs. answering a checklist line) but also on when they happen. If you answer checklist lines in quick succession, for example, those will get grouped together into one Compound Event. If you answer one line in the morning and another one after lunch, that probably won’t get lumped into the same Compound Event.
Compound Events are great for a few reasons. On the engineering side they don’t require all the database lookups that regular Events do so they are even more performant than Events. We can also adjust the time-based grouping to whatever interval we want (a minute, an hour, a day, etc.) so it gives us a lot of future flexibility to create things like weekly activity summaries.
On the UI side they allow you to get a sense of what’s been going on in your project without overwhelming you with a giant list of events. You can then selectively expand the Compound Events you are interested in to get more detail.
Additional Benefits
Our overhaul delivered the biggest benefit we were looking for: improved performance. But we also were able to leverage the new, superior code structure to add in a lot more improvements.
- Previously we hid checklist and test line changes. We now show all changes, including checklist and test line changes.
- Previously we hid changes that happened if something was a draft, but related events (like commenting on a draft issue) would still be shown. Now we hide those events as well.
- We’ve added many more filters to the project activity page.
- Previously activity for a person was limited to only the past seven days. Now we show all activity.
- Previously the history included in PDF reports was less detailed. Now it shows the exact same information as shown on the web.
And that isn’t even all the improvements!
History of History
It took us a long time to make this happen. The first code for this new approach was added in October of 2015, and we had been discussing it for quite a while before that. For all those that waited through long load times when viewing project activity or other history, thank you for your patience.
“Here be dragons no more.”
