# Market Data Service

A high-performance financial data API that provides comprehensive market data for various financial instruments including cryptocurrencies, stocks, forex, and commodities through both RESTful endpoints and real-time WebSocket connections.

## MT5 Integration

The service includes an MT5 Expert Advisor (EA) that automatically sends historical candle data and live tick prices to the API.

### EA Features
- Automatic symbol registration in the database
- Bulk upload of last 1000 candles per symbol
- Real-time price streaming on tick events
- Error handling with retry logic
- Configurable API endpoint and authentication

### Setup Instructions
1. Copy `MT5/Experts/MarketDataSender.mq5` to your MT5 Experts directory
2. Configure EA settings:
   - `ApiBaseUrl`: Your API endpoint (e.g., `http://your-server:3000`)
   - `ApiKey`: Optional authentication key
   - `HistoricalCandleCount`: Number of historical candles to send (default: 1000)
   - `HistoricalTimeframe`: Timeframe for historical data (default: H1)

3. Attach the EA to any MT5 chart
4. The EA will:
   - Register all available symbols with the API
   - Send historical candles on initialization
   - Stream live prices every second

### Notes
- Symbols are automatically created if they don't exist
- Exchange is derived from symbol name (format: `EXCHANGE_SYMBOL`)
- Default instrument type is forex (customize in EA code if needed)

## Features  
✅ **MT5 Integration**  
- Automatic retry logic (3 attempts with exponential backoff)  
- Precision-preserving data transmission  
- Symbol auto-registration  

✅ **Data Integrity**  
- Unique constraint on candle timestamps per symbol  
- 15-decimal precision enforcement  
- Strict schema validation  

✅ **Reliability**  
- Database transaction safety  
- Error recovery mechanisms  
- Comprehensive test coverage  

- **Multi-Asset Support**: Handles cryptocurrencies, stocks, forex, commodities, and indices
- **Real-time Data**: Live price feeds with bid/ask spreads
- **Historical Data**: OHLCV candle data with flexible timeframes
- **RESTful API**: Well-structured endpoints for all operations
- **Data Validation**: Comprehensive input validation using Joi
- **Error Handling**: Robust error handling with detailed responses
- **Security**: Helmet.js for security headers, CORS support
- **Logging**: Winston-based logging with multiple transports
- **Database**: PostgreSQL with Sequelize ORM
- **Scalable Architecture**: Modular design with controllers, routes, and middleware

## Tech Stack

- **Backend**: Node.js, Express.js
- **Database**: PostgreSQL
- **ORM**: Sequelize
- **Validation**: Joi
- **Security**: Helmet, CORS
- **Logging**: Winston, Morgan
- **Testing**: Jest, Supertest
- **Development**: Nodemon, ESLint, Sequelize CLI

## Project Structure

```
market-data-service/
├── src/
│   ├── config/
│   │   └── database.js          # Database configuration
│   ├── controllers/
│   │   ├── symbolController.js      # Symbol CRUD operations
│   │   ├── candleController.js      # Candle data operations
│   │   └── livePriceController.js   # Live price operations
│   ├── middleware/
│   │   ├── errorHandler.js          # Global error handling
│   │   └── validation.js            # Request validation
│   ├── models/
│   │   ├── Symbol.js                # Symbol model
│   │   ├── Candle1h.js              # 1-hour candle model
│   │   ├── LivePrice.js             # Live price model
│   │   └── index.js                 # Model associations
│   ├── routes/
│   │   ├── symbols.js               # Symbol routes
│   │   ├── candles.js               # Candle routes
│   │   └── livePrices.js            # Live price routes
│   ├── utils/
│   │   └── logger.js                # Logging utility
│   ├── app.js                       # Express app configuration
│   └── server.js                    # Server startup
├── tests/                           # Test files
├── schema.sql                       # Database schema
├── .env                             # Environment variables
├── .gitignore                       # Git ignore rules
├── package.json                     # Dependencies and scripts
└── README.md                        # This file
```

