Dengue Dashboard Backend

A Flask REST API service for the Dengue Dashboard application, providing endpoints for managing epidemiological, demographic, meteorological, and entomological data with user authentication.

Technology Stack
Architecture Overview
Project Structure
Data Flow
Developer Guide
Manzanification
Testing
Database
Migrations
Docker

Technology Stack

Core Framework

Technology	Purpose	Why?
Flask	Web Framework	Lightweight, flexible, excellent for REST APIs, large ecosystem
Python 3.12	Language	Strong data science libraries, type hints, async support
SQLAlchemy 2.0	ORM	Powerful query building, relationship handling, database agnostic
Alembic	Migrations	Database schema versioning, auto-generated migrations

Data Validation & Serialization

Technology	Purpose	Why?
Marshmallow	Schema Validation	Declarative schemas, nested object support, custom validators
marshmallow-sqlalchemy	Model Integration	Auto-generates schemas from SQLAlchemy models

Authentication & Security

Technology	Purpose	Why?
PyJWT	JWT Tokens	Stateless authentication, refresh token support
bcrypt	Password Hashing	Industry-standard, configurable work factor
Flask-CORS	CORS Handling	Cross-origin request configuration

Data Processing

Technology	Purpose	Why?
Pandas	Data Manipulation	Powerful DataFrames for file processing, data transformation
GeoPandas	Geospatial Data	Shapefile handling for manzanification
Shapely	Geometric Operations	Street intersection calculations, buffer zones
openpyxl	Excel Files	Read/write Excel files with formatting

API Documentation

Technology	Purpose	Why?
Flasgger	Swagger/OpenAPI	Auto-generated API docs from docstrings

Testing & Code Quality

Technology	Purpose	Why?
pytest	Testing Framework	Fixtures, parameterization, plugins
pytest-cov	Coverage Reports	Test coverage measurement
Ruff	Linting & Formatting	Fast Python linter, replaces flake8/black/isort

Production

Technology	Purpose	Why?
Waitress	WSGI Server	Production-ready, cross-platform
MariaDB/MySQL	Database	Reliable, well-supported, good for relational data

Architecture Overview

The backend follows a layered architecture pattern combining Flask Blueprints with a repository pattern:

┌─────────────────────────────────────────────────────────────┐
│                         Routes                               │
│            (API endpoints, Swagger docs)                     │
├─────────────────────────────────────────────────────────────┤
│                       Controllers                            │
│      (Request handling, validation, response formatting)     │
├─────────────────────────────────────────────────────────────┤
│                       Repositories                           │
│           (Database operations, query building)              │
├─────────────────────────────────────────────────────────────┤
│                         Schemas                              │
│      (Marshmallow validation & serialization)                │
├─────────────────────────────────────────────────────────────┤
│                         Models                               │
│             (SQLAlchemy ORM definitions)                     │
├─────────────────────────────────────────────────────────────┤
│                        Database                              │
│                   (MariaDB/MySQL)                            │
└─────────────────────────────────────────────────────────────┘

Why This Architecture?

Separation of Concerns: Each layer has a single responsibility
Testability: Repositories can be mocked for unit tests
Maintainability: Changes in one layer don't cascade to others
Flexibility: Easy to swap database or add caching layer

Project Structure

