Changes between Version 10 and Version 11 of ERModel


Ignore:
Timestamp:
09/29/26 19:57:20 (15 hours ago)
Author:
231285
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • ERModel

    v10 v11  
    1 = Entity-Relationship Model v.03 =
     1= Entity-Relationship Model v.05 =
    22
    33== Diagram ==
    44
    5 [[Image(ERModel_v04.png, 800px)]]
     5[[Image(ERModel_v05.png, 800px)]]
    66
    77Notation: Chen. Rectangles are entity sets, diamonds are relationships, ellipses
    … …  
    1111single line marks partial participation.
    1212
    13 Two deliberate modeling decisions worth stating up front:
    14 
    15  * '''No foreign keys appear in the diagram.''' Connections between entity sets are
    16    expressed as relationships, per the notation. Foreign-key columns appear only
    17    in the relational model in !RelationalDesign.
    18  * '''`Holds` and `Contains` are relationships, not entity sets.''' Both are M:N and
    19    both carry their own attributes, which is exactly what a Chen relationship is
    20    for. They become tables (`holdings`, `watchlist_items`) only in P2.
     13Three deliberate modeling decisions worth stating up front:
     14
     15 * '''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].
     16 * '''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.
     17 * '''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".
    2118
    2219== Data requirements ==
    … …  
    4542|| `id` || UUID || PK, required ||
    4643|| `username` || text(50) || required, unique ||
    47 || `email` || text(255) || required, unique, contains `@` ||
     44|| `email` || text(255) || required, unique, contains `@` (checked by the application at registration, not by a database constraint) ||
    4845|| `full_name` || text(200) || optional ||
    4946|| `password_hash` || text(255) || required — never the password itself; the prototype stores a SHA-256 hex digest ||
    5047|| `available_balance` || numeric(18,4) || required, default 0, ≥ 0 ||
    5148|| `invested_balance` || numeric(18,4) || required, default 0, ≥ 0 ||
     49|| `reserved_balance` || numeric(18,4) || required, default 0, ≥ 0 — cash set aside for the user's open buy orders (added in v04, after P7) ||
    5250|| `created_at` || timestamptz || required, defaults to now ||
    5351|| `updated_at` || timestamptz || optional (null until first change) ||
    … …  
    7674trades, candles and orders all reference the pair, not the asset.
    7775
    78 '''Keys:''' candidates `{id}`, `{crypto_id, quote_currency}` — that pair is
    79 unique by definition, since a given asset can only be quoted once per
    80 currency; primary key '''`id`''', so that the many entity sets referencing a
    81 market carry one narrow column instead of a composite key.
     76'''Keys:''' candidate `{id}`; primary key '''`id`''', so that the many entity sets
     77related to a market need one narrow identifier instead of a composite one.
     78'''Uniqueness rule:''' a crypto is quoted at most once per currency, so the crypto
     79a market is `QuotedOn` together with its `quote_currency` identifies the market
     80as well. Chen notation cannot draw this, because half of it comes through a
     81relationship. P2 enforces it as `UNIQUE(crypto_id, quote_currency)`.
    8282
    8383||= Attribute =||= Type =||= Constraints =||
    … …  
    9393makes the ledger auditable.
    9494
    95 Placing an order is what triggers a '''reservation''' of whatever it commits —
    96 the crypto being sold (`Holds.reserved_quantity`, below) on a sell, cash
    97 already handled the same way on a buy via `available_balance` /
    98 `invested_balance`. `status` therefore has real meaning as a lifecycle, not
    99 just a label: `open` means reserved but not yet settled, `executed` means
    100 settled, `cancelled` would release the reservation without settling (not yet
    101 exercised by any use case, since only market orders — which settle
    102 immediately — are implemented). See
    103 UseCase0005 for the reserve-then-settle
    104 sequence.
     95Placing an order is what triggers a '''reservation''' of whatever it commits:
     96the crypto being sold (`Holdings.reserved_quantity`, below) on a sell, and the
     97cash (`Users.reserved_balance`) on a buy. Since v04 (after P7) an order can
     98wait in the order book and be filled in parts, so `status` is a real
     99lifecycle driven by `filled_quantity`: `open` (nothing filled yet),
     100`partially_filled`, `executed` (completely filled), or `cancelled`, which
     101releases what is still reserved. See
     102[wiki:UseCase0005] for the reserve-then-settle
     103sequence and
     104[wiki:AdvancedDatabaseDevelopment]
     105for the rules that keep it consistent.
    105106
    106107'''Keys:''' candidate `{id}` only — there is no natural key, since the same user
    … …  
    111112|| `id` || UUID || PK, required ||
    112113|| `side` || text || required, `buy` or `sell` ||
    113 || `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 ||
    114 || `status` || text || required, `open`, `executed` or `cancelled` ||
     114|| `type` || text || required, `market` or `limit` (both executed since P7) ||
     115|| `status` || text || required, `open`, `partially_filled`, `executed` or `cancelled` ||
    115116|| `quantity` || numeric(20,4) || required, > 0 ||
    116 || `price` || numeric(18,6) || optional — null until the order settles, then the fill price ||
     117|| `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) ||
     118|| `price` || numeric(18,6) || optional — the limit price; for a market order, the market price when it was placed ||
    117119|| `placed_at` || timestamptz || required, defaults to now ||
    118120|| `executed_at` || timestamptz || optional, set when the order settles ||
    … …  
    140142writes directly.
    141143
    142 '''Keys:''' candidate `{id}` — `{market_id, executed_at}` looks unique in
    143 principle, but two trades can share a timestamp, so it is not a safe key;
     144'''Keys:''' candidate `{id}` — "market plus `executed_at`" looks unique in
     145principle, but two trades on a market can share a timestamp, so it is not a safe key;
    144146primary key '''`id`''' (a plain auto-incrementing integer here rather than a
    145147UUID, because this is the highest-volume entity set and it is only ever read
    … …  
    147149
    148150||= Attribute =||= Type =||= Constraints =||
    149 || `id` || integer || PK, required, auto-generated ||
     151|| `id` || big integer || PK, required, auto-generated (`bigserial` in P2) ||
    150152|| `executed_at` || timestamptz || required ||
    151153|| `price` || numeric(18,6) || required, > 0 ||
    … …  
    154156|| `source` || text(50) || required, default `simulation` — distinguishes a simulated trade from a user's own fill (`user`) ||
    155157
     158Since v04 (after P7) a trade also records which orders it filled, through the
     159relationships `FillsBuy` and `FillsSell` below.
     160
     161==== !OrderEvents ====
     162''Added in v04, after P7.'' The audit trail of an order: one event for its
     163placement, one for every (partial) fill, and one for a cancellation. The
     164`Orders` row only holds the current state; this entity keeps the history of
     165how the order got there. Events are recorded automatically by the database.
     166
     167'''Keys:''' candidate `{id}` only; primary key '''`id`''' (auto-incrementing
     168integer, events are only read in order).
     169
     170||= Attribute =||= Type =||= Constraints =||
     171|| `id` || big integer || PK, required, auto-generated (`bigserial` in P2) ||
     172|| `event_type` || text || required, `placed`, `partially_filled`, `filled` or `cancelled` ||
     173|| `quantity` || numeric(20,4) || required — the ordered quantity for `placed`, the filled amount for a fill, the unfilled rest for `cancelled` ||
     174|| `price` || numeric(18,6) || optional — the order price, or the trade price for a fill ||
     175|| `status_after` || text || required, the order's status after the event ||
     176|| `created_at` || timestamptz || required, set automatically when the event is recorded (`clock_timestamp()`, so events inside one transaction keep their real order) ||
     177
    156178==== !MarketCandles ====
    157179OHLCV aggregates per market and timeframe — the data a price chart is drawn
    … …  
    160182screen refresh does not scale.
    161183
    162 '''Keys:''' candidates `{id}`, `{market_id, timeframe, candle_time}` — a market
    163 has exactly one candle per timeframe per time bucket; primary key '''`id`''', the
    164 composite is enforced as a uniqueness rule because it is the real-world
    165 constraint and it is what prevents duplicate candles.
    166 
    167 ||= Attribute =||= Type =||= Constraints =||
    168 || `id` || integer || PK, required, auto-generated ||
     184'''Keys:''' candidate `{id}`; primary key '''`id`'''. '''Uniqueness rule:''' a market
     185has exactly one candle per timeframe per time bucket, so the market a candle
     186`Aggregates` together with `timeframe` and `candle_time` also identifies it.
     187This is the real-world constraint that prevents duplicate candles. P2 enforces
     188it as `UNIQUE(market_id, timeframe, candle_time)`.
     189
     190||= Attribute =||= Type =||= Constraints =||
     191|| `id` || big integer || PK, required, auto-generated (`bigserial` in P2) ||
    169192|| `timeframe` || text || required, `1m`, `5m`, `1h` or `1d` ||
    170193|| `open`, `high`, `low`, `close` || numeric(18,6) || all required ||
    … …  
    177200several lists ("long term", "watching today") and each needs its own name.
    178201
    179 '''Keys:''' candidate `{id}` — `{user_id, name}` would also work if list names
    180 were required to be unique per user, which the model does not impose, so it is
    181 not listed as a candidate key; primary key '''`id`'''.
     202'''Keys:''' candidate `{id}`; primary key '''`id`'''. "Owner plus `name`" would
     203also identify a list if names had to be unique per user, but the model does
     204not require that, so there is no uniqueness rule here.
    182205
    183206||= Attribute =||= Type =||= Constraints =||
    … …  
    185208|| `name` || text(100) || required ||
    186209|| `created_at` || timestamptz || required, defaults to now ||
     210
     211==== Holdings ====
     212A user's position in one crypto asset: how much of it the user owns, how much
     213of that is already promised to open sell orders, and at what average price it
     214was accumulated. ''An entity set since v05'' (until v04 it was the M:N
     215relationship `Holds`). A holding has its own identifier and its own
     216lifecycle: it is created on the first buy, updated on every later fill, and
     217the prototype reads and locks it as a unit (`SELECT … FOR UPDATE` on the sell
     218path). It is linked to its owner through `Holds` and to its asset through
     219`PositionIn`.
     220
     221'''Keys:''' candidate `{id}`; primary key '''`id`'''. '''Uniqueness rule:''' a user
     222has at most one holding per crypto, so the user who `Holds` it together with
     223the crypto it is a `PositionIn` also identifies a holding. P2 enforces this as
     224`UNIQUE(user_id, crypto_id)`.
     225
     226`reserved_quantity` mirrors `available_balance`/`invested_balance` on `Users`:
     227two independently updated stored numbers, with the amount actually free to use
     228computed on demand rather than stored (`quantity − reserved_quantity` here,
     229`available_balance` alone on the cash side). Without it, nothing stopped a
     230user from placing a second sell order against crypto already promised to a
     231first one — `quantity` alone cannot tell "owned" apart from "owned, but
     232already committed elsewhere." See history, v03.
     233
     234||= Attribute =||= Type =||= Constraints =||
     235|| `id` || UUID || PK, required ||
     236|| `quantity` || numeric(20,4) || required, ≥ 0 — total amount owned ||
     237|| `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 ||
     238|| `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 ||
     239|| `created_at` || timestamptz || required, defaults to now ||
     240|| `updated_at` || timestamptz || optional ||
     241
     242==== !WatchlistItems ====
     243One asset placed on one watchlist. ''An entity set since v05'' (until v04 it was
     244the M:N relationship `Contains`). It has its own identifier, and it is linked
     245to its list through `Contains` and to its asset through `Lists`.
     246
     247'''Keys:''' candidate `{id}`; primary key '''`id`'''. '''Uniqueness rule:''' an asset
     248appears at most once on a given list, so the watchlist that `Contains` an item
     249together with the crypto it `Lists` also identifies the item. P2 enforces this
     250as `UNIQUE(watchlist_id, crypto_id)`.
     251
     252||= Attribute =||= Type =||= Constraints =||
     253|| `id` || UUID || PK, required ||
     254|| `added_at` || timestamptz || required, defaults to now — recorded so a list can be shown in the order the user built it ||
    187255
    188256=== Relationships ===
    … …  
    215283Every executed trade happened on exactly one market. No attributes.
    216284
     285==== !FillsBuy — Orders (1) : !MarketTrades (N), partial on both sides ====
     286''Added in v04, after P7.'' The buy order a trade filled. An order can be
     287filled by many trades (partial fills); a trade fills at most one buy order,
     288and none when the simulated market was the buyer. The role of `Orders` in this
     289relationship is ''the buy order'' of the trade. No attributes.
     290
     291==== !FillsSell — Orders (1) : !MarketTrades (N), partial on both sides ====
     292''Added in v04, after P7.'' The sell order a trade filled, symmetric to
     293`FillsBuy`; the role of `Orders` here is ''the sell order'' of the trade. A
     294trade between two users' orders participates in both. No attributes.
     295
     296==== Logs — Orders (1) : !OrderEvents (N), total on !OrderEvents ====
     297''Added in v04, after P7.'' Every event belongs to exactly one order. No
     298attributes.
     299
    217300==== Aggregates — Markets (1) : !MarketCandles (N), total on !MarketCandles ====
    218301Every candle summarises trades of exactly one market. No attributes.
    … …  
    221304Every watchlist belongs to exactly one user. No attributes.
    222305
    223 ==== Holds — Users (M) : Cryptos (N), partial on both sides, '''with attributes''' ====
    224 A user's position in an asset. M:N because one user holds many assets and one
    225 asset is held by many users, and partial on both sides because a user may hold
    226 nothing and an asset may be held by nobody. Modeled as a relationship rather
    227 than an entity set because a position has no identity of its own — it is
    228 entirely described by ''which user'', ''which asset'', and how much.
    229 
    230 `reserved_quantity` mirrors `available_balance`/`invested_balance` on `Users`:
    231 two independently updated stored numbers, with the amount actually free to use
    232 computed on demand rather than stored (`quantity − reserved_quantity` here,
    233 `available_balance` alone on the cash side). Without it, nothing stopped a
    234 user from placing a second sell order against crypto already promised to a
    235 first one — `quantity` alone cannot tell "owned" apart from "owned, but
    236 already committed elsewhere." See the history section below, v03.
    237 
    238 ||= Attribute =||= Type =||= Constraints =||
    239 || `quantity` || numeric(20,4) || required, ≥ 0 — total amount owned ||
    240 || `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 ||
    241 || `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 ||
    242 || `created_at` || timestamptz || required, defaults to now ||
    243 || `updated_at` || timestamptz || optional ||
    244 
    245 ==== Contains — Watchlists (M) : Cryptos (N), partial on both sides, '''with attribute''' ====
    246 Which assets are on which watchlist. M:N: a list holds many assets, an asset
    247 appears on many lists. Partial on both sides — an empty list is valid and an
    248 asset need not be on any list.
    249 
    250 ||= Attribute =||= Type =||= Constraints =||
    251 || `added_at` || timestamptz || required, defaults to now — recorded so a list can be shown in the order the user built it ||
     306==== Holds — Users (1) : Holdings (N), total on Holdings ====
     307''1:N since v05.'' Every holding belongs to exactly one user. A user may hold
     308nothing yet, so participation is partial on the `Users` side. No attributes.
     309
     310==== !PositionIn — Cryptos (1) : Holdings (N), total on Holdings ====
     311''Added in v05.'' Every holding is a position in exactly one crypto asset. An
     312asset may be held by nobody. No attributes.
     313
     314Together, `Holds` and `PositionIn` still say what the old M:N `Holds` said:
     315a user can hold many assets and an asset can be held by many users. The
     316difference is that the position is now a thing with its own identity, not
     317just a pair. The rule "at most one holding per user and crypto" is stated
     318under Holdings.
     319
     320==== Contains — Watchlists (1) : !WatchlistItems (N), total on !WatchlistItems ====
     321''1:N since v05.'' Every watchlist item is on exactly one list. An empty list is
     322valid, so participation is partial on the `Watchlists` side. No attributes.
     323
     324==== Lists — Cryptos (1) : !WatchlistItems (N), total on !WatchlistItems ====
     325''Added in v05.'' Every watchlist item names exactly one crypto asset. An asset
     326need not be on any list. No attributes.
    252327
    253328== Entity-Relationship Model History ==
    254329
    255  * '''v01''' — First complete version. Built from the entity notes in
    256    `ep-diagram.md` (the initial hand-written model), with three
    257    changes made to that initial model while drawing it:
    258    1. `Markets` was promoted from an implied attribute of the asset to its own
    259       entity set, so that prices, orders, trades and candles can all reference a
    260       pair rather than an asset.
    261    2. `holdings` and `watchlist_items` were re-expressed as the M:N relationships
    262       `Holds` and `Contains` with their own attributes, instead of entity sets
    263       with foreign keys — the initial notes listed them as tables, which is a
    264       relational concept that does not belong in a Chen ERD.
    265    3. `avg_price` was marked as a derived attribute rather than a plain one, to
    266       make the denormalisation explicit rather than hidden.
     330 * '''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:
     331   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.
     332   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.
     333   3. `avg_price` was marked as a derived attribute rather than a plain one, to make the denormalisation explicit rather than hidden.
    267334 * '''v02''' — Student review pass over the AI-generated v01 in the TerraER GUI.
    268  * '''v03''' — Added `reserved_quantity` to `Holds`, and reworded `Orders.status`
    269    to state its reserve → settle → (cancel) lifecycle explicitly, instead of
    270    leaving `open`/`cancelled` as unused enum values. Triggered by a design
    271    review that pointed out the model had no way to stop a user from placing a
    272    second sell order against crypto already promised to a first, unsettled one
    273    — `quantity` alone cannot distinguish "owned" from "owned, but already
    274    committed." Also redrawn more compactly: every entity and relationship (with
    275    its own attributes moved along with it) was pulled proportionally toward the
    276    diagram's centroid, shrinking the canvas by roughly 45% with the same
    277    topology and no new overlaps. See ERModelAIUsage for
    278    the reasoning and how the diagram file itself was produced, and
    279    !RelationalDesign and
    280    UseCase0005 for how the new attribute
    281    is enforced.
     335 * '''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.
     336 * '''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:
     337   * `Users.reserved_balance`: cash reserved by open buy orders;
     338   * `Orders.filled_quantity` and the status value `partially_filled`: orders can now be filled in parts;
     339   * the relationships `FillsBuy` and `FillsSell` between `Orders` and `MarketTrades`: which orders a trade filled;
     340   * the entity set `OrderEvents` with the relationship `Logs`: the automatically recorded history of every order.
     341
     342  Nothing existing was removed or changed. See
     343  [wiki:AdvancedDatabaseDevelopment].
     344  The diagram files are `ERModel_v04.xml` / `ERModel_v04.png`.
     345 * '''v05 — correction after review.''' The review of P2 found that two parts of the model were implemented differently in the database:
     346   * `Contains` was an M:N relationship in the model, but `watchlist_items` has its own `id` primary key;
     347   * `Holds` was an M:N relationship in the model, but `holdings` has its own `id` primary key.
     348
     349  An M:N relationship has no identifier of its own; its table's key is the pair
     350  of participating keys. A table with its own `id` is the implementation of an
     351  entity set. Every phase after P2 (the prototype, the reports and the P7
     352  logic) already uses the database as it is. So the '''model''' was corrected to
     353  match P2, not the other way round:
     354   * `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;
     355   * `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;
     356   * 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");
     357   * 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;
     358   * 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.
     359
     360  The diagram files are `ERModel_v05.xml` / `ERModel_v05.png`; earlier versions
     361  are kept.
    282362
    283363Reasoning for the AI-assisted part of this phase, and the full interaction log,
    284 are on ERModelAIUsage.
    285 
    286 > '''Student action required.''' Open `ERModel_v03.xml` in TerraER, read the
    287 > whole diagram — not just the new `reserved_quantity` ellipse — and change
    288 > anything you disagree with, including the compaction. The phase rules
    289 > require the model to be yours; this is a generated revision to review and
    290 > take over, not an answer to submit unread.
     364are on [wiki:ERModelAIUsage].
     365