📈 Real-Time Crypto Analytics & Buy Advisor Platform

A production-ready, end-to-end real-time cryptocurrency analytics platform. It features multi-source ingestion, Microsoft SQL Server persistence, a FastAPI backend, a Next.js + Tailwind CSS + Recharts dashboard, and a PyCaret Machine Learning engine that dynamically auto-selects and fits the best classifier (by F1) and regressor (by RMSE) to deliver live investment recommendations.

🔍 System Architecture

The following diagram illustrates the lifecycle of data from extraction through storage, analytical processing, machine learning prediction, and visual rendering on the dashboard.

flowchart TD
    subgraph Data Sources [External Data Ingestion]
        CG_M[CoinGecko /coins/markets]
        CG_G[CoinGecko /global]
        CG_T[CoinGecko /search/trending]
        ALT_FG[Alternative.me Fear & Greed]
    end

    subgraph ETL Engine [ETL Orchestrator - etl_pipeline.py]
        E[Extract Layer - extract.py]
        T[Transform Layer - transform.py]
        L[Load Layer - load.py]
        APS[APScheduler - 5 Min Interval]
        
        CG_M & CG_G & CG_T & ALT_FG --> E
        E -->|Raw JSON Dumps| T
        T -->|Feature Engineering & Cleaning| L
        APS -.->|Triggers| E
    end

    subgraph Storage [Relational Data Store]
        DB[(Microsoft SQL Server - SQLEXPRESS)]
        L -->|PyODBC Bulk Inserts| DB
    end

    subgraph Backend [FastAPI Application - backend/main.py]
        API_A[Analytics Engine - analysis.py]
        API_ML[ML Endpoint Handler]
        
        DB <-->|SQL Queries & Pandas DataFrames| API_A
    end

    subgraph Machine Learning [PyCaret Pipeline - ml/]
        TR[Training Engine - train.py]
        INF[Inference Engine - predict.py]
        MODELS[(Saved Models & Metadata)]
        
        DB -->|Historical Records| TR
        TR -->|Finalizes Best Models| MODELS
        MODELS --> INF
        INF <--> API_ML
    end

    subgraph UI [Next.js Front End - frontend/]
        DASH[Dashboard Root]
        SWR[SWR Client - 60s Refresh]
        CHARTS[Responsive Recharts]
        ADVISOR[Buy Advisor Component]
        
        API_A <-->|JSON Rest API| SWR
        API_ML <-->|Live Predictions & Leaderboard| ADVISOR
        SWR --> DASH
        CHARTS -.-> DASH
        ADVISOR -.-> DASH
    end

🛠️ Tech Stack & Key Highlights

Data Ingestion & Transformation

Reliable Extraction: Multi-threaded, scheduled task pipelines pulling from multiple public REST endpoints.
Rate-Limit Resilience: Ingestion engines implement exponential backoff algorithms (e.g. backoff ** attempt) to prevent and bypass HTTP 429 rate limits.
Structured Cleansing: Null-check handling, case-standardization, and derived score generation (volatility_score).

Relational Database Store

Production Persistence: Engineered on top of Microsoft SQL Server (SQLEXPRESS) using SQLAlchemy and pyodbc core.
Bulk Insert Optimization: High-performance batch execution enabled by enabling fast_executemany=True on the SQL Alchemy engine.
Constraint Handling: Unique database indices created using T-SQL, configured with IGNORE_DUP_KEY = ON to filter and skip duplicate data entries silently without failing batch executions.

Asynchronous Backend

FastAPI Core: Lightweight asynchronous routing with automatic OpenAPI documentation.
Analytics CTEs: Complex analytics calculations (ranking, daily movement, historical trend vectors) performed directly in database queries using T-SQL Common Table Expressions (CTEs) in analysis.py.

Automated Machine Learning (PyCaret & Fallback Engine)

Auto-ML Architecture: Features PyCaret for automated model selection. When a user requests a recommendation, the system loads all history, evaluates multiple model structures, selects the best model, and caches it.
Robust Fallback Engine: If PyCaret is not installed (e.g., in environments running Python 3.12 or 3.13 where PyCaret cannot compile due to legacy numpy constraints), the system automatically falls back to a rule-based statistical model that predicts BUY/DON'T BUY and calculates expected prices based on volatility and 24h/7d price changes, ensuring zero runtime downtime.
Supervised Classification: Compares all classifiers to output a binary decision target buy_signal (representing price gain $> \text{threshold}$ over a future window). The model is optimized for F1 Score to balance precision and recall.
Supervised Regression: Compares all regressors to predict the precise future price (future_price). The model is optimized for minimum RMSE.
Dynamic In-Sync Inference: Selecting any coin in the dashboard snapshot dynamically triggers prediction requests for the target coin/horizon and displays classification confidence alongside the regression price.