backend/
├── app/
│   ├── app.py                    # Application entry point
│   ├── app_factory.py            # Factory function & configuration
│   ├── database.py               # SQLAlchemy initialization
│   │
│   ├── project/
│   │   ├── controllers/          # Request handlers
│   │   │   ├── infection_controller.py
│   │   │   ├── demography_controller.py
│   │   │   ├── meteorology_controller.py
│   │   │   ├── entomology_controller.py
│   │   │   ├── location_controller.py
│   │   │   ├── user_controller.py
│   │   │   ├── authentication_controller.py
│   │   │   ├── preprocess_controller.py    # Manzanification logic
│   │   │   └── upload_file_controller.py
│   │   │
│   │   ├── repositories/         # Database access layer
│   │   │   ├── infection_repository.py
│   │   │   ├── demography_repository.py
│   │   │   ├── user_repository.py
│   │   │   ├── area_repository.py
│   │   │   ├── manzana_repository.py
│   │   │   └── ...
│   │   │
│   │   ├── models/               # SQLAlchemy ORM models
│   │   │   ├── infection_model.py
│   │   │   ├── demography_model.py
│   │   │   ├── user_model.py
│   │   │   ├── manzana_model.py
│   │   │   ├── base_model.py     # Base model with common fields
│   │   │   └── enums.py          # Shared enumerations
│   │   │
│   │   ├── schemas/              # Marshmallow validation schemas
│   │   │   ├── infection_schema.py
│   │   │   ├── demography_schema.py
│   │   │   ├── user_schema.py
│   │   │   └── ...
│   │   │
│   │   ├── routes/               # Flask Blueprint routes
│   │   │   ├── infection.py
│   │   │   ├── demography.py
│   │   │   ├── authentication.py
│   │   │   ├── preprocess.py     # Manzanification endpoints
│   │   │   └── ...
│   │   │
│   │   ├── dto/                  # Data Transfer Objects (legacy)
│   │   │   └── ...               # Being migrated to schemas
│   │   │
│   │   ├── utils_files/          # Utility functions
│   │   │   ├── decorators.py     # @role_required, @handle_api_errors
│   │   │   ├── api_exceptions.py # Custom exception classes
│   │   │   ├── api_responses.py  # Standardized responses
│   │   │   ├── authentication.py # JWT helpers
│   │   │   ├── data_validator.py # Input validation helpers
│   │   │   ├── data_cleaner.py   # Data sanitization
│   │   │   ├── processors/       # File format processors
│   │   │   │   ├── base_processor.py
│   │   │   │   ├── excel_processor.py
│   │   │   │   ├── csv_processor.py
│   │   │   │   └── json_processor.py
│   │   │   └── ...
│   │   │
│   │   ├── seeders/              # Database seeding
│   │   │   └── seed_all.py
│   │   │
│   │   ├── config/               # Configuration files
│   │   ├── assets/               # Static assets (shapefiles)
│   │   └── Globals/              # Global constants
│   │
│   ├── migrations/               # Alembic migrations
│   │   ├── versions/             # Migration files
│   │   └── alembic.ini
│   │
│   ├── tests/                    # Test suite
│   │   ├── unit/                 # Unit tests
│   │   │   ├── test_infection.py
│   │   │   ├── test_locations.py
│   │   │   └── ...
│   │   └── integration/          # Integration tests
│   │       └── ...
│   │
│   ├── doc/                      # Development documentation
│   ├── temp_preprocess_files/    # Temporary manzanification output
│   └── temp_error_files/         # Error file storage
│
├── Dockerfile
├── entrypoint.sh
├── requirements.in               # Direct dependencies
└── requirements.txt              # Pinned dependencies (pip-compile)

Data Flow

Request Lifecycle

1. Client Request
       ↓
2. Route (Blueprint)
   - URL matching
   - Swagger documentation
       ↓
3. Controller
   - Parse request parameters
   - Call decorators (@role_required, @handle_api_errors)
   - Coordinate business logic
       ↓
4. Repository
   - Build SQLAlchemy queries
   - Execute database operations
   - Handle transactions
       ↓
5. Schema (if needed)
   - Validate input data
   - Transform field names (Excel → DB)
   - Serialize output
       ↓
6. Response
   - JSON formatting
   - Error handling

File Upload Flow

1. File Upload Request
       ↓
2. Upload Controller
   - File validation
   - Format detection
       ↓
3. Processor (Excel/CSV/JSON)
   - Parse file content
   - Extract rows
       ↓
4. Schema Validation
   - Field mapping
   - Data type conversion
   - Constraint validation
       ↓
5. Repository
   - Batch insert/update
   - Transaction handling
       ↓
6. Response
   - Success count
   - Error rows (if any)

Developer Guide

Adding a New Feature

Follow these steps when implementing a new feature:

Define the Model (project/models/)
Create Migration (Alembic)
Add Schema (project/schemas/) for validation
Create Repository (project/repositories/) for data access
Build Controller (project/controllers/) for business logic
Define Routes (project/routes/) with Swagger docs
Register Blueprint in app_factory.py
Write Tests (tests/unit/ and tests/integration/)

Adding a New Endpoint

1. Create or update the Route (`project/routes/my_feature.py`):

python

from flask import Blueprint

from project.controllers.my_feature_controller import MyFeatureController
from project.repositories.my_feature_repository import MyFeatureRepository

# Create Blueprint
bp = Blueprint("my_feature", __name__, url_prefix="/api/my-feature")

# Initialize controller with repository (dependency injection)
controller = MyFeatureController(MyFeatureRepository())

# Define routes
bp.route("/", methods=["GET"])(controller.get_all)
bp.route("/<int:id>", methods=["GET"])(controller.get_by_id)
bp.route("/", methods=["POST"])(controller.create)
bp.route("/<int:id>", methods=["PUT"])(controller.update)
bp.route("/<int:id>", methods=["DELETE"])(controller.delete)

2. Create the Controller (`project/controllers/my_feature_controller.py`):

python

import logging
from flask import jsonify, request
from project.utils_files.decorators import handle_api_errors, role_required
from project.utils_files.api_exceptions import AppException

