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
• Authentication
• API Reference
• Testing
• Database
• Contributing
• Development Status

---

Installation & Configuration

Prerequisites

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

Detailed Setup

1. Clone the repository:

   git clone https://github.com/dromerosm/stock-monitoring.git
   cd stock-monitoring
   

2. Set up virtual environment (recommended):

   python -m venv .venv
   source .venv/bin/activate  # On Windows: .venv\Scripts\activate
   

3. Install dependencies:

   pip install -r requirements.txt
   

4. Environment Configuration: Create a .env file in the project root with the following variables:

   # 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:

   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:

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:

{
  "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:

curl 'http://localhost:8000/tickers?is_sp500=true&limit=10'

POST /tickers/update 🔒

Performs synchronous ticker data update from Wikipedia sources.

Request Body:

{
  "force_refresh": true  // Optional: bypass cache and force update
}

Example:

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:

{
  "force_refresh": false  // Optional: bypass cache and force update
}

Response:

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

Example:

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:

   source .venv/bin/activate  # if using virtual environment
   python api/index.py
   

2. Run the test suite:

   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:

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:

   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:

   git commit -m 'Add new feature: description'
   

6. Push to your fork:

   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