Premium Frontend Dashboard

React/Next.js: Formatted using Next.js 14 App Router, styled with custom Vanilla CSS for visual excellence.
Data Hooking: Utilizes SWR hooks configured with a 60-second auto-refresh pattern to keep data constantly updated.
Responsive Visualizations: Custom Recharts implementation featuring interactive Area charts with gradients, multi-dataset bar indicators, and market dominance pie distributions.
Interactive Widgets: Interactive SVG gauge dashboard indicator depicting the real-time Fear & Greed index.

📁 Project Structure

The project is structured logically separating the ingestion pipelines, backend server, machine learning modules, and user interface application:

Real-Time Crypto Analytics/
├── config.py                 # ODBC database configuration and URL construction
├── logger.py                 # Core console & file logging utility mapping to logs/etl.log
├── database.py               # SQLAlchemy engines & T-SQL DDL statement migrations
├── extract.py                # Ingestion extraction scripts with exponential backoff
├── transform.py              # Data sanitization, formatting, and structural preparation
├── load.py                   # Pyodbc bulk insertions
├── etl_pipeline.py           # Pipeline scheduler managing the 5-minute ingestion intervals
├── analysis.py               # Analytical module implementing T-SQL CTEs & data queries
├── init_db.py                # Schema installation script
├── requirements.txt          # Python dependencies
├── .env.example              # Core environment configuration template
├── .gitignore                # Multi-level git exclusion guidelines
│
├── backend/
│   └── main.py               # FastAPI application entry point, routing, and middleware
│
├── ml/
│   ├── train.py              # PyCaret dataset assembly, validation, training, and persistence
│   └── predict.py            # Feature engineering, model loader, and recommendation outputs
│
└── frontend/                 # Next.js 14 Dashboard
    ├── app/
    │   ├── page.tsx          # Dashboard page assembly and SWR hook definitions
    │   └── globals.css       # Core layout styles, variables, typography, and button specs
    ├── components/
    │   ├── SnapshotTable.tsx # Sorted list displaying the latest state of all market assets
    │   ├── Predictions.tsx   # Interactive advisor interface with PyCaret analytics
    │   ├── Charts.tsx        # Responsive Recharts components (Dominance, Volatility, History)
    │   ├── FearGreedGauge.tsx# Custom SVG visual gauge displaying market sentiment
    │   └── TrendingCoins.tsx # List of current trending assets
    └── lib/api.ts            # SWR Fetcher setup and financial number formatters

🗄️ Database Schema & T-SQL Definitions

The platform leverages Microsoft SQL Server to persist real-time metrics. The relational database schema is structured into four core tables:

1. `crypto_market`

Stores historical snapshot data for tracked cryptocurrencies per ETL execution.

Column Name	Data Type	Constraints	Description
`id`	`INT`	`IDENTITY(1,1) PRIMARY KEY`	Auto-incrementing identifier
`coin_id`	`NVARCHAR(100)`	`NOT NULL`	Unique API coin name (e.g. `bitcoin`)
`symbol`	`NVARCHAR(50)`	-	Ticker symbol in lowercase
`name`	`NVARCHAR(200)`	-	Full asset name
`image`	`NVARCHAR(500)`	-	URL pointing to asset icon
`current_price`	`FLOAT`	-	Latest market price in USD
`market_cap`	`BIGINT`	-	Total market capitalization
`total_volume`	`BIGINT`	-	24-hour trading volume
`price_change_1h`	`FLOAT`	-	Hourly price change percentage
`price_change_24h`	`FLOAT`	-	Daily price change percentage
`price_change_7d`	`FLOAT`	-	7-day price change percentage
`market_cap_rank`	`INT`	-	Current ranking by market capitalization
`high_24h`	`FLOAT`	-	24-hour high price record
`low_24h`	`FLOAT`	-	24-hour low price record
`circulating_supply`	`FLOAT`	-	Circulating token amount
`ath`	`FLOAT`	-	All-time-high price
`atl`	`FLOAT`	-	All-time-low price
`volatility_score`	`FLOAT`	-	Derived score: `abs(price_change_24h) * total_volume`
`extracted_at`	`DATETIME2`	`NOT NULL`	UTC time of extraction