logger = logging.getLogger(__name__)

class MyFeatureController:
    """Controller for MyFeature endpoints."""

    def __init__(self, repository):
        self.repository = repository

    @handle_api_errors()
    @role_required(["User", "Admin", "Data Engineer"])
    def get_all(self):
        """Get all records
        ---
        tags:
          - MyFeature
        summary: Get all my feature records
        security:
          - Bearer: []
        responses:
          200:
            description: List of records
        """
        offset = request.args.get("offset", 0, type=int)
        limit = request.args.get("limit", 10, type=int)
        
        results = self.repository.get_all(offset=offset, limit=limit)
        return jsonify(results), 200

    @handle_api_errors()
    @role_required(["Admin", "Data Engineer"])
    def create(self):
        """Create a new record
        ---
        tags:
          - MyFeature
        summary: Create record
        security:
          - Bearer: []
        """
        data = request.get_json()
        
        if not data:
            raise AppException(
                translation_key="invalid_request_body",
                message="Request body is required",
                type="ValidationError"
            )
        
        result = self.repository.create(data)
        return jsonify(result), 201

3. Create the Repository (`project/repositories/my_feature_repository.py`):

python

import logging
from sqlalchemy import select
from sqlalchemy.exc import SQLAlchemyError
from database import db
from project.models.my_feature_model import MyFeature
from project.utils_files.api_exceptions import AppException

logger = logging.getLogger(__name__)

class MyFeatureRepository:
    """Repository for MyFeature database operations."""

    @staticmethod
    def get_all(offset: int = 0, limit: int = 10) -> list:
        """Get all records with pagination."""
        try:
            stmt = select(MyFeature).offset(offset).limit(limit)
            results = db.session.scalars(stmt).all()
            return [item.to_dict() for item in results]
        except SQLAlchemyError as e:
            logger.error(f"Database error: {e}")
            db.session.rollback()
            raise AppException(
                translation_key="database_error",
                message="Failed to fetch records",
                type="DatabaseError"
            )

    @staticmethod
    def create(data: dict) -> dict:
        """Create a new record."""
        try:
            record = MyFeature(**data)
            db.session.add(record)
            db.session.commit()
            return record.to_dict()
        except SQLAlchemyError as e:
            db.session.rollback()
            raise AppException(
                translation_key="create_failed",
                message=f"Failed to create record: {e}",
                type="DatabaseError"
            )

4. Register the Blueprint (`app_factory.py`):

python

# In create_app function, add:
from project.routes.my_feature import bp as my_feature_bp
app.register_blueprint(my_feature_bp)

Adding a New Database Model

1. Create the Model (`project/models/my_feature_model.py`):

python

from sqlalchemy import Column, Integer, String, ForeignKey, Date
from sqlalchemy.orm import relationship
from database import db
from .base_model import BaseModel

class MyFeature(BaseModel):
    """Model representing my feature data."""
    
    __tablename__ = "my_features"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100), nullable=False)
    description = Column(String(500))
    date_created = Column(Date, nullable=False)
    
    # Foreign key example
    area_id = Column(Integer, ForeignKey("areas.id"))
    area = relationship("Area", back_populates="my_features")

    def to_dict(self):
        """Convert model to dictionary."""
        return {
            "id": self.id,
            "name": self.name,
            "description": self.description,
            "date_created": self.date_created.isoformat() if self.date_created else None,
            "area_id": self.area_id,
        }

2. Generate Migration:

bash

docker exec -it <backend-container> bash
cd /app
alembic -c migrations/alembic.ini revision --autogenerate -m "add my_features table"
alembic -c migrations/alembic.ini upgrade head

3. Create Schema for Validation (`project/schemas/my_feature_schema.py`):

python

from marshmallow import fields, validates, ValidationError
from marshmallow_sqlalchemy import SQLAlchemyAutoSchema
from project.models.my_feature_model import MyFeature

class MyFeatureSchema(SQLAlchemyAutoSchema):
    """Schema for validating MyFeature data."""
    
    class Meta:
        model = MyFeature
        load_instance = True
        include_fk = True

    name = fields.String(required=True)
    description = fields.String(allow_none=True)
    date_created = fields.Date(required=True)

    @validates("name")
    def validate_name(self, value):
        if len(value) < 2:
            raise ValidationError("Name must be at least 2 characters")

Working with File Uploads

The backend supports Excel, CSV, and JSON file uploads with a processor pattern:

File Processor Architecture

BaseProcessor (abstract)
    ├── ExcelProcessor
    ├── CSVProcessor
    ├── JSONProcessor
    └── TXTProcessor

Adding Support for a New Data Type Upload

