✈️ Flight Tracker Bot

A production-ready Discord bot that monitors one-way flight prices from Algiers (ALG) to five destinations in Japan and Korea — Tokyo Narita (NRT), Tokyo Haneda (HND), Osaka (KIX), Seoul Incheon (ICN), and Busan (PUS) — and posts an alert in your Discord channel whenever today's lowest fare drops significantly below its recent norm.

Every day at 09:15 Africa/Algiers time, the bot fetches a ~30-day price calendar per route, stores every quote in SQLite, computes a 30-day rolling average per route, and fires a Discord embed alert when today's lowest fare is ≥ 20% below that average — with outlier rejection and a trimmed mean so one glitchy price never triggers a false alert.

---

Table of contents

1. What it does
2. How it works
3. Alert logic & statistics
4. The flight data API
5. Database schema
6. Using the bot in Discord
7. Setup guide
8. Configuration reference
9. Operations runbook
10. Project structure
11. Testing
12. Limitations & caveats

---

What it does

• Tracks 5 routes daily: ALG → NRT, HND, KIX, ICN, PUS (one-way, economy, 1 adult, prices in EUR)
• Fires price-drop alerts: rich Discord embed when today's window low is ≥ 20% under the 30-day rolling average
• Defends against false alerts: 4-layer statistics — quote sanity band, outlier exclusion, trimmed-mean baseline, optional confirmation re-fetch
• Survives real-world mess: quota tracking with a pre-run gate, retry/backoff on provider errors, crash-safe SQLite (WAL), corruption auto-recovery, pending-alert delivery with retries
• Runs itself: Dockerized, self-monitoring (healthcheck), restart-on-crash, logs to stdout with rotation
• Tells you when something's wrong: admin notices for quota exhaustion, auth failures, provider outages, routes with no data, stale upstream data

How it works

                    ┌────────────────────────────────────────────────┐
                    │              Docker container                  │
                    │                                                │
  09:15 Algiers ──► │  daily_check loop ──► tracker.run_cycle()     │
  (startup catch-up │      │                    │                   │
   if slot passed)  │      │                    ├──► RapidAPI       │
                    │      │                    │    (Google        │
                    │      │                    │     Flights 2)    │
                    │      ▼                    ▼                   │
                    │  delivery loop ──► SQLite (WAL, /data volume)  │
                    │  (every 1 min)        │                       │
                    │      │                 └── fare_quotes,        │
                    │      │                     route_day_stats,   │
                    │      ▼                     alerts, …          │
                    │  pending alerts ──► Discord embed ──► channel │
                    │  heartbeat ──► meta table (healthcheck reads) │
                    └────────────────────────────────────────────────┘

Daily cycle (one scheduled run = 5 API requests, one per route):

1. At 09:15 local (Africa/Algiers, no DST), the daily loop fires. If the bot was down at that moment, a startup catch-up runs the missing routes as soon as it boots.
2. For each route it calls the provider's price-calendar endpoint once — a single request returns the lowest fare per departure day across the whole ~30-day window (this is the trick that keeps quota usage at 5/run instead of 5 × 30).
3. Each quote is validated and stored raw; a stat row records today's window low, the cheapest departure date, and the fetch outcome.
4. The alert evaluation compares today's low against the route's 30-day baseline (see below).
5. The 1-minute delivery loop picks up any queued alert, builds the embed, sends it, and marks it sent. Failed sends retry up to 10 times before an admin notice is raised.

Alert logic & statistics

The window is lead-time-stationary: on day D it looks at departures D+1 … D+30, so a "drop" always means cheaper than usual for the same booking horizon.

An alert is decided as follows:

1. Sanity band — quotes outside €80–€12,000 are rejected outright (kills glitch prices and currency contamination).
2. Baseline — the last 30 prior daily lows for that route, excluding today, and only from days collected with the same methodology fingerprint (params_hash: provider, currency, market, class, window). Change any methodology setting and the baseline deliberately re-primes (7-day warmup) rather than comparing apples to oranges.
3. Outlier exclusion — baseline values outside [median ÷ 3, median × 3] are dropped.
4. Trimmed mean — the surviving baseline values are symmetrically trimmed 10% per tail; that mean is the "30-day rolling average" used in comparisons.
5. Drop test — pct_drop = (baseline − today_low) / baseline × 100; alert if ≥ the threshold (default 20%).
6. Warmup — no alerts until the route has ≥ 7 days of history.
7. Cooldown & deepening — after an alert, the route is quiet for 7 days, and a re-alert within that window must be at least 5 percentage points deeper than the last one.
8. Confirmation (optional, off on the free tier) — before alerting, the route is re-fetched once to confirm the low still stands.

