= Други Развојни Активности = == Анализа на перформанси == === Извештај за диверзификација на портфолио (report_portfolio_diversification) === Анализата на перформанси се врши врз основа на моменталната состојба во базата. '''SQL:''' {{{ 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; EXPLAIN ANALYZE SELECT 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 quantity > 0 AND portfolio_id = 1; QUERY PLAN | --------------------------------------------------------------------------------------------------------------------------------+ Nested Loop (cost=0.15..1418.59 rows=50 width=85) (actual time=0.085..8.009 rows=50 loops=1) | -> Seq Scan on portfolio_holdings ph (cost=0.00..1400.00 rows=50 width=26) (actual time=0.037..7.462 rows=50 loops=1) | Filter: ((quantity > 0) AND (portfolio_id = 1)) | Rows Removed by Filter: 59950 | -> Memoize (cost=0.15..0.49 rows=1 width=21) (actual time=0.005..0.005 rows=1 loops=50) | Cache Key: ph.stock_id | Cache Mode: logical | Hits: 0 Misses: 50 Evictions: 0 Overflows: 0 Memory Usage: 7kB | -> Index Scan using stock_pkey on stock s (cost=0.14..0.48 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=50)| Index Cond: (id = ph.stock_id) | Planning Time: 0.799 ms | Execution Time: 8.064 ms | }}} '''Индекси:''' **1. portfolio_holdings - Index (stock_id)** {{{ CREATE INDEX idx_ph_stock_id ON portfolio_holdings(stock_id); ANALYZE portfolio_holdings; EXPLAIN ANALYZE SELECT 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 quantity > 0 AND portfolio_id = 1; QUERY PLAN | --------------------------------------------------------------------------------------------------------------------------------+ Nested Loop (cost=0.15..1418.59 rows=50 width=85) (actual time=0.052..5.758 rows=50 loops=1) | -> Seq Scan on portfolio_holdings ph (cost=0.00..1400.00 rows=50 width=26) (actual time=0.018..5.389 rows=50 loops=1) | Filter: ((quantity > 0) AND (portfolio_id = 1)) | Rows Removed by Filter: 59950 | -> Memoize (cost=0.15..0.49 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=50) | Cache Key: ph.stock_id | Cache Mode: logical | Hits: 0 Misses: 50 Evictions: 0 Overflows: 0 Memory Usage: 7kB | -> Index Scan using stock_pkey on stock s (cost=0.14..0.48 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=50)| Index Cond: (id = ph.stock_id) | Planning Time: 0.471 ms | Execution Time: 5.802 ms | }}} --- **2. portfolio_holdings - Index (portfolio_id)** {{{ CREATE INDEX idx_ph_portfolio_id ON portfolio_holdings(portfolio_id); ANALYZE portfolio_holdings; EXPLAIN ANALYZE SELECT 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 quantity > 0 AND portfolio_id = 1; QUERY PLAN | --------------------------------------------------------------------------------------------------------------------------------+ Nested Loop (cost=0.15..1418.59 rows=50 width=85) (actual time=0.052..5.758 rows=50 loops=1) | -> Seq Scan on portfolio_holdings ph (cost=0.00..1400.00 rows=50 width=26) (actual time=0.018..5.389 rows=50 loops=1) | Filter: ((quantity > 0) AND (portfolio_id = 1)) | Rows Removed by Filter: 59950 | -> Memoize (cost=0.15..0.49 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=50) | Cache Key: ph.stock_id | Cache Mode: logical | Hits: 0 Misses: 50 Evictions: 0 Overflows: 0 Memory Usage: 7kB | -> Index Scan using stock_pkey on stock s (cost=0.14..0.48 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=50)| Index Cond: (id = ph.stock_id) | Planning Time: 0.471 ms | Execution Time: 5.802 ms | }}} Користење: GROUP BY и JOIN по `portfolio_id` во CTE-ата `stock_weights` и `portfolio_totals` Подобрување: Index Scan наместо Seq Scan при групирање по портфолио --- **3. Add index on portfolio_id** {{{ CREATE INDEX idx_ph_portfolio_id ON portfolio_holdings(portfolio_id); ANALYZE portfolio_holdings; EXPLAIN ANALYZE SELECT 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 quantity > 0 AND portfolio_id = 1; QUERY PLAN | ----------------------------------------------------------------------------------------------------------------------------------+ Hash Join (cost=9.00..181.17 rows=50 width=85) (actual time=0.245..0.515 rows=50 loops=1) | Hash Cond: (ph.stock_id = s.id) | -> Bitmap Heap Scan on portfolio_holdings ph (cost=4.75..175.92 rows=50 width=26) (actual time=0.156..0.265 rows=50 loops=1) | Recheck Cond: (portfolio_id = 1) | Filter: (quantity > 0) | Rows Removed by Filter: 10 | Heap Blocks: exact=60 | -> Bitmap Index Scan on idx_ph_portfolio_id (cost=0.00..4.74 rows=60 width=0) (actual time=0.136..0.137 rows=60 loops=1)| Index Cond: (portfolio_id = 1) | -> Hash (cost=3.00..3.00 rows=100 width=21) (actual time=0.059..0.060 rows=100 loops=1) | Buckets: 1024 Batches: 1 Memory Usage: 14kB | -> Seq Scan on stock s (cost=0.00..3.00 rows=100 width=21) (actual time=0.019..0.035 rows=100 loops=1) | Planning Time: 0.526 ms | Execution Time: 0.554 ms | }}} --- **4. partial index** {{{ CREATE INDEX idx_ph_quantity_positive ON portfolio_holdings(portfolio_id, stock_id) WHERE quantity > 0; ANALYZE portfolio_holdings; EXPLAIN ANALYZE SELECT 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 quantity > 0 AND portfolio_id = 1; QUERY PLAN | ---------------------------------------------------------------------------------------------------------------------------------------+ Hash Join (cost=8.93..158.07 rows=50 width=85) (actual time=0.315..0.573 rows=50 loops=1) | Hash Cond: (ph.stock_id = s.id) | -> Bitmap Heap Scan on portfolio_holdings ph (cost=4.68..152.81 rows=50 width=26) (actual time=0.225..0.320 rows=50 loops=1) | Recheck Cond: ((portfolio_id = 1) AND (quantity > 0)) | Heap Blocks: exact=50 | -> Bitmap Index Scan on idx_ph_quantity_positive (cost=0.00..4.67 rows=50 width=0) (actual time=0.209..0.209 rows=50 loops=1)| Index Cond: (portfolio_id = 1) | -> Hash (cost=3.00..3.00 rows=100 width=21) (actual time=0.059..0.060 rows=100 loops=1) | Buckets: 1024 Batches: 1 Memory Usage: 14kB | -> Seq Scan on stock s (cost=0.00..3.00 rows=100 width=21) (actual time=0.021..0.037 rows=100 loops=1) | Planning Time: 0.608 ms | Execution Time: 0.609 ms | }}} --- **5. Add index on users.role** {{{ CREATE INDEX idx_users_role ON users(role); ANALYZE users; EXPLAIN ANALYZE SELECT 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 quantity > 0 AND portfolio_id = 1; QUERY PLAN | ---------------------------------------------------------------------------------------------------------------------------------------+ Hash Join (cost=8.93..158.07 rows=50 width=85) (actual time=0.123..0.377 rows=50 loops=1) | Hash Cond: (ph.stock_id = s.id) | -> Bitmap Heap Scan on portfolio_holdings ph (cost=4.68..152.81 rows=50 width=26) (actual time=0.032..0.125 rows=50 loops=1) | Recheck Cond: ((portfolio_id = 1) AND (quantity > 0)) | Heap Blocks: exact=50 | -> Bitmap Index Scan on idx_ph_quantity_positive (cost=0.00..4.67 rows=50 width=0) (actual time=0.016..0.017 rows=50 loops=1)| Index Cond: (portfolio_id = 1) | -> Hash (cost=3.00..3.00 rows=100 width=21) (actual time=0.060..0.060 rows=100 loops=1) | Buckets: 1024 Batches: 1 Memory Usage: 14kB | -> Seq Scan on stock s (cost=0.00..3.00 rows=100 width=21) (actual time=0.018..0.037 rows=100 loops=1) | Planning Time: 0.299 ms | Execution Time: 0.416 ms | }}} --- '''Сумарно:''' 20x побрзо ---- === Извештај за волатилност на акции (report_stock_volatility_and_activity) === '''SQL:''' {{{ DROP INDEX IF EXISTS idx_sh_stock_id_timestamp; DROP INDEX IF EXISTS idx_tx_stock_id_timestamp; DROP INDEX IF EXISTS idx_watchlist_stock_id; DROP INDEX IF EXISTS idx_tr_symbol_status; EXPLAIN ANALYZE SELECT sh.stock_id, sh.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 daily_return FROM stock_history sh WHERE sh.timestamp >= '2024-01-01' AND sh.timestamp <= '2024-12-31'; QUERY PLAN | ---------------------------------------------------------------------------------------------------------------------------------+ WindowAgg (cost=8773.08..9784.81 rows=36791 width=28) (actual time=41.592..66.876 rows=36600 loops=1) | -> Sort (cost=8773.06..8865.03 rows=36791 width=20) (actual time=41.543..46.298 rows=36600 loops=1) | Sort Key: stock_id, "timestamp" | Sort Method: external merge Disk: 1256kB | -> Seq Scan on stock_history sh (cost=0.00..4471.00 rows=36791 width=20) (actual time=0.060..22.078 rows=36600 loops=1)| Filter: (("timestamp" >= '2024-01-01'::date) AND ("timestamp" <= '2024-12-31'::date)) | Rows Removed by Filter: 163400 | Planning Time: 0.526 ms | Execution Time: 69.235 ms | }}} '''Индекси:''' --- **1. transactions - Composite Index (stock_id, timestamp)** {{{ CREATE INDEX idx_tx_stock_id_timestamp ON transactions(stock_id, timestamp); ANALYZE transactions; EXPLAIN ANALYZE SELECT ... QUERY PLAN | -------------------------------------------------------------------------------------------------------------------------------------------------------------+ WindowAgg (cost=0.66..9098.75 rows=36545 width=28) (actual time=0.078..41.712 rows=36600 loops=1) | -> Index Scan using idx_sh_stock_id_timestamp on stock_history sh (cost=0.42..8185.12 rows=36545 width=20) (actual time=0.066..17.591 rows=36600 loops=1)| Index Cond: (("timestamp" >= '2024-01-01'::date) AND ("timestamp" <= '2024-12-31'::date)) | Planning Time: 0.163 ms | Execution Time: 43.629 ms | }}} --- **2. watchlist - Index (stock_id)** {{{ CREATE INDEX idx_watchlist_stock_id ON watchlist(stock_id); ANALYZE watchlist; EXPLAIN ANALYZE SELECT ... QUERY PLAN | -------------------------------------------------------------------------------------------------------------------------------------------------------------+ WindowAgg (cost=0.66..9098.75 rows=36545 width=28) (actual time=0.083..42.003 rows=36600 loops=1) | -> Index Scan using idx_sh_stock_id_timestamp on stock_history sh (cost=0.42..8185.12 rows=36545 width=20) (actual time=0.070..17.564 rows=36600 loops=1)| Index Cond: (("timestamp" >= '2024-01-01'::date) AND ("timestamp" <= '2024-12-31'::date)) | Planning Time: 0.162 ms | Execution Time: 43.946 ms | }}} '''Заклучок:''' Имаме забрзување од ~60% ---- == Безбедност и заштита == === 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` спречуваат дупликати кои би можеле да доведат до безбедносни пропусти ... ----