Create Schema with field mappings from file columns to database columns
Add Processor Logic if special file handling is needed
Create Controller Method to handle the upload
Add Route with appropriate role requirements

Example schema field mapping (from infection_schema.py):

python

FIELD_MAPPING = {
    "no": "study_id",                    # Excel column → DB column
    "edad": "agegroup_id",
    "sexo": "sex",
    "manzana": "manzana_id",
    "fps": "fps_date",
    # ... more mappings
}

Manzanification

Manzanification is the process of converting street addresses to manzana (block) identifiers for epidemiological mapping.

What is a Manzana?

A manzana (Spanish for "block") is the smallest geographic unit used in the Cuban healthcare system for patient location tracking. It represents a physical city block and enables:

Precise epidemiological mapping without storing exact addresses
Privacy-preserving location data
Aggregation for health area and municipal statistics

How It Works

The manzanification algorithm uses geographic intersection of street segments:

Input Address:
  - Main Street: "Calle 37"
  - Cross Streets: "Avenida 54 y Avenida 56"
  - House Number: "5412"

Processing Steps:
1. Normalize street names (C-37 → Calle 37)
2. Find street geometries from shapefile
3. Calculate intersection points
4. Extract street segment between cross streets
5. Determine side of street (odd/even house numbers)
6. Find manzana polygon that contains the segment
7. Return manzana identifier

Architecture

┌─────────────────────────────────────────────────────────────┐
│                    PreprocessController                      │
│              (Handles HTTP request/response)                 │
├─────────────────────────────────────────────────────────────┤
│                    MinimalPredictor                          │
│            (Core manzanification algorithm)                  │
├─────────────────────────────────────────────────────────────┤
│                      GeoPandas                               │
│            (Shapefile loading & queries)                     │
├─────────────────┬───────────────────────────────────────────┤
│  Streets.shp    │           Manzanas.shp                     │
│  (Street        │           (Block polygons                  │
│   network)      │            with IDs)                       │
└─────────────────┴───────────────────────────────────────────┘

Key Components

Component	Location	Purpose
`PreprocessController`	`controllers/preprocess_controller.py`	HTTP endpoint handling
`MinimalPredictor`	Embedded in controller	Geospatial calculations
`Streets_municipality.shp`	`assets/Streets_municipality/`	Street network geometry
`manzanero_AS_*.shp`	`assets/manzanero_AS_*/`	Manzana polygon boundaries

Algorithm Details

Street Name Normalization

python

# Input variations → Normalized output
"C-37"     → "Calle 37"
"A-54"     → "Avenida 54"
"calle 37" → "Calle 37"

Cross Street Parsing

Supports multiple separators:

y / Y (and)
, / ; (comma/semicolon)
/ / - (slash/dash)

Side Determination

python

# Odd house numbers → Left side of street
# Even house numbers → Right side of street
house_number = 5412  # Even → right side offset

API Endpoint

POST /api/preprocess/addresses
Authorization: Bearer <token>
Role Required: Admin, Data Engineer

Request:
  - multipart/form-data
  - file: Excel file (.xlsx, .xls)

Expected Excel Columns:
  - CALLE O AVENIDA (main street)
  - ENTRE CALLE O AVENIDAS (cross streets)
  - NUMERO DE VIVIENDA (house number)

Response:
  - Excel file with MANZANA column added
  - Failed rows (if any) highlighted in red

Error Handling

Rows that cannot be manzanified are:

Marked with None in the MANZANA column
Highlighted with red background in output Excel
Common failure reasons:
- Street not found in shapefile
- Only one cross street provided
- No intersection found
- Address outside mapped area

Configuration

Shapefiles are located in the assets/ directory:

project/assets/
├── Streets_municipality/
│   └── Streets_muncipality.shp    # Street network
└── manzanero_AS_1a8_19112024/
    └── manzanero_AS_1a8_19112024.shp  # Manzana polygons

Note: The predictor uses lazy initialization - shapefiles are only loaded on first use to avoid slow startup times.

Extending Manzanification

To support additional address formats:

Update normalize_street_name() for new abbreviations
Add separators to cross-street parsing
Update column detection keywords in _find_column()

Testing

Running Tests

bash

# Enter container
docker exec -it <backend-container> bash

# Run all tests
pytest

# Run with coverage
pytest --cov=project --cov-report=html

# Run specific test file
pytest tests/unit/test_infection.py

# Run specific test
pytest tests/unit/test_infection.py::test_get_infections

Test Structure

tests/
├── unit/                 # Unit tests (mocked dependencies)
│   ├── test_infection.py
│   ├── test_locations.py
│   └── ...
└── integration/          # Integration tests (real database)
    ├── test_age_group.py
    └── ...

Writing Tests

