This project is the complete solution for the Online Betting Data Capture and Analytics System assessment. Implemented is a .NET 8 distributed architecture designed to ingest 250+ wager events per second via an asynchronous RabbitMQ pipeline, utilizing batched saves to SQL Server and real-time analytics (via .NET Aspire) and caching handled via Redis

For a detailed explanation of the architectural trade-offs, performance optimizations (such as denormalized DB schema, TVP batching, and dual-layer caching), and design decisions made to me the requirement - refer to the Architectural Notes (NOTES.md)

To demonstrate a professional development workflow, this project was managed using a structured kanban GitHub project, incorporating task-tracking:

• Task Tracking: Requirements and epics/tasks were planned and tracked using a GitHub Project Board
• Code Quality & Git Flow: Feature development and merges were performed via Closed Pull Requests

• Implementation Deviations
• Features and Capabilities
• System Requirements
  • Core Requirements
  • Optional (Recommended)
• .NET Aspire Infrastructure
• Architecture Workflow
• Database Schema
• Benchmark and Load Test Results
  • Test Machine Specs:
  • NBomber/BogusTester Configuration:
  • Data Audit and Persistence
    • Data Audit Results:
• API Performance (During Load):
• Getting Started (How to Run)
  • Profile 1: Docker Compose Hybrid (Recommended)
  • Profile 2: .NET Aspire (Alternative)
  • Running the Load Test
• Project Nuget Packages

This solution utilizes .NET Aspire and Docker to provide a simple and easy setup process. As a result, the prerequisites from the initial specification in Online Betting Data Capture and Analytics System were modified:

• SQL Authentication vs Windows Auth: The original spec requested Integrated Security=SSPI - because this solution runs SQL Server in a Linux-based Docker container, Windows Authentication is not supported. The system uses SQL Authentication (sa user with password Guest123!)
• Orchestrated RabbitMQ: Rather than requiring a manual RabbitMQ installation, the message broker is provisioned automatically as a containerized resource via the Aspire AppHost or via the docker-compose.yaml included

To build and run this solution locally, ensure your environment meets the following core specifications:

• .NET 8.0 SDK (or later): Required to compile, build, and run
• Docker Desktop or a compatible container runtime: Required to host the SQL Server, RabbitMQ, and Redis
  Note: Ensure the WSL 2 backend is enabled (if running on Windows)
• Git: Got cloning the repo

The project can be managed/run completely standalone using the dotnet CLI (via the terminal), you can use one of the following IDEs which provide a better experience for debugging and review:

• Visual Studio 2022/2026
• JetBrains Rider
• VS Code

• Asynchronous Ingestion: Wagers are proxied directly to RabbitMQ, enabling the API to return a success response quickly without waiting for database locks
• Intelligent Batching: Background consumer uses MassTransit to buffer messages into batches of 200, executing bulk inserts into SQL Server via Table-Valued Parameters (TVPs)
• High-Performance Reads (Dual-Cache): A Cache-Aside pattern utilizing Redis bypasses database reads (assisting during peak load):
  • Leaderboard: Stored in Redis Sorted Sets
  • Wager History: Paginated player queries are serialized and cache
• Resilient Infrastructure: Includes automated DbUp schema migrations, Polly exponential backoff retries for container startup orchestration, and persistent volume mounts
• 3-Way Data Integrity Audit: Tooling to check data integrity by comparing between the Load Tester, the API ingestion layer, and the DB

.NET Aspire Dashboard (Healthy Resources)	.NET Aspire Console Logs (Structured Logs disabled for performance)

graph TD
    %% Define Styles
    classDef client fill:#f9f9f9,stroke:#333,stroke-width:2px;
    classDef api fill:#512BD4,stroke:#fff,stroke-width:2px,color:#fff;
    classDef queue fill:#FF6600,stroke:#fff,stroke-width:2px,color:#fff;
    classDef worker fill:#239120,stroke:#fff,stroke-width:2px,color:#fff;
    classDef db fill:#CC2927,stroke:#fff,stroke-width:2px,color:#fff;
    classDef cache fill:#DC382D,stroke:#fff,stroke-width:2px,color:#fff;

    Tester[Load Tester / Client]:::client

    subgraph App Layer
        API[REST API Proxy & Services]:::api
    end

    subgraph Messaging Layer
        Rabbit[(RabbitMQ)]:::queue
    end

    subgraph Consumer Layer
        Worker[Background Batch Consumer]:::worker
    end

    subgraph Data Layer
        Redis[(Redis Cache)]:::cache
        SQL[(SQL Server)]:::db
    end

    %% Ingestion Flow
    Tester -->|1. POST Wager| API
    API -->|2. Publish| Rabbit
    Rabbit -->|3. Consume| Worker
    Worker -->|4. Batch Insert TVP| SQL

    %% Read Flow
    Tester -.->|5. GET Analytics & History| API
    API -.->|6. Fetch Leaderboard & Paged Wagers| Redis
    Redis -.->|7. Cache Miss| SQL
    SQL -.->|8. Hydrate Cache & Return| API

