391 lines
16 KiB
Markdown
391 lines
16 KiB
Markdown
# OTel BI Platform
|
|
|
|
OpenTelemetry-instrumented business intelligence platform backed by two MSSQL data warehouses (AdventureWorks DW, WideWorldImporters DW), a PostgreSQL write store, a Go analytics engine with a built-in cron scheduler, and an OIDC-secured React frontend.
|
|
|
|
---
|
|
|
|
## Architecture
|
|
|
|
```
|
|
Browser (React + TypeScript)
|
|
│ OIDC Authorization Code + PKCE
|
|
│ Bearer JWT on every API call
|
|
▼
|
|
otel-bi-api :8000 Python FastAPI — JWT validation, report generation
|
|
├── GET /api/config OIDC config for the SPA (unauthenticated)
|
|
├── GET /api/health
|
|
├── GET /api/telemetry/status
|
|
├── GET /api/audit AuditLog rows from PostgreSQL
|
|
├── GET /api/exports ExportRecord rows from PostgreSQL
|
|
├── GET /api/jobs/aw JobExecution rows from PostgreSQL
|
|
├── GET /api/jobs/wwi
|
|
├── POST /api/reports/generate fan-out to Go → XLSX + PDF to shared mount
|
|
├── /api/aw/** proxied to otel-bi-analytics + PDF export
|
|
└── /api/wwi/** proxied to otel-bi-analytics + PDF export
|
|
|
|
otel-bi-analytics :8080 Go — both MSSQL sources, robfig/cron scheduler
|
|
├── GET /aw/sales/** AdventureWorks analytics
|
|
├── GET /aw/reps/**
|
|
├── GET /aw/products/**
|
|
├── GET /aw/anomalies
|
|
├── GET /aw/export/** XLSX generation (excelize)
|
|
├── GET /wwi/sales/** WideWorldImporters analytics
|
|
├── GET /wwi/stock/**
|
|
├── GET /wwi/suppliers/**
|
|
├── GET /wwi/export/** XLSX generation
|
|
└── POST /scheduler/** manual job triggers
|
|
|
|
PostgreSQL Shared write store
|
|
├── audit_log Append-only event trail
|
|
├── job_executions Scheduled job run history
|
|
├── export_records Per-view export metadata
|
|
├── aw_* AW persistence tables
|
|
└── wwi_* WWI persistence tables
|
|
|
|
Grafana Alloy (OTLP/HTTP) Traces + metrics → Tempo / Prometheus
|
|
```
|
|
|
|
---
|
|
|
|
## Features
|
|
|
|
| Feature | Description |
|
|
|---|---|
|
|
| **AW Analytics** | Sales KPIs, 4-year history, linear-regression forecast, rep scores, product demand, anomaly detection |
|
|
| **WWI Analytics** | Sales KPIs, reorder recommendations, supplier reliability scores, business events |
|
|
| **Scheduled Jobs** | 8 robfig/cron jobs in the Go service; per-job OTel root span; `job_executions` record |
|
|
| **Manual Triggers** | `POST /api/aw/jobs/{job}/trigger` and `/api/wwi/jobs/{job}/trigger` |
|
|
| **Audit Log** | Append-only `audit_log`; every analytics call, job, export, and report is recorded |
|
|
| **Data Export** | Per-view XLSX (Go/excelize) or PDF (Python/reportlab); `export_records` metadata |
|
|
| **Full Reports** | `POST /api/reports/generate` aggregates all views; writes `.xlsx` + `.pdf` to a shared mount |
|
|
| **Runtime OIDC** | Frontend fetches OIDC config from `GET /api/config` at boot — nothing baked into the image |
|
|
| **OTel** | W3C trace propagation end-to-end; auto-instrumentation on HTTP, SQL, and HTTPX layers |
|
|
|
|
---
|
|
|
|
## Repository Layout
|
|
|
|
```
|
|
.
|
|
├── .gitea/workflows/
|
|
│ └── ci.yml Gitea Actions: test + build 3 images on Rocky Linux 10
|
|
├── .env.example Docker Compose top-level vars
|
|
├── backend/
|
|
│ ├── Dockerfile otel-bi-api (Python FastAPI)
|
|
│ ├── Dockerfile.analytics otel-bi-analytics (Go)
|
|
│ ├── pyproject.toml
|
|
│ ├── uv.lock
|
|
│ ├── .env.example Python API local dev
|
|
│ ├── app/
|
|
│ │ ├── core/
|
|
│ │ │ ├── audit.py AuditLog, JobExecution, ExportRecord ORM + helpers
|
|
│ │ │ ├── config.py Pydantic settings
|
|
│ │ │ ├── db.py PostgreSQL engine + session factory
|
|
│ │ │ ├── export.py to_pdf_bytes() via reportlab
|
|
│ │ │ ├── executor.py ThreadPoolExecutor
|
|
│ │ │ ├── otel.py OTel setup
|
|
│ │ │ ├── reports.py save_report() → XLSX + PDF
|
|
│ │ │ └── security.py JWT validation
|
|
│ │ ├── domain/
|
|
│ │ │ ├── aw/ AW ORM models + persistence helpers
|
|
│ │ │ └── wwi/ WWI ORM models + persistence helpers
|
|
│ │ ├── routers/
|
|
│ │ │ ├── aw.py /api/aw/** — proxy to Go + PDF exports
|
|
│ │ │ ├── wwi.py /api/wwi/** — proxy to Go + PDF exports
|
|
│ │ │ └── platform.py /api/config, /api/health, /api/audit, /api/reports
|
|
│ │ └── main.py
|
|
│ └── analytics/ Go analytics service
|
|
│ ├── go.mod
|
|
│ ├── .env.example Go analytics local dev
|
|
│ └── cmd/server/main.go
|
|
│ └── internal/
|
|
│ ├── analytics/ AW + WWI query logic
|
|
│ ├── config/ env var loading
|
|
│ ├── db/ MSSQL (database/sql) + PostgreSQL (pgxpool)
|
|
│ ├── export/ XLSX generation (excelize)
|
|
│ ├── handler/ HTTP handlers + route registration
|
|
│ ├── persistence/ PostgreSQL writes + audit appends
|
|
│ └── scheduler/ robfig/cron job definitions + OTel metrics
|
|
└── frontend/
|
|
├── Dockerfile
|
|
├── nginx.conf
|
|
├── .env.example build-time vars only
|
|
└── src/
|
|
├── api/ API client, types, runtime config fetch
|
|
├── auth/ OIDC (oidc-client-ts)
|
|
└── pages/ Dashboard pages
|
|
```
|
|
|
|
---
|
|
|
|
## Prerequisites
|
|
|
|
| Tool | Min Version | Purpose |
|
|
|---|---|---|
|
|
| Docker + Docker Compose | 24+ | Run the full stack |
|
|
| Go | 1.23+ | Analytics service local dev |
|
|
| Python | 3.12+ | API local dev |
|
|
| uv | latest | Python package manager |
|
|
| Node.js | 22+ | Frontend local dev |
|
|
| SQL Server | 2019+ | AdventureWorks DW + WideWorldImporters DW |
|
|
| PostgreSQL | 15+ | Write store |
|
|
|
|
---
|
|
|
|
## Quick Start (Docker Compose)
|
|
|
|
```bash
|
|
git clone ssh://git@git.andric.com.hr:2222/domagoj/zavrsni-rad-otel-app.git
|
|
cd zavrsni-rad-otel-app
|
|
|
|
cp .env.example .env
|
|
$EDITOR .env # fill in MSSQL DSNs and PostgreSQL password
|
|
|
|
docker compose up -d
|
|
```
|
|
|
|
| Service | URL |
|
|
|---|---|
|
|
| Frontend | http://localhost:8080 |
|
|
| API | http://localhost:8000 |
|
|
| Grafana | http://localhost:3000 |
|
|
|
|
---
|
|
|
|
## Local Development
|
|
|
|
### Go analytics service
|
|
|
|
```bash
|
|
cd backend/analytics
|
|
cp .env.example .env
|
|
# edit AW_MSSQL_DSN, WWI_MSSQL_DSN, POSTGRES_DSN
|
|
set -a && source .env && set +a
|
|
go run ./cmd/server
|
|
```
|
|
|
|
### Python API
|
|
|
|
```bash
|
|
cd backend
|
|
cp .env.example .env
|
|
# edit POSTGRES_* and ANALYTICS_SERVICE_URL
|
|
uv sync
|
|
uv run uvicorn app.main:app --host 0.0.0.0 --port 8000 --reload
|
|
```
|
|
|
|
### Frontend
|
|
|
|
```bash
|
|
cd frontend
|
|
cp .env.example .env.local
|
|
# edit VITE_API_BASE_URL if the API is not on port 8000
|
|
npm install
|
|
npm run dev
|
|
```
|
|
|
|
---
|
|
|
|
## Configuration Reference
|
|
|
|
### Go analytics service (`otel-bi-analytics`)
|
|
|
|
| Variable | Required | Default | Description |
|
|
|---|---|---|---|
|
|
| `AW_MSSQL_DSN` | Yes | — | AdventureWorks DSN (go-mssqldb format) |
|
|
| `WWI_MSSQL_DSN` | Yes | — | WideWorldImporters DSN |
|
|
| `POSTGRES_DSN` | Yes | — | PostgreSQL DSN (pgx format) |
|
|
| `PORT` | No | `8080` | HTTP listen port |
|
|
| `OTEL_COLLECTOR_ENDPOINT` | No | `http://localhost:4318` | OTLP/HTTP endpoint |
|
|
| `OTEL_SERVICE_NAME` | No | `otel-bi-analytics` | |
|
|
| `OTEL_SERVICE_NAMESPACE` | No | `final-thesis` | |
|
|
| `DEFAULT_TOP_N` | No | `10` | Default ranking list length |
|
|
| `FORECAST_HORIZON_DAYS` | No | `30` | Default forecast horizon |
|
|
| `DEFAULT_HISTORY_DAYS` | No | `365` | Default sales history look-back |
|
|
|
|
### Python API (`otel-bi-api`)
|
|
|
|
| Variable | Required | Default | Description |
|
|
|---|---|---|---|
|
|
| `ANALYTICS_SERVICE_URL` | No | `http://localhost:8080` | Go analytics base URL |
|
|
| `POSTGRES_HOST` | No | `localhost` | |
|
|
| `POSTGRES_PORT` | No | `5432` | |
|
|
| `POSTGRES_DATABASE` | No | `otel_bi` | |
|
|
| `POSTGRES_USERNAME` | No | `otel_bi` | |
|
|
| `POSTGRES_PASSWORD` | No | `otel_bi` | |
|
|
| `POSTGRES_SSLMODE` | No | `prefer` | Use `require` in production |
|
|
| `POSTGRES_CONNECTION_STRING` | No | — | Full DSN override |
|
|
| `CORS_ORIGINS` | No | `http://localhost:5173` | Comma-separated allowed origins |
|
|
| `REQUIRE_FRONTEND_AUTH` | No | `true` | Enforce JWT on all endpoints |
|
|
| `FRONTEND_JWT_ISSUER_URL` | If auth | — | OIDC issuer URL |
|
|
| `FRONTEND_JWT_AUDIENCE` | If auth | — | Expected `aud` claim |
|
|
| `FRONTEND_JWT_JWKS_URL` | No | — | JWKS URL (derived from issuer if omitted) |
|
|
| `FRONTEND_JWT_ALGORITHM` | No | `RS256` | |
|
|
| `FRONTEND_REQUIRED_SCOPES` | No | `""` | Space-separated required scopes |
|
|
| `FRONTEND_OIDC_CLIENT_ID` | No | `""` | Served to SPA via `GET /api/config` |
|
|
| `FRONTEND_OIDC_SCOPE` | No | `openid profile email` | Served to SPA via `GET /api/config` |
|
|
| `REPORT_OUTPUT_DIR` | No | `/tmp/otel-bi-reports` | Mount a PVC here in Kubernetes |
|
|
| `OTEL_SERVICE_NAME` | No | `otel-bi-api` | |
|
|
| `OTEL_SERVICE_NAMESPACE` | No | `final-thesis` | |
|
|
| `OTEL_COLLECTOR_ENDPOINT` | No | `http://localhost:4318` | |
|
|
| `APP_ENV` | No | `dev` | Set `prod` to disable `/docs` |
|
|
| `LOG_LEVEL` | No | `INFO` | |
|
|
|
|
### Frontend (build-time only)
|
|
|
|
| Variable | Default | Description |
|
|
|---|---|---|
|
|
| `VITE_API_BASE_URL` | `http://localhost:8000` | API base URL seen by the browser |
|
|
| `VITE_OTEL_COLLECTOR_ENDPOINT` | `http://localhost:4318` | OTLP/HTTP for frontend traces |
|
|
| `VITE_OTEL_SERVICE_NAME` | `otel-bi-frontend` | |
|
|
| `VITE_OTEL_SERVICE_NAMESPACE` | `final-thesis` | |
|
|
|
|
OIDC config is **not** a build-time variable — it is fetched at runtime from `GET /api/config`.
|
|
|
|
---
|
|
|
|
## Scheduled Jobs
|
|
|
|
All jobs run inside the Go analytics service via robfig/cron. Each job emits an OTel root span and writes a `job_executions` row to PostgreSQL.
|
|
|
|
| Job ID | Schedule (UTC) | Description |
|
|
|---|---|---|
|
|
| `aw.daily.forecast` | 02:00 daily | Sales forecast via linear regression |
|
|
| `aw.daily.scores` | 02:30 daily | Rep performance + product demand scores |
|
|
| `aw.daily.data_quality` | 03:00 daily | AW data quality checks |
|
|
| `aw.daily.anomaly_detection` | 03:30 daily | Revenue anomaly detection |
|
|
| `wwi.hourly.reorder` | :00 every hour | Reorder recommendations + stock events |
|
|
| `wwi.daily.supplier_scores` | 03:30 daily | Supplier reliability scores |
|
|
| `wwi.hourly.events` | :30 every hour | HIGH-urgency stock-level event scan |
|
|
| `wwi.daily.data_quality` | 04:00 daily | WWI data quality checks |
|
|
|
|
**Manual trigger** (authenticated):
|
|
|
|
```bash
|
|
# AW jobs: forecast | scores | data_quality | anomaly_detection
|
|
curl -X POST http://localhost:8000/api/aw/jobs/forecast/trigger \
|
|
-H "Authorization: Bearer $TOKEN"
|
|
|
|
# WWI jobs: reorder | supplier_scores | events | data_quality
|
|
curl -X POST http://localhost:8000/api/wwi/jobs/reorder/trigger \
|
|
-H "Authorization: Bearer $TOKEN"
|
|
```
|
|
|
|
---
|
|
|
|
## API Reference
|
|
|
|
All endpoints are on `otel-bi-api` (port 8000). Endpoints marked *proxy* forward the request to `otel-bi-analytics` unchanged.
|
|
|
|
| Method | Path | Auth | Description |
|
|
|---|---|---|---|
|
|
| GET | `/api/config` | No | OIDC config for the SPA |
|
|
| GET | `/api/health` | No | Health check |
|
|
| GET | `/api/telemetry/status` | Yes | OTel instrumentation info |
|
|
| GET | `/api/audit` | Yes | Audit log (`?domain=aw\|wwi&limit=`) |
|
|
| GET | `/api/exports` | Yes | Export history (`?domain=aw\|wwi&limit=`) |
|
|
| GET | `/api/jobs/aw` | Yes | AW job history |
|
|
| GET | `/api/jobs/wwi` | Yes | WWI job history |
|
|
| POST | `/api/reports/generate` | Yes | Full XLSX + PDF report |
|
|
| GET | `/api/aw/sales/kpis` | Yes | AW KPIs (proxy) |
|
|
| GET | `/api/aw/sales/history` | Yes | AW sales history — `?days_back=` (proxy) |
|
|
| GET | `/api/aw/sales/forecast` | Yes | AW forecast — `?horizon_days=` (proxy) |
|
|
| GET | `/api/aw/reps/scores` | Yes | AW rep scores — `?top_n=` (proxy) |
|
|
| GET | `/api/aw/products/demand` | Yes | AW product demand — `?top_n=` (proxy) |
|
|
| GET | `/api/aw/export/sales-history` | Yes | `?format=xlsx\|pdf` |
|
|
| GET | `/api/aw/export/sales-forecast` | Yes | `?format=xlsx\|pdf` |
|
|
| GET | `/api/aw/export/rep-scores` | Yes | `?format=xlsx\|pdf` |
|
|
| GET | `/api/aw/export/product-demand` | Yes | `?format=xlsx\|pdf` |
|
|
| POST | `/api/aw/jobs/{job}/trigger` | Yes | Trigger AW job immediately |
|
|
| GET | `/api/wwi/sales/kpis` | Yes | WWI KPIs (proxy) |
|
|
| GET | `/api/wwi/stock/recommendations` | Yes | Reorder recommendations (proxy) |
|
|
| GET | `/api/wwi/suppliers/scores` | Yes | Supplier scores — `?top_n=` (proxy) |
|
|
| GET | `/api/wwi/export/stock-recommendations` | Yes | `?format=xlsx\|pdf` |
|
|
| GET | `/api/wwi/export/supplier-scores` | Yes | `?format=xlsx\|pdf` |
|
|
| GET | `/api/wwi/export/business-events` | Yes | `?format=xlsx\|pdf` |
|
|
| POST | `/api/wwi/jobs/{job}/trigger` | Yes | Trigger WWI job immediately |
|
|
|
|
---
|
|
|
|
## PostgreSQL Schema
|
|
|
|
Tables are created automatically on API startup via SQLAlchemy `metadata.create_all()`.
|
|
|
|
| Table | Purpose |
|
|
|---|---|
|
|
| `audit_log` | Append-only event trail |
|
|
| `job_executions` | One row per scheduled job run |
|
|
| `export_records` | Per-view download metadata |
|
|
| `aw_forecasts` | Persisted AW forecast points |
|
|
| `aw_rep_scores` | AW rep score snapshots |
|
|
| `aw_product_demand` | AW product demand snapshots |
|
|
| `aw_anomaly_runs` | AW anomaly detection results |
|
|
| `wwi_reorder_recommendations` | WWI reorder snapshots |
|
|
| `wwi_supplier_scores` | WWI supplier score snapshots |
|
|
| `wwi_business_events` | Stock-level business events |
|
|
|
|
---
|
|
|
|
## CI/CD
|
|
|
|
`.gitea/workflows/ci.yml` — three independent pipelines on push to `master` or a version tag. Test jobs run inside `rockylinux/rockylinux:10` containers using shell-based checkout.
|
|
|
|
| Pipeline | Test job | Build job | Image |
|
|
|---|---|---|---|
|
|
| Python API | `test` (uv + pytest) | `build-api` | `domagoj/otel-bi-api` |
|
|
| Go analytics | `test-analytics` (go vet + go test) | `build-analytics` | `domagoj/otel-bi-analytics` |
|
|
| Frontend | `test` | `build-frontend` | `domagoj/otel-bi-frontend` |
|
|
|
|
**Required repository secrets:**
|
|
|
|
| Secret | Description |
|
|
|---|---|
|
|
| `REGISTRY_HOST` | Container registry hostname |
|
|
| `REGISTRY_USERNAME` | Registry login |
|
|
| `REGISTRY_TOKEN` | Registry PAT (packages read + write) |
|
|
|
|
---
|
|
|
|
## OTel Coverage
|
|
|
|
**Frontend:**
|
|
- W3C `traceparent` / `tracestate` propagation on all fetch calls
|
|
- `@opentelemetry/instrumentation-document-load`
|
|
- `@opentelemetry/instrumentation-fetch`
|
|
- Manual spans on dashboard data aggregation
|
|
|
|
**Go analytics service:**
|
|
- `otelhttp` HTTP server auto-instrumentation
|
|
- Manual spans on all analytics and export functions
|
|
- Per-job root span (`trace.WithNewRoot()`) for independent trace trees
|
|
- OTel metrics: `scheduler.job.duration_seconds`, `scheduler.job.success_total`, `scheduler.job.failure_total`, `scheduler.job.records_processed_total`
|
|
|
|
**Python API:**
|
|
- FastAPI auto-instrumentation (request span per endpoint)
|
|
- SQLAlchemy auto-instrumentation (PostgreSQL)
|
|
- HTTPX auto-instrumentation (calls to Go analytics)
|
|
- `x-trace-id` / `x-span-id` response headers
|
|
- W3C context propagation injected into all Go analytics calls
|
|
|
|
---
|
|
|
|
## Read-Only Guarantee (MSSQL)
|
|
|
|
- `ApplicationIntent=ReadOnly` on all MSSQL DSNs
|
|
- SQL layer only executes `SELECT` / `WITH` statements
|
|
- All writes target PostgreSQL only
|
|
- Use a SQL Server login with `db_datareader` only — no DDL or DML permissions needed
|
|
|
|
---
|
|
|
|
## Verification Checklist
|
|
|
|
1. `GET /api/health` → `{"status": "ok", "service": "otel-bi-backend"}`
|
|
2. `GET /api/config` → OIDC config without a token
|
|
3. After login, `GET /api/aw/sales/kpis` → data rows
|
|
4. `GET /api/audit` → rows for the KPI call
|
|
5. `GET /api/jobs/aw` → job run records (after cron tick or manual trigger)
|
|
6. `GET /api/aw/export/sales-forecast?format=xlsx` → downloads `.xlsx`
|
|
7. `POST /api/reports/generate` → paths to `.xlsx` and `.pdf` in `REPORT_OUTPUT_DIR`
|
|
8. Grafana Tempo → trace spanning `otel-bi-api → otel-bi-analytics → MSSQL / PostgreSQL`
|