source: docs/P3-UseCaseModel/wiki/UseCase0006.md@ ef1c1c7

= 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 [wiki: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;
}}}