Други Развојни Активности
Анализа на перформанси
Извештај за диверзификација на портфолио (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спречуваат дупликати кои би можеле да доведат до безбедносни пропусти
...
Last modified
2 days ago
Last modified on 03/17/26 01:57:07
Note:
See TracWiki
for help on using the wiki.