python

import pytest
from unittest.mock import Mock, patch

class TestInfectionController:
    """Tests for InfectionController."""

    def test_get_infections_returns_list(self, client, auth_headers):
        """Test that get_infections returns a list."""
        response = client.get(
            "/api/infection/",
            headers=auth_headers
        )
        assert response.status_code == 200
        assert isinstance(response.json, list)

    @patch("project.repositories.infection_repository.InfectionRepository.get_infections")
    def test_get_infections_with_filters(self, mock_get, client, auth_headers):
        """Test filtering infections by date range."""
        mock_get.return_value = [{"id": 1, "fps_date": "2024-01-01"}]
        
        response = client.get(
            "/api/infection/?start_date=01-01-2024&end_date=31-12-2024",
            headers=auth_headers
        )
        
        assert response.status_code == 200
        mock_get.assert_called_once()

Database

Database Structure

This database is designed to store and manage epidemiological data for dengue virus surveillance in Cienfuegos, Cuba. It supports a dashboard system that visualizes dengue infections, demographic information, meteorological data, and entomological surveillance data.

Administrative Levels (Governmental and Geographic Divisions)

`municipios` (Municipalities)

The primary administrative division in Cuba. A province is divided into multiple municipalities, each with its own local government. In this project, we focus on Cienfuegos municipality.

Level: Highest administrative division tracked in this system
Example: Cienfuegos (municipality within Cienfuegos province)
Purpose: Main geographic boundary for data aggregation

Column	Type	Constraints	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
`name`	VARCHAR(100)	NOT NULL	Municipality name (e.g., "Cienfuegos")

`consego_populars` (Popular Councils)

An intermediate administrative subdivision within municipalities. These are community-based governance structures unique to Cuba, created to facilitate local participation in government decisions. For our project, we connect CP's to municipalities through health areas. This is purely a geographic connection to be able to aggregate data at the CP level.

Level: Sub-municipal administrative unit
Typical Size: 2,000-5,000 inhabitants in urban areas; larger in rural areas
Purpose: Community-level administrative organization
Note: Multiple popular councils exist within a single municipality

Column	Type	Constraints	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
`name`	VARCHAR(100)	NOT NULL	Popular council name
`areas_id`	INT	FOREIGN KEY → `areas.id`	Reference to health area

Medical/Healthcare Levels

Cuba's healthcare system operates independently from administrative divisions, though there is geographical overlap.

`areas` (Área de Salud, Health Areas)

The largest medical subdivision within a municipality. Health areas are designated with Roman numerals (I through VIII).

Level: Primary healthcare organizational unit
Designation: Roman numerals (e.g., Área I, Área II)
Function: Coordinates all healthcare facilities within its geographic boundary
Contains: Multiple CMFs (family medical offices)
Note: A single municipality may contain several health areas

Column	Type	Constraints	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
`code`	VARCHAR(5)	NOT NULL	Area code (I through VIII)
`municipios_id`	INT	FOREIGN KEY → `municipios.id`	Reference to municipality

`cmf` (Family Medical Offices)

The cornerstone of Cuba's primary healthcare system. Each CMF serves a specific neighborhood or community.

Level: Neighborhood-level healthcare unit
Typical Coverage: 120-150 families (~600-800 individuals)
Staffing: One family doctor and one nurse
Function: First point of contact for medical care; preventive and community medicine
Unique Identifier: cmf_unico - a standardized national code

Column	Type	Constraints	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
`cmf_unico`	INT	NOT NULL	Unique CMF identifier code
`areas_id`	INT	FOREIGN KEY → `areas.id`	Reference to health area

`manzanas` (Blocks)

The smallest geographic unit for patient location tracking. Represents a physical city block or housing block.

Level: Micro-geographic unit (smallest tracked)
Purpose: Precise patient location for epidemiological mapping
Relation: Multiple blocks belong to one CMF
Privacy: Provides granularity without storing exact addresses

Column	Type	Constraints	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
`cmf_id`	INT	FOREIGN KEY → `cmf.id`	Reference to CMF
`consengo_populars_id`	INT	FOREIGN KEY → `consengo_populars.id`	Reference to consengo popular
`name`	VARCHAR(45)	NOT NULL	Block name/identifier

`codigo_dimob` (DIMOB Codes)

Special entomological surveillance codes linked to blocks.

Purpose: Links mosquito surveillance data to specific geographic locations
Function: Enables targeted interventions and easily identify blocks
Usage: Tracks larval and adult mosquito populations by location

Column	Type	Constraints	Description
`codigo_dimob`	VARCHAR(20)	PRIMARY KEY	DIMOB surveillance code
`blocks_id`	INT	FOREIGN KEY → `manzanas.id`	Reference to manzana (block)

