Project 2 - Restaurant API and OLAP Layer

Course: Databases II
Team Members: Daniel Alemán, Luis Meza

EN | ES

Click the image to watch the explainer video. (Note: The video is in Spanish).

📑 Table of Contents

1. Project Description
2. Architecture
   • Logical Architecture
   • Project Structure
3. Main Components
4. Installation
5. Build and Deployment
6. Generating Synthetic Data
7. ETL, Airflow and OLAP Layer
8. Testing
9. CI/CD Pipeline
10. Interface Access
11. Environment Reset

---

📝 Project Description

This project implements a complete restaurant management system with a microservices architecture. It allows managing restaurants, menus, products, reservations, and orders through a set of RESTful APIs.

The system is designed with high availability, scalability, and performance as priorities, using modern technologies such as load balancing, database sharding, distributed caching, and optimized searches. It also implements DAGs via Airflow to perform ETL and automatically reindex products in Elastic. Additionally, it includes functionality to visualize significant analytics data.

---

🏗️ Architecture

📄 View Detailed Architecture Document (PDF)

(Note: The document is in Spanish)

Logical Architecture

The project follows a microservices architecture with the following key components:

1. API Microservices:

   • Main API for CRUD operations of restaurants, menus, products, reservations, and orders.
   • Authentication service for user and JWT token management.
   • Optimized search service with Elasticsearch.
   • Indexing and routing operations service within a graph using Neo4J.

2. Load Balancing:

   • Nginx as a reverse proxy and load balancer to distribute requests among instances.

3. Persistence:

   • MongoDB: Main storage with sharding and replication for high availability.
   • PostgreSQL: Configurable alternative storage.
   • Elasticsearch: Search index for optimized queries.
   • HiveDB: Storage as a Data Warehouse.

4. Cache:

   • Redis as a distributed cache store to improve performance.

5. CI/CD:

   • Automated pipeline for testing, building, and deployment.

📂 View Project Structure

proyecto2-bases2/
├── airflow/                # Airflow service for automated DAGs
├── analytics_service/      # Analytics dashboards service
├── api/                    # Main API
├── auth_service/           # Authentication service
├── drivers/                # Drivers required for Hive metadata
├── etl_service/            # Specific purpose service for full ETL
├── graph_service/          # Indexing and routing service with Neo4j
├── pruebas/                # Testing and data generation scripts
├── search_service/         # Search service
├── spark_analytics/        # Basic query script with Spark and Hive
├── docker-compose.yml      # Container configuration
├── hive_warehouse_init.sql # Warehouse initialization via HiveQL
├── init_cluster.sh         # Script to initialize MongoDB cluster
├── init_hive_warehouse.sh  # Script to initialize Hive
├── init.sql                # Initialization of Postgres tables for services
├── set_config.sh           # Script to configure the environment
├── nginx.conf              # Load balancer configuration
└── README.md               # Documentation

Each service follows an MVC (Model-Controller) structure with a clear separation of responsibilities:

servicio/
├── src/
│   ├── config/             # Configuration
│   ├── controllers/        # Controllers
│   ├── dao/                # Data Access Objects
│   ├── db/                 # Database connections
│   ├── middlewares/        # Middlewares
│   ├── models/             # Data models
│   ├── routes/             # Route definitions
│   └── app.js              # Main application
├── swagger/                # API Documentation
├── tests/                  # Tests
│   ├── integration/        # Integration tests
│   ├── unit/               # Unit tests
│   └── utils/              # Test utilities
└── server.js               # Entry point

---

🧩 Main Components

Main API

Handles all CRUD operations related to restaurants, menus, products, reservations, orders, and delivery drivers.

• RESTful implementation with Express.js and Swagger documentation.
• Horizontal scalability with multiple instances (api1, api2).

Authentication Service

Manages users, security, login, registration, roles, and JWT verification.

• JWT-based authentication and secure password hashing.
• High availability (auth_service1, auth_service2).

