source: docs/P7-AdvancedDatabaseDevelopment/wiki/AdvancedDatabaseDevelopment.md

    = Advanced Database Development =

    !EduBerza's users trade virtual money and virtual crypto with each other and with a simulated
    market. Up to P6 the prototype only knew market orders that filled immediately against the
    latest price, so an order was either untouched or completely done. This phase adds what makes an
    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
    database itself — so it holds no matter who writes the data (the CLI, the market bot, a script,
    or someone typing SQL in DBeaver).

    Only rules that span several rows or several tables are listed. `NOT NULL`, `UNIQUE`, `CHECK`,
    primary and foreign keys are P2 ([wiki:RelationalDesign])
    and are not presented as P7 features.

    All of it is in `server/db/advanced_db.sql`, run by `-init`
    between `schema_creation.sql` and `data_load.sql`. Every rule is exercised by
    `server/db/advanced_db_tests.sql` (see
    Tests).

    == Overview ==

    ||= # =||= Requirement =||= Triggers =||= Procedures / functions =||= Views =||= Tables affected =||
    || 1 || Order lifecycle and filled/remaining consistency || `orders_lifecycle` ||  ||  || `orders` ||
    || 2 || Trade consistency || `market_trades_validate`, `market_trades_fill`, `market_trades_immutable` || `execute_trade` ||  || `market_trades`, `orders`, `users`, `holdings`, `transactions` ||
    || 3 || Balance and reservation consistency || `reserved_cash_matches_orders`, `reserved_crypto_matches_orders`, `cash_matches_ledger` (deferred) || `order_reservation` ||  || `users`, `holdings`, `orders`, `transactions` ||
    || 4 || Placing and cancelling orders ||  || `place_order`, `match_order`, `cancel_order` ||  || all of the above ||
    || 5 || Automatic recording of order events || `orders_events` ||  ||  || `order_events` (new) ||
    || 6 || Views for derived trading data ||  ||  || `v_order_book`, `v_active_orders`, `v_order_history`, `v_trader_balances` || read-only ||
    || 7 || Background job: filling resting limit orders ||  || `fill_marketable_orders` ||  || `orders`, `market_trades`, … ||

    === Schema additions ===

    The existing design had no place for three things the requirements need, so four columns were
    added — nothing else in the design changed:

    ||= Column =||= Why it is needed =||
    || `orders.filled_quantity` (+ status value `partially_filled`) || "filled / remaining quantity" cannot be kept consistent without storing how much was filled; remaining = `quantity − filled_quantity` ||
    || `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 ||
    || `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` ||

    One new table, `order_events`, holds the automatically recorded events (requirement 5).

    == Order lifecycle and filled/remaining consistency ==

    === Data requirements description ===

    '''Business rule.'''

    * 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.
    * `executed` and `cancelled` are final — such an order can never be processed again (no further fills, no cancellation, no changes).
    * `filled_quantity` only grows, never exceeds `quantity`, and only changes when a trade fills the order.
    * What was ordered — user, market, side, type, quantity, price, time of placement — never changes after placement.
    * `executed_at` is set exactly when the order becomes `executed`.
    * 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).

    '''Why it is non-trivial.''' The rule compares the ''old'' and the ''new'' version of a row (valid
    transitions, "final" states, "only grows", immutable columns) and ties two columns together
    (status ↔ filled quantity). A `CHECK` constraint only sees one version of one row. It also
    depends on ''who'' changes `filled_quantity` — a trade may, a manual `UPDATE` may not.

    '''PostgreSQL feature.''' `BEFORE INSERT OR UPDATE` row trigger on `orders`. The trade trigger
    (requirement 2) sets a transaction-local setting (`set_config('eduberza.trade_fill', 'on', true)`)
    while it fills an order; the lifecycle trigger only accepts a change of `filled_quantity` when
    that setting is on.

    '''Tables affected.''' `orders` (reads `markets` for the active check).

    === Implementation ===

    ==== Triggers ====

    {{{
    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();
    }}}

    Because the status is derived, the application never sets `open`, `partially_filled` or
    `executed` itself — it records trades, and the status follows.

    == Trade consistency ==

    === Data requirements description ===

    '''Business rule.''' A trade that fills orders must be possible for those orders:

    * the buy side is a buy order and the sell side is a sell order;
    * both are on the trade's market and still active — a cancelled or executed order can never trade again;
    * the trade quantity does not exceed the remaining quantity of either order;
    * the price respects both limits: at most the buy order's price, at least the sell order's price;
    * the two orders belong to different users (no self-trade).

    Recording the trade must fill both orders by exactly the traded quantity (and so move their
    status), and move the money and crypto for both users, all together. A trade that filled orders
    is history and can't be changed or deleted afterwards — the fills and the money moved would no
    longer match it.

    '''Why it is non-trivial.''' One trade row has to be checked against two other rows of another
    table (the orders), including their current remaining quantity, and a single insert must cause
    consistent changes in five tables (`market_trades`, both `orders`, both users' `users` and
    `holdings` rows, two `transactions` rows).

    '''PostgreSQL features.'''

    * `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.
    * `AFTER INSERT` trigger `market_trades_fill` — raises `filled_quantity` on both orders (automatic status change through requirement 1).
    * `BEFORE UPDATE OR DELETE` trigger `market_trades_immutable`.
    * Stored function `execute_trade` — the settlement of money and crypto for one trade.

    Because the rules sit on `market_trades` itself, even a trade inserted by hand is validated and
    fills the orders.

    '''Tables affected.''' `market_trades`, `orders`, `users`, `holdings`, `transactions`.

    === Implementation ===

    ==== Triggers ====

    {{{
    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();
    }}}

    {{{
    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();
    }}}

    {{{
    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();
    }}}

    ==== Stored procedures/functions ====

    `execute_trade(buy_order, sell_order, quantity, price)` — one trade. Either order may be `NULL`
    when the simulated market is the counterparty. The `INSERT` into `market_trades` validates the
    trade and fills the orders (triggers above); the function then settles both users:

    * '''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.
    * '''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.

    Both orders are locked in a fixed order (by id), so two trades on the same pair of orders can't
    deadlock.

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

    == Balance and reservation consistency ==

    === Data requirements description ===

    '''Business rule.'''

    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.
    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.
    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).

    Together these make it impossible for an order operation to leave a balance in an inconsistent
    state: money or crypto reserved for nothing, an order that is not backed by a reservation (and
    so could spend the same money twice), or cash that appeared or vanished without a ledger entry.

    '''Why it is non-trivial.''' Each rule is an equality between a column and an aggregate over
    ''other'' rows of ''other'' tables. Worse, every legitimate operation breaks it for a moment:
    placing a buy moves cash to `reserved_balance` in one statement and inserts the order in the
    next; a trade releases the reservation, pays, and writes the ledger in several statements. Only
    the state at the end of the transaction has to be consistent.

    '''PostgreSQL feature.''' `CONSTRAINT TRIGGER … DEFERRABLE INITIALLY DEFERRED` — row triggers
    whose check runs at `COMMIT`, on the final state. A transaction that leaves any of the three
    equalities broken fails at `COMMIT` and is rolled back as a whole. Each rule has a trigger on
    every table whose change can break it. `order_reservation(remaining, price)` is the one place
    that defines how a reservation is rounded, so placing, filling, cancelling and checking always
    agree to the last decimal.

    '''Tables affected.''' `users`, `holdings`, `orders`, `transactions`.

    === Implementation ===

    ==== Stored procedures/functions ====

    {{{
    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)
    $$;
    }}}

    ==== Triggers ====

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

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

    {{{
    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();
    }}}

    The partial index `idx_orders_active` covers exactly the rows the reservation checks sum, and
    stays small because active orders are few compared with the whole order history.

    == Placing and cancelling orders ==

    === Data requirements description ===

    '''Business rule.''' Placing an order must, as one unit:

    1. check the market, side, type, quantity and price;
    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;
    3. record the order;
    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;
    5. fill whatever is still unfilled from the simulated market if it is marketable at the current market price.

    A '''market order''' is priced at the current market price, so it always fills completely in step
    4 or 5 and never waits. A '''limit order''' that isn't marketable stays in the order book.
    Cancelling an order must release exactly what it still reserves, and only for an active order of
    the caller.

    '''Why it is non-trivial.''' It is a multi-step operation over five tables whose steps depend on
    each other (how much is left after each match, what to release), and it has to be correct under
    concurrency: two orders of the same user must not both see the same free cash.

    '''PostgreSQL feature.''' Stored functions (PL/pgSQL). `place_order` locks the user's row
    (`SELECT … FOR UPDATE`) before checking free cash or crypto, so concurrent orders of the same
    user are serialised. Every step's consistency is still checked by the triggers of requirements
    1–3.

    '''Tables affected.''' `orders`, `users`, `holdings`, `market_trades`, `transactions`.

    === Implementation ===

    ==== Stored procedures/functions ====

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

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

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

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

    In the prototype, placing an order is now a single call:

    {{{
    err = db.DB.QueryRow(
        `SELECT place_order($1, $2, $3, $4, $5, $6)`,
        s.UserID, m.ID, side, orderType, qty, limit.value(),
    ).Scan(&orderID)
    }}}

    Cancelling is `SELECT cancel_order($1, $2)` with the order the user picked from a numbered list
    of their open orders (`server/trade.go`).

    == Automatic recording of order events ==

    === Data requirements description ===

    '''Business rule.''' Every important thing that happens to an order is recorded with its time:
    placement, each fill (with the quantity filled and the trade price), and cancellation. This is
    the order's audit trail — the history of ''how'' it reached its current state, which the order row
    alone (only the current state) cannot show.

    '''Why it is non-trivial.''' Events come from several places — `place_order`, trades made by
    `match_order`, trades made by the background job, `cancel_order`, and any direct SQL. Recording
    them in each of those places would miss some; recording them where the change actually happens
    cannot.

    '''PostgreSQL feature.''' `AFTER INSERT OR UPDATE` row trigger on `orders`, writing into the new
    table `order_events`. The fill price is handed over by the trade trigger through a
    transaction-local setting.

    '''Tables affected.''' `order_events` (new), written from changes to `orders`.

    === Implementation ===

    {{{
    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()
    );
    }}}

    ==== Triggers ====

    {{{
    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();
    }}}

    == Views for derived trading data ==

    === Data requirements description ===

    The application needs several things that are ''derived'' from orders, trades and balances. They
    are defined once as views, instead of repeating the calculations in the application code:

    ||= View =||= Derived data =||= Used by =||
    || `v_active_orders` || active orders with remaining quantity and what each one holds in reserve || CLI `[13] My open orders`, `[14] Cancel an order` ||
    || `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 ||
    || `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 ||
    || `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` ||

    None of these repeats a P6 report: P6 aggregates performance over a period, while these show
    the current state of the order book and accounts.

    === Implementation ===

    ==== Views ====

    {{{
    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');
    }}}

    {{{
    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;
    }}}

    {{{
    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;
    }}}

    {{{
    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;
    }}}

    == Background job: filling resting limit orders ==

    === Data requirements description ===

    '''Business rule.''' In !EduBerza the market price is moved by the simulator (the market bot), not
    by users' orders. A limit order that rests in the book — a buy at or above, or a sell at or
    below, the current market price — must then be filled by the simulated market, at the market
    price, just as it would have been had the price already been there when the order was placed.

    '''Why it is relevant, and why a background job.''' Without it, a limit order could only ever
    fill against another user's order, and with few users most limit orders would wait forever while
    the market price has long passed them. That would make limit orders useless in the simulation.
    Nothing happens at the moment the price crosses an order that a trigger could react to: the
    price moves through the bot's inserts into `market_trades`, which deliberately stay cheap single
    inserts. Scanning and filling every crossed order on every tick inside that insert would make
    each tick expensive. So the work runs as a periodic job after each round of price ticks.

    '''PostgreSQL feature.''' Stored function `fill_marketable_orders()`. It is scheduled by the
    application, because PostgreSQL has no built-in scheduler and the faculty server provides no
    `pg_cron` (checked: only `plpgsql` and `pgcrypto` are available, and the project role is not a
    superuser).

    * An advisory lock (`pg_try_advisory_xact_lock`) keeps two runs from filling the same orders twice.
    * `FOR UPDATE … SKIP LOCKED` leaves alone an order a user is cancelling at that moment; the next run picks it up.
    * Every fill goes through `execute_trade`, so all the rules above apply to it.

    '''Tables affected.''' `orders`, `market_trades`, `users`, `holdings`, `transactions`,
    `order_events`.

    === Implementation ===

    ==== Stored procedures/functions ====

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

    ==== Scheduling ====

    In `bots/main.go`, after every round of price ticks:

    {{{
    // P7 background job: the prices just moved, so fill any resting
    // limit order the new market price has reached.
    var filled int
    if err := db.QueryRow(`SELECT fill_marketable_orders()`).Scan(&filled); err != nil {
        log.Printf("fill_marketable_orders: %v", err)
    } else if filled > 0 {
        log.Printf("  filled %d resting limit order(s) at the new market price", filled)
    }
    }}}

    It can also be run by hand from any SQL client: `SELECT project.fill_marketable_orders();`.

    A run with the bot, after charlie placed a limit buy of 0.1 ETH at 3599 while the market was at
    3600: the bot's random walk took the price below 3599 and the job filled the order at the market
    price.

    {{{
    2026/09/24 13:19:02   filled 1 resting limit order(s) at the new market price
    }}}

    {{{
    username | side | type  |  status   | quantity | filled_quantity |    price    | avg_fill_price
    ----------+------+-------+-----------+----------+-----------------+-------------+----------------
    charlie  | buy  | limit | cancelled |   0.1000 |          0.0000 | 3400.000000 |
    charlie  | buy  | limit | executed  |   0.1000 |          0.1000 | 3599.000000 |    3593.979167
    }}}

    == Tests proving the rules ==

    `server/db/advanced_db_tests.sql` plays a short trading
    story on the sample data and, along the way, tries to break every rule. It starts from alice with
    8250 USD and 0.5 ETH, bob with 5000 USD, charlie with 2500 USD, and ETH/USD last traded at 3520:

    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.
    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.
    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.
    4. Invalid trades, state changes and balance changes are attempted directly in SQL.
    5. bob cancels his bid.
    6. The simulator moves the price to 3450, and the background job fills bob's new limit buy at 3500.

    The deferred checks are forced with `SET CONSTRAINTS ALL IMMEDIATE`, so a violation shows up
    inside the test instead of at the final `COMMIT`. Everything is rolled back at the end. Run on
    PostgreSQL 17 after `-init`:

    {{{
    PASS  place: limit sell above the market rests in the book, crypto reserved: alice ETH reserved = 0.3000
    PASS  place: limit buy below the market rests in the book, cash reserved: bob available 4650.0000 reserved 350.0000
    PASS  event: placement recorded automatically:
    PASS  view: order book shows both price levels: buy 0.1000 @ 3500.000000, sell 0.3000 @ 3600.000000
    PASS  consistency holds after placing:
    PASS  place: buy without enough free cash: insufficient funds: the order needs 60000.0000, available 2500.0000
    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
    PASS  match: trade between the two orders at the resting price:
    PASS  status: seller partially filled, buyer executed (automatic): alice_ask partially_filled 0.2000/0.3000
    PASS  money: buyer paid 720, got back the 10 reserved above the trade price: bob available 3930.0000 reserved 350.0000
    PASS  crypto: 0.2 ETH moved from alice (0.1 still reserved) to bob:
    PASS  ledger: one buy and one sell row, linked to the orders:
    PASS  event: fills recorded automatically:
    PASS  consistency holds after the trade:
    PASS  market order: filled completely in two trades: executed, 2 trades, avg 3600.000000
    PASS  market order: alice's ask is now executed, nothing left reserved:
    PASS  consistency holds after the market order:
    PASS  trade: price above the buyer's limit: trade price 3700.000000 is outside the limit 3500.000000 of buy order …
    PASS  trade: more than the order has remaining: trade quantity 0.500000 exceeds the remaining quantity 0.1000 of order …
    PASS  trade: a sell order used as the buy side: order … is a sell order and cannot be the buy side of a trade
    PASS  trade: order of another market: order … is on a different market than the trade
    PASS  trade: executed order cannot trade again: order … is executed and cannot trade
    PASS  trade: a user with their own order: a user cannot trade with their own order
    PASS  trade: a trade that filled orders cannot be deleted: a trade that filled orders cannot be changed or deleted
    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
    PASS  state: filled quantity cannot be changed by hand: filled quantity of order … can only change through a trade
    PASS  state: executed order cannot be processed again: order … is already executed and cannot be processed again
    PASS  state: ordered quantity cannot change: user, market, side, type, quantity, price and placed_at of an order cannot change
    PASS  state: cancelling by hand without releasing the reservation: reserved balance 350.0000 does not match the 0 needed by active buy orders (user …)
    PASS  cancel: someone else's order: order … does not belong to this user
    PASS  cancel: 350 back from reserved to available, event recorded: bob available 4280.0000 reserved 0.0000
    PASS  cancel: a cancelled order cannot be cancelled again: order … is cancelled and cannot be cancelled
    PASS  trade: cancelled order cannot trade: order … is cancelled and cannot trade
    PASS  balance: reserving cash with no order behind it: reserved balance 100.0000 does not match the 0 needed by active buy orders (user …)
    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 …)
    PASS  balance: cash changed without a ledger row: cash 2060.0000 (available + reserved) does not match the ledger total 1960.0000 (user …)
    PASS  job: fills exactly the orders the new price reached: bob_bid3 executed @ 3450.000000, charlie_ask (3700) still open
    PASS  job: buyer paid 345, got the 5 above the fill price back:
    PASS  job: nothing more to do on a second run:
    PASS  consistency holds after the job:
    PASS  views: every trader's cash equals their ledger:

    passed | failed
    --------+--------
        41 |      0
    }}}

    The same rules seen from the prototype (bob, after alice put 0.3 ETH up for sale at 3600):

    {{{
    -- Place buy order --
    Latest price for ETH/USD = 3520.000000
    Order book asks (other users' limit orders):
        3600.000000        0.3000  (1 orders)
    [1] Market order (fills now at the best available price)
    [2] Limit order (fills only at your price or better, otherwise waits in the order book)
    > 2
    Quantity: 0.2
    Limit price: 3650
    Order executed: buy 0.2000 ETH, average price 3600.000000
    }}}

    === Test script ===

    The complete test script, `advanced_db_tests.sql`:

    {{{
    -- advanced_db_tests.sql
    -- EduBerza - tests for the P7 rules in advanced_db.sql
    --
    -- Run right after data_load.sql (seed state: alice 8250 USD + 0.5 ETH,
    -- bob 5000 USD, charlie 2500 USD, ETH/USD last traded at 3520).
    -- It plays a short trading story and, along the way, tries to break every
    -- rule. Each check prints PASS/FAIL as a NOTICE; everything is rolled back
    -- at the end, so the data is left exactly as it was.
    --
    -- The reservation and balance checks are deferred to COMMIT; the tests force
    -- them with SET CONSTRAINTS ALL IMMEDIATE so a violation shows up inside the
    -- test instead of at the final COMMIT.

    BEGIN;
    SET search_path TO project, public;

    CREATE TEMP TABLE test_results (name text, passed boolean) ON COMMIT DROP;
    CREATE TEMP TABLE ids (name text PRIMARY KEY, id uuid) ON COMMIT DROP;

    -- expect_error: run p_sql (and the deferred checks); it must fail with an
    -- error containing p_fragment.
    CREATE PROCEDURE pg_temp.expect_error(p_name text, p_sql text, p_fragment text)
    LANGUAGE plpgsql AS $$
    BEGIN
        BEGIN
            EXECUTE p_sql;
            SET CONSTRAINTS ALL IMMEDIATE;
            RAISE NOTICE 'FAIL  %: no error raised', p_name;
            INSERT INTO test_results VALUES (p_name, false);
        EXCEPTION WHEN OTHERS THEN
            IF SQLERRM ILIKE '%' || p_fragment || '%' THEN
                RAISE NOTICE 'PASS  %: %', p_name, SQLERRM;
                INSERT INTO test_results VALUES (p_name, true);
            ELSE
                RAISE NOTICE 'FAIL  %: unexpected error: %', p_name, SQLERRM;
                INSERT INTO test_results VALUES (p_name, false);
            END IF;
        END;
        SET CONSTRAINTS ALL DEFERRED;
    END $$;

    CREATE FUNCTION pg_temp.expect_true(p_name text, p_ok boolean, p_detail text DEFAULT '')
    RETURNS void LANGUAGE plpgsql AS $$
    BEGIN
        RAISE NOTICE '%  %: %', CASE WHEN coalesce(p_ok, false) THEN 'PASS' ELSE 'FAIL' END, p_name, p_detail;
        INSERT INTO test_results VALUES (p_name, coalesce(p_ok, false));
    END $$;

    -- consistent: all deferred checks pass right now
    CREATE FUNCTION pg_temp.consistent() RETURNS boolean LANGUAGE plpgsql AS $$
    BEGIN
        SET CONSTRAINTS ALL IMMEDIATE;
        SET CONSTRAINTS ALL DEFERRED;
        RETURN true;
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE '      consistency check failed: %', SQLERRM;
        RETURN false;
    END $$;

    CREATE FUNCTION pg_temp.uid(p_name text) RETURNS uuid LANGUAGE sql AS $$
        SELECT id FROM users WHERE username = p_name
    $$;
    CREATE FUNCTION pg_temp.oid(p_name text) RETURNS uuid LANGUAGE sql AS $$
        SELECT id FROM ids WHERE name = p_name
    $$;


    -- ===========================================================================
    -- A. Placing orders reserves what they commit
    -- ===========================================================================
    INSERT INTO ids VALUES ('alice_ask',
        place_order(pg_temp.uid('alice'), 'a2222222-2222-2222-2222-222222222222', 'sell', 'limit', 0.3, 3600));
    INSERT INTO ids VALUES ('bob_bid',
        place_order(pg_temp.uid('bob'), 'a2222222-2222-2222-2222-222222222222', 'buy', 'limit', 0.1, 3500));

    SELECT pg_temp.expect_true('place: limit sell above the market rests in the book, crypto reserved',
        (SELECT status FROM orders WHERE id = pg_temp.oid('alice_ask')) = 'open'
        AND (SELECT reserved_quantity FROM holdings WHERE user_id = pg_temp.uid('alice')) = 0.3,
        'alice ETH reserved = ' || (SELECT reserved_quantity FROM holdings WHERE user_id = pg_temp.uid('alice')));
    SELECT pg_temp.expect_true('place: limit buy below the market rests in the book, cash reserved',
        (SELECT (available_balance, reserved_balance) FROM users WHERE username = 'bob') = (4650.0000, 350.0000),
        (SELECT format('bob available %s reserved %s', available_balance, reserved_balance) FROM users WHERE username = 'bob'));
    SELECT pg_temp.expect_true('event: placement recorded automatically',
        (SELECT count(*) FROM order_events WHERE order_id IN (pg_temp.oid('alice_ask'), pg_temp.oid('bob_bid'))
        AND event_type = 'placed') = 2);
    SELECT pg_temp.expect_true('view: order book shows both price levels',
        (SELECT string_agg(side || ' ' || quantity || ' @ ' || price, ', ' ORDER BY side)
        FROM v_order_book WHERE symbol = 'ETH') = 'buy 0.1000 @ 3500.000000, sell 0.3000 @ 3600.000000',
        (SELECT string_agg(side || ' ' || quantity || ' @ ' || price, ', ' ORDER BY side) FROM v_order_book WHERE symbol = 'ETH'));
    SELECT pg_temp.expect_true('consistency holds after placing', pg_temp.consistent());

    CALL pg_temp.expect_error('place: buy without enough free cash',
        $q$SELECT place_order(pg_temp.uid('charlie'), 'a1111111-1111-1111-1111-111111111111', 'buy', 'limit', 1, 60000)$q$,
        'insufficient funds');
    CALL pg_temp.expect_error('place: sell more than is free (0.2 of 0.5 is already reserved)',
        $q$SELECT place_order(pg_temp.uid('alice'), 'a2222222-2222-2222-2222-222222222222', 'sell', 'limit', 0.3, 3600)$q$,
        'insufficient holding');

    -- ===========================================================================
    -- B. A trade between two compatible orders, partial fill
    -- ===========================================================================
    -- bob bids 0.2 at 3650: crosses alice's ask at 3600 -> trade 0.2 @ 3600
    INSERT INTO ids VALUES ('bob_bid2',
        place_order(pg_temp.uid('bob'), 'a2222222-2222-2222-2222-222222222222', 'buy', 'limit', 0.2, 3650));

    SELECT pg_temp.expect_true('match: trade between the two orders at the resting price',
        EXISTS (SELECT 1 FROM market_trades
                WHERE buy_order_id = pg_temp.oid('bob_bid2') AND sell_order_id = pg_temp.oid('alice_ask')
                AND quantity = 0.2 AND price = 3600 AND source = 'match'));
    SELECT pg_temp.expect_true('status: seller partially filled, buyer executed (automatic)',
        (SELECT status || ' ' || filled_quantity FROM orders WHERE id = pg_temp.oid('alice_ask')) = 'partially_filled 0.2000'
        AND (SELECT status FROM orders WHERE id = pg_temp.oid('bob_bid2')) = 'executed',
        (SELECT format('alice_ask %s %s/%s', status, filled_quantity, quantity) FROM orders WHERE id = pg_temp.oid('alice_ask')));
    SELECT pg_temp.expect_true('money: buyer paid 720, got back the 10 reserved above the trade price',
        (SELECT (available_balance, reserved_balance) FROM users WHERE username = 'bob') = (3930.0000, 350.0000),
        (SELECT format('bob available %s reserved %s', available_balance, reserved_balance) FROM users WHERE username = 'bob'));
    SELECT pg_temp.expect_true('crypto: 0.2 ETH moved from alice (0.1 still reserved) to bob',
        (SELECT (quantity, reserved_quantity) FROM holdings WHERE user_id = pg_temp.uid('alice')) = (0.3000, 0.1000)
        AND (SELECT (quantity, avg_price) FROM holdings WHERE user_id = pg_temp.uid('bob')) = (0.2000, 3600.000000));
    SELECT pg_temp.expect_true('ledger: one buy and one sell row, linked to the orders',
        (SELECT count(*) FROM transactions WHERE related_order IN (pg_temp.oid('bob_bid2'), pg_temp.oid('alice_ask'))) = 2);
    SELECT pg_temp.expect_true('event: fills recorded automatically',
        (SELECT string_agg(event_type, ',' ORDER BY id) FROM order_events WHERE order_id = pg_temp.oid('alice_ask'))
            = 'placed,partially_filled');
    SELECT pg_temp.expect_true('consistency holds after the trade', pg_temp.consistent());

    -- ===========================================================================
    -- C. Market order: book first, then the simulated market
    -- ===========================================================================
    -- market price is now 3600 (last trade); charlie buys 0.15 at market:
    -- 0.1 from alice's remaining ask @ 3600, the other 0.05 from the market @ 3600
    INSERT INTO ids VALUES ('charlie_mkt',
        place_order(pg_temp.uid('charlie'), 'a2222222-2222-2222-2222-222222222222', 'buy', 'market', 0.15));

    SELECT pg_temp.expect_true('market order: filled completely in two trades',
        (SELECT (status, trades, avg_fill_price) FROM v_order_history WHERE order_id = pg_temp.oid('charlie_mkt'))
            = ('executed'::varchar, 2::bigint, 3600.000000::numeric),
        (SELECT format('%s, %s trades, avg %s', status, trades, avg_fill_price) FROM v_order_history WHERE order_id = pg_temp.oid('charlie_mkt')));
    SELECT pg_temp.expect_true('market order: alice''s ask is now executed, nothing left reserved',
        (SELECT status FROM orders WHERE id = pg_temp.oid('alice_ask')) = 'executed'
        AND (SELECT reserved_quantity FROM holdings WHERE user_id = pg_temp.uid('alice')) = 0
        AND (SELECT reserved_balance FROM users WHERE username = 'charlie') = 0);
    SELECT pg_temp.expect_true('consistency holds after the market order', pg_temp.consistent());

    -- ===========================================================================
    -- D. Trades are only possible between valid, compatible orders
    -- ===========================================================================
    INSERT INTO ids VALUES ('charlie_ask',
        place_order(pg_temp.uid('charlie'), 'a2222222-2222-2222-2222-222222222222', 'sell', 'limit', 0.1, 3700));
    INSERT INTO ids VALUES ('bob_ask',
        place_order(pg_temp.uid('bob'), 'a2222222-2222-2222-2222-222222222222', 'sell', 'limit', 0.1, 3800));

    CALL pg_temp.expect_error('trade: price above the buyer''s limit',
        $q$INSERT INTO market_trades (market_id, executed_at, price, quantity, buy_order_id, sell_order_id)
        VALUES ('a2222222-2222-2222-2222-222222222222', now(), 3700, 0.1, pg_temp.oid('bob_bid'), pg_temp.oid('charlie_ask'))$q$,
        'outside the limit');
    CALL pg_temp.expect_error('trade: more than the order has remaining',
        $q$INSERT INTO market_trades (market_id, executed_at, price, quantity, buy_order_id)
        VALUES ('a2222222-2222-2222-2222-222222222222', now(), 3500, 0.5, pg_temp.oid('bob_bid'))$q$,
        'exceeds the remaining quantity');
    CALL pg_temp.expect_error('trade: a sell order used as the buy side',
        $q$INSERT INTO market_trades (market_id, executed_at, price, quantity, buy_order_id)
        VALUES ('a2222222-2222-2222-2222-222222222222', now(), 3700, 0.1, pg_temp.oid('charlie_ask'))$q$,
        'cannot be the buy side');
    CALL pg_temp.expect_error('trade: order of another market',
        $q$INSERT INTO market_trades (market_id, executed_at, price, quantity, buy_order_id)
        VALUES ('a1111111-1111-1111-1111-111111111111', now(), 3500, 0.1, pg_temp.oid('bob_bid'))$q$,
        'different market');
    CALL pg_temp.expect_error('trade: executed order cannot trade again',
        $q$INSERT INTO market_trades (market_id, executed_at, price, quantity, sell_order_id)
        VALUES ('a2222222-2222-2222-2222-222222222222', now(), 3600, 0.1, pg_temp.oid('alice_ask'))$q$,
        'is executed and cannot trade');
    CALL pg_temp.expect_error('trade: a user with their own order',
        $q$INSERT INTO market_trades (market_id, executed_at, price, quantity, buy_order_id, sell_order_id)
        VALUES ('a2222222-2222-2222-2222-222222222222', now(), 3500, 0.1, pg_temp.oid('bob_bid'), pg_temp.oid('bob_ask'))$q$,
        'own order');
    CALL pg_temp.expect_error('trade: a trade that filled orders cannot be deleted',
        $q$DELETE FROM market_trades WHERE buy_order_id = pg_temp.oid('bob_bid2')$q$,
        'cannot be changed or deleted');

    -- ===========================================================================
    -- E. Order state changes
    -- ===========================================================================
    CALL pg_temp.expect_error('state: status cannot be set to executed by hand',
        $q$UPDATE orders SET status = 'executed' WHERE id = pg_temp.oid('bob_bid')$q$,
        'status is derived automatically');
    CALL pg_temp.expect_error('state: filled quantity cannot be changed by hand',
        $q$UPDATE orders SET filled_quantity = 0.05 WHERE id = pg_temp.oid('bob_bid')$q$,
        'can only change through a trade');
    CALL pg_temp.expect_error('state: executed order cannot be processed again',
        $q$UPDATE orders SET status = 'cancelled' WHERE id = pg_temp.oid('alice_ask')$q$,
        'already executed');
    CALL pg_temp.expect_error('state: ordered quantity cannot change',
        $q$UPDATE orders SET quantity = 1 WHERE id = pg_temp.oid('bob_bid')$q$,
        'cannot change');
    CALL pg_temp.expect_error('state: cancelling by hand without releasing the reservation',
        $q$UPDATE orders SET status = 'cancelled' WHERE id = pg_temp.oid('bob_bid')$q$,
        'reserved balance');

    -- ===========================================================================
    -- F. Cancelling releases the reservation, exactly once
    -- ===========================================================================
    CALL pg_temp.expect_error('cancel: someone else''s order',
        $q$SELECT cancel_order(pg_temp.oid('bob_bid'), pg_temp.uid('charlie'))$q$,
        'does not belong');
    SELECT cancel_order(pg_temp.oid('bob_bid'), pg_temp.uid('bob'));
    SELECT pg_temp.expect_true('cancel: 350 back from reserved to available, event recorded',
        (SELECT (available_balance, reserved_balance) FROM users WHERE username = 'bob') = (4280.0000, 0.0000)
        AND EXISTS (SELECT 1 FROM order_events WHERE order_id = pg_temp.oid('bob_bid') AND event_type = 'cancelled'),
        (SELECT format('bob available %s reserved %s', available_balance, reserved_balance) FROM users WHERE username = 'bob'));
    CALL pg_temp.expect_error('cancel: a cancelled order cannot be cancelled again',
        $q$SELECT cancel_order(pg_temp.oid('bob_bid'), pg_temp.uid('bob'))$q$,
        'cannot be cancelled');
    CALL pg_temp.expect_error('trade: cancelled order cannot trade',
        $q$INSERT INTO market_trades (market_id, executed_at, price, quantity, buy_order_id)
        VALUES ('a2222222-2222-2222-2222-222222222222', now(), 3500, 0.1, pg_temp.oid('bob_bid'))$q$,
        'is cancelled and cannot trade');

    -- ===========================================================================
    -- G. Balances cannot be put in an inconsistent state
    -- ===========================================================================
    CALL pg_temp.expect_error('balance: reserving cash with no order behind it',
        $q$UPDATE users SET available_balance = available_balance - 100, reserved_balance = reserved_balance + 100
            WHERE username = 'charlie'$q$,
        'reserved balance');
    CALL pg_temp.expect_error('balance: reserving crypto with no order behind it',
        $q$UPDATE holdings SET reserved_quantity = reserved_quantity + 0.01 WHERE user_id = pg_temp.uid('alice')$q$,
        'reserved quantity');
    CALL pg_temp.expect_error('balance: cash changed without a ledger row',
        $q$UPDATE users SET available_balance = available_balance + 100 WHERE username = 'charlie'$q$,
        'does not match the ledger');

    -- ===========================================================================
    -- H. Background job: resting limit orders filled when the market reaches them
    -- ===========================================================================
    INSERT INTO ids VALUES ('bob_bid3',
        place_order(pg_temp.uid('bob'), 'a2222222-2222-2222-2222-222222222222', 'buy', 'limit', 0.1, 3500));
    -- the simulator moves the price down to 3450 (a bot tick, no orders)
    INSERT INTO market_trades (market_id, executed_at, price, quantity, side)
    VALUES ('a2222222-2222-2222-2222-222222222222', now(), 3450, 0.01, 'sell');

    CREATE TEMP TABLE job_run ON COMMIT DROP AS SELECT fill_marketable_orders() AS filled;

    SELECT pg_temp.expect_true('job: fills exactly the orders the new price reached',
        (SELECT filled FROM job_run) = 1
        AND (SELECT (status, avg_fill_price) FROM v_order_history WHERE order_id = pg_temp.oid('bob_bid3'))
            = ('executed'::varchar, 3450.000000::numeric)
        AND (SELECT status FROM orders WHERE id = pg_temp.oid('charlie_ask')) = 'open',
        (SELECT format('bob_bid3 %s @ %s, charlie_ask (3700) still %s', h.status, h.avg_fill_price, o.status)
        FROM v_order_history h, orders o WHERE h.order_id = pg_temp.oid('bob_bid3') AND o.id = pg_temp.oid('charlie_ask')));
    SELECT pg_temp.expect_true('job: buyer paid 345, got the 5 above the fill price back',
        (SELECT reserved_balance FROM users WHERE username = 'bob') = 0
        AND (SELECT count(*) FROM transactions WHERE related_order = pg_temp.oid('bob_bid3') AND amount = -345) = 1);
    SELECT pg_temp.expect_true('job: nothing more to do on a second run', fill_marketable_orders() = 0);
    SELECT pg_temp.expect_true('consistency holds after the job', pg_temp.consistent());

    -- ===========================================================================
    -- Final state of every trader
    -- ===========================================================================
    SELECT pg_temp.expect_true('views: every trader''s cash equals their ledger',
        NOT EXISTS (SELECT 1 FROM v_trader_balances WHERE total_cash <> ledger_total));

    SELECT username, available_balance, reserved_balance, ledger_total, holdings_value
    FROM v_trader_balances ORDER BY username;

    SELECT count(*) FILTER (WHERE passed)     AS passed,
        count(*) FILTER (WHERE NOT passed) AS failed
    FROM test_results;

    ROLLBACK;
    }}}

    == Changes to earlier phases ==

    * '''Schema''' ([wiki:RelationalDesign], [wiki:ERModel]):
    * `users.reserved_balance`;
    * `orders.filled_quantity` and the status value `partially_filled`;
    * `market_trades.buy_order_id` / `sell_order_id` (optional references to `orders` — a new relationship "trade fills order");
    * the new table `order_events`.
    * '''Sample data''' (`data_load.sql`):
    * deposit rows for bob and charlie, whose balances previously had no ledger entries behind them;
    * alice's seeded order is imported as completely filled;
    * the script runs as one transaction.

    The balances documented in [wiki:BuildInstructions] are
    unchanged.
    * '''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.
    * '''Prototype:'''
    * placing an order is one call to `place_order`, and market or limit can be chosen;
    * new menu items `[12] Order book`, `[13] My open orders`, `[14] Cancel an order`;
    * the balance screen shows reserved cash;
    * the bot runs the background job.

    == AI usage ==

    AI was used in this phase and is logged in full, per the course rule for P1 onward.

    * '''Phase log:''' [wiki:AdvancedDatabaseDevelopmentAIUsage]

    '''In short:''' the requirements — order state consistency, filled/remaining quantities, reserved
    money and assets, trades only between compatible orders, the kinds of triggers, procedures and
    views, and a background job only if relevant — were mine. I asked the AI to turn them into
    concrete rules for the existing !EduBerza database, implement them without redesigning it, test
    them and document them.