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/UseCase0005Implementation.md

    ra531b45 ref1c1c7  
    1 # Use-case 0005 Implementation — Sell
    2 
    3 **Initiating actor:** Trader. **Source file:** `server/trade.go`, function `PlaceOrder(s, "sell")`.
    4 
    5 ## The bug this closes
    6 
    7 Before this change, `holdings` had `quantity` and `avg_price` only. The sell
    8 path checked `held < qty` straight against `quantity`, which cannot tell
    9 "owned" apart from "owned, but already committed to another order that has
    10 not settled." `holdings.reserved_quantity` fixes that: the crypto being sold
    11 is reserved before it is removed from the position, and the check is against
    12 `quantity - reserved_quantity`.
    13 
    14 ## Scenario (implemented)
    15 
    16 1. **User** chooses `[5] Place market SELL order`.
    17 2. **System** lists markets (same as UC0004 step 2).
    18 3. **User** enters market symbol, e.g. `ETH`, then quantity `0.5`.
    19 4. **System** opens a transaction and runs:
     1# Use-case 0005 Implementation - Place market SELL order
     2
     3**Initiating actor:** Trader
     4
     5**Other actors:** Market Simulator (indirect — supplies the current price).
     6
     7A logged-in Trader sells part or all of a holding at the current market price. The
     8Trader never types a symbol: the system lists only the cryptos the Trader holds and can
     9still sell (the quantity not already reserved by an open sell order), numbered, with
     10how much is held and how much is free, and the Trader picks one by its number and
     11enters the quantity. In one database transaction the system records the order,
     12reserves the crypto being sold and settles it, credits the proceeds to the Trader's
     13available cash while reducing the invested cash by the cost basis, writes a ledger
     14entry and a market trade, and marks the order executed. Cost basis is preserved, so
     15the realised P/L can be reconstructed from the ledger.
     16
     17Original use-case description (P3): [UseCase0005](../P3-UseCaseModel/UseCase0005.md).
     18Implementation: [`server/trade.go`](../../server/trade.go), function
     19`PlaceOrder(s, "sell")`, which calls `ChooseHolding`, `pickNumber` and `LatestPrice`
     20from [`server/market.go`](../../server/market.go).
     21
     22All statements run on the `project` schema (the connection sets
     23`search_path=project,public` in `server/db/db.go`). The SQL below is copied from the
     24Go code; only the Go source indentation is removed, a `;` is added after each
     25statement of the transaction, and `--` comments say what each `$n` placeholder is
     26bound to.
     27
     28The run shown is user `alice` right after the buy of
     29[UseCase0004](UseCase0004Implementation.md): 7578.60 USD available, holdings
     300.01 BTC (bought at 67140) and 0.5 ETH (bought at 3500). She sells 0.2 ETH.
     31
     32## Reserve, then settle
     33
     34The crypto being sold is **reserved** (`holdings.reserved_quantity`) before it is
     35removed from the position, and the sell check is against what is truly still free,
     36`quantity - reserved_quantity`, not against the raw `quantity`, which would also count
     37crypto already promised to another order. Because only market orders are implemented,
     38an order settles in the same transaction it is placed in, so reserve and settle are two
     39statements inside one commit; they stay logically distinct so that a future
     40limit-order matcher, where an order would stay `open` until a *later* transaction fills
     41it, needs a second transaction but no schema change.
     42
     43## Scenario
     44
     451. **Trader** chooses `[5] Place market SELL order` in the authenticated menu (types `5`).
     462. **System** prints `-- Place market sell order --` and lists, numbered, only the
     47   cryptos the Trader holds with some quantity still free to sell, with the quantity
     48   held, the quantity free to sell and the last price (`ChooseHolding`;
     49   `$1` = the logged-in user's id):
     50
     51   ```sql
     52   SELECT m.id, c.id, c.symbol, m.quote_currency,
     53          h.quantity, h.quantity - h.reserved_quantity AS free,
     54          COALESCE(lp.price, 0) AS price
     55     FROM holdings h
     56     JOIN crypto  c ON c.id = h.crypto_id
     57     JOIN markets m ON m.crypto_id = c.id AND m.is_active = true
     58     LEFT JOIN v_latest_prices lp ON lp.market_id = m.id
     59    WHERE h.user_id = $1
     60      AND h.quantity - h.reserved_quantity > 0
     61    ORDER BY c.symbol
     62   ```
     63
     64   For alice it prints `1 BTC USD 0.0100 0.0100 67140.000000` and
     65   `2 ETH USD 0.5000 0.5000 3520.000000`, then asks `Holding #:`. Go keeps each row's
     66   market id and crypto id in memory; the Trader only types the list number. (If the
     67   query returns no row, the system prints `you hold no crypto that is free to sell`
     68   and the use-case ends.)
     69
     70   ![UC0005 steps 1-2: Trader chooses SELL, system lists what they hold](screenshots/uc0005_1_2_holdings.png)
     71
     723. **Trader** picks the holding by its number in the list: `2` (ETH).
     734. **System** takes the market id and crypto id of row 2 from the list and reads the
     74   latest price of that market (`LatestPrice`; `$1` = the chosen market's id):
     75
     76   ```sql
     77   SELECT price FROM v_latest_prices WHERE market_id = $1
     78   ```
     79
     80   It prints `Latest price for ETH/USD = 3520.000000` and asks `Quantity:`.
     81
     82   ![UC0005 steps 3-4: Trader picks holding #2 (ETH), system shows the price](screenshots/uc0005_3_4_price.png)
     83
     845. **Trader** enters the quantity `0.2`.
     856. **System** computes in Go notional = quantity × price = 0.2 × 3520 = 704.00 and
     86   runs one database transaction; the statements are in exactly the order
     87   `PlaceOrder` executes them for a sell. After statement (b) Go also computes the
     88   cost basis = avg_price × quantity = 3500 × 0.2 = 700.00 from the locked holding row;
     89   both values are passed to SQL as parameters.
    2090
    2191   ```sql
    2292   BEGIN;
    2393
    24    -- (a) record the order as 'open' — no trade has happened yet
    25    INSERT INTO orders
    26        (user_id, market_id, side, type, status, quantity, price)
    27    VALUES
    28        ($1, $2, 'sell', 'market', 'open', $3, $4)
    29    RETURNING id;
    30 
    31    -- (b) lock the holding and check what is actually free to sell
     94   -- (a) record the order as 'open' — no trade has happened yet.
     95   --     $1 = user id, $2 = market id, $3 = side (the Go variable side = 'sell'),
     96   --     $4 = quantity (0.2), $5 = price (3520); the returned id is kept in Go.
     97   INSERT INTO orders (user_id, market_id, side, type, status, quantity, price)
     98    VALUES ($1, $2, $3, 'market', 'open', $4, $5)
     99    RETURNING id;
     100
     101   -- (b) lock the holding row and read what is held, what is already reserved and
     102   --     the average entry price. $1 = user id, $2 = crypto id.
     103   --     Go computes available = quantity - reserved_quantity (0.5 - 0 = 0.5);
     104   --     if there is no row or available < quantity -> alternate flow 5a.
    32105   SELECT quantity, reserved_quantity, avg_price FROM holdings
    33     WHERE user_id = $1 AND crypto_id = $c FOR UPDATE;
    34    -- available := quantity - reserved_quantity
    35    -- abort if missing or available < $qty
    36 
    37    -- (c) reserve: committed to this order, not yet removed from the position
     106     WHERE user_id = $1 AND crypto_id = $2 FOR UPDATE;
     107
     108   -- (c) reserve: committed to this order, not yet removed from the position.
     109   --     $1 = quantity (0.2), $2 = user id, $3 = crypto id.
    38110   UPDATE holdings
    39       SET reserved_quantity = reserved_quantity + $qty, updated_at = now()
    40     WHERE user_id = $1 AND crypto_id = $c;
    41 
    42    -- (d) settle: a market order fills immediately, so release the
    43    --     reservation and remove the asset in the same step
     111       SET reserved_quantity = reserved_quantity + $1,
     112           updated_at        = now()
     113     WHERE user_id = $2 AND crypto_id = $3;
     114
     115   -- (d) settle: a market order fills immediately, so release the reservation and
     116   --     remove the asset from the position in one step. Same parameters as (c).
    44117   UPDATE holdings
    45       SET quantity = quantity - $qty,
    46           reserved_quantity = reserved_quantity - $qty,
    47           updated_at = now()
    48     WHERE user_id = $1 AND crypto_id = $c;
    49 
     118       SET quantity          = quantity - $1,
     119           reserved_quantity = reserved_quantity - $1,
     120           updated_at        = now()
     121     WHERE user_id = $2 AND crypto_id = $3;
     122
     123   -- (e) credit the proceeds; reduce invested cash by the cost basis.
     124   --     $1 = notional (704.00), $2 = cost basis (700.00), $3 = user id.
    50125   UPDATE users
    51       SET available_balance = available_balance + $notional,
    52           invested_balance  = GREATEST(invested_balance - ($avg * $qty), 0),
    53           updated_at        = now()
    54     WHERE id = $1;
    55 
    56    INSERT INTO transactions
    57        (user_id, type, amount, currency, related_order, description)
    58    VALUES
    59        ($1, 'sell', $notional, 'USD', $orderId, 'Market sell ...');
    60 
    61    INSERT INTO market_trades
    62        (market_id, executed_at, price, quantity, side, source)
    63    VALUES
    64        ($2, now(), $price, $qty, 'sell', 'user');
    65 
    66    -- (e) settle the order itself — it has now actually been filled
    67    UPDATE orders SET status = 'executed', executed_at = now() WHERE id = $orderId;
     126       SET available_balance = available_balance + $1,
     127           invested_balance  = GREATEST(invested_balance - $2, 0),
     128           updated_at        = now()
     129     WHERE id = $3;
     130
     131   -- (f) ledger entry. $1 = user id, $2 = notional (704.00), $3 = order id from (a),
     132   --     $4 = description built in Go: 'Market sell 0.2000 ETH @ 3520.000000'.
     133   INSERT INTO transactions (user_id, type, amount, currency, related_order, description)
     134    VALUES ($1, 'sell', $2, 'USD', $3, $4);
     135
     136   -- (g) record the resulting market trade.
     137   --     $1 = market id, $2 = price, $3 = quantity, $4 = side ('sell').
     138   INSERT INTO market_trades (market_id, executed_at, price, quantity, side, source)
     139    VALUES ($1, now(), $2, $3, $4, 'user');
     140
     141   -- (h) settle the order itself — it has now actually been filled. $1 = order id.
     142   UPDATE orders SET status = 'executed', executed_at = now() WHERE id = $1;
    68143
    69144   COMMIT;
    70145   ```
    71146
    72    ![Selling 0.5 ETH at the current market price](screenshots/uc0005_sell.png)
    73 
    74 5. **System** prints: `Order executed: sell 0.5000 ETH @ 3520.000000 (notional 1760.0000 USD)`.
    75 
    76 ## Failure path — insufficient holding
    77 
    78 If the holding does not exist, or `quantity - reserved_quantity < requested`, the `defer tx.Rollback()` in `server/trade.go` reverts every statement above — including the `open` order, which was never committed — and the user sees:
    79 
    80 ```
    81 Insufficient holding: trying to sell X, available Y (of Z held, W reserved)
    82 ```
    83 
    84 ## Verified run — the exact scenario from the design review
    85 
    86 Run 2026-09-16 against PostgreSQL 16 (`bp_database` on `localhost:5433`).
    87 Alice's ETH/BTC holdings were seeded, then her BTC holding was set to exactly
    88 the scenario that motivated this fix: 2 BTC owned, nothing reserved.
    89 
    90 ```
    91 $ psql ... -c "SELECT symbol, quantity, reserved_quantity, avg_price
    92                FROM holdings h JOIN crypto c ON c.id = h.crypto_id
    93                WHERE user_id = '<alice>';"
    94 
    95  symbol | quantity | reserved_quantity |  avg_price
    96 --------+----------+--------------------+-------------
    97  BTC    |   2.0000 |             0.0000 | 65000.000000
    98  ETH    |   0.5000 |             0.0000 |  3500.000000
    99 ```
    100 
    101 **Step 1 — portfolio before the sell** (`[6] View portfolio`):
    102 
    103 ```
    104   Symbol        Quantity      Reserved     Available         Avg buy         Current           Value  Unrealised P/L
    105   ------------------------------------------------------------------------------------------------------------------
    106   BTC             2.0000        0.0000        2.0000    65000.000000    67140.000000     134280.0000      +4280.0000
    107   ETH             0.5000        0.0000        0.5000     3500.000000     3520.000000       1760.0000        +10.0000
    108   ------------------------------------------------------------------------------------------------------------------
    109   TOTAL                                                                                  136040.0000      +4290.0000
    110 ```
    111 
    112 **Step 2 — `[5] Place market SELL order` → `BTC` → `0.5`:**
    113 
    114 ```
    115 Order executed: sell 0.5000 BTC @ 67140.000000 (notional 33570.0000 USD)
    116 ```
    117 
    118 **Step 3 — portfolio after the sell:**
    119 
    120 ```
    121   BTC             1.5000        0.0000        1.5000    65000.000000    67140.000000     100710.0000      +3210.0000
    122 ```
    123 
    124 `quantity` dropped from 2.0 to 1.5 and `reserved_quantity` is back to 0.0000
    125 — reserve and settle both happened, inside the one commit, exactly as
    126 designed.
    127 
    128 ## Verified run — reserve and settle as two distinct, observable steps
    129 
    130 The CLI settles a market order in the same transaction it reserves in, so
    131 `reserved_quantity` is never visibly nonzero *outside* a transaction. Run by
    132 hand in one `psql` session (one transaction, so the session sees its own
    133 uncommitted writes) to show the intermediate state that step (c) alone would
    134 leave, before step (d) runs:
     1477. **System** confirms
     148   `Order executed: sell 0.2000 ETH @ 3520.000000 (notional 704.0000 USD)` and shows
     149   the authenticated menu again.
     150
     151   The screenshot shows steps 5–7: the entered quantity, the confirmation and the menu.
     152
     153   ![UC0005 steps 5-7: quantity entered, order executed](screenshots/uc0005_5_7_executed.png)
     154
     155After this run the database holds for alice: ETH `quantity` 0.3000 with
     156`reserved_quantity` 0.0000; `available_balance` 8282.60 (= 7578.60 + 704.00) and
     157`invested_balance` 1721.40 (= 2421.40 − 700.00); a `sell` row in `transactions` with
     158amount 704.0000 and description `Market sell 0.2000 ETH @ 3520.000000`; and the order
     159with status `executed`. The realised P/L of this sell is notional − cost basis =
     160704.00 − 700.00 = +4.00 USD.
     161
     162### Alternate flow 5a — insufficient holding
     163
     164Right after the sell above, alice chooses `[5]` again. The list from step 2 now shows
     165`2 ETH USD 0.3000 0.3000 3520.000000`. She picks `2` (ETH) and enters quantity `5`.
     166In the transaction, statement (a) inserts the `open` order and statement (b) returns
     167quantity 0.3000 and reserved_quantity 0.0000, so available = 0.3 < 5. `PlaceOrder`
     168prints
     169
     170```
     171Insufficient holding: trying to sell 5.0000, available 0.3000 (of 0.3000 held, 0.0000 reserved)
     172```
     173
     174and returns without running (c)–(h); the deferred `tx.Rollback()` undoes statement (a)
     175as well, so no order, no reservation and no ledger entry is left behind. The
     176authenticated menu is shown again. The same message is printed if the holding row no
     177longer exists (for example because it was sold out from another session after the
     178list was shown).
     179
     180![UC0005 alternate flow 5a: selling more than is held](screenshots/uc0005_5a_insufficient.png)
     181
     182## Reserve and settle, step by step
     183
     184The CLI reserves and settles inside one transaction, so `reserved_quantity` is never
     185nonzero *outside* a transaction. The intermediate state is shown by running statements
     186(c) and (d) by hand in one `psql` transaction (which sees its own uncommitted
     187writes) against alice's ETH holding after the scenario above (0.3 ETH), for a sell of
     1880.1, and rolling back at the end so nothing is changed. Literal values replace the
     189`$n` parameters; `:alice` and `:eth` are psql variables for
     190`(SELECT id FROM users WHERE username = 'alice')` and
     191`(SELECT id FROM crypto WHERE symbol = 'ETH')`:
    135192
    136193```sql
    137194BEGIN;
    138 
    139 -- before: Alice owns 2 BTC, none reserved
    140195SELECT quantity, reserved_quantity, quantity - reserved_quantity AS available
    141   FROM holdings WHERE user_id = '<alice>' AND crypto_id = '<btc>';
     196  FROM holdings WHERE user_id = :alice AND crypto_id = :eth;
    142197--  quantity | reserved_quantity | available
    143 -- ----------+--------------------+-----------
    144 --    2.0000 |             0.0000 |    2.0000
    145 
    146 -- step (c): order placed, 0.5 BTC reserved — no trade has happened yet
    147 UPDATE holdings SET reserved_quantity = reserved_quantity + 0.5, updated_at = now()
    148  WHERE user_id = '<alice>' AND crypto_id = '<btc>';
    149 
     198-- ----------+-------------------+-----------
     199--    0.3000 |            0.0000 |    0.3000
     200
     201-- (c) reserve 0.1: the order is placed, no trade has happened yet
     202UPDATE holdings SET reserved_quantity = reserved_quantity + 0.1, updated_at = now()
     203 WHERE user_id = :alice AND crypto_id = :eth;
    150204SELECT quantity, reserved_quantity, quantity - reserved_quantity AS available
    151   FROM holdings WHERE user_id = '<alice>' AND crypto_id = '<btc>';
     205  FROM holdings WHERE user_id = :alice AND crypto_id = :eth;
    152206--  quantity | reserved_quantity | available
    153 -- ----------+--------------------+-----------
    154 --    2.0000 |             0.5000 |    1.5000
    155 
    156 -- step (d): market order settles immediately, reservation released
    157 UPDATE holdings SET quantity = quantity - 0.5, reserved_quantity = reserved_quantity - 0.5, updated_at = now()
    158  WHERE user_id = '<alice>' AND crypto_id = '<btc>';
    159 
     207-- ----------+-------------------+-----------
     208--    0.3000 |            0.1000 |    0.2000
     209
     210-- (d) settle: the reservation is released and the asset removed
     211UPDATE holdings SET quantity = quantity - 0.1, reserved_quantity = reserved_quantity - 0.1, updated_at = now()
     212 WHERE user_id = :alice AND crypto_id = :eth;
    160213SELECT quantity, reserved_quantity, quantity - reserved_quantity AS available
    161   FROM holdings WHERE user_id = '<alice>' AND crypto_id = '<btc>';
     214  FROM holdings WHERE user_id = :alice AND crypto_id = :eth;
    162215--  quantity | reserved_quantity | available
    163 -- ----------+--------------------+-----------
    164 --    1.5000 |             0.0000 |    1.5000
    165 
    166 COMMIT;
    167 ```
    168 
    169 This is the row that would stay visible to every other connection for as long
    170 as the order stayed `open` — i.e. for as long as it took a matcher to fill
    171 it, once limit orders exist.
    172 
    173 ## Verified run — two concurrent sells, which is the bug itself
    174 
    175 The scenario the design review described: a user should not be able to place
    176 two sell orders whose combined quantity exceeds what they actually hold. With
    177 Alice's BTC holding at 1.5 BTC (0 reserved), two independent CLI processes
    178 were started at the same instant, each selling `1.0 BTC` — together 2.0 BTC,
    179 more than she has:
    180 
    181 ```
    182 $ ( eduberza-sell-1.0-BTC ) &   # process A
    183 $ ( eduberza-sell-1.0-BTC ) &   # process B
    184 $ wait
    185 
    186 === A ===
    187 Insufficient holding: trying to sell 1.0000, available 0.5000 (of 0.5000 held, 0.0000 reserved)
    188 === B ===
    189 Order executed: sell 1.0000 BTC @ 67140.000000 (notional 67140.0000 USD)
    190 
    191 === final holding ===
    192  quantity | reserved_quantity
    193 ----------+--------------------
    194    0.5000 |             0.0000
    195 ```
    196 
    197 One order settled, one was correctly rejected, and the final `quantity`
    198 (0.5) is consistent with exactly one 1.0 BTC sell having happened against the
    199 1.5 BTC available — not both, and not neither. This is enforced by the
    200 `SELECT ... FOR UPDATE` lock on the holdings row: whichever transaction gets
    201 there second blocks until the first commits, then re-reads the now-current
    202 `quantity`/`reserved_quantity` before deciding.
    203 
    204 ## Verified — the constraint holds even if application code did not
    205 
    206 ```sql
    207 UPDATE holdings SET reserved_quantity = quantity + 1 WHERE user_id = '<alice>' AND crypto_id = '<btc>';
    208 
     216-- ----------+-------------------+-----------
     217--    0.2000 |            0.0000 |    0.2000
     218ROLLBACK;
     219```
     220
     221The middle state is what every other connection would see for as long as an order
     222stayed `open` once limit orders exist: 0.1 ETH still owned but no longer free to sell.
     223
     224## Two concurrent sells
     225
     226A Trader must not be able to sell the same units twice from two sessions at once. Both
     227sessions may have listed the holding as free (step 2 runs outside the transaction), so
     228the protection is statement (b): `SELECT ... FOR UPDATE` locks the holding row, and a
     229second transaction that reaches (b) waits until the first one commits, then reads the
     230already reduced `quantity` before deciding.
     231
     232This was checked with two `psql` sessions running statements (b)–(d) against alice's
     2330.3 ETH. Session A locked the row, reserved and settled 0.2 ETH and committed after a
     2343-second pause; session B asked for the lock one second after A had taken it:
     235
     236```
     237A: SELECT ... FOR UPDATE  ->  quantity 0.3000, reserved_quantity 0.0000
     238A: reserve 0.2, settle 0.2, pg_sleep(3)
     239B: 11:43:54  SELECT ... FOR UPDATE   -- blocks, A holds the row lock
     240A: 11:43:56  COMMIT
     241B: 11:43:56  lock granted  ->  quantity 0.1000, reserved_quantity 0.0000
     242```
     243
     244Session B was blocked for the two seconds until A committed and then saw only
     2450.1 ETH, so a second 0.2 ETH sell in B takes alternate flow 5a
     246(`available 0.1000`) instead of selling units that no longer exist. (B was rolled
     247back and alice's holding was restored to 0.3 ETH after the check.)
     248
     249## The constraint holds even if the application code did not
     250
     251`schema_creation.sql` declares
     252`CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` on
     253`holdings.reserved_quantity`, so an inconsistent reservation is impossible at the
     254database level, independently of `trade.go` (run inside a transaction that was
     255rolled back):
     256
     257```
     258UPDATE holdings SET reserved_quantity = quantity + 1 WHERE user_id = :alice AND crypto_id = :eth;
    209259ERROR:  new row for relation "holdings" violates check constraint "holdings_check"
    210260```
    211 
    212 `CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` in
    213 `schema_creation.sql` makes an inconsistent reservation impossible at the
    214 database level, independent of `trade.go`.
Note: See TracChangeset for help on using the changeset viewer.