JK
>LOADING // LOADING
INITIALIZING VIEW
Completedgopostgresgingolang-migratevalidatorlogrusdocker
Stock Reward System
Monolithic REST API for managing stock-based employee rewards with double-entry ledger tracking, idempotent reward creation, financial fee calculation, corporate action handling via SQL views, and a background price updater with worker pool concurrency.
Problem & Solution
Tracks stock rewards given to users, maintains a double-entry-style ledger for every financial event, and computes current and historical portfolio values in INR that account for corporate actions (splits, bonus issues, mergers, delisting). Intended for internal/corporate use where a company grants stock units to employees.
Features
- Idempotent reward creation with per-user-per-day-per-stock uniqueness
- Financial fee calculation (brokerage 0.5%, STT 0.1%, GST 18% on brokerage+STT)
- Adjustment system (reversals, fee refunds, manual corrections) with row-level locking
- Double-entry ledger for stock units, INR outflows, and fee entries
- PostgreSQL views for portfolio aggregation using logarithmic multiplier accumulation
- Background price updater with worker pool, retry, and in-memory cache
- Corporate action handling: splits, bonus issues, mergers, delisting
- Docker Compose deployment with multi-stage build
Tech Stack
| Technology | Version | Purpose | Why Chosen |
|---|---|---|---|
| Go | 1.25.1 | Programming language | Strong stdlib, concurrency primitives, fast compilation |
| Gin | 1.11.0 | HTTP framework, routing, middleware | High performance, middleware support, active community |
| PostgreSQL | 17 | Relational database | Advanced SQL features (views, LATERAL, generated columns, ENUMs) |
| golang-migrate | v4.19.0 | Database migrations | Simple file-based migration runner |
| logrus | 1.9.3 | Structured logging | Structured fields, leveled logging |
| go-sqlmock | v1.5.2 | Test database mocking | Simulates SQL for unit tests without real DB |
| lib/pq | v1.10.9 | PostgreSQL driver | Battle-tested, native PostgreSQL support |
| Docker | — | Containerization | Reproducible builds, isolated environments |
Architecture
Core Components
- Gin HTTP server with RequestID middleware for request tracing
- Reward handler with SAVEPOINT-based idempotency pattern
- Adjustment handler with SELECT FOR UPDATE row-level locking
- Read handlers (today, historical, portfolio, stats) querying PostgreSQL views
- Background PriceService with 5-goroutine worker pool and 3-slot DB semaphore
System Resilience
- In-memory PriceCache with RWMutex for read-heavy workloads
- CAS-based concurrency control prevents cascading update slowdowns
- Panic recovery in price updater workers prevents process crashes
- Graceful HTTP shutdown with 10s drain timeout
- Transaction wrapping with deferred rollback for all write operations
Project Structure
cmd
stocky-api
main.go
internal
config
database
migrations
handlers
stocky
jobs
price_updater.go
storage
models
utils
API Endpoints
Stocky API (port 8080)
| Method | Route | Purpose |
|---|---|---|
| GET | /health | Health check — returns {status: OK} |
| POST | /api/v1/reward | Create reward — idempotent, transactional, fee calculation |
| POST | /api/v1/adjustments/:id | Apply adjustment — row locking, type-based ledger entries |
| GET | /api/v1/today-stocks/:userId | Today's rewards from today_rewards view |
| GET | /api/v1/historical-inr/:userId | Historical INR valuation — paginated, event-multiplied |
| GET | /api/v1/stats/:userId | Stats summary — today rewards + total portfolio value |
| GET | /api/v1/portfolio/:userId | Portfolio details — paginated, delist-filtered |
Database Design
users
| Column | Type | Notes |
|---|---|---|
| idPK | SERIAL | Auto-generated |
| username | VARCHAR(50) | NOT NULL, UNIQUE |
| VARCHAR(100) | NOT NULL, UNIQUE | |
| created_at | TIMESTAMP | Default: NOW() |
User accounts. Seeded with one test user.
rewards
| Column | Type | Notes |
|---|---|---|
| idPK | SERIAL | Auto-generated |
| user_id | INT | FK → users(id) ON DELETE CASCADE |
| stock_symbol | VARCHAR(20) | NOT NULL |
| quantity | NUMERIC(18,6) | NOT NULL, negative = reversal |
| idempotency_key | VARCHAR(64) | UNIQUE, allows NULLs |
| reward_date | DATE | GENERATED ALWAYS AS (created_at::DATE) STORED |
Core reward events. Unique on (user_id, stock_symbol, reward_date) with case-insensitive variant.
ledger
| Column | Type | Notes |
|---|---|---|
| idPK | SERIAL | Auto-generated |
| reward_id | INT | FK → rewards(id) ON DELETE CASCADE |
| entry_type | ledger_entry_type | ENUM: stock_units, inr_outflow, fees, brokerage_fee, stt_fee, gst_fee |
| stock_symbol | VARCHAR(20) | Nullable |
| quantity | NUMERIC(18,6) | Nullable |
| amount | NUMERIC(18,4) | Nullable |
Double-entry-style ledger. Each reward generates 2-5 entries.
stock_prices
| Column | Type | Notes |
|---|---|---|
| idPK | SERIAL | Auto-generated |
| stock_symbol | VARCHAR(20) | UNIQUE |
| price | NUMERIC(18,4) | Current price |
| updated_at | TIMESTAMP | Updated by PriceService |
Latest known price per stock. Updated by background PriceService.
stock_price_history
| Column | Type | Notes |
|---|---|---|
| idPK | SERIAL | Auto-generated |
| stock_symbol | VARCHAR(20) | NOT NULL |
| price | NUMERIC(18,4) | NOT NULL |
| date | DATE | NOT NULL, UNIQUE with stock_symbol |
Daily price snapshots for historical valuation. Upserted by PriceService.
stock_events
| Column | Type | Notes |
|---|---|---|
| idPK | SERIAL | Auto-generated |
| stock_symbol | VARCHAR(20) | NOT NULL |
| event_type | stock_event_type | ENUM: split, bonus, merge, delist |
| ratio_num | NUMERIC(18,6) | Multiplier numerator |
| ratio_den | NUMERIC(18,6) | Multiplier denominator |
| new_symbol | VARCHAR(20) | For mergers |
| effective_date | TIMESTAMP | NOT NULL |
Corporate actions. ratio_num/ratio_den defines multiplier (e.g., 2:1 split).
adjustments
| Column | Type | Notes |
|---|---|---|
| idPK | SERIAL | Auto-generated |
| reward_id | INT | FK → rewards(id) ON DELETE CASCADE |
| adjustment_type | VARCHAR(64) | reward_reversal, fee_refund, manual_correction |
| delta_quantity | NUMERIC(18,6) | NOT NULL |
| delta_amount | NUMERIC(18,4) | NOT NULL |
| reason | VARCHAR(255) | Nullable |
Modifications to existing rewards with row-level locking.
One-to-Many: users→rewards (CASCADE), rewards→ledger (CASCADE), rewards→adjustments (CASCADE). Implicit cross-referencing: rewards.stock_symbol ↔ stock_prices.stock_symbol (via UPPER()), rewards.stock_symbol ↔ stock_events.stock_symbol (event multipliers). No formal FK between rewards and stock_prices/stock_events — enforced at view level.
Key Design Decisions
SQL Views for Aggregation
Decision: Complex portfolio/historical calculations implemented as PostgreSQL views rather than in application code
Why: Reduces application complexity; leverages PostgreSQL query optimizer; single source of truth for calculations
Benefits: Consistent computation across all endpoints; database handles JOINs and aggregations efficiently
Trade-offs: Views can be slow for large datasets; harder to debug than application code; tied to PostgreSQL
Logarithmic Product for Multipliers
Decision: Use EXP(SUM(LN(ratio))) to compute cumulative corporate event multipliers
Why: Avoids floating-point overflow/underflow when many events compound
Benefits: Numerically stable; handles arbitrary number of events
Trade-offs: Less readable than direct multiplication; requires positive ratios
Handler-Only Architecture (No Service Layer)
Decision: Business logic lives directly in handlers without a separate service layer
Why: Simpler for small project; fewer files and abstractions to maintain
Benefits: Low boilerplate; easy to trace request flow
Trade-offs: Harder to reuse logic; tighter coupling between HTTP and data access; harder to test business logic independently
Simulated Price Fetcher
Decision: Use RandomPriceFetcher instead of real market data API
Why: Development/testing environment; no API key management needed
Benefits: Zero external dependencies; deterministic-ish behavior
Trade-offs: Not production-ready; portfolio values are meaningless; must replace for real deployment
SAVEPOINT-based Idempotency
Decision: Use PostgreSQL SAVEPOINTs to handle idempotency without aborting the outer transaction
Why: Allows retry safety without external idempotency service
Benefits: Sophisticated in-transaction idempotency that works within PostgreSQL constraints
Trade-offs: Complex error handling; SAVEPOINT overhead
No Authentication/Authorization
Decision: All endpoints are publicly accessible with no auth
Why: Development/demo project; auth expected to be added later (e.g., via API gateway)
Benefits: Simpler implementation; easier testing
Trade-offs: Not suitable for production; any client can create rewards or apply adjustments
Testing
Go standard testing + go-sqlmock v1.5.2•19 unit tests
What's Tested
- Reward handler: NULL idempotency, invalid JSON, zero/negative quantity, normalization, idempotent retry (8 tests)
- Adjustment handler: invalid reward ID, invalid JSON, invalid type, reversal ledger behavior (4 tests)
- Historical handler: invalid userId, invalid limit (2 tests)
- Portfolio handler: invalid userId, invalid offset, case-insensitive delist filter (3 tests)
- Today's handler: invalid userId (1 test)
- Stats handler: invalid userId (1 test)
Missing Coverage
- Integration tests against real PostgreSQL
- Tests for read handlers with actual data verification
- Background price updater tests (error paths, panics, retries)
- Concurrent adjustment handling tests
- Transaction rollback scenario tests
- Pagination edge case tests
Key Challenges
- Computing cumulative corporate event multipliers (splits, bonuses) using logarithmic aggregation in SQL views
- Idempotent reward creation without external state — SAVEPOINT-based pattern with pq.Error inspection
- Case-insensitive stock symbol matching across application and database layers
- Race condition prevention in adjustments using FOR UPDATE row locking with dual application+database checks
- Missing historical prices causing rewards to be invisible in historical views
Key Learnings
- Transactional consistency using Go & PostgreSQL with proper rollback patterns
- Advanced SQL techniques: LATERAL subqueries, EXP(SUM(LN())) for numerical stability
- Worker pool concurrency with buffered channel semaphores for controlled DB writes
- SAVEPOINT-based idempotency for retry-safe operations within transactions
- Trade-offs of handler-only architecture vs service layer separation