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 $$;