Index: docs/P1-ConceptualModel/ERModel.md =================================================================== --- docs/P1-ConceptualModel/ERModel.md (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4) +++ docs/P1-ConceptualModel/ERModel.md (revision ef1c1c725137d93fa85292f43440214aaddcdf05) @@ -1,7 +1,7 @@ -# 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 @@ -51,4 +51,5 @@ | `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) | @@ -96,14 +97,15 @@ 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 @@ -115,8 +117,9 @@ | `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 | @@ -160,4 +163,25 @@ | `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 @@ -222,4 +246,18 @@ #### 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 @@ -290,11 +328,21 @@ [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. Index: docs/P1-ConceptualModel/ERModelAIUsage.md =================================================================== --- docs/P1-ConceptualModel/ERModelAIUsage.md (revision 9577c7944ff24ddc9145ff6637494f07d87c3dd4) +++ docs/P1-ConceptualModel/ERModelAIUsage.md (revision ef1c1c725137d93fa85292f43440214aaddcdf05) @@ -283,6 +283,32 @@ 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. Index: docs/P1-ConceptualModel/ERModel_v04.xml =================================================================== --- docs/P1-ConceptualModel/ERModel_v04.xml (revision ef1c1c725137d93fa85292f43440214aaddcdf05) +++ docs/P1-ConceptualModel/ERModel_v04.xml (revision ef1c1c725137d93fa85292f43440214aaddcdf05) @@ -0,0 +1,1 @@ +CryptosMarketsMarketTradesMarketCandlesOrdersTransactionsUsersWatchlistsQuotedOnFillsAggregatesPlacedOnSettlesRecordsPlacesHoldsOwnsContains

3

3

3

3

3

3

3

created_at

name

symbol

truetrueid

created_at

is_active

quote_currency

truetrueid

truetrueid

executed_at

price

quantity

side

source

truetrueid

timeframe

open

high

low

close

volume

candle_time

truetrueid

side

type

status

quantity

price

placed_at

executed_at

truetrueid

type

amount

currency

created_at

description

truetrueid

username

email

full_name

password_hash

available_balance

invested_balance

created_at

updated_at

created_at

name

truetrueid

quantity

5avg_price

created_at

updated_at

added_at

reserved_quantity

reserved_balance

filled_quantity

FillsBuy

FillsSell

LogsOrderEvents

3truetrueid

event_type

quantity

price

status_after

created_at

