Index: backend/docs/Normalization.txt =================================================================== --- backend/docs/Normalization.txt (revision e9a0543bbb622a2b343d1f5fe3b2df7ddb6a0bd3) +++ (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: backend/docs/P6_Advanced_Reports.txt =================================================================== --- backend/docs/P6_Advanced_Reports.txt (revision e9a0543bbb622a2b343d1f5fe3b2df7ddb6a0bd3) +++ (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 e9a0543bbb622a2b343d1f5fe3b2df7ddb6a0bd3) +++ 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 e9a0543bbb622a2b343d1f5fe3b2df7ddb6a0bd3) +++ 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 e9a0543bbb622a2b343d1f5fe3b2df7ddb6a0bd3) +++ 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 e9a0543bbb622a2b343d1f5fe3b2df7ddb6a0bd3) +++ 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 e9a0543bbb622a2b343d1f5fe3b2df7ddb6a0bd3) +++ 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 e9a0543bbb622a2b343d1f5fe3b2df7ddb6a0bd3) +++ 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 e9a0543bbb622a2b343d1f5fe3b2df7ddb6a0bd3) +++ 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 e9a0543bbb622a2b343d1f5fe3b2df7ddb6a0bd3) +++ 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 e9a0543bbb622a2b343d1f5fe3b2df7ddb6a0bd3) +++ 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