Changeset ef1c1c7 for docs

docs/P1-ConceptualModel/ERModel.md

-              ra531b45
+              ref1c1c7
 # Entity-Relationship Model v.03
+# Entity-Relationship Model v.04
 ## Diagram
 ![ERModel_v03](ERModel_v03.png)
+![ERModel_v04](ERModel_v04.png)
 Notation: Chen. Rectangles are entity sets, diamonds are relationships, ellipses
 …
 | `available_balance` | numeric(18,4) | required, default 0, ≥ 0 |
 | `invested_balance` | numeric(18,4) | required, default 0, ≥ 0 |
+| `reserved_balance` | numeric(18,4) | required, default 0, ≥ 0 — cash set aside for the user's open buy orders (added in v04, after P7) |
 | `created_at` | timestamptz | required, defaults to now |
 | `updated_at` | timestamptz | optional (null until first change) |
 …
 makes the ledger auditable.
+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
+Placing an order is what triggers a **reservation** of whatever it commits:
+the crypto being sold (`Holds.reserved_quantity`, below) on a sell, and the
+cash (`Users.reserved_balance`) on a buy. Since v04 (after P7) an order can
+wait in the order book and be filled in parts, so `status` is a real
+lifecycle driven by `filled_quantity`: `open` (nothing filled yet),
+`partially_filled`, `executed` (completely filled), or `cancelled`, which
+releases what is still reserved. See
 [UseCase0005](../P3-UseCaseModel/UseCase0005.md) for the reserve-then-settle
+sequence.
+sequence and
+[AdvancedDatabaseDevelopment](../P7-AdvancedDatabaseDevelopment/AdvancedDatabaseDevelopment.md)
+for the rules that keep it consistent.
 **Keys:** candidate `{id}` only — there is no natural key, since the same user
 …
 | `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` |
+| `type` | text | required, `market` or `limit` (both executed since P7) |
+| `status` | text | required, `open`, `partially_filled`, `executed` or `cancelled` |
 | `quantity` | numeric(20,4) | required, > 0 |
+| `price` | numeric(18,6) | optional — null until the order settles, then the fill price |
+| `filled_quantity` | numeric(20,4) | required, default 0, between 0 and `quantity` — how much has been traded; remaining = `quantity − filled_quantity` (added in v04, after P7) |
+| `price` | numeric(18,6) | the limit price; for a market order, the market price when it was placed |
 | `placed_at` | timestamptz | required, defaults to now |
 | `executed_at` | timestamptz | optional, set when the order settles |
 …
 | `source` | text(50) | required, default `simulation` — distinguishes a simulated trade from a user's own fill (`user`) |
+Since v04 (after P7) a trade also records which orders it filled, through the
+relationships `FillsBuy` and `FillsSell` below.
+#### OrderEvents
+*Added in v04, after P7.* The audit trail of an order: one event for its
+placement, one for every (partial) fill, and one for a cancellation. The
+`Orders` row only holds the current state; this entity keeps the history of
+how the order got there. Events are recorded automatically by the database.
+**Keys:** candidate `{id}` only; primary key **`id`** (auto-incrementing
+integer, events are only read in order).
+| Attribute | Type | Constraints |
+|---|---|---|
+| `id` | integer | PK, required, auto-generated |
+| `event_type` | text | required, `placed`, `partially_filled`, `filled` or `cancelled` |
+| `quantity` | numeric(20,4) | required — the ordered quantity for `placed`, the filled amount for a fill, the unfilled rest for `cancelled` |
+| `price` | numeric(18,6) | optional — the order price, or the trade price for a fill |
+| `status_after` | text | required, the order's status after the event |
+| `created_at` | timestamptz | required, defaults to now |
 #### MarketCandles
 OHLCV aggregates per market and timeframe — the data a price chart is drawn
 …
 #### Fills — Markets (1) : MarketTrades (N), total on MarketTrades
 Every executed trade happened on exactly one market. No attributes.
+#### FillsBuy — Orders (1) : MarketTrades (N), partial on both sides
+*Added in v04, after P7.* The buy order a trade filled. An order can be
+filled by many trades (partial fills); a trade fills at most one buy order,
+and none when the simulated market was the buyer. No attributes.
+#### FillsSell — Orders (1) : MarketTrades (N), partial on both sides
+*Added in v04, after P7.* The sell order a trade filled, symmetric to
+`FillsBuy`. A trade between two users' orders participates in both. No
+attributes.
+#### Logs — Orders (1) : OrderEvents (N), total on OrderEvents
+*Added in v04, after P7.* Every event belongs to exactly one order. No
+attributes.
 #### Aggregates — Markets (1) : MarketCandles (N), total on MarketCandles
 …
   [UseCase0005](../P3-UseCaseModel/UseCase0005.md) for how the new attribute
   is enforced.
+- **v04 — after P7.** Phase 7 (order, balance and trade consistency) needed
+  data the model did not have, so the model was extended to stay in line with
+  the database:
+  - `Users.reserved_balance`: cash reserved by open buy orders;
+  - `Orders.filled_quantity` and the status value `partially_filled`: orders
+    can now be filled in parts;
+  - the relationships `FillsBuy` and `FillsSell` between `Orders` and
+    `MarketTrades`: which orders a trade filled;
+  - the entity set `OrderEvents` with the relationship `Logs`: the
+    automatically recorded history of every order.
+  Nothing existing was removed or changed. See
+  [AdvancedDatabaseDevelopment](../P7-AdvancedDatabaseDevelopment/AdvancedDatabaseDevelopment.md).
+  The diagram files are `ERModel_v04.xml` / `ERModel_v04.png`; earlier versions
+  are kept.
 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

-              ra531b45
+              ref1c1c7
 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/`.
+### Session 4 — 2026-09-24 (Claude Opus 5.5): v04 after P7
+**Prompts (student, verbatim):**
+> But this order_events is added after Phase 7 right? can we add that too?
+> can you make the ERmodel again with TerraER file to update it? and tell that after P7 we added this
+> Make it with a white background like earier versions
+**Response (AI, summarised):**
+- Explained that the P7 changes must also appear in P1 and P2, since both must describe the
+  current data structure.
+- Built `ERModel_v04.xml` in TerraER's own file format by taking `ERModel_v03.xml` unchanged
+  and appending the new elements with the same XML structure TerraER uses:
+  - the attribute `reserved_balance` on `Users`;
+  - the attribute `filled_quantity` on `Orders`;
+  - the relationships `FillsBuy` and `FillsSell` (Orders 1 : N MarketTrades, partial);
+  - the entity set `OrderEvents` (key `id`, `event_type`, `quantity`, `price`,
+    `status_after`, `created_at`) with `Logs` (Orders 1 : N OrderEvents, total on
+    OrderEvents).
+- Rendered `ERModel_v04.png` with TerraER 3.14's own drawing classes (loading the `.xml`
+  exactly as TerraER does and using its image export), on a white background and trimmed like
+  the earlier versions.
+- Updated [ERModel](ERModel.md) (title v.04, new attribute rows, the `OrderEvents` section, the
+  three relationships, and a v04 history entry stating these were added after P7).
+**What I decided:** to add the P7 structure to the ER model. The new elements are placed
+automatically, so the layout can be tidied by hand in TerraER.

docs/P2-RelationalDesign/RelationalDesign.md

ra531b45	ref1c1c7
148	148	2. Right-click the database → ERD For Database (or open a blank ERD and drag
149	149	the `project` tables in).
150		3. Arrange the tables to mirror `ERModel_v01.png`.
	150	3. Arrange the tables to mirror `ERModel_v03.png`.
151	151	4. Download image → PNG, then convert:
152	152	`convert relational_schema.png relational_schema.jpg`

docs/P3-UseCaseModel/UseCase0004.md

-              ra531b45
+              ref1c1c7
 . Trader chooses "Place market BUY order".
 . System lists the available markets with their latest price:
+. System lists the active markets, numbered, with their latest price:
    ```sql
+   SELECT m.id, c.symbol, m.quote_currency, COALESCE(lp.price, 0)
+   SELECT m.id, c.id, c.symbol, m.quote_currency,
+          COALESCE(lp.price, 0) AS price
      FROM project.markets m
      JOIN project.crypto  c  ON c.id = m.crypto_id
 …
     ORDER BY c.symbol;
    ```
+. Trader enters a market symbol, e.g. `ETH`.
+. System resolves the market and looks up the latest price:
+. Trader picks the market by its number in the listed markets, e.g. `2` (BTC).
+. System takes the chosen row's market id and crypto id from the list (no lookup by
+   symbol) and looks up the latest price:
    ```sql
+   SELECT m.id, c.id AS crypto_id, c.symbol, m.quote_currency
+     FROM project.markets m
+     JOIN project.crypto c ON c.id = m.crypto_id
+    WHERE upper(c.symbol) = upper($1) AND m.is_active = true;
+   SELECT price FROM project.v_latest_prices WHERE market_id = $2;
+   SELECT price FROM project.v_latest_prices WHERE market_id = $1;
    ```
 . Trader enters a quantity.
 …
 If `available_balance < notional`, the entire transaction rolls back and system shows "Insufficient funds: need X, have Y."
 ### Alternate flow 4a — market not found
+### Alternate flow 3a — number not in the list
 If the entered symbol does not match any active market, system shows "market X not found" and returns to the authenticated menu without opening a transaction.
+If the entered number is not one of the listed market numbers, system shows "Invalid choice, enter a number from 1 to N." and returns to the authenticated menu without opening a transaction.

docs/P3-UseCaseModel/UseCase0005.md

-              ra531b45
+              ref1c1c7
 . Trader chooses "Place market SELL order".
+. System lists markets (same SQL as UC0004 step 2).
+. Trader enters market symbol and quantity.
+. System resolves the market and looks up the latest price (same SQL as UC0004 step 4).
+. System lists, numbered, the Trader's holdings that still have a quantity free to
+   sell (not reserved by an open sell order), with the quantity held, the free
+   quantity and the latest price:
+   ```sql
+   SELECT m.id, c.id, c.symbol, m.quote_currency,
+          h.quantity, h.quantity - h.reserved_quantity AS free,
+          COALESCE(lp.price, 0) AS price
+     FROM project.holdings h
+     JOIN project.crypto  c ON c.id = h.crypto_id
+     JOIN project.markets m ON m.crypto_id = c.id AND m.is_active = true
+     LEFT JOIN project.v_latest_prices lp ON lp.market_id = m.id
+    WHERE h.user_id = $1
+      AND h.quantity - h.reserved_quantity > 0
+    ORDER BY c.symbol;
+   ```
+. Trader picks the holding by its number in the listed holdings, e.g. `2` (ETH), and
+   enters the quantity.
+. System takes the chosen row's market id and crypto id from the list (no lookup by
+   symbol) and looks up the latest price:
+   ```sql
+   SELECT price FROM project.v_latest_prices WHERE market_id = $1;
+   ```
 . System opens a transaction:

docs/P3-UseCaseModel/UseCase0007.md

-              ra531b45
+              ref1c1c7
 ### Add a crypto
+The Trader picks the crypto by its number from the listed cryptos that are not on the watchlist yet:
 ```sql
+-- 1. resolve the symbol to a crypto_id
+SELECT id FROM project.crypto WHERE upper(symbol) = upper($1);
+-- 1. list, numbered, the cryptos not yet on the watchlist
+SELECT c.id, c.symbol, c.name
+  FROM project.crypto c
+ WHERE NOT EXISTS (SELECT 1 FROM project.watchlist_items wi
+                    WHERE wi.watchlist_id = $1 AND wi.crypto_id = c.id)
+ ORDER BY c.symbol;
 -- 2. insert the item; do nothing if it's already there
+-- 2. insert the chosen crypto ($2 = its id from the list); do nothing if it's already there
 INSERT INTO project.watchlist_items (watchlist_id, crypto_id)
 VALUES ($watchlist_id, $crypto_id)
+VALUES ($1, $2)
 ON CONFLICT (watchlist_id, crypto_id) DO NOTHING;
 ```
 …
 ### Remove a crypto
+The Trader picks the crypto by its number from the listed cryptos on the watchlist:
 ```sql
+-- 1. list, numbered, the cryptos on the watchlist
+SELECT c.id, c.symbol, c.name
+  FROM project.watchlist_items wi
+  JOIN project.crypto c ON c.id = wi.crypto_id
+ WHERE wi.watchlist_id = $1
+ ORDER BY c.symbol;
+-- 2. delete the chosen crypto ($2 = its id from the list)
 DELETE FROM project.watchlist_items
+ WHERE watchlist_id = $1
+   AND crypto_id = (
+       SELECT id FROM project.crypto
+        WHERE upper(symbol) = upper($2)
+   );
+ WHERE watchlist_id = $1 AND crypto_id = $2;
 ```
 If the delete affects zero rows, system shows "Not in watchlist."
+If the entered number is not one of the listed numbers, system shows "Invalid choice, enter a number from 1 to N." and nothing is changed.

docs/P4-Prototype/BuildInstructions.md

-              ra531b45
+              ref1c1c7
 # Build Instructions
 How to compile, configure, run and test the EduBerza prototype.
 Linked from [PrototypeImplementation](PrototypeImplementation.md).
+This page explains how to compile, configure, run and test the EduBerza prototype.
+It is linked from [PrototypeImplementation](PrototypeImplementation.md).
 ## Development environment description
 …
 | Tool        | Version tested        | Needed for                                                    |
 |-------------|-----------------------|---------------------------------------------------------------|
+| Go          | 1.26 (1.25+ works)    | Building `server/` (the CLI) and `bots/` (the market bot).     |
+| PostgreSQL  | 16                    | The database. Docker image or the faculty server.              |
+| Docker      | any recent            | Optional — brings up a local PostgreSQL in one command.        |
+| `psql`      | any                   | Optional — running the SQL scripts by hand.                    |
+| Java        | 21 (8+ works)         | Optional — only to open/edit the ER diagram in TerraER.         |
+| DBeaver     | any recent            | Optional — only to export `relational_schema.jpg`.              |
+Nothing else has to be installed. The only third-party Go dependency
+(`github.com/lib/pq`, the PostgreSQL driver) is fetched automatically by
+`go build` from `go.mod`/`go.sum`.
+| Go          | 1.26.0 (`go.mod` asks for 1.25 or newer) | Building `server/` (the CLI) and `bots/` (the market bot). |
+| PostgreSQL  | 16.3 (Docker container) | The database. Either the local Docker container or the faculty server. |
+| Docker + Docker Compose | any recent | Optional. Starts a local PostgreSQL with one command. |
+| `psql`      | 16                    | Optional. Only for running the SQL scripts by hand.            |
+| Java        | 21 (8+ works)         | Optional. Only to open or edit the ER diagram in TerraER.      |
+| DBeaver     | any recent            | Optional. Only to export `relational_schema.jpg`.              |
+About the PostgreSQL version: `docker-compose.yml` uses the image `postgres` without a version
+tag. Docker therefore starts whatever version of the official image it has pulled. On the
+machine where the prototype was tested, that was PostgreSQL 16.3. The only extension the schema
+needs is `pgcrypto` (`CREATE EXTENSION IF NOT EXISTS pgcrypto`). It ships with PostgreSQL and is
+included in the official image.
+You do not need to install anything else. The only third-party Go library is the PostgreSQL
+driver `github.com/lib/pq`. `go build` downloads it automatically, at the version pinned in
+`go.mod` and `go.sum`.
 ## Build instructions
 All commands run from the repository root.
+Run all commands from the repository root.
 ### 1. Configure the database connection
 …
 ```
 The defaults in `.env.example` match the bundled Docker setup. To use the
+faculty database instead, either edit `.env` or pass the values as real
 environment variables — those take precedence over the file:
+The defaults in `.env.example` (`localhost:5433`, user `bp_project`, database `bp_database`)
+match the bundled Docker setup. To use the faculty database instead, edit `.env`, or pass the
+values as real environment variables. Real environment variables take precedence over the file:
 ```sh
 …
 ```
+`.env` is deliberately not committed (see `.gitignore`) because it holds a
+password.
+`.env` is not committed on purpose (see `.gitignore`), because it holds a password.
 ### 2. Start PostgreSQL
 …
 ```
 Skip this step if you are pointing at the faculty database.
+Skip this step if you use the faculty database.
 ### 3. Build
 …
 ```
+This runs `server/db/schema_creation.sql` and then `server/db/data_load.sql`.
+Both are **compiled into the binary** (`go:embed`), so `-init` works regardless
+of which directory you launch it from. It is destructive and idempotent — it
+drops and recreates the whole `project` schema, so it is also the reset button
+if a demo goes wrong. To reload only the data, keeping the schema:
+```sh
+./eduberza -load-data
+```
+The equivalent with `psql`, if you prefer to watch the statements run:
+This runs `server/db/schema_creation.sql` and then `server/db/data_load.sql`. It logs
+`Running schema_creation.sql ...`, `Running data_load.sql ...` and `Database initialised.`,
+then prints:
+```
+Schema initialised. Re-run without -init to start the CLI.
+```
+Both scripts are **compiled into the binary** (`go:embed`), so `-init` works from any
+directory. It is destructive and can be run again any number of times: it drops and recreates
+the whole `project` schema, so it also resets everything if a demo goes wrong. To reload only
+the data and keep the schema:
+```sh
+./eduberza -load-data          # prints "Sample data reloaded."
+```
+If you prefer to watch the statements run, the same can be done with `psql`:
 ```sh
 …
 ```
