source: docs/P3-UseCaseModel/wiki/UseCase0005.md@ 0cee8ec

= Use-case 0005 — Place market SELL order =

'''Initiating actor:''' Trader

'''Other actors:''' Market Simulator (indirect — supplies the current price).

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
[wiki:PrototypeImplementation]) 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 ==

 1. Trader chooses "Place market SELL order".
 2. System lists, numbered, the Trader's holdings that still have a quantity free to
    sell (not reserved by an open sell order), with the quantity held, the free
    quantity and the latest price:

{{{
SELECT m.id, c.id, c.symbol, m.quote_currency,
       h.quantity, h.quantity - h.reserved_quantity AS free,
       COALESCE(lp.price, 0) AS price
  FROM project.holdings h
  JOIN project.crypto  c ON c.id = h.crypto_id
  JOIN project.markets m ON m.crypto_id = c.id AND m.is_active = true
  LEFT JOIN project.v_latest_prices lp ON lp.market_id = m.id
 WHERE h.user_id = $1
   AND h.quantity - h.reserved_quantity > 0
 ORDER BY c.symbol;
}}}

 3. Trader picks the holding by its number in the listed holdings, e.g. `2` (ETH), and
    enters the quantity.
 4. System takes the chosen row's market id and crypto id from the list (no lookup by
    symbol) and looks up the latest price:

{{{
SELECT price FROM project.v_latest_prices WHERE market_id = $1;
}}}

 5. System opens a transaction:

{{{
BEGIN;

-- (a) record intent — no trade has happened yet.
INSERT INTO project.orders
    (user_id, market_id, side, type, status, quantity, price)
VALUES
    ($user_id, $market_id, 'sell', 'market', 'open', $qty, $price)
RETURNING id;   -- $order_id

-- (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;
-- available := quantity - reserved_quantity
-- abort if row missing or available < $qty
}}}

 6. If the check passes, system reserves the crypto, then — since this is a market order — settles it immediately, all inside the same transaction:

{{{
-- (c) reserve: committed to this order, not yet removed from the position.
UPDATE project.holdings
   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;

UPDATE project.users
   SET available_balance = available_balance + $notional,
       invested_balance  = GREATEST(invested_balance - ($avg_price * $qty), 0),
       updated_at        = now()
 WHERE id = $user_id;

INSERT INTO project.transactions
    (user_id, type, amount, currency, related_order, description)
VALUES
    ($user_id, 'sell', $notional, 'USD', $order_id, 'Market sell ...');

INSERT INTO project.market_trades
    (market_id, executed_at, price, quantity, side, source)
VALUES
    ($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 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
[wiki:UseCase0005Implementation].

=== Realised P/L (post-scenario) ===

The realised P/L for a sell is `$notional - ($avg_price * $qty)`. It is not persisted explicitly but can be computed from the ledger and the holding at sell time.