Index: docs/P1-ConceptualModel/ERModel.md =================================================================== --- docs/P1-ConceptualModel/ERModel.md (revision ef1c1c725137d93fa85292f43440214aaddcdf05) +++ docs/P1-ConceptualModel/ERModel.md (revision 1549dae4501012cfe2bffe1c5e9bdfdfb9dd62bd) @@ -1,7 +1,7 @@ -# Entity-Relationship Model v.04 +# Entity-Relationship Model v.05 ## Diagram -![ERModel_v04](ERModel_v04.png) +![ERModel_v05](ERModel_v05.png) Notation: Chen. Rectangles are entity sets, diamonds are relationships, ellipses @@ -11,12 +11,22 @@ single line marks partial participation. -Two deliberate modeling decisions worth stating up front: +Three 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 [RelationalDesign](../P2-RelationalDesign/RelationalDesign.md). -- **`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. +- **A position and a watchlist entry are entity sets, not M:N relationships.** + `Holdings` (a user's position in an asset) and `WatchlistItems` (an asset on a + watchlist) each have their own identifier `id`, and each is connected by two + 1:N relationships: `Holds` and `PositionIn` for a holding, `Contains` and + `Lists` for a watchlist item. Until v04 they were drawn as the M:N + relationships `Holds` and `Contains`, but the database has always given + `holdings` and `watchlist_items` their own `id` primary key. That is how an + entity set is implemented, not an M:N relationship, whose key would be the + pair of participating keys. v05 corrects the model to match; see + [history](#entity-relationship-model-history). +- **Key and uniqueness rules are stated with entity and relationship names, + never with foreign-key columns.** For example: "a crypto is quoted at most + once per currency", not "`{crypto_id, quote_currency}` is unique". ## Data requirements @@ -46,5 +56,5 @@ | `id` | UUID | PK, required | | `username` | text(50) | required, unique | -| `email` | text(255) | required, unique, contains `@` | +| `email` | text(255) | required, unique, contains `@` (checked by the application at registration, not by a database constraint) | | `full_name` | text(200) | optional | | `password_hash` | text(255) | required — never the password itself; the prototype stores a SHA-256 hex digest | @@ -79,8 +89,10 @@ 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. +**Keys:** candidate `{id}`; primary key **`id`**, so that the many entity sets +related to a market need one narrow identifier instead of a composite one. +**Uniqueness rule:** a crypto is quoted at most once per currency, so the crypto +a market is `QuotedOn` together with its `quote_currency` identifies the market +as well. Chen notation cannot draw this, because half of it comes through a +relationship. P2 enforces it as `UNIQUE(crypto_id, quote_currency)`. | Attribute | Type | Constraints | @@ -98,5 +110,5 @@ 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 +the crypto being sold (`Holdings.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 @@ -121,5 +133,5 @@ | `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 | +| `price` | numeric(18,6) | optional — 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 | @@ -148,6 +160,6 @@ 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; +**Keys:** candidate `{id}` — "market plus `executed_at`" looks unique in +principle, but two trades on a market 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 @@ -156,5 +168,5 @@ | Attribute | Type | Constraints | |---|---|---| -| `id` | integer | PK, required, auto-generated | +| `id` | big integer | PK, required, auto-generated (`bigserial` in P2) | | `executed_at` | timestamptz | required | | `price` | numeric(18,6) | required, > 0 | @@ -177,10 +189,10 @@ | Attribute | Type | Constraints | |---|---|---| -| `id` | integer | PK, required, auto-generated | +| `id` | big integer | PK, required, auto-generated (`bigserial` in P2) | | `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 | +| `created_at` | timestamptz | required, set automatically when the event is recorded (`clock_timestamp()`, so events inside one transaction keep their real order) | #### MarketCandles @@ -190,12 +202,13 @@ 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 | +**Keys:** candidate `{id}`; primary key **`id`**. **Uniqueness rule:** a market +has exactly one candle per timeframe per time bucket, so the market a candle +`Aggregates` together with `timeframe` and `candle_time` also identifies it. +This is the real-world constraint that prevents duplicate candles. P2 enforces +it as `UNIQUE(market_id, timeframe, candle_time)`. + +| Attribute | Type | Constraints | +|---|---|---| +| `id` | big integer | PK, required, auto-generated (`bigserial` in P2) | | `timeframe` | text | required, `1m`, `5m`, `1h` or `1d` | | `open`, `high`, `low`, `close` | numeric(18,6) | all required | @@ -208,7 +221,7 @@ 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`**. +**Keys:** candidate `{id}`; primary key **`id`**. "Owner plus `name`" would +also identify a list if names had to be unique per user, but the model does +not require that, so there is no uniqueness rule here. | Attribute | Type | Constraints | @@ -218,59 +231,18 @@ | `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. +#### Holdings +A user's position in one crypto asset: how much of it the user owns, how much +of that is already promised to open sell orders, and at what average price it +was accumulated. *An entity set since v05* (until v04 it was the M:N +relationship `Holds`). A holding has its own identifier and its own +lifecycle: it is created on the first buy, updated on every later fill, and +the prototype reads and locks it as a unit (`SELECT … FOR UPDATE` on the sell +path). It is linked to its owner through `Holds` and to its asset through +`PositionIn`. + +**Keys:** candidate `{id}`; primary key **`id`**. **Uniqueness rule:** a user +has at most one holding per crypto, so the user who `Holds` it together with +the crypto it is a `PositionIn` also identifies a holding. P2 enforces this as +`UNIQUE(user_id, crypto_id)`. `reserved_quantity` mirrors `available_balance`/`invested_balance` on `Users`: @@ -284,18 +256,97 @@ | Attribute | Type | Constraints | |---|---|---| +| `id` | UUID | PK, required | | `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 | +| `avg_price` | numeric(18,6) | required, default 0, ≥ 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 | -|---|---|---| +#### WatchlistItems +One asset placed on one watchlist. *An entity set since v05* (until v04 it was +the M:N relationship `Contains`). It has its own identifier, and it is linked +to its list through `Contains` and to its asset through `Lists`. + +**Keys:** candidate `{id}`; primary key **`id`**. **Uniqueness rule:** an asset +appears at most once on a given list, so the watchlist that `Contains` an item +together with the crypto it `Lists` also identifies the item. P2 enforces this +as `UNIQUE(watchlist_id, crypto_id)`. + +| Attribute | Type | Constraints | +|---|---|---| +| `id` | UUID | PK, required | | `added_at` | timestamptz | required, defaults to now — recorded so a list can be shown in the order the user built it | + +### 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. The role of `Orders` in this +relationship is *the buy order* of the trade. 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`; the role of `Orders` here is *the sell order* of the trade. 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 (1) : Holdings (N), total on Holdings +*1:N since v05.* Every holding belongs to exactly one user. A user may hold +nothing yet, so participation is partial on the `Users` side. No attributes. + +#### PositionIn — Cryptos (1) : Holdings (N), total on Holdings +*Added in v05.* Every holding is a position in exactly one crypto asset. An +asset may be held by nobody. No attributes. + +Together, `Holds` and `PositionIn` still say what the old M:N `Holds` said: +a user can hold many assets and an asset can be held by many users. The +difference is that the position is now a thing with its own identity, not +just a pair. The rule "at most one holding per user and crypto" is stated +under [Holdings](#holdings). + +#### Contains — Watchlists (1) : WatchlistItems (N), total on WatchlistItems +*1:N since v05.* Every watchlist item is on exactly one list. An empty list is +valid, so participation is partial on the `Watchlists` side. No attributes. + +#### Lists — Cryptos (1) : WatchlistItems (N), total on WatchlistItems +*Added in v05.* Every watchlist item names exactly one crypto asset. An asset +need not be on any list. No attributes. ## Entity-Relationship Model History @@ -341,5 +392,36 @@ Nothing existing was removed or changed. See [AdvancedDatabaseDevelopment](../P7-AdvancedDatabaseDevelopment/AdvancedDatabaseDevelopment.md). - The diagram files are `ERModel_v04.xml` / `ERModel_v04.png`; earlier versions + The diagram files are `ERModel_v04.xml` / `ERModel_v04.png`. +- **v05 — correction after review.** The review of P2 found that two parts of + the model were implemented differently in the database: + - `Contains` was an M:N relationship in the model, but `watchlist_items` + has its own `id` primary key; + - `Holds` was an M:N relationship in the model, but `holdings` has its own + `id` primary key. + + An M:N relationship has no identifier of its own; its table's key is the pair + of participating keys. A table with its own `id` is the implementation of an + entity set. Every phase after P2 (the prototype, the reports and the P7 + logic) already uses the database as it is. So the **model** was corrected to + match P2, not the other way round: + - `Holds` (M:N, with attributes) became the entity set `Holdings` (its former + attributes plus `id`) with two 1:N relationships, `Holds` (Users → Holdings) + and `PositionIn` (Cryptos → Holdings), both total on the `Holdings` side; + - `Contains` (M:N, with `added_at`) became the entity set `WatchlistItems` + (`id`, `added_at`) with `Contains` (Watchlists → WatchlistItems) and + `Lists` (Cryptos → WatchlistItems), both total on the `WatchlistItems` + side; + - the former keys of the two relationships are kept as uniqueness rules + ("one holding per user and crypto", "an asset at most once per list"); + - the key descriptions of `Markets`, `MarketTrades`, `MarketCandles` and + `Watchlists` no longer name foreign-key columns (`crypto_id`, + `market_id`, `user_id`), which do not exist in an ER model; + - the diagram was redrawn on a grid with no overlapping attributes. In v04, + `Watchlists.id` was hidden behind `added_at`, and several attributes of + `Orders`, `Transactions`, `MarketTrades` and `MarketCandles` overlapped. + The grid also makes it easier to compare the diagram with the P2 + relational diagram. + + The diagram files are `ERModel_v05.xml` / `ERModel_v05.png`; earlier versions are kept. Index: docs/P1-ConceptualModel/ERModelAIUsage.md =================================================================== --- docs/P1-ConceptualModel/ERModelAIUsage.md (revision ef1c1c725137d93fa85292f43440214aaddcdf05) +++ docs/P1-ConceptualModel/ERModelAIUsage.md (revision 1549dae4501012cfe2bffe1c5e9bdfdfb9dd62bd) @@ -13,5 +13,6 @@ ### Diagram -`ERModel_v01.xml` / `ERModel_v01.png`. +Current version: `ERModel_v05.xml` / `ERModel_v05.png` (see session 5 below). First +version: `ERModel_v01.xml` / `ERModel_v01.png`. **Declaration of how the diagram was produced.** The initial model is the @@ -313,2 +314,32 @@ **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. + + +### Session 5 — 2026-09-29 (Claude Opus 5.5): v05, correction after review + +**Prompt (student, excerpt):** I pasted the reviewer's comments that `Holds` and `Contains` +are implemented differently in the database than in the model, with the suggestion to +"correct only the Phase 1 model so that it matches Phase 2", and asked: + +> Can you take a look at this stuff? and fix them, but don't push anything just make the +> changes requested and try to explain it to me to see if you understand the problem with +> the solution + +**Response (AI, summarised):** + +- Explained that an M:N relationship has no identifier of its own, while `holdings` and + `watchlist_items` each have an `id` primary key, so the database treats them as entity + sets. +- Changed the model to v05. `Holdings` (`id`, `quantity`, `reserved_quantity`, derived + `avg_price`, `created_at`, `updated_at`) and `WatchlistItems` (`id`, `added_at`) became + entity sets, with the 1:N relationships `Holds`, `PositionIn`, `Contains` and `Lists`, + each total on the new entity's side. The old relationship keys are now stated as + uniqueness rules. The key descriptions no longer name foreign-key columns. +- Generated `ERModel_v05.xml` / `ERModel_v05.png` from scratch with TerraER 3.11's own figure + classes and writer (adapted from the v01 generator), on a grid, with no overlapping + attributes. It was verified by reading the file back with TerraER's reader (184 figures) + and by inspecting the rendered PNG. +- Updated [ERModel](ERModel.md) (v05 sections and history entry). + +**What I decided:** to follow the reviewer's advice and change the model rather than the +database, since every later phase already uses the database as it is. Index: docs/P1-ConceptualModel/ERModel_v05.xml =================================================================== --- docs/P1-ConceptualModel/ERModel_v05.xml (revision 1549dae4501012cfe2bffe1c5e9bdfdfb9dd62bd) +++ docs/P1-ConceptualModel/ERModel_v05.xml (revision 1549dae4501012cfe2bffe1c5e9bdfdfb9dd62bd) @@ -0,0 +1,1 @@ +WatchlistItemsCryptosMarketsMarketCandlesWatchlistsHoldingsMarketTradesUsersOrdersTransactionsOrderEventsListsContainsQuotedOnAggregatesPositionInOwnsHoldsPlacesPlacedOnFillsFillsBuyFillsSellRecordsSettlesLogs

