Changeset 9577c79

docs/P1-ConceptualModel/ERModel.md

-              rdf05838
+              r9577c79
 # Entity-Relationship Model v.02
+# Entity-Relationship Model v.03
 ## Diagram
+![ERModel_v02](ERModel_v02.png)
+![ERModel_v03](ERModel_v03.png)
 Notation: Chen. Rectangles are entity sets, diamonds are relationships, ellipses
 …
 ## 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
 …
 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
 …
 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
 …
 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
 …
 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
 …
 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
 …
 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
 …
 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
 …
 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
 …
 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
 …
 #### 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
 …
 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**
 …
 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
 …
 . `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.

docs/P1-ConceptualModel/ERModelAIUsage.md

-              rdf05838
+              r9577c79
 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
 …
 ## 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
 …
 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**:
 …
 >    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/`.

docs/P2-RelationalDesign/RelationalDesign.md

-              rdf05838
+              r9577c79
 - **Markets**(<u>**id**</u>, *crypto_id*, quote_currency, is_active, created_at)
   - Candidate keys: `{id}`, `{crypto_id, quote_currency}`. `UNIQUE(crypto_id, quote_currency)`.
 - **Holdings**(<u>**id**</u>, *user_id*, *crypto_id*, quantity, avg_price, created_at, updated_at)
+- **Holdings**(<u>**id**</u>, *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
 …
     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**(<u>**id**</u>, *user_id*, *market_id*, side, type, status, quantity, price, placed_at, executed_at)
   - `side ∈ {buy, sell}`, `type ∈ {market, limit}`, `status ∈ {open, executed, cancelled}`.
 …
 ### 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**:
 …
   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
 …
 - 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)

docs/P2-RelationalDesign/RelationalDesignAIUsage.md

-              rdf05838
+              r9577c79
 > 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.

docs/P3-UseCaseModel/UseCase0004.md

-              rdf05838
+              r9577c79
    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,
 …
     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
 …
    -- 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)
 …
        ($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;

docs/P3-UseCaseModel/UseCase0005.md

-              rdf05838
+              r9577c79
 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
 …
    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
    ```
+. If the holding check passes, system reduces the holding, credits cash and debits invested, and appends a ledger and a market trade:
+. 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;
 …
        ($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;
    ```
 . 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)

docs/P3-UseCaseModel/UseCase0006.md

-              rdf05838
+              r9577c79
    SELECT symbol,
           quantity,
+          COALESCE(reserved_quantity,  0),
+          COALESCE(available_quantity, quantity),
           COALESCE(avg_price,      0),
           COALESCE(current_price,  0),
 …
     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.
 . System displays the rows and a computed summary:
 …
        c.symbol,
        h.quantity,
+       h.reserved_quantity,
+       (h.quantity - h.reserved_quantity)      AS available_quantity,
        h.avg_price,
        lp.price                                AS current_price,

docs/P3-UseCaseModel/UseCaseModel.md

-              rdf05838
+              r9577c79
  * [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
 …
 ||[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.||
 …
    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).

docs/P3-UseCaseModel/UseCaseModelAIUsage.md

-              rdf05838
+              r9577c79
 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.

docs/P4-Prototype/BuildInstructions.md

-              rdf05838
+              r9577c79
 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
 …
 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
 …
 ### 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.
 . `./eduberza -init` — prints `Database initialised.`
 . `./eduberza`, then `[2] Login` → `alice` / `test123` → `Login successful.`
 . `[6] View portfolio` → one row: `ETH 0.5000` at avg 3500.000000, current
 .000000, value 1760.0000, unrealised P/L `+10.0000`. Cash available
 .0000, net worth 10010.0000.
+. `[6] View portfolio` → one row: `ETH 0.5000` reserved 0.0000, available
+.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] Place market BUY order` → `BTC` → `0.01` →
    `Order executed: buy 0.0100 BTC @ 67140.000000 (notional 671.4000 USD)`.
 …
   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
+.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
+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.`

docs/P4-Prototype/PrototypeImplementation.md

-              rdf05838
+              r9577c79
    `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.
 …
  * 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 ==
 …
    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
 …
 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).

docs/P4-Prototype/PrototypeImplementationAIUsage.md

-              rdf05838
+              r9577c79
 ## 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
 …
 > 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**
+. `holdings.reserved_quantity numeric(20,4) NOT NULL DEFAULT 0 CHECK
+   (reserved_quantity >= 0 AND reserved_quantity <= quantity)` added to
+   `schema_creation.sql`.
+. `v_portfolio` gained `reserved_quantity` and derived `available_quantity`.
+. `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.
+. 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.
+. `portfolio.go` gained `Reserved`/`Available` columns, reading
+   `v_portfolio.reserved_quantity`/`available_quantity`, so the new field is
+   something a Trader can actually see.
+. 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.