+Seed accounts — all with the password `test123`:
+| Username  | Starting state                                        |
+|-----------|-------------------------------------------------------|
+| `alice`   | 8250.00 USD cash, holds 0.5 ETH — best demo account   |
+| `bob`     | 5000.00 USD cash, no positions                        |
+| `charlie` | 2500.00 USD cash, no positions                        |
+### 6. Optional — run the market simulation bot
+In a second terminal:
+```sh
+go run ./bots
+```
+The bot walks the price of every active market, inserts a row into
+`market_trades` on each tick and upserts the current 1-minute candle. Prices in
+the CLI change while it runs, because the current price is always read from the
+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:
+### 6. Optional: run the market simulation bot
+In a second terminal, also from the repository root (the bot reads `.env` from the current
+directory):
+```sh
+go run ./bots                  # add -interval 1s for faster ticks; the default is 3s
+```
+On every tick the bot moves the price of every active market by a small random step, inserts a
+row into `market_trades` and updates the current 1-minute candle. Prices in the CLI change
+while it runs, because the current price is always read from the most recent trade
+(`v_latest_prices`) and never from a stored column. Leave the bot off if you want the exact
+numbers in the tests below.
+### 7. Optional: richer data for the P6 reports
+`data_load.sql` seeds only a few minutes of trade history. That is not enough for the
+[top traders](../P6-AdvancedReports/AdvancedReports.md) and
+[market performance](../P6-AdvancedReports/AdvancedReports.md) reports (menu `[10]` and `[11]`)
+to show more than one period. To see more interesting results, load five quarters of synthetic
+history on top:
 ```sh
 …
 ```
 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.
+This script is not part of `-init` or `-load-data` on purpose. The header of
+[`reports_demo_data.sql`](../../server/db/reports_demo_data.sql) explains why. Running it never
+changes the balances that the tests below check.
 ## Testing instructions
+### How to launch and log in
+Start the prototype with `./eduberza` after steps 1–4. The sample data creates three test
+users. All of them have the password **`test123`**:
+| Username  | Starting state after `-init` |
+|-----------|------------------------------|
+| `alice`   | 8250.00 USD available (1750.00 invested), holds 0.5 ETH bought at 3500.00. Watchlist "Favorites": BTC, ETH, SOL. Best demo account. |
+| `bob`     | 5000.00 USD available, no crypto. Watchlist "Bobs Picks": BTC, DOGE. |
+| `charlie` | 2500.00 USD available, no crypto, no watchlist yet. |
+The five sample markets are ADA, BTC, DOGE, ETH and SOL, all quoted in USD. Their starting
+last prices are 0.45375, 67140, 0.122, 3520 and 166.1.
 ### Mini-guide to the application
+The CLI has two menus. Before logging in: **Register**, **Login**,
+**Browse markets**. After logging in: **View balance**, **Deposit virtual
+funds**, **Browse markets**, **Place market BUY order**, **Place market SELL
+order**, **View portfolio**, **View transaction history**, **Manage watchlist**,
+**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
+numbered list with their current price before you are asked which one you want,
+and assets are referred to by symbol (`BTC`, `ETH`, …), never by database id.
+You always answer with the number of a menu option. When you have to choose a market, a
+holding or a crypto, the prototype prints a numbered list and you type the number from that
+list. You never type an id or a symbol. A number that is not in the list is refused with
+`Invalid choice, enter a number from 1 to N.`
+**Menu before login**
+| Option | What it does and how to use it |
+|--------|--------------------------------|
+| `[1] Register` | Enter a username, an e-mail (must contain `@`), your full name and a password (at least 6 characters). You get `Account created. You can now log in.`, or `Invalid email.`, `Password must be at least 6 characters.` or `Username or email already taken.` A new account starts with 0 USD. |
+| `[2] Login` | Enter your username and password. You get `Login successful.` and the second menu. A wrong password and an unknown username both give `Invalid credentials.` |
+| `[3] Browse markets` | Prints the numbered list of markets with their last price. |
+| `[0] Exit` | Ends the program. |
+**Menu after login** (headed `--- Logged in as <username> ---`)
+| Option | What it does and how to use it |
+|--------|--------------------------------|
+| `[1] View balance` | Shows the available, invested and total USD. |
+| `[2] Deposit virtual funds` | Enter an amount in USD. It must be a positive number, otherwise you get `Invalid amount.` You get `Deposited 500.0000 USD.` |
+| `[3] Browse markets` | Same list as before login. |
+| `[4] Place market BUY order` | Lists all markets, numbered, with their last price. Type the number at `Market #:`. The prototype shows the latest price. Type the quantity. You get `Order executed: buy …` or `Insufficient funds: need …, have …`. |
+| `[5] Place market SELL order` | Lists only the cryptos you hold, numbered, with columns `Held` and `Free to sell`. Type the number at `Holding #:`, then the quantity. You get `Order executed: sell …` or `Insufficient holding: …`. If you hold nothing, you get `you hold no crypto that is free to sell`. |
+| `[6] View portfolio` | One row per crypto you hold: quantity, reserved, available, average buy price, current price, value and unrealised P/L. Then your cash, portfolio value and net worth. |
+| `[7] View transaction history` | Your last 20 ledger entries (deposits, buys, sells), newest first. |
+| `[8] Manage watchlist` | Opens a submenu: `[1] List items` shows your watchlist with last prices. `[2] Add crypto` lists, numbered, the cryptos not on it yet; type a number. `[3] Remove crypto` lists, numbered, the cryptos on it; type a number. `[0] Back` returns. A user without a watchlist gets one named "Favorites" the first time. |
+| `[9] Logout` | Back to the first menu. |
+| `[10] Report: top traders` | P6 report. Enter a start date (inclusive) and an end date (exclusive) as `YYYY-MM-DD`. |
+| `[11] Report: market performance` | P6 report, with the same two dates. |
+| `[0] Exit` | Ends the program. |
 ### End-to-end smoke test
+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` 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` →
+These values were checked on 2026-09-24 against freshly loaded sample data (PostgreSQL 16.3),
+with the bot not running. The expected values are exact.
+. `./eduberza -init` prints `Schema initialised. Re-run without -init to start the CLI.`
+. `./eduberza`, then `2` (Login), then `alice` / `test123` gives `Login successful.`
+. `6` (View portfolio) shows one row: `ETH`, quantity 0.5000, reserved 0.0000, available
+.5000, average buy 3500.000000, current 3520.000000, value 1760.0000, unrealised P/L
+   `+10.0000`. Cash available is 8250.0000 and net worth is 10010.0000.
+. `4` (BUY). The market list shows `1 ADA`, `2 BTC`, `3 DOGE`, `4 ETH`, `5 SOL`. Type `2` at
+   `Market #:`, then `0.01` at `Quantity:`. The result is
    `Order executed: buy 0.0100 BTC @ 67140.000000 (notional 671.4000 USD)`.
+. `[6] View portfolio` → now BTC *and* ETH, total value 2431.4000, cash
+.6000 (= 8250.00 − 671.40), net worth still 10010.0000.
+. `[5] Place market SELL order` → `ETH` → `0.5` →
+. `6` (View portfolio) now shows BTC *and* ETH, with total value 2431.4000, cash 7578.6000
+   (= 8250.00 − 671.40), and net worth still 10010.0000.
+. `5` (SELL). The holdings list shows `1 BTC` (held 0.0100) and `2 ETH` (held 0.5000). Type
+   `2` at `Holding #:`, then `0.5`. The result is
    `Order executed: sell 0.5000 ETH @ 3520.000000 (notional 1760.0000 USD)`.
+. `[7] View transaction history` → deposit, buy, buy, sell, newest first.
+. `[8] Manage watchlist` → `[1] List items` → alice's `Favorites` contains
+   BTC, ETH, SOL with live prices.
+. `[9] Logout`, then `[0] Exit`.
+. `7` (View transaction history) lists, newest first: the `sell` (+1760.0000), the `buy` of
+   BTC (−671.4000), then the two rows from the sample data, which have the same timestamp:
+   `deposit` 10000.0000 "Initial virtual deposit" and `buy` −1750.0000 "Market buy 0.5 ETH @
+.00".
+. `8` (Manage watchlist), then `1` (List items), shows alice's watchlist with BTC, ETH and SOL
+   and their last prices. Then `2` (Add crypto) lists `1 ADA` and `2 DOGE`; type `1` and you get
+   `Added ADA.` Then `0` (Back).
+. `9` (Logout), then `0` (Exit).
 ### Testing the failure paths
+These matter more than the happy path, because they are what proves the
+transactions actually roll back:
+- **Insufficient funds:** log in as `charlie` (2500 USD) and try to buy `1` BTC.
+  Expect `Insufficient funds: need 67140.0000, have 2500.0000` and *no* change
+  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, 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.
+These matter more than the happy path, because they prove that the transactions really roll
+back and that invalid choices are refused:
+- **Insufficient funds:** log in as `charlie` (2500 USD). Choose `4`, market `2` (BTC),
+  quantity `1`. Expect `Insufficient funds: need 67140.0000, have 2500.0000` and *no* change to
+  any table: no order row, no ledger entry, no holding.
+- **Insufficient holding:** on fresh data (`./eduberza -load-data`), log in as `alice`. Choose
+  `5`; the list shows only `1 ETH` (held 0.5000, free 0.5000). Choose `1`, quantity `5`. Expect
+  `Insufficient holding: trying to sell 5.0000, available 0.5000 (of 0.5000 held, 0.0000 reserved)`.
+- **Nothing to sell:** as `bob` (no crypto), choose `5`. The holdings list is empty, and you
+  get `you hold no crypto that is free to sell` without being asked for a number.
+- **Invalid choice from a list:** in any list (for example `8`, then `3` Remove crypto), type a
+  number larger than the list. Expect `Invalid choice, enter a number from 1 to N.`
+- **Invalid deposit:** choose `2` and enter `-50`. Expect `Invalid amount.`
 - **Duplicate registration:** register with username `alice`. Expect
   `Username or email already taken.`
+- **Invalid e-mail:** register with an e-mail without `@`. Expect `Invalid email.`
 - **Wrong password:** log in as `alice` with any wrong password. Expect
+  `Invalid credentials.` — and note the same message for an unknown username, so
+  the prototype does not leak which accounts exist.
+  `Invalid credentials.` An unknown username gives the same message, so the prototype does not
+  reveal which accounts exist.
+The concurrency guarantee of the sell path (two processes selling the same crypto at the same
+moment) cannot be reproduced by typing into two terminals, because each order commits within
+milliseconds. It is described in [UseCase0005Implementation](UseCase0005Implementation.md).
 ### For the public presentation
 Demo with `alice` (already has a position, so the portfolio screen is not
+empty), and register a brand-new account live to show UC0001. Run the bot in a
 background terminal so the prices visibly move between two portfolio refreshes.
+Demo with `alice`. She already has a position, so the portfolio screen is not empty. Register a
+brand-new account live to show UC0001. Run the bot in a background terminal so the prices
+visibly move between two portfolio refreshes.
 ## Editing the ER diagram
+TerraER is a third-party tool and is deliberately **not** committed to this
+repository. Download the teacher's build from
+<https://bazi.finki.ukim.mk/resources/Software/> and run it:
+```sh
+java -jar TerraER3.11.jar     # then File → Open → docs/ERModel_v01.xml
+```
+Save new versions as `ERModel_v02.xml`, `ERModel_v03.xml`, … and export a
+matching PNG for each. TerraER does not add the extension itself — type
+`.xml` explicitly or the file will not reopen.
+TerraER is a third-party tool and is **not** committed to this repository on purpose. Download
+the teacher's build from <https://bazi.finki.ukim.mk/resources/Software/> and run it:
+```sh
+java -jar TerraER3.11.jar     # then File → Open → docs/P1-ConceptualModel/ERModel_v03.xml
+```
+The current version is `ERModel_v03.xml`. Save new versions as `ERModel_v04.xml` and so on,
+and export a matching PNG for each. TerraER does not add the extension itself: type `.xml`
+yourself, or the file will not reopen.
 ## Up-to-date source code
 The repository is pushed to the FINKI DEVELOP git server; see the Repositories
 section in EPRMS for the clone URL and credentials.
+The repository is pushed to the FINKI DEVELOP git server. The clone URL and credentials are in
+the Repositories section in EPRMS.
 ### About the source code
+- All source needed to run the prototype is in this repository: the CLI
+  (`server/`), the market bot (`bots/`), the DDL script and the sample-data
+  script (`server/db/`).
+- Third-party Go libraries are **not** vendored — `go build` downloads
+  `github.com/lib/pq` using the pinned versions in `go.mod` and `go.sum`.
+- Third-party executables are **not** committed. `.gitignore` excludes `*.jar`;
+  TerraER is downloaded from the URL above.
+- No third-party images, styles or frameworks are used, and there are no
+  images in the prototype at all — the interface is text.
+- All the source needed to run the prototype is in this repository: the CLI (`server/`), the
+  market bot (`bots/`), the DDL script and the sample-data script (`server/db/`).
+- Third-party Go libraries are **not** vendored. `go build` downloads `github.com/lib/pq` at
+  the versions pinned in `go.mod` and `go.sum`.
+- Third-party executables are **not** committed. `.gitignore` excludes `*.jar`, and TerraER is
+  downloaded from the URL above.
+- No third-party images, styles or frameworks are used. The prototype has no images at all;
+  the interface is text.

docs/P4-Prototype/PrototypeImplementation.md

