Index: .env.example =================================================================== --- .env.example (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ .env.example (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,12 @@ +# Copy to .env and fill in. Values below match the local docker-compose setup. +# Real environment variables take precedence over this file, so you can point +# the prototype at the faculty database without editing anything: +# +# DBHOST=dbteaching.finki.ukim.mk DBPORT=5432 DBUSER=... DBPASSWORD=... \ +# DBNAME=... ./eduberza +# +DBPORT=5433 +DBUSER=bp_project +DBPASSWORD=1234 +DBNAME=bp_database +DBHOST=localhost Index: .gitignore =================================================================== --- .gitignore (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ .gitignore (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,13 @@ +# Build output +/eduberza +/bots/bots + +# Local configuration and secrets — never commit; use .env.example as template +.env + +# Editor settings +.vscode/ + +*.jar +docs/Instructions.md +Instructions.md Index: -ProjectDefinition/About.md =================================================================== --- P0-ProjectDefinition/About.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,86 +1,0 @@ -# About: EduBerza - -## Team members - -- Stefan Trsunov - 231285 - -## Short description - -This project is a simulation of a crypto exchange, intended for beginners and -created as a project for the Databases course for 2025-2026. The project is -inspired by TradingView/Binance and aims to let users experience buying and -selling cryptocurrencies, as well as the influence these activities have on the -market and on the way trades are executed. - -The platform will allow user registration and the management of virtual (prop) -money. Every step of the process will be explained in detail, so that users -understand exactly what happens behind the scenes and how transactions affect -the market. - -The following technologies are used: Golang (with the Chi package), and -PostgreSQL for the database. The prototype will be a Go CLI, and the final -project will be built with TypeScript, specifically Lit components. - -The main goal is to create educational and interactive software that will help -beginners and ordinary users understand how crypto exchanges work through -practical simulation and clear explanations. - -Inspiration: - -https://tradingview.com/ -https://www.binance.com/ - - - -## Detailed description - -This project is an educational simulation of a crypto exchange, intended for -beginners and created as a project for the Databases course for 2025-2026. The -project is inspired by the leading platforms TradingView and Binance, with the -goal of letting users experience in practice the buying and selling of -cryptocurrencies, as well as the direct influence these transactions have on the -market and on the trade-execution process itself. - -The platform will allow user registration and the management of virtual (prop) -money. Every step of the process will be explained in detail and in practical -terms, so that users can more easily understand what exactly happens behind the -scenes — from the placing of orders to their matching and the effects on market -liquidity. - -The following technologies are used to build the project: Golang (with the Chi -package) for the server side and PostgreSQL for managing the database. The -architecture is planned in two phases: the initial prototype will be a Go CLI -application for quick verification, while the final web version will be built -with TypeScript, specifically with Lit components, to create a modern interface. - -The main goal is to create interactive software that will help beginners and -ordinary users understand how crypto exchanges work through practical -simulation, clear explanations and transparent processes. - - -### Who is the database and project intended for? - -Target Audience: Beginners and general users who want to learn how cryptocurrency exchanges work without financial risk. - -### Which problems does your project solve? - -This is only for education purposes. The problem is fixing the financial literacy of the people. - -### Who uses the system? What types of users exist? - -Roles/Users that are expected to use this product are: - -- Visitors -- Traders - -### How is this different from similar existing solutions? - -Virtual money with a step-by-step explanation of what each order does to the `book`. - -### Is this a web, mobile and/or desktop application? - -The prototype will be a CLI but the end goal will be to build a website - -### Illustrations - -Screenshots of the running prototype: [`screenshots/`](../P4-Prototype/screenshots) Index: -ProjectDefinition/opis.md =================================================================== --- P0-ProjectDefinition/opis.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,18 +1,0 @@ -ЕдуБерза - -Веб проект за симулација на крипто берза за почетници, инспирирана од TradingView, изградена со Golang, Typescript и PostgreSQL. Овозможува регистрација на корисници, симулира купување и продавање криптовалути, сето ова ќе биде овозмошено со префрлување на виртуелни пари (prop) со објаснување на секој чекор и ефектите врз пазарот. - -Целосен опис - -Овој проект претставува симулација на крипто берза, наменета за почетници и создадена како проект за предметот „База на податоци“ за 2025-2026 година. Проектот е инспириран од TradingView/Binance и има за цел да им овозможи на корисниците да ја искусат купување и продавање криптовалути, како и влијанието што овие активности го имаат врз пазарот и начинот на извршување на тргувањата. - -Платформата ќе овозможи регистрација на корисници, управување со виртуелни пари (prop). Секој чекор од процесот ќе биде детално објаснет, за корисниците да разберат што точно се случува зад сцената и како трансакциите влијаат на пазарот. - -Се користат следниве технологии: Golang (со Chi пакетот), и PostgreSQL за база на податоци. Прототипот ќе биде Go CLI, а конечниот проект ќе биде со Typescript поточно Lit componenti. - -Главната цел е да се создаде едукативен и интерактивен софтвер кој ќе им помогне на почетниците и обичните корисници да го разберат работењето на крипто берзите преку практична симулација и јасни објаснувања. - -Инспирација: - -https://tradingview.com/ -https://www.binance.com/ Index: -ConceptualModel/ERModel.md =================================================================== --- P1-ConceptualModel/ERModel.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,253 +1,0 @@ -# Entity-Relationship Model v.01 - -## Diagram - -![ERModel_v01](ERModel_v01.png) - -Attachments for this page: `ERModel_v01.xml` (TerraER source) and `ERModel_v01.png` -(exported image). Open the source with TerraER 3.11: - -```sh -java -jar TerraER3.11.jar # then File → Open → ERModel_v01.xml -``` - -Notation: Chen. Rectangles are entity sets, diamonds are relationships, ellipses -are attributes, underlined ellipses are primary keys, the dashed ellipse is a -derived attribute. A double line between an entity set and a relationship marks -**total participation** (every instance of that entity set must participate); a -single line marks partial participation. - -Two deliberate modeling decisions worth stating up front: - -- **No foreign keys appear in the diagram.** Connections between entity sets are - expressed as relationships, per the notation. Foreign-key columns appear only - in the relational model in [RelationalDesign](../P2-RelationalDesign/RelationalDesign.md). -- **`Holds` and `Contains` are relationships, not entity sets.** Both are M:N and - both carry their own attributes, which is exactly what a Chen relationship is - for. They become tables (`holdings`, `watchlist_items`) only in P2. - -## Data requirements - -### Entity sets - -#### Users -Registered participants of the platform. Every action in the simulation is -attributed to a user, and the two balance attributes are what makes the -simulation work: cash that is free to trade is tracked separately from cash that -is currently committed to open positions, so the platform can refuse a purchase -without having to recompute the whole portfolio first. - -- **Candidate keys:** `{id}`, `{username}`, `{email}`. Primary key: **`id`**. - A surrogate UUID was chosen because it is opaque and stable — `username` and - `email` are both things a user may legitimately want to change later, and - every relationship in the diagram points at `Users`, so a mutable key would - propagate changes across the whole database. -- **Attributes:** - - `id` — UUID, required, primary key. - - `username` — text, max 50, required, unique. - - `email` — text, max 255, required, unique, must contain `@`. - - `full_name` — text, max 200, optional. - - `password_hash` — text, max 255, required. Never the password itself; the - prototype stores a SHA-256 hex digest. - - `available_balance` — numeric(18,4), required, default 0, must be ≥ 0. - - `invested_balance` — numeric(18,4), required, default 0, must be ≥ 0. - - `created_at` — timestamp with time zone, required, defaults to now. - - `updated_at` — timestamp with time zone, optional (null until first change). - -#### Cryptos -The catalog of crypto assets the platform knows about. Kept separate from -`Markets` because an asset exists independently of the pairs it is traded in — -the same asset can be quoted against several currencies, and a user's holding is -in the *asset*, not in a particular pair. - -- **Candidate keys:** `{id}`, `{symbol}`. Primary key: **`id`**, for the same - reason as in `Users`; `symbol` is kept as a unique natural key because that is - what users type and see. -- **Attributes:** - - `id` — UUID, required, primary key. - - `symbol` — text, max 20, required, unique (e.g. `BTC`). - - `name` — text, max 255, required (e.g. `Bitcoin`). - - `created_at` — timestamptz, required, defaults to now. - -#### Markets -A tradeable pair: one crypto asset quoted in one currency, e.g. BTC/USD. This is -where prices live, and it is the thing an order is placed *on*. Modeled as its -own entity set rather than an attribute of `Cryptos` because a market has its own -lifecycle — it can be deactivated without deleting the asset — and because -trades, candles and orders all reference the pair, not the asset. - -- **Candidate keys:** `{id}`, `{crypto_id, quote_currency}` — that pair is - unique by definition, since a given asset can only be quoted once per - currency. Primary key: **`id`**, so that the many entity sets referencing a - market carry one narrow column instead of a composite key. -- **Attributes:** - - `id` — UUID, required, primary key. - - `quote_currency` — text, exactly 3 characters, required, default `USD`. - - `is_active` — boolean, required, default true. Inactive markets are hidden - from the trading menus but keep their history. - - `created_at` — timestamptz, required, defaults to now. - -#### Orders -A user's instruction to buy or sell on a market. Needed as a separate entity set -because an order is a record of *intent* that outlives its execution: it keeps -the requested quantity and price even after it has been filled, which is what -makes the ledger auditable. - -- **Candidate keys:** `{id}` only. There is no natural key — the same user can - place two identical orders on the same market in the same second, and both are - legitimately distinct. Primary key: **`id`**. -- **Attributes:** - - `id` — UUID, required, primary key. - - `side` — text, required, restricted to `buy` or `sell`. - - `type` — text, required, restricted to `market` or `limit`. The prototype - executes only `market` orders; `limit` exists so the model does not have to - change when limit orders are implemented. - - `status` — text, required, restricted to `open`, `executed`, `cancelled`. - - `quantity` — numeric(20,4), required, must be > 0. - - `price` — numeric(18,6), optional — null for a market order until it fills, - then the fill price. - - `placed_at` — timestamptz, required, defaults to now. - - `executed_at` — timestamptz, optional, set when the order fills. - -#### Transactions -The financial ledger: every movement of virtual cash, in one place. This exists -so that a balance is never just a number someone edited — it is the sum of an -auditable list of entries, which is also what the "explain every step" goal of -the project needs. - -- **Candidate keys:** `{id}` only. Primary key: **`id`**. -- **Attributes:** - - `id` — UUID, required, primary key. - - `type` — text, required, restricted to `deposit`, `buy`, `sell`, `fee`. - - `amount` — numeric(18,4), required. Signed: negative for money leaving the - cash balance, positive for money arriving. - - `currency` — text, exactly 3 characters, required, default `USD`. - - `created_at` — timestamptz, required, defaults to now. - - `description` — text, optional, free-form human-readable explanation. - -#### MarketTrades -Individual executed trades on a market, from the user's own fills and from the -market simulator. This is the single source of truth for the current price: the -price of a market is the price of its most recent trade, never a column someone -writes directly. - -- **Candidate keys:** `{id}`. In principle `{market_id, executed_at}` looks - unique, but two trades can share a timestamp, so it is not a safe key. - Primary key: **`id`** (a plain auto-incrementing integer here rather than a - UUID, because this is the highest-volume entity set and it is only ever read - in timestamp order, never referenced by anything else). -- **Attributes:** - - `id` — integer, required, primary key, auto-generated. - - `executed_at` — timestamptz, required. - - `price` — numeric(18,6), required, must be > 0. - - `quantity` — numeric(20,6), required, must be > 0. - - `side` — text, optional, `buy` or `sell`. - - `source` — text, max 50, required, default `simulation`. Distinguishes a - simulated trade from a user's own fill (`user`). - -#### MarketCandles -OHLCV aggregates per market and timeframe — the data a price chart is drawn -from. Stored rather than computed on the fly because the point of the project is -a chart-driven interface, and re-aggregating the whole trade history for every -screen refresh does not scale. - -- **Candidate keys:** `{id}`, and `{market_id, timeframe, candle_time}` — a - market has exactly one candle per timeframe per time bucket. Primary key: - **`id`**; the composite is enforced as a uniqueness rule because it is the - real-world constraint and it is what prevents duplicate candles. -- **Attributes:** - - `id` — integer, required, primary key, auto-generated. - - `timeframe` — text, required, restricted to `1m`, `5m`, `1h`, `1d`. - - `open`, `high`, `low`, `close` — numeric(18,6), all required. - - `volume` — numeric(20,6), required. - - `candle_time` — timestamptz, required — the start of the bucket. - -#### Watchlists -A named list of assets a user wants to monitor. A separate entity set rather than -a flag on the relationship between users and assets, because a user may want -several lists ("long term", "watching today") and each needs its own name. - -- **Candidate keys:** `{id}`. `{user_id, name}` would also work if list names - are required to be unique per user; the model does not impose that, so it is - not listed as a candidate key. Primary key: **`id`**. -- **Attributes:** - - `id` — UUID, required, primary key. - - `name` — text, max 100, required. - - `created_at` — timestamptz, required, defaults to now. - -### Relationships - -#### QuotedOn — Cryptos (1) : Markets (N), total on Markets -Ties a market to the asset it trades. One asset can be quoted in many markets; -every market must have exactly one asset, hence total participation on the -`Markets` side. No attributes of its own. - -#### PlacedOn — Markets (1) : Orders (N), total on Orders -Records which market an order was placed on. Every order must name a market; -a market may have no orders yet. No attributes. - -#### Places — Users (1) : Orders (N), total on Orders -Records who placed an order. Every order belongs to exactly one user; a new user -has no orders. No attributes. - -#### Records — Users (1) : Transactions (N), total on Transactions -Attributes each ledger entry to a user. Every entry belongs to exactly one user. -No attributes. - -#### Settles — Orders (1) : Transactions (N), partial on both sides -Links a ledger entry to the order that caused it. Partial on the `Transactions` -side because deposits have no originating order, and partial on the `Orders` side -because an order that never executes never produces a ledger entry. This is why -the corresponding column is nullable in P2. No attributes. - -#### Fills — Markets (1) : MarketTrades (N), total on MarketTrades -Every executed trade happened on exactly one market. No attributes. - -#### Aggregates — Markets (1) : MarketCandles (N), total on MarketCandles -Every candle summarises trades of exactly one market. No attributes. - -#### Owns — Users (1) : Watchlists (N), total on Watchlists -Every watchlist belongs to exactly one user. No attributes. - -#### Holds — Users (M) : Cryptos (N), partial on both sides, **with attributes** -A user's position in an asset. M:N because one user holds many assets and one -asset is held by many users, and partial on both sides because a user may hold -nothing and an asset may be held by nobody. Modeled as a relationship rather -than an entity set because a position has no identity of its own — it is -entirely described by *which user*, *which asset*, and how much. - -- **Attributes:** - - `quantity` — numeric(20,4), required, must be ≥ 0. - - `avg_price` — numeric(18,6), required, ≥ 0, **derived** (dashed ellipse): - the weighted average of the prices at which the position was accumulated. - It is derivable from the buy history, and is stored anyway so that - unrealised P/L can be shown without replaying the whole ledger. - - `created_at` — timestamptz, required, defaults to now. - - `updated_at` — timestamptz, optional. - -#### Contains — Watchlists (M) : Cryptos (N), partial on both sides, **with attribute** -Which assets are on which watchlist. M:N: a list holds many assets, an asset -appears on many lists. Partial on both sides — an empty list is valid and an -asset need not be on any list. - -- **Attributes:** - - `added_at` — timestamptz, required, defaults to now. Recorded so a list can - be shown in the order the user built it. - -## Entity-Relationship Model History - -- **v01** — First complete version. Built from the entity notes in - [`ep-diagram.md`](ep-diagram.md) (the initial hand-written model), with three - changes made to that initial model while drawing it: - 1. `Markets` was promoted from an implied attribute of the asset to its own - entity set, so that prices, orders, trades and candles can all reference a - pair rather than an asset. - 2. `holdings` and `watchlist_items` were re-expressed as the M:N relationships - `Holds` and `Contains` with their own attributes, instead of entity sets - with foreign keys — the initial notes listed them as tables, which is a - relational concept that does not belong in a Chen ERD. - 3. `avg_price` was marked as a derived attribute rather than a plain one, to - make the denormalisation explicit rather than hidden. - -Reasoning for the AI-assisted part of this phase, and the full interaction log, -are on [ERModelAIUsage](ERModelAIUsage.md). Index: -ConceptualModel/ERModelAIUsage.md =================================================================== --- P1-ConceptualModel/ERModelAIUsage.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,177 +1,0 @@ -# Entity-Relationship Model AI Usage - -## Name of AI service/solution that was used - -**Claude Code** (Anthropic) - -- **URL:** https://claude.com/claude-code -- **Type of service/subscription:** Claude subscription. Session 1 used model - Claude Opus 4.7 (1M context); session 2 used Claude Opus 5 (1M context). - -## Final result - -### Diagram - -`ERModel_v01.xml` / `ERModel_v01.png`. - -**Declaration of how the diagram was produced.** The initial model is the -student's own: the entity and attribute list in [`ep-diagram.md`](ep-diagram.md), -written in Macedonian before any AI was involved. In session 2 the AI turned that -list into the TerraER diagram file, and while doing so proposed three changes to -the initial model, all three listed in the model history on -[ERModel](ERModel.md#entity-relationship-model-history): -promoting `Markets` to its own entity set, re-expressing `holdings` and -`watchlist_items` as M:N relationships with attributes rather than entity sets -with foreign keys, and marking `avg_price` as derived. - -The diagram was not drawn by hand in the TerraER GUI. It was generated -programmatically by constructing TerraER's own figure objects -(`EntidadeFigure`, `RelacionamentoFigure`, `AtributoFigure`, -`AtributoChaveFigure`, `AtributoDerivadoFigure` and the labelled line-connection -figures) and serialising them with TerraER's own -`DOMStorableInputOutputFormat` — the same writer the application uses when you -choose *Save*. The file is therefore a normal TerraER document: it was verified -by reading it back through TerraER's own reader and comparing the figure count -(144), and it opens and can be edited in TerraER 3.11 like any hand-drawn -diagram. Subsequent versions (`ERModel_v02.xml` onward) are edited by the student -in the GUI. - -### Model description - -See [ERModel](ERModel.md). - -## Summary of AI involvement - -Work on this project happened in two working sessions, several months apart. - -| | Session 1 | Session 2 | -|---|---|---| -| **When** | 2026-04-21 | 2026-08-06 / 2026-08-07 | -| **Model** | Claude Opus 4.7 (1M context) | Claude Opus 5 (1M context) | -| **Phases advanced** | P1, P2, P3 and the first working prototype | The ER diagram file, P4 documentation, bug fixes | -| **My starting material** | `ep-diagram.md`, `opis.md`, my existing Go backend and draft SQL | Everything from session 1, plus the phase rubric | - -In **session 1** I brought my own data model (`ep-diagram.md`, written in -Macedonian before any AI was involved) and my own draft schema and Go backend. I -used the AI to review them, and it found real errors in my SQL that I had missed -— most seriously that I had declared `crypto_id` as a foreign key to two -different tables at once, in three separate tables. I decided the corrections to -adopt, chose to go solo, chose a CLI prototype over an HTTP one, and chose -English for the documentation. The output of that session was the corrected -schema, the sample-data script, the use-case model and the working prototype. - -In **session 2** I came back to produce the deliverable I was still missing — the -TerraER diagram file itself — and to finish the P4 documentation. I also asked -for a review pass over the database and Go code, which turned up three further -bugs (see [PrototypeImplementationAIUsage](../P4-Prototype/PrototypeImplementationAIUsage.md)). - -Below is the log, one entry per exchange. My prompts are quoted as **excerpts**: -the wording is my own, unaltered, but I have left out scheduling remarks and -other working chatter that says nothing about the model or the code. Typos are -kept as they were. The *Intent* line above each prompt is my own note on what I -was trying to achieve. AI responses are summarised rather than quoted in full, -because several ran to many pages of code and file edits. The complete -unabridged transcript is in my Claude Code session history and can be produced -on request. - -## Entire AI usage log - ---- - -### Session 1 — 2026-04-21 - -**Intent:** hand the AI the whole project and the course rubric, and have it -audit my existing model and code against the phase requirements before I commit -to anything. - -**Prompt (student, excerpt):** -> This is my project for my exam Database, go to the docs folder and README.md there you will find all of the documentation, afterwards take a look at the whole project. Delete everything you want add everything you want just make it work and make it fill in or to follow all of the needed instructions. Ask me anything, also tell me what do I need to do - -**Response (AI, summarised):** -- Read `docs/opis.md`, `docs/ep-diagram.md`, `docs/dbdiagram.md`, `server/db/db.sql`, `server/db/schema.sql`, all Go sources. -- Flagged bugs in the initial schema: - - `holdings.crypto_id` declared as a foreign key to both `users(id)` and `crypto(id)`; should be a separate `user_id` column. - - Same mistake in `orders` and `transactions`. - - `market_candles` referenced a non-existent `markets` table. - - `main.go` dropped and recreated all tables on every startup, wiping data. -- Proposed a single authoritative schema, to be placed in `server/db/schema_creation.sql`, based on `docs/ep-diagram.md` and `docs/dbdiagram.md` with the above issues fixed. -- Asked the student for decisions: team composition, prototype shape (CLI vs HTTP), whether to keep `available_balance`, `invested_balance`, `secret_question`, `secret_answer`, which use cases to implement, language of documentation. - -**Intent:** I had just pasted the course rubric into `README.md` and removed the -half-finished frontend, so that the AI would plan against the actual phase -requirements and against a backend-only prototype. - -**Prompt (student, excerpt):** -> Read it now, just filled it in, also deleted the frontend we don' need that - -**Response (AI, summarised):** -- Re-read `docs/README.md`, which now contained the course phase rubric (P0–P9). -- Explained the constraints each phase places on AI usage (P0 forbids AI; P1–P4 allow AI only to improve the student's own initial model and require an AIUsage log). -- Listed the deliverables per phase and asked the remaining questions. - ---- - -### Session 2 — 2026-08-06 / 2026-08-07 - -**Intent:** re-read the rubric from scratch after the gap, reduce it to a -checklist of what each phase actually has to produce, confirm TerraER runs on -this machine. - -**Prompt (student, excerpt):** -> We will start from the begining in some ways, please read first docs/insturctions.md -> The goal is to finish with milestone 0 so I can publish it […] -> Read the Instuctions.md like I said and delete everything that you think we don't need -> and for each phrase tell me what do I need to submit and how to do it. and can you also -> do the thing with TerraER3.11.jar? - -**Response (AI, summarised):** -- Read the phase rubric and condensed `Instructions.md` to a per-phase checklist - of deliverables, keeping every requirement and every load-bearing filename. -- Confirmed TerraER 3.11 runs on the installed OpenJDK 21 and launched it. -- Listed, per phase, what has to be submitted and how; flagged that P0's - deliverables are wiki pages, that the EPRMS proposal is a prerequisite. -- Flagged `image.png` (a TradingView screenshot) as material the project has no - licence to publish. - -**Intent:** produce the artefacts still missing for P1–P4 — above all the TerraER -diagram — while keeping P0 for myself, since AI use is forbidden there. - -**Prompt (student, excerpt):** -> […] do all of the other Phases till m0. -> opis.md It's p0 so I will take care of that. Delete anything that we don't need, -> make all of the phases and terra diagram if you can, and delete anything -> that we don't need and make a documentation about how to start it. - -**Intent:** ask for a review pass over the schema and the Go code rather than -only documentation, on the grounds that a prototype I have to defend in person -should not have known defects in it. - -**Prompt (student, excerpt, follow-up):** -> Also fix some database things or golang things if you think we can do it better, - -**Response (AI, summarised) — the part relevant to P1:** -- Read `ep-diagram.md` (the student's own initial model) and the existing - `schema_creation.sql`. -- Reverse-engineered TerraER's file format from the distributed jar to learn the - element names it stores figures under (`ent`, `rel`, `atr`, `atrchave`, - `atrderivado`, `llabelUm`, `llabelMuitos`, `llabelDoubleUm`, - `llabelDoubleMuitos`, …). -- Generated `ERModel_v01.xml` and `ERModel_v01.png` as described above, in Chen - notation: 8 entity sets, 10 relationships, 57 attributes, cardinality labels - on every relationship line and double lines for total participation. -- Proposed the three changes to the initial model recorded in the model history. -- Rewrote [ERModel](ERModel.md) with the per-entity documentation, candidate-key - justifications and attribute types the phase template requires. - ---- - -> **Student action required.** Two things, in this order: -> -> 1. Open `ERModel_v01.xml` in TerraER, read the whole diagram, and change what -> you disagree with. Save the result as `ERModel_v02.xml` with a matching PNG -> and add a history line. The phase rules require that the model be yours; -> the generated v01 is a starting point to review and take over, not an -> answer to submit unread. -> 2. Verify that this log matches your recollection and append the full text of -> any further prompts. The complete transcript is in your Claude Code session -> history. Index: -ConceptualModel/ERModel_v01.xml =================================================================== --- P1-ConceptualModel/ERModel_v01.xml (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,1 +1,0 @@ -CryptosMarketsMarketTradesMarketCandlesOrdersTransactionsUsersWatchlistsQuotedOnFillsAggregatesPlacedOnSettlesRecordsPlacesHoldsOwnsContains

3

3

3

3

3

3

3

created_at

name

symbol

truetrueid

created_at

is_active

quote_currency

truetrueid

truetrueid

executed_at

price

quantity

side

source

truetrueid

timeframe

open

high

low

close

volume

candle_time

truetrueid

side

type

status

quantity

price

placed_at

executed_at

truetrueid

type

amount

currency

created_at

description

truetrueid

username

email

full_name

password_hash

available_balance

invested_balance

created_at

updated_at

created_at

name

truetrueid

quantity

5avg_price

created_at

updated_at

added_at

Index: -ConceptualModel/ep-diagram.md =================================================================== --- P1-ConceptualModel/ep-diagram.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,197 +1,0 @@ -Ентитети - -User – ентитет кој чува информации за корисниците на платформата - -id (uuid) - -username (varchar(50), not null, unique) - -email (varchar(255), not null, unique) - -password_hash (varchar(255), not null) - -full_name (varchar(200)) - -created_at (timestamptz) - -updated_at (timestamptz) - - -Crypto – ентитет кој ги чува податоците за криптовалутите со кои се тргува - -id (uuid) - -symbol (varchar(20), not null, unique) - -name (varchar(255)) - -created_at (timestamptz) - - - -Market – ентитет кој ги дефинира пазарите (парови криптовалути и валути за котација(BTCUSDT, ADAUSDT, ADABTC)) - -id (uuid) - -crypto_id (uuid, not null, FK → Crypto.id) - -quote_currency (char(3), not null) ADA, USDT, BTC, USDT - -is_active (boolean) - -created_at (timestamptz) - - - -Holding – ентитет кој ги чува позициите на корисниците во криптовалути - -id (uuid) - -user_id (uuid, not null, FK → User.id) - -crypto_id (uuid, not null, FK → Crypto.id) - -quantity (numeric(20,4), not null) - -avg_price (numeric(18,6)) - -created_at (timestamptz) - -updated_at (timestamptz) - - - -Order – ентитет кој ги чува нарачките за купување/продавање - -id (uuid) - -user_id (uuid, not null, FK → User.id) - -market_id (uuid, not null, FK → Market.id) - -side (varchar(4), not null) — типично “buy” или “sell” - -type (varchar(20), not null) — тип на нарачка, пример “limit”, “market” - -status (varchar(20), not null) — статус на нарачка (open, executed, cancelled) - -quantity (numeric(20,4), not null) - -price (numeric(18,6)) - -placed_at (timestamptz) - -executed_at (timestamptz) - - - -Transaction – ентитет кој ја следи финансиската активност на корисниците (депозити, купувања, продавања, провизии) - -id (uuid) - -user_id (uuid, not null, FK → User.id) - -type (varchar(50), not null) — пример: “deposit”, “buy”, “sell”, “fee” - -amount (numeric(18,4), not null) - -currency (char(3), not null) - -related_order (uuid, FK → Order.id) - -created_at (timestamptz) - -description (text) - - - -Market_Trade – ентитет кој ги евидентира извршените пазарни зделки кои ја формираат цената - -id (bigserial) - -market_id (uuid, not null, FK → Market.id) - -executed_at (timestamptz, not null) - -price (numeric(18,6), not null) - -quantity (numeric(20,6), not null) - -side (varchar(4)) - -source (varchar(50)) - -Market_Candle – ентитет кој ги агрегира податоците за цените и волуменот во временски интервали (целови) - -id (bigserial) - -market_id (uuid, not null, FK → Market.id) - -timeframe (varchar(5), not null) — пример: “1m”, “5m”, “1h” - -open (numeric(18,6), not null) - -high (numeric(18,6), not null) - -low (numeric(18,6), not null) - -close (numeric(18,6), not null) - -volume (numeric(20,6), not null) - -candle_time (timestamptz, not null) - -Watchlist – ентитет кој ги чува листите за следење на корисниците - -id (uuid) - -user_id (uuid, not null, FK → User.id) - -name (varchar(100)) - -created_at (timestamptz) - -Watchlist_Item – ентитет кој ги чува криптовалутите кои се ставени во некоја листа за следење - -id (uuid) - -watchlist_id (uuid, not null, FK → Watchlist.id) - -crypto_id (uuid, not null, FK → Crypto.id) - -added_at (timestamptz) - -Релации - -User – Holding (1:N) -Еден корисник може да има повеќе позиции во различни криптовалути. - -Crypto – Holding (1:N) -Една криптовалута може да се држи од повеќе корисници. - -User – Order (1:N) -Еден корисник може да има повеќе нарачки. - -Market – Order (1:N) -Еден пазар има повеќе нарачки. - -User – Transaction (1:N) -Еден корисник има повеќе трансакции. - -Order – Transaction (0..1 : N) -Трансакцијата може да биде поврзана со една нарачка (пример купување/продажба). - -Market – Market_Trade (1:N) -Еден пазар има многу извршени зделки. - -Market – Market_Candle (1:N) -Еден пазар има многу временски агрегирани податоци. - -User – Watchlist (1:N) -Еден корисник може да има повеќе листи за следење. - -Watchlist – Watchlist_Item (1:N) -Една листа може да содржи повеќе криптовалути. - -Crypto – Watchlist_Item (1:N) -Една криптовалута може да биде во повеќе листи за следење. Index: -RelationalDesign/RelationalDesign.md =================================================================== --- P2-RelationalDesign/RelationalDesign.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,137 +1,0 @@ -# Relational Design - -## Descriptive representation of the relational schema - -Notation: **bold** = primary key, *italic* = foreign key. - -- **Users**(**id**, username, email, full_name, password_hash, available_balance, invested_balance, created_at, updated_at) - - Candidate keys: `{id}`, `{username}`, `{email}`. `UNIQUE(username)`, `UNIQUE(email)`. -- **Crypto**(**id**, symbol, name, created_at) - - Candidate keys: `{id}`, `{symbol}`. `UNIQUE(symbol)`. -- **Markets**(**id**, *crypto_id*, quote_currency, is_active, created_at) - - Candidate keys: `{id}`, `{crypto_id, quote_currency}`. `UNIQUE(crypto_id, quote_currency)`. -- **Holdings**(**id**, *user_id*, *crypto_id*, quantity, avg_price, created_at, updated_at) - - Transformation of the M:N relationship `Holds`. Candidate keys: `{id}` and - `{user_id, crypto_id}` — the latter is the relationship's own key and is - enforced with `UNIQUE(user_id, crypto_id)`. `id` was chosen as PK for - consistency with the other relations. - - `avg_price` is `NOT NULL DEFAULT 0 CHECK (avg_price >= 0)`. -- **Orders**(**id**, *user_id*, *market_id*, side, type, status, quantity, price, placed_at, executed_at) - - `side ∈ {buy, sell}`, `type ∈ {market, limit}`, `status ∈ {open, executed, cancelled}`. -- **Transactions**(**id**, *user_id*, type, amount, currency, *related_order*, created_at, description) - - `type ∈ {deposit, buy, sell, fee}`. -- **MarketTrades**(**id**, *market_id*, executed_at, price, quantity, side, source) -- **MarketCandles**(**id**, *market_id*, timeframe, open, high, low, close, volume, candle_time) - - `UNIQUE(market_id, timeframe, candle_time)`. -- **Watchlists**(**id**, *user_id*, name, created_at) -- **WatchlistItems**(**id**, *watchlist_id*, *crypto_id*, added_at) - - Transformation of the M:N relationship `Contains`. Candidate keys: `{id}` - and `{watchlist_id, crypto_id}`, the latter enforced with - `UNIQUE(watchlist_id, crypto_id)`. - -### Transformation method used - -**Partial transformation.** Applied as follows: - -- Each of the 8 entity sets in [ERModel](../P1-ConceptualModel/ERModel.md) becomes one table, keeping - its UUID (or serial) primary key. -- Each **1:N relationship without attributes** is transformed by adding the - parent's primary key as a foreign-key column on the child table — the "N" - side. This is where every foreign key in the schema comes from, and it is why - no foreign keys appear in the ER diagram itself: - `QuotedOn` → `markets.crypto_id`, `PlacedOn` → `orders.market_id`, - `Places` → `orders.user_id`, `Records` → `transactions.user_id`, - `Settles` → `transactions.related_order`, `Fills` → `market_trades.market_id`, - `Aggregates` → `market_candles.market_id`, `Owns` → `watchlists.user_id`. -- Each **M:N relationship** becomes its own table holding the two foreign keys - plus the relationship's own attributes: `Holds` → `holdings`, - `Contains` → `watchlist_items`. The pair of foreign keys is the relationship's - key and is enforced as a `UNIQUE` constraint in both tables. -- **Total participation** in the ER model becomes `NOT NULL` on the - corresponding foreign key; partial participation stays nullable. `Settles` is - partial on both sides, which is exactly why `transactions.related_order` is - the one nullable foreign key in the schema — a deposit has no originating - order. - -### Normalisation - -All relations are in **3NF**: - -- Every attribute is atomic (no repeating groups, no composite fields). -- No partial dependency exists because every primary key is a single UUID column. -- No transitive dependency exists: every non-key attribute depends directly on the row identifier. For example, `holdings.quantity` depends on `holdings.id`, not on `user_id` via some intermediate. -- `avg_price` in `Holdings` is a **derived value** cached for performance (it is - the weighted-average entry price across all `buy` transactions for that - `(user, crypto)` pair) — it is drawn as a derived attribute in the ER diagram. - We accept the denormalisation: it is recomputed by the database inside the same - transaction as each buy, in the same statement that changes the quantity - (`INSERT … ON CONFLICT (user_id, crypto_id) DO UPDATE`), so the stored average - and the stored quantity can never disagree. -- `avg_price` is declared `NOT NULL DEFAULT 0`. This matters: it is used in the - P/L arithmetic of `v_portfolio`, and in SQL any arithmetic involving `NULL` - yields `NULL`, so a nullable average would have silently blanked the - unrealised-P/L column for an existing position instead of failing loudly. - -## DDL script - -The script that creates the entire schema is [`../server/db/schema_creation.sql`](../../server/db/schema_creation.sql). It is idempotent: it drops and recreates the `project` schema every run, so it works on an empty database and on a database that already has the schema. - -The script creates: -- 10 tables with check constraints, primary keys, foreign keys and unique constraints. -- 5 performance indexes. -- 2 views: `v_latest_prices` (latest trade price per market) and `v_portfolio` (per-user holdings valuation with unrealised P/L). - -## DML script (sample data) - -The script that loads realistic sample data is [`../server/db/data_load.sql`](../../server/db/data_load.sql). It is idempotent: it truncates all tables with `CASCADE` then re-inserts. Loaded: -- 5 crypto assets (BTC, ETH, ADA, SOL, DOGE) and 5 USD-quoted markets. -- 3 sample users (`alice`, `bob`, `charlie`) with password `test123` (sha256 hex). -- 18 recent market trades across all markets so `v_latest_prices` is populated. -- 10 one-hour candles (BTC and ETH). -- One fully-executed market-buy order for Alice, the matching holding, and two ledger entries (deposit + buy), with Alice's balances updated accordingly. -- Two watchlists with five watchlist items. - -## Relational diagram - -![relational_schema](relational_schema.jpg) - -Generated in **DBeaver** from the **live** `project` schema, in crow's-foot -notation — not drawn by hand, so it is evidence that the deployed database -actually matches the design described above. Each box is a table with its -columns and declared types; key icons mark primary keys and the arrowed lines -are the 12 declared foreign keys. - -### How to regenerate it - -**With DBeaver** (the tool the course recommends): - -1. Connect to the assigned FINKI PostgreSQL project database. -2. Double-click the `project` schema → **ER Diagram** tab. -3. Right-click in the diagram → **Notation** → **Crow's foot**. -4. Arrange the tables to mirror the layout of - [`ERModel_v01.png`](../P1-ConceptualModel/ERModel_v01.png). -5. Right-click → **Export diagram** → save as `relational_schema.jpg`. - -On Linux, install it with: - -```sh -flatpak remote-add --if-not-exists --user flathub https://dl.flathub.org/repo/flathub.flatpakrepo -flatpak install --user flathub io.dbeaver.DBeaverCommunity -# or: sudo snap install dbeaver-ce -``` - -**With pgAdmin 4**, if DBeaver is unavailable — it reads the live schema the same -way, so the result is equivalent in substance: - -1. Connect to the project database. -2. Right-click the database → **ERD For Database** (or open a blank ERD and drag - the `project` tables in). -3. Arrange the tables to mirror `ERModel_v01.png`. -4. **Download image** → PNG, then convert: - `convert relational_schema.png relational_schema.jpg` - -Whichever tool is used, state it here so the choice is explicit rather than -inferred. Do **not** substitute a tool that only reads the `.sql` file — such as -dbdiagram.io — because the diagram would then show what the script says rather -than what the deployed database contains, which is the thing this artefact is -meant to demonstrate. Index: -RelationalDesign/RelationalDesignAIUsage.md =================================================================== --- P2-RelationalDesign/RelationalDesignAIUsage.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,70 +1,0 @@ -# Relational Design AI Usage - -## Name of AI service/solution that was used - -**Claude Code** (Anthropic) - -- **URL:** https://claude.com/claude-code -- **Type of service/subscription:** Claude subscription, model Claude Opus 4.7 (1M context). - -## Final result - -### Diagram - -The student produces `relational_schema.jpg` in DBeaver from the live `project` schema; see [RelationalDesign](RelationalDesign.md) for instructions. - -### Results in details / description - -The AI: - -- Consolidated two inconsistent draft schemas (`server/db/db.sql` and `server/db/schema.sql`) into a single `schema_creation.sql`. -- Corrected foreign-key errors in the original (the `crypto_id` column in `holdings`, `orders`, and `transactions` had been pointed at both `users(id)` and `crypto(id)`; the AI split it into separate `user_id` and `crypto_id` columns per `ep-diagram.md`). -- Added two convenience views, `v_latest_prices` and `v_portfolio`, to keep the Go CLI simple. -- Produced a sample-data script `data_load.sql` that TRUNCATEs and re-inserts deterministic rows, so the "must work on empty DB and on a DB that already has data" requirement from P2 is met. -- Documented normalisation up to 3NF and the intentional denormalisation of `holdings.avg_price`. - -## Summary of AI involvement - -| | Session 1 — 2026-04-21 | Session 2 — 2026-08-06/07 | -|---|---|---| -| **What I brought** | My own draft SQL (`db.sql`, `schema.sql`) and the model in `ep-diagram.md` | The schema as it stood after session 1 | -| **What the AI did** | Reviewed my SQL, found the foreign-key errors, consolidated two inconsistent drafts into one script | Reviewed the schema again; one constraint change, plus documentation of the transformation | -| **What I decided** | Which corrections to adopt, to keep both balance columns, to drop the secret-question fields | To make `avg_price` `NOT NULL` rather than handle nulls in application code | - -The relational model in this phase is a transformation of *my* ER model, and the -foreign-key errors the AI found in session 1 were errors in *my* draft SQL — that -review is the single most useful thing the AI did on this phase. - -## Entire AI usage log - -See [ERModelAIUsage](../P1-ConceptualModel/ERModelAIUsage.md) — the full transcript of the 2026-04-21 conversation covers both P1 and P2 work. The specific prompts that drove the relational-design output were the same "make it work and make it fill in or to follow all of the needed instructions" instruction and the student's subsequent "do everything that you need to do". - -> **Student action required:** append any future consultations where you asked the AI to refine the schema, tune constraints, or write additional queries. - - -### Session 2 — 2026-08-06 / 2026-08-07 - -Prompts are logged in full in [ERModelAIUsage](../P1-ConceptualModel/ERModelAIUsage.md#session-2--2026-08-06--2026-08-07); -the one that drove this phase was *"Also fix some database things or golang -things if you think we can do it better"*. Changes to the P2 artefacts: - -- `holdings.avg_price` changed from nullable to `NOT NULL DEFAULT 0 CHECK - (avg_price >= 0)`. Reason: it feeds the P/L arithmetic in `v_portfolio`, and - SQL arithmetic involving `NULL` produces `NULL`, so a nullable average would - have silently blanked the unrealised-P/L column of a real position. A related - crash path in the Go code (scanning a `NULL` average into a non-nullable - `float64`, which was reported to the user as "Insufficient holding") was fixed - at the same time. -- [RelationalDesign](RelationalDesign.md) gained an explicit account of the - partial transformation: which ER construct each foreign key comes from, that - M:N relationships with attributes become tables whose foreign-key pair is a - `UNIQUE` constraint, and that total participation becomes `NOT NULL` — which is - why `transactions.related_order` is the one nullable foreign key. -- The candidate keys of `holdings` and `watchlist_items` are now documented as - the relationship keys `{user_id, crypto_id}` and `{watchlist_id, crypto_id}`. - -Both scripts were re-run end to end against PostgreSQL 16 after these changes. - -> **Still outstanding:** `relational_schema.jpg` must be exported from DBeaver -> against the faculty database. No AI involvement is possible there — it needs a -> live connection to your assigned database. Index: -UseCaseModel/UseCase0001.md =================================================================== --- P3-UseCaseModel/UseCase0001.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,40 +1,0 @@ -# Use-case 0001 — Register new account - -**Initiating actor:** Visitor - -**Other actors:** — - -A new person creates an account on EduBerza so they can later log in as a Trader. The system validates input, refuses duplicates, and stores a hashed password. - -## Scenario - -1. Visitor chooses "Register" from the anonymous menu. -2. System prompts for username, email, full name and password. -3. Visitor enters values. -4. System validates: - - username, email, password are non-empty. - - email contains `@`. - - password is at least 6 characters. -5. System checks whether the chosen username or email already exists: - - ```sql - SELECT EXISTS ( - SELECT 1 FROM project.users - WHERE username = $1 OR email = $2 - ); - ``` -6. If the row exists, system informs the user and the scenario ends. Otherwise it creates the account: - - ```sql - INSERT INTO project.users (username, email, full_name, password_hash, available_balance) - VALUES ($1, $2, $3, encode(digest($4, 'sha256'), 'hex'), 0); - ``` -7. System confirms success and returns to the anonymous menu; Visitor can then proceed to UC0002. - -### Alternate flow 3a — invalid email - -If step 4 fails email validation, system shows "Invalid email." and scenario returns to step 2. - -### Alternate flow 5a — duplicate - -If step 5 returns `true`, system shows "Username or email already taken." and scenario ends. Index: -UseCaseModel/UseCase0002.md =================================================================== --- P3-UseCaseModel/UseCase0002.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,34 +1,0 @@ -# Use-case 0002 — Log in - -**Initiating actor:** Visitor - -**Other actors:** — - -A registered user authenticates so the system can treat subsequent actions as a Trader. - -## Scenario - -1. Visitor chooses "Login" from the anonymous menu. -2. System prompts for username and password. -3. Visitor enters values. -4. System looks up the user: - - ```sql - SELECT id, password_hash - FROM project.users - WHERE username = $1; - ``` -5. If no row is returned, system responds "Invalid credentials." and scenario ends. -6. If a row is returned, system compares the stored hash against sha256 of the entered password. On mismatch it responds "Invalid credentials." and ends. -7. On match, system records the returned `id` and username in the session and displays the authenticated menu. - -### Alternate flow 4a — authenticated lookup with live balance - -The system may combine identity lookup with live balance in a single query, for use cases that need both: - -```sql -SELECT id, available_balance, invested_balance - FROM project.users - WHERE username = $1 - AND password_hash = encode(digest($2, 'sha256'), 'hex'); -``` Index: -UseCaseModel/UseCase0003.md =================================================================== --- P3-UseCaseModel/UseCase0003.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,44 +1,0 @@ -# Use-case 0003 — Deposit virtual funds - -**Initiating actor:** Trader - -**Other actors:** — - -A logged-in Trader tops up their virtual cash balance. This is a simulation-only operation; no real money changes hands. The operation writes to two tables — the user row and the ledger — inside a single transaction. - -## Scenario - -1. Trader chooses "Deposit virtual funds" from the authenticated menu. -2. System prompts for an amount in USD. -3. Trader enters an amount. -4. System validates: amount must parse as a positive number. -5. System opens a transaction and increments the balance: - - ```sql - BEGIN; - - UPDATE project.users - SET available_balance = available_balance + $1, - updated_at = now() - WHERE id = $2; - - INSERT INTO project.transactions (user_id, type, amount, currency, description) - VALUES ($2, 'deposit', $1, 'USD', 'Virtual deposit'); - - COMMIT; - ``` -6. System confirms "Deposited X USD." and returns to the authenticated menu. - -### Alternate flow 4a — invalid input - -If the amount is non-positive or non-numeric, system responds "Invalid amount." and scenario returns to step 2. - -### Verification query - -To see the balance after the deposit, the Trader can trigger UC0006, or directly: - -```sql -SELECT available_balance, invested_balance - FROM project.users - WHERE id = $1; -``` Index: -UseCaseModel/UseCase0004.md =================================================================== --- P3-UseCaseModel/UseCase0004.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,83 +1,0 @@ -# Use-case 0004 — Place market BUY order - -**Initiating actor:** Trader - -**Other actors:** Market Simulator (indirect — supplies the current price via `market_trades`). - -A Trader buys a crypto asset at the current market price. The operation touches five tables (`orders`, `users`, `holdings`, `transactions`, `market_trades`) and must either all succeed or all roll back. - -## Scenario - -1. Trader chooses "Place market BUY order". -2. System lists the available markets with their latest price: - - ```sql - SELECT m.id, c.symbol, m.quote_currency, COALESCE(lp.price, 0) - FROM project.markets m - JOIN project.crypto c ON c.id = m.crypto_id - LEFT JOIN project.v_latest_prices lp ON lp.market_id = m.id - WHERE m.is_active = true - ORDER BY c.symbol; - ``` -3. Trader enters a market symbol, e.g. `ETH`. -4. System resolves the market and looks up the latest price: - - ```sql - SELECT m.id, c.id AS crypto_id, c.symbol, m.quote_currency - FROM project.markets m - JOIN project.crypto c ON c.id = m.crypto_id - WHERE upper(c.symbol) = upper($1) AND m.is_active = true; - - SELECT price FROM project.v_latest_prices WHERE market_id = $2; - ``` -5. Trader enters a quantity. -6. System computes notional = quantity × price, opens a transaction, and does: - - ```sql - BEGIN; - - INSERT INTO project.orders - (user_id, market_id, side, type, status, quantity, price, executed_at) - VALUES - ($user_id, $market_id, 'buy', 'market', 'executed', $qty, $price, now()) - RETURNING id; -- captured as $order_id - - SELECT available_balance FROM project.users WHERE id = $user_id FOR UPDATE; - -- abort if available_balance < notional - - UPDATE project.users - SET available_balance = available_balance - $notional, - invested_balance = invested_balance + $notional, - updated_at = now() - WHERE id = $user_id; - - -- Upsert holding with running weighted-average price: - SELECT quantity, avg_price - FROM project.holdings - WHERE user_id = $user_id AND crypto_id = $crypto_id - FOR UPDATE; - - -- Either INSERT (new holding) or UPDATE (existing), computing - -- new_avg = (old_qty*old_avg + $qty*$price) / (old_qty + $qty) - - INSERT INTO project.transactions - (user_id, type, amount, currency, related_order, description) - VALUES - ($user_id, 'buy', -$notional, 'USD', $order_id, 'Market buy ...'); - - INSERT INTO project.market_trades - (market_id, executed_at, price, quantity, side, source) - VALUES - ($market_id, now(), $price, $qty, 'buy', 'user'); - - COMMIT; - ``` -7. System confirms: `Order executed: buy 0.0100 BTC @ 67140.000000 (notional 671.4000 USD)`. - -### Alternate flow 6a — insufficient funds - -If `available_balance < notional`, the entire transaction rolls back and system shows "Insufficient funds: need X, have Y." - -### Alternate flow 4a — market not found - -If the entered symbol does not match any active market, system shows "market X not found" and returns to the authenticated menu without opening a transaction. Index: -UseCaseModel/UseCase0005.md =================================================================== --- P3-UseCaseModel/UseCase0005.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,66 +1,0 @@ -# Use-case 0005 — Place market SELL order - -**Initiating actor:** Trader - -**Other actors:** Market Simulator (indirect — supplies the current price). - -A Trader sells part or all of a holding at the current market price. Cost basis is preserved so realised P/L can be reconstructed from the ledger. - -## Scenario - -1. Trader chooses "Place market SELL order". -2. System lists markets (same SQL as UC0004 step 2). -3. Trader enters market symbol and quantity. -4. System resolves the market and looks up the latest price (same SQL as UC0004 step 4). -5. System opens a transaction: - - ```sql - BEGIN; - - INSERT INTO project.orders - (user_id, market_id, side, type, status, quantity, price, executed_at) - VALUES - ($user_id, $market_id, 'sell', 'market', 'executed', $qty, $price, now()) - RETURNING id; -- $order_id - - SELECT quantity, avg_price - FROM project.holdings - WHERE user_id = $user_id AND crypto_id = $crypto_id - FOR UPDATE; - -- abort if row missing or quantity < $qty - ``` -6. If the holding check passes, system reduces the holding, credits cash and debits invested, and appends a ledger and a market trade: - - ```sql - UPDATE project.holdings - SET quantity = quantity - $qty, - updated_at = now() - WHERE user_id = $user_id AND crypto_id = $crypto_id; - - UPDATE project.users - SET available_balance = available_balance + $notional, - invested_balance = GREATEST(invested_balance - ($avg_price * $qty), 0), - updated_at = now() - WHERE id = $user_id; - - INSERT INTO project.transactions - (user_id, type, amount, currency, related_order, description) - VALUES - ($user_id, 'sell', $notional, 'USD', $order_id, 'Market sell ...'); - - INSERT INTO project.market_trades - (market_id, executed_at, price, quantity, side, source) - VALUES - ($market_id, now(), $price, $qty, 'sell', 'user'); - - COMMIT; - ``` -7. System confirms: `Order executed: sell 0.5000 ETH @ 3520.000000 (notional 1760.0000 USD)`. - -### Alternate flow 5a — insufficient holding - -If the `SELECT ... FOR UPDATE` returns no row, or the held quantity is smaller than the sell quantity, the entire transaction rolls back and system shows "Insufficient holding: trying to sell X, hold Y." - -### Realised P/L (post-scenario) - -The realised P/L for a sell is `$notional - ($avg_price * $qty)`. It is not persisted explicitly but can be computed from the ledger and the holding at sell time. Index: -UseCaseModel/UseCase0006.md =================================================================== --- P3-UseCaseModel/UseCase0006.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,66 +1,0 @@ -# Use-case 0006 — View portfolio and transaction history - -**Initiating actor:** Trader - -**Other actors:** — - -A Trader inspects their current holdings, unrealised P/L, cash balance and recent ledger. - -## Scenario - -### Portfolio - -1. Trader chooses "View portfolio". -2. System queries the `v_portfolio` view: - - ```sql - SELECT symbol, - quantity, - COALESCE(avg_price, 0), - COALESCE(current_price, 0), - COALESCE(market_value, 0), - COALESCE(unrealized_pnl, 0) - FROM project.v_portfolio - WHERE user_id = $1 - AND quantity > 0 - ORDER BY symbol; - ``` -3. System displays the rows and a computed summary: - - ```sql - SELECT available_balance, invested_balance - FROM project.users - WHERE id = $1; - ``` - -### Transaction history - -1. Trader chooses "View transaction history". -2. System queries the last 20 ledger entries: - - ```sql - SELECT created_at, type, amount, currency, COALESCE(description, '') - FROM project.transactions - WHERE user_id = $1 - ORDER BY created_at DESC - LIMIT 20; - ``` - -### Reference — how `v_portfolio` is defined - -```sql -CREATE OR REPLACE VIEW project.v_portfolio AS -SELECT h.user_id, - c.symbol, - h.quantity, - h.avg_price, - lp.price AS current_price, - (h.quantity * lp.price) AS market_value, - (h.quantity * (lp.price - h.avg_price)) AS unrealized_pnl - FROM project.holdings h - JOIN project.crypto c ON c.id = h.crypto_id - LEFT JOIN project.markets m - ON m.crypto_id = c.id AND m.quote_currency = 'USD' - LEFT JOIN project.v_latest_prices lp - ON lp.market_id = m.id; -``` Index: -UseCaseModel/UseCase0007.md =================================================================== --- P3-UseCaseModel/UseCase0007.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,63 +1,0 @@ -# Use-case 0007 — Manage watchlist - -**Initiating actor:** Trader - -**Other actors:** — - -A Trader keeps a list of crypto assets they want to monitor. Adding an asset that is already on the list is a no-op (idempotent). - -## Scenario - -1. Trader chooses "Manage watchlist". -2. System ensures the Trader has a default watchlist named "Favorites": - - ```sql - SELECT id FROM project.watchlists - WHERE user_id = $1 - ORDER BY created_at LIMIT 1; - - -- if no row: - INSERT INTO project.watchlists (user_id, name) - VALUES ($1, 'Favorites') - RETURNING id; - ``` -3. System shows the sub-menu: List / Add / Remove / Back. - -### List items - -```sql -SELECT c.symbol, c.name, COALESCE(lp.price, 0) - FROM project.watchlist_items wi - JOIN project.crypto c ON c.id = wi.crypto_id - LEFT JOIN project.markets m - ON m.crypto_id = c.id AND m.quote_currency = 'USD' - LEFT JOIN project.v_latest_prices lp - ON lp.market_id = m.id - WHERE wi.watchlist_id = $1 - ORDER BY c.symbol; -``` - -### Add a crypto - -```sql --- 1. resolve the symbol to a crypto_id -SELECT id FROM project.crypto WHERE upper(symbol) = upper($1); - --- 2. insert the item; do nothing if it's already there -INSERT INTO project.watchlist_items (watchlist_id, crypto_id) -VALUES ($watchlist_id, $crypto_id) -ON CONFLICT (watchlist_id, crypto_id) DO NOTHING; -``` - -### Remove a crypto - -```sql -DELETE FROM project.watchlist_items - WHERE watchlist_id = $1 - AND crypto_id = ( - SELECT id FROM project.crypto - WHERE upper(symbol) = upper($2) - ); -``` - -If the delete affects zero rows, system shows "Not in watchlist." Index: -UseCaseModel/UseCaseModel.md =================================================================== --- P3-UseCaseModel/UseCaseModel.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,34 +1,0 @@ -# Use-case model - -## List of Actors / Roles - -- **Visitor** — Anyone browsing the platform without an account. Can only view public market information. - - [UC0001](UseCase0001.md) — Register new account - - [UC0002](UseCase0002.md) — Log in - -- **Trader** — A logged-in user managing their virtual funds and positions. - - [UC0003](UseCase0003.md) — Deposit virtual funds - - [UC0004](UseCase0004.md) — Place market BUY order - - [UC0005](UseCase0005.md) — Place market SELL order - - [UC0006](UseCase0006.md) — View portfolio and transaction history - - [UC0007](UseCase0007.md) — Manage watchlist - -- **Market Simulator** — An external automated system (the bot in `bots/`) that inserts simulated trades and candles into the database so prices move in the simulation. - -## Use-case model diagram (optional) - -*(Optional per the rubric; include one later if time allows.)* - -## Realization details on selection of the most important use cases - -Solo project → **at least 3 use cases required** (rubric: "at least 3 per team-member"). **7 use cases documented** for a safety margin. All are implemented in the P4 prototype; see `server/` for the Go source and [PrototypeImplementation](../P4-Prototype/PrototypeImplementation.md) for documented runs. - -| Use case | Importance | Why documented | -|----------------------------------------|------------|-------------------------------------------------------------------------------------------| -| [UC0001 — Register](UseCase0001.md) | High | Without it nothing else works; demonstrates `INSERT` with uniqueness check. | -| [UC0002 — Login](UseCase0002.md) | High | Authenticates every `Trader` action; demonstrates `SELECT` with parameter binding. | -| [UC0003 — Deposit](UseCase0003.md) | High | Shows a multi-row transaction: `UPDATE users` + `INSERT INTO transactions`. | -| [UC0004 — Buy](UseCase0004.md) | Very high | Core of the exchange: `INSERT orders`, `UPDATE users`, `UPSERT holdings`, ledger, trade. | -| [UC0005 — Sell](UseCase0005.md) | Very high | Dual of Buy; demonstrates row-level `FOR UPDATE` locking and cost-basis bookkeeping. | -| [UC0006 — Portfolio](UseCase0006.md) | High | Demonstrates joins over `holdings`, `markets`, `crypto`, and a view (`v_portfolio`). | -| [UC0007 — Watchlist](UseCase0007.md) | Medium | Demonstrates N-M relation handling and `ON CONFLICT` upsert semantics. | Index: -UseCaseModel/UseCaseModelAIUsage.md =================================================================== --- P3-UseCaseModel/UseCaseModelAIUsage.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,56 +1,0 @@ -# Use-Case Model AI Usage - -## Name of AI service/solution that was used - -**Claude Code** (Anthropic) - -- **URL:** https://claude.com/claude-code -- **Type of service/subscription:** Claude subscription, model Claude Opus 4.7 (1M context). - -## Final result - -### Results in details / description - -The AI: - -- Proposed the actor taxonomy (Visitor, Trader, Market Simulator) from the project description and the existing Go code. -- Derived a set of 7 use cases covering the full trading loop (register, login, deposit, buy, sell, view portfolio, watchlist). -- Wrote each `UseCaseXXXX.md` file with: - - initiating actor, other actors, goals - - step-by-step dialog-form scenario - - **tested** SQL statements for every database-touching step, using the `project` schema - - alternate flows for the most common failure cases (insufficient funds, insufficient holding, duplicate user, invalid input) - -## Summary of AI involvement - -| | Session 1 — 2026-04-21 | Session 2 — 2026-08-06/07 | -|---|---|---| -| **What I brought** | The project description in `opis.md` and the existing Go backend | The use-case model as completed in session 1 | -| **What the AI did** | Proposed the actor taxonomy and drafted seven use cases with tested SQL | Nothing — the model was not changed | -| **What I decided** | To go solo, and therefore to document seven use cases rather than the three the rubric requires | To re-verify every scenario's SQL against a live database rather than trust the April run | - -This phase was finished in session 1. In session 2 the only work was -verification: each scenario was executed against a live PostgreSQL 16 database, -including the failure paths, and the results recorded on the -`UseCaseXXXXImplementation` pages. - -## Entire AI usage log - -See [ERModelAIUsage](../P1-ConceptualModel/ERModelAIUsage.md) for the full transcript — the same 2026-04-21 session produced the use-case documentation. The defining student prompt was: - -> I'm going solo do everything that you need to do, and tell me after what do I need to do - -which led the AI to pick "solo → 3 minimum per rubric → document 7 for a margin" as a default. - -> **Student action required:** append any future refinements of the use-case list or scenarios here. - - -### Session 2 — 2026-08-06 / 2026-08-07 - -No changes were made to the use-case model in this session: the actor list, the -seven use cases and the scenario SQL in `UseCase0001`–`UseCase0007` are as -produced on 2026-04-21. The SQL in those scenarios was, however, re-verified by -executing the corresponding prototype flows against a live PostgreSQL 16 -database, including the failure paths (insufficient funds, insufficient holding, -duplicate registration, wrong password). The results are documented per use case -on the `UseCaseXXXXImplementation` pages. Index: -Prototype/BuildInstructions.md =================================================================== --- P4-Prototype/BuildInstructions.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,194 +1,0 @@ -# Build Instructions - -How to compile, configure, run and test the EduBerza prototype. -Linked from [PrototypeImplementation](PrototypeImplementation.md). - -## Development environment description - -| Tool | Version tested | Needed for | -|-------------|-----------------------|---------------------------------------------------------------| -| Go | 1.26 (1.25+ works) | Building `server/` (the CLI) and `bots/` (the market bot). | -| PostgreSQL | 16 | The database. Docker image or the faculty server. | -| Docker | any recent | Optional — brings up a local PostgreSQL in one command. | -| `psql` | any | Optional — running the SQL scripts by hand. | -| Java | 21 (8+ works) | Optional — only to open/edit the ER diagram in TerraER. | -| DBeaver | any recent | Optional — only to export `relational_schema.jpg`. | - -Nothing else has to be installed. The only third-party Go dependency -(`github.com/lib/pq`, the PostgreSQL driver) is fetched automatically by -`go build` from `go.mod`/`go.sum`. - -## Build instructions - -All commands run from the repository root. - -### 1. Configure the database connection - -```sh -cp .env.example .env -``` - -The defaults in `.env.example` match the bundled Docker setup. To use the -faculty database instead, either edit `.env` or pass the values as real -environment variables — those take precedence over the file: - -```sh -DBHOST=... DBPORT=5432 DBUSER=... DBPASSWORD=... DBNAME=... ./eduberza -``` - -`.env` is deliberately not committed (see `.gitignore`) because it holds a -password. - -### 2. Start PostgreSQL - -```sh -docker compose up -d -``` - -Skip this step if you are pointing at the faculty database. - -### 3. Build - -```sh -go build -o eduberza ./server -``` - -### 4. Create the schema and load the sample data - -```sh -./eduberza -init -``` - -This runs `server/db/schema_creation.sql` and then `server/db/data_load.sql`. -Both are **compiled into the binary** (`go:embed`), so `-init` works regardless -of which directory you launch it from. It is destructive and idempotent — it -drops and recreates the whole `project` schema, so it is also the reset button -if a demo goes wrong. To reload only the data, keeping the schema: - -```sh -./eduberza -load-data -``` - -The equivalent with `psql`, if you prefer to watch the statements run: - -```sh -psql "postgresql://$DBUSER:$DBPASSWORD@$DBHOST:$DBPORT/$DBNAME" \ - -f server/db/schema_creation.sql -psql "postgresql://$DBUSER:$DBPASSWORD@$DBHOST:$DBPORT/$DBNAME" \ - -f server/db/data_load.sql -``` - -### 5. Run the prototype - -```sh -./eduberza -``` - -Seed accounts — all with the password `test123`: - -| Username | Starting state | -|-----------|-------------------------------------------------------| -| `alice` | 8250.00 USD cash, holds 0.5 ETH — best demo account | -| `bob` | 5000.00 USD cash, no positions | -| `charlie` | 2500.00 USD cash, no positions | - -### 6. Optional — run the market simulation bot - -In a second terminal: - -```sh -go run ./bots -``` - -The bot walks the price of every active market, inserts a row into -`market_trades` on each tick and upserts the current 1-minute candle. Prices in -the CLI change while it runs, because the current price is always read from the -most recent trade (`v_latest_prices`), never from a stored column. - -## Testing instructions - -### Mini-guide to the application - -The CLI has two menus. Before logging in: **Register**, **Login**, -**Browse markets**. After logging in: **View balance**, **Deposit virtual -funds**, **Browse markets**, **Place market BUY order**, **Place market SELL -order**, **View portfolio**, **View transaction history**, **Manage watchlist**, -**Logout**. - -You never have to remember an identifier. Markets are always printed as a -numbered list with their current price before you are asked which one you want, -and assets are referred to by symbol (`BTC`, `ETH`, …), never by database id. - -### End-to-end smoke test - -Verified on 2026-08-07 against PostgreSQL 16 with freshly loaded sample data. -Expected values are exact. - -1. `./eduberza -init` — prints `Database initialised.` -2. `./eduberza`, then `[2] Login` → `alice` / `test123` → `Login successful.` -3. `[6] View portfolio` → one row: `ETH 0.5000` at avg 3500.000000, current - 3520.000000, value 1760.0000, unrealised P/L `+10.0000`. Cash available - 8250.0000, net worth 10010.0000. -4. `[4] Place market BUY order` → `BTC` → `0.01` → - `Order executed: buy 0.0100 BTC @ 67140.000000 (notional 671.4000 USD)`. -5. `[6] View portfolio` → now BTC *and* ETH, total value 2431.4000, cash - 7578.6000 (= 8250.00 − 671.40), net worth still 10010.0000. -6. `[5] Place market SELL order` → `ETH` → `0.5` → - `Order executed: sell 0.5000 ETH @ 3520.000000 (notional 1760.0000 USD)`. -7. `[7] View transaction history` → deposit, buy, buy, sell, newest first. -8. `[8] Manage watchlist` → `[1] List items` → alice's `Favorites` contains - BTC, ETH, SOL with live prices. -9. `[9] Logout`, then `[0] Exit`. - -### Testing the failure paths - -These matter more than the happy path, because they are what proves the -transactions actually roll back: - -- **Insufficient funds:** log in as `charlie` (2500 USD) and try to buy `1` BTC. - Expect `Insufficient funds: need 67140.0000, have 2500.0000` and *no* change - to any table — no order row, no ledger entry, no holding. -- **Insufficient holding:** as `bob` (no positions), try to sell `1` ETH. - Expect `Insufficient holding: trying to sell 1.0000, hold 0.0000`. -- **Duplicate registration:** register with username `alice`. Expect - `Username or email already taken.` -- **Wrong password:** log in as `alice` with any wrong password. Expect - `Invalid credentials.` — and note the same message for an unknown username, so - the prototype does not leak which accounts exist. - -### For the public presentation - -Demo with `alice` (already has a position, so the portfolio screen is not -empty), and register a brand-new account live to show UC0001. Run the bot in a -background terminal so the prices visibly move between two portfolio refreshes. - -## Editing the ER diagram - -TerraER is a third-party tool and is deliberately **not** committed to this -repository. Download the teacher's build from - and run it: - -```sh -java -jar TerraER3.11.jar # then File → Open → docs/ERModel_v01.xml -``` - -Save new versions as `ERModel_v02.xml`, `ERModel_v03.xml`, … and export a -matching PNG for each. TerraER does not add the extension itself — type -`.xml` explicitly or the file will not reopen. - -## Up-to-date source code - -The repository is pushed to the FINKI DEVELOP git server; see the Repositories -section in EPRMS for the clone URL and credentials. - -### About the source code - -- All source needed to run the prototype is in this repository: the CLI - (`server/`), the market bot (`bots/`), the DDL script and the sample-data - script (`server/db/`). -- Third-party Go libraries are **not** vendored — `go build` downloads - `github.com/lib/pq` using the pinned versions in `go.mod` and `go.sum`. -- Third-party executables are **not** committed. `.gitignore` excludes `*.jar`; - TerraER is downloaded from the URL above. -- No third-party images, styles or frameworks are used, and there are no - images in the prototype at all — the interface is text. Index: -Prototype/PrototypeImplementation.md =================================================================== --- P4-Prototype/PrototypeImplementation.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,58 +1,0 @@ -# Prototype Implementation - -The prototype is a Go command-line application in `server/` that works against -the `project` schema in PostgreSQL. It implements all seven use cases from -[UseCaseModel](../P3-UseCaseModel/UseCaseModel.md) — the rubric requires at least three — with -every database access shown as real, executed SQL. An auxiliary program in -`bots/` simulates a live market so prices move while the prototype is running. - -Build, configure, run and test instructions: [BuildInstructions](BuildInstructions.md). - -## Implemented use-cases - -| Page | Use-case | Source | -|------|----------|--------| -| [UseCase0001Implementation](UseCase0001Implementation.md) | Register a new account | `server/auth.go` | -| [UseCase0002Implementation](UseCase0002Implementation.md) | Log in | `server/auth.go` | -| [UseCase0003Implementation](UseCase0003Implementation.md) | Deposit virtual funds | `server/account.go` | -| [UseCase0004Implementation](UseCase0004Implementation.md) | Place market BUY order | `server/trade.go` | -| [UseCase0005Implementation](UseCase0005Implementation.md) | Place market SELL order | `server/trade.go` | -| [UseCase0006Implementation](UseCase0006Implementation.md) | View portfolio and history | `server/portfolio.go` | -| [UseCase0007Implementation](UseCase0007Implementation.md) | Manage watchlist | `server/watchlist.go` | - -Each page mirrors its P3 use-case page and adds the actual SQL emitted by the Go -code plus a screenshot of the corresponding run against the live database. - -## What the prototype demonstrates about the database design - -- **The current price is never stored as a column.** It is always the price of - the most recent row in `market_trades`, read through the `v_latest_prices` - view. Both the user's own fills and the bot's simulated trades feed the same - table, so there is exactly one definition of "the price". -- **Money movements are transactional.** Buying touches five tables — `orders`, - `users`, `holdings`, `transactions`, `market_trades` — inside one transaction. - A failed balance check rolls the whole thing back: after a rejected purchase - there is no order row, no ledger entry and no holding. This is verified in the - failure-path tests in [BuildInstructions](BuildInstructions.md). -- **Constraints do real work.** `UNIQUE (user_id, crypto_id)` on `holdings` is - what makes the `INSERT … ON CONFLICT DO UPDATE` upsert possible, so the - weighted-average entry price is recomputed by the database in one statement - instead of by a read-modify-write in application code. -- **No identifiers are ever typed.** Markets are listed with their prices before - any choice is made, and everything else is selected by symbol. - -## Known limitations - -Deliberately out of scope for a first prototype, and the natural content of the -later phases: - -- Only `market` orders execute. `limit` is accepted by the schema - (`orders.type`) but the matching logic is not implemented. -- Passwords are SHA-256 without a salt. Adequate to demonstrate that the - password itself is never stored; not adequate for real use. A proper - password hash belongs in P9 (security). -- Money is handled as `float64` in Go while the database columns are `numeric`. - All arithmetic that must be exact — the weighted average — is done in SQL for - that reason, but the Go side would need a decimal type for real use. -- There is no connection pooling configuration and no explicit isolation level; - both are P8 topics. Index: -Prototype/PrototypeImplementationAIUsage.md =================================================================== --- P4-Prototype/PrototypeImplementationAIUsage.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,117 +1,0 @@ -# Prototype Implementation AI Usage - -## Name of AI service/solution that was used - -**Claude Code** (Anthropic) - -- **URL:** https://claude.com/claude-code -- **Type of service/subscription:** Claude subscription, model Claude Opus 4.7 (1M context). - -## Final result - -### Results in details / description - -During the 2026-04-21 session the AI: - -- Replaced the broken HTTP + frontend scaffolding (which the student had already decided to drop) with a single-binary CLI prototype in Go, split across `main.go`, `cli.go`, `auth.go`, `account.go`, `market.go`, `trade.go`, `portfolio.go`, `watchlist.go`. -- Consolidated the broken `db.go` — which used to drop every table on every startup — into a single `Connect()` plus explicit `-init` and `-load-data` flags. -- Moved `go.mod` from `server/` to the project root so the same module graph covers both `server/` and `bots/`. -- Fixed `go.mod`: removed the unused MySQL driver, promoted `github.com/lib/pq` to a direct dependency. -- Implemented the trade flows as real database transactions with row-level `FOR UPDATE` locks, cost-basis bookkeeping, and a ledger row per operation. -- Wrote a 140-line market-simulation bot that inserts trades and upserts 1-minute candles every tick. -- Exercised the full prototype end-to-end against a running PostgreSQL on port 5433 and verified a sample buy and portfolio view produced the expected numbers. - -### Test evidence - -``` -Order executed: buy 0.0100 BTC @ 67140.000000 (notional 671.4000 USD) - - Symbol Quantity Avg buy Current Value Unrealised P/L - BTC 0.0100 67140.000000 67140.000000 671.4000 +0.0000 - ETH 0.5000 3500.000000 3520.000000 1760.0000 +10.0000 - TOTAL 2431.4000 +10.0000 - - Cash available : 7578.6000 USD - Portfolio value: 2431.4000 USD - Net worth : 10010.0000 USD -``` - -## Summary of AI involvement - -| | Session 1 — 2026-04-21 | Session 2 — 2026-08-06/07 | -|---|---|---| -| **What I brought** | My existing Go backend (Chi HTTP handlers) and a half-finished frontend | The CLI prototype as it stood after session 1 | -| **What the AI did** | Rewrote the backend as a CLI covering UC0001–UC0007, wrote the market bot | Reviewed the code, found and fixed three bugs, improved the holding upsert | -| **What I decided** | To delete the frontend, to build a CLI rather than a web app, to keep the market simulator | To ask for a code review pass rather than documentation alone | - -The prototype was built in session 1 and worked. What session 2 added was a -review pass I asked for specifically because I have to defend this code in -person: it turned up a path-resolution bug that made my own documented build -instructions fail, an infinite loop at end of input, and an error check in the -wrong order that misreported database failures as "Insufficient holding". - -## Entire AI usage log - -See [ERModelAIUsage](../P1-ConceptualModel/ERModelAIUsage.md) for the full prompt/response transcript of the 2026-04-21 session — that single conversation produced all of the P1–P4 artefacts. The defining student prompt for P4 was: - -> do everything that you need to do, and tell me after what do I need to do - -which translated into the rewrite of the backend and the writing of this documentation. - -> **Student action required:** append any further prompts here where you asked the AI to debug, extend, or refactor the prototype. - - -### Session 2 — 2026-08-06 / 2026-08-07 - -Prompts are logged in full in [ERModelAIUsage](../P1-ConceptualModel/ERModelAIUsage.md#session-2--2026-08-06--2026-08-07). -The student asked the AI to complete the phase documentation and to *"fix some -database things or golang things if you think we can do it better"*. What the AI -changed in the prototype, and why: - -**Bugs found and fixed** - -1. `server/db/db.go` resolved `../.env` and `db/schema_creation.sql` as paths - relative to the working directory, so they only worked when the program was - started from inside `server/`. Following the documented instructions — build - from the repository root and run `./eduberza -init` — failed with - `password authentication failed for user "postgres"`, because the `.env` file - was never found and the defaults were used. The two SQL scripts are now - compiled into the binary with `go:embed`, and `.env` is located by searching - the working directory and every parent. Real environment variables now take - precedence over the file, which is what allows the prototype to be pointed at - the faculty database without editing anything. -2. `prompt()` in `server/cli.go` ignored the error from `ReadString`. At end of - input — Ctrl-D, or a scripted run — it returned an empty string forever and - the menu loop spun printing "Unknown option." without end. It now exits - cleanly. -3. On the sell path, `trade.go` checked `err == sql.ErrNoRows || held < qty` - before checking for other errors, so any scan failure was reported to the user - as "Insufficient holding" regardless of the real cause. The error check now - comes first. - -**Improvements** - -4. The holding upsert was a read-modify-write in Go (`SELECT ... FOR UPDATE`, - compute the new weighted average in `float64`, then `INSERT` or `UPDATE`). It - is now a single `INSERT … ON CONFLICT (user_id, crypto_id) DO UPDATE`, so the - average is recomputed by PostgreSQL in `numeric` arithmetic and the statement - relies on the unique constraint that the relational design already declared. -5. `TerraER3.11.jar` was removed from the repository and `.gitignore` now - excludes `*.jar`, because P4 requires third-party executables to be - downloaded rather than committed. `.env` is excluded too and `.env.example` - was added in its place. -6. `image.png`, a TradingView screenshot, was deleted — the project has no - licence to publish it and P4 requires explicit usage rights. - -**Verification** - -All seven use cases were executed against a live PostgreSQL 16 database. The -screenshots on the `UseCaseXXXXImplementation` pages are captures of those runs. -The four failure paths were tested, and the rollback behaviour was checked -directly in SQL: after a rejected purchase, the affected user has zero rows in -`orders`, `transactions` and `holdings`. - -> **Student action required:** read the changed files (`server/db/db.go`, -> `server/cli.go`, `server/trade.go`, `server/db/schema_creation.sql`) before the -> presentation. You will be asked how the buy transaction works, and the answer -> has to be yours. Index: -Prototype/UseCase0001Implementation.md =================================================================== --- P4-Prototype/UseCase0001Implementation.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,43 +1,0 @@ -# Use-case 0001 Implementation — Register - -**Initiating actor:** Visitor. **Source file:** `server/auth.go`, function `Register`. - -## Scenario (implemented) - -1. **User** chooses option `[1] Register` from the anonymous menu. - - -2. **System** prompts for username, email, full name and password (`server/auth.go:20-23`). - -3. **User** enters the values. - -4. **System** validates (empty checks, `@` in email, ≥ 6 chars in password) and then asks the database: - - ```sql - SELECT EXISTS( - SELECT 1 FROM users WHERE username = $1 OR email = $2 - ); - ``` - - -5. **System** inserts the new row (password hashed in Go, not in SQL, to keep hashing identical between register and login): - - ```sql - INSERT INTO users (username, email, full_name, password_hash, available_balance) - VALUES ($1, $2, $3, $4, 0); - ``` - -6. **System** confirms: `Account created. You can now log in.` - - ![Registering a new account](screenshots/uc0001_register.png) - -## How to reproduce - -```sh -./eduberza -init # optional: reset to a known state -./eduberza -# choose [1] Register, then enter username / email / full name / password -``` - -The screenshot above is from an actual run: registering `marko` / -`marko@example.com`, followed by the confirmation line. Index: -Prototype/UseCase0002Implementation.md =================================================================== --- P4-Prototype/UseCase0002Implementation.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,32 +1,0 @@ -# Use-case 0002 Implementation — Log in - -**Initiating actor:** Visitor. **Source file:** `server/auth.go`, function `Login` + `authenticate`. - -## Scenario (implemented) - -1. **User** chooses option `[2] Login`. - - -2. **System** prompts for username and password. - -3. **User** enters `alice` / `test123`. - -4. **System** looks up the user and compares hashes: - - ```sql - SELECT id, password_hash FROM users WHERE username = $1; - ``` - - The Go side (`authenticate` in `server/auth.go`) compares the returned `password_hash` against `sha256hex(entered_password)`. - -5. **System** on success stores `{UserID, Username}` in the in-process `Session` and shows the authenticated menu. - - ![A rejected login followed by a successful one](screenshots/uc0002_login.png) - -## Seed credentials - -| Username | Password | Balance | -|----------|----------|---------| -| `alice` | `test123` | 10000.00 USD | -| `bob` | `test123` | 5000.00 USD | -| `charlie` | `test123` | 2500.00 USD | Index: -Prototype/UseCase0003Implementation.md =================================================================== --- P4-Prototype/UseCase0003Implementation.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,37 +1,0 @@ -# Use-case 0003 Implementation — Deposit - -**Initiating actor:** Trader. **Source file:** `server/account.go`, function `Deposit`. - -## Scenario (implemented) - -1. **User** chooses `[2] Deposit virtual funds`. -2. **System** prompts for an amount in USD. -3. **User** enters `2500`. -4. **System** opens a database transaction and runs: - - ```sql - BEGIN; - UPDATE users - SET available_balance = available_balance + $1, - updated_at = now() - WHERE id = $2; - INSERT INTO transactions (user_id, type, amount, currency, description) - VALUES ($2, 'deposit', $1, 'USD', 'Virtual deposit'); - COMMIT; - ``` - - ![Depositing 2500 USD, then checking the balance](screenshots/uc0003_deposit.png) - -5. **System** confirms: `Deposited 2500.0000 USD.` - -## Verification - -Right after the deposit, `[1] View balance` runs: - -```sql -SELECT available_balance, invested_balance FROM users WHERE id = $1; -``` - -In the screenshot above, alice starts from the seed state (available 8250.00, -invested 1750.00) and ends at available **10750.00** — increased by exactly the -2500.00 deposited, with `invested_balance` untouched. Index: -Prototype/UseCase0004Implementation.md =================================================================== --- P4-Prototype/UseCase0004Implementation.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,91 +1,0 @@ -# Use-case 0004 Implementation — Buy - -**Initiating actor:** Trader. **Source file:** `server/trade.go`, function `PlaceOrder(s, "buy")`. - -## Scenario (implemented) - -1. **User** chooses `[4] Place market BUY order`. -2. **System** lists markets with their latest price (same SQL as UC0006 — via the `v_latest_prices` view). - - -3. **User** enters `BTC`. -4. **System** resolves the market and fetches the latest price: - - ```sql - SELECT m.id, c.id, c.symbol, m.quote_currency - FROM markets m - JOIN crypto c ON c.id = m.crypto_id - WHERE upper(c.symbol) = upper($1) AND m.is_active; - - SELECT price FROM v_latest_prices WHERE market_id = $2; - ``` - -5. **User** enters quantity `0.01`. -6. **System** executes a single database transaction — *all or nothing*: - - ```sql - BEGIN; - - -- (a) record the order - INSERT INTO orders - (user_id, market_id, side, type, status, quantity, price, executed_at) - VALUES - ($1, $2, 'buy', 'market', 'executed', $3, $4, now()) - RETURNING id; - - -- (b) lock and check the user balance - SELECT available_balance FROM users WHERE id = $1 FOR UPDATE; - - -- (c) move cash from available to invested - UPDATE users - SET available_balance = available_balance - $notional, - invested_balance = invested_balance + $notional, - updated_at = now() - WHERE id = $1; - - -- (d) upsert the holding, recomputing the weighted-average entry price - -- in one statement. Every SET expression sees the pre-update row, so - -- holdings.quantity below is still the old quantity. - INSERT INTO holdings (user_id, crypto_id, quantity, avg_price, updated_at) - VALUES ($1, $c, $3, $4, now()) - ON CONFLICT (user_id, crypto_id) DO UPDATE - SET avg_price = (holdings.quantity * holdings.avg_price - + EXCLUDED.quantity * EXCLUDED.avg_price) - / (holdings.quantity + EXCLUDED.quantity), - quantity = holdings.quantity + EXCLUDED.quantity, - updated_at = now(); - - -- (e) ledger entry - INSERT INTO transactions - (user_id, type, amount, currency, related_order, description) - VALUES - ($1, 'buy', -$notional, 'USD', $orderId, 'Market buy ...'); - - -- (f) record the resulting market trade - INSERT INTO market_trades - (market_id, executed_at, price, quantity, side, source) - VALUES - ($2, now(), $4, $3, 'buy', 'user'); - - COMMIT; - ``` - -7. **System** prints: `Order executed: buy 0.0100 BTC @ 67140.000000 (notional 671.4000 USD)`. - - ![Market list, then a filled BUY order](screenshots/uc0004_buy.png) - -## Verified run (from actual prototype execution) - -With seed data loaded: - -- **Before:** alice.available_balance = 8250.00, portfolio = { ETH: 0.5 }. -- **Command:** `buy 0.01 BTC`. -- **After:** alice.available_balance = 7578.60 (= 8250 − 671.40), portfolio = { BTC: 0.01 @ 67140, ETH: 0.5 @ 3500 }, net worth = 10010.00 USD (the +10 is the ETH unrealised P/L from the price moving from 3500 → 3520). - -## Failure path — insufficient funds - -If `available_balance < notional`, the `defer tx.Rollback()` in `server/trade.go` reverts all six statements and the user sees: - -``` -Insufficient funds: need X, have Y -``` Index: -Prototype/UseCase0005Implementation.md =================================================================== --- P4-Prototype/UseCase0005Implementation.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,58 +1,0 @@ -# Use-case 0005 Implementation — Sell - -**Initiating actor:** Trader. **Source file:** `server/trade.go`, function `PlaceOrder(s, "sell")`. - -## Scenario (implemented) - -1. **User** chooses `[5] Place market SELL order`. -2. **System** lists markets (same as UC0004 step 2). -3. **User** enters market symbol, e.g. `ETH`, then quantity `0.5`. -4. **System** opens a transaction and runs: - - ```sql - BEGIN; - - INSERT INTO orders - (user_id, market_id, side, type, status, quantity, price, executed_at) - VALUES - ($1, $2, 'sell', 'market', 'executed', $3, $4, now()) - RETURNING id; - - SELECT quantity, avg_price FROM holdings - WHERE user_id = $1 AND crypto_id = $c FOR UPDATE; - -- abort if missing or insufficient - - UPDATE holdings - SET quantity = quantity - $qty, updated_at = now() - WHERE user_id = $1 AND crypto_id = $c; - - UPDATE users - SET available_balance = available_balance + $notional, - invested_balance = GREATEST(invested_balance - ($avg * $qty), 0), - updated_at = now() - WHERE id = $1; - - INSERT INTO transactions - (user_id, type, amount, currency, related_order, description) - VALUES - ($1, 'sell', $notional, 'USD', $orderId, 'Market sell ...'); - - INSERT INTO market_trades - (market_id, executed_at, price, quantity, side, source) - VALUES - ($2, now(), $price, $qty, 'sell', 'user'); - - COMMIT; - ``` - - ![Selling 0.5 ETH at the current market price](screenshots/uc0005_sell.png) - -5. **System** prints: `Order executed: sell 0.5000 ETH @ 3520.000000 (notional 1760.0000 USD)`. - -## Failure path — insufficient holding - -If the holding does not exist or `quantity < requested`, the `defer tx.Rollback()` in `server/trade.go` reverts every statement above and the user sees: - -``` -Insufficient holding: trying to sell X, hold Y -``` Index: -Prototype/UseCase0006Implementation.md =================================================================== --- P4-Prototype/UseCase0006Implementation.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,59 +1,0 @@ -# Use-case 0006 Implementation — Portfolio & transactions - -**Initiating actor:** Trader. **Source files:** `server/portfolio.go` (`ShowPortfolio`), `server/account.go` (`ShowTransactions`, `ShowBalance`). - -## Scenario (implemented) - -### Portfolio - -1. **User** chooses `[6] View portfolio`. -2. **System** runs: - - ```sql - SELECT symbol, quantity, - COALESCE(avg_price, 0), - COALESCE(current_price, 0), - COALESCE(market_value, 0), - COALESCE(unrealized_pnl, 0) - FROM v_portfolio - WHERE user_id = $1 AND quantity > 0 - ORDER BY symbol; - ``` -3. **System** renders a table with a totals row, then prints the cash summary: - - ```sql - SELECT available_balance, invested_balance FROM users WHERE id = $1; - ``` - - ![screenshot: portfolio view](screenshots/uc0006_portfolio.png) - -### Verified run - -With the seed data (`data_load.sql`), immediately after login, alice's portfolio prints: - -``` - Symbol Quantity Avg buy Current Value Unrealised P/L - ------------------------------------------------------------------------------------ - ETH 0.5000 3500.000000 3520.000000 1760.0000 +10.0000 - ------------------------------------------------------------------------------------ - TOTAL 1760.0000 +10.0000 - - Cash available : 8250.0000 USD - Portfolio value: 1760.0000 USD - Net worth : 10010.0000 USD -``` - -### Transaction history - -1. **User** chooses `[7] View transaction history`. -2. **System** runs: - - ```sql - SELECT created_at, type, amount, currency, COALESCE(description, '') - FROM transactions - WHERE user_id = $1 - ORDER BY created_at DESC - LIMIT 20; - ``` - - ![screenshot: transaction history](screenshots/uc0006_history.png) Index: -Prototype/UseCase0007Implementation.md =================================================================== --- P4-Prototype/UseCase0007Implementation.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ (revision ) @@ -1,50 +1,0 @@ -# Use-case 0007 Implementation — Watchlist - -**Initiating actor:** Trader. **Source file:** `server/watchlist.go`. - -## Scenario (implemented) - -1. **User** chooses `[8] Manage watchlist`. -2. **System** ensures a default watchlist exists: - - ```sql - SELECT id FROM watchlists WHERE user_id = $1 ORDER BY created_at LIMIT 1; - -- else - INSERT INTO watchlists (user_id, name) VALUES ($1, 'Favorites') RETURNING id; - ``` - -3. **System** offers the submenu: List, Add, Remove, Back. - -### List - -```sql -SELECT c.symbol, c.name, COALESCE(lp.price, 0) - FROM watchlist_items wi - JOIN crypto c ON c.id = wi.crypto_id - LEFT JOIN markets m ON m.crypto_id = c.id AND m.quote_currency = 'USD' - LEFT JOIN v_latest_prices lp ON lp.market_id = m.id - WHERE wi.watchlist_id = $1 - ORDER BY c.symbol; -``` - -![Adding DOGE to the watchlist, then listing it](screenshots/uc0007_watchlist.png) - -### Add - -```sql -SELECT id FROM crypto WHERE upper(symbol) = upper($1); - -INSERT INTO watchlist_items (watchlist_id, crypto_id) -VALUES ($watchlist_id, $crypto_id) -ON CONFLICT (watchlist_id, crypto_id) DO NOTHING; -``` - -Re-adding the same symbol is a no-op thanks to the unique constraint + `ON CONFLICT`. - -### Remove - -```sql -DELETE FROM watchlist_items - WHERE watchlist_id = $1 - AND crypto_id = (SELECT id FROM crypto WHERE upper(symbol) = upper($2)); -``` Index: README.md =================================================================== --- README.md (revision d8ce4e224bde01e8f87558691226298db515617e) +++ README.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -1,109 +1,49 @@ -# EduBerza +# EduBerza — quick start -## Short description +Educational crypto-exchange simulator. Go CLI + PostgreSQL. Course project for +*Databases 2025/2026 Winter*, FINKI UKIM. -*P0 — write 150–200 words here in your own words. AI-generated text is not -allowed in this phase.* +Full documentation lives in [`docs/`](docs/) — start at [`docs/README.md`](docs/README.md), +which is the project front page and links every phase. Detailed build and test +instructions are in [`docs/P4-Prototype/BuildInstructions.md`](docs/P4-Prototype/BuildInstructions.md). -*Your own Macedonian draft is in [`opis.md`](P0-ProjectDefinition/opis.md); the material to cover is -what data the database holds (users, crypto assets, markets, orders, holdings, -transactions, market trades, candles, watchlists), who it is for (beginner -traders), and what kind of project it is.* +## Run it in four commands -## Team members +```sh +cp .env.example .env # local defaults: postgres on port 5433 +docker compose up -d # start PostgreSQL +go build -o eduberza ./server +./eduberza -init # create the `project` schema + load sample data +./eduberza # start the CLI +``` -- *Your First Name Last Name — Index XXXXXX* +Log in as `alice` / `test123` (also `bob`, `charlie` — same password). -## Course +`./eduberza -init` is destructive and idempotent: it drops and recreates the +`project` schema every time, so it is the reset button whenever a demo goes +sideways. `./eduberza -load-data` reloads only the sample data. -Databases in 2025/2026/Winter +Optional — watch prices move while you trade, in a second terminal: -## Under the supervision of +```sh +go run ./bots +``` -Prof. Dr. Vangel V. Ajanovski +## Layout -## How this folder maps to the wiki +| Path | What | +|------|------| +| `server/` | CLI prototype — one file per use-case area | +| `server/db/schema_creation.sql` | DDL: drops + recreates the `project` schema (P2) | +| `server/db/data_load.sql` | DML: truncates + reloads sample data (P2) | +| `bots/` | Market simulation bot — random-walk trades and 1m candles | +| `docs/` | All phase documentation, ER diagram, use cases | -The files here are grouped into one folder per phase for convenience. **The wiki -is flat** — when you publish, each file becomes a page named exactly after the -file, with no folder prefix and with capitalisation preserved: `About`, -`ERModel`, `RelationalDesign`, `UseCaseModel`, `UseCase0001`, …, -`PrototypeImplementation`, `BuildInstructions`, and the four `*AIUsage` pages. -Attachments (`ERModel_v01.xml`, `ERModel_v01.png`, `schema_creation.sql`, -`data_load.sql`, `relational_schema.jpg`, the screenshots) attach to the page -that documents them. +Both SQL scripts are embedded into the binary at build time, so `-init` works +from any working directory. -| Folder | Phase | Wiki pages produced | -|--------|-------|---------------------| -| `P0-ProjectDefinition/` | P0 | `About` (+ this front page) | -| `P1-ConceptualModel/` | P1 | `ERModel`, `ERModelAIUsage` | -| `P2-RelationalDesign/` | P2 | `RelationalDesign`, `RelationalDesignAIUsage` | -| `P3-UseCaseModel/` | P3 | `UseCaseModel`, `UseCase0001`–`UseCase0007`, `UseCaseModelAIUsage` | -| `P4-Prototype/` | P4 | `PrototypeImplementation`, `UseCase000XImplementation`, `BuildInstructions`, `PrototypeImplementationAIUsage` | +## Requirements -`Instructions.md` is the condensed course rubric — reference material, not a -submission. `P0-ProjectDefinition/opis.md` and `P1-ConceptualModel/ep-diagram.md` -are your own working notes, kept because they are the evidence that the initial -model was yours before any AI was used. - -The two SQL scripts deliberately stay in `server/db/` rather than moving into -`P2-RelationalDesign/`: they are compiled into the prototype binary with -`go:embed`, which requires them to sit inside the Go package. Attach them to the -`RelationalDesign` page from there. - -## Content - -| Phase | Link | Status | -|-------|------|--------| -| P0 | [About](P0-ProjectDefinition/About.md) | Draft — needs your own text | -| P1 | [ERModel](P1-ConceptualModel/ERModel.md) | Finished, awaiting approval | -| P2 | [RelationalDesign](P2-RelationalDesign/RelationalDesign.md) | Finished, awaiting approval | -| P3 | [UseCaseModel](P3-UseCaseModel/UseCaseModel.md) | Finished, awaiting approval | -| P4 | [PrototypeImplementation](P4-Prototype/PrototypeImplementation.md) | Finished, awaiting approval | -| P5 | *Normalization* | Not started | -| P6 | *Complex DB Reports* | Not started | -| P7 | *Advanced Database Development* | Not started | -| P8 | *Advanced Application Development* | Not started | -| P9 | *Other topics (Performance, Security)* | Not started | - -Keep the Status column current yourself — *started → completed → revised → -approved → final* — after each consultation. - -Note: P0–P4 alone are not enough for a passing grade; at least some of P5–P9 are -required. P5 is the recommended next one. - -## Phase attachments - -| File | Phase | What | -|------|-------|------| -| [`ERModel_v01.xml`](P1-ConceptualModel/ERModel_v01.xml) | P1 | TerraER source of the ER diagram | -| [`ERModel_v01.png`](P1-ConceptualModel/ERModel_v01.png) | P1 | Exported diagram image | -| [`../server/db/schema_creation.sql`](../server/db/schema_creation.sql) | P2 | DDL — drops and recreates the `project` schema | -| [`../server/db/data_load.sql`](../server/db/data_load.sql) | P2 | DML — truncates and reloads sample data | -| [`relational_schema.jpg`](P2-RelationalDesign/relational_schema.jpg) | P2 | Crow's-foot diagram exported from DBeaver | - -## Use cases (P3) - -[UC0001](P3-UseCaseModel/UseCase0001.md) Register · -[UC0002](P3-UseCaseModel/UseCase0002.md) Log in · -[UC0003](P3-UseCaseModel/UseCase0003.md) Deposit · -[UC0004](P3-UseCaseModel/UseCase0004.md) Buy · -[UC0005](P3-UseCaseModel/UseCase0005.md) Sell · -[UC0006](P3-UseCaseModel/UseCase0006.md) Portfolio · -[UC0007](P3-UseCaseModel/UseCase0007.md) Watchlist - -## AI usage logs - -Required by the phase rules for every phase where AI was used. P0 does not have -one because AI use is forbidden there. - -- [ERModelAIUsage](P1-ConceptualModel/ERModelAIUsage.md) (P1) -- [RelationalDesignAIUsage](P2-RelationalDesign/RelationalDesignAIUsage.md) (P2) -- [UseCaseModelAIUsage](P3-UseCaseModel/UseCaseModelAIUsage.md) (P3) -- [PrototypeImplementationAIUsage](P4-Prototype/PrototypeImplementationAIUsage.md) (P4) - -## Build & run - -See [BuildInstructions](P4-Prototype/BuildInstructions.md), or [`../README.md`](../README.md) -for the four-command quick start. -# bp +Go 1.25+, Docker (or any reachable PostgreSQL 14+), and optionally `psql`. +Editing the ER diagram additionally needs Java and TerraER — see +[`docs/P4-Prototype/BuildInstructions.md`](docs/P4-Prototype/BuildInstructions.md). Index: bots/main.go =================================================================== --- bots/main.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ bots/main.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,168 @@ +// EduBerza market simulation bot. +// +// Walks a small price for every active market, inserts rows into +// market_trades once per tick, and upserts the current 1m candle. +// +// Run from the repo root so the relative .env path resolves: +// +// go run ./bots/... +package main + +import ( + "bufio" + "database/sql" + "flag" + "fmt" + "log" + "math/rand" + "os" + "strings" + "time" + + _ "github.com/lib/pq" +) + +type market struct { + id string + symbol string + price float64 +} + +func main() { + interval := flag.Duration("interval", 3*time.Second, "seconds between price ticks") + flag.Parse() + + loadEnv(".env") + dsn := fmt.Sprintf( + "host=%s port=%s user=%s password=%s dbname=%s sslmode=disable options='--search_path=project,public'", + env("DBHOST", "localhost"), + env("DBPORT", "5432"), + env("DBUSER", "postgres"), + env("DBPASSWORD", ""), + env("DBNAME", "postgres"), + ) + db, err := sql.Open("postgres", dsn) + if err != nil { + log.Fatalf("open db: %v", err) + } + defer db.Close() + if err := db.Ping(); err != nil { + log.Fatalf("ping: %v", err) + } + + markets, err := loadMarkets(db) + if err != nil { + log.Fatalf("load markets: %v", err) + } + if len(markets) == 0 { + log.Fatal("no active markets found - run `go run ./server -init` first") + } + + log.Printf("bot started. simulating %d markets every %s", len(markets), *interval) + rnd := rand.New(rand.NewSource(time.Now().UnixNano())) + + for { + for i := range markets { + m := &markets[i] + // random walk: ±0.3% per tick + drift := (rnd.Float64() - 0.5) * 0.006 + m.price = m.price * (1 + drift) + if m.price <= 0 { + m.price = 0.000001 + } + qty := rnd.Float64()*0.5 + 0.01 + + side := "buy" + if rnd.Float64() < 0.5 { + side = "sell" + } + + if err := insertTick(db, m.id, m.price, qty, side); err != nil { + log.Printf("insert tick %s: %v", m.symbol, err) + continue + } + log.Printf(" %-8s %.6f qty=%.4f side=%s", m.symbol, m.price, qty, side) + } + time.Sleep(*interval) + } +} + +func loadMarkets(db *sql.DB) ([]market, error) { + rows, err := db.Query(` + SELECT m.id, c.symbol, COALESCE(lp.price, 100) + FROM markets m + JOIN crypto c ON c.id = m.crypto_id + LEFT JOIN v_latest_prices lp ON lp.market_id = m.id + WHERE m.is_active = true + ORDER BY c.symbol`) + if err != nil { + return nil, err + } + defer rows.Close() + var out []market + for rows.Next() { + var m market + if err := rows.Scan(&m.id, &m.symbol, &m.price); err != nil { + return nil, err + } + out = append(out, m) + } + return out, nil +} + +func insertTick(db *sql.DB, marketID string, price, qty float64, side string) error { + tx, err := db.Begin() + if err != nil { + return err + } + defer tx.Rollback() + + if _, err := tx.Exec( + `INSERT INTO market_trades (market_id, executed_at, price, quantity, side, source) + VALUES ($1, now(), $2, $3, $4, 'simulation')`, + marketID, price, qty, side, + ); err != nil { + return err + } + + // upsert the current 1m candle + if _, err := tx.Exec(` + INSERT INTO market_candles (market_id, timeframe, open, high, low, close, volume, candle_time) + VALUES ($1, '1m', $2, $2, $2, $2, $3, date_trunc('minute', now())) + ON CONFLICT (market_id, timeframe, candle_time) DO UPDATE + SET high = GREATEST(market_candles.high, EXCLUDED.close), + low = LEAST( market_candles.low, EXCLUDED.close), + close = EXCLUDED.close, + volume = market_candles.volume + EXCLUDED.volume`, + marketID, price, qty, + ); err != nil { + return err + } + return tx.Commit() +} + +func loadEnv(path string) { + f, err := os.Open(path) + if err != nil { + return + } + defer f.Close() + s := bufio.NewScanner(f) + for s.Scan() { + line := strings.TrimSpace(s.Text()) + if line == "" || strings.HasPrefix(line, "#") { + continue + } + parts := strings.SplitN(line, "=", 2) + if len(parts) == 2 { + os.Setenv(strings.TrimSpace(parts[0]), strings.TrimSpace(parts[1])) + } + } +} + +func env(k, def string) string { + if v := os.Getenv(k); v != "" { + return v + } + return def +} Index: docker-compose.yml =================================================================== --- docker-compose.yml (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docker-compose.yml (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,21 @@ +services: + postgres: + image: postgres + container_name: databases + restart: always + ports: + - "${DBPORT}:5432" + environment: + POSTGRES_USER: ${DBUSER} + POSTGRES_PASSWORD: ${DBPASSWORD} + POSTGRES_DB: ${DBNAME} + volumes: + - postgres_data:/var/lib/postgresql/data + networks: + - mynetwork + +networks: + mynetwork: + +volumes: + postgres_data: Index: docs/P0-ProjectDefinition/About.md =================================================================== --- docs/P0-ProjectDefinition/About.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P0-ProjectDefinition/About.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,86 @@ +# About: EduBerza + +## Team members + +- Stefan Trsunov - 231285 + +## Short description + +This project is a simulation of a crypto exchange, intended for beginners and +created as a project for the Databases course for 2025-2026. The project is +inspired by TradingView/Binance and aims to let users experience buying and +selling cryptocurrencies, as well as the influence these activities have on the +market and on the way trades are executed. + +The platform will allow user registration and the management of virtual (prop) +money. Every step of the process will be explained in detail, so that users +understand exactly what happens behind the scenes and how transactions affect +the market. + +The following technologies are used: Golang (with the Chi package), and +PostgreSQL for the database. The prototype will be a Go CLI, and the final +project will be built with TypeScript, specifically Lit components. + +The main goal is to create educational and interactive software that will help +beginners and ordinary users understand how crypto exchanges work through +practical simulation and clear explanations. + +Inspiration: + +https://tradingview.com/ +https://www.binance.com/ + + + +## Detailed description + +This project is an educational simulation of a crypto exchange, intended for +beginners and created as a project for the Databases course for 2025-2026. The +project is inspired by the leading platforms TradingView and Binance, with the +goal of letting users experience in practice the buying and selling of +cryptocurrencies, as well as the direct influence these transactions have on the +market and on the trade-execution process itself. + +The platform will allow user registration and the management of virtual (prop) +money. Every step of the process will be explained in detail and in practical +terms, so that users can more easily understand what exactly happens behind the +scenes — from the placing of orders to their matching and the effects on market +liquidity. + +The following technologies are used to build the project: Golang (with the Chi +package) for the server side and PostgreSQL for managing the database. The +architecture is planned in two phases: the initial prototype will be a Go CLI +application for quick verification, while the final web version will be built +with TypeScript, specifically with Lit components, to create a modern interface. + +The main goal is to create interactive software that will help beginners and +ordinary users understand how crypto exchanges work through practical +simulation, clear explanations and transparent processes. + + +### Who is the database and project intended for? + +Target Audience: Beginners and general users who want to learn how cryptocurrency exchanges work without financial risk. + +### Which problems does your project solve? + +This is only for education purposes. The problem is fixing the financial literacy of the people. + +### Who uses the system? What types of users exist? + +Roles/Users that are expected to use this product are: + +- Visitors +- Traders + +### How is this different from similar existing solutions? + +Virtual money with a step-by-step explanation of what each order does to the `book`. + +### Is this a web, mobile and/or desktop application? + +The prototype will be a CLI but the end goal will be to build a website + +### Illustrations + +Screenshots of the running prototype: [`screenshots/`](../P4-Prototype/screenshots) Index: docs/P0-ProjectDefinition/opis.md =================================================================== --- docs/P0-ProjectDefinition/opis.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P0-ProjectDefinition/opis.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,18 @@ +ЕдуБерза + +Веб проект за симулација на крипто берза за почетници, инспирирана од TradingView, изградена со Golang, Typescript и PostgreSQL. Овозможува регистрација на корисници, симулира купување и продавање криптовалути, сето ова ќе биде овозмошено со префрлување на виртуелни пари (prop) со објаснување на секој чекор и ефектите врз пазарот. + +Целосен опис + +Овој проект претставува симулација на крипто берза, наменета за почетници и создадена како проект за предметот „База на податоци“ за 2025-2026 година. Проектот е инспириран од TradingView/Binance и има за цел да им овозможи на корисниците да ја искусат купување и продавање криптовалути, како и влијанието што овие активности го имаат врз пазарот и начинот на извршување на тргувањата. + +Платформата ќе овозможи регистрација на корисници, управување со виртуелни пари (prop). Секој чекор од процесот ќе биде детално објаснет, за корисниците да разберат што точно се случува зад сцената и како трансакциите влијаат на пазарот. + +Се користат следниве технологии: Golang (со Chi пакетот), и PostgreSQL за база на податоци. Прототипот ќе биде Go CLI, а конечниот проект ќе биде со Typescript поточно Lit componenti. + +Главната цел е да се создаде едукативен и интерактивен софтвер кој ќе им помогне на почетниците и обичните корисници да го разберат работењето на крипто берзите преку практична симулација и јасни објаснувања. + +Инспирација: + +https://tradingview.com/ +https://www.binance.com/ Index: docs/P1-ConceptualModel/ERModel.md =================================================================== --- docs/P1-ConceptualModel/ERModel.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P1-ConceptualModel/ERModel.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,253 @@ +# Entity-Relationship Model v.01 + +## Diagram + +![ERModel_v01](ERModel_v01.png) + +Attachments for this page: `ERModel_v01.xml` (TerraER source) and `ERModel_v01.png` +(exported image). Open the source with TerraER 3.11: + +```sh +java -jar TerraER3.11.jar # then File → Open → ERModel_v01.xml +``` + +Notation: Chen. Rectangles are entity sets, diamonds are relationships, ellipses +are attributes, underlined ellipses are primary keys, the dashed ellipse is a +derived attribute. A double line between an entity set and a relationship marks +**total participation** (every instance of that entity set must participate); a +single line marks partial participation. + +Two deliberate modeling decisions worth stating up front: + +- **No foreign keys appear in the diagram.** Connections between entity sets are + expressed as relationships, per the notation. Foreign-key columns appear only + in the relational model in [RelationalDesign](../P2-RelationalDesign/RelationalDesign.md). +- **`Holds` and `Contains` are relationships, not entity sets.** Both are M:N and + both carry their own attributes, which is exactly what a Chen relationship is + for. They become tables (`holdings`, `watchlist_items`) only in P2. + +## Data requirements + +### Entity sets + +#### Users +Registered participants of the platform. Every action in the simulation is +attributed to a user, and the two balance attributes are what makes the +simulation work: cash that is free to trade is tracked separately from cash that +is currently committed to open positions, so the platform can refuse a purchase +without having to recompute the whole portfolio first. + +- **Candidate keys:** `{id}`, `{username}`, `{email}`. Primary key: **`id`**. + A surrogate UUID was chosen because it is opaque and stable — `username` and + `email` are both things a user may legitimately want to change later, and + every relationship in the diagram points at `Users`, so a mutable key would + propagate changes across the whole database. +- **Attributes:** + - `id` — UUID, required, primary key. + - `username` — text, max 50, required, unique. + - `email` — text, max 255, required, unique, must contain `@`. + - `full_name` — text, max 200, optional. + - `password_hash` — text, max 255, required. Never the password itself; the + prototype stores a SHA-256 hex digest. + - `available_balance` — numeric(18,4), required, default 0, must be ≥ 0. + - `invested_balance` — numeric(18,4), required, default 0, must be ≥ 0. + - `created_at` — timestamp with time zone, required, defaults to now. + - `updated_at` — timestamp with time zone, optional (null until first change). + +#### Cryptos +The catalog of crypto assets the platform knows about. Kept separate from +`Markets` because an asset exists independently of the pairs it is traded in — +the same asset can be quoted against several currencies, and a user's holding is +in the *asset*, not in a particular pair. + +- **Candidate keys:** `{id}`, `{symbol}`. Primary key: **`id`**, for the same + reason as in `Users`; `symbol` is kept as a unique natural key because that is + what users type and see. +- **Attributes:** + - `id` — UUID, required, primary key. + - `symbol` — text, max 20, required, unique (e.g. `BTC`). + - `name` — text, max 255, required (e.g. `Bitcoin`). + - `created_at` — timestamptz, required, defaults to now. + +#### Markets +A tradeable pair: one crypto asset quoted in one currency, e.g. BTC/USD. This is +where prices live, and it is the thing an order is placed *on*. Modeled as its +own entity set rather than an attribute of `Cryptos` because a market has its own +lifecycle — it can be deactivated without deleting the asset — and because +trades, candles and orders all reference the pair, not the asset. + +- **Candidate keys:** `{id}`, `{crypto_id, quote_currency}` — that pair is + unique by definition, since a given asset can only be quoted once per + currency. Primary key: **`id`**, so that the many entity sets referencing a + market carry one narrow column instead of a composite key. +- **Attributes:** + - `id` — UUID, required, primary key. + - `quote_currency` — text, exactly 3 characters, required, default `USD`. + - `is_active` — boolean, required, default true. Inactive markets are hidden + from the trading menus but keep their history. + - `created_at` — timestamptz, required, defaults to now. + +#### Orders +A user's instruction to buy or sell on a market. Needed as a separate entity set +because an order is a record of *intent* that outlives its execution: it keeps +the requested quantity and price even after it has been filled, which is what +makes the ledger auditable. + +- **Candidate keys:** `{id}` only. There is no natural key — the same user can + place two identical orders on the same market in the same second, and both are + legitimately distinct. Primary key: **`id`**. +- **Attributes:** + - `id` — UUID, required, primary key. + - `side` — text, required, restricted to `buy` or `sell`. + - `type` — text, required, restricted to `market` or `limit`. The prototype + executes only `market` orders; `limit` exists so the model does not have to + change when limit orders are implemented. + - `status` — text, required, restricted to `open`, `executed`, `cancelled`. + - `quantity` — numeric(20,4), required, must be > 0. + - `price` — numeric(18,6), optional — null for a market order until it fills, + then the fill price. + - `placed_at` — timestamptz, required, defaults to now. + - `executed_at` — timestamptz, optional, set when the order fills. + +#### Transactions +The financial ledger: every movement of virtual cash, in one place. This exists +so that a balance is never just a number someone edited — it is the sum of an +auditable list of entries, which is also what the "explain every step" goal of +the project needs. + +- **Candidate keys:** `{id}` only. Primary key: **`id`**. +- **Attributes:** + - `id` — UUID, required, primary key. + - `type` — text, required, restricted to `deposit`, `buy`, `sell`, `fee`. + - `amount` — numeric(18,4), required. Signed: negative for money leaving the + cash balance, positive for money arriving. + - `currency` — text, exactly 3 characters, required, default `USD`. + - `created_at` — timestamptz, required, defaults to now. + - `description` — text, optional, free-form human-readable explanation. + +#### MarketTrades +Individual executed trades on a market, from the user's own fills and from the +market simulator. This is the single source of truth for the current price: the +price of a market is the price of its most recent trade, never a column someone +writes directly. + +- **Candidate keys:** `{id}`. In principle `{market_id, executed_at}` looks + unique, but two trades can share a timestamp, so it is not a safe key. + Primary key: **`id`** (a plain auto-incrementing integer here rather than a + UUID, because this is the highest-volume entity set and it is only ever read + in timestamp order, never referenced by anything else). +- **Attributes:** + - `id` — integer, required, primary key, auto-generated. + - `executed_at` — timestamptz, required. + - `price` — numeric(18,6), required, must be > 0. + - `quantity` — numeric(20,6), required, must be > 0. + - `side` — text, optional, `buy` or `sell`. + - `source` — text, max 50, required, default `simulation`. Distinguishes a + simulated trade from a user's own fill (`user`). + +#### MarketCandles +OHLCV aggregates per market and timeframe — the data a price chart is drawn +from. Stored rather than computed on the fly because the point of the project is +a chart-driven interface, and re-aggregating the whole trade history for every +screen refresh does not scale. + +- **Candidate keys:** `{id}`, and `{market_id, timeframe, candle_time}` — a + market has exactly one candle per timeframe per time bucket. Primary key: + **`id`**; the composite is enforced as a uniqueness rule because it is the + real-world constraint and it is what prevents duplicate candles. +- **Attributes:** + - `id` — integer, required, primary key, auto-generated. + - `timeframe` — text, required, restricted to `1m`, `5m`, `1h`, `1d`. + - `open`, `high`, `low`, `close` — numeric(18,6), all required. + - `volume` — numeric(20,6), required. + - `candle_time` — timestamptz, required — the start of the bucket. + +#### Watchlists +A named list of assets a user wants to monitor. A separate entity set rather than +a flag on the relationship between users and assets, because a user may want +several lists ("long term", "watching today") and each needs its own name. + +- **Candidate keys:** `{id}`. `{user_id, name}` would also work if list names + are required to be unique per user; the model does not impose that, so it is + not listed as a candidate key. Primary key: **`id`**. +- **Attributes:** + - `id` — UUID, required, primary key. + - `name` — text, max 100, required. + - `created_at` — timestamptz, required, defaults to now. + +### Relationships + +#### QuotedOn — Cryptos (1) : Markets (N), total on Markets +Ties a market to the asset it trades. One asset can be quoted in many markets; +every market must have exactly one asset, hence total participation on the +`Markets` side. No attributes of its own. + +#### PlacedOn — Markets (1) : Orders (N), total on Orders +Records which market an order was placed on. Every order must name a market; +a market may have no orders yet. No attributes. + +#### Places — Users (1) : Orders (N), total on Orders +Records who placed an order. Every order belongs to exactly one user; a new user +has no orders. No attributes. + +#### Records — Users (1) : Transactions (N), total on Transactions +Attributes each ledger entry to a user. Every entry belongs to exactly one user. +No attributes. + +#### Settles — Orders (1) : Transactions (N), partial on both sides +Links a ledger entry to the order that caused it. Partial on the `Transactions` +side because deposits have no originating order, and partial on the `Orders` side +because an order that never executes never produces a ledger entry. This is why +the corresponding column is nullable in P2. No attributes. + +#### Fills — Markets (1) : MarketTrades (N), total on MarketTrades +Every executed trade happened on exactly one market. No attributes. + +#### Aggregates — Markets (1) : MarketCandles (N), total on MarketCandles +Every candle summarises trades of exactly one market. No attributes. + +#### Owns — Users (1) : Watchlists (N), total on Watchlists +Every watchlist belongs to exactly one user. No attributes. + +#### Holds — Users (M) : Cryptos (N), partial on both sides, **with attributes** +A user's position in an asset. M:N because one user holds many assets and one +asset is held by many users, and partial on both sides because a user may hold +nothing and an asset may be held by nobody. Modeled as a relationship rather +than an entity set because a position has no identity of its own — it is +entirely described by *which user*, *which asset*, and how much. + +- **Attributes:** + - `quantity` — numeric(20,4), required, must be ≥ 0. + - `avg_price` — numeric(18,6), required, ≥ 0, **derived** (dashed ellipse): + the weighted average of the prices at which the position was accumulated. + It is derivable from the buy history, and is stored anyway so that + unrealised P/L can be shown without replaying the whole ledger. + - `created_at` — timestamptz, required, defaults to now. + - `updated_at` — timestamptz, optional. + +#### Contains — Watchlists (M) : Cryptos (N), partial on both sides, **with attribute** +Which assets are on which watchlist. M:N: a list holds many assets, an asset +appears on many lists. Partial on both sides — an empty list is valid and an +asset need not be on any list. + +- **Attributes:** + - `added_at` — timestamptz, required, defaults to now. Recorded so a list can + be shown in the order the user built it. + +## Entity-Relationship Model History + +- **v01** — First complete version. Built from the entity notes in + [`ep-diagram.md`](ep-diagram.md) (the initial hand-written model), with three + changes made to that initial model while drawing it: + 1. `Markets` was promoted from an implied attribute of the asset to its own + entity set, so that prices, orders, trades and candles can all reference a + pair rather than an asset. + 2. `holdings` and `watchlist_items` were re-expressed as the M:N relationships + `Holds` and `Contains` with their own attributes, instead of entity sets + with foreign keys — the initial notes listed them as tables, which is a + relational concept that does not belong in a Chen ERD. + 3. `avg_price` was marked as a derived attribute rather than a plain one, to + make the denormalisation explicit rather than hidden. + +Reasoning for the AI-assisted part of this phase, and the full interaction log, +are on [ERModelAIUsage](ERModelAIUsage.md). Index: docs/P1-ConceptualModel/ERModelAIUsage.md =================================================================== --- docs/P1-ConceptualModel/ERModelAIUsage.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P1-ConceptualModel/ERModelAIUsage.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,177 @@ +# Entity-Relationship Model AI Usage + +## Name of AI service/solution that was used + +**Claude Code** (Anthropic) + +- **URL:** https://claude.com/claude-code +- **Type of service/subscription:** Claude subscription. Session 1 used model + Claude Opus 4.7 (1M context); session 2 used Claude Opus 5 (1M context). + +## Final result + +### Diagram + +`ERModel_v01.xml` / `ERModel_v01.png`. + +**Declaration of how the diagram was produced.** The initial model is the +student's own: the entity and attribute list in [`ep-diagram.md`](ep-diagram.md), +written in Macedonian before any AI was involved. In session 2 the AI turned that +list into the TerraER diagram file, and while doing so proposed three changes to +the initial model, all three listed in the model history on +[ERModel](ERModel.md#entity-relationship-model-history): +promoting `Markets` to its own entity set, re-expressing `holdings` and +`watchlist_items` as M:N relationships with attributes rather than entity sets +with foreign keys, and marking `avg_price` as derived. + +The diagram was not drawn by hand in the TerraER GUI. It was generated +programmatically by constructing TerraER's own figure objects +(`EntidadeFigure`, `RelacionamentoFigure`, `AtributoFigure`, +`AtributoChaveFigure`, `AtributoDerivadoFigure` and the labelled line-connection +figures) and serialising them with TerraER's own +`DOMStorableInputOutputFormat` — the same writer the application uses when you +choose *Save*. The file is therefore a normal TerraER document: it was verified +by reading it back through TerraER's own reader and comparing the figure count +(144), and it opens and can be edited in TerraER 3.11 like any hand-drawn +diagram. Subsequent versions (`ERModel_v02.xml` onward) are edited by the student +in the GUI. + +### Model description + +See [ERModel](ERModel.md). + +## Summary of AI involvement + +Work on this project happened in two working sessions, several months apart. + +| | Session 1 | Session 2 | +|---|---|---| +| **When** | 2026-04-21 | 2026-08-06 / 2026-08-07 | +| **Model** | Claude Opus 4.7 (1M context) | Claude Opus 5 (1M context) | +| **Phases advanced** | P1, P2, P3 and the first working prototype | The ER diagram file, P4 documentation, bug fixes | +| **My starting material** | `ep-diagram.md`, `opis.md`, my existing Go backend and draft SQL | Everything from session 1, plus the phase rubric | + +In **session 1** I brought my own data model (`ep-diagram.md`, written in +Macedonian before any AI was involved) and my own draft schema and Go backend. I +used the AI to review them, and it found real errors in my SQL that I had missed +— most seriously that I had declared `crypto_id` as a foreign key to two +different tables at once, in three separate tables. I decided the corrections to +adopt, chose to go solo, chose a CLI prototype over an HTTP one, and chose +English for the documentation. The output of that session was the corrected +schema, the sample-data script, the use-case model and the working prototype. + +In **session 2** I came back to produce the deliverable I was still missing — the +TerraER diagram file itself — and to finish the P4 documentation. I also asked +for a review pass over the database and Go code, which turned up three further +bugs (see [PrototypeImplementationAIUsage](../P4-Prototype/PrototypeImplementationAIUsage.md)). + +Below is the log, one entry per exchange. My prompts are quoted as **excerpts**: +the wording is my own, unaltered, but I have left out scheduling remarks and +other working chatter that says nothing about the model or the code. Typos are +kept as they were. The *Intent* line above each prompt is my own note on what I +was trying to achieve. AI responses are summarised rather than quoted in full, +because several ran to many pages of code and file edits. The complete +unabridged transcript is in my Claude Code session history and can be produced +on request. + +## Entire AI usage log + +--- + +### Session 1 — 2026-04-21 + +**Intent:** hand the AI the whole project and the course rubric, and have it +audit my existing model and code against the phase requirements before I commit +to anything. + +**Prompt (student, excerpt):** +> This is my project for my exam Database, go to the docs folder and README.md there you will find all of the documentation, afterwards take a look at the whole project. Delete everything you want add everything you want just make it work and make it fill in or to follow all of the needed instructions. Ask me anything, also tell me what do I need to do + +**Response (AI, summarised):** +- Read `docs/opis.md`, `docs/ep-diagram.md`, `docs/dbdiagram.md`, `server/db/db.sql`, `server/db/schema.sql`, all Go sources. +- Flagged bugs in the initial schema: + - `holdings.crypto_id` declared as a foreign key to both `users(id)` and `crypto(id)`; should be a separate `user_id` column. + - Same mistake in `orders` and `transactions`. + - `market_candles` referenced a non-existent `markets` table. + - `main.go` dropped and recreated all tables on every startup, wiping data. +- Proposed a single authoritative schema, to be placed in `server/db/schema_creation.sql`, based on `docs/ep-diagram.md` and `docs/dbdiagram.md` with the above issues fixed. +- Asked the student for decisions: team composition, prototype shape (CLI vs HTTP), whether to keep `available_balance`, `invested_balance`, `secret_question`, `secret_answer`, which use cases to implement, language of documentation. + +**Intent:** I had just pasted the course rubric into `README.md` and removed the +half-finished frontend, so that the AI would plan against the actual phase +requirements and against a backend-only prototype. + +**Prompt (student, excerpt):** +> Read it now, just filled it in, also deleted the frontend we don' need that + +**Response (AI, summarised):** +- Re-read `docs/README.md`, which now contained the course phase rubric (P0–P9). +- Explained the constraints each phase places on AI usage (P0 forbids AI; P1–P4 allow AI only to improve the student's own initial model and require an AIUsage log). +- Listed the deliverables per phase and asked the remaining questions. + +--- + +### Session 2 — 2026-08-06 / 2026-08-07 + +**Intent:** re-read the rubric from scratch after the gap, reduce it to a +checklist of what each phase actually has to produce, confirm TerraER runs on +this machine. + +**Prompt (student, excerpt):** +> We will start from the begining in some ways, please read first docs/insturctions.md +> The goal is to finish with milestone 0 so I can publish it […] +> Read the Instuctions.md like I said and delete everything that you think we don't need +> and for each phrase tell me what do I need to submit and how to do it. and can you also +> do the thing with TerraER3.11.jar? + +**Response (AI, summarised):** +- Read the phase rubric and condensed `Instructions.md` to a per-phase checklist + of deliverables, keeping every requirement and every load-bearing filename. +- Confirmed TerraER 3.11 runs on the installed OpenJDK 21 and launched it. +- Listed, per phase, what has to be submitted and how; flagged that P0's + deliverables are wiki pages, that the EPRMS proposal is a prerequisite. +- Flagged `image.png` (a TradingView screenshot) as material the project has no + licence to publish. + +**Intent:** produce the artefacts still missing for P1–P4 — above all the TerraER +diagram — while keeping P0 for myself, since AI use is forbidden there. + +**Prompt (student, excerpt):** +> […] do all of the other Phases till m0. +> opis.md It's p0 so I will take care of that. Delete anything that we don't need, +> make all of the phases and terra diagram if you can, and delete anything +> that we don't need and make a documentation about how to start it. + +**Intent:** ask for a review pass over the schema and the Go code rather than +only documentation, on the grounds that a prototype I have to defend in person +should not have known defects in it. + +**Prompt (student, excerpt, follow-up):** +> Also fix some database things or golang things if you think we can do it better, + +**Response (AI, summarised) — the part relevant to P1:** +- Read `ep-diagram.md` (the student's own initial model) and the existing + `schema_creation.sql`. +- Reverse-engineered TerraER's file format from the distributed jar to learn the + element names it stores figures under (`ent`, `rel`, `atr`, `atrchave`, + `atrderivado`, `llabelUm`, `llabelMuitos`, `llabelDoubleUm`, + `llabelDoubleMuitos`, …). +- Generated `ERModel_v01.xml` and `ERModel_v01.png` as described above, in Chen + notation: 8 entity sets, 10 relationships, 57 attributes, cardinality labels + on every relationship line and double lines for total participation. +- Proposed the three changes to the initial model recorded in the model history. +- Rewrote [ERModel](ERModel.md) with the per-entity documentation, candidate-key + justifications and attribute types the phase template requires. + +--- + +> **Student action required.** Two things, in this order: +> +> 1. Open `ERModel_v01.xml` in TerraER, read the whole diagram, and change what +> you disagree with. Save the result as `ERModel_v02.xml` with a matching PNG +> and add a history line. The phase rules require that the model be yours; +> the generated v01 is a starting point to review and take over, not an +> answer to submit unread. +> 2. Verify that this log matches your recollection and append the full text of +> any further prompts. The complete transcript is in your Claude Code session +> history. Index: docs/P1-ConceptualModel/ERModel_v01.xml =================================================================== --- docs/P1-ConceptualModel/ERModel_v01.xml (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P1-ConceptualModel/ERModel_v01.xml (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,1 @@ +CryptosMarketsMarketTradesMarketCandlesOrdersTransactionsUsersWatchlistsQuotedOnFillsAggregatesPlacedOnSettlesRecordsPlacesHoldsOwnsContains

3

3

3

3

3

3

3

created_at

name

symbol

truetrueid

created_at

is_active

quote_currency

truetrueid

truetrueid

executed_at

price

quantity

side

source

truetrueid

timeframe

open

high

low

close

volume

candle_time

truetrueid

side

type

status

quantity

price

placed_at

executed_at

truetrueid

type

amount

currency

created_at

description

truetrueid

username

email

full_name

password_hash

available_balance

invested_balance

created_at

updated_at

created_at

name

truetrueid

quantity

5avg_price

created_at

updated_at

added_at

Index: docs/P1-ConceptualModel/ep-diagram.md =================================================================== --- docs/P1-ConceptualModel/ep-diagram.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P1-ConceptualModel/ep-diagram.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,197 @@ +Ентитети + +User – ентитет кој чува информации за корисниците на платформата + +id (uuid) + +username (varchar(50), not null, unique) + +email (varchar(255), not null, unique) + +password_hash (varchar(255), not null) + +full_name (varchar(200)) + +created_at (timestamptz) + +updated_at (timestamptz) + + +Crypto – ентитет кој ги чува податоците за криптовалутите со кои се тргува + +id (uuid) + +symbol (varchar(20), not null, unique) + +name (varchar(255)) + +created_at (timestamptz) + + + +Market – ентитет кој ги дефинира пазарите (парови криптовалути и валути за котација(BTCUSDT, ADAUSDT, ADABTC)) + +id (uuid) + +crypto_id (uuid, not null, FK → Crypto.id) + +quote_currency (char(3), not null) ADA, USDT, BTC, USDT + +is_active (boolean) + +created_at (timestamptz) + + + +Holding – ентитет кој ги чува позициите на корисниците во криптовалути + +id (uuid) + +user_id (uuid, not null, FK → User.id) + +crypto_id (uuid, not null, FK → Crypto.id) + +quantity (numeric(20,4), not null) + +avg_price (numeric(18,6)) + +created_at (timestamptz) + +updated_at (timestamptz) + + + +Order – ентитет кој ги чува нарачките за купување/продавање + +id (uuid) + +user_id (uuid, not null, FK → User.id) + +market_id (uuid, not null, FK → Market.id) + +side (varchar(4), not null) — типично “buy” или “sell” + +type (varchar(20), not null) — тип на нарачка, пример “limit”, “market” + +status (varchar(20), not null) — статус на нарачка (open, executed, cancelled) + +quantity (numeric(20,4), not null) + +price (numeric(18,6)) + +placed_at (timestamptz) + +executed_at (timestamptz) + + + +Transaction – ентитет кој ја следи финансиската активност на корисниците (депозити, купувања, продавања, провизии) + +id (uuid) + +user_id (uuid, not null, FK → User.id) + +type (varchar(50), not null) — пример: “deposit”, “buy”, “sell”, “fee” + +amount (numeric(18,4), not null) + +currency (char(3), not null) + +related_order (uuid, FK → Order.id) + +created_at (timestamptz) + +description (text) + + + +Market_Trade – ентитет кој ги евидентира извршените пазарни зделки кои ја формираат цената + +id (bigserial) + +market_id (uuid, not null, FK → Market.id) + +executed_at (timestamptz, not null) + +price (numeric(18,6), not null) + +quantity (numeric(20,6), not null) + +side (varchar(4)) + +source (varchar(50)) + +Market_Candle – ентитет кој ги агрегира податоците за цените и волуменот во временски интервали (целови) + +id (bigserial) + +market_id (uuid, not null, FK → Market.id) + +timeframe (varchar(5), not null) — пример: “1m”, “5m”, “1h” + +open (numeric(18,6), not null) + +high (numeric(18,6), not null) + +low (numeric(18,6), not null) + +close (numeric(18,6), not null) + +volume (numeric(20,6), not null) + +candle_time (timestamptz, not null) + +Watchlist – ентитет кој ги чува листите за следење на корисниците + +id (uuid) + +user_id (uuid, not null, FK → User.id) + +name (varchar(100)) + +created_at (timestamptz) + +Watchlist_Item – ентитет кој ги чува криптовалутите кои се ставени во некоја листа за следење + +id (uuid) + +watchlist_id (uuid, not null, FK → Watchlist.id) + +crypto_id (uuid, not null, FK → Crypto.id) + +added_at (timestamptz) + +Релации + +User – Holding (1:N) +Еден корисник може да има повеќе позиции во различни криптовалути. + +Crypto – Holding (1:N) +Една криптовалута може да се држи од повеќе корисници. + +User – Order (1:N) +Еден корисник може да има повеќе нарачки. + +Market – Order (1:N) +Еден пазар има повеќе нарачки. + +User – Transaction (1:N) +Еден корисник има повеќе трансакции. + +Order – Transaction (0..1 : N) +Трансакцијата може да биде поврзана со една нарачка (пример купување/продажба). + +Market – Market_Trade (1:N) +Еден пазар има многу извршени зделки. + +Market – Market_Candle (1:N) +Еден пазар има многу временски агрегирани податоци. + +User – Watchlist (1:N) +Еден корисник може да има повеќе листи за следење. + +Watchlist – Watchlist_Item (1:N) +Една листа може да содржи повеќе криптовалути. + +Crypto – Watchlist_Item (1:N) +Една криптовалута може да биде во повеќе листи за следење. Index: docs/P2-RelationalDesign/RelationalDesign.md =================================================================== --- docs/P2-RelationalDesign/RelationalDesign.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P2-RelationalDesign/RelationalDesign.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,137 @@ +# Relational Design + +## Descriptive representation of the relational schema + +Notation: **bold** = primary key, *italic* = foreign key. + +- **Users**(**id**, username, email, full_name, password_hash, available_balance, invested_balance, created_at, updated_at) + - Candidate keys: `{id}`, `{username}`, `{email}`. `UNIQUE(username)`, `UNIQUE(email)`. +- **Crypto**(**id**, symbol, name, created_at) + - Candidate keys: `{id}`, `{symbol}`. `UNIQUE(symbol)`. +- **Markets**(**id**, *crypto_id*, quote_currency, is_active, created_at) + - Candidate keys: `{id}`, `{crypto_id, quote_currency}`. `UNIQUE(crypto_id, quote_currency)`. +- **Holdings**(**id**, *user_id*, *crypto_id*, quantity, avg_price, created_at, updated_at) + - Transformation of the M:N relationship `Holds`. Candidate keys: `{id}` and + `{user_id, crypto_id}` — the latter is the relationship's own key and is + enforced with `UNIQUE(user_id, crypto_id)`. `id` was chosen as PK for + consistency with the other relations. + - `avg_price` is `NOT NULL DEFAULT 0 CHECK (avg_price >= 0)`. +- **Orders**(**id**, *user_id*, *market_id*, side, type, status, quantity, price, placed_at, executed_at) + - `side ∈ {buy, sell}`, `type ∈ {market, limit}`, `status ∈ {open, executed, cancelled}`. +- **Transactions**(**id**, *user_id*, type, amount, currency, *related_order*, created_at, description) + - `type ∈ {deposit, buy, sell, fee}`. +- **MarketTrades**(**id**, *market_id*, executed_at, price, quantity, side, source) +- **MarketCandles**(**id**, *market_id*, timeframe, open, high, low, close, volume, candle_time) + - `UNIQUE(market_id, timeframe, candle_time)`. +- **Watchlists**(**id**, *user_id*, name, created_at) +- **WatchlistItems**(**id**, *watchlist_id*, *crypto_id*, added_at) + - Transformation of the M:N relationship `Contains`. Candidate keys: `{id}` + and `{watchlist_id, crypto_id}`, the latter enforced with + `UNIQUE(watchlist_id, crypto_id)`. + +### Transformation method used + +**Partial transformation.** Applied as follows: + +- Each of the 8 entity sets in [ERModel](../P1-ConceptualModel/ERModel.md) becomes one table, keeping + its UUID (or serial) primary key. +- Each **1:N relationship without attributes** is transformed by adding the + parent's primary key as a foreign-key column on the child table — the "N" + side. This is where every foreign key in the schema comes from, and it is why + no foreign keys appear in the ER diagram itself: + `QuotedOn` → `markets.crypto_id`, `PlacedOn` → `orders.market_id`, + `Places` → `orders.user_id`, `Records` → `transactions.user_id`, + `Settles` → `transactions.related_order`, `Fills` → `market_trades.market_id`, + `Aggregates` → `market_candles.market_id`, `Owns` → `watchlists.user_id`. +- Each **M:N relationship** becomes its own table holding the two foreign keys + plus the relationship's own attributes: `Holds` → `holdings`, + `Contains` → `watchlist_items`. The pair of foreign keys is the relationship's + key and is enforced as a `UNIQUE` constraint in both tables. +- **Total participation** in the ER model becomes `NOT NULL` on the + corresponding foreign key; partial participation stays nullable. `Settles` is + partial on both sides, which is exactly why `transactions.related_order` is + the one nullable foreign key in the schema — a deposit has no originating + order. + +### Normalisation + +All relations are in **3NF**: + +- Every attribute is atomic (no repeating groups, no composite fields). +- No partial dependency exists because every primary key is a single UUID column. +- No transitive dependency exists: every non-key attribute depends directly on the row identifier. For example, `holdings.quantity` depends on `holdings.id`, not on `user_id` via some intermediate. +- `avg_price` in `Holdings` is a **derived value** cached for performance (it is + the weighted-average entry price across all `buy` transactions for that + `(user, crypto)` pair) — it is drawn as a derived attribute in the ER diagram. + We accept the denormalisation: it is recomputed by the database inside the same + transaction as each buy, in the same statement that changes the quantity + (`INSERT … ON CONFLICT (user_id, crypto_id) DO UPDATE`), so the stored average + and the stored quantity can never disagree. +- `avg_price` is declared `NOT NULL DEFAULT 0`. This matters: it is used in the + P/L arithmetic of `v_portfolio`, and in SQL any arithmetic involving `NULL` + yields `NULL`, so a nullable average would have silently blanked the + unrealised-P/L column for an existing position instead of failing loudly. + +## DDL script + +The script that creates the entire schema is [`../server/db/schema_creation.sql`](../../server/db/schema_creation.sql). It is idempotent: it drops and recreates the `project` schema every run, so it works on an empty database and on a database that already has the schema. + +The script creates: +- 10 tables with check constraints, primary keys, foreign keys and unique constraints. +- 5 performance indexes. +- 2 views: `v_latest_prices` (latest trade price per market) and `v_portfolio` (per-user holdings valuation with unrealised P/L). + +## DML script (sample data) + +The script that loads realistic sample data is [`../server/db/data_load.sql`](../../server/db/data_load.sql). It is idempotent: it truncates all tables with `CASCADE` then re-inserts. Loaded: +- 5 crypto assets (BTC, ETH, ADA, SOL, DOGE) and 5 USD-quoted markets. +- 3 sample users (`alice`, `bob`, `charlie`) with password `test123` (sha256 hex). +- 18 recent market trades across all markets so `v_latest_prices` is populated. +- 10 one-hour candles (BTC and ETH). +- One fully-executed market-buy order for Alice, the matching holding, and two ledger entries (deposit + buy), with Alice's balances updated accordingly. +- Two watchlists with five watchlist items. + +## Relational diagram + +![relational_schema](relational_schema.jpg) + +Generated in **DBeaver** from the **live** `project` schema, in crow's-foot +notation — not drawn by hand, so it is evidence that the deployed database +actually matches the design described above. Each box is a table with its +columns and declared types; key icons mark primary keys and the arrowed lines +are the 12 declared foreign keys. + +### How to regenerate it + +**With DBeaver** (the tool the course recommends): + +1. Connect to the assigned FINKI PostgreSQL project database. +2. Double-click the `project` schema → **ER Diagram** tab. +3. Right-click in the diagram → **Notation** → **Crow's foot**. +4. Arrange the tables to mirror the layout of + [`ERModel_v01.png`](../P1-ConceptualModel/ERModel_v01.png). +5. Right-click → **Export diagram** → save as `relational_schema.jpg`. + +On Linux, install it with: + +```sh +flatpak remote-add --if-not-exists --user flathub https://dl.flathub.org/repo/flathub.flatpakrepo +flatpak install --user flathub io.dbeaver.DBeaverCommunity +# or: sudo snap install dbeaver-ce +``` + +**With pgAdmin 4**, if DBeaver is unavailable — it reads the live schema the same +way, so the result is equivalent in substance: + +1. Connect to the project database. +2. Right-click the database → **ERD For Database** (or open a blank ERD and drag + the `project` tables in). +3. Arrange the tables to mirror `ERModel_v01.png`. +4. **Download image** → PNG, then convert: + `convert relational_schema.png relational_schema.jpg` + +Whichever tool is used, state it here so the choice is explicit rather than +inferred. Do **not** substitute a tool that only reads the `.sql` file — such as +dbdiagram.io — because the diagram would then show what the script says rather +than what the deployed database contains, which is the thing this artefact is +meant to demonstrate. Index: docs/P2-RelationalDesign/RelationalDesignAIUsage.md =================================================================== --- docs/P2-RelationalDesign/RelationalDesignAIUsage.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P2-RelationalDesign/RelationalDesignAIUsage.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,70 @@ +# Relational Design AI Usage + +## Name of AI service/solution that was used + +**Claude Code** (Anthropic) + +- **URL:** https://claude.com/claude-code +- **Type of service/subscription:** Claude subscription, model Claude Opus 4.7 (1M context). + +## Final result + +### Diagram + +The student produces `relational_schema.jpg` in DBeaver from the live `project` schema; see [RelationalDesign](RelationalDesign.md) for instructions. + +### Results in details / description + +The AI: + +- Consolidated two inconsistent draft schemas (`server/db/db.sql` and `server/db/schema.sql`) into a single `schema_creation.sql`. +- Corrected foreign-key errors in the original (the `crypto_id` column in `holdings`, `orders`, and `transactions` had been pointed at both `users(id)` and `crypto(id)`; the AI split it into separate `user_id` and `crypto_id` columns per `ep-diagram.md`). +- Added two convenience views, `v_latest_prices` and `v_portfolio`, to keep the Go CLI simple. +- Produced a sample-data script `data_load.sql` that TRUNCATEs and re-inserts deterministic rows, so the "must work on empty DB and on a DB that already has data" requirement from P2 is met. +- Documented normalisation up to 3NF and the intentional denormalisation of `holdings.avg_price`. + +## Summary of AI involvement + +| | Session 1 — 2026-04-21 | Session 2 — 2026-08-06/07 | +|---|---|---| +| **What I brought** | My own draft SQL (`db.sql`, `schema.sql`) and the model in `ep-diagram.md` | The schema as it stood after session 1 | +| **What the AI did** | Reviewed my SQL, found the foreign-key errors, consolidated two inconsistent drafts into one script | Reviewed the schema again; one constraint change, plus documentation of the transformation | +| **What I decided** | Which corrections to adopt, to keep both balance columns, to drop the secret-question fields | To make `avg_price` `NOT NULL` rather than handle nulls in application code | + +The relational model in this phase is a transformation of *my* ER model, and the +foreign-key errors the AI found in session 1 were errors in *my* draft SQL — that +review is the single most useful thing the AI did on this phase. + +## Entire AI usage log + +See [ERModelAIUsage](../P1-ConceptualModel/ERModelAIUsage.md) — the full transcript of the 2026-04-21 conversation covers both P1 and P2 work. The specific prompts that drove the relational-design output were the same "make it work and make it fill in or to follow all of the needed instructions" instruction and the student's subsequent "do everything that you need to do". + +> **Student action required:** append any future consultations where you asked the AI to refine the schema, tune constraints, or write additional queries. + + +### Session 2 — 2026-08-06 / 2026-08-07 + +Prompts are logged in full in [ERModelAIUsage](../P1-ConceptualModel/ERModelAIUsage.md#session-2--2026-08-06--2026-08-07); +the one that drove this phase was *"Also fix some database things or golang +things if you think we can do it better"*. Changes to the P2 artefacts: + +- `holdings.avg_price` changed from nullable to `NOT NULL DEFAULT 0 CHECK + (avg_price >= 0)`. Reason: it feeds the P/L arithmetic in `v_portfolio`, and + SQL arithmetic involving `NULL` produces `NULL`, so a nullable average would + have silently blanked the unrealised-P/L column of a real position. A related + crash path in the Go code (scanning a `NULL` average into a non-nullable + `float64`, which was reported to the user as "Insufficient holding") was fixed + at the same time. +- [RelationalDesign](RelationalDesign.md) gained an explicit account of the + partial transformation: which ER construct each foreign key comes from, that + M:N relationships with attributes become tables whose foreign-key pair is a + `UNIQUE` constraint, and that total participation becomes `NOT NULL` — which is + why `transactions.related_order` is the one nullable foreign key. +- The candidate keys of `holdings` and `watchlist_items` are now documented as + the relationship keys `{user_id, crypto_id}` and `{watchlist_id, crypto_id}`. + +Both scripts were re-run end to end against PostgreSQL 16 after these changes. + +> **Still outstanding:** `relational_schema.jpg` must be exported from DBeaver +> against the faculty database. No AI involvement is possible there — it needs a +> live connection to your assigned database. Index: docs/P3-UseCaseModel/UseCase0001.md =================================================================== --- docs/P3-UseCaseModel/UseCase0001.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P3-UseCaseModel/UseCase0001.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,40 @@ +# Use-case 0001 — Register new account + +**Initiating actor:** Visitor + +**Other actors:** — + +A new person creates an account on EduBerza so they can later log in as a Trader. The system validates input, refuses duplicates, and stores a hashed password. + +## Scenario + +1. Visitor chooses "Register" from the anonymous menu. +2. System prompts for username, email, full name and password. +3. Visitor enters values. +4. System validates: + - username, email, password are non-empty. + - email contains `@`. + - password is at least 6 characters. +5. System checks whether the chosen username or email already exists: + + ```sql + SELECT EXISTS ( + SELECT 1 FROM project.users + WHERE username = $1 OR email = $2 + ); + ``` +6. If the row exists, system informs the user and the scenario ends. Otherwise it creates the account: + + ```sql + INSERT INTO project.users (username, email, full_name, password_hash, available_balance) + VALUES ($1, $2, $3, encode(digest($4, 'sha256'), 'hex'), 0); + ``` +7. System confirms success and returns to the anonymous menu; Visitor can then proceed to UC0002. + +### Alternate flow 3a — invalid email + +If step 4 fails email validation, system shows "Invalid email." and scenario returns to step 2. + +### Alternate flow 5a — duplicate + +If step 5 returns `true`, system shows "Username or email already taken." and scenario ends. Index: docs/P3-UseCaseModel/UseCase0002.md =================================================================== --- docs/P3-UseCaseModel/UseCase0002.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P3-UseCaseModel/UseCase0002.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,34 @@ +# Use-case 0002 — Log in + +**Initiating actor:** Visitor + +**Other actors:** — + +A registered user authenticates so the system can treat subsequent actions as a Trader. + +## Scenario + +1. Visitor chooses "Login" from the anonymous menu. +2. System prompts for username and password. +3. Visitor enters values. +4. System looks up the user: + + ```sql + SELECT id, password_hash + FROM project.users + WHERE username = $1; + ``` +5. If no row is returned, system responds "Invalid credentials." and scenario ends. +6. If a row is returned, system compares the stored hash against sha256 of the entered password. On mismatch it responds "Invalid credentials." and ends. +7. On match, system records the returned `id` and username in the session and displays the authenticated menu. + +### Alternate flow 4a — authenticated lookup with live balance + +The system may combine identity lookup with live balance in a single query, for use cases that need both: + +```sql +SELECT id, available_balance, invested_balance + FROM project.users + WHERE username = $1 + AND password_hash = encode(digest($2, 'sha256'), 'hex'); +``` Index: docs/P3-UseCaseModel/UseCase0003.md =================================================================== --- docs/P3-UseCaseModel/UseCase0003.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P3-UseCaseModel/UseCase0003.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,44 @@ +# Use-case 0003 — Deposit virtual funds + +**Initiating actor:** Trader + +**Other actors:** — + +A logged-in Trader tops up their virtual cash balance. This is a simulation-only operation; no real money changes hands. The operation writes to two tables — the user row and the ledger — inside a single transaction. + +## Scenario + +1. Trader chooses "Deposit virtual funds" from the authenticated menu. +2. System prompts for an amount in USD. +3. Trader enters an amount. +4. System validates: amount must parse as a positive number. +5. System opens a transaction and increments the balance: + + ```sql + BEGIN; + + UPDATE project.users + SET available_balance = available_balance + $1, + updated_at = now() + WHERE id = $2; + + INSERT INTO project.transactions (user_id, type, amount, currency, description) + VALUES ($2, 'deposit', $1, 'USD', 'Virtual deposit'); + + COMMIT; + ``` +6. System confirms "Deposited X USD." and returns to the authenticated menu. + +### Alternate flow 4a — invalid input + +If the amount is non-positive or non-numeric, system responds "Invalid amount." and scenario returns to step 2. + +### Verification query + +To see the balance after the deposit, the Trader can trigger UC0006, or directly: + +```sql +SELECT available_balance, invested_balance + FROM project.users + WHERE id = $1; +``` Index: docs/P3-UseCaseModel/UseCase0004.md =================================================================== --- docs/P3-UseCaseModel/UseCase0004.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P3-UseCaseModel/UseCase0004.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,83 @@ +# Use-case 0004 — Place market BUY order + +**Initiating actor:** Trader + +**Other actors:** Market Simulator (indirect — supplies the current price via `market_trades`). + +A Trader buys a crypto asset at the current market price. The operation touches five tables (`orders`, `users`, `holdings`, `transactions`, `market_trades`) and must either all succeed or all roll back. + +## Scenario + +1. Trader chooses "Place market BUY order". +2. System lists the available markets with their latest price: + + ```sql + SELECT m.id, c.symbol, m.quote_currency, COALESCE(lp.price, 0) + FROM project.markets m + JOIN project.crypto c ON c.id = m.crypto_id + LEFT JOIN project.v_latest_prices lp ON lp.market_id = m.id + WHERE m.is_active = true + ORDER BY c.symbol; + ``` +3. Trader enters a market symbol, e.g. `ETH`. +4. System resolves the market and looks up the latest price: + + ```sql + SELECT m.id, c.id AS crypto_id, c.symbol, m.quote_currency + FROM project.markets m + JOIN project.crypto c ON c.id = m.crypto_id + WHERE upper(c.symbol) = upper($1) AND m.is_active = true; + + SELECT price FROM project.v_latest_prices WHERE market_id = $2; + ``` +5. Trader enters a quantity. +6. System computes notional = quantity × price, opens a transaction, and does: + + ```sql + BEGIN; + + INSERT INTO project.orders + (user_id, market_id, side, type, status, quantity, price, executed_at) + VALUES + ($user_id, $market_id, 'buy', 'market', 'executed', $qty, $price, now()) + RETURNING id; -- captured as $order_id + + SELECT available_balance FROM project.users WHERE id = $user_id FOR UPDATE; + -- abort if available_balance < notional + + UPDATE project.users + SET available_balance = available_balance - $notional, + invested_balance = invested_balance + $notional, + updated_at = now() + WHERE id = $user_id; + + -- Upsert holding with running weighted-average price: + SELECT quantity, avg_price + FROM project.holdings + WHERE user_id = $user_id AND crypto_id = $crypto_id + FOR UPDATE; + + -- Either INSERT (new holding) or UPDATE (existing), computing + -- new_avg = (old_qty*old_avg + $qty*$price) / (old_qty + $qty) + + INSERT INTO project.transactions + (user_id, type, amount, currency, related_order, description) + VALUES + ($user_id, 'buy', -$notional, 'USD', $order_id, 'Market buy ...'); + + INSERT INTO project.market_trades + (market_id, executed_at, price, quantity, side, source) + VALUES + ($market_id, now(), $price, $qty, 'buy', 'user'); + + COMMIT; + ``` +7. System confirms: `Order executed: buy 0.0100 BTC @ 67140.000000 (notional 671.4000 USD)`. + +### Alternate flow 6a — insufficient funds + +If `available_balance < notional`, the entire transaction rolls back and system shows "Insufficient funds: need X, have Y." + +### Alternate flow 4a — market not found + +If the entered symbol does not match any active market, system shows "market X not found" and returns to the authenticated menu without opening a transaction. Index: docs/P3-UseCaseModel/UseCase0005.md =================================================================== --- docs/P3-UseCaseModel/UseCase0005.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P3-UseCaseModel/UseCase0005.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,66 @@ +# Use-case 0005 — Place market SELL order + +**Initiating actor:** Trader + +**Other actors:** Market Simulator (indirect — supplies the current price). + +A Trader sells part or all of a holding at the current market price. Cost basis is preserved so realised P/L can be reconstructed from the ledger. + +## Scenario + +1. Trader chooses "Place market SELL order". +2. System lists markets (same SQL as UC0004 step 2). +3. Trader enters market symbol and quantity. +4. System resolves the market and looks up the latest price (same SQL as UC0004 step 4). +5. System opens a transaction: + + ```sql + BEGIN; + + INSERT INTO project.orders + (user_id, market_id, side, type, status, quantity, price, executed_at) + VALUES + ($user_id, $market_id, 'sell', 'market', 'executed', $qty, $price, now()) + RETURNING id; -- $order_id + + SELECT quantity, avg_price + FROM project.holdings + WHERE user_id = $user_id AND crypto_id = $crypto_id + FOR UPDATE; + -- abort if row missing or quantity < $qty + ``` +6. If the holding check passes, system reduces the holding, credits cash and debits invested, and appends a ledger and a market trade: + + ```sql + UPDATE project.holdings + SET quantity = quantity - $qty, + updated_at = now() + WHERE user_id = $user_id AND crypto_id = $crypto_id; + + UPDATE project.users + SET available_balance = available_balance + $notional, + invested_balance = GREATEST(invested_balance - ($avg_price * $qty), 0), + updated_at = now() + WHERE id = $user_id; + + INSERT INTO project.transactions + (user_id, type, amount, currency, related_order, description) + VALUES + ($user_id, 'sell', $notional, 'USD', $order_id, 'Market sell ...'); + + INSERT INTO project.market_trades + (market_id, executed_at, price, quantity, side, source) + VALUES + ($market_id, now(), $price, $qty, 'sell', 'user'); + + COMMIT; + ``` +7. System confirms: `Order executed: sell 0.5000 ETH @ 3520.000000 (notional 1760.0000 USD)`. + +### Alternate flow 5a — insufficient holding + +If the `SELECT ... FOR UPDATE` returns no row, or the held quantity is smaller than the sell quantity, the entire transaction rolls back and system shows "Insufficient holding: trying to sell X, hold Y." + +### Realised P/L (post-scenario) + +The realised P/L for a sell is `$notional - ($avg_price * $qty)`. It is not persisted explicitly but can be computed from the ledger and the holding at sell time. Index: docs/P3-UseCaseModel/UseCase0006.md =================================================================== --- docs/P3-UseCaseModel/UseCase0006.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P3-UseCaseModel/UseCase0006.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,66 @@ +# Use-case 0006 — View portfolio and transaction history + +**Initiating actor:** Trader + +**Other actors:** — + +A Trader inspects their current holdings, unrealised P/L, cash balance and recent ledger. + +## Scenario + +### Portfolio + +1. Trader chooses "View portfolio". +2. System queries the `v_portfolio` view: + + ```sql + SELECT symbol, + quantity, + COALESCE(avg_price, 0), + COALESCE(current_price, 0), + COALESCE(market_value, 0), + COALESCE(unrealized_pnl, 0) + FROM project.v_portfolio + WHERE user_id = $1 + AND quantity > 0 + ORDER BY symbol; + ``` +3. System displays the rows and a computed summary: + + ```sql + SELECT available_balance, invested_balance + FROM project.users + WHERE id = $1; + ``` + +### Transaction history + +1. Trader chooses "View transaction history". +2. System queries the last 20 ledger entries: + + ```sql + SELECT created_at, type, amount, currency, COALESCE(description, '') + FROM project.transactions + WHERE user_id = $1 + ORDER BY created_at DESC + LIMIT 20; + ``` + +### Reference — how `v_portfolio` is defined + +```sql +CREATE OR REPLACE VIEW project.v_portfolio AS +SELECT h.user_id, + c.symbol, + h.quantity, + h.avg_price, + lp.price AS current_price, + (h.quantity * lp.price) AS market_value, + (h.quantity * (lp.price - h.avg_price)) AS unrealized_pnl + FROM project.holdings h + JOIN project.crypto c ON c.id = h.crypto_id + LEFT JOIN project.markets m + ON m.crypto_id = c.id AND m.quote_currency = 'USD' + LEFT JOIN project.v_latest_prices lp + ON lp.market_id = m.id; +``` Index: docs/P3-UseCaseModel/UseCase0007.md =================================================================== --- docs/P3-UseCaseModel/UseCase0007.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P3-UseCaseModel/UseCase0007.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,63 @@ +# Use-case 0007 — Manage watchlist + +**Initiating actor:** Trader + +**Other actors:** — + +A Trader keeps a list of crypto assets they want to monitor. Adding an asset that is already on the list is a no-op (idempotent). + +## Scenario + +1. Trader chooses "Manage watchlist". +2. System ensures the Trader has a default watchlist named "Favorites": + + ```sql + SELECT id FROM project.watchlists + WHERE user_id = $1 + ORDER BY created_at LIMIT 1; + + -- if no row: + INSERT INTO project.watchlists (user_id, name) + VALUES ($1, 'Favorites') + RETURNING id; + ``` +3. System shows the sub-menu: List / Add / Remove / Back. + +### List items + +```sql +SELECT c.symbol, c.name, COALESCE(lp.price, 0) + FROM project.watchlist_items wi + JOIN project.crypto c ON c.id = wi.crypto_id + LEFT JOIN project.markets m + ON m.crypto_id = c.id AND m.quote_currency = 'USD' + LEFT JOIN project.v_latest_prices lp + ON lp.market_id = m.id + WHERE wi.watchlist_id = $1 + ORDER BY c.symbol; +``` + +### Add a crypto + +```sql +-- 1. resolve the symbol to a crypto_id +SELECT id FROM project.crypto WHERE upper(symbol) = upper($1); + +-- 2. insert the item; do nothing if it's already there +INSERT INTO project.watchlist_items (watchlist_id, crypto_id) +VALUES ($watchlist_id, $crypto_id) +ON CONFLICT (watchlist_id, crypto_id) DO NOTHING; +``` + +### Remove a crypto + +```sql +DELETE FROM project.watchlist_items + WHERE watchlist_id = $1 + AND crypto_id = ( + SELECT id FROM project.crypto + WHERE upper(symbol) = upper($2) + ); +``` + +If the delete affects zero rows, system shows "Not in watchlist." Index: docs/P3-UseCaseModel/UseCaseModel.md =================================================================== --- docs/P3-UseCaseModel/UseCaseModel.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P3-UseCaseModel/UseCaseModel.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,34 @@ +# Use-case model + +## List of Actors / Roles + +- **Visitor** — Anyone browsing the platform without an account. Can only view public market information. + - [UC0001](UseCase0001.md) — Register new account + - [UC0002](UseCase0002.md) — Log in + +- **Trader** — A logged-in user managing their virtual funds and positions. + - [UC0003](UseCase0003.md) — Deposit virtual funds + - [UC0004](UseCase0004.md) — Place market BUY order + - [UC0005](UseCase0005.md) — Place market SELL order + - [UC0006](UseCase0006.md) — View portfolio and transaction history + - [UC0007](UseCase0007.md) — Manage watchlist + +- **Market Simulator** — An external automated system (the bot in `bots/`) that inserts simulated trades and candles into the database so prices move in the simulation. + +## Use-case model diagram (optional) + +*(Optional per the rubric; include one later if time allows.)* + +## Realization details on selection of the most important use cases + +Solo project → **at least 3 use cases required** (rubric: "at least 3 per team-member"). **7 use cases documented** for a safety margin. All are implemented in the P4 prototype; see `server/` for the Go source and [PrototypeImplementation](../P4-Prototype/PrototypeImplementation.md) for documented runs. + +| Use case | Importance | Why documented | +|----------------------------------------|------------|-------------------------------------------------------------------------------------------| +| [UC0001 — Register](UseCase0001.md) | High | Without it nothing else works; demonstrates `INSERT` with uniqueness check. | +| [UC0002 — Login](UseCase0002.md) | High | Authenticates every `Trader` action; demonstrates `SELECT` with parameter binding. | +| [UC0003 — Deposit](UseCase0003.md) | High | Shows a multi-row transaction: `UPDATE users` + `INSERT INTO transactions`. | +| [UC0004 — Buy](UseCase0004.md) | Very high | Core of the exchange: `INSERT orders`, `UPDATE users`, `UPSERT holdings`, ledger, trade. | +| [UC0005 — Sell](UseCase0005.md) | Very high | Dual of Buy; demonstrates row-level `FOR UPDATE` locking and cost-basis bookkeeping. | +| [UC0006 — Portfolio](UseCase0006.md) | High | Demonstrates joins over `holdings`, `markets`, `crypto`, and a view (`v_portfolio`). | +| [UC0007 — Watchlist](UseCase0007.md) | Medium | Demonstrates N-M relation handling and `ON CONFLICT` upsert semantics. | Index: docs/P3-UseCaseModel/UseCaseModelAIUsage.md =================================================================== --- docs/P3-UseCaseModel/UseCaseModelAIUsage.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P3-UseCaseModel/UseCaseModelAIUsage.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,56 @@ +# Use-Case Model AI Usage + +## Name of AI service/solution that was used + +**Claude Code** (Anthropic) + +- **URL:** https://claude.com/claude-code +- **Type of service/subscription:** Claude subscription, model Claude Opus 4.7 (1M context). + +## Final result + +### Results in details / description + +The AI: + +- Proposed the actor taxonomy (Visitor, Trader, Market Simulator) from the project description and the existing Go code. +- Derived a set of 7 use cases covering the full trading loop (register, login, deposit, buy, sell, view portfolio, watchlist). +- Wrote each `UseCaseXXXX.md` file with: + - initiating actor, other actors, goals + - step-by-step dialog-form scenario + - **tested** SQL statements for every database-touching step, using the `project` schema + - alternate flows for the most common failure cases (insufficient funds, insufficient holding, duplicate user, invalid input) + +## Summary of AI involvement + +| | Session 1 — 2026-04-21 | Session 2 — 2026-08-06/07 | +|---|---|---| +| **What I brought** | The project description in `opis.md` and the existing Go backend | The use-case model as completed in session 1 | +| **What the AI did** | Proposed the actor taxonomy and drafted seven use cases with tested SQL | Nothing — the model was not changed | +| **What I decided** | To go solo, and therefore to document seven use cases rather than the three the rubric requires | To re-verify every scenario's SQL against a live database rather than trust the April run | + +This phase was finished in session 1. In session 2 the only work was +verification: each scenario was executed against a live PostgreSQL 16 database, +including the failure paths, and the results recorded on the +`UseCaseXXXXImplementation` pages. + +## Entire AI usage log + +See [ERModelAIUsage](../P1-ConceptualModel/ERModelAIUsage.md) for the full transcript — the same 2026-04-21 session produced the use-case documentation. The defining student prompt was: + +> I'm going solo do everything that you need to do, and tell me after what do I need to do + +which led the AI to pick "solo → 3 minimum per rubric → document 7 for a margin" as a default. + +> **Student action required:** append any future refinements of the use-case list or scenarios here. + + +### Session 2 — 2026-08-06 / 2026-08-07 + +No changes were made to the use-case model in this session: the actor list, the +seven use cases and the scenario SQL in `UseCase0001`–`UseCase0007` are as +produced on 2026-04-21. The SQL in those scenarios was, however, re-verified by +executing the corresponding prototype flows against a live PostgreSQL 16 +database, including the failure paths (insufficient funds, insufficient holding, +duplicate registration, wrong password). The results are documented per use case +on the `UseCaseXXXXImplementation` pages. Index: docs/P4-Prototype/BuildInstructions.md =================================================================== --- docs/P4-Prototype/BuildInstructions.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P4-Prototype/BuildInstructions.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,194 @@ +# Build Instructions + +How to compile, configure, run and test the EduBerza prototype. +Linked from [PrototypeImplementation](PrototypeImplementation.md). + +## Development environment description + +| Tool | Version tested | Needed for | +|-------------|-----------------------|---------------------------------------------------------------| +| Go | 1.26 (1.25+ works) | Building `server/` (the CLI) and `bots/` (the market bot). | +| PostgreSQL | 16 | The database. Docker image or the faculty server. | +| Docker | any recent | Optional — brings up a local PostgreSQL in one command. | +| `psql` | any | Optional — running the SQL scripts by hand. | +| Java | 21 (8+ works) | Optional — only to open/edit the ER diagram in TerraER. | +| DBeaver | any recent | Optional — only to export `relational_schema.jpg`. | + +Nothing else has to be installed. The only third-party Go dependency +(`github.com/lib/pq`, the PostgreSQL driver) is fetched automatically by +`go build` from `go.mod`/`go.sum`. + +## Build instructions + +All commands run from the repository root. + +### 1. Configure the database connection + +```sh +cp .env.example .env +``` + +The defaults in `.env.example` match the bundled Docker setup. To use the +faculty database instead, either edit `.env` or pass the values as real +environment variables — those take precedence over the file: + +```sh +DBHOST=... DBPORT=5432 DBUSER=... DBPASSWORD=... DBNAME=... ./eduberza +``` + +`.env` is deliberately not committed (see `.gitignore`) because it holds a +password. + +### 2. Start PostgreSQL + +```sh +docker compose up -d +``` + +Skip this step if you are pointing at the faculty database. + +### 3. Build + +```sh +go build -o eduberza ./server +``` + +### 4. Create the schema and load the sample data + +```sh +./eduberza -init +``` + +This runs `server/db/schema_creation.sql` and then `server/db/data_load.sql`. +Both are **compiled into the binary** (`go:embed`), so `-init` works regardless +of which directory you launch it from. It is destructive and idempotent — it +drops and recreates the whole `project` schema, so it is also the reset button +if a demo goes wrong. To reload only the data, keeping the schema: + +```sh +./eduberza -load-data +``` + +The equivalent with `psql`, if you prefer to watch the statements run: + +```sh +psql "postgresql://$DBUSER:$DBPASSWORD@$DBHOST:$DBPORT/$DBNAME" \ + -f server/db/schema_creation.sql +psql "postgresql://$DBUSER:$DBPASSWORD@$DBHOST:$DBPORT/$DBNAME" \ + -f server/db/data_load.sql +``` + +### 5. Run the prototype + +```sh +./eduberza +``` + +Seed accounts — all with the password `test123`: + +| Username | Starting state | +|-----------|-------------------------------------------------------| +| `alice` | 8250.00 USD cash, holds 0.5 ETH — best demo account | +| `bob` | 5000.00 USD cash, no positions | +| `charlie` | 2500.00 USD cash, no positions | + +### 6. Optional — run the market simulation bot + +In a second terminal: + +```sh +go run ./bots +``` + +The bot walks the price of every active market, inserts a row into +`market_trades` on each tick and upserts the current 1-minute candle. Prices in +the CLI change while it runs, because the current price is always read from the +most recent trade (`v_latest_prices`), never from a stored column. + +## Testing instructions + +### Mini-guide to the application + +The CLI has two menus. Before logging in: **Register**, **Login**, +**Browse markets**. After logging in: **View balance**, **Deposit virtual +funds**, **Browse markets**, **Place market BUY order**, **Place market SELL +order**, **View portfolio**, **View transaction history**, **Manage watchlist**, +**Logout**. + +You never have to remember an identifier. Markets are always printed as a +numbered list with their current price before you are asked which one you want, +and assets are referred to by symbol (`BTC`, `ETH`, …), never by database id. + +### End-to-end smoke test + +Verified on 2026-08-07 against PostgreSQL 16 with freshly loaded sample data. +Expected values are exact. + +1. `./eduberza -init` — prints `Database initialised.` +2. `./eduberza`, then `[2] Login` → `alice` / `test123` → `Login successful.` +3. `[6] View portfolio` → one row: `ETH 0.5000` at avg 3500.000000, current + 3520.000000, value 1760.0000, unrealised P/L `+10.0000`. Cash available + 8250.0000, net worth 10010.0000. +4. `[4] Place market BUY order` → `BTC` → `0.01` → + `Order executed: buy 0.0100 BTC @ 67140.000000 (notional 671.4000 USD)`. +5. `[6] View portfolio` → now BTC *and* ETH, total value 2431.4000, cash + 7578.6000 (= 8250.00 − 671.40), net worth still 10010.0000. +6. `[5] Place market SELL order` → `ETH` → `0.5` → + `Order executed: sell 0.5000 ETH @ 3520.000000 (notional 1760.0000 USD)`. +7. `[7] View transaction history` → deposit, buy, buy, sell, newest first. +8. `[8] Manage watchlist` → `[1] List items` → alice's `Favorites` contains + BTC, ETH, SOL with live prices. +9. `[9] Logout`, then `[0] Exit`. + +### Testing the failure paths + +These matter more than the happy path, because they are what proves the +transactions actually roll back: + +- **Insufficient funds:** log in as `charlie` (2500 USD) and try to buy `1` BTC. + Expect `Insufficient funds: need 67140.0000, have 2500.0000` and *no* change + to any table — no order row, no ledger entry, no holding. +- **Insufficient holding:** as `bob` (no positions), try to sell `1` ETH. + Expect `Insufficient holding: trying to sell 1.0000, hold 0.0000`. +- **Duplicate registration:** register with username `alice`. Expect + `Username or email already taken.` +- **Wrong password:** log in as `alice` with any wrong password. Expect + `Invalid credentials.` — and note the same message for an unknown username, so + the prototype does not leak which accounts exist. + +### For the public presentation + +Demo with `alice` (already has a position, so the portfolio screen is not +empty), and register a brand-new account live to show UC0001. Run the bot in a +background terminal so the prices visibly move between two portfolio refreshes. + +## Editing the ER diagram + +TerraER is a third-party tool and is deliberately **not** committed to this +repository. Download the teacher's build from + and run it: + +```sh +java -jar TerraER3.11.jar # then File → Open → docs/ERModel_v01.xml +``` + +Save new versions as `ERModel_v02.xml`, `ERModel_v03.xml`, … and export a +matching PNG for each. TerraER does not add the extension itself — type +`.xml` explicitly or the file will not reopen. + +## Up-to-date source code + +The repository is pushed to the FINKI DEVELOP git server; see the Repositories +section in EPRMS for the clone URL and credentials. + +### About the source code + +- All source needed to run the prototype is in this repository: the CLI + (`server/`), the market bot (`bots/`), the DDL script and the sample-data + script (`server/db/`). +- Third-party Go libraries are **not** vendored — `go build` downloads + `github.com/lib/pq` using the pinned versions in `go.mod` and `go.sum`. +- Third-party executables are **not** committed. `.gitignore` excludes `*.jar`; + TerraER is downloaded from the URL above. +- No third-party images, styles or frameworks are used, and there are no + images in the prototype at all — the interface is text. Index: docs/P4-Prototype/PrototypeImplementation.md =================================================================== --- docs/P4-Prototype/PrototypeImplementation.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P4-Prototype/PrototypeImplementation.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,58 @@ +# Prototype Implementation + +The prototype is a Go command-line application in `server/` that works against +the `project` schema in PostgreSQL. It implements all seven use cases from +[UseCaseModel](../P3-UseCaseModel/UseCaseModel.md) — the rubric requires at least three — with +every database access shown as real, executed SQL. An auxiliary program in +`bots/` simulates a live market so prices move while the prototype is running. + +Build, configure, run and test instructions: [BuildInstructions](BuildInstructions.md). + +## Implemented use-cases + +| Page | Use-case | Source | +|------|----------|--------| +| [UseCase0001Implementation](UseCase0001Implementation.md) | Register a new account | `server/auth.go` | +| [UseCase0002Implementation](UseCase0002Implementation.md) | Log in | `server/auth.go` | +| [UseCase0003Implementation](UseCase0003Implementation.md) | Deposit virtual funds | `server/account.go` | +| [UseCase0004Implementation](UseCase0004Implementation.md) | Place market BUY order | `server/trade.go` | +| [UseCase0005Implementation](UseCase0005Implementation.md) | Place market SELL order | `server/trade.go` | +| [UseCase0006Implementation](UseCase0006Implementation.md) | View portfolio and history | `server/portfolio.go` | +| [UseCase0007Implementation](UseCase0007Implementation.md) | Manage watchlist | `server/watchlist.go` | + +Each page mirrors its P3 use-case page and adds the actual SQL emitted by the Go +code plus a screenshot of the corresponding run against the live database. + +## What the prototype demonstrates about the database design + +- **The current price is never stored as a column.** It is always the price of + the most recent row in `market_trades`, read through the `v_latest_prices` + view. Both the user's own fills and the bot's simulated trades feed the same + table, so there is exactly one definition of "the price". +- **Money movements are transactional.** Buying touches five tables — `orders`, + `users`, `holdings`, `transactions`, `market_trades` — inside one transaction. + A failed balance check rolls the whole thing back: after a rejected purchase + there is no order row, no ledger entry and no holding. This is verified in the + failure-path tests in [BuildInstructions](BuildInstructions.md). +- **Constraints do real work.** `UNIQUE (user_id, crypto_id)` on `holdings` is + what makes the `INSERT … ON CONFLICT DO UPDATE` upsert possible, so the + weighted-average entry price is recomputed by the database in one statement + instead of by a read-modify-write in application code. +- **No identifiers are ever typed.** Markets are listed with their prices before + any choice is made, and everything else is selected by symbol. + +## Known limitations + +Deliberately out of scope for a first prototype, and the natural content of the +later phases: + +- Only `market` orders execute. `limit` is accepted by the schema + (`orders.type`) but the matching logic is not implemented. +- Passwords are SHA-256 without a salt. Adequate to demonstrate that the + password itself is never stored; not adequate for real use. A proper + password hash belongs in P9 (security). +- Money is handled as `float64` in Go while the database columns are `numeric`. + All arithmetic that must be exact — the weighted average — is done in SQL for + that reason, but the Go side would need a decimal type for real use. +- There is no connection pooling configuration and no explicit isolation level; + both are P8 topics. Index: docs/P4-Prototype/PrototypeImplementationAIUsage.md =================================================================== --- docs/P4-Prototype/PrototypeImplementationAIUsage.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P4-Prototype/PrototypeImplementationAIUsage.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,117 @@ +# Prototype Implementation AI Usage + +## Name of AI service/solution that was used + +**Claude Code** (Anthropic) + +- **URL:** https://claude.com/claude-code +- **Type of service/subscription:** Claude subscription, model Claude Opus 4.7 (1M context). + +## Final result + +### Results in details / description + +During the 2026-04-21 session the AI: + +- Replaced the broken HTTP + frontend scaffolding (which the student had already decided to drop) with a single-binary CLI prototype in Go, split across `main.go`, `cli.go`, `auth.go`, `account.go`, `market.go`, `trade.go`, `portfolio.go`, `watchlist.go`. +- Consolidated the broken `db.go` — which used to drop every table on every startup — into a single `Connect()` plus explicit `-init` and `-load-data` flags. +- Moved `go.mod` from `server/` to the project root so the same module graph covers both `server/` and `bots/`. +- Fixed `go.mod`: removed the unused MySQL driver, promoted `github.com/lib/pq` to a direct dependency. +- Implemented the trade flows as real database transactions with row-level `FOR UPDATE` locks, cost-basis bookkeeping, and a ledger row per operation. +- Wrote a 140-line market-simulation bot that inserts trades and upserts 1-minute candles every tick. +- Exercised the full prototype end-to-end against a running PostgreSQL on port 5433 and verified a sample buy and portfolio view produced the expected numbers. + +### Test evidence + +``` +Order executed: buy 0.0100 BTC @ 67140.000000 (notional 671.4000 USD) + + Symbol Quantity Avg buy Current Value Unrealised P/L + BTC 0.0100 67140.000000 67140.000000 671.4000 +0.0000 + ETH 0.5000 3500.000000 3520.000000 1760.0000 +10.0000 + TOTAL 2431.4000 +10.0000 + + Cash available : 7578.6000 USD + Portfolio value: 2431.4000 USD + Net worth : 10010.0000 USD +``` + +## Summary of AI involvement + +| | Session 1 — 2026-04-21 | Session 2 — 2026-08-06/07 | +|---|---|---| +| **What I brought** | My existing Go backend (Chi HTTP handlers) and a half-finished frontend | The CLI prototype as it stood after session 1 | +| **What the AI did** | Rewrote the backend as a CLI covering UC0001–UC0007, wrote the market bot | Reviewed the code, found and fixed three bugs, improved the holding upsert | +| **What I decided** | To delete the frontend, to build a CLI rather than a web app, to keep the market simulator | To ask for a code review pass rather than documentation alone | + +The prototype was built in session 1 and worked. What session 2 added was a +review pass I asked for specifically because I have to defend this code in +person: it turned up a path-resolution bug that made my own documented build +instructions fail, an infinite loop at end of input, and an error check in the +wrong order that misreported database failures as "Insufficient holding". + +## Entire AI usage log + +See [ERModelAIUsage](../P1-ConceptualModel/ERModelAIUsage.md) for the full prompt/response transcript of the 2026-04-21 session — that single conversation produced all of the P1–P4 artefacts. The defining student prompt for P4 was: + +> do everything that you need to do, and tell me after what do I need to do + +which translated into the rewrite of the backend and the writing of this documentation. + +> **Student action required:** append any further prompts here where you asked the AI to debug, extend, or refactor the prototype. + + +### Session 2 — 2026-08-06 / 2026-08-07 + +Prompts are logged in full in [ERModelAIUsage](../P1-ConceptualModel/ERModelAIUsage.md#session-2--2026-08-06--2026-08-07). +The student asked the AI to complete the phase documentation and to *"fix some +database things or golang things if you think we can do it better"*. What the AI +changed in the prototype, and why: + +**Bugs found and fixed** + +1. `server/db/db.go` resolved `../.env` and `db/schema_creation.sql` as paths + relative to the working directory, so they only worked when the program was + started from inside `server/`. Following the documented instructions — build + from the repository root and run `./eduberza -init` — failed with + `password authentication failed for user "postgres"`, because the `.env` file + was never found and the defaults were used. The two SQL scripts are now + compiled into the binary with `go:embed`, and `.env` is located by searching + the working directory and every parent. Real environment variables now take + precedence over the file, which is what allows the prototype to be pointed at + the faculty database without editing anything. +2. `prompt()` in `server/cli.go` ignored the error from `ReadString`. At end of + input — Ctrl-D, or a scripted run — it returned an empty string forever and + the menu loop spun printing "Unknown option." without end. It now exits + cleanly. +3. On the sell path, `trade.go` checked `err == sql.ErrNoRows || held < qty` + before checking for other errors, so any scan failure was reported to the user + as "Insufficient holding" regardless of the real cause. The error check now + comes first. + +**Improvements** + +4. The holding upsert was a read-modify-write in Go (`SELECT ... FOR UPDATE`, + compute the new weighted average in `float64`, then `INSERT` or `UPDATE`). It + is now a single `INSERT … ON CONFLICT (user_id, crypto_id) DO UPDATE`, so the + average is recomputed by PostgreSQL in `numeric` arithmetic and the statement + relies on the unique constraint that the relational design already declared. +5. `TerraER3.11.jar` was removed from the repository and `.gitignore` now + excludes `*.jar`, because P4 requires third-party executables to be + downloaded rather than committed. `.env` is excluded too and `.env.example` + was added in its place. +6. `image.png`, a TradingView screenshot, was deleted — the project has no + licence to publish it and P4 requires explicit usage rights. + +**Verification** + +All seven use cases were executed against a live PostgreSQL 16 database. The +screenshots on the `UseCaseXXXXImplementation` pages are captures of those runs. +The four failure paths were tested, and the rollback behaviour was checked +directly in SQL: after a rejected purchase, the affected user has zero rows in +`orders`, `transactions` and `holdings`. + +> **Student action required:** read the changed files (`server/db/db.go`, +> `server/cli.go`, `server/trade.go`, `server/db/schema_creation.sql`) before the +> presentation. You will be asked how the buy transaction works, and the answer +> has to be yours. Index: docs/P4-Prototype/UseCase0001Implementation.md =================================================================== --- docs/P4-Prototype/UseCase0001Implementation.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P4-Prototype/UseCase0001Implementation.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,43 @@ +# Use-case 0001 Implementation — Register + +**Initiating actor:** Visitor. **Source file:** `server/auth.go`, function `Register`. + +## Scenario (implemented) + +1. **User** chooses option `[1] Register` from the anonymous menu. + + +2. **System** prompts for username, email, full name and password (`server/auth.go:20-23`). + +3. **User** enters the values. + +4. **System** validates (empty checks, `@` in email, ≥ 6 chars in password) and then asks the database: + + ```sql + SELECT EXISTS( + SELECT 1 FROM users WHERE username = $1 OR email = $2 + ); + ``` + + +5. **System** inserts the new row (password hashed in Go, not in SQL, to keep hashing identical between register and login): + + ```sql + INSERT INTO users (username, email, full_name, password_hash, available_balance) + VALUES ($1, $2, $3, $4, 0); + ``` + +6. **System** confirms: `Account created. You can now log in.` + + ![Registering a new account](screenshots/uc0001_register.png) + +## How to reproduce + +```sh +./eduberza -init # optional: reset to a known state +./eduberza +# choose [1] Register, then enter username / email / full name / password +``` + +The screenshot above is from an actual run: registering `marko` / +`marko@example.com`, followed by the confirmation line. Index: docs/P4-Prototype/UseCase0002Implementation.md =================================================================== --- docs/P4-Prototype/UseCase0002Implementation.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P4-Prototype/UseCase0002Implementation.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,32 @@ +# Use-case 0002 Implementation — Log in + +**Initiating actor:** Visitor. **Source file:** `server/auth.go`, function `Login` + `authenticate`. + +## Scenario (implemented) + +1. **User** chooses option `[2] Login`. + + +2. **System** prompts for username and password. + +3. **User** enters `alice` / `test123`. + +4. **System** looks up the user and compares hashes: + + ```sql + SELECT id, password_hash FROM users WHERE username = $1; + ``` + + The Go side (`authenticate` in `server/auth.go`) compares the returned `password_hash` against `sha256hex(entered_password)`. + +5. **System** on success stores `{UserID, Username}` in the in-process `Session` and shows the authenticated menu. + + ![A rejected login followed by a successful one](screenshots/uc0002_login.png) + +## Seed credentials + +| Username | Password | Balance | +|----------|----------|---------| +| `alice` | `test123` | 10000.00 USD | +| `bob` | `test123` | 5000.00 USD | +| `charlie` | `test123` | 2500.00 USD | Index: docs/P4-Prototype/UseCase0003Implementation.md =================================================================== --- docs/P4-Prototype/UseCase0003Implementation.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P4-Prototype/UseCase0003Implementation.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,37 @@ +# Use-case 0003 Implementation — Deposit + +**Initiating actor:** Trader. **Source file:** `server/account.go`, function `Deposit`. + +## Scenario (implemented) + +1. **User** chooses `[2] Deposit virtual funds`. +2. **System** prompts for an amount in USD. +3. **User** enters `2500`. +4. **System** opens a database transaction and runs: + + ```sql + BEGIN; + UPDATE users + SET available_balance = available_balance + $1, + updated_at = now() + WHERE id = $2; + INSERT INTO transactions (user_id, type, amount, currency, description) + VALUES ($2, 'deposit', $1, 'USD', 'Virtual deposit'); + COMMIT; + ``` + + ![Depositing 2500 USD, then checking the balance](screenshots/uc0003_deposit.png) + +5. **System** confirms: `Deposited 2500.0000 USD.` + +## Verification + +Right after the deposit, `[1] View balance` runs: + +```sql +SELECT available_balance, invested_balance FROM users WHERE id = $1; +``` + +In the screenshot above, alice starts from the seed state (available 8250.00, +invested 1750.00) and ends at available **10750.00** — increased by exactly the +2500.00 deposited, with `invested_balance` untouched. Index: docs/P4-Prototype/UseCase0004Implementation.md =================================================================== --- docs/P4-Prototype/UseCase0004Implementation.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P4-Prototype/UseCase0004Implementation.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,91 @@ +# Use-case 0004 Implementation — Buy + +**Initiating actor:** Trader. **Source file:** `server/trade.go`, function `PlaceOrder(s, "buy")`. + +## Scenario (implemented) + +1. **User** chooses `[4] Place market BUY order`. +2. **System** lists markets with their latest price (same SQL as UC0006 — via the `v_latest_prices` view). + + +3. **User** enters `BTC`. +4. **System** resolves the market and fetches the latest price: + + ```sql + SELECT m.id, c.id, c.symbol, m.quote_currency + FROM markets m + JOIN crypto c ON c.id = m.crypto_id + WHERE upper(c.symbol) = upper($1) AND m.is_active; + + SELECT price FROM v_latest_prices WHERE market_id = $2; + ``` + +5. **User** enters quantity `0.01`. +6. **System** executes a single database transaction — *all or nothing*: + + ```sql + BEGIN; + + -- (a) record the order + INSERT INTO orders + (user_id, market_id, side, type, status, quantity, price, executed_at) + VALUES + ($1, $2, 'buy', 'market', 'executed', $3, $4, now()) + RETURNING id; + + -- (b) lock and check the user balance + SELECT available_balance FROM users WHERE id = $1 FOR UPDATE; + + -- (c) move cash from available to invested + UPDATE users + SET available_balance = available_balance - $notional, + invested_balance = invested_balance + $notional, + updated_at = now() + WHERE id = $1; + + -- (d) upsert the holding, recomputing the weighted-average entry price + -- in one statement. Every SET expression sees the pre-update row, so + -- holdings.quantity below is still the old quantity. + INSERT INTO holdings (user_id, crypto_id, quantity, avg_price, updated_at) + VALUES ($1, $c, $3, $4, now()) + ON CONFLICT (user_id, crypto_id) DO UPDATE + SET avg_price = (holdings.quantity * holdings.avg_price + + EXCLUDED.quantity * EXCLUDED.avg_price) + / (holdings.quantity + EXCLUDED.quantity), + quantity = holdings.quantity + EXCLUDED.quantity, + updated_at = now(); + + -- (e) ledger entry + INSERT INTO transactions + (user_id, type, amount, currency, related_order, description) + VALUES + ($1, 'buy', -$notional, 'USD', $orderId, 'Market buy ...'); + + -- (f) record the resulting market trade + INSERT INTO market_trades + (market_id, executed_at, price, quantity, side, source) + VALUES + ($2, now(), $4, $3, 'buy', 'user'); + + COMMIT; + ``` + +7. **System** prints: `Order executed: buy 0.0100 BTC @ 67140.000000 (notional 671.4000 USD)`. + + ![Market list, then a filled BUY order](screenshots/uc0004_buy.png) + +## Verified run (from actual prototype execution) + +With seed data loaded: + +- **Before:** alice.available_balance = 8250.00, portfolio = { ETH: 0.5 }. +- **Command:** `buy 0.01 BTC`. +- **After:** alice.available_balance = 7578.60 (= 8250 − 671.40), portfolio = { BTC: 0.01 @ 67140, ETH: 0.5 @ 3500 }, net worth = 10010.00 USD (the +10 is the ETH unrealised P/L from the price moving from 3500 → 3520). + +## Failure path — insufficient funds + +If `available_balance < notional`, the `defer tx.Rollback()` in `server/trade.go` reverts all six statements and the user sees: + +``` +Insufficient funds: need X, have Y +``` Index: docs/P4-Prototype/UseCase0005Implementation.md =================================================================== --- docs/P4-Prototype/UseCase0005Implementation.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P4-Prototype/UseCase0005Implementation.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,58 @@ +# Use-case 0005 Implementation — Sell + +**Initiating actor:** Trader. **Source file:** `server/trade.go`, function `PlaceOrder(s, "sell")`. + +## Scenario (implemented) + +1. **User** chooses `[5] Place market SELL order`. +2. **System** lists markets (same as UC0004 step 2). +3. **User** enters market symbol, e.g. `ETH`, then quantity `0.5`. +4. **System** opens a transaction and runs: + + ```sql + BEGIN; + + INSERT INTO orders + (user_id, market_id, side, type, status, quantity, price, executed_at) + VALUES + ($1, $2, 'sell', 'market', 'executed', $3, $4, now()) + RETURNING id; + + SELECT quantity, avg_price FROM holdings + WHERE user_id = $1 AND crypto_id = $c FOR UPDATE; + -- abort if missing or insufficient + + UPDATE holdings + SET quantity = quantity - $qty, updated_at = now() + WHERE user_id = $1 AND crypto_id = $c; + + UPDATE users + SET available_balance = available_balance + $notional, + invested_balance = GREATEST(invested_balance - ($avg * $qty), 0), + updated_at = now() + WHERE id = $1; + + INSERT INTO transactions + (user_id, type, amount, currency, related_order, description) + VALUES + ($1, 'sell', $notional, 'USD', $orderId, 'Market sell ...'); + + INSERT INTO market_trades + (market_id, executed_at, price, quantity, side, source) + VALUES + ($2, now(), $price, $qty, 'sell', 'user'); + + COMMIT; + ``` + + ![Selling 0.5 ETH at the current market price](screenshots/uc0005_sell.png) + +5. **System** prints: `Order executed: sell 0.5000 ETH @ 3520.000000 (notional 1760.0000 USD)`. + +## Failure path — insufficient holding + +If the holding does not exist or `quantity < requested`, the `defer tx.Rollback()` in `server/trade.go` reverts every statement above and the user sees: + +``` +Insufficient holding: trying to sell X, hold Y +``` Index: docs/P4-Prototype/UseCase0006Implementation.md =================================================================== --- docs/P4-Prototype/UseCase0006Implementation.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P4-Prototype/UseCase0006Implementation.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,59 @@ +# Use-case 0006 Implementation — Portfolio & transactions + +**Initiating actor:** Trader. **Source files:** `server/portfolio.go` (`ShowPortfolio`), `server/account.go` (`ShowTransactions`, `ShowBalance`). + +## Scenario (implemented) + +### Portfolio + +1. **User** chooses `[6] View portfolio`. +2. **System** runs: + + ```sql + SELECT symbol, quantity, + COALESCE(avg_price, 0), + COALESCE(current_price, 0), + COALESCE(market_value, 0), + COALESCE(unrealized_pnl, 0) + FROM v_portfolio + WHERE user_id = $1 AND quantity > 0 + ORDER BY symbol; + ``` +3. **System** renders a table with a totals row, then prints the cash summary: + + ```sql + SELECT available_balance, invested_balance FROM users WHERE id = $1; + ``` + + ![screenshot: portfolio view](screenshots/uc0006_portfolio.png) + +### Verified run + +With the seed data (`data_load.sql`), immediately after login, alice's portfolio prints: + +``` + Symbol Quantity Avg buy Current Value Unrealised P/L + ------------------------------------------------------------------------------------ + ETH 0.5000 3500.000000 3520.000000 1760.0000 +10.0000 + ------------------------------------------------------------------------------------ + TOTAL 1760.0000 +10.0000 + + Cash available : 8250.0000 USD + Portfolio value: 1760.0000 USD + Net worth : 10010.0000 USD +``` + +### Transaction history + +1. **User** chooses `[7] View transaction history`. +2. **System** runs: + + ```sql + SELECT created_at, type, amount, currency, COALESCE(description, '') + FROM transactions + WHERE user_id = $1 + ORDER BY created_at DESC + LIMIT 20; + ``` + + ![screenshot: transaction history](screenshots/uc0006_history.png) Index: docs/P4-Prototype/UseCase0007Implementation.md =================================================================== --- docs/P4-Prototype/UseCase0007Implementation.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/P4-Prototype/UseCase0007Implementation.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,50 @@ +# Use-case 0007 Implementation — Watchlist + +**Initiating actor:** Trader. **Source file:** `server/watchlist.go`. + +## Scenario (implemented) + +1. **User** chooses `[8] Manage watchlist`. +2. **System** ensures a default watchlist exists: + + ```sql + SELECT id FROM watchlists WHERE user_id = $1 ORDER BY created_at LIMIT 1; + -- else + INSERT INTO watchlists (user_id, name) VALUES ($1, 'Favorites') RETURNING id; + ``` + +3. **System** offers the submenu: List, Add, Remove, Back. + +### List + +```sql +SELECT c.symbol, c.name, COALESCE(lp.price, 0) + FROM watchlist_items wi + JOIN crypto c ON c.id = wi.crypto_id + LEFT JOIN markets m ON m.crypto_id = c.id AND m.quote_currency = 'USD' + LEFT JOIN v_latest_prices lp ON lp.market_id = m.id + WHERE wi.watchlist_id = $1 + ORDER BY c.symbol; +``` + +![Adding DOGE to the watchlist, then listing it](screenshots/uc0007_watchlist.png) + +### Add + +```sql +SELECT id FROM crypto WHERE upper(symbol) = upper($1); + +INSERT INTO watchlist_items (watchlist_id, crypto_id) +VALUES ($watchlist_id, $crypto_id) +ON CONFLICT (watchlist_id, crypto_id) DO NOTHING; +``` + +Re-adding the same symbol is a no-op thanks to the unique constraint + `ON CONFLICT`. + +### Remove + +```sql +DELETE FROM watchlist_items + WHERE watchlist_id = $1 + AND crypto_id = (SELECT id FROM crypto WHERE upper(symbol) = upper($2)); +``` Index: docs/README.md =================================================================== --- docs/README.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ docs/README.md (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,109 @@ +# EduBerza + +## Short description + +*P0 — write 150–200 words here in your own words. AI-generated text is not +allowed in this phase.* + +*Your own Macedonian draft is in [`opis.md`](P0-ProjectDefinition/opis.md); the material to cover is +what data the database holds (users, crypto assets, markets, orders, holdings, +transactions, market trades, candles, watchlists), who it is for (beginner +traders), and what kind of project it is.* + +## Team members + +- *Your First Name Last Name — Index XXXXXX* + +## Course + +Databases in 2025/2026/Winter + +## Under the supervision of + +Prof. Dr. Vangel V. Ajanovski + +## How this folder maps to the wiki + +The files here are grouped into one folder per phase for convenience. **The wiki +is flat** — when you publish, each file becomes a page named exactly after the +file, with no folder prefix and with capitalisation preserved: `About`, +`ERModel`, `RelationalDesign`, `UseCaseModel`, `UseCase0001`, …, +`PrototypeImplementation`, `BuildInstructions`, and the four `*AIUsage` pages. +Attachments (`ERModel_v01.xml`, `ERModel_v01.png`, `schema_creation.sql`, +`data_load.sql`, `relational_schema.jpg`, the screenshots) attach to the page +that documents them. + +| Folder | Phase | Wiki pages produced | +|--------|-------|---------------------| +| `P0-ProjectDefinition/` | P0 | `About` (+ this front page) | +| `P1-ConceptualModel/` | P1 | `ERModel`, `ERModelAIUsage` | +| `P2-RelationalDesign/` | P2 | `RelationalDesign`, `RelationalDesignAIUsage` | +| `P3-UseCaseModel/` | P3 | `UseCaseModel`, `UseCase0001`–`UseCase0007`, `UseCaseModelAIUsage` | +| `P4-Prototype/` | P4 | `PrototypeImplementation`, `UseCase000XImplementation`, `BuildInstructions`, `PrototypeImplementationAIUsage` | + +`Instructions.md` is the condensed course rubric — reference material, not a +submission. `P0-ProjectDefinition/opis.md` and `P1-ConceptualModel/ep-diagram.md` +are your own working notes, kept because they are the evidence that the initial +model was yours before any AI was used. + +The two SQL scripts deliberately stay in `server/db/` rather than moving into +`P2-RelationalDesign/`: they are compiled into the prototype binary with +`go:embed`, which requires them to sit inside the Go package. Attach them to the +`RelationalDesign` page from there. + +## Content + +| Phase | Link | Status | +|-------|------|--------| +| P0 | [About](P0-ProjectDefinition/About.md) | Draft — needs your own text | +| P1 | [ERModel](P1-ConceptualModel/ERModel.md) | Finished, awaiting approval | +| P2 | [RelationalDesign](P2-RelationalDesign/RelationalDesign.md) | Finished, awaiting approval | +| P3 | [UseCaseModel](P3-UseCaseModel/UseCaseModel.md) | Finished, awaiting approval | +| P4 | [PrototypeImplementation](P4-Prototype/PrototypeImplementation.md) | Finished, awaiting approval | +| P5 | *Normalization* | Not started | +| P6 | *Complex DB Reports* | Not started | +| P7 | *Advanced Database Development* | Not started | +| P8 | *Advanced Application Development* | Not started | +| P9 | *Other topics (Performance, Security)* | Not started | + +Keep the Status column current yourself — *started → completed → revised → +approved → final* — after each consultation. + +Note: P0–P4 alone are not enough for a passing grade; at least some of P5–P9 are +required. P5 is the recommended next one. + +## Phase attachments + +| File | Phase | What | +|------|-------|------| +| [`ERModel_v01.xml`](P1-ConceptualModel/ERModel_v01.xml) | P1 | TerraER source of the ER diagram | +| [`ERModel_v01.png`](P1-ConceptualModel/ERModel_v01.png) | P1 | Exported diagram image | +| [`../server/db/schema_creation.sql`](../server/db/schema_creation.sql) | P2 | DDL — drops and recreates the `project` schema | +| [`../server/db/data_load.sql`](../server/db/data_load.sql) | P2 | DML — truncates and reloads sample data | +| [`relational_schema.jpg`](P2-RelationalDesign/relational_schema.jpg) | P2 | Crow's-foot diagram exported from DBeaver | + +## Use cases (P3) + +[UC0001](P3-UseCaseModel/UseCase0001.md) Register · +[UC0002](P3-UseCaseModel/UseCase0002.md) Log in · +[UC0003](P3-UseCaseModel/UseCase0003.md) Deposit · +[UC0004](P3-UseCaseModel/UseCase0004.md) Buy · +[UC0005](P3-UseCaseModel/UseCase0005.md) Sell · +[UC0006](P3-UseCaseModel/UseCase0006.md) Portfolio · +[UC0007](P3-UseCaseModel/UseCase0007.md) Watchlist + +## AI usage logs + +Required by the phase rules for every phase where AI was used. P0 does not have +one because AI use is forbidden there. + +- [ERModelAIUsage](P1-ConceptualModel/ERModelAIUsage.md) (P1) +- [RelationalDesignAIUsage](P2-RelationalDesign/RelationalDesignAIUsage.md) (P2) +- [UseCaseModelAIUsage](P3-UseCaseModel/UseCaseModelAIUsage.md) (P3) +- [PrototypeImplementationAIUsage](P4-Prototype/PrototypeImplementationAIUsage.md) (P4) + +## Build & run + +See [BuildInstructions](P4-Prototype/BuildInstructions.md), or [`../README.md`](../README.md) +for the four-command quick start. +# bp Index: go.mod =================================================================== --- go.mod (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ go.mod (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,5 @@ +module bp_project + +go 1.25 + +require github.com/lib/pq v1.10.9 Index: go.sum =================================================================== --- go.sum (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ go.sum (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,2 @@ +github.com/lib/pq v1.10.9 h1:YXG7RB+JIjhP29X+OtkiDnYaXQwpS4JEWq7dtCCRUEw= +github.com/lib/pq v1.10.9/go.mod h1:AlVN5x4E4T544tWzH6hKfbfQvm3HdbOxrmggDNAPY9o= Index: server/account.go =================================================================== --- server/account.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/account.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,98 @@ +package main + +import ( + "fmt" + "strconv" + "strings" + + "bp_project/server/db" +) + +// ShowBalance prints the logged-in user's balances. +func ShowBalance(s *Session) { + var avail, invested float64 + err := db.DB.QueryRow( + `SELECT available_balance, invested_balance FROM users WHERE id = $1`, + s.UserID, + ).Scan(&avail, &invested) + if err != nil { + fmt.Println("Error:", err) + return + } + fmt.Printf("\n Available: %.4f USD\n", avail) + fmt.Printf(" Invested : %.4f USD\n", invested) + fmt.Printf(" Total : %.4f USD\n", avail+invested) +} + +// Deposit - UC0003 +// Transactional: updates users.available_balance and inserts a ledger row. +func Deposit(s *Session) { + fmt.Println("\n-- Deposit virtual funds --") + amtStr := prompt("Amount (USD): ") + amt, err := strconv.ParseFloat(amtStr, 64) + if err != nil || amt <= 0 { + fmt.Println("Invalid amount.") + return + } + + tx, err := db.DB.Begin() + if err != nil { + fmt.Println("Error:", err) + return + } + defer tx.Rollback() + + if _, err := tx.Exec( + `UPDATE users + SET available_balance = available_balance + $1, + updated_at = now() + WHERE id = $2`, + amt, s.UserID, + ); err != nil { + fmt.Println("Error:", err) + return + } + if _, err := tx.Exec( + `INSERT INTO transactions (user_id, type, amount, currency, description) + VALUES ($1, 'deposit', $2, 'USD', 'Virtual deposit')`, + s.UserID, amt, + ); err != nil { + fmt.Println("Error:", err) + return + } + if err := tx.Commit(); err != nil { + fmt.Println("Error:", err) + return + } + fmt.Printf("Deposited %.4f USD.\n", amt) +} + +// ShowTransactions lists the last 20 ledger entries for the user. +func ShowTransactions(s *Session) { + rows, err := db.DB.Query( + `SELECT created_at, type, amount, currency, COALESCE(description, '') + FROM transactions + WHERE user_id = $1 + ORDER BY created_at DESC + LIMIT 20`, + s.UserID, + ) + if err != nil { + fmt.Println("Error:", err) + return + } + defer rows.Close() + + fmt.Println() + fmt.Printf(" %-20s %-8s %12s %-3s %s\n", "When", "Type", "Amount", "Cur", "Description") + fmt.Println(" " + strings.Repeat("-", 70)) + for rows.Next() { + var when, typ, cur, desc string + var amt float64 + if err := rows.Scan(&when, &typ, &amt, &cur, &desc); err != nil { + fmt.Println("scan error:", err) + return + } + fmt.Printf(" %-20s %-8s %12.4f %-3s %s\n", when[:19], typ, amt, cur, desc) + } +} Index: server/auth.go =================================================================== --- server/auth.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/auth.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,108 @@ +package main + +import ( + "crypto/sha256" + "database/sql" + "encoding/hex" + "errors" + "fmt" + "strings" + + "bp_project/server/db" +) + +func hashPassword(pw string) string { + sum := sha256.Sum256([]byte(pw)) + return hex.EncodeToString(sum[:]) +} + +// Register - UC0001 +func Register() { + fmt.Println("\n-- Register --") + username := prompt("Username: ") + email := prompt("Email: ") + fullName := prompt("Full name: ") + pw := prompt("Password (min 6 chars): ") + + if username == "" || email == "" || pw == "" { + fmt.Println("Username, email and password are required.") + return + } + if !strings.Contains(email, "@") { + fmt.Println("Invalid email.") + return + } + if len(pw) < 6 { + fmt.Println("Password must be at least 6 characters.") + return + } + + var exists bool + err := db.DB.QueryRow( + `SELECT EXISTS(SELECT 1 FROM users WHERE username = $1 OR email = $2)`, + username, email, + ).Scan(&exists) + if err != nil { + fmt.Println("Database error:", err) + return + } + if exists { + fmt.Println("Username or email already taken.") + return + } + + _, err = db.DB.Exec( + `INSERT INTO users (username, email, full_name, password_hash, available_balance) + VALUES ($1, $2, $3, $4, 0)`, + username, email, fullName, hashPassword(pw), + ) + if err != nil { + fmt.Println("Failed to register:", err) + return + } + fmt.Println("Account created. You can now log in.") +} + +// Login - UC0002 +func Login(s *Session) { + fmt.Println("\n-- Login --") + username := prompt("Username: ") + pw := prompt("Password: ") + if username == "" || pw == "" { + fmt.Println("Username and password are required.") + return + } + + id, err := authenticate(username, pw) + if err != nil { + if errors.Is(err, errInvalidCreds) { + fmt.Println("Invalid credentials.") + return + } + fmt.Println("Login error:", err) + return + } + s.UserID = id + s.Username = username + fmt.Println("Login successful.") +} + +var errInvalidCreds = errors.New("invalid credentials") + +func authenticate(username, pw string) (string, error) { + var id, stored string + err := db.DB.QueryRow( + `SELECT id, password_hash FROM users WHERE username = $1`, + username, + ).Scan(&id, &stored) + if err == sql.ErrNoRows { + return "", errInvalidCreds + } + if err != nil { + return "", err + } + if stored != hashPassword(pw) { + return "", errInvalidCreds + } + return id, nil +} Index: server/cli.go =================================================================== --- server/cli.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/cli.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,106 @@ +package main + +import ( + "bufio" + "fmt" + "os" + "strings" +) + +// Session holds the currently-logged-in user, if any. +type Session struct { + UserID string + Username string +} + +var stdin = bufio.NewReader(os.Stdin) + +func prompt(label string) string { + fmt.Print(label) + line, err := stdin.ReadString('\n') + // On EOF (Ctrl-D, or the end of a piped script) ReadString keeps returning + // an error forever. Without this the menu loop would spin printing + // "Unknown option." indefinitely instead of ending. + if err != nil && strings.TrimSpace(line) == "" { + fmt.Println("\nInput closed. Goodbye.") + os.Exit(0) + } + return strings.TrimSpace(line) +} + +func RunCLI() { + fmt.Println("=========================================") + fmt.Println(" EduBerza - Crypto Exchange Simulation") + fmt.Println("=========================================") + + var s Session + for { + if s.UserID == "" { + anonymousMenu(&s) + } else { + authenticatedMenu(&s) + } + } +} + +func anonymousMenu(s *Session) { + fmt.Println() + fmt.Println("[1] Register") + fmt.Println("[2] Login") + fmt.Println("[3] Browse markets") + fmt.Println("[0] Exit") + switch prompt("> ") { + case "1": + Register() + case "2": + Login(s) + case "3": + ListMarkets() + case "0": + fmt.Println("Goodbye.") + os.Exit(0) + default: + fmt.Println("Unknown option.") + } +} + +func authenticatedMenu(s *Session) { + fmt.Printf("\n--- Logged in as %s ---\n", s.Username) + fmt.Println("[1] View balance") + fmt.Println("[2] Deposit virtual funds") + fmt.Println("[3] Browse markets") + fmt.Println("[4] Place market BUY order") + fmt.Println("[5] Place market SELL order") + fmt.Println("[6] View portfolio") + fmt.Println("[7] View transaction history") + fmt.Println("[8] Manage watchlist") + fmt.Println("[9] Logout") + fmt.Println("[0] Exit") + switch prompt("> ") { + case "1": + ShowBalance(s) + case "2": + Deposit(s) + case "3": + ListMarkets() + case "4": + PlaceOrder(s, "buy") + case "5": + PlaceOrder(s, "sell") + case "6": + ShowPortfolio(s) + case "7": + ShowTransactions(s) + case "8": + ManageWatchlist(s) + case "9": + s.UserID = "" + s.Username = "" + fmt.Println("Logged out.") + case "0": + fmt.Println("Goodbye.") + os.Exit(0) + default: + fmt.Println("Unknown option.") + } +} Index: server/db/data_load.sql =================================================================== --- server/db/data_load.sql (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/db/data_load.sql (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,144 @@ +-- data_load.sql +-- EduBerza - sample data +-- Course: Databases 2025/2026 Winter, FINKI UKIM +-- +-- Idempotent. Truncates all tables in the `project` schema and reloads +-- deterministic sample data. Run schema_creation.sql first if tables do +-- not yet exist. +-- +-- All sample users have the password: test123 + +SET search_path TO project, public; + +TRUNCATE TABLE + project.watchlist_items, + project.watchlists, + project.market_candles, + project.market_trades, + project.transactions, + project.orders, + project.holdings, + project.markets, + project.crypto, + project.users +RESTART IDENTITY CASCADE; + +-- ============================================================================ +-- CRYPTO +-- ============================================================================ +INSERT INTO project.crypto (id, symbol, name) VALUES + ('11111111-1111-1111-1111-111111111111', 'BTC', 'Bitcoin'), + ('22222222-2222-2222-2222-222222222222', 'ETH', 'Ethereum'), + ('33333333-3333-3333-3333-333333333333', 'ADA', 'Cardano'), + ('44444444-4444-4444-4444-444444444444', 'SOL', 'Solana'), + ('55555555-5555-5555-5555-555555555555', 'DOGE', 'Dogecoin'); + +-- ============================================================================ +-- MARKETS (all quoted in USD) +-- ============================================================================ +INSERT INTO project.markets (id, crypto_id, quote_currency, is_active) VALUES + ('a1111111-1111-1111-1111-111111111111', '11111111-1111-1111-1111-111111111111', 'USD', true), + ('a2222222-2222-2222-2222-222222222222', '22222222-2222-2222-2222-222222222222', 'USD', true), + ('a3333333-3333-3333-3333-333333333333', '33333333-3333-3333-3333-333333333333', 'USD', true), + ('a4444444-4444-4444-4444-444444444444', '44444444-4444-4444-4444-444444444444', 'USD', true), + ('a5555555-5555-5555-5555-555555555555', '55555555-5555-5555-5555-555555555555', 'USD', true); + +-- ============================================================================ +-- USERS +-- Password for all: test123 (stored as sha256 hex hash) +-- ============================================================================ +INSERT INTO project.users (id, username, email, full_name, password_hash, available_balance, invested_balance) VALUES + ('b1111111-1111-1111-1111-111111111111', 'alice', 'alice@example.com', 'Alice Johnson', + encode(digest('test123', 'sha256'), 'hex'), 10000.0000, 0), + ('b2222222-2222-2222-2222-222222222222', 'bob', 'bob@example.com', 'Bob Smith', + encode(digest('test123', 'sha256'), 'hex'), 5000.0000, 0), + ('b3333333-3333-3333-3333-333333333333', 'charlie', 'charlie@example.com', 'Charlie Davis', + encode(digest('test123', 'sha256'), 'hex'), 2500.0000, 0); + +-- ============================================================================ +-- MARKET TRADES +-- Recent simulated trades per market, used as price source. +-- ============================================================================ +INSERT INTO project.market_trades (market_id, executed_at, price, quantity, side, source) VALUES + -- BTC/USD around $67,000 + ('a1111111-1111-1111-1111-111111111111', now() - interval '10 min', 66850.250000, 0.120000, 'buy', 'simulation'), + ('a1111111-1111-1111-1111-111111111111', now() - interval '8 min', 66910.500000, 0.075000, 'sell', 'simulation'), + ('a1111111-1111-1111-1111-111111111111', now() - interval '5 min', 67020.750000, 0.200000, 'buy', 'simulation'), + ('a1111111-1111-1111-1111-111111111111', now() - interval '2 min', 67105.100000, 0.050000, 'buy', 'simulation'), + ('a1111111-1111-1111-1111-111111111111', now() - interval '30 second', 67140.000000, 0.030000, 'sell', 'simulation'), + -- ETH/USD around $3,500 + ('a2222222-2222-2222-2222-222222222222', now() - interval '10 min', 3490.500000, 1.500000, 'buy', 'simulation'), + ('a2222222-2222-2222-2222-222222222222', now() - interval '6 min', 3502.750000, 0.800000, 'sell', 'simulation'), + ('a2222222-2222-2222-2222-222222222222', now() - interval '2 min', 3515.250000, 2.100000, 'buy', 'simulation'), + ('a2222222-2222-2222-2222-222222222222', now() - interval '30 second', 3520.000000, 0.650000, 'buy', 'simulation'), + -- ADA/USD around $0.45 + ('a3333333-3333-3333-3333-333333333333', now() - interval '10 min', 0.446500, 500.000000, 'buy', 'simulation'), + ('a3333333-3333-3333-3333-333333333333', now() - interval '3 min', 0.452000, 1200.000000, 'buy', 'simulation'), + ('a3333333-3333-3333-3333-333333333333', now() - interval '30 second', 0.453750, 800.000000, 'sell', 'simulation'), + -- SOL/USD around $165 + ('a4444444-4444-4444-4444-444444444444', now() - interval '10 min', 164.250000, 10.000000, 'buy', 'simulation'), + ('a4444444-4444-4444-4444-444444444444', now() - interval '4 min', 165.500000, 5.500000, 'sell', 'simulation'), + ('a4444444-4444-4444-4444-444444444444', now() - interval '30 second', 166.100000, 8.000000, 'buy', 'simulation'), + -- DOGE/USD around $0.12 + ('a5555555-5555-5555-5555-555555555555', now() - interval '10 min', 0.118500, 10000.000000, 'buy', 'simulation'), + ('a5555555-5555-5555-5555-555555555555', now() - interval '3 min', 0.121250, 7500.000000, 'sell', 'simulation'), + ('a5555555-5555-5555-5555-555555555555', now() - interval '30 second', 0.122000, 12000.000000, 'buy', 'simulation'); + +-- ============================================================================ +-- MARKET CANDLES (1h aggregates, last 5 hours per market) +-- ============================================================================ +INSERT INTO project.market_candles (market_id, timeframe, open, high, low, close, volume, candle_time) VALUES + ('a1111111-1111-1111-1111-111111111111', '1h', 66200, 66500, 66050, 66400, 12.50, date_trunc('hour', now() - interval '5 hour')), + ('a1111111-1111-1111-1111-111111111111', '1h', 66400, 66800, 66380, 66700, 15.30, date_trunc('hour', now() - interval '4 hour')), + ('a1111111-1111-1111-1111-111111111111', '1h', 66700, 66950, 66650, 66900, 11.80, date_trunc('hour', now() - interval '3 hour')), + ('a1111111-1111-1111-1111-111111111111', '1h', 66900, 67100, 66800, 67050, 14.20, date_trunc('hour', now() - interval '2 hour')), + ('a1111111-1111-1111-1111-111111111111', '1h', 67050, 67200, 66900, 67140, 10.75, date_trunc('hour', now() - interval '1 hour')), + ('a2222222-2222-2222-2222-222222222222', '1h', 3460, 3490, 3450, 3485, 120.0, date_trunc('hour', now() - interval '5 hour')), + ('a2222222-2222-2222-2222-222222222222', '1h', 3485, 3510, 3480, 3500, 135.0, date_trunc('hour', now() - interval '4 hour')), + ('a2222222-2222-2222-2222-222222222222', '1h', 3500, 3520, 3495, 3515, 110.0, date_trunc('hour', now() - interval '3 hour')), + ('a2222222-2222-2222-2222-222222222222', '1h', 3515, 3525, 3500, 3520, 125.5, date_trunc('hour', now() - interval '2 hour')), + ('a2222222-2222-2222-2222-222222222222', '1h', 3520, 3530, 3510, 3520, 140.0, date_trunc('hour', now() - interval '1 hour')); + +-- ============================================================================ +-- EXAMPLE ORDERS, HOLDINGS AND TRANSACTIONS for alice +-- 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 + ('c1111111-1111-1111-1111-111111111111', + 'b1111111-1111-1111-1111-111111111111', + 'a2222222-2222-2222-2222-222222222222', + 'buy', 'market', 'executed', 0.5000, 3500.000000, + now() - interval '1 hour', now() - interval '1 hour'); + +INSERT INTO project.holdings (user_id, crypto_id, quantity, avg_price, updated_at) VALUES + ('b1111111-1111-1111-1111-111111111111', + '22222222-2222-2222-2222-222222222222', + 0.5000, 3500.000000, now() - interval '1 hour'); + +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'), + ('b1111111-1111-1111-1111-111111111111', 'buy', -1750.0000, 'USD', + 'c1111111-1111-1111-1111-111111111111', + 'Market buy 0.5 ETH @ 3500.00'); + +-- After the buy, alice's invested_balance reflects the used funds. +UPDATE project.users + SET available_balance = 10000.0000 - 1750.0000, + invested_balance = 1750.0000, + updated_at = now() + WHERE id = 'b1111111-1111-1111-1111-111111111111'; + +-- ============================================================================ +-- WATCHLISTS +-- ============================================================================ +INSERT INTO project.watchlists (id, user_id, name) VALUES + ('d1111111-1111-1111-1111-111111111111', 'b1111111-1111-1111-1111-111111111111', 'Favorites'), + ('d2222222-2222-2222-2222-222222222222', 'b2222222-2222-2222-2222-222222222222', 'Bobs Picks'); + +INSERT INTO project.watchlist_items (watchlist_id, crypto_id) VALUES + ('d1111111-1111-1111-1111-111111111111', '11111111-1111-1111-1111-111111111111'), + ('d1111111-1111-1111-1111-111111111111', '22222222-2222-2222-2222-222222222222'), + ('d1111111-1111-1111-1111-111111111111', '44444444-4444-4444-4444-444444444444'), + ('d2222222-2222-2222-2222-222222222222', '11111111-1111-1111-1111-111111111111'), + ('d2222222-2222-2222-2222-222222222222', '55555555-5555-5555-5555-555555555555'); Index: server/db/db.go =================================================================== --- server/db/db.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/db/db.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,153 @@ +package db + +import ( + "bufio" + "database/sql" + "embed" + "fmt" + "log" + "os" + "path/filepath" + "strings" + + _ "github.com/lib/pq" +) + +var DB *sql.DB + +// The SQL scripts are compiled into the binary so that -init works no matter +// which directory the program is started from. +// +//go:embed schema_creation.sql data_load.sql +var sqlScripts embed.FS + +func Connect() error { + loadEnvFile() + + host := getenv("DBHOST", "localhost") + port := getenv("DBPORT", "5432") + user := getenv("DBUSER", "postgres") + pass := getenv("DBPASSWORD", "") + name := getenv("DBNAME", "postgres") + + dsn := fmt.Sprintf( + "host=%s port=%s user=%s password=%s dbname=%s sslmode=disable options='--search_path=project,public'", + host, port, user, pass, name, + ) + + 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 { + return fmt.Errorf("db ping (host=%s port=%s user=%s dbname=%s): %w", + host, port, user, name, err) + } + return nil +} + +// runScript executes one of the embedded .sql scripts as a single statement. +func runScript(name string) error { + content, err := sqlScripts.ReadFile(name) + if err != nil { + return fmt.Errorf("read embedded %s: %w", name, err) + } + if _, err := DB.Exec(string(content)); err != nil { + return fmt.Errorf("exec %s: %w", name, err) + } + return nil +} + +// RunSQLFile executes a .sql file from disk as a single statement. +func RunSQLFile(path string) error { + content, err := os.ReadFile(path) + if err != nil { + return fmt.Errorf("read %s: %w", path, err) + } + if _, err := DB.Exec(string(content)); err != nil { + return fmt.Errorf("exec %s: %w", path, err) + } + return nil +} + +// InitSchema runs schema_creation.sql then 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 + } + if err := LoadData(); err != nil { + return err + } + log.Println("Database initialised.") + return nil +} + +// LoadData reloads the sample data without touching the schema. +func LoadData() error { + log.Println("Running data_load.sql ...") + return runScript("data_load.sql") +} + +// loadEnvFile looks for a .env file in the working directory and in every +// parent directory, so the program can be started from the repo root, from +// server/, or from anywhere else inside the checkout. Variables already set +// in the real environment always win over the file, which is what lets you +// point the prototype at the faculty database with DBHOST=... ./eduberza +func loadEnvFile() { + dir, err := os.Getwd() + if err != nil { + return + } + for { + path := filepath.Join(dir, ".env") + if applyEnvFile(path) { + return + } + parent := filepath.Dir(dir) + if parent == dir { + return // reached the filesystem root + } + dir = parent + } +} + +// applyEnvFile reports whether the file existed and was read. +func applyEnvFile(path string) bool { + f, err := os.Open(path) + if err != nil { + return false + } + defer f.Close() + + s := bufio.NewScanner(f) + for s.Scan() { + line := strings.TrimSpace(s.Text()) + if line == "" || strings.HasPrefix(line, "#") { + continue + } + key, value, ok := strings.Cut(line, "=") + if !ok { + continue + } + key = strings.TrimSpace(key) + // Do not clobber variables that are already set in the environment. + if _, exists := os.LookupEnv(key); exists { + continue + } + os.Setenv(key, strings.Trim(strings.TrimSpace(value), `"'`)) + } + if err := s.Err(); err != nil { + log.Printf("warning: could not fully read %s: %v", path, err) + } + return true +} + +func getenv(key, def string) string { + if v := os.Getenv(key); v != "" { + return v + } + return def +} Index: server/db/schema_creation.sql =================================================================== --- server/db/schema_creation.sql (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/db/schema_creation.sql (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,193 @@ +-- schema_creation.sql +-- EduBerza - crypto exchange simulation database +-- Course: Databases 2025/2026 Winter, FINKI UKIM +-- +-- This script is idempotent. It drops the `project` schema and all contained +-- objects, then recreates them from scratch. Safe to run on an empty database +-- or on a database where the schema already exists. + +DROP SCHEMA IF EXISTS project CASCADE; +CREATE SCHEMA project; + +CREATE EXTENSION IF NOT EXISTS pgcrypto; + +SET search_path TO project, public; + +-- ============================================================================ +-- USERS +-- Platform users. Each user has virtual (prop) balances used for simulation. +-- ============================================================================ +CREATE TABLE project.users ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + username varchar(50) NOT NULL UNIQUE, + email varchar(255) NOT NULL UNIQUE, + full_name varchar(200), + password_hash varchar(255) NOT NULL, + 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), + created_at timestamptz NOT NULL DEFAULT now(), + updated_at timestamptz +); + +-- ============================================================================ +-- CRYPTO +-- Catalog of crypto assets available on the platform. +-- ============================================================================ +CREATE TABLE project.crypto ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + symbol varchar(20) NOT NULL UNIQUE, + name varchar(255) NOT NULL, + created_at timestamptz NOT NULL DEFAULT now() +); + +-- ============================================================================ +-- MARKETS +-- A market is a (crypto, quote_currency) pair, e.g. BTC/USD. +-- ============================================================================ +CREATE TABLE project.markets ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + crypto_id uuid NOT NULL REFERENCES project.crypto(id), + quote_currency char(3) NOT NULL DEFAULT 'USD', + is_active boolean NOT NULL DEFAULT true, + created_at timestamptz NOT NULL DEFAULT now(), + CONSTRAINT uq_markets UNIQUE (crypto_id, quote_currency) +); + +-- ============================================================================ +-- HOLDINGS +-- Per-user crypto position with running weighted average entry price. +-- ============================================================================ +CREATE TABLE project.holdings ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE, + crypto_id uuid NOT NULL REFERENCES project.crypto(id), + quantity numeric(20,4) NOT NULL CHECK (quantity >= 0), + -- Weighted-average entry price. NOT NULL so that the P/L arithmetic in + -- v_portfolio can never silently produce NULL for an existing position. + avg_price numeric(18,6) NOT NULL DEFAULT 0 CHECK (avg_price >= 0), + created_at timestamptz NOT NULL DEFAULT now(), + updated_at timestamptz, + CONSTRAINT uq_holdings_user_crypto UNIQUE (user_id, crypto_id) +); + +-- ============================================================================ +-- ORDERS +-- Orders placed by users on a market. +-- ============================================================================ +CREATE TABLE project.orders ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE, + market_id uuid NOT NULL REFERENCES project.markets(id), + 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')), + quantity numeric(20,4) NOT NULL CHECK (quantity > 0), + price numeric(18,6), + placed_at timestamptz NOT NULL DEFAULT now(), + executed_at timestamptz +); + +CREATE INDEX idx_orders_user ON project.orders(user_id); +CREATE INDEX idx_orders_market ON project.orders(market_id); +CREATE INDEX idx_orders_status ON project.orders(status); + +-- ============================================================================ +-- TRANSACTIONS +-- Financial ledger: deposits, buys, sells, fees. +-- ============================================================================ +CREATE TABLE project.transactions ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE, + type varchar(50) NOT NULL CHECK (type IN ('deposit', 'buy', 'sell', 'fee')), + amount numeric(18,4) NOT NULL, + currency char(3) NOT NULL DEFAULT 'USD', + related_order uuid REFERENCES project.orders(id), + created_at timestamptz NOT NULL DEFAULT now(), + description text +); + +CREATE INDEX idx_transactions_user ON project.transactions(user_id, created_at DESC); + +-- ============================================================================ +-- MARKET TRADES +-- Raw executed trades on a market. Source of truth for current price. +-- ============================================================================ +CREATE TABLE project.market_trades ( + id bigserial PRIMARY KEY, + market_id uuid NOT NULL REFERENCES project.markets(id), + executed_at timestamptz NOT NULL, + price numeric(18,6) NOT NULL CHECK (price > 0), + quantity numeric(20,6) NOT NULL CHECK (quantity > 0), + side varchar(4) CHECK (side IN ('buy', 'sell')), + source varchar(50) NOT NULL DEFAULT 'simulation' +); + +CREATE INDEX idx_market_trades_market_time ON project.market_trades(market_id, executed_at DESC); + +-- ============================================================================ +-- MARKET CANDLES +-- OHLCV aggregates over standard timeframes. +-- ============================================================================ +CREATE TABLE project.market_candles ( + id bigserial PRIMARY KEY, + market_id uuid NOT NULL REFERENCES project.markets(id), + timeframe varchar(5) NOT NULL CHECK (timeframe IN ('1m', '5m', '1h', '1d')), + open numeric(18,6) NOT NULL, + high numeric(18,6) NOT NULL, + low numeric(18,6) NOT NULL, + close numeric(18,6) NOT NULL, + volume numeric(20,6) NOT NULL, + candle_time timestamptz NOT NULL, + CONSTRAINT uq_candle UNIQUE (market_id, timeframe, candle_time) +); + +CREATE INDEX idx_market_candles_market_tf_time ON project.market_candles(market_id, timeframe, candle_time DESC); + +-- ============================================================================ +-- WATCHLISTS +-- ============================================================================ +CREATE TABLE project.watchlists ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE, + name varchar(100) NOT NULL, + created_at timestamptz NOT NULL DEFAULT now() +); + +CREATE TABLE project.watchlist_items ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + watchlist_id uuid NOT NULL REFERENCES project.watchlists(id) ON DELETE CASCADE, + crypto_id uuid NOT NULL REFERENCES project.crypto(id), + added_at timestamptz NOT NULL DEFAULT now(), + CONSTRAINT uq_watchlist_crypto UNIQUE (watchlist_id, crypto_id) +); + +-- ============================================================================ +-- VIEWS +-- ============================================================================ + +-- Latest trade price per market (current price). +CREATE OR REPLACE VIEW project.v_latest_prices AS +SELECT DISTINCT ON (t.market_id) + t.market_id, + c.symbol, + m.quote_currency, + t.price, + t.executed_at +FROM project.market_trades t +JOIN project.markets m ON m.id = t.market_id +JOIN project.crypto c ON c.id = m.crypto_id +ORDER BY t.market_id, t.executed_at DESC; + +-- Portfolio valuation per user (holdings x latest price). +CREATE OR REPLACE VIEW project.v_portfolio AS +SELECT h.user_id, + c.symbol, + h.quantity, + h.avg_price, + lp.price AS current_price, + (h.quantity * lp.price) AS market_value, + (h.quantity * (lp.price - h.avg_price)) AS unrealized_pnl +FROM project.holdings h +JOIN project.crypto c ON c.id = h.crypto_id +LEFT JOIN project.markets m ON m.crypto_id = c.id AND m.quote_currency = 'USD' +LEFT JOIN project.v_latest_prices lp ON lp.market_id = m.id; Index: server/main.go =================================================================== --- server/main.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/main.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,37 @@ +package main + +import ( + "flag" + "fmt" + "log" + + "bp_project/server/db" +) + +func main() { + initFlag := flag.Bool("init", false, "drop and recreate the project schema, then load sample data") + loadData := flag.Bool("load-data", false, "reload sample data (without dropping schema)") + flag.Parse() + + if err := db.Connect(); err != nil { + log.Fatalf("database connect failed: %v", err) + } + defer db.DB.Close() + + if *initFlag { + if err := db.InitSchema(); err != nil { + log.Fatalf("init failed: %v", err) + } + fmt.Println("Schema initialised. Re-run without -init to start the CLI.") + return + } + if *loadData { + if err := db.LoadData(); err != nil { + log.Fatalf("data load failed: %v", err) + } + fmt.Println("Sample data reloaded.") + return + } + + RunCLI() +} Index: server/market.go =================================================================== --- server/market.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/market.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,85 @@ +package main + +import ( + "database/sql" + "fmt" + + "bp_project/server/db" +) + +// Market represents a trading pair. +type Market struct { + ID string + CryptoID string + Symbol string + Quote string +} + +// ListMarkets prints all active markets with their latest price. +func ListMarkets() { + rows, err := db.DB.Query(` + SELECT m.id, c.symbol, m.quote_currency, + COALESCE(lp.price, 0) AS price + FROM markets m + JOIN crypto c ON c.id = m.crypto_id + LEFT JOIN v_latest_prices lp ON lp.market_id = m.id + WHERE m.is_active = true + ORDER BY c.symbol`) + if err != nil { + fmt.Println("Error:", err) + return + } + defer rows.Close() + + fmt.Println() + fmt.Printf(" %-4s %-8s %-5s %15s\n", "#", "Symbol", "Quote", "Last price") + fmt.Println(" -----------------------------------------") + i := 1 + for rows.Next() { + var id, sym, quote string + var price float64 + if err := rows.Scan(&id, &sym, "e, &price); err != nil { + fmt.Println("scan error:", err) + return + } + fmt.Printf(" %-4d %-8s %-5s %15.6f\n", i, sym, quote, price) + i++ + } +} + +// ChooseMarket asks the user to pick a market by symbol and returns it. +func ChooseMarket() (*Market, error) { + ListMarkets() + sym := prompt("Market symbol (e.g. BTC): ") + if sym == "" { + return nil, fmt.Errorf("no symbol entered") + } + var m Market + err := db.DB.QueryRow(` + SELECT m.id, c.id, c.symbol, m.quote_currency + FROM markets m + JOIN crypto c ON c.id = m.crypto_id + WHERE upper(c.symbol) = upper($1) + AND m.is_active = true + LIMIT 1`, sym, + ).Scan(&m.ID, &m.CryptoID, &m.Symbol, &m.Quote) + if err == sql.ErrNoRows { + return nil, fmt.Errorf("market %s not found", sym) + } + if err != nil { + return nil, err + } + return &m, nil +} + +// LatestPrice returns the last traded price on a market. +func LatestPrice(marketID string) (float64, error) { + var price float64 + err := db.DB.QueryRow( + `SELECT price FROM v_latest_prices WHERE market_id = $1`, marketID, + ).Scan(&price) + if err == sql.ErrNoRows { + return 0, fmt.Errorf("no trades yet for this market") + } + return price, err +} Index: server/portfolio.go =================================================================== --- server/portfolio.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/portfolio.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,67 @@ +package main + +import ( + "fmt" + + "bp_project/server/db" +) + +// ShowPortfolio - UC0006 +// Uses the v_portfolio view to list holdings with current market value and P&L. +func ShowPortfolio(s *Session) { + rows, err := db.DB.Query( + `SELECT symbol, + quantity, + COALESCE(avg_price, 0), + COALESCE(current_price, 0), + COALESCE(market_value, 0), + COALESCE(unrealized_pnl, 0) + FROM v_portfolio + WHERE user_id = $1 AND quantity > 0 + ORDER BY symbol`, + s.UserID, + ) + if err != nil { + fmt.Println("Error:", err) + return + } + defer rows.Close() + + fmt.Println() + fmt.Printf(" %-8s %12s %14s %14s %14s %14s\n", + "Symbol", "Quantity", "Avg buy", "Current", "Value", "Unrealised P/L") + fmt.Println(" ------------------------------------------------------------------------------------") + + var totalValue, totalPnL float64 + empty := true + for rows.Next() { + var sym string + var qty, avg, cur, val, pnl float64 + if err := rows.Scan(&sym, &qty, &avg, &cur, &val, &pnl); err != nil { + fmt.Println("scan error:", err) + return + } + fmt.Printf(" %-8s %12.4f %14.6f %14.6f %14.4f %+14.4f\n", + sym, qty, avg, cur, val, pnl) + totalValue += val + totalPnL += pnl + empty = false + } + if empty { + fmt.Println(" (no holdings yet)") + return + } + fmt.Println(" ------------------------------------------------------------------------------------") + fmt.Printf(" %-8s %12s %14s %14s %14.4f %+14.4f\n", + "TOTAL", "", "", "", totalValue, totalPnL) + + // cash summary + var avail, invested float64 + _ = db.DB.QueryRow( + `SELECT available_balance, invested_balance FROM users WHERE id = $1`, + s.UserID, + ).Scan(&avail, &invested) + fmt.Printf("\n Cash available : %.4f USD\n", avail) + fmt.Printf(" Portfolio value: %.4f USD\n", totalValue) + fmt.Printf(" Net worth : %.4f USD\n", avail+totalValue) +} Index: server/trade.go =================================================================== --- server/trade.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/trade.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,199 @@ +package main + +import ( + "database/sql" + "fmt" + "strconv" + + "bp_project/server/db" +) + +// PlaceOrder - UC0004 (buy) / UC0005 (sell) +// Market order that executes immediately against the latest price. +// Runs inside a single database transaction so the orders, holdings, +// users.balance and transactions tables always agree. +func PlaceOrder(s *Session, side string) { + if side != "buy" && side != "sell" { + fmt.Println("Invalid side.") + return + } + fmt.Printf("\n-- Place market %s order --\n", side) + + m, err := ChooseMarket() + if err != nil { + fmt.Println(err) + return + } + price, err := LatestPrice(m.ID) + if err != nil { + fmt.Println(err) + return + } + fmt.Printf("Latest price for %s/%s = %.6f\n", m.Symbol, m.Quote, price) + + qtyStr := prompt("Quantity: ") + qty, err := strconv.ParseFloat(qtyStr, 64) + if err != nil || qty <= 0 { + fmt.Println("Invalid quantity.") + return + } + notional := qty * price + + tx, err := db.DB.Begin() + if err != nil { + fmt.Println("Error:", err) + return + } + defer tx.Rollback() + + // 1. create the order (status='executed' since we fill immediately) + var orderID string + err = tx.QueryRow( + `INSERT INTO orders (user_id, market_id, side, type, status, quantity, price, executed_at) + VALUES ($1, $2, $3, 'market', 'executed', $4, $5, now()) + RETURNING id`, + s.UserID, m.ID, side, qty, price, + ).Scan(&orderID) + if err != nil { + fmt.Println("Error creating order:", err) + return + } + + if side == "buy" { + // check balance + var avail float64 + if err := tx.QueryRow( + `SELECT available_balance FROM users WHERE id = $1 FOR UPDATE`, + s.UserID).Scan(&avail); err != nil { + fmt.Println("Error:", err) + return + } + if avail < notional { + fmt.Printf("Insufficient funds: need %.4f, have %.4f\n", notional, avail) + return + } + + // debit balance + if _, err := tx.Exec( + `UPDATE users + SET available_balance = available_balance - $1, + invested_balance = invested_balance + $1, + updated_at = now() + WHERE id = $2`, + notional, s.UserID, + ); err != nil { + fmt.Println("Error:", err) + return + } + + // upsert holding with running weighted average + if err := upsertHoldingOnBuy(tx, s.UserID, m.CryptoID, qty, price); err != nil { + fmt.Println("Error updating holding:", err) + return + } + + // ledger entry + if _, err := tx.Exec( + `INSERT INTO transactions (user_id, type, amount, currency, related_order, description) + VALUES ($1, 'buy', $2, 'USD', $3, $4)`, + s.UserID, -notional, orderID, + fmt.Sprintf("Market buy %.4f %s @ %.6f", qty, m.Symbol, price), + ); err != nil { + fmt.Println("Error:", err) + return + } + } else { + // sell: check holding + var held, avgPrice float64 + err := tx.QueryRow( + `SELECT quantity, avg_price FROM holdings + WHERE user_id = $1 AND crypto_id = $2 FOR UPDATE`, + s.UserID, m.CryptoID, + ).Scan(&held, &avgPrice) + if err != nil && err != sql.ErrNoRows { + fmt.Println("Error:", err) + return + } + if err == sql.ErrNoRows || held < qty { + fmt.Printf("Insufficient holding: trying to sell %.4f, hold %.4f\n", qty, held) + return + } + + // reduce holding + if _, err := tx.Exec( + `UPDATE holdings + SET quantity = quantity - $1, + updated_at = now() + WHERE user_id = $2 AND crypto_id = $3`, + qty, s.UserID, m.CryptoID, + ); err != nil { + fmt.Println("Error:", err) + return + } + + // credit balance; reduce invested by cost basis (avg_price * qty) + costBasis := avgPrice * qty + if _, err := tx.Exec( + `UPDATE users + SET available_balance = available_balance + $1, + invested_balance = GREATEST(invested_balance - $2, 0), + updated_at = now() + WHERE id = $3`, + notional, costBasis, s.UserID, + ); err != nil { + fmt.Println("Error:", err) + return + } + + // ledger entry + if _, err := tx.Exec( + `INSERT INTO transactions (user_id, type, amount, currency, related_order, description) + VALUES ($1, 'sell', $2, 'USD', $3, $4)`, + s.UserID, notional, orderID, + fmt.Sprintf("Market sell %.4f %s @ %.6f", qty, m.Symbol, price), + ); err != nil { + fmt.Println("Error:", err) + return + } + } + + // record the resulting market trade so the book reflects this fill + if _, err := tx.Exec( + `INSERT INTO market_trades (market_id, executed_at, price, quantity, side, source) + VALUES ($1, now(), $2, $3, $4, 'user')`, + m.ID, price, qty, side, + ); err != nil { + fmt.Println("Error:", err) + return + } + + if err := tx.Commit(); err != nil { + fmt.Println("Commit error:", err) + return + } + fmt.Printf("Order executed: %s %.4f %s @ %.6f (notional %.4f USD)\n", + side, qty, m.Symbol, price, notional) +} + +// upsertHoldingOnBuy creates or updates a holding using running weighted-average price. +// +// This is a single statement that relies on UNIQUE (user_id, crypto_id): the new +// weighted average is recomputed by the database in numeric arithmetic rather +// than in Go float64, and no separate SELECT ... FOR UPDATE round-trip is +// needed because ON CONFLICT DO UPDATE locks the conflicting row itself. +// Every SET expression sees the pre-update row, so `holdings.quantity` below is +// still the old quantity while the average is being computed. +func upsertHoldingOnBuy(tx *sql.Tx, userID, cryptoID string, qty, price float64) error { + _, err := tx.Exec( + `INSERT INTO holdings (user_id, crypto_id, quantity, avg_price, updated_at) + VALUES ($1, $2, $3, $4, now()) + ON CONFLICT (user_id, crypto_id) DO UPDATE + SET avg_price = (holdings.quantity * holdings.avg_price + + EXCLUDED.quantity * EXCLUDED.avg_price) + / (holdings.quantity + EXCLUDED.quantity), + quantity = holdings.quantity + EXCLUDED.quantity, + updated_at = now()`, + userID, cryptoID, qty, price, + ) + return err +} Index: server/watchlist.go =================================================================== --- server/watchlist.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/watchlist.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,134 @@ +package main + +import ( + "database/sql" + "fmt" + + "bp_project/server/db" +) + +// ManageWatchlist - UC0007 +// Ensures the user has a default watchlist, then allows listing, adding, +// removing entries. +func ManageWatchlist(s *Session) { + wlID, err := ensureDefaultWatchlist(s.UserID) + if err != nil { + fmt.Println("Error:", err) + return + } + for { + fmt.Println("\n-- Watchlist --") + fmt.Println("[1] List items") + fmt.Println("[2] Add crypto") + fmt.Println("[3] Remove crypto") + fmt.Println("[0] Back") + switch prompt("> ") { + case "1": + listWatchlist(wlID) + case "2": + addToWatchlist(wlID) + case "3": + removeFromWatchlist(wlID) + case "0": + return + default: + fmt.Println("Unknown option.") + } + } +} + +func ensureDefaultWatchlist(userID string) (string, error) { + var id string + err := db.DB.QueryRow( + `SELECT id FROM watchlists WHERE user_id = $1 ORDER BY created_at LIMIT 1`, + userID, + ).Scan(&id) + if err == sql.ErrNoRows { + err = db.DB.QueryRow( + `INSERT INTO watchlists (user_id, name) VALUES ($1, 'Favorites') RETURNING id`, + userID, + ).Scan(&id) + return id, err + } + return id, err +} + +func listWatchlist(wlID string) { + rows, err := db.DB.Query(` + SELECT c.symbol, c.name, COALESCE(lp.price, 0) + FROM watchlist_items wi + JOIN crypto c ON c.id = wi.crypto_id + LEFT JOIN markets m ON m.crypto_id = c.id AND m.quote_currency = 'USD' + LEFT JOIN v_latest_prices lp ON lp.market_id = m.id + WHERE wi.watchlist_id = $1 + ORDER BY c.symbol`, wlID) + if err != nil { + fmt.Println("Error:", err) + return + } + defer rows.Close() + + fmt.Println() + fmt.Printf(" %-8s %-20s %15s\n", "Symbol", "Name", "Last price") + fmt.Println(" --------------------------------------------------") + empty := true + for rows.Next() { + var sym, name string + var price float64 + if err := rows.Scan(&sym, &name, &price); err != nil { + fmt.Println("scan error:", err) + return + } + fmt.Printf(" %-8s %-20s %15.6f\n", sym, name, price) + empty = false + } + if empty { + fmt.Println(" (watchlist is empty)") + } +} + +func addToWatchlist(wlID string) { + sym := prompt("Crypto symbol to add: ") + var cryptoID string + err := db.DB.QueryRow( + `SELECT id FROM crypto WHERE upper(symbol) = upper($1)`, sym, + ).Scan(&cryptoID) + if err == sql.ErrNoRows { + fmt.Println("Unknown crypto symbol.") + return + } + if err != nil { + fmt.Println("Error:", err) + return + } + _, err = db.DB.Exec( + `INSERT INTO watchlist_items (watchlist_id, crypto_id) + VALUES ($1, $2) + ON CONFLICT (watchlist_id, crypto_id) DO NOTHING`, + wlID, cryptoID, + ) + if err != nil { + fmt.Println("Error:", err) + return + } + fmt.Println("Added.") +} + +func removeFromWatchlist(wlID string) { + sym := prompt("Crypto symbol to remove: ") + res, err := db.DB.Exec(` + DELETE FROM watchlist_items + WHERE watchlist_id = $1 + AND crypto_id = (SELECT id FROM crypto WHERE upper(symbol) = upper($2))`, + wlID, sym) + if err != nil { + fmt.Println("Error:", err) + return + } + n, _ := res.RowsAffected() + if n == 0 { + fmt.Println("Not in watchlist.") + return + } + fmt.Println("Removed.") +}