TECHNICAL.md

# Technical Documentation - Stock Monitoring API

This document provides comprehensive technical information about the Stock Monitoring API, including detailed setup instructions, API reference, testing procedures, and deployment guidelines.

## Table of Contents

- [Installation & Configuration](#installation--configuration)
- [Authentication](#authentication)
- [API Reference](#api-reference)
- [Testing](#testing)
- [Database](#database)
- [Contributing](#contributing)
- [Development Status](#development-status)

---

## Installation & Configuration

### Prerequisites

- Python 3.8 or higher
- MySQL database server
- Virtual environment (recommended)

### Detailed Setup

1. **Clone the repository:**
    ```bash
    git clone https://github.com/dromerosm/stock-monitoring.git
    cd stock-monitoring
    ```

2. **Set up virtual environment (recommended):**
    ```bash
    python -m venv .venv
    source .venv/bin/activate  # On Windows: .venv\Scripts\activate
    ```

3. **Install dependencies:**
    ```bash
    pip install -r requirements.txt
    ```

4. **Environment Configuration:**
    Create a `.env` file in the project root with the following variables:
    ```env
    # Database Configuration
    MYSQL_USER=youruser
    MYSQL_PASSWORD=yourpassword
    MYSQL_HOST=localhost
    MYSQL_PORT=3306
    MYSQL_DB=stockdb
    
    # API Security - Generate a secure API key
    API_KEY=your_secure_api_key_here
    
    # Optional: Server Configuration
    PORT=7860
    ```

5. **Database Setup:**
    - Ensure MySQL server is running
    - Create the database specified in `MYSQL_DB`
    - Tables (`tickers` and `tasks`) will be created automatically on first run

6. **Start the API:**
    ```bash
    python api/index.py
    # Alternative with Uvicorn
    uvicorn api.index:app --host 0.0.0.0 --port 8000
    ```

---

## Authentication

### API Key Security

🔒 The API uses Bearer token authentication for protected endpoints. Include the API key in the Authorization header:

```bash
Authorization: Bearer your_api_key_here
```

### Endpoint Classification

**Protected Endpoints** (require API key):
- `POST /tickers/update` - Manual ticker updates
- `POST /tickers/update-async` - Asynchronous ticker updates
- `GET /tasks` - List all background tasks
- `GET /tasks/{task_id}` - Get specific task details
- `DELETE /tasks/old` - Clean up old tasks

**Public Endpoints** (no authentication required):
- `GET /` - Health check and system status
- `GET /tickers` - Read-only ticker data access

---

## API Reference

### Health Check Endpoint

#### `GET /`

Returns comprehensive system health information including database connectivity, versions, and performance metrics.

**Response Example:**
```json
{
  "status": "healthy",
  "timestamp": "2025-07-19T17:38:26+00:00",
  "versions": {
    "python": "3.12.0",
    "uvicorn": "0.24.0",
    "fastapi": "0.110.0",
    "sqlalchemy": "2.0.30",
    "pandas": "2.2.2"
  },
  "database": {
    "connected": true,
    "tickers_table": true,
    "tasks_table": true
  },
  "db_check_seconds": 0.0123
}
```

### Ticker Endpoints

#### `GET /tickers`

Retrieve ticker data with optional filtering.

**Query Parameters:**
- `is_sp500` (boolean): Filter by S&P 500 membership
- `is_nasdaq` (boolean): Filter by Nasdaq 100 membership  
- `limit` (integer): Maximum number of results

**Example:**
```bash
curl 'http://localhost:8000/tickers?is_sp500=true&limit=10'
```

#### `POST /tickers/update` 🔒

Performs synchronous ticker data update from Wikipedia sources.

**Request Body:**
```json
{
  "force_refresh": true  // Optional: bypass cache and force update
}
```

**Example:**
```bash
curl -X POST 'http://localhost:8000/tickers/update' \
  -H 'Content-Type: application/json' \
  -H 'Authorization: Bearer your_api_key_here' \
  -d '{"force_refresh": true}'
```

#### `POST /tickers/update-async` 🔒

Launches asynchronous background task for ticker updates.

**Request Body:**
```json
{
  "force_refresh": false  // Optional: bypass cache and force update
}
```

**Response:**
```json
{
  "task_id": "uuid-string",
  "status": "pending",
  "message": "Background update task started"
}
```

**Example:**
```bash
curl -X POST 'http://localhost:8000/tickers/update-async' \
  -H 'Content-Type: application/json' \
  -H 'Authorization: Bearer your_api_key_here' \
  -d '{"force_refresh": false}'
```

### Task Management Endpoints

#### `GET /tasks` 🔒

List all background tasks with their current status and results.

#### `GET /tasks/{task_id}` 🔒

Get detailed information about a specific background task.

**Path Parameters:**
- `task_id` (string): UUID of the task

#### `DELETE /tasks/old` 🔒

Remove completed tasks older than 1 hour (3600 seconds) to maintain database performance.

---

## Testing

### Automated Test Suite

The project includes a comprehensive testing framework that validates API functionality, security, and data integrity.

### Test Setup and Execution

1. **Start the API server:**
   ```bash
   source .venv/bin/activate  # if using virtual environment
   python api/index.py
   ```

2. **Run the test suite:**
   ```bash
   cd tests
   chmod +x run_tests.sh  # First time only
   ./run_tests.sh
   ```

### Test Coverage

The test suite validates the following areas:

- ✅ **Authentication Security**: Verifies that protected endpoints properly reject unauthorized requests
- ✅ **Public Access**: Confirms public endpoints function without authentication
- ✅ **SQL Injection Protection**: Tests input sanitization and parameterized queries
- ✅ **Complete Workflow**: End-to-end testing of task creation, monitoring, and cleanup
- ✅ **Error Handling**: Validates proper HTTP status codes and error responses
- ✅ **Data Integrity**: Ensures ticker data accuracy and consistency

### Test Files Structure

- `tests/test_api.py` - Main test script with comprehensive coverage
- `tests/run_tests.sh` - Test runner with prerequisite checks

### Example Test Output

```
🧪 Starting Stock Monitoring API Tests
🔗 Base URL: http://localhost:8000
🔑 API Key: Vsb5Zkujk2...

============================================================
🧪 Health Check (Public Endpoint)
============================================================
✅ GET /
   Expected: 200, Got: 200
   📊 Status: healthy
   🕐 Timestamp: 2025-07-30T15:25:49+02:00
   💾 DB Connected: True

============================================================
🧪 Authentication Tests
============================================================
✅ POST /tickers/update (No Auth)
   Expected: 401/403, Got: 403
✅ GET /tasks (No Auth)
   Expected: 401/403, Got: 403

============================================================
🧪 SQL Injection Tests
============================================================
✅ GET /tickers?limit='; DROP TABLE tickers; --
   Expected: 422, Got: 422
   🔒 SQL injection attempt blocked

🎉 ALL TESTS PASSED! ✅
```

---

## Database

### Architecture

The application uses MySQL with SQLAlchemy ORM for database operations, providing:

- **Async Operations**: Non-blocking database interactions using `aiomysql`
- **Connection Pooling**: Efficient connection management
- **Auto Schema Creation**: Tables created automatically on startup

### Database Schema

#### `tickers` Table
Stores stock ticker information for S&P 500 and Nasdaq 100 indices.

#### `tasks` Table  
Tracks background task execution status and results.

### Database Configuration

Configure database connection via environment variables:
```env
MYSQL_USER=youruser
MYSQL_PASSWORD=yourpassword
MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_DB=stockdb
```

---

## Static Files

The API no longer serves static files. Static file serving functionality has been removed to simplify the deployment.

---

## Contributing

### Development Workflow

1. **Fork the repository**
2. **Create a feature branch:**
   ```bash
   git checkout -b feature/my-new-feature
   ```
3. **Make your changes and add tests**
4. **Run the test suite to ensure everything works**
5. **Commit your changes:**
   ```bash
   git commit -m 'Add new feature: description'
   ```
6. **Push to your fork:**
   ```bash
   git push origin feature/my-new-feature
   ```
7. **Open a pull request**

### Code Standards

- Follow PEP 8 Python style guide
- Include docstrings for all functions and classes
- Add appropriate error handling
- Write tests for new functionality
- Ensure all tests pass before submitting

---

## Development Status

### Recently Completed ✅

- ✅ **Security Implementation** - API key authentication for protected endpoints
- ✅ **SQL Injection Prevention** - Replaced unsafe `text()` operations with parameterized queries
- ✅ **UTC Timezone Standardization** - All timestamps now use UTC for consistency
- ✅ **Enhanced Logging** - Added comprehensive logging in TaskManager for database operations

### Pending Tasks

- **Apple Touch Icons**: Implement missing `/apple-touch-icon-precomposed.png` and `/apple-touch-icon.png` endpoints
- **Enhanced Request Logging**: Add detailed request/response logging for improved debugging and monitoring
- **Rate Limiting**: Implement API rate limiting for production use
- **Docker Support**: Add containerization for easier deployment

### Architecture Improvements Under Consideration

- **Caching Layer**: Redis integration for improved performance
- **Message Queue**: Background task processing with Celery
- **API Versioning**: Implement versioned API endpoints for backward compatibility
- **Metrics Collection**: Prometheus metrics for monitoring and alerting