source: server/db/schema_creation.sql@ a531b45

main
Last change on this file since a531b45 was 9e6d8a2, checked in by Stefan <trsunovstefan@…>, 13 days ago

Remove volatility from Phase 6
  • Property mode set to 100644
File size: 14.0 KB
Line 
1-- schema_creation.sql
2-- EduBerza - crypto exchange simulation database
3-- Course: Databases 2025/2026 Winter, FINKI UKIM
4--
5-- This script is idempotent. It drops the `project` schema and all contained
6-- objects, then recreates them from scratch. Safe to run on an empty database
7-- or on a database where the schema already exists.
8
9DROP SCHEMA IF EXISTS project CASCADE;
10CREATE SCHEMA project;
11
12CREATE EXTENSION IF NOT EXISTS pgcrypto;
13
14SET search_path TO project, public;
15
16-- ============================================================================
17-- USERS
18-- Platform users. Each user has virtual (prop) balances used for simulation.
19-- ============================================================================
20CREATE TABLE project.users (
21 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
22 username varchar(50) NOT NULL UNIQUE,
23 email varchar(255) NOT NULL UNIQUE,
24 full_name varchar(200),
25 password_hash varchar(255) NOT NULL,
26 available_balance numeric(18,4) NOT NULL DEFAULT 0 CHECK (available_balance >= 0),
27 invested_balance numeric(18,4) NOT NULL DEFAULT 0 CHECK (invested_balance >= 0),
28 created_at timestamptz NOT NULL DEFAULT now(),
29 updated_at timestamptz
30);
31
32-- ============================================================================
33-- CRYPTO
34-- Catalog of crypto assets available on the platform.
35-- ============================================================================
36CREATE TABLE project.crypto (
37 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
38 symbol varchar(20) NOT NULL UNIQUE,
39 name varchar(255) NOT NULL,
40 created_at timestamptz NOT NULL DEFAULT now()
41);
42
43-- ============================================================================
44-- MARKETS
45-- A market is a (crypto, quote_currency) pair, e.g. BTC/USD.
46-- ============================================================================
47CREATE TABLE project.markets (
48 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
49 crypto_id uuid NOT NULL REFERENCES project.crypto(id),
50 quote_currency char(3) NOT NULL DEFAULT 'USD',
51 is_active boolean NOT NULL DEFAULT true,
52 created_at timestamptz NOT NULL DEFAULT now(),
53 CONSTRAINT uq_markets UNIQUE (crypto_id, quote_currency)
54);
55
56-- ============================================================================
57-- HOLDINGS
58-- Per-user crypto position with running weighted average entry price.
59-- ============================================================================
60CREATE TABLE project.holdings (
61 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
62 user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE,
63 crypto_id uuid NOT NULL REFERENCES project.crypto(id),
64 quantity numeric(20,4) NOT NULL CHECK (quantity >= 0),
65 -- Committed to the user's own open sell orders, not yet removed from the
66 -- position. quantity - reserved_quantity is what is actually free to
67 -- sell — the crypto-side equivalent of users.available_balance.
68 reserved_quantity numeric(20,4) NOT NULL DEFAULT 0
69 CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity),
70 -- Weighted-average entry price. NOT NULL so that the P/L arithmetic in
71 -- v_portfolio can never silently produce NULL for an existing position.
72 avg_price numeric(18,6) NOT NULL DEFAULT 0 CHECK (avg_price >= 0),
73 created_at timestamptz NOT NULL DEFAULT now(),
74 updated_at timestamptz,
75 CONSTRAINT uq_holdings_user_crypto UNIQUE (user_id, crypto_id)
76);
77
78-- ============================================================================
79-- ORDERS
80-- Orders placed by users on a market.
81-- ============================================================================
82CREATE TABLE project.orders (
83 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
84 user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE,
85 market_id uuid NOT NULL REFERENCES project.markets(id),
86 side varchar(4) NOT NULL CHECK (side IN ('buy', 'sell')),
87 type varchar(20) NOT NULL CHECK (type IN ('market', 'limit')),
88 status varchar(20) NOT NULL CHECK (status IN ('open', 'executed', 'cancelled')),
89 quantity numeric(20,4) NOT NULL CHECK (quantity > 0),
90 price numeric(18,6),
91 placed_at timestamptz NOT NULL DEFAULT now(),
92 executed_at timestamptz
93);
94
95CREATE INDEX idx_orders_user ON project.orders(user_id);
96CREATE INDEX idx_orders_market ON project.orders(market_id);
97CREATE INDEX idx_orders_status ON project.orders(status);
98
99-- ============================================================================
100-- TRANSACTIONS
101-- Financial ledger: deposits, buys, sells, fees.
102-- ============================================================================
103CREATE TABLE project.transactions (
104 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
105 user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE,
106 type varchar(50) NOT NULL CHECK (type IN ('deposit', 'buy', 'sell', 'fee')),
107 amount numeric(18,4) NOT NULL,
108 currency char(3) NOT NULL DEFAULT 'USD',
109 related_order uuid REFERENCES project.orders(id),
110 created_at timestamptz NOT NULL DEFAULT now(),
111 description text
112);
113
114CREATE INDEX idx_transactions_user ON project.transactions(user_id, created_at DESC);
115
116-- ============================================================================
117-- MARKET TRADES
118-- Raw executed trades on a market. Source of truth for current price.
119-- ============================================================================
120CREATE TABLE project.market_trades (
121 id bigserial PRIMARY KEY,
122 market_id uuid NOT NULL REFERENCES project.markets(id),
123 executed_at timestamptz NOT NULL,
124 price numeric(18,6) NOT NULL CHECK (price > 0),
125 quantity numeric(20,6) NOT NULL CHECK (quantity > 0),
126 side varchar(4) CHECK (side IN ('buy', 'sell')),
127 source varchar(50) NOT NULL DEFAULT 'simulation'
128);
129
130CREATE INDEX idx_market_trades_market_time ON project.market_trades(market_id, executed_at DESC);
131
132-- ============================================================================
133-- MARKET CANDLES
134-- OHLCV aggregates over standard timeframes.
135-- ============================================================================
136CREATE TABLE project.market_candles (
137 id bigserial PRIMARY KEY,
138 market_id uuid NOT NULL REFERENCES project.markets(id),
139 timeframe varchar(5) NOT NULL CHECK (timeframe IN ('1m', '5m', '1h', '1d')),
140 open numeric(18,6) NOT NULL,
141 high numeric(18,6) NOT NULL,
142 low numeric(18,6) NOT NULL,
143 close numeric(18,6) NOT NULL,
144 volume numeric(20,6) NOT NULL,
145 candle_time timestamptz NOT NULL,
146 CONSTRAINT uq_candle UNIQUE (market_id, timeframe, candle_time)
147);
148
149CREATE INDEX idx_market_candles_market_tf_time ON project.market_candles(market_id, timeframe, candle_time DESC);
150
151-- ============================================================================
152-- WATCHLISTS
153-- ============================================================================
154CREATE TABLE project.watchlists (
155 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
156 user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE,
157 name varchar(100) NOT NULL,
158 created_at timestamptz NOT NULL DEFAULT now()
159);
160
161CREATE TABLE project.watchlist_items (
162 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
163 watchlist_id uuid NOT NULL REFERENCES project.watchlists(id) ON DELETE CASCADE,
164 crypto_id uuid NOT NULL REFERENCES project.crypto(id),
165 added_at timestamptz NOT NULL DEFAULT now(),
166 CONSTRAINT uq_watchlist_crypto UNIQUE (watchlist_id, crypto_id)
167);
168
169-- ============================================================================
170-- VIEWS
171-- ============================================================================
172
173-- Latest trade price per market (current price).
174CREATE OR REPLACE VIEW project.v_latest_prices AS
175SELECT DISTINCT ON (t.market_id)
176 t.market_id,
177 c.symbol,
178 m.quote_currency,
179 t.price,
180 t.executed_at
181FROM project.market_trades t
182JOIN project.markets m ON m.id = t.market_id
183JOIN project.crypto c ON c.id = m.crypto_id
184ORDER BY t.market_id, t.executed_at DESC;
185
186-- Portfolio valuation per user (holdings x latest price).
187CREATE OR REPLACE VIEW project.v_portfolio AS
188SELECT h.user_id,
189 c.symbol,
190 h.quantity,
191 h.reserved_quantity,
192 (h.quantity - h.reserved_quantity) AS available_quantity,
193 h.avg_price,
194 lp.price AS current_price,
195 (h.quantity * lp.price) AS market_value,
196 (h.quantity * (lp.price - h.avg_price)) AS unrealized_pnl
197FROM project.holdings h
198JOIN project.crypto c ON c.id = h.crypto_id
199LEFT JOIN project.markets m ON m.crypto_id = c.id AND m.quote_currency = 'USD'
200LEFT JOIN project.v_latest_prices lp ON lp.market_id = m.id;
201
202-- ============================================================================
203-- REPORTS (P6 — Complex DB Reports)
204-- Both are single SELECT statements (with CTEs), wrapped as SQL functions so
205-- they can be called as parameterised reports from the prototype instead of
206-- being copy-pasted SQL text. See docs/P6-AdvancedReports/AdvancedReports.md.
207-- ============================================================================
208
209-- report_top_traders: realized trading performance per user over [p_from, p_to),
210-- bucketed into quarters to measure how consistently each user was profitable.
211CREATE OR REPLACE FUNCTION project.report_top_traders(p_from timestamptz, p_to timestamptz)
212RETURNS TABLE (
213 username varchar,
214 realized_pl numeric,
215 total_invested numeric,
216 roi_pct numeric,
217 profitable_periods bigint,
218 losing_periods bigint,
219 total_periods bigint,
220 consistency_pct numeric
221)
222LANGUAGE sql STABLE AS $$
223 WITH period_pl AS (
224 SELECT
225 t.user_id,
226 date_trunc('quarter', t.created_at) AS period,
227 SUM(t.amount) AS period_pl,
228 SUM(t.amount) FILTER (WHERE t.type = 'buy') AS period_buy
229 FROM project.transactions t
230 WHERE t.type IN ('buy', 'sell', 'fee')
231 AND t.created_at >= p_from
232 AND t.created_at < p_to
233 GROUP BY t.user_id, date_trunc('quarter', t.created_at)
234 )
235 SELECT
236 u.username,
237 SUM(pp.period_pl) AS realized_pl,
238 ABS(SUM(pp.period_buy)) AS total_invested,
239 ROUND(SUM(pp.period_pl) / NULLIF(ABS(SUM(pp.period_buy)), 0) * 100, 2) AS roi_pct,
240 COUNT(*) FILTER (WHERE pp.period_pl > 0) AS profitable_periods,
241 COUNT(*) FILTER (WHERE pp.period_pl < 0) AS losing_periods,
242 COUNT(*) AS total_periods,
243 ROUND(COUNT(*) FILTER (WHERE pp.period_pl > 0)::numeric
244 / NULLIF(COUNT(*), 0) * 100, 2) AS consistency_pct
245 FROM period_pl pp
246 JOIN project.users u ON u.id = pp.user_id
247 GROUP BY u.id, u.username
248 ORDER BY realized_pl DESC;
249$$;
250
251-- report_market_performance: trading activity and price behaviour per market
252-- over [p_from, p_to). Volume/trade-count/price stats come from market_trades
253-- (the complete tape — user fills and simulated fills alike); participating
254-- users can only come from orders, since market_trades has no user_id column.
255CREATE OR REPLACE FUNCTION project.report_market_performance(p_from timestamptz, p_to timestamptz)
256RETURNS TABLE (
257 symbol varchar,
258 quote_currency char(3),
259 total_volume numeric,
260 trade_count bigint,
261 avg_price numeric,
262 market_return_pct numeric,
263 participating_users bigint
264)
265LANGUAGE sql STABLE AS $$
266 WITH trades AS (
267 SELECT
268 market_id, price, quantity, executed_at,
269 FIRST_VALUE(price) OVER w AS first_price,
270 LAST_VALUE(price) OVER (PARTITION BY market_id ORDER BY executed_at
271 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_price
272 FROM project.market_trades
273 WHERE executed_at >= p_from AND executed_at < p_to
274 WINDOW w AS (PARTITION BY market_id ORDER BY executed_at)
275 ),
276 market_stats AS (
277 SELECT
278 market_id,
279 SUM(quantity) AS total_volume,
280 COUNT(*) AS trade_count,
281 AVG(price) AS avg_price,
282 MAX(first_price) AS first_price,
283 MAX(last_price) AS last_price
284 FROM trades
285 GROUP BY market_id
286 ),
287 participation AS (
288 SELECT market_id, COUNT(DISTINCT user_id) AS participating_users
289 FROM project.orders
290 WHERE status = 'executed' AND executed_at >= p_from AND executed_at < p_to
291 GROUP BY market_id
292 )
293 SELECT
294 c.symbol,
295 m.quote_currency,
296 ms.total_volume,
297 ms.trade_count,
298 ROUND(ms.avg_price, 6) AS avg_price,
299 ROUND((ms.last_price - ms.first_price) / NULLIF(ms.first_price, 0) * 100, 2) AS market_return_pct,
300 COALESCE(p.participating_users, 0) AS participating_users
301 FROM market_stats ms
302 JOIN project.markets m ON m.id = ms.market_id
303 JOIN project.crypto c ON c.id = m.crypto_id
304 LEFT JOIN participation p ON p.market_id = ms.market_id
305 ORDER BY ms.total_volume DESC;
306$$;
Note: See TracBrowser for help on using the repository browser.