All Docs
FeaturesDepositClearUpdated March 12, 2026

Under the Hood: Eliminating Sequential Database Waterfalls in the Work Queue

Under the Hood: Eliminating Sequential Database Waterfalls in the Work Queue

Release: v0.1.207
Area: Server Performance
File: src/lib/routers/dashboard.ts

The Problem

As tenancies, negotiations, and message threads accumulate on the platform, we identified a performance anti-pattern in the workQueue procedure responsible for surfacing unread negotiation message counts to landlords, agents, and tenants on their dashboards.

The original implementation resolved the count through three sequential database queries wrapped in a nested async IIFE:

1. SELECT thread IDs   → fetch all open thread IDs into memory
        ↓
2. SELECT messages     → fetch all messages for those thread IDs into memory
        ↓
3. SELECT receipts     → fetch all read receipts for those messages into memory
        ↓
   count in JavaScript

Each query depended on the result of the previous one, meaning no parallelism was possible and all three arrays had to be held in Node.js heap memory simultaneously. As a platform grows — more properties, more tenancies, more message threads — this pattern causes both increased latency (network round-trips stack up) and increased memory pressure (arrays balloon in size).

A second issue existed in the stats section of the same router: counts were being derived by selecting full ID columns and calling .length in JavaScript, rather than asking the database to count.

// Before — wasteful: fetches every ID just to count them
const result = await db.select({ id }).from(properties);
const count = result.length;

The Fix

Unread Message Count: Single-Query JOIN Pattern

The three-query waterfall has been replaced with a single SQL query using a LEFT JOIN … IS NULL (or equivalent NOT EXISTS) pattern. The database now does all the work in one round-trip, returning only the final integer count — no intermediate arrays are ever materialised in application memory.

Before: 3 sequential round-trips, O(messages + receipts) memory
After:  1 round-trip, O(1) memory

Stats Counts: SQL COUNT() Pushdown

All .select({ id }).length patterns in the stats query have been replaced with a proper SQL aggregate:

// After — efficient: let the database count
const [{ count }] = await db
  .select({ count: sql<number>`count(*)::int` })
  .from(properties);

This means only a single integer is transferred over the wire rather than an entire array of UUIDs.

What Stays the Same

This is a pure performance change. The numbers displayed in the work queue and dashboard stats are identical before and after — only the efficiency of how they are computed has changed. No API contracts, response shapes, or UI behaviours have been modified.

Why This Matters

Fair, fast deposit resolution depends on a responsive platform. As our customer base grows, the efficiency of hot-path queries like unread message counts directly affects how quickly landlords and tenants can act on notifications. Eliminating unnecessary memory allocations and database round-trips keeps the platform performant at scale without requiring infrastructure changes.