Calculate summary info using triggers

[enriquezrene]

Each time a record is inserted in the time_entry table we need to perform an operation to calculate the time_entries summary.

For this, we need to follow the steps detailed above:

1. Create a new time_entries_summary table with these fields:

• id
• day_date
• worked_time_in_day
• starting_week_day_date
• ending_week_day_date
• worked_time_in_week
• starting_month_day_date
• ending_month_day_date
• worked_time_in_month

2. Create a trigger to populate the information in this table

I NEED TO FINISH THIS TICKET DESCRIPTION ----

[ralden]

It's not clear what "id" is. Is it a guid for this unique row in the time_entries_summary table? Is it an FK to a user? I think the rows in this table are only useful if they can be linked to a single user, correct? And the purpose of this table is basically to cache the summary for a user, correct? So after there are many records in this table for a user we are only interested in the last one inserted, correct? How do we know which is the last one? Does day_date really have milliseconds of resolution in spite of the name?

Isn't it true that the only row in this table that is useful is the latest row for a given user? Perhaps one option is to always delete all the rows in this table (for a user) before inserting a new one? That way cleanup is built into the algorithm.

Note if all of the above is true this is really just a cache. Is this the best place to store it? In Cosmos? I guess if it is calculated by a Cosmos trigger....maybe yes. I would think just storing in the memory of the Docker container would be fine too. What do we do if we want to know this information but the cache is empty? Is the only way to populate the cache to insert a record into the time_entry table?

[enriquezrene]

This issue is no longer needed since we already address this problem in a different way.