Changes between Initial Version and Version 1 of UseCase0006Implementation

Timestamp:

09/24/26 14:03:20 (4 days ago)

Author:

231285

Comment:

--

UseCase0006Implementation

@@ +1 @@
+= Use-case 0006 Implementation - View portfolio and transaction history =
+
+'''Initiating actor:''' Trader
+
+'''Other actors:''' —
+
+A logged-in Trader inspects the current state of their account. The portfolio view
+lists every cryptocurrency the Trader holds with the quantity (also split into the
+part reserved by open sell orders and the part that is free to sell), the average
+buy price, the current market price, the market value and the unrealised
+profit/loss, followed by a totals row and a cash summary (cash available, portfolio
+value, net worth). The transaction history lists the Trader's last 20 ledger
+entries — deposits, buys and sells — newest first. Both are read-only: nothing in
+the database is changed.
+
+Original use-case description (P3): [wiki:UseCase0006].
+Implementation: `server/portfolio.go`, function `ShowPortfolio`, and
+`server/account.go`, function `ShowTransactions` (the code is shown at the end of
+this page).
+
+Precondition: the Trader is logged in ([wiki:UseCase0002Implementation UseCase0002]).
+The run below is alice's, after she bought 0.01 BTC
+([wiki:UseCase0004Implementation UseCase0004]) and sold 0.2 ETH
+([wiki:UseCase0005Implementation UseCase0005]) on top of the seed data (0.5 ETH,
+8250.00 USD cash).
+
+== Scenario ==
+
+=== Portfolio ===
+
+ 1. '''Trader''' chooses `[6] View portfolio` in the authenticated menu (types `6`).
+    The menu is the one shown in [wiki:UseCase0002Implementation UseCase0002], step 7.
+ 2. '''System''' queries the `v_portfolio` view (`$1` = user id):
+
+{{{
+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 v_portfolio
+ WHERE user_id = $1 AND quantity > 0
+ ORDER BY symbol
+}}}
+
+ 3. '''System''' displays the rows and a `TOTAL` row (sums of the value and P/L columns,
+    computed in Go), then reads the cash balance for the summary (`$1` = user id):
+
+{{{
+SELECT available_balance, invested_balance FROM users WHERE id = $1
+}}}
+
+and prints `Cash available` (= `available_balance`), `Portfolio value`
+(= the total market value) and `Net worth` (= their sum).
+
+The screenshot shows the result of steps 2–3. The table is wider than the
+terminal window, so each long line wraps and the header row has scrolled out of
+the top of the window; the complete output of this run is reproduced below it.
+
+[[Image(uc0006_portfolio.png)]]
+
+{{{
+  Symbol        Quantity      Reserved     Available         Avg buy         Current           Value  Unrealised P/L
+  ------------------------------------------------------------------------------------------------------------------
+  BTC             0.0100        0.0000        0.0100    67140.000000    67140.000000        671.4000         +0.0000
+  ETH             0.3000        0.0000        0.3000     3500.000000     3520.000000       1056.0000         +6.0000
+  ------------------------------------------------------------------------------------------------------------------
+  TOTAL                                                                                    1727.4000         +6.0000
+
+  Cash available : 8282.6000 USD
+  Portfolio value: 1727.4000 USD
+  Net worth      : 10010.0000 USD
+}}}
+
+Checking the figures: ETH is 0.5 − 0.2 = 0.3 at an average buy price of 3500 and
+a current price of 3520, so value 1056.00 and P/L 0.3 × 20 = +6.00; BTC was just
+bought at the current price 67140, so its P/L is 0. Cash is
+8250.00 − 671.40 (buy) + 704.00 (sell) = 8282.60. `Reserved` is 0.0000 for both
+because the market orders executed immediately — a quantity is reserved only
+while a sell order is still open.
+
+=== Transaction history ===
+
+ 1. '''Trader''' chooses `[7] View transaction history` in the authenticated menu
+    (types `7`).
+ 2. '''System''' queries the last 20 ledger entries of the Trader (`$1` = user id) and
+    prints them, newest first (the time is shown as the first 19 characters of
+    `created_at`):
+
+{{{
+SELECT created_at, type, amount, currency, COALESCE(description, '')
+  FROM transactions
+ WHERE user_id = $1
+ ORDER BY created_at DESC
+ LIMIT 20
+}}}
+
+The screenshot shows steps 1–2: the choice `7` and the four ledger rows of this
+run — the sell of 0.2 ETH (+704.0000 USD), the buy of 0.01 BTC (−671.4000 USD),
+and the two seed rows, the initial deposit of 10000.0000 USD and the seed buy of
+0.5 ETH (−1750.0000 USD). Buys are stored with a negative amount, deposits and
+sells with a positive one. The two seed rows were inserted by `data_load.sql` in
+one statement and have the same `created_at`, so their relative order is not
+determined by the `ORDER BY`.
+
+[[Image(uc0006_history.png)]]
+
+All statements run on the `project` schema (the connection sets
+`search_path=project,public`).
+
+=== Reference — how v_portfolio is defined ===
+
+From `server/db/schema_creation.sql`:
+
+{{{
+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;
+}}}
+
+== How to reproduce ==
+
+{{{
+./eduberza -init
+./eduberza
+# [2] Login: alice / test123
+# [4] buy 0.01 BTC, [5] sell 0.2 ETH   (UseCase0004 / UseCase0005)
+# [6] View portfolio
+# [7] View transaction history
+}}}
+
+Both screenshots come from one real run (portfolio and history taken right after
+the buy and sell runs).
+
+== Source code ==
+
+`server/portfolio.go` — `ShowPortfolio`:
+
+{{{
+// ShowPortfolio - UC0006
+// Uses the v_portfolio view to list holdings with current market value and P&L.
+func ShowPortfolio(s *Session) {
+        rows, err := db.DB.Query(
+                `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 v_portfolio
+                  WHERE user_id = $1 AND quantity > 0
+                  ORDER BY symbol`,
+                s.UserID,
+        )
+        if err != nil {
+                fmt.Println("Error:", err)
+                return
+        }
+        defer rows.Close()
+
+        header := fmt.Sprintf("  %-8s  %12s  %12s  %12s  %14s  %14s  %14s  %14s",
+                "Symbol", "Quantity", "Reserved", "Available", "Avg buy", "Current", "Value", "Unrealised P/L")
+        fmt.Println()
+        fmt.Println(header)
+        fmt.Println("  " + strings.Repeat("-", len(header)-2))
+
+        var totalValue, totalPnL float64
+        empty := true
+        for rows.Next() {
+                var sym string
+                var qty, reserved, avail, avg, cur, val, pnl float64
+                if err := rows.Scan(&sym, &qty, &reserved, &avail, &avg, &cur, &val, &pnl); err != nil {
+                        fmt.Println("scan error:", err)
+                        return
+                }
+                fmt.Printf("  %-8s  %12.4f  %12.4f  %12.4f  %14.6f  %14.6f  %14.4f  %+14.4f\n",
+                        sym, qty, reserved, avail, avg, cur, val, pnl)
+                totalValue += val
+                totalPnL += pnl
+                empty = false
+        }
+        if empty {
+                fmt.Println("  (no holdings yet)")
+                return
+        }
+        fmt.Println("  " + strings.Repeat("-", len(header)-2))
+        fmt.Printf("  %-8s  %12s  %12s  %12s  %14s  %14s  %14.4f  %+14.4f\n",
+                "TOTAL", "", "", "", "", "", totalValue, totalPnL)
+
+        // cash summary
+        var avail, invested float64
+        _ = db.DB.QueryRow(
+                `SELECT available_balance, invested_balance FROM users WHERE id = $1`,
+                s.UserID,
+        ).Scan(&avail, &invested)
+        fmt.Printf("\n  Cash available : %.4f USD\n", avail)
+        fmt.Printf("  Portfolio value: %.4f USD\n", totalValue)
+        fmt.Printf("  Net worth      : %.4f USD\n", avail+totalValue)
+}
+}}}
+
+`server/account.go` — `ShowTransactions`:
+
+{{{
+// ShowTransactions lists the last 20 ledger entries for the user.
+func ShowTransactions(s *Session) {
+        rows, err := db.DB.Query(
+                `SELECT created_at, type, amount, currency, COALESCE(description, '')
+                   FROM transactions
+                  WHERE user_id = $1
+                  ORDER BY created_at DESC
+                  LIMIT 20`,
+                s.UserID,
+        )
+        if err != nil {
+                fmt.Println("Error:", err)
+                return
+        }
+        defer rows.Close()
+
+        fmt.Println()
+        fmt.Printf("  %-20s  %-8s  %12s  %-3s  %s\n", "When", "Type", "Amount", "Cur", "Description")
+        fmt.Println("  " + strings.Repeat("-", 70))
+        for rows.Next() {
+                var when, typ, cur, desc string
+                var amt float64
+                if err := rows.Scan(&when, &typ, &amt, &cur, &desc); err != nil {
+                        fmt.Println("scan error:", err)
+                        return
+                }
+                fmt.Printf("  %-20s  %-8s  %12.4f  %-3s  %s\n", when[:19], typ, amt, cur, desc)
+        }
+}
+}}}