Context Navigation

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

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

check the market, side, type, quantity and price;
reserve what the order commits — cash (quantity × price) for a buy, crypto for a sell — and refuse the order if not enough is free;
record the order;
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;
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:

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.
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.
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.
Invalid trades, state changes and balance changes are attempted directly in SQL.
bob cancels his bid.
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 (RelationalDesign, 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 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 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: 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.

Last modified 2 days ago Last modified on 09/24/26 13:59:33

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text

#	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`, …

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`

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`