Optimizing Executive Workflows: Efficient Data Aggregation in NestJS

In our ongoing development of the crm-saas-backend, we recently identified a need to streamline how executives manage their daily tasks. Specifically, the system required a more performant way to retrieve a complete list of pending assignments that not only tracks the task status but also surfaces the associated client names in a single operation.

The Challenge: Reducing Database Round-trips

Initially, fetching pending tasks meant making multiple calls to our data layer. An executive would request their pending list, and the backend would first retrieve the task IDs, then perform subsequent lookups to join client information. This approach is similar to visiting a grocery store: instead of picking up every item in one trip, you return to the shelf for every single ingredient. It’s inefficient and adds unnecessary latency to the user experience.

The Implementation

To solve this, we leveraged Prisma's relation querying capabilities within our NestJS service layer. By defining clean relations between our task entities and client profiles, we can fetch the combined data set in a single query execution.

@Injectable()
export class TaskService {
  constructor(private readonly prisma: PrismaService) {}

  async getExecutivePendingTasks(executiveId: string) {
    return this.prisma.task.findMany({
      where: {
        executiveId,
        status: 'PENDING',
      },
      include: {
        client: {
          select: {
            name: true,
          },
        },
      },
    });
  }
}

By utilizing dependency injection to provide the PrismaService, we ensure our business logic remains decoupled from the infrastructure layer, making our services easier to test and maintain.

The Outcome

This refactoring condensed our database interaction significantly. The front-end now receives a fully hydrated object containing both task details and client identity without requiring secondary API calls. This reduction in overhead results in a snappier interface for our users and a lower load on our database server.

Takeaway

Whenever you find yourself performing N+1 queries to gather related data, stop and re-evaluate your ORM relations. Use include or select statements to retrieve nested dependencies in one go; it’s a simple change that drastically improves performance and reduces boilerplate code.

Generated with Gitvlg.com