# phamkhanhduy669/arsenalstats architecture

> Architecture diagram of the GitHub repository phamkhanhduy669/arsenalstats, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-09-30). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/phamkhanhduy669/arsenalstats
- Repository: https://github.com/phamkhanhduy669/arsenalstats

## Overview

ArsenalStats is a hybrid Arsenal football-data platform. Its live workflow polls SofaScore for fixtures and match statistics, writes fixture status to PostgreSQL, publishes events to Kafka, and runs them through Flink SQL into match-stat tables. Separately, scheduled batch ETL fetches squad and coach data and upserts dimensions. Prometheus and Grafana provide observability and dashboards. The README documents several runtime and monitoring connections; SQL templates and infrastructure details are not sampled, so those links rely on documented relationships rather than inspected implementation.

Main entry points: [`streaming/code/producer/match_producer.py`](https://github.com/phamkhanhduy669/arsenalstats/blob/main/streaming/code/producer/match_producer.py) (Match Producer), [`batching/code/arsPRJ/data_etl.py`](https://github.com/phamkhanhduy669/arsenalstats/blob/main/batching/code/arsPRJ/data_etl.py) (Squad ETL), [`postgesql/create_table.sql`](https://github.com/phamkhanhduy669/arsenalstats/blob/main/postgesql/create_table.sql) (PostgreSQL Warehouse), [`batching/code/arsPRJ/utils/db_connection.py`](https://github.com/phamkhanhduy669/arsenalstats/blob/main/batching/code/arsPRJ/utils/db_connection.py) (Warehouse Connection)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_live["Live Match Pipeline"]
  node_producer["Match Producer<br/>[match_producer.py]"]
  node_kafka["Kafka"]
  node_flinkjob["Flink Job<br/>[match_stream.py]"]
  node_sourcesql["Kafka Source SQL"]
  node_processsql["Transform SQL"]
  node_sinksql["Postgres Sink SQL"]
end

subgraph group_batch["Squad ETL"]
  node_batchjob["Squad ETL<br/>[data_etl.py]"]
  node_scheduler["Hourly Scheduler"]
end

subgraph group_storage["Data Warehouse"]
  node_dbconfig["DB Configuration<br/>[db_config.py]"]
  node_dbconnection["Warehouse Connection<br/>[db_connection.py]"]
  node_postgres[("PostgreSQL Warehouse<br/>[create_table.sql]")]
  node_triggers["Integrity Triggers<br/>[trigger.sql]"]
end

subgraph group_monitoring["Monitoring and Dashboards"]
  node_prometheus["Prometheus<br/>[prometheus.yml]"]
  node_grafana["Grafana Dashboards<br/>[arsenal_data.json]"]
end

node_sofascore(("SofaScore API"))
node_footballapi(("Football Data API"))

node_producer -->|"polls fixtures and stats"| node_sofascore
node_producer -->|"publishes events"| node_kafka
node_producer -->|"loads configuration"| node_dbconfig
node_producer -->|"upserts match status"| node_dbconnection
node_scheduler -->|"runs hourly"| node_batchjob
node_batchjob -->|"fetches squad data"| node_footballapi
node_batchjob -->|"loads configuration"| node_dbconfig
node_batchjob -->|"upserts dimensions"| node_dbconnection
node_dbconfig -->|"creates connection config"| node_dbconnection
node_dbconnection -->|"connects to warehouse"| node_postgres
node_postgres -.->|"enforces integrity"| node_triggers
node_kafka -->|"provides event topic"| node_sourcesql
node_flinkjob -->|"executes source DDL"| node_sourcesql
node_flinkjob -->|"executes transform"| node_processsql
node_flinkjob -->|"executes sink DDL"| node_sinksql
node_sinksql -->|"writes match statistics"| node_postgres
node_postgres -->|"supplies business KPIs"| node_grafana
node_prometheus -->|"provides monitoring metrics"| node_grafana

click node_producer "https://github.com/phamkhanhduy669/arsenalstats/blob/main/streaming/code/producer/match_producer.py"
click node_flinkjob "https://github.com/phamkhanhduy669/arsenalstats/blob/main/streaming/code/match_stream.py"
click node_sourcesql "https://github.com/phamkhanhduy669/arsenalstats/blob/main/streaming/code/source/arsenal_live_match.sql"
click node_processsql "https://github.com/phamkhanhduy669/arsenalstats/blob/main/streaming/code/process/arsenal_live_match.sql"
click node_sinksql "https://github.com/phamkhanhduy669/arsenalstats/blob/main/streaming/code/sink/arsenal_match_stats.sql"
click node_batchjob "https://github.com/phamkhanhduy669/arsenalstats/blob/main/batching/code/arsPRJ/data_etl.py"
click node_scheduler "https://github.com/phamkhanhduy669/arsenalstats/blob/main/batching/schedule/crontab"
click node_dbconfig "https://github.com/phamkhanhduy669/arsenalstats/blob/main/batching/code/arsPRJ/utils/db_config.py"
click node_dbconnection "https://github.com/phamkhanhduy669/arsenalstats/blob/main/batching/code/arsPRJ/utils/db_connection.py"
click node_postgres "https://github.com/phamkhanhduy669/arsenalstats/blob/main/postgesql/create_table.sql"
click node_triggers "https://github.com/phamkhanhduy669/arsenalstats/blob/main/postgesql/trigger.sql"
click node_prometheus "https://github.com/phamkhanhduy669/arsenalstats/blob/main/prometheus/prometheus.yml"
click node_grafana "https://github.com/phamkhanhduy669/arsenalstats/blob/main/grafana/dashboards/arsenal_data.json"

classDef toneNeutral fill:#f8fafc,stroke:#334155,stroke-width:1.5px,color:#0f172a
classDef toneBlue fill:#dbeafe,stroke:#2563eb,stroke-width:1.5px,color:#172554
classDef toneAmber fill:#fef3c7,stroke:#d97706,stroke-width:1.5px,color:#78350f
classDef toneMint fill:#dcfce7,stroke:#16a34a,stroke-width:1.5px,color:#14532d
classDef toneRose fill:#ffe4e6,stroke:#e11d48,stroke-width:1.5px,color:#881337
classDef toneIndigo fill:#e0e7ff,stroke:#4f46e5,stroke-width:1.5px,color:#312e81
classDef toneTeal fill:#ccfbf1,stroke:#0f766e,stroke-width:1.5px,color:#134e4a
class node_producer,node_kafka,node_flinkjob,node_sourcesql,node_processsql,node_sinksql toneBlue
class node_batchjob,node_scheduler toneAmber
class node_dbconfig,node_dbconnection,node_postgres,node_triggers,node_sofascore,node_footballapi toneMint
class node_prometheus,node_grafana toneRose
```

## Components

### Live Match Pipeline

- [Match Producer](https://github.com/phamkhanhduy669/arsenalstats/blob/main/streaming/code/producer/match_producer.py): `streaming/code/producer/match_producer.py`
- **Kafka**
- [Flink Job](https://github.com/phamkhanhduy669/arsenalstats/blob/main/streaming/code/match_stream.py): `streaming/code/match_stream.py`
- [Kafka Source SQL](https://github.com/phamkhanhduy669/arsenalstats/blob/main/streaming/code/source/arsenal_live_match.sql): `streaming/code/source/arsenal_live_match.sql`
- [Transform SQL](https://github.com/phamkhanhduy669/arsenalstats/blob/main/streaming/code/process/arsenal_live_match.sql): `streaming/code/process/arsenal_live_match.sql`
- [Postgres Sink SQL](https://github.com/phamkhanhduy669/arsenalstats/blob/main/streaming/code/sink/arsenal_match_stats.sql): `streaming/code/sink/arsenal_match_stats.sql`

### Squad ETL

- [Squad ETL](https://github.com/phamkhanhduy669/arsenalstats/blob/main/batching/code/arsPRJ/data_etl.py): `batching/code/arsPRJ/data_etl.py`
- [Hourly Scheduler](https://github.com/phamkhanhduy669/arsenalstats/blob/main/batching/schedule/crontab): `batching/schedule/crontab`

### Data Warehouse

- [DB Configuration](https://github.com/phamkhanhduy669/arsenalstats/blob/main/batching/code/arsPRJ/utils/db_config.py): `batching/code/arsPRJ/utils/db_config.py`
- [Warehouse Connection](https://github.com/phamkhanhduy669/arsenalstats/blob/main/batching/code/arsPRJ/utils/db_connection.py): `batching/code/arsPRJ/utils/db_connection.py`
- [PostgreSQL Warehouse](https://github.com/phamkhanhduy669/arsenalstats/blob/main/postgesql/create_table.sql): `postgesql/create_table.sql`
- [Integrity Triggers](https://github.com/phamkhanhduy669/arsenalstats/blob/main/postgesql/trigger.sql): `postgesql/trigger.sql`

### Monitoring and Dashboards

- [Prometheus](https://github.com/phamkhanhduy669/arsenalstats/blob/main/prometheus/prometheus.yml): `prometheus/prometheus.yml`
- [Grafana Dashboards](https://github.com/phamkhanhduy669/arsenalstats/blob/main/grafana/dashboards/arsenal_data.json): `grafana/dashboards/arsenal_data.json`

### Other components

- **SofaScore API**
- **Football Data API**

## Connections

- Match Producer → SofaScore API: polls fixtures and stats (evidence: `README.md`)
- Match Producer → Kafka: publishes events (evidence: `README.md`)
- Match Producer → DB Configuration: loads configuration (evidence: `streaming/code/producer/match_producer.py`)
- Match Producer → Warehouse Connection: upserts match status (evidence: `streaming/code/producer/match_producer.py`)
- Hourly Scheduler → Squad ETL: runs hourly (evidence: `README.md`)
- Squad ETL → Football Data API: fetches squad data (evidence: `README.md`)
- Squad ETL → DB Configuration: loads configuration (evidence: `batching/code/arsPRJ/data_etl.py`)
- Squad ETL → Warehouse Connection: upserts dimensions (evidence: `batching/code/arsPRJ/data_etl.py`)
- DB Configuration → Warehouse Connection: creates connection config (evidence: `batching/code/arsPRJ/utils/db_config.py`)
- Warehouse Connection → PostgreSQL Warehouse: connects to warehouse (evidence: `batching/code/arsPRJ/utils/db_connection.py`)
- PostgreSQL Warehouse → Integrity Triggers: enforces integrity
- Kafka → Kafka Source SQL: provides event topic (evidence: `README.md`)
- Flink Job → Kafka Source SQL: executes source DDL (evidence: `README.md`)
- Flink Job → Transform SQL: executes transform (evidence: `README.md`)
- Flink Job → Postgres Sink SQL: executes sink DDL (evidence: `README.md`)
- Postgres Sink SQL → PostgreSQL Warehouse: writes match statistics (evidence: `README.md`)
- PostgreSQL Warehouse → Grafana Dashboards: supplies business KPIs (evidence: `README.md`)
- Prometheus → Grafana Dashboards: provides monitoring metrics (evidence: `README.md`)