-              ra531b45
+              ref1c1c7
+= Prototype Implementation =
+# Prototype Implementation
+The prototype is a Go command-line application in
+[https://github.com/StefanTrsunov/bp/tree/main/server server/] that works against the `project`
+schema in PostgreSQL. It implements all seven use cases from
+[https://github.com/StefanTrsunov/bp/blob/main/docs/P3-UseCaseModel/UseCaseModel.md UseCaseModel]
+– the rubric requires at least three – with every database access shown as real, executed SQL.
+An auxiliary program in [https://github.com/StefanTrsunov/bp/tree/main/bots bots/] simulates a
+live market so prices move while the prototype is running.
+EduBerza's P4 prototype is a Go command-line program (in [`server/`](../../server/)). It works
+against the `project` schema in PostgreSQL. It implements all seven use cases from
+[UseCaseModel](../P3-UseCaseModel/UseCaseModel.md); the course asks for at least three. Every
+database access is real SQL that was executed and tested. A second program, the market bot in
+[`bots/`](../../bots/), simulates a live market so prices move while the prototype runs.
+Build, configure, run and test instructions:
+[https://github.com/StefanTrsunov/bp/blob/main/docs/P4-Prototype/BuildInstructions.md BuildInstructions].
+## Implemented use-cases
+All pages listed below, together with the screenshots of each run, are kept in the project's
+GitHub repository, [https://github.com/StefanTrsunov/bp StefanTrsunov/bp], under
+`docs/P4-Prototype/`.
+- [UseCase0001Implementation](UseCase0001Implementation.md) — Register new account
+- [UseCase0002Implementation](UseCase0002Implementation.md) — Log in
+- [UseCase0003Implementation](UseCase0003Implementation.md) — Deposit virtual funds
+- [UseCase0004Implementation](UseCase0004Implementation.md) — Place market BUY order
+- [UseCase0005Implementation](UseCase0005Implementation.md) — Place market SELL order
+- [UseCase0006Implementation](UseCase0006Implementation.md) — View portfolio and transaction history
+- [UseCase0007Implementation](UseCase0007Implementation.md) — Manage watchlist
+== Implemented use-cases ==
+Each page follows its P3 use case step by step. It adds the exact SQL the Go code runs in that
+step and a screenshot of the step from a real run against the database. Screenshots are in
+[`screenshots/`](screenshots/).
+||=Page=||=Use-case=||=Source=||
+||[https://github.com/StefanTrsunov/bp/blob/main/docs/P4-Prototype/UseCase0001Implementation.md UseCase0001Implementation]||Register a new account||[https://github.com/StefanTrsunov/bp/blob/main/server/auth.go server/auth.go]||
+||[https://github.com/StefanTrsunov/bp/blob/main/docs/P4-Prototype/UseCase0002Implementation.md UseCase0002Implementation]||Log in||[https://github.com/StefanTrsunov/bp/blob/main/server/auth.go server/auth.go]||
+||[https://github.com/StefanTrsunov/bp/blob/main/docs/P4-Prototype/UseCase0003Implementation.md UseCase0003Implementation]||Deposit virtual funds||[https://github.com/StefanTrsunov/bp/blob/main/server/account.go server/account.go]||
+||[https://github.com/StefanTrsunov/bp/blob/main/docs/P4-Prototype/UseCase0004Implementation.md UseCase0004Implementation]||Place market BUY order||[https://github.com/StefanTrsunov/bp/blob/main/server/trade.go server/trade.go]||
+||[https://github.com/StefanTrsunov/bp/blob/main/docs/P4-Prototype/UseCase0005Implementation.md UseCase0005Implementation]||Place market SELL order||[https://github.com/StefanTrsunov/bp/blob/main/server/trade.go server/trade.go]||
+||[https://github.com/StefanTrsunov/bp/blob/main/docs/P4-Prototype/UseCase0006Implementation.md UseCase0006Implementation]||View portfolio and history||[https://github.com/StefanTrsunov/bp/blob/main/server/portfolio.go server/portfolio.go]||
+||[https://github.com/StefanTrsunov/bp/blob/main/docs/P4-Prototype/UseCase0007Implementation.md UseCase0007Implementation]||Manage watchlist||[https://github.com/StefanTrsunov/bp/blob/main/server/watchlist.go server/watchlist.go]||
+- How to build, configure, run and test the prototype: [BuildInstructions](BuildInstructions.md)
+- AI usage for this phase: [PrototypeImplementationAIUsage](PrototypeImplementationAIUsage.md)
+Each page mirrors its P3 use-case page and adds the actual SQL emitted by the Go code plus a
+screenshot of the corresponding run against the live database. The screenshots are committed
+alongside the pages, in
+[https://github.com/StefanTrsunov/bp/tree/main/docs/P4-Prototype/screenshots docs/P4-Prototype/screenshots/].
+## Technology and architecture
+== What the prototype demonstrates about the database design ==
+- **Language:** Go (module `bp_project`, `go 1.25` in [`go.mod`](../../go.mod)). The only
+  third-party library is the PostgreSQL driver `github.com/lib/pq`.
+- **Database:** PostgreSQL. Every table, view and function is in the `project` schema. The DDL
+  is [`schema_creation.sql`](../../server/db/schema_creation.sql) and the sample data is
+  [`data_load.sql`](../../server/db/data_load.sql). Both scripts are compiled into the binary
+  and run by `./eduberza -init`.
+- **Interface:** plain text menus on standard input and output. There are no web server,
+  frameworks, images or styles.
+- **Structure:** one source file per area of the application.
+ * '''The current price is never stored as a column.''' It is always the price of the most recent
+   row in `market_trades`, read through the `v_latest_prices` view. Both the user's own fills and
+   the bot's simulated trades feed the same table, so there is exactly one definition of "the
+   price".
+ * '''Money movements are transactional.''' Buying touches five tables – `orders`, `users`,
+   `holdings`, `transactions`, `market_trades` – inside one transaction. A failed balance check
+   rolls the whole thing back: after a rejected purchase there is no order row, no ledger entry
+   and no holding. This is verified in the failure-path tests in
+   [https://github.com/StefanTrsunov/bp/blob/main/docs/P4-Prototype/BuildInstructions.md BuildInstructions].
+ * '''Constraints do real work.''' `UNIQUE (user_id, crypto_id)` on `holdings` is what makes the
+   `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. `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.
+| File | Responsibility | Use cases |
+|------|----------------|-----------|
+| [`server/main.go`](../../server/main.go) | Flags `-init` / `-load-data`, then starts the menu loop | — |
+| [`server/cli.go`](../../server/cli.go) | The two menus (before and after login), input reading | all |
+| [`server/db/db.go`](../../server/db/db.go) | Connection from `.env` / environment variables, embedded SQL scripts | — |
+| [`server/auth.go`](../../server/auth.go) | Register, log in (SHA-256 password hash) | UC0001, UC0002 |
+| [`server/account.go`](../../server/account.go) | Balance, deposit, transaction history | UC0003, UC0006 |
+| [`server/market.go`](../../server/market.go) | Market list, choosing a market or a holding by number, latest price | UC0004, UC0005 |
+| [`server/trade.go`](../../server/trade.go) | Market buy and sell orders, each in one transaction | UC0004, UC0005 |
+| [`server/portfolio.go`](../../server/portfolio.go) | Portfolio with current value and unrealised P/L | UC0006 |
+| [`server/watchlist.go`](../../server/watchlist.go) | List, add and remove watchlist items | UC0007 |
+| [`bots/main.go`](../../bots/main.go) | Market bot: random-walk price ticks into `market_trades`, 1-minute candles | — |
+== Known limitations ==
+## No identifiers to remember
 Deliberately out of scope for a first prototype, and the natural content of the later phases:
+The user never has to type or remember an id, a code or a symbol:
+ * Only `market` orders execute. `limit` is accepted by the schema (`orders.type`) but the
+   matching logic is not implemented.
+ * Passwords are SHA-256 without a salt. Adequate to demonstrate that the password itself is
+   never stored; not adequate for real use. A proper password hash belongs in P9 (security).
+ * Money is handled as `float64` in Go while the database columns are `numeric`. All arithmetic
+   that must be exact – the weighted average – is done in SQL for that reason, but the Go side
+   would need a decimal type for real use.
+ * There is no connection pooling configuration and no explicit isolation level; both are P8
+   topics.
+ * 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.
+- Every menu is numbered, and the user answers with the number of an option.
+- **Buying:** all active markets are listed with their latest price, numbered `1…n`. The user
+  enters the market's number at `Market #:` (`ChooseMarket` in `market.go`).
+- **Selling:** only the cryptos the user actually holds are listed, each with the quantity held
+  and the quantity still free to sell. The user enters the holding's number at `Holding #:`
+  (`ChooseHolding`). A user who holds nothing free to sell gets
+  `you hold no crypto that is free to sell` and is never asked to choose.
+- **Watchlist:** *Add* lists the cryptos that are not on the watchlist yet. *Remove* lists the
+  ones that are on it. Both are numbered, and the user enters the number.
+- A number outside the list is refused with `Invalid choice, enter a number from 1 to N.` and
+  nothing is changed.
+== AI usage ==
+The only things the user types are their own data: username, e-mail, full name, password, the
+amount to deposit, the quantity to buy or sell, and the date range of the two P6 reports.
+AI was used in this phase and is logged in full, per the course rule for P1 onward.
+## What the prototype demonstrates about the database design
+ * '''Phase log:'''
+   [https://github.com/StefanTrsunov/bp/blob/main/docs/P4-Prototype/PrototypeImplementationAIUsage.md PrototypeImplementationAIUsage.md]
+   – service used, the bugs found and fixed, the test evidence, 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],
+   [https://github.com/StefanTrsunov/bp/blob/main/docs/P1-ConceptualModel/ERModelAIUsage.md#session-3--2026-09-16 Session 3 – 2026-09-16].
+- **The current price is never stored as a column.** It is always the price of the most recent
+  row in `market_trades`, read through the `v_latest_prices` view. The user's own fills and the
+  bot's simulated trades go into the same table, so there is only one definition of "the
+  price".
+- **Money movements are transactional.** A buy touches five tables (`orders`, `users`,
+  `holdings`, `transactions`, `market_trades`) inside one transaction. If the balance check
+  fails, the whole transaction is rolled back: after a rejected purchase there is no order row,
+  no ledger entry and no holding. The failure-path tests in
+  [BuildInstructions](BuildInstructions.md) check this.
+- **Constraints do real work.** `UNIQUE (user_id, crypto_id)` on `holdings` is what makes the
+  `INSERT … ON CONFLICT DO UPDATE` upsert possible, so the database recomputes the
+  weighted-average entry price in one statement, instead of the application reading, changing
+  and writing the row. `CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` does
+  the same for the sell path: the database itself makes an inconsistent reservation
+  impossible, and it does not rely only on `trade.go` being careful.
+- **Selling reserves before it removes.** A sell order locks the holding row with
+  `SELECT … FOR UPDATE`, reserves the quantity being sold, then settles by removing it (see
+  [UseCase0005Implementation](UseCase0005Implementation.md)). Two sell orders for more than the
+  free quantity, placed at the same moment from two separate processes, are serialised by the
+  row lock. Exactly one of them succeeds. This was tested with two concurrent processes in
+  session 3 (see [PrototypeImplementationAIUsage](PrototypeImplementationAIUsage.md)).
+'''Service:''' Claude Code (Anthropic), https://claude.com/claude-code – Claude subscription,
+model Claude Opus 4.7 (1M context) in sessions 1–2, Claude Sonnet 5 in session 3.
+## Known limitations
+'''In short:''' session 1 rewrote the existing Chi/HTTP backend as the CLI prototype covering
+UC0001–UC0007 and added the market bot. Session 2 was a review pass I asked for, which found and
+fixed three bugs – a path-resolution bug that made the documented build instructions fail, an
+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`. 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).
+These were left out on purpose for a first prototype. They belong to the later phases:
+- Only `market` orders execute. The schema accepts `limit` (`orders.type`), but there is no
+  matching logic for it.
+- Passwords are hashed with SHA-256 and no salt. That shows the password itself is never
+  stored, but it is not good enough for real use. A proper password hash belongs in P9
+  (security).
+- Money is `float64` in Go, while the database columns are `numeric`. For that reason, all
+  arithmetic that must be exact (the weighted average) is done in SQL. Real use would need a
+  decimal type on the Go side too.
+- The prototype sets no connection pool and no explicit isolation level. Both are P8 topics.
+- A reservation only exists inside one transaction, because the prototype only has market
+  orders, and they settle immediately. A real limit-order matcher would leave
+  `holdings.reserved_quantity` set and `orders.status = 'open'` between two separate commits.
+  It would also need a way to cancel an order and release the reservation. Neither is
+  implemented, because nothing in the prototype creates an order that stays open.
+## History of changes
+The code started as my own Go backend: HTTP handlers, a draft schema, and a `db.go` that
+recreated the tables on every start. It changed as follows. The AI's share of each change is
+logged in [PrototypeImplementationAIUsage](PrototypeImplementationAIUsage.md).
+| Date | Change | Origin |
+|------|--------|--------|
+| 2026-04-21 | My HTTP backend rewritten as the CLI prototype covering UC0001–UC0007. The schema errors in my draft were corrected. The market bot was added. | My code and decisions (CLI instead of web, drop the frontend, keep a simulator); rewrite by AI (session 1) |
+| 2026-08-06/07 | Three bugs fixed: path resolution of `.env` and the SQL scripts, an endless loop at end of input, and an error check in the wrong order on the sell path. The holding update became one `INSERT … ON CONFLICT DO UPDATE`. | I asked for a code review; fixes by AI (session 2) |
+| 2026-09-16 | `holdings.reserved_quantity` added. The sell path now reserves, then settles. Orders go from `open` to `executed`. | The edge case was mine; implementation by AI (session 3) |
+| 2026-09-24 | Every choice is picked from a numbered list: markets by number, a sell lists only the user's holdings, the watchlist lists the cryptos. All screenshots were retaken, one per step. | I asked for a check against the P4 rules; implementation by AI (session 4) |
+**Service:** Claude Code (Anthropic), Claude subscription. Session 1 used Claude Opus 4.7 (1M
+context), session 2 Claude Opus 5 (1M context), session 3 Claude Sonnet 5, and session 4
+Claude Opus 5.5 (1M context).

docs/P4-Prototype/PrototypeImplementationAIUsage.md

-              ra531b45
+              ref1c1c7
 ## Name of AI service/solution that was used
+**Claude Code** (Anthropic)
+**Claude Code** (Anthropic), an AI coding assistant that runs in the terminal and reads and
+edits the project files.
 - **URL:** https://claude.com/claude-code
+- **Type of service/subscription:** Claude subscription, model Claude Opus 4.7 (1M context).
+- **Type of service/subscription:** Claude subscription (Claude Code CLI). A different model was
+  used in each session:
+| Session | Date | Model |
+|---------|------|-------|
+| 1 | 2026-04-21 | Claude Opus 4.7 (1M context) |
+| 2 | 2026-08-06 / 2026-08-07 | Claude Opus 5 (1M context) |
+| 3 | 2026-09-16 | Claude Sonnet 5 |
+| 4 | 2026-09-24 | Claude Opus 5.5 (1M context) |
+The same sessions also worked on other phases. This page covers only what concerns the P4
+prototype and its documentation.
 ## Final result
 …
 ### Results in details / description
+During the 2026-04-21 session the AI:
+- Replaced the broken HTTP + frontend scaffolding (which the student had already decided to drop) with a single-binary CLI prototype in Go, split across `main.go`, `cli.go`, `auth.go`, `account.go`, `market.go`, `trade.go`, `portfolio.go`, `watchlist.go`.
+- Consolidated the broken `db.go` — which used to drop every table on every startup — into a single `Connect()` plus explicit `-init` and `-load-data` flags.
+- Moved `go.mod` from `server/` to the project root so the same module graph covers both `server/` and `bots/`.
+- Fixed `go.mod`: removed the unused MySQL driver, promoted `github.com/lib/pq` to a direct dependency.
+- Implemented the trade flows as real database transactions with row-level `FOR UPDATE` locks, cost-basis bookkeeping, and a ledger row per operation.
+- Wrote a 140-line market-simulation bot that inserts trades and upserts 1-minute candles every tick.
+- Exercised the full prototype end-to-end against a running PostgreSQL on port 5433 and verified a sample buy and portfolio view produced the expected numbers.
+**My own starting code (before any AI was used).** Before session 1 I had written:
+- a Go backend with HTTP handlers (Chi router);
+- a draft database schema (`server/db/db.sql`, `server/db/schema.sql`) and a diagram description
+  (`docs/dbdiagram.md`), based on my data model in `ep-diagram.md`;
+- a `main.go` / `db.go` that connected to PostgreSQL and dropped and recreated every table on
+  each start;
+- a half-finished frontend, which I deleted myself at the start of session 1 because the
+  prototype does not need it.
+This code is older than the git repository. The first commit (2026-08-07) was made after
+sessions 1 and 2, so the history in git starts from the AI-improved version. My original files
+are not in the repository. What they contained, and which errors the AI found in them, is
+recorded in the session 1 log below and in [ERModelAIUsage](../P1-ConceptualModel/ERModelAIUsage.md).
+**Session 1 (2026-04-21).** Starting from that code, the AI:
+- replaced my HTTP backend and the frontend scaffolding with a single-binary CLI prototype in Go,
+  split across `main.go`, `cli.go`, `auth.go`, `account.go`, `market.go`, `trade.go`,
+  `portfolio.go` and `watchlist.go`. This followed my decision to make a CLI and not a web app;
+- corrected my schema. `crypto_id` had been declared as a foreign key to two tables at once in
+  `holdings`, `orders` and `transactions`, and `market_candles` referenced a `markets` table that
+  did not exist. The corrected schema became `schema_creation.sql`, with the sample data in
+  `data_load.sql`;
+- replaced my `db.go`, which dropped every table on every start, with one `Connect()` plus
+  explicit `-init` and `-load-data` flags;
+- moved `go.mod` to the project root, removed an unused MySQL driver, and made
+  `github.com/lib/pq` a direct dependency;
+- wrote the trade flows as database transactions with `FOR UPDATE` row locks, cost-basis
+  bookkeeping and one ledger row per operation;
+- wrote the market-simulation bot (`bots/main.go`), after I decided to keep a market simulator
+  in the project.
+**Session 2 (2026-08-06/07).** I asked for a code review. The AI found and fixed three bugs
+(`.env`/SQL path resolution, an endless loop at end of input, and a wrong error check on the
+sell path). It also replaced the read-modify-write holding update with one
+`INSERT … ON CONFLICT DO UPDATE`, removed the committed `TerraER3.11.jar` and a TradingView
+screenshot we had no licence for, and wrote the first version of the P4 pages.
+**Session 3 (2026-09-16).** From an edge case I described, the AI added
+`holdings.reserved_quantity`, made the sell path reserve and then settle, and gave
+`orders.status` a real `open` → `executed` lifecycle.
+**Session 4 (2026-09-24).** I asked whether P4 fulfils the course rules. The AI found that the
+prototype still asked the user to type a market symbol and crypto symbols, which breaks the rule
+that the user must never have to remember identifiers or codes. It changed every choice to a
+numbered list (`market.go`, `trade.go`, `watchlist.go`), retook every screenshot, one per
+scenario step, and rewrote the P4 pages.
 ### Test evidence
+This is the current prototype (after session 4) on fresh sample data, logged in as `alice`. It
+is a real run, and the same run is on the screenshots of
+[UseCase0004Implementation](UseCase0004Implementation.md):
 ```
+-- Place market buy order --
+  #     Symbol    Quote       Last price
+  -----------------------------------------
+     ADA       USD           0.453750
+     BTC       USD       67140.000000
+     DOGE      USD           0.122000
+     ETH       USD        3520.000000
+     SOL       USD         166.100000
+Market #: 2
+Latest price for BTC/USD = 67140.000000
+Quantity: 0.01
 Order executed: buy 0.0100 BTC @ 67140.000000 (notional 671.4000 USD)
+  Symbol        Quantity         Avg buy         Current           Value  Unrealised P/L
+  BTC             0.0100    67140.000000    67140.000000        671.4000         +0.0000
+  ETH             0.5000     3500.000000     3520.000000       1760.0000        +10.0000
+  TOTAL                                                        2431.4000        +10.0000
+  Symbol        Quantity      Reserved     Available         Avg buy         Current           Value  Unrealised P/L
+  ------------------------------------------------------------------------------------------------------------------
+  BTC             0.0100        0.0000        0.0100    67140.000000    67140.000000        671.4000         +0.0000
+  ETH             0.5000        0.0000        0.5000     3500.000000     3520.000000       1760.0000        +10.0000
+  ------------------------------------------------------------------------------------------------------------------
+  TOTAL                                                                                    2431.4000        +10.0000
   Cash available : 7578.6000 USD
 …
 ## Summary of AI involvement
+| | 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
+review pass I asked for specifically because I have to defend this code in
+person: it turned up a path-resolution bug that made my own documented build
+instructions fail, an infinite loop at end of input, and an error check in the
+wrong order that misreported database failures as "Insufficient holding".
+| | Session 1 — 2026-04-21 | Session 2 — 2026-08-06/07 | Session 3 — 2026-09-16 | Session 4 — 2026-09-24 |
+|---|---|---|---|---|
+| **What I brought** | My Go backend (Chi HTTP handlers), draft schema, 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 | The official P4 instructions and the question whether the prototype and pages meet them |
+| **What the AI did** | Rewrote the backend as a CLI covering UC0001–UC0007, corrected the schema, wrote the market bot | Reviewed the code, found and fixed three bugs, improved the holding upsert | Added `holdings.reserved_quantity`, reserve-then-settle sell path, `orders.status` lifecycle, tested it including real concurrency | Audited P4, made every choice a numbered list, retook all screenshots, rewrote the P4 pages |
+| **What I decided** | To delete the frontend, to build a CLI and not a web app, to keep the market simulator | To ask for a code review and not only documentation | To keep reserve and settle in one transaction, because there is no cancel-order use case to free a stuck reservation | To fix the problems found and to have the screenshots taken from real runs |
 ## Entire AI usage log
+See [ERModelAIUsage](../P1-ConceptualModel/ERModelAIUsage.md) for the full prompt/response transcript of the 2026-04-21 session — that single conversation produced all of the P1–P4 artefacts. The defining student prompt for P4 was:
+**About this log.** Sessions 3 and 4 are still in my local Claude Code history, so their
+prompts are quoted **verbatim** below, typos included. The local history of sessions 1 and 2
+(April and August 2026) is no longer available. For them, the prompts are the excerpts I wrote
+down at the time, the same ones as on [ERModelAIUsage](../P1-ConceptualModel/ERModelAIUsage.md),
+where they were logged because the same conversation produced P1–P4. The AI's responses are
+summarised, because several ran to many pages of code and file edits.
+---
+### Session 1 — 2026-04-21 (Claude Opus 4.7, 1M context)
+**Prompt (student, excerpt):**
+> This is my project for my exam Database, go to the docs folder and README.md there you will find all of the documentation, afterwards take a look at the whole project. Delete everything you want add everything you want just make it work and make it fill in or to follow all of the needed instructions. Ask me anything, also tell me what do I need to do
+**Response (AI, summarised):**
+- Read `docs/opis.md`, `docs/ep-diagram.md`, `docs/dbdiagram.md`, `server/db/db.sql`,
+  `server/db/schema.sql` and all Go sources.
+- Found errors in my schema: `holdings.crypto_id` declared as a foreign key to both
+  `users(id)` and `crypto(id)` (and the same mistake in `orders` and `transactions`), and
+  `market_candles` referencing a non-existent `markets` table. Also found that `main.go` dropped
+  and recreated all tables on every start, wiping the data.
+- Asked me to decide on team composition, prototype shape (CLI or HTTP), which columns to keep,
+  which use cases to implement, and the documentation language.
+**Prompt (student, excerpt):**
+> Read it now, just filled it in, also deleted the frontend we don' need that
+**Response (AI, summarised):**
+- Re-read `docs/README.md`, which now contained the course rubric, and explained the AI rules
+  per phase and the deliverables of each phase.
+**Prompt (student, excerpt):**
 > do everything that you need to do, and tell me after what do I need to do
+which translated into the rewrite of the backend and the writing of this documentation.
+> **Student action required:** append any further prompts here where you asked the AI to debug, extend, or refactor the prototype.
+### Session 2 — 2026-08-06 / 2026-08-07
+Prompts are logged in full in [ERModelAIUsage](../P1-ConceptualModel/ERModelAIUsage.md#session-2--2026-08-06--2026-08-07).
+The student asked the AI to complete the phase documentation and to *"fix some
+database things or golang things if you think we can do it better"*. What the AI
+changed in the prototype, and why:
+**Response (AI, summarised):**
+- Rewrote the backend as the CLI prototype (UC0001–UC0007), with the corrected
+  `schema_creation.sql`, `data_load.sql`, the new `db.go` with `-init` / `-load-data`, the
+  transactional trade flows and the market bot. The details are under *Results in details*
+  above.
+- Ran the prototype end to end against PostgreSQL on port 5433 and checked that a sample buy
+  and the portfolio view gave the expected numbers.
+---
+### Session 2 — 2026-08-06 / 2026-08-07 (Claude Opus 5, 1M context)
+**Prompt (student, excerpt):**
+> […] do all of the other Phases till m0.
+> opis.md It's p0 so I will take care of that. Delete anything that we don't need,
+> make all of the phases and terra diagram if you can, and delete anything
+> that we don't need and make a documentation about how to start it.
+**Prompt (student, excerpt, follow-up):**
+> Also fix some database things or golang things if you think we can do it better,
+**Response (AI, summarised): what changed in the prototype, and why**
 **Bugs found and fixed**
+. `server/db/db.go` resolved `../.env` and `db/schema_creation.sql` as paths
+   relative to the working directory, so they only worked when the program was
+   started from inside `server/`. Following the documented instructions — build
+   from the repository root and run `./eduberza -init` — failed with
+   `password authentication failed for user "postgres"`, because the `.env` file
+   was never found and the defaults were used. The two SQL scripts are now
+   compiled into the binary with `go:embed`, and `.env` is located by searching
+   the working directory and every parent. Real environment variables now take
+   precedence over the file, which is what allows the prototype to be pointed at
+   the faculty database without editing anything.
+. `prompt()` in `server/cli.go` ignored the error from `ReadString`. At end of
+   input — Ctrl-D, or a scripted run — it returned an empty string forever and
+   the menu loop spun printing "Unknown option." without end. It now exits
+   cleanly.
+. On the sell path, `trade.go` checked `err == sql.ErrNoRows || held < qty`
+   before checking for other errors, so any scan failure was reported to the user
+   as "Insufficient holding" regardless of the real cause. The error check now
+   comes first.
+. `server/db/db.go` resolved `../.env` and `db/schema_creation.sql` relative to the working
+   directory, so they only worked when the program was started from inside `server/`.
+   Following the documented instructions (build from the repository root and run
+   `./eduberza -init`) failed with `password authentication failed for user "postgres"`,
+   because `.env` was never found and the defaults were used. The two SQL scripts are now
+   compiled into the binary with `go:embed`, and `.env` is searched for in the working directory
+   and every parent. Real environment variables now take precedence over the file.
+. `prompt()` in `server/cli.go` ignored the error from `ReadString`. At end of input (Ctrl-D,
+   or a scripted run) it returned an empty string forever, and the menu loop kept printing
+   "Unknown option." without end. It now exits cleanly.
+. On the sell path, `trade.go` checked `err == sql.ErrNoRows || held < qty` before checking
+   for other errors, so any scan failure was reported as "Insufficient holding". The error
+   check now comes first.
 **Improvements**
+. The holding upsert was a read-modify-write in Go (`SELECT ... FOR UPDATE`,
+   compute the new weighted average in `float64`, then `INSERT` or `UPDATE`). It
+   is now a single `INSERT … ON CONFLICT (user_id, crypto_id) DO UPDATE`, so the
+   average is recomputed by PostgreSQL in `numeric` arithmetic and the statement
+   relies on the unique constraint that the relational design already declared.
+. `TerraER3.11.jar` was removed from the repository and `.gitignore` now
+   excludes `*.jar`, because P4 requires third-party executables to be
+   downloaded rather than committed. `.env` is excluded too and `.env.example`
+   was added in its place.
+. `image.png`, a TradingView screenshot, was deleted — the project has no
+   licence to publish it and P4 requires explicit usage rights.
+**Verification**
+All seven use cases were executed against a live PostgreSQL 16 database. The
+screenshots on the `UseCaseXXXXImplementation` pages are captures of those runs.
+The four failure paths were tested, and the rollback behaviour was checked
+directly in SQL: after a rejected purchase, the affected user has zero rows in
+`orders`, `transactions` and `holdings`.
+> **Student action required:** read the changed files (`server/db/db.go`,
+> `server/cli.go`, `server/trade.go`, `server/db/schema_creation.sql`) before the
+> 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`):
+. The holding upsert was a read-modify-write in Go. It is now a single
+   `INSERT … ON CONFLICT (user_id, crypto_id) DO UPDATE`, so PostgreSQL recomputes the average
+   in `numeric` arithmetic, relying on the unique constraint of the relational design.
+. `TerraER3.11.jar` was removed from the repository and `.gitignore` now excludes `*.jar`,
+   because P4 requires third-party executables to be downloaded, not committed. `.env` is
+   excluded too, and `.env.example` was added in its place.
+. `image.png`, a TradingView screenshot, was deleted, because the project has no licence to
+   publish it.
+**Verification.** All seven use cases were run against PostgreSQL 16, and the four failure
+paths were tested. After a rejected purchase, the affected user had zero rows in `orders`,
+`transactions` and `holdings`.
+---
+### Session 3 — 2026-09-16 (Claude Sonnet 5)
+**Prompt (student, verbatim):**
+> Soo we have a problem here In this scenario we have an edge case where our functionallity doesn't work:
+> Suppose the user owns:
+>
+> 2 BTC
+>
+> and wants to sell:
+>
+> 0.5 BTC at market price
+>
+> A sensible procedure is:
+>
+> 1. User creates an Order
+>
+> Orders gets something like:
+>
+> id    user    market    side    type    quantity    status
+> O1    Alice    BTC/USD    sell    market    0.5    open
+>
+> At this point, no trade has necessarily happened yet.
+>
+> 2. Reserve the crypto
+>
+> This is where your current model has a gap.
+>
+> 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
+>
+> So Alice might go from:
+>
+> quantity = 2.0 BTC
+> reserved_quantity = 0
+>
+> to:
+>
+> quantity = 2.0 BTC
+> reserved_quantity = 0.5 BTC
+>
+> with:
+>
+> available BTC = quantity - reserved_quantity = 1.5 BTC
+>
+> The important point is that you generally don't actually remove the 0.5 BTC from her position yet. You reserve it so she cannot submit another sell order for the same BTC.
+>
+> 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.
+The other prompts of that day were about the P1 diagram (logged on
+[ERModelAIUsage](../P1-ConceptualModel/ERModelAIUsage.md)) and about P5 and P6. None of them
+changed the P4 prototype.
+**Response (AI, summarised)**
+The gap: `trade.go`'s sell path checked `held < qty` directly against `holdings.quantity`.
+Nothing distinguished "owned" from "owned, but already promised to an order". This was safe only
+because the whole sell runs in one transaction with a `SELECT … FOR UPDATE` lock. It would stop
+being safe the moment an order could stay `open` across transactions.
+What changed:
+. `holdings.reserved_quantity numeric(20,4) NOT NULL DEFAULT 0 CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)`
+   was added to `schema_creation.sql`.
+. `v_portfolio` gained `reserved_quantity` and the derived `available_quantity`.
+. The sell path in `trade.go` now locks the holding, computes
+   `available := quantity - reserved_quantity`, rejects the order if `available < qty`,
+   reserves (`reserved_quantity += qty`), then settles (`quantity -= qty; reserved_quantity -= qty`).
+   All of this stays inside the same transaction.
+. Both branches of `PlaceOrder` insert the order as `status='open'` and set
+   `status='executed', executed_at=now()` at the end.
+. `portfolio.go` shows new `Reserved` and `Available` columns.
+. The sell error message became
+   `"Insufficient holding: trying to sell X, available Y (of Z held, W reserved)"`.
+**Test evidence** (PostgreSQL 16, `bp_database`, `localhost:5433`):
 ```
 …
 ```
+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.
+(At that time the sell path still asked for a symbol, so a user with no holding could reach the
+check. Since session 4, such a user is never offered anything to sell.)
+**What I decided:** to keep reserve and settle inside a single transaction. Splitting them into
+two commits, so that an order really sits `open` and reserved in between, is what a real
+limit-order matcher will need. Building it now would add a way for an order to get stuck without
+a way to cancel it.
+---
+### Session 4 — 2026-09-24 (Claude Opus 5.5, 1M context)
+**Prompt (student, verbatim):**
+> Does the P4 documentation, fullfill this:
+>
+> *[pasted: the complete official text "Instructions on Phase P4: First Application Prototype"
+> from the course site — the prototype requirements, the rule that the user must never have to
+> remember identifiers or codes, the PrototypeImplementation / UseCaseXXXXImplementation /
+> BuildInstructions templates, the source-code rules and the AI Use rules with the
+> PrototypeImplementationAIUsage template]*
+>
+> is this correct?
+> Or do we need to change anything?
+**Response (AI, summarised):** an audit of the prototype and of the P4 pages against each
+requirement. The main findings:
+- The prototype asked the user to type a market symbol (`Market symbol (e.g. BTC):`) and, in the
+  watchlist, a crypto symbol. That breaks the rule that every choice must be picked from a list.
+- The use-case implementation pages had one screenshot per use case, not one per step, and did
+  not show the SQL at every step.
+- `PrototypeImplementation.md` was written in Trac markup inside a Markdown file. It did not
+  follow the template, and it said identifiers were never typed, which was not accurate.
+- `BuildInstructions` had wrong details: the `-init` message, the order of the history, an
+  outdated ER diagram path, an unpinned PostgreSQL version described as 16, and a concurrency
+  test with no steps to prepare it.
+- This page listed only one model, still had "Student action required" notes, and had no
+  line-by-line log.
+**Prompt (student, verbatim):**
+> YEs you can start fixing it, and can you make the screenshots yourself?
+**Response (AI, summarised):**
+- **Code** (`market.go`, `trade.go`, `watchlist.go`):
+  - `ListMarkets` now returns the numbered list it prints. `ChooseMarket` asks for `Market #:`
+    and takes the market by its position in that list.
+  - New `ChooseHolding` for the sell path. It lists only the user's holdings that still have a
+    free quantity, with `Held` and `Free to sell`, and asks for `Holding #:`.
+  - Watchlist *Add* lists, numbered, the cryptos not yet on the watchlist. *Remove* lists the ones
+    on it.
+  - A shared `pickNumber` refuses anything outside `1…n` with
+    `Invalid choice, enter a number from 1 to N.`
+  - The SQL that looked a crypto up by symbol (`WHERE upper(c.symbol) = upper($1)`) is gone. The
+    chosen row's id is used directly.
+- **Screenshots:** all of them were retaken from real CLI sessions, driven in a pseudo-terminal
+  against freshly initialised sample data. There is one screenshot per scenario step, including
+  the alternate flows (invalid e-mail, duplicate user, wrong password, invalid amount,
+  insufficient funds, insufficient holding, invalid list choice). This gave 30 screenshots,
+  which replace the old 8.
+- **Documentation:**
+  - Rewrote the seven UseCase000XImplementation pages, with the SQL of each step quoted literally
+    from the code.
+  - Rewrote [PrototypeImplementation](PrototypeImplementation.md) (real Markdown, template
+    order, accurate description of the choices), [BuildInstructions](BuildInstructions.md) (the
+    corrections above, a mini-guide of every menu item, and a smoke test re-run on 2026-09-24)
+    and this page.
+  - Made small matching edits to the P3 use cases UC0004, UC0005 and UC0007, so that the "system
+    lists …, user picks …" steps match the prototype.
+  - Wrote the wiki versions of the P4 pages for the faculty site.
+**What I decided:** to accept the numbered-list change, because it is what the P4 rule asks for.
+I also decided to have the screenshots made from real runs instead of editing the old ones.

docs/P4-Prototype/UseCase0001Implementation.md

-              ra531b45
+              ref1c1c7
 # Use-case 0001 Implementation — Register
+# Use-case 0001 Implementation - Register new account
 **Initiating actor:** Visitor. **Source file:** `server/auth.go`, function `Register`.
+**Initiating actor:** Visitor
+## Scenario (implemented)
+**Other actors:** —
+. **User** chooses option `[1] Register` from the anonymous menu.
+A new person creates an account on EduBerza so that they can later log in as a
+Trader ([UseCase0002](UseCase0002Implementation.md)). The Visitor enters a
+username, an e-mail address, a full name and a password. The system validates the
+input (required fields, an `@` in the e-mail, a password of at least 6 characters),
+refuses a username or e-mail that is already registered, and stores only a SHA-256
+hash of the password, never the password itself. A new account starts with a cash
+balance of 0 USD; money is added later with a deposit
+([UseCase0003](UseCase0003Implementation.md)).
+Original use-case description (P3): [UseCase0001](../P3-UseCaseModel/UseCase0001.md).
+Implementation: [`server/auth.go`](../../server/auth.go), function `Register`
+(the password hash is computed by `hashPassword` in the same file).
+. **System** prompts for username, email, full name and password (`server/auth.go:20-23`).
+## Scenario
+. **User** enters the values.
+. **Visitor** chooses `[1] Register` in the anonymous menu (types `1`).
+. **System** prints `-- Register --` and asks, one prompt after another, for
+   `Username:`, `Email:`, `Full name:` and `Password (min 6 chars):`.
+. **System** validates (empty checks, `@` in email, ≥ 6 chars in password) and then asks the database:
+   The screenshot shows steps 1–2: option `1` is chosen and the first prompt
+   (`Username:`) is waiting for input.
+   ![UC0001 steps 1-2: Visitor chooses Register, system asks for the data](screenshots/uc0001_1_register.png)
+. **Visitor** enters the values: `marko`, `marko@example.com`, `Marko Markovski`,
+   `secret1`.
+. **System** validates the input in Go, without accessing the database:
+   - username, e-mail and password must be non-empty, otherwise it prints
+     `Username, email and password are required.` and the scenario ends;
+   - the e-mail must contain `@` (see alternate flow 3a);
+   - the password must be at least 6 characters long, otherwise it prints
+     `Password must be at least 6 characters.` and the scenario ends.
+. **System** checks whether the username or the e-mail already exists
+   (`$1` = username, `$2` = e-mail):
    ```sql
+   SELECT EXISTS(
+       SELECT 1 FROM users WHERE username = $1 OR email = $2
+   );
+   SELECT EXISTS(SELECT 1 FROM users WHERE username = $1 OR email = $2)
    ```
+. **System** inserts the new row (password hashed in Go, not in SQL, to keep hashing identical between register and login):
+   If the result is `true`, alternate flow 5a applies.
+. **System** creates the account (`$1` = username, `$2` = e-mail, `$3` = full name,
+   `$4` = password hash). The hash is computed in Go by `hashPassword` as the
+   hex-encoded SHA-256 of the password — the same value that P3's
+   `encode(digest($4, 'sha256'), 'hex')` would produce in SQL. Hashing on the Go side
+   keeps it identical to the check done at login (SQL as in the code, only the Go
+   source indentation removed):
    ```sql
    INSERT INTO users (username, email, full_name, password_hash, available_balance)
    VALUES ($1, $2, $3, $4, 0);
+   VALUES ($1, $2, $3, $4, 0)
    ```
+. **System** confirms: `Account created. You can now log in.`
+. **System** prints `Account created. You can now log in.` and returns to the
+   anonymous menu; the Visitor can continue with
+   [UseCase0002](UseCase0002Implementation.md).
+   ![Registering a new account](screenshots/uc0001_register.png)
+   The screenshot shows steps 3–7 of the successful attempt (bottom half): the
+   entered values, the confirmation and the anonymous menu again. The top half is the
+   earlier rejected attempt from alternate flow 3a.
+   ![UC0001 steps 3-7: valid data, account created](screenshots/uc0001_3_7_created.png)
+All statements are run on the `project` schema: the connection sets
+`search_path=project,public` (`server/db/db.go`), so `users` means `project.users`.
+### Alternate flow 3a — invalid e-mail
+In step 3 the Visitor entered `marko.example.com` (no `@`). The check in step 4
+fails, the system prints `Invalid email.` and no SQL statement is executed. In the
+prototype the system then shows the anonymous menu again and the Visitor chooses
+`[1] Register` once more, which returns the scenario to step 2.
+![UC0001 alternate flow 3a: invalid e-mail](screenshots/uc0001_3a_invalid_email.png)
+### Alternate flow 5a — duplicate username or e-mail
+After `marko` has been created, the Visitor tries to register again with username
+`marko`, e-mail `other@example.com`, full name `Marko Two`, password `secret2`. The
+query from step 5 (`$1` = `marko`, `$2` = `other@example.com`) returns `true`
+because the username is taken, so the system prints
+`Username or email already taken.`, does not run the `INSERT`, and the scenario
+ends in the anonymous menu.
+![UC0001 alternate flow 5a: username already taken](screenshots/uc0001_5a_duplicate.png)
 ## How to reproduce
 …
 ./eduberza -init      # optional: reset to a known state
 ./eduberza
+# choose [1] Register, then enter username / email / full name / password
+# [1] Register: marko / marko.example.com / Marko Markovski / secret1  -> Invalid email.
+# [1] Register: marko / marko@example.com / Marko Markovski / secret1  -> Account created.
+# [1] Register: marko / other@example.com / Marko Two / secret2        -> Username or email already taken.
 ```
+The screenshot above is from an actual run: registering `marko` /
+`marko@example.com`, followed by the confirmation line.
+All three screenshots come from one real run of exactly these inputs.

docs/P4-Prototype/UseCase0002Implementation.md

-              ra531b45
+              ref1c1c7
 # Use-case 0002 Implementation — Log in
+# Use-case 0002 Implementation - Log in
 **Initiating actor:** Visitor. **Source file:** `server/auth.go`, function `Login` + `authenticate`.
+**Initiating actor:** Visitor
+## Scenario (implemented)
+**Other actors:** —
+. **User** chooses option `[2] Login`.
+A registered user authenticates with a username and password so that the system
+treats all following actions as actions of that Trader. The system looks the user up
+by username and compares the stored password hash with the SHA-256 hash of the
+entered password. An unknown username and a wrong password give the same answer,
+`Invalid credentials.`, so the system does not reveal which usernames exist. After a
+successful login the user's id and username are kept in the in-process session and
+the authenticated (Trader) menu is shown, from which all other Trader use-cases
+start.
+Original use-case description (P3): [UseCase0002](../P3-UseCaseModel/UseCase0002.md).
+Implementation: [`server/auth.go`](../../server/auth.go), functions `Login` and
+`authenticate` (password hash by `hashPassword`).
+. **System** prompts for username and password.
+## Scenario
+. **User** enters `alice` / `test123`.
+. **Visitor** chooses `[2] Login` in the anonymous menu (types `2`).
+. **System** prints `-- Login --` and asks for `Username:` and then `Password:`.
+. **System** looks up the user and compares hashes:
+   The screenshot shows steps 1–2: option `2` is chosen and the `Username:` prompt
+   is waiting for input.
+   ![UC0002 steps 1-2: Visitor chooses Login, system asks for credentials](screenshots/uc0002_1_login.png)
+. **Visitor** enters the username and the password. (If either is empty, the
+   system prints `Username and password are required.` without accessing the
+   database.)
+. **System** looks up the user (`$1` = entered username):
    ```sql
    SELECT id, password_hash FROM users WHERE username = $1;
+   SELECT id, password_hash FROM users WHERE username = $1
    ```
+   The Go side (`authenticate` in `server/auth.go`) compares the returned `password_hash` against `sha256hex(entered_password)`.
+. If no row is returned (`sql.ErrNoRows` in Go), the **System** responds
+   `Invalid credentials.` and the scenario ends.
+. If a row is returned, the **System** compares the returned `password_hash` with
+   `hashPassword(entered password)` (hex-encoded SHA-256, computed in Go). On a
+   mismatch it responds `Invalid credentials.` and the scenario ends.
+. **System** on success stores `{UserID, Username}` in the in-process `Session` and shows the authenticated menu.
+   The screenshot shows this failure path with an existing user and a wrong
+   password: `alice` / `wrongpass`. The query from step 4 finds alice's row, the
+   hash comparison of step 6 fails, and the system prints `Invalid credentials.` and
+   returns to the anonymous menu. (An unknown username — step 5 — prints exactly the
+   same message.)
+   ![A rejected login followed by a successful one](screenshots/uc0002_login.png)
+   ![UC0002 steps 5-6: wrong password, Invalid credentials](screenshots/uc0002_5_6_invalid.png)
+. On a match, the **System** stores the returned `id` and the username in the
+   session (`s.UserID`, `s.Username`), prints `Login successful.` and displays the
+   authenticated menu headed `--- Logged in as alice ---`.
+   The screenshot shows steps 3–7 of the second, successful attempt with the seed
+   credentials `alice` / `test123` (the first, rejected attempt is still visible at
+   the top of the window).
+   ![UC0002 steps 3-7: correct credentials, authenticated menu](screenshots/uc0002_7_success.png)
+The query runs on the `project` schema (the connection sets
+`search_path=project,public`), so `users` means `project.users`.
+### Alternate flow 4a (P3) — lookup combined with the live balance
+P3 describes an optional variant that checks the password in SQL and returns the
+balances in the same query. The P4 prototype does **not** use it: login always uses
+the query from step 4 with the hash comparison in Go, and the balances are read
+separately when the Trader asks for them (`[1] View balance`, see
+[UseCase0003](UseCase0003Implementation.md)).
 ## Seed credentials
+| Username | Password | Balance |
+|----------|----------|---------|
+| `alice`    | `test123` | 10000.00 USD |
+| `bob`      | `test123` |  5000.00 USD |
+| `charlie`  | `test123` |  2500.00 USD |
+State after `./eduberza -init` (`server/db/data_load.sql`):
+| Username  | Password  | Available balance | Invested balance | Holdings |
+|-----------|-----------|------------------:|-----------------:|----------|
+| `alice`   | `test123` | 8250.00 USD       | 1750.00 USD      | 0.5 ETH  |
+| `bob`     | `test123` | 5000.00 USD       | 0.00 USD         | —        |
+| `charlie` | `test123` | 2500.00 USD       | 0.00 USD         | —        |
+## How to reproduce
+```sh
+./eduberza -init
+./eduberza
+# [2] Login: alice / wrongpass  -> Invalid credentials.
+# [2] Login: alice / test123    -> Login successful.  (authenticated menu)
+```
+The screenshots come from one real run of exactly these inputs.

docs/P4-Prototype/UseCase0003Implementation.md

-              ra531b45
+              ref1c1c7
 # Use-case 0003 Implementation — Deposit
+# Use-case 0003 Implementation - Deposit virtual funds
 **Initiating actor:** Trader. **Source file:** `server/account.go`, function `Deposit`.
+**Initiating actor:** Trader
+## Scenario (implemented)
+**Other actors:** —
+. **User** chooses `[2] Deposit virtual funds`.
+. **System** prompts for an amount in USD.
+. **User** enters `2500`.
+. **System** opens a database transaction and runs:
+A logged-in Trader tops up their virtual cash balance in USD. This is a
+simulation-only operation: no real money changes hands, the amount is simply added to
+the Trader's `available_balance`. Every deposit is also recorded in the ledger
+(`transactions`) so that it appears in the transaction history
+([UseCase0006](UseCase0006Implementation.md)). The operation writes to two tables —
+the user row and the ledger — inside a single database transaction, so either both
+changes are stored or neither is. Non-numeric, zero or negative amounts are rejected
+before the database is touched.
+Original use-case description (P3): [UseCase0003](../P3-UseCaseModel/UseCase0003.md).
+Implementation: [`server/account.go`](../../server/account.go), function `Deposit`;
+the verification uses `ShowBalance` from the same file.
+Precondition: the Trader is logged in ([UseCase0002](UseCase0002Implementation.md));
+in the run below as `alice`, who starts from the seed state (available 8250.00 USD,
+invested 1750.00 USD).
+## Scenario
+. **Trader** chooses `[2] Deposit virtual funds` in the authenticated menu
+   (types `2`).
+. **System** prints `-- Deposit virtual funds --` and asks `Amount (USD):`.
+   The screenshot shows steps 1–2: the login as alice, the authenticated menu, the
+   choice `2` and the amount prompt waiting for input.
+   ![UC0003 steps 1-2: Trader chooses Deposit, system asks for the amount](screenshots/uc0003_1_2_deposit.png)
+. **Trader** enters an amount: `500`.
+. **System** validates the input in Go, without accessing the database: the text
+   must parse as a number (`strconv.ParseFloat`) and be greater than 0 (see
+   alternate flow 4a).
+. **System** opens one database transaction (`db.DB.Begin()`), increments the
+   balance, writes the ledger row and commits. Both statements run in this single
+   transaction; if either fails, the deferred `tx.Rollback()` undoes everything.
+   `BEGIN` and `COMMIT` are issued by Go's `Begin()` / `Commit()`; the two
+   statements are sent exactly as in the code:
    ```sql
    BEGIN;
+     UPDATE users
+        SET available_balance = available_balance + $1,
+            updated_at        = now()
+      WHERE id = $2;
+     INSERT INTO transactions (user_id, type, amount, currency, description)
+     VALUES ($2, 'deposit', $1, 'USD', 'Virtual deposit');
+   UPDATE users
+       SET available_balance = available_balance + $1,
+           updated_at        = now()
+     WHERE id = $2;
+   INSERT INTO transactions (user_id, type, amount, currency, description)
+   VALUES ($1, 'deposit', $2, 'USD', 'Virtual deposit');
    COMMIT;
    ```
+   ![Depositing 2500 USD, then checking the balance](screenshots/uc0003_deposit.png)
+   Parameters: placeholders are numbered per statement. In the `UPDATE`, `$1` is the
+   amount (`500`) and `$2` the user id (`amt, s.UserID`); in the `INSERT` it is the
+   other way round, `$1` is the user id and `$2` the amount (`s.UserID, amt`),
+   matching the column order.
+. **System** confirms `Deposited 500.0000 USD.` and returns to the authenticated
+   menu.
+. **System** confirms: `Deposited 2500.0000 USD.`
+   The screenshot shows steps 3–6: the entered amount `500`, the confirmation and the
+   authenticated menu again.
+   ![UC0003 steps 3-6: amount deposited](screenshots/uc0003_3_6_deposited.png)
+The statements run on the `project` schema (the connection sets
+`search_path=project,public`), so `users` and `transactions` mean `project.users`
+and `project.transactions`.
+### Alternate flow 4a — invalid amount
+If the amount is not a number, or is zero or negative, the system prints
+`Invalid amount.`; no transaction is started and nothing is written. In the run the
+Trader first entered `-50`. In the prototype the system then shows the
+authenticated menu again and the Trader chooses `[2] Deposit virtual funds` once
+more, which returns the scenario to step 2.
+![UC0003 alternate flow 4a: invalid amount](screenshots/uc0003_4a_invalid.png)
 ## Verification
+Right after the deposit, `[1] View balance` runs:
+Right after the deposit the Trader chooses `[1] View balance` (function
+`ShowBalance`), which runs (`$1` = user id):
 ```sql
 SELECT available_balance, invested_balance FROM users WHERE id = $1;
+SELECT available_balance, invested_balance FROM users WHERE id = $1
 ```
+In the screenshot above, alice starts from the seed state (available 8250.00,
+invested 1750.00) and ends at available **10750.00** — increased by exactly the
+.00 deposited, with `invested_balance` untouched.
+It prints `Available: 8750.0000 USD`, `Invested : 1750.0000 USD`,
+`Total    : 10500.0000 USD`. The available balance grew from the seed value 8250.00
+by exactly the 500.00 deposited (the rejected `-50` changed nothing), and
+`invested_balance` is untouched.
+![UC0003 verification: balance after the deposit](screenshots/uc0003_verify_balance.png)
+## How to reproduce
+```sh
+./eduberza -init
+./eduberza
+# [2] Login: alice / test123
+# [2] Deposit virtual funds: -50   -> Invalid amount.
+# [2] Deposit virtual funds: 500   -> Deposited 500.0000 USD.
+# [1] View balance                 -> Available: 8750.0000 USD
+```
+All four screenshots come from one real run of exactly these inputs.

docs/P4-Prototype/UseCase0004Implementation.md

-              ra531b45
+              ref1c1c7
 # Use-case 0004 Implementation — Buy
+# Use-case 0004 Implementation - Place market BUY order
 **Initiating actor:** Trader. **Source file:** `server/trade.go`, function `PlaceOrder(s, "buy")`.
+**Initiating actor:** Trader
+## Scenario (implemented)
+**Other actors:** Market Simulator (indirect — supplies the current price via `market_trades`).
+. **User** chooses `[4] Place market BUY order`.
+. **System** lists markets with their latest price (same SQL as UC0006 — via the `v_latest_prices` view).
+A logged-in Trader buys a crypto asset at the current market price. The Trader never
+types a symbol or an identifier: the system lists the active markets with their last
+price, numbered, and the Trader picks one by its number and then enters only the
+quantity. The system checks that the Trader has enough available cash for
+quantity × price and then, in one database transaction, records the order, moves the
+cash from available to invested, adds the crypto to the Trader's holding (recomputing
+the weighted-average entry price), writes a ledger entry and a market trade, and marks
+the order executed. The operation touches five tables (`orders`, `users`, `holdings`,
+`transactions`, `market_trades`) and either all of it succeeds or all of it is rolled
+back.
+Original use-case description (P3): [UseCase0004](../P3-UseCaseModel/UseCase0004.md).
+Implementation: [`server/trade.go`](../../server/trade.go), function
+`PlaceOrder(s, "buy")` (with `upsertHoldingOnBuy` in the same file), which calls
+`ChooseMarket`, `ListMarkets`, `pickNumber` and `LatestPrice` from
+[`server/market.go`](../../server/market.go).
+. **User** enters `BTC`.
+. **System** resolves the market and fetches the latest price:
+All statements run on the `project` schema: the connection sets
+`search_path=project,public` (`server/db/db.go`), so `orders` means `project.orders`.
+The SQL below is copied from the Go code; only the Go source indentation is removed,
+a `;` is added after each statement of the transaction, and `--` comments say what
+each `$n` placeholder is bound to.
+The run shown is user `alice` on the seed data (available 8250.00 USD, holding
+.5 ETH bought at 3500), buying 0.01 BTC.
+## Scenario
+. **Trader** chooses `[4] Place market BUY order` in the authenticated menu (types `4`).
+. **System** prints `-- Place market buy order --` and lists all active markets,
+   numbered, with their last price (`ListMarkets`, called by `ChooseMarket`):
    ```sql
+   SELECT m.id, c.id, c.symbol, m.quote_currency
+   SELECT m.id, c.id, c.symbol, m.quote_currency,
+          COALESCE(lp.price, 0) AS price
      FROM markets m
      JOIN crypto c ON c.id = m.crypto_id
     WHERE upper(c.symbol) = upper($1) AND m.is_active;
    SELECT price FROM v_latest_prices WHERE market_id = $2;
+     JOIN crypto  c  ON c.id = m.crypto_id
+     LEFT JOIN v_latest_prices lp ON lp.market_id = m.id
+    WHERE m.is_active = true
+    ORDER BY c.symbol
    ```
+. **User** enters quantity `0.01`.
+. **System** executes a single database transaction — *all or nothing*:
+   The rows are printed in this order as `1 ADA`, `2 BTC`, `3 DOGE`, `4 ETH`, `5 SOL`;
+   Go keeps each row's market id and crypto id in memory, so the Trader only ever sees
+   and types the list number. The system then asks `Market #:`.
+   ![UC0004 steps 1-2: Trader chooses BUY, system lists the markets](screenshots/uc0004_1_2_markets.png)
+. **Trader** picks the market by its number in the list: `2` (BTC/USD).
+. **System** takes the market id and crypto id of row 2 from the list (no further
+   lookup by symbol) and reads the latest price of that market (`LatestPrice`;
+   `$1` = the chosen market's id):
+   ```sql
+   SELECT price FROM v_latest_prices WHERE market_id = $1
+   ```
+   It prints `Latest price for BTC/USD = 67140.000000` and asks `Quantity:`.
+   ![UC0004 steps 3-4: Trader picks market #2 (BTC), system shows the price](screenshots/uc0004_3_4_price.png)
+. **Trader** enters the quantity `0.01`.
+. **System** computes in Go notional = quantity × price = 0.01 × 67140 = 671.40 and
+   passes it to SQL as a parameter. It then runs one database transaction; the
+   statements below are in exactly the order `PlaceOrder` executes them for a buy:
    ```sql
    BEGIN;
    -- (a) record the order as 'open' — no trade has happened yet
    INSERT INTO orders
        (user_id, market_id, side, type, status, quantity, price)
    VALUES
        ($1, $2, 'buy', 'market', 'open', $3, $4)
    RETURNING id;
+   -- (a) record the order as 'open' — no trade has happened yet.
+   --     $1 = user id, $2 = market id, $3 = side (the Go variable side = 'buy'),
+   --     $4 = quantity (0.01), $5 = price (67140); the returned id is kept in Go.
+   INSERT INTO orders (user_id, market_id, side, type, status, quantity, price)
+    VALUES ($1, $2, $3, 'market', 'open', $4, $5)
+    RETURNING id;
+   -- (b) lock and check the user balance
+   -- (b) lock the user's row and read the available cash. $1 = user id.
+   --     Go compares it with the notional; if it is smaller -> alternate flow 6a.
    SELECT available_balance FROM users WHERE id = $1 FOR UPDATE;
+   -- (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.
+   -- (c) move the notional from available to invested cash.
+   --     $1 = notional (671.40), $2 = user id.
    UPDATE users
       SET available_balance = available_balance - $notional,
           invested_balance  = invested_balance  + $notional,
           updated_at        = now()
     WHERE id = $1;
+       SET available_balance = available_balance - $1,
+           invested_balance  = invested_balance  + $1,
+           updated_at        = now()
+     WHERE id = $2;
    -- (d) upsert the holding, recomputing the weighted-average entry price
    --     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.
+   -- (d) add the crypto to the holding (upsertHoldingOnBuy), recomputing the
+   --     weighted-average entry price in the database. Every SET expression sees
+   --     the pre-update row, so holdings.quantity is still the old quantity.
+   --     $1 = user id, $2 = crypto id, $3 = quantity (0.01), $4 = price (67140).
    INSERT INTO holdings (user_id, crypto_id, quantity, avg_price, updated_at)
    VALUES ($1, $c, $3, $4, now())
    ON CONFLICT (user_id, crypto_id) DO UPDATE
       SET avg_price  = (holdings.quantity * holdings.avg_price
                          + EXCLUDED.quantity * EXCLUDED.avg_price)
                        / (holdings.quantity + EXCLUDED.quantity),
           quantity   = holdings.quantity + EXCLUDED.quantity,
           updated_at = now();
+    VALUES ($1, $2, $3, $4, now())
+    ON CONFLICT (user_id, crypto_id) DO UPDATE
+       SET avg_price  = (holdings.quantity * holdings.avg_price
+                          + EXCLUDED.quantity * EXCLUDED.avg_price)
+                        / (holdings.quantity + EXCLUDED.quantity),
+           quantity   = holdings.quantity + EXCLUDED.quantity,
+           updated_at = now();
+   -- (e) ledger entry
+   INSERT INTO transactions
+       (user_id, type, amount, currency, related_order, description)
+   VALUES
+       ($1, 'buy', -$notional, 'USD', $orderId, 'Market buy ...');
+   -- (e) ledger entry. $1 = user id, $2 = -notional (-671.40), $3 = order id from (a),
+   --     $4 = description built in Go: 'Market buy 0.0100 BTC @ 67140.000000'.
+   INSERT INTO transactions (user_id, type, amount, currency, related_order, description)
+    VALUES ($1, 'buy', $2, 'USD', $3, $4);
+   -- (f) record the resulting market trade
+   INSERT INTO market_trades
+       (market_id, executed_at, price, quantity, side, source)
+   VALUES
+       ($2, now(), $4, $3, 'buy', 'user');
+   -- (f) record the resulting market trade.
+   --     $1 = market id, $2 = price, $3 = quantity, $4 = side ('buy').
+   INSERT INTO market_trades (market_id, executed_at, price, quantity, side, source)
+    VALUES ($1, now(), $2, $3, $4, 'user');
    -- (g) settle the order itself — it has now actually been filled
    UPDATE orders SET status = 'executed', executed_at = now() WHERE id = $orderId;
+   -- (g) settle the order itself — it has now actually been filled. $1 = order id.
+   UPDATE orders SET status = 'executed', executed_at = now() WHERE id = $1;
    COMMIT;
    ```
+. **System** prints: `Order executed: buy 0.0100 BTC @ 67140.000000 (notional 671.4000 USD)`.
+   A buy never reserves crypto (only a sell does, see
+   [UseCase0005](UseCase0005Implementation.md)), so `holdings.reserved_quantity` is not
+   touched and stays 0.
+   ![Market list, then a filled BUY order](screenshots/uc0004_buy.png)
+. **System** confirms
+   `Order executed: buy 0.0100 BTC @ 67140.000000 (notional 671.4000 USD)` and shows
+   the authenticated menu again.
+## Verified run (from actual prototype execution)
+   The screenshot shows steps 5–7: the entered quantity, the confirmation and the menu.
+Re-run 2026-09-16 against PostgreSQL 16 (`bp_database` on `localhost:5433`) with freshly loaded seed data:
+   ![UC0004 steps 5-7: quantity entered, order executed](screenshots/uc0004_5_7_executed.png)
+- **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.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.
+### Verification — portfolio after the buy
+## Failure path — insufficient funds
+Right after the buy the Trader chooses `[6] View portfolio`
+([UseCase0006](UseCase0006Implementation.md)). It shows the new holding
+`BTC 0.0100` with average buy price and current price 67140.000000 (value 671.4000),
+the unchanged `ETH 0.5000` (average 3500, current 3520, unrealised P/L +10.0000),
+`Cash available : 7578.6000 USD` (= 8250.00 − 671.40), portfolio value 2431.4000 and
+net worth 10010.0000 USD. The `Reserved` column is 0.0000 on both rows — a buy never
+reserves anything.
+If `available_balance < notional`, the `defer tx.Rollback()` in `server/trade.go` reverts every statement above and the user sees:
+![UC0004 verification: portfolio after the buy](screenshots/uc0004_verify_portfolio.png)
+```
+Insufficient funds: need X, have Y
+```
+### Alternate flow 6a — insufficient funds
+User `charlie` (seed data: 2500.00 USD available, no crypto) chooses `[4]`, picks
+market `2` (BTC, 67140.000000) and enters quantity `1`. Go computes the notional
+.00. In the transaction, statement (a) inserts the `open` order and statement (b)
+`SELECT available_balance FROM users WHERE id = $1 FOR UPDATE` returns 2500.00, which
+is less than the notional. `PlaceOrder` prints
+`Insufficient funds: need 67140.0000, have 2500.0000` and returns without running
+(c)–(g); the deferred `tx.Rollback()` undoes statement (a), so no order, no ledger
+entry and no balance change is left behind (after this run charlie has no row in
+`orders` and still 2500.00 USD available). The authenticated menu is shown again.
+![UC0004 alternate flow: insufficient funds](screenshots/uc0004_6a_insufficient.png)
+### Alternate flow 3a — number not in the list
+If in step 3 the Trader enters something that is not a number from 1 to the number of
+listed markets, `pickNumber` prints `Invalid choice, enter a number from 1 to 5.`, no
+further SQL is run and the authenticated menu is shown again (the same check is shown
+in [UseCase0007](UseCase0007Implementation.md), alternate flow 12a). Likewise, a
+quantity that is not a positive number in step 5 prints `Invalid quantity.` before any
+transaction is opened.

docs/P4-Prototype/UseCase0005Implementation.md

-              ra531b45
+              ref1c1c7
+# Use-case 0005 Implementation — Sell
+**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)
+. **User** chooses `[5] Place market SELL order`.
+. **System** lists markets (same as UC0004 step 2).
+. **User** enters market symbol, e.g. `ETH`, then quantity `0.5`.
+. **System** opens a transaction and runs:
+# Use-case 0005 Implementation - Place market SELL order
+**Initiating actor:** Trader
+**Other actors:** Market Simulator (indirect — supplies the current price).
+A logged-in Trader sells part or all of a holding at the current market price. The
+Trader never types a symbol: the system lists only the cryptos the Trader holds and can
+still sell (the quantity not already reserved by an open sell order), numbered, with
+how much is held and how much is free, and the Trader picks one by its number and
+enters the quantity. In one database transaction the system records the order,
+reserves the crypto being sold and settles it, credits the proceeds to the Trader's
+available cash while reducing the invested cash by the cost basis, writes a ledger
+entry and a market trade, and marks the order executed. Cost basis is preserved, so
+the realised P/L can be reconstructed from the ledger.
+Original use-case description (P3): [UseCase0005](../P3-UseCaseModel/UseCase0005.md).
+Implementation: [`server/trade.go`](../../server/trade.go), function
+`PlaceOrder(s, "sell")`, which calls `ChooseHolding`, `pickNumber` and `LatestPrice`
+from [`server/market.go`](../../server/market.go).
+All statements run on the `project` schema (the connection sets
+`search_path=project,public` in `server/db/db.go`). The SQL below is copied from the
+Go code; only the Go source indentation is removed, a `;` is added after each
+statement of the transaction, and `--` comments say what each `$n` placeholder is
+bound to.
+The run shown is user `alice` right after the buy of
+[UseCase0004](UseCase0004Implementation.md): 7578.60 USD available, holdings
+.01 BTC (bought at 67140) and 0.5 ETH (bought at 3500). She sells 0.2 ETH.
+## Reserve, then settle
+The crypto being sold is **reserved** (`holdings.reserved_quantity`) before it is
+removed from the position, and the sell check is against what is truly still free,
+`quantity - reserved_quantity`, not against the raw `quantity`, which would also count
+crypto already promised to another order. Because only market orders are implemented,
+an order settles in the same transaction it is placed in, so reserve and settle are two
+statements inside one commit; they stay logically distinct so that a future
+limit-order matcher, where an order would stay `open` until a *later* transaction fills
+it, needs a second transaction but no schema change.
+## Scenario
+. **Trader** chooses `[5] Place market SELL order` in the authenticated menu (types `5`).
+. **System** prints `-- Place market sell order --` and lists, numbered, only the
+   cryptos the Trader holds with some quantity still free to sell, with the quantity
+   held, the quantity free to sell and the last price (`ChooseHolding`;
+   `$1` = the logged-in user's id):
+   ```sql
+   SELECT m.id, c.id, c.symbol, m.quote_currency,
+          h.quantity, h.quantity - h.reserved_quantity AS free,
+          COALESCE(lp.price, 0) AS price
+     FROM holdings h
+     JOIN crypto  c ON c.id = h.crypto_id
+     JOIN markets m ON m.crypto_id = c.id AND m.is_active = true
+     LEFT JOIN v_latest_prices lp ON lp.market_id = m.id
+    WHERE h.user_id = $1
+      AND h.quantity - h.reserved_quantity > 0
+    ORDER BY c.symbol
+   ```
+   For alice it prints `1 BTC USD 0.0100 0.0100 67140.000000` and
+   `2 ETH USD 0.5000 0.5000 3520.000000`, then asks `Holding #:`. Go keeps each row's
+   market id and crypto id in memory; the Trader only types the list number. (If the
+   query returns no row, the system prints `you hold no crypto that is free to sell`
+   and the use-case ends.)
+   ![UC0005 steps 1-2: Trader chooses SELL, system lists what they hold](screenshots/uc0005_1_2_holdings.png)
+. **Trader** picks the holding by its number in the list: `2` (ETH).
+. **System** takes the market id and crypto id of row 2 from the list and reads the
+   latest price of that market (`LatestPrice`; `$1` = the chosen market's id):
+   ```sql
+   SELECT price FROM v_latest_prices WHERE market_id = $1
+   ```
+   It prints `Latest price for ETH/USD = 3520.000000` and asks `Quantity:`.
+   ![UC0005 steps 3-4: Trader picks holding #2 (ETH), system shows the price](screenshots/uc0005_3_4_price.png)
+. **Trader** enters the quantity `0.2`.
+. **System** computes in Go notional = quantity × price = 0.2 × 3520 = 704.00 and
+   runs one database transaction; the statements are in exactly the order
+   `PlaceOrder` executes them for a sell. After statement (b) Go also computes the
+   cost basis = avg_price × quantity = 3500 × 0.2 = 700.00 from the locked holding row;
+   both values are passed to SQL as parameters.
    ```sql
    BEGIN;
+   -- (a) record the order as 'open' — no trade has happened yet
+   INSERT INTO orders
+       (user_id, market_id, side, type, status, quantity, price)
+   VALUES
+       ($1, $2, 'sell', 'market', 'open', $3, $4)
+   RETURNING id;
+   -- (b) lock the holding and check what is actually free to sell
+   -- (a) record the order as 'open' — no trade has happened yet.
+   --     $1 = user id, $2 = market id, $3 = side (the Go variable side = 'sell'),
+   --     $4 = quantity (0.2), $5 = price (3520); the returned id is kept in Go.
+   INSERT INTO orders (user_id, market_id, side, type, status, quantity, price)
+    VALUES ($1, $2, $3, 'market', 'open', $4, $5)
+    RETURNING id;
+   -- (b) lock the holding row and read what is held, what is already reserved and
+   --     the average entry price. $1 = user id, $2 = crypto id.
+   --     Go computes available = quantity - reserved_quantity (0.5 - 0 = 0.5);
+   --     if there is no row or available < quantity -> alternate flow 5a.
    SELECT quantity, reserved_quantity, avg_price FROM holdings
+    WHERE user_id = $1 AND crypto_id = $c FOR UPDATE;
+   -- available := quantity - reserved_quantity
+   -- abort if missing or available < $qty
+   -- (c) reserve: committed to this order, not yet removed from the position
+     WHERE user_id = $1 AND crypto_id = $2 FOR UPDATE;
+   -- (c) reserve: committed to this order, not yet removed from the position.
+   --     $1 = quantity (0.2), $2 = user id, $3 = crypto id.
    UPDATE holdings
+      SET reserved_quantity = reserved_quantity + $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
+       SET reserved_quantity = reserved_quantity + $1,
+           updated_at        = now()
+     WHERE user_id = $2 AND crypto_id = $3;
+   -- (d) settle: a market order fills immediately, so release the reservation and
+   --     remove the asset from the position in one step. Same parameters as (c).
    UPDATE holdings
+      SET quantity = quantity - $qty,
+          reserved_quantity = reserved_quantity - $qty,
+          updated_at = now()
+    WHERE user_id = $1 AND crypto_id = $c;
+       SET quantity          = quantity - $1,
+           reserved_quantity = reserved_quantity - $1,
+           updated_at        = now()
+     WHERE user_id = $2 AND crypto_id = $3;
+   -- (e) credit the proceeds; reduce invested cash by the cost basis.
+   --     $1 = notional (704.00), $2 = cost basis (700.00), $3 = user id.
    UPDATE users
       SET available_balance = available_balance + $notional,
           invested_balance  = GREATEST(invested_balance - ($avg * $qty), 0),
           updated_at        = now()
     WHERE id = $1;
    INSERT INTO transactions
        (user_id, type, amount, currency, related_order, description)
    VALUES
        ($1, 'sell', $notional, 'USD', $orderId, 'Market sell ...');
    INSERT INTO market_trades
        (market_id, executed_at, price, quantity, side, source)
    VALUES
        ($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;
+       SET available_balance = available_balance + $1,
+           invested_balance  = GREATEST(invested_balance - $2, 0),
+           updated_at        = now()
+     WHERE id = $3;
+   -- (f) ledger entry. $1 = user id, $2 = notional (704.00), $3 = order id from (a),
+   --     $4 = description built in Go: 'Market sell 0.2000 ETH @ 3520.000000'.
+   INSERT INTO transactions (user_id, type, amount, currency, related_order, description)
+    VALUES ($1, 'sell', $2, 'USD', $3, $4);
+   -- (g) record the resulting market trade.
+   --     $1 = market id, $2 = price, $3 = quantity, $4 = side ('sell').
+   INSERT INTO market_trades (market_id, executed_at, price, quantity, side, source)
+    VALUES ($1, now(), $2, $3, $4, 'user');
+   -- (h) settle the order itself — it has now actually been filled. $1 = order id.
+   UPDATE orders SET status = 'executed', executed_at = now() WHERE id = $1;
    COMMIT;
    ```
+   ![Selling 0.5 ETH at the current market price](screenshots/uc0005_sell.png)
+. **System** prints: `Order executed: sell 0.5000 ETH @ 3520.000000 (notional 1760.0000 USD)`.
+## Failure path — insufficient holding
+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:
+. **System** confirms
+   `Order executed: sell 0.2000 ETH @ 3520.000000 (notional 704.0000 USD)` and shows
+   the authenticated menu again.
+   The screenshot shows steps 5–7: the entered quantity, the confirmation and the menu.
+   ![UC0005 steps 5-7: quantity entered, order executed](screenshots/uc0005_5_7_executed.png)
+After this run the database holds for alice: ETH `quantity` 0.3000 with
+`reserved_quantity` 0.0000; `available_balance` 8282.60 (= 7578.60 + 704.00) and
+`invested_balance` 1721.40 (= 2421.40 − 700.00); a `sell` row in `transactions` with
+amount 704.0000 and description `Market sell 0.2000 ETH @ 3520.000000`; and the order
+with status `executed`. The realised P/L of this sell is notional − cost basis =
+.00 − 700.00 = +4.00 USD.
+### Alternate flow 5a — insufficient holding
+Right after the sell above, alice chooses `[5]` again. The list from step 2 now shows
+`2 ETH USD 0.3000 0.3000 3520.000000`. She picks `2` (ETH) and enters quantity `5`.
+In the transaction, statement (a) inserts the `open` order and statement (b) returns
+quantity 0.3000 and reserved_quantity 0.0000, so available = 0.3 < 5. `PlaceOrder`
+prints
+```
+Insufficient holding: trying to sell 5.0000, available 0.3000 (of 0.3000 held, 0.0000 reserved)
+```
+and returns without running (c)–(h); the deferred `tx.Rollback()` undoes statement (a)
+as well, so no order, no reservation and no ledger entry is left behind. The
+authenticated menu is shown again. The same message is printed if the holding row no
+longer exists (for example because it was sold out from another session after the
+list was shown).
+![UC0005 alternate flow 5a: selling more than is held](screenshots/uc0005_5a_insufficient.png)
+## Reserve and settle, step by step
+The CLI reserves and settles inside one transaction, so `reserved_quantity` is never
+nonzero *outside* a transaction. The intermediate state is shown by running statements
+(c) and (d) by hand in one `psql` transaction (which sees its own uncommitted
+writes) against alice's ETH holding after the scenario above (0.3 ETH), for a sell of
+.1, and rolling back at the end so nothing is changed. Literal values replace the
+`$n` parameters; `:alice` and `:eth` are psql variables for
+`(SELECT id FROM users WHERE username = 'alice')` and
+`(SELECT id FROM crypto WHERE symbol = 'ETH')`:
 ```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>';
+  FROM holdings WHERE user_id = :alice AND crypto_id = :eth;
 --  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>';
+-- ----------+-------------------+-----------
+--    0.3000 |            0.0000 |    0.3000
+-- (c) reserve 0.1: the order is placed, no trade has happened yet
+UPDATE holdings SET reserved_quantity = reserved_quantity + 0.1, updated_at = now()
+ WHERE user_id = :alice AND crypto_id = :eth;
 SELECT quantity, reserved_quantity, quantity - reserved_quantity AS available
   FROM holdings WHERE user_id = '<alice>' AND crypto_id = '<btc>';
+  FROM holdings WHERE user_id = :alice AND crypto_id = :eth;
 --  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>';
+-- ----------+-------------------+-----------
+--    0.3000 |            0.1000 |    0.2000
+-- (d) settle: the reservation is released and the asset removed
+UPDATE holdings SET quantity = quantity - 0.1, reserved_quantity = reserved_quantity - 0.1, updated_at = now()
+ WHERE user_id = :alice AND crypto_id = :eth;
 SELECT quantity, reserved_quantity, quantity - reserved_quantity AS available
   FROM holdings WHERE user_id = '<alice>' AND crypto_id = '<btc>';
+  FROM holdings WHERE user_id = :alice AND crypto_id = :eth;
 --  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>';
+-- ----------+-------------------+-----------
+--    0.2000 |            0.0000 |    0.2000
+ROLLBACK;
+```
+The middle state is what every other connection would see for as long as an order
+stayed `open` once limit orders exist: 0.1 ETH still owned but no longer free to sell.
+## Two concurrent sells
+A Trader must not be able to sell the same units twice from two sessions at once. Both
+sessions may have listed the holding as free (step 2 runs outside the transaction), so
+the protection is statement (b): `SELECT ... FOR UPDATE` locks the holding row, and a
+second transaction that reaches (b) waits until the first one commits, then reads the
+already reduced `quantity` before deciding.
+This was checked with two `psql` sessions running statements (b)–(d) against alice's
+.3 ETH. Session A locked the row, reserved and settled 0.2 ETH and committed after a
+-second pause; session B asked for the lock one second after A had taken it:
+```
+A: SELECT ... FOR UPDATE  ->  quantity 0.3000, reserved_quantity 0.0000
+A: reserve 0.2, settle 0.2, pg_sleep(3)
+B: 11:43:54  SELECT ... FOR UPDATE   -- blocks, A holds the row lock
+A: 11:43:56  COMMIT
+B: 11:43:56  lock granted  ->  quantity 0.1000, reserved_quantity 0.0000
+```
+Session B was blocked for the two seconds until A committed and then saw only
+.1 ETH, so a second 0.2 ETH sell in B takes alternate flow 5a
+(`available 0.1000`) instead of selling units that no longer exist. (B was rolled
+back and alice's holding was restored to 0.3 ETH after the check.)
+## The constraint holds even if the application code did not
+`schema_creation.sql` declares
+`CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` on
+`holdings.reserved_quantity`, so an inconsistent reservation is impossible at the
+database level, independently of `trade.go` (run inside a transaction that was
+rolled back):
+```
+UPDATE holdings SET reserved_quantity = quantity + 1 WHERE user_id = :alice AND crypto_id = :eth;
 ERROR:  new row for relation "holdings" violates check constraint "holdings_check"
 ```
-`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

-              ra531b45
+              ref1c1c7
 # Use-case 0006 Implementation — Portfolio & transactions
+# Use-case 0006 Implementation - View portfolio and transaction history
 **Initiating actor:** Trader. **Source files:** `server/portfolio.go` (`ShowPortfolio`), `server/account.go` (`ShowTransactions`, `ShowBalance`).
+**Initiating actor:** Trader
+## Scenario (implemented)
+**Other actors:** —
+A logged-in Trader inspects the current state of their account. The portfolio view
+lists every cryptocurrency the Trader holds with the quantity (also split into the
+part reserved by open sell orders and the part that is free to sell), the average
+buy price, the current market price, the market value and the unrealised
+profit/loss, followed by a totals row and a cash summary (cash available, portfolio
+value, net worth). The transaction history lists the Trader's last 20 ledger
+entries — deposits, buys and sells — newest first. Both are read-only: nothing in
+the database is changed.
+Original use-case description (P3): [UseCase0006](../P3-UseCaseModel/UseCase0006.md).
+Implementation: [`server/portfolio.go`](../../server/portfolio.go), function
+`ShowPortfolio`, and [`server/account.go`](../../server/account.go), function
+`ShowTransactions`.
+Precondition: the Trader is logged in ([UseCase0002](UseCase0002Implementation.md)).
+The run below is alice's, after she bought 0.01 BTC
+([UseCase0004](UseCase0004Implementation.md)) and sold 0.2 ETH
+([UseCase0005](UseCase0005Implementation.md)) on top of the seed data (0.5 ETH,
+.00 USD cash).
+## Scenario
 ### Portfolio
+. **User** chooses `[6] View portfolio`.
+. **System** runs:
+. **Trader** chooses `[6] View portfolio` in the authenticated menu (types `6`).
+   The menu is the one shown in [UseCase0002](UseCase0002Implementation.md), step 7.
+. **System** queries the `v_portfolio` view (`$1` = user id):
    ```sql
+   SELECT symbol, quantity,
+          COALESCE(reserved_quantity,  0),
+   SELECT symbol,
+          quantity,
+          COALESCE(reserved_quantity, 0),
           COALESCE(available_quantity, quantity),
           COALESCE(avg_price,      0),
           COALESCE(current_price,  0),
           COALESCE(market_value,   0),
+          COALESCE(avg_price, 0),
+          COALESCE(current_price, 0),
+          COALESCE(market_value, 0),
           COALESCE(unrealized_pnl, 0)
      FROM v_portfolio
     WHERE user_id = $1 AND quantity > 0
     ORDER BY symbol;
+    ORDER BY symbol
    ```
+. **System** renders a table with a totals row, then prints the cash summary:
+. **System** displays the rows and a `TOTAL` row (sums of the value and P/L columns,
+   computed in Go), then reads the cash balance for the summary (`$1` = user id):
    ```sql
    SELECT available_balance, invested_balance FROM users WHERE id = $1;
+   SELECT available_balance, invested_balance FROM users WHERE id = $1
    ```
+   ![screenshot: portfolio view](screenshots/uc0006_portfolio.png)
+   and prints `Cash available` (= `available_balance`), `Portfolio value`
+   (= the total market value) and `Net worth` (= their sum).
+### Verified run
+   The screenshot shows the result of steps 2–3. The table is wider than the
+   terminal window, so each long line wraps and the header row has scrolled out of
+   the top of the window; the complete output of this run is reproduced below it.
+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`):
+   ![UC0006 portfolio: holdings, P/L and cash summary](screenshots/uc0006_portfolio.png)
+```
+  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
+   ```
+     Symbol        Quantity      Reserved     Available         Avg buy         Current           Value  Unrealised P/L
+     ------------------------------------------------------------------------------------------------------------------
+     BTC             0.0100        0.0000        0.0100    67140.000000    67140.000000        671.4000         +0.0000
+     ETH             0.3000        0.0000        0.3000     3500.000000     3520.000000       1056.0000         +6.0000
+     ------------------------------------------------------------------------------------------------------------------
+     TOTAL                                                                                    1727.4000         +6.0000
   Cash available : 8250.0000 USD
   Portfolio value: 1760.0000 USD
   Net worth      : 10010.0000 USD
 ```
+     Cash available : 8282.6000 USD
+     Portfolio value: 1727.4000 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.
+   Checking the figures: ETH is 0.5 − 0.2 = 0.3 at an average buy price of 3500 and
+   a current price of 3520, so value 1056.00 and P/L 0.3 × 20 = +6.00; BTC was just
+   bought at the current price 67140, so its P/L is 0. Cash is
+.00 − 671.40 (buy) + 704.00 (sell) = 8282.60. `Reserved` is 0.0000 for both
+   because the market orders executed immediately — a quantity is reserved only
+   while a sell order is still open.
 ### Transaction history
+. **User** chooses `[7] View transaction history`.
+. **System** runs:
+. **Trader** chooses `[7] View transaction history` in the authenticated menu
+   (types `7`).
+. **System** queries the last 20 ledger entries of the Trader (`$1` = user id) and
+   prints them, newest first (the time is shown as the first 19 characters of
+   `created_at`):
    ```sql
 …
     WHERE user_id = $1
     ORDER BY created_at DESC
     LIMIT 20;
+    LIMIT 20
    ```
+   ![screenshot: transaction history](screenshots/uc0006_history.png)
+   The screenshot shows steps 1–2: the choice `7` and the four ledger rows of this
+   run — the sell of 0.2 ETH (+704.0000 USD), the buy of 0.01 BTC (−671.4000 USD),
+   and the two seed rows, the initial deposit of 10000.0000 USD and the seed buy of
+.5 ETH (−1750.0000 USD). Buys are stored with a negative amount, deposits and
+   sells with a positive one. The two seed rows were inserted by `data_load.sql` in
+   one statement and have the same `created_at`, so their relative order is not
+   determined by the `ORDER BY`.
+   ![UC0006 transaction history: last ledger entries](screenshots/uc0006_history.png)
+All statements run on the `project` schema (the connection sets
+`search_path=project,public`).
+### Reference — how `v_portfolio` is defined
+From `server/db/schema_creation.sql`:
+```sql
+CREATE OR REPLACE VIEW project.v_portfolio AS
+SELECT h.user_id,
+       c.symbol,
+       h.quantity,
+       h.reserved_quantity,
+       (h.quantity - h.reserved_quantity) AS available_quantity,
+       h.avg_price,
+       lp.price                           AS current_price,
+       (h.quantity * lp.price)            AS market_value,
+       (h.quantity * (lp.price - h.avg_price)) AS unrealized_pnl
+FROM   project.holdings h
+JOIN   project.crypto   c ON c.id = h.crypto_id
+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;
+```
+## How to reproduce
+```sh
+./eduberza -init
+./eduberza
+# [2] Login: alice / test123
+# [4] buy 0.01 BTC, [5] sell 0.2 ETH   (UseCase0004 / UseCase0005)
+# [6] View portfolio
+# [7] View transaction history
+```
+Both screenshots come from one real run (portfolio and history taken right after
+the buy and sell runs).

docs/P4-Prototype/UseCase0007Implementation.md

-              ra531b45
+              ref1c1c7
 # Use-case 0007 Implementation — Watchlist
+# Use-case 0007 Implementation - Manage watchlist
 **Initiating actor:** Trader. **Source file:** `server/watchlist.go`.
+**Initiating actor:** Trader
+## Scenario (implemented)
+**Other actors:** —
+. **User** chooses `[8] Manage watchlist`.
+. **System** ensures a default watchlist exists:
+A logged-in Trader keeps a list of crypto assets they want to monitor, with the last
+price of each. The first time the watchlist is opened the system creates a default
+watchlist named "Favorites" for the Trader. From a sub-menu the Trader can list the
+watchlist, add a crypto or remove one. The Trader never types a symbol: for adding, the
+system lists, numbered, only the cryptos that are not on the watchlist yet, and for
+removing, only the cryptos that are on it; the Trader picks one by its number. Adding
+a crypto that is already on the list is a no-op (idempotent), and a number that is not
+in the list is refused without touching the database.
+Original use-case description (P3): [UseCase0007](../P3-UseCaseModel/UseCase0007.md).
+Implementation: [`server/watchlist.go`](../../server/watchlist.go), functions
+`ManageWatchlist`, `ensureDefaultWatchlist`, `listWatchlist`, `addToWatchlist` and
+`removeFromWatchlist`, with `pickNumber` from
+[`server/market.go`](../../server/market.go).
+All statements run on the `project` schema (the connection sets
+`search_path=project,public` in `server/db/db.go`). The SQL below is copied from the
+Go code; only the Go source indentation is removed.
+The run shown is user `alice` on the seed data, whose watchlist contains BTC, ETH and
+SOL. She lists it, adds ADA, tries to remove a number that is not in the list, removes
+SOL and lists the result.
+## Scenario
+. **Trader** chooses `[8] Manage watchlist` in the authenticated menu (types `8`).
+. **System** makes sure the Trader has a watchlist and takes the id of the oldest one
+   (`ensureDefaultWatchlist`; `$1` = the logged-in user's id):
    ```sql
+   SELECT id FROM watchlists WHERE user_id = $1 ORDER BY created_at LIMIT 1;
+   -- else
+   INSERT INTO watchlists (user_id, name) VALUES ($1, 'Favorites') RETURNING id;
+   SELECT id FROM watchlists WHERE user_id = $1 ORDER BY created_at LIMIT 1
    ```
+. **System** offers the submenu: List, Add, Remove, Back.
+   Only if this returns no row, it creates the default watchlist and uses its id:
+### List
+   ```sql
+   INSERT INTO watchlists (user_id, name) VALUES ($1, 'Favorites') RETURNING id
+   ```
+```sql
+SELECT c.symbol, c.name, COALESCE(lp.price, 0)
+  FROM watchlist_items wi
+  JOIN crypto c ON c.id = wi.crypto_id
+  LEFT JOIN markets m ON m.crypto_id = c.id AND m.quote_currency = 'USD'
+  LEFT JOIN v_latest_prices lp ON lp.market_id = m.id
+ WHERE wi.watchlist_id = $1
+ ORDER BY c.symbol;
+```
+   (alice already has the seed watchlist "Favorites", so only the `SELECT` runs.) The
+   watchlist id is kept in Go and used as `$1` in all statements below.
+. **System** shows the sub-menu `-- Watchlist --` with `[1] List items`,
+   `[2] Add crypto`, `[3] Remove crypto` and `[0] Back`.
 ![Adding DOGE to the watchlist, then listing it](screenshots/uc0007_watchlist.png)
+   ![UC0007 steps 1-3: Trader opens the watchlist, system shows the sub-menu](screenshots/uc0007_1_3_menu.png)
 ### Add
+### List items
+```sql
+SELECT id FROM crypto WHERE upper(symbol) = upper($1);
+. **Trader** chooses `[1] List items`.
+. **System** lists the cryptos on the watchlist with their last price against USD
+   (`listWatchlist`; `$1` = watchlist id):
+INSERT INTO watchlist_items (watchlist_id, crypto_id)
+VALUES ($watchlist_id, $crypto_id)
+ON CONFLICT (watchlist_id, crypto_id) DO NOTHING;
+```
+   ```sql
+   SELECT c.symbol, c.name, COALESCE(lp.price, 0)
+     FROM watchlist_items wi
+     JOIN crypto  c  ON c.id = wi.crypto_id
+     LEFT JOIN markets       m  ON m.crypto_id = c.id AND m.quote_currency = 'USD'
+     LEFT JOIN v_latest_prices lp ON lp.market_id = m.id
+    WHERE wi.watchlist_id = $1
+    ORDER BY c.symbol
+   ```
+Re-adding the same symbol is a no-op thanks to the unique constraint + `ON CONFLICT`.
+   For alice it prints `BTC Bitcoin 67140.000000`, `ETH Ethereum 3520.000000` and
+   `SOL Solana 166.100000` (an empty watchlist prints `(watchlist is empty)`), then
+   shows the sub-menu again.
+### Remove
+   ![UC0007 List items](screenshots/uc0007_list.png)
+```sql
+DELETE FROM watchlist_items
+ WHERE watchlist_id = $1
+   AND crypto_id = (SELECT id FROM crypto WHERE upper(symbol) = upper($2));
+```
+### Add a crypto
+. **Trader** chooses `[2] Add crypto`.
+. **System** lists, numbered, the cryptos that are not on the watchlist yet
+   (`addToWatchlist`; `$1` = watchlist id):
+   ```sql
+   SELECT c.id, c.symbol, c.name
+     FROM crypto c
+    WHERE NOT EXISTS (SELECT 1 FROM watchlist_items wi
+                       WHERE wi.watchlist_id = $1 AND wi.crypto_id = c.id)
+    ORDER BY c.symbol
+   ```
+   For alice it prints `1 ADA Cardano` and `2 DOGE Dogecoin` and asks
+   `Crypto # to add:`. Go keeps each row's crypto id in memory. (If every crypto is
+   already on the watchlist, it prints `Every crypto is already on your watchlist.`
+   instead.)
+   ![UC0007 Add: system lists the cryptos not yet on the watchlist](screenshots/uc0007_add_1_list.png)
+. **Trader** picks the crypto by its number in the list: `1` (ADA).
+. **System** adds the crypto of row 1 to the watchlist (`$1` = watchlist id,
+   `$2` = the chosen crypto's id); thanks to the unique constraint and
+   `ON CONFLICT ... DO NOTHING`, adding a crypto that is already there changes
+   nothing:
+   ```sql
+   INSERT INTO watchlist_items (watchlist_id, crypto_id)
+    VALUES ($1, $2)
+    ON CONFLICT (watchlist_id, crypto_id) DO NOTHING
+   ```
+   It prints `Added ADA.` and shows the sub-menu again.
+   ![UC0007 Add: Trader picks #1 (ADA), system adds it](screenshots/uc0007_add_2_added.png)
+### Remove a crypto
+. **Trader** chooses `[3] Remove crypto`.
+. **System** lists, numbered, the cryptos that are on the watchlist
+    (`removeFromWatchlist`; `$1` = watchlist id):
+    ```sql
+    SELECT c.id, c.symbol, c.name
+      FROM watchlist_items wi
+      JOIN crypto c ON c.id = wi.crypto_id
+     WHERE wi.watchlist_id = $1
+     ORDER BY c.symbol
+    ```
+    For alice it now prints `1 ADA Cardano`, `2 BTC Bitcoin`, `3 ETH Ethereum` and
+    `4 SOL Solana` and asks `Crypto # to remove:`. Go keeps each row's crypto id in
+    memory. (If the watchlist is empty, it prints `Your watchlist is empty.` instead.)
+    ![UC0007 Remove: system lists the watchlist's cryptos](screenshots/uc0007_remove_1_list.png)
+. **Trader** picks the crypto by its number in the list: `4` (SOL).
+. **System** removes the crypto of row 4 from the watchlist (`$1` = watchlist id,
+    `$2` = the chosen crypto's id):
+    ```sql
+    DELETE FROM watchlist_items WHERE watchlist_id = $1 AND crypto_id = $2
+    ```
+    It prints `Removed SOL.` and shows the sub-menu again.
+    ![UC0007 Remove: Trader picks #4 (SOL), system removes it](screenshots/uc0007_remove_2_removed.png)
+#### Alternate flow 12a — number not in the list
+Before removing SOL, alice first chose `[3] Remove crypto` and, at step 12, entered `5`
+while only numbers 1–4 were listed. `pickNumber` prints
+`Invalid choice, enter a number from 1 to 4.`, the `DELETE` is not run and the sub-menu
+is shown again; she then chose `[3]` once more, which returned the scenario to step 11.
+The same check applies to the number entered at step 8.
+![UC0007 Remove: a number that is not in the list is refused](screenshots/uc0007_remove_invalid.png)
+### Verification — list after the changes
+Choosing `[1] List items` again runs the query from step 5, which now returns
+`ADA Cardano 0.453750`, `BTC Bitcoin 67140.000000` and `ETH Ethereum 3520.000000`:
+ADA was added and SOL removed. `[0] Back` returns to the authenticated menu.
+![UC0007 List items after the changes](screenshots/uc0007_list_after.png)

docs/P5-Normalization/Normalization.md

-              ra531b45
+              ref1c1c7
 again: nothing was lost.
+**Lossless join.** For every pair (referencing relation, referenced relation) connected by a
+foreign key — `R_MARKETS.M_CRYPTO_ID → R_CRYPTO.C_ID`, `R_HOLDINGS.H_USER_ID → R_USERS.U_ID` /
+`R_HOLDINGS.H_CRYPTO_ID → R_CRYPTO.C_ID`, `R_ORDERS.O_USER_ID → R_USERS.U_ID` /
+`R_ORDERS.O_MARKET_ID → R_MARKETS.M_ID`, `R_TRANSACTIONS.T_USER_ID → R_USERS.U_ID` /
+`R_TRANSACTIONS.T_RELATED_ORDER → R_ORDERS.O_ID`, `R_MARKET_TRADES.MT_MARKET_ID →
+R_MARKETS.M_ID`, `R_MARKET_CANDLES.MC_MARKET_ID → R_MARKETS.M_ID`,
+`R_WATCHLISTS.W_USER_ID → R_USERS.U_ID`, `R_WATCHLIST_ITEMS.WI_WATCHLIST_ID →
+R_WATCHLISTS.W_ID` / `R_WATCHLIST_ITEMS.WI_CRYPTO_ID → R_CRYPTO.C_ID` — the join attribute on
+the "one" side is that relation's own primary key (`U_ID`, `C_ID`, `M_ID`, `O_ID`, `W_ID`).
+A join on a foreign key equated to the primary key it references is the textbook sufficient
+condition for a lossless decomposition (`Ri ∩ Rj` is a key of `Rj`), so re-joining all ten
+relations on their foreign-key/primary-key pairs reconstructs `R_EDUBERZA` exactly, with no
+spurious rows and none missing.
+**Lossless join — chase test.**
+> *Note: the chase algorithm is not part of the course material. I was curious about a
+> stricter way to test lossless join than the usual "the common attributes are a key of one
+> side" argument, so I applied it here.*
+The chase decides whether a decomposition `R = R1 ∪ … ∪ Rn` is lossless under a set of
+functional dependencies. Build a tableau with one column per attribute of `R` and one row per
+relation `Ri`. In row `i`, put a distinguished symbol `a` in every column of `Ri` and a unique
+symbol `b_i` in every other column. Then repeat, until nothing changes: for each FD `X → Y`,
+whenever two rows agree on all of `X`, make them agree on `Y`. If they disagree, an `a` wins,
+otherwise one `b` replaces the other. **The decomposition is lossless exactly when some row
+ends up with `a` in every column.**
+All attributes of one cluster (`U_*`, `C_*`, `M_*`, …) always appear together, and FD1–FD17
+never mix clusters. So each cluster is one column group below: `a` means every column of the
+group holds a distinguished symbol, and `b` means none of them does. The foreign-key
+attributes (`H_USER_ID`, `O_MARKET_ID`, …) belong to their own cluster (`H_*`, `O_*`, …), not
+to the cluster they reference.
+**Step 1 — the ten relations from the table above.**
+```
+              U*  C*  M*  H*  O*  T*  MT* MC* W*  WI*
+R_USERS        a   b   b   b   b   b   b   b   b   b
+R_CRYPTO       b   a   b   b   b   b   b   b   b   b
+R_MARKETS      b   b   a   b   b   b   b   b   b   b
+R_HOLDINGS     b   b   b   a   b   b   b   b   b   b
+R_ORDERS       b   b   b   b   a   b   b   b   b   b
+R_TRANSACTIONS b   b   b   b   b   a   b   b   b   b
+R_MARKET_TR.   b   b   b   b   b   b   a   b   b   b
+R_MARKET_CA.   b   b   b   b   b   b   b   a   b   b
+R_WATCHLISTS   b   b   b   b   b   b   b   b   a   b
+R_WATCHLIST_I. b   b   b   b   b   b   b   b   b   a
+```
+Every FD has its left side inside one cluster, for example `U_ID → U_*` or
+`H_USER_ID, H_CRYPTO_ID → H_ID`. For such an FD to fire, two rows would have to agree on that
+left side. But only one row has `a`s in that cluster, and the `b`s of different rows are
+all different, so no two rows ever agree on any left side. **The chase changes nothing, and
+no row becomes all `a`.** Under FD1–FD17 alone, the ten relations are *not* guaranteed to
+join back to `R_EDUBERZA`. This is not an accident of this model. It is exactly why
+Bernstein's synthesis algorithm has a final step: *if no synthesised relation contains a
+candidate key of `R`, add one that does.* None of the ten contains the ten-attribute key.
+**Step 2 — add the key relation** `R_KEY(U_ID, C_ID, M_ID, H_ID, O_ID, T_ID, MT_ID, MC_ID,
+W_ID, WI_ID)`. Its row has `a` only in the ten ID columns, written `a·` for "`a` in the ID,
+`b` in the rest of the group":
+```
+              U*  C*  M*  H*  O*  T*  MT* MC* W*  WI*
+R_KEY          a·  a·  a·  a·  a·  a·  a·  a·  a·  a·
+(the ten rows of step 1 unchanged)
+```
+Now FD1 `U_ID → U_*` fires: row `R_KEY` and row `R_USERS` both have `a` in `U_ID`, so they
+must agree on the rest of `U_*`, and `R_USERS` has `a` there. `R_KEY` becomes `a` in the whole
+`U*` group. The same happens with FD4 (`C*`), FD6 (`M*`), FD8 (`H*`), FD10 (`O*`), FD11 (`T*`),
+FD12 (`MT*`), FD13 (`MC*`), FD15 (`W*`) and FD16 (`WI*`):
+```
+              U*  C*  M*  H*  O*  T*  MT* MC* W*  WI*
+R_KEY          a   a   a   a   a   a   a   a   a   a     <- all distinguished
+```
+**Row `R_KEY` is all `a`, so the decomposition into the ten relations plus `R_KEY` is
+lossless.**
+**Why `R_KEY` is not kept in the final schema.** An instance of `R_KEY` would only record
+which ID of one cluster appears together with which ID of every other cluster. As shown under
+*Candidate keys and primary key*, the ten clusters are independent record types, and
+`R_EDUBERZA` pairs every row of one with every row of the others. So `R_KEY` would be just the
+cross product of the ten ID sets and would carry no information. The same independence means
+the join dependency `⋈[R_USERS, …, R_WATCHLIST_ITEMS]` holds on `R_EDUBERZA` by construction.
+Under that dependency the ten relations alone already reconstruct it: their natural join, with
+no common attributes, is exactly that cross product. The chase makes this reasoning explicit.
+FDs by themselves cannot prove the join lossless; you need either the key relation or the
+independence of the clusters. That was hidden in the earlier "foreign key equals primary key"
+argument, which described the equi-joins the application runs, not the natural join the
+lossless-join property is about.
 ## 3NF decomposition

docs/README.md

-              ra531b45
+              ref1c1c7
 | `P5-Normalization/`     | P5 | `Normalization`, `NormalizationAIUsage` |
 | `P6-AdvancedReports/`   | P6 | `AdvancedReports`, `AdvancedReportsAIUsage` |
+| `P7-AdvancedDatabaseDevelopment/` | P7 | `AdvancedDatabaseDevelopment`, `AdvancedDatabaseDevelopmentAIUsage` |
 `Instructions.md` is the condensed course rubric — reference material, not a
 …
 | P5 | [Normalization](P5-Normalization/Normalization.md) | Finished, awaiting approval |
 | P6 | [AdvancedReports](P6-AdvancedReports/AdvancedReports.md) | Finished, awaiting approval |
 | P7 | *Advanced Database Development* | Not started |
+| P7 | [AdvancedDatabaseDevelopment](P7-AdvancedDatabaseDevelopment/AdvancedDatabaseDevelopment.md) | Finished, awaiting approval |
 | P8 | *Advanced Application Development* | Not started |
 | P9 | *Other topics (Performance, Security)* | Not started |
 …
 | [`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`) |
+| [`../server/db/advanced_db.sql`](../server/db/advanced_db.sql) | P7 | Triggers, functions, views and the background job (part of `-init`) |
+| [`../server/db/advanced_db_tests.sql`](../server/db/advanced_db_tests.sql) | P7 | Tests for every P7 rule, rolled back at the end |
 ## Use cases (P3)
 …
 - [UseCaseModelAIUsage](P3-UseCaseModel/UseCaseModelAIUsage.md) (P3)
 - [PrototypeImplementationAIUsage](P4-Prototype/PrototypeImplementationAIUsage.md) (P4)
+- [NormalizationAIUsage](P5-Normalization/NormalizationAIUsage.md) (P5)
+- [AdvancedReportsAIUsage](P6-AdvancedReports/AdvancedReportsAIUsage.md) (P6)
+- [AdvancedDatabaseDevelopmentAIUsage](P7-AdvancedDatabaseDevelopment/AdvancedDatabaseDevelopmentAIUsage.md) (P7)
 ## Build & run

Context Navigation

Legend:

docs/P1-ConceptualModel/ERModel.md

docs/P1-ConceptualModel/ERModelAIUsage.md

docs/P2-RelationalDesign/RelationalDesign.md

docs/P3-UseCaseModel/UseCase0004.md

docs/P3-UseCaseModel/UseCase0005.md

docs/P3-UseCaseModel/UseCase0007.md

docs/P4-Prototype/BuildInstructions.md

docs/P4-Prototype/PrototypeImplementation.md

docs/P4-Prototype/PrototypeImplementationAIUsage.md

docs/P4-Prototype/UseCase0001Implementation.md

docs/P4-Prototype/UseCase0002Implementation.md

docs/P4-Prototype/UseCase0003Implementation.md

docs/P4-Prototype/UseCase0004Implementation.md

docs/P4-Prototype/UseCase0005Implementation.md

docs/P4-Prototype/UseCase0006Implementation.md

docs/P4-Prototype/UseCase0007Implementation.md

docs/P5-Normalization/Normalization.md

docs/README.md

Download in other formats: