Changeset 9577c79 for docs/P4-Prototype


Ignore:
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

Location:
docs/P4-Prototype
Files:
7 edited

Legend:

Unmodified
Added
Removed

  • docs/P4-Prototype/BuildInstructions.md

    rdf05838 r9577c79  
    106106most recent trade (`v_latest_prices`), never from a stored column.
    107107
     108### 7. Optional — richer data for the P6 reports
     109
     110`data_load.sql` only seeds a few minutes of trade history, which is not enough
     111for the [top traders](../P6-AdvancedReports/AdvancedReports.md#top-traders-by-realized-performance)
     112or [market performance](../P6-AdvancedReports/AdvancedReports.md#market-performance-leaderboard)
     113reports (menu `[10]`/`[11]`) to show more than a single period. To see them do
     114something more interesting, load five quarters of synthetic history on top:
     115
     116```sh
     117psql "postgresql://$DBUSER:$DBPASSWORD@$DBHOST:$DBPORT/$DBNAME" \
     118  -f server/db/reports_demo_data.sql
     119```
     120
     121It is deliberately not part of `-init`/`-load-data` — see the header of
     122[`reports_demo_data.sql`](../../server/db/reports_demo_data.sql) for why — so
     123running it never changes the balances the smoke test below checks.
     124
    108125## Testing instructions
    109126
    … …  
    114131funds**, **Browse markets**, **Place market BUY order**, **Place market SELL
    115132order**, **View portfolio**, **View transaction history**, **Manage watchlist**,
    116 **Logout**.
     133**Logout**, and two [P6](../P6-AdvancedReports/AdvancedReports.md) reports:
     134**Report: top traders** and **Report: market performance**.
    117135
    118136You never have to remember an identifier. Markets are always printed as a
    … …  
    122140### End-to-end smoke test
    123141
    124 Verified on 2026-08-07 against PostgreSQL 16 with freshly loaded sample data.
     142Verified on 2026-09-16 against PostgreSQL 16 with freshly loaded sample data.
    125143Expected values are exact.
    126144
    1271451. `./eduberza -init` — prints `Database initialised.`
    1281462. `./eduberza`, then `[2] Login` → `alice` / `test123` → `Login successful.`
    129 3. `[6] View portfolio` → one row: `ETH 0.5000` at avg 3500.000000, current
    130    3520.000000, value 1760.0000, unrealised P/L `+10.0000`. Cash available
    131    8250.0000, net worth 10010.0000.
     1473. `[6] View portfolio` → one row: `ETH 0.5000` reserved 0.0000, available
     148   0.5000, at avg 3500.000000, current 3520.000000, value 1760.0000,
     149   unrealised P/L `+10.0000`. Cash available 8250.0000, net worth 10010.0000.
    1321504. `[4] Place market BUY order` → `BTC` → `0.01` →
    133151   `Order executed: buy 0.0100 BTC @ 67140.000000 (notional 671.4000 USD)`.
    … …  
    150168  to any table — no order row, no ledger entry, no holding.
    151169- **Insufficient holding:** as `bob` (no positions), try to sell `1` ETH.
    152   Expect `Insufficient holding: trying to sell 1.0000, hold 0.0000`.
     170  Expect `Insufficient holding: trying to sell 1.0000, available 0.0000 (of
     171  0.0000 held, 0.0000 reserved)`.
     172- **Two sell orders racing for the same crypto:** give `alice` a 2 BTC holding
     173  and start two `eduberza` processes at once, each selling `1.5` BTC (together
     174  3 BTC, more than she has). Expect exactly one `Order executed`, and the
     175  other `Insufficient holding` reading the post-commit quantity — see
     176  [UseCase0005Implementation](UseCase0005Implementation.md) for the exact
     177  transcript. This is the concurrency guarantee that
     178  `holdings.reserved_quantity` and the `SELECT ... FOR UPDATE` lock together
     179  provide.
    153180- **Duplicate registration:** register with username `alice`. Expect
    154181  `Username or email already taken.`

  • docs/P4-Prototype/PrototypeImplementation.md

    rdf05838 r9577c79  
    4646   `INSERT … ON CONFLICT DO UPDATE` upsert possible, so the weighted-average entry price is
    4747   recomputed by the database in one statement instead of by a read-modify-write in application
    48    code.
     48   code. `CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` is the same idea
     49   applied to the sell path: an inconsistent reservation is impossible at the database level, not
     50   just something `trade.go` is careful about.
     51 * '''Selling reserves before it removes.''' A sell order locks the holding row, reserves the
     52   quantity being sold, then settles by removing it — see
     53   [UseCase0005Implementation](UseCase0005Implementation.md). Two sell orders placed at the same
     54   instant for more than the available quantity are serialised correctly by `SELECT ... FOR
     55   UPDATE`, not just by luck of everything happening in one CLI process; this is demonstrated
     56   there with two concurrent processes.
    4957 * '''No identifiers are ever typed.''' Markets are listed with their prices before any choice is
    5058   made, and everything else is selected by symbol.
    … …  
    6371 * There is no connection pooling configuration and no explicit isolation level; both are P8
    6472   topics.
    65 == AI usage ==
    66 
    67 AI was used in this phase and is logged in full, per the course rule for P1 onward.
    68 
    69  * '''Phase log:'''
    70    [https://github.com/StefanTrsunov/bp/blob/main/docs/P3-UseCaseModel/UseCaseModelAIUsage.md UseCaseModelAIUsage.md]
    71    – service used, what the AI produced, and what I decided myself.
    72  * '''Full conversation transcript:'''
    73    [https://github.com/StefanTrsunov/bp/blob/main/docs/P1-ConceptualModel/ERModelAIUsage.md ERModelAIUsage.md]
    74    – the same conversation produced the P1–P4 artefacts, so the complete prompt/response log is
    75    kept in one place. Direct links:
    76    [https://github.com/StefanTrsunov/bp/blob/main/docs/P1-ConceptualModel/ERModelAIUsage.md#session-1--2026-04-21 Session 1 – 2026-04-21],
    77    [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].
    78 
    79 '''Service:''' Claude Code (Anthropic), https://claude.com/claude-code – Claude subscription,
    80 model Claude Opus 4.7 (1M context).
    81 
    82 '''In short:''' the AI proposed the actor taxonomy and drafted the seven use cases with their SQL
    83 in session 1. In session 2 the use-case model itself was '''not''' changed – the only work was
    84 re-executing every scenario, including the failure paths, against a live PostgreSQL 16 database.
     73 * Reservation only ever lives inside one transaction, because only market orders (which settle
     74   immediately) exist. A real limit-order matcher would leave `holdings.reserved_quantity` set
     75   and `orders.status = 'open'` between two separate commits, and would need a way to cancel an
     76   order to release the reservation — neither is implemented, since nothing in the prototype
     77   produces an order that stays open.
    8578
    8679== AI usage ==
    … …  
    9689   kept in one place. Direct links:
    9790   [https://github.com/StefanTrsunov/bp/blob/main/docs/P1-ConceptualModel/ERModelAIUsage.md#session-1--2026-04-21 Session 1 – 2026-04-21],
    98    [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].
     91   [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],
     92   [https://github.com/StefanTrsunov/bp/blob/main/docs/P1-ConceptualModel/ERModelAIUsage.md#session-3--2026-09-16 Session 3 – 2026-09-16].
    9993
    10094'''Service:''' Claude Code (Anthropic), https://claude.com/claude-code – Claude subscription,
    101 model Claude Opus 4.7 (1M context).
     95model Claude Opus 4.7 (1M context) in sessions 1–2, Claude Sonnet 5 in session 3.
    10296
    10397'''In short:''' session 1 rewrote the existing Chi/HTTP backend as the CLI prototype covering
    … …  
    106100infinite loop at end of input, and an error check in the wrong order that misreported database
    107101failures as "Insufficient holding" – and replaced the read-modify-write holding update with a
    108 single `INSERT … ON CONFLICT DO UPDATE`.
     102single `INSERT … ON CONFLICT DO UPDATE`. Session 3 added `holdings.reserved_quantity` and changed
     103`trade.go`'s sell path to reserve crypto before removing it, closing a gap where two sell orders
     104could be granted the same units; see
     105[PrototypeImplementationAIUsage](PrototypeImplementationAIUsage.md#session-3--2026-09-16).

  • docs/P4-Prototype/PrototypeImplementationAIUsage.md

    rdf05838 r9577c79  
    3939## Summary of AI involvement
    4040
    41 | | Session 1 — 2026-04-21 | Session 2 — 2026-08-06/07 |
    42 |---|---|---|
    43 | **What I brought** | My existing Go backend (Chi HTTP handlers) and a half-finished frontend | The CLI prototype as it stood after session 1 |
    44 | **What the AI did** | Rewrote the backend as a CLI covering UC0001–UC0007, wrote the market bot | Reviewed the code, found and fixed three bugs, improved the holding upsert |
    45 | **What I decided** | To delete the frontend, to build a CLI rather than a web app, to keep the market simulator | To ask for a code review pass rather than documentation alone |
     41| | Session 1 — 2026-04-21 | Session 2 — 2026-08-06/07 | Session 3 — 2026-09-16 |
     42|---|---|---|---|
     43| **What I brought** | My existing Go backend (Chi HTTP handlers) and a half-finished frontend | The CLI prototype as it stood after session 1 | A design review: the sell path had no way to reserve crypto committed to an order |
     44| **What the AI did** | Rewrote the backend as a CLI covering UC0001–UC0007, wrote the market bot | Reviewed the code, found and fixed three bugs, improved the holding upsert | Added `holdings.reserved_quantity`, changed the sell path to reserve-then-settle, gave `Orders.status` a real lifecycle, tested it live including under real concurrency |
     45| **What I decided** | To delete the frontend, to build a CLI rather than a web app, to keep the market simulator | To ask for a code review pass rather than documentation alone | To keep reserve+settle in one transaction rather than split it across two, since there is no cancel-order use case to recover a stuck reservation |
    4646
    4747The prototype was built in session 1 and worked. What session 2 added was a
    … …  
    116116> presentation. You will be asked how the buy transaction works, and the answer
    117117> has to be yours.
     118
     119### Session 3 — 2026-09-16
     120
     121Prompted by a design review I did myself, logged in full in
     122[ERModelAIUsage](../P1-ConceptualModel/ERModelAIUsage.md#session-3--2026-09-16).
     123The report: a user who owns 2 BTC and places a sell order for 0.5 BTC has that
     124crypto immediately removed from `quantity`, but nothing in the model recorded
     125that a *pending* order had already committed part of a position before it
     126settled — `holdings` had `quantity` and `avg_price` only, no equivalent of the
     127`available_balance`/`invested_balance` split already used for cash.
     128
     129**Bug fixed**
     130
     131`server/trade.go`'s sell path checked `held < qty` directly against
     132`holdings.quantity`. This happened to be safe against concurrent double-sells
     133only because the whole operation — order, holding check, holding update,
     134balance update, ledger, trade — runs inside one transaction with a
     135`SELECT ... FOR UPDATE` lock. It was not safe against the actual scenario
     136described: nothing distinguished "owned" from "owned, but already promised to
     137this order," which matters the moment an order can legitimately sit `open`
     138across more than one transaction — exactly what a limit-order matcher would
     139need, and what `orders.status` already implied was coming.
     140
     141**What changed**
     142
     1431. `holdings.reserved_quantity numeric(20,4) NOT NULL DEFAULT 0 CHECK
     144   (reserved_quantity >= 0 AND reserved_quantity <= quantity)` added to
     145   `schema_creation.sql`.
     1462. `v_portfolio` gained `reserved_quantity` and derived `available_quantity`.
     1473. `trade.go`'s sell path now: locks the holding, computes
     148   `available := quantity - reserved_quantity`, rejects if `available < qty`
     149   (previously: rejects if `quantity < qty`), reserves
     150   (`reserved_quantity += qty`), then settles (`quantity -= qty;
     151   reserved_quantity -= qty`) — two statements instead of one, kept inside the
     152   same transaction rather than split into two commits, which would risk an
     153   order stuck `open` with reserved crypto and no cancel command to free it.
     1544. Both `PlaceOrder` branches now insert the order as `status='open'` and
     155   `UPDATE ... SET status='executed', executed_at=now()` at the end, instead
     156   of inserting `'executed'` directly — `Orders.status` is now a real
     157   lifecycle rather than a label written once.
     1585. `portfolio.go` gained `Reserved`/`Available` columns, reading
     159   `v_portfolio.reserved_quantity`/`available_quantity`, so the new field is
     160   something a Trader can actually see.
     1616. The error message on the sell path changed from
     162   `"Insufficient holding: trying to sell X, hold Y"` to
     163   `"Insufficient holding: trying to sell X, available Y (of Z held, W
     164   reserved)"`, since "how much you hold" is no longer the only number that
     165   matters.
     166
     167**Test evidence**
     168
     169Verified against PostgreSQL 16 (`bp_database`, `localhost:5433`):
     170
     171```
     172$ eduberza sell 0.5 BTC  (Alice: 2.0000 BTC held, 0.0000 reserved)
     173Order executed: sell 0.5000 BTC @ 67140.000000 (notional 33570.0000 USD)
     174# holdings.quantity: 2.0000 -> 1.5000, reserved_quantity: 0.0000 (unchanged net of reserve+release)
     175
     176$ eduberza sell 1 ETH   (Bob: no holdings row at all)
     177Insufficient holding: trying to sell 1.0000, available 0.0000 (of 0.0000 held, 0.0000 reserved)
     178
     179# Two concurrent processes, Alice at 1.5 BTC / 0 reserved, each selling 1.0 BTC:
     180=== process A === Insufficient holding: trying to sell 1.0000, available 0.5000 (of 0.5000 held, 0.0000 reserved)
     181=== process B === Order executed: sell 1.0000 BTC @ 67140.000000 (notional 67140.0000 USD)
     182# final holding: quantity 0.5000, reserved_quantity 0.0000 — exactly one sell went through
     183
     184# Reserve visible mid-transaction, in one psql session (BEGIN; ...; COMMIT;):
     185before:            quantity 2.0000, reserved_quantity 0.0000, available 2.0000
     186after reserve:     quantity 2.0000, reserved_quantity 0.5000, available 1.5000
     187after settle:      quantity 1.5000, reserved_quantity 0.0000, available 1.5000
     188
     189# The CHECK constraint holds even without going through trade.go:
     190UPDATE holdings SET reserved_quantity = quantity + 1 WHERE ...;
     191ERROR:  new row for relation "holdings" violates check constraint "holdings_check"
     192```
     193
     194Full transcripts are on
     195[UseCase0005Implementation](UseCase0005Implementation.md). The rejected-order
     196rollback guarantee from session 2 was re-checked too: after both the
     197insufficient-funds and insufficient-holding failure paths, the affected user
     198still has zero new rows in `orders`.
     199
     200**What I decided:** to keep reserve and settle inside a single transaction —
     201splitting them into two, so an order genuinely sits `open` and reserved
     202between two commits, is what a real limit-order matcher will eventually need,
     203but building that now would add a way for an order to get stuck without also
     204building a way to cancel it, which is out of scope for this review. `go build
     205./...` was run after every change; all seven use cases were re-exercised
     206manually.

  • docs/P4-Prototype/UseCase0004Implementation.md

    rdf05838 r9577c79  
    2727   BEGIN;
    2828
    29    -- (a) record the order
     29   -- (a) record the order as 'open' — no trade has happened yet
    3030   INSERT INTO orders
    31        (user_id, market_id, side, type, status, quantity, price, executed_at)
     31       (user_id, market_id, side, type, status, quantity, price)
    3232   VALUES
    33        ($1, $2, 'buy', 'market', 'executed', $3, $4, now())
     33       ($1, $2, 'buy', 'market', 'open', $3, $4)
    3434   RETURNING id;
    3535
    … …  
    3737   SELECT available_balance FROM users WHERE id = $1 FOR UPDATE;
    3838
    39    -- (c) move cash from available to invested
     39   -- (c) move cash from available to invested. A buy never reserves crypto
     40   --     the way a sell does (see UC0005) — it only ever adds to the
     41   --     position, so there is nothing to commit on the holdings side
     42   --     before settling.
    4043   UPDATE users
    4144      SET available_balance = available_balance - $notional,
    … …  
    4750   --     in one statement. Every SET expression sees the pre-update row, so
    4851   --     holdings.quantity below is still the old quantity.
     52   --     reserved_quantity is untouched by a buy and defaults to 0.
    4953   INSERT INTO holdings (user_id, crypto_id, quantity, avg_price, updated_at)
    5054   VALUES ($1, $c, $3, $4, now())
    … …  
    6872       ($2, now(), $4, $3, 'buy', 'user');
    6973
     74   -- (g) settle the order itself — it has now actually been filled
     75   UPDATE orders SET status = 'executed', executed_at = now() WHERE id = $orderId;
     76
    7077   COMMIT;
    7178   ```
    … …  
    7784## Verified run (from actual prototype execution)
    7885
    79 With seed data loaded:
     86Re-run 2026-09-16 against PostgreSQL 16 (`bp_database` on `localhost:5433`) with freshly loaded seed data:
    8087
    81 - **Before:** alice.available_balance = 8250.00, portfolio = { ETH: 0.5 }.
     88- **Before:** alice.available_balance = 8250.00, portfolio = { ETH: 0.5000, reserved 0.0000 }.
    8289- **Command:** `buy 0.01 BTC`.
    83 - **After:** alice.available_balance = 7578.60 (= 8250 − 671.40), portfolio = { BTC: 0.01 @ 67140, ETH: 0.5 @ 3500 }, net worth = 10010.00 USD (the +10 is the ETH unrealised P/L from the price moving from 3500 → 3520).
     90- **After:** alice.available_balance = 7578.60 (= 8250 − 671.40), portfolio = { BTC: 0.0100 @ 67140 (reserved 0.0000), ETH: 0.5000 @ 3500 (reserved 0.0000) }, net worth = 10010.00 USD (the +10 is the ETH unrealised P/L from the price moving from 3500 → 3520). A buy never sets `reserved_quantity`, so it reads 0 on every row here.
    8491
    8592## Failure path — insufficient funds
    8693
    87 If `available_balance < notional`, the `defer tx.Rollback()` in `server/trade.go` reverts all six statements and the user sees:
     94If `available_balance < notional`, the `defer tx.Rollback()` in `server/trade.go` reverts every statement above and the user sees:
    8895
    8996```

  • docs/P4-Prototype/UseCase0005Implementation.md

    rdf05838 r9577c79  
    22
    33**Initiating actor:** Trader. **Source file:** `server/trade.go`, function `PlaceOrder(s, "sell")`.
     4
     5## The bug this closes
     6
     7Before this change, `holdings` had `quantity` and `avg_price` only. The sell
     8path checked `held < qty` straight against `quantity`, which cannot tell
     9"owned" apart from "owned, but already committed to another order that has
     10not settled." `holdings.reserved_quantity` fixes that: the crypto being sold
     11is reserved before it is removed from the position, and the check is against
     12`quantity - reserved_quantity`.
    413
    514## Scenario (implemented)
    … …  
    1322   BEGIN;
    1423
     24   -- (a) record the order as 'open' — no trade has happened yet
    1525   INSERT INTO orders
    16        (user_id, market_id, side, type, status, quantity, price, executed_at)
     26       (user_id, market_id, side, type, status, quantity, price)
    1727   VALUES
    18        ($1, $2, 'sell', 'market', 'executed', $3, $4, now())
     28       ($1, $2, 'sell', 'market', 'open', $3, $4)
    1929   RETURNING id;
    2030
    21    SELECT quantity, avg_price FROM holdings
     31   -- (b) lock the holding and check what is actually free to sell
     32   SELECT quantity, reserved_quantity, avg_price FROM holdings
    2233    WHERE user_id = $1 AND crypto_id = $c FOR UPDATE;
    23    -- abort if missing or insufficient
    24 
     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
    2538   UPDATE holdings
    26       SET quantity = quantity - $qty, updated_at = now()
     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
     44   UPDATE holdings
     45      SET quantity = quantity - $qty,
     46          reserved_quantity = reserved_quantity - $qty,
     47          updated_at = now()
    2748    WHERE user_id = $1 AND crypto_id = $c;
    2849
    … …  
    4364       ($2, now(), $price, $qty, 'sell', 'user');
    4465
     66   -- (e) settle the order itself — it has now actually been filled
     67   UPDATE orders SET status = 'executed', executed_at = now() WHERE id = $orderId;
     68
    4569   COMMIT;
    4670   ```
    … …  
    5276## Failure path — insufficient holding
    5377
    54 If the holding does not exist or `quantity < requested`, the `defer tx.Rollback()` in `server/trade.go` reverts every statement above and the user sees:
    55 
    56 ```
    57 Insufficient holding: trying to sell X, hold Y
    58 ```
     78If 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```
     81Insufficient 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
     86Run 2026-09-16 against PostgreSQL 16 (`bp_database` on `localhost:5433`).
     87Alice's ETH/BTC holdings were seeded, then her BTC holding was set to exactly
     88the 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```
     115Order 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
     126designed.
     127
     128## Verified run — reserve and settle as two distinct, observable steps
     129
     130The 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
     132hand in one `psql` session (one transaction, so the session sees its own
     133uncommitted writes) to show the intermediate state that step (c) alone would
     134leave, before step (d) runs:
     135
     136```sql
     137BEGIN;
     138
     139-- before: Alice owns 2 BTC, none reserved
     140SELECT quantity, reserved_quantity, quantity - reserved_quantity AS available
     141  FROM holdings WHERE user_id = '<alice>' AND crypto_id = '<btc>';
     142--  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
     147UPDATE holdings SET reserved_quantity = reserved_quantity + 0.5, updated_at = now()
     148 WHERE user_id = '<alice>' AND crypto_id = '<btc>';
     149
     150SELECT quantity, reserved_quantity, quantity - reserved_quantity AS available
     151  FROM holdings WHERE user_id = '<alice>' AND crypto_id = '<btc>';
     152--  quantity | reserved_quantity | available
     153-- ----------+--------------------+-----------
     154--    2.0000 |             0.5000 |    1.5000
     155
     156-- step (d): market order settles immediately, reservation released
     157UPDATE 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
     160SELECT quantity, reserved_quantity, quantity - reserved_quantity AS available
     161  FROM holdings WHERE user_id = '<alice>' AND crypto_id = '<btc>';
     162--  quantity | reserved_quantity | available
     163-- ----------+--------------------+-----------
     164--    1.5000 |             0.0000 |    1.5000
     165
     166COMMIT;
     167```
     168
     169This is the row that would stay visible to every other connection for as long
     170as the order stayed `open` — i.e. for as long as it took a matcher to fill
     171it, once limit orders exist.
     172
     173## Verified run — two concurrent sells, which is the bug itself
     174
     175The scenario the design review described: a user should not be able to place
     176two sell orders whose combined quantity exceeds what they actually hold. With
     177Alice's BTC holding at 1.5 BTC (0 reserved), two independent CLI processes
     178were started at the same instant, each selling `1.0 BTC` — together 2.0 BTC,
     179more 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 ===
     187Insufficient holding: trying to sell 1.0000, available 0.5000 (of 0.5000 held, 0.0000 reserved)
     188=== B ===
     189Order 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
     197One 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
     1991.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
     201there 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
     207UPDATE holdings SET reserved_quantity = quantity + 1 WHERE user_id = '<alice>' AND crypto_id = '<btc>';
     208
     209ERROR:  new row for relation "holdings" violates check constraint "holdings_check"
     210```
     211
     212`CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` in
     213`schema_creation.sql` makes an inconsistent reservation impossible at the
     214database level, independent of `trade.go`.

  • docs/P4-Prototype/UseCase0006Implementation.md

    rdf05838 r9577c79  
    1212   ```sql
    1313   SELECT symbol, quantity,
     14          COALESCE(reserved_quantity,  0),
     15          COALESCE(available_quantity, quantity),
    1416          COALESCE(avg_price,      0),
    1517          COALESCE(current_price,  0),
    … …  
    3032### Verified run
    3133
    32 With the seed data (`data_load.sql`), immediately after login, alice's portfolio prints:
     34Re-run 2026-09-16 against PostgreSQL 16 with the seed data (`data_load.sql`).
     35Immediately after login, alice's portfolio prints (now with the
     36`Reserved`/`Available` columns from `holdings.reserved_quantity`):
    3337
    3438```
    35   Symbol        Quantity         Avg buy         Current           Value  Unrealised P/L
    36   ------------------------------------------------------------------------------------
    37   ETH             0.5000     3500.000000     3520.000000       1760.0000        +10.0000
    38   ------------------------------------------------------------------------------------
    39   TOTAL                                                        1760.0000        +10.0000
     39  Symbol        Quantity      Reserved     Available         Avg buy         Current           Value  Unrealised P/L
     40  ------------------------------------------------------------------------------------------------------------------
     41  ETH             0.5000        0.0000        0.5000     3500.000000     3520.000000       1760.0000        +10.0000
     42  ------------------------------------------------------------------------------------------------------------------
     43  TOTAL                                                                                    1760.0000        +10.0000
    4044
    4145  Cash available : 8250.0000 USD
    … …  
    4347  Net worth      : 10010.0000 USD
    4448```
     49
     50`Reserved` is 0.0000 here because nothing is mid-sell; see
     51[UseCase0005Implementation](UseCase0005Implementation.md) for a portfolio
     52snapshot taken with crypto actually reserved.
    4553
    4654### Transaction history
Note: See TracChangeset for help on using the changeset viewer.