| Version 5 (modified by , 10 days ago) ( diff ) |
|---|
Други Развојни Активности
Анализа на перформанси
Извештај за диверзификација на портфолио (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спречуваат дупликати кои би можеле да доведат до безбедносни пропусти
...
