Faster Quarterly Dashboard Loads with New Database Indexes

Release: v1.0.46 | Category: Performance

Overview

Starting with v1.0.46, the quarterly dashboard loads faster thanks to two new composite indexes on the quarterly_summaries database table. This change eliminates costly full-table sequential scans that occurred on every dashboard page load.

What Was the Problem?

Every time a landlord opens their quarterly dashboard, the platform queries the quarterly_summaries table through two endpoints:

• listBusiness — retrieves business-level quarterly summaries, filtering by orgId + taxYear + hmrcBusinessId
• list — retrieves per-property quarterly summaries, filtering by orgId + taxYear + propertyId

Without dedicated indexes covering these filter columns, PostgreSQL had no choice but to scan the entire quarterly_summaries table for each query — reading every row, regardless of how many actually matched the filter. As organisations accumulate more quarterly records over time, this overhead grows proportionally.

While the table already had unique constraints, those constraints act only as uniqueness guards. They are not effective for the multi-column equality lookups that the dashboard endpoints perform.

What Changed?

Two new composite indexes were added to src/db/schema.ts:

// Business-level dashboard queries
index('qs_org_business_year_idx').on(
  quarterlySummaries.orgId,
  quarterlySummaries.hmrcBusinessId,
  quarterlySummaries.taxYear
)

// Per-property dashboard queries
index('qs_org_property_year_idx').on(
  quarterlySummaries.orgId,
  quarterlySummaries.propertyId,
  quarterlySummaries.taxYear
)

Both indexes are designed to exactly match the filter patterns used by the dashboard endpoints, allowing the database engine to seek directly to matching rows rather than scanning the full table.

Performance Impact

Scenario	Before	After
Quarterly dashboard load	Sequential full-table scan	Composite index seek
Estimated latency saving	—	10–40 ms per request
Scales with data volume?	Yes (gets worse over time)	No (index seek is O(log n))

Who Benefits?

All landlords using the quarterly dashboard will see the improvement automatically after the migration is applied. The benefit is most noticeable for organisations with a larger history of quarterly summary records.

Applying the Migration

This is a non-breaking, additive schema change. To apply it:

1. Deploy the v1.0.46 release.
2. Run your standard database migration command to create the new indexes.
3. No application configuration or code changes are needed.

Note: Index creation on large tables can briefly increase database load. It is recommended to apply migrations during a low-traffic window if your quarterly_summaries table contains a very large number of rows.