= Use-case 0005 Implementation - Place market SELL order =

'''Initiating actor:''' Trader

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

A logged-in Trader sells part or all of a holding at the current market price. The
Trader never types a symbol: the system lists only the cryptos the Trader holds and can
still sell (the quantity not already reserved by an open sell order), numbered, with
how much is held and how much is free, and the Trader picks one by its number and
enters the quantity. In one database transaction the system records the order,
reserves the crypto being sold and settles it, credits the proceeds to the Trader's
available cash while reducing the invested cash by the cost basis, writes a ledger
entry and a market trade, and marks the order executed. Cost basis is preserved, so
the realised P/L can be reconstructed from the ledger.

Original use-case description (P3): [wiki:UseCase0005].
Implementation: `server/trade.go`, function
`PlaceOrder(s, "sell")`, which calls `ChooseHolding`, `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` in `server/db/db.go`). 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` right after the buy of
[wiki:UseCase0004Implementation UseCase0004]: 7578.60 USD available, holdings
0.01 BTC (bought at 67140) and 0.5 ETH (bought at 3500). She sells 0.2 ETH.

== Reserve, then settle ==

The crypto being sold is '''reserved''' (`holdings.reserved_quantity`) before it is
removed from the position, and the sell check is against what is truly still free,
`quantity - reserved_quantity`, not against the raw `quantity`, which would also count
crypto already promised to another order. Because only market orders are implemented,
an order settles in the same transaction it is placed in, so reserve and settle are two
statements inside one commit; they stay logically distinct so that a future
limit-order matcher, where an order would stay `open` until a ''later'' transaction fills
it, needs a second transaction but no schema change.

== Scenario ==

 1. '''Trader''' chooses `[5] Place market SELL order` in the authenticated menu (types `5`).
 2. '''System''' prints `-- Place market sell order --` and lists, numbered, only the
    cryptos the Trader holds with some quantity still free to sell, with the quantity
    held, the quantity free to sell and the last price (`ChooseHolding`;
    `$1` = the logged-in user's id):

{{{
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 holdings h
  JOIN crypto  c ON c.id = h.crypto_id
  JOIN markets m ON m.crypto_id = c.id AND m.is_active = true
  LEFT JOIN 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
}}}

For alice it prints `1 BTC USD 0.0100 0.0100 67140.000000` and
`2 ETH USD 0.5000 0.5000 3520.000000`, then asks `Holding #:`. Go keeps each row's
market id and crypto id in memory; the Trader only types the list number. (If the
query returns no row, the system prints `you hold no crypto that is free to sell`
and the use-case ends.)

