Sportpark Visitor Analytics

A hobby project exploring how far you can get building a full-stack application from a single specification document — with AI assistance and a few manual tweaks.

The app scrapes live visitor counts from a fitness club, stores historical data, and provides an analytics dashboard with occupancy trends, personalized recommendations, and ML-based visitor predictions.

What's in here

Component	Tech	Purpose
Scraper	Python + APScheduler	Polls visitor count + weather data on a schedule, caches the 7-day weather forecast
Backend	FastAPI	REST API for analytics, recommendations, ML training & prediction
Frontend	React + Recharts	Dashboard with live status, charts, heatmap, predictions
ML Pipeline	scikit-learn, XGBoost, LightGBM, Optuna	Visitor count forecasting + realtime ghost correction
Database	PostgreSQL	Time-series storage for visitor counts and weather
Databricks Sync	Python + APScheduler	Nightly incremental export of visitor counts to Databricks
Deployment	Docker Compose	Single-command local deploy

How it was built

The entire application — scraper, backend API, ML pipeline, frontend dashboard, database schema, and Docker setup — was generated from SPECIFICATION.md using AI coding tools. Manual additions were limited to tweaks and iterative refinements.

Quick start

cp .env.example .env
# Edit .env with your API URL and a strong database password
docker compose up -d

The dashboard is available at http://localhost:18080 and the API at http://localhost:18432.

Databricks sync

The databricks-sync service pushes newly scraped visitor counts to a Databricks Free Edition SQL warehouse once a night (03:15 Europe/Berlin by default), so the data can be analysed with Databricks SQL and notebooks. Only visitor_counts is exported — weather data stays in Postgres.

Each run stamps its rows with an incrementing snapshot_id, so you can always tell which load brought a row in:

SELECT snapshot_id, COUNT(*), MIN(`timestamp`), MAX(`timestamp`), MAX(loaded_at)
FROM workspace.sportpark.visitor_counts
GROUP BY snapshot_id ORDER BY snapshot_id;

The watermark lives in Databricks itself (MAX(id) on the target table), so there is no local state to drift: dropping the table on the Databricks side simply re-triggers a full backfill, and a run that fails halfway resumes exactly where it stopped.

Setup: create a Free Edition workspace, start its SQL warehouse and copy the warehouse ID, generate a personal access token under Settings → Developer → Access tokens, then fill in DATABRICKS_HOST, DATABRICKS_TOKEN, and DATABRICKS_WAREHOUSE_ID in .env.

The container syncs once on startup before scheduling the nightly run, so the initial backfill happens as soon as you bring it up — no manual step. To run a one-off sync without starting the scheduler:

docker compose run --rm databricks-sync python main.py --once

Weather cache

Predictions never call Open-Meteo directly. The scraper stores hourly weather for the past 7 days and the next 7 days in the weather_hourly table and refreshes it whenever the forecast is older than 12 hours (checked hourly, so a failed fetch is retried an hour later). If the API is down, the Forecast tab keeps working off the last snapshot and flags it as stale; hours without any weather are predicted from time and visit history alone. When the per-scrape "current weather" call fails, the scraper falls back to the cached value for that hour.

Older days are backfilled from Open-Meteo's Historical Forecast API: at startup and once a day the scraper looks for days between the first visitor reading and the refresh window that have fewer than 24 hourly rows and fetches them (at most 31 days per request). Backfilled rows are insert-only and stored with is_forecast = FALSE, so they never overwrite existing rows or make a stale forecast look fresh. The first run after deploying can take a few minutes; a failed fetch is logged and retried on the next run.

Optional scraper environment variables (defaults in parentheses): WEATHER_REFRESH_HOURS (12), WEATHER_PAST_DAYS (7), WEATHER_FORECAST_DAYS (8 — today plus the next 7 days), WEATHER_HISTORY_URL (Open-Meteo Historical Forecast API; start/end dates are appended).

Configuration

See .env.example for all available environment variables.

Project specification

The full specification that this project was built from is in SPECIFICATION.md.