The flight data API

Primary: Google Flights 2 (by DataCrawler) via RapidAPI — google-flights2.p.rapidapi.com.

Endpoint	Used for	Requests
GET /api/v1/getCalendarPicker	The workhorse: per-day lowest fares for the whole window in one call	1 per route per run
GET /api/v1/getPriceGraph	Fallback when a route's calendar window comes back empty (optional)	+1, only on empty
GET /api/v1/searchFlights → getBookingDetails → getBookingURL	Resolving a real checkout link for an alert (3-request chain)	+3 per sent alert

Calendar response shape (all endpoints share the envelope {status, message, timestamp, data}):

{"status": true, "message": "Success",
 "data": [{"departure": "2026-09-08", "return": null, "price": 764}, …]}

Request shape note (verified live): the calendar endpoint takes departure_id, arrival_id, start_date, end_date and the market params (currency, country_code, adults, travel_class) — sending outbound_date or trip_type makes it return a 200 with status:false "Something went wrong", which the retry logic then treats as a provider error.

Plans (hard limits, no overage):

Plan	Requests/month	What it supports
BASIC (free)	150	The daily schedule alone (5/day × ~30 days ≈ 150). Zero headroom for manual /checkflights, and the last run of long months gets skipped by the quota gate.
PRO ($12.99)	40,000	Everything on: price-graph fallbacks, confirmation re-fetches, resolved booking links, unlimited manual checks.

Quota discipline is built in: every response's x-ratelimit-requests-remaining header is recorded, the effective remaining is min(local counter, header), a run only starts if ≥ 11 requests remain (5 routes + 6 reserve), and exhaustion surfaces as a quota_low / quota_exhausted admin notice — never a crash.

Alternate provider: sky-scrapper (by apiheya) is supported via API_PROVIDER=sky-scrapper + RAPIDAPI_HOST=sky-scrapper.p.rapidapi.com. Its free tier (20 requests/month) cannot sustain 5-route daily tracking, so it's mainly useful on its paid tier or for fewer routes. Note it has no deep links at all — alerts always fall back to the static search URL.

Database schema

SQLite, WAL mode, single-writer. Seven tables in flight_tracker.db:

Table	What it holds
routes	The 5 tracked origin–destination pairs (active flag)
runs	One row per cycle: trigger, status, routes ok/failed, params snapshot
fare_quotes	Raw quotes: route, price, currency, departure_date, fetched_at, booking_link — one row per priced day per fetch
route_day_stats	The daily analysis: window low, cheapest departure, fetch/eval outcomes, baseline, pct_drop
alerts	Alert decisions and delivery state (pending → sent, ≤ 10 attempts)
system_alerts	Ops notices (quota, auth, outage, …) with delivery state
meta	Heartbeat, quota counters, schema version

Using the bot in Discord

Two slash commands (sync instantly when DISCORD_GUILD_ID is set):

Command	What it does	Cost
/checkflights	Runs a full cycle right now and replies with today's low per route	5 requests (+ profile extras)
/status	Per-route today's price, baseline progress, last run, quota remaining	Free (reads DB only)

A price-drop alert looks like:

✈️ ALG → ICN — price drop!
Today's lowest fare is €491 — 23.1% below the 30-day rolling average
(€639, 10%-trimmed, outliers excluded).

Cheapest departure: 2026-10-02
Search (free link): https://www.google.com/travel/flights?q=…

as of 2026-09-14T08:15 UTC · rolling avg over 30 days · threshold −20%

Free-tier alerts link to a hand-built one-way Google Flights search URL (booking_link_mode=static); on PRO the bot resolves a real checkout link through the 3-request chain and labels it "Book / check".

Admin notices (quota, auth failures, outages, DB corruption, …) go to DISCORD_ADMIN_CHANNEL_ID if set, otherwise to the alerts channel.