3

3

3

3

3

3

3

3

3

3

3

3truetrueid

added_at

truetrueid

symbol

name

created_at

truetrueid

quote_currency

is_active

created_at

truetrueid

timeframe

open

high

low

close

volume

candle_time

truetrueid

name

created_at

truetrueid

quantity

reserved_quantity

5avg_price

created_at

updated_at

truetrueid

executed_at

price

quantity

side

source

truetrueid

username

email

full_name

password_hash

available_balance

invested_balance

reserved_balance

created_at

updated_at

truetrueid

side

type

status

quantity

filled_quantity

price

placed_at

executed_at

truetrueid

type

amount

currency

created_at

description

truetrueid

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 1549dae4501012cfe2bffe1c5e9bdfdfb9dd62bd) @@ -1,7 +1,7 @@ -= Entity-Relationship Model v.04 = += Entity-Relationship Model v.05 = == Diagram == -[[Image(ERModel_v04.png, 800px)]] +[[Image(ERModel_v05.png, 800px)]] Notation: Chen. Rectangles are entity sets, diamonds are relationships, ellipses @@ -11,8 +11,9 @@ single line marks partial participation. -Two deliberate modeling decisions worth stating up front: +Three 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. + * '''A position and a watchlist entry are entity sets, not M:N relationships.''' `Holdings` (a user's position in an asset) and `WatchlistItems` (an asset on a watchlist) each have their own identifier `id`, and each is connected by two 1:N relationships: `Holds` and `PositionIn` for a holding, `Contains` and `Lists` for a watchlist item. Until v04 they were drawn as the M:N relationships `Holds` and `Contains`, but the database has always given `holdings` and `watchlist_items` their own `id` primary key. That is how an entity set is implemented, not an M:N relationship, whose key would be the pair of participating keys. v05 corrects the model to match; see history. + * '''Key and uniqueness rules are stated with entity and relationship names, never with foreign-key columns.''' For example: "a crypto is quoted at most once per currency", not "`{crypto_id, quote_currency}` is unique". == Data requirements == @@ -41,5 +42,5 @@ || `id` || UUID || PK, required || || `username` || text(50) || required, unique || -|| `email` || text(255) || required, unique, contains `@` || +|| `email` || text(255) || required, unique, contains `@` (checked by the application at registration, not by a database constraint) || || `full_name` || text(200) || optional || || `password_hash` || text(255) || required — never the password itself; the prototype stores a SHA-256 hex digest || @@ -73,8 +74,10 @@ 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. +'''Keys:''' candidate `{id}`; primary key '''`id`''', so that the many entity sets +related to a market need one narrow identifier instead of a composite one. +'''Uniqueness rule:''' a crypto is quoted at most once per currency, so the crypto +a market is `QuotedOn` together with its `quote_currency` identifies the market +as well. Chen notation cannot draw this, because half of it comes through a +relationship. P2 enforces it as `UNIQUE(crypto_id, quote_currency)`. ||= Attribute =||= Type =||= Constraints =|| @@ -91,5 +94,5 @@ 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 +the crypto being sold (`Holdings.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 @@ -113,5 +116,5 @@ || `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 || +|| `price` || numeric(18,6) || optional — 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 || @@ -139,6 +142,6 @@ 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; +'''Keys:''' candidate `{id}` — "market plus `executed_at`" looks unique in +principle, but two trades on a market 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 @@ -146,5 +149,5 @@ ||= Attribute =||= Type =||= Constraints =|| -|| `id` || integer || PK, required, auto-generated || +|| `id` || big integer || PK, required, auto-generated (`bigserial` in P2) || || `executed_at` || timestamptz || required || || `price` || numeric(18,6) || required, > 0 || @@ -166,10 +169,10 @@ ||= Attribute =||= Type =||= Constraints =|| -|| `id` || integer || PK, required, auto-generated || +|| `id` || big integer || PK, required, auto-generated (`bigserial` in P2) || || `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 || +|| `created_at` || timestamptz || required, set automatically when the event is recorded (`clock_timestamp()`, so events inside one transaction keep their real order) || ==== !MarketCandles ==== @@ -179,11 +182,12 @@ 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 || +'''Keys:''' candidate `{id}`; primary key '''`id`'''. '''Uniqueness rule:''' a market +has exactly one candle per timeframe per time bucket, so the market a candle +`Aggregates` together with `timeframe` and `candle_time` also identifies it. +This is the real-world constraint that prevents duplicate candles. P2 enforces +it as `UNIQUE(market_id, timeframe, candle_time)`. + +||= Attribute =||= Type =||= Constraints =|| +|| `id` || big integer || PK, required, auto-generated (`bigserial` in P2) || || `timeframe` || text || required, `1m`, `5m`, `1h` or `1d` || || `open`, `high`, `low`, `close` || numeric(18,6) || all required || @@ -196,7 +200,7 @@ 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`'''. +'''Keys:''' candidate `{id}`; primary key '''`id`'''. "Owner plus `name`" would +also identify a list if names had to be unique per user, but the model does +not require that, so there is no uniqueness rule here. ||= Attribute =||= Type =||= Constraints =|| @@ -205,59 +209,18 @@ || `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. +==== Holdings ==== +A user's position in one crypto asset: how much of it the user owns, how much +of that is already promised to open sell orders, and at what average price it +was accumulated. ''An entity set since v05'' (until v04 it was the M:N +relationship `Holds`). A holding has its own identifier and its own +lifecycle: it is created on the first buy, updated on every later fill, and +the prototype reads and locks it as a unit (`SELECT … FOR UPDATE` on the sell +path). It is linked to its owner through `Holds` and to its asset through +`PositionIn`. + +'''Keys:''' candidate `{id}`; primary key '''`id`'''. '''Uniqueness rule:''' a user +has at most one holding per crypto, so the user who `Holds` it together with +the crypto it is a `PositionIn` also identifies a holding. P2 enforces this as +`UNIQUE(user_id, crypto_id)`. `reserved_quantity` mirrors `available_balance`/`invested_balance` on `Users`: @@ -270,17 +233,96 @@ ||= Attribute =||= Type =||= Constraints =|| +|| `id` || UUID || PK, required || || `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 || +|| `avg_price` || numeric(18,6) || required, default 0, ≥ 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 =|| +==== !WatchlistItems ==== +One asset placed on one watchlist. ''An entity set since v05'' (until v04 it was +the M:N relationship `Contains`). It has its own identifier, and it is linked +to its list through `Contains` and to its asset through `Lists`. + +'''Keys:''' candidate `{id}`; primary key '''`id`'''. '''Uniqueness rule:''' an asset +appears at most once on a given list, so the watchlist that `Contains` an item +together with the crypto it `Lists` also identifies the item. P2 enforces this +as `UNIQUE(watchlist_id, crypto_id)`. + +||= Attribute =||= Type =||= Constraints =|| +|| `id` || UUID || PK, required || || `added_at` || timestamptz || required, defaults to now — recorded so a list can be shown in the order the user built it || + +=== 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. The role of `Orders` in this +relationship is ''the buy order'' of the trade. 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`; the role of `Orders` here is ''the sell order'' of the trade. 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 (1) : Holdings (N), total on Holdings ==== +''1:N since v05.'' Every holding belongs to exactly one user. A user may hold +nothing yet, so participation is partial on the `Users` side. No attributes. + +==== !PositionIn — Cryptos (1) : Holdings (N), total on Holdings ==== +''Added in v05.'' Every holding is a position in exactly one crypto asset. An +asset may be held by nobody. No attributes. + +Together, `Holds` and `PositionIn` still say what the old M:N `Holds` said: +a user can hold many assets and an asset can be held by many users. The +difference is that the position is now a thing with its own identity, not +just a pair. The rule "at most one holding per user and crypto" is stated +under Holdings. + +==== Contains — Watchlists (1) : !WatchlistItems (N), total on !WatchlistItems ==== +''1:N since v05.'' Every watchlist item is on exactly one list. An empty list is +valid, so participation is partial on the `Watchlists` side. No attributes. + +==== Lists — Cryptos (1) : !WatchlistItems (N), total on !WatchlistItems ==== +''Added in v05.'' Every watchlist item names exactly one crypto asset. An asset +need not be on any list. No attributes. == Entity-Relationship Model History == @@ -300,5 +342,21 @@ Nothing existing was removed or changed. See [wiki:AdvancedDatabaseDevelopment]. - The diagram files are `ERModel_v04.xml` / `ERModel_v04.png`; earlier versions + The diagram files are `ERModel_v04.xml` / `ERModel_v04.png`. + * '''v05 — correction after review.''' The review of P2 found that two parts of the model were implemented differently in the database: + * `Contains` was an M:N relationship in the model, but `watchlist_items` has its own `id` primary key; + * `Holds` was an M:N relationship in the model, but `holdings` has its own `id` primary key. + + An M:N relationship has no identifier of its own; its table's key is the pair + of participating keys. A table with its own `id` is the implementation of an + entity set. Every phase after P2 (the prototype, the reports and the P7 + logic) already uses the database as it is. So the '''model''' was corrected to + match P2, not the other way round: + * `Holds` (M:N, with attributes) became the entity set `Holdings` (its former attributes plus `id`) with two 1:N relationships, `Holds` (Users → Holdings) and `PositionIn` (Cryptos → Holdings), both total on the `Holdings` side; + * `Contains` (M:N, with `added_at`) became the entity set `WatchlistItems` (`id`, `added_at`) with `Contains` (Watchlists → !WatchlistItems) and `Lists` (Cryptos → !WatchlistItems), both total on the `WatchlistItems` side; + * the former keys of the two relationships are kept as uniqueness rules ("one holding per user and crypto", "an asset at most once per list"); + * the key descriptions of `Markets`, `MarketTrades`, `MarketCandles` and `Watchlists` no longer name foreign-key columns (`crypto_id`, `market_id`, `user_id`), which do not exist in an ER model; + * the diagram was redrawn on a grid with no overlapping attributes. In v04, `Watchlists.id` was hidden behind `added_at`, and several attributes of `Orders`, `Transactions`, `MarketTrades` and `MarketCandles` overlapped. The grid also makes it easier to compare the diagram with the P2 relational diagram. + + The diagram files are `ERModel_v05.xml` / `ERModel_v05.png`; earlier versions are kept. Index: docs/P1-ConceptualModel/wiki/ERModelAIUsage.md =================================================================== --- docs/P1-ConceptualModel/wiki/ERModelAIUsage.md (revision ef1c1c725137d93fa85292f43440214aaddcdf05) +++ docs/P1-ConceptualModel/wiki/ERModelAIUsage.md (revision 1549dae4501012cfe2bffe1c5e9bdfdfb9dd62bd) @@ -13,5 +13,6 @@ === Diagram === -`ERModel_v01.xml` / `ERModel_v01.png`. +Current version: `ERModel_v05.xml` / `ERModel_v05.png` (see session 5 below). First +version: `ERModel_v01.xml` / `ERModel_v01.png`. '''Declaration of how the diagram was produced.''' The initial model is the @@ -305,2 +306,22 @@ '''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. + +=== Session 5 — 2026-09-29 (Claude Opus 5.5): v05, correction after review === + +'''Prompt (student, excerpt):''' I pasted the reviewer's comments that `Holds` and `Contains` +are implemented differently in the database than in the model, with the suggestion to +"correct only the Phase 1 model so that it matches Phase 2", and asked: + +> Can you take a look at this stuff? and fix them, but don't push anything just make the +> changes requested and try to explain it to me to see if you understand the problem with +> the solution + +'''Response (AI, summarised):''' + + * Explained that an M:N relationship has no identifier of its own, while `holdings` and `watchlist_items` each have an `id` primary key, so the database treats them as entity sets. + * Changed the model to v05. `Holdings` (`id`, `quantity`, `reserved_quantity`, derived `avg_price`, `created_at`, `updated_at`) and `WatchlistItems` (`id`, `added_at`) became entity sets, with the 1:N relationships `Holds`, `PositionIn`, `Contains` and `Lists`, each total on the new entity's side. The old relationship keys are now stated as uniqueness rules. The key descriptions no longer name foreign-key columns. + * Generated `ERModel_v05.xml` / `ERModel_v05.png` from scratch with TerraER 3.11's own figure classes and writer (adapted from the v01 generator), on a grid, with no overlapping attributes. It was verified by reading the file back with TerraER's reader (184 figures) and by inspecting the rendered PNG. + * Updated [wiki:ERModel] (v05 sections and history entry). + +'''What I decided:''' to follow the reviewer's advice and change the model rather than the +database, since every later phase already uses the database as it is.