erDiagram
    Wagers {
        UNIQUEIDENTIFIER WagerId PK "NONCLUSTERED"
        UNIQUEIDENTIFIER AccountId "Indexed"
        DECIMAL Amount
        DATETIME2 CreatedDateTime "CLUSTERED INDEX"
        NVARCHAR Theme
        NVARCHAR Provider
        NVARCHAR GameName
        UNIQUEIDENTIFIER TransactionId
    }
    
    PlayerSpendStats {
        UNIQUEIDENTIFIER AccountId PK
        NVARCHAR Username
        DECIMAL TotalSpend
        DATETIME2 LastUpdated
    }
    
    WagerTableType {
        TABLE TVP "Used for Bulk Inserts"
    }

Architecture Note: This schema is intentionally denormalized and lacks Foreign Key (FK) constraints to maximize write throughput. By removing referential integrity checks at the database level, the system avoids the overhead and locking associated with high-volume inserts. Integrity is instead guaranteed by the sp_ProcessWagerBatch SP

Dell Inspiron 16 6520

• Processor: Intel(R) Core(TM) i9-14900KF @ 3.20 GHz (24 Cores, 32 Threads)
• Memory: 64.0 GB RAM
• System Type: 64-bit OS, x64-based processor
• OS: Windows 11 Pro (Version 23H2)

────────────────────────────────────────────────────── test info ───────────────────────────────────────────────────────

test suite: nbomber_default_test_suite_name
test name: nbomber_default_test_name
session id: 2026-04-07_10.26.69_session_844888a3

──────────────────────────────────────────────────── scenario stats ────────────────────────────────────────────────────

scenario: hello_world_scenario
  - ok count: 7000
  - fail count: 0
  - all data: 0 MB
  - duration: 00:00:28

load simulations:
  - iterations_for_inject, rate: 500, interval: 00:00:02, iterations: 7000

┌─────────────────────────┬────────────────────────────────────────────────────────┐
│                    step │ ok stats                                               │
├─────────────────────────┼────────────────────────────────────────────────────────┤
│                    name │ global information                                     │
│           request count │ all = 7000, ok = 7000, RPS = 250                       │
│            latency (ms) │ min = 12.95, mean = 24.62, max = 546.2, StdDev = 22.88 │
│ latency percentile (ms) │ p50 = 20.51, p75 = 23.89, p95 = 38.75, p99 = 113.02    │
└─────────────────────────┴────────────────────────────────────────────────────────┘

To validate the completed solution the system generates two JSON files within the data_audit directory during a test run. These files are used by the TestComparisonService to generate the 3-way integrity report

• sent_wagers_audit.json: Generated by the BogusTester - contains the every wager sent exactly as it was generated and sent by the load tester
• received_wagers_audit.json: Generated by the API via the /api/Player/debug/testResults endpoint - captures the wagers as they were handled by the API controllers before being published to the message broker

By comparing these two files against the live SQL Wagers table, the user can determine if any failures occured and whether there are any descrepencies in the data

The report can be generated by calling the API endpoint /api/Player/debug/testResults after a test run

Wager 3-Way Integrity Report
============================
1. Total Wagers Sent (Tester):  7000 (Unique: 6804)
2. Total Wagers Received (API): 7000 (Unique: 6804)
3. Total Wagers Saved (DB):     6804
----------------------------
[PASS] Network: All unique wagers successfully reached the API
[PASS] Persistence: DB count matches unique API receipts (Deduplication successful)

Top 10 Player Stats Comparison (DB vs Sent Unique Source):
(Duplicates have been removed from Sent Source for accurate comparison)

Player Josephine.Schaefer36 (f59886ce-2f76-9c52-cf27-a937828798df):
  - DB Spend: 431,745.57 | Sent Source: 431,745.57
  - Diff: 0.00 (MATCH)

Player Gerard_Reinger10 (0aeb4946-9aa3-cdc1-31eb-187f9d967169):
  - DB Spend: 425,333.13 | Sent Source: 425,333.13
  - Diff: 0.00 (MATCH)

Player Stuart.Considine7 (a41de994-c7bb-42a3-607d-0fb53798e8f6):
  - DB Spend: 422,105.91 | Sent Source: 422,105.92
  - Diff: -0.01 (MATCH)

