source: server/db/schema_creation.sql

main
Last change on this file was ef1c1c7, checked in by Stefan <trsunovstefan@…>, 5 days ago

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