Skip to main content
All Docs
FeaturesBlockManOSUpdated March 26, 2026

Under the Hood: How We Eliminated Full Table Scans Across Every Multi-Tenant Query

Under the Hood: How We Eliminated Full Table Scans Across Every Multi-Tenant Query

Release: v1.0.59 · PERF-10

The Problem

BlockManOS is a multi-tenant platform. Every database query — whether listing maintenance requests, generating an arrears report, or checking compliance obligations — includes a WHERE org_id = $1 clause to isolate data per organisation. This is correct and necessary.

However, PostgreSQL only creates indexes automatically for primary keys and columns declared with UNIQUE constraints. Until v1.0.59, every other column — including org_id — had no index at all. That meant:

  • A query for "all service charge demands for this organisation" would scan every row in service_charge_demands, regardless of how many organisations share the table.
  • As data grows, this scales linearly with total row count rather than per-tenant row count.
  • Filtered views — open maintenance requests, overdue demands, upcoming compliance due dates — compounded the cost by adding a second unindexed column predicate.

This was the single largest latency bottleneck in the schema.

The Solution

A new standalone file, src/db/schema-indexes.ts, defines 40+ Drizzle index() entries covering all 30+ high-traffic tables. It is registered in drizzle.config.ts alongside the existing schema files, so verifyAndSyncSchema applies the indexes as a non-destructive DDL migration — no application code changes required and no data is altered.

Index Strategy

Three tiers of indexes were applied based on query patterns:

Tier 1 — Tenant isolation (every table)

Every table gets a single-column index on orgId. This is the minimum required to avoid a full table scan on the most basic tenant-scoped query.

developments  → (orgId)
owners        → (orgId)
contractors   → (orgId)
omcDetails    → (orgId)
...and all others

Tier 2 — Composite filter indexes (high-traffic tables)

The most common query pattern in every router is "filter by org, then by development or status". Composite indexes satisfy both predicates in a single B-tree lookup:

developments             → (orgId, status)
buildings                → (orgId, developmentId)
units                    → (orgId, developmentId), (orgId, status)
omcDirectors             → (orgId, developmentId), (orgId, status)
omcMeetings              → (orgId, developmentId), (orgId, status)
serviceChargeDemands     → (orgId, status), (orgId, budgetId),
                           (orgId, dueDate), (orgId, developmentId)
complianceObligations    → (orgId, developmentId), (orgId, status),
                           (orgId, nextDueDate)
maintenanceRequests      → (orgId, developmentId), (orgId, status),
                           (orgId, createdAt)
contractors              → (orgId, isActive), (orgId, publicLiabilityExpiry)

Tier 3 — Foreign key lookup indexes

Join columns that appear in WHERE clauses or are used as lookup keys after a parent fetch:

unitOwnerships       → (unitId), (ownerId)
serviceChargePayments → (demandId), (ownerId)
arrearsActions       → (demandId)
bankStatementLines   → (accountId), (accountId, reconciliationStatus)
complianceEvents     → (obligationId)
maintenanceNotes     → (requestId)

Key Tables and Their Indexes

TableIndexes AddedPrimary Use Case
developments(orgId), (orgId, status)Tenant filter, active/archived views
buildings(orgId, developmentId)Building list per development
units(orgId, developmentId), (orgId, status)Unit list, vacancy views
owners(orgId), (orgId, createdAt)Owner list, recent additions
unitOwnerships(unitId), (ownerId)Current owner lookup, owner's units
omcDirectors(orgId, developmentId), (orgId, status)Board listing, active directors
omcMeetings(orgId, developmentId), (orgId, status)AGM list, status filter
serviceChargeDemands6 indexesArrears reports, overdue reminders, billing engine
serviceChargePayments(demandId), (orgId), (ownerId)Payment reconciliation
arrearsActions(demandId), (orgId)Debt recovery workflow
complianceObligations(orgId, developmentId), (orgId, status), (orgId, nextDueDate)Compliance dashboard, alert engine
contractors(orgId), (orgId, isActive), (orgId, publicLiabilityExpiry)Contractor list, insurance expiry alerts
maintenanceRequests(orgId, developmentId), (orgId, status), (contractorId), (orgId, createdAt)Request list, status views, contractor workload
bankStatementLines(accountId), (accountId, reconciliationStatus)Reconciliation matching engine
documents(orgId, developmentId), (orgId, category), (developmentId, accessLevel)Document library, owner portal filtering
PPM tables(orgId, developmentId), (orgId, status), (orgId, dueDate)PPM scheduling engine
GDPR tables(orgId, status), (orgId, deadlineAt), (dsrId)DSR 30-day deadline tracking

Infrastructure Changes

Health Endpoint (/api/health)

The health check endpoint now returns explicit headers:

Content-Type: application/json
Cache-Control: no-store

This prevents load balancers or CDN layers from caching a stale 200 ok response and ensures the Content-Type is never inferred incorrectly by intermediate proxies.

Middleware Bypass for Infrastructure Routes

The following paths now bypass shell auth middleware entirely:

  • /api/health
  • /api/inngest
  • /api/webhooks
  • /api/trpc
  • /api/auth

This ensures uptime monitoring, background job handlers, and webhook receivers are never inadvertently blocked by session validation logic.

Migration Notes

  • No application code changes required. Indexes are transparent to query execution — no queries need updating.
  • Non-destructive. Drizzle's verifyAndSyncSchema applies indexes using CREATE INDEX IF NOT EXISTS, leaving existing data untouched.
  • Naming convention. All index names follow {table_prefix}_{columns}_idx in snake_case (e.g. sc_demands_org_status_idx, maint_requests_org_dev_idx).
  • The new file is at src/db/schema-indexes.ts and is registered in drizzle.config.ts under the schema array.