ELO Snitch Bot

A data pipeline to track and report on the ELO progress of members of the League of Naija group chat using Python, PostgreSQL, node.js, and WhatsApp Web API.

On launch, members of the gc were able to type commands which generated a table of elo changes for each member of the group chat who had registered in a Google form I shared with them. My machine is prod.

The pipeline runs hourly via cron or systemd timer, fetching player data from Google Forms, generating PUUIDs, checking current ELO ratings, and reporting changes to the WhatsApp group.

Developer hours wasted:

Prerequisites

Before running the bot, ensure you have the following installed:

1. Docker and Docker Compose

   • Install Docker Desktop from here
   • Ensure Docker Compose is included in your installation

2. Google API Credentials

   • Create a Google Cloud project
   • Enable Google Forms API and Google Sheets API
   • Create credentials (OAuth 2.0 Client ID)
   • Download credentials JSON file, rename it to credentials.json and place it in the .google directory.

3. Riot Games API Key

   • Register at Riot Games Developer Portal
   • Generate a developer API key, rename it to riot_api_key and place it in the .env directory.

4. WhatsApp Group ID

   • Open your WhatsApp group of choice on WhatsApp Web
   • Inspect the page elements of the group chat
   • Copy the group ID from the HTML element with the class name 'chat-title'
   • Declare it as whatsapp_group_id in the .env directory.

Environment Setup

1. Create a .env file in the config directory with the following variables:

# Google API Credentials
GOOGLE_APPLICATION_CREDENTIALS=path/to/credentials.json
GOOGLE_FORM_ID=your_form_id
GOOGLE_SHEET_ID=your_sheet_id

# Riot Games API
RIOT_API_KEY=your_riot_api_key
RIOT_REGION=na1

# WhatsApp
WHATSAPP_GROUP_ID=your_group_id
# Announce the command list to the group on startup. Off by default -- the bot
# restarts on every crash, and one announcement per restart is noise.
ANNOUNCE_ON_START=false

Directory Structure

elo_snitch_bot/
├── assets/               # Static assets
├── config/               # Configuration files
├── data/                 # Data storage directory
│   └── elo_changes/      # ELO change history
├── docker/               # Docker configuration
├── logs/                 # Application logs
├── node_modules/         # JavaScript dependencies
├── sql/migrations/       # Ordered, re-runnable schema migrations
├── src/
│   ├── python/           # Python source code
│   │   ├── run_pipeline.py    # Pipeline orchestrator (replaces Airflow)
│   │   ├── fetch_google_forms_data.py  # Fetch player data
│   │   ├── generate_puuid.py  # Player PUUID generation
│   │   ├── elo_check.py       # ELO checking
│   │   └── elo_tracker.py     # ELO tracking and reporting
│   └── js/               # WhatsApp bot
│       ├── bot.js             # Client wiring and event handlers
│       ├── commands.js        # Command table and rate limiting
│       ├── format.js          # Report formatting (pure, tested)
│       └── data.js            # Reads the pipeline's latest.json
├── .env                  # Environment variables (repo root, for docker-compose)
├── Dockerfile            # Docker configuration
└── docker-compose.yaml   # Docker Compose configuration

Installation

1. Clone the repository
2. Create and configure your .env file as described above
3. Start Postgres:

docker compose up -d pgdatabase

If port 5432 is already taken by another project, set POSTGRES_PORT in a .env at the repo root (for compose) and in config/.env (for the Python client) so the two agree:

POSTGRES_PORT=5433 docker compose up -d pgdatabase

4. Apply database migrations, in order:

docker exec -i <postgres-container> psql -U root -d snitch_bot_db \
  -v ON_ERROR_STOP=1 < sql/migrations/001_consolidate_players.sql

Then sanity-check the result — every count in the output should show zero orphans before you rely on it:

docker exec -i <postgres-container> psql -U root -d snitch_bot_db \
  < sql/migrations/001_verify.sql

5. Start the WhatsApp bot (from the repo root) — see WhatsApp Bot for the first-run QR pairing:

npm install && npm start

Player identity

Players live in a single players table keyed on their Riot ID (summ_id + player_tag). Earlier versions keyed players on the row index of the Google Sheet, which meant deleting or reordering a sheet row silently reassigned that player's entire ELO history to someone else. 001 migrates off that scheme; players.legacy_id retains the old index for auditing only.

