source: server/db/advanced_db.sql@ ef1c1c7

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

Wiki docs, phase 6 and phase 7 added
  • Property mode set to 100644
File size: 36.1 KB
RevLine 
[ef1c1c7]1-- advanced_db.sql
2-- EduBerza - P7 Advanced Database Development
3-- Course: Databases 2025/2026 Winter, FINKI UKIM
4--
5-- Order, reservation and trade consistency implemented in the database.
6-- Run after schema_creation.sql and before data_load.sql (-init does both).
7--
8-- 1. Order lifecycle - status is derived from filled_quantity, only
9-- valid transitions, finished orders are final,
10-- filled_quantity only changes through a trade.
11-- 2. Trade consistency - a trade row may only fill compatible, active
12-- orders, never more than they have remaining;
13-- inserting it fills the orders automatically.
14-- 3. Reservations/balance - reserved cash and reserved crypto always equal
15-- what the user's active orders still need, and
16-- cash (available + reserved) always equals the
17-- ledger. Checked at COMMIT.
18-- 4. Order events - every placement, fill and cancellation is
19-- recorded automatically.
20-- 5. Operations - place_order, execute_trade, cancel_order.
21-- 6. Views - order book, active orders, order history,
22-- trader balances.
23-- 7. Background job - fills resting limit orders once the simulated
24-- market price reaches them.
25
26SET search_path TO project, public;
27
28-- Cash a buy order still holds in reserve: its remaining quantity at its
29-- price, rounded to the 4 decimals of the balance columns. Defined once so
30-- placing, filling, cancelling and checking all round the same way.
31CREATE OR REPLACE FUNCTION project.order_reservation(p_remaining numeric, p_price numeric)
32RETURNS numeric LANGUAGE sql IMMUTABLE AS $$
33 SELECT round(p_remaining * p_price, 4)
34$$;
35
36-- Latest traded price of a market (the simulated market price).
37CREATE OR REPLACE FUNCTION project.latest_price(p_market_id uuid)
38RETURNS numeric LANGUAGE sql STABLE AS $$
39 SELECT price FROM project.market_trades
40 WHERE market_id = p_market_id
41 ORDER BY executed_at DESC, id DESC
42 LIMIT 1
43$$;
44
45-- ============================================================================
46-- 1. ORDER LIFECYCLE
47-- ============================================================================
48
49-- Automatic recording of order events (placement, fills, cancellation).
50CREATE TABLE project.order_events (
51 id bigserial PRIMARY KEY,
52 order_id uuid NOT NULL REFERENCES project.orders(id) ON DELETE CASCADE,
53 event_type varchar(20) NOT NULL
54 CHECK (event_type IN ('placed', 'partially_filled', 'filled', 'cancelled')),
55 quantity numeric(20,4) NOT NULL,
56 price numeric(18,6),
57 status_after varchar(20) NOT NULL,
58 created_at timestamptz NOT NULL DEFAULT clock_timestamp()
59);
60
61CREATE INDEX idx_order_events_order ON project.order_events(order_id, id);
62
63-- Status is not set by hand: it follows from how much has been filled.
64-- filled = 0 -> open
65-- 0 < filled < qty -> partially_filled
66-- filled = qty -> executed (executed_at set)
67-- The only status that is set explicitly is 'cancelled', and only on an
68-- active order. executed and cancelled orders are final. What was ordered
69-- never changes. filled_quantity only changes when a trade fills the order
70-- (the trade trigger sets a transaction-local flag while it does that).
71CREATE OR REPLACE FUNCTION project.trg_orders_lifecycle()
72RETURNS trigger LANGUAGE plpgsql AS $$
73DECLARE
74 v_derived varchar(20);
75BEGIN
76 IF TG_OP = 'UPDATE' THEN
77 IF OLD.status IN ('executed', 'cancelled') THEN
78 RAISE EXCEPTION 'order % is already % and cannot be processed again', OLD.id, OLD.status
79 USING ERRCODE = 'check_violation';
80 END IF;
81 IF (NEW.user_id, NEW.market_id, NEW.side, NEW.type, NEW.quantity, NEW.price, NEW.placed_at)
82 IS DISTINCT FROM
83 (OLD.user_id, OLD.market_id, OLD.side, OLD.type, OLD.quantity, OLD.price, OLD.placed_at) THEN
84 RAISE EXCEPTION 'user, market, side, type, quantity, price and placed_at of an order cannot change'
85 USING ERRCODE = 'check_violation';
86 END IF;
87 IF NEW.filled_quantity <> OLD.filled_quantity THEN
88 IF current_setting('eduberza.trade_fill', true) IS DISTINCT FROM 'on' THEN
89 RAISE EXCEPTION 'filled quantity of order % can only change through a trade', OLD.id
90 USING ERRCODE = 'check_violation';
91 END IF;
92 IF NEW.filled_quantity < OLD.filled_quantity THEN
93 RAISE EXCEPTION 'filled quantity of order % cannot decrease', OLD.id
94 USING ERRCODE = 'check_violation';
95 END IF;
96 END IF;
97 IF NEW.status = 'cancelled' AND OLD.status <> 'cancelled' THEN
98 IF NEW.filled_quantity <> OLD.filled_quantity THEN
99 RAISE EXCEPTION 'an order cannot be filled and cancelled in the same step'
100 USING ERRCODE = 'check_violation';
101 END IF;
102 NEW.executed_at := NULL;
103 RETURN NEW;
104 END IF;
105 ELSE
106 IF NOT EXISTS (SELECT 1 FROM project.markets WHERE id = NEW.market_id AND is_active) THEN
107 RAISE EXCEPTION 'market % is not active; no new orders accepted', NEW.market_id
108 USING ERRCODE = 'check_violation';
109 END IF;
110 IF NEW.price IS NULL OR NEW.price <= 0 THEN
111 RAISE EXCEPTION 'an order needs a positive price (limit price, or the market price for a market order)'
112 USING ERRCODE = 'check_violation';
113 END IF;
114 IF NEW.status = 'cancelled' THEN
115 RAISE EXCEPTION 'an order cannot be created already cancelled'
116 USING ERRCODE = 'check_violation';
117 END IF;
118 -- A new order starts unfilled. The only exception is importing an
119 -- order that was completely executed in the past (sample data).
120 IF NEW.filled_quantity NOT IN (0, NEW.quantity) THEN
121 RAISE EXCEPTION 'a new order is either unfilled or (imported history) completely filled'
122 USING ERRCODE = 'check_violation';
123 END IF;
124 END IF;
125
126 v_derived := CASE
127 WHEN NEW.filled_quantity = 0 THEN 'open'
128 WHEN NEW.filled_quantity < NEW.quantity THEN 'partially_filled'
129 ELSE 'executed'
130 END;
131 IF NEW.status IS DISTINCT FROM v_derived
132 AND (TG_OP = 'INSERT' OR NEW.status IS DISTINCT FROM OLD.status) THEN
133 RAISE EXCEPTION 'order status % does not match filled quantity % of %; status is derived automatically',
134 NEW.status, NEW.filled_quantity, NEW.quantity
135 USING ERRCODE = 'check_violation';
136 END IF;
137 NEW.status := v_derived;
138
139 IF v_derived = 'executed' THEN
140 NEW.executed_at := COALESCE(NEW.executed_at, now());
141 ELSE
142 NEW.executed_at := NULL;
143 END IF;
144 RETURN NEW;
145END $$;
146
147CREATE TRIGGER orders_lifecycle
148 BEFORE INSERT OR UPDATE ON project.orders
149 FOR EACH ROW EXECUTE FUNCTION project.trg_orders_lifecycle();
150
151CREATE OR REPLACE FUNCTION project.trg_orders_events()
152RETURNS trigger LANGUAGE plpgsql AS $$
153BEGIN
154 IF TG_OP = 'INSERT' THEN
155 INSERT INTO project.order_events (order_id, event_type, quantity, price, status_after)
156 VALUES (NEW.id, 'placed', NEW.quantity, NEW.price, NEW.status);
157 ELSIF NEW.filled_quantity > OLD.filled_quantity THEN
158 INSERT INTO project.order_events (order_id, event_type, quantity, price, status_after)
159 VALUES (NEW.id,
160 CASE WHEN NEW.status = 'executed' THEN 'filled' ELSE 'partially_filled' END,
161 NEW.filled_quantity - OLD.filled_quantity,
162 current_setting('eduberza.trade_price', true)::numeric,
163 NEW.status);
164 ELSIF NEW.status = 'cancelled' AND OLD.status <> 'cancelled' THEN
165 INSERT INTO project.order_events (order_id, event_type, quantity, price, status_after)
166 VALUES (NEW.id, 'cancelled', NEW.quantity - NEW.filled_quantity, NEW.price, NEW.status);
167 END IF;
168 RETURN NULL;
169END $$;
170
171CREATE TRIGGER orders_events
172 AFTER INSERT OR UPDATE ON project.orders
173 FOR EACH ROW EXECUTE FUNCTION project.trg_orders_events();
174
175-- ============================================================================
176-- 2. TRADE CONSISTENCY
177-- ============================================================================
178
179-- A trade that names orders must be possible for those orders:
180-- * the buy_order_id is a buy order and the sell_order_id a sell order,
181-- * both are on the trade's market and still active (open or partially
182-- filled) - a cancelled or executed order can never trade again,
183-- * the trade quantity does not exceed either order's remaining quantity,
184-- * the price respects both limits (buy: price <= its limit, sell:
185-- price >= its limit),
186-- * the two orders belong to different users (no self-trade).
187-- A trade with no orders at all is a simulated market tick (the bot).
188CREATE OR REPLACE FUNCTION project.trg_market_trades_validate()
189RETURNS trigger LANGUAGE plpgsql AS $$
190DECLARE
191 o project.orders%ROWTYPE;
192 v_uid uuid;
193 v_id uuid;
194 v_role varchar(4);
195BEGIN
196 IF NEW.buy_order_id IS NULL AND NEW.sell_order_id IS NULL THEN
197 RETURN NEW;
198 END IF;
199 IF NEW.buy_order_id IS NOT DISTINCT FROM NEW.sell_order_id THEN
200 RAISE EXCEPTION 'an order cannot trade with itself'
201 USING ERRCODE = 'check_violation';
202 END IF;
203
204 FOREACH v_role IN ARRAY ARRAY['buy', 'sell'] LOOP
205 v_id := CASE v_role WHEN 'buy' THEN NEW.buy_order_id ELSE NEW.sell_order_id END;
206 CONTINUE WHEN v_id IS NULL;
207
208 SELECT * INTO o FROM project.orders WHERE id = v_id FOR UPDATE;
209 IF o.side <> v_role THEN
210 RAISE EXCEPTION 'order % is a % order and cannot be the % side of a trade', v_id, o.side, v_role
211 USING ERRCODE = 'check_violation';
212 END IF;
213 IF o.market_id <> NEW.market_id THEN
214 RAISE EXCEPTION 'order % is on a different market than the trade', v_id
215 USING ERRCODE = 'check_violation';
216 END IF;
217 IF o.status NOT IN ('open', 'partially_filled') THEN
218 RAISE EXCEPTION 'order % is % and cannot trade', v_id, o.status
219 USING ERRCODE = 'check_violation';
220 END IF;
221 IF NEW.quantity > o.quantity - o.filled_quantity THEN
222 RAISE EXCEPTION 'trade quantity % exceeds the remaining quantity % of order %',
223 NEW.quantity, o.quantity - o.filled_quantity, v_id
224 USING ERRCODE = 'check_violation';
225 END IF;
226 IF v_uid IS NOT NULL AND v_uid = o.user_id THEN
227 RAISE EXCEPTION 'a user cannot trade with their own order'
228 USING ERRCODE = 'check_violation';
229 END IF;
230 IF v_role = 'buy' AND NEW.price > o.price OR v_role = 'sell' AND NEW.price < o.price THEN
231 RAISE EXCEPTION 'trade price % is outside the limit % of % order %', NEW.price, o.price, v_role, v_id
232 USING ERRCODE = 'check_violation';
233 END IF;
234 v_uid := o.user_id;
235 END LOOP;
236 RETURN NEW;
237END $$;
238
239CREATE TRIGGER market_trades_validate
240 BEFORE INSERT ON project.market_trades
241 FOR EACH ROW EXECUTE FUNCTION project.trg_market_trades_validate();
242
243-- Recording a trade fills the orders it names; their status follows via
244-- orders_lifecycle. The flag is what orders_lifecycle checks to allow the
245-- change of filled_quantity.
246CREATE OR REPLACE FUNCTION project.trg_market_trades_fill()
247RETURNS trigger LANGUAGE plpgsql AS $$
248BEGIN
249 IF NEW.buy_order_id IS NULL AND NEW.sell_order_id IS NULL THEN
250 RETURN NULL;
251 END IF;
252 PERFORM set_config('eduberza.trade_fill', 'on', true);
253 PERFORM set_config('eduberza.trade_price', NEW.price::text, true);
254 UPDATE project.orders
255 SET filled_quantity = filled_quantity + NEW.quantity,
256 executed_at = CASE WHEN filled_quantity + NEW.quantity = quantity
257 THEN NEW.executed_at END
258 WHERE id IN (NEW.buy_order_id, NEW.sell_order_id);
259 PERFORM set_config('eduberza.trade_fill', 'off', true);
260 RETURN NULL;
261END $$;
262
263CREATE TRIGGER market_trades_fill
264 AFTER INSERT ON project.market_trades
265 FOR EACH ROW EXECUTE FUNCTION project.trg_market_trades_fill();
266
267-- Trades are history: they are never changed or removed (the fills and the
268-- money they moved would no longer match).
269CREATE OR REPLACE FUNCTION project.trg_market_trades_immutable()
270RETURNS trigger LANGUAGE plpgsql AS $$
271BEGIN
272 IF OLD.buy_order_id IS NULL AND OLD.sell_order_id IS NULL THEN
273 -- a simulated tick filled no order; allow it unless it is being
274 -- turned into one that did
275 IF TG_OP = 'DELETE' THEN
276 RETURN OLD;
277 ELSIF NEW.buy_order_id IS NULL AND NEW.sell_order_id IS NULL THEN
278 RETURN NEW;
279 END IF;
280 END IF;
281 RAISE EXCEPTION 'a trade that filled orders cannot be changed or deleted'
282 USING ERRCODE = 'check_violation';
283END $$;
284
285CREATE TRIGGER market_trades_immutable
286 BEFORE UPDATE OR DELETE ON project.market_trades
287 FOR EACH ROW EXECUTE FUNCTION project.trg_market_trades_immutable();
288
289-- ============================================================================
290-- 3. RESERVATIONS AND BALANCES (deferred: checked at COMMIT)
291-- ============================================================================
292-- Placing, filling and cancelling an order each change several rows in
293-- separate statements, and in between the rows disagree. Only the state at
294-- COMMIT must be consistent, so these are DEFERRABLE INITIALLY DEFERRED
295-- constraint triggers: a transaction that leaves any of them broken is
296-- rolled back as a whole.
297
298-- 3a. reserved_balance = what the user's active buy orders still reserve.
299CREATE OR REPLACE FUNCTION project.trg_reserved_cash_matches_orders()
300RETURNS trigger LANGUAGE plpgsql AS $$
301DECLARE
302 v_user uuid := CASE WHEN TG_TABLE_NAME = 'users' THEN NEW.id END;
303 v_reserved numeric;
304 v_needed numeric;
305BEGIN
306 IF TG_TABLE_NAME = 'orders' THEN
307 v_user := NEW.user_id;
308 END IF;
309 SELECT reserved_balance INTO v_reserved FROM project.users WHERE id = v_user;
310 IF NOT FOUND THEN
311 RETURN NULL;
312 END IF;
313 SELECT COALESCE(SUM(project.order_reservation(quantity - filled_quantity, price)), 0)
314 INTO v_needed
315 FROM project.orders
316 WHERE user_id = v_user AND side = 'buy' AND status IN ('open', 'partially_filled');
317 IF v_reserved <> v_needed THEN
318 RAISE EXCEPTION 'reserved balance % does not match the % needed by active buy orders (user %)',
319 v_reserved, v_needed, v_user
320 USING ERRCODE = 'check_violation', CONSTRAINT = 'reserved_cash_matches_orders';
321 END IF;
322 RETURN NULL;
323END $$;
324
325-- 3b. holdings.reserved_quantity = what the user's active sell orders for
326-- that crypto still have to deliver.
327CREATE OR REPLACE FUNCTION project.trg_reserved_crypto_matches_orders()
328RETURNS trigger LANGUAGE plpgsql AS $$
329DECLARE
330 v_user uuid;
331 v_crypto uuid;
332 v_reserved numeric;
333 v_needed numeric;
334BEGIN
335 IF TG_TABLE_NAME = 'holdings' THEN
336 v_user := NEW.user_id;
337 v_crypto := NEW.crypto_id;
338 ELSE
339 IF NEW.side <> 'sell' THEN
340 RETURN NULL;
341 END IF;
342 v_user := NEW.user_id;
343 SELECT crypto_id INTO v_crypto FROM project.markets WHERE id = NEW.market_id;
344 END IF;
345 SELECT COALESCE(SUM(reserved_quantity), 0) INTO v_reserved
346 FROM project.holdings WHERE user_id = v_user AND crypto_id = v_crypto;
347 SELECT COALESCE(SUM(o.quantity - o.filled_quantity), 0) INTO v_needed
348 FROM project.orders o
349 JOIN project.markets m ON m.id = o.market_id
350 WHERE o.user_id = v_user AND m.crypto_id = v_crypto
351 AND o.side = 'sell' AND o.status IN ('open', 'partially_filled');
352 IF v_reserved <> v_needed THEN
353 RAISE EXCEPTION 'reserved quantity % does not match the % needed by active sell orders (user %, crypto %)',
354 v_reserved, v_needed, v_user, v_crypto
355 USING ERRCODE = 'check_violation', CONSTRAINT = 'reserved_crypto_matches_orders';
356 END IF;
357 RETURN NULL;
358END $$;
359
360-- 3c. available_balance + reserved_balance = sum of the user's ledger.
361-- Reserving only moves cash between the two columns; money actually
362-- leaves or arrives only with a ledger row (deposit, buy fill, sell fill).
363CREATE OR REPLACE FUNCTION project.trg_cash_matches_ledger()
364RETURNS trigger LANGUAGE plpgsql AS $$
365DECLARE
366 v_user uuid;
367 v_cash numeric;
368 v_ledger numeric;
369BEGIN
370 IF TG_TABLE_NAME = 'users' THEN
371 v_user := NEW.id;
372 ELSIF TG_OP = 'DELETE' THEN
373 v_user := OLD.user_id;
374 ELSE
375 v_user := NEW.user_id;
376 END IF;
377 SELECT available_balance + reserved_balance INTO v_cash FROM project.users WHERE id = v_user;
378 IF NOT FOUND THEN
379 RETURN NULL;
380 END IF;
381 SELECT COALESCE(SUM(amount), 0) INTO v_ledger FROM project.transactions WHERE user_id = v_user;
382 IF v_cash <> v_ledger THEN
383 RAISE EXCEPTION 'cash % (available + reserved) does not match the ledger total % (user %)',
384 v_cash, v_ledger, v_user
385 USING ERRCODE = 'check_violation', CONSTRAINT = 'cash_matches_ledger';
386 END IF;
387 RETURN NULL;
388END $$;
389
390CREATE INDEX idx_orders_active ON project.orders (user_id, side)
391 WHERE status IN ('open', 'partially_filled');
392
393CREATE CONSTRAINT TRIGGER reserved_cash_matches_orders
394 AFTER INSERT OR UPDATE OF reserved_balance ON project.users
395 DEFERRABLE INITIALLY DEFERRED
396 FOR EACH ROW EXECUTE FUNCTION project.trg_reserved_cash_matches_orders();
397CREATE CONSTRAINT TRIGGER reserved_cash_matches_orders
398 AFTER INSERT OR UPDATE OF status, filled_quantity ON project.orders
399 DEFERRABLE INITIALLY DEFERRED
400 FOR EACH ROW EXECUTE FUNCTION project.trg_reserved_cash_matches_orders();
401
402CREATE CONSTRAINT TRIGGER reserved_crypto_matches_orders
403 AFTER INSERT OR UPDATE OF reserved_quantity ON project.holdings
404 DEFERRABLE INITIALLY DEFERRED
405 FOR EACH ROW EXECUTE FUNCTION project.trg_reserved_crypto_matches_orders();
406CREATE CONSTRAINT TRIGGER reserved_crypto_matches_orders
407 AFTER INSERT OR UPDATE OF status, filled_quantity ON project.orders
408 DEFERRABLE INITIALLY DEFERRED
409 FOR EACH ROW EXECUTE FUNCTION project.trg_reserved_crypto_matches_orders();
410
411CREATE CONSTRAINT TRIGGER cash_matches_ledger
412 AFTER INSERT OR UPDATE OF available_balance, reserved_balance ON project.users
413 DEFERRABLE INITIALLY DEFERRED
414 FOR EACH ROW EXECUTE FUNCTION project.trg_cash_matches_ledger();
415CREATE CONSTRAINT TRIGGER cash_matches_ledger
416 AFTER INSERT OR UPDATE OR DELETE ON project.transactions
417 DEFERRABLE INITIALLY DEFERRED
418 FOR EACH ROW EXECUTE FUNCTION project.trg_cash_matches_ledger();
419
420-- ============================================================================
421-- 5. OPERATIONS
422-- ============================================================================
423
424-- execute_trade: one trade of p_quantity at p_price between a buy order and
425-- a sell order. Either side may be NULL, meaning the simulated market is the
426-- counterparty. Inserting the trade row validates it and fills the orders
427-- (triggers above); this function moves the money and the crypto:
428-- buyer: reservation released for the filled part, the actual cost paid
429-- (any difference to the limit price goes back to available),
430-- crypto added to the holding at a running weighted average,
431-- 'buy' ledger row.
432-- seller: reserved crypto delivered out of the holding, proceeds credited,
433-- cost basis removed from invested_balance, 'sell' ledger row.
434CREATE OR REPLACE FUNCTION project.execute_trade(
435 p_buy_order uuid, p_sell_order uuid, p_quantity numeric, p_price numeric,
436 p_aggressor varchar DEFAULT NULL)
437RETURNS bigint LANGUAGE plpgsql AS $$
438DECLARE
439 b project.orders%ROWTYPE;
440 s project.orders%ROWTYPE;
441 v_market project.markets%ROWTYPE;
442 v_trade_id bigint;
443 v_release numeric;
444 v_cost numeric;
445 v_proceeds numeric;
446 v_avg numeric;
447BEGIN
448 IF p_buy_order IS NULL AND p_sell_order IS NULL THEN
449 RAISE EXCEPTION 'a trade needs at least one order' USING ERRCODE = 'check_violation';
450 END IF;
451
452 -- lock both orders in a fixed order (by id) so two concurrent trades on
453 -- the same pair of orders cannot deadlock
454 PERFORM 1 FROM project.orders WHERE id IN (p_buy_order, p_sell_order) ORDER BY id FOR UPDATE;
455 SELECT * INTO b FROM project.orders WHERE id = p_buy_order;
456 SELECT * INTO s FROM project.orders WHERE id = p_sell_order;
457 SELECT * INTO v_market FROM project.markets WHERE id = COALESCE(b.market_id, s.market_id);
458
459 INSERT INTO project.market_trades
460 (market_id, executed_at, price, quantity, side, source, buy_order_id, sell_order_id)
461 VALUES (v_market.id, now(), p_price, p_quantity,
462 COALESCE(p_aggressor, CASE WHEN p_sell_order IS NULL THEN 'buy' ELSE 'sell' END),
463 CASE WHEN p_buy_order IS NOT NULL AND p_sell_order IS NOT NULL THEN 'match' ELSE 'market' END,
464 p_buy_order, p_sell_order)
465 RETURNING id INTO v_trade_id;
466
467 IF p_buy_order IS NOT NULL THEN
468 v_release := project.order_reservation(b.quantity - b.filled_quantity, b.price)
469 - project.order_reservation(b.quantity - b.filled_quantity - p_quantity, b.price);
470 v_cost := LEAST(round(p_quantity * p_price, 4), v_release);
471
472 UPDATE project.users
473 SET reserved_balance = reserved_balance - v_release,
474 available_balance = available_balance + (v_release - v_cost),
475 invested_balance = invested_balance + v_cost,
476 updated_at = now()
477 WHERE id = b.user_id;
478
479 INSERT INTO project.holdings AS h (user_id, crypto_id, quantity, avg_price, updated_at)
480 VALUES (b.user_id, v_market.crypto_id, p_quantity, p_price, now())
481 ON CONFLICT (user_id, crypto_id) DO UPDATE
482 SET avg_price = (h.quantity * h.avg_price + EXCLUDED.quantity * EXCLUDED.avg_price)
483 / (h.quantity + EXCLUDED.quantity),
484 quantity = h.quantity + EXCLUDED.quantity,
485 updated_at = now();
486
487 INSERT INTO project.transactions (user_id, type, amount, currency, related_order, description)
488 VALUES (b.user_id, 'buy', -v_cost, v_market.quote_currency, b.id,
489 format('Buy %s @ %s (trade %s)', p_quantity, p_price, v_trade_id));
490 END IF;
491
492 IF p_sell_order IS NOT NULL THEN
493 SELECT avg_price INTO v_avg FROM project.holdings
494 WHERE user_id = s.user_id AND crypto_id = v_market.crypto_id FOR UPDATE;
495
496 UPDATE project.holdings
497 SET quantity = quantity - p_quantity,
498 reserved_quantity = reserved_quantity - p_quantity,
499 updated_at = now()
500 WHERE user_id = s.user_id AND crypto_id = v_market.crypto_id;
501
502 v_proceeds := round(p_quantity * p_price, 4);
503 UPDATE project.users
504 SET available_balance = available_balance + v_proceeds,
505 invested_balance = GREATEST(invested_balance - round(p_quantity * v_avg, 4), 0),
506 updated_at = now()
507 WHERE id = s.user_id;
508
509 INSERT INTO project.transactions (user_id, type, amount, currency, related_order, description)
510 VALUES (s.user_id, 'sell', v_proceeds, v_market.quote_currency, s.id,
511 format('Sell %s @ %s (trade %s)', p_quantity, p_price, v_trade_id));
512 END IF;
513
514 RETURN v_trade_id;
515END $$;
516
517-- match_order: trade an order against the opposite side of the order book -
518-- other users' active limit orders on the same market whose price is
519-- acceptable - best price first, then oldest first (price-time priority).
520-- Each trade is at the resting order's price. Returns the number of trades.
521CREATE OR REPLACE FUNCTION project.match_order(p_order_id uuid)
522RETURNS int LANGUAGE plpgsql AS $$
523DECLARE
524 o project.orders%ROWTYPE;
525 r record;
526 v_rem numeric;
527 v_qty numeric;
528 v_count int := 0;
529BEGIN
530 SELECT * INTO o FROM project.orders WHERE id = p_order_id FOR UPDATE;
531 FOR r IN
532 SELECT id, price, quantity - filled_quantity AS remaining
533 FROM project.orders
534 WHERE market_id = o.market_id
535 AND side <> o.side
536 AND type = 'limit'
537 AND status IN ('open', 'partially_filled')
538 AND user_id <> o.user_id
539 AND (o.side = 'buy' AND price <= o.price
540 OR o.side = 'sell' AND price >= o.price)
541 ORDER BY CASE WHEN o.side = 'buy' THEN price END ASC,
542 CASE WHEN o.side = 'sell' THEN price END DESC,
543 placed_at, id
544 FOR UPDATE
545 LOOP
546 SELECT quantity - filled_quantity INTO v_rem FROM project.orders WHERE id = p_order_id;
547 EXIT WHEN v_rem = 0;
548 v_qty := LEAST(v_rem, r.remaining);
549 IF o.side = 'buy' THEN
550 PERFORM project.execute_trade(o.id, r.id, v_qty, r.price, 'buy');
551 ELSE
552 PERFORM project.execute_trade(r.id, o.id, v_qty, r.price, 'sell');
553 END IF;
554 v_count := v_count + 1;
555 END LOOP;
556 RETURN v_count;
557END $$;
558
559-- place_order: the one correct way to place an order.
560-- 1. checks the market, side, type, quantity and price;
561-- 2. locks the user and reserves what the order commits: cash
562-- (remaining x price) for a buy, crypto for a sell - refusing the order
563-- if not enough is free;
564-- 3. records the order (open);
565-- 4. matches it against the order book (match_order);
566-- 5. whatever is still unfilled and is marketable at the current market
567-- price trades with the simulated market. A market order is priced at
568-- the current market price, so it always fills completely here; a limit
569-- order that is not marketable stays in the book.
570-- Returns the order id.
571CREATE OR REPLACE FUNCTION project.place_order(
572 p_user_id uuid, p_market_id uuid, p_side varchar, p_type varchar,
573 p_quantity numeric, p_limit_price numeric DEFAULT NULL)
574RETURNS uuid LANGUAGE plpgsql AS $$
575DECLARE
576 v_market numeric := project.latest_price(p_market_id);
577 v_price numeric;
578 v_available numeric;
579 v_free numeric;
580 v_crypto uuid;
581 v_order uuid;
582 v_rem numeric;
583BEGIN
584 IF p_side NOT IN ('buy', 'sell') OR p_type NOT IN ('market', 'limit') THEN
585 RAISE EXCEPTION 'invalid side % or type %', p_side, p_type USING ERRCODE = 'check_violation';
586 END IF;
587 IF p_quantity IS NULL OR p_quantity <= 0 THEN
588 RAISE EXCEPTION 'quantity must be positive' USING ERRCODE = 'check_violation';
589 END IF;
590 IF p_type = 'limit' THEN
591 IF p_limit_price IS NULL OR p_limit_price <= 0 THEN
592 RAISE EXCEPTION 'a limit order needs a positive limit price' USING ERRCODE = 'check_violation';
593 END IF;
594 v_price := p_limit_price;
595 ELSE
596 IF v_market IS NULL THEN
597 RAISE EXCEPTION 'market has no price yet' USING ERRCODE = 'check_violation';
598 END IF;
599 v_price := v_market;
600 END IF;
601
602 SELECT available_balance INTO v_available FROM project.users WHERE id = p_user_id FOR UPDATE;
603 IF NOT FOUND THEN
604 RAISE EXCEPTION 'user % does not exist', p_user_id USING ERRCODE = 'no_data_found';
605 END IF;
606
607 IF p_side = 'buy' THEN
608 IF v_available < project.order_reservation(p_quantity, v_price) THEN
609 RAISE EXCEPTION 'insufficient funds: the order needs %, available %',
610 project.order_reservation(p_quantity, v_price), v_available
611 USING ERRCODE = 'check_violation';
612 END IF;
613 UPDATE project.users
614 SET available_balance = available_balance - project.order_reservation(p_quantity, v_price),
615 reserved_balance = reserved_balance + project.order_reservation(p_quantity, v_price),
616 updated_at = now()
617 WHERE id = p_user_id;
618 ELSE
619 SELECT crypto_id INTO v_crypto FROM project.markets WHERE id = p_market_id;
620 SELECT quantity - reserved_quantity INTO v_free FROM project.holdings
621 WHERE user_id = p_user_id AND crypto_id = v_crypto FOR UPDATE;
622 IF COALESCE(v_free, 0) < p_quantity THEN
623 RAISE EXCEPTION 'insufficient holding: trying to sell %, free to sell %', p_quantity, COALESCE(v_free, 0)
624 USING ERRCODE = 'check_violation';
625 END IF;
626 UPDATE project.holdings
627 SET reserved_quantity = reserved_quantity + p_quantity, updated_at = now()
628 WHERE user_id = p_user_id AND crypto_id = v_crypto;
629 END IF;
630
631 INSERT INTO project.orders (user_id, market_id, side, type, status, quantity, price)
632 VALUES (p_user_id, p_market_id, p_side, p_type, 'open', p_quantity, v_price)
633 RETURNING id INTO v_order;
634
635 PERFORM project.match_order(v_order);
636
637 SELECT quantity - filled_quantity INTO v_rem FROM project.orders WHERE id = v_order;
638 IF v_rem > 0 AND v_market IS NOT NULL
639 AND (p_side = 'buy' AND v_market <= v_price OR p_side = 'sell' AND v_market >= v_price) THEN
640 IF p_side = 'buy' THEN
641 PERFORM project.execute_trade(v_order, NULL, v_rem, v_market, 'buy');
642 ELSE
643 PERFORM project.execute_trade(NULL, v_order, v_rem, v_market, 'sell');
644 END IF;
645 END IF;
646 RETURN v_order;
647END $$;
648
649-- cancel_order: cancels an active order and releases what it still
650-- reserves. p_user_id = NULL is a system cancellation (no ownership check).
651CREATE OR REPLACE FUNCTION project.cancel_order(p_order_id uuid, p_user_id uuid DEFAULT NULL)
652RETURNS void LANGUAGE plpgsql AS $$
653DECLARE
654 o project.orders%ROWTYPE;
655 v_rem numeric;
656BEGIN
657 SELECT * INTO o FROM project.orders WHERE id = p_order_id FOR UPDATE;
658 IF NOT FOUND THEN
659 RAISE EXCEPTION 'order % does not exist', p_order_id USING ERRCODE = 'no_data_found';
660 END IF;
661 IF p_user_id IS NOT NULL AND o.user_id <> p_user_id THEN
662 RAISE EXCEPTION 'order % does not belong to this user', p_order_id
663 USING ERRCODE = 'insufficient_privilege';
664 END IF;
665 IF o.status NOT IN ('open', 'partially_filled') THEN
666 RAISE EXCEPTION 'order % is % and cannot be cancelled', p_order_id, o.status
667 USING ERRCODE = 'check_violation';
668 END IF;
669
670 v_rem := o.quantity - o.filled_quantity;
671 IF o.side = 'buy' THEN
672 UPDATE project.users
673 SET reserved_balance = reserved_balance - project.order_reservation(v_rem, o.price),
674 available_balance = available_balance + project.order_reservation(v_rem, o.price),
675 updated_at = now()
676 WHERE id = o.user_id;
677 ELSE
678 UPDATE project.holdings h
679 SET reserved_quantity = h.reserved_quantity - v_rem, updated_at = now()
680 FROM project.markets m
681 WHERE m.id = o.market_id AND h.user_id = o.user_id AND h.crypto_id = m.crypto_id;
682 END IF;
683
684 UPDATE project.orders SET status = 'cancelled' WHERE id = p_order_id;
685END $$;
686
687-- ============================================================================
688-- 6. VIEWS
689-- ============================================================================
690
691-- Active orders with what they still need and what they hold in reserve.
692CREATE VIEW project.v_active_orders AS
693SELECT o.id AS order_id,
694 o.user_id,
695 u.username,
696 o.market_id,
697 c.symbol,
698 m.quote_currency,
699 o.side,
700 o.type,
701 o.status,
702 o.quantity,
703 o.filled_quantity,
704 o.quantity - o.filled_quantity AS remaining,
705 o.price,
706 CASE WHEN o.side = 'buy'
707 THEN project.order_reservation(o.quantity - o.filled_quantity, o.price) ELSE 0 END AS reserved_cash,
708 CASE WHEN o.side = 'sell' THEN o.quantity - o.filled_quantity ELSE 0 END AS reserved_crypto,
709 o.placed_at
710FROM project.orders o
711JOIN project.users u ON u.id = o.user_id
712JOIN project.markets m ON m.id = o.market_id
713JOIN project.crypto c ON c.id = m.crypto_id
714WHERE o.status IN ('open', 'partially_filled');
715
716-- Current order book: resting limit orders aggregated per price level.
717CREATE VIEW project.v_order_book AS
718SELECT market_id,
719 symbol,
720 quote_currency,
721 side,
722 price,
723 SUM(remaining) AS quantity,
724 COUNT(*) AS orders
725FROM project.v_active_orders
726WHERE type = 'limit'
727GROUP BY market_id, symbol, quote_currency, side, price;
728
729-- Every order with its fill progress and average fill price from its trades.
730CREATE VIEW project.v_order_history AS
731SELECT o.id AS order_id,
732 o.user_id,
733 u.username,
734 c.symbol,
735 o.side,
736 o.type,
737 o.status,
738 o.quantity,
739 o.filled_quantity,
740 o.quantity - o.filled_quantity AS remaining,
741 o.price,
742 f.trades,
743 f.avg_fill_price,
744 o.placed_at,
745 o.executed_at
746FROM project.orders o
747JOIN project.users u ON u.id = o.user_id
748JOIN project.markets m ON m.id = o.market_id
749JOIN project.crypto c ON c.id = m.crypto_id
750LEFT JOIN LATERAL (
751 SELECT COUNT(*) AS trades,
752 round(SUM(t.quantity * t.price) / NULLIF(SUM(t.quantity), 0), 6) AS avg_fill_price
753 FROM (SELECT quantity, price FROM project.market_trades WHERE buy_order_id = o.id
754 UNION ALL
755 SELECT quantity, price FROM project.market_trades WHERE sell_order_id = o.id) t
756) f ON true;
757
758-- Trader balances: cash split into free and reserved, the ledger it must
759-- equal, holdings at market value, and net worth.
760CREATE VIEW project.v_trader_balances AS
761SELECT u.id AS user_id,
762 u.username,
763 u.available_balance,
764 u.reserved_balance,
765 u.available_balance + u.reserved_balance AS total_cash,
766 COALESCE(l.ledger_total, 0) AS ledger_total,
767 u.invested_balance,
768 COALESCE(p.holdings_value, 0) AS holdings_value,
769 u.available_balance + u.reserved_balance + COALESCE(p.holdings_value, 0) AS net_worth
770FROM project.users u
771LEFT JOIN (SELECT user_id, SUM(amount) AS ledger_total
772 FROM project.transactions GROUP BY user_id) l ON l.user_id = u.id
773LEFT JOIN (SELECT user_id, SUM(market_value) AS holdings_value
774 FROM project.v_portfolio GROUP BY user_id) p ON p.user_id = u.id;
775
776-- ============================================================================
777-- 7. BACKGROUND JOB
778-- ============================================================================
779
780-- EduBerza's market price moves with the simulator (bots/main.go), not with
781-- user orders. A resting limit order - buy at or above, or sell at or below,
782-- the current market price - must then be filled by the simulated market,
783-- just as it would have been had the price already been there when it was
784-- placed. Nothing else happens at that moment that a trigger could react to
785-- (the bot's price ticks deliberately stay cheap inserts), so this runs as a
786-- periodic job: the bot process calls it after every round of price ticks.
787-- The faculty server has no pg_cron, so the scheduling is done there.
788-- An advisory lock keeps two runs from filling the same orders twice;
789-- SKIP LOCKED leaves any order a user is cancelling right now for next time.
790-- Returns the number of orders filled.
791CREATE OR REPLACE FUNCTION project.fill_marketable_orders()
792RETURNS int LANGUAGE plpgsql AS $$
793DECLARE
794 r record;
795 v_count int := 0;
796BEGIN
797 IF NOT pg_try_advisory_xact_lock(hashtext('project.fill_marketable_orders')) THEN
798 RETURN 0;
799 END IF;
800 FOR r IN
801 SELECT o.id, o.side, o.quantity - o.filled_quantity AS remaining, lp.price AS market_price
802 FROM project.orders o
803 JOIN project.markets m ON m.id = o.market_id AND m.is_active
804 CROSS JOIN LATERAL (SELECT project.latest_price(o.market_id) AS price) lp
805 WHERE o.type = 'limit'
806 AND o.status IN ('open', 'partially_filled')
807 AND (o.side = 'buy' AND o.price >= lp.price
808 OR o.side = 'sell' AND o.price <= lp.price)
809 ORDER BY o.placed_at, o.id
810 FOR UPDATE OF o SKIP LOCKED
811 LOOP
812 IF r.side = 'buy' THEN
813 PERFORM project.execute_trade(r.id, NULL, r.remaining, r.market_price, 'sell');
814 ELSE
815 PERFORM project.execute_trade(NULL, r.id, r.remaining, r.market_price, 'buy');
816 END IF;
817 v_count := v_count + 1;
818 END LOOP;
819 RETURN v_count;
820END $$;
Note: See TracBrowser for help on using the repository browser.