Family Quiniela

A serverless bot that runs my family's weekly Quiniela (the Spanish football pools) end to end: it detects each new LaLiga matchday from the official lottery data, collects everyone's predictions through a small web app, posts progress and results to a Telegram group, works out who pays the next ticket and reports any prize won. It runs entirely on free tiers, with no server to maintain.

📄 Full guide (functional and technical, for any reader): English · Español

The code, documentation and database are in English. Everything the players see (Telegram messages, ticket images and the web app) is in Spanish, their language.

Highlights

• Fully automated pipeline on a schedule with GitHub Actions, triggered every 10 minutes through the GitHub API (GitHub's own scheduler is kept as a fallback): no servers, no manual steps.
• Idempotent design: every message is flagged in the database once sent, so runs can be repeated or overlap safely and nothing is ever duplicated.
• Unofficial data source handled defensively: the lottery has no public API, so the client uses its website's JSON endpoints with browser impersonation, tolerant parsing and a built-in diagnose command.
• Self-maintaining rules: the LaLiga team list is pulled from ESPN every season, kickoff changes move the betting deadline automatically, and postponed matches are handled.
• Interactive without a server: Telegram button presses (closing betting early) are collected by polling on each scheduled run, with the processed update stored in the database.
• Two data sources for results: the lottery can publish results hours late, so final scores also come from ESPN; the official results always take precedence.
• Secure by default: the public website only works with a family key that travels in the link posted to the private Telegram group (never in the repository), plus a per-player PIN hashed with bcrypt and rate-limited; Row Level Security with the site restricted to three database functions; secrets kept out of the code; encrypted backups.
• Tested in CI: an offline simulation runs on every push and exercises full matchdays (announcement → results → payments → prizes), including edge cases.

Architecture

 Loterías y Apuestas ─┐                                 ┌─> Telegram group
 (fixtures, results,  ├─> Bot (Python, GitHub Actions) ─┤   (announcements, images,
  prize breakdown)    │          │                      │    reminders, results, prizes)
 ESPN (LaLiga teams, ─┘          ▼                      │
  live scores)            Supabase (PostgreSQL) <───────┴── Web app (GitHub Pages)
                                                             players submit their picks

Component	Technology	Role
Bot	Python 3.12 on GitHub Actions (bot/)	Reads the official data, computes hits, posts to Telegram
Web app	Static HTML + JS on GitHub Pages (web/)	Each player picks their name, enters a PIN and fills in their column
Database	Supabase / PostgreSQL (supabase/)	Matchdays, fixtures, picks, reports sent, payments
Messaging	Telegram Bot API	Ticket images, reminders, who pays, prizes
Data	Loterías y Apuestas del Estado (LAE)	Official fixtures, kickoff times, results, prize breakdown
Teams and live scores	ESPN public API	Current LaLiga top-flight teams (league filter) and final scores as soon as each match ends
Backup	GitHub Actions (backups/)	Weekly encrypted database backup

How a matchday works

1. LAE publishes a LaLiga Quiniela → the bot creates the matchday, chooses who fills in the Pleno al 15 and posts the fixtures image with a Hacer mi quiniela button.
2. Each player submits a column on the web app with their PIN, and can change it until betting closes: 24 h before the first kickoff, or earlier (step 4).
3. 48 h before the first kickoff → reminder in the group, mentioning whoever is missing.
4. When all five are in → the bot asks whoever pays the ticket, with Yes/No buttons, whether to close betting now. Yes closes it at once; No (or no answer) keeps it open, and the question is asked again if someone changes their column. Only the payers can answer.
5. When betting closes → image with every column, ready to be stamped at the lottery shop, and who pays for it.
6. After the last match of each day → image with hits (green), misses (red) and the leader. If the lottery has not published a result yet, ESPN's final score is used; the official one replaces it as soon as it appears.
7. When the matchday ends → final image, everyone's hits and who pays the next ticket.
8. When LAE publishes the prize breakdown → how much each winning column gets, if any.

The bot runs every 10 minutes (see Reliable schedule).

Game rules

Who pays

• The player with the fewest hits pays the next ticket (€3.75). Ties split it, with leftover cents assigned to the first player (€1.87 + €1.88).
• A player who misses the deadline counts as 0 hits.
• Counting started at matchday 16 of the 2026-27 season.

Pleno al 15 (the 15th match, where each team's goals are predicted: 0, 1, 2 or M = 3+)

• Filled in by the best player of the previous matchday; ties, or the first matchday, are drawn at random. If the previous matchday is still being played, the last finished one is used.
• It does not add hits. There is one per ticket, so it counts for any column with 14 hits.

Which Quinielas are played

• Only LaLiga ones: at least 5 matches between two top-flight teams. Champions League, Copa del Rey, etc. are skipped. Women's teams are shown with (F) and never count.

When a new matchday is announced

• Players bet on one matchday at a time, and the Pleno and who pays come from the previous one. So if the lottery publishes a matchday early, the bot waits: never while an earlier matchday is still open for betting, and, if the earlier one will finish before the new one closes, until it has finished (unless that would leave less than 48 h to bet).
• Example: next weekend's matchday published on Wednesday is announced on Monday night, right after this weekend's matchday ends, with the correct Pleno player and payer.

Overlapping matchdays

• A midweek matchday may have to be announced while the weekend one is still being played (its deadline comes first). Both are handled independently: own announcement, deadline, columns, reports and ticket; the announcement says so explicitly, and the web app always shows the matchday open for betting.

Postponements and rescheduling

• Until kickoff, fixtures are re-read on every run. If the first kickoff moves while betting is open, the deadline moves with it and the group is told.
• If a match has no result 8 hours after the last kickoff of its day, that day's report is sent anyway, naming the missing match.

Prizes

• From the official breakdown, one line per winning column: «Borja ha tenido 11 aciertos por lo que corresponde 2,22 € según el sorteo.»

Setup (about 30 minutes)

1. Supabase

1. Create a free project at https://supabase.com.
2. SQL Editor → paste supabase/schema.sql → Run.
3. Set each player's PIN privately: select set_pin(1::smallint, '....'); (ids 1–5). Never commit real PINs.
4. Generate the family key: select set_family_key(); and copy the value it returns (it is shown only once; only its hash is stored).
5. Project Settings → API Keys → Legacy anon, service_role API keys: note the URL, the anon key and the service_role key. The service_role key is secret and only goes into GitHub Secrets.

2. Telegram bot

1. Talk to @BotFather → /newbot → note the token.
2. /setprivacy → your bot → Disable (before adding it to the group), so it can see who writes and mention people.
3. Create the group, add the bot and the players, and have everyone send a message.

3. GitHub

1. Push this folder to a public repository (unlimited Actions minutes; no secrets in code).
2. Settings → Secrets and variables → Actions:
   • Secrets: SUPABASE_URL, SUPABASE_SERVICE_KEY, TELEGRAM_TOKEN, TELEGRAM_CHAT_ID, FAMILY_KEY (the value from step 1.4), BACKUP_KEY (a long made-up password that encrypts backups; store it elsewhere too, it cannot be recovered from GitHub)
   • Variable: WEB_URL (the GitHub Pages address from step 4). The family key is added to the link by the bot; keep it a secret, because Actions logs are public.
3. To find the chat and user ids, have everyone write in the group and run Actions → Bot → Run workflow → telegram-ids: it lists everyone the bot has seen writing (recorded on every run, so it works days later too). The group id is negative. Store each player's id: update players set telegram_user_id = 123456789 where name = 'Rosana';

4. Web app

1. Fill in web/config.js with the Supabase URL and anon key (public by design).
2. Settings → Pages → Source: GitHub Actions. It is published at https://<user>.github.io/<repo>/.
3. Players always open it from the bot's Hacer mi quiniela button, which carries the family key (#k=…, a URL fragment that is never sent to any server); the device then remembers it. Opened without the key it only shows «Quiniela privada».
4. Preview without a database: add ?demo to the URL. Preselect a player: ?p=5.

5. Check the data sources

Actions → Bot → Run workflow → diagnose prints the ESPN team list, the upcoming matchdays with their 15 fixtures (whether they qualify as LaLiga, and each result marked LAE or ESPN), the number of finished games ESPN reports and recent prize breakdowns. If something comes back empty, LAE changed its website and bot/lae.py needs adjusting.

6. Reliable schedule

GitHub runs scheduled workflows late when it is busy (hours, in practice), so the bot is started every 10 minutes by cron-job.org (free) through the GitHub API; the workflow's own schedule remains as a fallback.

1. GitHub → Settings → Developer settings → Fine-grained tokens → new token with access to this repository only and the permission Actions: Read and write (nothing else).
2. cron-job.org → Create cronjob:
   • URL: https://api.github.com/repos/<user>/<repo>/actions/workflows/quiniela.yml/dispatches
   • Schedule: every 10 minutes
   • Advanced → method POST, headers Authorization: Bearer <token>, Accept: application/vnd.github+json, X-GitHub-Api-Version: 2022-11-28, body {"ref":"main","inputs":{"command":"tick"}}
   • A successful call answers 204.
3. Renew the token before it expires (GitHub emails a reminder).

Backups

Supabase's free tier keeps no backups, so every Tuesday the Backup workflow exports all tables, encrypts them with BACKUP_KEY and commits backups/quiniela.json.enc; git history keeps every previous version. To restore:

openssl enc -d -aes-256-cbc -pbkdf2 -iter 200000 -in backups/quiniela.json.enc -out backup.json
python -m bot.backup restore backup.json      # needs SUPABASE_URL and SUPABASE_SERVICE_KEY

Then, in the SQL Editor: select setval('matchdays_id_seq', (select coalesce(max(id), 1) from matchdays));

Because the bot commits a backup every week, run git pull before pushing your own changes.

Maintenance

• A run fails (GitHub emails you): Actions → the red run → bot → Run, and read the error.
• Runs only every few hours: the cron-job.org trigger stopped (expired token or failed calls, shown in its history); create a new token and update the header.
• The bot stops posting: if the Telegram group became a supergroup its id changed (it starts with -100); update TELEGRAM_CHAT_ID.
• Rotate the family key (e.g. if a link leaks): select set_family_key();, update the FAMILY_KEY secret and re-send the announcement (below) so the button carries the new key.
• Re-send a matchday announcement: update matchdays set announced = false where number = 16; then Run workflow → tick.
• GitHub Actions versions: workflows use actions/checkout@v5, actions/setup-python@v6 and actions/upload-pages-artifact@v4, and force Node.js 24.
• supabase/migrations/ holds one-off scripts already applied to the production database. Fresh installs only need schema.sql.

Project structure

.github/workflows/quiniela.yml   Bot: schedule and secrets
.github/workflows/web.yml        Deploys web/ to GitHub Pages
.github/workflows/backup.yml     Weekly encrypted database backup
.github/workflows/tests.yml      Runs the simulation on every push
bot/main.py                      Orchestration: one idempotent run (tick) and the CLI
bot/rules.py                     Game rules: hits, who pays, Pleno, daily reports, league filter, prizes
bot/lae.py                       Client for the official lottery data (fixtures, results, prizes)
bot/teams.py                     LaLiga team list from ESPN and fuzzy name matching
bot/espn.py                      Live scores from ESPN while the lottery has no results yet
bot/messages.py                  Every user-facing text (Spanish)
bot/ticket.py                    Renders the ticket images (Pillow)
bot/db.py                        Supabase (PostgREST) access
bot/telegram.py                  Telegram Bot API client
bot/backup.py                    Database export and restore
web/index.html · web/config.js   Betting web app
supabase/schema.sql              Database schema, security and functions
supabase/migrations/             One-off scripts applied to the production database
tests/test_simulation.py         Offline simulation of whole matchdays
docs/                            Full guide (English and Spanish) and README images
backups/                         Encrypted database backups

Development

pip install -r requirements.txt
python -m tests.test_simulation output/   # full offline simulation; images and messages land in output/
python -m bot.main tick --dry-run output/ # real Supabase and LAE data, but nothing is sent to Telegram