WhatsApp Bot

npm install
npm start

On first run the terminal prints a QR code. Open WhatsApp on your phone → Settings → Linked devices → Link a device → scan it. The session is saved to src/js/.wwebjs_auth/, so later starts skip the QR entirely. Delete that directory to force a re-pair.

Once linked the bot prints Listening in "<group name>". If instead it prints that no group matches WHATSAPP_GROUP_ID, it lists every group it can see with their IDs — copy the right one into config/.env.

Commands

Recognised only in the group named by WHATSAPP_GROUP_ID, and only when the message is exactly the command. Anything else is ignored in silence, including in direct messages.

Command	Reports
!elocheck	Every tracked player's ELO change, grouped by queue
!topelo	The five largest ELO changes
!winrate	Solo/duo win rates, most active players, and totals
!help	The command list

Each report ends with when the pipeline last ran, in both absolute and relative form: _Last updated: 2026/08/08 21:32:50 (3 hours ago)_. Reports read data/*/latest.json, so the bot has nothing to show until the pipeline has run at least once.

Commands are rate limited to 5 per user per minute. Exceeding it is ignored silently rather than answered, so a flood is not amplified into a reply per message.

Tests

npm test

Node's built-in runner (node --test) — no test framework to install. The suite covers command parsing, timestamp handling, the three formatters, and the rate limiter. bot.js is deliberately untested: it is I/O only, and everything worth asserting was moved out of it.

Pipeline Overview

The bot runs hourly (via cron or systemd timer) and executes the following tasks in sequence:

1. fetch_google_forms_data.py - Fetch player data from Google Forms
2. generate_puuid.py - Generate PUUIDs for players
3. elo_check.py - Check current ELO for all players
4. elo_tracker.py - Track and report ELO changes

Scheduling the Pipeline

The pipeline is orchestrated by src/python/run_pipeline.py. Choose one of the following to run it hourly:

Option 1: Cron

Add to your crontab (crontab -e):

0 * * * * cd /path/to/elo_snitch_bot && python -m src.python.run_pipeline >> logs/pipeline.log 2>&1

This runs at the top of every hour (minute 0). Adjust the minute field (first 0) if you prefer a different time within the hour.

Option 2: Systemd Timer (Linux only)

Create /etc/systemd/system/elo-snitch.service:

[Unit]
Description=ELO Snitch Bot Pipeline
After=network-online.target postgresql.service
Wants=network-online.target

[Service]
Type=oneshot
User=youruser
WorkingDirectory=/path/to/elo_snitch_bot
ExecStart=/usr/bin/python -m src.python.run_pipeline
StandardOutput=journal
StandardError=journal

Create /etc/systemd/system/elo-snitch.timer:

[Unit]
Description=Run ELO Snitch Pipeline Hourly
Requires=elo-snitch.service

[Timer]
OnBootSec=5min
OnUnitActiveSec=1h
Persistent=true

[Install]
WantedBy=timers.target

Enable and start:

sudo systemctl daemon-reload
sudo systemctl enable --now elo-snitch.timer

Monitor:

sudo systemctl status elo-snitch.timer
sudo journalctl -u elo-snitch.service -f

Deploying to Fly.io

One machine runs both the bot and the pipeline. They are coupled through the filesystem -- the pipeline writes data/<folder>/latest.json, data.js reads it -- so separating them would mean a second machine, a second volume and some way to share files between them.

The machine cannot scale to zero. WhatsApp Web is an outbound websocket, so there is no inbound request for Fly to wake a stopped machine on; if it stops, the group loses the bot until you notice.

Roughly $3.35/mo at the time of writing: ~$3.20 for an always-on shared-cpu-1x 512MB machine, $0.15 for a 1GB volume, $0 for Postgres on a free tier. Fly's plan minimum is higher than that, so it is likely what you actually pay. Check current prices before committing.

One-time setup

Postgres first. Create a free database (Neon, Supabase, or similar), then apply the migrations to it from your machine:

psql "$NEON_URL" -f sql/migrations/001_consolidate_players.sql
psql "$NEON_URL" -f sql/migrations/002_normalize_and_merge_players.sql

Then create the app and its volume. Pick a region near you -- lhr is the default in fly.toml:

fly launch --no-deploy --copy-config --name elo-snitch-bot
fly volumes create snitch_data --size 1 --region lhr

Secrets. The Google service-account JSON is base64-encoded because Fly secrets are single-line:

fly secrets set \
  DATABASE_URL="postgresql://..." \
  WHATSAPP_GROUP_ID="1234567890@g.us" \
  RIOT_API_KEY="RGAPI-..." \
  RIOT_REGION="europe" \
  RIOT_PLATFORM="euw1" \
  GOOGLE_SHEET_ID="..." \
  GOOGLE_SHEET_RANGE="Form Responses 1!A:D" \
  GOOGLE_CREDENTIALS_B64="$(base64 -w0 .google/credentials.json)"

On Windows PowerShell the last one is:

[Convert]::ToBase64String([IO.File]::ReadAllBytes(".google\credentials.json"))

First deploy

fly deploy
fly logs

The bot has no session yet, so it prints a QR code to the log. Scan it with WhatsApp on your phone (Settings -> Linked Devices). The code refreshes every 20 seconds, so a missed one is not a problem.

If log streaming mangles the QR into unscannable noise, set LOG_QR_PAYLOAD=true as a secret, redeploy, and paste the logged payload into any QR renderer. Unset it once you are linked -- that string links a device to your WhatsApp account, and log output has a way of ending up in chats.

The session persists on the volume, so subsequent deploys do not re-prompt.

Operating it

fly logs	bot and pipeline output, interleaved
fly ssh console	shell on the machine; snapshots are under /data/snapshots
fly status	machine state and restart count
fly secrets set RIOT_API_KEY=...	rotate the key; restarts the machine

A Riot development key expires every 24 hours. The pipeline will start failing a day after each rotation. That is survivable by design -- failures are logged and swallowed, and the bot keeps answering from the last good snapshot -- but the numbers go stale until you set a fresh key. Apply for a personal or production key if this is meant to run unattended.

Set RUN_PIPELINE=false to deploy the bot alone, serving whatever is already on the volume.

Ports

Two .env files, deliberately separate:

File	Read by	Purpose
.env (repo root)	docker compose only	host port substitution in docker-compose.yaml
config/.env	the Python pipeline	Riot/Google credentials, DB connection

POSTGRES_PORT must be set to the same value in both, or the pipeline will connect to a different database than the one compose published.

Defaults are POSTGRES_PORT=5432 and PGADMIN_PORT=5051. Override in the root .env when another project already binds those.

pgAdmin

Browse to http://localhost:${PGADMIN_PORT} and log in with PGADMIN_DEFAULT_EMAIL / PGADMIN_DEFAULT_PASSWORD from config/pgadmin.env.

The snitch_bot server is preregistered from config/pgadmin_servers.json. Expanding it prompts once for the Postgres password (POSTGRES_USER's password, root by default) — tick Save Password to be asked only once.

Note that pgAdmin reads only PGADMIN_* variables. The DB_HOST/DB_PORT/ DB_USER/DB_PASS/DB_NAME entries in config/pgadmin.env are inert; the connection is defined in pgadmin_servers.json instead. That file uses the compose service name pgdatabase and its internal port 5432, not the published host port.

Tests

pip install -r config/requirements.dev.txt
pytest

The suite covers the ranked-ladder maths, which has no database or network dependency. ladder_points is verified exhaustively — every reachable rank from Iron IV 0 LP to Challenger is enumerated and asserted strictly increasing — rather than by sampled examples.

Why ladder points exist

Riot's league_points resets to near zero on promotion, so subtracting two raw values reports a tier climb as a large loss. Gold IV 98 LP → Platinum IV 4 LP came out as -94 LP while the player had in fact gained 306. Because get_top_changes then ranked by absolute LP, the leaderboard was dominated by promotions masquerading as the worst losses of the day — the headline output was wrong precisely when something worth reporting had happened.

ladder_points maps a rank onto one monotonic scale (tier * 400 + division * 100 + lp) before differencing, so the sign always agrees with the direction the player actually moved.

Troubleshooting

• If you encounter Google API authentication issues, verify your credentials in the .env file
• For Riot API rate limiting issues, consider implementing a retry mechanism or increasing the delay between API calls
• Check Docker logs for detailed error messages: docker-compose logs