How We Eliminated 400+ DB Roundtrips from Bulk Service Charge Demand Generation

Released in v1.0.65 · PERF-09

Background

In Irish block management, generating service charge demands is one of the most database-intensive operations in the platform. When a managing agent raises annual or quarterly instalment demands for a development, the system must create one demand record per unit (annual) or one per unit per instalment (quarterly). For a 100-unit block on a quarterly schedule, that's 400 individual demand records created in a single mutation.

Prior to v1.0.65, three core mutations — generateDemands, generateInstalmentDemands, and issueDemandsBulk — all contained N+1 (and N×M) database query patterns. Each loop iteration issued its own COUNT and INSERT against Postgres, causing severe latency at scale that grew linearly with block size.

The Problem in Detail

Demand reference generation

Every demand requires a unique sequential reference in the format SC-{YEAR}-{SEQ} (e.g. SC-2025-0042). The original implementation used a helper called nextDemandRef() that issued a COUNT(*) query against the demands table to determine the next available sequence number — and this was called inside the loop, once per unit or per unit-instalment pair:

for each unit:
  seq = COUNT existing demands   ← DB roundtrip
  INSERT new demand              ← DB roundtrip

For n units, that's 2n roundtrips just for this one mutation.

Instalment demands

generateInstalmentDemands compounded this further. With n units and m instalments, the loop was O(2×n×m): for a 100-unit block with 4 quarterly instalments, this meant 800 DB roundtrips per invocation.

Bulk issuance

issueDemandsBulk suffered a similar pattern: after transitioning demands from draft to issued, it issued one UPDATE per demand for the status transition and a second UPDATE per emailed demand to record the email recipient — O(2k) updates for k demands.

The Fix

Pre-computing the sequence range

The core insight is that sequence numbers don't need to be resolved one at a time. If we know how many demands already exist at the start of the operation, we can assign all new refs in-memory:

// One COUNT query — called once before the loop
const startSeq = await demandRefStartSeq(ctx.orgId, year);

// Assign refs sequentially in-memory — no DB calls
const demandValues = unitRows.map((unit, idx) => ({
  demandRef: formatDemandRef(year, startSeq + idx),
  // ... other fields
}));

// One bulk INSERT for all units
const inserted = await db
  .insert(serviceChargeDemands)
  .values(demandValues)
  .returning({ id: serviceChargeDemands.id });

The new demandRefStartSeq() function issues a single COUNT(*) and returns the first available sequence number. The new formatDemandRef(year, seq) function formats a reference string purely in-memory. Together they replace the per-iteration nextDemandRef() call.

Instalment demands

The same approach extends to the N×M case. A seqOffset counter increments as the outer (units) and inner (instalments) loops run, ensuring each demand in the batch gets a unique, sequential ref:

const startSeq = await demandRefStartSeq(ctx.orgId, year);
let seqOffset = 0;

for (const unit of unitRows) {
  for (const inst of input.instalments) {
    demandValues.push({
      demandRef: formatDemandRef(year, startSeq + seqOffset),
      // ...
    });
    seqOffset++;
  }
}

// One bulk INSERT for all n×m rows
await db.insert(serviceChargeDemands).values(demandValues);

Bulk issuance

For issueDemandsBulk, the per-demand UPDATE loops are replaced with inArray-based bulk updates:

• Status transition: one UPDATE ... WHERE id IN (...) for all draft demand IDs.
• Email tracking: after email sends (which remain per-demand — unavoidable external I/O), IDs of successfully emailed demands are collected and applied in a single bulk UPDATE using a CASE WHEN expression.

Results

Mutation	Before	After
generateDemands (n units)	O(2n) roundtrips	O(2) — constant
generateInstalmentDemands (n units × m instalments)	O(2×n×m) roundtrips	O(2) — constant
issueDemandsBulk (k draft demands)	O(2k) roundtrips	O(2) — constant

For a 100-unit block on quarterly billing: 400+ DB roundtrips → ~6 per mutation. For a 200-unit block: 401 roundtrips (budget fetch + units query + 200 COUNTs + 200 INSERTs) → 3 (budget fetch + units query + 1 bulk INSERT).

Correctness & Safety

• Collision safety: demandRefStartSeq() is called before any inserts in the same transaction window. Postgres MVCC ensures the COUNT reflects only committed state, so concurrent operations cannot produce duplicate refs.
• Email I/O is unchanged: demand emails are still sent individually (external I/O is inherently per-demand); only the DB write to record the email recipient is batched.
• Return shapes preserved: all mutations return the same response structures (demandsCreated, demandIds, etc.) and write identical audit log entries as before.
• No schema changes: this is a query-layer optimisation only — no database migrations are required.

Also in This Release

Health check route exclusion (src/middleware.ts): The /api/health exclusion was moved from the middleware allowlist into the route matcher pattern directly. This ensures uptime monitoring probes receive clean 200 responses without any middleware logic (authentication, session handling, etc.) being applied to health check requests.