Changeset 9577c79 for docs/P3-UseCaseModel
- Timestamp:
- 09/16/26 23:37:15 (13 days ago)
- Branches:
- main
- Children:
- 8b447ef
- Parents:
- df05838
- Location:
- docs/P3-UseCaseModel
- Files:
-
- 6 edited
-
P3.zip (modified) ( previous)
-
UseCase0004.md (modified) (4 diffs)
-
UseCase0005.md (modified) (3 diffs)
-
UseCase0006.md (modified) (3 diffs)
-
UseCaseModel.md (modified) (3 diffs)
-
UseCaseModelAIUsage.md (modified) (1 diff)
Legend:
- Unmodified
- Added
- Removed
-
docs/P3-UseCaseModel/UseCase0004.md
rdf05838 r9577c79 37 37 BEGIN; 38 38 39 -- (a) record intent — no trade has happened yet. 39 40 INSERT INTO project.orders 40 (user_id, market_id, side, type, status, quantity, price , executed_at)41 (user_id, market_id, side, type, status, quantity, price) 41 42 VALUES 42 ($user_id, $market_id, 'buy', 'market', ' executed', $qty, $price, now())43 ($user_id, $market_id, 'buy', 'market', 'open', $qty, $price) 43 44 RETURNING id; -- captured as $order_id 44 45 46 -- (b) lock and check the user balance 45 47 SELECT available_balance FROM project.users WHERE id = $user_id FOR UPDATE; 46 48 -- abort if available_balance < notional 47 49 50 -- (c) move cash from available to invested. A buy never reserves crypto 51 -- the way a sell does — it only ever adds to the position, so there 52 -- is nothing on the holdings side to commit before settling. 48 53 UPDATE project.users 49 54 SET available_balance = available_balance - $notional, … … 52 57 WHERE id = $user_id; 53 58 54 -- Upsert holding with running weighted-average price:59 -- (d) upsert holding with running weighted-average price: 55 60 SELECT quantity, avg_price 56 61 FROM project.holdings … … 61 66 -- new_avg = (old_qty*old_avg + $qty*$price) / (old_qty + $qty) 62 67 68 -- (e) ledger entry 63 69 INSERT INTO project.transactions 64 70 (user_id, type, amount, currency, related_order, description) … … 66 72 ($user_id, 'buy', -$notional, 'USD', $order_id, 'Market buy ...'); 67 73 74 -- (f) record the resulting market trade 68 75 INSERT INTO project.market_trades 69 76 (market_id, executed_at, price, quantity, side, source) 70 77 VALUES 71 78 ($market_id, now(), $price, $qty, 'buy', 'user'); 79 80 -- (g) settle the order itself — it has now actually been filled. 81 UPDATE project.orders 82 SET status = 'executed', executed_at = now() 83 WHERE id = $order_id; 72 84 73 85 COMMIT; -
docs/P3-UseCaseModel/UseCase0005.md
rdf05838 r9577c79 6 6 7 7 A Trader sells part or all of a holding at the current market price. Cost basis is preserved so realised P/L can be reconstructed from the ledger. 8 9 ## Reserve, then settle 10 11 The crypto being sold is **reserved** (`holdings.reserved_quantity`) before it 12 is actually removed from the position, so the check a second sell order makes 13 is always against what is truly still free (`quantity - reserved_quantity`), 14 not against the raw `quantity`, which would also count crypto already 15 promised to this order. Because only market orders are implemented, an order 16 settles in the same database transaction it is placed in, so reserve and 17 settle below are two statements inside one commit rather than two separate 18 ones — the existing all-or-nothing guarantee (see 19 [PrototypeImplementation](../P4-Prototype/PrototypeImplementation.md)) is 20 kept. They stay logically distinct so that a future limit-order matcher — 21 where an order really would sit `open` for a while before a *later* 22 transaction settles it — needs only a second transaction where today there is 23 one, not a schema change. 8 24 9 25 ## Scenario … … 18 34 BEGIN; 19 35 36 -- (a) record intent — no trade has happened yet. 20 37 INSERT INTO project.orders 21 (user_id, market_id, side, type, status, quantity, price , executed_at)38 (user_id, market_id, side, type, status, quantity, price) 22 39 VALUES 23 ($user_id, $market_id, 'sell', 'market', ' executed', $qty, $price, now())40 ($user_id, $market_id, 'sell', 'market', 'open', $qty, $price) 24 41 RETURNING id; -- $order_id 25 42 26 SELECT quantity, avg_price 43 -- (b) lock the holding and check what is actually free to sell. 44 SELECT quantity, reserved_quantity, avg_price 27 45 FROM project.holdings 28 46 WHERE user_id = $user_id AND crypto_id = $crypto_id 29 47 FOR UPDATE; 30 -- abort if row missing or quantity < $qty 48 -- available := quantity - reserved_quantity 49 -- abort if row missing or available < $qty 31 50 ``` 32 6. If the holding check passes, system reduces the holding, credits cash and debits invested, and appends a ledger and a market trade: 51 52 6. If the check passes, system reserves the crypto, then — since this is a market order — settles it immediately, all inside the same transaction: 33 53 34 54 ```sql 55 -- (c) reserve: committed to this order, not yet removed from the position. 35 56 UPDATE project.holdings 36 SET quantity = quantity - $qty, 37 updated_at = now() 57 SET reserved_quantity = reserved_quantity + $qty, 58 updated_at = now() 59 WHERE user_id = $user_id AND crypto_id = $crypto_id; 60 61 -- (d) settle: release the reservation and remove the asset in one step. 62 UPDATE project.holdings 63 SET quantity = quantity - $qty, 64 reserved_quantity = reserved_quantity - $qty, 65 updated_at = now() 38 66 WHERE user_id = $user_id AND crypto_id = $crypto_id; 39 67 … … 54 82 ($market_id, now(), $price, $qty, 'sell', 'user'); 55 83 84 -- (e) settle the order itself — it has now actually been filled. 85 UPDATE project.orders 86 SET status = 'executed', executed_at = now() 87 WHERE id = $order_id; 88 56 89 COMMIT; 57 90 ``` 91 58 92 7. System confirms: `Order executed: sell 0.5000 ETH @ 3520.000000 (notional 1760.0000 USD)`. 59 93 60 94 ### Alternate flow 5a — insufficient holding 61 95 62 If the `SELECT ... FOR UPDATE` returns no row, or the held quantity is smaller than the sell quantity, the entire transaction rolls back and system shows "Insufficient holding: trying to sell X, hold Y." 96 If the holding row is missing, or `quantity - reserved_quantity < $qty`, the 97 entire transaction rolls back — including the `open` order from step 5, which 98 was never committed — and system shows: 99 `"Insufficient holding: trying to sell X, available Y (of Z held, W reserved)."` 100 101 ### Worked example — the case this fixes 102 103 Alice holds 2 BTC, `reserved_quantity = 0`, and places `sell 0.5 BTC`: 104 105 | | quantity | reserved_quantity | available | 106 |---|---|---|---| 107 | before | 2.0000 | 0.0000 | 2.0000 | 108 | after step (c) — reserved | 2.0000 | 0.5000 | 1.5000 | 109 | after step (d) — settled | 1.5000 | 0.0000 | 1.5000 | 110 111 If a second sell for more than 1.5 BTC is placed concurrently, its own 112 `SELECT … FOR UPDATE` in step 5b blocks until the first transaction commits, 113 then sees the reduced `quantity` and correctly reports insufficient holding — 114 proven under real concurrency in 115 [UseCase0005Implementation](../P4-Prototype/UseCase0005Implementation.md). 63 116 64 117 ### Realised P/L (post-scenario) -
docs/P3-UseCaseModel/UseCase0006.md
rdf05838 r9577c79 17 17 SELECT symbol, 18 18 quantity, 19 COALESCE(reserved_quantity, 0), 20 COALESCE(available_quantity, quantity), 19 21 COALESCE(avg_price, 0), 20 22 COALESCE(current_price, 0), … … 26 28 ORDER BY symbol; 27 29 ``` 30 31 `reserved_quantity` is the amount committed to the Trader's own open sell 32 orders (see [UseCase0005](UseCase0005.md)); `available_quantity` is what is 33 actually free to sell right now. 28 34 3. System displays the rows and a computed summary: 29 35 … … 54 60 c.symbol, 55 61 h.quantity, 62 h.reserved_quantity, 63 (h.quantity - h.reserved_quantity) AS available_quantity, 56 64 h.avg_price, 57 65 lp.price AS current_price, -
docs/P3-UseCaseModel/UseCaseModel.md
rdf05838 r9577c79 38 38 * [https://github.com/StefanTrsunov/bp/blob/main/docs/P3-UseCaseModel/UseCase0005.md UC0005] – 39 39 '''Place market SELL order''' – Trader sells part or all of a holding at the current market 40 price, which credits cash and preserves the cost basis.40 price, which reserves the crypto being sold, credits cash and preserves the cost basis. 41 41 * [https://github.com/StefanTrsunov/bp/blob/main/docs/P3-UseCaseModel/UseCase0006.md UC0006] – 42 42 '''View portfolio and transaction history''' – Trader inspects current holdings, unrealised … … 88 88 ||[https://github.com/StefanTrsunov/bp/blob/main/docs/P3-UseCaseModel/UseCase0003.md UC0003 – Deposit]||High||Shows a multi-row transaction: `UPDATE users` plus `INSERT INTO transactions`.|| 89 89 ||[https://github.com/StefanTrsunov/bp/blob/main/docs/P3-UseCaseModel/UseCase0004.md UC0004 – Buy]||Very high||Core of the exchange: `INSERT orders`, `UPDATE users`, upsert `holdings`, ledger entry, market trade.|| 90 ||[https://github.com/StefanTrsunov/bp/blob/main/docs/P3-UseCaseModel/UseCase0005.md UC0005 – Sell]||Very high||Dual of Buy; demonstrates row-level `FOR UPDATE` locking and cost-basis bookkeeping.||90 ||[https://github.com/StefanTrsunov/bp/blob/main/docs/P3-UseCaseModel/UseCase0005.md UC0005 – Sell]||Very high||Dual of Buy; demonstrates row-level `FOR UPDATE` locking, reservation of committed crypto (`holdings.reserved_quantity`) and cost-basis bookkeeping.|| 91 91 ||[https://github.com/StefanTrsunov/bp/blob/main/docs/P3-UseCaseModel/UseCase0006.md UC0006 – Portfolio]||High||Demonstrates joins over `holdings`, `markets` and `crypto`, and the `v_portfolio` view.|| 92 92 ||[https://github.com/StefanTrsunov/bp/blob/main/docs/P3-UseCaseModel/UseCase0007.md UC0007 – Watchlist]||Medium||Demonstrates N–M relation handling and `ON CONFLICT` upsert semantics.|| … … 104 104 kept in one place. Direct links: 105 105 [https://github.com/StefanTrsunov/bp/blob/main/docs/P1-ConceptualModel/ERModelAIUsage.md#session-1--2026-04-21 Session 1 – 2026-04-21], 106 [https://github.com/StefanTrsunov/bp/blob/main/docs/P1-ConceptualModel/ERModelAIUsage.md#session-2--2026-08-06--2026-08-07 Session 2 – 2026-08-06/07]. 106 [https://github.com/StefanTrsunov/bp/blob/main/docs/P1-ConceptualModel/ERModelAIUsage.md#session-2--2026-08-06--2026-08-07 Session 2 – 2026-08-06/07], 107 [https://github.com/StefanTrsunov/bp/blob/main/docs/P1-ConceptualModel/ERModelAIUsage.md#session-3--2026-09-16 Session 3 – 2026-09-16]. 107 108 108 109 '''Service:''' Claude Code (Anthropic), https://claude.com/claude-code – Claude subscription, 109 model Claude Opus 4.7 (1M context) .110 model Claude Opus 4.7 (1M context) in sessions 1–2, Claude Sonnet 5 in session 3. 110 111 111 112 '''In short:''' the AI proposed the actor taxonomy and drafted the seven use cases with their SQL 112 113 in session 1. In session 2 the use-case model itself was '''not''' changed – the only work was 113 114 re-executing every scenario, including the failure paths, against a live PostgreSQL 16 database. 115 In session 3, UC0004 and UC0005 were revised to reserve the resource an order commits (crypto on 116 a sell) before settling it, closing a gap where nothing stopped a second sell order from being 117 granted crypto already promised to a first one; see 118 [UseCaseModelAIUsage](UseCaseModelAIUsage.md#session-3--2026-09-16). -
docs/P3-UseCaseModel/UseCaseModelAIUsage.md
rdf05838 r9577c79 55 55 duplicate registration, wrong password). The results are documented per use case 56 56 on the `UseCaseXXXXImplementation` pages. 57 58 ### Session 3 — 2026-09-16 59 60 Driven by the design review logged in full in 61 [ERModelAIUsage](../P1-ConceptualModel/ERModelAIUsage.md#session-3--2026-09-16): 62 placing a sell order checked `holdings.quantity` directly, with no way to 63 record that part of a position was already promised to another, unsettled 64 order. 65 66 **What changed:** 67 68 - [UseCase0005](UseCase0005.md) — the scenario now reserves the crypto 69 (`holdings.reserved_quantity`) before removing it from the position, checks 70 `quantity - reserved_quantity` rather than raw `quantity`, and adds a 71 worked example and a note on why the reserve and settle steps stay inside 72 one transaction rather than two (only market orders are implemented, and 73 splitting into two commits would risk an order stuck `open` with no cancel 74 use case to recover it). 75 - [UseCase0004](UseCase0004.md) — no change to the balance logic, but the 76 order insert now goes through `status='open'` before a final 77 `UPDATE ... SET status='executed'`, matching the sell side, so `Orders` 78 genuinely has the lifecycle [ERModel](../P1-ConceptualModel/ERModel.md) 79 describes for it rather than a status column that is only ever written 80 once. 81 - [UseCase0006](UseCase0006.md) — the `v_portfolio` reference and its query 82 gained `reserved_quantity`/`available_quantity`, since the portfolio screen 83 is where a Trader would actually see the new field. 84 - The use-case importance table and UC0005's one-line description in 85 [UseCaseModel](UseCaseModel.md) were reworded to mention the reservation. 86 87 Every changed scenario's SQL was re-run against the live database, including a 88 two-concurrent-sells test that reproduces the exact bug being fixed: see 89 [UseCase0005Implementation](../P4-Prototype/UseCase0005Implementation.md). 90 91 **What I decided:** to keep this a revision of the existing UC0004/UC0005 92 pages rather than a new use case (e.g. "cancel order") — `cancelled` remains 93 an unused status, same as before, since nothing in the prototype produces it 94 and inventing a cancel flow was not what the review asked for.
Note:
See TracChangeset
for help on using the changeset viewer.