Setup guide

1. Discord bot

1. discord.com/developers/applications → New Application → Bot tab → Reset Token → copy to DISCORD_BOT_TOKEN.
2. No privileged intents are needed — leave intents default.
3. OAuth2 → URL Generator: check scopes bot and applications.commands (both are required — bot alone means no slash commands). Under Bot Permissions check View Channels, Send Messages, Embed Links. Open the generated URL and invite the bot to your server.
4. In Discord, enable Developer Mode (User Settings → Advanced), then right-click your alerts channel → Copy Channel ID → DISCORD_CHANNEL_ID; right-click the server name → Copy Server ID → DISCORD_GUILD_ID (for instant slash-command sync).

2. RapidAPI key

1. Create a RapidAPI account.
2. Subscribe on the Google Flights 2 pricing page — BASIC (free) or PRO. Subscribing is mandatory: without it every call returns 403.
3. Copy your app key (RapidAPI dashboard → Apps) → RAPIDAPI_KEY.

3. Configure and run

cp .env.example .env        # then fill DISCORD_BOT_TOKEN, DISCORD_CHANNEL_ID,
                            # DISCORD_GUILD_ID, RAPIDAPI_KEY
docker compose up -d --build
docker compose logs -f      # watch the first login + catch-up

That's it. The container is restart: unless-stopped, self-monitors via a heartbeat healthcheck, and stores the database on the named flight-data volume so it survives rebuilds.

Prefer running without Docker? pip install -r requirements.txt, set DB_PATH=./flight_tracker.db (the default), then python bot.py. There's also python tracker.py — a standalone cycle that exercises API + DB without Discord, handy for debugging the provider connection.

4. First week

Expect no alerts for the first 7 days — that's the baseline warmup, by design. /status shows each route's warmup progress (building baseline (n/7 days)). The first alert becomes possible on day 8.

Configuration reference

All settings live in .env (see .env.example for the annotated copy). Methodology changes re-prime the baseline.

Key	Default	Meaning
DISCORD_BOT_TOKEN	—	Bot token (required)
DISCORD_CHANNEL_ID	—	Channel receiving deal alerts (required)
DISCORD_ADMIN_CHANNEL_ID	—	Ops notices; empty = alerts channel
DISCORD_GUILD_ID	—	Server ID for instant slash-command sync; empty = global (~1 h)
RAPIDAPI_KEY / RAPIDAPI_HOST	— / google-flights2.p.rapidapi.com	Credentials + provider host
API_PROVIDER	google-flights2	google-flights2 or sky-scrapper (must match host)
TIMEZONE	Africa/Algiers	IANA zone for the run slot and stat dates (no DST)
RUN_TIME_LOCAL	09:15	Daily run time in that zone
TRACKED_ROUTES	ALG-NRT,ALG-HND,ALG-KIX,ALG-ICN,ALG-PUS	One-way ORIGIN-DEST IATA pairs
LEAD_DAYS / WINDOW_DAYS	1 / 30	Window = D+1 … D+30 (lead-time-stationary)
COUNTRY_CODE / CURRENCY / TRAVEL_CLASS / ADULTS	DZ / EUR / ECONOMY / 1	Market context for pricing
DROP_THRESHOLD_PCT	20	Alert when today's low is ≥ this % below baseline
MIN_HISTORY_DAYS	7	Warmup before first alert
BASELINE_TRIM_PCT	10	Trim per tail when averaging the baseline
SERIES_OUTLIER_FACTOR	3.0	Baseline values outside median÷f … median×f excluded
ALERT_COOLDOWN_DAYS	7	Quiet period after an alert
RE_ALERT_DEEPEN_PCT	5	Extra percentage points required to re-alert in cooldown
FARE_FLOOR_EUR / FARE_CEILING_EUR	80 / 12000	Quote sanity band
CONFIRM_BEFORE_ALERT	1 → 0 on free	Re-fetch to confirm a drop (+1 request per candidate)
STALE_STREAK_RUNS	3	Tag alerts after N identical price vectors
USE_PRICE_GRAPH_FALLBACK	1 → 0 on free	Retry empty windows via Price Graph (+1 request)
BOOKING_LINK_MODE	resolve → static on free	resolve (real checkout link, +3 req/alert) · static (free search URL) · off
PLAN_MONTHLY_LIMIT	40000 → 150 on free	Local quota budget; true limit still enforced by the provider header
QUOTA_RESERVE_REQUESTS	6	Requests that must remain after a run for it to start
REQUEST_TIMEOUT_SECONDS / RETRY_MAX_ATTEMPTS / RETRY_BACKOFF_SECONDS / INTER_CALL_DELAY_SECONDS	20 / 3 / 5 / 2	HTTP behavior; 2 s spacing between provider calls
DB_PATH	./flight_tracker.db	SQLite path — compose pins /data/flight_tracker.db
LOG_LEVEL	INFO	stdout logging

