How do you handle the search cost for financial dashboards with large datasets?

Need help Database
1

I have a financial system already running in Bubble, and I’m running into an issue with the search cost required to populate my dashboard.

Some of the metrics, such as total outstanding, received today, received yesterday, monthly costs, accounts receivable, profitability, new contracts, and new clients, require sums and counts over tables that can contain thousands of records per user.

As far as I know, Bubble doesn’t allow running database-level aggregations like SELECT SUM() on only the required fields. Instead, it seems necessary to search through all matching records and then calculate the totals, which becomes quite expensive as the dataset grows.

I’ve considered denormalizing the data by creating additional tables with pre-calculated values, but my application has many different metrics, date ranges, and variables. That would add a lot of complexity and also increase the risk of data becoming inconsistent if one of the update workflows fails.

I’m curious how others handle this kind of scenario. How are you calculating dashboard metrics efficiently in Bubble when working with large amounts of financial data, without relying on multiple denormalized tables?

2

Actually it does…

An expression like:

search for sales: each item's value: sum

only retrieves the single aggregated number from the DB query.

3

How many entries per user? Are the entries tied to a time component so can be grouped by a date? If time is factor is there a time cut off for how you search or present the data through date filters?