Indexes:

idx_crypto_coin_id on coin_id for quick asset history retrieval.
idx_crypto_extracted_at on extracted_at DESC for time-series range analysis.
uq_crypto_coin_extracted on (coin_id, extracted_at) Unique Index containing configuration directive WITH (IGNORE_DUP_KEY = ON) to silently drop duplicate snapshots.

2. `global_metrics`

Saves aggregate cryptocurrency market indicators.

Column Name	Data Type	Constraints	Description
`id`	`INT`	`IDENTITY(1,1) PRIMARY KEY`	Auto-incrementing identifier
`active_cryptocurrencies`	`INT`	-	Total count of live crypto assets
`ongoing_icos`	`INT`	-	Ongoing initial coin offerings
`markets`	`INT`	-	Total number of trading markets
`total_market_cap_usd`	`FLOAT`	-	Total market valuation in USD
`total_volume_24h_usd`	`FLOAT`	-	Aggregate 24-hour transaction volume
`btc_dominance`	`FLOAT`	-	Bitcoin market cap dominance (%)
`eth_dominance`	`FLOAT`	-	Ethereum market cap dominance (%)
`market_cap_change_24h`	`FLOAT`	-	24h market cap change percentage
`extracted_at`	`DATETIME2`	`NOT NULL`	UTC time of extraction

3. `fear_greed_index`

Stores market sentiment indexes pulled from Alternative.me.

Column Name	Data Type	Constraints	Description
`id`	`INT`	`IDENTITY(1,1) PRIMARY KEY`	Auto-incrementing identifier
`value`	`INT`	-	Index value (0 to 100)
`classification`	`NVARCHAR(50)`	-	Sentiment label (e.g. `Greed`, `Fear`)
`sampled_at`	`DATETIME2`	-	Historical sampling time
`extracted_at`	`DATETIME2`	`NOT NULL`	UTC extraction time

4. `trending_coins`

Saves the trending assets listed on CoinGecko search indexes.

Column Name	Data Type	Constraints	Description
`id`	`INT`	`IDENTITY(1,1) PRIMARY KEY`	Auto-incrementing identifier
`coin_id`	`NVARCHAR(100)`	-	Target API coin id
`name`	`NVARCHAR(200)`	-	Asset name
`symbol`	`NVARCHAR(50)`	-	Asset symbol
`market_cap_rank`	`INT`	-	Current market capitalization rank
`score`	`INT`	-	Trending search score rank index
`thumb`	`NVARCHAR(500)`	-	Icon thumbnail URL
`extracted_at`	`DATETIME2`	`NOT NULL`	UTC extraction time

🔌 API Endpoint Registry

The FastAPI server exposes endpoints classified under Analytics and Machine Learning services.

Analytics Endpoints

Method	Endpoint	Query Parameters	Description
GET	`/api/health`	-	Diagnostic check testing database connectivity state and server time.
GET	`/api/kpis`	-	Computes global KPIs: total cap, average price, highest gainer, and most volatile asset.
GET	`/api/snapshot`	-	Lists latest market snapshots for all tracked cryptocurrencies.
GET	`/api/top-gainers`	`n: int = 5`	Lists the top `n` gainers over the past 24 hours.
GET	`/api/top-losers`	`n: int = 5`	Lists the top `n` losers over the past 24 hours.
GET	`/api/top-market-cap`	`n: int = 5`	Lists the top `n` assets ordered by total market capitalization.
GET	`/api/volatility`	`n: int = 10`	Lists top `n` most volatile assets using `volatility_score` DESC.
GET	`/api/history/{coin_id}`	`limit: int = 100`	Fetches historical price snapshots for the specified `coin_id`.
GET	`/api/global`	-	Retrieves the latest global market capitalization statistics and dominance values.
GET	`/api/global/history`	`limit: int = 50`	Fetches historical global metrics trends.
GET	`/api/sentiment`	-	Retrieves the latest Fear & Greed sentiment index value.
GET	`/api/sentiment/history`	`limit: int = 30`	Fetches historical sentiment snapshots.
GET	`/api/trending`	`n: int = 10`	Returns the latest trending list of coins.
POST	`/api/etl/run`	-	Triggers an immediate background extraction loop inside an asynchronous worker.

Machine Learning Endpoints

