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;