Relationship Between Administrative and Healthcare Systems

While both hierarchies exist within the same geographic space, they serve different purposes:

Administrative (Government)	Healthcare (Medical)
Municipios → Consejo Popular	Municipios → Área de Salud → CMF → Manzana
Used for: civic governance	Used for: patient care and epidemiology

Important: A single manzana (block) belongs to both:

One CMF (healthcare)
One Consejo Popular (administrative)

This dual hierarchy allows the database to support both medical epidemiology and administrative reporting requirements.

`infections` (Infection Cases)

Core table storing individual dengue infection cases.

Column	Type	Constraints	Description
`id`	INT UNSIGNED	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
`guid`	VARCHAR(36)	UNIQUE, NOT NULL	Globally unique identifier for patient
`date_positive`	DATE	NOT NULL	Date of positive test result
`date_diagnosis`	DATE	NOT NULL	Date of diagnosis (first medical consult)
`date_first_sym`	DATE	NOT NULL	Date of first symptoms (FPS)
`sex`	ENUM('F','M','O')	NOT NULL	Patient sex
`agegroup_id`	INT	FOREIGN KEY → `agegroups.id`	Reference to age group
`areas_id`	INT	FOREIGN KEY → `areas.id`	Reference to health area
`manzana_id`	INT	FOREIGN KEY → `manzanas.id`	Reference to block
`cmf_id`	INT	FOREIGN KEY → `cmf.id`	Reference to CMF

Data Source Mapping (from Template)

Template Column	Maps To
EDAD (Age)	`agegroup_id` (grouped)
SEXO (Sex)	`sex`
FPS (First Symptom Date)	`date_first_sym`
FECHA 1ra CONSULTA	`date_diagnosis`
IGM Test Date	`date_positive`
MANZANA (Block)	`manzana_id`
CMF	`cmf_id`
AREA	`areas_id`

`agegroups` (Age Groups)

Age stratification for privacy and analysis.

Column	Type	Constraints	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
`min_age`	VARCHAR(45)	NOT NULL	Minimum age in group
`max_age`	VARCHAR(45)	NOT NULL	Maximum age in group

`deaths` (Mortality Data)

Tracks dengue-related deaths by location and time.

Column	Type	Constraints	Description
`id`	INT	PRIMARY KEY	Unique identifier
`month`	INT	NOT NULL	Month of death (1-12)
`year`	YEAR	NOT NULL	Year of death
`amount_of_deaths`	INT	NOT NULL	Number of deaths
`locations_id`	INT UNSIGNED		Reference to location

`meteorologies` (Meteorological Data)

Climate data that influences mosquito populations and dengue transmission.

Column	Type	Constraints	Description
`id`	INT UNSIGNED	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
`date`	DATE	NOT NULL	Date of measurement
`temp_min`	DOUBLE	NOT NULL	Minimum temperature (°C)
`temp_med`	DOUBLE	NOT NULL	Mean temperature (°C)
`temp_max`	DOUBLE	NOT NULL	Maximum temperature (°C)
`relative_humidity_min`	DOUBLE	NOT NULL	Minimum relative humidity (%)
`relative_humidity_med`	DOUBLE	NOT NULL	Mean relative humidity (%)
`relative_humidity_max`	DOUBLE	NOT NULL	Maximum relative humidity (%)
`precipitation`	DOUBLE	NOT NULL	Precipitation (mm)
`municipios_id`	INT	FOREIGN KEY → `municipios.id`	Reference to municipality

`entomologies` (Entomological Surveillance)

Mosquito larvae and adult surveillance data by location.

Column	Type	Constraints	Description
`id`	INT UNSIGNED	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
`codigo_dimob`	VARCHAR(20)	FOREIGN KEY → `codigo_dimob.codigo_dimob`	Reference to DIMOB code
`year`	YEAR	NOT NULL	Year of surveillance
`month`	INT	NOT NULL	Month of surveillance (1-12)
`larvas_i`	INT	DEFAULT 0	Count of larvae stage I
`larvas_ii`	INT	DEFAULT 0	Count of larvae stage II
`larvas_iii`	INT	DEFAULT 0	Count of larvae stage III
`larvas_iv`	INT	DEFAULT 0	Count of larvae stage IV
`pupa`	INT	DEFAULT 0	Count of pupae
`adultos_h`	INT	DEFAULT 0	Count of adult males
`adultos_m`	INT	DEFAULT 0	Count of adult females
`location_id`	INT UNSIGNED		Generic location reference

`demographies` (Demographic Data)

Population counts by location and year for rate calculations.

