Under the Hood: Fixing Unbounded Queries in listLandlords, listTenants, and the Stats Procedure

Release: v0.1.182 · PERF-15
Category: Performance — Data Patterns

---

The problem

Two tRPC procedures — listLandlords and listTenants — were fetching every row in their respective tables on every call. There was no LIMIT, no cursor, and no server-side filtering. A form dropdown that renders a list of landlords for an agency with 300+ records was silently loading all 300+ rows from the database, serialising them, and transmitting them to the client — even though only the first handful were ever visible.

Separately, the stats procedure was counting properties, landlords, tenants, and tenancies by doing .select({ id }) on each table and then measuring the length of the returned array in application code. Four full column scans for four numbers.

These patterns are individually small, but compound quickly: a dashboard that loads stats and two dropdowns on mount was triggering five unbounded queries simultaneously.

---

What was fixed

Pagination on listLandlords and listTenants

Both procedures now accept pagination parameters drawn from the existing paginationInput schema in src/lib/pagination.ts. The default page size is 50 records.

// Before
const landlords = await db.query.landlords.findMany();

// After
const landlords = await db.query.landlords.findMany({
  where: search ? ilike(landlords.name, `%${search}%`) : undefined,
  limit: limit ?? 50,
  offset: offset ?? 0,
});

For form dropdowns that genuinely need to search across all records, a search string parameter is now accepted. Filtering happens in the database with a LIKE / ILIKE clause — not by loading everything and filtering in JavaScript.

SQL COUNT(*) in the stats procedure

The four in-memory counts are replaced with direct SQL aggregate queries:

// Before — loads every ID row into memory
const landlordRows = await db.select({ id: landlords.id }).from(landlords);
const landlordCount = landlordRows.length;

// After — single integer over the wire
const [{ count: landlordCount }] = await db
  .select({ count: sql<number>`count(*)::int` })
  .from(landlords);

The database computes the aggregate; the application receives one integer per table.

---

Using the updated procedures

Basic paginated list

// Fetch the first page of landlords (default: 50)
const { data } = trpc.properties.listLandlords.useQuery({});

// Fetch page 2
const { data } = trpc.properties.listLandlords.useQuery({ limit: 50, offset: 50 });

Server-side search for dropdowns

// Search landlords by name — filtering happens in the database
const { data } = trpc.properties.listLandlords.useQuery({ search: 'Smith' });

paginationInput schema reference

The shared schema used by these (and other) procedures lives in src/lib/pagination.ts:

export const paginationInput = z.object({
  limit:  z.number().int().min(1).max(200).optional(),
  offset: z.number().int().min(0).optional(),
  search: z.string().optional(),
});

---

Impact summary

• Dropdown renders on large agencies: network payload and query time drop proportionally with agency size.
• Stats panel: reduced from four full column scans to four single-row aggregate queries.
• No breaking changes for existing callers — the limit defaults to 50 if not supplied.

---

Migration checklist

If your code calls listLandlords or listTenants and expects all records in one response:

1. Add pagination controls to your UI (next/previous page, or infinite scroll).
2. Or use the search parameter to narrow results server-side before displaying them.
3. Review any downstream logic that assumed the full list was present in the response — this now requires iterating pages.