Free (BASIC) profile — the four overrides marked above (CONFIRM_BEFORE_ALERT=0, USE_PRICE_GRAPH_FALLBACK=0, BOOKING_LINK_MODE=static, PLAN_MONTHLY_LIMIT=150) keep a day's scheduled usage at exactly 5 requests. PRO runs everything on and still uses a trivial ~200 requests/month.

Operations runbook

docker compose ps                       # health (heartbeat < 10 min old)
docker compose logs -f flight-bot       # follow logs (also: logs --since 1h)
docker compose restart                  # clean restart
docker compose down                     # stop (volume survives; -v would delete data!)

• Quota exhaustion — the bot skips runs once fewer than 11 requests remain and posts a quota_low/quota_exhausted notice. It resumes automatically at the next monthly reset.
• Backup — docker compose stop && docker cp flight-tracker-bot:/data/flight_tracker.db ./backup.db && docker compose start (stopping first checkpoints the WAL cleanly).
• Corruption — detected automatically at boot: the damaged file is quarantined to /data/quarantine/<timestamp>/ inside the volume, a fresh DB is created, and a db_corrupt notice is posted. Your data lives in the named volume flight-data (docker volume inspect flight_tracker_app_flight-data).
• Shutdown — SIGTERM triggers a bounded graceful shutdown that drains in-flight work before closing the DB, so a docker compose stop never cuts a write mid-flight.
• Healthcheck — the container reports healthy while the 1-minute heartbeat stays fresh; unhealthy means the delivery loop stopped ticking.

Project structure

flight_tracker_app/
├── bot.py             Discord surface: loops, slash commands, embeds, shutdown
├── tracker.py         Provider adapters, validation, statistics, alert decision
├── database.py        SQLite schema, single-writer WAL layer, quota counters
├── healthcheck.py     Container HEALTHCHECK (reads heartbeat age)
├── test_offline.py    49 offline tests — no network, no Discord needed
├── requirements.txt   discord.py, requests, python-dotenv, tzdata
├── Dockerfile         python:3.11-slim, non-root, outbound-only
├── docker-compose.yml one service + named volume, healthcheck, log rotation
├── .env.example       Annotated configuration template
├── plan.md            Full design document (architecture rationale)
└── flight_tracker.db  Local-run DB (Docker uses the named volume instead)

Testing

python test_offline.py     # 49 tests, fully offline

The suite covers the alert statistics end-to-end (real drops alert, warmup suppresses, outliers excluded, trimmed mean, cooldown/deepening, same-day dedup), quote validation, schema and supersede rules, quota accounting, the run-lock, config validation, and healthcheck paths. All provider and Discord interaction is stubbed — the tests never touch the network or your quota.

Limitations & caveats

• One-way, economy, 1 adult, EUR — that's the fixed lens; round-trips and multi-city aren't modeled.
• Aggregator prices — figures come from the provider, which mirrors Google Flights; a price can move between alert and booking. Treat alerts as "check this now," not a reservation.
• Sparse routes — ALG→PUS often has only a handful of priced days in a window (6 of 30 observed on day one); its stats are computed over what exists.
• Free-tier link quality — static mode links to a Google Flights search URL, not a pre-filled checkout.
• 7-day warmup after any methodology change — changing routes/window/currency re-primes baselines on purpose.
• First alert possible on day 8 — by design, not a bug.

---

Built with RapidAPI (Google Flights 2 by DataCrawler), discord.py, SQLite, and Docker. Design rationale and the full specification live in plan.md.