Changes between Initial Version and Version 1 of UseCase0004Implementation

Timestamp:

09/24/26 14:02:48 (4 days ago)

Author:

231285

Comment:

--

UseCase0004Implementation

@@ +1 @@
+= Use-case 0004 Implementation - Place market BUY order =
+
+'''Initiating actor:''' Trader
+
+'''Other actors:''' Market Simulator (indirect — supplies the current price via `market_trades`).
+
+A logged-in Trader buys a crypto asset at the current market price. The Trader never
+types a symbol or an identifier: the system lists the active markets with their last
+price, numbered, and the Trader picks one by its number and then enters only the
+quantity. The system checks that the Trader has enough available cash for
+quantity × price and then, in one database transaction, records the order, moves the
+cash from available to invested, adds the crypto to the Trader's holding (recomputing
+the weighted-average entry price), writes a ledger entry and a market trade, and marks
+the order executed. The operation touches five tables (`orders`, `users`, `holdings`,
+`transactions`, `market_trades`) and either all of it succeeds or all of it is rolled
+back.
+
+Original use-case description (P3): [wiki:UseCase0004].
+Implementation: `server/trade.go`, function
+`PlaceOrder(s, "buy")` (with `upsertHoldingOnBuy` in the same file), which calls
+`ChooseMarket`, `ListMarkets`, `pickNumber` and `LatestPrice` from
+`server/market.go` (the code is shown at the end of this page).
+
+All statements run on the `project` schema: the connection sets
+`search_path=project,public` (`server/db/db.go`), so `orders` means `project.orders`.
+The SQL below is copied from the Go code; only the Go source indentation is removed,
+a `;` is added after each statement of the transaction, and `--` comments say what
+each `$n` placeholder is bound to.
+
+The run shown is user `alice` on the seed data (available 8250.00 USD, holding
+0.5 ETH bought at 3500), buying 0.01 BTC.
+
+== Scenario ==
+
+ 1. '''Trader''' chooses `[4] Place market BUY order` in the authenticated menu (types `4`).
+ 2. '''System''' prints `-- Place market buy order --` and lists all active markets,
+    numbered, with their last price (`ListMarkets`, called by `ChooseMarket`):
+
+{{{
+SELECT m.id, c.id, c.symbol, m.quote_currency,
+       COALESCE(lp.price, 0) AS price
+  FROM markets m
+  JOIN crypto  c  ON c.id = m.crypto_id
+  LEFT JOIN v_latest_prices lp ON lp.market_id = m.id
+ WHERE m.is_active = true
+ ORDER BY c.symbol
+}}}
+
+The rows are printed in this order as `1 ADA`, `2 BTC`, `3 DOGE`, `4 ETH`, `5 SOL`;
+Go keeps each row's market id and crypto id in memory, so the Trader only ever sees
+and types the list number. The system then asks `Market #:`.
+
+[[Image(uc0004_1_2_markets.png)]]
+
+ 3. '''Trader''' picks the market by its number in the list: `2` (BTC/USD).
+ 4. '''System''' takes the market id and crypto id of row 2 from the list (no further
+    lookup by symbol) and reads the latest price of that market (`LatestPrice`;
+    `$1` = the chosen market's id):
+
+{{{
+SELECT price FROM v_latest_prices WHERE market_id = $1
+}}}
+
+It prints `Latest price for BTC/USD = 67140.000000` and asks `Quantity:`.
+
+[[Image(uc0004_3_4_price.png)]]
+
+ 5. '''Trader''' enters the quantity `0.01`.
+ 6. '''System''' computes in Go notional = quantity × price = 0.01 × 67140 = 671.40 and
+    passes it to SQL as a parameter. It then runs one database transaction; the
+    statements below are in exactly the order `PlaceOrder` executes them for a buy:
+
+{{{
+BEGIN;
+
+-- (a) record the order as 'open' — no trade has happened yet.
+--     $1 = user id, $2 = market id, $3 = side (the Go variable side = 'buy'),
+--     $4 = quantity (0.01), $5 = price (67140); the returned id is kept in Go.
+INSERT INTO orders (user_id, market_id, side, type, status, quantity, price)
+ VALUES ($1, $2, $3, 'market', 'open', $4, $5)
+ RETURNING id;
+
+-- (b) lock the user's row and read the available cash. $1 = user id.
+--     Go compares it with the notional; if it is smaller -> alternate flow 6a.
+SELECT available_balance FROM users WHERE id = $1 FOR UPDATE;
+
+-- (c) move the notional from available to invested cash.
+--     $1 = notional (671.40), $2 = user id.
+UPDATE users
+    SET available_balance = available_balance - $1,
+        invested_balance  = invested_balance  + $1,
+        updated_at        = now()
+  WHERE id = $2;
+
+-- (d) add the crypto to the holding (upsertHoldingOnBuy), recomputing the
+--     weighted-average entry price in the database. Every SET expression sees
+--     the pre-update row, so holdings.quantity is still the old quantity.
+--     $1 = user id, $2 = crypto id, $3 = quantity (0.01), $4 = price (67140).
+INSERT INTO holdings (user_id, crypto_id, quantity, avg_price, updated_at)
+ VALUES ($1, $2, $3, $4, now())
+ ON CONFLICT (user_id, crypto_id) DO UPDATE
+    SET avg_price  = (holdings.quantity * holdings.avg_price
+                       + EXCLUDED.quantity * EXCLUDED.avg_price)
+                     / (holdings.quantity + EXCLUDED.quantity),
+        quantity   = holdings.quantity + EXCLUDED.quantity,
+        updated_at = now();
+
+-- (e) ledger entry. $1 = user id, $2 = -notional (-671.40), $3 = order id from (a),
+--     $4 = description built in Go: 'Market buy 0.0100 BTC @ 67140.000000'.
+INSERT INTO transactions (user_id, type, amount, currency, related_order, description)
+ VALUES ($1, 'buy', $2, 'USD', $3, $4);
+
+-- (f) record the resulting market trade.
+--     $1 = market id, $2 = price, $3 = quantity, $4 = side ('buy').
+INSERT INTO market_trades (market_id, executed_at, price, quantity, side, source)
+ VALUES ($1, now(), $2, $3, $4, 'user');
+
+-- (g) settle the order itself — it has now actually been filled. $1 = order id.
+UPDATE orders SET status = 'executed', executed_at = now() WHERE id = $1;
+
+COMMIT;
+}}}
+
+A buy never reserves crypto (only a sell does, see
+[wiki:UseCase0005Implementation UseCase0005]), so `holdings.reserved_quantity` is not
+touched and stays 0.
+
+ 7. '''System''' confirms
+    `Order executed: buy 0.0100 BTC @ 67140.000000 (notional 671.4000 USD)` and shows
+    the authenticated menu again.
+
+The screenshot shows steps 5–7: the entered quantity, the confirmation and the menu.
+
+[[Image(uc0004_5_7_executed.png)]]
+
+=== Verification — portfolio after the buy ===
+
+Right after the buy the Trader chooses `[6] View portfolio`
+([wiki:UseCase0006Implementation UseCase0006]). It shows the new holding
+`BTC 0.0100` with average buy price and current price 67140.000000 (value 671.4000),
+the unchanged `ETH 0.5000` (average 3500, current 3520, unrealised P/L +10.0000),
+`Cash available : 7578.6000 USD` (= 8250.00 − 671.40), portfolio value 2431.4000 and
+net worth 10010.0000 USD. The `Reserved` column is 0.0000 on both rows — a buy never
+reserves anything.
+
+[[Image(uc0004_verify_portfolio.png)]]
+
+=== Alternate flow 6a — insufficient funds ===
+
+User `charlie` (seed data: 2500.00 USD available, no crypto) chooses `[4]`, picks
+market `2` (BTC, 67140.000000) and enters quantity `1`. Go computes the notional
+67140.00. In the transaction, statement (a) inserts the `open` order and statement (b)
+`SELECT available_balance FROM users WHERE id = $1 FOR UPDATE` returns 2500.00, which
+is less than the notional. `PlaceOrder` prints
+`Insufficient funds: need 67140.0000, have 2500.0000` and returns without running
+(c)–(g); the deferred `tx.Rollback()` undoes statement (a), so no order, no ledger
+entry and no balance change is left behind (after this run charlie has no row in
+`orders` and still 2500.00 USD available). The authenticated menu is shown again.
+
+[[Image(uc0004_6a_insufficient.png)]]
+
+=== Alternate flow 3a — number not in the list ===
+
+If in step 3 the Trader enters something that is not a number from 1 to the number of
+listed markets, `pickNumber` prints `Invalid choice, enter a number from 1 to 5.`, no
+further SQL is run and the authenticated menu is shown again (the same check is shown
+in [wiki:UseCase0007Implementation UseCase0007], alternate flow 12a). Likewise, a
+quantity that is not a positive number in step 5 prints `Invalid quantity.` before any
+transaction is opened.
+
+== Source code ==
+
+`server/trade.go` — `PlaceOrder` (buy and sell) and `upsertHoldingOnBuy`:
+
+{{{
+// PlaceOrder - UC0004 (buy) / UC0005 (sell)
+// Market order that executes immediately against the latest price.
+// Runs inside a single database transaction so the orders, holdings,
+// users.balance and transactions tables always agree.
+//
+// The order still passes through 'open' before 'executed'. Placing it
+// reserves whatever it commits — on a sell, the crypto being sold, tracked in
+// holdings.reserved_quantity — before anything is actually moved, so a
+// second order against the same holding can never be granted the same units
+// twice. Because only market orders are implemented, reserve and settle
+// happen inside this one transaction rather than across two commits; a
+// future limit-order matcher would split them into a second transaction
+// later, without needing a schema change.
+func PlaceOrder(s *Session, side string) {
+        if side != "buy" && side != "sell" {
+                fmt.Println("Invalid side.")
+                return
+        }
+        fmt.Printf("\n-- Place market %s order --\n", side)
+
+        // buy: any market; sell: only what the user holds and can still sell
+        var m *Market
+        var err error
+        if side == "buy" {
+                m, err = ChooseMarket()
+        } else {
+                m, err = ChooseHolding(s)
+        }
+        if err != nil {
+                fmt.Println(err)
+                return
+        }
+        price, err := LatestPrice(m.ID)
+        if err != nil {
+                fmt.Println(err)
+                return
+        }
+        fmt.Printf("Latest price for %s/%s = %.6f\n", m.Symbol, m.Quote, price)
+
+        qtyStr := prompt("Quantity: ")
+        qty, err := strconv.ParseFloat(qtyStr, 64)
+        if err != nil || qty <= 0 {
+                fmt.Println("Invalid quantity.")
+                return
+        }
+        notional := qty * price
+
+        tx, err := db.DB.Begin()
+        if err != nil {
+                fmt.Println("Error:", err)
+                return
+        }
+        defer tx.Rollback()
+
+        // 1. record the order as 'open' — no trade has happened yet.
+        var orderID string
+        err = tx.QueryRow(
+                `INSERT INTO orders (user_id, market_id, side, type, status, quantity, price)
+                 VALUES ($1, $2, $3, 'market', 'open', $4, $5)
+                 RETURNING id`,
+                s.UserID, m.ID, side, qty, price,
+        ).Scan(&orderID)
+        if err != nil {
+                fmt.Println("Error creating order:", err)
+                return
+        }
+
+        if side == "buy" {
+                // check balance
+                var avail float64
+                if err := tx.QueryRow(
+                        `SELECT available_balance FROM users WHERE id = $1 FOR UPDATE`,
+                        s.UserID).Scan(&avail); err != nil {
+                        fmt.Println("Error:", err)
+                        return
+                }
+                if avail < notional {
+                        fmt.Printf("Insufficient funds: need %.4f, have %.4f\n", notional, avail)
+                        return
+                }
+
+                // debit balance
+                if _, err := tx.Exec(
+                        `UPDATE users
+                            SET available_balance = available_balance - $1,
+                                invested_balance  = invested_balance  + $1,
+                                updated_at        = now()
+                          WHERE id = $2`,
+                        notional, s.UserID,
+                ); err != nil {
+                        fmt.Println("Error:", err)
+                        return
+                }
+
+                // a buy never reserves crypto, only ever adds it — upsert holding
+                // with running weighted average
+                if err := upsertHoldingOnBuy(tx, s.UserID, m.CryptoID, qty, price); err != nil {
+                        fmt.Println("Error updating holding:", err)
+                        return
+                }
+
+                // ledger entry
+                if _, err := tx.Exec(
+                        `INSERT INTO transactions (user_id, type, amount, currency, related_order, description)
+                         VALUES ($1, 'buy', $2, 'USD', $3, $4)`,
+                        s.UserID, -notional, orderID,
+                        fmt.Sprintf("Market buy %.4f %s @ %.6f", qty, m.Symbol, price),
+                ); err != nil {
+                        fmt.Println("Error:", err)
+                        return
+                }
+        } else {
+                // sell: lock the holding and check what is actually free to sell —
+                // quantity minus whatever another open order has already reserved.
+                var held, reserved, avgPrice float64
+                err := tx.QueryRow(
+                        `SELECT quantity, reserved_quantity, avg_price FROM holdings
+                          WHERE user_id = $1 AND crypto_id = $2 FOR UPDATE`,
+                        s.UserID, m.CryptoID,
+                ).Scan(&held, &reserved, &avgPrice)
+                if err != nil && err != sql.ErrNoRows {
+                        fmt.Println("Error:", err)
+                        return
+                }
+                available := held - reserved
+                if err == sql.ErrNoRows || available < qty {
+                        fmt.Printf("Insufficient holding: trying to sell %.4f, available %.4f (of %.4f held, %.4f reserved)\n",
+                                qty, available, held, reserved)
+                        return
+                }
+
+                // reserve: committed to this order, not yet removed from the position.
+                if _, err := tx.Exec(
+                        `UPDATE holdings
+                            SET reserved_quantity = reserved_quantity + $1,
+                                updated_at        = now()
+                          WHERE user_id = $2 AND crypto_id = $3`,
+                        qty, s.UserID, m.CryptoID,
+                ); err != nil {
+                        fmt.Println("Error:", err)
+                        return
+                }
+
+                // settle: a market order fills immediately, so release the
+                // reservation and remove the asset from the position in one step.
+                if _, err := tx.Exec(
+                        `UPDATE holdings
+                            SET quantity          = quantity - $1,
+                                reserved_quantity = reserved_quantity - $1,
+                                updated_at        = now()
+                          WHERE user_id = $2 AND crypto_id = $3`,
+                        qty, s.UserID, m.CryptoID,
+                ); err != nil {
+                        fmt.Println("Error:", err)
+                        return
+                }
+
+                // credit balance; reduce invested by cost basis (avg_price * qty)
+                costBasis := avgPrice * qty
+                if _, err := tx.Exec(
+                        `UPDATE users
+                            SET available_balance = available_balance + $1,
+                                invested_balance  = GREATEST(invested_balance - $2, 0),
+                                updated_at        = now()
+                          WHERE id = $3`,
+                        notional, costBasis, s.UserID,
+                ); err != nil {
+                        fmt.Println("Error:", err)
+                        return
+                }
+
+                // ledger entry
+                if _, err := tx.Exec(
+                        `INSERT INTO transactions (user_id, type, amount, currency, related_order, description)
+                         VALUES ($1, 'sell', $2, 'USD', $3, $4)`,
+                        s.UserID, notional, orderID,
+                        fmt.Sprintf("Market sell %.4f %s @ %.6f", qty, m.Symbol, price),
+                ); err != nil {
+                        fmt.Println("Error:", err)
+                        return
+                }
+        }
+
+        // record the resulting market trade so the book reflects this fill
+        if _, err := tx.Exec(
+                `INSERT INTO market_trades (market_id, executed_at, price, quantity, side, source)
+                 VALUES ($1, now(), $2, $3, $4, 'user')`,
+                m.ID, price, qty, side,
+        ); err != nil {
+                fmt.Println("Error:", err)
+                return
+        }
+
+        // settle the order itself: it has now actually been filled.
+        if _, err := tx.Exec(
+                `UPDATE orders SET status = 'executed', executed_at = now() WHERE id = $1`,
+                orderID,
+        ); err != nil {
+                fmt.Println("Error:", err)
+                return
+        }
+
+        if err := tx.Commit(); err != nil {
+                fmt.Println("Commit error:", err)
+                return
+        }
+        fmt.Printf("Order executed: %s %.4f %s @ %.6f (notional %.4f USD)\n",
+                side, qty, m.Symbol, price, notional)
+}
+
+// upsertHoldingOnBuy creates or updates a holding using running weighted-average price.
+//
+// This is a single statement that relies on UNIQUE (user_id, crypto_id): the new
+// weighted average is recomputed by the database in numeric arithmetic rather
+// than in Go float64, and no separate SELECT ... FOR UPDATE round-trip is
+// needed because ON CONFLICT DO UPDATE locks the conflicting row itself.
+// Every SET expression sees the pre-update row, so `holdings.quantity` below is
+// still the old quantity while the average is being computed.
+func upsertHoldingOnBuy(tx *sql.Tx, userID, cryptoID string, qty, price float64) error {
+        _, err := tx.Exec(
+                `INSERT INTO holdings (user_id, crypto_id, quantity, avg_price, updated_at)
+                 VALUES ($1, $2, $3, $4, now())
+                 ON CONFLICT (user_id, crypto_id) DO UPDATE
+                    SET avg_price  = (holdings.quantity * holdings.avg_price
+                                       + EXCLUDED.quantity * EXCLUDED.avg_price)
+                                     / (holdings.quantity + EXCLUDED.quantity),
+                        quantity   = holdings.quantity + EXCLUDED.quantity,
+                        updated_at = now()`,
+                userID, cryptoID, qty, price,
+        )
+        return err
+}
+}}}
+
+`server/market.go` — `ListMarkets`, `pickNumber`, `ChooseMarket` and `LatestPrice`:
+
+{{{
+// ListMarkets prints all active markets, numbered, with their latest price,
+// and returns them in the printed order so a caller can pick one by number.
+func ListMarkets() []Market {
+        rows, err := db.DB.Query(`
+                SELECT m.id, c.id, c.symbol, m.quote_currency,
+                       COALESCE(lp.price, 0) AS price
+                  FROM markets m
+                  JOIN crypto  c  ON c.id = m.crypto_id
+                  LEFT JOIN v_latest_prices lp ON lp.market_id = m.id
+                 WHERE m.is_active = true
+                 ORDER BY c.symbol`)
+        if err != nil {
+                fmt.Println("Error:", err)
+                return nil
+        }
+        defer rows.Close()
+
+        fmt.Println()
+        fmt.Printf("  %-4s  %-8s  %-5s  %15s\n", "#", "Symbol", "Quote", "Last price")
+        fmt.Println("  -----------------------------------------")
+        var list []Market
+        for rows.Next() {
+                var m Market
+                var price float64
+                if err := rows.Scan(&m.ID, &m.CryptoID, &m.Symbol, &m.Quote, &price); err != nil {
+                        fmt.Println("scan error:", err)
+                        return nil
+                }
+                list = append(list, m)
+                fmt.Printf("  %-4d  %-8s  %-5s  %15.6f\n", len(list), m.Symbol, m.Quote, price)
+        }
+        return list
+}
+
+// pickNumber reads a 1-based choice from a list of n items.
+func pickNumber(label string, n int) (int, error) {
+        if n == 0 {
+                return 0, fmt.Errorf("Nothing to choose from.")
+        }
+        k, err := strconv.Atoi(prompt(label))
+        if err != nil || k < 1 || k > n {
+                return 0, fmt.Errorf("Invalid choice, enter a number from 1 to %d.", n)
+        }
+        return k - 1, nil
+}
+
+// ChooseMarket lists the active markets and lets the user pick one by its
+// number in the list.
+func ChooseMarket() (*Market, error) {
+        list := ListMarkets()
+        k, err := pickNumber("Market #: ", len(list))
+        if err != nil {
+                return nil, err
+        }
+        return &list[k], nil
+}
+
+// LatestPrice returns the last traded price on a market.
+func LatestPrice(marketID string) (float64, error) {
+        var price float64
+        err := db.DB.QueryRow(
+                `SELECT price FROM v_latest_prices WHERE market_id = $1`, marketID,
+        ).Scan(&price)
+        if err == sql.ErrNoRows {
+                return 0, fmt.Errorf("no trades yet for this market")
+        }
+        return price, err
+}
+}}}