Index: docs/P1-ConceptualModel/ERModel.md
===================================================================
--- docs/P1-ConceptualModel/ERModel.md (revision df058387ebe7d07864d874fd1376f15512418581)
+++ docs/P1-ConceptualModel/ERModel.md (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4)
@@ -1,8 +1,7 @@
-# Entity-Relationship Model v.02
+# Entity-Relationship Model v.03
## Diagram
-
-
+
Notation: Chen. Rectangles are entity sets, diamonds are relationships, ellipses
@@ -23,4 +22,9 @@
## Data requirements
+Each entity set is given as a short rationale for why it exists as its own set,
+its keys, and its attributes as a table. Each relationship is given as its
+cardinality and participation, a short rationale, and — where it carries data —
+an attribute table.
+
### Entity sets
@@ -28,24 +32,25 @@
Registered participants of the platform. Every action in the simulation is
attributed to a user, and the two balance attributes are what makes the
-simulation work: cash that is free to trade is tracked separately from cash that
-is currently committed to open positions, so the platform can refuse a purchase
-without having to recompute the whole portfolio first.
-
-- **Candidate keys:** `{id}`, `{username}`, `{email}`. Primary key: **`id`**.
- A surrogate UUID was chosen because it is opaque and stable — `username` and
- `email` are both things a user may legitimately want to change later, and
- every relationship in the diagram points at `Users`, so a mutable key would
- propagate changes across the whole database.
-- **Attributes:**
- - `id` — UUID, required, primary key.
- - `username` — text, max 50, required, unique.
- - `email` — text, max 255, required, unique, must contain `@`.
- - `full_name` — text, max 200, optional.
- - `password_hash` — text, max 255, required. Never the password itself; the
- prototype stores a SHA-256 hex digest.
- - `available_balance` — numeric(18,4), required, default 0, must be ≥ 0.
- - `invested_balance` — numeric(18,4), required, default 0, must be ≥ 0.
- - `created_at` — timestamp with time zone, required, defaults to now.
- - `updated_at` — timestamp with time zone, optional (null until first change).
+simulation work: cash that is free to trade is tracked separately from cash
+that is currently committed to open positions, so the platform can refuse a
+purchase without having to recompute the whole portfolio first.
+
+**Keys:** candidates `{id}`, `{username}`, `{email}`; primary key **`id`**. A
+surrogate UUID was chosen because it is opaque and stable — `username` and
+`email` are both things a user may legitimately want to change later, and
+every relationship in the diagram points at `Users`, so a mutable key would
+propagate changes across the whole database.
+
+| Attribute | Type | Constraints |
+|---|---|---|
+| `id` | UUID | PK, required |
+| `username` | text(50) | required, unique |
+| `email` | text(255) | required, unique, contains `@` |
+| `full_name` | text(200) | optional |
+| `password_hash` | text(255) | required — never the password itself; the prototype stores a SHA-256 hex digest |
+| `available_balance` | numeric(18,4) | required, default 0, ≥ 0 |
+| `invested_balance` | numeric(18,4) | required, default 0, ≥ 0 |
+| `created_at` | timestamptz | required, defaults to now |
+| `updated_at` | timestamptz | optional (null until first change) |
#### Cryptos
@@ -55,12 +60,14 @@
in the *asset*, not in a particular pair.
-- **Candidate keys:** `{id}`, `{symbol}`. Primary key: **`id`**, for the same
- reason as in `Users`; `symbol` is kept as a unique natural key because that is
- what users type and see.
-- **Attributes:**
- - `id` — UUID, required, primary key.
- - `symbol` — text, max 20, required, unique (e.g. `BTC`).
- - `name` — text, max 255, required (e.g. `Bitcoin`).
- - `created_at` — timestamptz, required, defaults to now.
+**Keys:** candidates `{id}`, `{symbol}`; primary key **`id`**, for the same
+reason as in `Users`. `symbol` is kept as a unique natural key because that is
+what users type and see.
+
+| Attribute | Type | Constraints |
+|---|---|---|
+| `id` | UUID | PK, required |
+| `symbol` | text(20) | required, unique (e.g. `BTC`) |
+| `name` | text(255) | required (e.g. `Bitcoin`) |
+| `created_at` | timestamptz | required, defaults to now |
#### Markets
@@ -71,14 +78,15 @@
trades, candles and orders all reference the pair, not the asset.
-- **Candidate keys:** `{id}`, `{crypto_id, quote_currency}` — that pair is
- unique by definition, since a given asset can only be quoted once per
- currency. Primary key: **`id`**, so that the many entity sets referencing a
- market carry one narrow column instead of a composite key.
-- **Attributes:**
- - `id` — UUID, required, primary key.
- - `quote_currency` — text, exactly 3 characters, required, default `USD`.
- - `is_active` — boolean, required, default true. Inactive markets are hidden
- from the trading menus but keep their history.
- - `created_at` — timestamptz, required, defaults to now.
+**Keys:** candidates `{id}`, `{crypto_id, quote_currency}` — that pair is
+unique by definition, since a given asset can only be quoted once per
+currency; primary key **`id`**, so that the many entity sets referencing a
+market carry one narrow column instead of a composite key.
+
+| Attribute | Type | Constraints |
+|---|---|---|
+| `id` | UUID | PK, required |
+| `quote_currency` | text(3) | required, default `USD` |
+| `is_active` | boolean | required, default true — inactive markets are hidden from the trading menus but keep their history |
+| `created_at` | timestamptz | required, defaults to now |
#### Orders
@@ -88,19 +96,29 @@
makes the ledger auditable.
-- **Candidate keys:** `{id}` only. There is no natural key — the same user can
- place two identical orders on the same market in the same second, and both are
- legitimately distinct. Primary key: **`id`**.
-- **Attributes:**
- - `id` — UUID, required, primary key.
- - `side` — text, required, restricted to `buy` or `sell`.
- - `type` — text, required, restricted to `market` or `limit`. The prototype
- executes only `market` orders; `limit` exists so the model does not have to
- change when limit orders are implemented.
- - `status` — text, required, restricted to `open`, `executed`, `cancelled`.
- - `quantity` — numeric(20,4), required, must be > 0.
- - `price` — numeric(18,6), optional — null for a market order until it fills,
- then the fill price.
- - `placed_at` — timestamptz, required, defaults to now.
- - `executed_at` — timestamptz, optional, set when the order fills.
+Placing an order is what triggers a **reservation** of whatever it commits —
+the crypto being sold (`Holds.reserved_quantity`, below) on a sell, cash
+already handled the same way on a buy via `available_balance` /
+`invested_balance`. `status` therefore has real meaning as a lifecycle, not
+just a label: `open` means reserved but not yet settled, `executed` means
+settled, `cancelled` would release the reservation without settling (not yet
+exercised by any use case, since only market orders — which settle
+immediately — are implemented). See
+[UseCase0005](../P3-UseCaseModel/UseCase0005.md) for the reserve-then-settle
+sequence.
+
+**Keys:** candidate `{id}` only — there is no natural key, since the same user
+can place two identical orders on the same market in the same second, and both
+are legitimately distinct; primary key **`id`**.
+
+| Attribute | Type | Constraints |
+|---|---|---|
+| `id` | UUID | PK, required |
+| `side` | text | required, `buy` or `sell` |
+| `type` | text | required, `market` or `limit` — the prototype executes only `market`; `limit` exists so the model does not have to change when limit orders are implemented |
+| `status` | text | required, `open`, `executed` or `cancelled` |
+| `quantity` | numeric(20,4) | required, > 0 |
+| `price` | numeric(18,6) | optional — null until the order settles, then the fill price |
+| `placed_at` | timestamptz | required, defaults to now |
+| `executed_at` | timestamptz | optional, set when the order settles |
#### Transactions
@@ -110,13 +128,14 @@
the project needs.
-- **Candidate keys:** `{id}` only. Primary key: **`id`**.
-- **Attributes:**
- - `id` — UUID, required, primary key.
- - `type` — text, required, restricted to `deposit`, `buy`, `sell`, `fee`.
- - `amount` — numeric(18,4), required. Signed: negative for money leaving the
- cash balance, positive for money arriving.
- - `currency` — text, exactly 3 characters, required, default `USD`.
- - `created_at` — timestamptz, required, defaults to now.
- - `description` — text, optional, free-form human-readable explanation.
+**Keys:** candidate `{id}` only; primary key **`id`**.
+
+| Attribute | Type | Constraints |
+|---|---|---|
+| `id` | UUID | PK, required |
+| `type` | text | required, `deposit`, `buy`, `sell` or `fee` |
+| `amount` | numeric(18,4) | required, signed — negative for money leaving the cash balance, positive for money arriving |
+| `currency` | text(3) | required, default `USD` |
+| `created_at` | timestamptz | required, defaults to now |
+| `description` | text | optional, free-form |
#### MarketTrades
@@ -126,17 +145,18 @@
writes directly.
-- **Candidate keys:** `{id}`. In principle `{market_id, executed_at}` looks
- unique, but two trades can share a timestamp, so it is not a safe key.
- Primary key: **`id`** (a plain auto-incrementing integer here rather than a
- UUID, because this is the highest-volume entity set and it is only ever read
- in timestamp order, never referenced by anything else).
-- **Attributes:**
- - `id` — integer, required, primary key, auto-generated.
- - `executed_at` — timestamptz, required.
- - `price` — numeric(18,6), required, must be > 0.
- - `quantity` — numeric(20,6), required, must be > 0.
- - `side` — text, optional, `buy` or `sell`.
- - `source` — text, max 50, required, default `simulation`. Distinguishes a
- simulated trade from a user's own fill (`user`).
+**Keys:** candidate `{id}` — `{market_id, executed_at}` looks unique in
+principle, but two trades can share a timestamp, so it is not a safe key;
+primary key **`id`** (a plain auto-incrementing integer here rather than a
+UUID, because this is the highest-volume entity set and it is only ever read
+in timestamp order, never referenced by anything else).
+
+| Attribute | Type | Constraints |
+|---|---|---|
+| `id` | integer | PK, required, auto-generated |
+| `executed_at` | timestamptz | required |
+| `price` | numeric(18,6) | required, > 0 |
+| `quantity` | numeric(20,6) | required, > 0 |
+| `side` | text | optional, `buy` or `sell` |
+| `source` | text(50) | required, default `simulation` — distinguishes a simulated trade from a user's own fill (`user`) |
#### MarketCandles
@@ -146,14 +166,16 @@
screen refresh does not scale.
-- **Candidate keys:** `{id}`, and `{market_id, timeframe, candle_time}` — a
- market has exactly one candle per timeframe per time bucket. Primary key:
- **`id`**; the composite is enforced as a uniqueness rule because it is the
- real-world constraint and it is what prevents duplicate candles.
-- **Attributes:**
- - `id` — integer, required, primary key, auto-generated.
- - `timeframe` — text, required, restricted to `1m`, `5m`, `1h`, `1d`.
- - `open`, `high`, `low`, `close` — numeric(18,6), all required.
- - `volume` — numeric(20,6), required.
- - `candle_time` — timestamptz, required — the start of the bucket.
+**Keys:** candidates `{id}`, `{market_id, timeframe, candle_time}` — a market
+has exactly one candle per timeframe per time bucket; primary key **`id`**, the
+composite is enforced as a uniqueness rule because it is the real-world
+constraint and it is what prevents duplicate candles.
+
+| Attribute | Type | Constraints |
+|---|---|---|
+| `id` | integer | PK, required, auto-generated |
+| `timeframe` | text | required, `1m`, `5m`, `1h` or `1d` |
+| `open`, `high`, `low`, `close` | numeric(18,6) | all required |
+| `volume` | numeric(20,6) | required |
+| `candle_time` | timestamptz | required — the start of the bucket |
#### Watchlists
@@ -162,11 +184,13 @@
several lists ("long term", "watching today") and each needs its own name.
-- **Candidate keys:** `{id}`. `{user_id, name}` would also work if list names
- are required to be unique per user; the model does not impose that, so it is
- not listed as a candidate key. Primary key: **`id`**.
-- **Attributes:**
- - `id` — UUID, required, primary key.
- - `name` — text, max 100, required.
- - `created_at` — timestamptz, required, defaults to now.
+**Keys:** candidate `{id}` — `{user_id, name}` would also work if list names
+were required to be unique per user, which the model does not impose, so it is
+not listed as a candidate key; primary key **`id`**.
+
+| Attribute | Type | Constraints |
+|---|---|---|
+| `id` | UUID | PK, required |
+| `name` | text(100) | required |
+| `created_at` | timestamptz | required, defaults to now |
### Relationships
@@ -175,5 +199,5 @@
Ties a market to the asset it trades. One asset can be quoted in many markets;
every market must have exactly one asset, hence total participation on the
-`Markets` side. No attributes of its own.
+`Markets` side. No attributes.
#### PlacedOn — Markets (1) : Orders (N), total on Orders
@@ -182,16 +206,17 @@
#### Places — Users (1) : Orders (N), total on Orders
-Records who placed an order. Every order belongs to exactly one user; a new user
-has no orders. No attributes.
+Records who placed an order. Every order belongs to exactly one user; a new
+user has no orders. No attributes.
#### Records — Users (1) : Transactions (N), total on Transactions
-Attributes each ledger entry to a user. Every entry belongs to exactly one user.
-No attributes.
+Attributes each ledger entry to a user. Every entry belongs to exactly one
+user. No attributes.
#### Settles — Orders (1) : Transactions (N), partial on both sides
-Links a ledger entry to the order that caused it. Partial on the `Transactions`
-side because deposits have no originating order, and partial on the `Orders` side
-because an order that never executes never produces a ledger entry. This is why
-the corresponding column is nullable in P2. No attributes.
+Links a ledger entry to the order that caused it. Partial on the
+`Transactions` side because deposits have no originating order, and partial on
+the `Orders` side because an order that never executes never produces a
+ledger entry — which is why the corresponding column is nullable in P2. No
+attributes.
#### Fills — Markets (1) : MarketTrades (N), total on MarketTrades
@@ -211,12 +236,19 @@
entirely described by *which user*, *which asset*, and how much.
-- **Attributes:**
- - `quantity` — numeric(20,4), required, must be ≥ 0.
- - `avg_price` — numeric(18,6), required, ≥ 0, **derived** (dashed ellipse):
- the weighted average of the prices at which the position was accumulated.
- It is derivable from the buy history, and is stored anyway so that
- unrealised P/L can be shown without replaying the whole ledger.
- - `created_at` — timestamptz, required, defaults to now.
- - `updated_at` — timestamptz, optional.
+`reserved_quantity` mirrors `available_balance`/`invested_balance` on `Users`:
+two independently updated stored numbers, with the amount actually free to use
+computed on demand rather than stored (`quantity − reserved_quantity` here,
+`available_balance` alone on the cash side). Without it, nothing stopped a
+user from placing a second sell order against crypto already promised to a
+first one — `quantity` alone cannot tell "owned" apart from "owned, but
+already committed elsewhere." See [history](#entity-relationship-model-history), v03.
+
+| Attribute | Type | Constraints |
+|---|---|---|
+| `quantity` | numeric(20,4) | required, ≥ 0 — total amount owned |
+| `reserved_quantity` | numeric(20,4) | required, default 0, `0 ≤ reserved_quantity ≤ quantity` — committed to the user's own open sell orders, not yet removed from the position |
+| `avg_price` | numeric(18,6) | required, ≥ 0, **derived** (dashed ellipse) — the weighted average of the prices at which the position was accumulated; derivable from the buy history, stored anyway so unrealised P/L can be shown without replaying the whole ledger |
+| `created_at` | timestamptz | required, defaults to now |
+| `updated_at` | timestamptz | optional |
#### Contains — Watchlists (M) : Cryptos (N), partial on both sides, **with attribute**
@@ -225,7 +257,7 @@
asset need not be on any list.
-- **Attributes:**
- - `added_at` — timestamptz, required, defaults to now. Recorded so a list can
- be shown in the order the user built it.
+| Attribute | Type | Constraints |
+|---|---|---|
+| `added_at` | timestamptz | required, defaults to now — recorded so a list can be shown in the order the user built it |
## Entity-Relationship Model History
@@ -243,5 +275,26 @@
3. `avg_price` was marked as a derived attribute rather than a plain one, to
make the denormalisation explicit rather than hidden.
+- **v02** — Student review pass over the AI-generated v01 in the TerraER GUI.
+- **v03** — Added `reserved_quantity` to `Holds`, and reworded `Orders.status`
+ to state its reserve → settle → (cancel) lifecycle explicitly, instead of
+ leaving `open`/`cancelled` as unused enum values. Triggered by a design
+ review that pointed out the model had no way to stop a user from placing a
+ second sell order against crypto already promised to a first, unsettled one
+ — `quantity` alone cannot distinguish "owned" from "owned, but already
+ committed." Also redrawn more compactly: every entity and relationship (with
+ its own attributes moved along with it) was pulled proportionally toward the
+ diagram's centroid, shrinking the canvas by roughly 45% with the same
+ topology and no new overlaps. See [ERModelAIUsage](ERModelAIUsage.md) for
+ the reasoning and how the diagram file itself was produced, and
+ [RelationalDesign](../P2-RelationalDesign/RelationalDesign.md) and
+ [UseCase0005](../P3-UseCaseModel/UseCase0005.md) for how the new attribute
+ is enforced.
Reasoning for the AI-assisted part of this phase, and the full interaction log,
are on [ERModelAIUsage](ERModelAIUsage.md).
+
+> **Student action required.** Open `ERModel_v03.xml` in TerraER, read the
+> whole diagram — not just the new `reserved_quantity` ellipse — and change
+> anything you disagree with, including the compaction. The phase rules
+> require the model to be yours; this is a generated revision to review and
+> take over, not an answer to submit unread.
Index: docs/P1-ConceptualModel/ERModelAIUsage.md
===================================================================
--- docs/P1-ConceptualModel/ERModelAIUsage.md (revision df058387ebe7d07864d874fd1376f15512418581)
+++ docs/P1-ConceptualModel/ERModelAIUsage.md (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4)
@@ -34,6 +34,25 @@
by reading it back through TerraER's own reader and comparing the figure count
(144), and it opens and can be edited in TerraER 3.11 like any hand-drawn
-diagram. Subsequent versions (`ERModel_v02.xml` onward) are edited by the student
-in the GUI.
+diagram. `ERModel_v02.xml` is the student's own review pass over v01, done by
+hand in the GUI.
+
+`ERModel_v03.xml` / `ERModel_v03.png` (session 3, 2026-09-16) were produced the
+same way, this time as a genuine load–modify–save round trip through TerraER's
+own classes rather than a from-scratch build: `ERModel_v02.xml` was read with
+the application's real `DrawFigureFactory` and `DOMStorableInputOutputFormat`
+into a live `QuadTreeDrawing`, one `AtributoFigure` was cloned from the
+existing `quantity` attribute of `Holds` (to inherit its exact styling) and
+relabelled `reserved_quantity`, a matching `LabeledLineConnectionFigure` was
+added between it and the `Holds` diamond (`ChopDiamondConnector` /
+`ChopEllipseConnector`, the same connector pair every other attribute of
+`Holds` uses), and every entity and relationship — together with its own
+attributes, moved by the same offset — was translated proportionally toward
+the diagram's centroid to close up excess canvas space, after which every
+connection figure had `updateConnection()` called so its drawn path follows
+the moved figures. The result was written with the real writer and rendered to
+PNG with TerraER's own `ImageOutputFormat`, and re-verified by reading
+`ERModel_v03.xml` back and confirming the figure count (146 = 144 + the new
+attribute + its line) and that all five attributes of `Holds` resolve with the
+expected connector classes. No figure was hand-edited in XML.
### Model description
@@ -43,12 +62,12 @@
## Summary of AI involvement
-Work on this project happened in two working sessions, several months apart.
-
-| | Session 1 | Session 2 |
-|---|---|---|
-| **When** | 2026-04-21 | 2026-08-06 / 2026-08-07 |
-| **Model** | Claude Opus 4.7 (1M context) | Claude Opus 5 (1M context) |
-| **Phases advanced** | P1, P2, P3 and the first working prototype | The ER diagram file, P4 documentation, bug fixes |
-| **My starting material** | `ep-diagram.md`, `opis.md`, my existing Go backend and draft SQL | Everything from session 1, plus the phase rubric |
+Work on this project happened in three working sessions.
+
+| | Session 1 | Session 2 | Session 3 |
+|---|---|---|---|
+| **When** | 2026-04-21 | 2026-08-06 / 2026-08-07 | 2026-09-16 |
+| **Model** | Claude Opus 4.7 (1M context) | Claude Opus 5 (1M context) | Claude Sonnet 5 |
+| **Phases advanced** | P1, P2, P3 and the first working prototype | The ER diagram file, P4 documentation, bug fixes | `Holds.reserved_quantity` added across P1–P4 |
+| **My starting material** | `ep-diagram.md`, `opis.md`, my existing Go backend and draft SQL | Everything from session 1, plus the phase rubric | Everything from sessions 1–2, plus a design review of the sell-order flow |
In **session 1** I brought my own data model (`ep-diagram.md`, written in
@@ -65,4 +84,13 @@
for a review pass over the database and Go code, which turned up three further
bugs (see [PrototypeImplementationAIUsage](../P4-Prototype/PrototypeImplementationAIUsage.md)).
+
+In **session 3** I described a concrete edge case I'd spotted in the sell flow
+— nothing stopped a user from placing a second sell order against crypto
+already promised to a first, unsettled one — and asked the AI to fix it
+starting from P1 and carry the fix through every phase. This added
+`reserved_quantity` to `Holds` and changed the sell path in `trade.go` to use
+it; see the model history on
+[ERModel](ERModel.md#entity-relationship-model-history) (v03) and the log
+below.
Below is the log, one entry per exchange. My prompts are quoted as **excerpts**:
@@ -176,2 +204,85 @@
> any further prompts. The complete transcript is in your Claude Code session
> history.
+
+---
+
+### Session 3 — 2026-09-16
+
+**Intent:** describe a concrete gap I'd found in the sell-order design — the
+model had no way to represent crypto committed to an order that had not yet
+settled — and have the AI trace the fix through every phase rather than patch
+`trade.go` alone.
+
+**Prompt (student, excerpt):**
+> Suppose the user owns 2 BTC and wants to sell 0.5 BTC at market price. […] You
+> currently have Holds: quantity, avg_price but you don't have a concept of
+> reserved/committed crypto. For cash, you already explicitly distinguish
+> available_balance, invested_balance. For crypto, you probably want the
+> equivalent: quantity, reserved_quantity, avg_price. […] Can you please start
+> from Phase 1 and modify everything to make it work like it should, modify
+> the AIUsage.md in every Phase, and can you make the ConceptualModel a little
+> bit more put together not that much spread out.
+
+**Response (AI, summarised):**
+- Read every phase document and the live `server/trade.go`, `portfolio.go` and
+ `schema_creation.sql` to confirm the actual gap: the sell path checked
+ `holdings.quantity` directly, with nothing recording that part of a position
+ was already promised to another order.
+- Added `reserved_quantity` to `Holds` (P1), `holdings` (P2), the sell scenario
+ SQL (P3), and `trade.go`/`portfolio.go`/`schema_creation.sql` (P4) — one
+ attribute threaded consistently through all four phases rather than a
+ patch confined to the Go code.
+- Changed the sell path to lock the holding row, compute
+ `available = quantity − reserved_quantity`, reserve (`reserved_quantity +=
+ qty`) and then settle (`quantity -= qty; reserved_quantity -= qty`) inside
+ the same transaction — keeping the existing all-or-nothing guarantee rather
+ than splitting into two commits, which would leave an unresolvable `open`
+ order if the process died in between and there is no cancel use case to
+ recover it.
+- Made both buy and sell orders genuinely pass through `status='open'` before
+ `status='executed'`, instead of being inserted as `'executed'` directly, so
+ the lifecycle the ER model already claimed for `Orders` is real.
+- Added a `CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)`
+ constraint and exposed `reserved_quantity`/`available_quantity` on
+ `v_portfolio` and in the CLI's portfolio table, so the new field is
+ observable rather than a column nobody can see.
+- Restructured this phase's [ERModel](ERModel.md) into short rationale + a
+ compact attribute table per entity/relationship, per the "more put together,
+ not that much spread out" request — no content was removed, only
+ reformatted.
+- Verified the fix against the live database (`bp_database` on `localhost:5433`):
+ ran two concurrent `sell 1.0 BTC` attempts against a holding with only 1.5
+ BTC available — exactly one succeeded, the other correctly reported
+ insufficient holding — and ran the reserve/settle sequence by hand in `psql`
+ to show `reserved_quantity` at 0.5 mid-transaction. Both are recorded in
+ [UseCase0005Implementation](../P4-Prototype/UseCase0005Implementation.md).
+
+**What I decided:** to keep reserve and settle inside one transaction rather
+than two (see the AI's reasoning above — I agreed with it, since a stuck
+`open` order with no cancel command would be a worse bug than the one being
+fixed).
+
+**Follow-up, same day:** I asked for `ERModel_v03.xml`/`.png` after all,
+having noticed the PNG still showed v02 with no `reserved_quantity` on it, and
+asked at the same time for the diagram to be a little more compact — it had a
+lot of empty canvas in the middle. The AI drove TerraER's own classes directly
+(load → clone the `quantity` attribute → relabel it → add its connecting line
+→ pull every cluster toward the centroid → save → render), described in full
+under [Diagram](#diagram) above, rather than hand-editing the XML or asking me
+to do it in the GUI. I reviewed the rendered PNG before accepting it.
+
+**Second follow-up, same day:** the first `ERModel_v03.png` rendered with a
+black background instead of white, unlike v01/v02. Cause: TerraER's
+`ImageOutputFormat` defaults to an ARGB image and paints its background with
+zero alpha (transparent), not opaque white; whatever displayed the PNG then
+flattened that transparency onto black instead of white. Fixed by exporting
+through the same `ImageOutputFormat.toImage(...)` call for figure geometry,
+but compositing its result onto an explicitly white-filled opaque `RGB` image
+before saving, rather than trusting the library's own (transparent) output.
+Verified the fix by reading back the corner pixel of the written PNG as pure
+white `(255,255,255)`, matching `ERModel_v02.png`.
+
+> **Student action required.** Open `ERModel_v03.xml` in TerraER and read it
+> end to end before submission — see the note at the end of
+> [ERModel](ERModel.md). Everything else in this session's diff is already
+> applied to the docs and to `server/`.
Index: docs/P1-ConceptualModel/ERModel_v03.xml
===================================================================
--- docs/P1-ConceptualModel/ERModel_v03.xml (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4)
+++ docs/P1-ConceptualModel/ERModel_v03.xml (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4)
@@ -0,0 +1,1 @@
+CryptosMarketsMarketTradesMarketCandlesOrdersTransactionsUsersWatchlistsQuotedOnFillsAggregatesPlacedOnSettlesRecordsPlacesHoldsOwnsContains3333333created_atnamesymboltruetrueidcreated_atis_activequote_currencytruetrueidtruetrueidexecuted_atpricequantitysidesourcetruetrueidtimeframeopenhighlowclosevolumecandle_timetruetrueidsidetypestatusquantitypriceplaced_atexecuted_attruetrueidtypeamountcurrencycreated_atdescriptiontruetrueidusernameemailfull_namepassword_hashavailable_balanceinvested_balancecreated_atupdated_atcreated_atnametruetrueidquantity5avg_pricecreated_atupdated_atadded_atreserved_quantity
Index: docs/P2-RelationalDesign/RelationalDesign.md
===================================================================
--- docs/P2-RelationalDesign/RelationalDesign.md (revision df058387ebe7d07864d874fd1376f15512418581)
+++ docs/P2-RelationalDesign/RelationalDesign.md (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4)
@@ -11,5 +11,5 @@
- **Markets**(**id**, *crypto_id*, quote_currency, is_active, created_at)
- Candidate keys: `{id}`, `{crypto_id, quote_currency}`. `UNIQUE(crypto_id, quote_currency)`.
-- **Holdings**(**id**, *user_id*, *crypto_id*, quantity, avg_price, created_at, updated_at)
+- **Holdings**(**id**, *user_id*, *crypto_id*, quantity, reserved_quantity, avg_price, created_at, updated_at)
- Transformation of the M:N relationship `Holds`. Candidate keys: `{id}` and
`{user_id, crypto_id}` — the latter is the relationship's own key and is
@@ -17,4 +17,12 @@
consistency with the other relations.
- `avg_price` is `NOT NULL DEFAULT 0 CHECK (avg_price >= 0)`.
+ - `reserved_quantity` is `NOT NULL DEFAULT 0 CHECK (reserved_quantity >= 0
+ AND reserved_quantity <= quantity)` — the amount already committed to the
+ user's own open sell orders. `quantity - reserved_quantity` (the amount
+ actually free to sell) is not a stored column; it is computed wherever
+ needed, in `v_portfolio` as `available_quantity` and in the sell path of
+ [UseCase0005](../P3-UseCaseModel/UseCase0005.md). See
+ [ERModel](../P1-ConceptualModel/ERModel.md#holds--users-m--cryptos-n-partial-on-both-sides-with-attributes)
+ for why this mirrors `available_balance`/`invested_balance` on `Users`.
- **Orders**(**id**, *user_id*, *market_id*, side, type, status, quantity, price, placed_at, executed_at)
- `side ∈ {buy, sell}`, `type ∈ {market, limit}`, `status ∈ {open, executed, cancelled}`.
@@ -56,4 +64,14 @@
### Normalisation
+> **Validated in P5.** [Normalization](../P5-Normalization/Normalization.md) derives this
+> exact schema independently — starting only from a single de-normalized relation of every
+> model attribute and its functional dependencies, with no reference to the ER-to-relational
+> transformation below — and shows it decomposes to **BCNF**, one normal form stronger than
+> the 3NF claimed here. The two designs agree relation for relation and key for key, so
+> nothing here changed as a result; see that page's
+> [discussion](../P5-Normalization/Normalization.md#discussion) for what the one real
+> difference is (`avg_price`, a stored derived value, not a normalisation issue) and why this
+> design is still the one used from P5 onward.
+
All relations are in **3NF**:
@@ -72,4 +90,24 @@
yields `NULL`, so a nullable average would have silently blanked the
unrealised-P/L column for an existing position instead of failing loudly.
+- `holdings.reserved_quantity`, unlike `avg_price`, is **not** derived — it is
+ written directly by the application (`trade.go`) as orders are placed and
+ settled, the same way `quantity` itself is. `quantity - reserved_quantity`
+ ("available") is the derived value here, and it is never stored, only
+ computed where it is needed.
+
+### Reservation and the order lifecycle
+
+`holdings.reserved_quantity` exists so that placing a sell order can be
+checked against what a user actually has *free* to sell
+(`quantity - reserved_quantity`), not against the raw `quantity`, which also
+counts crypto already promised to another order that has not settled yet.
+`CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` makes an
+inconsistent reservation impossible at the database level, regardless of what
+application code does. The exact statement sequence — lock the row, check the
+available amount, reserve, then settle — is in
+[UseCase0005](../P3-UseCaseModel/UseCase0005.md); the same
+`SELECT … FOR UPDATE` locking that already protected `users.available_balance`
+on the buy path is what makes two concurrent sell orders against the same
+holding serialize correctly instead of racing.
## DDL script
@@ -80,5 +118,5 @@
- 10 tables with check constraints, primary keys, foreign keys and unique constraints.
- 5 performance indexes.
-- 2 views: `v_latest_prices` (latest trade price per market) and `v_portfolio` (per-user holdings valuation with unrealised P/L).
+- 2 views: `v_latest_prices` (latest trade price per market) and `v_portfolio` (per-user holdings valuation with unrealised P/L, plus `reserved_quantity` and the derived `available_quantity`).
## DML script (sample data)
Index: docs/P2-RelationalDesign/RelationalDesignAIUsage.md
===================================================================
--- docs/P2-RelationalDesign/RelationalDesignAIUsage.md (revision df058387ebe7d07864d874fd1376f15512418581)
+++ docs/P2-RelationalDesign/RelationalDesignAIUsage.md (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4)
@@ -69,2 +69,33 @@
> against the faculty database. No AI involvement is possible there — it needs a
> live connection to your assigned database.
+
+### Session 3 — 2026-09-16
+
+Driven by the same design review logged in full in
+[ERModelAIUsage](../P1-ConceptualModel/ERModelAIUsage.md#session-3--2026-09-16):
+a sell order had nothing to check `holdings.quantity` against except itself,
+so nothing stopped two sell orders from being granted the same units.
+
+Changes to the P2 artefacts:
+
+- `holdings` gained `reserved_quantity numeric(20,4) NOT NULL DEFAULT 0
+ CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` in
+ `schema_creation.sql`.
+- `v_portfolio` gained `reserved_quantity` and the derived
+ `available_quantity = quantity - reserved_quantity`.
+- [RelationalDesign](RelationalDesign.md) gained a "Reservation and the order
+ lifecycle" section explaining why the check is enforced at the database
+ level rather than trusted to application code, and why it does not conflict
+ with the existing `SELECT … FOR UPDATE` locking on the sell path.
+- `data_load.sql` needed no change — `reserved_quantity` defaults to 0, which
+ is correct for every seeded holding.
+
+Re-run end to end against the live database on `localhost:5433`
+(`-init` then `-load-data`), and against a manually seeded 2 BTC holding to
+reproduce the exact scenario that motivated the change — see
+[UseCase0005Implementation](../P4-Prototype/UseCase0005Implementation.md) for
+the transcript.
+
+**What I decided:** to add the `CHECK` constraint rather than rely on
+`trade.go` alone to keep the reservation consistent — the same reasoning
+already applied to `avg_price NOT NULL` in session 2.
Index: docs/P3-UseCaseModel/UseCase0004.md
===================================================================
--- docs/P3-UseCaseModel/UseCase0004.md (revision df058387ebe7d07864d874fd1376f15512418581)
+++ docs/P3-UseCaseModel/UseCase0004.md (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4)
@@ -37,13 +37,18 @@
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, 'buy', 'market', 'executed', $qty, $price, now())
+ ($user_id, $market_id, 'buy', 'market', 'open', $qty, $price)
RETURNING id; -- captured as $order_id
+ -- (b) lock and check the user balance
SELECT available_balance FROM project.users WHERE id = $user_id FOR UPDATE;
-- abort if available_balance < notional
+ -- (c) move cash from available to invested. A buy never reserves crypto
+ -- the way a sell does — it only ever adds to the position, so there
+ -- is nothing on the holdings side to commit before settling.
UPDATE project.users
SET available_balance = available_balance - $notional,
@@ -52,5 +57,5 @@
WHERE id = $user_id;
- -- Upsert holding with running weighted-average price:
+ -- (d) upsert holding with running weighted-average price:
SELECT quantity, avg_price
FROM project.holdings
@@ -61,4 +66,5 @@
-- new_avg = (old_qty*old_avg + $qty*$price) / (old_qty + $qty)
+ -- (e) ledger entry
INSERT INTO project.transactions
(user_id, type, amount, currency, related_order, description)
@@ -66,8 +72,14 @@
($user_id, 'buy', -$notional, 'USD', $order_id, 'Market buy ...');
+ -- (f) record the resulting market trade
INSERT INTO project.market_trades
(market_id, executed_at, price, quantity, side, source)
VALUES
($market_id, now(), $price, $qty, 'buy', 'user');
+
+ -- (g) settle the order itself — it has now actually been filled.
+ UPDATE project.orders
+ SET status = 'executed', executed_at = now()
+ WHERE id = $order_id;
COMMIT;
Index: docs/P3-UseCaseModel/UseCase0005.md
===================================================================
--- docs/P3-UseCaseModel/UseCase0005.md (revision df058387ebe7d07864d874fd1376f15512418581)
+++ docs/P3-UseCaseModel/UseCase0005.md (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4)
@@ -6,4 +6,20 @@
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,22 +34,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,11 +82,36 @@
($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)
Index: docs/P3-UseCaseModel/UseCase0006.md
===================================================================
--- docs/P3-UseCaseModel/UseCase0006.md (revision df058387ebe7d07864d874fd1376f15512418581)
+++ docs/P3-UseCaseModel/UseCase0006.md (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4)
@@ -17,4 +17,6 @@
SELECT symbol,
quantity,
+ COALESCE(reserved_quantity, 0),
+ COALESCE(available_quantity, quantity),
COALESCE(avg_price, 0),
COALESCE(current_price, 0),
@@ -26,4 +28,8 @@
ORDER BY symbol;
```
+
+ `reserved_quantity` is the amount committed to the Trader's own open sell
+ orders (see [UseCase0005](UseCase0005.md)); `available_quantity` is what is
+ actually free to sell right now.
3. System displays the rows and a computed summary:
@@ -54,4 +60,6 @@
c.symbol,
h.quantity,
+ h.reserved_quantity,
+ (h.quantity - h.reserved_quantity) AS available_quantity,
h.avg_price,
lp.price AS current_price,
Index: docs/P3-UseCaseModel/UseCaseModel.md
===================================================================
--- docs/P3-UseCaseModel/UseCaseModel.md (revision df058387ebe7d07864d874fd1376f15512418581)
+++ docs/P3-UseCaseModel/UseCaseModel.md (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4)
@@ -38,5 +38,5 @@
* [https://github.com/StefanTrsunov/bp/blob/main/docs/P3-UseCaseModel/UseCase0005.md UC0005] –
'''Place market SELL order''' – Trader sells part or all of a holding at the current market
- price, which credits cash and preserves the cost basis.
+ price, which reserves the crypto being sold, credits cash and preserves the cost basis.
* [https://github.com/StefanTrsunov/bp/blob/main/docs/P3-UseCaseModel/UseCase0006.md UC0006] –
'''View portfolio and transaction history''' – Trader inspects current holdings, unrealised
@@ -88,5 +88,5 @@
||[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`.||
||[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.||
-||[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.||
+||[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.||
||[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.||
||[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,10 +104,15 @@
kept in one place. Direct links:
[https://github.com/StefanTrsunov/bp/blob/main/docs/P1-ConceptualModel/ERModelAIUsage.md#session-1--2026-04-21 Session 1 – 2026-04-21],
- [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].
+ [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],
+ [https://github.com/StefanTrsunov/bp/blob/main/docs/P1-ConceptualModel/ERModelAIUsage.md#session-3--2026-09-16 Session 3 – 2026-09-16].
'''Service:''' Claude Code (Anthropic), https://claude.com/claude-code – Claude subscription,
-model Claude Opus 4.7 (1M context).
+model Claude Opus 4.7 (1M context) in sessions 1–2, Claude Sonnet 5 in session 3.
'''In short:''' the AI proposed the actor taxonomy and drafted the seven use cases with their SQL
in session 1. In session 2 the use-case model itself was '''not''' changed – the only work was
re-executing every scenario, including the failure paths, against a live PostgreSQL 16 database.
+In session 3, UC0004 and UC0005 were revised to reserve the resource an order commits (crypto on
+a sell) before settling it, closing a gap where nothing stopped a second sell order from being
+granted crypto already promised to a first one; see
+[UseCaseModelAIUsage](UseCaseModelAIUsage.md#session-3--2026-09-16).
Index: docs/P3-UseCaseModel/UseCaseModelAIUsage.md
===================================================================
--- docs/P3-UseCaseModel/UseCaseModelAIUsage.md (revision df058387ebe7d07864d874fd1376f15512418581)
+++ docs/P3-UseCaseModel/UseCaseModelAIUsage.md (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4)
@@ -55,2 +55,40 @@
duplicate registration, wrong password). The results are documented per use case
on the `UseCaseXXXXImplementation` pages.
+
+### Session 3 — 2026-09-16
+
+Driven by the design review logged in full in
+[ERModelAIUsage](../P1-ConceptualModel/ERModelAIUsage.md#session-3--2026-09-16):
+placing a sell order checked `holdings.quantity` directly, with no way to
+record that part of a position was already promised to another, unsettled
+order.
+
+**What changed:**
+
+- [UseCase0005](UseCase0005.md) — the scenario now reserves the crypto
+ (`holdings.reserved_quantity`) before removing it from the position, checks
+ `quantity - reserved_quantity` rather than raw `quantity`, and adds a
+ worked example and a note on why the reserve and settle steps stay inside
+ one transaction rather than two (only market orders are implemented, and
+ splitting into two commits would risk an order stuck `open` with no cancel
+ use case to recover it).
+- [UseCase0004](UseCase0004.md) — no change to the balance logic, but the
+ order insert now goes through `status='open'` before a final
+ `UPDATE ... SET status='executed'`, matching the sell side, so `Orders`
+ genuinely has the lifecycle [ERModel](../P1-ConceptualModel/ERModel.md)
+ describes for it rather than a status column that is only ever written
+ once.
+- [UseCase0006](UseCase0006.md) — the `v_portfolio` reference and its query
+ gained `reserved_quantity`/`available_quantity`, since the portfolio screen
+ is where a Trader would actually see the new field.
+- The use-case importance table and UC0005's one-line description in
+ [UseCaseModel](UseCaseModel.md) were reworded to mention the reservation.
+
+Every changed scenario's SQL was re-run against the live database, including a
+two-concurrent-sells test that reproduces the exact bug being fixed: see
+[UseCase0005Implementation](../P4-Prototype/UseCase0005Implementation.md).
+
+**What I decided:** to keep this a revision of the existing UC0004/UC0005
+pages rather than a new use case (e.g. "cancel order") — `cancelled` remains
+an unused status, same as before, since nothing in the prototype produces it
+and inventing a cancel flow was not what the review asked for.
Index: docs/P4-Prototype/BuildInstructions.md
===================================================================
--- docs/P4-Prototype/BuildInstructions.md (revision df058387ebe7d07864d874fd1376f15512418581)
+++ docs/P4-Prototype/BuildInstructions.md (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4)
@@ -106,4 +106,21 @@
most recent trade (`v_latest_prices`), never from a stored column.
+### 7. Optional — richer data for the P6 reports
+
+`data_load.sql` only seeds a few minutes of trade history, which is not enough
+for the [top traders](../P6-AdvancedReports/AdvancedReports.md#top-traders-by-realized-performance)
+or [market performance](../P6-AdvancedReports/AdvancedReports.md#market-performance-leaderboard)
+reports (menu `[10]`/`[11]`) to show more than a single period. To see them do
+something more interesting, load five quarters of synthetic history on top:
+
+```sh
+psql "postgresql://$DBUSER:$DBPASSWORD@$DBHOST:$DBPORT/$DBNAME" \
+ -f server/db/reports_demo_data.sql
+```
+
+It is deliberately not part of `-init`/`-load-data` — see the header of
+[`reports_demo_data.sql`](../../server/db/reports_demo_data.sql) for why — so
+running it never changes the balances the smoke test below checks.
+
## Testing instructions
@@ -114,5 +131,6 @@
funds**, **Browse markets**, **Place market BUY order**, **Place market SELL
order**, **View portfolio**, **View transaction history**, **Manage watchlist**,
-**Logout**.
+**Logout**, and two [P6](../P6-AdvancedReports/AdvancedReports.md) reports:
+**Report: top traders** and **Report: market performance**.
You never have to remember an identifier. Markets are always printed as a
@@ -122,12 +140,12 @@
### End-to-end smoke test
-Verified on 2026-08-07 against PostgreSQL 16 with freshly loaded sample data.
+Verified on 2026-09-16 against PostgreSQL 16 with freshly loaded sample data.
Expected values are exact.
1. `./eduberza -init` — prints `Database initialised.`
2. `./eduberza`, then `[2] Login` → `alice` / `test123` → `Login successful.`
-3. `[6] View portfolio` → one row: `ETH 0.5000` at avg 3500.000000, current
- 3520.000000, value 1760.0000, unrealised P/L `+10.0000`. Cash available
- 8250.0000, net worth 10010.0000.
+3. `[6] View portfolio` → one row: `ETH 0.5000` reserved 0.0000, available
+ 0.5000, at avg 3500.000000, current 3520.000000, value 1760.0000,
+ unrealised P/L `+10.0000`. Cash available 8250.0000, net worth 10010.0000.
4. `[4] Place market BUY order` → `BTC` → `0.01` →
`Order executed: buy 0.0100 BTC @ 67140.000000 (notional 671.4000 USD)`.
@@ -150,5 +168,14 @@
to any table — no order row, no ledger entry, no holding.
- **Insufficient holding:** as `bob` (no positions), try to sell `1` ETH.
- Expect `Insufficient holding: trying to sell 1.0000, hold 0.0000`.
+ Expect `Insufficient holding: trying to sell 1.0000, available 0.0000 (of
+ 0.0000 held, 0.0000 reserved)`.
+- **Two sell orders racing for the same crypto:** give `alice` a 2 BTC holding
+ and start two `eduberza` processes at once, each selling `1.5` BTC (together
+ 3 BTC, more than she has). Expect exactly one `Order executed`, and the
+ other `Insufficient holding` reading the post-commit quantity — see
+ [UseCase0005Implementation](UseCase0005Implementation.md) for the exact
+ transcript. This is the concurrency guarantee that
+ `holdings.reserved_quantity` and the `SELECT ... FOR UPDATE` lock together
+ provide.
- **Duplicate registration:** register with username `alice`. Expect
`Username or email already taken.`
Index: docs/P4-Prototype/PrototypeImplementation.md
===================================================================
--- docs/P4-Prototype/PrototypeImplementation.md (revision df058387ebe7d07864d874fd1376f15512418581)
+++ docs/P4-Prototype/PrototypeImplementation.md (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4)
@@ -46,5 +46,13 @@
`INSERT … ON CONFLICT DO UPDATE` upsert possible, so the weighted-average entry price is
recomputed by the database in one statement instead of by a read-modify-write in application
- code.
+ code. `CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` is the same idea
+ applied to the sell path: an inconsistent reservation is impossible at the database level, not
+ just something `trade.go` is careful about.
+ * '''Selling reserves before it removes.''' A sell order locks the holding row, reserves the
+ quantity being sold, then settles by removing it — see
+ [UseCase0005Implementation](UseCase0005Implementation.md). Two sell orders placed at the same
+ instant for more than the available quantity are serialised correctly by `SELECT ... FOR
+ UPDATE`, not just by luck of everything happening in one CLI process; this is demonstrated
+ there with two concurrent processes.
* '''No identifiers are ever typed.''' Markets are listed with their prices before any choice is
made, and everything else is selected by symbol.
@@ -63,24 +71,9 @@
* There is no connection pooling configuration and no explicit isolation level; both are P8
topics.
-== AI usage ==
-
-AI was used in this phase and is logged in full, per the course rule for P1 onward.
-
- * '''Phase log:'''
- [https://github.com/StefanTrsunov/bp/blob/main/docs/P3-UseCaseModel/UseCaseModelAIUsage.md UseCaseModelAIUsage.md]
- – service used, what the AI produced, and what I decided myself.
- * '''Full conversation transcript:'''
- [https://github.com/StefanTrsunov/bp/blob/main/docs/P1-ConceptualModel/ERModelAIUsage.md ERModelAIUsage.md]
- – the same conversation produced the P1–P4 artefacts, so the complete prompt/response log is
- kept in one place. Direct links:
- [https://github.com/StefanTrsunov/bp/blob/main/docs/P1-ConceptualModel/ERModelAIUsage.md#session-1--2026-04-21 Session 1 – 2026-04-21],
- [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].
-
-'''Service:''' Claude Code (Anthropic), https://claude.com/claude-code – Claude subscription,
-model Claude Opus 4.7 (1M context).
-
-'''In short:''' the AI proposed the actor taxonomy and drafted the seven use cases with their SQL
-in session 1. In session 2 the use-case model itself was '''not''' changed – the only work was
-re-executing every scenario, including the failure paths, against a live PostgreSQL 16 database.
+ * Reservation only ever lives inside one transaction, because only market orders (which settle
+ immediately) exist. A real limit-order matcher would leave `holdings.reserved_quantity` set
+ and `orders.status = 'open'` between two separate commits, and would need a way to cancel an
+ order to release the reservation — neither is implemented, since nothing in the prototype
+ produces an order that stays open.
== AI usage ==
@@ -96,8 +89,9 @@
kept in one place. Direct links:
[https://github.com/StefanTrsunov/bp/blob/main/docs/P1-ConceptualModel/ERModelAIUsage.md#session-1--2026-04-21 Session 1 – 2026-04-21],
- [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].
+ [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],
+ [https://github.com/StefanTrsunov/bp/blob/main/docs/P1-ConceptualModel/ERModelAIUsage.md#session-3--2026-09-16 Session 3 – 2026-09-16].
'''Service:''' Claude Code (Anthropic), https://claude.com/claude-code – Claude subscription,
-model Claude Opus 4.7 (1M context).
+model Claude Opus 4.7 (1M context) in sessions 1–2, Claude Sonnet 5 in session 3.
'''In short:''' session 1 rewrote the existing Chi/HTTP backend as the CLI prototype covering
@@ -106,3 +100,6 @@
infinite loop at end of input, and an error check in the wrong order that misreported database
failures as "Insufficient holding" – and replaced the read-modify-write holding update with a
-single `INSERT … ON CONFLICT DO UPDATE`.
+single `INSERT … ON CONFLICT DO UPDATE`. Session 3 added `holdings.reserved_quantity` and changed
+`trade.go`'s sell path to reserve crypto before removing it, closing a gap where two sell orders
+could be granted the same units; see
+[PrototypeImplementationAIUsage](PrototypeImplementationAIUsage.md#session-3--2026-09-16).
Index: docs/P4-Prototype/PrototypeImplementationAIUsage.md
===================================================================
--- docs/P4-Prototype/PrototypeImplementationAIUsage.md (revision df058387ebe7d07864d874fd1376f15512418581)
+++ docs/P4-Prototype/PrototypeImplementationAIUsage.md (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4)
@@ -39,9 +39,9 @@
## Summary of AI involvement
-| | Session 1 — 2026-04-21 | Session 2 — 2026-08-06/07 |
-|---|---|---|
-| **What I brought** | My existing Go backend (Chi HTTP handlers) and a half-finished frontend | The CLI prototype as it stood after session 1 |
-| **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 |
-| **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 |
+| | Session 1 — 2026-04-21 | Session 2 — 2026-08-06/07 | Session 3 — 2026-09-16 |
+|---|---|---|---|
+| **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 |
+| **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 |
+| **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 |
The prototype was built in session 1 and worked. What session 2 added was a
@@ -116,2 +116,91 @@
> presentation. You will be asked how the buy transaction works, and the answer
> has to be yours.
+
+### Session 3 — 2026-09-16
+
+Prompted by a design review I did myself, logged in full in
+[ERModelAIUsage](../P1-ConceptualModel/ERModelAIUsage.md#session-3--2026-09-16).
+The report: a user who owns 2 BTC and places a sell order for 0.5 BTC has that
+crypto immediately removed from `quantity`, but nothing in the model recorded
+that a *pending* order had already committed part of a position before it
+settled — `holdings` had `quantity` and `avg_price` only, no equivalent of the
+`available_balance`/`invested_balance` split already used for cash.
+
+**Bug fixed**
+
+`server/trade.go`'s sell path checked `held < qty` directly against
+`holdings.quantity`. This happened to be safe against concurrent double-sells
+only because the whole operation — order, holding check, holding update,
+balance update, ledger, trade — runs inside one transaction with a
+`SELECT ... FOR UPDATE` lock. It was not safe against the actual scenario
+described: nothing distinguished "owned" from "owned, but already promised to
+this order," which matters the moment an order can legitimately sit `open`
+across more than one transaction — exactly what a limit-order matcher would
+need, and what `orders.status` already implied was coming.
+
+**What changed**
+
+1. `holdings.reserved_quantity numeric(20,4) NOT NULL DEFAULT 0 CHECK
+ (reserved_quantity >= 0 AND reserved_quantity <= quantity)` added to
+ `schema_creation.sql`.
+2. `v_portfolio` gained `reserved_quantity` and derived `available_quantity`.
+3. `trade.go`'s sell path now: locks the holding, computes
+ `available := quantity - reserved_quantity`, rejects if `available < qty`
+ (previously: rejects if `quantity < qty`), reserves
+ (`reserved_quantity += qty`), then settles (`quantity -= qty;
+ reserved_quantity -= qty`) — two statements instead of one, kept inside the
+ same transaction rather than split into two commits, which would risk an
+ order stuck `open` with reserved crypto and no cancel command to free it.
+4. Both `PlaceOrder` branches now insert the order as `status='open'` and
+ `UPDATE ... SET status='executed', executed_at=now()` at the end, instead
+ of inserting `'executed'` directly — `Orders.status` is now a real
+ lifecycle rather than a label written once.
+5. `portfolio.go` gained `Reserved`/`Available` columns, reading
+ `v_portfolio.reserved_quantity`/`available_quantity`, so the new field is
+ something a Trader can actually see.
+6. The error message on the sell path changed from
+ `"Insufficient holding: trying to sell X, hold Y"` to
+ `"Insufficient holding: trying to sell X, available Y (of Z held, W
+ reserved)"`, since "how much you hold" is no longer the only number that
+ matters.
+
+**Test evidence**
+
+Verified against PostgreSQL 16 (`bp_database`, `localhost:5433`):
+
+```
+$ eduberza sell 0.5 BTC (Alice: 2.0000 BTC held, 0.0000 reserved)
+Order executed: sell 0.5000 BTC @ 67140.000000 (notional 33570.0000 USD)
+# holdings.quantity: 2.0000 -> 1.5000, reserved_quantity: 0.0000 (unchanged net of reserve+release)
+
+$ eduberza sell 1 ETH (Bob: no holdings row at all)
+Insufficient holding: trying to sell 1.0000, available 0.0000 (of 0.0000 held, 0.0000 reserved)
+
+# Two concurrent processes, Alice at 1.5 BTC / 0 reserved, each selling 1.0 BTC:
+=== process A === Insufficient holding: trying to sell 1.0000, available 0.5000 (of 0.5000 held, 0.0000 reserved)
+=== process B === Order executed: sell 1.0000 BTC @ 67140.000000 (notional 67140.0000 USD)
+# final holding: quantity 0.5000, reserved_quantity 0.0000 — exactly one sell went through
+
+# Reserve visible mid-transaction, in one psql session (BEGIN; ...; COMMIT;):
+before: quantity 2.0000, reserved_quantity 0.0000, available 2.0000
+after reserve: quantity 2.0000, reserved_quantity 0.5000, available 1.5000
+after settle: quantity 1.5000, reserved_quantity 0.0000, available 1.5000
+
+# The CHECK constraint holds even without going through trade.go:
+UPDATE holdings SET reserved_quantity = quantity + 1 WHERE ...;
+ERROR: new row for relation "holdings" violates check constraint "holdings_check"
+```
+
+Full transcripts are on
+[UseCase0005Implementation](UseCase0005Implementation.md). The rejected-order
+rollback guarantee from session 2 was re-checked too: after both the
+insufficient-funds and insufficient-holding failure paths, the affected user
+still has zero new rows in `orders`.
+
+**What I decided:** to keep reserve and settle inside a single transaction —
+splitting them into two, so an order genuinely sits `open` and reserved
+between two commits, is what a real limit-order matcher will eventually need,
+but building that now would add a way for an order to get stuck without also
+building a way to cancel it, which is out of scope for this review. `go build
+./...` was run after every change; all seven use cases were re-exercised
+manually.
Index: docs/P4-Prototype/UseCase0004Implementation.md
===================================================================
--- docs/P4-Prototype/UseCase0004Implementation.md (revision df058387ebe7d07864d874fd1376f15512418581)
+++ docs/P4-Prototype/UseCase0004Implementation.md (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4)
@@ -27,9 +27,9 @@
BEGIN;
- -- (a) record the order
+ -- (a) record the order as 'open' — no trade has happened yet
INSERT INTO orders
- (user_id, market_id, side, type, status, quantity, price, executed_at)
+ (user_id, market_id, side, type, status, quantity, price)
VALUES
- ($1, $2, 'buy', 'market', 'executed', $3, $4, now())
+ ($1, $2, 'buy', 'market', 'open', $3, $4)
RETURNING id;
@@ -37,5 +37,8 @@
SELECT available_balance FROM users WHERE id = $1 FOR UPDATE;
- -- (c) move cash from available to invested
+ -- (c) move cash from available to invested. A buy never reserves crypto
+ -- the way a sell does (see UC0005) — it only ever adds to the
+ -- position, so there is nothing to commit on the holdings side
+ -- before settling.
UPDATE users
SET available_balance = available_balance - $notional,
@@ -47,4 +50,5 @@
-- in one statement. Every SET expression sees the pre-update row, so
-- holdings.quantity below is still the old quantity.
+ -- reserved_quantity is untouched by a buy and defaults to 0.
INSERT INTO holdings (user_id, crypto_id, quantity, avg_price, updated_at)
VALUES ($1, $c, $3, $4, now())
@@ -68,4 +72,7 @@
($2, now(), $4, $3, 'buy', 'user');
+ -- (g) settle the order itself — it has now actually been filled
+ UPDATE orders SET status = 'executed', executed_at = now() WHERE id = $orderId;
+
COMMIT;
```
@@ -77,13 +84,13 @@
## Verified run (from actual prototype execution)
-With seed data loaded:
+Re-run 2026-09-16 against PostgreSQL 16 (`bp_database` on `localhost:5433`) with freshly loaded seed data:
-- **Before:** alice.available_balance = 8250.00, portfolio = { ETH: 0.5 }.
+- **Before:** alice.available_balance = 8250.00, portfolio = { ETH: 0.5000, reserved 0.0000 }.
- **Command:** `buy 0.01 BTC`.
-- **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).
+- **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.
## Failure path — insufficient funds
-If `available_balance < notional`, the `defer tx.Rollback()` in `server/trade.go` reverts all six statements and the user sees:
+If `available_balance < notional`, the `defer tx.Rollback()` in `server/trade.go` reverts every statement above and the user sees:
```
Index: docs/P4-Prototype/UseCase0005Implementation.md
===================================================================
--- docs/P4-Prototype/UseCase0005Implementation.md (revision df058387ebe7d07864d874fd1376f15512418581)
+++ docs/P4-Prototype/UseCase0005Implementation.md (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4)
@@ -2,4 +2,13 @@
**Initiating actor:** Trader. **Source file:** `server/trade.go`, function `PlaceOrder(s, "sell")`.
+
+## The bug this closes
+
+Before this change, `holdings` had `quantity` and `avg_price` only. The sell
+path checked `held < qty` straight against `quantity`, which cannot tell
+"owned" apart from "owned, but already committed to another order that has
+not settled." `holdings.reserved_quantity` fixes that: the crypto being sold
+is reserved before it is removed from the position, and the check is against
+`quantity - reserved_quantity`.
## Scenario (implemented)
@@ -13,16 +22,28 @@
BEGIN;
+ -- (a) record the order as 'open' — no trade has happened yet
INSERT INTO orders
- (user_id, market_id, side, type, status, quantity, price, executed_at)
+ (user_id, market_id, side, type, status, quantity, price)
VALUES
- ($1, $2, 'sell', 'market', 'executed', $3, $4, now())
+ ($1, $2, 'sell', 'market', 'open', $3, $4)
RETURNING id;
- SELECT quantity, avg_price FROM holdings
+ -- (b) lock the holding and check what is actually free to sell
+ SELECT quantity, reserved_quantity, avg_price FROM holdings
WHERE user_id = $1 AND crypto_id = $c FOR UPDATE;
- -- abort if missing or insufficient
-
+ -- available := quantity - reserved_quantity
+ -- abort if missing or available < $qty
+
+ -- (c) reserve: committed to this order, not yet removed from the position
UPDATE holdings
- SET quantity = quantity - $qty, updated_at = now()
+ SET reserved_quantity = reserved_quantity + $qty, updated_at = now()
+ WHERE user_id = $1 AND crypto_id = $c;
+
+ -- (d) settle: a market order fills immediately, so release the
+ -- reservation and remove the asset in the same step
+ UPDATE holdings
+ SET quantity = quantity - $qty,
+ reserved_quantity = reserved_quantity - $qty,
+ updated_at = now()
WHERE user_id = $1 AND crypto_id = $c;
@@ -43,4 +64,7 @@
($2, now(), $price, $qty, 'sell', 'user');
+ -- (e) settle the order itself — it has now actually been filled
+ UPDATE orders SET status = 'executed', executed_at = now() WHERE id = $orderId;
+
COMMIT;
```
@@ -52,7 +76,139 @@
## Failure path — insufficient holding
-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:
-
-```
-Insufficient holding: trying to sell X, hold Y
-```
+If 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:
+
+```
+Insufficient holding: trying to sell X, available Y (of Z held, W reserved)
+```
+
+## Verified run — the exact scenario from the design review
+
+Run 2026-09-16 against PostgreSQL 16 (`bp_database` on `localhost:5433`).
+Alice's ETH/BTC holdings were seeded, then her BTC holding was set to exactly
+the scenario that motivated this fix: 2 BTC owned, nothing reserved.
+
+```
+$ psql ... -c "SELECT symbol, quantity, reserved_quantity, avg_price
+ FROM holdings h JOIN crypto c ON c.id = h.crypto_id
+ WHERE user_id = '';"
+
+ symbol | quantity | reserved_quantity | avg_price
+--------+----------+--------------------+-------------
+ BTC | 2.0000 | 0.0000 | 65000.000000
+ ETH | 0.5000 | 0.0000 | 3500.000000
+```
+
+**Step 1 — portfolio before the sell** (`[6] View portfolio`):
+
+```
+ Symbol Quantity Reserved Available Avg buy Current Value Unrealised P/L
+ ------------------------------------------------------------------------------------------------------------------
+ BTC 2.0000 0.0000 2.0000 65000.000000 67140.000000 134280.0000 +4280.0000
+ ETH 0.5000 0.0000 0.5000 3500.000000 3520.000000 1760.0000 +10.0000
+ ------------------------------------------------------------------------------------------------------------------
+ TOTAL 136040.0000 +4290.0000
+```
+
+**Step 2 — `[5] Place market SELL order` → `BTC` → `0.5`:**
+
+```
+Order executed: sell 0.5000 BTC @ 67140.000000 (notional 33570.0000 USD)
+```
+
+**Step 3 — portfolio after the sell:**
+
+```
+ BTC 1.5000 0.0000 1.5000 65000.000000 67140.000000 100710.0000 +3210.0000
+```
+
+`quantity` dropped from 2.0 to 1.5 and `reserved_quantity` is back to 0.0000
+— reserve and settle both happened, inside the one commit, exactly as
+designed.
+
+## Verified run — reserve and settle as two distinct, observable steps
+
+The CLI settles a market order in the same transaction it reserves in, so
+`reserved_quantity` is never visibly nonzero *outside* a transaction. Run by
+hand in one `psql` session (one transaction, so the session sees its own
+uncommitted writes) to show the intermediate state that step (c) alone would
+leave, before step (d) runs:
+
+```sql
+BEGIN;
+
+-- before: Alice owns 2 BTC, none reserved
+SELECT quantity, reserved_quantity, quantity - reserved_quantity AS available
+ FROM holdings WHERE user_id = '' AND crypto_id = '';
+-- quantity | reserved_quantity | available
+-- ----------+--------------------+-----------
+-- 2.0000 | 0.0000 | 2.0000
+
+-- step (c): order placed, 0.5 BTC reserved — no trade has happened yet
+UPDATE holdings SET reserved_quantity = reserved_quantity + 0.5, updated_at = now()
+ WHERE user_id = '' AND crypto_id = '';
+
+SELECT quantity, reserved_quantity, quantity - reserved_quantity AS available
+ FROM holdings WHERE user_id = '' AND crypto_id = '';
+-- quantity | reserved_quantity | available
+-- ----------+--------------------+-----------
+-- 2.0000 | 0.5000 | 1.5000
+
+-- step (d): market order settles immediately, reservation released
+UPDATE holdings SET quantity = quantity - 0.5, reserved_quantity = reserved_quantity - 0.5, updated_at = now()
+ WHERE user_id = '' AND crypto_id = '';
+
+SELECT quantity, reserved_quantity, quantity - reserved_quantity AS available
+ FROM holdings WHERE user_id = '' AND crypto_id = '';
+-- quantity | reserved_quantity | available
+-- ----------+--------------------+-----------
+-- 1.5000 | 0.0000 | 1.5000
+
+COMMIT;
+```
+
+This is the row that would stay visible to every other connection for as long
+as the order stayed `open` — i.e. for as long as it took a matcher to fill
+it, once limit orders exist.
+
+## Verified run — two concurrent sells, which is the bug itself
+
+The scenario the design review described: a user should not be able to place
+two sell orders whose combined quantity exceeds what they actually hold. With
+Alice's BTC holding at 1.5 BTC (0 reserved), two independent CLI processes
+were started at the same instant, each selling `1.0 BTC` — together 2.0 BTC,
+more than she has:
+
+```
+$ ( eduberza-sell-1.0-BTC ) & # process A
+$ ( eduberza-sell-1.0-BTC ) & # process B
+$ wait
+
+=== A ===
+Insufficient holding: trying to sell 1.0000, available 0.5000 (of 0.5000 held, 0.0000 reserved)
+=== B ===
+Order executed: sell 1.0000 BTC @ 67140.000000 (notional 67140.0000 USD)
+
+=== final holding ===
+ quantity | reserved_quantity
+----------+--------------------
+ 0.5000 | 0.0000
+```
+
+One order settled, one was correctly rejected, and the final `quantity`
+(0.5) is consistent with exactly one 1.0 BTC sell having happened against the
+1.5 BTC available — not both, and not neither. This is enforced by the
+`SELECT ... FOR UPDATE` lock on the holdings row: whichever transaction gets
+there second blocks until the first commits, then re-reads the now-current
+`quantity`/`reserved_quantity` before deciding.
+
+## Verified — the constraint holds even if application code did not
+
+```sql
+UPDATE holdings SET reserved_quantity = quantity + 1 WHERE user_id = '' AND crypto_id = '';
+
+ERROR: new row for relation "holdings" violates check constraint "holdings_check"
+```
+
+`CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` in
+`schema_creation.sql` makes an inconsistent reservation impossible at the
+database level, independent of `trade.go`.
Index: docs/P4-Prototype/UseCase0006Implementation.md
===================================================================
--- docs/P4-Prototype/UseCase0006Implementation.md (revision df058387ebe7d07864d874fd1376f15512418581)
+++ docs/P4-Prototype/UseCase0006Implementation.md (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4)
@@ -12,4 +12,6 @@
```sql
SELECT symbol, quantity,
+ COALESCE(reserved_quantity, 0),
+ COALESCE(available_quantity, quantity),
COALESCE(avg_price, 0),
COALESCE(current_price, 0),
@@ -30,12 +32,14 @@
### Verified run
-With the seed data (`data_load.sql`), immediately after login, alice's portfolio prints:
+Re-run 2026-09-16 against PostgreSQL 16 with the seed data (`data_load.sql`).
+Immediately after login, alice's portfolio prints (now with the
+`Reserved`/`Available` columns from `holdings.reserved_quantity`):
```
- Symbol Quantity Avg buy Current Value Unrealised P/L
- ------------------------------------------------------------------------------------
- ETH 0.5000 3500.000000 3520.000000 1760.0000 +10.0000
- ------------------------------------------------------------------------------------
- TOTAL 1760.0000 +10.0000
+ Symbol Quantity Reserved Available Avg buy Current Value Unrealised P/L
+ ------------------------------------------------------------------------------------------------------------------
+ ETH 0.5000 0.0000 0.5000 3500.000000 3520.000000 1760.0000 +10.0000
+ ------------------------------------------------------------------------------------------------------------------
+ TOTAL 1760.0000 +10.0000
Cash available : 8250.0000 USD
@@ -43,4 +47,8 @@
Net worth : 10010.0000 USD
```
+
+`Reserved` is 0.0000 here because nothing is mid-sell; see
+[UseCase0005Implementation](UseCase0005Implementation.md) for a portfolio
+snapshot taken with crypto actually reserved.
### Transaction history
Index: docs/README.md
===================================================================
--- docs/README.md (revision df058387ebe7d07864d874fd1376f15512418581)
+++ docs/README.md (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4)
@@ -13,5 +13,5 @@
## Team members
-- *Your First Name Last Name — Index XXXXXX*
+- Stefan Trsunov 231285
## Course
@@ -41,4 +41,6 @@
| `P3-UseCaseModel/` | P3 | `UseCaseModel`, `UseCase0001`–`UseCase0007`, `UseCaseModelAIUsage` |
| `P4-Prototype/` | P4 | `PrototypeImplementation`, `UseCase000XImplementation`, `BuildInstructions`, `PrototypeImplementationAIUsage` |
+| `P5-Normalization/` | P5 | `Normalization`, `NormalizationAIUsage` |
+| `P6-AdvancedReports/` | P6 | `AdvancedReports`, `AdvancedReportsAIUsage` |
`Instructions.md` is the condensed course rubric — reference material, not a
@@ -61,6 +63,6 @@
| P3 | [UseCaseModel](P3-UseCaseModel/UseCaseModel.md) | Finished, awaiting approval |
| P4 | [PrototypeImplementation](P4-Prototype/PrototypeImplementation.md) | Finished, awaiting approval |
-| P5 | *Normalization* | Not started |
-| P6 | *Complex DB Reports* | Not started |
+| P5 | [Normalization](P5-Normalization/Normalization.md) | Finished, awaiting approval |
+| P6 | [AdvancedReports](P6-AdvancedReports/AdvancedReports.md) | Finished, awaiting approval |
| P7 | *Advanced Database Development* | Not started |
| P8 | *Advanced Application Development* | Not started |
@@ -77,6 +79,8 @@
| File | Phase | What |
|------|-------|------|
-| [`ERModel_v02.xml`](P1-ConceptualModel/ERModel_v02.xml) | P1 | TerraER source, current version |
-| [`ERModel_v02.png`](P1-ConceptualModel/ERModel_v02.png) | P1 | Exported diagram image, current version |
+| [`ERModel_v03.xml`](P1-ConceptualModel/ERModel_v03.xml) | P1 | TerraER source, current version |
+| [`ERModel_v03.png`](P1-ConceptualModel/ERModel_v03.png) | P1 | Exported diagram image, current version |
+| [`ERModel_v02.xml`](P1-ConceptualModel/ERModel_v02.xml) | P1 | TerraER source, previous version (kept per P1 rules) |
+| [`ERModel_v02.png`](P1-ConceptualModel/ERModel_v02.png) | P1 | Exported diagram image, previous version |
| [`ERModel_v01.xml`](P1-ConceptualModel/ERModel_v01.xml) | P1 | TerraER source, first version (kept per P1 rules) |
| [`ERModel_v01.png`](P1-ConceptualModel/ERModel_v01.png) | P1 | Exported diagram image, first version |
@@ -84,4 +88,5 @@
| [`../server/db/data_load.sql`](../server/db/data_load.sql) | P2 | DML — truncates and reloads sample data |
| [`relational_schema.jpg`](P2-RelationalDesign/relational_schema.jpg) | P2 | Crow's-foot diagram exported from DBeaver |
+| [`../server/db/reports_demo_data.sql`](../server/db/reports_demo_data.sql) | P6 | Optional multi-quarter demo data for the two reports (not part of `-init`) |
## Use cases (P3)
Index: server/cli.go
===================================================================
--- server/cli.go (revision df058387ebe7d07864d874fd1376f15512418581)
+++ server/cli.go (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4)
@@ -76,4 +76,6 @@
fmt.Println("[8] Manage watchlist")
fmt.Println("[9] Logout")
+ fmt.Println("[10] Report: top traders")
+ fmt.Println("[11] Report: market performance")
fmt.Println("[0] Exit")
switch prompt("> ") {
@@ -94,4 +96,8 @@
case "8":
ManageWatchlist(s)
+ case "10":
+ ShowTopTraders(s)
+ case "11":
+ ShowMarketPerformance(s)
case "9":
s.UserID = ""
Index: server/db/schema_creation.sql
===================================================================
--- server/db/schema_creation.sql (revision df058387ebe7d07864d874fd1376f15512418581)
+++ server/db/schema_creation.sql (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4)
@@ -59,13 +59,18 @@
-- ============================================================================
CREATE TABLE project.holdings (
- id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
- user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE,
- crypto_id uuid NOT NULL REFERENCES project.crypto(id),
- quantity numeric(20,4) NOT NULL CHECK (quantity >= 0),
+ id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
+ user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE,
+ crypto_id uuid NOT NULL REFERENCES project.crypto(id),
+ quantity numeric(20,4) NOT NULL CHECK (quantity >= 0),
+ -- Committed to the user's own open sell orders, not yet removed from the
+ -- position. quantity - reserved_quantity is what is actually free to
+ -- sell — the crypto-side equivalent of users.available_balance.
+ reserved_quantity numeric(20,4) NOT NULL DEFAULT 0
+ CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity),
-- Weighted-average entry price. NOT NULL so that the P/L arithmetic in
-- v_portfolio can never silently produce NULL for an existing position.
- avg_price numeric(18,6) NOT NULL DEFAULT 0 CHECK (avg_price >= 0),
- created_at timestamptz NOT NULL DEFAULT now(),
- updated_at timestamptz,
+ avg_price numeric(18,6) NOT NULL DEFAULT 0 CHECK (avg_price >= 0),
+ created_at timestamptz NOT NULL DEFAULT now(),
+ updated_at timestamptz,
CONSTRAINT uq_holdings_user_crypto UNIQUE (user_id, crypto_id)
);
@@ -184,4 +189,6 @@
c.symbol,
h.quantity,
+ h.reserved_quantity,
+ (h.quantity - h.reserved_quantity) AS available_quantity,
h.avg_price,
lp.price AS current_price,
@@ -192,2 +199,111 @@
LEFT JOIN project.markets m ON m.crypto_id = c.id AND m.quote_currency = 'USD'
LEFT JOIN project.v_latest_prices lp ON lp.market_id = m.id;
+
+-- ============================================================================
+-- REPORTS (P6 — Complex DB Reports)
+-- Both are single SELECT statements (with CTEs), wrapped as SQL functions so
+-- they can be called as parameterised reports from the prototype instead of
+-- being copy-pasted SQL text. See docs/P6-AdvancedReports/AdvancedReports.md.
+-- ============================================================================
+
+-- report_top_traders: realized trading performance per user over [p_from, p_to),
+-- bucketed into quarters to measure how consistently each user was profitable.
+CREATE OR REPLACE FUNCTION project.report_top_traders(p_from timestamptz, p_to timestamptz)
+RETURNS TABLE (
+ username varchar,
+ realized_pl numeric,
+ total_invested numeric,
+ roi_pct numeric,
+ profitable_periods bigint,
+ losing_periods bigint,
+ total_periods bigint,
+ consistency_pct numeric
+)
+LANGUAGE sql STABLE AS $$
+ WITH period_pl AS (
+ SELECT
+ t.user_id,
+ date_trunc('quarter', t.created_at) AS period,
+ SUM(t.amount) AS period_pl,
+ SUM(t.amount) FILTER (WHERE t.type = 'buy') AS period_buy
+ FROM project.transactions t
+ WHERE t.type IN ('buy', 'sell', 'fee')
+ AND t.created_at >= p_from
+ AND t.created_at < p_to
+ GROUP BY t.user_id, date_trunc('quarter', t.created_at)
+ )
+ SELECT
+ u.username,
+ SUM(pp.period_pl) AS realized_pl,
+ ABS(SUM(pp.period_buy)) AS total_invested,
+ ROUND(SUM(pp.period_pl) / NULLIF(ABS(SUM(pp.period_buy)), 0) * 100, 2) AS roi_pct,
+ COUNT(*) FILTER (WHERE pp.period_pl > 0) AS profitable_periods,
+ COUNT(*) FILTER (WHERE pp.period_pl < 0) AS losing_periods,
+ COUNT(*) AS total_periods,
+ ROUND(COUNT(*) FILTER (WHERE pp.period_pl > 0)::numeric
+ / NULLIF(COUNT(*), 0) * 100, 2) AS consistency_pct
+ FROM period_pl pp
+ JOIN project.users u ON u.id = pp.user_id
+ GROUP BY u.id, u.username
+ ORDER BY realized_pl DESC;
+$$;
+
+-- report_market_performance: trading activity and price behaviour per market
+-- over [p_from, p_to). Volume/trade-count/price stats come from market_trades
+-- (the complete tape — user fills and simulated fills alike); participating
+-- users can only come from orders, since market_trades has no user_id column.
+CREATE OR REPLACE FUNCTION project.report_market_performance(p_from timestamptz, p_to timestamptz)
+RETURNS TABLE (
+ symbol varchar,
+ quote_currency char(3),
+ total_volume numeric,
+ trade_count bigint,
+ avg_price numeric,
+ market_return_pct numeric,
+ price_volatility numeric,
+ participating_users bigint
+)
+LANGUAGE sql STABLE AS $$
+ WITH trades AS (
+ SELECT
+ market_id, price, quantity, executed_at,
+ FIRST_VALUE(price) OVER w AS first_price,
+ LAST_VALUE(price) OVER (PARTITION BY market_id ORDER BY executed_at
+ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_price
+ FROM project.market_trades
+ WHERE executed_at >= p_from AND executed_at < p_to
+ WINDOW w AS (PARTITION BY market_id ORDER BY executed_at)
+ ),
+ market_stats AS (
+ SELECT
+ market_id,
+ SUM(quantity) AS total_volume,
+ COUNT(*) AS trade_count,
+ AVG(price) AS avg_price,
+ STDDEV(price) AS price_volatility,
+ MAX(first_price) AS first_price,
+ MAX(last_price) AS last_price
+ FROM trades
+ GROUP BY market_id
+ ),
+ participation AS (
+ SELECT market_id, COUNT(DISTINCT user_id) AS participating_users
+ FROM project.orders
+ WHERE status = 'executed' AND executed_at >= p_from AND executed_at < p_to
+ GROUP BY market_id
+ )
+ SELECT
+ c.symbol,
+ m.quote_currency,
+ ms.total_volume,
+ ms.trade_count,
+ ROUND(ms.avg_price, 6) AS avg_price,
+ ROUND((ms.last_price - ms.first_price) / NULLIF(ms.first_price, 0) * 100, 2) AS market_return_pct,
+ ROUND(COALESCE(ms.price_volatility, 0), 6) AS price_volatility,
+ COALESCE(p.participating_users, 0) AS participating_users
+ FROM market_stats ms
+ JOIN project.markets m ON m.id = ms.market_id
+ JOIN project.crypto c ON c.id = m.crypto_id
+ LEFT JOIN participation p ON p.market_id = ms.market_id
+ ORDER BY ms.total_volume DESC;
+$$;
Index: server/portfolio.go
===================================================================
--- server/portfolio.go (revision df058387ebe7d07864d874fd1376f15512418581)
+++ server/portfolio.go (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4)
@@ -3,4 +3,5 @@
import (
"fmt"
+ "strings"
"bp_project/server/db"
@@ -13,4 +14,6 @@
`SELECT symbol,
quantity,
+ COALESCE(reserved_quantity, 0),
+ COALESCE(available_quantity, quantity),
COALESCE(avg_price, 0),
COALESCE(current_price, 0),
@@ -28,8 +31,9 @@
defer rows.Close()
+ header := fmt.Sprintf(" %-8s %12s %12s %12s %14s %14s %14s %14s",
+ "Symbol", "Quantity", "Reserved", "Available", "Avg buy", "Current", "Value", "Unrealised P/L")
fmt.Println()
- fmt.Printf(" %-8s %12s %14s %14s %14s %14s\n",
- "Symbol", "Quantity", "Avg buy", "Current", "Value", "Unrealised P/L")
- fmt.Println(" ------------------------------------------------------------------------------------")
+ fmt.Println(header)
+ fmt.Println(" " + strings.Repeat("-", len(header)-2))
var totalValue, totalPnL float64
@@ -37,11 +41,11 @@
for rows.Next() {
var sym string
- var qty, avg, cur, val, pnl float64
- if err := rows.Scan(&sym, &qty, &avg, &cur, &val, &pnl); err != nil {
+ var qty, reserved, avail, avg, cur, val, pnl float64
+ if err := rows.Scan(&sym, &qty, &reserved, &avail, &avg, &cur, &val, &pnl); err != nil {
fmt.Println("scan error:", err)
return
}
- fmt.Printf(" %-8s %12.4f %14.6f %14.6f %14.4f %+14.4f\n",
- sym, qty, avg, cur, val, pnl)
+ fmt.Printf(" %-8s %12.4f %12.4f %12.4f %14.6f %14.6f %14.4f %+14.4f\n",
+ sym, qty, reserved, avail, avg, cur, val, pnl)
totalValue += val
totalPnL += pnl
@@ -52,7 +56,7 @@
return
}
- fmt.Println(" ------------------------------------------------------------------------------------")
- fmt.Printf(" %-8s %12s %14s %14s %14.4f %+14.4f\n",
- "TOTAL", "", "", "", totalValue, totalPnL)
+ fmt.Println(" " + strings.Repeat("-", len(header)-2))
+ fmt.Printf(" %-8s %12s %12s %12s %14s %14s %14.4f %+14.4f\n",
+ "TOTAL", "", "", "", "", "", totalValue, totalPnL)
// cash summary
Index: server/trade.go
===================================================================
--- server/trade.go (revision df058387ebe7d07864d874fd1376f15512418581)
+++ server/trade.go (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4)
@@ -13,4 +13,13 @@
// 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" {
@@ -47,9 +56,9 @@
defer tx.Rollback()
- // 1. create the order (status='executed' since we fill immediately)
+ // 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, executed_at)
- VALUES ($1, $2, $3, 'market', 'executed', $4, $5, now())
+ `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,
@@ -87,5 +96,6 @@
}
- // upsert holding with running weighted average
+ // 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)
@@ -104,25 +114,42 @@
}
} else {
- // sell: check holding
- var held, avgPrice float64
+ // 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, avg_price FROM holdings
+ `SELECT quantity, reserved_quantity, avg_price FROM holdings
WHERE user_id = $1 AND crypto_id = $2 FOR UPDATE`,
s.UserID, m.CryptoID,
- ).Scan(&held, &avgPrice)
+ ).Scan(&held, &reserved, &avgPrice)
if err != nil && err != sql.ErrNoRows {
fmt.Println("Error:", err)
return
}
- if err == sql.ErrNoRows || held < qty {
- fmt.Printf("Insufficient holding: trying to sell %.4f, hold %.4f\n", qty, held)
- return
- }
-
- // reduce holding
+ 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 quantity = quantity - $1,
- updated_at = now()
+ 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,
@@ -163,4 +190,13 @@
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)