Changeset ef1c1c7 for server/db

Timestamp:

09/24/26 17:43:19 (5 days ago)

Author:

Stefan <trsunovstefan@…>

Branches:

main

Children:

0cee8ec

Parents:

a531b45

Message:

Wiki docs, phase 6 and phase 7 added

Location:

server/db

Files:

• advanced_db.sql (added)
• advanced_db_tests.sql (added)
• data_load.sql (modified) (4 diffs)
• db.go (modified) (4 diffs)
• reports_demo_data.sql (modified) (2 diffs)
• schema_creation.sql (modified) (3 diffs)
• ssh.go (added)

server/db/data_load.sql

@@ -8 +8 @@
 --
 -- All sample users have the password: test123
+--
+-- One transaction: the P7 checks in advanced_db.sql compare balances with
+-- the ledger at COMMIT, and the users are inserted with their balances
+-- before the deposit rows that back them. In an auto-commit client
+-- (DBeaver) every statement would otherwise be checked on its own.
+
+BEGIN;
 
 SET search_path TO project, public;
@@ -104 +111 @@
 -- Shows a fully-filled market buy and its resulting holding & ledger entry.
 -- ============================================================================
-INSERT INTO project.orders (id, user_id, market_id, side, type, status, quantity, price, placed_at, executed_at) VALUES
+-- Imported as already completely filled (filled_quantity = quantity).
+INSERT INTO project.orders (id, user_id, market_id, side, type, status, quantity, filled_quantity, price, placed_at, executed_at) VALUES
     ('c1111111-1111-1111-1111-111111111111',
      'b1111111-1111-1111-1111-111111111111',
      'a2222222-2222-2222-2222-222222222222',
-     'buy', 'market', 'executed', 0.5000, 3500.000000,
+     'buy', 'market', 'executed', 0.5000, 0.5000, 3500.000000,
      now() - interval '1 hour', now() - interval '1 hour');
 
@@ -118 +126 @@
 INSERT INTO project.transactions (user_id, type, amount, currency, related_order, description) VALUES
     ('b1111111-1111-1111-1111-111111111111', 'deposit',  10000.0000, 'USD', NULL,
+        'Initial virtual deposit'),
+    ('b2222222-2222-2222-2222-222222222222', 'deposit',   5000.0000, 'USD', NULL,
+        'Initial virtual deposit'),
+    ('b3333333-3333-3333-3333-333333333333', 'deposit',   2500.0000, 'USD', NULL,
         'Initial virtual deposit'),
     ('b1111111-1111-1111-1111-111111111111', 'buy',      -1750.0000, 'USD',
@@ -143 +155 @@
     ('d2222222-2222-2222-2222-222222222222', '11111111-1111-1111-1111-111111111111'),
     ('d2222222-2222-2222-2222-222222222222', '55555555-5555-5555-5555-555555555555');
+
+COMMIT;

server/db/db.go

@@ -11 +11 @@
         "strings"
 
-        _ "github.com/lib/pq"
+        "github.com/lib/pq"
 )
 
@@ -19 +19 @@
 // which directory the program is started from.
 //
-//go:embed schema_creation.sql data_load.sql
+//go:embed schema_creation.sql advanced_db.sql data_load.sql
 var sqlScripts embed.FS
 
@@ -36 +36 @@
         )
 
