Implementing Cross‑Category Collaborator Search in pcview (Next.js 13 + Prisma)

TL;DR: Added a public API endpoint and service that pulls every asset assigned to a collaborator number across six device categories. The change required schema extensions, new Prisma queries, UI updates, and a thin caching layer to keep the dashboard responsive.

---

The Problem

Our internal dashboard let users filter assets by name or by device ID, but the field that actually ties an asset to a person – collaboratorNumber – was only stored in the PC table. When a technician needed to see all equipment (PCs, monitors, UPS, phones, POS terminals, and mobile lines) assigned to a given collaborator, they had to run six separate queries in the UI, copy‑pasting numbers back and forth.

The symptom was a noisy UI and a high number of round‑trips to the database, which showed up in the logs as:

GET /api/search/collaborator?number=12345  → 6 × PrismaClientKnownRequestError: Record not found

The real problem: no single source of truth for collaborator‑centric searches, and the front‑end lacked a unified result set.

---

What I Tried First

My first instinct was to add a client‑side aggregation: fetch each category separately via the existing endpoints, then merge the arrays in GlobalSearch.tsx. I wrote a Promise.all wrapper that called:

• /api/search/pc?collab=12345
• /api/search/monitor?collab=12345
• …etc.

The UI displayed the merged list, but the approach had two fatal flaws:

1. Performance – Six parallel DB calls still hit the DB six times per search, saturating the connection pool under load.
2. Consistency – Each endpoint had slightly different response shapes; the merge logic became brittle and broke whenever a new field was added to any category.

I rolled back the changes and decided to push the aggregation into the back‑end where we could use a single Prisma transaction.

---

The Implementation

1. Extend the Prisma schema

We needed a collaboratorNumber column on every device model. The first commit added it only to PcDevice, but the later commit (751f510c) added the field and a “falta info” flag to the other models.

// prisma/schema.prisma
model PcDevice {
  id                 Int      @id @default(autoincrement())
  serialNumber       String
  collaboratorNumber String?   // New nullable field
  // ...
  // Pendiente de cruzar contra el listado de cola
  missingInfo        Boolean  @default(false) // “falta info” indicator
}
model Monitor {
  id                 Int      @id @default(autoincrement())
  collaboratorNumber String?
  missingInfo        Boolean  @default(false)
}
model Ups {
  id                 Int      @id @default(autoincrement())
  collaboratorNumber String?
  missingInfo        Boolean  @default(false)
}
// … same for MobileLine, OfficePhone, PosTerminal

Running npx prisma migrate dev --name add-collaborator-fields generated the migration and updated the DB.

2. Service layer – single source query

File added: src/services/collaboratorSearch.service.ts

// src/services/collaboratorSearch.service.ts
import { prisma } from "@/lib/prisma";

/**
 * Returns all assets (PC, Monitor, UPS, MobileLine, OfficePhone, PosTerminal)
 * that belong to the given collaborator number.
 */
export async function searchByCollaboratorNumber(number: string) {
  const [
    pcs,
    monitors,
    ups,
    mobiles,
    phones,
    pos,
  ] = await Promise.all([
    prisma.pcDevice.findMany({
      where: { collaboratorNumber: number },
      include: { assignedTechnician: true },
    }),
    prisma.monitor.findMany({
      where: { collaboratorNumber: number },
    }),
    prisma.ups.findMany({
      where: { collaboratorNumber: number },
    }),
    prisma.mobileLine.findMany({
      where: { collaboratorNumber: number },
    }),
    prisma.officePhone.findMany({
      where: { collaboratorNumber: number },
    }),
    prisma.posTerminal.findMany({
      where: { collaboratorNumber: number },
    }),
  ]);

  // Normalise shape for the front‑end
  const flatten = (type: string, items: any[]) =>
    items.map(item => ({
      type,
      id: item.id,
      serial: item.serialNumber ?? item.imei ?? item.mac,
      collaboratorNumber: item.collaboratorNumber,
      missingInfo: item.missingInfo ?? false,
      assignedTechnician: item.assignedTechnician?.name ?? null,
    }));

  return [
    ...flatten("PC", pcs),
    ...flatten("Monitor", monitors),
    ...flatten("UPS", ups),
    ...flatten("Mobile", mobiles),
    ...flatten("Phone", phones),
    ...flatten("POS", pos),
  ];
}

Key decisions:

• Parallel queries inside a single service keep the latency low while still using a single DB connection pool.
• Normalization ensures the UI only has to handle one interface (type, id, serial, …).

3. API route

File added: src/app/api/search/collaborator/route.ts

// src/app/api/search/collaborator/route.ts
import { NextResponse } from "next/server";
import { searchByCollaboratorNumber } from "@/services/collaboratorSearch.service";

export async function GET(req: Request) {
  const { searchParams } = new URL(req.url);
  const number = searchParams.get("number");

  if (!number) {
    return NextResponse.json(
      { error: "Missing collaborator number" },
      { status: 400 }
    );
  }

  try {
    const results = await searchByCollaboratorNumber(number);
    return NextResponse.json(results);
  } catch (err) {
    console.error("[collaborator search] ", err);
    return NextResponse.json(
      { error: "Internal server error" },
      { status: 500 }
    );
  }
}

We used the new Next.js 13 route convention (route.ts) to keep the endpoint stateless and cache‑friendly. Errors are logged with a clear prefix for observability.

4. Front‑end integration

GlobalSearch component

src/features/search/GlobalSearch.tsx grew from ~100 lines to ~300, adding a new tab and handling the new endpoint.

tsx
// src/features/search/GlobalSearch.tsx (excerpt)
import { useEffect, useState } from "react";
import { useSearchParams } from "next/navigation";
import { Search, Monitor, SmartPhone, Laptop, Server, Phone } from "lucide-react";

type Asset = {
  type: string;
  id: number;
  serial: string;
  collaboratorNumber?: string;
  missingInfo: boolean;
  assignedTechnician?: string | null;
};

export function GlobalSearch() {
  const params = useSearchParams();
  const collab = params?.get("collab");
  const [results, setResults] = useState<Asset[]>([]);
  const [loading, setLoading] = useState(false);
  const [error, setError] = useState<string | null>(null);

  useEffect(() => {
    if (!collab) return;
    setLoading(true);
    fetch(`/api/search/collaborator?number=${collab}`)
      .then(r => r.json())
      .then(data => {
        setResults(data);
        setError(null);
      })
      .catch(e => {
        console.error(e);
        setError("Failed to fetch assets");
      })
      .finally(() => setLoading(false));
  }, [collab]);

  if (loading) return <p>Loading assets…</p>;
  if (error) return <p className="text-red-600

---

*Part of my [Build in Public](https://dev.to/zaerohell) series — sharing the real process of building SaaS projects from Playa del Carmen, México.*

*Repo: `zaerohell/pcview` · 2026-09-11*

\#playadev #buildinpublic