gistfile1.txt

CREATE SCHEMA core;

CREATE DOMAIN currency_id AS TEXT
    CHECK (LENGTH(VALUE) > 0);

CREATE TABLE currency (
    currency currency_id PRIMARY KEY
);

CREATE DOMAIN amount AS NUMERIC(20, 8)
    CHECK (VALUE >= 0);

CREATE DOMAIN account_type AS TEXT
	CHECK (VALUE IN ('user'));
CREATE TABLE user (
    user_id SERIAL PRIMARY KEY
);

-- Create accounts with user
CREATE FUNCTION user_create_accounts_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO account (user_id, currency, type)
    SELECT NEW.user_id, currency, 'user'
    FROM currency;

    RETURN NEW;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER create_accounts
AFTER INSERT ON user
FOR EACH ROW
EXECUTE PROCEDURE user_create_accounts_trigger();
-- Account
CREATE TABLE account (
    account_id SERIAL PRIMARY KEY,
    currency currency_id NOT NULL,
    user_id INT REFERENCES user(user_id),
    balance amount NOT NULL DEFAULT(0) CHECK(balance >= 0),
    held amount NOT NULL DEFAULT(0) CHECK (held >= 0),
    available amount NOT NULL DEFAULT(0) CHECK (available >= 0),
    type account_type NOT NULL,
    CHECK (held + available = balance)
);

-- Keep available in sync
CREATE FUNCTION account_before_update_trigger()
RETURNS TRIGGER AS $$
BEGIN
    NEW.available := NEW.balance - NEW.held;
    RETURN NEW;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER before_update_trigger
BEFORE UPDATE OF balance, held ON account
FOR EACH ROW
EXECUTE PROCEDURE account_before_update_trigger();
-- Hold
CREATE TABLE hold (
    hold_id SERIAL PRIMARY KEY,
    account_id INT NOT NULL REFERENCES account(account_id),
    amount amount NOT NULL CHECK (amount > 0),
    created_at TIMESTAMPTZ NOT NULL DEFAULT(now())
);
-- Transfer
CREATE TABLE transfer (
    transfer_id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL DEFAULT(now()),
    debit_account_id INT NOT NULL REFERENCES account(account_id),
    credit_account_id INT NOT NULL REFERENCES account(account_id),
    amount amount NOT NULL CHECK (amount > 0),
    CHECK (debit_account_id <> credit_account_id)
);

-- Validate account currencies
CREATE FUNCTION transfer_validate_account_currencies()
RETURNS TRIGGER AS $$
DECLARE
    debit_currency currency_id;
    credit_currency currency_id;
BEGIN
    SELECT currency
    INTO debit_currency
    FROM account
    WHERE account_id = NEW.debit_account_id;

    SELECT currency
    INTO credit_currency
    FROM account
    WHERE account_id = NEW.credit_account_id;

    IF debit_currency <> credit_currency THEN
        RAISE 'Currency mismatch (debit %, credit %)', debit_currency, credit_currency;
    END IF;

    RETURN NEW;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER validate_account_currencies
BEFORE INSERT ON transfer
FOR EACH ROW
EXECUTE PROCEDURE transfer_validate_account_currencies();

-- Update account balances
CREATE FUNCTION transfer_update_balances_trigger()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE account
    SET balance = balance - NEW.amount
    WHERE account_id = NEW.debit_account_id;

    UPDATE account
    SET balance = balance + NEW.amount
    WHERE account_id = NEW.credit_account_id;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER update_balances
AFTER INSERT ON transfer
FOR EACH ROW
EXECUTE PROCEDURE transfer_update_balances_trigger();
CREATE SCHEMA exchange;

CREATE DOMAIN exchange.order_amount AS NUMERIC(20, 5)
    CHECK (VALUE >= 0);

CREATE DOMAIN exchange.order_price AS NUMERIC(20, 3)
    CHECK (VALUE > 0);

CREATE DOMAIN exchange.order_side AS TEXT
    CHECK (VALUE IN ('bid', 'ask'));
CREATE TABLE exchange.market (
	market_id TEXT PRIMARY KEY,
	base currency_id NOT NULL,
	quote currency_id NOT NULL
);

CREATE FUNCTION exchange.market_set_id()
RETURNS TRIGGER AS $$
BEGIN
	NEW.market_id := NEW.base || '/' || NEW.quote;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER set_id