Column	Type	Constraints	Description
`id`	INT UNSIGNED	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
`year`	YEAR	NOT NULL	Census year
`population_count`	INT	NOT NULL	Population count
`location_id`	INT UNSIGNED		Generic location reference
`areas_id`	INT	FOREIGN KEY → `areas.id`	Reference to health area
`consego_populars_id`	INT	FOREIGN KEY → `consego_populars.id`	Reference to popular council

`users` (System Users)

Users with access to the dashboard system.

Column	Type	Constraints	Description
`id`	VARCHAR(36)	PRIMARY KEY	Unique user identifier (UUID)
`username`	VARCHAR(45)	NOT NULL	Login username
`password`	VARCHAR(60)	NOT NULL	Hashed password
`role`	ENUM	NOT NULL	'User', 'Admin', or 'Data Engineer'
`email`	VARCHAR(60)	NOT NULL	User email address
`department`	VARCHAR(45)	NOT NULL	User's department
`birthdate`	DATE	NOT NULL	User's birth date
`approved`	TINYINT	NOT NULL	Account approval status
`expiration_date`	DATE	NOT NULL	Account expiration date
`invite_link_guid`	VARCHAR(45)	FOREIGN KEY → `invite_links.guid`	Reference to invitation

`invite_links` (Invitation Links)

Manages user invitation system.

Column	Type	Constraints	Description
`guid`	VARCHAR(45)	PRIMARY KEY	Unique invitation identifier
`expires_at`	TIMESTAMP	NOT NULL	Expiration timestamp
`clicked_amount`	INT	NOT NULL	Number of times clicked
`invite_code`	VARCHAR(70)	NOT NULL	Invitation code
`name`	VARCHAR(45)	NOT NULL	Invitation name/description
`invited_by_id`	VARCHAR(36)	FOREIGN KEY → `users.id`	Reference to inviting user

`refresh_tokens` (Authentication Tokens)

Manages JWT refresh tokens for authentication.

Column	Type	Constraints	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	Unique identifier
`token`	VARCHAR(45)	NOT NULL	Refresh token value
`expires_at`	DATETIME	NOT NULL	Token expiration
`created_at`	DATETIME	NOT NULL	Token creation time
`created_by_ip`	VARCHAR(45)		IP address of creation
`revoked_at`	DATETIME		Token revocation time
`replaced_by_token`	VARCHAR(45)		Replacement token
`user_id`	VARCHAR(36)	FOREIGN KEY → `users.id`	Reference to user

Database Hierarchies

The database implements a dual hierarchical structure:

Administrative Hierarchy

Municipios (Municipality)
    └── Consego Populars (Popular Councils)

Medical/Health Hierarchy

Municipios (Municipality)
    └── Areas (Health Areas: I-VIII)
        └── CMF (Family Medical Offices)
            └── Manzanas (Blocks)
                └── Codigo DIMOB (Surveillance Codes)

Key Relationships

Infection Case Location: Each infection can be linked to multiple geographic levels (area, CMF, block) allowing flexible spatial analysis
Entomological Surveillance: Linked to specific blocks via DIMOB codes for targeted vector control
Meteorological Data: Stored at municipality level, can be joined with infections for climate-disease correlation analysis
Demographics: Provides population denominators for calculating incidence rates at various geographic levels
User Access: Role-based access control with invitation system and token-based authentication

Privacy Considerations

✅ Patient names (Column C) are not stored
✅ Detailed addresses are not stored in dashboard
✅ Age is aggregated into age groups
✅ Individual cases use GUIDs rather than personal identifiers

Migrations

To change database table schema, make the change in the model you want to change (inside app/project/models/) and then run:

bash

docker exec -it <backend-container> bash
cd /app
alembic -c migrations/alembic.ini revision --autogenerate -m "describe migration"
alembic -c migrations/alembic.ini upgrade head  # Apply the migration

To rollback a migration:

bash

alembic -c migrations/alembic.ini downgrade -1

Then delete the migration file from app/migrations/versions/.

If changes are too drastic, you can reset the database:

bash

docker exec -it <mariadb-container> mariadb -u root -p
DROP DATABASE dev_db;
CREATE DATABASE dev_db;
EXIT;

docker exec -it <backend-container> bash
alembic -c migrations/alembic.ini upgrade head

Docker

This project uses Docker. To run commands inside the running application:

bash

docker-compose exec -u 1000:1000 dengue-backend bash

Add Data to the Database

To create all locations, use the Excel file in folder app/doc and upload it to the route /add_locations with sheet_name in the body.

Then login with credentials from .env or defaults:

Email: dev@dev.com
Password: Azerty123

API Documentation

API documentation is auto-generated using Flasgger (Swagger/OpenAPI).

Access the interactive API docs at:

