jdenoc / money-tracker

income/expense tracker with receipt retention
MIT License
5 stars 0 forks source link

Improve stats page with timeseries #572

Open jdenoc opened 3 months ago

jdenoc commented 3 months ago

We should create a new database table that will store a consolidated grouping of entry values.

Proposed MySQL table schema:

CREATE TABLE timeseries (
  `entry_date` date not null,
  `x` JSON not null,
  `last_updated` timestamp default CURRENT_TIMESTAMP
    on update CURRENT_TIMESTAMP,
  primary key (`entry_date`)
);

Proposed JSON schema (for database table):

[
  {
    "id": 1,
    "value": 1000,
   "account_type_id": 1,
    "tags": [1, 2, 3]
  },
  {
    "id": 2,
    "value": 500,
   "account_type_id": 1,
    "tags": []
  }
]

When a new entry is created:

  1. gather the entry.id, entry.value, entry.account_type_id, and entry.tags (if any).
  2. check to see if a time series record exist.
  3. If a time series record exists, append the gathered data as JSON to the date record
  4. If a time series record DOES NOT exist, create record with gathered data

There should be some sort of automated and scheduled monitoring process to make sure the time series database remains in sync and should alert if not. The automated monitoring process should not attempt to make changes.

There should also be some sort of API to allow the UI to fetch this data.