Changes between Version 3 and Version 4 of DatabaseProgramming

Timestamp:

05/26/26 11:40:52 (5 hours ago)

Author:

231550

Comment:

--

DatabaseProgramming

@@ -332 +332 @@
 * This procedure manages the creation of a new meal record by validating user inputs against business rules before inserting data into the logs. It explicitly blocks invalid meal types, zero or negative quantities, and non-existent user or food identifiers. Used as a safe write-API for application data entry. It protects database integrity by throwing explicit procedural exceptions to reject malformed requests before they hit storage.
 
-3. !trg_update_food_calories (Trigger & Function)
-
-* This trigger intercepts inserts or updates on the food nutrient mapping table and automatically extracts energy data. If the nutrient matches the specific !kilocalorie identifier, it modifies the main food item's summary column. Used to enforce automated data !denormalization. It ensures that the frequently read calories_per_100g summary column on the main table remains perfectly synchronized with raw, deeply nested nutrient records without manual !backend updates.
+3. trg_update_food_calories (Trigger & Function)
+
+* This trigger intercepts inserts or updates on the food nutrient mapping table and automatically extracts energy data. If the nutrient matches the specific kilocalorie identifier, it modifies the main food item's summary column. Used to enforce automated data denormalization. It ensures that the frequently read calories_per_100g summary column on the main table remains perfectly synchronized with raw, deeply nested nutrient records without manual backend updates.
+
+4. get_user_daily_calories_burned (Function)
+
+* This function aggregates the sum of all tracked physical output metrics for a targeted individual across a single calendar day. It extracts raw data directly from activity history tables to return a single numeric metric. Used directly alongside the food tracking functions to calculate the net daily energy balance for a user profile. It encapsulates simple temporal filters behind a clean, readable retrieval interface.
+
+5. log_activity_entry (Procedure)
+
+* This routine validates a workout submission and dynamically computes total energy expenditures by multiplying duration against a specific exercise's calories burned per minute. It handles foreign key checks procedurally before inserting the finalized metrics into activity history. Used to centralize business calculations during a write transaction. It removes the mathematical burden from the frontend application layer, ensuring consistent caloric burn logic regardless of where the entry is submitted.
+
+6. trg_update_activity_streak (Trigger & Function)
+
+* This trigger listens for incoming workout records and processes conditional state logic to maintain user behavioral metrics. It handles three distinct states: initializing a new track, skipping identical-day duplicate increments, or breaking and resetting stagnant counts. Used to power the application's gamification features completely on the data layer. It provides instant, reactive state changes to profile records the exact millisecond a user logs an active workout.
+
+7. check_calorie_target (Function)
+
+* This function evaluates current daily consumption metrics against the parameters of a user's actively assigned dietary phase using an integrated common table expression (CTE). It outputs a virtual table showing targets, current metrics, remaining discrepancies, and success states. It compares how many calories a user ate today against their diet goals for that specific date. It combines their goal, their actual intake, the mathematical difference, and a true/false success flag into one organized row that the app can instantly use to draw progress charts.
+
+8. assign_diet_to_user (Procedure)
+
+* This administrative routine manages user configuration changes while actively preventing chronological schedule conflicts. It uses PostgreSQL's native OVERLAPS operator to strictly block the registration of overlapping diet windows. Used to securely handle user diet changes. It establishes strict date-range validations directly at the transactional layer to prevent logical data corruption that simple interface validation might miss.
+
+9. add_body_measurement (Procedure)
+
+* This conditional routine records user physical tracking metrics like weight and body fat percentages. It detects if an entry already exists for the current calendar date and seamlessly shifts its operational behavior from an insert to an update. Implements standard Update/Insert data logic for tracking historical progress. It ensures a clean timeline of metrics by restricting users to a single, easily updated entry per day.
+
+10. get_user_nutrition_summary (Function)
+
+* This function processes a daily log timeline and dynamically separates text-heavy nutrient criteria using filtered aggregate patterns. It filters and computes independent tracking tallies for foundational macronutrients (protein, carbs, fats, and fiber) simultaneously. Serves as the primary data source for rendering nutritional charts and breakdown widgets.