Index: .idea/copilotDiffState.xml =================================================================== --- .idea/copilotDiffState.xml (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) +++ .idea/copilotDiffState.xml (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -0,0 +1,44 @@ + + + + + + Index: ckend/docs/Normalization.txt =================================================================== --- backend/docs/Normalization.txt (revision a8381b18babc398c094b70c3ca5dc6cb0483c327) +++ (revision ) @@ -1,183 +1,0 @@ -# **Нормализација** - -== **Де-нормализирана база на податоци** - -Се тргнува од една глобална, де-нормализирана релација што ги содржи атрибутите од целиот модел: - -`R = { user_id, email, username, password, training_user_id, training_gender, training_age, training_weight, training_id, training_date, training_type, training_duration, training_calories, investor_user_id, asset_id, asset_ticker_symbol, asset_buy_price, asset_buy_date, asset_quantity, weight_user_id, weight_current, weight_height, weight_goal_weight, weight_goal_calories, daily_intake_id, daily_intake_date, daily_intake_calories, discipline_user_id, custom_tracking_id, custom_tracking_name, task_id, name, is_finished, daily_completion_id, daily_completion_date, procent, finance_user_id, finance_spending_budget, finance_saving_budget, finance_investing_budget, finance_donation_budget, finance_credit, income_id, income_date, income_amount }` - -Оваа релација е де-нормализирана затоа што содржи повторливи групи, како и зависности од атрибути што не се клуч. - -== **Функционални зависности** - -- **FD1:** `user_id -> email, username, password` -- **FD2:** `email -> user_id, username, password` -- **FD3:** `username -> user_id, email, password` -- **FD4:** `training_user_id -> training_gender, training_age, training_weight` -- **FD5:** `training_id -> training_user_id, training_date, training_type, training_duration, training_calories` -- **FD6:** `investor_user_id -> user_id` -- **FD7:** `asset_id -> investor_user_id, asset_ticker_symbol, asset_buy_price, asset_buy_date, asset_quantity` -- **FD8:** `weight_user_id -> weight_current, weight_height, weight_goal_weight, weight_goal_calories` -- **FD9:** `daily_intake_id -> weight_user_id, daily_intake_date, daily_intake_calories` -- **FD10:** `discipline_user_id -> user_id` -- **FD11:** `custom_tracking_id -> user_id, custom_tracking_name` -- **FD12:** `task_id -> discipline_user_id, custom_tracking_id, name, is_finished` -- **FD13:** `daily_completion_id -> user_id, daily_completion_date, procent` -- **FD14:** `(task_id, daily_completion_id) -> /` -- **FD15:** `finance_user_id -> finance_spending_budget, finance_saving_budget, finance_investing_budget, finance_donation_budget, finance_credit` -- **FD16:** `income_id -> finance_user_id, income_date, income_amount` - -**Леви и десен дел:** - -- **Леви:** `user_id, email, username, training_user_id, training_id, investor_user_id, asset_id, weight_user_id, daily_intake_id, discipline_user_id, custom_tracking_id, task_id, daily_completion_id, finance_user_id, income_id` -- **Десен:** `password, training_gender, training_age, training_weight, training_date, training_type, training_duration, training_calories, asset_ticker_symbol, asset_buy_price, asset_buy_date, asset_quantity, weight_current, weight_height, weight_goal_weight, weight_goal_calories, daily_intake_date, daily_intake_calories, custom_tracking_name, name, is_finished, daily_completion_date, procent, finance_spending_budget, finance_saving_budget, finance_investing_budget, finance_donation_budget, finance_credit, income_date, income_amount` - -== **Глобална релација** - -`R = { user_id, email, username, password, training_user_id, training_gender, training_age, training_weight, training_id, training_date, training_type, training_duration, training_calories, investor_user_id, asset_id, asset_ticker_symbol, asset_buy_price, asset_buy_date, asset_quantity, weight_user_id, weight_current, weight_height, weight_goal_weight, weight_goal_calories, daily_intake_id, daily_intake_date, daily_intake_calories, discipline_user_id, custom_tracking_id, custom_tracking_name, task_id, name, is_finished, daily_completion_id, daily_completion_date, procent, finance_user_id, finance_spending_budget, finance_saving_budget, finance_investing_budget, finance_donation_budget, finance_credit, income_id, income_date, income_amount }` - -== **Покривачи** - -- **`user_id+`** = { `user_id`, `email`, `username`, `password` } -> **не ги содржи сите атрибути** -- **`training_user_id+`** = { `training_user_id`, `training_gender`, `training_age`, `training_weight` } -> **не ги содржи сите атрибути** -- **`training_id+`** = { `training_id`, `training_user_id`, `training_date`, `training_type`, `training_duration`, `training_calories` } -> **не ги содржи сите атрибути** -- **`investor_user_id+`** = { `investor_user_id`, `user_id` } -> **не ги содржи сите атрибути** -- **`asset_id+`** = { `asset_id`, `investor_user_id`, `asset_ticker_symbol`, `asset_buy_price`, `asset_buy_date`, `asset_quantity` } -> **не ги содржи сите атрибути** -- **`weight_user_id+`** = { `weight_user_id`, `weight_current`, `weight_height`, `weight_goal_weight`, `weight_goal_calories` } -> **не ги содржи сите атрибути** -- **`daily_intake_id+`** = { `daily_intake_id`, `weight_user_id`, `daily_intake_date`, `daily_intake_calories` } -> **не ги содржи сите атрибути** -- **`discipline_user_id+`** = { `discipline_user_id`, `user_id` } -> **не ги содржи сите атрибути** -- **`custom_tracking_id+`** = { `custom_tracking_id`, `user_id`, `custom_tracking_name` } -> **не ги содржи сите атрибути** -- **`task_id+`** = { `task_id`, `discipline_user_id`, `custom_tracking_id`, `name`, `is_finished` } -> **не ги содржи сите атрибути** -- **`daily_completion_id+`** = { `daily_completion_id`, `user_id`, `daily_completion_date`, `procent` } -> **не ги содржи сите атрибути** -- **`finance_user_id+`** = { `finance_user_id`, `finance_spending_budget`, `finance_saving_budget`, `finance_investing_budget`, `finance_donation_budget`, `finance_credit` } -> **не ги содржи сите атрибути** -- **`income_id+`** = { `income_id`, `finance_user_id`, `income_date`, `income_amount` } -> **не ги содржи сите атрибути** -- **`{task_id, daily_completion_id}+`** = { `task_id`, `daily_completion_id` } -> **не ги содржи сите атрибути** - -== **Спојување покривачи** - -Бидејќи атрибутите што се појавуваат само на левата страна не можат да се изведат на друг начин, тие мора да бидат дел од примарниот клуч. - -Земајќи ги сите леви атрибути заедно, добиваме минимален суперклуч: - -`{ user_id, training_user_id, training_id, investor_user_id, asset_id, weight_user_id, daily_intake_id, discipline_user_id, custom_tracking_id, task_id, daily_completion_id, finance_user_id, income_id }` - -Во нашата нормализирана конструкција, ова е теоретскиот глобален кандидат-клуч на де-нормализираната релација. - -Избран примарен клуч: **`{ user_id, training_user_id, training_id, investor_user_id, asset_id, weight_user_id, daily_intake_id, discipline_user_id, custom_tracking_id, task_id, daily_completion_id, finance_user_id, income_id }`** - -== **Проверка за 1НФ** - -Бидејќи релацијата содржи повторливи групи и неатомски листи во де-нормализираниот поглед, **не ја задоволува 1НФ**. - -== **Декомпозиција по 1НФ** - -**Релација што се анализира** - -`R` - -**Проблем** - -Лист атрибутите и повторливите групи не се атомски. - -**Прва декомпозиција** - -- `TASKS(task_id, discipline_user_id, custom_tracking_id, name, is_finished)` -- `TASK_DAILY_COMPLETION(task_id, daily_completion_id)` - -**Резултат** - -Се добиваат атомски вредности и релации со локални клучеви. - -== **Проверка за 2НФ** - -`R` **не ја задоволува 2НФ** поради парцијални зависности, на пример: - -- `user_id -> email, username, password` -- `training_user_id -> training_gender, training_age, training_weight` -- `asset_id -> asset_ticker_symbol, asset_buy_price, asset_buy_date, asset_quantity` - -== **Декомпозиција по 2НФ** - -Атрибутите се групираат според тоа од кој клуч зависат: - -- **`USERS(user_id, email, username, password)`** -- **`TRAINING_USERS(user_id, gender, age, weight)`** -- **`TRAINING_SESSIONS(training_id, training_user_id, date, type, duration, calories)`** -- **`INVESTOR_USERS(user_id)`** -- **`ASSETS(asset_id, user_id, ticker_symbol, buy_price, buy_date, quantity)`** -- **`WEIGHT_USERS(user_id, weight, height, goal_weight, goal_calories)`** -- **`DAILY_INTAKES(daily_intake_id, user_id, date, calories)`** -- **`DISCIPLINE_USERS(user_id)`** -- **`CUSTOM_TRACKING_CATEGORIES(custom_tracking_id, user_id, name)`** -- **`TASKS(task_id, discipline_user_id, custom_tracking_id, name, is_finished)`** -- **`DAILY_COMPLETION(daily_completion_id, user_id, date, procent)`** -- **`TASK_DAILY_COMPLETION(task_id, daily_completion_id)`** -- **`FINANCE_USERS(user_id, spending_budget, saving_budget, investing_budget, donation_budget, credit)`** -- **`INCOMES(income_id, user_id, date, amount)`** - -== **Проверка за 3НФ** - -Ги разгледуваме релациите добиени по 2НФ. - -Проблематични транзитивни/изведени атрибути се: - -- **`num_tasks`** -- **`tasks`** -- **`weight_user_id`** во `TRAINING_SESSIONS` како непотребна зависност за оваа верзија на моделот - -== **Декомпозиција по 3НФ** - -1. **`DISCIPLINE_USERS(user_id)`** -> се задржува само идентификаторот што ја врзува дисциплинската улога со `USERS`. - -2. **`CUSTOM_TRACKING_CATEGORIES(custom_tracking_id, user_id, name)`** -> се задржува само името на категоријата, без броење и листи на задачи. - -3. **`TRAINING_SESSIONS(training_id, training_user_id, date, type, duration, calories)`** -> сесијата се врзува само со `training_user_id`. - -**Резултат** - -Нема не-клучен атрибут што зависи од друг не-клучен атрибут во истата релација. - -== **Проверка за БКНФ** - -Сите добиени релации се во БКНФ, бидејќи секој детерминант е кандидат-клуч во својата релација. - -Особено: - -- **`USERS`** ги задржува алтернативните клучеви `email` и `username` -- **`TASK_DAILY_COMPLETION(task_id, daily_completion_id)`** е спојна релација без не-клучни атрибути -- профилните релации со `user_id` се чисти 1:1 продолжувања на `USERS` - -== **Финален резултат и дискусија** - -== **Нормализиран релациски модел** - -Финалниот модел што го користиме во тековната имплементација е: - -- **`USERS`** -- **`TRAINING_USERS`** -- **`TRAINING_SESSIONS`** -- **`INVESTOR_USERS`** -- **`ASSETS`** -- **`WEIGHT_USERS`** -- **`DAILY_INTAKES`** -- **`DISCIPLINE_USERS`** -- **`CUSTOM_TRACKING_CATEGORIES`** -- **`TASKS`** -- **`DAILY_COMPLETION`** -- **`TASK_DAILY_COMPLETION`** -- **`FINANCE_USERS`** -- **`INCOMES`** - -== **Дискусија** - -Разлики во однос на претходниот дизајн: - -- Се отстранети **`num_tasks`** и **`tasks`** од дисциплинските табели. -- Се отстранета непотребната релација **`weight_user_id`** од `TRAINING_SESSIONS`. -- Се задржуваат профилните табели со **`user_id`** како заеднички примарен и странски клуч. - -Одлука за следните фази: - -- **Овој нормализиран модел** останува извор на вистина. -- **API форматот** може да остане стабилен, додека внатрешната шема е нормализирана. -- **DDL и DML скриптите** треба да ја следат оваа шема. Index: ckend/docs/P6_Advanced_Reports.txt =================================================================== --- backend/docs/P6_Advanced_Reports.txt (revision a8381b18babc398c094b70c3ca5dc6cb0483c327) +++ (revision ) @@ -1,702 +1,0 @@ -= Напредни извештаи од базата (SQL, складирани процедури и релациона алгебра) - -=== 1. Детален годишен извештај за финансиска резилиентност, стабилност на приходи и буџетски притисок по корисник - -==== SQL -{{{ -SET search_path TO trekr; - -WITH params AS ( - SELECT 2026::int AS report_year -), -months AS ( - SELECT generate_series(1, 12) AS month_no -), -finance_base AS ( - SELECT - fu.user_id, - u.username, - u.email, - COALESCE(fu.spending_budget, 0) AS spending_budget, - COALESCE(fu.saving_budget, 0) AS saving_budget, - COALESCE(fu.investing_budget, 0) AS investing_budget, - COALESCE(fu.donation_budget, 0) AS donation_budget, - COALESCE(fu.credit, 0) AS credit - FROM finance_users fu - JOIN users u ON u.user_id = fu.user_id -), -monthly_income AS ( - SELECT - fb.user_id, - m.month_no, - COALESCE(SUM(i.amount), 0) AS month_income - FROM finance_base fb - CROSS JOIN months m - LEFT JOIN incomes i - ON i.user_id = fb.user_id - AND EXTRACT(YEAR FROM i.date)::int = (SELECT report_year FROM params) - AND EXTRACT(MONTH FROM i.date)::int = m.month_no - GROUP BY fb.user_id, m.month_no -), -monthly_income_ranked AS ( - SELECT - mi.*, - DENSE_RANK() OVER (PARTITION BY mi.user_id ORDER BY mi.month_income DESC, mi.month_no ASC) AS best_month_rank, - DENSE_RANK() OVER (PARTITION BY mi.user_id ORDER BY mi.month_income ASC, mi.month_no ASC) AS worst_month_rank - FROM monthly_income mi -), -annual_income AS ( - SELECT - user_id, - SUM(month_income) AS total_income, - AVG(month_income) AS avg_monthly_income, - STDDEV_SAMP(month_income) AS income_stddev, - MAX(month_income) AS best_month_income, - MIN(month_income) AS worst_month_income, - COUNT(*) FILTER (WHERE month_income > 0) AS active_income_months - FROM monthly_income - GROUP BY user_id -), -best_worst_months AS ( - SELECT - user_id, - MAX(month_no) FILTER (WHERE best_month_rank = 1) AS best_month_no, - MAX(month_no) FILTER (WHERE worst_month_rank = 1) AS worst_month_no - FROM monthly_income_ranked - GROUP BY user_id -) -SELECT - fb.user_id, - fb.username, - fb.email, - (fb.spending_budget + fb.saving_budget + fb.investing_budget + fb.donation_budget) * 12 AS planned_annual_budget, - ai.total_income AS actual_annual_income, - ai.avg_monthly_income, - ai.active_income_months, - ai.best_month_income, - ai.worst_month_income, - bwm.best_month_no, - bwm.worst_month_no, - ROUND( - (ai.income_stddev / NULLIF(ai.avg_monthly_income, 0))::numeric, - 4 - ) AS income_volatility_cv, - ROUND( - (ai.total_income - (fb.spending_budget * 12))::numeric, - 2 - ) AS annual_free_cash_after_spending, - ROUND( - ((fb.spending_budget * 12) / NULLIF(ai.total_income, 0))::numeric, - 4 - ) AS spending_pressure_ratio, - ROUND( - (fb.credit / NULLIF(ai.total_income, 0))::numeric, - 4 - ) AS leverage_ratio, - DENSE_RANK() OVER ( - ORDER BY - (ai.total_income - (fb.spending_budget * 12)) DESC, - ((fb.spending_budget * 12) / NULLIF(ai.total_income, 0)) ASC, - fb.user_id ASC - ) AS finance_resilience_rank -FROM finance_base fb -JOIN annual_income ai ON ai.user_id = fb.user_id -JOIN best_worst_months bwm ON bwm.user_id = fb.user_id -ORDER BY finance_resilience_rank, fb.user_id; -}}} - -==== Релациона Алгебра -{{{ -FB <- pi_{fu.user_id, u.username, u.email, - COALESCE(fu.spending_budget,0)->spending_budget, - COALESCE(fu.saving_budget,0)->saving_budget, - COALESCE(fu.investing_budget,0)->investing_budget, - COALESCE(fu.donation_budget,0)->donation_budget, - COALESCE(fu.credit,0)->credit} - (finance_users fu bowtie_{fu.user_id = u.user_id} users u) - -FBM <- FB x M -IY <- sigma_{YEAR(i.date)=Y}(incomes i) -MI0 <- FBM leftouterjoin_{FBM.user_id = i.user_id AND FBM.month_no = MONTH(i.date)} IY -MI <- gamma_{user_id, month_no; - SUM(COALESCE(i.amount,0))->month_income}(MI0) - -MIR <- omega_{PARTITION BY user_id ORDER BY month_income DESC, month_no ASC; - DENSE_RANK()->best_month_rank, - DENSE_RANK(PARTITION BY user_id ORDER BY month_income ASC, month_no ASC)->worst_month_rank}(MI) - -AI <- gamma_{user_id; - SUM(month_income)->total_income, - AVG(month_income)->avg_monthly_income, - STDDEV_SAMP(month_income)->income_stddev, - MAX(month_income)->best_month_income, - MIN(month_income)->worst_month_income, - COUNT_IF(month_income>0)->active_income_months}(MI) - -BWM <- gamma_{user_id; - MAX_IF(month_no, best_month_rank=1)->best_month_no, - MAX_IF(month_no, worst_month_rank=1)->worst_month_no}(MIR) - -R0 <- FB bowtie_{FB.user_id=AI.user_id} AI bowtie_{FB.user_id=BWM.user_id} BWM -R1 <- alpha_{(spending_budget+saving_budget+investing_budget+donation_budget)*12->planned_annual_budget, - total_income->actual_annual_income, - income_stddev/NULLIF(avg_monthly_income,0)->income_volatility_cv, - total_income-(spending_budget*12)->annual_free_cash_after_spending, - (spending_budget*12)/NULLIF(total_income,0)->spending_pressure_ratio, - credit/NULLIF(total_income,0)->leverage_ratio}(R0) -R <- omega_{ORDER BY annual_free_cash_after_spending DESC, - spending_pressure_ratio ASC, - user_id ASC; - DENSE_RANK()->finance_resilience_rank}(R1) -}}} - -=== 2. Детален годишен извештај за конзистентност на тренинг, оптоварување и тренд на перформанс - -==== SQL -{{{ -SET search_path TO trekr; - -WITH params AS ( - SELECT 2026::int AS report_year -), -months AS ( - SELECT generate_series(1, 12) AS month_no -), -training_base AS ( - SELECT - tu.user_id, - u.username, - u.email, - tu.gender, - tu.age, - tu.weight - FROM training_users tu - JOIN users u ON u.user_id = tu.user_id -), -monthly_sessions AS ( - SELECT - tb.user_id, - m.month_no, - COALESCE(COUNT(ts.training_id), 0) AS sessions_count, - COALESCE(SUM(ts.duration), 0) AS total_duration_minutes, - COALESCE(SUM(ts.calories), 0) AS total_calories, - COALESCE(AVG(ts.duration), 0) AS avg_session_duration, - COALESCE(AVG(ts.calories), 0) AS avg_session_calories - FROM training_base tb - CROSS JOIN months m - LEFT JOIN training_sessions ts - ON ts.training_user_id = tb.user_id - AND EXTRACT(YEAR FROM ts.date)::int = (SELECT report_year FROM params) - AND EXTRACT(MONTH FROM ts.date)::int = m.month_no - GROUP BY tb.user_id, m.month_no -), -monthly_ranked AS ( - SELECT - ms.*, - DENSE_RANK() OVER (PARTITION BY ms.user_id ORDER BY ms.total_calories DESC, ms.month_no ASC) AS peak_calorie_month_rank, - DENSE_RANK() OVER (PARTITION BY ms.user_id ORDER BY ms.sessions_count DESC, ms.month_no ASC) AS peak_sessions_month_rank - FROM monthly_sessions ms -), -active_month_streaks AS ( - SELECT - user_id, - month_no, - month_no - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY month_no) AS grp - FROM monthly_sessions - WHERE sessions_count > 0 -), -longest_streak AS ( - SELECT - user_id, - MAX(streak_len) AS longest_active_month_streak - FROM ( - SELECT user_id, grp, COUNT(*) AS streak_len - FROM active_month_streaks - GROUP BY user_id, grp - ) s - GROUP BY user_id -), -annual_training AS ( - SELECT - user_id, - SUM(sessions_count) AS annual_sessions, - SUM(total_duration_minutes) AS annual_duration_minutes, - SUM(total_calories) AS annual_calories, - AVG(total_duration_minutes) AS avg_monthly_duration, - AVG(total_calories) AS avg_monthly_calories, - COUNT(*) FILTER (WHERE sessions_count > 0) AS active_months, - REGR_SLOPE(total_calories::numeric, month_no::numeric) AS calories_trend_slope, - REGR_SLOPE(total_duration_minutes::numeric, month_no::numeric) AS duration_trend_slope - FROM monthly_sessions - GROUP BY user_id -), -peak_months AS ( - SELECT - user_id, - MAX(month_no) FILTER (WHERE peak_calorie_month_rank = 1) AS peak_calorie_month_no, - MAX(month_no) FILTER (WHERE peak_sessions_month_rank = 1) AS peak_sessions_month_no - FROM monthly_ranked - GROUP BY user_id -) -SELECT - tb.user_id, - tb.username, - tb.email, - tb.gender, - tb.age, - tb.weight, - at.annual_sessions, - ROUND(at.annual_duration_minutes::numeric, 2) AS annual_duration_minutes, - ROUND(at.annual_calories::numeric, 2) AS annual_calories, - at.active_months, - ROUND((at.active_months / 12.0)::numeric, 4) AS consistency_ratio, - COALESCE(ls.longest_active_month_streak, 0) AS longest_active_month_streak, - pm.peak_calorie_month_no, - pm.peak_sessions_month_no, - ROUND(COALESCE(at.calories_trend_slope, 0)::numeric, 4) AS calories_trend_slope, - ROUND(COALESCE(at.duration_trend_slope, 0)::numeric, 4) AS duration_trend_slope, - DENSE_RANK() OVER ( - ORDER BY - at.annual_calories DESC, - at.active_months DESC, - COALESCE(ls.longest_active_month_streak, 0) DESC, - tb.user_id ASC - ) AS training_annual_rank -FROM training_base tb -JOIN annual_training at ON at.user_id = tb.user_id -JOIN peak_months pm ON pm.user_id = tb.user_id -LEFT JOIN longest_streak ls ON ls.user_id = tb.user_id -ORDER BY training_annual_rank, tb.user_id; -}}} - -==== Релациона Алгебра -{{{ -TB <- pi_{tu.user_id, u.username, u.email, tu.gender, tu.age, tu.weight} - (training_users tu bowtie_{tu.user_id = u.user_id} users u) - -TBM <- TB x M -TSY <- sigma_{YEAR(ts.date)=Y}(training_sessions ts) -MS0 <- TBM leftouterjoin_{TBM.user_id = ts.training_user_id AND TBM.month_no = MONTH(ts.date)} TSY -MS <- gamma_{user_id, month_no; - COUNT(ts.training_id)->sessions_count, - SUM(COALESCE(ts.duration,0))->total_duration_minutes, - SUM(COALESCE(ts.calories,0))->total_calories, - AVG(COALESCE(ts.duration,0))->avg_session_duration, - AVG(COALESCE(ts.calories,0))->avg_session_calories}(MS0) - -MR <- omega_{PARTITION BY user_id ORDER BY total_calories DESC, month_no ASC; - DENSE_RANK()->peak_calorie_month_rank, - DENSE_RANK(PARTITION BY user_id ORDER BY sessions_count DESC, month_no ASC)->peak_sessions_month_rank}(MS) - -AMS <- sigma_{sessions_count>0}(MS) -AMS1 <- omega_{PARTITION BY user_id ORDER BY month_no; - ROW_NUMBER()->rn}(AMS) -AMS2 <- alpha_{month_no - rn -> grp}(AMS1) -LS0 <- gamma_{user_id, grp; COUNT(*)->streak_len}(AMS2) -LS <- gamma_{user_id; MAX(streak_len)->longest_active_month_streak}(LS0) - -AT <- gamma_{user_id; - SUM(sessions_count)->annual_sessions, - SUM(total_duration_minutes)->annual_duration_minutes, - SUM(total_calories)->annual_calories, - AVG(total_duration_minutes)->avg_monthly_duration, - AVG(total_calories)->avg_monthly_calories, - COUNT_IF(sessions_count>0)->active_months, - REGR_SLOPE(total_calories, month_no)->calories_trend_slope, - REGR_SLOPE(total_duration_minutes, month_no)->duration_trend_slope}(MS) - -PM <- gamma_{user_id; - MAX_IF(month_no, peak_calorie_month_rank=1)->peak_calorie_month_no, - MAX_IF(month_no, peak_sessions_month_rank=1)->peak_sessions_month_no}(MR) - -R0 <- TB bowtie_{TB.user_id=AT.user_id} AT - bowtie_{TB.user_id=PM.user_id} PM - leftouterjoin_{TB.user_id=LS.user_id} LS -R1 <- alpha_{active_months/12.0->consistency_ratio, - COALESCE(longest_active_month_streak,0)->longest_active_month_streak_nz, - COALESCE(calories_trend_slope,0)->calories_trend_slope_nz, - COALESCE(duration_trend_slope,0)->duration_trend_slope_nz}(R0) -R <- omega_{ORDER BY annual_calories DESC, - active_months DESC, - longest_active_month_streak_nz DESC, - user_id ASC; - DENSE_RANK()->training_annual_rank}(R1) -}}} - -=== 3. Детален годишен извештај за дисциплина, квалитет на завршување и однесување преку streaks - -==== SQL -{{{ -SET search_path TO trekr; - -WITH params AS ( - SELECT 2026::int AS report_year -), -discipline_base AS ( - SELECT - du.user_id, - u.username, - u.email - FROM discipline_users du - JOIN users u ON u.user_id = du.user_id -), -task_mix AS ( - SELECT - COALESCE(t.discipline_user_id, c.user_id) AS user_id, - COUNT(*) AS total_tasks_defined, - COUNT(*) FILTER (WHERE t.custom_tracking_id IS NULL) AS core_tasks, - COUNT(*) FILTER (WHERE t.custom_tracking_id IS NOT NULL) AS custom_tasks, - COUNT(DISTINCT COALESCE(t.custom_tracking_id::text, 'core')) AS task_category_span - FROM tasks t - LEFT JOIN custom_tracking_categories c - ON c.custom_tracking_id = t.custom_tracking_id - WHERE t.discipline_user_id IS NOT NULL - OR t.custom_tracking_id IS NOT NULL - GROUP BY COALESCE(t.discipline_user_id, c.user_id) -), -annual_daily_completion AS ( - SELECT - dc.user_id, - dc.date, - COALESCE(dc.procent, 0) AS procent, - CASE WHEN COALESCE(dc.procent, 0) >= 80 THEN 1 ELSE 0 END AS strong_day - FROM daily_completion dc - WHERE EXTRACT(YEAR FROM dc.date)::int = (SELECT report_year FROM params) -), -daily_completion_stats AS ( - SELECT - adc.user_id, - COUNT(*) AS tracked_days, - AVG(adc.procent) AS avg_completion_percent, - PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY adc.procent) AS median_completion_percent, - COUNT(*) FILTER (WHERE adc.procent = 100) AS perfect_days, - COUNT(*) FILTER (WHERE adc.procent >= 80) AS strong_days, - STDDEV_SAMP(adc.procent) AS completion_variability - FROM annual_daily_completion adc - GROUP BY adc.user_id -), -strong_day_streaks AS ( - SELECT - user_id, - date, - date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date))::int AS grp - FROM annual_daily_completion - WHERE strong_day = 1 -), -longest_strong_streak AS ( - SELECT - user_id, - MAX(streak_len) AS longest_strong_day_streak - FROM ( - SELECT user_id, grp, COUNT(*) AS streak_len - FROM strong_day_streaks - GROUP BY user_id, grp - ) s - GROUP BY user_id -), -annual_task_execution AS ( - SELECT - dc.user_id, - COUNT(tdc.task_id) AS completed_task_events - FROM daily_completion dc - LEFT JOIN task_daily_completion tdc - ON tdc.daily_completion_id = dc.daily_completion_id - WHERE EXTRACT(YEAR FROM dc.date)::int = (SELECT report_year FROM params) - GROUP BY dc.user_id -) -SELECT - db.user_id, - db.username, - db.email, - COALESCE(tm.total_tasks_defined, 0) AS total_tasks_defined, - COALESCE(tm.core_tasks, 0) AS core_tasks, - COALESCE(tm.custom_tasks, 0) AS custom_tasks, - COALESCE(tm.task_category_span, 0) AS task_category_span, - COALESCE(dcs.tracked_days, 0) AS tracked_days, - ROUND(COALESCE(dcs.avg_completion_percent, 0)::numeric, 2) AS avg_completion_percent, - ROUND(COALESCE(dcs.median_completion_percent, 0)::numeric, 2) AS median_completion_percent, - COALESCE(dcs.perfect_days, 0) AS perfect_days, - COALESCE(dcs.strong_days, 0) AS strong_days, - ROUND(COALESCE(dcs.completion_variability, 0)::numeric, 4) AS completion_variability, - COALESCE(ate.completed_task_events, 0) AS completed_task_events, - COALESCE(lss.longest_strong_day_streak, 0) AS longest_strong_day_streak, - ROUND( - COALESCE((COALESCE(dcs.strong_days, 0) / NULLIF(COALESCE(dcs.tracked_days, 0), 0)::numeric), 0), - 4 - ) AS strong_day_ratio, - ROUND( - ( - COALESCE(dcs.avg_completion_percent, 0) * 0.45 - + COALESCE(lss.longest_strong_day_streak, 0) * 2.00 - + COALESCE(ate.completed_task_events, 0) * 0.35 - )::numeric, - 2 - ) AS discipline_composite_score, - DENSE_RANK() OVER ( - ORDER BY - ( - COALESCE(dcs.avg_completion_percent, 0) * 0.45 - + COALESCE(lss.longest_strong_day_streak, 0) * 2.00 - + COALESCE(ate.completed_task_events, 0) * 0.35 - ) DESC, - db.user_id ASC - ) AS discipline_annual_rank -FROM discipline_base db -LEFT JOIN task_mix tm ON tm.user_id = db.user_id -LEFT JOIN daily_completion_stats dcs ON dcs.user_id = db.user_id -LEFT JOIN annual_task_execution ate ON ate.user_id = db.user_id -LEFT JOIN longest_strong_streak lss ON lss.user_id = db.user_id -ORDER BY discipline_annual_rank, db.user_id; -}}} - -==== Релациона Алгебра -{{{ -DB <- pi_{du.user_id, u.username, u.email} - (discipline_users du bowtie_{du.user_id = u.user_id} users u) - -TC <- tasks t leftouterjoin_{t.custom_tracking_id = c.custom_tracking_id} custom_tracking_categories c -TM0 <- alpha_{COALESCE(t.discipline_user_id, c.user_id)->owner_user_id}(TC) -TM1 <- sigma_{t.discipline_user_id IS NOT NULL OR t.custom_tracking_id IS NOT NULL}(TM0) -TM <- gamma_{owner_user_id; - COUNT(*)->total_tasks_defined, - COUNT_IF(t.custom_tracking_id IS NULL)->core_tasks, - COUNT_IF(t.custom_tracking_id IS NOT NULL)->custom_tasks, - COUNT_DISTINCT(COALESCE(t.custom_tracking_id,'core'))->task_category_span}(TM1) - -ADC0 <- sigma_{YEAR(dc.date)=Y}(daily_completion dc) -ADC <- alpha_{COALESCE(dc.procent,0)->procent, - CASE(procent>=80,1,0)->strong_day}(ADC0) - -DCS <- gamma_{user_id; - COUNT(*)->tracked_days, - AVG(procent)->avg_completion_percent, - PERCENTILE_CONT_0_5(procent)->median_completion_percent, - COUNT_IF(procent=100)->perfect_days, - COUNT_IF(procent>=80)->strong_days, - STDDEV_SAMP(procent)->completion_variability}(ADC) - -SDS0 <- sigma_{strong_day=1}(ADC) -SDS1 <- omega_{PARTITION BY user_id ORDER BY date; ROW_NUMBER()->rn}(SDS0) -SDS2 <- alpha_{date - rn -> grp}(SDS1) -LSS0 <- gamma_{user_id, grp; COUNT(*)->streak_len}(SDS2) -LSS <- gamma_{user_id; MAX(streak_len)->longest_strong_day_streak}(LSS0) - -ATE0 <- ADC0 leftouterjoin_{ADC0.daily_completion_id = tdc.daily_completion_id} task_daily_completion tdc -ATE <- gamma_{ADC0.user_id; COUNT(tdc.task_id)->completed_task_events}(ATE0) - -R0 <- DB - leftouterjoin_{DB.user_id = TM.owner_user_id} TM - leftouterjoin_{DB.user_id = DCS.user_id} DCS - leftouterjoin_{DB.user_id = ATE.user_id} ATE - leftouterjoin_{DB.user_id = LSS.user_id} LSS -R1 <- alpha_{COALESCE(total_tasks_defined,0)->total_tasks_defined_nz, - COALESCE(core_tasks,0)->core_tasks_nz, - COALESCE(custom_tasks,0)->custom_tasks_nz, - COALESCE(task_category_span,0)->task_category_span_nz, - COALESCE(tracked_days,0)->tracked_days_nz, - COALESCE(avg_completion_percent,0)->avg_completion_percent_nz, - COALESCE(median_completion_percent,0)->median_completion_percent_nz, - COALESCE(perfect_days,0)->perfect_days_nz, - COALESCE(strong_days,0)->strong_days_nz, - COALESCE(completion_variability,0)->completion_variability_nz, - COALESCE(completed_task_events,0)->completed_task_events_nz, - COALESCE(longest_strong_day_streak,0)->longest_strong_day_streak_nz, - COALESCE(strong_days/NULLIF(tracked_days,0),0)->strong_day_ratio, - (COALESCE(avg_completion_percent,0)*0.45 + - COALESCE(longest_strong_day_streak,0)*2.00 + - COALESCE(completed_task_events,0)*0.35)->discipline_composite_score}(R0) -R <- omega_{ORDER BY discipline_composite_score DESC, user_id ASC; - DENSE_RANK()->discipline_annual_rank}(R1) -}}} - -=== 4. Детален годишен извештај за инвестициска диверзификација, концентрација и темпо на вложување - -==== SQL -{{{ -SET search_path TO trekr; - -WITH params AS ( - SELECT 2026::int AS report_year -), -months AS ( - SELECT generate_series(1, 12) AS month_no -), -investor_base AS ( - SELECT - iu.user_id, - u.username, - u.email - FROM investor_users iu - JOIN users u ON u.user_id = iu.user_id -), -annual_asset_lots AS ( - SELECT - a.user_id, - a.ticker_symbol, - COALESCE(a.quantity, 0) AS quantity, - COALESCE(a.buy_price, 0) AS buy_price, - COALESCE(a.quantity, 0) * COALESCE(a.buy_price, 0) AS invested_amount, - a.buy_date - FROM assets a - WHERE EXTRACT(YEAR FROM a.buy_date)::int = (SELECT report_year FROM params) -), -ticker_rollup AS ( - SELECT - aal.user_id, - aal.ticker_symbol, - SUM(aal.quantity) AS total_quantity, - SUM(aal.invested_amount) AS total_invested_amount, - COUNT(*) AS lot_count, - MIN(aal.buy_date) AS first_buy_date, - MAX(aal.buy_date) AS last_buy_date - FROM annual_asset_lots aal - GROUP BY aal.user_id, aal.ticker_symbol -), -portfolio_totals AS ( - SELECT - user_id, - SUM(total_invested_amount) AS annual_total_invested, - SUM(lot_count) AS annual_lot_count, - COUNT(*) AS distinct_tickers - FROM ticker_rollup - GROUP BY user_id -), -weights AS ( - SELECT - tr.user_id, - tr.ticker_symbol, - tr.total_invested_amount, - pt.annual_total_invested, - (tr.total_invested_amount / NULLIF(pt.annual_total_invested, 0)) AS position_weight, - DENSE_RANK() OVER ( - PARTITION BY tr.user_id - ORDER BY tr.total_invested_amount DESC, tr.ticker_symbol ASC - ) AS position_rank - FROM ticker_rollup tr - JOIN portfolio_totals pt ON pt.user_id = tr.user_id -), -concentration AS ( - SELECT - user_id, - SUM(position_weight * position_weight) AS hhi_concentration, - MAX(position_weight) AS top_position_weight, - MAX(ticker_symbol) FILTER (WHERE position_rank = 1) AS top_ticker - FROM weights - GROUP BY user_id -), -monthly_investment AS ( - SELECT - ib.user_id, - m.month_no, - COALESCE(SUM(a.quantity * a.buy_price), 0) AS monthly_invested_amount - FROM investor_base ib - CROSS JOIN months m - LEFT JOIN assets a - ON a.user_id = ib.user_id - AND EXTRACT(YEAR FROM a.buy_date)::int = (SELECT report_year FROM params) - AND EXTRACT(MONTH FROM a.buy_date)::int = m.month_no - GROUP BY ib.user_id, m.month_no -), -monthly_investment_stats AS ( - SELECT - user_id, - AVG(monthly_invested_amount) AS avg_monthly_contribution, - STDDEV_SAMP(monthly_invested_amount) AS contribution_stddev, - COUNT(*) FILTER (WHERE monthly_invested_amount > 0) AS active_investing_months - FROM monthly_investment - GROUP BY user_id -) -SELECT - ib.user_id, - ib.username, - ib.email, - COALESCE(pt.annual_total_invested, 0) AS annual_total_invested, - COALESCE(pt.annual_lot_count, 0) AS annual_lot_count, - COALESCE(pt.distinct_tickers, 0) AS distinct_tickers, - ROUND(COALESCE(ms.avg_monthly_contribution, 0)::numeric, 2) AS avg_monthly_contribution, - COALESCE(ms.active_investing_months, 0) AS active_investing_months, - ROUND((COALESCE(ms.active_investing_months, 0) / 12.0)::numeric, 4) AS activity_ratio, - ROUND(COALESCE(c.hhi_concentration, 0)::numeric, 4) AS hhi_concentration, - ROUND((1 - COALESCE(c.hhi_concentration, 1))::numeric, 4) AS diversification_index, - ROUND(COALESCE(c.top_position_weight, 0)::numeric, 4) AS top_position_weight, - c.top_ticker, - ROUND((COALESCE(ms.contribution_stddev, 0) / NULLIF(ms.avg_monthly_contribution, 0))::numeric, 4) AS contribution_volatility_cv, - DENSE_RANK() OVER ( - ORDER BY - (1 - COALESCE(c.hhi_concentration, 1)) DESC, - COALESCE(pt.annual_total_invested, 0) DESC, - COALESCE(ms.active_investing_months, 0) DESC, - ib.user_id ASC - ) AS investing_annual_rank -FROM investor_base ib -LEFT JOIN portfolio_totals pt ON pt.user_id = ib.user_id -LEFT JOIN concentration c ON c.user_id = ib.user_id -LEFT JOIN monthly_investment_stats ms ON ms.user_id = ib.user_id -ORDER BY investing_annual_rank, ib.user_id; -}}} - -==== Релациона Алгебра -{{{ -IB <- pi_{iu.user_id, u.username, u.email} - (investor_users iu bowtie_{iu.user_id = u.user_id} users u) - -AAL <- pi_{a.user_id, a.ticker_symbol, - COALESCE(a.quantity,0)->quantity, - COALESCE(a.buy_price,0)->buy_price, - COALESCE(a.quantity,0)*COALESCE(a.buy_price,0)->invested_amount, - a.buy_date} - (sigma_{YEAR(a.buy_date)=Y}(assets a)) - -TR <- gamma_{user_id, ticker_symbol; - SUM(quantity)->total_quantity, - SUM(invested_amount)->total_invested_amount, - COUNT(*)->lot_count, - MIN(buy_date)->first_buy_date, - MAX(buy_date)->last_buy_date}(AAL) - -PT <- gamma_{user_id; - SUM(total_invested_amount)->annual_total_invested, - SUM(lot_count)->annual_lot_count, - COUNT(*)->distinct_tickers}(TR) - -W0 <- TR bowtie_{TR.user_id = PT.user_id} PT -W1 <- alpha_{total_invested_amount/NULLIF(annual_total_invested,0)->position_weight}(W0) -W <- omega_{PARTITION BY user_id ORDER BY total_invested_amount DESC, ticker_symbol ASC; - DENSE_RANK()->position_rank}(W1) - -C <- gamma_{user_id; - SUM(position_weight*position_weight)->hhi_concentration, - MAX(position_weight)->top_position_weight, - MAX_IF(ticker_symbol, position_rank=1)->top_ticker}(W) - -IBM <- IB x M -AY <- sigma_{YEAR(a.buy_date)=Y}(assets a) -MI0 <- IBM leftouterjoin_{IBM.user_id=a.user_id AND IBM.month_no=MONTH(a.buy_date)} AY -MI <- gamma_{user_id, month_no; - SUM(COALESCE(a.quantity,0)*COALESCE(a.buy_price,0))->monthly_invested_amount}(MI0) -MS <- gamma_{user_id; - AVG(monthly_invested_amount)->avg_monthly_contribution, - STDDEV_SAMP(monthly_invested_amount)->contribution_stddev, - COUNT_IF(monthly_invested_amount>0)->active_investing_months}(MI) - -R0 <- IB - leftouterjoin_{IB.user_id=PT.user_id} PT - leftouterjoin_{IB.user_id=C.user_id} C - leftouterjoin_{IB.user_id=MS.user_id} MS -R1 <- alpha_{COALESCE(annual_total_invested,0)->annual_total_invested_nz, - COALESCE(annual_lot_count,0)->annual_lot_count_nz, - COALESCE(distinct_tickers,0)->distinct_tickers_nz, - COALESCE(avg_monthly_contribution,0)->avg_monthly_contribution_nz, - COALESCE(active_investing_months,0)->active_investing_months_nz, - COALESCE(active_investing_months,0)/12.0->activity_ratio, - COALESCE(hhi_concentration,0)->hhi_concentration_nz, - 1-COALESCE(hhi_concentration,1)->diversification_index, - COALESCE(top_position_weight,0)->top_position_weight_nz, - COALESCE(contribution_stddev/NULLIF(avg_monthly_contribution,0),0)->contribution_volatility_cv}(R0) -R <- omega_{ORDER BY diversification_index DESC, - annual_total_invested_nz DESC, - active_investing_months_nz DESC, - user_id ASC; - DENSE_RANK()->investing_annual_rank}(R1) -}}} - Index: backend/docs/SQL_Injection_Prevention.txt =================================================================== --- backend/docs/SQL_Injection_Prevention.txt (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) +++ backend/docs/SQL_Injection_Prevention.txt (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -0,0 +1,350 @@ += SQL Injection Prevention Strategy = + +==概述 == + +SQL Injection е критична безбедносна ранливост каде нападачот внесува злонамерен SQL код преку корисничкиот влез за манипулирање со базата на податоци. Овој документ опишува како е имплементирана заштитата во Trekr backend за спречување на овој вид напади. + +== 1. Безбедни пракси имплементирани во проектот == + +=== 1.1 Параметризирани пропити преку JPA === + +Целиот backend користи Spring Data JPA која автоматски генерира параметризирани пропити што спречуваат SQL injection. + +==== Безбедно: Derived Query Methods ==== + +Кога користиме method names на Spring Data, те се автоматски преведуваат во параметризирани SQL пропити: + +{{{ +// UserRepository.java +public interface UserRepository extends JpaRepository { + Optional findByEmail(String email); + Optional findByUsername(String username); +} + +// Сэчно користи параметриран пропит во подложина (EQUIVALENT TO): +// SELECT * FROM users WHERE email = ? +// параметарот se праќа одвоено од SQL командата +}}} + +==== Безбедно: @Query со @Param ==== + +За комплексни пропити, користториве @Query со именувани параметри: + +{{{ +// TaskRepository.java +@Modifying +@Query("update Task t set t.finished = false where t.disciplineUser.userId = :userId") +int resetFinishedForUser(@Param("userId") Long userId); + +// Параметарот :userId се врзува безбедно преку JDBC, не преку string concatenation +}}} + +==== Безбедно: Collection параметри ==== + +За множество вредности (IN клаузули), користи параметризиран пропит: + +{{{ +// TrainingSessionRepository.java +@java.util.List findByTrainingUser_UserIdAndDateIn( + Long userId, + Collection dates +); + +// Сэчно: SELECT * FROM training_sessions WHERE training_user_id = ? AND date IN (?, ?, ?) +// Сите параметри се врзени безбедно, не преку строг конкатенација +}}} + +=== 1.2 Строга типизација на параметри === + +Кога параметарите се типизирани (Long, BigDecimal, LocalDate), т.е. не strings, SQL injection е веќе невозможна: + +{{{ +// FinanceService.java - пример од createIncome +BigDecimal amount = request.getAmount(); // BigDecimal, не String +if (amount.compareTo(BigDecimal.ZERO) <= 0) { + throw new RuntimeException("Amount must be greater than 0"); +} + +Income income = new Income(); +income.setAmount(amount); // Num values can't inject SQL +incomeRepository.save(income); + +// Генерирана SQL: INSERT INTO incomes (amount) VALUES (?) +// Параметарот amount се врзува како број, не како строк +}}} + +=== 1.3 Основна валидација на влезот === + +Така поради внесување и валидација на нивото на апликација пред db операции: + +{{{ +// CustomTrackingService.java +public CustomTrackingCategoryDto createCustomTrackingCategory( + Long userId, + CreateCustomTrackingCategoryRequest request) { + + String name = request.getName(); + + // Валидација: не null/empty + if (name == null || name.isBlank()) { + throw new RuntimeException("Name is required"); + } + + String trimmed = name.trim(); + + // Валидација: должина + if (trimmed.length() > 100) { + throw new RuntimeException("Name is too long"); + } + + // На крај, trimmed value се зачувува преку ORM + // Никогаш не се користи raw SQL string concatenation + CustomTrackingCategory category = new CustomTrackingCategory(); + category.setName(trimmed); + customTrackingCategoryRepository.save(category); +} +}}} + +=== 1.4 Authorization checks за ownership === + +Дополнително заштита: провера дека конкретен корисник има право да приступи на специфичната информација: + +{{{ +// CustomTrackingService.java +@Transactional +public void deleteCustomCategoryTask(Long userId, Long customTrackingId, Long taskId) { + Task task = taskRepository.findById(taskId) + .orElseThrow(() -> new RuntimeException("Task not found")); + + // Провера: дали задачата припаѓа на корисникот + if (task.getCustomTrackingCategory() == null + || !customTrackingId.equals(task.getCustomTrackingCategory().getCustomTrackingId()) + || task.getCustomTrackingCategory().getUser() == null + || !userId.equals(task.getCustomTrackingCategory().getUser().getUserId())) { + throw new RuntimeException("Task not found"); // Скриена информација + } + + int deleted = taskRepository.deleteByTaskIdAndCustomTrackingCategory_CustomTrackingIdAndCustomTrackingCategory_User_UserId( + taskId, customTrackingId, userId); + if (deleted == 0) { + throw new RuntimeException("Task not found"); + } +} +}}} + +== 2. НЕБЕЗБЕДНИ ПРАКСИ која НИКОГАШ не смеаат да бидат користени == + +=== 2.1 String Concatenation со SQL === + +❌ НЕБЕЗБЕДНО - НЕ КВАЧИ НИКОГАШ: + +{{{ +// ОПАСНО: SQL injection уязвимост +String email = userInput; // од форма или параметар +String query = "SELECT * FROM users WHERE email = '" + email + "'"; +// Ако userInput = "admin' OR '1'='1", резултира: +// SELECT * FROM users WHERE email = 'admin' OR '1'='1' +// Сие врати сите корисници! +}}} + +=== 2.2 Директни SQL пропити со методи како createNativeQuery без параметри === + +❌ НЕБЕЗБЕДНО: + +{{{ +// ОПАСНО +String userProvidedFilter = request.getFilter(); +TypedQuery query = entityManager.createNativeQuery( + "SELECT * FROM users WHERE name = '" + userProvidedFilter + "'", + User.class +); +// Позволува SQL injection +}}} + +✅ БЕЗБЕДНО: + +{{{ +// ПРАВИЛНО +TypedQuery query = entityManager.createNativeQuery( + "SELECT * FROM users WHERE name = ?1", + User.class +); +query.setParameter(1, userProvidedFilter); +// Параметарот se врзува безбедно +}}} + +=== 2.3 Динамички конструирање на JPQL пропити преку string concatenation === + +❌ НЕБЕЗБЕДНО: + +{{{ +// ОПАСНО +String sortBy = request.getSortBy(); // "name' OR 'x'='x" +String jpql = "SELECT u FROM User u ORDER BY u." + sortBy; +TypedQuery query = entityManager.createQuery(jpql, User.class); +// Позволува SQL injection преку ORDER BY clause +}}} + +✅ БЕЗБЕДНО: + +{{{ +// ПРАВИЛНО: користи Specification или CriteriaBuilder за динамички пропити +CriteriaBuilder cb = entityManager.getCriteriaBuilder(); +CriteriaQuery cq = cb.createQuery(User.class); +Root root = cq.from(User.class); + +// Динамички sort параметар е безбедно обработен +Order order = Sort.Direction.ASC.equals(direction) + ? cb.asc(root.get(sortField)) + : cb.desc(root.get(sortField)); + +cq.orderBy(order); +TypedQuery query = entityManager.createQuery(cq); +}}} + +== 3. Best Practices за имплементација == + +=== 3.1 Правило: Никогаш не користи user input директно во SQL === + +* Сите user inputs мора да бидат параметри +* Користи Spring Data JPA методи deriva или @Query со @Param +* За комплексни пропити, користи CriteriaBuilder или Specification + +=== 3.2 Правило: Типизирај параметри секогаш === + +❌ ЛОШО: +{{{ +Long userId = Long.parseLong(request.getUserId()); // String -> Long конверзија +String query = "SELECT * FROM users WHERE id = " + userId; // Неопходно SQL injection ризик +}}} + +✅ ДОБРО: +{{{ +Long userId = Long.parseLong(request.getUserId()); // String -> Long конверзија +userRepository.findById(userId); // Параметар е Long, не String +// Типизирани параметри не можат да содржат SQL код +}}} + +=== 3.3 Правило: Валидирай влез пред користење === + +* Должина на strings +* Формат на emails (regex или validator) +* Диапаз��н на числа +* Null/empty проверки + +{{{ +// FinanceService.java - пример +public void startOrUpdateTracking(Long userId, FinanceStartRequest request) { + BigDecimal spending = normalizePercent(request.getSpendingBudget()); + // ... остали валидации ... + + // Валидирај сума = 100 + validateSumTo100(spending, saving, investing, donation, credit); + + // Сека валидација е успешна пред зачување во DB + financeUserRepository.save(financeUser); +} +}}} + +=== 3.4 Правило: Користи ОРМ библиотеки (JPA/Hibernate) === + +* ОРМ автоматски параметризира пропити +* Избегнувај raw JDBC комнди без параметри +* За специјални случаи, користи JPA Criteria API + +=== 3.5 Правило: Логирај и мониторирај пропокусни пропити === + +{{{ +// application.properties +spring.jpa.show-sql=true +logging.level.org.hibernate.SQL=DEBUG +logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE + +// TRACE анализира пропити и видија каков параметри се врзуваат +}}} + +== 4. Минимални правила за нови допринесувачи == + +При собачување нов feature, следи: + +1. ✅ Користи Spring Data JPA методи (findBy*, save*, etc.) +2. ✅ За custom пропити, користи @Query("#jpql") со @Param параметри +3. ✅ Никогаш не користи string concatenation со SQL +4. ✅ Валидирај user inputs пред DB операции +5. ✅ Провери ownership/authorization пред delete/update операции +6. ✅ Типизирај сите параметри (Long, BigDecimal, LocalDate, не String) + +== 5. Пример: Правилна имплементација новог endpoint-а == + +Сценарио: Нов endpoint за ажурирање на трошок по категорија. + +{{{ +// FinanceController.java +@PutMapping("/allocations/{categoryId}") +public ResponseEntity updateAllocation( + @PathVariable Long categoryId, + @Valid @RequestBody UpdateAllocationRequest request) { + + AllocationDto result = financeService.updateAllocation(userId, categoryId, request); + return ResponseEntity.ok(result); +} + +// FinanceService.java +@Transactional +public AllocationDto updateAllocation(Long userId, Long categoryId, UpdateAllocationRequest request) { + + // 1. Валидирај input + BigDecimal amount = request.getAmount(); + if (amount == null || amount.compareTo(BigDecimal.ZERO) <= 0) { + throw new RuntimeException("Amount must be greater than 0"); + } + + // 2. Преземи објект од DB (JPA - безбедно) + Allocation allocation = allocationRepository.findById(categoryId) + .orElseThrow(() -> new RuntimeException("Allocation not found")); + + // 3. Провери ownership + if (!userId.equals(allocation.getUser().getUserId())) { + throw new RuntimeException("Allocation not found"); + } + + // 4. Ажурирај и зачувај (JPA - безбедно) + allocation.setAmount(amount); + Allocation saved = allocationRepository.save(allocation); + + // 5. Врати DTO + return new AllocationDto(saved.getId(), saved.getAmount()); +} +}}} + +== 6. Заднила и прилична функција == + +Оваа заштита е имплементирана во: + +* UserRepository.java - findByEmail/Username +* TaskRepository.java - findByDisciplineUser_UserId, resetFinishedForUser (@Query) +* TrainingSessionRepository.java - findByTrainingUser_UserIdAndDateIn +* CustomTrackingService.java - ownership checking + validation +* FinanceService.java - input validation + JPA save +* DisciplineService.java - ownership checks + validation + +== 7. Потребни алатки за QA == + +При тестирање на нови endpoints, следи: + +1. SQL logging: вклучи DEBUG на Hibernate SQL во тест environment +2. Penetration testing: алати како OWASP ZAP или SQLmap (за белите хакери) +3. Code review: проверка дека нема string concatenation со SQL +4. Static analysis: SonarQube или Checkmarx може да детектери потенцијални SQL injection ризици + +== Закончување == + +Trekr backend е заштитен од SQL Injection напади преку: +* Spring Data JPA параметризирани пропити +* Типизирани параметри +* Приоритетна валидација +* Ownership & authorization checks +* Никогаш нема string concatenation со SQL + +За сите нови features, следи ги best practices навултени во овој документ. + Index: backend/src/main/java/com/trekr/backend/repository/TrainingSessionRepository.java =================================================================== --- backend/src/main/java/com/trekr/backend/repository/TrainingSessionRepository.java (revision a8381b18babc398c094b70c3ca5dc6cb0483c327) +++ backend/src/main/java/com/trekr/backend/repository/TrainingSessionRepository.java (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -7,7 +7,13 @@ import org.springframework.stereotype.Repository; +import java.time.LocalDate; +import java.util.Collection; + @Repository public interface TrainingSessionRepository extends JpaRepository { Page findByTrainingUser_UserIdOrderByDateDesc(Long userId, Pageable pageable); java.util.List findByTrainingUser_UserIdAndDate(Long userId, java.time.LocalDate date); + + @SuppressWarnings("unused") + java.util.List findByTrainingUser_UserIdAndDateIn(Long userId, Collection dates); } Index: backend/src/main/java/com/trekr/backend/service/AuthService.java =================================================================== --- backend/src/main/java/com/trekr/backend/service/AuthService.java (revision a8381b18babc398c094b70c3ca5dc6cb0483c327) +++ backend/src/main/java/com/trekr/backend/service/AuthService.java (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -43,4 +43,5 @@ } + @Transactional(readOnly = true) public AuthResponse login(LoginRequest request) { String usernameOrEmail = request.getUsernameOrEmail().trim(); Index: backend/src/main/java/com/trekr/backend/service/CustomTrackingService.java =================================================================== --- backend/src/main/java/com/trekr/backend/service/CustomTrackingService.java (revision a8381b18babc398c094b70c3ca5dc6cb0483c327) +++ backend/src/main/java/com/trekr/backend/service/CustomTrackingService.java (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -36,4 +36,5 @@ } + @Transactional(readOnly = true) public CustomTrackingCategoriesResponse getCustomTrackingCategories(Long userId) { List items = customTrackingCategoryRepository @@ -72,4 +73,5 @@ } + @Transactional(readOnly = true) public TasksResponse getCustomCategoryTasks(Long userId, Long customTrackingId) { requireOwnership(userId, customTrackingId); Index: backend/src/main/java/com/trekr/backend/service/DisciplineService.java =================================================================== --- backend/src/main/java/com/trekr/backend/service/DisciplineService.java (revision a8381b18babc398c094b70c3ca5dc6cb0483c327) +++ backend/src/main/java/com/trekr/backend/service/DisciplineService.java (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -71,4 +71,5 @@ } + @Transactional(readOnly = true) public TasksResponse getTasks(Long userId, int page, int size) { if (!disciplineUserRepository.existsById(userId)) { @@ -162,5 +163,4 @@ * Computes the daily completion percentage for the given date: * finished_tasks / total_tasks * 100 - * * - Persisted once per user+date. If already computed, it is not re-computed. * - After computing, all tasks for that user are reset to unfinished. @@ -210,12 +210,15 @@ // (not strictly required for now, but matches schema and enables future analytics) if (finished > 0) { - List finishedTasks = taskRepository.findByDisciplineUser_UserIdAndFinishedTrue(userId); - for (Task t : finishedTasks) { - TaskDailyCompletion link = new TaskDailyCompletion(); - link.setTask(t); - link.setDailyCompletion(savedCompletion); - link.setId(new TaskDailyCompletionId(t.getTaskId(), savedCompletion.getDailyCompletionId())); - taskDailyCompletionRepository.save(link); - } + List links = taskRepository.findByDisciplineUser_UserIdAndFinishedTrue(userId) + .stream() + .map(t -> { + TaskDailyCompletion link = new TaskDailyCompletion(); + link.setTask(t); + link.setDailyCompletion(savedCompletion); + link.setId(new TaskDailyCompletionId(t.getTaskId(), savedCompletion.getDailyCompletionId())); + return link; + }) + .toList(); + taskDailyCompletionRepository.saveAll(links); } @@ -228,4 +231,5 @@ } + @Transactional(readOnly = true) public DailyCompletionsResponse getDailyCompletions(Long userId, int page, int size) { if (!disciplineUserRepository.existsById(userId)) { Index: backend/src/main/java/com/trekr/backend/service/FinanceService.java =================================================================== --- backend/src/main/java/com/trekr/backend/service/FinanceService.java (revision a8381b18babc398c094b70c3ca5dc6cb0483c327) +++ backend/src/main/java/com/trekr/backend/service/FinanceService.java (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -69,4 +69,5 @@ } + @Transactional(readOnly = true) public FinanceProfileResponse getProfile(Long userId) { FinanceUser financeUser = financeUserRepository.findById(userId) @@ -81,4 +82,5 @@ } + @Transactional(readOnly = true) public IncomesResponse getIncomes(Long userId, int page, int size) { if (!financeUserRepository.existsById(userId)) { Index: backend/src/main/java/com/trekr/backend/service/InvestingService.java =================================================================== --- backend/src/main/java/com/trekr/backend/service/InvestingService.java (revision a8381b18babc398c094b70c3ca5dc6cb0483c327) +++ backend/src/main/java/com/trekr/backend/service/InvestingService.java (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -53,4 +53,5 @@ } + @Transactional(readOnly = true) public InvestingAssetsResponse getAssets(Long userId, int page, int size) { if (!investorUserRepository.existsById(userId)) { Index: backend/src/main/java/com/trekr/backend/service/TrainingService.java =================================================================== --- backend/src/main/java/com/trekr/backend/service/TrainingService.java (revision a8381b18babc398c094b70c3ca5dc6cb0483c327) +++ backend/src/main/java/com/trekr/backend/service/TrainingService.java (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -21,4 +21,5 @@ import java.math.RoundingMode; import java.time.LocalDate; +import java.util.Arrays; import java.util.List; import java.util.Locale; @@ -108,4 +109,5 @@ } + @Transactional(readOnly = true) public TrainingSessionsResponse getSessions(Long userId, int page, int size) { if (!trainingUserRepository.existsById(userId)) { @@ -134,4 +136,5 @@ } + @Transactional(readOnly = true) public TrainingProfileResponse getProfile(Long userId) { TrainingUser trainingUser = trainingUserRepository.findById(userId) @@ -202,7 +205,6 @@ String normalized = type.replace('_', '-'); - return List.of(normalized.split("-")) - .stream() - .filter(part -> part != null && !part.isBlank()) + return Arrays.stream(normalized.split("-")) + .filter(part -> !part.isBlank()) .map(part -> Character.toUpperCase(part.charAt(0)) + part.substring(1)) .collect(Collectors.joining(" ")); Index: backend/src/main/java/com/trekr/backend/service/WeightService.java =================================================================== --- backend/src/main/java/com/trekr/backend/service/WeightService.java (revision a8381b18babc398c094b70c3ca5dc6cb0483c327) +++ backend/src/main/java/com/trekr/backend/service/WeightService.java (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -21,5 +21,7 @@ import java.math.BigDecimal; import java.time.LocalDate; +import java.util.HashMap; import java.util.List; +import java.util.Map; @Service @@ -73,4 +75,5 @@ } + @Transactional(readOnly = true) public WeightProfileResponse getProfile(Long userId) { WeightUser weightUser = weightUserRepository.findById(userId) @@ -84,4 +87,5 @@ } + @Transactional(readOnly = true) public WeightDailyIntakesResponse getDailyIntakes(Long userId, int page, int size) { if (!weightUserRepository.existsById(userId)) { @@ -92,14 +96,26 @@ .findByWeightUser_UserIdOrderByDateDesc(userId, PageRequest.of(page, size)); + List dates = result.getContent().stream() + .map(DailyIntake::getDate) + .distinct() + .toList(); + + Map trainingCountsByDate = new HashMap<>(); + Map burnedCaloriesByDate = new HashMap<>(); + if (!dates.isEmpty()) { + trainingSessionRepository.findByTrainingUser_UserIdAndDateIn(userId, dates).forEach(session -> { + LocalDate sessionDate = session.getDate(); + trainingCountsByDate.merge(sessionDate, 1, Integer::sum); + BigDecimal calories = session.getCalories() != null ? session.getCalories() : BigDecimal.ZERO; + burnedCaloriesByDate.merge(sessionDate, calories, BigDecimal::add); + }); + } + List intakes = result.getContent().stream() .map(d -> { - // Fetch training data for this date - var trainingSessions = trainingSessionRepository.findByTrainingUser_UserIdAndDate(userId, d.getDate()); - boolean trainedThatDay = !trainingSessions.isEmpty(); - BigDecimal burnedCalories = trainingSessions.stream() - .map(ts -> ts.getCalories() != null ? ts.getCalories() : BigDecimal.ZERO) - .reduce(BigDecimal.ZERO, BigDecimal::add) + boolean trainedThatDay = trainingCountsByDate.getOrDefault(d.getDate(), 0) > 0; + BigDecimal burnedCalories = burnedCaloriesByDate.getOrDefault(d.getDate(), BigDecimal.ZERO) .setScale(2, java.math.RoundingMode.HALF_UP); - + return new WeightDailyIntakeDto( d.getDailyIntakeId(), @@ -143,5 +159,4 @@ } - @Transactional public WeightProfileResponse updateProfile(Long userId, WeightStartRequest request) { WeightUser weightUser = weightUserRepository.findById(userId) @@ -163,4 +178,5 @@ } + @Transactional(readOnly = true) public TodayTrainingInfoDto getTodayTrainingInfo(Long userId) { // Try to get training user, if doesn't exist just return zeros Index: backend/src/main/resources/application.properties =================================================================== --- backend/src/main/resources/application.properties (revision a8381b18babc398c094b70c3ca5dc6cb0483c327) +++ backend/src/main/resources/application.properties (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -43,7 +43,8 @@ spring.datasource.hikari.pool-name=trekrHikariPool # Remote PostgreSQL (especially via SSH tunnel) often has a small max_connections. -# Use conservative defaults to avoid exhausting connection slots; override via env if needed. -spring.datasource.hikari.maximum-pool-size=${SPRING_DATASOURCE_HIKARI_MAX_POOL_SIZE:4} -spring.datasource.hikari.minimum-idle=${SPRING_DATASOURCE_HIKARI_MIN_IDLE:0} +# Use a slightly larger default so the app can serve more concurrent users without +# overloading the database; override via env if your provider has tighter limits. +spring.datasource.hikari.maximum-pool-size=${SPRING_DATASOURCE_HIKARI_MAX_POOL_SIZE:20} +spring.datasource.hikari.minimum-idle=${SPRING_DATASOURCE_HIKARI_MIN_IDLE:5} spring.datasource.hikari.connection-timeout=30000 spring.datasource.hikari.idle-timeout=600000 Index: db-scripts/triggers/compute_daily_completion.sql =================================================================== --- db-scripts/triggers/compute_daily_completion.sql (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) +++ db-scripts/triggers/compute_daily_completion.sql (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -0,0 +1,84 @@ +-- Compute daily completion for a single user and date entirely in the DB +-- Inserts a daily_completions row (if not exists), links finished tasks into task_daily_completions, +-- and resets tasks.finished = false for that user. +-- Usage: SELECT trekr.fn_compute_daily_completion(user_id := 123, day := DATE '2026-05-06'); + +CREATE OR REPLACE FUNCTION trekr.fn_compute_daily_completion(user_id bigint, day date) +RETURNS TABLE(created boolean, daily_completion_id bigint, procent numeric) +LANGUAGE plpgsql +AS $$ +DECLARE + total_count bigint; + finished_count bigint; + pct numeric; + dc_id bigint; + user_rec record; + finished_tasks RECORD; +BEGIN + IF user_id IS NULL THEN + RAISE EXCEPTION 'user_id is required'; + END IF; + IF day IS NULL THEN + RAISE EXCEPTION 'day is required'; + END IF; + IF day > current_date THEN + RAISE EXCEPTION 'date cannot be in the future'; + END IF; + + -- ensure user tracking exists: rely on discipline_users table existence + IF NOT EXISTS (SELECT 1 FROM trekr.discipline_users du WHERE du.user_id = user_id) THEN + RAISE EXCEPTION 'Discipline tracking is not enabled for this user'; + END IF; + + -- if already computed for that user+date, return existing + SELECT dc.daily_completion_id, dc.procent INTO dc_id, pct + FROM trekr.daily_completions dc + WHERE dc.user_id = user_id AND dc.date = day + LIMIT 1; + + IF dc_id IS NOT NULL THEN + RETURN QUERY SELECT false, dc_id, pct; + RETURN; + END IF; + + SELECT COUNT(*) INTO total_count FROM trekr.tasks t WHERE t.discipline_user_id = ( + SELECT du.discipline_user_id FROM trekr.discipline_users du WHERE du.user_id = user_id + ); + + SELECT COUNT(*) INTO finished_count FROM trekr.tasks t WHERE t.discipline_user_id = ( + SELECT du.discipline_user_id FROM trekr.discipline_users du WHERE du.user_id = user_id + ) AND t.finished = true; + + IF total_count <= 0 THEN + pct := 0; + ELSE + pct := round((finished_count::numeric * 100) / total_count::numeric, 2); + END IF; + + -- insert daily completion + INSERT INTO trekr.daily_completions (user_id, date, procent) + VALUES (user_id, day, pct) + RETURNING daily_completion_id INTO dc_id; + + -- link finished tasks + FOR finished_tasks IN + SELECT t.task_id FROM trekr.tasks t + WHERE t.discipline_user_id = ( + SELECT du.discipline_user_id FROM trekr.discipline_users du WHERE du.user_id = user_id + ) AND t.finished = true + LOOP + INSERT INTO trekr.task_daily_completions (task_id, daily_completion_id) + VALUES (finished_tasks.task_id, dc_id) + ON CONFLICT DO NOTHING; + END LOOP; + + -- reset all tasks for user + UPDATE trekr.tasks t SET finished = false + WHERE t.discipline_user_id = ( + SELECT du.discipline_user_id FROM trekr.discipline_users du WHERE du.user_id = user_id + ); + + RETURN QUERY SELECT true, dc_id, pct; +END; +$$; + Index: db-scripts/triggers/compute_daily_completion_all.sql =================================================================== --- db-scripts/triggers/compute_daily_completion_all.sql (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) +++ db-scripts/triggers/compute_daily_completion_all.sql (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -0,0 +1,31 @@ +-- Compute daily completion for all discipline users for a given date +-- Usage: SELECT trekr.fn_compute_daily_completion_for_all(day := DATE '2026-05-06'); + +CREATE OR REPLACE FUNCTION trekr.fn_compute_daily_completion_for_all(day date) +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + u RECORD; +BEGIN + IF day IS NULL THEN + RAISE EXCEPTION 'day is required'; + END IF; + + FOR u IN SELECT user_id FROM trekr.discipline_users LOOP + BEGIN + PERFORM trekr.fn_compute_daily_completion(u.user_id, day); + EXCEPTION WHEN OTHERS THEN + -- log and continue (requires a logging table or use RAISE NOTICE) + RAISE NOTICE 'compute_daily_completion failed for user %: %', u.user_id, SQLERRM; + END; + END LOOP; +END; +$$; + +-- Optionally create a pg_cron job to run nightly (requires pg_cron extension) +-- Example (commented): +-- CREATE EXTENSION IF NOT EXISTS pg_cron; +-- SELECT cron.schedule('compute_daily_completions_every_day', '59 23 * * *', +-- $$SELECT trekr.fn_compute_daily_completion_for_all(current_date - INTERVAL '1 day')$$); + Index: db-scripts/triggers/finance_validate_trigger.sql =================================================================== --- db-scripts/triggers/finance_validate_trigger.sql (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) +++ db-scripts/triggers/finance_validate_trigger.sql (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -0,0 +1,48 @@ +-- Validate finance percentages on insert/update +-- Ensures the five budget fields sum to ~100 (tolerance 0.01) and each is between 0 and 100 +-- Schema: trekr + +CREATE OR REPLACE FUNCTION trekr.fn_validate_finance_percentages() +RETURNS trigger +LANGUAGE plpgsql +AS $$ +DECLARE + s NUMERIC; + eps CONSTANT NUMERIC := 0.01; -- allow small rounding differences + vals NUMERIC[] := ARRAY[NEW.spending_budget, NEW.saving_budget, NEW.investing_budget, NEW.donation_budget, NEW.credit]; + v NUMERIC; +BEGIN + -- Null checks: all five values must be provided (business rule) + FOREACH v IN ARRAY vals LOOP + IF v IS NULL THEN + RAISE EXCEPTION 'All 5 finance percentage values are required'; + END IF; + IF v < 0 OR v > 100 THEN + RAISE EXCEPTION 'Finance percentage values must be between 0 and 100'; + END IF; + END LOOP; + + s := (NEW.spending_budget + NEW.saving_budget + NEW.investing_budget + NEW.donation_budget + NEW.credit)::numeric; + IF abs(s - 100) > eps THEN + RAISE EXCEPTION 'Finance percentages must sum to 100 (got: %)', s; + END IF; + + RETURN NEW; +END; +$$; + +-- Trigger on finance_users table +DO $$ +BEGIN + IF NOT EXISTS ( + SELECT 1 FROM pg_trigger t + JOIN pg_class c ON t.tgrelid = c.oid + WHERE t.tgname = 'trg_validate_finance_percentages' AND c.relname = 'finance_users' + ) THEN + CREATE TRIGGER trg_validate_finance_percentages + BEFORE INSERT OR UPDATE ON trekr.finance_users + FOR EACH ROW + EXECUTE FUNCTION trekr.fn_validate_finance_percentages(); + END IF; +END$$; + Index: db-scripts/triggers/uniqueness_and_training_checks.sql =================================================================== --- db-scripts/triggers/uniqueness_and_training_checks.sql (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) +++ db-scripts/triggers/uniqueness_and_training_checks.sql (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -0,0 +1,36 @@ +-- Add useful DB constraints and checks + +-- Unique index: ensure a user has at most one daily intake per date +DO $$ +BEGIN + IF NOT EXISTS ( + SELECT 1 FROM pg_indexes WHERE schemaname = 'trekr' AND tablename = 'daily_intakes' AND indexname = 'uq_daily_intake_user_date' + ) THEN + CREATE UNIQUE INDEX uq_daily_intake_user_date ON trekr.daily_intakes (weight_user_id, date); + END IF; +END$$; + +-- Prevent inserting training sessions with future date +CREATE OR REPLACE FUNCTION trekr.fn_check_training_date() +RETURNS trigger LANGUAGE plpgsql AS $$ +BEGIN + IF NEW.date > current_date THEN + RAISE EXCEPTION 'Training session date cannot be in the future: %', NEW.date; + END IF; + RETURN NEW; +END$$; + +DO $$ +BEGIN + IF NOT EXISTS ( + SELECT 1 FROM pg_trigger t + JOIN pg_class c ON t.tgrelid = c.oid + WHERE t.tgname = 'trg_check_training_date' AND c.relname = 'training_sessions' + ) THEN + CREATE TRIGGER trg_check_training_date + BEFORE INSERT OR UPDATE ON trekr.training_sessions + FOR EACH ROW + EXECUTE FUNCTION trekr.fn_check_training_date(); + END IF; +END$$; + Index: db-scripts/views/finance_allocations_view.sql =================================================================== --- db-scripts/views/finance_allocations_view.sql (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) +++ db-scripts/views/finance_allocations_view.sql (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -0,0 +1,18 @@ +-- View: calculate allocation amounts per finance category for the current month +CREATE OR REPLACE VIEW trekr.vw_finance_allocations_current_month AS +SELECT + f.user_id, + fm.total_earned_this_month, + f.spending_budget, + f.saving_budget, + f.investing_budget, + f.donation_budget, + f.credit, + ROUND((COALESCE(f.spending_budget,0) / 100.0) * fm.total_earned_this_month, 2) AS spending_amount, + ROUND((COALESCE(f.saving_budget,0) / 100.0) * fm.total_earned_this_month, 2) AS saving_amount, + ROUND((COALESCE(f.investing_budget,0) / 100.0) * fm.total_earned_this_month, 2) AS investing_amount, + ROUND((COALESCE(f.donation_budget,0) / 100.0) * fm.total_earned_this_month, 2) AS donation_amount, + ROUND((COALESCE(f.credit,0) / 100.0) * fm.total_earned_this_month, 2) AS credit_amount +FROM trekr.finance_users f +LEFT JOIN trekr.vw_finance_current_month fm ON fm.user_id = f.user_id; + Index: db-scripts/views/finance_monthly_summary_view.sql =================================================================== --- db-scripts/views/finance_monthly_summary_view.sql (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) +++ db-scripts/views/finance_monthly_summary_view.sql (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -0,0 +1,22 @@ +-- View: monthly income summary per user +-- Shows total income per user per month and year +CREATE OR REPLACE VIEW trekr.vw_finance_monthly_summary AS +SELECT + i.finance_user_id AS user_id, + EXTRACT(YEAR FROM i.date)::int AS year, + EXTRACT(MONTH FROM i.date)::int AS month, + SUM(i.amount) AS total_income +FROM trekr.incomes i +GROUP BY i.finance_user_id, EXTRACT(YEAR FROM i.date), EXTRACT(MONTH FROM i.date); + +-- View: current month total money earned for each user +CREATE OR REPLACE VIEW trekr.vw_finance_current_month AS +SELECT + f.user_id, + COALESCE(SUM(i.amount), 0) AS total_earned_this_month +FROM trekr.finance_users f +LEFT JOIN trekr.incomes i + ON i.finance_user_id = f.user_id + AND date_trunc('month', i.date) = date_trunc('month', current_date) +GROUP BY f.user_id; + Index: frontend/src/App.jsx =================================================================== --- frontend/src/App.jsx (revision a8381b18babc398c094b70c3ca5dc6cb0483c327) +++ frontend/src/App.jsx (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -1,3 +1,4 @@ import { Navigate, Route, Routes } from "react-router-dom"; +import { clearAuthSession, getAuthToken, isTokenExpired } from "./utils/authSession"; import LandingPage from "./pages/LandingPage/LandingPage.jsx"; @@ -23,5 +24,9 @@ function RequireAuth({ children }) { - const token = localStorage.getItem("authToken"); + const token = getAuthToken(); + if (token && isTokenExpired(token)) { + clearAuthSession(); + return ; + } if (!token) return ; return children; Index: frontend/src/api/axios.js =================================================================== --- frontend/src/api/axios.js (revision a8381b18babc398c094b70c3ca5dc6cb0483c327) +++ frontend/src/api/axios.js (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -1,3 +1,4 @@ import axios from "axios"; +import { getAuthToken, isTokenExpired, logoutAndRedirect } from "../utils/authSession"; const api = axios.create({ @@ -6,6 +7,10 @@ api.interceptors.request.use((config) => { - const token = localStorage.getItem("authToken"); + const token = getAuthToken(); if (token) { + if (isTokenExpired(token)) { + logoutAndRedirect("/dashboard"); + return Promise.reject(new Error("Authentication session expired")); + } config.headers = config.headers ?? {}; config.headers.Authorization = `Bearer ${token}`; @@ -14,3 +19,14 @@ }); +api.interceptors.response.use( + (response) => response, + (error) => { + const status = error?.response?.status; + if (status === 401 || status === 403) { + logoutAndRedirect("/dashboard"); + } + return Promise.reject(error); + }, +); + export default api; Index: frontend/src/pages/Dashboard/DashboardLayout.jsx =================================================================== --- frontend/src/pages/Dashboard/DashboardLayout.jsx (revision a8381b18babc398c094b70c3ca5dc6cb0483c327) +++ frontend/src/pages/Dashboard/DashboardLayout.jsx (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -6,4 +6,5 @@ import DashboardSidebar from "./components/DashboardSidebar.jsx"; import DashboardTopbar from "./components/DashboardTopbar.jsx"; +import { clearAuthSession } from "../../utils/authSession"; const DashboardLayout = () => { @@ -22,6 +23,5 @@ const onLogout = () => { - localStorage.removeItem("authToken"); - localStorage.removeItem("authUser"); + clearAuthSession(); navigate("/", { replace: true }); }; Index: frontend/src/pages/Dashboard/pages/Investing/InvestingTracking.jsx =================================================================== --- frontend/src/pages/Dashboard/pages/Investing/InvestingTracking.jsx (revision a8381b18babc398c094b70c3ca5dc6cb0483c327) +++ frontend/src/pages/Dashboard/pages/Investing/InvestingTracking.jsx (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -214,5 +214,5 @@ onAddAsset={() => navigate("/dashboard/investing/assets/new")} /> - + {quotesError ? ( Index: frontend/src/pages/Dashboard/pages/Investing/components/InvestingProgressCard.jsx =================================================================== --- frontend/src/pages/Dashboard/pages/Investing/components/InvestingProgressCard.jsx (revision a8381b18babc398c094b70c3ca5dc6cb0483c327) +++ frontend/src/pages/Dashboard/pages/Investing/components/InvestingProgressCard.jsx (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -3,25 +3,139 @@ import TimeRangeToggle from "../../../../../components/graphs/TimeRangeToggle.jsx"; import PercentChangeAreaChart from "../../../../../components/graphs/PercentChangeAreaChart.jsx"; -import { percentChangeSeries, sumByTimeBucket } from "../../../../../utils/timeSeries.js"; - -export default function InvestingProgressCard({ assets }) { +import { formatBucketLabel } from "../../../../../utils/timeSeries.js"; + +function bucketKey(date, granularity) { + const d = new Date(date); + if (Number.isNaN(d.getTime())) return null; + + function startOfDay(d) { + return new Date(d.getFullYear(), d.getMonth(), d.getDate()); + } + + function startOfISOWeek(d) { + const date = startOfDay(d); + const day = date.getDay(); + const diff = (day === 0 ? -6 : 1) - day; + date.setDate(date.getDate() + diff); + return startOfDay(date); + } + + function startOfMonth(d) { + return new Date(d.getFullYear(), d.getMonth(), 1); + } + + function startOfYear(d) { + return new Date(d.getFullYear(), 0, 1); + } + + let s; + switch (granularity) { + case "daily": + s = startOfDay(d); + break; + case "weekly": + s = startOfISOWeek(d); + break; + case "monthly": + s = startOfMonth(d); + break; + case "yearly": + s = startOfYear(d); + break; + default: + s = startOfDay(d); + } + return s.getTime(); +} + +export default function InvestingProgressCard({ assets, quotesBySymbol = {} }) { const [range, setRange] = useState("weekly"); const points = useMemo(() => { - // Option B: "net invested momentum". - // Since we only have assets (with buy date/price/quantity) and no transaction history, - // we treat each asset as a one-time investment at buyDate. - const buckets = sumByTimeBucket( - assets, - range, - (a) => a.buyDate, - (a) => (Number(a?.quantity) || 0) * (Number(a?.buyPrice) || 0), - ); - return percentChangeSeries(buckets); - }, [assets, range]); + // Calculate portfolio percentage gain/loss over time using current prices + // Display cumulative portfolio value and percentage change between time buckets + + if (!assets || assets.length === 0) { + return []; + } + + // Group assets by buy date bucket + const buckets = new Map(); + for (const asset of assets) { + if (asset?.buyDate) { + const ts = bucketKey(asset.buyDate, range); + if (ts !== null) { + if (!buckets.has(ts)) { + buckets.set(ts, []); + } + buckets.get(ts).push(asset); + } + } + } + + // Sort buckets chronologically + const sortedBuckets = Array.from(buckets.entries()).sort((a, b) => a[0] - b[0]); + + // Calculate cumulative portfolio value at each bucket + const points = []; + let cumulativeAssets = []; + + for (const [ts, assetsInBucket] of sortedBuckets) { + cumulativeAssets = [...cumulativeAssets, ...assetsInBucket]; + + let totalInvested = 0; + let totalCurrentValue = 0; + + for (const asset of cumulativeAssets) { + const qty = Number(asset?.quantity) || 0; + const buyPrice = Number(asset?.buyPrice) || 0; + const ticker = String(asset?.tickerSymbol || "").toUpperCase(); + const currentPrice = quotesBySymbol[ticker] || buyPrice; + + totalInvested += qty * buyPrice; + totalCurrentValue += qty * currentPrice; + } + + if (totalInvested > 0) { + points.push({ + ts, + invested: totalInvested, + current: totalCurrentValue, + }); + } + } + + // Calculate percentage gain from invested to current value + const result = []; + for (const p of points) { + const percentageGain = ((p.current - p.invested) / p.invested) * 100; + result.push({ + ts: p.ts, + value: percentageGain, + base: p.current, + invested: p.invested, + }); + } + + return result; + }, [assets, range, quotesBySymbol]); const latest = points?.length ? points[points.length - 1] : null; const latestPct = latest ? Number(latest.value ?? 0) : 0; - const latestBase = latest ? Number(latest.base ?? 0) : 0; + const latestCurrentValue = latest ? Number(latest.base ?? 0) : 0; + const latestInvested = latest ? Number(latest.invested ?? 0) : 0; + + const totalCurrentPortfolioValue = assets.reduce((sum, asset) => { + const qty = Number(asset?.quantity) || 0; + const ticker = String(asset?.tickerSymbol || "").toUpperCase(); + const currentPrice = quotesBySymbol[ticker] || Number(asset?.buyPrice) || 0; + return sum + qty * currentPrice; + }, 0); + + const totalInvestedAmount = assets.reduce((sum, asset) => { + const qty = Number(asset?.quantity) || 0; + const buyPrice = Number(asset?.buyPrice) || 0; + return sum + qty * buyPrice; + }, 0); return ( @@ -29,10 +143,19 @@
-

Progress

+

Portfolio Value

- - {latestPct >= 0 ? "+" : ""} - {latestPct.toFixed(1)}% (last) - +
+ + ${totalCurrentPortfolioValue.toFixed(2)} + + = 0 ? "text-success" : "text-error" + }`} + > + {latestPct >= 0 ? "+" : ""} + {latestPct.toFixed(2)}% + +
@@ -40,7 +163,7 @@
- {points.length < 2 ? ( + {points.length < 1 ? (
- Add at least 2 investments to see % change. + Add investments to see portfolio growth.
) : ( @@ -49,11 +172,30 @@ granularity={range} height={260} - positiveColor="#14b8a6" + positiveColor="#10b981" /> )}
-
- Showing percentage change in invested amount per {range} bucket. Latest bucket invested: {Math.round(latestBase)}. +
+
+
Invested
+
${totalInvestedAmount.toFixed(2)}
+
+
+
Current Value
+
${totalCurrentPortfolioValue.toFixed(2)}
+
+
+
Gain/Loss
+
= totalInvestedAmount + ? "text-success" + : "text-error" + }`} + > + ${(totalCurrentPortfolioValue - totalInvestedAmount).toFixed(2)} +
+
Index: frontend/src/pages/LandingPage/components/Benefits.jsx =================================================================== --- frontend/src/pages/LandingPage/components/Benefits.jsx (revision a8381b18babc398c094b70c3ca5dc6cb0483c327) +++ frontend/src/pages/LandingPage/components/Benefits.jsx (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -43,10 +43,10 @@ return (
-
+

Why Trekr

-

+

Designed for serious progress

Index: frontend/src/pages/LandingPage/components/Faq.defaults.js =================================================================== --- frontend/src/pages/LandingPage/components/Faq.defaults.js (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) +++ frontend/src/pages/LandingPage/components/Faq.defaults.js (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -0,0 +1,40 @@ +export const FaqDefaults = { + heading: "Frequently asked questions", + description: + "Quick answers on how Trekr helps you track self-improvement across multiple areas — in one place.", + questions: [ + { + title: "What can I track in Trekr?", + answer: + "Workouts (type and duration), weight & nutrition (goal, calories), finances & budgeting (income and expenses), investing (portfolio and returns), and discipline (daily tasks you can check off).", + }, + { + title: "Do I need multiple apps to do all this?", + answer: + "No — Trekr is designed to unify the most important self-improvement categories so you can get a clear overview without switching between tools.", + }, + { + title: "How do goals and progress tracking work?", + answer: + "You set a goal (for example: target weight or monthly budget), then log daily/weekly data. Trekr helps you see where you are vs. the goal and how you're trending over time.", + }, + { + title: "Where is my data stored?", + answer: + "Your entries are stored in a database and linked to your user profile so you can track progress consistently over time.", + }, + { + title: "Is Trekr a mobile app?", + answer: + "Right now Trekr is a web app. The goal is a fast, simple experience on both mobile and desktop in the browser.", + }, + ], + footerHeading: "Still have questions?", + footerDescription: "Send us a message and we’ll help you get started.", + button: { + title: "Contact", + variant: "secondary", + }, +}; + + Index: frontend/src/pages/LandingPage/components/Faq.jsx =================================================================== --- frontend/src/pages/LandingPage/components/Faq.jsx (revision a8381b18babc398c094b70c3ca5dc6cb0483c327) +++ frontend/src/pages/LandingPage/components/Faq.jsx (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -6,4 +6,6 @@ AccordionTrigger, } from "@relume_io/relume-ui"; + +import { FaqDefaults } from "./Faq.defaults"; export const Faq = (props) => { @@ -16,5 +18,5 @@ button, } = { - ...Faq1Defaults, + ...FaqDefaults, ...props, }; @@ -24,10 +26,12 @@ className="px-[5%] py-16 md:py-24 lg:py-28 flex items-center justify-center" > -
+
-

+

{heading}

-

{description}

+

+ {description} +

@@ -47,5 +51,7 @@ {footerHeading} -

{footerDescription}

+

+ {footerDescription} +

@@ -56,41 +62,2 @@ ); }; - -export const Faq1Defaults = { - heading: "Frequently asked questions", - description: - "Quick answers on how Trekr helps you track self-improvement across multiple areas — in one place.", - questions: [ - { - title: "What can I track in Trekr?", - answer: - "Workouts (type and duration), weight & nutrition (goal, calories), finances & budgeting (income and expenses), investing (portfolio and returns), and discipline (daily tasks you can check off).", - }, - { - title: "Do I need multiple apps to do all this?", - answer: - "No — Trekr is designed to unify the most important self-improvement categories so you can get a clear overview without switching between tools.", - }, - { - title: "How do goals and progress tracking work?", - answer: - "You set a goal (for example: target weight or monthly budget), then log daily/weekly data. Trekr helps you see where you are vs. the goal and how you're trending over time.", - }, - { - title: "Where is my data stored?", - answer: - "Your entries are stored in a database and linked to your user profile so you can track progress consistently over time.", - }, - { - title: "Is Trekr a mobile app?", - answer: - "Right now Trekr is a web app. The goal is a fast, simple experience on both mobile and desktop in the browser.", - }, - ], - footerHeading: "Still have questions?", - footerDescription: "Send us a message and we’ll help you get started.", - button: { - title: "Contact", - variant: "secondary", - }, -}; Index: frontend/src/pages/LandingPage/components/Features.jsx =================================================================== --- frontend/src/pages/LandingPage/components/Features.jsx (revision a8381b18babc398c094b70c3ca5dc6cb0483c327) +++ frontend/src/pages/LandingPage/components/Features.jsx (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -46,10 +46,10 @@ className="px-[5%] py-16 md:py-24 lg:py-28 bg-base-200" > -
+

What you can track

-

+

Everything in one dashboard

Index: frontend/src/pages/LandingPage/components/Footer.jsx =================================================================== --- frontend/src/pages/LandingPage/components/Footer.jsx (revision a8381b18babc398c094b70c3ca5dc6cb0483c327) +++ frontend/src/pages/LandingPage/components/Footer.jsx (revision 42da64dc0d28f9380336816f6fb82c7572d0021d) @@ -4,5 +4,5 @@ const Footer = () => { return ( -