[[Image(uc0005_1_2_holdings.png)]]

 3. '''Trader''' picks the holding by its number in the list: `2` (ETH).
 4. '''System''' takes the market id and crypto id of row 2 from the list 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 ETH/USD = 3520.000000` and asks `Quantity:`.

[[Image(uc0005_3_4_price.png)]]

 5. '''Trader''' enters the quantity `0.2`.
 6. '''System''' computes in Go notional = quantity × price = 0.2 × 3520 = 704.00 and
    runs one database transaction; the statements are in exactly the order
    `PlaceOrder` executes them for a sell. After statement (b) Go also computes the
    cost basis = avg_price × quantity = 3500 × 0.2 = 700.00 from the locked holding row;
    both values are passed to SQL as parameters.

{{{
BEGIN;

-- (a) record the order as 'open' — no trade has happened yet.
--     $1 = user id, $2 = market id, $3 = side (the Go variable side = 'sell'),
--     $4 = quantity (0.2), $5 = price (3520); 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 holding row and read what is held, what is already reserved and
--     the average entry price. $1 = user id, $2 = crypto id.
--     Go computes available = quantity - reserved_quantity (0.5 - 0 = 0.5);
--     if there is no row or available < quantity -> alternate flow 5a.
SELECT quantity, reserved_quantity, avg_price FROM holdings
  WHERE user_id = $1 AND crypto_id = $2 FOR UPDATE;

-- (c) reserve: committed to this order, not yet removed from the position.
--     $1 = quantity (0.2), $2 = user id, $3 = crypto id.
UPDATE holdings
    SET reserved_quantity = reserved_quantity + $1,
        updated_at        = now()
  WHERE user_id = $2 AND crypto_id = $3;

-- (d) settle: a market order fills immediately, so release the reservation and
--     remove the asset from the position in one step. Same parameters as (c).
UPDATE holdings
    SET quantity          = quantity - $1,
        reserved_quantity = reserved_quantity - $1,
        updated_at        = now()
  WHERE user_id = $2 AND crypto_id = $3;

-- (e) credit the proceeds; reduce invested cash by the cost basis.
--     $1 = notional (704.00), $2 = cost basis (700.00), $3 = user id.
UPDATE users
    SET available_balance = available_balance + $1,
        invested_balance  = GREATEST(invested_balance - $2, 0),
        updated_at        = now()
  WHERE id = $3;

-- (f) ledger entry. $1 = user id, $2 = notional (704.00), $3 = order id from (a),
--     $4 = description built in Go: 'Market sell 0.2000 ETH @ 3520.000000'.
INSERT INTO transactions (user_id, type, amount, currency, related_order, description)
 VALUES ($1, 'sell', $2, 'USD', $3, $4);

-- (g) record the resulting market trade.
--     $1 = market id, $2 = price, $3 = quantity, $4 = side ('sell').
INSERT INTO market_trades (market_id, executed_at, price, quantity, side, source)
 VALUES ($1, now(), $2, $3, $4, 'user');

-- (h) 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;
}}}

 7. '''System''' confirms
    `Order executed: sell 0.2000 ETH @ 3520.000000 (notional 704.0000 USD)` and shows
    the authenticated menu again.

The screenshot shows steps 5–7: the entered quantity, the confirmation and the menu.

[[Image(uc0005_5_7_executed.png)]]

After this run the database holds for alice: ETH `quantity` 0.3000 with
`reserved_quantity` 0.0000; `available_balance` 8282.60 (= 7578.60 + 704.00) and
`invested_balance` 1721.40 (= 2421.40 − 700.00); a `sell` row in `transactions` with
amount 704.0000 and description `Market sell 0.2000 ETH @ 3520.000000`; and the order
with status `executed`. The realised P/L of this sell is notional − cost basis =
704.00 − 700.00 = +4.00 USD.

=== Alternate flow 5a — insufficient holding ===

Right after the sell above, alice chooses `[5]` again. The list from step 2 now shows
`2 ETH USD 0.3000 0.3000 3520.000000`. She picks `2` (ETH) and enters quantity `5`.
In the transaction, statement (a) inserts the `open` order and statement (b) returns
quantity 0.3000 and reserved_quantity 0.0000, so available = 0.3 < 5. `PlaceOrder`
prints

{{{
Insufficient holding: trying to sell 5.0000, available 0.3000 (of 0.3000 held, 0.0000 reserved)
}}}

and returns without running (c)–(h); the deferred `tx.Rollback()` undoes statement (a)
as well, so no order, no reservation and no ledger entry is left behind. The
authenticated menu is shown again. The same message is printed if the holding row no
longer exists (for example because it was sold out from another session after the
list was shown).

[[Image(uc0005_5a_insufficient.png)]]

== Reserve and settle, step by step ==

The CLI reserves and settles inside one transaction, so `reserved_quantity` is never
nonzero ''outside'' a transaction. The intermediate state is shown by running statements
(c) and (d) by hand in one `psql` transaction (which sees its own uncommitted
writes) against alice's ETH holding after the scenario above (0.3 ETH), for a sell of
0.1, and rolling back at the end so nothing is changed. Literal values replace the
`$n` parameters; `:alice` and `:eth` are psql variables for
`(SELECT id FROM users WHERE username = 'alice')` and
`(SELECT id FROM crypto WHERE symbol = 'ETH')`:

{{{
BEGIN;
SELECT quantity, reserved_quantity, quantity - reserved_quantity AS available
  FROM holdings WHERE user_id = :alice AND crypto_id = :eth;
--  quantity | reserved_quantity | available
-- ----------+-------------------+-----------
--    0.3000 |            0.0000 |    0.3000

-- (c) reserve 0.1: the order is placed, no trade has happened yet
UPDATE holdings SET reserved_quantity = reserved_quantity + 0.1, updated_at = now()
 WHERE user_id = :alice AND crypto_id = :eth;
SELECT quantity, reserved_quantity, quantity - reserved_quantity AS available
  FROM holdings WHERE user_id = :alice AND crypto_id = :eth;
--  quantity | reserved_quantity | available
-- ----------+-------------------+-----------
--    0.3000 |            0.1000 |    0.2000

-- (d) settle: the reservation is released and the asset removed
UPDATE holdings SET quantity = quantity - 0.1, reserved_quantity = reserved_quantity - 0.1, updated_at = now()
 WHERE user_id = :alice AND crypto_id = :eth;
SELECT quantity, reserved_quantity, quantity - reserved_quantity AS available
  FROM holdings WHERE user_id = :alice AND crypto_id = :eth;
--  quantity | reserved_quantity | available
-- ----------+-------------------+-----------
--    0.2000 |            0.0000 |    0.2000
ROLLBACK;
}}}

The middle state is what every other connection would see for as long as an order
stayed `open` once limit orders exist: 0.1 ETH still owned but no longer free to sell.

== Two concurrent sells ==

A Trader must not be able to sell the same units twice from two sessions at once. Both
sessions may have listed the holding as free (step 2 runs outside the transaction), so
the protection is statement (b): `SELECT ... FOR UPDATE` locks the holding row, and a
second transaction that reaches (b) waits until the first one commits, then reads the
already reduced `quantity` before deciding.

This was checked with two `psql` sessions running statements (b)–(d) against alice's
0.3 ETH. Session A locked the row, reserved and settled 0.2 ETH and committed after a
3-second pause; session B asked for the lock one second after A had taken it:

{{{
A: SELECT ... FOR UPDATE  ->  quantity 0.3000, reserved_quantity 0.0000
A: reserve 0.2, settle 0.2, pg_sleep(3)
B: 11:43:54  SELECT ... FOR UPDATE   -- blocks, A holds the row lock
A: 11:43:56  COMMIT
B: 11:43:56  lock granted  ->  quantity 0.1000, reserved_quantity 0.0000
}}}

Session B was blocked for the two seconds until A committed and then saw only
0.1 ETH, so a second 0.2 ETH sell in B takes alternate flow 5a
(`available 0.1000`) instead of selling units that no longer exist. (B was rolled
back and alice's holding was restored to 0.3 ETH after the check.)

== The constraint holds even if the application code did not ==

`schema_creation.sql` declares
`CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` on
`holdings.reserved_quantity`, so an inconsistent reservation is impossible at the
database level, independently of `trade.go` (run inside a transaction that was
rolled back):

{{{
UPDATE holdings SET reserved_quantity = quantity + 1 WHERE user_id = :alice AND crypto_id = :eth;
ERROR:  new row for relation "holdings" violates check constraint "holdings_check"
}}}

== Source code ==

`server/trade.go` — `PlaceOrder` (buy and sell):

{{{
// 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)
}
}}}

`server/market.go` — `pickNumber`, `ChooseHolding` and `LatestPrice`:

{{{
// 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
}

// ChooseHolding lists only the markets the user can sell in — cryptos they
// hold with some quantity still free (not reserved by an open sell order) —
// with how much is held and free, and lets them pick one by number.
func ChooseHolding(s *Session) (*Market, error) {
	rows, err := db.DB.Query(`
		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 holdings h
		  JOIN crypto  c ON c.id = h.crypto_id
		  JOIN markets m ON m.crypto_id = c.id AND m.is_active = true
		  LEFT JOIN 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`, s.UserID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	fmt.Println()
	fmt.Printf("  %-4s  %-8s  %-5s  %12s  %12s  %15s\n", "#", "Symbol", "Quote", "Held", "Free to sell", "Last price")
	fmt.Println("  -------------------------------------------------------------------")
	var list []Market
	for rows.Next() {
		var m Market
		var held, free, price float64
		if err := rows.Scan(&m.ID, &m.CryptoID, &m.Symbol, &m.Quote, &held, &free, &price); err != nil {
			return nil, err
		}
		list = append(list, m)
		fmt.Printf("  %-4d  %-8s  %-5s  %12.4f  %12.4f  %15.6f\n", len(list), m.Symbol, m.Quote, held, free, price)
	}
	if len(list) == 0 {
		return nil, fmt.Errorf("you hold no crypto that is free to sell")
	}
	k, err := pickNumber("Holding #: ", 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
}
}}}