Method	Endpoint	Query Parameters	Description
GET	`/api/ml/status`	-	Reports model readiness status and loads metadata of finalized algorithms.
GET	`/api/ml/leaderboard`	-	Outputs full PyCaret test diagnostic scores for evaluated classification & regression models.
GET	`/api/ml/coins`	-	Returns all coins currently saved in database that are eligible for forecasting.
GET	`/api/ml/recommend/{coin_id}`	`horizon_steps: int = 12`	Evaluates classification & regression forecasts for the target coin. Triggers PyCaret auto-training on first call.

🔮 Machine Learning Pipeline Deep Dive

The platform's Buy Advisor leverages an automated training and inference pipeline powered by PyCaret:

                  Raw Database History (crypto_market)
                                  │
                                  ▼
                        Feature Engineering
                ┌──────────────────────────────────┐
                │ • intraday_range_pct             │
                │ • price_vs_ath_pct               │
                └──────────────────────────────────┘
                                  │
         ┌────────────────────────┴────────────────────────┐
         ▼                                                 ▼
   Classification Setup                             Regression Setup
┌─────────────────────────────────┐               ┌─────────────────────────────────┐
│ Target: buy_signal              │               │ Target: future_price            │
│ (1 if price change > threshold) │               │ (Expected price at step + H)    │
└────────┬────────────────────────┘               └────────┬────────────────────────┘
         │                                                 │
         ▼                                                 ▼
PyCaret compare_models()                          PyCaret compare_models()
  - Optimizes F1 Score                              - Optimizes RMSE
  - 5-Fold Cross Validation                         - 5-Fold Cross Validation
  - Finalizes Best Classifier                       - Finalizes Best Regressor
         │                                                 │
         └────────────────────────┬────────────────────────┘
                                  │
                                  ▼
                         Model Storage
                ┌──────────────────────────────────┐
                │ • models/best_classifier.pkl     │
                │ • models/best_regressor.pkl      │
                │ • models/meta.json               │
                │ • models/leaderboard.json        │
                └──────────────────────────────────┘

1. Feature Engineering

Input records are enriched with derived financial metrics to strengthen predictive capability:

Intraday Range: $\text{intraday_range_pct} = \frac{\text{high_24h} - \text{low_24h}}{\text{current_price}}$
ATH Distance: $\text{price_vs_ath_pct} = \frac{\text{current_price} - \text{ath}}{\text{ath}}$
Standardized Features: Features such as current_price, market_cap, total_volume, price_change_1h, price_change_24h, price_change_7d, market_cap_rank, high_24h, low_24h, circulating_supply, and volatility_score are normalized automatically by PyCaret's pipeline setup.

2. Supervised Target Setup

Historical data points are offset by horizon_steps to build supervised datasets:

Regression Target: $y_{\text{reg}} = \text{Price}_{t + \text{horizon}}$
Classification Target: $y_{\text{clf}} = \begin{cases} 1 & \text{if } \frac{\text{Price}_{t + \text{horizon}} - \text{Price}_t}{\text{Price}_t} \times 100 > \text{buy_threshold_pct} \ 0 & \text{otherwise} \end{cases}$

3. Model Comparison & Training

The pipeline splits data into an 80/20 train/test split with 5-fold cross-validation.
Classifier Selection: Evaluates over 15 classification algorithms (e.g., LightGBM, XGBoost, Random Forest, Logistic Regression). The pipeline evaluates models based on F1 Score to balance trading accuracy while minimizing false-positive buy signals.
Regressor Selection: Evaluates algorithms to forecast future price, selecting the model that minimizes Root Mean Squared Error (RMSE).
Finalization & Persistence: The winning models are fitted on the entire dataset and persisted under models/ as pickle binaries alongside execution logs.

🚀 Getting Started

Follow these steps to configure, install, and run the platform locally.

Prerequisites & Dependencies

Operating System: Windows (tested on Windows 10/11)
Database Engine: Microsoft SQL Server (LocalDB or Express instance named SQLEXPRESS)
ODBC Driver: Microsoft ODBC Driver 17 or 18 for SQL Server
Python: version 3.9 through 3.13+ (PyCaret fits best on 3.9-3.11; on Python 3.12/3.13, a rule-based mathematical model handles recommendations seamlessly without requiring PyCaret compilation)
Node.js: version 18 or above

Step 1: Database Setup

Launch SQL Server Management Studio (SSMS) or use the command line to connect to your instance.

Create an empty database named exactly:

CREATE DATABASE [Real-Time-Crypto-Analytics];

Verify that your Windows user has db_owner permissions on the database.

