Changes between Version 2 and Version 3 of dbdevelopment

Timestamp:

03/01/26 23:48:08 (2 weeks ago)

Author:

231020

Comment:

--

dbdevelopment

@@ -12 +12 @@
 
 === Имплементација ===
+
+==== Прилагодени домени (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;
+}}}