-        var err error
-        DB, err = sql.Open("postgres", dsn)
-        if err != nil {
-                return fmt.Errorf("sql.Open: %w", err)
+        // Optional SSH tunnel, the same thing DBeaver's "SSH" tab does. When
+        // SSH_HOST is set, DBHOST/DBPORT are resolved from the SSH server's side
+        // (for the faculty server that is usually localhost:5432).
+        if sshHost := os.Getenv("SSH_HOST"); sshHost != "" {
+                dialer, err := newSSHDialer(sshHost)
+                if err != nil {
+                        return err
+                }
+                connector, err := pq.NewConnector(dsn)
+                if err != nil {
+                        return fmt.Errorf("pq.NewConnector: %w", err)
+                }
+                connector.Dialer(dialer)
+                DB = sql.OpenDB(connector)
+        } else {
+                var err error
+                DB, err = sql.Open("postgres", dsn)
+                if err != nil {
+                        return fmt.Errorf("sql.Open: %w", err)
+                }
         }
         if err := DB.Ping(); err != nil {
@@ -72 +88 @@
 }
 
-// InitSchema runs schema_creation.sql then data_load.sql.
+// InitSchema runs schema_creation.sql, advanced_db.sql (P7) and data_load.sql.
 // Destructive: drops the `project` schema. Intended for the -init flag.
 func InitSchema() error {
-        log.Println("Running schema_creation.sql ...")
-        if err := runScript("schema_creation.sql"); err != nil {
-                return err
+        for _, name := range []string{"schema_creation.sql", "advanced_db.sql"} {
+                log.Printf("Running %s ...", name)
+                if err := runScript(name); err != nil {
+                        return err
+                }
         }
         if err := LoadData(); err != nil {

server/db/reports_demo_data.sql

@@ -28 +28 @@
 -- description/source) before re-inserting.
 
+-- P7: users' cash (available + reserved) must equal their ledger at
+-- COMMIT, so the balance moves by exactly what this script removes and
+-- re-adds to the ledger, all in one transaction. The historical orders are
+-- imported as completely filled.
+
+BEGIN;
+
 SET search_path TO project, public;
+
+UPDATE users u
+   SET available_balance = u.available_balance - d.total
+  FROM (SELECT user_id, SUM(amount) AS total FROM transactions
+         WHERE description = 'P6 demo data' GROUP BY user_id) d
+ WHERE u.id = d.user_id;
 
 DELETE FROM transactions  WHERE description = 'P6 demo data';
@@ -98 +111 @@
 -- Executed orders: who participated in which market, across the same quarters.
 -- ============================================================================
-INSERT INTO orders (id, user_id, market_id, side, type, status, quantity, price, placed_at, executed_at) VALUES
+INSERT INTO orders (id, user_id, market_id, side, type, status, quantity, filled_quantity, price, placed_at, executed_at) VALUES
     ('e1111111-1111-1111-1111-111111111111', 'b1111111-1111-1111-1111-111111111111',
-     'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 0.5000, 40000.000000,
+     'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 0.5000, 0.5000, 40000.000000,
      '2025-07-15 10:00', '2025-07-15 10:00'),
     ('e2222222-2222-2222-2222-222222222222', 'b1111111-1111-1111-1111-111111111111',
-     'a2222222-2222-2222-2222-222222222222', 'sell', 'market', 'executed', 3.0000, 4000.000000,
+     'a2222222-2222-2222-2222-222222222222', 'sell', 'market', 'executed', 3.0000, 3.0000, 4000.000000,
      '2025-10-15 10:00', '2025-10-15 10:00'),
     ('e3333333-3333-3333-3333-333333333333', 'b2222222-2222-2222-2222-222222222222',
-     'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 1.2000, 55000.000000,
+     'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 1.2000, 1.2000, 55000.000000,
      '2026-01-15 10:00', '2026-01-15 10:00'),
     ('e4444444-4444-4444-4444-444444444444', 'b2222222-2222-2222-2222-222222222222',
-     'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 1.0000, 60000.000000,
+     'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 1.0000, 1.0000, 60000.000000,
      '2026-04-15 10:00', '2026-04-15 10:00'),
     ('e5555555-5555-5555-5555-555555555555', 'b3333333-3333-3333-3333-333333333333',
-     'a2222222-2222-2222-2222-222222222222', 'sell', 'market', 'executed', 2.0000, 3600.000000,
+     'a2222222-2222-2222-2222-222222222222', 'sell', 'market', 'executed', 2.0000, 2.0000, 3600.000000,
      '2026-01-15 10:00', '2026-01-15 10:00');
+
+UPDATE users u
+   SET available_balance = u.available_balance + d.total
+  FROM (SELECT user_id, SUM(amount) AS total FROM transactions
+         WHERE description = 'P6 demo data' GROUP BY user_id) d
+ WHERE u.id = d.user_id;
+
+COMMIT;

server/db/schema_creation.sql

@@ -26 +26 @@
     available_balance numeric(18,4)   NOT NULL DEFAULT 0 CHECK (available_balance >= 0),
     invested_balance  numeric(18,4)   NOT NULL DEFAULT 0 CHECK (invested_balance  >= 0),
+    -- P7: cash committed to the user's active buy orders, moved out of
+    -- available_balance when the order is placed and consumed as it fills.
+    reserved_balance  numeric(18,4)   NOT NULL DEFAULT 0 CHECK (reserved_balance  >= 0),
     created_at        timestamptz     NOT NULL DEFAULT now(),
     updated_at        timestamptz
@@ -86 +89 @@
     side        varchar(4)     NOT NULL CHECK (side   IN ('buy', 'sell')),
     type        varchar(20)    NOT NULL CHECK (type   IN ('market', 'limit')),
-    status      varchar(20)    NOT NULL CHECK (status IN ('open', 'executed', 'cancelled')),
+    status      varchar(20)    NOT NULL CHECK (status IN ('open', 'partially_filled', 'executed', 'cancelled')),
     quantity    numeric(20,4)  NOT NULL CHECK (quantity > 0),
+    -- P7: how much of the order has been traded so far; remaining is
+    -- quantity - filled_quantity. Maintained from market_trades.
+    filled_quantity numeric(20,4) NOT NULL DEFAULT 0
+                               CHECK (filled_quantity >= 0 AND filled_quantity <= quantity),
     price       numeric(18,6),
     placed_at   timestamptz    NOT NULL DEFAULT now(),
@@ -125 +132 @@
     quantity    numeric(20,6)  NOT NULL CHECK (quantity > 0),
     side        varchar(4)     CHECK (side IN ('buy', 'sell')),
-    source      varchar(50)    NOT NULL DEFAULT 'simulation'
+    source      varchar(50)    NOT NULL DEFAULT 'simulation',
+    -- P7: the orders this trade filled. NULL on a side means the counterparty
+    -- was the simulated market (bot ticks have both NULL).
+    buy_order_id  uuid         REFERENCES project.orders(id),
+    sell_order_id uuid         REFERENCES project.orders(id)
 );
 
 CREATE INDEX idx_market_trades_market_time ON project.market_trades(market_id, executed_at DESC);
+CREATE INDEX idx_market_trades_buy_order  ON project.market_trades(buy_order_id)  WHERE buy_order_id  IS NOT NULL;
+CREATE INDEX idx_market_trades_sell_order ON project.market_trades(sell_order_id) WHERE sell_order_id IS NOT NULL;
 
 -- ============================================================================