Player Beulah29 (b4828e36-f5a1-0ad3-fee8-8cef5497e7fb):
  - DB Spend: 415,358.03 | Sent Source: 415,358.04
  - Diff: -0.01 (MATCH)

Player Richard_Wunsch (2ec848d1-2a45-8850-d792-971a647754ec):
  - DB Spend: 402,346.08 | Sent Source: 402,346.07
  - Diff: 0.01 (MATCH)

Player Ebony_Kemmer58 (d215ac3d-e99a-19cd-a84b-81e201004e92):
  - DB Spend: 400,840.98 | Sent Source: 400,840.96
  - Diff: 0.02 (MATCH)

Player Brandi.Spinka (1e125c82-fb74-1433-a83f-f4620745e4eb):
  - DB Spend: 399,713.58 | Sent Source: 399,713.59
  - Diff: -0.01 (MATCH)

Player Rachel67 (40986b34-bee7-0639-ab99-960b3dc6a933):
  - DB Spend: 394,748.58 | Sent Source: 394,748.59
  - Diff: -0.01 (MATCH)

Player Tasha_Reilly37 (d9d12813-a605-12e8-212b-9b3683ec9037):
  - DB Spend: 394,585.77 | Sent Source: 394,585.78
  - Diff: -0.01 (MATCH)

Player Sandy_Ward (b84ea7d0-3bab-08e5-d4fd-f63a9d8dbee3):
  - DB Spend: 390,132.71 | Sent Source: 390,132.71
  - Diff: 0.00 (MATCH)

• POST /casinowager (page size 10):
  • 255ms (from DB)
  • 103ms (from Redis Cache)
• GET /topSpenders (count 10):
  • 192ms (from DB)
  • 103ms (from Redis Cache)

The database schema includes a custom password (Guest123!) and standardized port mappings to ensure compatibility across Aspire and Docker environments. Automated PowerShell scripts are provided in the assets/scripts directory to ensure environment variables and connection strings are correctly mapped in Release mode.

Note: Swagger UI is enabled by default for this assessment

This profile is for testing in a containerized infrastructure while running the .NET projects locally via the CLI. The script automatically handles infrastructure startup and launches the API and Consumer in separate terminal windows. Performance is optimal locally using this approach.

1. Open a terminal in the root directory
2. Run the Docker hybrid script:

   .\assets\scripts\docker_run.ps1

3. The script will:
   • Spin up SQL Server, Redis, and RabbitMQ via Docker
   • Launch the Consumer in a new window
   • Launch the App API in a new window
4. Access Swagger UI at: http://localhost:5021/swagger

.NET Aspire orchestrates the API, Worker, RabbitMQ, SQL Server, and Redis automatically. This script ensures the dashboard and telemetry are correctly initialized.

Note: OpenTelemetry has been disabled by default to improve performance. Running Aspire locally has too much overhead and results are far worse than the docker method. In a real-world environment it would be deployed on a host machine and performance would be comparable

1. Open a terminal in the root directory
2. Run the Aspire orchestration script:

   .\assets\scripts\aspire_run.ps1

3. Open the Aspire Dashboard (link provided in terminal) to view telemetry and access the Swagger UI endpoint
   > You can access the App API Swagger, RabbitMQ Web Portal, view SQL Connection Strings, and view system logs all from the Aspire Dashboard

NOTE: Before running the load test, you must wait for the infrastructure and database to fully initialize

1. Ensure the infrastructure is running via Profile 1 or Profile 2
2. Wait for Health Checks:
   • Docker: Ensure the terminal windows show the applications have successfully started and connected
   • Aspire: Ensure all resources (api-app, consumer-worker, sql, redis, messaging) are marked as Healthy (green)
3. Verify Database Upgrade: Ensure DbUp has successfully migrated the schema. Look for the "Success" log in the terminal or Aspire Console Logs
4. Once verified, run the automated tester script:

   .\assets\scripts\tester_run.ps1

5. Monitor the Consumer logs to see wagers being processed and saved
6. Check the API endpoint /api/Player/debug/testResults to view the automated 3-way reconciliation audit

• MassTransit.RabbitMQ (8.5.8) – Message brokering and batching (docs)
• Dapper (2.1.72) – High-performance micro-ORM for SQL reads/writes (docs)
• dbup-sqlserver (7.2.0) – Automated schema migrations (docs)
• StackExchange.Redis (2.11.0) – Distributed caching (docs)
• Polly (8.6.6) – Resilience pipelines (exponential backoffs for container startup) (docs)
• Aspire.Hosting.AppHost (13.2.0) – Infrastructure orchestration (docs)