Index: server/db/advanced_db.sql =================================================================== --- server/db/advanced_db.sql (revision ef1c1c725137d93fa85292f43440214aaddcdf05) +++ server/db/advanced_db.sql (revision ef1c1c725137d93fa85292f43440214aaddcdf05) @@ -0,0 +1,820 @@ +-- advanced_db.sql +-- EduBerza - P7 Advanced Database Development +-- Course: Databases 2025/2026 Winter, FINKI UKIM +-- +-- Order, reservation and trade consistency implemented in the database. +-- Run after schema_creation.sql and before data_load.sql (-init does both). +-- +-- 1. Order lifecycle - status is derived from filled_quantity, only +-- valid transitions, finished orders are final, +-- filled_quantity only changes through a trade. +-- 2. Trade consistency - a trade row may only fill compatible, active +-- orders, never more than they have remaining; +-- inserting it fills the orders automatically. +-- 3. Reservations/balance - reserved cash and reserved crypto always equal +-- what the user's active orders still need, and +-- cash (available + reserved) always equals the +-- ledger. Checked at COMMIT. +-- 4. Order events - every placement, fill and cancellation is +-- recorded automatically. +-- 5. Operations - place_order, execute_trade, cancel_order. +-- 6. Views - order book, active orders, order history, +-- trader balances. +-- 7. Background job - fills resting limit orders once the simulated +-- market price reaches them. + +SET search_path TO project, public; + +-- Cash a buy order still holds in reserve: its remaining quantity at its +-- price, rounded to the 4 decimals of the balance columns. Defined once so +-- placing, filling, cancelling and checking all round the same way. +CREATE OR REPLACE FUNCTION project.order_reservation(p_remaining numeric, p_price numeric) +RETURNS numeric LANGUAGE sql IMMUTABLE AS $$ + SELECT round(p_remaining * p_price, 4) +$$; + +-- Latest traded price of a market (the simulated market price). +CREATE OR REPLACE FUNCTION project.latest_price(p_market_id uuid) +RETURNS numeric LANGUAGE sql STABLE AS $$ + SELECT price FROM project.market_trades + WHERE market_id = p_market_id + ORDER BY executed_at DESC, id DESC + LIMIT 1 +$$; + +-- ============================================================================ +-- 1. ORDER LIFECYCLE +-- ============================================================================ + +-- Automatic recording of order events (placement, fills, cancellation). +CREATE TABLE project.order_events ( + id bigserial PRIMARY KEY, + order_id uuid NOT NULL REFERENCES project.orders(id) ON DELETE CASCADE, + event_type varchar(20) NOT NULL + CHECK (event_type IN ('placed', 'partially_filled', 'filled', 'cancelled')), + quantity numeric(20,4) NOT NULL, + price numeric(18,6), + status_after varchar(20) NOT NULL, + created_at timestamptz NOT NULL DEFAULT clock_timestamp() +); + +CREATE INDEX idx_order_events_order ON project.order_events(order_id, id); + +-- Status is not set by hand: it follows from how much has been filled. +-- filled = 0 -> open +-- 0 < filled < qty -> partially_filled +-- filled = qty -> executed (executed_at set) +-- The only status that is set explicitly is 'cancelled', and only on an +-- active order. executed and cancelled orders are final. What was ordered +-- never changes. filled_quantity only changes when a trade fills the order +-- (the trade trigger sets a transaction-local flag while it does that). +CREATE OR REPLACE FUNCTION project.trg_orders_lifecycle() +RETURNS trigger LANGUAGE plpgsql AS $$ +DECLARE + v_derived varchar(20); +BEGIN + IF TG_OP = 'UPDATE' THEN + IF OLD.status IN ('executed', 'cancelled') THEN + RAISE EXCEPTION 'order % is already % and cannot be processed again', OLD.id, OLD.status + USING ERRCODE = 'check_violation'; + END IF; + IF (NEW.user_id, NEW.market_id, NEW.side, NEW.type, NEW.quantity, NEW.price, NEW.placed_at) + IS DISTINCT FROM + (OLD.user_id, OLD.market_id, OLD.side, OLD.type, OLD.quantity, OLD.price, OLD.placed_at) THEN + RAISE EXCEPTION 'user, market, side, type, quantity, price and placed_at of an order cannot change' + USING ERRCODE = 'check_violation'; + END IF; + IF NEW.filled_quantity <> OLD.filled_quantity THEN + IF current_setting('eduberza.trade_fill', true) IS DISTINCT FROM 'on' THEN + RAISE EXCEPTION 'filled quantity of order % can only change through a trade', OLD.id + USING ERRCODE = 'check_violation'; + END IF; + IF NEW.filled_quantity < OLD.filled_quantity THEN + RAISE EXCEPTION 'filled quantity of order % cannot decrease', OLD.id + USING ERRCODE = 'check_violation'; + END IF; + END IF; + IF NEW.status = 'cancelled' AND OLD.status <> 'cancelled' THEN + IF NEW.filled_quantity <> OLD.filled_quantity THEN + RAISE EXCEPTION 'an order cannot be filled and cancelled in the same step' + USING ERRCODE = 'check_violation'; + END IF; + NEW.executed_at := NULL; + RETURN NEW; + END IF; + ELSE + IF NOT EXISTS (SELECT 1 FROM project.markets WHERE id = NEW.market_id AND is_active) THEN + RAISE EXCEPTION 'market % is not active; no new orders accepted', NEW.market_id + USING ERRCODE = 'check_violation'; + END IF; + IF NEW.price IS NULL OR NEW.price <= 0 THEN + RAISE EXCEPTION 'an order needs a positive price (limit price, or the market price for a market order)' + USING ERRCODE = 'check_violation'; + END IF; + IF NEW.status = 'cancelled' THEN + RAISE EXCEPTION 'an order cannot be created already cancelled' + USING ERRCODE = 'check_violation'; + END IF; + -- A new order starts unfilled. The only exception is importing an + -- order that was completely executed in the past (sample data). + IF NEW.filled_quantity NOT IN (0, NEW.quantity) THEN + RAISE EXCEPTION 'a new order is either unfilled or (imported history) completely filled' + USING ERRCODE = 'check_violation'; + END IF; + END IF; + + v_derived := CASE + WHEN NEW.filled_quantity = 0 THEN 'open' + WHEN NEW.filled_quantity < NEW.quantity THEN 'partially_filled' + ELSE 'executed' + END; + IF NEW.status IS DISTINCT FROM v_derived + AND (TG_OP = 'INSERT' OR NEW.status IS DISTINCT FROM OLD.status) THEN + RAISE EXCEPTION 'order status % does not match filled quantity % of %; status is derived automatically', + NEW.status, NEW.filled_quantity, NEW.quantity + USING ERRCODE = 'check_violation'; + END IF; + NEW.status := v_derived; + + IF v_derived = 'executed' THEN + NEW.executed_at := COALESCE(NEW.executed_at, now()); + ELSE + NEW.executed_at := NULL; + END IF; + RETURN NEW; +END $$; + +CREATE TRIGGER orders_lifecycle + BEFORE INSERT OR UPDATE ON project.orders + FOR EACH ROW EXECUTE FUNCTION project.trg_orders_lifecycle(); + +CREATE OR REPLACE FUNCTION project.trg_orders_events() +RETURNS trigger LANGUAGE plpgsql AS $$ +BEGIN + IF TG_OP = 'INSERT' THEN + INSERT INTO project.order_events (order_id, event_type, quantity, price, status_after) + VALUES (NEW.id, 'placed', NEW.quantity, NEW.price, NEW.status); + ELSIF NEW.filled_quantity > OLD.filled_quantity THEN + INSERT INTO project.order_events (order_id, event_type, quantity, price, status_after) + VALUES (NEW.id, + CASE WHEN NEW.status = 'executed' THEN 'filled' ELSE 'partially_filled' END, + NEW.filled_quantity - OLD.filled_quantity, + current_setting('eduberza.trade_price', true)::numeric, + NEW.status); + ELSIF NEW.status = 'cancelled' AND OLD.status <> 'cancelled' THEN + INSERT INTO project.order_events (order_id, event_type, quantity, price, status_after) + VALUES (NEW.id, 'cancelled', NEW.quantity - NEW.filled_quantity, NEW.price, NEW.status); + END IF; + RETURN NULL; +END $$; + +CREATE TRIGGER orders_events + AFTER INSERT OR UPDATE ON project.orders + FOR EACH ROW EXECUTE FUNCTION project.trg_orders_events(); + +-- ============================================================================ +-- 2. TRADE CONSISTENCY +-- ============================================================================ + +-- A trade that names orders must be possible for those orders: +-- * the buy_order_id is a buy order and the sell_order_id a sell order, +-- * both are on the trade's market and still active (open or partially +-- filled) - a cancelled or executed order can never trade again, +-- * the trade quantity does not exceed either order's remaining quantity, +-- * the price respects both limits (buy: price <= its limit, sell: +-- price >= its limit), +-- * the two orders belong to different users (no self-trade). +-- A trade with no orders at all is a simulated market tick (the bot). +CREATE OR REPLACE FUNCTION project.trg_market_trades_validate() +RETURNS trigger LANGUAGE plpgsql AS $$ +DECLARE + o project.orders%ROWTYPE; + v_uid uuid; + v_id uuid; + v_role varchar(4); +BEGIN + IF NEW.buy_order_id IS NULL AND NEW.sell_order_id IS NULL THEN + RETURN NEW; + END IF; + IF NEW.buy_order_id IS NOT DISTINCT FROM NEW.sell_order_id THEN + RAISE EXCEPTION 'an order cannot trade with itself' + USING ERRCODE = 'check_violation'; + END IF; + + FOREACH v_role IN ARRAY ARRAY['buy', 'sell'] LOOP + v_id := CASE v_role WHEN 'buy' THEN NEW.buy_order_id ELSE NEW.sell_order_id END; + CONTINUE WHEN v_id IS NULL; + + SELECT * INTO o FROM project.orders WHERE id = v_id FOR UPDATE; + IF o.side <> v_role THEN + RAISE EXCEPTION 'order % is a % order and cannot be the % side of a trade', v_id, o.side, v_role + USING ERRCODE = 'check_violation'; + END IF; + IF o.market_id <> NEW.market_id THEN + RAISE EXCEPTION 'order % is on a different market than the trade', v_id + USING ERRCODE = 'check_violation'; + END IF; + IF o.status NOT IN ('open', 'partially_filled') THEN + RAISE EXCEPTION 'order % is % and cannot trade', v_id, o.status + USING ERRCODE = 'check_violation'; + END IF; + IF NEW.quantity > o.quantity - o.filled_quantity THEN + RAISE EXCEPTION 'trade quantity % exceeds the remaining quantity % of order %', + NEW.quantity, o.quantity - o.filled_quantity, v_id + USING ERRCODE = 'check_violation'; + END IF; + IF v_uid IS NOT NULL AND v_uid = o.user_id THEN + RAISE EXCEPTION 'a user cannot trade with their own order' + USING ERRCODE = 'check_violation'; + END IF; + IF v_role = 'buy' AND NEW.price > o.price OR v_role = 'sell' AND NEW.price < o.price THEN + RAISE EXCEPTION 'trade price % is outside the limit % of % order %', NEW.price, o.price, v_role, v_id + USING ERRCODE = 'check_violation'; + END IF; + v_uid := o.user_id; + END LOOP; + RETURN NEW; +END $$; + +CREATE TRIGGER market_trades_validate + BEFORE INSERT ON project.market_trades + FOR EACH ROW EXECUTE FUNCTION project.trg_market_trades_validate(); + +-- Recording a trade fills the orders it names; their status follows via +-- orders_lifecycle. The flag is what orders_lifecycle checks to allow the +-- change of filled_quantity. +CREATE OR REPLACE FUNCTION project.trg_market_trades_fill() +RETURNS trigger LANGUAGE plpgsql AS $$ +BEGIN + IF NEW.buy_order_id IS NULL AND NEW.sell_order_id IS NULL THEN + RETURN NULL; + END IF; + PERFORM set_config('eduberza.trade_fill', 'on', true); + PERFORM set_config('eduberza.trade_price', NEW.price::text, true); + UPDATE project.orders + SET filled_quantity = filled_quantity + NEW.quantity, + executed_at = CASE WHEN filled_quantity + NEW.quantity = quantity + THEN NEW.executed_at END + WHERE id IN (NEW.buy_order_id, NEW.sell_order_id); + PERFORM set_config('eduberza.trade_fill', 'off', true); + RETURN NULL; +END $$; + +CREATE TRIGGER market_trades_fill + AFTER INSERT ON project.market_trades + FOR EACH ROW EXECUTE FUNCTION project.trg_market_trades_fill(); + +-- Trades are history: they are never changed or removed (the fills and the +-- money they moved would no longer match). +CREATE OR REPLACE FUNCTION project.trg_market_trades_immutable() +RETURNS trigger LANGUAGE plpgsql AS $$ +BEGIN + IF OLD.buy_order_id IS NULL AND OLD.sell_order_id IS NULL THEN + -- a simulated tick filled no order; allow it unless it is being + -- turned into one that did + IF TG_OP = 'DELETE' THEN + RETURN OLD; + ELSIF NEW.buy_order_id IS NULL AND NEW.sell_order_id IS NULL THEN + RETURN NEW; + END IF; + END IF; + RAISE EXCEPTION 'a trade that filled orders cannot be changed or deleted' + USING ERRCODE = 'check_violation'; +END $$; + +CREATE TRIGGER market_trades_immutable + BEFORE UPDATE OR DELETE ON project.market_trades + FOR EACH ROW EXECUTE FUNCTION project.trg_market_trades_immutable(); + +-- ============================================================================ +-- 3. RESERVATIONS AND BALANCES (deferred: checked at COMMIT) +-- ============================================================================ +-- Placing, filling and cancelling an order each change several rows in +-- separate statements, and in between the rows disagree. Only the state at +-- COMMIT must be consistent, so these are DEFERRABLE INITIALLY DEFERRED +-- constraint triggers: a transaction that leaves any of them broken is +-- rolled back as a whole. + +-- 3a. reserved_balance = what the user's active buy orders still reserve. +CREATE OR REPLACE FUNCTION project.trg_reserved_cash_matches_orders() +RETURNS trigger LANGUAGE plpgsql AS $$ +DECLARE + v_user uuid := CASE WHEN TG_TABLE_NAME = 'users' THEN NEW.id END; + v_reserved numeric; + v_needed numeric; +BEGIN + IF TG_TABLE_NAME = 'orders' THEN + v_user := NEW.user_id; + END IF; + SELECT reserved_balance INTO v_reserved FROM project.users WHERE id = v_user; + IF NOT FOUND THEN + RETURN NULL; + END IF; + SELECT COALESCE(SUM(project.order_reservation(quantity - filled_quantity, price)), 0) + INTO v_needed + FROM project.orders + WHERE user_id = v_user AND side = 'buy' AND status IN ('open', 'partially_filled'); + IF v_reserved <> v_needed THEN + RAISE EXCEPTION 'reserved balance % does not match the % needed by active buy orders (user %)', + v_reserved, v_needed, v_user + USING ERRCODE = 'check_violation', CONSTRAINT = 'reserved_cash_matches_orders'; + END IF; + RETURN NULL; +END $$; + +-- 3b. holdings.reserved_quantity = what the user's active sell orders for +-- that crypto still have to deliver. +CREATE OR REPLACE FUNCTION project.trg_reserved_crypto_matches_orders() +RETURNS trigger LANGUAGE plpgsql AS $$ +DECLARE + v_user uuid; + v_crypto uuid; + v_reserved numeric; + v_needed numeric; +BEGIN + IF TG_TABLE_NAME = 'holdings' THEN + v_user := NEW.user_id; + v_crypto := NEW.crypto_id; + ELSE + IF NEW.side <> 'sell' THEN + RETURN NULL; + END IF; + v_user := NEW.user_id; + SELECT crypto_id INTO v_crypto FROM project.markets WHERE id = NEW.market_id; + END IF; + SELECT COALESCE(SUM(reserved_quantity), 0) INTO v_reserved + FROM project.holdings WHERE user_id = v_user AND crypto_id = v_crypto; + SELECT COALESCE(SUM(o.quantity - o.filled_quantity), 0) INTO v_needed + FROM project.orders o + JOIN project.markets m ON m.id = o.market_id + WHERE o.user_id = v_user AND m.crypto_id = v_crypto + AND o.side = 'sell' AND o.status IN ('open', 'partially_filled'); + IF v_reserved <> v_needed THEN + RAISE EXCEPTION 'reserved quantity % does not match the % needed by active sell orders (user %, crypto %)', + v_reserved, v_needed, v_user, v_crypto + USING ERRCODE = 'check_violation', CONSTRAINT = 'reserved_crypto_matches_orders'; + END IF; + RETURN NULL; +END $$; + +-- 3c. available_balance + reserved_balance = sum of the user's ledger. +-- Reserving only moves cash between the two columns; money actually +-- leaves or arrives only with a ledger row (deposit, buy fill, sell fill). +CREATE OR REPLACE FUNCTION project.trg_cash_matches_ledger() +RETURNS trigger LANGUAGE plpgsql AS $$ +DECLARE + v_user uuid; + v_cash numeric; + v_ledger numeric; +BEGIN + IF TG_TABLE_NAME = 'users' THEN + v_user := NEW.id; + ELSIF TG_OP = 'DELETE' THEN + v_user := OLD.user_id; + ELSE + v_user := NEW.user_id; + END IF; + SELECT available_balance + reserved_balance INTO v_cash FROM project.users WHERE id = v_user; + IF NOT FOUND THEN + RETURN NULL; + END IF; + SELECT COALESCE(SUM(amount), 0) INTO v_ledger FROM project.transactions WHERE user_id = v_user; + IF v_cash <> v_ledger THEN + RAISE EXCEPTION 'cash % (available + reserved) does not match the ledger total % (user %)', + v_cash, v_ledger, v_user + USING ERRCODE = 'check_violation', CONSTRAINT = 'cash_matches_ledger'; + END IF; + RETURN NULL; +END $$; + +CREATE INDEX idx_orders_active ON project.orders (user_id, side) + WHERE status IN ('open', 'partially_filled'); + +CREATE CONSTRAINT TRIGGER reserved_cash_matches_orders + AFTER INSERT OR UPDATE OF reserved_balance ON project.users + DEFERRABLE INITIALLY DEFERRED + FOR EACH ROW EXECUTE FUNCTION project.trg_reserved_cash_matches_orders(); +CREATE CONSTRAINT TRIGGER reserved_cash_matches_orders + AFTER INSERT OR UPDATE OF status, filled_quantity ON project.orders + DEFERRABLE INITIALLY DEFERRED + FOR EACH ROW EXECUTE FUNCTION project.trg_reserved_cash_matches_orders(); + +CREATE CONSTRAINT TRIGGER reserved_crypto_matches_orders + AFTER INSERT OR UPDATE OF reserved_quantity ON project.holdings + DEFERRABLE INITIALLY DEFERRED + FOR EACH ROW EXECUTE FUNCTION project.trg_reserved_crypto_matches_orders(); +CREATE CONSTRAINT TRIGGER reserved_crypto_matches_orders + AFTER INSERT OR UPDATE OF status, filled_quantity ON project.orders + DEFERRABLE INITIALLY DEFERRED + FOR EACH ROW EXECUTE FUNCTION project.trg_reserved_crypto_matches_orders(); + +CREATE CONSTRAINT TRIGGER cash_matches_ledger + AFTER INSERT OR UPDATE OF available_balance, reserved_balance ON project.users + DEFERRABLE INITIALLY DEFERRED + FOR EACH ROW EXECUTE FUNCTION project.trg_cash_matches_ledger(); +CREATE CONSTRAINT TRIGGER cash_matches_ledger + AFTER INSERT OR UPDATE OR DELETE ON project.transactions + DEFERRABLE INITIALLY DEFERRED + FOR EACH ROW EXECUTE FUNCTION project.trg_cash_matches_ledger(); + +-- ============================================================================ +-- 5. OPERATIONS +-- ============================================================================ + +-- execute_trade: one trade of p_quantity at p_price between a buy order and +-- a sell order. Either side may be NULL, meaning the simulated market is the +-- counterparty. Inserting the trade row validates it and fills the orders +-- (triggers above); this function moves the money and the crypto: +-- buyer: reservation released for the filled part, the actual cost paid +-- (any difference to the limit price goes back to available), +-- crypto added to the holding at a running weighted average, +-- 'buy' ledger row. +-- seller: reserved crypto delivered out of the holding, proceeds credited, +-- cost basis removed from invested_balance, 'sell' ledger row. +CREATE OR REPLACE FUNCTION project.execute_trade( + p_buy_order uuid, p_sell_order uuid, p_quantity numeric, p_price numeric, + p_aggressor varchar DEFAULT NULL) +RETURNS bigint LANGUAGE plpgsql AS $$ +DECLARE + b project.orders%ROWTYPE; + s project.orders%ROWTYPE; + v_market project.markets%ROWTYPE; + v_trade_id bigint; + v_release numeric; + v_cost numeric; + v_proceeds numeric; + v_avg numeric; +BEGIN + IF p_buy_order IS NULL AND p_sell_order IS NULL THEN + RAISE EXCEPTION 'a trade needs at least one order' USING ERRCODE = 'check_violation'; + END IF; + + -- lock both orders in a fixed order (by id) so two concurrent trades on + -- the same pair of orders cannot deadlock + PERFORM 1 FROM project.orders WHERE id IN (p_buy_order, p_sell_order) ORDER BY id FOR UPDATE; + SELECT * INTO b FROM project.orders WHERE id = p_buy_order; + SELECT * INTO s FROM project.orders WHERE id = p_sell_order; + SELECT * INTO v_market FROM project.markets WHERE id = COALESCE(b.market_id, s.market_id); + + INSERT INTO project.market_trades + (market_id, executed_at, price, quantity, side, source, buy_order_id, sell_order_id) + VALUES (v_market.id, now(), p_price, p_quantity, + COALESCE(p_aggressor, CASE WHEN p_sell_order IS NULL THEN 'buy' ELSE 'sell' END), + CASE WHEN p_buy_order IS NOT NULL AND p_sell_order IS NOT NULL THEN 'match' ELSE 'market' END, + p_buy_order, p_sell_order) + RETURNING id INTO v_trade_id; + + IF p_buy_order IS NOT NULL THEN + v_release := project.order_reservation(b.quantity - b.filled_quantity, b.price) + - project.order_reservation(b.quantity - b.filled_quantity - p_quantity, b.price); + v_cost := LEAST(round(p_quantity * p_price, 4), v_release); + + UPDATE project.users + SET reserved_balance = reserved_balance - v_release, + available_balance = available_balance + (v_release - v_cost), + invested_balance = invested_balance + v_cost, + updated_at = now() + WHERE id = b.user_id; + + INSERT INTO project.holdings AS h (user_id, crypto_id, quantity, avg_price, updated_at) + VALUES (b.user_id, v_market.crypto_id, p_quantity, p_price, now()) + ON CONFLICT (user_id, crypto_id) DO UPDATE + SET avg_price = (h.quantity * h.avg_price + EXCLUDED.quantity * EXCLUDED.avg_price) + / (h.quantity + EXCLUDED.quantity), + quantity = h.quantity + EXCLUDED.quantity, + updated_at = now(); + + INSERT INTO project.transactions (user_id, type, amount, currency, related_order, description) + VALUES (b.user_id, 'buy', -v_cost, v_market.quote_currency, b.id, + format('Buy %s @ %s (trade %s)', p_quantity, p_price, v_trade_id)); + END IF; + + IF p_sell_order IS NOT NULL THEN + SELECT avg_price INTO v_avg FROM project.holdings + WHERE user_id = s.user_id AND crypto_id = v_market.crypto_id FOR UPDATE; + + UPDATE project.holdings + SET quantity = quantity - p_quantity, + reserved_quantity = reserved_quantity - p_quantity, + updated_at = now() + WHERE user_id = s.user_id AND crypto_id = v_market.crypto_id; + + v_proceeds := round(p_quantity * p_price, 4); + UPDATE project.users + SET available_balance = available_balance + v_proceeds, + invested_balance = GREATEST(invested_balance - round(p_quantity * v_avg, 4), 0), + updated_at = now() + WHERE id = s.user_id; + + INSERT INTO project.transactions (user_id, type, amount, currency, related_order, description) + VALUES (s.user_id, 'sell', v_proceeds, v_market.quote_currency, s.id, + format('Sell %s @ %s (trade %s)', p_quantity, p_price, v_trade_id)); + END IF; + + RETURN v_trade_id; +END $$; + +-- match_order: trade an order against the opposite side of the order book - +-- other users' active limit orders on the same market whose price is +-- acceptable - best price first, then oldest first (price-time priority). +-- Each trade is at the resting order's price. Returns the number of trades. +CREATE OR REPLACE FUNCTION project.match_order(p_order_id uuid) +RETURNS int LANGUAGE plpgsql AS $$ +DECLARE + o project.orders%ROWTYPE; + r record; + v_rem numeric; + v_qty numeric; + v_count int := 0; +BEGIN + SELECT * INTO o FROM project.orders WHERE id = p_order_id FOR UPDATE; + FOR r IN + SELECT id, price, quantity - filled_quantity AS remaining + FROM project.orders + WHERE market_id = o.market_id + AND side <> o.side + AND type = 'limit' + AND status IN ('open', 'partially_filled') + AND user_id <> o.user_id + AND (o.side = 'buy' AND price <= o.price + OR o.side = 'sell' AND price >= o.price) + ORDER BY CASE WHEN o.side = 'buy' THEN price END ASC, + CASE WHEN o.side = 'sell' THEN price END DESC, + placed_at, id + FOR UPDATE + LOOP + SELECT quantity - filled_quantity INTO v_rem FROM project.orders WHERE id = p_order_id; + EXIT WHEN v_rem = 0; + v_qty := LEAST(v_rem, r.remaining); + IF o.side = 'buy' THEN + PERFORM project.execute_trade(o.id, r.id, v_qty, r.price, 'buy'); + ELSE + PERFORM project.execute_trade(r.id, o.id, v_qty, r.price, 'sell'); + END IF; + v_count := v_count + 1; + END LOOP; + RETURN v_count; +END $$; + +-- place_order: the one correct way to place an order. +-- 1. checks the market, side, type, quantity and price; +-- 2. locks the user and reserves what the order commits: cash +-- (remaining x price) for a buy, crypto for a sell - refusing the order +-- if not enough is free; +-- 3. records the order (open); +-- 4. matches it against the order book (match_order); +-- 5. whatever is still unfilled and is marketable at the current market +-- price trades with the simulated market. A market order is priced at +-- the current market price, so it always fills completely here; a limit +-- order that is not marketable stays in the book. +-- Returns the order id. +CREATE OR REPLACE FUNCTION project.place_order( + p_user_id uuid, p_market_id uuid, p_side varchar, p_type varchar, + p_quantity numeric, p_limit_price numeric DEFAULT NULL) +RETURNS uuid LANGUAGE plpgsql AS $$ +DECLARE + v_market numeric := project.latest_price(p_market_id); + v_price numeric; + v_available numeric; + v_free numeric; + v_crypto uuid; + v_order uuid; + v_rem numeric; +BEGIN + IF p_side NOT IN ('buy', 'sell') OR p_type NOT IN ('market', 'limit') THEN + RAISE EXCEPTION 'invalid side % or type %', p_side, p_type USING ERRCODE = 'check_violation'; + END IF; + IF p_quantity IS NULL OR p_quantity <= 0 THEN + RAISE EXCEPTION 'quantity must be positive' USING ERRCODE = 'check_violation'; + END IF; + IF p_type = 'limit' THEN + IF p_limit_price IS NULL OR p_limit_price <= 0 THEN + RAISE EXCEPTION 'a limit order needs a positive limit price' USING ERRCODE = 'check_violation'; + END IF; + v_price := p_limit_price; + ELSE + IF v_market IS NULL THEN + RAISE EXCEPTION 'market has no price yet' USING ERRCODE = 'check_violation'; + END IF; + v_price := v_market; + END IF; + + SELECT available_balance INTO v_available FROM project.users WHERE id = p_user_id FOR UPDATE; + IF NOT FOUND THEN + RAISE EXCEPTION 'user % does not exist', p_user_id USING ERRCODE = 'no_data_found'; + END IF; + + IF p_side = 'buy' THEN + IF v_available < project.order_reservation(p_quantity, v_price) THEN + RAISE EXCEPTION 'insufficient funds: the order needs %, available %', + project.order_reservation(p_quantity, v_price), v_available + USING ERRCODE = 'check_violation'; + END IF; + UPDATE project.users + SET available_balance = available_balance - project.order_reservation(p_quantity, v_price), + reserved_balance = reserved_balance + project.order_reservation(p_quantity, v_price), + updated_at = now() + WHERE id = p_user_id; + ELSE + SELECT crypto_id INTO v_crypto FROM project.markets WHERE id = p_market_id; + SELECT quantity - reserved_quantity INTO v_free FROM project.holdings + WHERE user_id = p_user_id AND crypto_id = v_crypto FOR UPDATE; + IF COALESCE(v_free, 0) < p_quantity THEN + RAISE EXCEPTION 'insufficient holding: trying to sell %, free to sell %', p_quantity, COALESCE(v_free, 0) + USING ERRCODE = 'check_violation'; + END IF; + UPDATE project.holdings + SET reserved_quantity = reserved_quantity + p_quantity, updated_at = now() + WHERE user_id = p_user_id AND crypto_id = v_crypto; + END IF; + + INSERT INTO project.orders (user_id, market_id, side, type, status, quantity, price) + VALUES (p_user_id, p_market_id, p_side, p_type, 'open', p_quantity, v_price) + RETURNING id INTO v_order; + + PERFORM project.match_order(v_order); + + SELECT quantity - filled_quantity INTO v_rem FROM project.orders WHERE id = v_order; + IF v_rem > 0 AND v_market IS NOT NULL + AND (p_side = 'buy' AND v_market <= v_price OR p_side = 'sell' AND v_market >= v_price) THEN + IF p_side = 'buy' THEN + PERFORM project.execute_trade(v_order, NULL, v_rem, v_market, 'buy'); + ELSE + PERFORM project.execute_trade(NULL, v_order, v_rem, v_market, 'sell'); + END IF; + END IF; + RETURN v_order; +END $$; + +-- cancel_order: cancels an active order and releases what it still +-- reserves. p_user_id = NULL is a system cancellation (no ownership check). +CREATE OR REPLACE FUNCTION project.cancel_order(p_order_id uuid, p_user_id uuid DEFAULT NULL) +RETURNS void LANGUAGE plpgsql AS $$ +DECLARE + o project.orders%ROWTYPE; + v_rem numeric; +BEGIN + SELECT * INTO o FROM project.orders WHERE id = p_order_id FOR UPDATE; + IF NOT FOUND THEN + RAISE EXCEPTION 'order % does not exist', p_order_id USING ERRCODE = 'no_data_found'; + END IF; + IF p_user_id IS NOT NULL AND o.user_id <> p_user_id THEN + RAISE EXCEPTION 'order % does not belong to this user', p_order_id + USING ERRCODE = 'insufficient_privilege'; + END IF; + IF o.status NOT IN ('open', 'partially_filled') THEN + RAISE EXCEPTION 'order % is % and cannot be cancelled', p_order_id, o.status + USING ERRCODE = 'check_violation'; + END IF; + + v_rem := o.quantity - o.filled_quantity; + IF o.side = 'buy' THEN + UPDATE project.users + SET reserved_balance = reserved_balance - project.order_reservation(v_rem, o.price), + available_balance = available_balance + project.order_reservation(v_rem, o.price), + updated_at = now() + WHERE id = o.user_id; + ELSE + UPDATE project.holdings h + SET reserved_quantity = h.reserved_quantity - v_rem, updated_at = now() + FROM project.markets m + WHERE m.id = o.market_id AND h.user_id = o.user_id AND h.crypto_id = m.crypto_id; + END IF; + + UPDATE project.orders SET status = 'cancelled' WHERE id = p_order_id; +END $$; + +-- ============================================================================ +-- 6. VIEWS +-- ============================================================================ + +-- Active orders with what they still need and what they hold in reserve. +CREATE VIEW project.v_active_orders AS +SELECT o.id AS order_id, + o.user_id, + u.username, + o.market_id, + c.symbol, + m.quote_currency, + o.side, + o.type, + o.status, + o.quantity, + o.filled_quantity, + o.quantity - o.filled_quantity AS remaining, + o.price, + CASE WHEN o.side = 'buy' + THEN project.order_reservation(o.quantity - o.filled_quantity, o.price) ELSE 0 END AS reserved_cash, + CASE WHEN o.side = 'sell' THEN o.quantity - o.filled_quantity ELSE 0 END AS reserved_crypto, + o.placed_at +FROM project.orders o +JOIN project.users u ON u.id = o.user_id +JOIN project.markets m ON m.id = o.market_id +JOIN project.crypto c ON c.id = m.crypto_id +WHERE o.status IN ('open', 'partially_filled'); + +-- Current order book: resting limit orders aggregated per price level. +CREATE VIEW project.v_order_book AS +SELECT market_id, + symbol, + quote_currency, + side, + price, + SUM(remaining) AS quantity, + COUNT(*) AS orders +FROM project.v_active_orders +WHERE type = 'limit' +GROUP BY market_id, symbol, quote_currency, side, price; + +-- Every order with its fill progress and average fill price from its trades. +CREATE VIEW project.v_order_history AS +SELECT o.id AS order_id, + o.user_id, + u.username, + c.symbol, + o.side, + o.type, + o.status, + o.quantity, + o.filled_quantity, + o.quantity - o.filled_quantity AS remaining, + o.price, + f.trades, + f.avg_fill_price, + o.placed_at, + o.executed_at +FROM project.orders o +JOIN project.users u ON u.id = o.user_id +JOIN project.markets m ON m.id = o.market_id +JOIN project.crypto c ON c.id = m.crypto_id +LEFT JOIN LATERAL ( + SELECT COUNT(*) AS trades, + round(SUM(t.quantity * t.price) / NULLIF(SUM(t.quantity), 0), 6) AS avg_fill_price + FROM (SELECT quantity, price FROM project.market_trades WHERE buy_order_id = o.id + UNION ALL + SELECT quantity, price FROM project.market_trades WHERE sell_order_id = o.id) t +) f ON true; + +-- Trader balances: cash split into free and reserved, the ledger it must +-- equal, holdings at market value, and net worth. +CREATE VIEW project.v_trader_balances AS +SELECT u.id AS user_id, + u.username, + u.available_balance, + u.reserved_balance, + u.available_balance + u.reserved_balance AS total_cash, + COALESCE(l.ledger_total, 0) AS ledger_total, + u.invested_balance, + COALESCE(p.holdings_value, 0) AS holdings_value, + u.available_balance + u.reserved_balance + COALESCE(p.holdings_value, 0) AS net_worth +FROM project.users u +LEFT JOIN (SELECT user_id, SUM(amount) AS ledger_total + FROM project.transactions GROUP BY user_id) l ON l.user_id = u.id +LEFT JOIN (SELECT user_id, SUM(market_value) AS holdings_value + FROM project.v_portfolio GROUP BY user_id) p ON p.user_id = u.id; + +-- ============================================================================ +-- 7. BACKGROUND JOB +-- ============================================================================ + +-- EduBerza's market price moves with the simulator (bots/main.go), not with +-- user orders. A resting limit order - buy at or above, or sell at or below, +-- the current market price - must then be filled by the simulated market, +-- just as it would have been had the price already been there when it was +-- placed. Nothing else happens at that moment that a trigger could react to +-- (the bot's price ticks deliberately stay cheap inserts), so this runs as a +-- periodic job: the bot process calls it after every round of price ticks. +-- The faculty server has no pg_cron, so the scheduling is done there. +-- An advisory lock keeps two runs from filling the same orders twice; +-- SKIP LOCKED leaves any order a user is cancelling right now for next time. +-- Returns the number of orders filled. +CREATE OR REPLACE FUNCTION project.fill_marketable_orders() +RETURNS int LANGUAGE plpgsql AS $$ +DECLARE + r record; + v_count int := 0; +BEGIN + IF NOT pg_try_advisory_xact_lock(hashtext('project.fill_marketable_orders')) THEN + RETURN 0; + END IF; + FOR r IN + SELECT o.id, o.side, o.quantity - o.filled_quantity AS remaining, lp.price AS market_price + FROM project.orders o + JOIN project.markets m ON m.id = o.market_id AND m.is_active + CROSS JOIN LATERAL (SELECT project.latest_price(o.market_id) AS price) lp + WHERE o.type = 'limit' + AND o.status IN ('open', 'partially_filled') + AND (o.side = 'buy' AND o.price >= lp.price + OR o.side = 'sell' AND o.price <= lp.price) + ORDER BY o.placed_at, o.id + FOR UPDATE OF o SKIP LOCKED + LOOP + IF r.side = 'buy' THEN + PERFORM project.execute_trade(r.id, NULL, r.remaining, r.market_price, 'sell'); + ELSE + PERFORM project.execute_trade(NULL, r.id, r.remaining, r.market_price, 'buy'); + END IF; + v_count := v_count + 1; + END LOOP; + RETURN v_count; +END $$; Index: server/db/advanced_db_tests.sql =================================================================== --- server/db/advanced_db_tests.sql (revision ef1c1c725137d93fa85292f43440214aaddcdf05) +++ server/db/advanced_db_tests.sql (revision ef1c1c725137d93fa85292f43440214aaddcdf05) @@ -0,0 +1,270 @@ +-- advanced_db_tests.sql +-- EduBerza - tests for the P7 rules in advanced_db.sql +-- +-- Run right after data_load.sql (seed state: alice 8250 USD + 0.5 ETH, +-- bob 5000 USD, charlie 2500 USD, ETH/USD last traded at 3520). +-- It plays a short trading story and, along the way, tries to break every +-- rule. Each check prints PASS/FAIL as a NOTICE; everything is rolled back +-- at the end, so the data is left exactly as it was. +-- +-- The reservation and balance checks are deferred to COMMIT; the tests force +-- them with SET CONSTRAINTS ALL IMMEDIATE so a violation shows up inside the +-- test instead of at the final COMMIT. + +BEGIN; +SET search_path TO project, public; + +CREATE TEMP TABLE test_results (name text, passed boolean) ON COMMIT DROP; +CREATE TEMP TABLE ids (name text PRIMARY KEY, id uuid) ON COMMIT DROP; + +-- expect_error: run p_sql (and the deferred checks); it must fail with an +-- error containing p_fragment. +CREATE PROCEDURE pg_temp.expect_error(p_name text, p_sql text, p_fragment text) +LANGUAGE plpgsql AS $$ +BEGIN + BEGIN + EXECUTE p_sql; + SET CONSTRAINTS ALL IMMEDIATE; + RAISE NOTICE 'FAIL %: no error raised', p_name; + INSERT INTO test_results VALUES (p_name, false); + EXCEPTION WHEN OTHERS THEN + IF SQLERRM ILIKE '%' || p_fragment || '%' THEN + RAISE NOTICE 'PASS %: %', p_name, SQLERRM; + INSERT INTO test_results VALUES (p_name, true); + ELSE + RAISE NOTICE 'FAIL %: unexpected error: %', p_name, SQLERRM; + INSERT INTO test_results VALUES (p_name, false); + END IF; + END; + SET CONSTRAINTS ALL DEFERRED; +END $$; + +CREATE FUNCTION pg_temp.expect_true(p_name text, p_ok boolean, p_detail text DEFAULT '') +RETURNS void LANGUAGE plpgsql AS $$ +BEGIN + RAISE NOTICE '% %: %', CASE WHEN coalesce(p_ok, false) THEN 'PASS' ELSE 'FAIL' END, p_name, p_detail; + INSERT INTO test_results VALUES (p_name, coalesce(p_ok, false)); +END $$; + +-- consistent: all deferred checks pass right now +CREATE FUNCTION pg_temp.consistent() RETURNS boolean LANGUAGE plpgsql AS $$ +BEGIN + SET CONSTRAINTS ALL IMMEDIATE; + SET CONSTRAINTS ALL DEFERRED; + RETURN true; +EXCEPTION WHEN OTHERS THEN + RAISE NOTICE ' consistency check failed: %', SQLERRM; + RETURN false; +END $$; + +CREATE FUNCTION pg_temp.uid(p_name text) RETURNS uuid LANGUAGE sql AS $$ + SELECT id FROM users WHERE username = p_name +$$; +CREATE FUNCTION pg_temp.oid(p_name text) RETURNS uuid LANGUAGE sql AS $$ + SELECT id FROM ids WHERE name = p_name +$$; + + +-- =========================================================================== +-- A. Placing orders reserves what they commit +-- =========================================================================== +INSERT INTO ids VALUES ('alice_ask', + place_order(pg_temp.uid('alice'), 'a2222222-2222-2222-2222-222222222222', 'sell', 'limit', 0.3, 3600)); +INSERT INTO ids VALUES ('bob_bid', + place_order(pg_temp.uid('bob'), 'a2222222-2222-2222-2222-222222222222', 'buy', 'limit', 0.1, 3500)); + +SELECT pg_temp.expect_true('place: limit sell above the market rests in the book, crypto reserved', + (SELECT status FROM orders WHERE id = pg_temp.oid('alice_ask')) = 'open' + AND (SELECT reserved_quantity FROM holdings WHERE user_id = pg_temp.uid('alice')) = 0.3, + 'alice ETH reserved = ' || (SELECT reserved_quantity FROM holdings WHERE user_id = pg_temp.uid('alice'))); +SELECT pg_temp.expect_true('place: limit buy below the market rests in the book, cash reserved', + (SELECT (available_balance, reserved_balance) FROM users WHERE username = 'bob') = (4650.0000, 350.0000), + (SELECT format('bob available %s reserved %s', available_balance, reserved_balance) FROM users WHERE username = 'bob')); +SELECT pg_temp.expect_true('event: placement recorded automatically', + (SELECT count(*) FROM order_events WHERE order_id IN (pg_temp.oid('alice_ask'), pg_temp.oid('bob_bid')) + AND event_type = 'placed') = 2); +SELECT pg_temp.expect_true('view: order book shows both price levels', + (SELECT string_agg(side || ' ' || quantity || ' @ ' || price, ', ' ORDER BY side) + FROM v_order_book WHERE symbol = 'ETH') = 'buy 0.1000 @ 3500.000000, sell 0.3000 @ 3600.000000', + (SELECT string_agg(side || ' ' || quantity || ' @ ' || price, ', ' ORDER BY side) FROM v_order_book WHERE symbol = 'ETH')); +SELECT pg_temp.expect_true('consistency holds after placing', pg_temp.consistent()); + +CALL pg_temp.expect_error('place: buy without enough free cash', + $q$SELECT place_order(pg_temp.uid('charlie'), 'a1111111-1111-1111-1111-111111111111', 'buy', 'limit', 1, 60000)$q$, + 'insufficient funds'); +CALL pg_temp.expect_error('place: sell more than is free (0.2 of 0.5 is already reserved)', + $q$SELECT place_order(pg_temp.uid('alice'), 'a2222222-2222-2222-2222-222222222222', 'sell', 'limit', 0.3, 3600)$q$, + 'insufficient holding'); + +-- =========================================================================== +-- B. A trade between two compatible orders, partial fill +-- =========================================================================== +-- bob bids 0.2 at 3650: crosses alice's ask at 3600 -> trade 0.2 @ 3600 +INSERT INTO ids VALUES ('bob_bid2', + place_order(pg_temp.uid('bob'), 'a2222222-2222-2222-2222-222222222222', 'buy', 'limit', 0.2, 3650)); + +SELECT pg_temp.expect_true('match: trade between the two orders at the resting price', + EXISTS (SELECT 1 FROM market_trades + WHERE buy_order_id = pg_temp.oid('bob_bid2') AND sell_order_id = pg_temp.oid('alice_ask') + AND quantity = 0.2 AND price = 3600 AND source = 'match')); +SELECT pg_temp.expect_true('status: seller partially filled, buyer executed (automatic)', + (SELECT status || ' ' || filled_quantity FROM orders WHERE id = pg_temp.oid('alice_ask')) = 'partially_filled 0.2000' + AND (SELECT status FROM orders WHERE id = pg_temp.oid('bob_bid2')) = 'executed', + (SELECT format('alice_ask %s %s/%s', status, filled_quantity, quantity) FROM orders WHERE id = pg_temp.oid('alice_ask'))); +SELECT pg_temp.expect_true('money: buyer paid 720, got back the 10 reserved above the trade price', + (SELECT (available_balance, reserved_balance) FROM users WHERE username = 'bob') = (3930.0000, 350.0000), + (SELECT format('bob available %s reserved %s', available_balance, reserved_balance) FROM users WHERE username = 'bob')); +SELECT pg_temp.expect_true('crypto: 0.2 ETH moved from alice (0.1 still reserved) to bob', + (SELECT (quantity, reserved_quantity) FROM holdings WHERE user_id = pg_temp.uid('alice')) = (0.3000, 0.1000) + AND (SELECT (quantity, avg_price) FROM holdings WHERE user_id = pg_temp.uid('bob')) = (0.2000, 3600.000000)); +SELECT pg_temp.expect_true('ledger: one buy and one sell row, linked to the orders', + (SELECT count(*) FROM transactions WHERE related_order IN (pg_temp.oid('bob_bid2'), pg_temp.oid('alice_ask'))) = 2); +SELECT pg_temp.expect_true('event: fills recorded automatically', + (SELECT string_agg(event_type, ',' ORDER BY id) FROM order_events WHERE order_id = pg_temp.oid('alice_ask')) + = 'placed,partially_filled'); +SELECT pg_temp.expect_true('consistency holds after the trade', pg_temp.consistent()); + +-- =========================================================================== +-- C. Market order: book first, then the simulated market +-- =========================================================================== +-- market price is now 3600 (last trade); charlie buys 0.15 at market: +-- 0.1 from alice's remaining ask @ 3600, the other 0.05 from the market @ 3600 +INSERT INTO ids VALUES ('charlie_mkt', + place_order(pg_temp.uid('charlie'), 'a2222222-2222-2222-2222-222222222222', 'buy', 'market', 0.15)); + +SELECT pg_temp.expect_true('market order: filled completely in two trades', + (SELECT (status, trades, avg_fill_price) FROM v_order_history WHERE order_id = pg_temp.oid('charlie_mkt')) + = ('executed'::varchar, 2::bigint, 3600.000000::numeric), + (SELECT format('%s, %s trades, avg %s', status, trades, avg_fill_price) FROM v_order_history WHERE order_id = pg_temp.oid('charlie_mkt'))); +SELECT pg_temp.expect_true('market order: alice''s ask is now executed, nothing left reserved', + (SELECT status FROM orders WHERE id = pg_temp.oid('alice_ask')) = 'executed' + AND (SELECT reserved_quantity FROM holdings WHERE user_id = pg_temp.uid('alice')) = 0 + AND (SELECT reserved_balance FROM users WHERE username = 'charlie') = 0); +SELECT pg_temp.expect_true('consistency holds after the market order', pg_temp.consistent()); + +-- =========================================================================== +-- D. Trades are only possible between valid, compatible orders +-- =========================================================================== +INSERT INTO ids VALUES ('charlie_ask', + place_order(pg_temp.uid('charlie'), 'a2222222-2222-2222-2222-222222222222', 'sell', 'limit', 0.1, 3700)); +INSERT INTO ids VALUES ('bob_ask', + place_order(pg_temp.uid('bob'), 'a2222222-2222-2222-2222-222222222222', 'sell', 'limit', 0.1, 3800)); + +CALL pg_temp.expect_error('trade: price above the buyer''s limit', + $q$INSERT INTO market_trades (market_id, executed_at, price, quantity, buy_order_id, sell_order_id) + VALUES ('a2222222-2222-2222-2222-222222222222', now(), 3700, 0.1, pg_temp.oid('bob_bid'), pg_temp.oid('charlie_ask'))$q$, + 'outside the limit'); +CALL pg_temp.expect_error('trade: more than the order has remaining', + $q$INSERT INTO market_trades (market_id, executed_at, price, quantity, buy_order_id) + VALUES ('a2222222-2222-2222-2222-222222222222', now(), 3500, 0.5, pg_temp.oid('bob_bid'))$q$, + 'exceeds the remaining quantity'); +CALL pg_temp.expect_error('trade: a sell order used as the buy side', + $q$INSERT INTO market_trades (market_id, executed_at, price, quantity, buy_order_id) + VALUES ('a2222222-2222-2222-2222-222222222222', now(), 3700, 0.1, pg_temp.oid('charlie_ask'))$q$, + 'cannot be the buy side'); +CALL pg_temp.expect_error('trade: order of another market', + $q$INSERT INTO market_trades (market_id, executed_at, price, quantity, buy_order_id) + VALUES ('a1111111-1111-1111-1111-111111111111', now(), 3500, 0.1, pg_temp.oid('bob_bid'))$q$, + 'different market'); +CALL pg_temp.expect_error('trade: executed order cannot trade again', + $q$INSERT INTO market_trades (market_id, executed_at, price, quantity, sell_order_id) + VALUES ('a2222222-2222-2222-2222-222222222222', now(), 3600, 0.1, pg_temp.oid('alice_ask'))$q$, + 'is executed and cannot trade'); +CALL pg_temp.expect_error('trade: a user with their own order', + $q$INSERT INTO market_trades (market_id, executed_at, price, quantity, buy_order_id, sell_order_id) + VALUES ('a2222222-2222-2222-2222-222222222222', now(), 3500, 0.1, pg_temp.oid('bob_bid'), pg_temp.oid('bob_ask'))$q$, + 'own order'); +CALL pg_temp.expect_error('trade: a trade that filled orders cannot be deleted', + $q$DELETE FROM market_trades WHERE buy_order_id = pg_temp.oid('bob_bid2')$q$, + 'cannot be changed or deleted'); + +-- =========================================================================== +-- E. Order state changes +-- =========================================================================== +CALL pg_temp.expect_error('state: status cannot be set to executed by hand', + $q$UPDATE orders SET status = 'executed' WHERE id = pg_temp.oid('bob_bid')$q$, + 'status is derived automatically'); +CALL pg_temp.expect_error('state: filled quantity cannot be changed by hand', + $q$UPDATE orders SET filled_quantity = 0.05 WHERE id = pg_temp.oid('bob_bid')$q$, + 'can only change through a trade'); +CALL pg_temp.expect_error('state: executed order cannot be processed again', + $q$UPDATE orders SET status = 'cancelled' WHERE id = pg_temp.oid('alice_ask')$q$, + 'already executed'); +CALL pg_temp.expect_error('state: ordered quantity cannot change', + $q$UPDATE orders SET quantity = 1 WHERE id = pg_temp.oid('bob_bid')$q$, + 'cannot change'); +CALL pg_temp.expect_error('state: cancelling by hand without releasing the reservation', + $q$UPDATE orders SET status = 'cancelled' WHERE id = pg_temp.oid('bob_bid')$q$, + 'reserved balance'); + +-- =========================================================================== +-- F. Cancelling releases the reservation, exactly once +-- =========================================================================== +CALL pg_temp.expect_error('cancel: someone else''s order', + $q$SELECT cancel_order(pg_temp.oid('bob_bid'), pg_temp.uid('charlie'))$q$, + 'does not belong'); +SELECT cancel_order(pg_temp.oid('bob_bid'), pg_temp.uid('bob')); +SELECT pg_temp.expect_true('cancel: 350 back from reserved to available, event recorded', + (SELECT (available_balance, reserved_balance) FROM users WHERE username = 'bob') = (4280.0000, 0.0000) + AND EXISTS (SELECT 1 FROM order_events WHERE order_id = pg_temp.oid('bob_bid') AND event_type = 'cancelled'), + (SELECT format('bob available %s reserved %s', available_balance, reserved_balance) FROM users WHERE username = 'bob')); +CALL pg_temp.expect_error('cancel: a cancelled order cannot be cancelled again', + $q$SELECT cancel_order(pg_temp.oid('bob_bid'), pg_temp.uid('bob'))$q$, + 'cannot be cancelled'); +CALL pg_temp.expect_error('trade: cancelled order cannot trade', + $q$INSERT INTO market_trades (market_id, executed_at, price, quantity, buy_order_id) + VALUES ('a2222222-2222-2222-2222-222222222222', now(), 3500, 0.1, pg_temp.oid('bob_bid'))$q$, + 'is cancelled and cannot trade'); + +-- =========================================================================== +-- G. Balances cannot be put in an inconsistent state +-- =========================================================================== +CALL pg_temp.expect_error('balance: reserving cash with no order behind it', + $q$UPDATE users SET available_balance = available_balance - 100, reserved_balance = reserved_balance + 100 + WHERE username = 'charlie'$q$, + 'reserved balance'); +CALL pg_temp.expect_error('balance: reserving crypto with no order behind it', + $q$UPDATE holdings SET reserved_quantity = reserved_quantity + 0.01 WHERE user_id = pg_temp.uid('alice')$q$, + 'reserved quantity'); +CALL pg_temp.expect_error('balance: cash changed without a ledger row', + $q$UPDATE users SET available_balance = available_balance + 100 WHERE username = 'charlie'$q$, + 'does not match the ledger'); + +-- =========================================================================== +-- H. Background job: resting limit orders filled when the market reaches them +-- =========================================================================== +INSERT INTO ids VALUES ('bob_bid3', + place_order(pg_temp.uid('bob'), 'a2222222-2222-2222-2222-222222222222', 'buy', 'limit', 0.1, 3500)); +-- the simulator moves the price down to 3450 (a bot tick, no orders) +INSERT INTO market_trades (market_id, executed_at, price, quantity, side) +VALUES ('a2222222-2222-2222-2222-222222222222', now(), 3450, 0.01, 'sell'); + +CREATE TEMP TABLE job_run ON COMMIT DROP AS SELECT fill_marketable_orders() AS filled; + +SELECT pg_temp.expect_true('job: fills exactly the orders the new price reached', + (SELECT filled FROM job_run) = 1 + AND (SELECT (status, avg_fill_price) FROM v_order_history WHERE order_id = pg_temp.oid('bob_bid3')) + = ('executed'::varchar, 3450.000000::numeric) + AND (SELECT status FROM orders WHERE id = pg_temp.oid('charlie_ask')) = 'open', + (SELECT format('bob_bid3 %s @ %s, charlie_ask (3700) still %s', h.status, h.avg_fill_price, o.status) + FROM v_order_history h, orders o WHERE h.order_id = pg_temp.oid('bob_bid3') AND o.id = pg_temp.oid('charlie_ask'))); +SELECT pg_temp.expect_true('job: buyer paid 345, got the 5 above the fill price back', + (SELECT reserved_balance FROM users WHERE username = 'bob') = 0 + AND (SELECT count(*) FROM transactions WHERE related_order = pg_temp.oid('bob_bid3') AND amount = -345) = 1); +SELECT pg_temp.expect_true('job: nothing more to do on a second run', fill_marketable_orders() = 0); +SELECT pg_temp.expect_true('consistency holds after the job', pg_temp.consistent()); + +-- =========================================================================== +-- Final state of every trader +-- =========================================================================== +SELECT pg_temp.expect_true('views: every trader''s cash equals their ledger', + NOT EXISTS (SELECT 1 FROM v_trader_balances WHERE total_cash <> ledger_total)); + +SELECT username, available_balance, reserved_balance, ledger_total, holdings_value + FROM v_trader_balances ORDER BY username; + +SELECT count(*) FILTER (WHERE passed) AS passed, + count(*) FILTER (WHERE NOT passed) AS failed + FROM test_results; + +ROLLBACK; Index: server/db/data_load.sql =================================================================== --- server/db/data_load.sql (revision 9e6d8a2e95a15f67178a3f9b0a89aafa9a1d7f59) +++ server/db/data_load.sql (revision ef1c1c725137d93fa85292f43440214aaddcdf05) @@ -8,4 +8,11 @@ -- -- All sample users have the password: test123 +-- +-- One transaction: the P7 checks in advanced_db.sql compare balances with +-- the ledger at COMMIT, and the users are inserted with their balances +-- before the deposit rows that back them. In an auto-commit client +-- (DBeaver) every statement would otherwise be checked on its own. + +BEGIN; SET search_path TO project, public; @@ -104,9 +111,10 @@ -- 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 +-- Imported as already completely filled (filled_quantity = quantity). +INSERT INTO project.orders (id, user_id, market_id, side, type, status, quantity, filled_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, + 'buy', 'market', 'executed', 0.5000, 0.5000, 3500.000000, now() - interval '1 hour', now() - interval '1 hour'); @@ -118,4 +126,8 @@ 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'), + ('b2222222-2222-2222-2222-222222222222', 'deposit', 5000.0000, 'USD', NULL, + 'Initial virtual deposit'), + ('b3333333-3333-3333-3333-333333333333', 'deposit', 2500.0000, 'USD', NULL, 'Initial virtual deposit'), ('b1111111-1111-1111-1111-111111111111', 'buy', -1750.0000, 'USD', @@ -143,2 +155,4 @@ ('d2222222-2222-2222-2222-222222222222', '11111111-1111-1111-1111-111111111111'), ('d2222222-2222-2222-2222-222222222222', '55555555-5555-5555-5555-555555555555'); + +COMMIT; Index: server/db/db.go =================================================================== --- server/db/db.go (revision 9e6d8a2e95a15f67178a3f9b0a89aafa9a1d7f59) +++ server/db/db.go (revision ef1c1c725137d93fa85292f43440214aaddcdf05) @@ -11,5 +11,5 @@ "strings" - _ "github.com/lib/pq" + "github.com/lib/pq" ) @@ -19,5 +19,5 @@ // which directory the program is started from. // -//go:embed schema_creation.sql data_load.sql +//go:embed schema_creation.sql advanced_db.sql data_load.sql var sqlScripts embed.FS @@ -36,8 +36,24 @@ ) - var err error - DB, err = sql.Open("postgres", dsn) - if err != nil { - return fmt.Errorf("sql.Open: %w", err) + // Optional SSH tunnel, the same thing DBeaver's "SSH" tab does. When + // SSH_HOST is set, DBHOST/DBPORT are resolved from the SSH server's side + // (for the faculty server that is usually localhost:5432). + if sshHost := os.Getenv("SSH_HOST"); sshHost != "" { + dialer, err := newSSHDialer(sshHost) + if err != nil { + return err + } + connector, err := pq.NewConnector(dsn) + if err != nil { + return fmt.Errorf("pq.NewConnector: %w", err) + } + connector.Dialer(dialer) + DB = sql.OpenDB(connector) + } else { + var err error + DB, err = sql.Open("postgres", dsn) + if err != nil { + return fmt.Errorf("sql.Open: %w", err) + } } if err := DB.Ping(); err != nil { @@ -72,10 +88,12 @@ } -// InitSchema runs schema_creation.sql then data_load.sql. +// InitSchema runs schema_creation.sql, advanced_db.sql (P7) and data_load.sql. // Destructive: drops the `project` schema. Intended for the -init flag. func InitSchema() error { - log.Println("Running schema_creation.sql ...") - if err := runScript("schema_creation.sql"); err != nil { - return err + for _, name := range []string{"schema_creation.sql", "advanced_db.sql"} { + log.Printf("Running %s ...", name) + if err := runScript(name); err != nil { + return err + } } if err := LoadData(); err != nil { Index: server/db/reports_demo_data.sql =================================================================== --- server/db/reports_demo_data.sql (revision 9e6d8a2e95a15f67178a3f9b0a89aafa9a1d7f59) +++ server/db/reports_demo_data.sql (revision ef1c1c725137d93fa85292f43440214aaddcdf05) @@ -28,5 +28,18 @@ -- description/source) before re-inserting. +-- P7: users' cash (available + reserved) must equal their ledger at +-- COMMIT, so the balance moves by exactly what this script removes and +-- re-adds to the ledger, all in one transaction. The historical orders are +-- imported as completely filled. + +BEGIN; + SET search_path TO project, public; + +UPDATE users u + SET available_balance = u.available_balance - d.total + FROM (SELECT user_id, SUM(amount) AS total FROM transactions + WHERE description = 'P6 demo data' GROUP BY user_id) d + WHERE u.id = d.user_id; DELETE FROM transactions WHERE description = 'P6 demo data'; @@ -98,18 +111,26 @@ -- Executed orders: who participated in which market, across the same quarters. -- ============================================================================ -INSERT INTO orders (id, user_id, market_id, side, type, status, quantity, price, placed_at, executed_at) VALUES +INSERT INTO orders (id, user_id, market_id, side, type, status, quantity, filled_quantity, price, placed_at, executed_at) VALUES ('e1111111-1111-1111-1111-111111111111', 'b1111111-1111-1111-1111-111111111111', - 'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 0.5000, 40000.000000, + 'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 0.5000, 0.5000, 40000.000000, '2025-07-15 10:00', '2025-07-15 10:00'), ('e2222222-2222-2222-2222-222222222222', 'b1111111-1111-1111-1111-111111111111', - 'a2222222-2222-2222-2222-222222222222', 'sell', 'market', 'executed', 3.0000, 4000.000000, + 'a2222222-2222-2222-2222-222222222222', 'sell', 'market', 'executed', 3.0000, 3.0000, 4000.000000, '2025-10-15 10:00', '2025-10-15 10:00'), ('e3333333-3333-3333-3333-333333333333', 'b2222222-2222-2222-2222-222222222222', - 'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 1.2000, 55000.000000, + 'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 1.2000, 1.2000, 55000.000000, '2026-01-15 10:00', '2026-01-15 10:00'), ('e4444444-4444-4444-4444-444444444444', 'b2222222-2222-2222-2222-222222222222', - 'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 1.0000, 60000.000000, + 'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 1.0000, 1.0000, 60000.000000, '2026-04-15 10:00', '2026-04-15 10:00'), ('e5555555-5555-5555-5555-555555555555', 'b3333333-3333-3333-3333-333333333333', - 'a2222222-2222-2222-2222-222222222222', 'sell', 'market', 'executed', 2.0000, 3600.000000, + 'a2222222-2222-2222-2222-222222222222', 'sell', 'market', 'executed', 2.0000, 2.0000, 3600.000000, '2026-01-15 10:00', '2026-01-15 10:00'); + +UPDATE users u + SET available_balance = u.available_balance + d.total + FROM (SELECT user_id, SUM(amount) AS total FROM transactions + WHERE description = 'P6 demo data' GROUP BY user_id) d + WHERE u.id = d.user_id; + +COMMIT; Index: server/db/schema_creation.sql =================================================================== --- server/db/schema_creation.sql (revision 9e6d8a2e95a15f67178a3f9b0a89aafa9a1d7f59) +++ server/db/schema_creation.sql (revision ef1c1c725137d93fa85292f43440214aaddcdf05) @@ -26,4 +26,7 @@ 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), + -- P7: cash committed to the user's active buy orders, moved out of + -- available_balance when the order is placed and consumed as it fills. + reserved_balance numeric(18,4) NOT NULL DEFAULT 0 CHECK (reserved_balance >= 0), created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz @@ -86,6 +89,10 @@ 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')), + status varchar(20) NOT NULL CHECK (status IN ('open', 'partially_filled', 'executed', 'cancelled')), quantity numeric(20,4) NOT NULL CHECK (quantity > 0), + -- P7: how much of the order has been traded so far; remaining is + -- quantity - filled_quantity. Maintained from market_trades. + filled_quantity numeric(20,4) NOT NULL DEFAULT 0 + CHECK (filled_quantity >= 0 AND filled_quantity <= quantity), price numeric(18,6), placed_at timestamptz NOT NULL DEFAULT now(), @@ -125,8 +132,14 @@ quantity numeric(20,6) NOT NULL CHECK (quantity > 0), side varchar(4) CHECK (side IN ('buy', 'sell')), - source varchar(50) NOT NULL DEFAULT 'simulation' + source varchar(50) NOT NULL DEFAULT 'simulation', + -- P7: the orders this trade filled. NULL on a side means the counterparty + -- was the simulated market (bot ticks have both NULL). + buy_order_id uuid REFERENCES project.orders(id), + sell_order_id uuid REFERENCES project.orders(id) ); CREATE INDEX idx_market_trades_market_time ON project.market_trades(market_id, executed_at DESC); +CREATE INDEX idx_market_trades_buy_order ON project.market_trades(buy_order_id) WHERE buy_order_id IS NOT NULL; +CREATE INDEX idx_market_trades_sell_order ON project.market_trades(sell_order_id) WHERE sell_order_id IS NOT NULL; -- ============================================================================ Index: server/db/ssh.go =================================================================== --- server/db/ssh.go (revision ef1c1c725137d93fa85292f43440214aaddcdf05) +++ server/db/ssh.go (revision ef1c1c725137d93fa85292f43440214aaddcdf05) @@ -0,0 +1,72 @@ +package db + +import ( + "fmt" + "net" + "os" + "time" + + "golang.org/x/crypto/ssh" +) + +// sshDialer implements pq.Dialer by opening every database connection +// through an SSH client, so no separate `ssh -L` tunnel is needed. +type sshDialer struct { + client *ssh.Client +} + +func (d sshDialer) Dial(network, address string) (net.Conn, error) { + return d.client.Dial(network, address) +} + +func (d sshDialer) DialTimeout(network, address string, _ time.Duration) (net.Conn, error) { + return d.client.Dial(network, address) +} + +// newSSHDialer connects to the SSH server described by the SSH_* variables. +// Authentication uses SSH_KEY (path to a private key) and/or SSH_PASSWORD. +func newSSHDialer(host string) (sshDialer, error) { + port := getenv("SSH_PORT", "22") + user := os.Getenv("SSH_USER") + if user == "" { + return sshDialer{}, fmt.Errorf("SSH_HOST is set but SSH_USER is empty") + } + + var auth []ssh.AuthMethod + if keyPath := os.Getenv("SSH_KEY"); keyPath != "" { + key, err := os.ReadFile(keyPath) + if err != nil { + return sshDialer{}, fmt.Errorf("read SSH_KEY: %w", err) + } + var signer ssh.Signer + if phrase := os.Getenv("SSH_KEY_PASSPHRASE"); phrase != "" { + signer, err = ssh.ParsePrivateKeyWithPassphrase(key, []byte(phrase)) + } else { + signer, err = ssh.ParsePrivateKey(key) + } + if err != nil { + return sshDialer{}, fmt.Errorf("parse SSH_KEY: %w", err) + } + auth = append(auth, ssh.PublicKeys(signer)) + } + if pass := os.Getenv("SSH_PASSWORD"); pass != "" { + auth = append(auth, ssh.Password(pass)) + } + if len(auth) == 0 { + return sshDialer{}, fmt.Errorf("SSH_HOST is set but neither SSH_KEY nor SSH_PASSWORD is") + } + + config := &ssh.ClientConfig{ + User: user, + Auth: auth, + // Host key is not pinned; acceptable for a course prototype that only + // talks to the faculty server. + HostKeyCallback: ssh.InsecureIgnoreHostKey(), + Timeout: 10 * time.Second, + } + client, err := ssh.Dial("tcp", net.JoinHostPort(host, port), config) + if err != nil { + return sshDialer{}, fmt.Errorf("ssh dial %s@%s:%s: %w", user, host, port, err) + } + return sshDialer{client: client}, nil +}