| Version 3 (modified by , 2 weeks ago) ( diff ) |
|---|
Други Развојни Активности
Анализа на перформанси
Извештај за диверзификација на портфолио (report_portfolio_diversification)
Анализата на перформанси се врши врз основа на моменталната состојба во базата.
SQL:
CREATE OR REPLACE FUNCTION report_portfolio_diversification()
RETURNS TABLE (
user_id INTEGER,
username TEXT,
portfolio_id INTEGER,
portfolio_cash_balance NUMERIC,
total_holdings_value NUMERIC,
total_portfolio_value NUMERIC,
num_distinct_stocks BIGINT,
total_lots BIGINT,
unrealized_pnl NUMERIC,
unrealized_pnl_pct NUMERIC,
hhi_score NUMERIC,
risk_classification TEXT,
dominant_stock_symbol TEXT,
dominant_stock_weight_pct NUMERIC,
max_single_weight_pct NUMERIC,
min_single_weight_pct NUMERIC,
diversification_rank BIGINT
) AS $$
BEGIN
RETURN QUERY
WITH
holding_values AS (
SELECT
ph.portfolio_id AS hv_portfolio_id,
ph.stock_id AS hv_stock_id,
s.symbol AS hv_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
),
stock_weights AS (
SELECT
hv.hv_portfolio_id,
hv.hv_stock_id,
hv.hv_symbol,
SUM(hv.market_value) AS stock_market_value,
SUM(hv.unrealized_pnl) AS stock_unrealized_pnl,
COUNT(*) AS lots_count
FROM holding_values hv
GROUP BY hv.hv_portfolio_id, hv.hv_stock_id, hv.hv_symbol
),
portfolio_totals AS (
SELECT
sw.hv_portfolio_id,
SUM(sw.stock_market_value) AS total_holdings_value,
SUM(sw.stock_unrealized_pnl) AS total_unrealized_pnl,
COUNT(DISTINCT sw.hv_stock_id) AS num_stocks,
SUM(sw.lots_count) AS total_lots
FROM stock_weights sw
GROUP BY sw.hv_portfolio_id
),
hhi_calc AS (
SELECT
sw.hv_portfolio_id AS hhi_portfolio_id,
SUM(POWER(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC), 2::NUMERIC)) AS hhi,
MAX(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC) AS max_weight_pct,
MIN(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC) AS min_weight_pct
FROM stock_weights sw
JOIN portfolio_totals pt ON sw.hv_portfolio_id = pt.hv_portfolio_id
GROUP BY sw.hv_portfolio_id
),
dominant_stock AS (
SELECT DISTINCT ON (sw.hv_portfolio_id)
sw.hv_portfolio_id AS ds_portfolio_id,
sw.hv_symbol AS dominant_symbol,
ROUND(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC, 2) AS dominant_weight_pct
FROM stock_weights sw
JOIN portfolio_totals pt ON sw.hv_portfolio_id = pt.hv_portfolio_id
ORDER BY sw.hv_portfolio_id,
sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) DESC
)
SELECT
u.id::INTEGER,
u.username::TEXT,
p.id::INTEGER,
p.balance::NUMERIC,
COALESCE(pt.total_holdings_value, 0::NUMERIC),
(p.balance::NUMERIC + COALESCE(pt.total_holdings_value, 0::NUMERIC)),
COALESCE(pt.num_stocks, 0)::BIGINT,
COALESCE(pt.total_lots, 0)::BIGINT,
COALESCE(pt.total_unrealized_pnl, 0::NUMERIC),
ROUND(COALESCE(pt.total_unrealized_pnl, 0::NUMERIC)
/ NULLIF(pt.total_holdings_value - COALESCE(pt.total_unrealized_pnl, 0::NUMERIC), 0::NUMERIC) * 100::NUMERIC, 2),
ROUND(COALESCE(hhi.hhi, 0::NUMERIC), 4),
CASE
WHEN hhi.hhi >= 0.25 THEN 'HIGH CONCENTRATION RISK'
WHEN hhi.hhi >= 0.10 THEN 'MEDIUM CONCENTRATION RISK'
ELSE 'WELL DIVERSIFIED'
END::TEXT,
COALESCE(ds.dominant_symbol, 'N/A')::TEXT,
COALESCE(ds.dominant_weight_pct, 0::NUMERIC),
COALESCE(hhi.max_weight_pct, 0::NUMERIC),
COALESCE(hhi.min_weight_pct, 0::NUMERIC),
RANK() OVER (ORDER BY COALESCE(hhi.hhi, 1::NUMERIC) ASC)::BIGINT
FROM users u
JOIN portfolios p ON u.id = p.user_id
LEFT JOIN portfolio_totals pt ON p.id = pt.hv_portfolio_id
LEFT JOIN hhi_calc hhi ON p.id = hhi.hhi_portfolio_id
LEFT JOIN dominant_stock ds ON p.id = ds.ds_portfolio_id
WHERE u.role = 'USER'
ORDER BY hhi_score ASC, total_portfolio_value DESC;
END;
$$ LANGUAGE plpgsql;
EXPLAIN ANALYZE без индекси:
EXPLAIN ANALYZE SELECT * FROM report_portfolio_diversification(); -- резултат: QUERY PLAN | -------------------------------------------------------------------------------------------------------------------------------------+ Function Scan on report_portfolio_diversification (cost=0.25..10.25 rows=1000 width=416) (actual time=10.842..10.843 rows=2 loops=1)| Planning Time: 0.084 ms | Execution Time: 12.265 ms |
Индекси:
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 табела
---
EXPLAIN ANALYZE со индекси:
EXPLAIN ANALYZE SELECT * FROM report_portfolio_diversification();
-- резултат:
-----------------------------------------------------------------------------------------------------------------------------------+
Function Scan on report_portfolio_diversification (cost=0.25..10.25 rows=1000 width=416) (actual time=4.145..4.146 rows=2 loops=1)|
Planning Time: 0.070 ms |
Execution Time: 4.212 ms |
Заклучок: Имаме забрзување.
Извештај за волатилност на акции (report_stock_volatility_and_activity)
SQL:
-- Core query inside report_stock_volatility_and_activity()
WITH 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 sh.timestamp >= p_from_date
AND sh.timestamp <= p_to_date
),
price_stats AS (
SELECT dr.dr_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
),
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 t.timestamp >= p_from_date
AND t.timestamp <= p_to_date
GROUP BY t.stock_id
)
SELECT s.id, s.symbol, s.name, s.current_price, ...
FROM stock s
LEFT JOIN price_stats ps ON s.id = ps.dr_stock_id
LEFT JOIN txn_stats ts ON s.id = ts.ts_stock_id
ORDER BY historical_volatility_pct DESC, activity_score DESC;
EXPLAIN ANALYZE без индекси:
EXPLAIN ANALYZE
SELECT sh.stock_id, sh.price, sh.timestamp
FROM stock_history sh
WHERE sh.timestamp >= '2024-01-01'
AND sh.timestamp <= '2024-12-31'
ORDER BY sh.stock_id, sh.timestamp;
-- резултат:
Sort (cost=31.85..31.87 rows=7 width=20) (actual time=0.051..0.052 rows=0 loops=1)
Sort Key: stock_id, "timestamp"
Sort Method: quicksort Memory: 25kB
-> Seq Scan on stock_history sh (cost=0.00..31.75 rows=7 width=20) (actual time=0.018..0.018 rows=0 loops=1)
Filter: (("timestamp" >= '2024-01-01'::date) AND ("timestamp" <= '2024-12-31'::date))
Rows Removed by Filter: 8
Planning Time: 0.417 ms
Execution Time: 0.077 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 sh.stock_id, sh.price, sh.timestamp
FROM stock_history sh
WHERE sh.timestamp >= '2024-01-01'
AND sh.timestamp <= '2024-12-31'
ORDER BY sh.stock_id, sh.timestamp;
-- резултат:
Sort (cost=1.13..1.14 rows=1 width=20) (actual time=0.024..0.025 rows=0 loops=1)
Sort Key: stock_id, "timestamp"
Sort Method: quicksort Memory: 25kB
-> Seq Scan on stock_history sh (cost=0.00..1.12 rows=1 width=20) (actual time=0.017..0.017 rows=0 loops=1)
Filter: (("timestamp" >= '2024-01-01'::date) AND ("timestamp" <= '2024-12-31'::date))
Rows Removed by Filter: 8
Planning Time: 0.397 ms
Execution Time: 0.054 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спречуваат дупликати кои би можеле да доведат до безбедносни пропусти
...
