API Performance Optimization - Up to 23x Faster

Context: This work was done on an insurance claims and fleet management platform, where an Incident represents a claim event tied to a fleet object, contract warranties, involved users, and a lot more. The backend is written in TypeScript and Express, using Sequelize (via sequelize-typescript decorators) as the ORM, and a PostgreSQL database. I improved the performance of two key API routes: getUserIncidentsList and getAllUsersLinkedToIncidentById, used respectively on the main user dashboard, and on an Incident's page. Here are the improvements I made for that purpose. Reducing data transfer Most Sequelize includes were fetching entire related rows when only a handful of columns were used downstream. Adding explicit attributes lists to nearly every include cut both query cost and payload size. I removed several unused includes (damage records, created-by/verified-by/user-in-charge user objects with their nested Keycloak data) entirely after confirming they weren't consumed by the response. Moving filtering closer to the data A field-type filter that previously ran in application code after fetching all fields was moved into the SQL where clause, so the database returns only the rows needed. I also trimmed the response post-fetch, stripping empty-value fields before sending it to the client, reducing payload size for large incident objects. Eliminating N+1 query patterns The largest gain came from reworking getAllUsersLinkedToIncidentById. The previous implementation resolved fleet access per candidate user inside a loop, issuing a new set of nested queries for each one. I reworked it to fetch the incident's object data and the client's fleet/group hierarchy once, then check each user's business units, companies, and perimeters against that data in memory. This turned a query count proportional to the number of candidate users into a small, constant number of queries. I also added an early return for confidential incidents, skipping the entire client/fleet resolution path when it isn't needed. Query shape by use case Where a route needed both a full incident view and a lighter linked-incidents variant, I parameterized the include and attribute lists instead of reusing the heaviest shape everywhere, so each query path only pays for what it actually uses. Result Together, these changes cut both the number of database round trips and the volume of data transferred per request. getUserIncidentsList, the heaviest of the two routes, dropped from roughly 11.5 seconds to an average of 500ms, a 23x improvement. getAllUsersLinkedToIncidentById went from just over 5 seconds to around 350ms, a 15x improvement.