| 2 | | |
| 3 | | The prototype is a Go command-line application in |
| 4 | | `server/` that works against the `project` |
| 5 | | schema in PostgreSQL. It implements all seven use cases from |
| 6 | | !UseCaseModel |
| 7 | | – the rubric requires at least three – with every database access shown as real, executed SQL. |
| 8 | | An auxiliary program in `bots/` simulates a |
| 9 | | live market so prices move while the prototype is running. |
| 10 | | |
| 11 | | `bots/main.go`: |
| 12 | | |
| 13 | | {{{ |
| 14 | | // EduBerza market simulation bot. |
| 15 | | // |
| 16 | | // Walks a small price for every active market, inserts rows into |
| 17 | | // market_trades once per tick, and upserts the current 1m candle. |
| 18 | | // |
| 19 | | // Run from the repo root so the relative .env path resolves: |
| 20 | | // |
| 21 | | // go run ./bots/... |
| 22 | | package main |
| 23 | | |
| 24 | | import ( |
| 25 | | "bufio" |
| 26 | | "database/sql" |
| 27 | | "flag" |
| 28 | | "fmt" |
| 29 | | "log" |
| 30 | | "math/rand" |
| 31 | | "os" |
| 32 | | "strings" |
| 33 | | "time" |
| 34 | | |
| 35 | | _ "github.com/lib/pq" |
| 36 | | ) |
| 37 | | |
| 38 | | type market struct { |
| 39 | | id string |
| 40 | | symbol string |
| 41 | | price float64 |
| 42 | | } |
| 43 | | |
| 44 | | func main() { |
| 45 | | interval := flag.Duration("interval", 3*time.Second, "seconds between price ticks") |
| 46 | | flag.Parse() |
| 47 | | |
| 48 | | loadEnv(".env") |
| 49 | | dsn := fmt.Sprintf( |
| 50 | | "host=%s port=%s user=%s password=%s dbname=%s sslmode=disable options='--search_path=project,public'", |
| 51 | | env("DBHOST", "localhost"), |
| 52 | | env("DBPORT", "5432"), |
| 53 | | env("DBUSER", "postgres"), |
| 54 | | env("DBPASSWORD", ""), |
| 55 | | env("DBNAME", "postgres"), |
| 56 | | ) |
| 57 | | db, err := sql.Open("postgres", dsn) |
| 58 | | if err != nil { |
| 59 | | log.Fatalf("open db: %v", err) |
| 60 | | } |
| 61 | | defer db.Close() |
| 62 | | if err := db.Ping(); err != nil { |
| 63 | | log.Fatalf("ping: %v", err) |
| 64 | | } |
| 65 | | |
| 66 | | markets, err := loadMarkets(db) |
| 67 | | if err != nil { |
| 68 | | log.Fatalf("load markets: %v", err) |
| 69 | | } |
| 70 | | if len(markets) == 0 { |
| 71 | | log.Fatal("no active markets found - run `go run ./server -init` first") |
| 72 | | } |
| 73 | | |
| 74 | | log.Printf("bot started. simulating %d markets every %s", len(markets), *interval) |
| 75 | | rnd := rand.New(rand.NewSource(time.Now().UnixNano())) |
| 76 | | |
| 77 | | for { |
| 78 | | for i := range markets { |
| 79 | | m := &markets[i] |
| 80 | | // random walk: ±0.3% per tick |
| 81 | | drift := (rnd.Float64() - 0.5) * 0.006 |
| 82 | | m.price = m.price * (1 + drift) |
| 83 | | if m.price <= 0 { |
| 84 | | m.price = 0.000001 |
| 85 | | } |
| 86 | | qty := rnd.Float64()*0.5 + 0.01 |
| 87 | | |
| 88 | | side := "buy" |
| 89 | | if rnd.Float64() < 0.5 { |
| 90 | | side = "sell" |
| 91 | | } |
| 92 | | |
| 93 | | if err := insertTick(db, m.id, m.price, qty, side); err != nil { |
| 94 | | log.Printf("insert tick %s: %v", m.symbol, err) |
| 95 | | continue |
| 96 | | } |
| 97 | | log.Printf(" %-8s %.6f qty=%.4f side=%s", m.symbol, m.price, qty, side) |
| 98 | | } |
| 99 | | time.Sleep(*interval) |
| 100 | | } |
| 101 | | } |
| 102 | | |
| 103 | | func loadMarkets(db *sql.DB) ([]market, error) { |
| 104 | | rows, err := db.Query(` |
| 105 | | SELECT m.id, c.symbol, COALESCE(lp.price, 100) |
| 106 | | FROM markets m |
| 107 | | JOIN crypto c ON c.id = m.crypto_id |
| 108 | | LEFT JOIN v_latest_prices lp ON lp.market_id = m.id |
| 109 | | WHERE m.is_active = true |
| 110 | | ORDER BY c.symbol`) |
| 111 | | if err != nil { |
| 112 | | return nil, err |
| 113 | | } |
| 114 | | defer rows.Close() |
| 115 | | var out []market |
| 116 | | for rows.Next() { |
| 117 | | var m market |
| 118 | | if err := rows.Scan(&m.id, &m.symbol, &m.price); err != nil { |
| 119 | | return nil, err |
| 120 | | } |
| 121 | | out = append(out, m) |
| 122 | | } |
| 123 | | return out, nil |
| 124 | | } |
| 125 | | |
| 126 | | func insertTick(db *sql.DB, marketID string, price, qty float64, side string) error { |
| 127 | | tx, err := db.Begin() |
| 128 | | if err != nil { |
| 129 | | return err |
| 130 | | } |
| 131 | | defer tx.Rollback() |
| 132 | | |
| 133 | | if _, err := tx.Exec( |
| 134 | | `INSERT INTO market_trades (market_id, executed_at, price, quantity, side, source) |
| 135 | | VALUES ($1, now(), $2, $3, $4, 'simulation')`, |
| 136 | | marketID, price, qty, side, |
| 137 | | ); err != nil { |
| 138 | | return err |
| 139 | | } |
| 140 | | |
| 141 | | // upsert the current 1m candle |
| 142 | | if _, err := tx.Exec(` |
| 143 | | INSERT INTO market_candles (market_id, timeframe, open, high, low, close, volume, candle_time) |
| 144 | | VALUES ($1, '1m', $2, $2, $2, $2, $3, date_trunc('minute', now())) |
| 145 | | ON CONFLICT (market_id, timeframe, candle_time) DO UPDATE |
| 146 | | SET high = GREATEST(market_candles.high, EXCLUDED.close), |
| 147 | | low = LEAST( market_candles.low, EXCLUDED.close), |
| 148 | | close = EXCLUDED.close, |
| 149 | | volume = market_candles.volume + EXCLUDED.volume`, |
| 150 | | marketID, price, qty, |
| 151 | | ); err != nil { |
| 152 | | return err |
| 153 | | } |
| 154 | | return tx.Commit() |
| 155 | | } |
| 156 | | |
| 157 | | func loadEnv(path string) { |
| 158 | | f, err := os.Open(path) |
| 159 | | if err != nil { |
| 160 | | return |
| 161 | | } |
| 162 | | defer f.Close() |
| 163 | | s := bufio.NewScanner(f) |
| 164 | | for s.Scan() { |
| 165 | | line := strings.TrimSpace(s.Text()) |
| 166 | | if line == "" || strings.HasPrefix(line, "#") { |
| 167 | | continue |
| 168 | | } |
| 169 | | parts := strings.SplitN(line, "=", 2) |
| 170 | | if len(parts) == 2 { |
| 171 | | os.Setenv(strings.TrimSpace(parts[0]), strings.TrimSpace(parts[1])) |
| 172 | | } |
| 173 | | } |
| 174 | | } |
| 175 | | |
| 176 | | func env(k, def string) string { |
| 177 | | if v := os.Getenv(k); v != "" { |
| 178 | | return v |
| 179 | | } |
| 180 | | return def |
| 181 | | } |
| 182 | | }}} |
| 183 | | |
| 184 | | Build, configure, run and test instructions: |
| 185 | | !BuildInstructions. |
| 186 | | |
| 187 | | All pages listed below, together with the screenshots of each run, are kept in the project's |
| 188 | | !GitHub repository, !StefanTrsunov/bp, under |
| 189 | | `docs/P4-Prototype/`. |
| 202 | | Each page mirrors its P3 use-case page and adds the actual SQL emitted by the Go code plus a |
| 203 | | screenshot of the corresponding run against the live database. The screenshots are committed |
| 204 | | alongside the pages, in |
| 205 | | `docs/P4-Prototype/screenshots/`. |
| | 13 | Each page follows its P3 use case step by step. It adds the exact SQL the Go code runs in that |
| | 14 | step and a screenshot of the step from a real run against the database. |
| | 15 | |
| | 16 | * How to build, configure, run and test the prototype: [wiki:BuildInstructions] |
| | 17 | * AI usage for this phase: [wiki:PrototypeImplementationAIUsage] |
| | 18 | |
| | 19 | == Overview == |
| | 20 | |
| | 21 | !EduBerza's P4 prototype is a Go command-line program. It works against the `project` schema in |
| | 22 | PostgreSQL. It implements all seven use cases from [wiki:UseCaseModel]; the course asks for at |
| | 23 | least three. Every database access is real SQL that was executed and tested. A second program, |
| | 24 | the market bot, simulates a live market so prices move while the prototype runs. |
| | 25 | |
| | 26 | The source code is in the project's git repository: the CLI in `server/`, the bot in `bots/`, |
| | 27 | and the SQL scripts in `server/db/`. |
| | 28 | |
| | 29 | == Technology and architecture == |
| | 30 | |
| | 31 | * '''Language:''' Go (module `bp_project`, `go 1.25` in `go.mod`). The only third-party |
| | 32 | library is the PostgreSQL driver `github.com/lib/pq`. |
| | 33 | * '''Database:''' PostgreSQL. Every table, view and function is in the `project` schema. The |
| | 34 | DDL is `schema_creation.sql` and the sample data is `data_load.sql`. Both scripts are |
| | 35 | compiled into the binary and run by `./eduberza -init`. |
| | 36 | * '''Interface:''' plain text menus on standard input and output. There are no web server, |
| | 37 | frameworks, images or styles. |
| | 38 | * '''Structure:''' one source file per area of the application. |
| | 39 | |
| | 40 | ||= File =||= Responsibility =||= Use cases =|| |
| | 41 | || `server/main.go` || Flags `-init` / `-load-data`, then starts the menu loop || — || |
| | 42 | || `server/cli.go` || The two menus (before and after login), input reading || all || |
| | 43 | || `server/db/db.go` || Connection from `.env` / environment variables, embedded SQL scripts || — || |
| | 44 | || `server/auth.go` || Register, log in (SHA-256 password hash) || UC0001, UC0002 || |
| | 45 | || `server/account.go` || Balance, deposit, transaction history || UC0003, UC0006 || |
| | 46 | || `server/market.go` || Market list, choosing a market or a holding by number, latest price || UC0004, UC0005 || |
| | 47 | || `server/trade.go` || Market buy and sell orders, each in one transaction || UC0004, UC0005 || |
| | 48 | || `server/portfolio.go` || Portfolio with current value and unrealised P/L || UC0006 || |
| | 49 | || `server/watchlist.go` || List, add and remove watchlist items || UC0007 || |
| | 50 | || `bots/main.go` || Market bot: random-walk price ticks into `market_trades`, 1-minute candles || — || |
| | 51 | |
| | 52 | == No identifiers to remember == |
| | 53 | |
| | 54 | The user never has to type or remember an id, a code or a symbol: |
| | 55 | |
| | 56 | * Every menu is numbered, and the user answers with the number of an option. |
| | 57 | * '''Buying:''' all active markets are listed with their latest price, numbered 1…n. The user |
| | 58 | enters the market's number at `Market #:` (`ChooseMarket` in `market.go`). |
| | 59 | * '''Selling:''' only the cryptos the user actually holds are listed, each with the quantity |
| | 60 | held and the quantity still free to sell. The user enters the holding's number at |
| | 61 | `Holding #:` (`ChooseHolding`). A user who holds nothing free to sell gets |
| | 62 | `you hold no crypto that is free to sell` and is never asked to choose. |
| | 63 | * '''Watchlist:''' ''Add'' lists the cryptos that are not on the watchlist yet. ''Remove'' |
| | 64 | lists the ones that are on it. Both are numbered, and the user enters the number. |
| | 65 | * A number outside the list is refused with `Invalid choice, enter a number from 1 to N.` and |
| | 66 | nothing is changed. |
| | 67 | |
| | 68 | The only things the user types are their own data: username, e-mail, full name, password, the |
| | 69 | amount to deposit, the quantity to buy or sell, and the date range of the two P6 reports. |
| 209 | | * '''The current price is never stored as a column.''' It is always the price of the most recent |
| 210 | | row in `market_trades`, read through the `v_latest_prices` view. Both the user's own fills and |
| 211 | | the bot's simulated trades feed the same table, so there is exactly one definition of "the |
| 212 | | price". |
| 213 | | * '''Money movements are transactional.''' Buying touches five tables – `orders`, `users`, |
| 214 | | `holdings`, `transactions`, `market_trades` – inside one transaction. A failed balance check |
| 215 | | rolls the whole thing back: after a rejected purchase there is no order row, no ledger entry |
| 216 | | and no holding. This is verified in the failure-path tests in |
| 217 | | !BuildInstructions. |
| 218 | | * '''Constraints do real work.''' `UNIQUE (user_id, crypto_id)` on `holdings` is what makes the |
| 219 | | `INSERT … ON CONFLICT DO UPDATE` upsert possible, so the weighted-average entry price is |
| 220 | | recomputed by the database in one statement instead of by a read-modify-write in application |
| 221 | | code. `CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` is the same idea |
| 222 | | applied to the sell path: an inconsistent reservation is impossible at the database level, not |
| 223 | | just something `trade.go` is careful about. |
| 224 | | * '''Selling reserves before it removes.''' A sell order locks the holding row, reserves the |
| 225 | | quantity being sold, then settles by removing it — see |
| 226 | | UseCase0005Implementation. Two sell orders placed at the same |
| 227 | | instant for more than the available quantity are serialised correctly by `SELECT ... FOR |
| 228 | | UPDATE`, not just by luck of everything happening in one CLI process; this is demonstrated |
| 229 | | there with two concurrent processes. |
| 230 | | * '''No identifiers are ever typed.''' Markets are listed with their prices before any choice is |
| 231 | | made, and everything else is selected by symbol. |
| | 73 | * '''The current price is never stored as a column.''' It is always the price of the most |
| | 74 | recent row in `market_trades`, read through the `v_latest_prices` view. The user's own fills |
| | 75 | and the bot's simulated trades go into the same table, so there is only one definition of |
| | 76 | "the price". |
| | 77 | * '''Money movements are transactional.''' A buy touches five tables (`orders`, `users`, |
| | 78 | `holdings`, `transactions`, `market_trades`) inside one transaction. If the balance check |
| | 79 | fails, the whole transaction is rolled back: after a rejected purchase there is no order |
| | 80 | row, no ledger entry and no holding. The failure-path tests in [wiki:BuildInstructions] |
| | 81 | check this. |
| | 82 | * '''Constraints do real work.''' `UNIQUE (user_id, crypto_id)` on `holdings` is what makes |
| | 83 | the `INSERT … ON CONFLICT DO UPDATE` upsert possible, so the database recomputes the |
| | 84 | weighted-average entry price in one statement, instead of the application reading, changing |
| | 85 | and writing the row. `CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` does |
| | 86 | the same for the sell path: the database itself makes an inconsistent reservation |
| | 87 | impossible, and it does not rely only on `trade.go` being careful. |
| | 88 | * '''Selling reserves before it removes.''' A sell order locks the holding row with |
| | 89 | `SELECT … FOR UPDATE`, reserves the quantity being sold, then settles by removing it (see |
| | 90 | [wiki:UseCase0005Implementation]). Two sell orders for more than the free quantity, placed |
| | 91 | at the same moment from two separate processes, are serialised by the row lock. Exactly one |
| | 92 | of them succeeds. This was tested with two concurrent processes in session 3 (see |
| | 93 | [wiki:PrototypeImplementationAIUsage]). |
| 237 | | * Only `market` orders execute. `limit` is accepted by the schema (`orders.type`) but the |
| 238 | | matching logic is not implemented. |
| 239 | | * Passwords are SHA-256 without a salt. Adequate to demonstrate that the password itself is |
| 240 | | never stored; not adequate for real use. A proper password hash belongs in P9 (security). |
| 241 | | * Money is handled as `float64` in Go while the database columns are `numeric`. All arithmetic |
| 242 | | that must be exact – the weighted average – is done in SQL for that reason, but the Go side |
| 243 | | would need a decimal type for real use. |
| 244 | | * There is no connection pooling configuration and no explicit isolation level; both are P8 |
| 245 | | topics. |
| 246 | | * Reservation only ever lives inside one transaction, because only market orders (which settle |
| 247 | | immediately) exist. A real limit-order matcher would leave `holdings.reserved_quantity` set |
| 248 | | and `orders.status = 'open'` between two separate commits, and would need a way to cancel an |
| 249 | | order to release the reservation — neither is implemented, since nothing in the prototype |
| 250 | | produces an order that stays open. |
| | 99 | * Only `market` orders execute. The schema accepts `limit` (`orders.type`), but there is no |
| | 100 | matching logic for it. |
| | 101 | * Passwords are hashed with SHA-256 and no salt. That shows the password itself is never |
| | 102 | stored, but it is not good enough for real use. A proper password hash belongs in P9 |
| | 103 | (security). |
| | 104 | * Money is `float64` in Go, while the database columns are `numeric`. For that reason, all |
| | 105 | arithmetic that must be exact (the weighted average) is done in SQL. Real use would need a |
| | 106 | decimal type on the Go side too. |
| | 107 | * The prototype sets no connection pool and no explicit isolation level. Both are P8 topics. |
| | 108 | * A reservation only exists inside one transaction, because the prototype only has market |
| | 109 | orders, and they settle immediately. A real limit-order matcher would leave |
| | 110 | `holdings.reserved_quantity` set and `orders.status = 'open'` between two separate commits. |
| | 111 | It would also need a way to cancel an order and release the reservation. Neither is |
| | 112 | implemented, because nothing in the prototype creates an order that stays open. |
| 256 | | * '''Phase log:''' |
| 257 | | PrototypeImplementationAIUsage |
| 258 | | – service used, the bugs found and fixed, the test evidence, and what I decided myself. |
| 259 | | * '''Full conversation transcript:''' |
| 260 | | ERModelAIUsage |
| 261 | | – the same conversation produced the P1–P4 artefacts, so the complete prompt/response log is |
| 262 | | kept in one place. Its sections: |
| 263 | | Session 1 – 2026-04-21, |
| 264 | | Session 2 – 2026-08-06/07, |
| 265 | | Session 3 – 2026-09-16. |
| | 120 | ||= Date =||= Change =||= Origin =|| |
| | 121 | || 2026-04-21 || My HTTP backend rewritten as the CLI prototype covering UC0001–UC0007. The schema errors in my draft were corrected. The market bot was added. || My code and decisions (CLI instead of web, drop the frontend, keep a simulator); rewrite by AI (session 1) || |
| | 122 | || 2026-08-06/07 || Three bugs fixed: path resolution of `.env` and the SQL scripts, an endless loop at end of input, and an error check in the wrong order on the sell path. The holding update became one `INSERT … ON CONFLICT DO UPDATE`. || I asked for a code review; fixes by AI (session 2) || |
| | 123 | || 2026-09-16 || `holdings.reserved_quantity` added. The sell path now reserves, then settles. Orders go from `open` to `executed`. || The edge case was mine; implementation by AI (session 3) || |
| | 124 | || 2026-09-24 || Every choice is picked from a numbered list: markets by number, a sell lists only the user's holdings, the watchlist lists the cryptos. All screenshots were retaken, one per step. || I asked for a check against the P4 rules; implementation by AI (session 4) || |
| 267 | | '''Service:''' Claude Code (Anthropic), `https://claude.com/claude-code` – Claude subscription, |
| 268 | | model Claude Opus 4.7 (1M context) in sessions 1–2, Claude Sonnet 5 in session 3. |
| 269 | | |
| 270 | | '''In short:''' session 1 rewrote the existing Chi/HTTP backend as the CLI prototype covering |
| 271 | | UC0001–UC0007 and added the market bot. Session 2 was a review pass I asked for, which found and |
| 272 | | fixed three bugs – a path-resolution bug that made the documented build instructions fail, an |
| 273 | | infinite loop at end of input, and an error check in the wrong order that misreported database |
| 274 | | failures as "Insufficient holding" – and replaced the read-modify-write holding update with a |
| 275 | | single `INSERT … ON CONFLICT DO UPDATE`. Session 3 added `holdings.reserved_quantity` and changed |
| 276 | | `trade.go`'s sell path to reserve crypto before removing it, closing a gap where two sell orders |
| 277 | | could be granted the same units; see the "Session 3 — 2026-09-16" section of |
| 278 | | PrototypeImplementationAIUsage. |
| | 126 | '''Service:''' Claude Code (Anthropic), Claude subscription. Session 1 used Claude Opus 4.7 (1M |
| | 127 | context), session 2 Claude Opus 5 (1M context), session 3 Claude Sonnet 5, and session 4 |
| | 128 | Claude Opus 5.5 (1M context). |
