Performance: Eliminating N+1 Queries in Bulk Demand Generation

Released in v1.0.66 · Ticket PERF-09

This page explains the database performance improvements made to the three core service charge billing mutations in BlockManOS: generateDemands, generateInstalmentDemands, and issueDemandsBulk.

Problem: N+1 Query Patterns at Scale

Before v1.0.66, all three billing mutations issued individual database queries inside loops, causing severe latency at the scale of real Irish block developments.

generateDemands

For each unit in a development, the mutation called nextDemandRef() — a COUNT(*) query — followed by an individual INSERT. With 100 units, that was 200 DB roundtrips just to generate annual demands.

generateInstalmentDemands

The instalment variant nested this pattern: for every combination of unit × instalment schedule, the same COUNT + INSERT pair ran. A 100-unit development on quarterly billing (4 instalments) produced 800 DB roundtrips.

issueDemandsBulk

Issuing draft demands involved two update loops: one UPDATE per demand for the status transition, and a second UPDATE per emailed demand to record the email recipient. For 200 draft demands, this meant 400 DB roundtrips.

Summary of Before/After

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

For a 100-unit development on quarterly billing: 400+ roundtrips → ~6 per mutation.

---

Solution

Demand Reference Sequencing

The original nextDemandRef() function was an async helper that issued a COUNT(*) against the serviceChargeDemands table to determine the next available sequence number for a given org and year. Because it was called inside the per-unit / per-instalment loop, it ran once per row.

In v1.0.66, this was replaced with two functions:

• demandRefStartSeq(orgId, year) — issues the COUNT(*) once, before any loop, and returns the first available sequence number as an integer.
• formatDemandRef(year, seq) — a pure in-memory function that formats a sequence number into the SC-YYYY-NNNN reference string.

Callers can now assign all demand refs locally by incrementing from startSeq:

const startSeq = await demandRefStartSeq(ctx.orgId, year);
// Inside loop, idx is the zero-based offset:
const demandRef = formatDemandRef(year, startSeq + idx);

This is collision-safe: the start sequence is fetched before any inserts in the same transaction window, and PostgreSQL MVCC ensures the COUNT reflects the committed state.

Bulk INSERT for generateDemands and generateInstalmentDemands

Instead of inserting each demand row individually inside the loop, all demand objects are now constructed in-memory first, then written to the database in a single call:

// Build all rows in-memory
const demandValues = unitRows.map((unit, idx) => ({
  // ...demand fields
  demandRef: formatDemandRef(year, startSeq + idx),
}));

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

The same pattern applies to generateInstalmentDemands, which iterates over units × instalments — a seqOffset counter tracks the running index across both loops before the single bulk insert.

Bulk UPDATE for issueDemandsBulk

Issuing demands previously looped over each draft demand and issued an individual UPDATE. It has been refactored to:

1. Status transition — a single db.update().where(inArray(demands.id, allIds)) covering all demand IDs at once.
2. Email recipient tracking — after email sending (which remains per-demand, as it is inherently external I/O), the IDs of successfully emailed demands are collected and a single bulk UPDATE with a CASE WHEN expression records the emailRecipient field.

This eliminates two full O(k) update loops while preserving exact audit trail behaviour.

---

What Did Not Change

• Email sending remains per-demand. Sending an email to an owner is an external I/O operation and cannot be batched at the database level. Only the DB write that records the email outcome is batched.
• Return shapes from all three mutations are unchanged — callers receive the same demandsCreated, demandIds, and related fields as before.
• Audit log entries continue to be written with the same action, resourceType, resourceId, and metadata structure.
• Demand ref format (SC-YYYY-NNNN) is unchanged.

---

Health Endpoint Middleware Fix

Also shipped in v1.0.66: /api/health now bypasses all middleware via the Next.js matcher configuration. Previously, infrastructure health probes could receive non-200 responses depending on the authentication state of the request. The fix is a framework-level exclusion that ensures the health endpoint always responds with 200 regardless of auth context.

This affects infrastructure and deployment tooling — no changes are required to existing health probe configuration.