= Други Развојни Активности = == Анализа на перформанси == === Извештај за диверзификација на портфолио (report_portfolio_diversification) === Анализата на перформанси се врши врз основа на моменталната состојба во базата. '''SQL:''' {{{ CREATE OR REPLACE FUNCTION test_portfolio_diversification() RETURNS void AS $$ DECLARE start_time timestamptz; end_time timestamptz; duration int; BEGIN start_time := clock_timestamp(); PERFORM ph.portfolio_id, ph.stock_id, s.symbol, ph.quantity::NUMERIC * s.current_price::NUMERIC AS market_value, ph.quantity::NUMERIC * (s.current_price::NUMERIC - ph.avg_price::NUMERIC) AS unrealized_pnl FROM portfolio_holdings ph JOIN stock s ON ph.stock_id = s.id WHERE ph.quantity > 0; end_time := clock_timestamp(); duration := round(1000 * (extract(epoch FROM end_time) - extract(epoch FROM start_time))); RAISE NOTICE 'Query executed in: % ms', duration; END; $$ LANGUAGE plpgsql; DROP INDEX IF EXISTS idx_ph_portfolio_id; DROP INDEX IF EXISTS idx_ph_stock_id; DROP INDEX IF EXISTS idx_ph_quantity_positive; DROP INDEX IF EXISTS idx_users_role; -- run 1: no index SELECT test_portfolio_diversification(); -- run 2: + idx_ph_stock_id CREATE INDEX idx_ph_stock_id ON portfolio_holdings(stock_id); ANALYZE portfolio_holdings; SELECT test_portfolio_diversification(); -- run 3: + idx_ph_portfolio_id CREATE INDEX idx_ph_portfolio_id ON portfolio_holdings(portfolio_id); ANALYZE portfolio_holdings; SELECT test_portfolio_diversification(); -- run 4: + idx_ph_quantity_positive (partial index) CREATE INDEX idx_ph_quantity_positive ON portfolio_holdings(portfolio_id, stock_id) WHERE quantity > 0; ANALYZE portfolio_holdings; SELECT test_portfolio_diversification(); -- run 5: + idx_users_role CREATE INDEX idx_users_role ON users(role); ANALYZE users; SELECT test_portfolio_diversification(); DROP FUNCTION test_portfolio_diversification(); }}} '''Индекси:''' **1. portfolio_holdings - Index (portfolio_id)** {{{ CREATE INDEX idx_ph_portfolio_id ON portfolio_holdings(portfolio_id); }}} Користење: GROUP BY и JOIN по `portfolio_id` во CTE-ата `stock_weights` и `portfolio_totals` Подобрување: Index Scan наместо Seq Scan при групирање по портфолио --- **2. portfolio_holdings - Index (stock_id)** {{{ CREATE INDEX idx_ph_stock_id ON portfolio_holdings(stock_id); }}} Користење: JOIN `ph.stock_id = s.id` во `holding_values` CTE Подобрување: Директен lookup наместо Hash Join со Seq Scan --- **3. portfolio_holdings - Partial Index (quantity > 0)** {{{ CREATE INDEX idx_ph_quantity_positive ON portfolio_holdings(portfolio_id, stock_id) WHERE quantity > 0; }}} Користење: WHERE филтер `ph.quantity > 0` во `holding_values` CTE Подобрување: Индексот ги содржи само редовите со quantity > 0, елиминирајќи ги нулираните холдинзи уште при скенирањето --- **4. users - Index (role)** {{{ CREATE INDEX idx_users_role ON users(role); }}} Користење: WHERE филтер `u.role = 'USER'` во финалниот SELECT Подобрување: Директен Index Scan наместо Seq Scan на целата users табела --- '''Сумарно:''' Без индекси: 201ms Со индекси: 140ms Имаме забрзување. ---- === Извештај за волатилност на акции (report_stock_volatility_and_activity) === '''SQL:''' {{{ CREATE OR REPLACE FUNCTION report_stock_volatility_and_activity( p_from_date TIMESTAMP DEFAULT NULL, p_to_date TIMESTAMP DEFAULT NULL ) RETURNS TABLE ( stock_id INTEGER, symbol TEXT, stock_name TEXT, current_price NUMERIC, period_min_price NUMERIC, period_max_price NUMERIC, price_range_pct NUMERIC, avg_daily_price NUMERIC, historical_volatility_pct NUMERIC, total_history_points BIGINT, total_txn_volume NUMERIC, total_txn_count BIGINT, buy_txn_count BIGINT, sell_txn_count BIGINT, buy_sell_ratio NUMERIC, active_watchlist_users BIGINT, pending_trade_requests BIGINT, trend_direction TEXT, trend_slope NUMERIC, activity_score NUMERIC, volatility_rank BIGINT, activity_rank BIGINT ) AS $$ BEGIN RETURN QUERY WITH -- Volatility: stddev of daily returns computed with LAG window function -- computing daily return per row using LAG window function daily_returns AS ( SELECT sh.stock_id AS dr_stock_id, sh.price AS dr_price, (sh.price - LAG(sh.price) OVER ( PARTITION BY sh.stock_id ORDER BY sh.timestamp )) / NULLIF(LAG(sh.price) OVER ( PARTITION BY sh.stock_id ORDER BY sh.timestamp ), 0) AS dr_daily_return FROM stock_history sh WHERE (p_from_date IS NULL OR sh.timestamp >= p_from_date) AND (p_to_date IS NULL OR sh.timestamp <= p_to_date) ), -- aggregate per stock price_stats AS ( SELECT dr.dr_stock_id AS ps_stock_id, COUNT(*) AS ps_total_points, MIN(dr.dr_price) AS ps_min_price, MAX(dr.dr_price) AS ps_max_price, AVG(dr.dr_price) AS ps_avg_price, COALESCE(STDDEV(dr.dr_daily_return) * 100, 0) AS ps_volatility_pct FROM daily_returns dr GROUP BY dr.dr_stock_id ), -- trend least-squares linear regression slope on (row_number, price) price_ordered AS ( SELECT sh.stock_id AS po_stock_id, ROW_NUMBER() OVER (PARTITION BY sh.stock_id ORDER BY sh.timestamp) AS po_rn, sh.price AS po_price FROM stock_history sh WHERE (p_from_date IS NULL OR sh.timestamp >= p_from_date) AND (p_to_date IS NULL OR sh.timestamp <= p_to_date) ), trend_calc AS ( SELECT po.po_stock_id AS tc_stock_id, ROUND( ((COUNT(*) * SUM(po.po_rn * po.po_price) - SUM(po.po_rn) * SUM(po.po_price)) / NULLIF(COUNT(*) * SUM(po.po_rn * po.po_rn) - SUM(po.po_rn) * SUM(po.po_rn), 0))::NUMERIC , 6) AS tc_slope FROM price_ordered po GROUP BY po.po_stock_id ), -- transctikomn stats per stock in period txn_stats AS ( SELECT t.stock_id AS ts_stock_id, SUM(t.price * t.quantity) AS ts_total_volume, COUNT(*) AS ts_total_count, COUNT(*) FILTER (WHERE t.type = 'BUY') AS ts_buy_count, COUNT(*) FILTER (WHERE t.type = 'SELL') AS ts_sell_count FROM transactions t WHERE (p_from_date IS NULL OR t.timestamp >= p_from_date) AND (p_to_date IS NULL OR t.timestamp <= p_to_date) GROUP BY t.stock_id ), -- watchlist interest stock watchlist_stats AS ( SELECT w.stock_id AS ws_stock_id, COUNT(DISTINCT w.user_id) AS ws_active_watchers FROM watchlist w GROUP BY w.stock_id ), -- pending trade requests per stock pending_stats AS ( SELECT s.id AS pst_stock_id, COUNT(tr.id) AS pst_pending_count FROM trade_request tr JOIN stock s ON tr.stock_symbol = s.symbol WHERE tr.status = 'PENDING' GROUP BY s.id ) SELECT s.id::INTEGER AS stock_id, s.symbol::TEXT, s.name::TEXT AS stock_name, s.current_price::NUMERIC, COALESCE(ps.ps_min_price, s.current_price)::NUMERIC AS period_min_price, COALESCE(ps.ps_max_price, s.current_price)::NUMERIC AS period_max_price, ROUND( ((COALESCE(ps.ps_max_price, s.current_price) - COALESCE(ps.ps_min_price, s.current_price)) / NULLIF(COALESCE(ps.ps_min_price, s.current_price), 0) * 100)::NUMERIC , 2)::NUMERIC AS price_range_pct, COALESCE(ps.ps_avg_price, s.current_price)::NUMERIC AS avg_daily_price, COALESCE(ps.ps_volatility_pct, 0)::NUMERIC AS historical_volatility_pct, COALESCE(ps.ps_total_points, 0)::BIGINT AS total_history_points, COALESCE(ts.ts_total_volume, 0)::NUMERIC AS total_txn_volume, COALESCE(ts.ts_total_count, 0)::BIGINT AS total_txn_count, COALESCE(ts.ts_buy_count, 0)::BIGINT AS buy_txn_count, COALESCE(ts.ts_sell_count, 0)::BIGINT AS sell_txn_count, ROUND( (COALESCE(ts.ts_buy_count, 0)::NUMERIC / NULLIF(COALESCE(ts.ts_sell_count, 0), 0))::NUMERIC , 2)::NUMERIC AS buy_sell_ratio, COALESCE(ws.ws_active_watchers, 0)::BIGINT AS active_watchlist_users, COALESCE(pst.pst_pending_count, 0)::BIGINT AS pending_trade_requests, CASE WHEN tc.tc_slope > 0.01 THEN 'UPTREND' WHEN tc.tc_slope < -0.01 THEN 'DOWNTREND' ELSE 'SIDEWAYS' END::TEXT AS trend_direction, COALESCE(tc.tc_slope, 0)::NUMERIC AS trend_slope, ROUND( (COALESCE(ws.ws_active_watchers, 0) * 2.0 + COALESCE(ts.ts_total_count, 0) * 1.0 + COALESCE(pst.pst_pending_count, 0) * 3.0)::NUMERIC , 2)::NUMERIC AS activity_score, RANK() OVER ( ORDER BY COALESCE(ps.ps_volatility_pct, 0) DESC )::BIGINT AS volatility_rank, RANK() OVER ( ORDER BY ( COALESCE(ws.ws_active_watchers, 0) * 2.0 + COALESCE(ts.ts_total_count, 0) * 1.0 + COALESCE(pst.pst_pending_count, 0) * 3.0 ) DESC )::BIGINT AS activity_rank FROM stock s LEFT JOIN price_stats ps ON s.id = ps.ps_stock_id LEFT JOIN trend_calc tc ON s.id = tc.tc_stock_id LEFT JOIN txn_stats ts ON s.id = ts.ts_stock_id LEFT JOIN watchlist_stats ws ON s.id = ws.ws_stock_id LEFT JOIN pending_stats pst ON s.id = pst.pst_stock_id ORDER BY historical_volatility_pct DESC, activity_score DESC; END; $$ LANGUAGE plpgsql; }}} '''EXPLAIN ANALYZE без индекси:''' {{{ EXPLAIN ANALYZE SELECT * FROM report_stock_volatility_and_activity('2024-01-01', '2024-12-31'); -- резултат: QUERY PLAN | -----------------------------------------------------------------------------------------------------------------------------------------+ Function Scan on report_stock_volatility_and_activity (cost=0.25..10.25 rows=1000 width=484) (actual time=13.601..13.602 rows=4 loops=1)| Planning Time: 0.094 ms | Execution Time: 15.248 ms | }}} '''Индекси:''' **1. stock_history - Composite Index (stock_id, timestamp)** {{{ CREATE INDEX idx_sh_stock_id_timestamp ON stock_history(stock_id, timestamp); }}} Користење: `PARTITION BY sh.stock_id ORDER BY sh.timestamp` во LAG прозорската функција и WHERE филтерот за дати Подобрување: Composite индексот ги покрива и партиционирањето и сортирањето -= елиминира Sort чекор --- **2. transactions - Composite Index (stock_id, timestamp)** {{{ CREATE INDEX idx_tx_stock_id_timestamp ON transactions(stock_id, timestamp); }}} Користење: GROUP BY `t.stock_id` и WHERE на `timestamp` во `txn_stats` Подобрување: Директен Index Scan наместо Seq Scan + Sort при временски филтрирани агрегации --- **3. watchlist - Index (stock_id)** {{{ CREATE INDEX idx_watchlist_stock_id ON watchlist(stock_id); }}} Користење: GROUP BY `stock_id` во `watchlist_stats` Подобрување: Index Scan при агрегација по акција наместо Seq Scan --- **4. trade_request - Composite Index (stock_symbol, status)** {{{ CREATE INDEX idx_tr_symbol_status ON trade_request(stock_symbol, status); }}} Користење: JOIN `tr.stock_symbol = s.symbol` и WHERE `tr.status = 'PENDING'` во `pending_stats` Подобрување: Composite индексот ги покрива и JOIN условот и WHERE филтерот истовремено --- '''EXPLAIN ANALYZE со индекси:''' {{{ EXPLAIN ANALYZE SELECT * FROM report_stock_volatility_and_activity('2024-01-01', '2024-12-31'); -- резултат: QUERY PLAN | ---------------------------------------------------------------------------------------------------------------------------------------+ Function Scan on report_stock_volatility_and_activity (cost=0.25..10.25 rows=1000 width=484) (actual time=6.624..6.625 rows=4 loops=1)| Planning Time: 0.111 ms | Execution Time: 6.693 ms | }}} '''Заклучок:''' Имаме забрзување. ---- == Безбедност и заштита == === JWT Token Authorization (Spring Security) === JWT е stateless начин на автентикација - серверот НЕ чува информации за активни сесии во база, туку сите потребни податоци се енкодирани во самиот токен кој корисникот го чува локално. JWT содржи енкодирани информации: user_id, email, role, expiry. Имплементацијата во `SecurityConfig.java` дефинира кои endpoints се јавни и кои бараат автентикација, со посебни правила за ADMIN: {{{ // SecurityConfig.java @Bean public SecurityFilterChain filterChain(HttpSecurity http) throws Exception { http .cors(cors -> cors.configurationSource(corsConfigurationSource())) .csrf(csrf -> csrf.disable()) .authorizeHttpRequests(auth -> auth // Public endpoints - no token required .requestMatchers("/api/auth/**").permitAll() .requestMatchers("/api/stocks/**").permitAll() .requestMatchers("/ws/**", "/topic/**").permitAll() .requestMatchers("/api/history/**").permitAll() // Admin only - role-based access control .requestMatchers("/api/trades/*/approve").hasAuthority("ADMIN") .requestMatchers("/api/trades/*/decline").hasAuthority("ADMIN") .requestMatchers("/api/trades/pending").hasAuthority("ADMIN") // Authenticated users .requestMatchers("/api/trades/**").authenticated() .requestMatchers("/api/watchlist/**").authenticated() .requestMatchers("/api/transactions/**").authenticated() .anyRequest().authenticated() ) .sessionManagement(s -> s .sessionCreationPolicy(SessionCreationPolicy.STATELESS) ) .authenticationProvider(authenticationProvider) .addFilterBefore(jwtAuthenticationFilter, UsernamePasswordAuthenticationFilter.class); return http.build(); } }}} Секој HTTP барање поминува низ `JwtAuthenticationFilter` пред да стигне до контролерот. Филтерот го верификува потписот на токенот, го проверува expiry и го вчитува корисникот. Корисник без валиден JWT токен добива HTTP 401 Unauthorized. === Хеширање на лозинки (BCrypt) === Лозинките на корисниците се чуваат во базата во хеширана форма преку BCrypt алгоритам, а никогаш како plain text. Во базата колоната `users.password` содржи BCrypt hash со формат `$2a$10$...`. === CORS Конфигурација === CORS е безбеден механизам кој ги ограничува HTTP барањата само од дозволени домени. Апликацијата има двојна CORS конфигурација: {{{ // SecurityConfig.java - CORS на Spring Security ниво @Bean public CorsConfigurationSource corsConfigurationSource() { CorsConfiguration config = new CorsConfiguration(); config.setAllowedOrigins(List.of( "http://localhost:5173", "http://localhost:5174", "http://localhost:5175", "http://localhost:5176", "http://localhost:3000" )); config.setAllowedMethods(List.of("GET", "POST", "PUT", "DELETE", "OPTIONS", "PATCH")); config.setAllowedHeaders(List.of("*")); config.setAllowCredentials(true); UrlBasedCorsConfigurationSource source = new UrlBasedCorsConfigurationSource(); source.registerCorsConfiguration("/**", source); return source; } }}} {{{ // WebConfig.java - CORS на Spring MVC ниво @Configuration public class WebConfig implements WebMvcConfigurer { @Override public void addCorsMappings(CorsRegistry registry) { registry.addMapping("/**") .allowedOrigins( "http://localhost:5173", "http://localhost:5174", "http://localhost:5175", "http://localhost:3000" ) .allowedMethods("*") .allowedHeaders("*") .allowCredentials(true); } } }}} Само барања кои потекнуваат од дозволените localhost портови ќе бидат прифатени. Секое барање од непознат домен добива HTTP 403 уште на ниво на CORS preflight проверката. === Заштита на податоци на ниво на база === На ниво на базата на податоци, безбедноста е обезбедена преку: * '''Role-based CHECK constraints''' - `users.role CHECK (role IN ('USER','ADMIN'))` спречува внесување на невалидни улоги директно во базата * '''Enum CHECK constraints''' - `transactions.origin CHECK (origin IN ('INTERNAL','EXTERNAL'))` и `user_auth_providers.auth_providers CHECK (auth_providers IN ('INTERNAL','GOOGLE'))` обезбедуваат интегритет на enum вредностите * '''Foreign key constraints''' - сите референцијални правила се дефинирани на ниво на база, не само на апликациско ниво * '''UNIQUE constraints''' - `users.email UNIQUE`, `users.username UNIQUE`, `stock.symbol UNIQUE` и `portfolios.user_id UNIQUE` спречуваат дупликати кои би можеле да доведат до безбедносни пропусти ... ----