Engineering: Eliminating N+1 Queries in Bulk Demand Generation (PERF-09)

Version: 1.0.64
Area: Service Charge Billing · Server Performance

---

Background

The service charge billing pipeline is one of the most database-intensive workflows in the platform. When a managing agent generates demands for a multi-unit development, the system must create a demand record for every unit (or every unit × instalment combination), assign each demand a sequential reference number, and then — separately — bulk-issue those demands to owners.

Prior to v1.0.64, all three of the mutations that power this workflow contained N+1 query patterns: they iterated over rows and issued individual INSERT or UPDATE statements per row inside a loop. For a typical 100-unit development with quarterly billing, a single end-to-end billing run could generate over 400 individual database roundtrips.

---

The Problem in Detail

generateDemands — O(2n) roundtrips

The mutation called nextDemandRef() — a COUNT(*) query — on every iteration of the unit loop in order to assign an incrementing reference number to each demand. It then issued a separate db.insert() for each unit.

// Before — one COUNT + one INSERT per unit
for (const unit of unitRows) {
  const ref = await nextDemandRef(orgId); // SELECT COUNT(*) ...
  await db.insert(serviceChargeDemands).values({ ref, ...unit });
}

For 100 units: 200 database roundtrips.

generateInstalmentDemands — O(n×m) roundtrips

This variant supports instalment-based billing (e.g. quarterly). It contained a nested loop — one level for units, one for instalments — each iteration issuing its own db.insert().

For 100 units × 4 instalments: 400 database roundtrips.

issueDemandsBulk — O(2n) roundtrips

When issuing drafted demands, the mutation looped over draftDemands and called two db.update() statements per demand (one for status, one for issued metadata).

For 100 demands: 200 database roundtrips.

---

The Fix

1. Pre-compute the full sequence range before the loop

Instead of calling nextDemandRef() inside the loop, a single query now fetches the starting sequence number for the current year's demand run. All subsequent reference values are computed in-process with a simple increment.

// After — one query, all refs computed locally
const { start_seq } = await db
  .select({ start_seq: sql`COUNT(*) + 1` })
  .from(serviceChargeDemands)
  .where(
    and(
      eq(serviceChargeDemands.orgId, orgId),
      sql`EXTRACT(YEAR FROM created_at) = ${year}`
    )
  )
  .then(rows => rows[0]);

const allDemands = unitRows.map((unit, i) => ({
  ref: formatDemandRef(orgId, year, start_seq + i),
  ...unit,
}));

2. Single bulk insert

All demand value objects are accumulated into an array and committed in one statement, regardless of whether the billing mode is standard or instalment-based.

// After — one INSERT for all demands
await db.insert(serviceChargeDemands).values(allDemands);

This applies to both generateDemands (flat array) and generateInstalmentDemands (array accumulated across the nested loop).

3. Bulk update for issueDemandsBulk

The per-row update loop is replaced by a single UPDATE ... WHERE id IN (...) using Drizzle's inArray helper.

// After — one UPDATE for all issued demands
const allIds = draftDemands.map(d => d.id);
await db
  .update(serviceChargeDemands)
  .set({ status: 'issued', issuedAt: now, issuedBy: userId })
  .where(inArray(serviceChargeDemands.id, allIds));

---

Results

Mutation	Roundtrips Before	Roundtrips After	Reduction
generateDemands (100 units)	~200	2	99%
generateInstalmentDemands (100 units × 4)	~400	2	99.5%
issueDemandsBulk (100 demands)	~200	1	99.5%

The full billing lifecycle for a 100-unit development with quarterly billing now completes in ~5 database roundtrips compared to ~800 previously.

---

Compatibility

• No changes to the public API surface or tRPC procedure signatures.
• No schema migrations required.
• Demand reference numbering is preserved exactly — the sequence logic produces identical values, just computed locally rather than via repeated COUNT queries.
• All changes are contained within src/lib/routers/serviceCharge.ts.