## Technical Specifications  

**Database Constraints**  
```sql
ALTER TABLE candles_1h 
ADD CONSTRAINT unique_symbol_open_time 
UNIQUE (symbol_id, open_time);
```

**Precision Requirements**  
```javascript
// All numeric fields require 15 decimal precision
Joi.number().precision(15)
```

## 🚀 Complete Deployment Guide

## 📋 Prerequisites (All Versions)

### Required Software (Latest Stable Versions)
- **Node.js**: v18.x LTS or higher
- **PostgreSQL**: v13.x or higher
- **Nginx**: v1.24.x or higher
- **Git**: Latest version
- **Certbot** (for SSL certificates)

---

## 🆕 FRESH SERVER DEPLOYMENT (Step-by-Step)

### Step 1: Server Preparation
```bash
# Update system packages
sudo apt update && sudo apt upgrade -y

# Install required tools
sudo apt install -y curl wget git htop nano ufw
```

### Step 2: Install Node.js (v18.x LTS)
```bash
# Using NodeSource repository
curl -fsSL https://deb.nodesource.com/setup_18.x | sudo -E bash -
sudo apt-get install -y nodejs

# Verify installation
node --version  # Should show v18.x.x
npm --version   # Should show latest version
```

### Step 3: Install PostgreSQL
```bash
# Install PostgreSQL
sudo apt install -y postgresql postgresql-contrib

# Start and enable PostgreSQL
sudo systemctl enable postgresql
sudo systemctl start postgresql

# Verify installation
psql --version  # Should show 15.x or higher
```

### Step 4: Deploy Application
```bash
# Create application directory
sudo mkdir -p /root/market-data-service
sudo chown $USER:$USER /root/market-data-service
cd /root/market-data-service

# Clone repository (replace with your actual repo URL)
git clone https://git.mqldevelopment.com/muhammad.uzair/market-data-service.git .
# OR copy your project files to this directory

# Install dependencies
npm install --production

# Set up environment variables
cat > .env << EOF
DB_TYPE=postgres
DB_HOST=localhost
DB_PORT=5432
DB_NAME=financial_data
DB_USER=postgres
DB_PASSWORD=your_secure_password_here

PORT=3001
NODE_ENV=production

JWT_SECRET=your_secure_jwt_secret_key_here
CORS_ORIGIN=*

LOG_LEVEL=info
EOF
```

### Step 5: Database Setup
```bash
# Switch to postgres user
sudo -u postgres psql

# In PostgreSQL shell, run:
CREATE DATABASE financial_data;
ALTER USER postgres PASSWORD 'your_secure_password_here';
GRANT ALL PRIVILEGES ON DATABASE financial_data TO postgres;
\q

# Exit and run migrations
npx sequelize-cli db:migrate

# Verify tables were created
sudo -u postgres psql -d financial_data -c "\dt"
```

### Step 6: Install PM2 (Process Manager)
```bash
# Install PM2 globally
sudo npm install -g pm2

# Create PM2 ecosystem file
cat > ecosystem.config.js << EOF
module.exports = {
  apps: [{
    name: 'market-data-api',
    script: 'src/server.js',
    instances: 'max',
    exec_mode: 'cluster',
    env: {
      NODE_ENV: 'production',
      PORT: 3001
    },
    error_file: './logs/err.log',
    out_file: './logs/out.log',
    log_file: './logs/combined.log',
    time: true,
    merge_logs: true,
    watch: false,
    max_memory_restart: '1G'
  }]
};
EOF

# Start with PM2
pm2 start ecosystem.config.js
pm2 save
pm2 startup

# Verify PM2 status
pm2 status
pm2 logs
```

### Step 7: Configure Nginx (WITHOUT SSL first)
```bash
# Copy nginx configuration
sudo cp nginx-1.24.0/conf/nginx.conf /etc/nginx/nginx.conf

# Test configuration
sudo nginx -t

# Start Nginx
sudo systemctl enable nginx
sudo systemctl start nginx
```

