Delivering a large asset tree end to end, from recursive SQL to progressive prefetch

claim 1.6dependent — cites claim 1 (Dynamox)

Delivered search and fast navigation over very large asset trees end to end, with recursive SQL on the backend and a search UX with in-memory caching and progressive background prefetch on the frontend, eliminating the repeated loading that made the most-used flow slow.

impactDelivered search and fast navigation over large asset trees end to end across database, backend, and frontend, in the module's most-used flow.Eliminated the repeated loading that made browsing large trees slow, via caching and progressive prefetch.Added asset search where there was none, with matches shown in context.Qualitative: a clear improvement to perceived performance and UX in a high-traffic flow; no hard before/after metric was captured.

Context

This is my strongest evidence of full-stack ownership and performance thinking. Because I owned the database, the backend, and the frontend, I could put each part of the solution at the layer where it belonged, traversal in the database and latency-hiding in the client, instead of forcing one layer to compensate for another.

It also shows a product-level UX decision made as an engineering trade-off: choosing to reveal search matches by expanding the tree rather than filtering it changed both what the user sees and how data has to load.

Problem

When building or editing an inspection route, users navigate a hierarchy of assets that can be very large. They needed to see asset descriptions and to search assets by name, but the tree loaded slowly and there was no search. In the module's most-used flow, that meant repeated waiting and no way to jump to a known asset.

Constraints

—The data is deeply hierarchical and large. Finding matches and showing them in context means traversing a big tree, and the naive approach is a cascade of queries per level.

—Search over a tree has a UX fork with data consequences. Do you filter the tree down to matches, or reveal matches in place? The choice changes what the user understands and what data you must load.

—Perceived performance is the real target. Even a fast backend feels slow if the client blocks on every expansion, so the latency had to be hidden, not just reduced.

—I owned all three layers, so every trade-off between doing work in SQL, in the API, or in the client was mine to get right.

Decision

I put each responsibility at the layer suited to it.

—Traversal in the database, via recursive SQL. A recursive query finds matching assets and walks up to their ancestors in one pass, so the server returns matches already in their tree context instead of the client stitching together many requests. I deduplicated matches and computed "has children" cheaply so nodes render correctly without extra round-trips.

—Search that reveals rather than filters. On the frontend I debounced the query and chose to expand the nodes of the matches in place, with navigation between results, rather than collapse the tree to matches only. This keeps each result legible in its real hierarchy.

—An in-memory cache per search term, so repeating or refining a search doesn't refetch what's already known.

—Progressive, level-by-level prefetch in the background, so the next levels are already loading before the user expands them, which hides latency on the common path.

—Documented the use cases (including alternative flows for the different actors) and rolled out behind a feature flag, staging before production.

Trade-offs

—A recursive query over many per-level queries. One recursive traversal returns matches with their ancestors in a single pass, avoiding a chatty cascade, at the cost of a more complex query to own and reason about.

—Expanding matches in place over filtering the tree. Revealing results in their real hierarchy preserves context and orientation, where a filtered list would be simpler but strip the structure users rely on. I accepted more involved loading logic to keep the result meaningful.

—Progressive background prefetch over on-demand loading. Prefetching hides latency on the most common path at the cost of doing some fetching the user might not ultimately need, a good trade in the module's busiest flow.

—A per-term in-memory cache over refetching. Caching trades a little memory and cache bookkeeping for the elimination of repeated loading during a search session.

Impact

—Delivered search and fast navigation over large asset trees end to end across database, backend, and frontend, in the module's most-used flow.

—Eliminated the repeated loading that made browsing large trees slow, via caching and progressive prefetch.

—Added asset search where there was none, with matches shown in context.

—Qualitative: a clear improvement to perceived performance and UX in a high-traffic flow; no hard before/after metric was captured.

Lessons Learned

Reusable engineering knowledge I carry forward from this:

—Push hierarchy traversal into the database. A recursive query that returns matches with their ancestors beats a per-level request cascade the client has to orchestrate.

—A search UX choice is an engineering decision. "Reveal in place" vs. "filter down" changes both comprehension and the shape of the data you load, so decide it deliberately.

—Hide latency, don't just reduce it. Progressive prefetch and per-term caching make the common path feel instant even when some work remains.

—Owning every layer lets you solve each problem where it belongs. That is the biggest advantage of true full-stack ownership.

Evidence

—Delivered solo across database (recursive SQL), backend, and frontend.

—Feature shipped to production behind a feature flag after staging.

—Documented use cases including alternative actor flows.

—Verified against the tracker (2026-05-19 to 2026-06-17): two items, the endpoint that exposes an asset's description to the route forms, and the recursive traversal itself. The recorded before/after is N×3 sequential queries replaced by 3 total: one recursive CTE for all descendant nodes, then one batched query each for measurement points and checklists, with the tree assembled in memory in O(n) via an id→node map.

—Verified architectural work, not just a query: the tree-assembly rules (grouping a single leaf as a direct node versus N leaves under a grouper, the ordering rule, and `hasChildren` computed from the assembled children rather than a database subquery) were extracted out of the repository adapter into a domain-layer assembler, restoring the hexagonal boundary the previous code had crossed. A sentinel root node removed the need for separate arrays for root versus nested children.

—Verified bug found and root-caused in the same pass: leaf machines with no child nodes but with direct measurement points returned nothing recursively, because an early return on "no descendant rows" discarded them before the leaf query ran. Fixed by including the root's own id in the leaf lookup.

—Verified frontend work: eliminated a double fetch on add (a one-level expand immediately followed by a recursive one), cached the recursive result into the client tree so a later expand costs nothing, and switched the saga from latest-wins to per-action handling so adding two machines quickly no longer cancels the first. All of it gated by a feature flag, with the legacy path untouched when the flag is off.

—Source (private): Jira items in the inspection domain, 2026-05 to 2026-06; consolidated career knowledge base.