# felidb/etl-movilidad-interdepartamental-postgres architecture

> Architecture diagram of the GitHub repository felidb/etl-movilidad-interdepartamental-postgres, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-05-29). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/felidb/etl-movilidad-interdepartamental-postgres
- Repository: https://github.com/felidb/etl-movilidad-interdepartamental-postgres

## Overview

##### Project Type and Purpose
- This is an ETL‐and‐analytics project packaged with Docker Compose.
- It loads open CSV datasets (departamentos.csv, provincias.csv, movilidadinterdepartamental.csv) into a PostgreSQL database via an initialization script.
- It then exposes two UIs—pgAdmin (for DB administration) and Apache Superset (for BI dashboards and SQL queries)—all running as Docker services.

##### Main Components
A. Data Sources (Host)
- CSV files under `/datos`
B. Initialization Layer
- `init/init.sql` script (mounted into the Postgres container’s `/docker-entrypoint-initdb.d`)
- `init.sh` / `init.bat` scripts (entrypoint to `docker-compose up`)
C. Docker Compose Orchestrator
- `docker-compose.yml` defines 3 services and two named volumes
D. PostgreSQL Service
- Image: postgres:15
- Mounted volumes:
- `./datos` → `/datos` (holds CSVs)
- `./init` → `/docker-entrypoint-initdb.d` (executes `init.sql` on startup)
- Exposed port: 5432
E. pgAdmin Service
- Image: dpage/pgadmin4
- Named volume: `pgadmin-data` → `/var/lib/pgadmin`
- Exposed port: 8080
F. Apache Superset Service
- Built from `superset/Dockerfile`
- Named volume: `superset-data` → `/app/superset_home`
- Exposed port: 8088
- Depends on PostgreSQL (ensures DB is up before Superset initializes)
G. Host OS / Developer
- Runs `init.sh`/`init.bat` to bring containers up
- Connects via browser to pgAdmin and Superset

##### Relationships & Data Flows
- On `docker-compose up`, Postgres container reads `init.sql`, creates tables (`provincia`, `departamento`, `movilidadinterdepartamental`), and bulk‐loads CSVs from `/datos`.
- Once DB is ready:
- pgAdmin connects to `postgres` service on port 5432 for CRUD and schema inspection.
- Superset connects via SQLAlchemy URI (`postgresql+psycopg2://postgres:postgres@postgres:5432/movilidad`) for analytics.
- Developer/Analyst interacts with UIs through host browser.

##### Architectural Patterns & Design Principles
- Containerization: each major component runs in its own Docker container with minimal coupling.
- Volume mounts used for both persistence (named volumes) and data injection (host directories).
- Entrypoint scripts handle schema creation and data loading (pattern: immutable infrastructure on init).
- Separation of concerns:
- ETL/import at DB init
- DB administration via pgAdmin
- Analytics/visualization via Superset

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Layer 5: User
    User["Host Browser"]:::user

    %% Layer 4: User Interfaces
    subgraph "Docker Compose"
      direction TB
      pgAdmin["pgAdmin (8080)"]:::service
      Superset["Superset (8088)"]:::service
      Postgres["PostgreSQL (5432)"]:::service
    end

    %% Layer 3: Orchestrator & Init
    compose[/docker-compose.yml/]:::script
    initEntry[/init.sh\ninit.bat/]:::script
    initSQL[/init/init.sql/]:::script

    %% Layer 2 & 1: Data Sources and CSVs
    subgraph "Data Sources"
      direction TB
      datosDir[(datos/)]:::data
      depCSV[(departamentos.csv)]:::data
      provCSV[(provincias.csv)]:::data
      movCSV[(movilidadinterdepartamental.csv)]:::data
    end

    %% Connections
    User -->|"access UI"| pgAdmin
    User -->|"access UI"| Superset

    User -->|"runs"| initEntry
    initEntry -->|"uses"| compose
    compose -->|"orchestrates"| Postgres
    compose -->|"orchestrates"| pgAdmin
    compose -->|"orchestrates"| Superset

    datosDir -->|mounts CSVs| Postgres
    depCSV -->|located in| datosDir
    provCSV -->|located in| datosDir
    movCSV -->|located in| datosDir

    initSQL -->|executes on init| Postgres

    Postgres -->|connects on 5432| pgAdmin
    Postgres -->|"depends_on"| Superset
    Postgres -->|data source| Superset

    %% Click Events
    click datosDir "https://github.com/felidb/etl-movilidad-interdepartamental-postgres/tree/main/datos/"
    click depCSV "https://github.com/felidb/etl-movilidad-interdepartamental-postgres/blob/main/datos/departamentos.csv"
    click provCSV "https://github.com/felidb/etl-movilidad-interdepartamental-postgres/blob/main/datos/provincias.csv"
    click movCSV "https://github.com/felidb/etl-movilidad-interdepartamental-postgres/blob/main/datos/movilidadinterdepartamental.csv"
    click initSQL "https://github.com/felidb/etl-movilidad-interdepartamental-postgres/blob/main/init/init.sql"
    click initEntry "https://github.com/felidb/etl-movilidad-interdepartamental-postgres/blob/main/init.sh"
    click initEntry "https://github.com/felidb/etl-movilidad-interdepartamental-postgres/blob/main/init.bat"
    click compose "https://github.com/felidb/etl-movilidad-interdepartamental-postgres/blob/main/docker-compose.yml"
    click supDir "https://github.com/felidb/etl-movilidad-interdepartamental-postgres/tree/main/superset/"
    click supDF "https://github.com/felidb/etl-movilidad-interdepartamental-postgres/tree/main/superset/Dockerfile"

    %% Styles
    classDef data fill:#a2fca2,stroke:#333,stroke-width:1px
    classDef service fill:#A2D1F2,stroke:#333,stroke-width:1px
    classDef script fill:#F9F871,stroke:#333,stroke-width:1px
    classDef user fill:#F4A261,stroke:#333,stroke-width:1px
```
