source: docs/P1-ConceptualModel/ERModel.md

# Entity-Relationship Model v.05

## Diagram

![ERModel_v05](ERModel_v05.png)

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](../P2-RelationalDesign/RelationalDesign.md).
- **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

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](../P3-UseCaseModel/UseCase0005.md) for the reserve-then-settle
sequence and
[AdvancedDatabaseDevelopment](../P7-AdvancedDatabaseDevelopment/AdvancedDatabaseDevelopment.md)
for the rules that keep it consistent.

**Keys:** candidate `{id}` only — there is no natural key, since the same user
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](#entity-relationship-model-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](#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`](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](ERModelAIUsage.md) for
  the reasoning and how the diagram file itself was produced, and
  [RelationalDesign](../P2-RelationalDesign/RelationalDesign.md) and
  [UseCase0005](../P3-UseCaseModel/UseCase0005.md) for how the new attribute
  is enforced.
- **v04 — after P7.** Phase 7 (order, balance and trade consistency) needed
  data the model did not have, so the model was extended to stay in line with
  the database:
  - `Users.reserved_balance`: cash reserved by open buy orders;
  - `Orders.filled_quantity` and the status value `partially_filled`: orders
    can now be filled in parts;
  - the relationships `FillsBuy` and `FillsSell` between `Orders` and
    `MarketTrades`: which orders a trade filled;
  - the entity set `OrderEvents` with the relationship `Logs`: the
    automatically recorded history of every order.

  Nothing existing was removed or changed. See
  [AdvancedDatabaseDevelopment](../P7-AdvancedDatabaseDevelopment/AdvancedDatabaseDevelopment.md).
  The diagram files are `ERModel_v04.xml` / `ERModel_v04.png`.
- **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](ERModelAIUsage.md).