⚡🐴 Prepare database for budget reconciliation and analysis

[jrosseel]

Once a budget is marked as approved, the system moves from budget modelling to expense management.

Technical needs

To allow for this budget management to happen effectively, we need to "expand the budgets."

With "expand", we mean:

1. Fetch the transaction plans

2. Calculate the budget lines using the "___PlannedTransactionsToBudgetLines" function from the applications finance model.

3. For each of the budget lines, store the calculated values (monthly, quarterly, yearly budgets per line) in the database so we can easily calculate the allocable amount left for each of these periods.

Technical requirements

The main technical requirement for this area of the application is around data management.
As we'll be requiring a lot of analytics and reasoning over allocation data, the data needs to be stored and managed on a platform which can easily handle large amounts of complex queries and reasoning.

Some examples of reasoning we will need to do constantly:

• Line-to-line access management of the budgets
• Various deep analysis's and reports on budget usage, forecasting, cashflow management, ...

Firestore is great for rapid and realtime storage and retrieval of data, but suboptimal for the purposes of reporting and fast querying. We therefore need to integrate a second database into the application, used by the dashboard and operation application domains. SQL/Acid databases are generally a better fit for these types of needs.

The requirements to complete this assignment are therefore:

• 🗒️ Develop normalised database design for the budget expansions including tables such as budget lines, monthly budget amounts, quarterly budget amounts, yearly budget amounts, ...
• 🧮 Develop table for budget allocations (invoice ID (invoice itself stored in firestore), invoice category id, invoice type id, amount, year, month, date)
• 🔧 Analyse cloud database options and create the database (dev, staging, prod) on most appropriate option (for the long term)
• 🔧 Link the database to the backend (cloud functions architecture) (integration with @NGFI tools?)
• 🤖 Create scripts to connect developer PCs to database
• 🧮 Cloud function to "promote"/activate budgets #18
• 🏘️⚡ Add reactive subscriptions to Cloud SQL #22