Step 2: Backend & Python Installation

Clone the repository and navigate to the project root:
```
cd Real-Time Crypto Analytics
```

Set up and activate a Virtual Environment:

python -m venv .venv
.venv\Scripts\activate

Install dependencies:

pip install --upgrade pip
pip install -r requirements.txt

Copy the environment template and initialize the SQL tables:
```
copy .env.example .env
python init_db.py
```

Step 3: Frontend Installation

Navigate to the frontend directory:
```
cd frontend
```
Install npm packages:
```
npm install
```
Set up the environment variables:
```
copy .env.local.example .env.local
```
(Ensure NEXT_PUBLIC_API_URL points to http://localhost:8000 or your customized port).

⚙️ Running the Services

To run the application, open three separate terminal windows with active virtual environments (for Python tasks):

Terminal 1: Background ETL Ingestor

Fires the extraction pipeline immediately and schedules the recurring 5-minute ingest loop.

.venv\Scripts\activate
python etl_pipeline.py

Note: To trigger a single, run-once ingestion immediately to populate the database without starting the scheduler daemon, execute:

python etl_pipeline.py --once

Terminal 2: FastAPI Backend Server

Launches the API on port 8000.

.venv\Scripts\activate
uvicorn backend.main:app --reload --port 8000

Interactive Swagger API docs will be accessible at http://localhost:8000/docs.

Terminal 3: Next.js Frontend App

Starts the dashboard client.

cd frontend
npm run dev -- -p 3000

Open http://localhost:3000 in your browser to view the live dashboard.

🛠️ Troubleshooting & Debugging

SQL Server Driver Failures

Symptom: pyodbc.InterfaceError: ('IM002', '[Microsoft][ODBC Driver Manager] Data source name not found...')
Resolution: Install the Microsoft ODBC Driver 17 for SQL Server. If you installed ODBC Driver 18, configure .env to override:
```
ODBC_DRIVER=ODBC Driver 18 for SQL Server
```

SSL & Handshake Encryption Errors

Symptom: pyodbc.OperationalError: ('08001', '[Microsoft][ODBC Driver 18 for SQL Server] SSL Provider: The certificate chain was issued by an authority that is not trusted.')
Resolution: Disable client verification in your local test environment by adding the following parameters to your .env file:
```
DB_ENCRYPT=no
DB_TRUST_CERT=yes
```

Machine Learning Inactive / Training Failures

Symptom: RuntimeError: Not enough labelled rows (0 < 20). Let the ETL collect more snapshots, then retry.
Resolution: The PyCaret models require historical datasets with varying timeslots per coin to fit classifiers. Let the ETL ingestor run or run the command python etl_pipeline.py --once multiple times (waiting brief periods between runs to ensure CoinGecko metrics fluctuate) to build history. You need a minimum of 20 historical records in total to train, though 200+ is highly recommended for predictive stability.
Symptom: RuntimeError: BUY-signal target has only one class.
Resolution: The price movement of your assets hasn't crossed your classification trigger threshold. Decrease buy_threshold_pct in ml/train.py or wait for larger market volatility to generate both 0 and 1 labels.
Symptom: ModuleNotFoundError: No module named 'pycaret' (on Python 3.12/3.13)
Resolution: PyCaret requires legacy dependencies (numpy < 1.27) which cannot compile on newer Python versions (3.12+). The platform has an auto-detection fallback built-in. If PyCaret is not found, the backend falls back to rule-based forecasting models, displaying full diagnostics and predictions in the UI without error. No action is required.

API Rate Limiting (HTTP 429)

Symptom: 429 from https://api.coingecko.com/api/v3/coins/markets; sleeping...
Resolution: The free public CoinGecko API has strict hourly request limits. The platform is configured with an exponential backoff loop in extract.py to sleep and retry requests. Avoid manual browser API refreshing or calling /api/etl/run too frequently. Consider upgrading to a pro API key if you require high-frequency updates.

Name		Name	Last commit message	Last commit date
backend		backend
dataset		dataset
frontend		frontend
ml		ml
notebooks		notebooks
.gitignore		.gitignore
README.md		README.md
analysis.py		analysis.py
database.py		database.py
etl_pipeline.py		etl_pipeline.py
extract.py		extract.py
import_dataset.py		import_dataset.py
init_db.py		init_db.py
load.py		load.py
logger.py		logger.py
requirements.txt		requirements.txt
transform.py		transform.py

Latest commit

History

Folders and files

Repository files navigation