Development: http://localhost:5000/apidocs/
Staging: Available at /apidocs/

Note: Swagger is disabled in production for security.

Dengue Dashboard Backend ​

Table of Contents ​

Technology Stack ​

Core Framework ​

Data Validation & Serialization ​

Authentication & Security ​

Data Processing ​

API Documentation ​

Testing & Code Quality ​

Production ​

Architecture Overview ​

Why This Architecture? ​

Project Structure ​

Data Flow ​

Request Lifecycle ​

File Upload Flow ​

Developer Guide ​

Adding a New Feature ​

Adding a New Endpoint ​

1. Create or update the Route (project/routes/my_feature.py): ​

2. Create the Controller (project/controllers/my_feature_controller.py): ​

3. Create the Repository (project/repositories/my_feature_repository.py): ​

4. Register the Blueprint (app_factory.py): ​

Adding a New Database Model ​

1. Create the Model (project/models/my_feature_model.py): ​

2. Generate Migration: ​

3. Create Schema for Validation (project/schemas/my_feature_schema.py): ​

Working with File Uploads ​

File Processor Architecture ​

Adding Support for a New Data Type Upload ​

Manzanification ​

What is a Manzana? ​

How It Works ​

Architecture ​

Key Components ​

Algorithm Details ​

Street Name Normalization ​

Cross Street Parsing ​

Side Determination ​

API Endpoint ​

Error Handling ​

Configuration ​

Extending Manzanification ​

Testing ​

Running Tests ​

Test Structure ​

Writing Tests ​

Database ​

Database Structure ​

Administrative Levels (Governmental and Geographic Divisions) ​

municipios (Municipalities) ​

consego_populars (Popular Councils) ​

Medical/Healthcare Levels ​

areas (Área de Salud, Health Areas) ​

cmf (Family Medical Offices) ​

manzanas (Blocks) ​

codigo_dimob (DIMOB Codes) ​

Relationship Between Administrative and Healthcare Systems ​

infections (Infection Cases) ​

Data Source Mapping (from Template) ​

agegroups (Age Groups) ​

deaths (Mortality Data) ​

meteorologies (Meteorological Data) ​

entomologies (Entomological Surveillance) ​

demographies (Demographic Data) ​

users (System Users) ​

invite_links (Invitation Links) ​

refresh_tokens (Authentication Tokens) ​

Database Hierarchies ​

Administrative Hierarchy ​

Medical/Health Hierarchy ​

Key Relationships ​

Privacy Considerations ​

Migrations ​

Docker ​

Add Data to the Database ​

API Documentation ​

Dengue Dashboard Backend

Table of Contents

Technology Stack

Core Framework

Data Validation & Serialization

Authentication & Security

Data Processing

API Documentation

Testing & Code Quality

Production

Architecture Overview

Why This Architecture?

Project Structure

Data Flow

Request Lifecycle

File Upload Flow

Developer Guide

Adding a New Feature

Adding a New Endpoint

1. Create or update the Route (`project/routes/my_feature.py`):

2. Create the Controller (`project/controllers/my_feature_controller.py`):

3. Create the Repository (`project/repositories/my_feature_repository.py`):

4. Register the Blueprint (`app_factory.py`):

Adding a New Database Model

1. Create the Model (`project/models/my_feature_model.py`):

2. Generate Migration:

3. Create Schema for Validation (`project/schemas/my_feature_schema.py`):

Working with File Uploads

File Processor Architecture

Adding Support for a New Data Type Upload

Manzanification

What is a Manzana?

How It Works

Architecture

Key Components

Algorithm Details

Street Name Normalization

Cross Street Parsing

Side Determination

API Endpoint

Error Handling

Configuration

Extending Manzanification

Testing

Running Tests

Test Structure

Writing Tests

Database

Database Structure

Administrative Levels (Governmental and Geographic Divisions)

`municipios` (Municipalities)

`consego_populars` (Popular Councils)

Medical/Healthcare Levels

`areas` (Área de Salud, Health Areas)

`cmf` (Family Medical Offices)

`manzanas` (Blocks)

`codigo_dimob` (DIMOB Codes)

Relationship Between Administrative and Healthcare Systems

`infections` (Infection Cases)

Data Source Mapping (from Template)

`agegroups` (Age Groups)

`deaths` (Mortality Data)

`meteorologies` (Meteorological Data)

`entomologies` (Entomological Surveillance)

`demographies` (Demographic Data)

`users` (System Users)

`invite_links` (Invitation Links)

`refresh_tokens` (Authentication Tokens)

Database Hierarchies

Administrative Hierarchy

Medical/Health Hierarchy

Key Relationships

Privacy Considerations

Migrations

Docker

Add Data to the Database

API Documentation