I'm working on an application where it has financial control for the incomes and expenses of the company. These informations can be controled by accounts (bank accounts - can be 1 or more), categories and subcategories.

The main objective is to display the current month financial information (or the same information on a specified period ex 15/02/2022 -> 15/04/2022). It would be something like the image below.

What I need to do now is to display, at the beggining of the period, the current balance of the account (or all accounts summed) and at the end the balance after sum/subtract the incomes/expenses of that month, see image below.

The problem I'm finding most difficult is to keep track of those balances whe the database has many entries, specially really old ones.

First approach: I was thinking about load all the information ever inserted on the database and start to sum/subtract until I get to the current period. Then I can create a more detailed list (for example, with balance for each day) without compromise performance. It doesn`t feel right, imagine I have financial data from 4-5 years ago. I would need to load thousands of rows from the database and do many math operations just to get the data from the current period.

Secont approach: After each time the user insert/edit/remove entries from the database I would update a table on the database where it would control the balance for each account. This seems to kind of solve the issue. But if I need to filter by categories/subcategories/accounts I'm not going to have the correct information. Also, this seems a bit complex to keep track of the correct balance with so many transactions. If for some reason one of them fails and don`t add to the balance properly, everything else will be messed up.

Third approach: After each month I would "lock" all the information of the previous month, so users cant edit any of the information from previous months. From the financial point of view its not a "problem", since we can't change those informations anyway (unless human error detected later on). And then I can prooced with a static data from a period closes to "Today". The problem with this is that if I need to get information of a period that is not a full month, or has filters, I will have the same issues as before.

Database structure is pretty simple, see image below.

I have one more table that control installments, but its not relevant to this part, since its based on the finance_entries table.

Is there a better/proper approach to this type of data process? I'm really confused and don't know how to prooced without compromising the performance of the application.

I'm working on an application where it has financial control for the incomes and expenses of the company. These informations can be controled by accounts (bank accounts - can be 1 or more), categories and subcategories.

The main objective is to display the current month financial information (or the same information on a specified period ex 15/02/2022 -> 15/04/2022). It would be something like the image below.

What I need to do now is to display, at the beggining of the period, the current balance of the account (or all accounts summed) and at the end the balance after sum/subtract the incomes/expenses of that month, see image below.

The problem I'm finding most difficult is to keep track of those balances whe the database has many entries, specially really old ones.

First approach: I was thinking about load all the information ever inserted on the database and start to sum/subtract until I get to the current period. Then I can create a more detailed list (for example, with balance for each day) without compromise performance. It doesn`t feel right, imagine I have financial data from 4-5 years ago. I would need to load thousands of rows from the database and do many math operations just to get the data from the current period.

Secont approach: After each time the user insert/edit/remove entries from the database I would update a table on the database where it would control the balance for each account. This seems to kind of solve the issue. But if I need to filter by categories/subcategories/accounts I'm not going to have the correct information. Also, this seems a bit complex to keep track of the correct balance with so many transactions. If for some reason one of them fails and don`t add to the balance properly, everything else will be messed up.

Third approach: After each month I would "lock" all the information of the previous month, so users can't edit any of the information from previous months. From the financial point of view it`s not a "problem", since we can't change those informations anyway (unless human error detected later on). And then I can proceed with a static data from a period closes to "Today". The problem with this is that if I need to get information of a period that is not a full month, or has filters, I will have the same issues as before.

Database structure is pretty simple, see image below.

I have one more table that control installments, but it's not relevant to this part, since it's based on the finance_entries table.

Is there a better/proper approach to this type of data process? I'm really confused and don't know how to proceed without compromising the performance of the application.