BEFORE INSERT ON exchange.market
FOR EACH ROW
EXECUTE PROCEDURE exchange.market_set_id();
CREATE TABLE exchange.order (
    order_id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL DEFAULT(now()),
    source_account_id INT NOT NULL REFERENCES account(account_id),
    dest_account_id INT NOT NULL REFERENCES account(account_id),
    base currency_id NOT NULL,
    quote currency_id NOT NULL,
    amount exchange.order_amount NOT NULL,
    side exchange.order_side NOT NULL,
    hold_id INT NOT NULL REFERENCES hold(hold_id),
    price exchange.order_price NOT NULL,
    amount_remaining exchange.order_amount NOT NULL,
    amount_matched exchange.order_amount NOT NULL DEFAULT(0),
    amount_canceled exchange.order_amount NOT NULL DEFAULT(0),
    canceled_at TIMESTAMPTZ,
    CHECK (amount = amount_remaining + amount_matched + amount_canceled),
    CHECK (canceled_at IS NULL AND amount_canceled = 0 OR canceled_at IS NOT NULL AND amount_canceled > 0 AND amount_remaining = 0)
);

-- Cancel order (by id)
CREATE FUNCTION cancel_order (_order_id INT)
RETURNS BOOLEAN AS $$
DECLARE
    _order exchange.order%ROWTYPE;
BEGIN
    SELECT *
    INTO _order
    FROM exchange.order
    WHERE order_id = _order_id
    FOR SHARE;

    IF NOT FOUND OR _order.amount_remaining = 0 THEN
        RETURN FALSE;
    END IF;

    UPDATE "order"
    SET
        hold_id = NULL,
        aomunt_canceled = _order.amount_remaining,
        amount_remaining = 0
    WHERE order_id = _order_id;

    RETURN TRUE;
END; $$ LANGUAGE PLPGSQL VOLATILE;


-- Create hold on insert
CREATE FUNCTION exchange.order_insert_create_hold_trigger()
RETURNS TRIGGER AS $$
BEGIN
    -- TODO: Confirm currency
    NEW.amount_remaining := NEW.amount;

    INSERT INTO hold (account_id, amount)
    VALUES (NEW.source_account_id, CASE WHEN _side = 'bid' THEN NEW.amount * NEW.price ELSE NEW.amount END)
    RETURNING hold_id
    INTO NEW.hold_id;

    RETURN NEW;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER create_hold
BEFORE INSERT ON exchange.order
FOR EACH ROW
EXECUTE PROCEDURE exchange.order_insert_create_hold_trigger();

-- Update hold on update
CREATE FUNCTION exchange.order_update_hold()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.amount = 0 THEN
        NEW.hold_id := NULL;
    ELSIF
        UPDATE hold
        SET amount = (CASE WHEN _side = 'bid' THEN NEW.amount * NEW.price ELSE NEW.amount END)
        WHERE hold_id = NEW.hold_id;
    END IF;

    RETURN NEW;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER update_hold
BEFORE UPDATE OF amount ON exchange.order
FOR EACH ROW
EXECUTE PROCEDURE exchange.order_update_hold();

-- Delete hold when amount
CREATE FUNCTION exchange.order_delete_hold()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM hold
    WHERE hold_id = OLD.hold_id;

    RETURN NULL;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER update_hold
BEFORE UPDATE OF amount ON exchange.order
FOR EACH ROW
WHEN (OLD.hold_id IS NOT NULL AND NEW.hold_id IS NULL)
EXECUTE PROCEDURE exchange.order_delete_hold();
-- Order match
CREATE TABLE exchange.match (
	match_id SERIAL PRIMARY KEY,
	bid_order_id INT NOT NULL REFERENCES exchange.order(order_id),
	ask_order_id INT NOT NULL REFERENCES exchange.order(order_id),
	created_at TIMESTAMPTZ NOT NULL DEFAULT(now()),
	amount exchange.order_amount NOT NULL CHECK (amount > 0),
	price exchange.order_price NOT NULL,
	-- TODO: Market id?
);

CREATE FUNCTION exchange.match_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE
	_bid exchange.order%ROWTYPE;
	_ask exchange.order%ROWTYPE;
BEGIN
	SELECT *
	INTO _bid
	FROM exchange.order
	WHERE order_id = NEW.bid_order_id
	FOR SHARE;

	SELECT *
	INTO _ask
	FROM exchange.order
	WHERE order_id = NEW.ask_order_id
	FOR SHARE;

	NEW.price := CASE WHEN NEW.bid_order_id < NEW.ask_order_id THEN _bid.price ELSE _ask.price END;
	NEW.amount := MIN(_bid.amount, _ask.amount);

	-- Reduce amounts (causes hold reductions)
	UPDATE "order"
	SET
		amount = amount - NEW.amount,
		matched = matched + NEW.amount
	WHERE order_id IN (NEW.bid_order_id, NEW.ask_order_id);

	-- Perform swaps
	INSERT INTO transfer (debit_account_id, credit_account_id, amount)
	VALUES
		(_bid.source_account_id, _ask.dest_account_id, NEW.amount * NEW.price),
		(_ask.source_account_id, _bid.dest_account_id, NEW.amount);

END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER insert_trigger
BEFORE INSERT ON exchange.match
FOR EACH ROW
EXECUTE PROCEDURE exchange.match_insert_trigger();