source: server/db/schema_creation.sql@ 35bcb41

main
Last change on this file since 35bcb41 was 9577c79, checked in by Stefan <trsunovstefan@…>, 13 days ago

add reserved_quantity and modify the phases, add v_03.png and v_03.xml for P1
  • Property mode set to 100644
File size: 14.2 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 price_volatility numeric,
264 participating_users bigint
265)
266LANGUAGE sql STABLE AS $$
267 WITH trades AS (
268 SELECT
269 market_id, price, quantity, executed_at,
270 FIRST_VALUE(price) OVER w AS first_price,
271 LAST_VALUE(price) OVER (PARTITION BY market_id ORDER BY executed_at
272 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_price
273 FROM project.market_trades
274 WHERE executed_at >= p_from AND executed_at < p_to
275 WINDOW w AS (PARTITION BY market_id ORDER BY executed_at)
276 ),
277 market_stats AS (
278 SELECT
279 market_id,
280 SUM(quantity) AS total_volume,
281 COUNT(*) AS trade_count,
282 AVG(price) AS avg_price,
283 STDDEV(price) AS price_volatility,
284 MAX(first_price) AS first_price,
285 MAX(last_price) AS last_price
286 FROM trades
287 GROUP BY market_id
288 ),
289 participation AS (
290 SELECT market_id, COUNT(DISTINCT user_id) AS participating_users
291 FROM project.orders
292 WHERE status = 'executed' AND executed_at >= p_from AND executed_at < p_to
293 GROUP BY market_id
294 )
295 SELECT
296 c.symbol,
297 m.quote_currency,
298 ms.total_volume,
299 ms.trade_count,
300 ROUND(ms.avg_price, 6) AS avg_price,
301 ROUND((ms.last_price - ms.first_price) / NULLIF(ms.first_price, 0) * 100, 2) AS market_return_pct,
302 ROUND(COALESCE(ms.price_volatility, 0), 6) AS price_volatility,
303 COALESCE(p.participating_users, 0) AS participating_users
304 FROM market_stats ms
305 JOIN project.markets m ON m.id = ms.market_id
306 JOIN project.crypto c ON c.id = m.crypto_id
307 LEFT JOIN participation p ON p.market_id = ms.market_id
308 ORDER BY ms.total_volume DESC;
309$$;
Note: See TracBrowser for help on using the repository browser.