Under the Hood: Eliminating Query Amplification in Service Charge Billing

Release: v1.0.67 · Control: PERF-17 · Category: Performance

Service charge billing is one of the highest-volume operations on the platform. For a typical 200-unit development running quarterly billing, a single billing cycle touches hundreds of database rows across demand generation and issuance. This post explains what was wrong, what we changed, and what it means for performance.

---

The Problem: O(n) Loops Around Database Calls

The original implementation of three mutations — generateDemands, generateInstalmentDemands, and issueDemandsBulk — all shared the same structural problem: they called the database once per row, inside a loop.

This is a classic N+1 (or N×M) query pattern. Each iteration waits for a round-trip to the database before the next one starts, meaning the total latency scales linearly with the number of units or instalments. Under the default PostgreSQL connection pool, these sequential round-trips also hold a connection open for the full duration of the loop.

Concrete Example: issueDemandsBulk

Before this release, issuing demands for a 200-unit development looked like this at the database layer:

UPDATE service_charge_demands SET status='issued', issued_at=? WHERE id=1
UPDATE service_charge_demands SET status='issued', issued_at=? WHERE id=2
...
UPDATE service_charge_demands SET status='issued', issued_at=? WHERE id=200
-- then conditionally, per row with email tracking:
UPDATE service_charge_demands SET email_sent_at=? WHERE id=1
UPDATE service_charge_demands SET email_sent_at=? WHERE id=2
...

That's up to 400 sequential UPDATE statements for one user action.

---

The Fix: Bulk Operations and In-Memory Accumulation

1. generateDemands — Pre-calculate, then bulk insert

Demand reference numbers previously required a COUNT query per row to determine the next sequence value. The refactored version fetches any required sequence data once, computes all reference numbers in JavaScript, accumulates the full set of value objects in an array, and then issues a single INSERT:

await db.insert(serviceChargeDemands).values(allValues);

Result: ~400 database round-trips reduced to 1–2.

2. generateInstalmentDemands — Flatten the nested loop

The original code had a nested loop — outer loop over units, inner loop over instalments — with an INSERT at the innermost level. For 200 units with 4 quarterly instalments this meant 800 individual inserts.

The refactored version flattens both loops into a single array construction pass, then calls one bulk insert:

const allInstalmentValues = units.flatMap(unit =>
  instalments.map(instalment => buildInstalmentRow(unit, instalment))
);
await db.insert(serviceChargeDemands).values(allInstalmentValues);

Result: ~800 database round-trips reduced to 1.

3. issueDemandsBulk — Replace row-by-row updates with a predicate UPDATE

Instead of updating each demand individually, the refactored mutation issues a single UPDATE statement that targets all matching draft demands by predicate:

await db
  .update(serviceChargeDemands)
  .set({ status: 'issued', issuedAt: now })
  .where(
    and(
      eq(serviceChargeDemands.orgId, orgId),
      eq(serviceChargeDemands.budgetId, budgetId),
      eq(serviceChargeDemands.status, 'draft')
    )
  );

Where multiple statements are still required (e.g. atomically grouped audit or email-tracking writes), db.batch() is used to send them together in a single network round-trip.

Result: up to 400 sequential statements reduced to 1–2.

---

Performance Impact

Operation	Statements Before (200 units, quarterly)	Statements After
generateDemands	~400	1–2
generateInstalmentDemands	~800	1
issueDemandsBulk	~400	1–2
Total	~1,600	~5

For larger developments (300–500 units, which are common in new-build schemes), the before/after gap widens proportionally. The practical effect is that billing operations which previously timed out or caused noticeable UI latency on larger OMCs now complete in a fraction of the time.

---

Files Changed

• src/lib/routers/serviceCharge.ts

---

This improvement is part of an ongoing performance audit of high-volume data paths. Further PERF-series controls will address query patterns in other billing and compliance modules.