Search Service

Provides independent optimized search functionality.

• Free-text, category searches, and typo tolerance using Elasticsearch.
• Real-time updates and automatic indexing.

Graph Service

Resolves complex queries using graph structures (Neo4j).

• Calculation of optimal delivery routes and automatic delivery driver assignment.
• Co-purchase analysis and identification of influential users.

ETL and Data Warehouse Service

Extracts, transforms, and loads (ETL) data for OLAP analysis.

• Automated extraction from MongoDB/PostgreSQL with Python and processing with Apache Spark.
• Load into Apache Hive optimized for columnar storage.

Apache Airflow Orchestration

Automates programmable workflows.

• ETL DAG executed every 6 hours to keep the Data Warehouse updated.
• Automatic reindexing DAG for Elasticsearch when catalog changes are detected.

Load Balancer (Nginx)

Distributes traffic (round-robin) among backend service instances and manages API routes in a unified manner.

Cache System (Redis)

Implements the Cache-Aside pattern to alleviate database load and reduce response times.

---

🚀 Installation

Prerequisites

• Docker and Docker Compose
• Git
• Node.js and npm (for local development)

Clone the Repository

git clone https://github.com/DanielAR27/proyecto2-bases2.git
cd proyecto2-bases2

Module Installation (Local)

For each service that requires module installation (api, auth_service, search_service, graph_service), navigate to its folder and install:

cd <service_name>
npm install

---

🛠️ Build and Deployment

1. Grant execution permissions to the scripts:

chmod +x set_config.sh init_cluster.sh init_hive_warehouse.sh

2. Execute the main script:

./set_config.sh

This script starts the base services, initializes the MongoDB cluster (with sharding and replication), prepares the Hive warehouse, and starts the backend services.

---

📈 Generating Synthetic Data

If you want to populate the database with test data:

cd pruebas/
npm install
node generarDatosMasivos.js

---

🔄 ETL, Airflow and OLAP Layer

1. Start the ETL service:

docker-compose --profile etl up --build -d

2. After the first execution, start Airflow (responsible for the periodic ETL):

docker-compose --profile airflow up --build -d

3. Start the analytics dashboard:

docker-compose --profile analytics up --build -d

You can view the dashboards at http://localhost:8501/ and check the metrics logs:

docker logs -f spark_analytics

---

🧪 Testing

The project includes unit and integration tests using Jest. They evaluate CRUD flows, security, resilience, and cache behavior.

Testing Commands

Authentication:

docker-compose --profile test build auth_test
docker-compose --profile test run --rm auth_test

Search:

docker-compose --profile test build search_test
docker-compose --profile test run --rm search_test

Main API:

docker-compose --profile test build api_test
docker-compose --profile test run --rm api_test

---

⚙️ CI/CD Pipeline

GitHub Actions is used for Continuous Integration and Continuous Deployment (CI/CD):

• Test: Sets up the test infrastructure, runs microservices tests, and validates coverage.
• Build-and-Push: If tests are successful on main/master, it builds multi-architecture Docker images and pushes them to the GitHub Container Registry.
• Deploy: Generates an artifact with the Docker Compose configuration, scripts, and environment, preparing deployment documentation.

---

🖥️ Interface Access

API Documentation (Swagger)

• Main API: http://localhost/api/api-docs/
• Auth: http://localhost/auth/api-docs/
• Search: http://localhost/search/api-docs/
• Graphs: http://localhost/graph/api-docs/

Databases & Monitoring

• PgAdmin (Postgres): http://localhost:5050 (Host: postgres_container, User: postgres, Pass: postgres)
• Mongo Express: http://localhost:8081 (Check credentials in .env)
• Kibana (Elasticsearch): http://localhost:5601

---

🧹 Environment Reset

To completely remove all containers, networks, and volumes:

docker-compose down -v

---

Last update: 21/8/2026