docs/P4-Prototype/UseCase0004Implementation.md

-              rdf05838
+              r9577c79
    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;
 …
    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,
 …
    --     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())
 …
        ($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;
    ```
 …
 ## 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:
 ```

docs/P4-Prototype/UseCase0005Implementation.md

-              rdf05838
+              r9577c79
 **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)
 …
    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;
 …
        ($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;
    ```
 …
 ## 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 = '<alice>';"
+ 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 = '<alice>' AND crypto_id = '<btc>';
+--  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 = '<alice>' AND crypto_id = '<btc>';
+SELECT quantity, reserved_quantity, quantity - reserved_quantity AS available
+  FROM holdings WHERE user_id = '<alice>' AND crypto_id = '<btc>';
+--  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 = '<alice>' AND crypto_id = '<btc>';
+SELECT quantity, reserved_quantity, quantity - reserved_quantity AS available
+  FROM holdings WHERE user_id = '<alice>' AND crypto_id = '<btc>';
+--  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
+----------+--------------------
+.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
+.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 = '<alice>' AND crypto_id = '<btc>';
+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`.

docs/P4-Prototype/UseCase0006Implementation.md

-              rdf05838
+              r9577c79
    ```sql
    SELECT symbol, quantity,
+          COALESCE(reserved_quantity,  0),
+          COALESCE(available_quantity, quantity),
           COALESCE(avg_price,      0),
           COALESCE(current_price,  0),
 …
 ### 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
 …
   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

docs/README.md

-              rdf05838
+              r9577c79
 ## Team members
 - *Your First Name Last Name — Index XXXXXX*
+- Stefan Trsunov 231285
 ## Course
 …
 | `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
 …
 | 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 |
 …
 | 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 |
 …
 | [`../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)

server/cli.go

-              rdf05838
+              r9577c79
         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("> ") {
 …
         case "8":
                 ManageWatchlist(s)
+        case "10":
+                ShowTopTraders(s)
+        case "11":
+                ShowMarketPerformance(s)
         case "9":
                 s.UserID = ""

server/db/schema_creation.sql

-              rdf05838
+              r9577c79
 -- ============================================================================
 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)
 );
 …
        c.symbol,
        h.quantity,
+       h.reserved_quantity,
+       (h.quantity - h.reserved_quantity) AS available_quantity,
        h.avg_price,
        lp.price                           AS current_price,
 …
 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;
+$$;

server/portfolio.go

-              rdf05838
+              r9577c79
 import (
         "fmt"
+        "strings"
         "bp_project/server/db"
 …
                 `SELECT symbol,
                         quantity,
+                        COALESCE(reserved_quantity, 0),
+                        COALESCE(available_quantity, quantity),
                         COALESCE(avg_price, 0),
                         COALESCE(current_price, 0),
 …
         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
 …
         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
 …
                 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

server/trade.go

-              rdf05838
+              r9577c79
 // 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" {
 …
         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,
 …
+                }
+                // 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)
 …
+                }
         } 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,
 …
                  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)

Context Navigation

Legend:

docs/P1-ConceptualModel/ERModel.md

docs/P1-ConceptualModel/ERModelAIUsage.md

docs/P2-RelationalDesign/RelationalDesign.md

docs/P2-RelationalDesign/RelationalDesignAIUsage.md

docs/P3-UseCaseModel/UseCase0004.md

docs/P3-UseCaseModel/UseCase0005.md

docs/P3-UseCaseModel/UseCase0006.md

docs/P3-UseCaseModel/UseCaseModel.md

docs/P3-UseCaseModel/UseCaseModelAIUsage.md

docs/P4-Prototype/BuildInstructions.md

docs/P4-Prototype/PrototypeImplementation.md

docs/P4-Prototype/PrototypeImplementationAIUsage.md

docs/P4-Prototype/UseCase0004Implementation.md

docs/P4-Prototype/UseCase0005Implementation.md

docs/P4-Prototype/UseCase0006Implementation.md

docs/README.md

server/cli.go

server/db/schema_creation.sql

server/portfolio.go

server/trade.go

Download in other formats: