| | 1 | = Prototype Implementation = |
| | 2 | |
| | 3 | == Implemented use-cases == |
| | 4 | |
| | 5 | * [wiki:UseCase0001Implementation] — Register new account |
| | 6 | * [wiki:UseCase0002Implementation] — Log in |
| | 7 | * [wiki:UseCase0003Implementation] — Deposit virtual funds |
| | 8 | * [wiki:UseCase0004Implementation] — Place market BUY order |
| | 9 | * [wiki:UseCase0005Implementation] — Place market SELL order |
| | 10 | * [wiki:UseCase0006Implementation] — View portfolio and transaction history |
| | 11 | * [wiki:UseCase0007Implementation] — Manage watchlist |
| | 12 | |
| | 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. |
| | 70 | |
| | 71 | == What the prototype demonstrates about the database design == |
| | 72 | |
| | 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]). |
| | 94 | |
| | 95 | == Known limitations == |
| | 96 | |
| | 97 | These were left out on purpose for a first prototype. They belong to the later phases: |
| | 98 | |
| | 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. |
| | 113 | |
| | 114 | == History of changes == |
| | 115 | |
| | 116 | The code started as my own Go backend: HTTP handlers, a draft schema, and a `db.go` that |
| | 117 | recreated the tables on every start. It changed as follows. The AI's share of each change is |
| | 118 | logged in [wiki:PrototypeImplementationAIUsage]. |
| | 119 | |
| | 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) || |
| | 125 | |
| | 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). |
