Month-end reports in a shared-database SaaS
LedgerCloud runs a multi-tenant SaaS on O11 for 420 accounting firms. All tenants share one database; every entity has a TenantId and a site property resolves the current tenant at login. The Customer entity has 1.4 million rows and nine indexes, six of which start with TenantId. A month-end report runs an aggregate that joins Customer, Invoice, and Payment and filters by TenantId, but the report screen also runs a second aggregate that loads all invoices for the tenant and filters in a list widget by status. Average report time is 38 seconds; the largest tenant accounts for 22% of rows and 60% of report time. Two timers recalculate tenant balances hourly, each iterating all tenants sequentially. The platform has one database server and two front-end servers.
1. Evaluate the index and query strategy and propose changes.
Read the worked answer
The tenant-leading indexes are directionally correct because every query filters by TenantId, but nine indexes on one entity is excessive for a write-heavy table, and the report is not using them well. The second aggregate loads all invoices for the tenant and filters status in the list widget, so the database cannot use an index for status and returns far more rows than needed.
Move the status filter and pagination into the aggregate, then create a composite index on (TenantId, Status, InvoiceDate) and verify the execution plan. Check join order so the plan starts from the tenant-filtered set. If the plan shows key lookups for the report's selected columns, consider a covering index for that report only. Drop unused indexes after measuring; each one adds write cost and maintenance. Do not add a separate index per column, and do not assume the largest tenant's 22% row share explains 60% of report time until the query plans are compared.
2. How should the hourly balance timers be redesigned for scale and fairness?
Read the worked answer
The sequential all-tenant loop means the largest tenant delays every other tenant and the timer can overrun its hourly window. Redesign it as per-tenant work items. A launcher timer enumerates tenants in pages and launches one BPT process per tenant, or per shard if tenant count grows. Cap Max Instantiations to protect the database, and make each process idempotent with a recorded last successful period.
Each tenant then succeeds or fails independently and can be retried without rerunning everyone. Prioritize or isolate the largest tenant so it cannot starve smaller ones. Add a lock to prevent overlapping hourly runs, and track queue depth, per-tenant duration, and failure count. Alert when a tenant exceeds its SLA or when the queue is still growing at the next scheduled run.
The goal is bounded resource use, fairness across tenants, and partial failure isolation, not merely moving the same loop into a background process.
3. What options exist for the largest tenant, and how would you decide?
Read the worked answer
In increasing order of cost: pre-aggregate monthly balances into a summary entity updated incrementally; archive closed periods; add a read replica for reporting; partition Invoice by tenant or period at the database level; move the tenant to a dedicated catalog.
Decide by measurement, not by row share. Compare query plans and per-tenant timings. If the 60% report time comes from loading all invoices or missing indexes, fixing the query is far cheaper than isolating the tenant. A dedicated database is justified only when the tenant's SLA and volume make shared-resource contention unmanageable and the other options have been tried. OutSystems supports separate catalogs per module, but that adds deployment, monitoring, and cross-tenant reporting complexity. Put the decision in an architecture record with the measured evidence so it can be revisited when tenant volume changes.