| Version 4 (modified by , 2 weeks ago) ( diff ) |
|---|
Напреден развој на базата
Валидација на трговски барања (Trade Request Validation)
Опис на барањата за податочни ограничувања
Системот мора да обезбеди дека:
- Корисникот не смее да поднесе BUY барање ако неговото портфолио нема доволно готовина (balance) за покривање на вкупната вредност на барањето (price_per_unit * quantity)
- Корисникот не смее да поднесе SELL барање за акција ако неговото портфолио не ги поседува бараните акции во доволна количина
- Статусот на trade_request смее да биде само: PENDING, EXECUTED, CANCELLED
- Типот на trade_request смее да биде само: BUY, SELL
Имплементација
Прилагодени домени (Custom Domains)
CREATE DOMAIN trade_status_domain AS VARCHAR(255)
CHECK (VALUE IN ('PENDING', 'COMPLETED', 'DECLINED'));
CREATE DOMAIN trade_type_domain AS VARCHAR(255)
CHECK (VALUE IN ('BUY', 'SELL'));
ALTER TABLE trade_request ALTER COLUMN status TYPE trade_status_domain;
ALTER TABLE trade_request ALTER COLUMN type TYPE trade_type_domain;
Тригери
BEFORE INSERT тригер на trade_request кој ги валидира BUY и SELL барањата пред да бидат зачувани.
CREATE OR REPLACE FUNCTION validate_trade_request()
RETURNS TRIGGER AS $$
DECLARE
v_balance NUMERIC(18,2);
v_total_cost NUMERIC(18,2);
v_owned_quantity INTEGER;
v_stock_id BIGINT;
BEGIN
-- total cost of the request
v_total_cost := NEW.price_per_unit * NEW.quantity;
IF NEW.type = 'BUY' THEN
-- Check - portfolio has enough balance
SELECT p.balance
INTO v_balance
FROM portfolios p
WHERE p.id = NEW.portfolio_id;
IF v_balance IS NULL THEN
RAISE EXCEPTION 'Portfolio with id=% not found', NEW.portfolio_id;
END IF;
IF v_balance < v_total_cost THEN
RAISE EXCEPTION
'Insufficient balance: portfolio has %, but trade requires %',
v_balance, v_total_cost;
END IF;
ELSIF NEW.type = 'SELL' THEN
-- stock_id from symbol
SELECT s.id INTO v_stock_id
FROM stock s
WHERE s.symbol = NEW.stock_symbol;
IF v_stock_id IS NULL THEN
RAISE EXCEPTION 'Stock with symbol=% not found', NEW.stock_symbol;
END IF;
-- Sum all stock in the portfolio
SELECT COALESCE(SUM(ph.quantity), 0)
INTO v_owned_quantity
FROM portfolio_holdings ph
WHERE ph.portfolio_id = NEW.portfolio_id
AND ph.stock_id = v_stock_id;
IF v_owned_quantity < NEW.quantity THEN
RAISE EXCEPTION
'Insufficient holdings: portfolio owns % shares of %, but trade requires %',
v_owned_quantity, NEW.stock_symbol, NEW.quantity;
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_validate_trade_request
BEFORE INSERT ON trade_request
FOR EACH ROW
EXECUTE FUNCTION validate_trade_request();
Погледи (Views)
Поглед за преглед на сите pending барања со информации за корисникот и портфолиото.
CREATE OR REPLACE VIEW pending_trade_requests AS
SELECT
tr.id AS trade_id,
tr.type AS trade_type,
tr.stock_symbol,
tr.quantity,
tr.price_per_unit,
(tr.price_per_unit * tr.quantity)::NUMERIC AS total_value,
tr.status,
tr.timestamp,
p.id AS portfolio_id,
p.balance AS portfolio_balance,
u.id AS user_id,
u.username,
u.email
FROM trade_request tr
JOIN portfolios p ON tr.portfolio_id = p.id
JOIN users u ON p.user_id = u.id
WHERE tr.status = 'PENDING'
ORDER BY tr.timestamp DESC;
Автоматско ажурирање на портфолио при извршување на трансакција
Опис на барањата за податочни ограничувања
Системот мора да обезбеди дека:
- Кога се INSERT-ира нова трансакција од тип BUY, балансот на портфолиото на корисникот автоматски се намалува за (price * quantity), а во portfolio_holdings се додава или се ажурира avg_price на постоечкото холдинг за таа акција
- Кога се INSERT-ира нова трансакција од тип SELL, балансот на портфолиото автоматски се зголемува за (price * quantity), а количината во portfolio_holdings се намалува; ако quantity достигне 0, записот се брише
- avg_price при BUY се пресметува со weighted average формула: (стара_вредност + нова_вредност) / (стар_qty + нов_qty)
Имплементација
Тригери
AFTER INSERT тригер на transactions кој автоматски го ажурира балансот на портфолиото и portfolio_holdings.
CREATE OR REPLACE FUNCTION sync_portfolio_on_transaction()
RETURNS TRIGGER AS $$
DECLARE
v_portfolio_id BIGINT;
v_existing_qty INTEGER;
v_existing_avg NUMERIC(38,2);
v_new_avg NUMERIC(38,2);
v_total_value NUMERIC(38,2);
BEGIN
v_total_value := NEW.price * NEW.quantity;
-- portfolio from user
SELECT p.id INTO v_portfolio_id
FROM portfolios p
WHERE p.user_id = NEW.user_id;
IF v_portfolio_id IS NULL THEN
RAISE EXCEPTION 'No portfolio found for user_id=%', NEW.user_id;
END IF;
IF NEW.type = 'BUY' THEN
-- balance
UPDATE portfolios
SET balance = balance - v_total_value
WHERE id = v_portfolio_id;
-- chekc if holding already exists
SELECT ph.quantity, ph.avg_price
INTO v_existing_qty, v_existing_avg
FROM portfolio_holdings ph
WHERE ph.portfolio_id = v_portfolio_id
AND ph.stock_id = NEW.stock_id
LIMIT 1;
IF v_existing_qty IS NOT NULL THEN
-- weighted average price
v_new_avg := ((v_existing_avg * v_existing_qty) + (NEW.price * NEW.quantity))
/ (v_existing_qty + NEW.quantity);
UPDATE portfolio_holdings
SET quantity = quantity + NEW.quantity,
avg_price = v_new_avg
WHERE portfolio_id = v_portfolio_id
AND stock_id = NEW.stock_id;
ELSE
-- insert new holding lot
INSERT INTO portfolio_holdings (id, quantity, avg_price, stock_id, portfolio_id)
VALUES (
nextval('portfolio_holdings_id_seq'),
NEW.quantity,
NEW.price,
NEW.stock_id,
v_portfolio_id
);
END IF;
ELSIF NEW.type = 'SELL' THEN
-- add proceeds to balance
UPDATE portfolios
SET balance = balance + v_total_value
WHERE id = v_portfolio_id;
-- reduce holding quantity
UPDATE portfolio_holdings
SET quantity = quantity - NEW.quantity
WHERE portfolio_id = v_portfolio_id
AND stock_id = NEW.stock_id;
-- remove holding if quantity reaches zero
DELETE FROM portfolio_holdings
WHERE portfolio_id = v_portfolio_id
AND stock_id = NEW.stock_id
AND quantity <= 0;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_sync_portfolio_on_transaction
AFTER INSERT ON transactions
FOR EACH ROW
EXECUTE FUNCTION sync_portfolio_on_transaction();
Погледи (Views)
Поглед за преглед на вкупната вредност на портфолиото за секој корисник, со тековните пазарни цени.
CREATE OR REPLACE VIEW portfolio_summary AS
SELECT
u.id AS user_id,
u.username,
p.id AS portfolio_id,
p.balance AS cash_balance,
COALESCE(SUM(ph.quantity * s.current_price), 0) AS holdings_market_value,
COALESCE(SUM(ph.quantity * ph.avg_price), 0) AS holdings_cost_basis,
COALESCE(SUM(ph.quantity * s.current_price)
- SUM(ph.quantity * ph.avg_price), 0) AS unrealized_pnl,
p.balance + COALESCE(SUM(ph.quantity * s.current_price), 0) AS total_portfolio_value,
COUNT(DISTINCT ph.stock_id) AS num_stocks_held
FROM users u
JOIN portfolios p ON u.id = p.user_id
LEFT JOIN portfolio_holdings ph ON p.id = ph.portfolio_id
LEFT JOIN stock s ON ph.stock_id = s.id
WHERE u.role = 'USER'
GROUP BY u.id, u.username, p.id, p.balance
ORDER BY total_portfolio_value DESC;
Валидација на Watchlist Alert прагови
Опис на барањата за податочни ограничувања
Системот мора да обезбеди дека:
- Ако се зададени и двата прага (price_above и price_below), price_above мора да биде строго поголем од price_below; спротивното е логички невозможно
- Барем еден од двата прагови мора да биде зададен (не смеат и двата да бидат NULL истовремено)
- Праговите мора да бидат позитивни броеви (> 0)
Имплементација
Тригери
BEFORE INSERT OR UPDATE тригер на watchlist кој ги валидира alert праговите.
CREATE OR REPLACE FUNCTION validate_watchlist_thresholds()
RETURNS TRIGGER AS $$
BEGIN
-- 1one threshold must be set
IF NEW.price_above IS NULL AND NEW.price_below IS NULL THEN
RAISE EXCEPTION
'Watchlist entry must have at least one threshold set (price_above or price_below)';
END IF;
-- thresholds must be positive
IF NEW.price_above IS NOT NULL AND NEW.price_above <= 0 THEN
RAISE EXCEPTION 'price_above must be a positive value, got %', NEW.price_above;
END IF;
IF NEW.price_below IS NOT NULL AND NEW.price_below <= 0 THEN
RAISE EXCEPTION 'price_below must be a positive value, got %', NEW.price_below;
END IF;
-- price_above --- greater than price_below when both are set
IF NEW.price_above IS NOT NULL AND NEW.price_below IS NOT NULL THEN
IF NEW.price_above <= NEW.price_below THEN
RAISE EXCEPTION
'price_above (%) must be strictly greater than price_below (%)',
NEW.price_above, NEW.price_below;
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_validate_watchlist_thresholds
BEFORE INSERT OR UPDATE ON watchlist
FOR EACH ROW
EXECUTE FUNCTION validate_watchlist_thresholds();
Функции / Stored Procedures
Функција која ги враќа сите watchlist записи за кои тековната цена на акцијата го надминала или паднала под зададениот праг (активни alerts).
CREATE OR REPLACE FUNCTION get_triggered_watchlist_alerts()
RETURNS TABLE (
watchlist_id BIGINT,
user_id BIGINT,
username TEXT,
stock_id BIGINT,
symbol TEXT,
current_price DOUBLE PRECISION,
price_above DOUBLE PRECISION,
price_below DOUBLE PRECISION,
alert_type TEXT
) AS $$
BEGIN
RETURN QUERY
SELECT
w.id AS watchlist_id,
u.id AS user_id,
u.username::TEXT,
s.id AS stock_id,
s.symbol::TEXT,
s.current_price,
w.price_above,
w.price_below,
CASE
WHEN w.price_above IS NOT NULL
AND s.current_price >= w.price_above THEN 'PRICE_ABOVE_TRIGGERED'
WHEN w.price_below IS NOT NULL
AND s.current_price <= w.price_below THEN 'PRICE_BELOW_TRIGGERED'
END::TEXT AS alert_type
FROM watchlist w
JOIN stock s ON w.stock_id = s.id
JOIN users u ON w.user_id = u.id
WHERE
(w.price_above IS NOT NULL AND s.current_price >= w.price_above)
OR
(w.price_below IS NOT NULL AND s.current_price <= w.price_below)
ORDER BY u.id, s.symbol;
END;
$$ LANGUAGE plpgsql;
Погледи (Views)
Поглед за преглед на сите watchlist записи со тековните цени и статус на alert.
CREATE OR REPLACE VIEW watchlist_with_alert_status AS
SELECT
w.id AS watchlist_id,
u.id AS user_id,
u.username,
s.id AS stock_id,
s.symbol,
s.name AS stock_name,
s.current_price,
w.price_above,
w.price_below,
CASE
WHEN w.price_above IS NOT NULL
AND s.current_price >= w.price_above THEN 'PRICE_ABOVE_TRIGGERED'
WHEN w.price_below IS NOT NULL
AND s.current_price <= w.price_below THEN 'PRICE_BELOW_TRIGGERED'
ELSE 'WATCHING'
END AS alert_status
FROM watchlist w
JOIN stock s ON w.stock_id = s.id
JOIN users u ON w.user_id = u.id
ORDER BY alert_status DESC, u.username, s.symbol;
Управување со OAuth токени (OAuth Token Expiry Management)
Опис на барањата за податочни ограничувања
Системот мора да обезбеди дека:
- Не смее да се INSERT-ира нов OAuth токен за корисник кој веќе има активен (неистечен) токен за ист провајдер
- При обид за користење на OAuth токен, системот мора да провери дали е истечен (expires_at < NOW()) и да фрли грешка ако е
- Истечените токени треба автоматски да се чистат преку background job за да не се трупаат во базата
Имплементација
Тригери
BEFORE INSERT тригер на oauth_pending_links кој спречува дупликат активни токени за ист корисник и провајдер.
CREATE OR REPLACE FUNCTION validate_oauth_token_before_insert()
RETURNS TRIGGER AS $$
DECLARE
v_existing_count INTEGER;
BEGIN
-- check for existing non-expired token for same user and provider
SELECT COUNT(*) INTO v_existing_count
FROM oauth_pending_links
WHERE user_id = NEW.user_id
AND provider = NEW.provider
AND expires_at > NOW();
IF v_existing_count > 0 THEN
RAISE EXCEPTION
'User % already has an active OAuth token for provider %. Wait for it to expire or revoke it first.',
NEW.user_id, NEW.provider;
END IF;
-- token is not created already expired
IF NEW.expires_at <= NEW.created_at THEN
RAISE EXCEPTION
'expires_at (%) must be after created_at (%)',
NEW.expires_at, NEW.created_at;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_validate_oauth_token
BEFORE INSERT ON oauth_pending_links
FOR EACH ROW
EXECUTE FUNCTION validate_oauth_token_before_insert();
Функции / Stored Procedures
Функција за верификација на OAuth токен проверува дали токенот постои и не е истечен, и го враќа корисничкото id и провајдерот.
CREATE OR REPLACE FUNCTION verify_oauth_token(p_token VARCHAR(255))
RETURNS TABLE (
user_id BIGINT,
provider TEXT,
email TEXT,
is_valid BOOLEAN,
reason TEXT
) AS $$
DECLARE
v_record oauth_pending_links%ROWTYPE;
BEGIN
SELECT * INTO v_record
FROM oauth_pending_links
WHERE token = p_token;
IF v_record.token IS NULL THEN
RETURN QUERY SELECT
NULL::BIGINT, NULL::TEXT, NULL::TEXT,
FALSE, 'Token does not exist';
RETURN;
END IF;
IF v_record.expires_at < NOW() THEN
RETURN QUERY SELECT
v_record.user_id,
v_record.provider::TEXT,
v_record.email::TEXT,
FALSE, 'Token has expired';
RETURN;
END IF;
RETURN QUERY SELECT
v_record.user_id,
v_record.provider::TEXT,
v_record.email::TEXT,
TRUE, 'Token is valid';
END;
$$ LANGUAGE plpgsql;
Процедура за рачно чистење на истечени OAuth токени (може да се повика и преку background job).
CREATE OR REPLACE PROCEDURE cleanup_expired_oauth_tokens()
LANGUAGE plpgsql AS $$
DECLARE
v_deleted_count INTEGER;
BEGIN
DELETE FROM oauth_pending_links
WHERE expires_at < NOW();
GET DIAGNOSTICS v_deleted_count = ROW_COUNT;
RAISE NOTICE 'Cleaned up % expired OAuth token(s) at %', v_deleted_count, NOW();
END;
$$;
Погледи (Views)
Поглед за преглед на сите OAuth токени со нивниот статус (активен / истечен).
CREATE OR REPLACE VIEW oauth_tokens_status AS
SELECT
o.token,
o.user_id,
u.username,
u.email AS user_email,
o.provider,
o.email AS oauth_email,
o.created_at,
o.expires_at,
CASE
WHEN o.expires_at > NOW() THEN 'ACTIVE'
ELSE 'EXPIRED'
END AS token_status,
EXTRACT(EPOCH FROM (o.expires_at - NOW()))::INTEGER AS seconds_until_expiry
FROM oauth_pending_links o
JOIN users u ON o.user_id = u.id
ORDER BY o.expires_at DESC;
Note:
See TracWiki
for help on using the wiki.