Index: docs/P1-ConceptualModel/wiki/ERModel.md =================================================================== --- docs/P1-ConceptualModel/wiki/ERModel.md (revision ef1c1c725137d93fa85292f43440214aaddcdf05) +++ docs/P1-ConceptualModel/wiki/ERModel.md (revision ef1c1c725137d93fa85292f43440214aaddcdf05) @@ -0,0 +1,307 @@ += Entity-Relationship Model v.04 = + +== Diagram == + +[[Image(ERModel_v04.png, 800px)]] + +Notation: Chen. Rectangles are entity sets, diamonds are relationships, ellipses +are attributes, underlined ellipses are primary keys, the dashed ellipse is a +derived attribute. A double line between an entity set and a relationship marks +'''total participation''' (every instance of that entity set must participate); a +single line marks partial participation. + +Two deliberate modeling decisions worth stating up front: + + * '''No foreign keys appear in the diagram.''' Connections between entity sets are expressed as relationships, per the notation. Foreign-key columns appear only in the relational model in [wiki:RelationalDesign]. + * '''`Holds` and `Contains` are relationships, not entity sets.''' Both are M:N and both carry their own attributes, which is exactly what a Chen relationship is for. They become tables (`holdings`, `watchlist_items`) only in P2. + +== Data requirements == + +Each entity set is given as a short rationale for why it exists as its own set, +its keys, and its attributes as a table. Each relationship is given as its +cardinality and participation, a short rationale, and — where it carries data — +an attribute table. + +=== Entity sets === + +==== Users ==== +Registered participants of the platform. Every action in the simulation is +attributed to a user, and the two balance attributes are what makes the +simulation work: cash that is free to trade is tracked separately from cash +that is currently committed to open positions, so the platform can refuse a +purchase without having to recompute the whole portfolio first. + +'''Keys:''' candidates `{id}`, `{username}`, `{email}`; primary key '''`id`'''. A +surrogate UUID was chosen because it is opaque and stable — `username` and +`email` are both things a user may legitimately want to change later, and +every relationship in the diagram points at `Users`, so a mutable key would +propagate changes across the whole database. + +||= Attribute =||= Type =||= Constraints =|| +|| `id` || UUID || PK, required || +|| `username` || text(50) || required, unique || +|| `email` || text(255) || required, unique, contains `@` || +|| `full_name` || text(200) || optional || +|| `password_hash` || text(255) || required — never the password itself; the prototype stores a SHA-256 hex digest || +|| `available_balance` || numeric(18,4) || required, default 0, ≥ 0 || +|| `invested_balance` || numeric(18,4) || required, default 0, ≥ 0 || +|| `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) || + +==== Cryptos ==== +The catalog of crypto assets the platform knows about. Kept separate from +`Markets` because an asset exists independently of the pairs it is traded in — +the same asset can be quoted against several currencies, and a user's holding is +in the ''asset'', not in a particular pair. + +'''Keys:''' candidates `{id}`, `{symbol}`; primary key '''`id`''', for the same +reason as in `Users`. `symbol` is kept as a unique natural key because that is +what users type and see. + +||= Attribute =||= Type =||= Constraints =|| +|| `id` || UUID || PK, required || +|| `symbol` || text(20) || required, unique (e.g. `BTC`) || +|| `name` || text(255) || required (e.g. `Bitcoin`) || +|| `created_at` || timestamptz || required, defaults to now || + +==== Markets ==== +A tradeable pair: one crypto asset quoted in one currency, e.g. BTC/USD. This is +where prices live, and it is the thing an order is placed ''on''. Modeled as its +own entity set rather than an attribute of `Cryptos` because a market has its own +lifecycle — it can be deactivated without deleting the asset — and because +trades, candles and orders all reference the pair, not the asset. + +'''Keys:''' candidates `{id}`, `{crypto_id, quote_currency}` — that pair is +unique by definition, since a given asset can only be quoted once per +currency; primary key '''`id`''', so that the many entity sets referencing a +market carry one narrow column instead of a composite key. + +||= Attribute =||= Type =||= Constraints =|| +|| `id` || UUID || PK, required || +|| `quote_currency` || text(3) || required, default `USD` || +|| `is_active` || boolean || required, default true — inactive markets are hidden from the trading menus but keep their history || +|| `created_at` || timestamptz || required, defaults to now || + +==== Orders ==== +A user's instruction to buy or sell on a market. Needed as a separate entity set +because an order is a record of ''intent'' that outlives its execution: it keeps +the requested quantity and price even after it has been filled, which is what +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, 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 +[wiki:UseCase0005] for the reserve-then-settle +sequence and +[wiki:AdvancedDatabaseDevelopment] +for the rules that keep it consistent. + +'''Keys:''' candidate `{id}` only — there is no natural key, since the same user +can place two identical orders on the same market in the same second, and both +are legitimately distinct; primary key '''`id`'''. + +||= Attribute =||= Type =||= Constraints =|| +|| `id` || UUID || PK, required || +|| `side` || text || required, `buy` or `sell` || +|| `type` || text || required, `market` or `limit` (both executed since P7) || +|| `status` || text || required, `open`, `partially_filled`, `executed` or `cancelled` || +|| `quantity` || numeric(20,4) || required, > 0 || +|| `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 || + +==== Transactions ==== +The financial ledger: every movement of virtual cash, in one place. This exists +so that a balance is never just a number someone edited — it is the sum of an +auditable list of entries, which is also what the "explain every step" goal of +the project needs. + +'''Keys:''' candidate `{id}` only; primary key '''`id`'''. + +||= Attribute =||= Type =||= Constraints =|| +|| `id` || UUID || PK, required || +|| `type` || text || required, `deposit`, `buy`, `sell` or `fee` || +|| `amount` || numeric(18,4) || required, signed — negative for money leaving the cash balance, positive for money arriving || +|| `currency` || text(3) || required, default `USD` || +|| `created_at` || timestamptz || required, defaults to now || +|| `description` || text || optional, free-form || + +==== !MarketTrades ==== +Individual executed trades on a market, from the user's own fills and from the +market simulator. This is the single source of truth for the current price: the +price of a market is the price of its most recent trade, never a column someone +writes directly. + +'''Keys:''' candidate `{id}` — `{market_id, executed_at}` looks unique in +principle, but two trades can share a timestamp, so it is not a safe key; +primary key '''`id`''' (a plain auto-incrementing integer here rather than a +UUID, because this is the highest-volume entity set and it is only ever read +in timestamp order, never referenced by anything else). + +||= Attribute =||= Type =||= Constraints =|| +|| `id` || integer || PK, required, auto-generated || +|| `executed_at` || timestamptz || required || +|| `price` || numeric(18,6) || required, > 0 || +|| `quantity` || numeric(20,6) || required, > 0 || +|| `side` || text || optional, `buy` or `sell` || +|| `source` || text(50) || required, default `simulation` — distinguishes a simulated trade from a user's own fill (`user`) || + +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 +from. Stored rather than computed on the fly because the point of the project is +a chart-driven interface, and re-aggregating the whole trade history for every +screen refresh does not scale. + +'''Keys:''' candidates `{id}`, `{market_id, timeframe, candle_time}` — a market +has exactly one candle per timeframe per time bucket; primary key '''`id`''', the +composite is enforced as a uniqueness rule because it is the real-world +constraint and it is what prevents duplicate candles. + +||= Attribute =||= Type =||= Constraints =|| +|| `id` || integer || PK, required, auto-generated || +|| `timeframe` || text || required, `1m`, `5m`, `1h` or `1d` || +|| `open`, `high`, `low`, `close` || numeric(18,6) || all required || +|| `volume` || numeric(20,6) || required || +|| `candle_time` || timestamptz || required — the start of the bucket || + +==== Watchlists ==== +A named list of assets a user wants to monitor. A separate entity set rather than +a flag on the relationship between users and assets, because a user may want +several lists ("long term", "watching today") and each needs its own name. + +'''Keys:''' candidate `{id}` — `{user_id, name}` would also work if list names +were required to be unique per user, which the model does not impose, so it is +not listed as a candidate key; primary key '''`id`'''. + +||= Attribute =||= Type =||= Constraints =|| +|| `id` || UUID || PK, required || +|| `name` || text(100) || required || +|| `created_at` || timestamptz || required, defaults to now || + +=== Relationships === + +==== !QuotedOn — Cryptos (1) : Markets (N), total on Markets ==== +Ties a market to the asset it trades. One asset can be quoted in many markets; +every market must have exactly one asset, hence total participation on the +`Markets` side. No attributes. + +==== !PlacedOn — Markets (1) : Orders (N), total on Orders ==== +Records which market an order was placed on. Every order must name a market; +a market may have no orders yet. No attributes. + +==== Places — Users (1) : Orders (N), total on Orders ==== +Records who placed an order. Every order belongs to exactly one user; a new +user has no orders. No attributes. + +==== Records — Users (1) : Transactions (N), total on Transactions ==== +Attributes each ledger entry to a user. Every entry belongs to exactly one +user. No attributes. + +==== Settles — Orders (1) : Transactions (N), partial on both sides ==== +Links a ledger entry to the order that caused it. Partial on the +`Transactions` side because deposits have no originating order, and partial on +the `Orders` side because an order that never executes never produces a +ledger entry — which is why the corresponding column is nullable in P2. No +attributes. + +==== 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 ==== +Every candle summarises trades of exactly one market. No attributes. + +==== Owns — Users (1) : Watchlists (N), total on Watchlists ==== +Every watchlist belongs to exactly one user. No attributes. + +==== Holds — Users (M) : Cryptos (N), partial on both sides, '''with attributes''' ==== +A user's position in an asset. M:N because one user holds many assets and one +asset is held by many users, and partial on both sides because a user may hold +nothing and an asset may be held by nobody. Modeled as a relationship rather +than an entity set because a position has no identity of its own — it is +entirely described by ''which user'', ''which asset'', and how much. + +`reserved_quantity` mirrors `available_balance`/`invested_balance` on `Users`: +two independently updated stored numbers, with the amount actually free to use +computed on demand rather than stored (`quantity − reserved_quantity` here, +`available_balance` alone on the cash side). Without it, nothing stopped a +user from placing a second sell order against crypto already promised to a +first one — `quantity` alone cannot tell "owned" apart from "owned, but +already committed elsewhere." See history, v03. + +||= Attribute =||= Type =||= Constraints =|| +|| `quantity` || numeric(20,4) || required, ≥ 0 — total amount owned || +|| `reserved_quantity` || numeric(20,4) || required, default 0, `0 ≤ reserved_quantity ≤ quantity` — committed to the user's own open sell orders, not yet removed from the position || +|| `avg_price` || numeric(18,6) || required, ≥ 0, '''derived''' (dashed ellipse) — the weighted average of the prices at which the position was accumulated; derivable from the buy history, stored anyway so unrealised P/L can be shown without replaying the whole ledger || +|| `created_at` || timestamptz || required, defaults to now || +|| `updated_at` || timestamptz || optional || + +==== Contains — Watchlists (M) : Cryptos (N), partial on both sides, '''with attribute''' ==== +Which assets are on which watchlist. M:N: a list holds many assets, an asset +appears on many lists. Partial on both sides — an empty list is valid and an +asset need not be on any list. + +||= Attribute =||= Type =||= Constraints =|| +|| `added_at` || timestamptz || required, defaults to now — recorded so a list can be shown in the order the user built it || + +== Entity-Relationship Model History == + + * '''v01''' — First complete version. Built from the entity notes in `ep-diagram.md` (the initial hand-written model), with three changes made to that initial model while drawing it: + 1. `Markets` was promoted from an implied attribute of the asset to its own entity set, so that prices, orders, trades and candles can all reference a pair rather than an asset. + 2. `holdings` and `watchlist_items` were re-expressed as the M:N relationships `Holds` and `Contains` with their own attributes, instead of entity sets with foreign keys — the initial notes listed them as tables, which is a relational concept that does not belong in a Chen ERD. + 3. `avg_price` was marked as a derived attribute rather than a plain one, to make the denormalisation explicit rather than hidden. + * '''v02''' — Student review pass over the AI-generated v01 in the TerraER GUI. + * '''v03''' — Added `reserved_quantity` to `Holds`, and reworded `Orders.status` to state its reserve → settle → (cancel) lifecycle explicitly, instead of leaving `open`/`cancelled` as unused enum values. Triggered by a design review that pointed out the model had no way to stop a user from placing a second sell order against crypto already promised to a first, unsettled one — `quantity` alone cannot distinguish "owned" from "owned, but already committed." Also redrawn more compactly: every entity and relationship (with its own attributes moved along with it) was pulled proportionally toward the diagram's centroid, shrinking the canvas by roughly 45% with the same topology and no new overlaps. See [wiki:ERModelAIUsage] for the reasoning and how the diagram file itself was produced, and [wiki:RelationalDesign] and [wiki:UseCase0005] 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 + [wiki:AdvancedDatabaseDevelopment]. + 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 [wiki:ERModelAIUsage]. + Index: docs/P1-ConceptualModel/wiki/ERModelAIUsage.md =================================================================== --- docs/P1-ConceptualModel/wiki/ERModelAIUsage.md (revision ef1c1c725137d93fa85292f43440214aaddcdf05) +++ docs/P1-ConceptualModel/wiki/ERModelAIUsage.md (revision ef1c1c725137d93fa85292f43440214aaddcdf05) @@ -0,0 +1,306 @@ += Entity-Relationship Model AI Usage = + +== Name of AI service/solution that was used == + +'''Claude Code''' (Anthropic) + + * '''URL:''' `https://claude.com/claude-code` + * '''Type of service/subscription:''' Claude subscription. Session 1 used model + Claude Opus 4.7 (1M context); session 2 used Claude Opus 5 (1M context). + +== Final result == + +=== Diagram === + +`ERModel_v01.xml` / `ERModel_v01.png`. + +'''Declaration of how the diagram was produced.''' The initial model is the +student's own: the entity and attribute list in `ep-diagram.md`, +written in Macedonian before any AI was involved. In session 2 the AI turned that +list into the TerraER diagram file, and while doing so proposed three changes to +the initial model, all three listed in the model history on +[wiki:ERModel]: +promoting `Markets` to its own entity set, re-expressing `holdings` and +`watchlist_items` as M:N relationships with attributes rather than entity sets +with foreign keys, and marking `avg_price` as derived. + +The diagram was not drawn by hand in the TerraER GUI. It was generated +programmatically by constructing TerraER's own figure objects +(`EntidadeFigure`, `RelacionamentoFigure`, `AtributoFigure`, +`AtributoChaveFigure`, `AtributoDerivadoFigure` and the labelled line-connection +figures) and serialising them with TerraER's own +`DOMStorableInputOutputFormat` — the same writer the application uses when you +choose ''Save''. The file is therefore a normal TerraER document: it was verified +by reading it back through TerraER's own reader and comparing the figure count +(144), and it opens and can be edited in TerraER 3.11 like any hand-drawn +diagram. `ERModel_v02.xml` is the student's own review pass over v01, done by +hand in the GUI. + +`ERModel_v03.xml` / `ERModel_v03.png` (session 3, 2026-09-16) were produced the +same way, this time as a genuine load–modify–save round trip through TerraER's +own classes rather than a from-scratch build: `ERModel_v02.xml` was read with +the application's real `DrawFigureFactory` and `DOMStorableInputOutputFormat` +into a live `QuadTreeDrawing`, one `AtributoFigure` was cloned from the +existing `quantity` attribute of `Holds` (to inherit its exact styling) and +relabelled `reserved_quantity`, a matching `LabeledLineConnectionFigure` was +added between it and the `Holds` diamond (`ChopDiamondConnector` / +`ChopEllipseConnector`, the same connector pair every other attribute of +`Holds` uses), and every entity and relationship — together with its own +attributes, moved by the same offset — was translated proportionally toward +the diagram's centroid to close up excess canvas space, after which every +connection figure had `updateConnection()` called so its drawn path follows +the moved figures. The result was written with the real writer and rendered to +PNG with TerraER's own `ImageOutputFormat`, and re-verified by reading +`ERModel_v03.xml` back and confirming the figure count (146 = 144 + the new +attribute + its line) and that all five attributes of `Holds` resolve with the +expected connector classes. No figure was hand-edited in XML. + +=== Model description === + +See [wiki:ERModel]. + +== Summary of AI involvement == + +Work on this project happened in three working sessions. + +||= =||= Session 1 =||= Session 2 =||= Session 3 =|| +|| '''When''' || 2026-04-21 || 2026-08-06 / 2026-08-07 || 2026-09-16 || +|| '''Model''' || Claude Opus 4.7 (1M context) || Claude Opus 5 (1M context) || Claude Sonnet 5 || +|| '''Phases advanced''' || P1, P2, P3 and the first working prototype || The ER diagram file, P4 documentation, bug fixes || `Holds.reserved_quantity` added across P1–P4 || +|| '''My starting material''' || `ep-diagram.md`, `opis.md`, my existing Go backend and draft SQL || Everything from session 1, plus the phase rubric || Everything from sessions 1–2, plus a design review of the sell-order flow || + +In '''session 1''' I brought my own data model (`ep-diagram.md`, written in +Macedonian before any AI was involved) and my own draft schema and Go backend. I +used the AI to review them, and it found real errors in my SQL that I had missed +— most seriously that I had declared `crypto_id` as a foreign key to two +different tables at once, in three separate tables. I decided the corrections to +adopt, chose to go solo, chose a CLI prototype over an HTTP one, and chose +English for the documentation. The output of that session was the corrected +schema, the sample-data script, the use-case model and the working prototype. + +In '''session 2''' I came back to produce the deliverable I was still missing — the +TerraER diagram file itself — and to finish the P4 documentation. I also asked +for a review pass over the database and Go code, which turned up three further +bugs (see [wiki:PrototypeImplementationAIUsage]). + +In '''session 3''' I described a concrete edge case I'd spotted in the sell flow +— nothing stopped a user from placing a second sell order against crypto +already promised to a first, unsettled one — and asked the AI to fix it +starting from P1 and carry the fix through every phase. This added +`reserved_quantity` to `Holds` and changed the sell path in `trade.go` to use +it; see the model history on +[wiki:ERModel] (v03) and the log +below. + +Below is the log, one entry per exchange. My prompts are quoted as '''excerpts''': +the wording is my own, unaltered, but I have left out scheduling remarks and +other working chatter that says nothing about the model or the code. Typos are +kept as they were. The ''Intent'' line above each prompt is my own note on what I +was trying to achieve. AI responses are summarised rather than quoted in full, +because several ran to many pages of code and file edits. The complete +unabridged transcript is in my Claude Code session history and can be produced +on request. + +== Entire AI usage log == + +---- + +=== Session 1 — 2026-04-21 === + +'''Intent:''' hand the AI the whole project and the course rubric, and have it +audit my existing model and code against the phase requirements before I commit +to anything. + +'''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`, all Go sources. + * Flagged bugs in the initial schema: + * `holdings.crypto_id` declared as a foreign key to both `users(id)` and `crypto(id)`; should be a separate `user_id` column. + * Same mistake in `orders` and `transactions`. + * `market_candles` referenced a non-existent `markets` table. + * `main.go` dropped and recreated all tables on every startup, wiping data. + * Proposed a single authoritative schema, to be placed in `server/db/schema_creation.sql`, based on `docs/ep-diagram.md` and `docs/dbdiagram.md` with the above issues fixed. + * Asked the student for decisions: team composition, prototype shape (CLI vs HTTP), whether to keep `available_balance`, `invested_balance`, `secret_question`, `secret_answer`, which use cases to implement, language of documentation. + +'''Intent:''' I had just pasted the course rubric into `README.md` and removed the +half-finished frontend, so that the AI would plan against the actual phase +requirements and against a backend-only prototype. + +'''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 phase rubric (P0–P9). + * Explained the constraints each phase places on AI usage (P0 forbids AI; P1–P4 allow AI only to improve the student's own initial model and require an AIUsage log). + * Listed the deliverables per phase and asked the remaining questions. + +---- + +=== Session 2 — 2026-08-06 / 2026-08-07 === + +'''Intent:''' re-read the rubric from scratch after the gap, reduce it to a +checklist of what each phase actually has to produce, confirm TerraER runs on +this machine. + +'''Prompt (student, excerpt):''' +> We will start from the begining in some ways, please read first docs/insturctions.md +> The goal is to finish with milestone 0 so I can publish it […] +> Read the Instuctions.md like I said and delete everything that you think we don't need +> and for each phrase tell me what do I need to submit and how to do it. and can you also +> do the thing with TerraER3.11.jar? + +'''Response (AI, summarised):''' + * Read the phase rubric and condensed `Instructions.md` to a per-phase checklist + of deliverables, keeping every requirement and every load-bearing filename. + * Confirmed TerraER 3.11 runs on the installed OpenJDK 21 and launched it. + * Listed, per phase, what has to be submitted and how; flagged that P0's + deliverables are wiki pages, that the EPRMS proposal is a prerequisite. + * Flagged `image.png` (a !TradingView screenshot) as material the project has no + licence to publish. + +'''Intent:''' produce the artefacts still missing for P1–P4 — above all the TerraER +diagram — while keeping P0 for myself, since AI use is forbidden there. + +'''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. + +'''Intent:''' ask for a review pass over the schema and the Go code rather than +only documentation, on the grounds that a prototype I have to defend in person +should not have known defects in 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) — the part relevant to P1:''' + * Read `ep-diagram.md` (the student's own initial model) and the existing + `schema_creation.sql`. + * Reverse-engineered TerraER's file format from the distributed jar to learn the + element names it stores figures under (`ent`, `rel`, `atr`, `atrchave`, + `atrderivado`, `llabelUm`, `llabelMuitos`, `llabelDoubleUm`, + `llabelDoubleMuitos`, …). + * Generated `ERModel_v01.xml` and `ERModel_v01.png` as described above, in Chen + notation: 8 entity sets, 10 relationships, 57 attributes, cardinality labels + on every relationship line and double lines for total participation. + * Proposed the three changes to the initial model recorded in the model history. + * Rewrote [wiki:ERModel] with the per-entity documentation, candidate-key + justifications and attribute types the phase template requires. + +---- + +> '''Student action required.''' Two things, in this order: +> +> 1. Open `ERModel_v01.xml` in TerraER, read the whole diagram, and change what +> you disagree with. Save the result as `ERModel_v02.xml` with a matching PNG +> and add a history line. The phase rules require that the model be yours; +> the generated v01 is a starting point to review and take over, not an +> answer to submit unread. +> 2. Verify that this log matches your recollection and append the full text of +> any further prompts. The complete transcript is in your Claude Code session +> history. + +---- + +=== Session 3 — 2026-09-16 === + +'''Intent:''' describe a concrete gap I'd found in the sell-order design — the +model had no way to represent crypto committed to an order that had not yet +settled — and have the AI trace the fix through every phase rather than patch +`trade.go` alone. + +'''Prompt (student, excerpt):''' +> Suppose the user owns 2 BTC and wants to sell 0.5 BTC at market price. […] You +> currently have Holds: quantity, avg_price but you don't have a concept of +> reserved/committed crypto. For cash, you already explicitly distinguish +> available_balance, invested_balance. For crypto, you probably want the +> equivalent: quantity, reserved_quantity, avg_price. […] Can you please start +> from Phase 1 and modify everything to make it work like it should, modify +> the AIUsage.md in every Phase, and can you make the !ConceptualModel a little +> bit more put together not that much spread out. + +'''Response (AI, summarised):''' + * Read every phase document and the live `server/trade.go`, `portfolio.go` and + `schema_creation.sql` to confirm the actual gap: the sell path checked + `holdings.quantity` directly, with nothing recording that part of a position + was already promised to another order. + * Added `reserved_quantity` to `Holds` (P1), `holdings` (P2), the sell scenario + SQL (P3), and `trade.go`/`portfolio.go`/`schema_creation.sql` (P4) — one + attribute threaded consistently through all four phases rather than a + patch confined to the Go code. + * Changed the sell path to lock the holding row, compute + `available = quantity − reserved_quantity`, reserve (`reserved_quantity += qty`) + and then settle (`quantity -= qty; reserved_quantity -= qty`) inside + the same transaction — keeping the existing all-or-nothing guarantee rather + than splitting into two commits, which would leave an unresolvable `open` + order if the process died in between and there is no cancel use case to + recover it. + * Made both buy and sell orders genuinely pass through `status='open'` before + `status='executed'`, instead of being inserted as `'executed'` directly, so + the lifecycle the ER model already claimed for `Orders` is real. + * Added a `CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` + constraint and exposed `reserved_quantity`/`available_quantity` on + `v_portfolio` and in the CLI's portfolio table, so the new field is + observable rather than a column nobody can see. + * Restructured this phase's [wiki:ERModel] into short rationale + a + compact attribute table per entity/relationship, per the "more put together, + not that much spread out" request — no content was removed, only + reformatted. + * Verified the fix against the live database (`bp_database` on `localhost:5433`): + ran two concurrent `sell 1.0 BTC` attempts against a holding with only 1.5 + BTC available — exactly one succeeded, the other correctly reported + insufficient holding — and ran the reserve/settle sequence by hand in `psql` + to show `reserved_quantity` at 0.5 mid-transaction. Both are recorded in + [wiki:UseCase0005Implementation]. + +'''What I decided:''' to keep reserve and settle inside one transaction rather +than two (see the AI's reasoning above — I agreed with it, since a stuck +`open` order with no cancel command would be a worse bug than the one being +fixed). + +'''Follow-up, same day:''' I asked for `ERModel_v03.xml`/`.png` after all, +having noticed the PNG still showed v02 with no `reserved_quantity` on it, and +asked at the same time for the diagram to be a little more compact — it had a +lot of empty canvas in the middle. The AI drove TerraER's own classes directly +(load → clone the `quantity` attribute → relabel it → add its connecting line +→ pull every cluster toward the centroid → save → render), described in full +under Diagram above, rather than hand-editing the XML or asking me +to do it in the GUI. I reviewed the rendered PNG before accepting it. + +'''Second follow-up, same day:''' the first `ERModel_v03.png` rendered with a +black background instead of white, unlike v01/v02. Cause: TerraER's +`ImageOutputFormat` defaults to an ARGB image and paints its background with +zero alpha (transparent), not opaque white; whatever displayed the PNG then +flattened that transparency onto black instead of white. Fixed by exporting +through the same `ImageOutputFormat.toImage(...)` call for figure geometry, +but compositing its result onto an explicitly white-filled opaque `RGB` image +before saving, rather than trusting the library's own (transparent) output. +Verified the fix by reading back the corner pixel of the written PNG as pure +white `(255,255,255)`, matching `ERModel_v02.png`. + + +=== 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 [wiki:ERModel] (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.