Changes between Initial Version and Version 1 of UC0006

Timestamp:

09/24/26 13:50:38 (4 days ago)

Author:

231285

Comment:

--

UC0006

@@ +1 @@
+= Use-case 0006 — View portfolio and transaction history =
+
+'''Initiating actor:''' Trader
+
+'''Other actors:''' —
+
+A Trader inspects their current holdings, unrealised P/L, cash balance and recent ledger.
+
+== Scenario ==
+
+=== Portfolio ===
+
+ 1. Trader chooses "View portfolio".
+ 2. System queries the `v_portfolio` view:
+
+{{{
+SELECT symbol,
+       quantity,
+       COALESCE(reserved_quantity,  0),
+       COALESCE(available_quantity, quantity),
+       COALESCE(avg_price,      0),
+       COALESCE(current_price,  0),
+       COALESCE(market_value,   0),
+       COALESCE(unrealized_pnl, 0)
+  FROM project.v_portfolio
+ WHERE user_id = $1
+   AND quantity > 0
+ ORDER BY symbol;
+}}}
+
+`reserved_quantity` is the amount committed to the Trader's own open sell
+orders (see UseCase0005); `available_quantity` is what is
+actually free to sell right now.
+
+ 3. System displays the rows and a computed summary:
+
+{{{
+SELECT available_balance, invested_balance
+  FROM project.users
+ WHERE id = $1;
+}}}
+
+=== Transaction history ===
+
+ 1. Trader chooses "View transaction history".
+ 2. System queries the last 20 ledger entries:
+
+{{{
+SELECT created_at, type, amount, currency, COALESCE(description, '')
+  FROM project.transactions
+ WHERE user_id = $1
+ ORDER BY created_at DESC
+ LIMIT 20;
+}}}
+
+=== Reference — how `v_portfolio` is defined ===
+
+{{{
+CREATE OR REPLACE VIEW project.v_portfolio AS
+SELECT h.user_id,
+       c.symbol,
+       h.quantity,
+       h.reserved_quantity,
+       (h.quantity - h.reserved_quantity)      AS available_quantity,
+       h.avg_price,
+       lp.price                                AS current_price,
+       (h.quantity * lp.price)                 AS market_value,
+       (h.quantity * (lp.price - h.avg_price)) AS unrealized_pnl
+  FROM project.holdings h
+  JOIN project.crypto   c ON c.id = h.crypto_id
+  LEFT JOIN project.markets m
+         ON m.crypto_id = c.id AND m.quote_currency = 'USD'
+  LEFT JOIN project.v_latest_prices lp
+         ON lp.market_id = m.id;
+}}}