Ignore:
Timestamp:
09/24/26 17:43:19 (6 days ago)
Author:
Stefan <trsunovstefan@…>
Branches:
main
Children:
0cee8ec
Parents:
a531b45
Message:

Wiki docs, phase 6 and phase 7 added

File:
1 edited

Legend:

Unmodified
Added
Removed

  • docs/P4-Prototype/UseCase0006Implementation.md

    ra531b45 ref1c1c7  
    1 # Use-case 0006 Implementation — Portfolio & transactions
     1# Use-case 0006 Implementation - View portfolio and transaction history
    22
    3 **Initiating actor:** Trader. **Source files:** `server/portfolio.go` (`ShowPortfolio`), `server/account.go` (`ShowTransactions`, `ShowBalance`).
     3**Initiating actor:** Trader
    44
    5 ## Scenario (implemented)
     5**Other actors:** —
     6
     7A logged-in Trader inspects the current state of their account. The portfolio view
     8lists every cryptocurrency the Trader holds with the quantity (also split into the
     9part reserved by open sell orders and the part that is free to sell), the average
     10buy price, the current market price, the market value and the unrealised
     11profit/loss, followed by a totals row and a cash summary (cash available, portfolio
     12value, net worth). The transaction history lists the Trader's last 20 ledger
     13entries — deposits, buys and sells — newest first. Both are read-only: nothing in
     14the database is changed.
     15
     16Original use-case description (P3): [UseCase0006](../P3-UseCaseModel/UseCase0006.md).
     17Implementation: [`server/portfolio.go`](../../server/portfolio.go), function
     18`ShowPortfolio`, and [`server/account.go`](../../server/account.go), function
     19`ShowTransactions`.
     20
     21Precondition: the Trader is logged in ([UseCase0002](UseCase0002Implementation.md)).
     22The run below is alice's, after she bought 0.01 BTC
     23([UseCase0004](UseCase0004Implementation.md)) and sold 0.2 ETH
     24([UseCase0005](UseCase0005Implementation.md)) on top of the seed data (0.5 ETH,
     258250.00 USD cash).
     26
     27## Scenario
    628
    729### Portfolio
    830
    9 1. **User** chooses `[6] View portfolio`.
    10 2. **System** runs:
     311. **Trader** chooses `[6] View portfolio` in the authenticated menu (types `6`).
     32   The menu is the one shown in [UseCase0002](UseCase0002Implementation.md), step 7.
     332. **System** queries the `v_portfolio` view (`$1` = user id):
    1134
    1235   ```sql
    13    SELECT symbol, quantity,
    14           COALESCE(reserved_quantity,  0),
     36   SELECT symbol,
     37          quantity,
     38          COALESCE(reserved_quantity, 0),
    1539          COALESCE(available_quantity, quantity),
    16           COALESCE(avg_price,      0),
    17           COALESCE(current_price,  0),
    18           COALESCE(market_value,   0),
     40          COALESCE(avg_price, 0),
     41          COALESCE(current_price, 0),
     42          COALESCE(market_value, 0),
    1943          COALESCE(unrealized_pnl, 0)
    2044     FROM v_portfolio
    2145    WHERE user_id = $1 AND quantity > 0
    22     ORDER BY symbol;
     46    ORDER BY symbol
    2347   ```
    24 3. **System** renders a table with a totals row, then prints the cash summary:
     48
     493. **System** displays the rows and a `TOTAL` row (sums of the value and P/L columns,
     50   computed in Go), then reads the cash balance for the summary (`$1` = user id):
    2551
    2652   ```sql
    27    SELECT available_balance, invested_balance FROM users WHERE id = $1;
     53   SELECT available_balance, invested_balance FROM users WHERE id = $1
    2854   ```
    2955
    30    ![screenshot: portfolio view](screenshots/uc0006_portfolio.png)
     56   and prints `Cash available` (= `available_balance`), `Portfolio value`
     57   (= the total market value) and `Net worth` (= their sum).
    3158
    32 ### Verified run
     59   The screenshot shows the result of steps 2–3. The table is wider than the
     60   terminal window, so each long line wraps and the header row has scrolled out of
     61   the top of the window; the complete output of this run is reproduced below it.
    3362
    34 Re-run 2026-09-16 against PostgreSQL 16 with the seed data (`data_load.sql`).
    35 Immediately after login, alice's portfolio prints (now with the
    36 `Reserved`/`Available` columns from `holdings.reserved_quantity`):
     63   ![UC0006 portfolio: holdings, P/L and cash summary](screenshots/uc0006_portfolio.png)
    3764
    38 ```
    39   Symbol        Quantity      Reserved     Available         Avg buy         Current           Value  Unrealised P/L
    40   ------------------------------------------------------------------------------------------------------------------
    41   ETH             0.5000        0.0000        0.5000     3500.000000     3520.000000       1760.0000        +10.0000
    42   ------------------------------------------------------------------------------------------------------------------
    43   TOTAL                                                                                    1760.0000        +10.0000
     65   ```
     66     Symbol        Quantity      Reserved     Available         Avg buy         Current           Value  Unrealised P/L
     67     ------------------------------------------------------------------------------------------------------------------
     68     BTC             0.0100        0.0000        0.0100    67140.000000    67140.000000        671.4000         +0.0000
     69     ETH             0.3000        0.0000        0.3000     3500.000000     3520.000000       1056.0000         +6.0000
     70     ------------------------------------------------------------------------------------------------------------------
     71     TOTAL                                                                                    1727.4000         +6.0000
    4472
    45   Cash available : 8250.0000 USD
    46   Portfolio value: 1760.0000 USD
    47   Net worth      : 10010.0000 USD
    48 ```
     73     Cash available : 8282.6000 USD
     74     Portfolio value: 1727.4000 USD
     75     Net worth      : 10010.0000 USD
     76   ```
    4977
    50 `Reserved` is 0.0000 here because nothing is mid-sell; see
    51 [UseCase0005Implementation](UseCase0005Implementation.md) for a portfolio
    52 snapshot taken with crypto actually reserved.
     78   Checking the figures: ETH is 0.5 − 0.2 = 0.3 at an average buy price of 3500 and
     79   a current price of 3520, so value 1056.00 and P/L 0.3 × 20 = +6.00; BTC was just
     80   bought at the current price 67140, so its P/L is 0. Cash is
     81   8250.00 − 671.40 (buy) + 704.00 (sell) = 8282.60. `Reserved` is 0.0000 for both
     82   because the market orders executed immediately — a quantity is reserved only
     83   while a sell order is still open.
    5384
    5485### Transaction history
    5586
    56 1. **User** chooses `[7] View transaction history`.
    57 2. **System** runs:
     871. **Trader** chooses `[7] View transaction history` in the authenticated menu
     88   (types `7`).
     892. **System** queries the last 20 ledger entries of the Trader (`$1` = user id) and
     90   prints them, newest first (the time is shown as the first 19 characters of
     91   `created_at`):
    5892
    5993   ```sql
    … …  
    6296    WHERE user_id = $1
    6397    ORDER BY created_at DESC
    64     LIMIT 20;
     98    LIMIT 20
    6599   ```
    66100
    67    ![screenshot: transaction history](screenshots/uc0006_history.png)
     101   The screenshot shows steps 1–2: the choice `7` and the four ledger rows of this
     102   run — the sell of 0.2 ETH (+704.0000 USD), the buy of 0.01 BTC (−671.4000 USD),
     103   and the two seed rows, the initial deposit of 10000.0000 USD and the seed buy of
     104   0.5 ETH (−1750.0000 USD). Buys are stored with a negative amount, deposits and
     105   sells with a positive one. The two seed rows were inserted by `data_load.sql` in
     106   one statement and have the same `created_at`, so their relative order is not
     107   determined by the `ORDER BY`.
     108
     109   ![UC0006 transaction history: last ledger entries](screenshots/uc0006_history.png)
     110
     111All statements run on the `project` schema (the connection sets
     112`search_path=project,public`).
     113
     114### Reference — how `v_portfolio` is defined
     115
     116From `server/db/schema_creation.sql`:
     117
     118```sql
     119CREATE OR REPLACE VIEW project.v_portfolio AS
     120SELECT h.user_id,
     121       c.symbol,
     122       h.quantity,
     123       h.reserved_quantity,
     124       (h.quantity - h.reserved_quantity) AS available_quantity,
     125       h.avg_price,
     126       lp.price                           AS current_price,
     127       (h.quantity * lp.price)            AS market_value,
     128       (h.quantity * (lp.price - h.avg_price)) AS unrealized_pnl
     129FROM   project.holdings h
     130JOIN   project.crypto   c ON c.id = h.crypto_id
     131LEFT   JOIN project.markets m ON m.crypto_id = c.id AND m.quote_currency = 'USD'
     132LEFT   JOIN project.v_latest_prices lp ON lp.market_id = m.id;
     133```
     134
     135## How to reproduce
     136
     137```sh
     138./eduberza -init
     139./eduberza
     140# [2] Login: alice / test123
     141# [4] buy 0.01 BTC, [5] sell 0.2 ETH   (UseCase0004 / UseCase0005)
     142# [6] View portfolio
     143# [7] View transaction history
     144```
     145
     146Both screenshots come from one real run (portfolio and history taken right after
     147the buy and sell runs).
Note: See TracChangeset for help on using the changeset viewer.