Entity-Relationship Model v.05

Diagram

Notation: Chen. Rectangles are entity sets, diamonds are relationships, ellipses are attributes, underlined ellipses are primary keys, the dashed ellipse is a derived attribute. A double line between an entity set and a relationship marks total participation (every instance of that entity set must participate); a single line marks partial participation.

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.
• 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

Each entity set is given as a short rationale for why it exists as its own set, its keys, and its attributes as a table. Each relationship is given as its cardinality and participation, a short rationale, and — where it carries data — an attribute table.

Entity sets

Users

Registered participants of the platform. Every action in the simulation is attributed to a user, and the two balance attributes are what makes the simulation work: cash that is free to trade is tracked separately from cash that is currently committed to open positions, so the platform can refuse a purchase without having to recompute the whole portfolio first.

Keys: candidates {id}, {username}, {email}; primary key id. A surrogate UUID was chosen because it is opaque and stable — username and email are both things a user may legitimately want to change later, and every relationship in the diagram points at Users, so a mutable key would propagate changes across the whole database.

Attribute	Type	Constraints
id	UUID	PK, required
username	text(50)	required, unique
email	text(255)	required, unique, contains @ (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
available_balance	numeric(18,4)	required, default 0, ≥ 0
invested_balance	numeric(18,4)	required, default 0, ≥ 0
reserved_balance	numeric(18,4)	required, default 0, ≥ 0 — cash set aside for the user's open buy orders (added in v04, after P7)
created_at	timestamptz	required, defaults to now
updated_at	timestamptz	optional (null until first change)

Cryptos

The catalog of crypto assets the platform knows about. Kept separate from Markets because an asset exists independently of the pairs it is traded in — the same asset can be quoted against several currencies, and a user's holding is in the asset, not in a particular pair.

Keys: candidates {id}, {symbol}; primary key id, for the same reason as in Users. symbol is kept as a unique natural key because that is what users type and see.

Attribute	Type	Constraints
id	UUID	PK, required
symbol	text(20)	required, unique (e.g. BTC)
name	text(255)	required (e.g. Bitcoin)
created_at	timestamptz	required, defaults to now

Markets

A tradeable pair: one crypto asset quoted in one currency, e.g. BTC/USD. This is where prices live, and it is the thing an order is placed on. Modeled as its own entity set rather than an attribute of Cryptos because a market has its own lifecycle — it can be deactivated without deleting the asset — and because trades, candles and orders all reference the pair, not the asset.

Keys: 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
id	UUID	PK, required
quote_currency	text(3)	required, default USD
is_active	boolean	required, default true — inactive markets are hidden from the trading menus but keep their history
created_at	timestamptz	required, defaults to now

Orders

A user's instruction to buy or sell on a market. Needed as a separate entity set because an order is a record of intent that outlives its execution: it keeps the requested quantity and price even after it has been filled, which is what makes the ledger auditable.

Placing an order is what triggers a reservation of whatever it commits: the crypto being sold (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 lifecycle driven by filled_quantity: open (nothing filled yet), partially_filled, executed (completely filled), or cancelled, which releases what is still reserved. See UseCase0005 for the reserve-then-settle sequence and AdvancedDatabaseDevelopment for the rules that keep it consistent.

Keys: candidate {id} only — there is no natural key, since the same user can place two identical orders on the same market in the same second, and both are legitimately distinct; primary key id.

Attribute	Type	Constraints
id	UUID	PK, required
side	text	required, buy or sell
type	text	required, market or limit (both executed since P7)
status	text	required, open, partially_filled, executed or cancelled
quantity	numeric(20,4)	required, > 0
filled_quantity	numeric(20,4)	required, default 0, between 0 and quantity — how much has been traded; remaining = quantity − filled_quantity (added in v04, after P7)
price	numeric(18,6)	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

Transactions

The financial ledger: every movement of virtual cash, in one place. This exists so that a balance is never just a number someone edited — it is the sum of an auditable list of entries, which is also what the "explain every step" goal of the project needs.

Keys: candidate {id} only; primary key id.

Attribute	Type	Constraints
id	UUID	PK, required
type	text	required, deposit, buy, sell or fee
amount	numeric(18,4)	required, signed — negative for money leaving the cash balance, positive for money arriving
currency	text(3)	required, default USD
created_at	timestamptz	required, defaults to now
description	text	optional, free-form

MarketTrades

Individual executed trades on a market, from the user's own fills and from the market simulator. This is the single source of truth for the current price: the price of a market is the price of its most recent trade, never a column someone writes directly.

Keys: candidate {id} — "market 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 in timestamp order, never referenced by anything else).

Attribute	Type	Constraints
id	big integer	PK, required, auto-generated (bigserial in P2)
executed_at	timestamptz	required
price	numeric(18,6)	required, > 0
quantity	numeric(20,6)	required, > 0
side	text	optional, buy or sell
source	text(50)	required, default simulation — distinguishes a simulated trade from a user's own fill (user)

Since v04 (after P7) a trade also records which orders it filled, through the relationships FillsBuy and FillsSell below.

OrderEvents

Added in v04, after P7. The audit trail of an order: one event for its placement, one for every (partial) fill, and one for a cancellation. The Orders row only holds the current state; this entity keeps the history of how the order got there. Events are recorded automatically by the database.

Keys: candidate {id} only; primary key id (auto-incrementing integer, events are only read in order).

Attribute	Type	Constraints
id	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, set automatically when the event is recorded (clock_timestamp(), so events inside one transaction keep their real order)

MarketCandles

OHLCV aggregates per market and timeframe — the data a price chart is drawn from. Stored rather than computed on the fly because the point of the project is a chart-driven interface, and re-aggregating the whole trade history for every screen refresh does not scale.

Keys: 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
volume	numeric(20,6)	required
candle_time	timestamptz	required — the start of the bucket

Watchlists

A named list of assets a user wants to monitor. A separate entity set rather than a flag on the relationship between users and assets, because a user may want several lists ("long term", "watching today") and each needs its own name.

Keys: candidate {id}; 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
id	UUID	PK, required
name	text(100)	required
created_at	timestamptz	required, defaults to now

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: two independently updated stored numbers, with the amount actually free to use computed on demand rather than stored (quantity − reserved_quantity here, available_balance alone on the cash side). Without it, nothing stopped a user from placing a second sell order against crypto already promised to a first one — quantity alone cannot tell "owned" apart from "owned, but already committed elsewhere." See history, v03.

Attribute	Type	Constraints
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, 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

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

• v01 — First complete version. Built from the entity notes in ep-diagram.md (the initial hand-written model), with three changes made to that initial model while drawing it:
  1. Markets was promoted from an implied attribute of the asset to its own entity set, so that prices, orders, trades and candles can all reference a pair rather than an asset.
  2. holdings and watchlist_items were re-expressed as the M:N relationships Holds and Contains with their own attributes, instead of entity sets with foreign keys — the initial notes listed them as tables, which is a relational concept that does not belong in a Chen ERD.
  3. avg_price was marked as a derived attribute rather than a plain one, to make the denormalisation explicit rather than hidden.
• v02 — Student review pass over the AI-generated v01 in the TerraER GUI.
• v03 — Added reserved_quantity to Holds, and reworded Orders.status to state its reserve → settle → (cancel) lifecycle explicitly, instead of leaving open/cancelled as unused enum values. Triggered by a design review that pointed out the model had no way to stop a user from placing a second sell order against crypto already promised to a first, unsettled one — quantity alone cannot distinguish "owned" from "owned, but already committed." Also redrawn more compactly: every entity and relationship (with its own attributes moved along with it) was pulled proportionally toward the diagram's centroid, shrinking the canvas by roughly 45% with the same topology and no new overlaps. See ERModelAIUsage for the reasoning and how the diagram file itself was produced, and RelationalDesign and UseCase0005 for how the new attribute is enforced.
• v04 — after P7. Phase 7 (order, balance and trade consistency) needed data the model did not have, so the model was extended to stay in line with the database:
  • Users.reserved_balance: cash reserved by open buy orders;
  • Orders.filled_quantity and the status value partially_filled: orders can now be filled in parts;
  • the relationships FillsBuy and FillsSell between Orders and MarketTrades: which orders a trade filled;
  • the entity set OrderEvents with the relationship Logs: the automatically recorded history of every order.

Nothing existing was removed or changed. See AdvancedDatabaseDevelopment. 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.

Reasoning for the AI-assisted part of this phase, and the full interaction log, are on ERModelAIUsage.