### Step 8: Install SSL Certificate (Certbot)
```bash
# Install certbot
sudo apt install -y certbot python3-certbot-nginx

# Generate SSL certificate
sudo certbot --nginx -d your-domain.com

# Test certificate
sudo certbot certificates
```

### Step 9: Update Nginx for SSL (After Certificate Generation)
```bash
# Update nginx configuration with SSL
sudo tee /etc/nginx/sites-available/market-data-api << EOF
server {
    listen 80;
    server_name your-domain.com;

    # Redirect HTTP to HTTPS
    return 301 https://\$server_name\$request_uri;
}

server {
    listen 443 ssl http2;
    server_name your-domain.com;

    # SSL Configuration
    ssl_certificate /etc/letsencrypt/live/your-domain.com/fullchain.pem;
    ssl_certificate_key /etc/letsencrypt/live/your-domain.com/privkey.pem;
    ssl_protocols TLSv1.2 TLSv1.3;
    ssl_ciphers ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384;
    ssl_prefer_server_ciphers off;

    # Security Headers
    add_header Strict-Transport-Security "max-age=31536000; includeSubDomains" always;
    add_header X-Frame-Options DENY always;
    add_header X-Content-Type-Options nosniff always;
    add_header X-XSS-Protection "1; mode=block" always;

    # API Proxy Settings
    location /api/ {
        proxy_pass http://localhost:3001;
        proxy_http_version 1.1;
        proxy_set_header Upgrade \$http_upgrade;
        proxy_set_header Connection 'upgrade';
        proxy_set_header Host \$host;
        proxy_set_header X-Real-IP \$remote_addr;
        proxy_set_header X-Forwarded-For \$proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto \$scheme;

        # Timeout and buffer settings
        proxy_connect_timeout 60s;
        proxy_send_timeout 60s;
        proxy_read_timeout 60s;
        proxy_buffering on;
        proxy_buffer_size 4k;
        proxy_buffers 8 4k;
        client_max_body_size 50M;
    }

    location /health {
        proxy_pass http://localhost:3001/health;
        proxy_http_version 1.1;
        proxy_set_header Host \$host;
        proxy_set_header X-Real-IP \$remote_addr;
        proxy_set_header X-Forwarded-For \$proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto \$scheme;
    }

    location / {
        return 200 "Market Data API is running. Use /api/ for endpoints\\n";
        add_header Content-Type text/plain;
    }
}
EOF

# Enable site and restart nginx
sudo ln -s /etc/nginx/sites-available/market-data-api /etc/nginx/sites-enabled/
sudo rm -f /etc/nginx/sites-enabled/default
sudo nginx -t
sudo systemctl restart nginx
```

### Step 10: Final Testing
```bash
# Test HTTP to HTTPS redirect
curl -I http://your-domain.com

# Test HTTPS API endpoints
curl https://your-domain.com/health
curl https://your-domain.com/api/health
curl https://your-domain.com/api/symbols

# Test bulk endpoint
curl -X POST https://your-domain.com/api/live-prices/bulk \
  -H "Content-Type: application/json" \
  -d '{"prices": [{"symbolId": 1, "price": 123.45}]}'
```

---

## 💻 LOCAL DEVELOPMENT SETUP

This section explains how to run the project locally on your development machine.

### Prerequisites for Local Development
- **Node.js**: v18.x LTS or higher
- **PostgreSQL**: v13.x or higher
- **Git**: Latest version

### Step-by-Step Local Setup

#### 1. Clone the Repository
```bash
# Clone from GitHub
git clone https://git.mqldevelopment.com/muhammad.uzair/market-data-service.git
cd market-data-service

# Install dependencies
npm install
```

#### 2. Set Up PostgreSQL Database
```bash
# Create local database
createdb financial_data

# Set up environment variables
cat > .env << EOF
DB_TYPE=postgres
DB_HOST=localhost
DB_PORT=5432
DB_NAME=financial_data
DB_USER=your_local_username
DB_PASSWORD=your_local_password

PORT=3001
NODE_ENV=development

JWT_SECRET=your_development_jwt_secret_key
CORS_ORIGIN=http://localhost:3000

LOG_LEVEL=debug
EOF
```

