Skills matrix

Context

At Klara, I built the first implementation of a skills matrix for managers and administrators (HR leads). It gives them an overview of the skills of every employee they supervise, so they can suggest training, track expiring certifications and plan replacements.

The matrix had to:

• cross every skill with every managed employee
• flag an expired certification at the employee level
• support filtering
• show the details of each rating (employee, managers) on click

A skill is computed from the answers to specific questions in the review questionnaire. Several answers can be candidates: among the managers' most recent answers, the best one wins. But the answers table had no marker for the right answer at the time, so it had to be computed on every read.

An average account has about 3,000 employees and around a hundred skills (the number varies by account), which is roughly 300,000 cells.

Approach

First version: ship on time by reusing what existed

The project was scoped with Shape Up. To make sure it shipped complete within the cycle, tests and reviews included, I kept the scope tight and reused as much as possible:

• On the frontend, the matrix is virtualized with react-window, which the project already used. Only the cells visible on screen are rendered, which keeps the DOM light.
• On the backend, I reused the existing methods that compute the right answers, paginated according to the user's scroll. The computation could be very slow across all employees, especially for administrators, who see everyone.
• A small overscan prefetches the next data before the user reaches it.

1. Virtualized matrix (react-window)↓→
2. Paginated query on scroll↓→
3. Right-answer computation↓→
4. Answers table

Later: compute on write instead of on read

The team then evolved the matrix a lot:

1. Flag computed on write. Another team member added a flag, computed whenever an answer changes, that points directly to the right row. With no need to look for the right answer on read, pagination could be removed and the data loaded in one go.
2. Materialized view. A materialized view built on that flag was then tried to precompute the matrix data. But the answers table changes very often and the matrix must reflect the latest values quickly. Refreshed every 5 minutes by a Rails job, the view was too often out of date, and every refresh recomputed the whole dataset. It was dropped.
3. JSON endpoint and client-side filtering. All the data in the user's scope is served by a JSON endpoint, and filters run on the client, with no further server call.

1. Answer updated↓→
2. Flag computed↓→
3. Answers table (flag)↓→
4. JSON endpoint↓→
5. Client-side filtering

Outcome

First version: The matrix shipped within the cycle, and virtualization delivered a light DOM and smooth rendering, even with thousands of rows. Pagination, however, did not bring the expected gain. Pages were sorted by employee and skill name, but on the server the right answers were computed for the whole dataset before being sorted and sliced. The first render was slightly faster, and pages already loaded came back instantly from the Apollo cache. But every new page redid the full computation, which showed up as waiting while scrolling.

Second version: A fairly fast first render, sorting and filtering on the client, thanks to the index on the flag. With accounts of several thousand employees, and growing, the full load time remains the thing to watch.

In hindsight

Pagination only saves time if the server can produce a page without processing the whole dataset. Here, the sort was on names, which are very cheap to sort: paginating employees and skills by name first, then computing the right answers only for those on the page, would have been enough. The cost of a page would then no longer have depended on the size of the account.

Had the sort been on computed values, such as a skill level, the alternative would have been to compute and sort once, keep that result in a server-side cache, and serve each page as a simple slice of that cache.

Possible improvements

• Measure where the load time goes (query, serialization, network, rendering) before optimizing.
• In the database: partitioning the answers table by the flag if it grows much larger than available memory.

Takeaways

• A first version that reuses existing code ships fast and lets you watch real usage before optimizing.
• Pagination speeds nothing up if every page forces the server to process all the data.
• When data is read far more often than it is written, it is better to compute once on write than on every read.