Changeset 9577c79 for docs/P3-UseCaseModel/UseCase0005.md

Timestamp:

09/16/26 23:37:15 (13 days ago)

Author:

Stefan <trsunovstefan@…>

Branches:

main

Children:

8b447ef

Parents:

df05838

Message:

add reserved_quantity and modify the phases, add v_03.png and v_03.xml for P1

File:

• docs/P3-UseCaseModel/UseCase0005.md (modified) (3 diffs)

docs/P3-UseCaseModel/UseCase0005.md

@@ -6 +6 @@
 
 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.
+
+## Reserve, then settle
+
+The crypto being sold is **reserved** (`holdings.reserved_quantity`) before it
+is actually removed from the position, so the check a second sell order makes
+is always against what is truly still free (`quantity - reserved_quantity`),
+not against the raw `quantity`, which would also count crypto already
+promised to this order. Because only market orders are implemented, an order
+settles in the same database transaction it is placed in, so reserve and
+settle below are two statements inside one commit rather than two separate
+ones — the existing all-or-nothing guarantee (see
+[PrototypeImplementation](../P4-Prototype/PrototypeImplementation.md)) is
+kept. They stay logically distinct so that a future limit-order matcher —
+where an order really would sit `open` for a while before a *later*
+transaction settles it — needs only a second transaction where today there is
+one, not a schema change.
 
 ## Scenario
@@ -18 +34 @@
    BEGIN;
 
+   -- (a) record intent — no trade has happened yet.
    INSERT INTO project.orders
-       (user_id, market_id, side, type, status, quantity, price, executed_at)
+       (user_id, market_id, side, type, status, quantity, price)
    VALUES
-       ($user_id, $market_id, 'sell', 'market', 'executed', $qty, $price, now())
+       ($user_id, $market_id, 'sell', 'market', 'open', $qty, $price)
    RETURNING id;   -- $order_id
 
-   SELECT quantity, avg_price
+   -- (b) lock the holding and check what is actually free to sell.
+   SELECT quantity, reserved_quantity, avg_price
      FROM project.holdings
     WHERE user_id = $user_id AND crypto_id = $crypto_id
     FOR UPDATE;
-   -- abort if row missing or quantity < $qty
+   -- available := quantity - reserved_quantity
+   -- abort if row missing or available < $qty
    ```
-6. If the holding check passes, system reduces the holding, credits cash and debits invested, and appends a ledger and a market trade:
+
+6. If the check passes, system reserves the crypto, then — since this is a market order — settles it immediately, all inside the same transaction:
 
    ```sql
+   -- (c) reserve: committed to this order, not yet removed from the position.
    UPDATE project.holdings
-      SET quantity   = quantity - $qty,
-          updated_at = now()
+      SET reserved_quantity = reserved_quantity + $qty,
+          updated_at        = now()
+    WHERE user_id = $user_id AND crypto_id = $crypto_id;
+
+   -- (d) settle: release the reservation and remove the asset in one step.
+   UPDATE project.holdings
+      SET quantity          = quantity - $qty,
+          reserved_quantity = reserved_quantity - $qty,
+          updated_at        = now()
     WHERE user_id = $user_id AND crypto_id = $crypto_id;
 
@@ -54 +82 @@
        ($market_id, now(), $price, $qty, 'sell', 'user');
 
+   -- (e) settle the order itself — it has now actually been filled.
+   UPDATE project.orders
+      SET status = 'executed', executed_at = now()
+    WHERE id = $order_id;
+
    COMMIT;
    ```
+
 7. System confirms: `Order executed: sell 0.5000 ETH @ 3520.000000 (notional 1760.0000 USD)`.
 
 ### Alternate flow 5a — insufficient holding
 
-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."
+If the holding row is missing, or `quantity - reserved_quantity < $qty`, the
+entire transaction rolls back — including the `open` order from step 5, which
+was never committed — and system shows:
+`"Insufficient holding: trying to sell X, available Y (of Z held, W reserved)."`
+
+### Worked example — the case this fixes
+
+Alice holds 2 BTC, `reserved_quantity = 0`, and places `sell 0.5 BTC`:
+
+| | quantity | reserved_quantity | available |
+|---|---|---|---|
+| before | 2.0000 | 0.0000 | 2.0000 |
+| after step (c) — reserved | 2.0000 | 0.5000 | 1.5000 |
+| after step (d) — settled | 1.5000 | 0.0000 | 1.5000 |
+
+If a second sell for more than 1.5 BTC is placed concurrently, its own
+`SELECT … FOR UPDATE` in step 5b blocks until the first transaction commits,
+then sees the reduced `quantity` and correctly reports insufficient holding —
+proven under real concurrency in
+[UseCase0005Implementation](../P4-Prototype/UseCase0005Implementation.md).
 
 ### Realised P/L (post-scenario)