| 1 | | = Advanced Database Development = |
| 2 | | |
| 3 | | !EduBerza's users trade virtual money and virtual crypto with each other and with a simulated |
| 4 | | market. Up to P6 the prototype only knew market orders that filled immediately against the |
| 5 | | latest price, so an order was either untouched or completely done. This phase adds what makes an |
| 6 | | exchange consistent once orders can '''wait in an order book, fill in parts, and trade with each other''', and puts every rule that keeps orders, trades and balances in agreement into the |
| 7 | | database itself — so it holds no matter who writes the data (the CLI, the market bot, a script, |
| 8 | | or someone typing SQL in DBeaver). |
| 9 | | |
| 10 | | Only rules that span several rows or several tables are listed. `NOT NULL`, `UNIQUE`, `CHECK`, |
| 11 | | primary and foreign keys are P2 ([wiki:RelationalDesign]) |
| 12 | | and are not presented as P7 features. |
| 13 | | |
| 14 | | All of it is in `server/db/advanced_db.sql`, run by `-init` |
| 15 | | between `schema_creation.sql` and `data_load.sql`. Every rule is exercised by |
| 16 | | `server/db/advanced_db_tests.sql` (see |
| 17 | | Tests). |
| 18 | | |
| 19 | | == Overview == |
| 20 | | |
| 21 | | ||= # =||= Requirement =||= Triggers =||= Procedures / functions =||= Views =||= Tables affected =|| |
| 22 | | || 1 || Order lifecycle and filled/remaining consistency || `orders_lifecycle` || || || `orders` || |
| 23 | | || 2 || Trade consistency || `market_trades_validate`, `market_trades_fill`, `market_trades_immutable` || `execute_trade` || || `market_trades`, `orders`, `users`, `holdings`, `transactions` || |
| 24 | | || 3 || Balance and reservation consistency || `reserved_cash_matches_orders`, `reserved_crypto_matches_orders`, `cash_matches_ledger` (deferred) || `order_reservation` || || `users`, `holdings`, `orders`, `transactions` || |
| 25 | | || 4 || Placing and cancelling orders || || `place_order`, `match_order`, `cancel_order` || || all of the above || |
| 26 | | || 5 || Automatic recording of order events || `orders_events` || || || `order_events` (new) || |
| 27 | | || 6 || Views for derived trading data || || || `v_order_book`, `v_active_orders`, `v_order_history`, `v_trader_balances` || read-only || |
| 28 | | || 7 || Background job: filling resting limit orders || || `fill_marketable_orders` || || `orders`, `market_trades`, … || |
| 29 | | |
| 30 | | === Schema additions === |
| 31 | | |
| 32 | | The existing design had no place for three things the requirements need, so four columns were |
| 33 | | added — nothing else in the design changed: |
| 34 | | |
| 35 | | ||= Column =||= Why it is needed =|| |
| 36 | | || `orders.filled_quantity` (+ status value `partially_filled`) || "filled / remaining quantity" cannot be kept consistent without storing how much was filled; remaining = `quantity − filled_quantity` || |
| 37 | | || `users.reserved_balance` || cash committed to open buy orders must be set aside somewhere; `holdings.reserved_quantity` already did this for crypto on sell orders || |
| 38 | | || `market_trades.buy_order_id`, `market_trades.sell_order_id` || "a trade can only happen between compatible orders" needs the trade to say which orders it filled. `NULL` on a side means the simulated market was the counterparty; the bot's price ticks have both `NULL` || |
| 39 | | |
| 40 | | One new table, `order_events`, holds the automatically recorded events (requirement 5). |
| 41 | | |
| 42 | | == Order lifecycle and filled/remaining consistency == |
| 43 | | |
| 44 | | === Data requirements description === |
| 45 | | |
| 46 | | '''Business rule.''' |
| 47 | | |
| 48 | | * An order's status follows from how much of it has been filled: nothing filled → `open`, partly filled → `partially_filled`, completely filled → `executed`. The only status that is set explicitly is `cancelled`, and only on an order that is still active. |
| 49 | | * `executed` and `cancelled` are final — such an order can never be processed again (no further fills, no cancellation, no changes). |
| 50 | | * `filled_quantity` only grows, never exceeds `quantity`, and only changes when a trade fills the order. |
| 51 | | * What was ordered — user, market, side, type, quantity, price, time of placement — never changes after placement. |
| 52 | | * `executed_at` is set exactly when the order becomes `executed`. |
| 53 | | * New orders are only accepted on active markets, need a positive price, and start unfilled (the one exception is importing an order that was completely executed in the past, used by the sample data). |
| 54 | | |
| 55 | | '''Why it is non-trivial.''' The rule compares the ''old'' and the ''new'' version of a row (valid |
| 56 | | transitions, "final" states, "only grows", immutable columns) and ties two columns together |
| 57 | | (status ↔ filled quantity). A `CHECK` constraint only sees one version of one row. It also |
| 58 | | depends on ''who'' changes `filled_quantity` — a trade may, a manual `UPDATE` may not. |
| 59 | | |
| 60 | | '''PostgreSQL feature.''' `BEFORE INSERT OR UPDATE` row trigger on `orders`. The trade trigger |
| 61 | | (requirement 2) sets a transaction-local setting (`set_config('eduberza.trade_fill', 'on', true)`) |
| 62 | | while it fills an order; the lifecycle trigger only accepts a change of `filled_quantity` when |
| 63 | | that setting is on. |
| 64 | | |
| 65 | | '''Tables affected.''' `orders` (reads `markets` for the active check). |
| 66 | | |
| 67 | | === Implementation === |
| 68 | | |
| 69 | | ==== Triggers ==== |
| 70 | | |
| 71 | | {{{ |
| 72 | | CREATE OR REPLACE FUNCTION project.trg_orders_lifecycle() |
| 73 | | RETURNS trigger LANGUAGE plpgsql AS $$ |
| 74 | | DECLARE |
| 75 | | v_derived varchar(20); |
| 76 | | BEGIN |
| 77 | | IF TG_OP = 'UPDATE' THEN |
| 78 | | IF OLD.status IN ('executed', 'cancelled') THEN |
| 79 | | RAISE EXCEPTION 'order % is already % and cannot be processed again', OLD.id, OLD.status |
| 80 | | USING ERRCODE = 'check_violation'; |
| 81 | | END IF; |
| 82 | | IF (NEW.user_id, NEW.market_id, NEW.side, NEW.type, NEW.quantity, NEW.price, NEW.placed_at) |
| 83 | | IS DISTINCT FROM |
| 84 | | (OLD.user_id, OLD.market_id, OLD.side, OLD.type, OLD.quantity, OLD.price, OLD.placed_at) THEN |
| 85 | | RAISE EXCEPTION 'user, market, side, type, quantity, price and placed_at of an order cannot change' |
| 86 | | USING ERRCODE = 'check_violation'; |
| 87 | | END IF; |
| 88 | | IF NEW.filled_quantity <> OLD.filled_quantity THEN |
| 89 | | IF current_setting('eduberza.trade_fill', true) IS DISTINCT FROM 'on' THEN |
| 90 | | RAISE EXCEPTION 'filled quantity of order % can only change through a trade', OLD.id |
| 91 | | USING ERRCODE = 'check_violation'; |
| 92 | | END IF; |
| 93 | | IF NEW.filled_quantity < OLD.filled_quantity THEN |
| 94 | | RAISE EXCEPTION 'filled quantity of order % cannot decrease', OLD.id |
| 95 | | USING ERRCODE = 'check_violation'; |
| 96 | | END IF; |
| 97 | | END IF; |
| 98 | | IF NEW.status = 'cancelled' AND OLD.status <> 'cancelled' THEN |
| 99 | | IF NEW.filled_quantity <> OLD.filled_quantity THEN |
| 100 | | RAISE EXCEPTION 'an order cannot be filled and cancelled in the same step' |
| 101 | | USING ERRCODE = 'check_violation'; |
| 102 | | END IF; |
| 103 | | NEW.executed_at := NULL; |
| 104 | | RETURN NEW; |
| 105 | | END IF; |
| 106 | | ELSE |
| 107 | | IF NOT EXISTS (SELECT 1 FROM project.markets WHERE id = NEW.market_id AND is_active) THEN |
| 108 | | RAISE EXCEPTION 'market % is not active; no new orders accepted', NEW.market_id |
| 109 | | USING ERRCODE = 'check_violation'; |
| 110 | | END IF; |
| 111 | | IF NEW.price IS NULL OR NEW.price <= 0 THEN |
| 112 | | RAISE EXCEPTION 'an order needs a positive price (limit price, or the market price for a market order)' |
| 113 | | USING ERRCODE = 'check_violation'; |
| 114 | | END IF; |
| 115 | | IF NEW.status = 'cancelled' THEN |
| 116 | | RAISE EXCEPTION 'an order cannot be created already cancelled' |
| 117 | | USING ERRCODE = 'check_violation'; |
| 118 | | END IF; |
| 119 | | -- A new order starts unfilled. The only exception is importing an |
| 120 | | -- order that was completely executed in the past (sample data). |
| 121 | | IF NEW.filled_quantity NOT IN (0, NEW.quantity) THEN |
| 122 | | RAISE EXCEPTION 'a new order is either unfilled or (imported history) completely filled' |
| 123 | | USING ERRCODE = 'check_violation'; |
| 124 | | END IF; |
| 125 | | END IF; |
| 126 | | |
| 127 | | v_derived := CASE |
| 128 | | WHEN NEW.filled_quantity = 0 THEN 'open' |
| 129 | | WHEN NEW.filled_quantity < NEW.quantity THEN 'partially_filled' |
| 130 | | ELSE 'executed' |
| 131 | | END; |
| 132 | | IF NEW.status IS DISTINCT FROM v_derived |
| 133 | | AND (TG_OP = 'INSERT' OR NEW.status IS DISTINCT FROM OLD.status) THEN |
| 134 | | RAISE EXCEPTION 'order status % does not match filled quantity % of %; status is derived automatically', |
| 135 | | NEW.status, NEW.filled_quantity, NEW.quantity |
| 136 | | USING ERRCODE = 'check_violation'; |
| 137 | | END IF; |
| 138 | | NEW.status := v_derived; |
| 139 | | |
| 140 | | IF v_derived = 'executed' THEN |
| 141 | | NEW.executed_at := COALESCE(NEW.executed_at, now()); |
| 142 | | ELSE |
| 143 | | NEW.executed_at := NULL; |
| 144 | | END IF; |
| 145 | | RETURN NEW; |
| 146 | | END $$; |
| 147 | | |
| 148 | | CREATE TRIGGER orders_lifecycle |
| 149 | | BEFORE INSERT OR UPDATE ON project.orders |
| 150 | | FOR EACH ROW EXECUTE FUNCTION project.trg_orders_lifecycle(); |
| 151 | | }}} |
| 152 | | |
| 153 | | Because the status is derived, the application never sets `open`, `partially_filled` or |
| 154 | | `executed` itself — it records trades, and the status follows. |
| 155 | | |
| 156 | | == Trade consistency == |
| 157 | | |
| 158 | | === Data requirements description === |
| 159 | | |
| 160 | | '''Business rule.''' A trade that fills orders must be possible for those orders: |
| 161 | | |
| 162 | | * the buy side is a buy order and the sell side is a sell order; |
| 163 | | * both are on the trade's market and still active — a cancelled or executed order can never trade again; |
| 164 | | * the trade quantity does not exceed the remaining quantity of either order; |
| 165 | | * the price respects both limits: at most the buy order's price, at least the sell order's price; |
| 166 | | * the two orders belong to different users (no self-trade). |
| 167 | | |
| 168 | | Recording the trade must fill both orders by exactly the traded quantity (and so move their |
| 169 | | status), and move the money and crypto for both users, all together. A trade that filled orders |
| 170 | | is history and can't be changed or deleted afterwards — the fills and the money moved would no |
| 171 | | longer match it. |
| 172 | | |
| 173 | | '''Why it is non-trivial.''' One trade row has to be checked against two other rows of another |
| 174 | | table (the orders), including their current remaining quantity, and a single insert must cause |
| 175 | | consistent changes in five tables (`market_trades`, both `orders`, both users' `users` and |
| 176 | | `holdings` rows, two `transactions` rows). |
| 177 | | |
| 178 | | '''PostgreSQL features.''' |
| 179 | | |
| 180 | | * `BEFORE INSERT` trigger `market_trades_validate` — the compatibility rules. It locks both orders (`FOR UPDATE`), so two concurrent trades can't both take the same remaining quantity. |
| 181 | | * `AFTER INSERT` trigger `market_trades_fill` — raises `filled_quantity` on both orders (automatic status change through requirement 1). |
| 182 | | * `BEFORE UPDATE OR DELETE` trigger `market_trades_immutable`. |
| 183 | | * Stored function `execute_trade` — the settlement of money and crypto for one trade. |
| 184 | | |
| 185 | | Because the rules sit on `market_trades` itself, even a trade inserted by hand is validated and |
| 186 | | fills the orders. |
| 187 | | |
| 188 | | '''Tables affected.''' `market_trades`, `orders`, `users`, `holdings`, `transactions`. |
| 189 | | |
| 190 | | === Implementation === |
| 191 | | |
| 192 | | ==== Triggers ==== |
| 193 | | |
| 194 | | {{{ |
| 195 | | CREATE OR REPLACE FUNCTION project.trg_market_trades_validate() |
| 196 | | RETURNS trigger LANGUAGE plpgsql AS $$ |
| 197 | | DECLARE |
| 198 | | o project.orders%ROWTYPE; |
| 199 | | v_uid uuid; |
| 200 | | v_id uuid; |
| 201 | | v_role varchar(4); |
| 202 | | BEGIN |
| 203 | | IF NEW.buy_order_id IS NULL AND NEW.sell_order_id IS NULL THEN |
| 204 | | RETURN NEW; |
| 205 | | END IF; |
| 206 | | IF NEW.buy_order_id IS NOT DISTINCT FROM NEW.sell_order_id THEN |
| 207 | | RAISE EXCEPTION 'an order cannot trade with itself' |
| 208 | | USING ERRCODE = 'check_violation'; |
| 209 | | END IF; |
| 210 | | |
| 211 | | FOREACH v_role IN ARRAY ARRAY['buy', 'sell'] LOOP |
| 212 | | v_id := CASE v_role WHEN 'buy' THEN NEW.buy_order_id ELSE NEW.sell_order_id END; |
| 213 | | CONTINUE WHEN v_id IS NULL; |
| 214 | | |
| 215 | | SELECT * INTO o FROM project.orders WHERE id = v_id FOR UPDATE; |
| 216 | | IF o.side <> v_role THEN |
| 217 | | RAISE EXCEPTION 'order % is a % order and cannot be the % side of a trade', v_id, o.side, v_role |
| 218 | | USING ERRCODE = 'check_violation'; |
| 219 | | END IF; |
| 220 | | IF o.market_id <> NEW.market_id THEN |
| 221 | | RAISE EXCEPTION 'order % is on a different market than the trade', v_id |
| 222 | | USING ERRCODE = 'check_violation'; |
| 223 | | END IF; |
| 224 | | IF o.status NOT IN ('open', 'partially_filled') THEN |
| 225 | | RAISE EXCEPTION 'order % is % and cannot trade', v_id, o.status |
| 226 | | USING ERRCODE = 'check_violation'; |
| 227 | | END IF; |
| 228 | | IF NEW.quantity > o.quantity - o.filled_quantity THEN |
| 229 | | RAISE EXCEPTION 'trade quantity % exceeds the remaining quantity % of order %', |
| 230 | | NEW.quantity, o.quantity - o.filled_quantity, v_id |
| 231 | | USING ERRCODE = 'check_violation'; |
| 232 | | END IF; |
| 233 | | IF v_uid IS NOT NULL AND v_uid = o.user_id THEN |
| 234 | | RAISE EXCEPTION 'a user cannot trade with their own order' |
| 235 | | USING ERRCODE = 'check_violation'; |
| 236 | | END IF; |
| 237 | | IF v_role = 'buy' AND NEW.price > o.price OR v_role = 'sell' AND NEW.price < o.price THEN |
| 238 | | RAISE EXCEPTION 'trade price % is outside the limit % of % order %', NEW.price, o.price, v_role, v_id |
| 239 | | USING ERRCODE = 'check_violation'; |
| 240 | | END IF; |
| 241 | | v_uid := o.user_id; |
| 242 | | END LOOP; |
| 243 | | RETURN NEW; |
| 244 | | END $$; |
| 245 | | |
| 246 | | CREATE TRIGGER market_trades_validate |
| 247 | | BEFORE INSERT ON project.market_trades |
| 248 | | FOR EACH ROW EXECUTE FUNCTION project.trg_market_trades_validate(); |
| 249 | | }}} |
| 250 | | |
| 251 | | {{{ |
| 252 | | CREATE OR REPLACE FUNCTION project.trg_market_trades_fill() |
| 253 | | RETURNS trigger LANGUAGE plpgsql AS $$ |
| 254 | | BEGIN |
| 255 | | IF NEW.buy_order_id IS NULL AND NEW.sell_order_id IS NULL THEN |
| 256 | | RETURN NULL; |
| 257 | | END IF; |
| 258 | | PERFORM set_config('eduberza.trade_fill', 'on', true); |
| 259 | | PERFORM set_config('eduberza.trade_price', NEW.price::text, true); |
| 260 | | UPDATE project.orders |
| 261 | | SET filled_quantity = filled_quantity + NEW.quantity, |
| 262 | | executed_at = CASE WHEN filled_quantity + NEW.quantity = quantity |
| 263 | | THEN NEW.executed_at END |
| 264 | | WHERE id IN (NEW.buy_order_id, NEW.sell_order_id); |
| 265 | | PERFORM set_config('eduberza.trade_fill', 'off', true); |
| 266 | | RETURN NULL; |
| 267 | | END $$; |
| 268 | | |
| 269 | | CREATE TRIGGER market_trades_fill |
| 270 | | AFTER INSERT ON project.market_trades |
| 271 | | FOR EACH ROW EXECUTE FUNCTION project.trg_market_trades_fill(); |
| 272 | | }}} |
| 273 | | |
| 274 | | {{{ |
| 275 | | CREATE OR REPLACE FUNCTION project.trg_market_trades_immutable() |
| 276 | | RETURNS trigger LANGUAGE plpgsql AS $$ |
| 277 | | BEGIN |
| 278 | | IF OLD.buy_order_id IS NULL AND OLD.sell_order_id IS NULL THEN |
| 279 | | -- a simulated tick filled no order; allow it unless it is being |
| 280 | | -- turned into one that did |
| 281 | | IF TG_OP = 'DELETE' THEN |
| 282 | | RETURN OLD; |
| 283 | | ELSIF NEW.buy_order_id IS NULL AND NEW.sell_order_id IS NULL THEN |
| 284 | | RETURN NEW; |
| 285 | | END IF; |
| 286 | | END IF; |
| 287 | | RAISE EXCEPTION 'a trade that filled orders cannot be changed or deleted' |
| 288 | | USING ERRCODE = 'check_violation'; |
| 289 | | END $$; |
| 290 | | |
| 291 | | CREATE TRIGGER market_trades_immutable |
| 292 | | BEFORE UPDATE OR DELETE ON project.market_trades |
| 293 | | FOR EACH ROW EXECUTE FUNCTION project.trg_market_trades_immutable(); |
| 294 | | }}} |
| 295 | | |
| 296 | | ==== Stored procedures/functions ==== |
| 297 | | |
| 298 | | `execute_trade(buy_order, sell_order, quantity, price)` — one trade. Either order may be `NULL` |
| 299 | | when the simulated market is the counterparty. The `INSERT` into `market_trades` validates the |
| 300 | | trade and fills the orders (triggers above); the function then settles both users: |
| 301 | | |
| 302 | | * '''Buyer:''' the reservation for the filled part is released. The buyer pays the actual cost (trade price × quantity); if the trade price is below the order's limit, the difference goes back to `available_balance`. The crypto is added to the holding at a running weighted average price, and a `buy` ledger row is written. |
| 303 | | * '''Seller:''' the reserved crypto is delivered out of the holding, the proceeds are credited, the cost basis is removed from `invested_balance`, and a `sell` ledger row is written. |
| 304 | | |
| 305 | | Both orders are locked in a fixed order (by id), so two trades on the same pair of orders can't |
| 306 | | deadlock. |
| 307 | | |
| 308 | | {{{ |
| 309 | | CREATE OR REPLACE FUNCTION project.execute_trade( |
| 310 | | p_buy_order uuid, p_sell_order uuid, p_quantity numeric, p_price numeric, |
| 311 | | p_aggressor varchar DEFAULT NULL) |
| 312 | | RETURNS bigint LANGUAGE plpgsql AS $$ |
| 313 | | DECLARE |
| 314 | | b project.orders%ROWTYPE; |
| 315 | | s project.orders%ROWTYPE; |
| 316 | | v_market project.markets%ROWTYPE; |
| 317 | | v_trade_id bigint; |
| 318 | | v_release numeric; |
| 319 | | v_cost numeric; |
| 320 | | v_proceeds numeric; |
| 321 | | v_avg numeric; |
| 322 | | BEGIN |
| 323 | | IF p_buy_order IS NULL AND p_sell_order IS NULL THEN |
| 324 | | RAISE EXCEPTION 'a trade needs at least one order' USING ERRCODE = 'check_violation'; |
| 325 | | END IF; |
| 326 | | |
| 327 | | -- lock both orders in a fixed order (by id) so two concurrent trades on |
| 328 | | -- the same pair of orders cannot deadlock |
| 329 | | PERFORM 1 FROM project.orders WHERE id IN (p_buy_order, p_sell_order) ORDER BY id FOR UPDATE; |
| 330 | | SELECT * INTO b FROM project.orders WHERE id = p_buy_order; |
| 331 | | SELECT * INTO s FROM project.orders WHERE id = p_sell_order; |
| 332 | | SELECT * INTO v_market FROM project.markets WHERE id = COALESCE(b.market_id, s.market_id); |
| 333 | | |
| 334 | | INSERT INTO project.market_trades |
| 335 | | (market_id, executed_at, price, quantity, side, source, buy_order_id, sell_order_id) |
| 336 | | VALUES (v_market.id, now(), p_price, p_quantity, |
| 337 | | COALESCE(p_aggressor, CASE WHEN p_sell_order IS NULL THEN 'buy' ELSE 'sell' END), |
| 338 | | CASE WHEN p_buy_order IS NOT NULL AND p_sell_order IS NOT NULL THEN 'match' ELSE 'market' END, |
| 339 | | p_buy_order, p_sell_order) |
| 340 | | RETURNING id INTO v_trade_id; |
| 341 | | |
| 342 | | IF p_buy_order IS NOT NULL THEN |
| 343 | | v_release := project.order_reservation(b.quantity - b.filled_quantity, b.price) |
| 344 | | - project.order_reservation(b.quantity - b.filled_quantity - p_quantity, b.price); |
| 345 | | v_cost := LEAST(round(p_quantity * p_price, 4), v_release); |
| 346 | | |
| 347 | | UPDATE project.users |
| 348 | | SET reserved_balance = reserved_balance - v_release, |
| 349 | | available_balance = available_balance + (v_release - v_cost), |
| 350 | | invested_balance = invested_balance + v_cost, |
| 351 | | updated_at = now() |
| 352 | | WHERE id = b.user_id; |
| 353 | | |
| 354 | | INSERT INTO project.holdings AS h (user_id, crypto_id, quantity, avg_price, updated_at) |
| 355 | | VALUES (b.user_id, v_market.crypto_id, p_quantity, p_price, now()) |
| 356 | | ON CONFLICT (user_id, crypto_id) DO UPDATE |
| 357 | | SET avg_price = (h.quantity * h.avg_price + EXCLUDED.quantity * EXCLUDED.avg_price) |
| 358 | | / (h.quantity + EXCLUDED.quantity), |
| 359 | | quantity = h.quantity + EXCLUDED.quantity, |
| 360 | | updated_at = now(); |
| 361 | | |
| 362 | | INSERT INTO project.transactions (user_id, type, amount, currency, related_order, description) |
| 363 | | VALUES (b.user_id, 'buy', -v_cost, v_market.quote_currency, b.id, |
| 364 | | format('Buy %s @ %s (trade %s)', p_quantity, p_price, v_trade_id)); |
| 365 | | END IF; |
| 366 | | |
| 367 | | IF p_sell_order IS NOT NULL THEN |
| 368 | | SELECT avg_price INTO v_avg FROM project.holdings |
| 369 | | WHERE user_id = s.user_id AND crypto_id = v_market.crypto_id FOR UPDATE; |
| 370 | | |
| 371 | | UPDATE project.holdings |
| 372 | | SET quantity = quantity - p_quantity, |
| 373 | | reserved_quantity = reserved_quantity - p_quantity, |
| 374 | | updated_at = now() |
| 375 | | WHERE user_id = s.user_id AND crypto_id = v_market.crypto_id; |
| 376 | | |
| 377 | | v_proceeds := round(p_quantity * p_price, 4); |
| 378 | | UPDATE project.users |
| 379 | | SET available_balance = available_balance + v_proceeds, |
| 380 | | invested_balance = GREATEST(invested_balance - round(p_quantity * v_avg, 4), 0), |
| 381 | | updated_at = now() |
| 382 | | WHERE id = s.user_id; |
| 383 | | |
| 384 | | INSERT INTO project.transactions (user_id, type, amount, currency, related_order, description) |
| 385 | | VALUES (s.user_id, 'sell', v_proceeds, v_market.quote_currency, s.id, |
| 386 | | format('Sell %s @ %s (trade %s)', p_quantity, p_price, v_trade_id)); |
| 387 | | END IF; |
| 388 | | |
| 389 | | RETURN v_trade_id; |
| 390 | | END $$; |
| 391 | | }}} |
| 392 | | |
| 393 | | == Balance and reservation consistency == |
| 394 | | |
| 395 | | === Data requirements description === |
| 396 | | |
| 397 | | '''Business rule.''' |
| 398 | | |
| 399 | | 1. '''Reserved cash matches active buy orders.''' A user's `reserved_balance` always equals what their active buy orders still reserve: remaining quantity × order price, summed. |
| 400 | | 2. '''Reserved crypto matches active sell orders.''' A user's `holdings.reserved_quantity` for a crypto always equals the remaining quantity of their active sell orders for it. |
| 401 | | 3. '''Cash matches the ledger.''' `available_balance + reserved_balance` always equals the sum of the user's ledger (`transactions`). Reserving only moves cash between the two columns; money actually arrives or leaves only with a ledger row (deposit, buy fill, sell fill). |
| 402 | | |
| 403 | | Together these make it impossible for an order operation to leave a balance in an inconsistent |
| 404 | | state: money or crypto reserved for nothing, an order that is not backed by a reservation (and |
| 405 | | so could spend the same money twice), or cash that appeared or vanished without a ledger entry. |
| 406 | | |
| 407 | | '''Why it is non-trivial.''' Each rule is an equality between a column and an aggregate over |
| 408 | | ''other'' rows of ''other'' tables. Worse, every legitimate operation breaks it for a moment: |
| 409 | | placing a buy moves cash to `reserved_balance` in one statement and inserts the order in the |
| 410 | | next; a trade releases the reservation, pays, and writes the ledger in several statements. Only |
| 411 | | the state at the end of the transaction has to be consistent. |
| 412 | | |
| 413 | | '''PostgreSQL feature.''' `CONSTRAINT TRIGGER … DEFERRABLE INITIALLY DEFERRED` — row triggers |
| 414 | | whose check runs at `COMMIT`, on the final state. A transaction that leaves any of the three |
| 415 | | equalities broken fails at `COMMIT` and is rolled back as a whole. Each rule has a trigger on |
| 416 | | every table whose change can break it. `order_reservation(remaining, price)` is the one place |
| 417 | | that defines how a reservation is rounded, so placing, filling, cancelling and checking always |
| 418 | | agree to the last decimal. |
| 419 | | |
| 420 | | '''Tables affected.''' `users`, `holdings`, `orders`, `transactions`. |
| 421 | | |
| 422 | | === Implementation === |
| 423 | | |
| 424 | | ==== Stored procedures/functions ==== |
| 425 | | |
| 426 | | {{{ |
| 427 | | CREATE OR REPLACE FUNCTION project.order_reservation(p_remaining numeric, p_price numeric) |
| 428 | | RETURNS numeric LANGUAGE sql IMMUTABLE AS $$ |
| 429 | | SELECT round(p_remaining * p_price, 4) |
| 430 | | $$; |
| 431 | | }}} |
| 432 | | |
| 433 | | ==== Triggers ==== |
| 434 | | |
| 435 | | {{{ |
| 436 | | CREATE OR REPLACE FUNCTION project.trg_reserved_cash_matches_orders() |
| 437 | | RETURNS trigger LANGUAGE plpgsql AS $$ |
| 438 | | DECLARE |
| 439 | | v_user uuid := CASE WHEN TG_TABLE_NAME = 'users' THEN NEW.id END; |
| 440 | | v_reserved numeric; |
| 441 | | v_needed numeric; |
| 442 | | BEGIN |
| 443 | | IF TG_TABLE_NAME = 'orders' THEN |
| 444 | | v_user := NEW.user_id; |
| 445 | | END IF; |
| 446 | | SELECT reserved_balance INTO v_reserved FROM project.users WHERE id = v_user; |
| 447 | | IF NOT FOUND THEN |
| 448 | | RETURN NULL; |
| 449 | | END IF; |
| 450 | | SELECT COALESCE(SUM(project.order_reservation(quantity - filled_quantity, price)), 0) |
| 451 | | INTO v_needed |
| 452 | | FROM project.orders |
| 453 | | WHERE user_id = v_user AND side = 'buy' AND status IN ('open', 'partially_filled'); |
| 454 | | IF v_reserved <> v_needed THEN |
| 455 | | RAISE EXCEPTION 'reserved balance % does not match the % needed by active buy orders (user %)', |
| 456 | | v_reserved, v_needed, v_user |
| 457 | | USING ERRCODE = 'check_violation', CONSTRAINT = 'reserved_cash_matches_orders'; |
| 458 | | END IF; |
| 459 | | RETURN NULL; |
| 460 | | END $$; |
| 461 | | }}} |
| 462 | | |
| 463 | | {{{ |
| 464 | | CREATE OR REPLACE FUNCTION project.trg_reserved_crypto_matches_orders() |
| 465 | | RETURNS trigger LANGUAGE plpgsql AS $$ |
| 466 | | DECLARE |
| 467 | | v_user uuid; |
| 468 | | v_crypto uuid; |
| 469 | | v_reserved numeric; |
| 470 | | v_needed numeric; |
| 471 | | BEGIN |
| 472 | | IF TG_TABLE_NAME = 'holdings' THEN |
| 473 | | v_user := NEW.user_id; |
| 474 | | v_crypto := NEW.crypto_id; |
| 475 | | ELSE |
| 476 | | IF NEW.side <> 'sell' THEN |
| 477 | | RETURN NULL; |
| 478 | | END IF; |
| 479 | | v_user := NEW.user_id; |
| 480 | | SELECT crypto_id INTO v_crypto FROM project.markets WHERE id = NEW.market_id; |
| 481 | | END IF; |
| 482 | | SELECT COALESCE(SUM(reserved_quantity), 0) INTO v_reserved |
| 483 | | FROM project.holdings WHERE user_id = v_user AND crypto_id = v_crypto; |
| 484 | | SELECT COALESCE(SUM(o.quantity - o.filled_quantity), 0) INTO v_needed |
| 485 | | FROM project.orders o |
| 486 | | JOIN project.markets m ON m.id = o.market_id |
| 487 | | WHERE o.user_id = v_user AND m.crypto_id = v_crypto |
| 488 | | AND o.side = 'sell' AND o.status IN ('open', 'partially_filled'); |
| 489 | | IF v_reserved <> v_needed THEN |
| 490 | | RAISE EXCEPTION 'reserved quantity % does not match the % needed by active sell orders (user %, crypto %)', |
| 491 | | v_reserved, v_needed, v_user, v_crypto |
| 492 | | USING ERRCODE = 'check_violation', CONSTRAINT = 'reserved_crypto_matches_orders'; |
| 493 | | END IF; |
| 494 | | RETURN NULL; |
| 495 | | END $$; |
| 496 | | }}} |
| 497 | | |
| 498 | | {{{ |
| 499 | | CREATE OR REPLACE FUNCTION project.trg_cash_matches_ledger() |
| 500 | | RETURNS trigger LANGUAGE plpgsql AS $$ |
| 501 | | DECLARE |
| 502 | | v_user uuid; |
| 503 | | v_cash numeric; |
| 504 | | v_ledger numeric; |
| 505 | | BEGIN |
| 506 | | IF TG_TABLE_NAME = 'users' THEN |
| 507 | | v_user := NEW.id; |
| 508 | | ELSIF TG_OP = 'DELETE' THEN |
| 509 | | v_user := OLD.user_id; |
| 510 | | ELSE |
| 511 | | v_user := NEW.user_id; |
| 512 | | END IF; |
| 513 | | SELECT available_balance + reserved_balance INTO v_cash FROM project.users WHERE id = v_user; |
| 514 | | IF NOT FOUND THEN |
| 515 | | RETURN NULL; |
| 516 | | END IF; |
| 517 | | SELECT COALESCE(SUM(amount), 0) INTO v_ledger FROM project.transactions WHERE user_id = v_user; |
| 518 | | IF v_cash <> v_ledger THEN |
| 519 | | RAISE EXCEPTION 'cash % (available + reserved) does not match the ledger total % (user %)', |
| 520 | | v_cash, v_ledger, v_user |
| 521 | | USING ERRCODE = 'check_violation', CONSTRAINT = 'cash_matches_ledger'; |
| 522 | | END IF; |
| 523 | | RETURN NULL; |
| 524 | | END $$; |
| 525 | | }}} |
| 526 | | |
| 527 | | {{{ |
| 528 | | CREATE INDEX idx_orders_active ON project.orders (user_id, side) |
| 529 | | WHERE status IN ('open', 'partially_filled'); |
| 530 | | |
| 531 | | CREATE CONSTRAINT TRIGGER reserved_cash_matches_orders |
| 532 | | AFTER INSERT OR UPDATE OF reserved_balance ON project.users |
| 533 | | DEFERRABLE INITIALLY DEFERRED |
| 534 | | FOR EACH ROW EXECUTE FUNCTION project.trg_reserved_cash_matches_orders(); |
| 535 | | |
| 536 | | CREATE CONSTRAINT TRIGGER reserved_cash_matches_orders |
| 537 | | AFTER INSERT OR UPDATE OF status, filled_quantity ON project.orders |
| 538 | | DEFERRABLE INITIALLY DEFERRED |
| 539 | | FOR EACH ROW EXECUTE FUNCTION project.trg_reserved_cash_matches_orders(); |
| 540 | | |
| 541 | | CREATE CONSTRAINT TRIGGER reserved_crypto_matches_orders |
| 542 | | AFTER INSERT OR UPDATE OF reserved_quantity ON project.holdings |
| 543 | | DEFERRABLE INITIALLY DEFERRED |
| 544 | | FOR EACH ROW EXECUTE FUNCTION project.trg_reserved_crypto_matches_orders(); |
| 545 | | |
| 546 | | CREATE CONSTRAINT TRIGGER reserved_crypto_matches_orders |
| 547 | | AFTER INSERT OR UPDATE OF status, filled_quantity ON project.orders |
| 548 | | DEFERRABLE INITIALLY DEFERRED |
| 549 | | FOR EACH ROW EXECUTE FUNCTION project.trg_reserved_crypto_matches_orders(); |
| 550 | | |
| 551 | | CREATE CONSTRAINT TRIGGER cash_matches_ledger |
| 552 | | AFTER INSERT OR UPDATE OF available_balance, reserved_balance ON project.users |
| 553 | | DEFERRABLE INITIALLY DEFERRED |
| 554 | | FOR EACH ROW EXECUTE FUNCTION project.trg_cash_matches_ledger(); |
| 555 | | |
| 556 | | CREATE CONSTRAINT TRIGGER cash_matches_ledger |
| 557 | | AFTER INSERT OR UPDATE OR DELETE ON project.transactions |
| 558 | | DEFERRABLE INITIALLY DEFERRED |
| 559 | | FOR EACH ROW EXECUTE FUNCTION project.trg_cash_matches_ledger(); |
| 560 | | }}} |
| 561 | | |
| 562 | | The partial index `idx_orders_active` covers exactly the rows the reservation checks sum, and |
| 563 | | stays small because active orders are few compared with the whole order history. |
| 564 | | |
| 565 | | == Placing and cancelling orders == |
| 566 | | |
| 567 | | === Data requirements description === |
| 568 | | |
| 569 | | '''Business rule.''' Placing an order must, as one unit: |
| 570 | | |
| 571 | | 1. check the market, side, type, quantity and price; |
| 572 | | 2. reserve what the order commits — cash (quantity × price) for a buy, crypto for a sell — and refuse the order if not enough is free; |
| 573 | | 3. record the order; |
| 574 | | 4. match it against the order book: other users' active limit orders on the same market whose price is acceptable, best price first and oldest first (price–time priority), each trade at the resting order's price; |
| 575 | | 5. fill whatever is still unfilled from the simulated market if it is marketable at the current market price. |
| 576 | | |
| 577 | | A '''market order''' is priced at the current market price, so it always fills completely in step |
| 578 | | 4 or 5 and never waits. A '''limit order''' that isn't marketable stays in the order book. |
| 579 | | Cancelling an order must release exactly what it still reserves, and only for an active order of |
| 580 | | the caller. |
| 581 | | |
| 582 | | '''Why it is non-trivial.''' It is a multi-step operation over five tables whose steps depend on |
| 583 | | each other (how much is left after each match, what to release), and it has to be correct under |
| 584 | | concurrency: two orders of the same user must not both see the same free cash. |
| 585 | | |
| 586 | | '''PostgreSQL feature.''' Stored functions (PL/pgSQL). `place_order` locks the user's row |
| 587 | | (`SELECT … FOR UPDATE`) before checking free cash or crypto, so concurrent orders of the same |
| 588 | | user are serialised. Every step's consistency is still checked by the triggers of requirements |
| 589 | | 1–3. |
| 590 | | |
| 591 | | '''Tables affected.''' `orders`, `users`, `holdings`, `market_trades`, `transactions`. |
| 592 | | |
| 593 | | === Implementation === |
| 594 | | |
| 595 | | ==== Stored procedures/functions ==== |
| 596 | | |
| 597 | | {{{ |
| 598 | | CREATE OR REPLACE FUNCTION project.latest_price(p_market_id uuid) |
| 599 | | RETURNS numeric LANGUAGE sql STABLE AS $$ |
| 600 | | SELECT price FROM project.market_trades |
| 601 | | WHERE market_id = p_market_id |
| 602 | | ORDER BY executed_at DESC, id DESC |
| 603 | | LIMIT 1 |
| 604 | | $$; |
| 605 | | }}} |
| 606 | | |
| 607 | | {{{ |
| 608 | | CREATE OR REPLACE FUNCTION project.match_order(p_order_id uuid) |
| 609 | | RETURNS int LANGUAGE plpgsql AS $$ |
| 610 | | DECLARE |
| 611 | | o project.orders%ROWTYPE; |
| 612 | | r record; |
| 613 | | v_rem numeric; |
| 614 | | v_qty numeric; |
| 615 | | v_count int := 0; |
| 616 | | BEGIN |
| 617 | | SELECT * INTO o FROM project.orders WHERE id = p_order_id FOR UPDATE; |
| 618 | | FOR r IN |
| 619 | | SELECT id, price, quantity - filled_quantity AS remaining |
| 620 | | FROM project.orders |
| 621 | | WHERE market_id = o.market_id |
| 622 | | AND side <> o.side |
| 623 | | AND type = 'limit' |
| 624 | | AND status IN ('open', 'partially_filled') |
| 625 | | AND user_id <> o.user_id |
| 626 | | AND (o.side = 'buy' AND price <= o.price |
| 627 | | OR o.side = 'sell' AND price >= o.price) |
| 628 | | ORDER BY CASE WHEN o.side = 'buy' THEN price END ASC, |
| 629 | | CASE WHEN o.side = 'sell' THEN price END DESC, |
| 630 | | placed_at, id |
| 631 | | FOR UPDATE |
| 632 | | LOOP |
| 633 | | SELECT quantity - filled_quantity INTO v_rem FROM project.orders WHERE id = p_order_id; |
| 634 | | EXIT WHEN v_rem = 0; |
| 635 | | v_qty := LEAST(v_rem, r.remaining); |
| 636 | | IF o.side = 'buy' THEN |
| 637 | | PERFORM project.execute_trade(o.id, r.id, v_qty, r.price, 'buy'); |
| 638 | | ELSE |
| 639 | | PERFORM project.execute_trade(r.id, o.id, v_qty, r.price, 'sell'); |
| 640 | | END IF; |
| 641 | | v_count := v_count + 1; |
| 642 | | END LOOP; |
| 643 | | RETURN v_count; |
| 644 | | END $$; |
| 645 | | }}} |
| 646 | | |
| 647 | | {{{ |
| 648 | | CREATE OR REPLACE FUNCTION project.place_order( |
| 649 | | p_user_id uuid, p_market_id uuid, p_side varchar, p_type varchar, |
| 650 | | p_quantity numeric, p_limit_price numeric DEFAULT NULL) |
| 651 | | RETURNS uuid LANGUAGE plpgsql AS $$ |
| 652 | | DECLARE |
| 653 | | v_market numeric := project.latest_price(p_market_id); |
| 654 | | v_price numeric; |
| 655 | | v_available numeric; |
| 656 | | v_free numeric; |
| 657 | | v_crypto uuid; |
| 658 | | v_order uuid; |
| 659 | | v_rem numeric; |
| 660 | | BEGIN |
| 661 | | IF p_side NOT IN ('buy', 'sell') OR p_type NOT IN ('market', 'limit') THEN |
| 662 | | RAISE EXCEPTION 'invalid side % or type %', p_side, p_type USING ERRCODE = 'check_violation'; |
| 663 | | END IF; |
| 664 | | IF p_quantity IS NULL OR p_quantity <= 0 THEN |
| 665 | | RAISE EXCEPTION 'quantity must be positive' USING ERRCODE = 'check_violation'; |
| 666 | | END IF; |
| 667 | | IF p_type = 'limit' THEN |
| 668 | | IF p_limit_price IS NULL OR p_limit_price <= 0 THEN |
| 669 | | RAISE EXCEPTION 'a limit order needs a positive limit price' USING ERRCODE = 'check_violation'; |
| 670 | | END IF; |
| 671 | | v_price := p_limit_price; |
| 672 | | ELSE |
| 673 | | IF v_market IS NULL THEN |
| 674 | | RAISE EXCEPTION 'market has no price yet' USING ERRCODE = 'check_violation'; |
| 675 | | END IF; |
| 676 | | v_price := v_market; |
| 677 | | END IF; |
| 678 | | |
| 679 | | SELECT available_balance INTO v_available FROM project.users WHERE id = p_user_id FOR UPDATE; |
| 680 | | IF NOT FOUND THEN |
| 681 | | RAISE EXCEPTION 'user % does not exist', p_user_id USING ERRCODE = 'no_data_found'; |
| 682 | | END IF; |
| 683 | | |
| 684 | | IF p_side = 'buy' THEN |
| 685 | | IF v_available < project.order_reservation(p_quantity, v_price) THEN |
| 686 | | RAISE EXCEPTION 'insufficient funds: the order needs %, available %', |
| 687 | | project.order_reservation(p_quantity, v_price), v_available |
| 688 | | USING ERRCODE = 'check_violation'; |
| 689 | | END IF; |
| 690 | | UPDATE project.users |
| 691 | | SET available_balance = available_balance - project.order_reservation(p_quantity, v_price), |
| 692 | | reserved_balance = reserved_balance + project.order_reservation(p_quantity, v_price), |
| 693 | | updated_at = now() |
| 694 | | WHERE id = p_user_id; |
| 695 | | ELSE |
| 696 | | SELECT crypto_id INTO v_crypto FROM project.markets WHERE id = p_market_id; |
| 697 | | SELECT quantity - reserved_quantity INTO v_free FROM project.holdings |
| 698 | | WHERE user_id = p_user_id AND crypto_id = v_crypto FOR UPDATE; |
| 699 | | IF COALESCE(v_free, 0) < p_quantity THEN |
| 700 | | RAISE EXCEPTION 'insufficient holding: trying to sell %, free to sell %', p_quantity, COALESCE(v_free, 0) |
| 701 | | USING ERRCODE = 'check_violation'; |
| 702 | | END IF; |
| 703 | | UPDATE project.holdings |
| 704 | | SET reserved_quantity = reserved_quantity + p_quantity, updated_at = now() |
| 705 | | WHERE user_id = p_user_id AND crypto_id = v_crypto; |
| 706 | | END IF; |
| 707 | | |
| 708 | | INSERT INTO project.orders (user_id, market_id, side, type, status, quantity, price) |
| 709 | | VALUES (p_user_id, p_market_id, p_side, p_type, 'open', p_quantity, v_price) |
| 710 | | RETURNING id INTO v_order; |
| 711 | | |
| 712 | | PERFORM project.match_order(v_order); |
| 713 | | |
| 714 | | SELECT quantity - filled_quantity INTO v_rem FROM project.orders WHERE id = v_order; |
| 715 | | IF v_rem > 0 AND v_market IS NOT NULL |
| 716 | | AND (p_side = 'buy' AND v_market <= v_price OR p_side = 'sell' AND v_market >= v_price) THEN |
| 717 | | IF p_side = 'buy' THEN |
| 718 | | PERFORM project.execute_trade(v_order, NULL, v_rem, v_market, 'buy'); |
| 719 | | ELSE |
| 720 | | PERFORM project.execute_trade(NULL, v_order, v_rem, v_market, 'sell'); |
| 721 | | END IF; |
| 722 | | END IF; |
| 723 | | RETURN v_order; |
| 724 | | END $$; |
| 725 | | }}} |
| 726 | | |
| 727 | | {{{ |
| 728 | | CREATE OR REPLACE FUNCTION project.cancel_order(p_order_id uuid, p_user_id uuid DEFAULT NULL) |
| 729 | | RETURNS void LANGUAGE plpgsql AS $$ |
| 730 | | DECLARE |
| 731 | | o project.orders%ROWTYPE; |
| 732 | | v_rem numeric; |
| 733 | | BEGIN |
| 734 | | SELECT * INTO o FROM project.orders WHERE id = p_order_id FOR UPDATE; |
| 735 | | IF NOT FOUND THEN |
| 736 | | RAISE EXCEPTION 'order % does not exist', p_order_id USING ERRCODE = 'no_data_found'; |
| 737 | | END IF; |
| 738 | | IF p_user_id IS NOT NULL AND o.user_id <> p_user_id THEN |
| 739 | | RAISE EXCEPTION 'order % does not belong to this user', p_order_id |
| 740 | | USING ERRCODE = 'insufficient_privilege'; |
| 741 | | END IF; |
| 742 | | IF o.status NOT IN ('open', 'partially_filled') THEN |
| 743 | | RAISE EXCEPTION 'order % is % and cannot be cancelled', p_order_id, o.status |
| 744 | | USING ERRCODE = 'check_violation'; |
| 745 | | END IF; |
| 746 | | |
| 747 | | v_rem := o.quantity - o.filled_quantity; |
| 748 | | IF o.side = 'buy' THEN |
| 749 | | UPDATE project.users |
| 750 | | SET reserved_balance = reserved_balance - project.order_reservation(v_rem, o.price), |
| 751 | | available_balance = available_balance + project.order_reservation(v_rem, o.price), |
| 752 | | updated_at = now() |
| 753 | | WHERE id = o.user_id; |
| 754 | | ELSE |
| 755 | | UPDATE project.holdings h |
| 756 | | SET reserved_quantity = h.reserved_quantity - v_rem, updated_at = now() |
| 757 | | FROM project.markets m |
| 758 | | WHERE m.id = o.market_id AND h.user_id = o.user_id AND h.crypto_id = m.crypto_id; |
| 759 | | END IF; |
| 760 | | |
| 761 | | UPDATE project.orders SET status = 'cancelled' WHERE id = p_order_id; |
| 762 | | END $$; |
| 763 | | }}} |
| 764 | | |
| 765 | | In the prototype, placing an order is now a single call: |
| 766 | | |
| 767 | | {{{ |
| 768 | | err = db.DB.QueryRow( |
| 769 | | `SELECT place_order($1, $2, $3, $4, $5, $6)`, |
| 770 | | s.UserID, m.ID, side, orderType, qty, limit.value(), |
| 771 | | ).Scan(&orderID) |
| 772 | | }}} |
| 773 | | |
| 774 | | Cancelling is `SELECT cancel_order($1, $2)` with the order the user picked from a numbered list |
| 775 | | of their open orders (`server/trade.go`). |
| 776 | | |
| 777 | | == Automatic recording of order events == |
| 778 | | |
| 779 | | === Data requirements description === |
| 780 | | |
| 781 | | '''Business rule.''' Every important thing that happens to an order is recorded with its time: |
| 782 | | placement, each fill (with the quantity filled and the trade price), and cancellation. This is |
| 783 | | the order's audit trail — the history of ''how'' it reached its current state, which the order row |
| 784 | | alone (only the current state) cannot show. |
| 785 | | |
| 786 | | '''Why it is non-trivial.''' Events come from several places — `place_order`, trades made by |
| 787 | | `match_order`, trades made by the background job, `cancel_order`, and any direct SQL. Recording |
| 788 | | them in each of those places would miss some; recording them where the change actually happens |
| 789 | | cannot. |
| 790 | | |
| 791 | | '''PostgreSQL feature.''' `AFTER INSERT OR UPDATE` row trigger on `orders`, writing into the new |
| 792 | | table `order_events`. The fill price is handed over by the trade trigger through a |
| 793 | | transaction-local setting. |
| 794 | | |
| 795 | | '''Tables affected.''' `order_events` (new), written from changes to `orders`. |
| 796 | | |
| 797 | | === Implementation === |
| 798 | | |
| 799 | | {{{ |
| 800 | | CREATE TABLE project.order_events ( |
| 801 | | id bigserial PRIMARY KEY, |
| 802 | | order_id uuid NOT NULL REFERENCES project.orders(id) ON DELETE CASCADE, |
| 803 | | event_type varchar(20) NOT NULL |
| 804 | | CHECK (event_type IN ('placed', 'partially_filled', 'filled', 'cancelled')), |
| 805 | | quantity numeric(20,4) NOT NULL, |
| 806 | | price numeric(18,6), |
| 807 | | status_after varchar(20) NOT NULL, |
| 808 | | created_at timestamptz NOT NULL DEFAULT clock_timestamp() |
| 809 | | ); |
| 810 | | }}} |
| 811 | | |
| 812 | | ==== Triggers ==== |
| 813 | | |
| 814 | | {{{ |
| 815 | | CREATE OR REPLACE FUNCTION project.trg_orders_events() |
| 816 | | RETURNS trigger LANGUAGE plpgsql AS $$ |
| 817 | | BEGIN |
| 818 | | IF TG_OP = 'INSERT' THEN |
| 819 | | INSERT INTO project.order_events (order_id, event_type, quantity, price, status_after) |
| 820 | | VALUES (NEW.id, 'placed', NEW.quantity, NEW.price, NEW.status); |
| 821 | | ELSIF NEW.filled_quantity > OLD.filled_quantity THEN |
| 822 | | INSERT INTO project.order_events (order_id, event_type, quantity, price, status_after) |
| 823 | | VALUES (NEW.id, |
| 824 | | CASE WHEN NEW.status = 'executed' THEN 'filled' ELSE 'partially_filled' END, |
| 825 | | NEW.filled_quantity - OLD.filled_quantity, |
| 826 | | current_setting('eduberza.trade_price', true)::numeric, |
| 827 | | NEW.status); |
| 828 | | ELSIF NEW.status = 'cancelled' AND OLD.status <> 'cancelled' THEN |
| 829 | | INSERT INTO project.order_events (order_id, event_type, quantity, price, status_after) |
| 830 | | VALUES (NEW.id, 'cancelled', NEW.quantity - NEW.filled_quantity, NEW.price, NEW.status); |
| 831 | | END IF; |
| 832 | | RETURN NULL; |
| 833 | | END $$; |
| 834 | | |
| 835 | | CREATE TRIGGER orders_events |
| 836 | | AFTER INSERT OR UPDATE ON project.orders |
| 837 | | FOR EACH ROW EXECUTE FUNCTION project.trg_orders_events(); |
| 838 | | }}} |
| 839 | | |
| 840 | | == Views for derived trading data == |
| 841 | | |
| 842 | | === Data requirements description === |
| 843 | | |
| 844 | | The application needs several things that are ''derived'' from orders, trades and balances. They |
| 845 | | are defined once as views, instead of repeating the calculations in the application code: |
| 846 | | |
| 847 | | ||= View =||= Derived data =||= Used by =|| |
| 848 | | || `v_active_orders` || active orders with remaining quantity and what each one holds in reserve || CLI `[13] My open orders`, `[14] Cancel an order` || |
| 849 | | || `v_order_book` || current order book: resting limit orders aggregated per market, side and price level || CLI `[12] Order book`, and when placing an order || |
| 850 | | || `v_order_history` || every order with its fill progress, number of trades and average fill price (from its trades) || CLI result of placing an order || |
| 851 | | || `v_trader_balances` || cash split into available and reserved, the ledger total it must equal, holdings at market value, net worth || CLI `[1] View balance` || |
| 852 | | |
| 853 | | None of these repeats a P6 report: P6 aggregates performance over a period, while these show |
| 854 | | the current state of the order book and accounts. |
| 855 | | |
| 856 | | === Implementation === |
| 857 | | |
| 858 | | ==== Views ==== |
| 859 | | |
| 860 | | {{{ |
| 861 | | CREATE VIEW project.v_active_orders AS |
| 862 | | SELECT o.id AS order_id, |
| 863 | | o.user_id, |
| 864 | | u.username, |
| 865 | | o.market_id, |
| 866 | | c.symbol, |
| 867 | | m.quote_currency, |
| 868 | | o.side, |
| 869 | | o.type, |
| 870 | | o.status, |
| 871 | | o.quantity, |
| 872 | | o.filled_quantity, |
| 873 | | o.quantity - o.filled_quantity AS remaining, |
| 874 | | o.price, |
| 875 | | CASE WHEN o.side = 'buy' |
| 876 | | THEN project.order_reservation(o.quantity - o.filled_quantity, o.price) ELSE 0 END AS reserved_cash, |
| 877 | | CASE WHEN o.side = 'sell' THEN o.quantity - o.filled_quantity ELSE 0 END AS reserved_crypto, |
| 878 | | o.placed_at |
| 879 | | FROM project.orders o |
| 880 | | JOIN project.users u ON u.id = o.user_id |
| 881 | | JOIN project.markets m ON m.id = o.market_id |
| 882 | | JOIN project.crypto c ON c.id = m.crypto_id |
| 883 | | WHERE o.status IN ('open', 'partially_filled'); |
| 884 | | }}} |
| 885 | | |
| 886 | | {{{ |
| 887 | | CREATE VIEW project.v_order_book AS |
| 888 | | SELECT market_id, |
| 889 | | symbol, |
| 890 | | quote_currency, |
| 891 | | side, |
| 892 | | price, |
| 893 | | SUM(remaining) AS quantity, |
| 894 | | COUNT(*) AS orders |
| 895 | | FROM project.v_active_orders |
| 896 | | WHERE type = 'limit' |
| 897 | | GROUP BY market_id, symbol, quote_currency, side, price; |
| 898 | | }}} |
| 899 | | |
| 900 | | {{{ |
| 901 | | CREATE VIEW project.v_order_history AS |
| 902 | | SELECT o.id AS order_id, |
| 903 | | o.user_id, |
| 904 | | u.username, |
| 905 | | c.symbol, |
| 906 | | o.side, |
| 907 | | o.type, |
| 908 | | o.status, |
| 909 | | o.quantity, |
| 910 | | o.filled_quantity, |
| 911 | | o.quantity - o.filled_quantity AS remaining, |
| 912 | | o.price, |
| 913 | | f.trades, |
| 914 | | f.avg_fill_price, |
| 915 | | o.placed_at, |
| 916 | | o.executed_at |
| 917 | | FROM project.orders o |
| 918 | | JOIN project.users u ON u.id = o.user_id |
| 919 | | JOIN project.markets m ON m.id = o.market_id |
| 920 | | JOIN project.crypto c ON c.id = m.crypto_id |
| 921 | | LEFT JOIN LATERAL ( |
| 922 | | SELECT COUNT(*) AS trades, |
| 923 | | round(SUM(t.quantity * t.price) / NULLIF(SUM(t.quantity), 0), 6) AS avg_fill_price |
| 924 | | FROM (SELECT quantity, price FROM project.market_trades WHERE buy_order_id = o.id |
| 925 | | UNION ALL |
| 926 | | SELECT quantity, price FROM project.market_trades WHERE sell_order_id = o.id) t |
| 927 | | ) f ON true; |
| 928 | | }}} |
| 929 | | |
| 930 | | {{{ |
| 931 | | CREATE VIEW project.v_trader_balances AS |
| 932 | | SELECT u.id AS user_id, |
| 933 | | u.username, |
| 934 | | u.available_balance, |
| 935 | | u.reserved_balance, |
| 936 | | u.available_balance + u.reserved_balance AS total_cash, |
| 937 | | COALESCE(l.ledger_total, 0) AS ledger_total, |
| 938 | | u.invested_balance, |
| 939 | | COALESCE(p.holdings_value, 0) AS holdings_value, |
| 940 | | u.available_balance + u.reserved_balance + COALESCE(p.holdings_value, 0) AS net_worth |
| 941 | | FROM project.users u |
| 942 | | LEFT JOIN (SELECT user_id, SUM(amount) AS ledger_total |
| 943 | | FROM project.transactions GROUP BY user_id) l ON l.user_id = u.id |
| 944 | | LEFT JOIN (SELECT user_id, SUM(market_value) AS holdings_value |
| 945 | | FROM project.v_portfolio GROUP BY user_id) p ON p.user_id = u.id; |
| 946 | | }}} |
| 947 | | |
| 948 | | == Background job: filling resting limit orders == |
| 949 | | |
| 950 | | === Data requirements description === |
| 951 | | |
| 952 | | '''Business rule.''' In !EduBerza the market price is moved by the simulator (the market bot), not |
| 953 | | by users' orders. A limit order that rests in the book — a buy at or above, or a sell at or |
| 954 | | below, the current market price — must then be filled by the simulated market, at the market |
| 955 | | price, just as it would have been had the price already been there when the order was placed. |
| 956 | | |
| 957 | | '''Why it is relevant, and why a background job.''' Without it, a limit order could only ever |
| 958 | | fill against another user's order, and with few users most limit orders would wait forever while |
| 959 | | the market price has long passed them. That would make limit orders useless in the simulation. |
| 960 | | Nothing happens at the moment the price crosses an order that a trigger could react to: the |
| 961 | | price moves through the bot's inserts into `market_trades`, which deliberately stay cheap single |
| 962 | | inserts. Scanning and filling every crossed order on every tick inside that insert would make |
| 963 | | each tick expensive. So the work runs as a periodic job after each round of price ticks. |
| 964 | | |
| 965 | | '''PostgreSQL feature.''' Stored function `fill_marketable_orders()`. It is scheduled by the |
| 966 | | application, because PostgreSQL has no built-in scheduler and the faculty server provides no |
| 967 | | `pg_cron` (checked: only `plpgsql` and `pgcrypto` are available, and the project role is not a |
| 968 | | superuser). |
| 969 | | |
| 970 | | * An advisory lock (`pg_try_advisory_xact_lock`) keeps two runs from filling the same orders twice. |
| 971 | | * `FOR UPDATE … SKIP LOCKED` leaves alone an order a user is cancelling at that moment; the next run picks it up. |
| 972 | | * Every fill goes through `execute_trade`, so all the rules above apply to it. |
| 973 | | |
| 974 | | '''Tables affected.''' `orders`, `market_trades`, `users`, `holdings`, `transactions`, |
| 975 | | `order_events`. |
| 976 | | |
| 977 | | === Implementation === |
| 978 | | |
| 979 | | ==== Stored procedures/functions ==== |
| 980 | | |
| 981 | | {{{ |
| 982 | | CREATE OR REPLACE FUNCTION project.fill_marketable_orders() |
| 983 | | RETURNS int LANGUAGE plpgsql AS $$ |
| 984 | | DECLARE |
| 985 | | r record; |
| 986 | | v_count int := 0; |
| 987 | | BEGIN |
| 988 | | IF NOT pg_try_advisory_xact_lock(hashtext('project.fill_marketable_orders')) THEN |
| 989 | | RETURN 0; |
| 990 | | END IF; |
| 991 | | FOR r IN |
| 992 | | SELECT o.id, o.side, o.quantity - o.filled_quantity AS remaining, lp.price AS market_price |
| 993 | | FROM project.orders o |
| 994 | | JOIN project.markets m ON m.id = o.market_id AND m.is_active |
| 995 | | CROSS JOIN LATERAL (SELECT project.latest_price(o.market_id) AS price) lp |
| 996 | | WHERE o.type = 'limit' |
| 997 | | AND o.status IN ('open', 'partially_filled') |
| 998 | | AND (o.side = 'buy' AND o.price >= lp.price |
| 999 | | OR o.side = 'sell' AND o.price <= lp.price) |
| 1000 | | ORDER BY o.placed_at, o.id |
| 1001 | | FOR UPDATE OF o SKIP LOCKED |
| 1002 | | LOOP |
| 1003 | | IF r.side = 'buy' THEN |
| 1004 | | PERFORM project.execute_trade(r.id, NULL, r.remaining, r.market_price, 'sell'); |
| 1005 | | ELSE |
| 1006 | | PERFORM project.execute_trade(NULL, r.id, r.remaining, r.market_price, 'buy'); |
| 1007 | | END IF; |
| 1008 | | v_count := v_count + 1; |
| 1009 | | END LOOP; |
| 1010 | | RETURN v_count; |
| 1011 | | END $$; |
| 1012 | | }}} |
| 1013 | | |
| 1014 | | ==== Scheduling ==== |
| 1015 | | |
| 1016 | | In `bots/main.go`, after every round of price ticks: |
| 1017 | | |
| 1018 | | {{{ |
| 1019 | | // P7 background job: the prices just moved, so fill any resting |
| 1020 | | // limit order the new market price has reached. |
| 1021 | | var filled int |
| 1022 | | if err := db.QueryRow(`SELECT fill_marketable_orders()`).Scan(&filled); err != nil { |
| 1023 | | log.Printf("fill_marketable_orders: %v", err) |
| 1024 | | } else if filled > 0 { |
| 1025 | | log.Printf(" filled %d resting limit order(s) at the new market price", filled) |
| 1026 | | } |
| 1027 | | }}} |
| 1028 | | |
| 1029 | | It can also be run by hand from any SQL client: `SELECT project.fill_marketable_orders();`. |
| 1030 | | |
| 1031 | | A run with the bot, after charlie placed a limit buy of 0.1 ETH at 3599 while the market was at |
| 1032 | | 3600: the bot's random walk took the price below 3599 and the job filled the order at the market |
| 1033 | | price. |
| 1034 | | |
| 1035 | | {{{ |
| 1036 | | 2026/09/24 13:19:02 filled 1 resting limit order(s) at the new market price |
| 1037 | | }}} |
| 1038 | | |
| 1039 | | {{{ |
| 1040 | | username | side | type | status | quantity | filled_quantity | price | avg_fill_price |
| 1041 | | ----------+------+-------+-----------+----------+-----------------+-------------+---------------- |
| 1042 | | charlie | buy | limit | cancelled | 0.1000 | 0.0000 | 3400.000000 | |
| 1043 | | charlie | buy | limit | executed | 0.1000 | 0.1000 | 3599.000000 | 3593.979167 |
| 1044 | | }}} |
| 1045 | | |
| 1046 | | == Tests proving the rules == |
| 1047 | | |
| 1048 | | `server/db/advanced_db_tests.sql` plays a short trading |
| 1049 | | story on the sample data and, along the way, tries to break every rule. It starts from alice with |
| 1050 | | 8250 USD and 0.5 ETH, bob with 5000 USD, charlie with 2500 USD, and ETH/USD last traded at 3520: |
| 1051 | | |
| 1052 | | 1. alice places a limit sell of 0.3 ETH at 3600, and bob a limit buy of 0.1 at 3500. Both rest in the book. |
| 1053 | | 2. bob places a limit buy of 0.2 at 3650. It crosses alice's ask, so they trade 0.2 at 3600: alice's order becomes partially filled, and bob gets back the 10 he had reserved above the trade price. |
| 1054 | | 3. charlie places a market buy of 0.15. He takes alice's remaining 0.1 from the book, and the other 0.05 comes from the simulated market. |
| 1055 | | 4. Invalid trades, state changes and balance changes are attempted directly in SQL. |
| 1056 | | 5. bob cancels his bid. |
| 1057 | | 6. The simulator moves the price to 3450, and the background job fills bob's new limit buy at 3500. |
| 1058 | | |
| 1059 | | The deferred checks are forced with `SET CONSTRAINTS ALL IMMEDIATE`, so a violation shows up |
| 1060 | | inside the test instead of at the final `COMMIT`. Everything is rolled back at the end. Run on |
| 1061 | | PostgreSQL 17 after `-init`: |
| 1062 | | |
| 1063 | | {{{ |
| 1064 | | PASS place: limit sell above the market rests in the book, crypto reserved: alice ETH reserved = 0.3000 |
| 1065 | | PASS place: limit buy below the market rests in the book, cash reserved: bob available 4650.0000 reserved 350.0000 |
| 1066 | | PASS event: placement recorded automatically: |
| 1067 | | PASS view: order book shows both price levels: buy 0.1000 @ 3500.000000, sell 0.3000 @ 3600.000000 |
| 1068 | | PASS consistency holds after placing: |
| 1069 | | PASS place: buy without enough free cash: insufficient funds: the order needs 60000.0000, available 2500.0000 |
| 1070 | | PASS place: sell more than is free (0.2 of 0.5 is already reserved): insufficient holding: trying to sell 0.3, free to sell 0.2000 |
| 1071 | | PASS match: trade between the two orders at the resting price: |
| 1072 | | PASS status: seller partially filled, buyer executed (automatic): alice_ask partially_filled 0.2000/0.3000 |
| 1073 | | PASS money: buyer paid 720, got back the 10 reserved above the trade price: bob available 3930.0000 reserved 350.0000 |
| 1074 | | PASS crypto: 0.2 ETH moved from alice (0.1 still reserved) to bob: |
| 1075 | | PASS ledger: one buy and one sell row, linked to the orders: |
| 1076 | | PASS event: fills recorded automatically: |
| 1077 | | PASS consistency holds after the trade: |
| 1078 | | PASS market order: filled completely in two trades: executed, 2 trades, avg 3600.000000 |
| 1079 | | PASS market order: alice's ask is now executed, nothing left reserved: |
| 1080 | | PASS consistency holds after the market order: |
| 1081 | | PASS trade: price above the buyer's limit: trade price 3700.000000 is outside the limit 3500.000000 of buy order … |
| 1082 | | PASS trade: more than the order has remaining: trade quantity 0.500000 exceeds the remaining quantity 0.1000 of order … |
| 1083 | | PASS trade: a sell order used as the buy side: order … is a sell order and cannot be the buy side of a trade |
| 1084 | | PASS trade: order of another market: order … is on a different market than the trade |
| 1085 | | PASS trade: executed order cannot trade again: order … is executed and cannot trade |
| 1086 | | PASS trade: a user with their own order: a user cannot trade with their own order |
| 1087 | | PASS trade: a trade that filled orders cannot be deleted: a trade that filled orders cannot be changed or deleted |
| 1088 | | PASS state: status cannot be set to executed by hand: order status executed does not match filled quantity 0.0000 of 0.1000; status is derived automatically |
| 1089 | | PASS state: filled quantity cannot be changed by hand: filled quantity of order … can only change through a trade |
| 1090 | | PASS state: executed order cannot be processed again: order … is already executed and cannot be processed again |
| 1091 | | PASS state: ordered quantity cannot change: user, market, side, type, quantity, price and placed_at of an order cannot change |
| 1092 | | PASS state: cancelling by hand without releasing the reservation: reserved balance 350.0000 does not match the 0 needed by active buy orders (user …) |
| 1093 | | PASS cancel: someone else's order: order … does not belong to this user |
| 1094 | | PASS cancel: 350 back from reserved to available, event recorded: bob available 4280.0000 reserved 0.0000 |
| 1095 | | PASS cancel: a cancelled order cannot be cancelled again: order … is cancelled and cannot be cancelled |
| 1096 | | PASS trade: cancelled order cannot trade: order … is cancelled and cannot trade |
| 1097 | | PASS balance: reserving cash with no order behind it: reserved balance 100.0000 does not match the 0 needed by active buy orders (user …) |
| 1098 | | PASS balance: reserving crypto with no order behind it: reserved quantity 0.0100 does not match the 0 needed by active sell orders (user …, crypto …) |
| 1099 | | PASS balance: cash changed without a ledger row: cash 2060.0000 (available + reserved) does not match the ledger total 1960.0000 (user …) |
| 1100 | | PASS job: fills exactly the orders the new price reached: bob_bid3 executed @ 3450.000000, charlie_ask (3700) still open |
| 1101 | | PASS job: buyer paid 345, got the 5 above the fill price back: |
| 1102 | | PASS job: nothing more to do on a second run: |
| 1103 | | PASS consistency holds after the job: |
| 1104 | | PASS views: every trader's cash equals their ledger: |
| 1105 | | |
| 1106 | | passed | failed |
| 1107 | | --------+-------- |
| 1108 | | 41 | 0 |
| 1109 | | }}} |
| 1110 | | |
| 1111 | | The same rules seen from the prototype (bob, after alice put 0.3 ETH up for sale at 3600): |
| 1112 | | |
| 1113 | | {{{ |
| 1114 | | -- Place buy order -- |
| 1115 | | Latest price for ETH/USD = 3520.000000 |
| 1116 | | Order book asks (other users' limit orders): |
| 1117 | | 3600.000000 0.3000 (1 orders) |
| 1118 | | [1] Market order (fills now at the best available price) |
| 1119 | | [2] Limit order (fills only at your price or better, otherwise waits in the order book) |
| 1120 | | > 2 |
| 1121 | | Quantity: 0.2 |
| 1122 | | Limit price: 3650 |
| 1123 | | Order executed: buy 0.2000 ETH, average price 3600.000000 |
| 1124 | | }}} |
| 1125 | | |
| 1126 | | === Test script === |
| 1127 | | |
| 1128 | | The complete test script, `advanced_db_tests.sql`: |
| 1129 | | |
| 1130 | | {{{ |
| 1131 | | -- advanced_db_tests.sql |
| 1132 | | -- EduBerza - tests for the P7 rules in advanced_db.sql |
| 1133 | | -- |
| 1134 | | -- Run right after data_load.sql (seed state: alice 8250 USD + 0.5 ETH, |
| 1135 | | -- bob 5000 USD, charlie 2500 USD, ETH/USD last traded at 3520). |
| 1136 | | -- It plays a short trading story and, along the way, tries to break every |
| 1137 | | -- rule. Each check prints PASS/FAIL as a NOTICE; everything is rolled back |
| 1138 | | -- at the end, so the data is left exactly as it was. |
| 1139 | | -- |
| 1140 | | -- The reservation and balance checks are deferred to COMMIT; the tests force |
| 1141 | | -- them with SET CONSTRAINTS ALL IMMEDIATE so a violation shows up inside the |
| 1142 | | -- test instead of at the final COMMIT. |
| 1143 | | |
| 1144 | | BEGIN; |
| 1145 | | SET search_path TO project, public; |
| 1146 | | |
| 1147 | | CREATE TEMP TABLE test_results (name text, passed boolean) ON COMMIT DROP; |
| 1148 | | CREATE TEMP TABLE ids (name text PRIMARY KEY, id uuid) ON COMMIT DROP; |
| 1149 | | |
| 1150 | | -- expect_error: run p_sql (and the deferred checks); it must fail with an |
| 1151 | | -- error containing p_fragment. |
| 1152 | | CREATE PROCEDURE pg_temp.expect_error(p_name text, p_sql text, p_fragment text) |
| 1153 | | LANGUAGE plpgsql AS $$ |
| 1154 | | BEGIN |
| 1155 | | BEGIN |
| 1156 | | EXECUTE p_sql; |
| 1157 | | SET CONSTRAINTS ALL IMMEDIATE; |
| 1158 | | RAISE NOTICE 'FAIL %: no error raised', p_name; |
| 1159 | | INSERT INTO test_results VALUES (p_name, false); |
| 1160 | | EXCEPTION WHEN OTHERS THEN |
| 1161 | | IF SQLERRM ILIKE '%' || p_fragment || '%' THEN |
| 1162 | | RAISE NOTICE 'PASS %: %', p_name, SQLERRM; |
| 1163 | | INSERT INTO test_results VALUES (p_name, true); |
| 1164 | | ELSE |
| 1165 | | RAISE NOTICE 'FAIL %: unexpected error: %', p_name, SQLERRM; |
| 1166 | | INSERT INTO test_results VALUES (p_name, false); |
| 1167 | | END IF; |
| 1168 | | END; |
| 1169 | | SET CONSTRAINTS ALL DEFERRED; |
| 1170 | | END $$; |
| 1171 | | |
| 1172 | | CREATE FUNCTION pg_temp.expect_true(p_name text, p_ok boolean, p_detail text DEFAULT '') |
| 1173 | | RETURNS void LANGUAGE plpgsql AS $$ |
| 1174 | | BEGIN |
| 1175 | | RAISE NOTICE '% %: %', CASE WHEN coalesce(p_ok, false) THEN 'PASS' ELSE 'FAIL' END, p_name, p_detail; |
| 1176 | | INSERT INTO test_results VALUES (p_name, coalesce(p_ok, false)); |
| 1177 | | END $$; |
| 1178 | | |
| 1179 | | -- consistent: all deferred checks pass right now |
| 1180 | | CREATE FUNCTION pg_temp.consistent() RETURNS boolean LANGUAGE plpgsql AS $$ |
| 1181 | | BEGIN |
| 1182 | | SET CONSTRAINTS ALL IMMEDIATE; |
| 1183 | | SET CONSTRAINTS ALL DEFERRED; |
| 1184 | | RETURN true; |
| 1185 | | EXCEPTION WHEN OTHERS THEN |
| 1186 | | RAISE NOTICE ' consistency check failed: %', SQLERRM; |
| 1187 | | RETURN false; |
| 1188 | | END $$; |
| 1189 | | |
| 1190 | | CREATE FUNCTION pg_temp.uid(p_name text) RETURNS uuid LANGUAGE sql AS $$ |
| 1191 | | SELECT id FROM users WHERE username = p_name |
| 1192 | | $$; |
| 1193 | | CREATE FUNCTION pg_temp.oid(p_name text) RETURNS uuid LANGUAGE sql AS $$ |
| 1194 | | SELECT id FROM ids WHERE name = p_name |
| 1195 | | $$; |
| 1196 | | |
| 1197 | | |
| 1198 | | -- =========================================================================== |
| 1199 | | -- A. Placing orders reserves what they commit |
| 1200 | | -- =========================================================================== |
| 1201 | | INSERT INTO ids VALUES ('alice_ask', |
| 1202 | | place_order(pg_temp.uid('alice'), 'a2222222-2222-2222-2222-222222222222', 'sell', 'limit', 0.3, 3600)); |
| 1203 | | INSERT INTO ids VALUES ('bob_bid', |
| 1204 | | place_order(pg_temp.uid('bob'), 'a2222222-2222-2222-2222-222222222222', 'buy', 'limit', 0.1, 3500)); |
| 1205 | | |
| 1206 | | SELECT pg_temp.expect_true('place: limit sell above the market rests in the book, crypto reserved', |
| 1207 | | (SELECT status FROM orders WHERE id = pg_temp.oid('alice_ask')) = 'open' |
| 1208 | | AND (SELECT reserved_quantity FROM holdings WHERE user_id = pg_temp.uid('alice')) = 0.3, |
| 1209 | | 'alice ETH reserved = ' || (SELECT reserved_quantity FROM holdings WHERE user_id = pg_temp.uid('alice'))); |
| 1210 | | SELECT pg_temp.expect_true('place: limit buy below the market rests in the book, cash reserved', |
| 1211 | | (SELECT (available_balance, reserved_balance) FROM users WHERE username = 'bob') = (4650.0000, 350.0000), |
| 1212 | | (SELECT format('bob available %s reserved %s', available_balance, reserved_balance) FROM users WHERE username = 'bob')); |
| 1213 | | SELECT pg_temp.expect_true('event: placement recorded automatically', |
| 1214 | | (SELECT count(*) FROM order_events WHERE order_id IN (pg_temp.oid('alice_ask'), pg_temp.oid('bob_bid')) |
| 1215 | | AND event_type = 'placed') = 2); |
| 1216 | | SELECT pg_temp.expect_true('view: order book shows both price levels', |
| 1217 | | (SELECT string_agg(side || ' ' || quantity || ' @ ' || price, ', ' ORDER BY side) |
| 1218 | | FROM v_order_book WHERE symbol = 'ETH') = 'buy 0.1000 @ 3500.000000, sell 0.3000 @ 3600.000000', |
| 1219 | | (SELECT string_agg(side || ' ' || quantity || ' @ ' || price, ', ' ORDER BY side) FROM v_order_book WHERE symbol = 'ETH')); |
| 1220 | | SELECT pg_temp.expect_true('consistency holds after placing', pg_temp.consistent()); |
| 1221 | | |
| 1222 | | CALL pg_temp.expect_error('place: buy without enough free cash', |
| 1223 | | $q$SELECT place_order(pg_temp.uid('charlie'), 'a1111111-1111-1111-1111-111111111111', 'buy', 'limit', 1, 60000)$q$, |
| 1224 | | 'insufficient funds'); |
| 1225 | | CALL pg_temp.expect_error('place: sell more than is free (0.2 of 0.5 is already reserved)', |
| 1226 | | $q$SELECT place_order(pg_temp.uid('alice'), 'a2222222-2222-2222-2222-222222222222', 'sell', 'limit', 0.3, 3600)$q$, |
| 1227 | | 'insufficient holding'); |
| 1228 | | |
| 1229 | | -- =========================================================================== |
| 1230 | | -- B. A trade between two compatible orders, partial fill |
| 1231 | | -- =========================================================================== |
| 1232 | | -- bob bids 0.2 at 3650: crosses alice's ask at 3600 -> trade 0.2 @ 3600 |
| 1233 | | INSERT INTO ids VALUES ('bob_bid2', |
| 1234 | | place_order(pg_temp.uid('bob'), 'a2222222-2222-2222-2222-222222222222', 'buy', 'limit', 0.2, 3650)); |
| 1235 | | |
| 1236 | | SELECT pg_temp.expect_true('match: trade between the two orders at the resting price', |
| 1237 | | EXISTS (SELECT 1 FROM market_trades |
| 1238 | | WHERE buy_order_id = pg_temp.oid('bob_bid2') AND sell_order_id = pg_temp.oid('alice_ask') |
| 1239 | | AND quantity = 0.2 AND price = 3600 AND source = 'match')); |
| 1240 | | SELECT pg_temp.expect_true('status: seller partially filled, buyer executed (automatic)', |
| 1241 | | (SELECT status || ' ' || filled_quantity FROM orders WHERE id = pg_temp.oid('alice_ask')) = 'partially_filled 0.2000' |
| 1242 | | AND (SELECT status FROM orders WHERE id = pg_temp.oid('bob_bid2')) = 'executed', |
| 1243 | | (SELECT format('alice_ask %s %s/%s', status, filled_quantity, quantity) FROM orders WHERE id = pg_temp.oid('alice_ask'))); |
| 1244 | | SELECT pg_temp.expect_true('money: buyer paid 720, got back the 10 reserved above the trade price', |
| 1245 | | (SELECT (available_balance, reserved_balance) FROM users WHERE username = 'bob') = (3930.0000, 350.0000), |
| 1246 | | (SELECT format('bob available %s reserved %s', available_balance, reserved_balance) FROM users WHERE username = 'bob')); |
| 1247 | | SELECT pg_temp.expect_true('crypto: 0.2 ETH moved from alice (0.1 still reserved) to bob', |
| 1248 | | (SELECT (quantity, reserved_quantity) FROM holdings WHERE user_id = pg_temp.uid('alice')) = (0.3000, 0.1000) |
| 1249 | | AND (SELECT (quantity, avg_price) FROM holdings WHERE user_id = pg_temp.uid('bob')) = (0.2000, 3600.000000)); |
| 1250 | | SELECT pg_temp.expect_true('ledger: one buy and one sell row, linked to the orders', |
| 1251 | | (SELECT count(*) FROM transactions WHERE related_order IN (pg_temp.oid('bob_bid2'), pg_temp.oid('alice_ask'))) = 2); |
| 1252 | | SELECT pg_temp.expect_true('event: fills recorded automatically', |
| 1253 | | (SELECT string_agg(event_type, ',' ORDER BY id) FROM order_events WHERE order_id = pg_temp.oid('alice_ask')) |
| 1254 | | = 'placed,partially_filled'); |
| 1255 | | SELECT pg_temp.expect_true('consistency holds after the trade', pg_temp.consistent()); |
| 1256 | | |
| 1257 | | -- =========================================================================== |
| 1258 | | -- C. Market order: book first, then the simulated market |
| 1259 | | -- =========================================================================== |
| 1260 | | -- market price is now 3600 (last trade); charlie buys 0.15 at market: |
| 1261 | | -- 0.1 from alice's remaining ask @ 3600, the other 0.05 from the market @ 3600 |
| 1262 | | INSERT INTO ids VALUES ('charlie_mkt', |
| 1263 | | place_order(pg_temp.uid('charlie'), 'a2222222-2222-2222-2222-222222222222', 'buy', 'market', 0.15)); |
| 1264 | | |
| 1265 | | SELECT pg_temp.expect_true('market order: filled completely in two trades', |
| 1266 | | (SELECT (status, trades, avg_fill_price) FROM v_order_history WHERE order_id = pg_temp.oid('charlie_mkt')) |
| 1267 | | = ('executed'::varchar, 2::bigint, 3600.000000::numeric), |
| 1268 | | (SELECT format('%s, %s trades, avg %s', status, trades, avg_fill_price) FROM v_order_history WHERE order_id = pg_temp.oid('charlie_mkt'))); |
| 1269 | | SELECT pg_temp.expect_true('market order: alice''s ask is now executed, nothing left reserved', |
| 1270 | | (SELECT status FROM orders WHERE id = pg_temp.oid('alice_ask')) = 'executed' |
| 1271 | | AND (SELECT reserved_quantity FROM holdings WHERE user_id = pg_temp.uid('alice')) = 0 |
| 1272 | | AND (SELECT reserved_balance FROM users WHERE username = 'charlie') = 0); |
| 1273 | | SELECT pg_temp.expect_true('consistency holds after the market order', pg_temp.consistent()); |
| 1274 | | |
| 1275 | | -- =========================================================================== |
| 1276 | | -- D. Trades are only possible between valid, compatible orders |
| 1277 | | -- =========================================================================== |
| 1278 | | INSERT INTO ids VALUES ('charlie_ask', |
| 1279 | | place_order(pg_temp.uid('charlie'), 'a2222222-2222-2222-2222-222222222222', 'sell', 'limit', 0.1, 3700)); |
| 1280 | | INSERT INTO ids VALUES ('bob_ask', |
| 1281 | | place_order(pg_temp.uid('bob'), 'a2222222-2222-2222-2222-222222222222', 'sell', 'limit', 0.1, 3800)); |
| 1282 | | |
| 1283 | | CALL pg_temp.expect_error('trade: price above the buyer''s limit', |
| 1284 | | $q$INSERT INTO market_trades (market_id, executed_at, price, quantity, buy_order_id, sell_order_id) |
| 1285 | | VALUES ('a2222222-2222-2222-2222-222222222222', now(), 3700, 0.1, pg_temp.oid('bob_bid'), pg_temp.oid('charlie_ask'))$q$, |
| 1286 | | 'outside the limit'); |
| 1287 | | CALL pg_temp.expect_error('trade: more than the order has remaining', |
| 1288 | | $q$INSERT INTO market_trades (market_id, executed_at, price, quantity, buy_order_id) |
| 1289 | | VALUES ('a2222222-2222-2222-2222-222222222222', now(), 3500, 0.5, pg_temp.oid('bob_bid'))$q$, |
| 1290 | | 'exceeds the remaining quantity'); |
| 1291 | | CALL pg_temp.expect_error('trade: a sell order used as the buy side', |
| 1292 | | $q$INSERT INTO market_trades (market_id, executed_at, price, quantity, buy_order_id) |
| 1293 | | VALUES ('a2222222-2222-2222-2222-222222222222', now(), 3700, 0.1, pg_temp.oid('charlie_ask'))$q$, |
| 1294 | | 'cannot be the buy side'); |
| 1295 | | CALL pg_temp.expect_error('trade: order of another market', |
| 1296 | | $q$INSERT INTO market_trades (market_id, executed_at, price, quantity, buy_order_id) |
| 1297 | | VALUES ('a1111111-1111-1111-1111-111111111111', now(), 3500, 0.1, pg_temp.oid('bob_bid'))$q$, |
| 1298 | | 'different market'); |
| 1299 | | CALL pg_temp.expect_error('trade: executed order cannot trade again', |
| 1300 | | $q$INSERT INTO market_trades (market_id, executed_at, price, quantity, sell_order_id) |
| 1301 | | VALUES ('a2222222-2222-2222-2222-222222222222', now(), 3600, 0.1, pg_temp.oid('alice_ask'))$q$, |
| 1302 | | 'is executed and cannot trade'); |
| 1303 | | CALL pg_temp.expect_error('trade: a user with their own order', |
| 1304 | | $q$INSERT INTO market_trades (market_id, executed_at, price, quantity, buy_order_id, sell_order_id) |
| 1305 | | VALUES ('a2222222-2222-2222-2222-222222222222', now(), 3500, 0.1, pg_temp.oid('bob_bid'), pg_temp.oid('bob_ask'))$q$, |
| 1306 | | 'own order'); |
| 1307 | | CALL pg_temp.expect_error('trade: a trade that filled orders cannot be deleted', |
| 1308 | | $q$DELETE FROM market_trades WHERE buy_order_id = pg_temp.oid('bob_bid2')$q$, |
| 1309 | | 'cannot be changed or deleted'); |
| 1310 | | |
| 1311 | | -- =========================================================================== |
| 1312 | | -- E. Order state changes |
| 1313 | | -- =========================================================================== |
| 1314 | | CALL pg_temp.expect_error('state: status cannot be set to executed by hand', |
| 1315 | | $q$UPDATE orders SET status = 'executed' WHERE id = pg_temp.oid('bob_bid')$q$, |
| 1316 | | 'status is derived automatically'); |
| 1317 | | CALL pg_temp.expect_error('state: filled quantity cannot be changed by hand', |
| 1318 | | $q$UPDATE orders SET filled_quantity = 0.05 WHERE id = pg_temp.oid('bob_bid')$q$, |
| 1319 | | 'can only change through a trade'); |
| 1320 | | CALL pg_temp.expect_error('state: executed order cannot be processed again', |
| 1321 | | $q$UPDATE orders SET status = 'cancelled' WHERE id = pg_temp.oid('alice_ask')$q$, |
| 1322 | | 'already executed'); |
| 1323 | | CALL pg_temp.expect_error('state: ordered quantity cannot change', |
| 1324 | | $q$UPDATE orders SET quantity = 1 WHERE id = pg_temp.oid('bob_bid')$q$, |
| 1325 | | 'cannot change'); |
| 1326 | | CALL pg_temp.expect_error('state: cancelling by hand without releasing the reservation', |
| 1327 | | $q$UPDATE orders SET status = 'cancelled' WHERE id = pg_temp.oid('bob_bid')$q$, |
| 1328 | | 'reserved balance'); |
| 1329 | | |
| 1330 | | -- =========================================================================== |
| 1331 | | -- F. Cancelling releases the reservation, exactly once |
| 1332 | | -- =========================================================================== |
| 1333 | | CALL pg_temp.expect_error('cancel: someone else''s order', |
| 1334 | | $q$SELECT cancel_order(pg_temp.oid('bob_bid'), pg_temp.uid('charlie'))$q$, |
| 1335 | | 'does not belong'); |
| 1336 | | SELECT cancel_order(pg_temp.oid('bob_bid'), pg_temp.uid('bob')); |
| 1337 | | SELECT pg_temp.expect_true('cancel: 350 back from reserved to available, event recorded', |
| 1338 | | (SELECT (available_balance, reserved_balance) FROM users WHERE username = 'bob') = (4280.0000, 0.0000) |
| 1339 | | AND EXISTS (SELECT 1 FROM order_events WHERE order_id = pg_temp.oid('bob_bid') AND event_type = 'cancelled'), |
| 1340 | | (SELECT format('bob available %s reserved %s', available_balance, reserved_balance) FROM users WHERE username = 'bob')); |
| 1341 | | CALL pg_temp.expect_error('cancel: a cancelled order cannot be cancelled again', |
| 1342 | | $q$SELECT cancel_order(pg_temp.oid('bob_bid'), pg_temp.uid('bob'))$q$, |
| 1343 | | 'cannot be cancelled'); |
| 1344 | | CALL pg_temp.expect_error('trade: cancelled order cannot trade', |
| 1345 | | $q$INSERT INTO market_trades (market_id, executed_at, price, quantity, buy_order_id) |
| 1346 | | VALUES ('a2222222-2222-2222-2222-222222222222', now(), 3500, 0.1, pg_temp.oid('bob_bid'))$q$, |
| 1347 | | 'is cancelled and cannot trade'); |
| 1348 | | |
| 1349 | | -- =========================================================================== |
| 1350 | | -- G. Balances cannot be put in an inconsistent state |
| 1351 | | -- =========================================================================== |
| 1352 | | CALL pg_temp.expect_error('balance: reserving cash with no order behind it', |
| 1353 | | $q$UPDATE users SET available_balance = available_balance - 100, reserved_balance = reserved_balance + 100 |
| 1354 | | WHERE username = 'charlie'$q$, |
| 1355 | | 'reserved balance'); |
| 1356 | | CALL pg_temp.expect_error('balance: reserving crypto with no order behind it', |
| 1357 | | $q$UPDATE holdings SET reserved_quantity = reserved_quantity + 0.01 WHERE user_id = pg_temp.uid('alice')$q$, |
| 1358 | | 'reserved quantity'); |
| 1359 | | CALL pg_temp.expect_error('balance: cash changed without a ledger row', |
| 1360 | | $q$UPDATE users SET available_balance = available_balance + 100 WHERE username = 'charlie'$q$, |
| 1361 | | 'does not match the ledger'); |
| 1362 | | |
| 1363 | | -- =========================================================================== |
| 1364 | | -- H. Background job: resting limit orders filled when the market reaches them |
| 1365 | | -- =========================================================================== |
| 1366 | | INSERT INTO ids VALUES ('bob_bid3', |
| 1367 | | place_order(pg_temp.uid('bob'), 'a2222222-2222-2222-2222-222222222222', 'buy', 'limit', 0.1, 3500)); |
| 1368 | | -- the simulator moves the price down to 3450 (a bot tick, no orders) |
| 1369 | | INSERT INTO market_trades (market_id, executed_at, price, quantity, side) |
| 1370 | | VALUES ('a2222222-2222-2222-2222-222222222222', now(), 3450, 0.01, 'sell'); |
| 1371 | | |
| 1372 | | CREATE TEMP TABLE job_run ON COMMIT DROP AS SELECT fill_marketable_orders() AS filled; |
| 1373 | | |
| 1374 | | SELECT pg_temp.expect_true('job: fills exactly the orders the new price reached', |
| 1375 | | (SELECT filled FROM job_run) = 1 |
| 1376 | | AND (SELECT (status, avg_fill_price) FROM v_order_history WHERE order_id = pg_temp.oid('bob_bid3')) |
| 1377 | | = ('executed'::varchar, 3450.000000::numeric) |
| 1378 | | AND (SELECT status FROM orders WHERE id = pg_temp.oid('charlie_ask')) = 'open', |
| 1379 | | (SELECT format('bob_bid3 %s @ %s, charlie_ask (3700) still %s', h.status, h.avg_fill_price, o.status) |
| 1380 | | FROM v_order_history h, orders o WHERE h.order_id = pg_temp.oid('bob_bid3') AND o.id = pg_temp.oid('charlie_ask'))); |
| 1381 | | SELECT pg_temp.expect_true('job: buyer paid 345, got the 5 above the fill price back', |
| 1382 | | (SELECT reserved_balance FROM users WHERE username = 'bob') = 0 |
| 1383 | | AND (SELECT count(*) FROM transactions WHERE related_order = pg_temp.oid('bob_bid3') AND amount = -345) = 1); |
| 1384 | | SELECT pg_temp.expect_true('job: nothing more to do on a second run', fill_marketable_orders() = 0); |
| 1385 | | SELECT pg_temp.expect_true('consistency holds after the job', pg_temp.consistent()); |
| 1386 | | |
| 1387 | | -- =========================================================================== |
| 1388 | | -- Final state of every trader |
| 1389 | | -- =========================================================================== |
| 1390 | | SELECT pg_temp.expect_true('views: every trader''s cash equals their ledger', |
| 1391 | | NOT EXISTS (SELECT 1 FROM v_trader_balances WHERE total_cash <> ledger_total)); |
| 1392 | | |
| 1393 | | SELECT username, available_balance, reserved_balance, ledger_total, holdings_value |
| 1394 | | FROM v_trader_balances ORDER BY username; |
| 1395 | | |
| 1396 | | SELECT count(*) FILTER (WHERE passed) AS passed, |
| 1397 | | count(*) FILTER (WHERE NOT passed) AS failed |
| 1398 | | FROM test_results; |
| 1399 | | |
| 1400 | | ROLLBACK; |
| 1401 | | }}} |
| 1402 | | |
| 1403 | | == Changes to earlier phases == |
| 1404 | | |
| 1405 | | * '''Schema''' ([wiki:RelationalDesign], [wiki:ERModel]): |
| 1406 | | * `users.reserved_balance`; |
| 1407 | | * `orders.filled_quantity` and the status value `partially_filled`; |
| 1408 | | * `market_trades.buy_order_id` / `sell_order_id` (optional references to `orders` — a new relationship "trade fills order"); |
| 1409 | | * the new table `order_events`. |
| 1410 | | * '''Sample data''' (`data_load.sql`): |
| 1411 | | * deposit rows for bob and charlie, whose balances previously had no ledger entries behind them; |
| 1412 | | * alice's seeded order is imported as completely filled; |
| 1413 | | * the script runs as one transaction. |
| 1414 | | |
| 1415 | | The balances documented in [wiki:BuildInstructions] are |
| 1416 | | unchanged. |
| 1417 | | * '''P6 demo data''' (`reports_demo_data.sql`): it adjusts the users' balances by exactly what it adds to the ledger, and imports its orders as completely filled. Both [wiki:AdvancedReports] outputs are unchanged. |
| 1418 | | * '''Prototype:''' |
| 1419 | | * placing an order is one call to `place_order`, and market or limit can be chosen; |
| 1420 | | * new menu items `[12] Order book`, `[13] My open orders`, `[14] Cancel an order`; |
| 1421 | | * the balance screen shows reserved cash; |
| 1422 | | * the bot runs the background job. |
| 1423 | | |
| 1424 | | == AI usage == |
| 1425 | | |
| 1426 | | AI was used in this phase and is logged in full, per the course rule for P1 onward. |
| 1427 | | |
| 1428 | | * '''Phase log:''' [wiki:AdvancedDatabaseDevelopmentAIUsage] |
| 1429 | | |
| 1430 | | '''In short:''' the requirements — order state consistency, filled/remaining quantities, reserved |
| 1431 | | money and assets, trades only between compatible orders, the kinds of triggers, procedures and |
| 1432 | | views, and a background job only if relevant — were mine. I asked the AI to turn them into |
| 1433 | | concrete rules for the existing !EduBerza database, implement them without redesigning it, test |
| 1434 | | them and document them. |
