Changeset 9577c79 for server


Ignore:
Timestamp:
09/16/26 23:37:15 (2 weeks ago)
Author:
Stefan <trsunovstefan@…>
Branches:
main
Children:
8b447ef
Parents:
df05838
Message:

add reserved_quantity and modify the phases, add v_03.png and v_03.xml for P1

Location:
server
Files:
4 edited

Legend:

Unmodified
Added
Removed

  • server/cli.go

    rdf05838 r9577c79  
    7676        fmt.Println("[8] Manage watchlist")
    7777        fmt.Println("[9] Logout")
     78        fmt.Println("[10] Report: top traders")
     79        fmt.Println("[11] Report: market performance")
    7880        fmt.Println("[0] Exit")
    7981        switch prompt("> ") {
    … …  
    9496        case "8":
    9597                ManageWatchlist(s)
     98        case "10":
     99                ShowTopTraders(s)
     100        case "11":
     101                ShowMarketPerformance(s)
    96102        case "9":
    97103                s.UserID = ""

  • server/db/schema_creation.sql

    rdf05838 r9577c79  
    5959-- ============================================================================
    6060CREATE TABLE project.holdings (
    61     id         uuid           PRIMARY KEY DEFAULT gen_random_uuid(),
    62     user_id    uuid           NOT NULL REFERENCES project.users(id)  ON DELETE CASCADE,
    63     crypto_id  uuid           NOT NULL REFERENCES project.crypto(id),
    64     quantity   numeric(20,4)  NOT NULL CHECK (quantity >= 0),
     61    id                uuid           PRIMARY KEY DEFAULT gen_random_uuid(),
     62    user_id           uuid           NOT NULL REFERENCES project.users(id)  ON DELETE CASCADE,
     63    crypto_id         uuid           NOT NULL REFERENCES project.crypto(id),
     64    quantity          numeric(20,4)  NOT NULL CHECK (quantity >= 0),
     65    -- Committed to the user's own open sell orders, not yet removed from the
     66    -- position. quantity - reserved_quantity is what is actually free to
     67    -- sell — the crypto-side equivalent of users.available_balance.
     68    reserved_quantity numeric(20,4)  NOT NULL DEFAULT 0
     69                                      CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity),
    6570    -- Weighted-average entry price. NOT NULL so that the P/L arithmetic in
    6671    -- v_portfolio can never silently produce NULL for an existing position.
    67     avg_price  numeric(18,6)  NOT NULL DEFAULT 0 CHECK (avg_price >= 0),
    68     created_at timestamptz    NOT NULL DEFAULT now(),
    69     updated_at timestamptz,
     72    avg_price         numeric(18,6)  NOT NULL DEFAULT 0 CHECK (avg_price >= 0),
     73    created_at        timestamptz    NOT NULL DEFAULT now(),
     74    updated_at        timestamptz,
    7075    CONSTRAINT uq_holdings_user_crypto UNIQUE (user_id, crypto_id)
    7176);
    … …  
    184189       c.symbol,
    185190       h.quantity,
     191       h.reserved_quantity,
     192       (h.quantity - h.reserved_quantity) AS available_quantity,
    186193       h.avg_price,
    187194       lp.price                           AS current_price,
    … …  
    192199LEFT   JOIN project.markets m ON m.crypto_id = c.id AND m.quote_currency = 'USD'
    193200LEFT   JOIN project.v_latest_prices lp ON lp.market_id = m.id;
     201
     202-- ============================================================================
     203-- REPORTS (P6 — Complex DB Reports)
     204-- Both are single SELECT statements (with CTEs), wrapped as SQL functions so
     205-- they can be called as parameterised reports from the prototype instead of
     206-- being copy-pasted SQL text. See docs/P6-AdvancedReports/AdvancedReports.md.
     207-- ============================================================================
     208
     209-- report_top_traders: realized trading performance per user over [p_from, p_to),
     210-- bucketed into quarters to measure how consistently each user was profitable.
     211CREATE OR REPLACE FUNCTION project.report_top_traders(p_from timestamptz, p_to timestamptz)
     212RETURNS TABLE (
     213    username            varchar,
     214    realized_pl         numeric,
     215    total_invested      numeric,
     216    roi_pct             numeric,
     217    profitable_periods  bigint,
     218    losing_periods      bigint,
     219    total_periods       bigint,
     220    consistency_pct     numeric
     221)
     222LANGUAGE sql STABLE AS $$
     223    WITH period_pl AS (
     224        SELECT
     225            t.user_id,
     226            date_trunc('quarter', t.created_at)          AS period,
     227            SUM(t.amount)                                AS period_pl,
     228            SUM(t.amount) FILTER (WHERE t.type = 'buy')  AS period_buy
     229        FROM project.transactions t
     230        WHERE t.type IN ('buy', 'sell', 'fee')
     231          AND t.created_at >= p_from
     232          AND t.created_at <  p_to
     233        GROUP BY t.user_id, date_trunc('quarter', t.created_at)
     234    )
     235    SELECT
     236        u.username,
     237        SUM(pp.period_pl)                                                       AS realized_pl,
     238        ABS(SUM(pp.period_buy))                                                 AS total_invested,
     239        ROUND(SUM(pp.period_pl) / NULLIF(ABS(SUM(pp.period_buy)), 0) * 100, 2)  AS roi_pct,
     240        COUNT(*) FILTER (WHERE pp.period_pl > 0)                                AS profitable_periods,
     241        COUNT(*) FILTER (WHERE pp.period_pl < 0)                                AS losing_periods,
     242        COUNT(*)                                                                AS total_periods,
     243        ROUND(COUNT(*) FILTER (WHERE pp.period_pl > 0)::numeric
     244              / NULLIF(COUNT(*), 0) * 100, 2)                                   AS consistency_pct
     245    FROM period_pl pp
     246    JOIN project.users u ON u.id = pp.user_id
     247    GROUP BY u.id, u.username
     248    ORDER BY realized_pl DESC;
     249$$;
     250
     251-- report_market_performance: trading activity and price behaviour per market
     252-- over [p_from, p_to). Volume/trade-count/price stats come from market_trades
     253-- (the complete tape — user fills and simulated fills alike); participating
     254-- users can only come from orders, since market_trades has no user_id column.
     255CREATE OR REPLACE FUNCTION project.report_market_performance(p_from timestamptz, p_to timestamptz)
     256RETURNS TABLE (
     257    symbol               varchar,
     258    quote_currency       char(3),
     259    total_volume         numeric,
     260    trade_count          bigint,
     261    avg_price            numeric,
     262    market_return_pct    numeric,
     263    price_volatility     numeric,
     264    participating_users  bigint
     265)
     266LANGUAGE sql STABLE AS $$
     267    WITH trades AS (
     268        SELECT
     269            market_id, price, quantity, executed_at,
     270            FIRST_VALUE(price) OVER w AS first_price,
     271            LAST_VALUE(price)  OVER (PARTITION BY market_id ORDER BY executed_at
     272                                      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_price
     273        FROM project.market_trades
     274        WHERE executed_at >= p_from AND executed_at < p_to
     275        WINDOW w AS (PARTITION BY market_id ORDER BY executed_at)
     276    ),
     277    market_stats AS (
     278        SELECT
     279            market_id,
     280            SUM(quantity)    AS total_volume,
     281            COUNT(*)         AS trade_count,
     282            AVG(price)       AS avg_price,
     283            STDDEV(price)    AS price_volatility,
     284            MAX(first_price) AS first_price,
     285            MAX(last_price)  AS last_price
     286        FROM trades
     287        GROUP BY market_id
     288    ),
     289    participation AS (
     290        SELECT market_id, COUNT(DISTINCT user_id) AS participating_users
     291        FROM project.orders
     292        WHERE status = 'executed' AND executed_at >= p_from AND executed_at < p_to
     293        GROUP BY market_id
     294    )
     295    SELECT
     296        c.symbol,
     297        m.quote_currency,
     298        ms.total_volume,
     299        ms.trade_count,
     300        ROUND(ms.avg_price, 6)                                                            AS avg_price,
     301        ROUND((ms.last_price - ms.first_price) / NULLIF(ms.first_price, 0) * 100, 2)      AS market_return_pct,
     302        ROUND(COALESCE(ms.price_volatility, 0), 6)                                        AS price_volatility,
     303        COALESCE(p.participating_users, 0)                                                AS participating_users
     304    FROM market_stats ms
     305    JOIN project.markets m ON m.id = ms.market_id
     306    JOIN project.crypto  c ON c.id = m.crypto_id
     307    LEFT JOIN participation p ON p.market_id = ms.market_id
     308    ORDER BY ms.total_volume DESC;
     309$$;

  • server/portfolio.go

    rdf05838 r9577c79  
    33import (
    44        "fmt"
     5        "strings"
    56
    67        "bp_project/server/db"
    … …  
    1314                `SELECT symbol,
    1415                        quantity,
     16                        COALESCE(reserved_quantity, 0),
     17                        COALESCE(available_quantity, quantity),
    1518                        COALESCE(avg_price, 0),
    1619                        COALESCE(current_price, 0),
    … …  
    2831        defer rows.Close()
    2932
     33        header := fmt.Sprintf("  %-8s  %12s  %12s  %12s  %14s  %14s  %14s  %14s",
     34                "Symbol", "Quantity", "Reserved", "Available", "Avg buy", "Current", "Value", "Unrealised P/L")
    3035        fmt.Println()
    31         fmt.Printf("  %-8s  %12s  %14s  %14s  %14s  %14s\n",
    32                 "Symbol", "Quantity", "Avg buy", "Current", "Value", "Unrealised P/L")
    33         fmt.Println("  ------------------------------------------------------------------------------------")
     36        fmt.Println(header)
     37        fmt.Println("  " + strings.Repeat("-", len(header)-2))
    3438
    3539        var totalValue, totalPnL float64
    … …  
    3741        for rows.Next() {
    3842                var sym string
    39                 var qty, avg, cur, val, pnl float64
    40                 if err := rows.Scan(&sym, &qty, &avg, &cur, &val, &pnl); err != nil {
     43                var qty, reserved, avail, avg, cur, val, pnl float64
     44                if err := rows.Scan(&sym, &qty, &reserved, &avail, &avg, &cur, &val, &pnl); err != nil {
    4145                        fmt.Println("scan error:", err)
    4246                        return
    4347                }
    44                 fmt.Printf("  %-8s  %12.4f  %14.6f  %14.6f  %14.4f  %+14.4f\n",
    45                         sym, qty, avg, cur, val, pnl)
     48                fmt.Printf("  %-8s  %12.4f  %12.4f  %12.4f  %14.6f  %14.6f  %14.4f  %+14.4f\n",
     49                        sym, qty, reserved, avail, avg, cur, val, pnl)
    4650                totalValue += val
    4751                totalPnL += pnl
    … …  
    5256                return
    5357        }
    54         fmt.Println("  ------------------------------------------------------------------------------------")
    55         fmt.Printf("  %-8s  %12s  %14s  %14s  %14.4f  %+14.4f\n",
    56                 "TOTAL", "", "", "", totalValue, totalPnL)
     58        fmt.Println("  " + strings.Repeat("-", len(header)-2))
     59        fmt.Printf("  %-8s  %12s  %12s  %12s  %14s  %14s  %14.4f  %+14.4f\n",
     60                "TOTAL", "", "", "", "", "", totalValue, totalPnL)
    5761
    5862        // cash summary

  • server/trade.go

    rdf05838 r9577c79  
    1313// Runs inside a single database transaction so the orders, holdings,
    1414// users.balance and transactions tables always agree.
     15//
     16// The order still passes through 'open' before 'executed'. Placing it
     17// reserves whatever it commits — on a sell, the crypto being sold, tracked in
     18// holdings.reserved_quantity — before anything is actually moved, so a
     19// second order against the same holding can never be granted the same units
     20// twice. Because only market orders are implemented, reserve and settle
     21// happen inside this one transaction rather than across two commits; a
     22// future limit-order matcher would split them into a second transaction
     23// later, without needing a schema change.
    1524func PlaceOrder(s *Session, side string) {
    1625        if side != "buy" && side != "sell" {
    … …  
    4756        defer tx.Rollback()
    4857
    49         // 1. create the order (status='executed' since we fill immediately)
     58        // 1. record the order as 'open' — no trade has happened yet.
    5059        var orderID string
    5160        err = tx.QueryRow(
    52                 `INSERT INTO orders (user_id, market_id, side, type, status, quantity, price, executed_at)
    53                  VALUES ($1, $2, $3, 'market', 'executed', $4, $5, now())
     61                `INSERT INTO orders (user_id, market_id, side, type, status, quantity, price)
     62                 VALUES ($1, $2, $3, 'market', 'open', $4, $5)
    5463                 RETURNING id`,
    5564                s.UserID, m.ID, side, qty, price,
    … …  
    8796                }
    8897
    89                 // upsert holding with running weighted average
     98                // a buy never reserves crypto, only ever adds it — upsert holding
     99                // with running weighted average
    90100                if err := upsertHoldingOnBuy(tx, s.UserID, m.CryptoID, qty, price); err != nil {
    91101                        fmt.Println("Error updating holding:", err)
    … …  
    104114                }
    105115        } else {
    106                 // sell: check holding
    107                 var held, avgPrice float64
     116                // sell: lock the holding and check what is actually free to sell —
     117                // quantity minus whatever another open order has already reserved.
     118                var held, reserved, avgPrice float64
    108119                err := tx.QueryRow(
    109                         `SELECT quantity, avg_price FROM holdings
     120                        `SELECT quantity, reserved_quantity, avg_price FROM holdings
    110121                          WHERE user_id = $1 AND crypto_id = $2 FOR UPDATE`,
    111122                        s.UserID, m.CryptoID,
    112                 ).Scan(&held, &avgPrice)
     123                ).Scan(&held, &reserved, &avgPrice)
    113124                if err != nil && err != sql.ErrNoRows {
    114125                        fmt.Println("Error:", err)
    115126                        return
    116127                }
    117                 if err == sql.ErrNoRows || held < qty {
    118                         fmt.Printf("Insufficient holding: trying to sell %.4f, hold %.4f\n", qty, held)
    119                         return
    120                 }
    121 
    122                 // reduce holding
     128                available := held - reserved
     129                if err == sql.ErrNoRows || available < qty {
     130                        fmt.Printf("Insufficient holding: trying to sell %.4f, available %.4f (of %.4f held, %.4f reserved)\n",
     131                                qty, available, held, reserved)
     132                        return
     133                }
     134
     135                // reserve: committed to this order, not yet removed from the position.
    123136                if _, err := tx.Exec(
    124137                        `UPDATE holdings
    125                             SET quantity   = quantity - $1,
    126                                 updated_at = now()
     138                            SET reserved_quantity = reserved_quantity + $1,
     139                                updated_at        = now()
     140                          WHERE user_id = $2 AND crypto_id = $3`,
     141                        qty, s.UserID, m.CryptoID,
     142                ); err != nil {
     143                        fmt.Println("Error:", err)
     144                        return
     145                }
     146
     147                // settle: a market order fills immediately, so release the
     148                // reservation and remove the asset from the position in one step.
     149                if _, err := tx.Exec(
     150                        `UPDATE holdings
     151                            SET quantity          = quantity - $1,
     152                                reserved_quantity = reserved_quantity - $1,
     153                                updated_at        = now()
    127154                          WHERE user_id = $2 AND crypto_id = $3`,
    128155                        qty, s.UserID, m.CryptoID,
    … …  
    163190                 VALUES ($1, now(), $2, $3, $4, 'user')`,
    164191                m.ID, price, qty, side,
     192        ); err != nil {
     193                fmt.Println("Error:", err)
     194                return
     195        }
     196
     197        // settle the order itself: it has now actually been filled.
     198        if _, err := tx.Exec(
     199                `UPDATE orders SET status = 'executed', executed_at = now() WHERE id = $1`,
     200                orderID,
    165201        ); err != nil {
    166202                fmt.Println("Error:", err)
Note: See TracChangeset for help on using the changeset viewer.