Index: docs/P2-RelationalDesign/RelationalDesign.md =================================================================== --- docs/P2-RelationalDesign/RelationalDesign.md (revision 4438e45b25e30493554075850d48b2dd419f570b) +++ docs/P2-RelationalDesign/RelationalDesign.md (revision 0cee8ec194e8e518624fcec244168861a6a7de27) @@ -148,5 +148,5 @@ 2. Right-click the database → **ERD For Database** (or open a blank ERD and drag the `project` tables in). -3. Arrange the tables to mirror `ERModel_v01.png`. +3. Arrange the tables to mirror `ERModel_v03.png`. 4. **Download image** → PNG, then convert: `convert relational_schema.png relational_schema.jpg` Index: docs/P2-RelationalDesign/wiki/RelationalDesign.md =================================================================== --- docs/P2-RelationalDesign/wiki/RelationalDesign.md (revision 0cee8ec194e8e518624fcec244168861a6a7de27) +++ docs/P2-RelationalDesign/wiki/RelationalDesign.md (revision 0cee8ec194e8e518624fcec244168861a6a7de27) @@ -0,0 +1,506 @@ += Relational Design = + +== Descriptive representation of the relational schema == + +Notation: '''bold''' = primary key, ''italic'' = foreign key. + + * '''Users'''(__'''id'''__, username, email, full_name, password_hash, available_balance, invested_balance, created_at, updated_at) + * Candidate keys: `{id}`, `{username}`, `{email}`. `UNIQUE(username)`, `UNIQUE(email)`. + * '''Crypto'''(__'''id'''__, symbol, name, created_at) + * Candidate keys: `{id}`, `{symbol}`. `UNIQUE(symbol)`. + * '''Markets'''(__'''id'''__, ''crypto_id'', quote_currency, is_active, created_at) + * Candidate keys: `{id}`, `{crypto_id, quote_currency}`. `UNIQUE(crypto_id, quote_currency)`. + * '''Holdings'''(__'''id'''__, ''user_id'', ''crypto_id'', quantity, reserved_quantity, avg_price, created_at, updated_at) + * Transformation of the M:N relationship `Holds`. Candidate keys: `{id}` and + `{user_id, crypto_id}` — the latter is the relationship's own key and is + enforced with `UNIQUE(user_id, crypto_id)`. `id` was chosen as PK for + consistency with the other relations. + * `avg_price` is `NOT NULL DEFAULT 0 CHECK (avg_price >= 0)`. + * `reserved_quantity` is `NOT NULL DEFAULT 0 CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` — the amount already committed to the + user's own open sell orders. `quantity - reserved_quantity` (the amount + actually free to sell) is not a stored column; it is computed wherever + needed, in `v_portfolio` as `available_quantity` and in the sell path of + [wiki:UseCase0005]. See the `Holds` section of [wiki:ERModel] + for why this mirrors `available_balance`/`invested_balance` on `Users`. + * '''Orders'''(__'''id'''__, ''user_id'', ''market_id'', side, type, status, quantity, price, placed_at, executed_at) + * `side ∈ {buy, sell}`, `type ∈ {market, limit}`, `status ∈ {open, executed, cancelled}`. + * '''Transactions'''(__'''id'''__, ''user_id'', type, amount, currency, ''related_order'', created_at, description) + * `type ∈ {deposit, buy, sell, fee}`. + * '''!MarketTrades'''(__'''id'''__, ''market_id'', executed_at, price, quantity, side, source) + * '''!MarketCandles'''(__'''id'''__, ''market_id'', timeframe, open, high, low, close, volume, candle_time) + * `UNIQUE(market_id, timeframe, candle_time)`. + * '''Watchlists'''(__'''id'''__, ''user_id'', name, created_at) + * '''!WatchlistItems'''(__'''id'''__, ''watchlist_id'', ''crypto_id'', added_at) + * Transformation of the M:N relationship `Contains`. Candidate keys: `{id}` + and `{watchlist_id, crypto_id}`, the latter enforced with + `UNIQUE(watchlist_id, crypto_id)`. + +=== Transformation method used === + +'''Partial transformation.''' Applied as follows: + + * Each of the 8 entity sets in [wiki:ERModel] becomes one table, keeping + its UUID (or serial) primary key. + * Each '''1:N relationship without attributes''' is transformed by adding the + parent's primary key as a foreign-key column on the child table — the "N" + side. This is where every foreign key in the schema comes from, and it is why + no foreign keys appear in the ER diagram itself: + `QuotedOn` → `markets.crypto_id`, `PlacedOn` → `orders.market_id`, + `Places` → `orders.user_id`, `Records` → `transactions.user_id`, + `Settles` → `transactions.related_order`, `Fills` → `market_trades.market_id`, + `Aggregates` → `market_candles.market_id`, `Owns` → `watchlists.user_id`. + * Each '''M:N relationship''' becomes its own table holding the two foreign keys + plus the relationship's own attributes: `Holds` → `holdings`, + `Contains` → `watchlist_items`. The pair of foreign keys is the relationship's + key and is enforced as a `UNIQUE` constraint in both tables. + * '''Total participation''' in the ER model becomes `NOT NULL` on the + corresponding foreign key; partial participation stays nullable. `Settles` is + partial on both sides, which is exactly why `transactions.related_order` is + the one nullable foreign key in the schema — a deposit has no originating + order. + +=== Normalisation === + +> '''Validated in P5.''' Normalization derives this +> exact schema independently — starting only from a single de-normalized relation of every +> model attribute and its functional dependencies, with no reference to the ER-to-relational +> transformation below — and shows it decomposes to '''BCNF''', one normal form stronger than +> the 3NF claimed here. The two designs agree relation for relation and key for key, so +> nothing here changed as a result; see that page's +> discussion section for what the one real +> difference is (`avg_price`, a stored derived value, not a normalisation issue) and why this +> design is still the one used from P5 onward. + +All relations are in '''3NF''': + + * Every attribute is atomic (no repeating groups, no composite fields). + * No partial dependency exists because every primary key is a single UUID column. + * No transitive dependency exists: every non-key attribute depends directly on the row identifier. For example, `holdings.quantity` depends on `holdings.id`, not on `user_id` via some intermediate. + * `avg_price` in `Holdings` is a '''derived value''' cached for performance (it is + the weighted-average entry price across all `buy` transactions for that + `(user, crypto)` pair) — it is drawn as a derived attribute in the ER diagram. + We accept the denormalisation: it is recomputed by the database inside the same + transaction as each buy, in the same statement that changes the quantity + (`INSERT … ON CONFLICT (user_id, crypto_id) DO UPDATE`), so the stored average + and the stored quantity can never disagree. + * `avg_price` is declared `NOT NULL DEFAULT 0`. This matters: it is used in the + P/L arithmetic of `v_portfolio`, and in SQL any arithmetic involving `NULL` + yields `NULL`, so a nullable average would have silently blanked the + unrealised-P/L column for an existing position instead of failing loudly. + * `holdings.reserved_quantity`, unlike `avg_price`, is '''not''' derived — it is + written directly by the application (`trade.go`) as orders are placed and + settled, the same way `quantity` itself is. `quantity - reserved_quantity` + ("available") is the derived value here, and it is never stored, only + computed where it is needed. + +=== Reservation and the order lifecycle === + +`holdings.reserved_quantity` exists so that placing a sell order can be +checked against what a user actually has ''free'' to sell +(`quantity - reserved_quantity`), not against the raw `quantity`, which also +counts crypto already promised to another order that has not settled yet. +`CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` makes an +inconsistent reservation impossible at the database level, regardless of what +application code does. The exact statement sequence — lock the row, check the +available amount, reserve, then settle — is in +[wiki:UseCase0005]; the same +`SELECT … FOR UPDATE` locking that already protected `users.available_balance` +on the buy path is what makes two concurrent sell orders against the same +holding serialize correctly instead of racing. + +== DDL script == + +The script that creates the entire schema is `../server/db/schema_creation.sql` (shown in full below). It is idempotent: it drops and recreates the `project` schema every run, so it works on an empty database and on a database that already has the schema. + +The script creates: + * 10 tables with check constraints, primary keys, foreign keys and unique constraints. + * 5 performance indexes. + * 2 views: `v_latest_prices` (latest trade price per market) and `v_portfolio` (per-user holdings valuation with unrealised P/L, plus `reserved_quantity` and the derived `available_quantity`). + +=== schema_creation.sql === + +The two report functions at the end of the file (`report_top_traders` and `report_market_performance`) belong to Phase 6 ([wiki:AdvancedReports]) and are left out here. + +{{{ +-- schema_creation.sql +-- EduBerza - crypto exchange simulation database +-- Course: Databases 2025/2026 Winter, FINKI UKIM +-- +-- This script is idempotent. It drops the `project` schema and all contained +-- objects, then recreates them from scratch. Safe to run on an empty database +-- or on a database where the schema already exists. + +DROP SCHEMA IF EXISTS project CASCADE; +CREATE SCHEMA project; + +CREATE EXTENSION IF NOT EXISTS pgcrypto; + +SET search_path TO project, public; + +-- ============================================================================ +-- USERS +-- Platform users. Each user has virtual (prop) balances used for simulation. +-- ============================================================================ +CREATE TABLE project.users ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + username varchar(50) NOT NULL UNIQUE, + email varchar(255) NOT NULL UNIQUE, + full_name varchar(200), + password_hash varchar(255) NOT NULL, + available_balance numeric(18,4) NOT NULL DEFAULT 0 CHECK (available_balance >= 0), + invested_balance numeric(18,4) NOT NULL DEFAULT 0 CHECK (invested_balance >= 0), + created_at timestamptz NOT NULL DEFAULT now(), + updated_at timestamptz +); + +-- ============================================================================ +-- CRYPTO +-- Catalog of crypto assets available on the platform. +-- ============================================================================ +CREATE TABLE project.crypto ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + symbol varchar(20) NOT NULL UNIQUE, + name varchar(255) NOT NULL, + created_at timestamptz NOT NULL DEFAULT now() +); + +-- ============================================================================ +-- MARKETS +-- A market is a (crypto, quote_currency) pair, e.g. BTC/USD. +-- ============================================================================ +CREATE TABLE project.markets ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + crypto_id uuid NOT NULL REFERENCES project.crypto(id), + quote_currency char(3) NOT NULL DEFAULT 'USD', + is_active boolean NOT NULL DEFAULT true, + created_at timestamptz NOT NULL DEFAULT now(), + CONSTRAINT uq_markets UNIQUE (crypto_id, quote_currency) +); + +-- ============================================================================ +-- HOLDINGS +-- Per-user crypto position with running weighted average entry price. +-- ============================================================================ +CREATE TABLE project.holdings ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE, + crypto_id uuid NOT NULL REFERENCES project.crypto(id), + quantity numeric(20,4) NOT NULL CHECK (quantity >= 0), + -- Committed to the user's own open sell orders, not yet removed from the + -- position. quantity - reserved_quantity is what is actually free to + -- sell — the crypto-side equivalent of users.available_balance. + reserved_quantity numeric(20,4) NOT NULL DEFAULT 0 + CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity), + -- Weighted-average entry price. NOT NULL so that the P/L arithmetic in + -- v_portfolio can never silently produce NULL for an existing position. + avg_price numeric(18,6) NOT NULL DEFAULT 0 CHECK (avg_price >= 0), + created_at timestamptz NOT NULL DEFAULT now(), + updated_at timestamptz, + CONSTRAINT uq_holdings_user_crypto UNIQUE (user_id, crypto_id) +); + +-- ============================================================================ +-- ORDERS +-- Orders placed by users on a market. +-- ============================================================================ +CREATE TABLE project.orders ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE, + market_id uuid NOT NULL REFERENCES project.markets(id), + side varchar(4) NOT NULL CHECK (side IN ('buy', 'sell')), + type varchar(20) NOT NULL CHECK (type IN ('market', 'limit')), + status varchar(20) NOT NULL CHECK (status IN ('open', 'executed', 'cancelled')), + quantity numeric(20,4) NOT NULL CHECK (quantity > 0), + price numeric(18,6), + placed_at timestamptz NOT NULL DEFAULT now(), + executed_at timestamptz +); + +CREATE INDEX idx_orders_user ON project.orders(user_id); +CREATE INDEX idx_orders_market ON project.orders(market_id); +CREATE INDEX idx_orders_status ON project.orders(status); + +-- ============================================================================ +-- TRANSACTIONS +-- Financial ledger: deposits, buys, sells, fees. +-- ============================================================================ +CREATE TABLE project.transactions ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE, + type varchar(50) NOT NULL CHECK (type IN ('deposit', 'buy', 'sell', 'fee')), + amount numeric(18,4) NOT NULL, + currency char(3) NOT NULL DEFAULT 'USD', + related_order uuid REFERENCES project.orders(id), + created_at timestamptz NOT NULL DEFAULT now(), + description text +); + +CREATE INDEX idx_transactions_user ON project.transactions(user_id, created_at DESC); + +-- ============================================================================ +-- MARKET TRADES +-- Raw executed trades on a market. Source of truth for current price. +-- ============================================================================ +CREATE TABLE project.market_trades ( + id bigserial PRIMARY KEY, + market_id uuid NOT NULL REFERENCES project.markets(id), + executed_at timestamptz NOT NULL, + price numeric(18,6) NOT NULL CHECK (price > 0), + quantity numeric(20,6) NOT NULL CHECK (quantity > 0), + side varchar(4) CHECK (side IN ('buy', 'sell')), + source varchar(50) NOT NULL DEFAULT 'simulation' +); + +CREATE INDEX idx_market_trades_market_time ON project.market_trades(market_id, executed_at DESC); + +-- ============================================================================ +-- MARKET CANDLES +-- OHLCV aggregates over standard timeframes. +-- ============================================================================ +CREATE TABLE project.market_candles ( + id bigserial PRIMARY KEY, + market_id uuid NOT NULL REFERENCES project.markets(id), + timeframe varchar(5) NOT NULL CHECK (timeframe IN ('1m', '5m', '1h', '1d')), + open numeric(18,6) NOT NULL, + high numeric(18,6) NOT NULL, + low numeric(18,6) NOT NULL, + close numeric(18,6) NOT NULL, + volume numeric(20,6) NOT NULL, + candle_time timestamptz NOT NULL, + CONSTRAINT uq_candle UNIQUE (market_id, timeframe, candle_time) +); + +CREATE INDEX idx_market_candles_market_tf_time ON project.market_candles(market_id, timeframe, candle_time DESC); + +-- ============================================================================ +-- WATCHLISTS +-- ============================================================================ +CREATE TABLE project.watchlists ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE, + name varchar(100) NOT NULL, + created_at timestamptz NOT NULL DEFAULT now() +); + +CREATE TABLE project.watchlist_items ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + watchlist_id uuid NOT NULL REFERENCES project.watchlists(id) ON DELETE CASCADE, + crypto_id uuid NOT NULL REFERENCES project.crypto(id), + added_at timestamptz NOT NULL DEFAULT now(), + CONSTRAINT uq_watchlist_crypto UNIQUE (watchlist_id, crypto_id) +); + +-- ============================================================================ +-- VIEWS +-- ============================================================================ + +-- Latest trade price per market (current price). +CREATE OR REPLACE VIEW project.v_latest_prices AS +SELECT DISTINCT ON (t.market_id) + t.market_id, + c.symbol, + m.quote_currency, + t.price, + t.executed_at +FROM project.market_trades t +JOIN project.markets m ON m.id = t.market_id +JOIN project.crypto c ON c.id = m.crypto_id +ORDER BY t.market_id, t.executed_at DESC; + +-- Portfolio valuation per user (holdings x latest price). +CREATE OR REPLACE VIEW project.v_portfolio AS +SELECT h.user_id, + c.symbol, + h.quantity, + h.reserved_quantity, + (h.quantity - h.reserved_quantity) AS available_quantity, + h.avg_price, + lp.price AS current_price, + (h.quantity * lp.price) AS market_value, + (h.quantity * (lp.price - h.avg_price)) AS unrealized_pnl +FROM project.holdings h +JOIN project.crypto c ON c.id = h.crypto_id +LEFT JOIN project.markets m ON m.crypto_id = c.id AND m.quote_currency = 'USD' +LEFT JOIN project.v_latest_prices lp ON lp.market_id = m.id; +}}} + +== DML script (sample data) == + +The script that loads realistic sample data is `../server/db/data_load.sql` (shown in full below). It is idempotent: it truncates all tables with `CASCADE` then re-inserts. Loaded: + * 5 crypto assets (BTC, ETH, ADA, SOL, DOGE) and 5 USD-quoted markets. + * 3 sample users (`alice`, `bob`, `charlie`) with password `test123` (sha256 hex). + * 18 recent market trades across all markets so `v_latest_prices` is populated. + * 10 one-hour candles (BTC and ETH). + * One fully-executed market-buy order for Alice, the matching holding, and two ledger entries (deposit + buy), with Alice's balances updated accordingly. + * Two watchlists with five watchlist items. + +=== data_load.sql === + +{{{ +-- data_load.sql +-- EduBerza - sample data +-- Course: Databases 2025/2026 Winter, FINKI UKIM +-- +-- Idempotent. Truncates all tables in the `project` schema and reloads +-- deterministic sample data. Run schema_creation.sql first if tables do +-- not yet exist. +-- +-- All sample users have the password: test123 + +SET search_path TO project, public; + +TRUNCATE TABLE + project.watchlist_items, + project.watchlists, + project.market_candles, + project.market_trades, + project.transactions, + project.orders, + project.holdings, + project.markets, + project.crypto, + project.users +RESTART IDENTITY CASCADE; + +-- ============================================================================ +-- CRYPTO +-- ============================================================================ +INSERT INTO project.crypto (id, symbol, name) VALUES + ('11111111-1111-1111-1111-111111111111', 'BTC', 'Bitcoin'), + ('22222222-2222-2222-2222-222222222222', 'ETH', 'Ethereum'), + ('33333333-3333-3333-3333-333333333333', 'ADA', 'Cardano'), + ('44444444-4444-4444-4444-444444444444', 'SOL', 'Solana'), + ('55555555-5555-5555-5555-555555555555', 'DOGE', 'Dogecoin'); + +-- ============================================================================ +-- MARKETS (all quoted in USD) +-- ============================================================================ +INSERT INTO project.markets (id, crypto_id, quote_currency, is_active) VALUES + ('a1111111-1111-1111-1111-111111111111', '11111111-1111-1111-1111-111111111111', 'USD', true), + ('a2222222-2222-2222-2222-222222222222', '22222222-2222-2222-2222-222222222222', 'USD', true), + ('a3333333-3333-3333-3333-333333333333', '33333333-3333-3333-3333-333333333333', 'USD', true), + ('a4444444-4444-4444-4444-444444444444', '44444444-4444-4444-4444-444444444444', 'USD', true), + ('a5555555-5555-5555-5555-555555555555', '55555555-5555-5555-5555-555555555555', 'USD', true); + +-- ============================================================================ +-- USERS +-- Password for all: test123 (stored as sha256 hex hash) +-- ============================================================================ +INSERT INTO project.users (id, username, email, full_name, password_hash, available_balance, invested_balance) VALUES + ('b1111111-1111-1111-1111-111111111111', 'alice', 'alice@example.com', 'Alice Johnson', + encode(digest('test123', 'sha256'), 'hex'), 10000.0000, 0), + ('b2222222-2222-2222-2222-222222222222', 'bob', 'bob@example.com', 'Bob Smith', + encode(digest('test123', 'sha256'), 'hex'), 5000.0000, 0), + ('b3333333-3333-3333-3333-333333333333', 'charlie', 'charlie@example.com', 'Charlie Davis', + encode(digest('test123', 'sha256'), 'hex'), 2500.0000, 0); + +-- ============================================================================ +-- MARKET TRADES +-- Recent simulated trades per market, used as price source. +-- ============================================================================ +INSERT INTO project.market_trades (market_id, executed_at, price, quantity, side, source) VALUES + -- BTC/USD around $67,000 + ('a1111111-1111-1111-1111-111111111111', now() - interval '10 min', 66850.250000, 0.120000, 'buy', 'simulation'), + ('a1111111-1111-1111-1111-111111111111', now() - interval '8 min', 66910.500000, 0.075000, 'sell', 'simulation'), + ('a1111111-1111-1111-1111-111111111111', now() - interval '5 min', 67020.750000, 0.200000, 'buy', 'simulation'), + ('a1111111-1111-1111-1111-111111111111', now() - interval '2 min', 67105.100000, 0.050000, 'buy', 'simulation'), + ('a1111111-1111-1111-1111-111111111111', now() - interval '30 second', 67140.000000, 0.030000, 'sell', 'simulation'), + -- ETH/USD around $3,500 + ('a2222222-2222-2222-2222-222222222222', now() - interval '10 min', 3490.500000, 1.500000, 'buy', 'simulation'), + ('a2222222-2222-2222-2222-222222222222', now() - interval '6 min', 3502.750000, 0.800000, 'sell', 'simulation'), + ('a2222222-2222-2222-2222-222222222222', now() - interval '2 min', 3515.250000, 2.100000, 'buy', 'simulation'), + ('a2222222-2222-2222-2222-222222222222', now() - interval '30 second', 3520.000000, 0.650000, 'buy', 'simulation'), + -- ADA/USD around $0.45 + ('a3333333-3333-3333-3333-333333333333', now() - interval '10 min', 0.446500, 500.000000, 'buy', 'simulation'), + ('a3333333-3333-3333-3333-333333333333', now() - interval '3 min', 0.452000, 1200.000000, 'buy', 'simulation'), + ('a3333333-3333-3333-3333-333333333333', now() - interval '30 second', 0.453750, 800.000000, 'sell', 'simulation'), + -- SOL/USD around $165 + ('a4444444-4444-4444-4444-444444444444', now() - interval '10 min', 164.250000, 10.000000, 'buy', 'simulation'), + ('a4444444-4444-4444-4444-444444444444', now() - interval '4 min', 165.500000, 5.500000, 'sell', 'simulation'), + ('a4444444-4444-4444-4444-444444444444', now() - interval '30 second', 166.100000, 8.000000, 'buy', 'simulation'), + -- DOGE/USD around $0.12 + ('a5555555-5555-5555-5555-555555555555', now() - interval '10 min', 0.118500, 10000.000000, 'buy', 'simulation'), + ('a5555555-5555-5555-5555-555555555555', now() - interval '3 min', 0.121250, 7500.000000, 'sell', 'simulation'), + ('a5555555-5555-5555-5555-555555555555', now() - interval '30 second', 0.122000, 12000.000000, 'buy', 'simulation'); + +-- ============================================================================ +-- MARKET CANDLES (1h aggregates, last 5 hours per market) +-- ============================================================================ +INSERT INTO project.market_candles (market_id, timeframe, open, high, low, close, volume, candle_time) VALUES + ('a1111111-1111-1111-1111-111111111111', '1h', 66200, 66500, 66050, 66400, 12.50, date_trunc('hour', now() - interval '5 hour')), + ('a1111111-1111-1111-1111-111111111111', '1h', 66400, 66800, 66380, 66700, 15.30, date_trunc('hour', now() - interval '4 hour')), + ('a1111111-1111-1111-1111-111111111111', '1h', 66700, 66950, 66650, 66900, 11.80, date_trunc('hour', now() - interval '3 hour')), + ('a1111111-1111-1111-1111-111111111111', '1h', 66900, 67100, 66800, 67050, 14.20, date_trunc('hour', now() - interval '2 hour')), + ('a1111111-1111-1111-1111-111111111111', '1h', 67050, 67200, 66900, 67140, 10.75, date_trunc('hour', now() - interval '1 hour')), + ('a2222222-2222-2222-2222-222222222222', '1h', 3460, 3490, 3450, 3485, 120.0, date_trunc('hour', now() - interval '5 hour')), + ('a2222222-2222-2222-2222-222222222222', '1h', 3485, 3510, 3480, 3500, 135.0, date_trunc('hour', now() - interval '4 hour')), + ('a2222222-2222-2222-2222-222222222222', '1h', 3500, 3520, 3495, 3515, 110.0, date_trunc('hour', now() - interval '3 hour')), + ('a2222222-2222-2222-2222-222222222222', '1h', 3515, 3525, 3500, 3520, 125.5, date_trunc('hour', now() - interval '2 hour')), + ('a2222222-2222-2222-2222-222222222222', '1h', 3520, 3530, 3510, 3520, 140.0, date_trunc('hour', now() - interval '1 hour')); + +-- ============================================================================ +-- EXAMPLE ORDERS, HOLDINGS AND TRANSACTIONS for alice +-- Shows a fully-filled market buy and its resulting holding & ledger entry. +-- ============================================================================ +INSERT INTO project.orders (id, user_id, market_id, side, type, status, quantity, price, placed_at, executed_at) VALUES + ('c1111111-1111-1111-1111-111111111111', + 'b1111111-1111-1111-1111-111111111111', + 'a2222222-2222-2222-2222-222222222222', + 'buy', 'market', 'executed', 0.5000, 3500.000000, + now() - interval '1 hour', now() - interval '1 hour'); + +INSERT INTO project.holdings (user_id, crypto_id, quantity, avg_price, updated_at) VALUES + ('b1111111-1111-1111-1111-111111111111', + '22222222-2222-2222-2222-222222222222', + 0.5000, 3500.000000, now() - interval '1 hour'); + +INSERT INTO project.transactions (user_id, type, amount, currency, related_order, description) VALUES + ('b1111111-1111-1111-1111-111111111111', 'deposit', 10000.0000, 'USD', NULL, + 'Initial virtual deposit'), + ('b1111111-1111-1111-1111-111111111111', 'buy', -1750.0000, 'USD', + 'c1111111-1111-1111-1111-111111111111', + 'Market buy 0.5 ETH @ 3500.00'); + +-- After the buy, alice's invested_balance reflects the used funds. +UPDATE project.users + SET available_balance = 10000.0000 - 1750.0000, + invested_balance = 1750.0000, + updated_at = now() + WHERE id = 'b1111111-1111-1111-1111-111111111111'; + +-- ============================================================================ +-- WATCHLISTS +-- ============================================================================ +INSERT INTO project.watchlists (id, user_id, name) VALUES + ('d1111111-1111-1111-1111-111111111111', 'b1111111-1111-1111-1111-111111111111', 'Favorites'), + ('d2222222-2222-2222-2222-222222222222', 'b2222222-2222-2222-2222-222222222222', 'Bobs Picks'); + +INSERT INTO project.watchlist_items (watchlist_id, crypto_id) VALUES + ('d1111111-1111-1111-1111-111111111111', '11111111-1111-1111-1111-111111111111'), + ('d1111111-1111-1111-1111-111111111111', '22222222-2222-2222-2222-222222222222'), + ('d1111111-1111-1111-1111-111111111111', '44444444-4444-4444-4444-444444444444'), + ('d2222222-2222-2222-2222-222222222222', '11111111-1111-1111-1111-111111111111'), + ('d2222222-2222-2222-2222-222222222222', '55555555-5555-5555-5555-555555555555'); +}}} + +== Relational diagram == + +[[Image(relational_schema.jpg)]] + +Generated in '''Pgadmin''' from the '''live''' `project` schema, in crow's-foot +notation — not drawn by hand, so it is evidence that the deployed database +actually matches the design described above. Each box is a table with its +columns and declared types; key icons mark primary keys and the arrowed lines +are the 12 declared foreign keys. + +=== How to regenerate it === + +'''With pgAdmin 4''', if DBeaver is unavailable — it reads the live schema the same +way, so the result is equivalent in substance: + + 1. Connect to the project database. + 2. Right-click the database → '''ERD For Database''' (or open a blank ERD and drag + the `project` tables in). + 3. Arrange the tables to mirror `ERModel_v03.png`. + 4. '''Download image''' → PNG, then convert: + `convert relational_schema.png relational_schema.jpg` Index: docs/P2-RelationalDesign/wiki/RelationalDesignAIUsage.md =================================================================== --- docs/P2-RelationalDesign/wiki/RelationalDesignAIUsage.md (revision 0cee8ec194e8e518624fcec244168861a6a7de27) +++ docs/P2-RelationalDesign/wiki/RelationalDesignAIUsage.md (revision 0cee8ec194e8e518624fcec244168861a6a7de27) @@ -0,0 +1,97 @@ += Relational Design AI Usage = + +== Name of AI service/solution that was used == + +'''Claude Code''' (Anthropic) + + * '''URL:''' `https://claude.com/claude-code` + * '''Type of service/subscription:''' Claude subscription, model Claude Opus 4.7 (1M context). + +== Final result == + +=== Diagram === + +The student produces `relational_schema.jpg` in DBeaver from the live `project` schema; see [wiki:RelationalDesign] for instructions. + +=== Results in details / description === + +The AI: + + * Consolidated two inconsistent draft schemas (`server/db/db.sql` and `server/db/schema.sql`) into a single `schema_creation.sql`. + * Corrected foreign-key errors in the original (the `crypto_id` column in `holdings`, `orders`, and `transactions` had been pointed at both `users(id)` and `crypto(id)`; the AI split it into separate `user_id` and `crypto_id` columns per `ep-diagram.md`). + * Added two convenience views, `v_latest_prices` and `v_portfolio`, to keep the Go CLI simple. + * Produced a sample-data script `data_load.sql` that TRUNCATEs and re-inserts deterministic rows, so the "must work on empty DB and on a DB that already has data" requirement from P2 is met. + * Documented normalisation up to 3NF and the intentional denormalisation of `holdings.avg_price`. + +== Summary of AI involvement == + +||= =||= Session 1 — 2026-04-21 =||= Session 2 — 2026-08-06/07 =|| +|| '''What I brought''' || My own draft SQL (`db.sql`, `schema.sql`) and the model in `ep-diagram.md` || The schema as it stood after session 1 || +|| '''What the AI did''' || Reviewed my SQL, found the foreign-key errors, consolidated two inconsistent drafts into one script || Reviewed the schema again; one constraint change, plus documentation of the transformation || +|| '''What I decided''' || Which corrections to adopt, to keep both balance columns, to drop the secret-question fields || To make `avg_price` `NOT NULL` rather than handle nulls in application code || + +The relational model in this phase is a transformation of ''my'' ER model, and the +foreign-key errors the AI found in session 1 were errors in ''my'' draft SQL — that +review is the single most useful thing the AI did on this phase. + +== Entire AI usage log == + +See [wiki:ERModelAIUsage] — the full transcript of the 2026-04-21 conversation covers both P1 and P2 work. The specific prompts that drove the relational-design output were the same "make it work and make it fill in or to follow all of the needed instructions" instruction and the student's subsequent "do everything that you need to do". + +> '''Student action required:''' append any future consultations where you asked the AI to refine the schema, tune constraints, or write additional queries. + + +=== Session 2 — 2026-08-06 / 2026-08-07 === + +Prompts are logged in full in [wiki:ERModelAIUsage] (section "Session 2 — 2026-08-06 / 2026-08-07"); +the one that drove this phase was ''"Also fix some database things or golang things if you think we can do it better"''. Changes to the P2 artefacts: + + * `holdings.avg_price` changed from nullable to `NOT NULL DEFAULT 0 CHECK (avg_price >= 0)`. Reason: it feeds the P/L arithmetic in `v_portfolio`, and + SQL arithmetic involving `NULL` produces `NULL`, so a nullable average would + have silently blanked the unrealised-P/L column of a real position. A related + crash path in the Go code (scanning a `NULL` average into a non-nullable + `float64`, which was reported to the user as "Insufficient holding") was fixed + at the same time. + * [wiki:RelationalDesign] gained an explicit account of the + partial transformation: which ER construct each foreign key comes from, that + M:N relationships with attributes become tables whose foreign-key pair is a + `UNIQUE` constraint, and that total participation becomes `NOT NULL` — which is + why `transactions.related_order` is the one nullable foreign key. + * The candidate keys of `holdings` and `watchlist_items` are now documented as + the relationship keys `{user_id, crypto_id}` and `{watchlist_id, crypto_id}`. + +Both scripts were re-run end to end against PostgreSQL 16 after these changes. + +> '''Still outstanding:''' `relational_schema.jpg` must be exported from DBeaver +> against the faculty database. No AI involvement is possible there — it needs a +> live connection to your assigned database. + +=== Session 3 — 2026-09-16 === + +Driven by the same design review logged in full in +[wiki:ERModelAIUsage] (section "Session 3 — 2026-09-16"): +a sell order had nothing to check `holdings.quantity` against except itself, +so nothing stopped two sell orders from being granted the same units. + +Changes to the P2 artefacts: + + * `holdings` gained `reserved_quantity numeric(20,4) NOT NULL DEFAULT 0 CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` in + `schema_creation.sql`. + * `v_portfolio` gained `reserved_quantity` and the derived + `available_quantity = quantity - reserved_quantity`. + * [wiki:RelationalDesign] gained a "Reservation and the order + lifecycle" section explaining why the check is enforced at the database + level rather than trusted to application code, and why it does not conflict + with the existing `SELECT … FOR UPDATE` locking on the sell path. + * `data_load.sql` needed no change — `reserved_quantity` defaults to 0, which + is correct for every seeded holding. + +Re-run end to end against the live database on `localhost:5433` +(`-init` then `-load-data`), and against a manually seeded 2 BTC holding to +reproduce the exact scenario that motivated the change — see +[wiki:UseCase0005Implementation] for +the transcript. + +'''What I decided:''' to add the `CHECK` constraint rather than rely on +`trade.go` alone to keep the reservation consistent — the same reasoning +already applied to `avg_price NOT NULL` in session 2.