Fix a slow OnMission list without throwing Prisma away

OnMission’s assignment board looked simple in Prisma and expensive in Postgres. Follow these steps on any HR list page that loads more columns than it renders.

OnMission (and LOMA, which uses the same Prisma + Postgres shape) made schema changes easy. The open-assignments endpoint did not. Here is the path I use before I write raw SQL.

Step 1: Keep Prisma on the write path

Creates, updates, and transactions stay in Prisma. Nested writes and typed input beat hand-rolled SQL for employee records.

Step 2: Select only the columns the list paints

If the board shows id, title, and due date, do not include the employee relation tree.

import { prisma } from "../db";

export function listOpenAssignments(orgId: string) {
  return prisma.assignment.findMany({
    where: { orgId, status: "open" },
    select: { id: true, title: true, dueAt: true },
    orderBy: { dueAt: "asc" },
    take: 50,
  });
}

Step 3: Add the index that matches filter + sort

model Assignment {
  id     String   @id @default(cuid())
  orgId  String
  status String
  title  String
  dueAt  DateTime

  @@index([orgId, status, dueAt])
}

Step 4: EXPLAIN the query before you blame the ORM

• If EXPLAIN shows a sequential scan on a growing table, the query shape is the bug.
• Repeat this on LOMA employee tracking lists before they hit production size.
• Only drop to $queryRaw when select + index still scan the table.