= 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
– 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.

`bots/main.go`:

{{{
// 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
}
}}}

Build, configure, run and test instructions:
!BuildInstructions.

All pages listed below, together with the screenshots of each run, are kept in the project's
!GitHub repository, !StefanTrsunov/bp, under
`docs/P4-Prototype/`.

== Implemented use-cases ==

||=Page=||=Use-case=||=Source=||
||UseCase0001Implementation||Register a new account||`server/auth.go`||
||UseCase0002Implementation||Log in||`server/auth.go`||
||UseCase0003Implementation||Deposit virtual funds||`server/account.go`||
||UseCase0004Implementation||Place market BUY order||`server/trade.go`||
||UseCase0005Implementation||Place market SELL order||`server/trade.go`||
||UseCase0006Implementation||View portfolio and history||`server/portfolio.go`||
||UseCase0007Implementation||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. The screenshots are committed
alongside the pages, in
`docs/P4-Prototype/screenshots/`.

== 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.
 * '''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. `CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` is the same idea
   applied to the sell path: an inconsistent reservation is impossible at the database level, not
   just something `trade.go` is careful about.
 * '''Selling reserves before it removes.''' A sell order locks the holding row, reserves the
   quantity being sold, then settles by removing it — see
   UseCase0005Implementation. Two sell orders placed at the same
   instant for more than the available quantity are serialised correctly by `SELECT ... FOR
   UPDATE`, not just by luck of everything happening in one CLI process; this is demonstrated
   there with two concurrent processes.
 * '''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.
 * Reservation only ever lives inside one transaction, because only market orders (which settle
   immediately) exist. A real limit-order matcher would leave `holdings.reserved_quantity` set
   and `orders.status = 'open'` between two separate commits, and would need a way to cancel an
   order to release the reservation — neither is implemented, since nothing in the prototype
   produces an order that stays open.

== AI usage ==

AI was used in this phase and is logged in full, per the course rule for P1 onward.

 * '''Phase log:'''
   PrototypeImplementationAIUsage
   – service used, the bugs found and fixed, the test evidence, and what I decided myself.
 * '''Full conversation transcript:'''
   ERModelAIUsage
   – the same conversation produced the P1–P4 artefacts, so the complete prompt/response log is
   kept in one place. Its sections:
   Session 1 – 2026-04-21,
   Session 2 – 2026-08-06/07,
   Session 3 – 2026-09-16.

'''Service:''' Claude Code (Anthropic), `https://claude.com/claude-code` – Claude subscription,
model Claude Opus 4.7 (1M context) in sessions 1–2, Claude Sonnet 5 in session 3.

'''In short:''' session 1 rewrote the existing Chi/HTTP backend as the CLI prototype covering
UC0001–UC0007 and added the market bot. Session 2 was a review pass I asked for, which found and
fixed three bugs – a path-resolution bug that made the 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" – and replaced the read-modify-write holding update with a
single `INSERT … ON CONFLICT DO UPDATE`. Session 3 added `holdings.reserved_quantity` and changed
`trade.go`'s sell path to reserve crypto before removing it, closing a gap where two sell orders
could be granted the same units; see the "Session 3 — 2026-09-16" section of
PrototypeImplementationAIUsage.