| 1 | | == Diagram |
| 2 | | |
| 3 | | [[Image(https://github.com/StefanTrsunov/bp/blob/main/docs/P1-ConceptualModel/ERModel_v01.png)]] |
| 4 | | |
| 5 | | == Data requirements |
| 6 | | |
| 7 | | === Entity sets |
| 8 | | |
| 9 | | ==== Users |
| | 1 | = Entity-Relationship Model v.03 = |
| | 2 | |
| | 3 | == Diagram == |
| | 4 | |
| | 5 | [[Image(ERModel_v03.png)]] |
| | 6 | |
| | 7 | Notation: Chen. Rectangles are entity sets, diamonds are relationships, ellipses |
| | 8 | are attributes, underlined ellipses are primary keys, the dashed ellipse is a |
| | 9 | derived attribute. A double line between an entity set and a relationship marks |
| | 10 | '''total participation''' (every instance of that entity set must participate); a |
| | 11 | single line marks partial participation. |
| | 12 | |
| | 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. |
| | 21 | |
| | 22 | == Data requirements == |
| | 23 | |
| | 24 | Each entity set is given as a short rationale for why it exists as its own set, |
| | 25 | its keys, and its attributes as a table. Each relationship is given as its |
| | 26 | cardinality and participation, a short rationale, and — where it carries data — |
| | 27 | an attribute table. |
| | 28 | |
| | 29 | === Entity sets === |
| | 30 | |
| | 31 | ==== Users ==== |
| 12 | | simulation work: cash that is free to trade is tracked separately from cash that |
| 13 | | is currently committed to open positions, so the platform can refuse a purchase |
| 14 | | |
| 15 | | |
| 16 | | - **Candidate keys:** `{id}`, `{username}`, `{email}`. Primary key: **`id`**. |
| 17 | | A surrogate UUID was chosen because it is opaque and stable — `username` and |
| 18 | | `email` are both things a user may legitimately want to change later, and |
| 19 | | every relationship in the diagram points at `Users`, so a mutable key would |
| 20 | | propagate changes across the whole database. |
| 21 | | - **Attributes:** |
| 22 | | - `id` — UUID, required, primary key. |
| 23 | | - `username` — text, max 50, required, unique. |
| 24 | | - `email` — text, max 255, required, unique, must contain `@`. |
| 25 | | - `full_name` — text, max 200, optional. |
| 26 | | - `password_hash` — text, max 255, required. Never the password itself; the |
| 27 | | prototype stores a SHA-256 hex digest. |
| 28 | | - `available_balance` — numeric(18,4), required, default 0, must be ≥ 0. |
| 29 | | - `invested_balance` — numeric(18,4), required, default 0, must be ≥ 0. |
| 30 | | - `created_at` — timestamp with time zone, required, defaults to now. |
| 31 | | - `updated_at` — timestamp with time zone, optional (null until first change). |
| 32 | | |
| 33 | | ==== Cryptos |
| | 34 | simulation work: cash that is free to trade is tracked separately from cash |
| | 35 | that is currently committed to open positions, so the platform can refuse a |
| | 36 | purchase without having to recompute the whole portfolio first. |
| | 37 | |
| | 38 | '''Keys:''' candidates `{id}`, `{username}`, `{email}`; primary key '''`id`'''. A |
| | 39 | surrogate UUID was chosen because it is opaque and stable — `username` and |
| | 40 | `email` are both things a user may legitimately want to change later, and |
| | 41 | every relationship in the diagram points at `Users`, so a mutable key would |
| | 42 | propagate changes across the whole database. |
| | 43 | |
| | 44 | ||= Attribute =||= Type =||= Constraints =|| |
| | 45 | || `id` || UUID || PK, required || |
| | 46 | || `username` || text(50) || required, unique || |
| | 47 | || `email` || text(255) || required, unique, contains `@` || |
| | 48 | || `full_name` || text(200) || optional || |
| | 49 | || `password_hash` || text(255) || required — never the password itself; the prototype stores a SHA-256 hex digest || |
| | 50 | || `available_balance` || numeric(18,4) || required, default 0, ≥ 0 || |
| | 51 | || `invested_balance` || numeric(18,4) || required, default 0, ≥ 0 || |
| | 52 | || `created_at` || timestamptz || required, defaults to now || |
| | 53 | || `updated_at` || timestamptz || optional (null until first change) || |
| | 54 | |
| | 55 | ==== Cryptos ==== |
| 37 | | in the *asset*, not in a particular pair. |
| 38 | | |
| 39 | | - **Candidate keys:** `{id}`, `{symbol}`. Primary key: **`id`**, for the same |
| 40 | | reason as in `Users`; `symbol` is kept as a unique natural key because that is |
| 41 | | what users type and see. |
| 42 | | - **Attributes:** |
| 43 | | - `id` — UUID, required, primary key. |
| 44 | | - `symbol` — text, max 20, required, unique (e.g. `BTC`). |
| 45 | | - `name` — text, max 255, required (e.g. `Bitcoin`). |
| 46 | | - `created_at` — timestamptz, required, defaults to now. |
| 47 | | |
| 48 | | ==== Markets |
| | 59 | in the ''asset'', not in a particular pair. |
| | 60 | |
| | 61 | '''Keys:''' candidates `{id}`, `{symbol}`; primary key '''`id`''', for the same |
| | 62 | reason as in `Users`. `symbol` is kept as a unique natural key because that is |
| | 63 | what users type and see. |
| | 64 | |
| | 65 | ||= Attribute =||= Type =||= Constraints =|| |
| | 66 | || `id` || UUID || PK, required || |
| | 67 | || `symbol` || text(20) || required, unique (e.g. `BTC`) || |
| | 68 | || `name` || text(255) || required (e.g. `Bitcoin`) || |
| | 69 | || `created_at` || timestamptz || required, defaults to now || |
| | 70 | |
| | 71 | ==== Markets ==== |
| 55 | | - **Candidate keys:** `{id}`, `{crypto_id, quote_currency}` — that pair is |
| 56 | | unique by definition, since a given asset can only be quoted once per |
| 57 | | currency. Primary key: **`id`**, so that the many entity sets referencing a |
| 58 | | market carry one narrow column instead of a composite key. |
| 59 | | - **Attributes:** |
| 60 | | - `id` — UUID, required, primary key. |
| 61 | | - `quote_currency` — text, exactly 3 characters, required, default `USD`. |
| 62 | | - `is_active` — boolean, required, default true. Inactive markets are hidden |
| 63 | | from the trading menus but keep their history. |
| 64 | | - `created_at` — timestamptz, required, defaults to now. |
| 65 | | |
| 66 | | ==== Orders |
| | 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. |
| | 82 | |
| | 83 | ||= Attribute =||= Type =||= Constraints =|| |
| | 84 | || `id` || UUID || PK, required || |
| | 85 | || `quote_currency` || text(3) || required, default `USD` || |
| | 86 | || `is_active` || boolean || required, default true — inactive markets are hidden from the trading menus but keep their history || |
| | 87 | || `created_at` || timestamptz || required, defaults to now || |
| | 88 | |
| | 89 | ==== Orders ==== |
| 72 | | - **Candidate keys:** `{id}` only. There is no natural key — the same user can |
| 73 | | place two identical orders on the same market in the same second, and both are |
| 74 | | legitimately distinct. Primary key: **`id`**. |
| 75 | | - **Attributes:** |
| 76 | | - `id` — UUID, required, primary key. |
| 77 | | - `side` — text, required, restricted to `buy` or `sell`. |
| 78 | | - `type` — text, required, restricted to `market` or `limit`. The prototype |
| 79 | | executes only `market` orders; `limit` exists so the model does not have to |
| 80 | | change when limit orders are implemented. |
| 81 | | - `status` — text, required, restricted to `open`, `executed`, `cancelled`. |
| 82 | | - `quantity` — numeric(20,4), required, must be > 0. |
| 83 | | - `price` — numeric(18,6), optional — null for a market order until it fills, |
| 84 | | then the fill price. |
| 85 | | - `placed_at` — timestamptz, required, defaults to now. |
| 86 | | - `executed_at` — timestamptz, optional, set when the order fills. |
| 87 | | |
| 88 | | ==== Transactions |
| | 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. |
| | 105 | |
| | 106 | '''Keys:''' candidate `{id}` only — there is no natural key, since the same user |
| | 107 | can place two identical orders on the same market in the same second, and both |
| | 108 | are legitimately distinct; primary key '''`id`'''. |
| | 109 | |
| | 110 | ||= Attribute =||= Type =||= Constraints =|| |
| | 111 | || `id` || UUID || PK, required || |
| | 112 | || `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` || |
| | 115 | || `quantity` || numeric(20,4) || required, > 0 || |
| | 116 | || `price` || numeric(18,6) || optional — null until the order settles, then the fill price || |
| | 117 | || `placed_at` || timestamptz || required, defaults to now || |
| | 118 | || `executed_at` || timestamptz || optional, set when the order settles || |
| | 119 | |
| | 120 | ==== Transactions ==== |
| 94 | | - **Candidate keys:** `{id}` only. Primary key: **`id`**. |
| 95 | | - **Attributes:** |
| 96 | | - `id` — UUID, required, primary key. |
| 97 | | - `type` — text, required, restricted to `deposit`, `buy`, `sell`, `fee`. |
| 98 | | - `amount` — numeric(18,4), required. Signed: negative for money leaving the |
| 99 | | cash balance, positive for money arriving. |
| 100 | | - `currency` — text, exactly 3 characters, required, default `USD`. |
| 101 | | - `created_at` — timestamptz, required, defaults to now. |
| 102 | | - `description` — text, optional, free-form human-readable explanation. |
| 103 | | |
| 104 | | ==== MarketTrades |
| | 126 | '''Keys:''' candidate `{id}` only; primary key '''`id`'''. |
| | 127 | |
| | 128 | ||= Attribute =||= Type =||= Constraints =|| |
| | 129 | || `id` || UUID || PK, required || |
| | 130 | || `type` || text || required, `deposit`, `buy`, `sell` or `fee` || |
| | 131 | || `amount` || numeric(18,4) || required, signed — negative for money leaving the cash balance, positive for money arriving || |
| | 132 | || `currency` || text(3) || required, default `USD` || |
| | 133 | || `created_at` || timestamptz || required, defaults to now || |
| | 134 | || `description` || text || optional, free-form || |
| | 135 | |
| | 136 | ==== !MarketTrades ==== |
| 110 | | - **Candidate keys:** `{id}`. In principle `{market_id, executed_at}` looks |
| 111 | | unique, but two trades can share a timestamp, so it is not a safe key. |
| 112 | | Primary key: **`id`** (a plain auto-incrementing integer here rather than a |
| 113 | | UUID, because this is the highest-volume entity set and it is only ever read |
| 114 | | in timestamp order, never referenced by anything else). |
| 115 | | - **Attributes:** |
| 116 | | - `id` — integer, required, primary key, auto-generated. |
| 117 | | - `executed_at` — timestamptz, required. |
| 118 | | - `price` — numeric(18,6), required, must be > 0. |
| 119 | | - `quantity` — numeric(20,6), required, must be > 0. |
| 120 | | - `side` — text, optional, `buy` or `sell`. |
| 121 | | - `source` — text, max 50, required, default `simulation`. Distinguishes a |
| 122 | | simulated trade from a user's own fill (`user`). |
| 123 | | |
| 124 | | ===== MarketCandles |
| | 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 | primary key '''`id`''' (a plain auto-incrementing integer here rather than a |
| | 145 | UUID, because this is the highest-volume entity set and it is only ever read |
| | 146 | in timestamp order, never referenced by anything else). |
| | 147 | |
| | 148 | ||= Attribute =||= Type =||= Constraints =|| |
| | 149 | || `id` || integer || PK, required, auto-generated || |
| | 150 | || `executed_at` || timestamptz || required || |
| | 151 | || `price` || numeric(18,6) || required, > 0 || |
| | 152 | || `quantity` || numeric(20,6) || required, > 0 || |
| | 153 | || `side` || text || optional, `buy` or `sell` || |
| | 154 | || `source` || text(50) || required, default `simulation` — distinguishes a simulated trade from a user's own fill (`user`) || |
| | 155 | |
| | 156 | ==== !MarketCandles ==== |
| 130 | | - **Candidate keys:** `{id}`, and `{market_id, timeframe, candle_time}` — a |
| 131 | | market has exactly one candle per timeframe per time bucket. Primary key: |
| 132 | | **`id`**; the composite is enforced as a uniqueness rule because it is the |
| 133 | | real-world constraint and it is what prevents duplicate candles. |
| 134 | | - **Attributes:** |
| 135 | | - `id` — integer, required, primary key, auto-generated. |
| 136 | | - `timeframe` — text, required, restricted to `1m`, `5m`, `1h`, `1d`. |
| 137 | | - `open`, `high`, `low`, `close` — numeric(18,6), all required. |
| 138 | | - `volume` — numeric(20,6), required. |
| 139 | | - `candle_time` — timestamptz, required — the start of the bucket. |
| 140 | | |
| 141 | | ===== Watchlists |
| | 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 || |
| | 169 | || `timeframe` || text || required, `1m`, `5m`, `1h` or `1d` || |
| | 170 | || `open`, `high`, `low`, `close` || numeric(18,6) || all required || |
| | 171 | || `volume` || numeric(20,6) || required || |
| | 172 | || `candle_time` || timestamptz || required — the start of the bucket || |
| | 173 | |
| | 174 | ==== Watchlists ==== |
| 146 | | - **Candidate keys:** `{id}`. `{user_id, name}` would also work if list names |
| 147 | | are required to be unique per user; the model does not impose that, so it is |
| 148 | | not listed as a candidate key. Primary key: **`id`**. |
| 149 | | - **Attributes:** |
| 150 | | - `id` — UUID, required, primary key. |
| 151 | | - `name` — text, max 100, required. |
| 152 | | - `created_at` — timestamptz, required, defaults to now. |
| 153 | | |
| 154 | | ==== Relationships |
| 155 | | |
| 156 | | ===== QuotedOn — Cryptos (1) : Markets (N), total on Markets |
| | 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`'''. |
| | 182 | |
| | 183 | ||= Attribute =||= Type =||= Constraints =|| |
| | 184 | || `id` || UUID || PK, required || |
| | 185 | || `name` || text(100) || required || |
| | 186 | || `created_at` || timestamptz || required, defaults to now || |
| | 187 | |
| | 188 | === Relationships === |
| | 189 | |
| | 190 | ==== !QuotedOn — Cryptos (1) : Markets (N), total on Markets ==== |
| 165 | | ===== Places — Users (1) : Orders (N), total on Orders |
| 166 | | Records who placed an order. Every order belongs to exactly one user; a new user |
| 167 | | has no orders. No attributes. |
| 168 | | |
| 169 | | ===== Records — Users (1) : Transactions (N), total on Transactions |
| 170 | | Attributes each ledger entry to a user. Every entry belongs to exactly one user. |
| 171 | | No attributes. |
| 172 | | |
| 173 | | ===== Settles — Orders (1) : Transactions (N), partial on both sides |
| 174 | | Links a ledger entry to the order that caused it. Partial on the `Transactions` |
| 175 | | side because deposits have no originating order, and partial on the `Orders` side |
| 176 | | because an order that never executes never produces a ledger entry. This is why |
| 177 | | the corresponding column is nullable in P2. No attributes. |
| 178 | | |
| 179 | | ===== Fills — Markets (1) : MarketTrades (N), total on MarketTrades |
| | 199 | ==== Places — Users (1) : Orders (N), total on Orders ==== |
| | 200 | Records who placed an order. Every order belongs to exactly one user; a new |
| | 201 | user has no orders. No attributes. |
| | 202 | |
| | 203 | ==== Records — Users (1) : Transactions (N), total on Transactions ==== |
| | 204 | Attributes each ledger entry to a user. Every entry belongs to exactly one |
| | 205 | user. No attributes. |
| | 206 | |
| | 207 | ==== Settles — Orders (1) : Transactions (N), partial on both sides ==== |
| | 208 | Links a ledger entry to the order that caused it. Partial on the |
| | 209 | `Transactions` side because deposits have no originating order, and partial on |
| | 210 | the `Orders` side because an order that never executes never produces a |
| | 211 | ledger entry — which is why the corresponding column is nullable in P2. No |
| | 212 | attributes. |
| | 213 | |
| | 214 | ==== Fills — Markets (1) : !MarketTrades (N), total on !MarketTrades ==== |
| 193 | | entirely described by *which user*, *which asset*, and how much. |
| 194 | | |
| 195 | | - **Attributes:** |
| 196 | | - `quantity` — numeric(20,4), required, must be ≥ 0. |
| 197 | | - `avg_price` — numeric(18,6), required, ≥ 0, **derived** (dashed ellipse): |
| 198 | | the weighted average of the prices at which the position was accumulated. |
| 199 | | It is derivable from the buy history, and is stored anyway so that |
| 200 | | unrealised P/L can be shown without replaying the whole ledger. |
| 201 | | - `created_at` — timestamptz, required, defaults to now. |
| 202 | | - `updated_at` — timestamptz, optional. |
| 203 | | |
| 204 | | ===== Contains — Watchlists (M) : Cryptos (N), partial on both sides, **with attribute** |
| | 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''' ==== |
| 209 | | - **Attributes:** |
| 210 | | - `added_at` — timestamptz, required, defaults to now. Recorded so a list can |
| 211 | | be shown in the order the user built it. |
| 212 | | |
| 213 | | === Entity-Relationship Model History |
| 214 | | |
| 215 | | - **v01** — First complete version. Built from the entity notes in |
| 216 | | [`ep-diagram.md`](ep-diagram.md) (the initial hand-written model), with three |
| 217 | | changes made to that initial model while drawing it: |
| 218 | | 1. `Markets` was promoted from an implied attribute of the asset to its own |
| 219 | | entity set, so that prices, orders, trades and candles can all reference a |
| 220 | | pair rather than an asset. |
| 221 | | 2. `holdings` and `watchlist_items` were re-expressed as the M:N relationships |
| 222 | | `Holds` and `Contains` with their own attributes, instead of entity sets |
| 223 | | with foreign keys — the initial notes listed them as tables, which is a |
| 224 | | relational concept that does not belong in a Chen ERD. |
| 225 | | 3. `avg_price` was marked as a derived attribute rather than a plain one, to |
| 226 | | make the denormalisation explicit rather than hidden. |
| | 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 || |
| | 252 | |
| | 253 | == Entity-Relationship Model History == |
| | 254 | |
| | 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. |
| | 267 | * '''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. |