#### 3. Run Database Migrations
```bash
# Install Sequelize CLI (if not already installed)
npm install -g sequelize-cli

# Run migrations to create tables
npx sequelize-cli db:migrate

# Verify tables were created
psql -d financial_data -c "\dt"
```

#### 4. Start Development Server
```bash
# Option 1: Using npm script (recommended)
npm run dev

# Option 2: Using nodemon directly
npx nodemon src/server.js

# Option 3: Using node directly
node src/server.js
```

#### 5. Verify Local Setup
```bash
# Test health endpoint
curl http://localhost:3001/health

# Test API endpoints
curl http://localhost:3001/api/symbols
curl http://localhost:3001/api/live-prices

# Test bulk endpoint
curl -X POST http://localhost:3001/api/live-prices/bulk \
  -H "Content-Type: application/json" \
  -d '{"prices": [{"symbolId": 1, "price": 123.45}]}'
```


## 🔄 UPDATING EXISTING DEPLOYMENT

### Quick Update (When Code Changes on GitHub)

```bash
# Navigate to project directory
cd /root/market-data-service

# Stop the application
pm2 stop market-data-api

# Backup current deployment (optional but recommended)
cp .env .env.backup
cp ecosystem.config.js ecosystem.config.js.backup

# Pull latest changes
git pull origin master

# Install any new dependencies
npm install --production

# Run database migrations (if schema changed)
npx sequelize-cli db:migrate

# Restart application
pm2 start ecosystem.config.js
pm2 save

# Verify everything is working
pm2 status
curl https://your-domain.com/health
```

### Full Update (Major Changes)

```bash
# Navigate to project directory
cd /root/market-data-service

# Create backup
sudo cp -r /root/market-data-service /root/market-data-service-backup-$(date +%Y%m%d_%H%M%S)

# Stop services
pm2 stop market-data-api
sudo systemctl stop nginx

# Update code
git fetch origin
git reset --hard origin/master

# Install dependencies
npm install --production

# Update environment if needed
nano .env

# Run migrations
npx sequelize-cli db:migrate

# Update PM2 configuration if needed
pm2 start ecosystem.config.js
pm2 save

# Restart nginx
sudo systemctl start nginx

# Verify deployment
pm2 status
sudo systemctl status nginx
curl https://your-domain.com/health
```

---

## 🛠️ Troubleshooting Guide

### Common Issues & Solutions

#### 1. PostgreSQL User Already Exists
**Error:** `ERROR: role "postgres" already exists`

**Solution:**
```bash
# Just set password for existing user
sudo -u postgres psql
ALTER USER postgres PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE financial_data TO postgres;
\q
```

#### 2. SSL Certificate Not Found
**Error:** `cannot load certificate "/etc/letsencrypt/live/.../fullchain.pem"`

**Solution:**
```bash
# Generate certificate first
sudo certbot --nginx -d your-domain.com

# Check if certificates exist
ls -la /etc/letsencrypt/live/your-domain.com/

# Test nginx configuration
sudo nginx -t
```

#### 3. PM2 Multiple Instances
**Question:** Why are there 8 instances running?

**Answer:** This is **GOOD for production**:
- Uses all CPU cores efficiently
- Better load distribution
- Fault tolerance
- Zero-downtime restarts

**To adjust instances:**
```bash
pm2 scale market-data-api 4  # Use 4 instances
pm2 scale market-data-api 1  # Use 1 instance for testing
```

## 📊 Monitoring & Maintenance

```bash
# Check service status
pm2 status
sudo systemctl status nginx
sudo systemctl status postgresql

# View logs
pm2 logs --lines 50
sudo tail -f /var/log/nginx/access.log
sudo tail -f /var/log/nginx/error.log

```

### SSL Certificate Auto-Renewal
```bash
# Check renewal status
sudo certbot certificates
sudo systemctl status certbot.timer

# Manual renewal test
sudo certbot renew --dry-run
```

---

## 🔧 Management Commands

### PM2 Management
```bash
pm2 status              # Check status
pm2 logs                # View logs
pm2 monit               # Real-time monitoring
pm2 restart all         # Restart all apps
pm2 stop all            # Stop all apps
pm2 delete all          # Delete all apps
```

### Nginx Management
```bash
sudo systemctl status nginx      # Check status
sudo systemctl restart nginx     # Restart nginx
sudo nginx -t                    # Test configuration
sudo tail -f /var/log/nginx/error.log  # View error logs
```

### Database Management
```bash
# Connect to database
sudo -u postgres psql -d financial_data

# View tables
\dt

# View table structure
\d table_name

# Backup database
pg_dump financial_data > backup.sql

# Restore database
psql financial_data < backup.sql
```

---

## Environment Variables

Create a `.env` file in the root directory with the following variables:

```env
# Database Configuration
DB_HOST=localhost
DB_PORT=5432
DB_NAME=market_data
DB_USER=your_username
DB_PASSWORD=your_password

# Server Configuration
PORT=3001
NODE_ENV=development

# JWT Configuration (if needed for authentication)
JWT_SECRET=your_jwt_secret_key

# API Keys (if needed for external services)
# BINANCE_API_KEY=your_api_key
# BINANCE_API_SECRET=your_api_secret

# CORS Configuration
CORS_ORIGIN=*
```

## API Endpoints

### Health Check
- `GET /health` - Check service health

### Symbols
- `GET /api/symbols` - Get all symbols (with filtering)
- `GET /api/symbols/search?q=EURUSD` - Search symbols by name
- `GET /api/symbols/:id` - Get symbol by ID
- `POST /api/symbols` - Create new symbol
- `PUT /api/symbols/:id` - Update symbol
- `DELETE /api/symbols/:id` - Delete symbol (soft delete)

### Candles
- `GET /api/candles?symbolId=1&startTime=2025-01-01T00:00:00Z&endTime=2025-01-02T00:00:00Z&limit=100&offset=0` - Get candles with filtering
- `GET /api/candles/ohlc?symbolId=1&period=1h&limit=100` - Get OHLC data
- `GET /api/candles/:symbolId/latest` - Get latest candle for symbol
- `POST /api/candles` - Create new candle
- `POST /api/candles/bulk` - Bulk create candles
- `DELETE /api/candles/cleanup/:symbolId?keep=1000` - Clean up old candles, keep latest N (default 1000)

### Live Prices
- `GET /api/live-prices` - Get all live prices
- `GET /api/live-prices/exchange/:exchange` - Get live prices by exchange
- `GET /api/live-prices/type/:type` - Get live prices by instrument type
- `GET /api/live-prices/:symbolId` - Get live price for symbol
- `POST /api/live-prices` - Create/update live price
- `POST /api/live-prices/bulk` - Bulk update live prices
- `DELETE /api/live-prices/:symbolId` - Delete live price

## Development

### Available Scripts

- `npm start` - Start production server
- `npm run dev` - Start development server with auto-reload
- `npm test` - Run Jest test suite
- `npm run test:watch` - Run tests in watch mode
- `npm run migrate` - Run database migrations
- `npm run migrate:undo` - Revert last migration
- `npm run lint` - Run ESLint
- `npm run lint:fix` - Fix ESLint issues


### Database Migrations

We use Sequelize CLI for database migrations:

```bash
# Create new migration
npx sequelize-cli migration:generate --name your-migration-name

# Run pending migrations
npx sequelize-cli db:migrate

# Revert last migration
npx sequelize-cli db:migrate:undo
```

## Deployment

1. Set `NODE_ENV=production` in your environment
2. Run `npm start` to start the production server
3. Consider using a process manager like PM2 for production deployments