Skip to content

plan.md

Latest commit

 

History

History
559 lines (451 loc) · 52.5 KB

plan.md

File metadata and controls

559 lines (451 loc) · 52.5 KB
Raw

Flight Tracker Bot — Architecture & Implementation Plan

Status: draft for review · Date: 2026-09-07 · Scope: v1.0

A production-ready Python Discord bot that tracks daily one-way flight prices from Algiers (ALG) to Tokyo Narita (NRT), Tokyo Haneda (HND), Osaka (KIX), Seoul Incheon (ICN), and Busan (PUS), stores them in SQLite, maintains a 30-day rolling average per route, and sends a Discord Embed alert when today's lowest fare drops ≥ 20% below that average.

Decisions locked with the owner: one-way fares · window + daily-low strategy (each day, sample the cheapest departure within the next 30 days) · EUR · Google Flights 2 as default provider (Sky Scrapper adapter included).

1. How this plan was produced

Two research agents verified both RapidAPI providers against their live hub documentation, pricing pages, and ~10 independent consumer codebases (GitHub wrappers, MCP trackers, clones). Three independent architecture proposals (production-reliability, minimal-complexity, and data-correctness lenses) were then synthesized into this plan. Raw research and design artifacts are kept in .research_gf2/ (gitignored) as implementation reference.

2. Verified API facts (these correct several common assumptions)

The most important finding: the endpoint names you'd guess for both providers don't exist.

Google Flights 2 (DataCrawler) — google-flights2.p.rapidapi.com — default provider

Fact Detail
Search endpoint GET /api/v1/searchFlights — one-way and round-trip share it (omit return_date for one-way). There is no searchFlightsOneWay / searchFlightsRoundTrip / searchEverywhereQuest, no rowDataIndex/hl/gl params.
Daily-low workhorse GET /api/v1/getCalendarPicker — one call per route returns the cheapest fare per departure date for the whole window (up to ~60 days): data[] = [{departure: "YYYY-MM-DD", price: <int>}, ...]. This is exactly the daily-low series we need, at 5 requests per run instead of 5×N.
Fallback GET /api/v1/getPriceGraph — identical data[] shape; used when the calendar window comes back empty.
Envelope {"status": true/false, "message": ..., "timestamp": <epoch-ms>, "data": ...} — clean JSON. status:false inside HTTP 200 = provider-side scrape failure.
Prices Integers in the requested currency (whole euros when currency=EUR). No decimals, no protobuf units/nanos.
Params departure_id, arrival_id (IATA), outbound_date, start_date, end_date (all YYYY-MM-DD), trip_type=ONE_WAY, currency, country_code (market context; DZ = Algeria), adults='1' (string), travel_class=ECONOMY. Docs conflict on whether outbound_date is required for the calendar endpoint — we pass it always.
Booking links No URL on flight items. A bookable link is a 3-request chain: searchFlights → per-item booking_token → getBookingDetails?booking_token=... (partner list) → getBookingURL?token=... (plain URL string). ~2–3 billed requests per link.
Pagination Token-based (next_token), not index-based. We never need it (calendar calls).
Quota BASIC 150 req/month HARD limit; PRO $12.99 = 40,000/mo. Practical throttle ~250/min. Every response carries x-ratelimit-requests-remaining etc.
Error modes (verified live) 403 {"message":"You are not subscribed to this API."} (valid key, no subscription — the most common day-one failure); 429 = per-minute throttle or monthly hard-limit exhaustion or missing key header (all three present as 429 — disambiguate via Retry-After + x-ratelimit-requests-remaining); 5xx = upstream scrape faults; HTTP 200 with empty data = legitimate "no inventory" (expected frequently for ALG→PUS), not an error.
Latency Live aggregation: ~6.4 s average, up to 10 s cold. Client timeout must be ≥ 20 s.

Sky Scrapper (apiheya) — sky-scrapper.p.rapidapi.com — secondary adapter

Fact Detail
No /flights/search-one-way — one-way is searchFlights without returnDate.
Daily-low workhorse GET /api/v1/flights/getPriceCalendar with originSkyId, destinationSkyId, fromDate. SkyIds are plain IATA codes for these airports — the entityId two-step lookup is not needed on the calendar path. Response: data.flights.days[] = [{day, group, price(float)}] (defensive fallback path: data.days[]).
Prices itineraries[].price.raw — already a plain float. No price_raw.units/nanos, no deep links anywhere in this API (booking links are a Google-Flights-2-only feature; Sky Scrapper alerts fall back to a free web-search URL).
Quota Free tier = 20 req/month HARD (≈ 4 runs); PRO $9.99 = 10,600/mo.
EUR Supported (currency=EUR).

Booking-link URLs when the chain fails

We always have a zero-cost fallback: a hand-built one-way Google Flights search URL (https://www.google.com/travel/flights?q=Flights%20from%20ALG%20to%20NRT%20on%20YYYY-MM-DD%20one%20way) — origin, destination, date, and the one-way trip type are all encoded (Google Flights otherwise defaults to the viewer's market trip type, usually round-trip, showing prices that differ from the alert's one-way fare) — so every alert carries some actionable link.

All RapidAPI headers are parameterized: RAPIDAPI_KEY → X-RapidAPI-Key, RAPIDAPI_HOST → X-RapidAPI-Host, set once on a shared requests.Session.

3. Architecture overview

┌────────────────────── 09:15 Africa/Algiers, daily ──────────────────────┐
│ bot.py  @tasks.loop daily_check                                         │
│   └─ asyncio.to_thread(tracker.run_cycle('scheduled'))                   │
│        ├─ stat_date D = today's Algiers date (computed ONCE per run)     │
│        ├─ pre-run gates: quota check (meta), orphan-run cleanup          │
│        ├─ per active route (isolated try/except, 2 s pacing):            │
│        │    getCalendarPicker → validate each quote                      │
│        │    (ISO date, numeric, >0, floor≤price≤ceiling, in window)      │
│        │    → fare_quotes (RAW layer: every quote, valid or not)         │
│        │    → low = min(valid in-window) → route_day_stats (data point)  │
│        │    → empty window? retry once via getPriceGraph (if enabled)    │
│        ├─ evaluate each route (database.get_baseline_series →            │
│        │    warmup guard → outlier band → trimmed mean → ≥20% test →     │
│        │    cooldown → confirmation re-fetch) → alerts row (pending)    │
│        └─ system notices (quota / auth / outage / stale / dead routes)  │
└──────────────────────────────────────────────────────────────────────────┘
                                       │
┌────── delivery loop @tasks.loop(minutes=1) ──────────────────────────────┐
│ write heartbeat → meta │ drain pending alerts: resolve booking link      │
│ (if mode=resolve + quota headroom) → send Embed → mark sent │            │
│ retry ≤10 with backoff │ drain pending system_alerts → admin channel     │
└──────────────────────────────────────────────────────────────────────────┘

Design posture (synthesized from three independent designs):

  • Simplicity spine — exactly the specified file set (database.py, tracker.py, bot.py) plus one ~20-line healthcheck.py for Docker. No framework, no ORM, no cogs, no adapter classes — the two providers are two plain functions behind one signature, picked once from API_PROVIDER.
  • Reliability machinery — evaluation is decoupled from delivery (at-least-once send with DB truth, ≤10 retries); a 1-minute heartbeat loop drives the Docker healthcheck and delivery; every API call is classified (retryable / throttle / fatal-auth / quota-exhausted) with bounded retries and per-route isolation; one SQLite writer under WAL with short per-route transactions.
  • Data-correctness core — the data point is a lead-time-stationary market index, not a raw quote; a full raw-quote layer makes every alert recomputable; four stacked defenses (quote band → series outlier band → trimmed mean → confirmation re-fetch) stand between a glitched quote and a false alert; a params_hash methodology fingerprint prevents phantom drops after any config change.

4. File structure

File Purpose
requirements.txt discord.py>=2.4,<3 · requests>=2.32,<3 · python-dotenv>=1.0,<2 · tzdata>=2024.1 (mandatory: python:3.11-slim ships no system zoneinfo — without it ZoneInfo("Africa/Algiers") crashes at startup). Everything else is stdlib (sqlite3, zoneinfo, statistics, hashlib, json, logging, asyncio).
.env.example Every knob, grouped + commented (full contents in §11).
database.py The only module that touches sqlite3. Connection pragmas, idempotent DDL + route seeding, and typed helpers — including get_baseline_series() implementing the 30-day rolling average. connect() mkdirs the DB's parent directory (via os.path.abspath) before the first connection so a bad path fails loudly at startup. No Discord imports.
tracker.py RapidAPI access + the alert engine. Both provider adapters (fetch_daily_lows(origin, destination, start_date, end_date, ...) -> dict[date, price]), the shared HTTP client with the full error taxonomy, quote validation, run_cycle(), evaluate_route(), resolve_booking_link(). No Discord imports. Runnable standalone (python tracker.py) to smoke-test API + DB without Discord.
bot.py Discord surface only: client (no privileged intents — slash commands don't need Message Content Intent), the two @tasks.loops, /checkflights + /status slash commands, Embed rendering, graceful shutdown. Validates all env config fail-fast at startup and passes explicit arguments into database/tracker (no shared config module needed).
healthcheck.py Docker HEALTHCHECK: reads meta.heartbeat_utc read-only; exit 0 if younger than 10 minutes.
Dockerfile python:3.11-slim-bookworm, non-root, tzdata, layered caching.
docker-compose.yml Single service, named volume, healthcheck, log rotation.
.dockerignore / .gitignore Keep .env, *.db*, .git, .research_gf2/, litellm_config.yaml, __pycache__/ out of image and VCS.
plan.md This document.

5. Database schema (SQLite, schema_version = 1)

All calendar dates (run_date, stat_date, departure_date, alert_date) are YYYY-MM-DD strings computed in Africa/Algiers — they sort lexicographically and are immune to clock/DST games. Machine timestamps (*_utc) are ISO-8601 UTC. SQLite has no leap seconds, and Algeria has no DST, so date-keyed logic is stable by construction.

-- Connection pragmas applied on EVERY open (in database.py):
PRAGMA journal_mode = WAL;      -- crash-safe; readers never block the writer
PRAGMA synchronous = NORMAL;    -- survives process crash; host power loss may lose last checkpoint (accepted)
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;

-- Key/value state: schema_version, heartbeat_utc, quota counters, stale-streak scratch
CREATE TABLE IF NOT EXISTS meta (
  key   TEXT PRIMARY KEY,
  value TEXT NOT NULL
);

-- Route registry; seeded idempotently from TRACKED_ROUTES (INSERT OR IGNORE)
CREATE TABLE IF NOT EXISTS routes (
  route_id    INTEGER PRIMARY KEY AUTOINCREMENT,
  origin      TEXT NOT NULL CHECK (length(origin) = 3 AND origin = upper(origin)),
  destination TEXT NOT NULL CHECK (length(destination) = 3 AND destination = upper(destination)),
  is_active   INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1)),
  created_utc TEXT NOT NULL,
  UNIQUE (origin, destination)
);

-- One row per execution of the cycle
CREATE TABLE IF NOT EXISTS runs (
  run_id        INTEGER PRIMARY KEY AUTOINCREMENT,
  run_date      TEXT NOT NULL CHECK (run_date = date(run_date)),
  trigger_kind  TEXT NOT NULL CHECK (trigger_kind IN ('scheduled','catchup','manual')),
  started_utc   TEXT NOT NULL,
  finished_utc  TEXT,
  status        TEXT NOT NULL DEFAULT 'running'
                CHECK (status IN ('running','completed','partial','skipped',
                                  'quota_exhausted','auth_error','interrupted','failed')),
  routes_ok     INTEGER NOT NULL DEFAULT 0,
  routes_failed INTEGER NOT NULL DEFAULT 0,
  params_hash   TEXT NOT NULL,   -- methodology fingerprint (see §7)
  params_json   TEXT NOT NULL,   -- full param set of this run, for audit
  error         TEXT
);
CREATE INDEX IF NOT EXISTS idx_runs_date ON runs (run_date, run_id);

-- RAW LAYER: every quote ever returned, valid or not. Nothing is ever overwritten.
-- This is the spec's quote table, columns normalized. Spec -> schema map:
--   route -> route_id + routes(origin, destination) (normalized route identity)
--   price -> price_eur (one price column; currency is CHECKed to 'EUR')
--   currency -> currency (unchanged) · departure_date -> departure_date (unchanged)
--   fetched_at -> fetched_utc (machine timestamps are UTC, per the §5 header note)
--   booking_link -> alerts.booking_url (one per SENT alert, not per quote — the link
--     is resolved per sent alert's winning route+date at delivery time (§8/§9);
--     the triggering quote is identifiable via route_id + stat_date +
--     departure_date = alerts.best_departure_date + fetch_kind='canonical')
CREATE TABLE IF NOT EXISTS fare_quotes (
  id             INTEGER PRIMARY KEY AUTOINCREMENT,
  run_id         INTEGER NOT NULL REFERENCES runs(run_id),
  route_id       INTEGER NOT NULL REFERENCES routes(route_id),
  stat_date      TEXT NOT NULL CHECK (stat_date = date(stat_date)),
  departure_date TEXT NOT NULL CHECK (departure_date = date(departure_date)),
  price_eur      REAL CHECK (price_eur IS NULL OR price_eur > 0),
  currency       TEXT NOT NULL DEFAULT 'EUR' CHECK (currency = 'EUR'),
  fetch_kind     TEXT NOT NULL CHECK (fetch_kind IN ('canonical','confirmation','manual')),
  source         TEXT NOT NULL CHECK (source IN ('calendar','price_graph')),
  is_valid       INTEGER NOT NULL DEFAULT 0 CHECK (is_valid IN (0, 1)),
  invalid_reason TEXT,          -- 'below_floor','above_ceiling','bad_date','non_numeric','out_of_window'
  fetched_utc    TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_quotes_route ON fare_quotes (route_id, stat_date, fetch_kind);

-- THE DATA POINT: one row per (route, Algiers day). Holds the fetch outcome and the
-- full evaluation audit, so any third party can recompute an alert's numbers.
CREATE TABLE IF NOT EXISTS route_day_stats (
  route_id            INTEGER NOT NULL REFERENCES routes(route_id),
  stat_date           TEXT NOT NULL CHECK (stat_date = date(stat_date)),
  params_hash         TEXT NOT NULL,
  window_start        TEXT NOT NULL,   -- D + LEAD_DAYS
  window_end          TEXT NOT NULL,   -- window_start + WINDOW_DAYS - 1
  quotes_in_window    INTEGER NOT NULL DEFAULT 0,
  raw_min_eur         REAL,             -- pre-validation min (audit only)
  daily_low_eur       REAL CHECK (daily_low_eur IS NULL OR daily_low_eur > 0),  -- NULL = no data (NEVER 0)
  best_departure_date TEXT,
  fetch_time_kind     TEXT NOT NULL CHECK (fetch_time_kind IN ('scheduled','off_schedule')),
  fetch_outcome       TEXT NOT NULL CHECK (fetch_outcome IN
                        ('ok','empty','http_error','provider_error','invalid',
                         'throttled','quota_exhausted','skipped')),
  fetch_error         TEXT,
  -- evaluation audit (written by the once-daily evaluation pass):
  baseline_eur        REAL,
  plain_mean_eur       REAL,
  pct_drop            REAL,
  n_prior_days         INTEGER,
  n_outliers_excluded  INTEGER,
  n_trimmed_per_tail   INTEGER,
  eval_outcome         TEXT CHECK (eval_outcome IN
                        ('no_alert','alert_sent','suppressed_warmup','suppressed_cooldown',
                         'suppressed_no_confirm','suppressed_methodology_change',
                         'no_data_today','error')),
  stale               INTEGER NOT NULL DEFAULT 0,
  evaluated_utc       TEXT,
  PRIMARY KEY (route_id, stat_date)
);
CREATE INDEX IF NOT EXISTS idx_stats_series
  ON route_day_stats (route_id, stat_date) WHERE daily_low_eur IS NOT NULL;

-- Sent-alert delivery state; UNIQUE(route, day) = hard same-day dedup
CREATE TABLE IF NOT EXISTS alerts (
  alert_id            INTEGER PRIMARY KEY AUTOINCREMENT,
  route_id            INTEGER NOT NULL REFERENCES routes(route_id),
  stat_date           TEXT NOT NULL CHECK (stat_date = date(stat_date)),
  low_eur             REAL NOT NULL CHECK (low_eur > 0),
  best_departure_date TEXT,
  baseline_eur        REAL NOT NULL CHECK (baseline_eur > 0),
  pct_drop            REAL NOT NULL,
  confirmed_low_eur   REAL,      -- the confirmation re-fetch reading
  stale               INTEGER NOT NULL DEFAULT 0,
  booking_url         TEXT,
  booking_kind        TEXT CHECK (booking_kind IN ('resolved','static','none')),
  discord_message_id  TEXT,
  status              TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','sent','failed')),
  attempts            INTEGER NOT NULL DEFAULT 0,
  created_utc         TEXT NOT NULL,
  sent_utc            TEXT,
  UNIQUE (route_id, stat_date)
);
CREATE INDEX IF NOT EXISTS idx_alerts_pending ON alerts (status) WHERE status = 'pending';

-- Ops notices (auth/quota/outage/stale/failed sends/db corruption), deduped by (kind, day)
CREATE TABLE IF NOT EXISTS system_alerts (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  kind        TEXT NOT NULL CHECK (kind IN ('auth_error','quota_low','quota_exhausted',
                       'provider_outage','route_no_data','stale_data','discord_send_failed',
                       'db_corrupt')),
  alert_date  TEXT NOT NULL CHECK (alert_date = date(alert_date)),
  detail      TEXT,
  status      TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','sent','failed')),
  created_utc TEXT NOT NULL,
  UNIQUE (kind, alert_date)
);

Table-by-table rationale (7 tables, each earns its place):

  • meta — schema version, the heartbeat the Docker healthcheck reads, monthly quota counters (local count cross-checked against provider headers).
  • routes — normalized route identity; is_active lets you disable a route without losing history.
  • runs — audit trail: what ran, when, triggered how, with which exact methodology (params_json).
  • fare_quotes — the raw layer. Storing every quote (even validation failures, flagged with invalid_reason) makes every alert recomputable from first principles; the spec's booking_link lives one table over, on alerts.booking_url, filled at resolve time only for sent alerts (see the spec column map in the DDL comment above).
  • route_day_stats — the canonical data point + fetch outcome + evaluation audit in one row: "why didn't I get an alert today?" is always answerable.
  • alerts — delivery state machine (pending → sent/failed), same-day dedup via UNIQUE(route_id, stat_date), booking link (the spec's booking_link, §5) + Discord message id.
  • system_alerts — ops notices with a hard cap of one per kind per day (no 3 a.m. spam when the provider is down).

Volume is trivial for SQLite (~110k quote rows/year; sub-millisecond queries). Single-writer discipline: exactly one connection, check_same_thread=False, guarded by a lock; every write is a short explicit transaction, never held open across a network call; one process only — never --scale this service (a second writer on the same volume is the one supported way to corrupt the DB; WAL + busy_timeout only make local smoke-tests safe).

Canonical-supersede rule for route_day_stats upserts (makes every re-run idempotent):

  1. No existing row for (route, day) → insert.
  2. Existing row has NULL daily_low_eur and the incoming fetch produced a low → replace (a failed morning fetch doesn't permanently kill the day's data point).
  3. Existing row is off_schedule (e.g. a catchup/manual run filled the day) and the incoming fetch is scheduled → replace (the consistent-hour reading wins).
  4. Otherwise (a scheduled/canonical row exists) → first-canonical-wins: the stat is left untouched; the new fetch lands only in the raw layer.

6. The data point & sampling protocol

A data point is not one quote. It is the route-day market index:

low(route, D) = min EUR fare across all VALID quotes for departures
                within the sliding window W(D) = [D+LEAD_DAYS, D+LEAD_DAYS+WINDOW_DAYS-1]
                defaults: LEAD_DAYS=1, WINDOW_DAYS=30  →  [D+1, D+30]
  • Lead-time stationarity: the window always spans exactly departure lead times 1..30. Each day, the lead-1 date that fell out is replaced by a fresh lead-30 date — the index always measures "cheapest departure within the next month," with no composition drift, and departed flights can never enter the window.
  • One canonical observation per route per Algiers calendar day. The daily run fires at RUN_TIME_LOCAL (default 09:15, Africa/Algiers): far from midnight (no date-straddling), a consistent sampling hour (removes intra-day price-cycle bias), and the window is unambiguously future-dated in every timezone.
  • Explicit dates always — we send start_date/end_date on every call and never rely on the API's own "today" default, whose timezone we don't control.
  • No backfill, ever. Fares are live-scraped and stale within minutes; a missed day is a gap (NULL), never interpolated, never filled from "price history" endpoints (those return today's quotes, not history). Gaps simply shrink the baseline sample; the min-history guard absorbs them. A missing day biases the low upward (min over a subset), i.e. against false alerts — the safe direction.
  • Empty is data. ALG→PUS legitimately returns an empty calendar on many dates (Google surfaces little inventory for that O&D). That's fetch_outcome='empty', a NULL daily low, zero retries — never an error, never 0.
  • params_hash = sha256 over {provider, currency, country_code, travel_class, lead_days, window_days, adults}. Stored on every run and every stat row. Baselines are computed only over same-fingerprint days — change any methodology knob and the series re-primes (suppressed_methodology_change during the new warmup) instead of manufacturing phantom drops by comparing incomparable data.

7. Alert algorithm (the 20%-drop logic, exact)

Per route, per stat_date D, evaluated once daily from the canonical stat row. All comparisons at full precision; the threshold test is strict (>= 20.0; 19.999 does not alert). Defaults in §11 are assumed.

Tier 1 — quote validation (applied to every (departure_date, price) the adapter returns, before any min): departure strict-parses as ISO YYYY-MM-DD (never guessed); price numeric, finite, > 0; FARE_FLOOR_EUR <= price <= FARE_CEILING_EUR (80–12,000 EUR: nothing real flies ALG→East-Asia under ~80 EUR, and the ceiling instantly quarantines DZD-scale contamination, ~146× EUR). Invalid quotes are persisted with is_valid=0 + reason, never enter the min. Derive daily_low_eur = min over valid in-window quotes; best_departure_date = earliest date achieving the min (deterministic tie-break). Record raw_min_eur (pre-validation, audit) and quotes_in_window.

Tier 2 — series outlier exclusion. Baseline input H = daily_low_eur from route_day_stats for stat_date ∈ [D-30, D-1], daily_low_eur NOT NULL, same params_hash. Let n = |H|.

Guards and evaluation, in order (short-circuit, every outcome recorded in eval_outcome):

  1. daily_low_eur IS NULL → no_data_today. Done.
  2. n < MIN_HISTORY_DAYS (7) → suppressed_warmup. /status shows "building baseline (k/7 days)". Counts distinct prior stat dates, not fetches.
  3. Median band: m = median(H); exclude values outside [m / SERIES_OUTLIER_FACTOR, m × SERIES_OUTLIER_FACTOR] (3.0, symmetric so the estimator stays unbiased). Exclusions counted and logged. If remaining n' < 7 → suppressed_warmup.
  4. 30-day rolling average (robust): k = floor(n' × BASELINE_TRIM_PCT / 100) (10% ⇒ k=0 below 10 days, 1 for 10–19, 2 for 20–29, 3 at n=30). Sort ascending, drop the k lowest and k highest; baseline = mean of the middle. The plain (untrimmed) mean is computed and stored for audit but is deliberately not a second trigger — an AND-check on it would suppress real drops exactly when low-side glitches deflate the raw mean, which is when the trimmed statistic is the more correct one. This is the spec's "30-day rolling average," hardened: a plain mean is one glitched 9,999-EUR day away from poisoning every alert for a month; a median isn't an average; a 10%-trimmed mean is both.
  5. pct_drop = (baseline - low) / baseline × 100. If < DROP_THRESHOLD_PCT → no_alert.
  6. Cooldown: if an alert was sent for this route within ALERT_COOLDOWN_DAYS (7 Algiers days) and pct_drop < last_sent_pct_drop + RE_ALERT_DEEPEN_PCT (5 pp) → suppressed_cooldown. A materially deeper collapse re-alerts immediately; a flat cheap regime does not spam.
  7. Tier 4 — confirmation re-fetch (CONFIRM_BEFORE_ALERT=1): one fresh calendar fetch for the route (quotes stored with fetch_kind='confirmation'; they never replace the canonical point). Fire only if the confirmed low also satisfies ≥ 20% against the same baseline. Confirmation failing/erroring → suppressed_no_confirm (if the drop is real it re-fires on the next day's run naturally). Cost: +1 request per alert candidate — rare, budgeted.
  8. Candidate survives → INSERT INTO alerts ... ON CONFLICT DO NOTHING (same-day crash-safe dedup; if a row already exists, skip) → status pending; eval_outcome='alert_sent' reserved for after delivery confirms (the evaluation row meanwhile carries the decision).
  9. Audit row: every route-day gets its numbers — baseline_eur, plain_mean_eur, pct_drop, n_prior_days, n_outliers_excluded, n_trimmed_per_tail, eval_outcome, evaluated_utc — so "why no alert?" and "was this alert right?" are always answerable from the DB alone.

Stale-data tagging: if a route's full price vector is identical for STALE_STREAK_RUNS (3) consecutive runs, that day's stat is tagged stale=1; an alert on a stale day carries a "(possibly stale upstream data)" caveat and a stale_data system notice fires (deduped). If all five routes come back empty/failed in one run → provider_outage notice (don't let a dead provider look like "no deals"). A route with 3+ consecutive NULL stat days → route_no_data notice.

First-week behavior, explicitly: days 1–7 — no alerts possible, /status shows warmup progress; days 8–30 — alerts possible off the available history (trimming engages at n≥10; below that tier-1 band validation — always on — is the primary glitch filter); day 31+ — full 30-day window modulo gaps.

8. Scheduling & delivery (discord.py discord.ext.tasks)

Two loops, both @tasks.loop (the required scheduling mechanism), both started once in setup_hook, both with their entire tick body wrapped in try/except Exception (a raising tasks loop cancels itself — these loops are immortal):

  1. daily_check — @tasks.loop(time=[datetime.time(9, 15, tzinfo=ZoneInfo("Africa/Algiers"))]) (parsed from RUN_TIME_LOCAL). Tz-aware time works regardless of the container's UTC clock. Tick: guard is_ready() → asyncio.to_thread(tracker.run_cycle, 'scheduled') (the whole sync cycle runs in a worker thread — 5 calls × up to 20 s would otherwise block the event loop ~40+ s and drop the Discord gateway).
  2. delivery_loop — @tasks.loop(minutes=1). Tick: asyncio.to_thread(delivery_tick) — heartbeat write to meta (this is what the Docker healthcheck reads — process liveness, deliberately not data freshness: an API outage must not trigger a restart loop; it surfaces via the admin channel), pending-alerts drain, and pending system-notice drain all run in a worker thread; only the Embed sends (await channel.send(...)) stay on the event loop, per alert as resolve (thread) → send (loop) → mark_sent + discord_message_id (thread). Same rationale as daily_check: in the default BOOKING_LINK_MODE=resolve, §9's chain is 3 sequential blocking requests calls (× up to 20 s × retries, plus the 429 branch's ≤60 s wait) per alert — and one market-wide shock can leave all five routes pending in a single tick. On the event loop that blocks it for tens to hundreds of seconds, dropping the Discord gateway heartbeat, stalling the slash commands, and starving the daily_check tick; the wrap-everything try/except keeps the loop alive but cannot unblock it.

Exactly-once semantics are DB properties, not memory assumptions:

  • The stat row PK (route_id, stat_date) + the canonical-supersede rule (§5) make any re-run — crash, restart, manual — idempotent.
  • The alerts UNIQUE(route_id, stat_date) + ON CONFLICT DO NOTHING make a same-day double-alert impossible even across a crash-and-retry.
  • Run serialization (concurrent triggers): the three run_cycle entry points — the daily_check tick, startup catch-up, and /checkflights — each run on their own worker thread (to_thread), and the idempotence above is about sequential re-runs: DB PKs dedupe rows but cannot stop two concurrent cycles spending quota against each other. A module-level threading.Lock in tracker.py guards run_cycle, acquired non-blocking at entry. If held: scheduled/catchup return immediately without writing a runs row; manual returns at once and /checkflights replies "a run is already in progress — try again in a minute". Lock order is always run-lock → §5 connection lock (never reversed), so no deadlock; the standalone python tracker.py path is unaffected.
  • Catch-up at startup (setup_hook/on_ready): if today's Algiers stat_date has no stat rows (bot was down at 09:15) and local time is past the slot → run once with trigger='catchup', marked off_schedule (per §5 rule 3, a later canonical run would supersede it — none will come today, so it stands). If rows exist → no-op. An orphaned runs row stuck in running from a killed process → marked interrupted; per-route commits mean completed routes are already durable, and the catch-up rule refetches only routes whose stat row is absent or whose fetch_outcome is retryable (http_error, provider_error, invalid, throttled) — never ok/empty rows.
  • Missed days while the container was down: skipped, no backfill (§6).

Delivery state machine: pending → resolve booking link (see §9) → send Embed → sent + discord_message_id. A send failure increments attempts, retries next tick; after 10 attempts → failed + one discord_send_failed notice. A crash between send and mark_sent can at worst duplicate one identical message once (accepted; at-most-duplicate chosen over at-most-lost).

Graceful shutdown: SIGTERM/SIGINT → stop loops → finish the current short transaction → PRAGMA wal_checkpoint(TRUNCATE) → close connection → bot.close(). Bounded by a 20 s asyncio timeout; compose gives 30 s stop_grace_period before SIGKILL. WAL makes a SIGKILL at any instant a recoverable non-event.

Slash commands (no privileged gateway intents required — slash commands don't need Message Content Intent):

  • /checkflights (required by the spec) — runs the cycle immediately (trigger='manual', sync via to_thread). Replies with today's fresh prices per route + each route's baseline state. Series-safe by construction: the manual fetch lands in the raw layer, the stat row follows the supersede rule (fills the day only if nothing canonical exists), and alert evaluation reads the stat row — so a manual run can never double-alert or contaminate the series.
  • /status — per route: last stat (low, best date, warmup k/7, eval outcome), quota remaining, last run status, pending/failed alerts.

Alert Embed (rich, per spec): title ALG → Tokyo Narita (NRT) — price drop!; fields for today's low (€), the rolling average it's compared against (with n and "10%-trimmed, outliers excluded" footnote), the drop %, cheapest departure date, a booking link (resolved or static fallback), fetched-at Algiers timestamp; green accent for deals (amber + stale caveat when tagged).

9. RapidAPI adapter design (tracker.py)

Interface — two plain functions behind one signature, selected once at startup from API_PROVIDER (the system's only abstraction, present because provider switchability is a requirement):

def fetch_daily_lows(origin: str, destination: str,
                     start_date: str, end_date: str,
                     session: requests.Session, cfg) -> dict[str, float]
    # 'YYYY-MM-DD' -> lowest EUR fare the provider quotes for that departure date.
    # {} (HTTP 200, status true, no rows) = no inventory -> NULL stat, NOT an error.
    # Raises AdapterError (classification in .kind) on hard failure after the retry policy.

google-flights2 adapter (default). GET /api/v1/getCalendarPicker with departure_id, arrival_id, outbound_date=start_date (passed despite docs conflict), start_date, end_date, trip_type=ONE_WAY, currency=EUR, country_code, adults='1', travel_class (no language_code — the calendar endpoints don't document it, and the response is only {departure, price}). Parse: require status true; data a list of {departure, price}; per-row validation (§7 tier 1). If the window comes back empty and USE_PRICE_GRAPH_FALLBACK=1 → one retry via GET /api/v1/getPriceGraph (same shape, source='price_graph' recorded). Empty 200s are never retried further.

sky-scrapper adapter. GET /api/v1/flights/getPriceCalendar with originSkyId, destinationSkyId, fromDate, currency=EUR (sent explicitly on every call, mirroring the google-flights2 adapter — the interface contract is EUR-denominated lows; currency is already part of the params_hash, so this is purely additive). SkyIds are plain IATA codes on this path; the entityId two-step is documented as the contingency if the calendar ever comes back empty across all routes, not built preemptively. Parse: data.flights.days[] (fallback data.days[]), day defensive-parsed (first 10 chars / ISO prefix), price plain float; client-side filter to the window.

Shared HTTP client — one requests.Session with X-RapidAPI-Key / X-RapidAPI-Host set once; timeout=20; 2 s pacing between route calls; error classification → action:

Class Action
requests.Timeout / ConnectionError / HTTP 5xx Retry up to RETRY_MAX_ATTEMPTS (3) total, exponential backoff + jitter. Exhausted → route recorded with outcome, run continues (a dead route never kills the run).
HTTP 429 Triangulate via Retry-After + x-ratelimit-requests-remaining: throttle → bounded wait (≤60 s), retry ≤2; monthly hard-limit exhaustion → QuotaExhausted aborts the whole run immediately (every further call fails identically; burning retries against a hard limit is pointless) → quota_exhausted status + notice. (Verified trap: a missing key header also presents as 429 — on any 429 we log which headers were attached, key masked to last 4 chars.)
HTTP 401 / 403 Fatal, zero retries (auth is not transient). Abort run, auth_error status + notice carrying the gateway message — 403 "You are not subscribed to this API." means the key isn't subscribed to this API's page. The bot stays up and tries again next day; an operator fixing the key needs no restart.
HTTP 200 + status:false One retry after backoff, then provider_error for the route.
Malformed JSON / unexpected shape One retry, then invalid for the route. Never an exception escaping to the loop.
Empty-but-200 window empty — legitimate, no retry (beyond the optional price-graph fallback).

Quota accounting — every response (including 429s and 5xx) updates meta counters for the current month — as a single-statement UPDATE meta SET value = CAST(value AS INTEGER) + ? WHERE key = ? (helper in database.py), never a Python read-modify-write, so concurrent billers (the delivery loop's booking chain bills while a cycle runs, by design) cannot lose an increment. Provider headers are persisted when parseable. effective_remaining = min(PLAN_MONTHLY_LIMIT - used, header_remaining) — the min means the provider's own 150-hard-limit header binds even if the operator forgets to edit PLAN_MONTHLY_LIMIT down from the PRO default. Pre-run gate: the run refuses to start when effective_remaining < routes_to_fetch + QUOTA_RESERVE_REQUESTS — it still writes a runs row (status='skipped', routes_ok=0, routes_failed=0) plus a quota_low notice (deduped per day), and writes no stat rows for the day; a later catch-up re-trips the gate the same way. checkServer is not called routinely (every call bills); auth failures surface on the first real call.

Run cost model — the default profile spends ≈ 155–170+ requests/month: 5 calendar calls/day, plus price-graph fallbacks (routine, not occasional — ALG→PUS empties are the norm), +1 confirmation per alert candidate, +3 per resolved booking link. That is a PRO-tier profile; PRO ($12.99, 40k/mo) is the realistic tier and what the §11 defaults assume. Even the bare core cannot finish a BASIC (150/mo hard) month: the pre-run gate needs 11 requests to start, so 150 funds at most ~28 runs — days 29–31 are skipped with a quota_low notice every month (safe per §6: gaps shrink the baseline sample and bias against false alerts). A BASIC subscriber must apply the degradation set (BOOKING_LINK_MODE=static, USE_PRICE_GRAPH_FALLBACK=0, CONFIRM_BEFORE_ALERT=0) and accept the 2–3 tail days. (Sky Scrapper free tier supports ~4 runs/month total — effectively unusable for a daily bot; its adapter is for paid-tier switching.)

Booking links (BOOKING_LINK_MODE): for a sent alert's winning route+date, resolve mode runs the chain — searchFlights (search_type=cheap) → cheapest itinerary's booking_token (if absent: skip straight to fallback, never chase getNextFlights pages) → getBookingDetails → pick partner (lowest price, prefer is_airline) → getBookingURL → plain URL string (accept only http...) — quota-gated (needs headroom ≥ reserve+3, else static fallback). static mode skips the chain. Any failure at any step falls back to a free hand-built one-way Google Flights search URL (§2 template) — the alert always ships with a link. Sky Scrapper has no booking chain at all → static fallback only.

10. Docker design

Dockerfile — FROM python:3.11-slim-bookworm (pinned base whose libsqlite3 is 3.40+ — WAL fully supported). ENV PYTHONUNBUFFERED=1 PYTHONDONTWRITEBYTECODE=1 TZ=Africa/Algiers (TZ affects log readability only — every correctness path uses ZoneInfo explicitly). Layer order for caching: COPY requirements.txt → pip install --no-cache-dir (installs tzdata — slim images ship no /usr/share/zoneinfo) → COPY *.py. Non-root user (uid 1000) owning /app and /data. No EXPOSE (outbound-only: Discord gateway + RapidAPI). No secrets in any image layer — everything arrives via compose env_file. CMD ["python", "bot.py"] (signal handling in-process; no ENTRYPOINT wrapper needed). .dockerignore: .env, *.db*, .git, .research_gf2/, litellm_config.yaml, __pycache__/, plan.md.

docker-compose.yml — single service flight-bot:

services:
  flight-bot:
    build: .
    container_name: flight-tracker-bot
    init: true                    # proper PID-1: SIGTERM reaches python, zombies reaped
    restart: unless-stopped       # survives host reboots and transient crashes
    stop_grace_period: 30s        # matches the 20 s bounded in-process shutdown
    env_file: .env
    environment:
      TZ: Africa/Algiers
      DB_PATH: /data/flight_tracker.db   # pinned — compose environment overrides env_file
    volumes:
      - flight-data:/data         # NAMED volume — see note below
    healthcheck:
      test: ["CMD", "python", "healthcheck.py"]   # heartbeat age < 10 min
      interval: 2m
      timeout: 10s
      retries: 3
      start_period: 2m
    logging:
      driver: json-file
      options: { max-size: "10m", max-file: "3" }
volumes:
  flight-data:

DB_PATH is pinned in the compose environment block (which overrides env_file) so the container always lands the DB on the named volume regardless of what .env carries — .env ships the local no-Docker default, and no edit is needed for either mode.

Why a named volume, not a host bind mount (this repo lives on a Windows host): SQLite in WAL mode writes .db-wal and .db-shm siblings that need coherent POSIX filesystem semantics; Docker Desktop's Windows bind-mount translation is exactly where WAL corruption and slow-locking gremlins live. A named volume keeps the whole DB family on one Linux filesystem inside the VM, and survives rebuilds/recreations. (Local no-Docker dev on Windows/NTFS is fine — only the Desktop bind mount is the hazard.)

No ports (zero inbound surface). No scaling — one SQLite writer (§5). Backup: docker compose stop → copy the named volume — WAL siblings must be checkpointed or copied together; the graceful shutdown checkpoints on stop. From a running container: docker compose exec flight-bot python -c "import sqlite3; sqlite3.connect('/data/flight_tracker.db').backup(sqlite3.connect('/data/backup.db'))", then docker cp. Upgrades are backward-safe: all DDL is CREATE IF NOT EXISTS keyed on meta.schema_version; a newer image never touches existing data.

11. .env.example (exact contents)

# ============================================================
# Flight Tracker Bot configuration
# Copy to .env and fill in. NEVER commit .env.
# ============================================================

# --- Discord ---
# https://discord.com/developers/applications -> Bot -> Token
DISCORD_BOT_TOKEN=
# Channel ID that receives deal alerts (right-click channel -> Copy Channel ID)
DISCORD_CHANNEL_ID=
# Optional ops channel for system notices (quota, auth, outages). Empty = alerts channel.
DISCORD_ADMIN_CHANNEL_ID=
# Optional: sync slash commands instantly in this one server. Empty = global sync (up to 1h).
DISCORD_GUILD_ID=

# --- RapidAPI ---
# Subscribe to the chosen API's page first, or every call returns 403.
RAPIDAPI_KEY=
# google-flights2.p.rapidapi.com (default) or sky-scrapper.p.rapidapi.com
RAPIDAPI_HOST=google-flights2.p.rapidapi.com
# Must match the host: google-flights2 | sky-scrapper
API_PROVIDER=google-flights2

# --- Schedule & timezone ---
# All calendar dates and the run slot use this IANA zone. Algeria has no DST.
TIMEZONE=Africa/Algiers
RUN_TIME_LOCAL=09:15

# --- Tracking strategy ---
# One-way routes as ORIGIN-DEST IATA pairs
TRACKED_ROUTES=ALG-NRT,ALG-HND,ALG-KIX,ALG-ICN,ALG-PUS
# Window: departures D+LEAD_DAYS .. D+LEAD_DAYS+WINDOW_DAYS-1 (lead-time-stationary)
LEAD_DAYS=1
WINDOW_DAYS=30
# Market context for pricing/inventory (DZ = Algeria). Changing ANY methodology
# value below re-primes the 30-day baseline (7-day warmup) on purpose.
COUNTRY_CODE=DZ
CURRENCY=EUR
TRAVEL_CLASS=ECONOMY
ADULTS=1
# 0 = exclude separate-ticket itineraries from verification searches (safer, default)
SHOW_HIDDEN=0
# Retry an empty calendar window once via the Price Graph endpoint (+1 request)
USE_PRICE_GRAPH_FALLBACK=1

# --- Statistics & alerting ---
# Fire when today's low is at least this % below the 30-day rolling average
DROP_THRESHOLD_PCT=20
# Days of prior history required before any alert (warmup)
MIN_HISTORY_DAYS=7
# Symmetric trim of the baseline mean (% per tail)
BASELINE_TRIM_PCT=10
# Exclude baseline values outside [median/factor, median*factor]
SERIES_OUTLIER_FACTOR=3.0
# Re-alert suppression (Algiers days) + required deepening (percentage points)
ALERT_COOLDOWN_DAYS=7
RE_ALERT_DEEPEN_PCT=5
# Quote sanity band in EUR (kills glitch quotes and currency contamination)
FARE_FLOOR_EUR=80
FARE_CEILING_EUR=12000
# Re-fetch to confirm a drop before alerting (+1 request per alert candidate)
CONFIRM_BEFORE_ALERT=1
# Tag alerts "possibly stale" after N identical price vectors in a row
STALE_STREAK_RUNS=3

# --- HTTP behavior ---
REQUEST_TIMEOUT_SECONDS=20
RETRY_MAX_ATTEMPTS=3
RETRY_BACKOFF_SECONDS=5
INTER_CALL_DELAY_SECONDS=2
# Health-check call at run start (costs 1 request; auth errors surface on the
# first real call anyway)
CHECK_SERVER_EACH_RUN=0
# resolve = 3 extra requests per sent alert for a real checkout link
# static   = free hand-built Google Flights search URL (BASIC-tier setting)
BOOKING_LINK_MODE=resolve

# --- RapidAPI monthly quota ---
# google-flights2: BASIC 150/mo HARD | PRO 40,000/mo. sky-scrapper: 20/mo | 10,600/mo.
# Defaults below assume PRO. On free BASIC, also set: USE_PRICE_GRAPH_FALLBACK=0,
# CONFIRM_BEFORE_ALERT=0, BOOKING_LINK_MODE=static, PLAN_MONTHLY_LIMIT=150 — the
# provider's own header still binds the true 150 hard limit either way.
PLAN_MONTHLY_LIMIT=40000
# A run needs at least this many requests remaining after it finishes
QUOTA_RESERVE_REQUESTS=6

# --- Storage ---
# Local no-Docker default (sqlite creates it in the working directory).
# docker-compose.yml pins /data/flight_tracker.db for containers — no edit needed
# for either mode. database.py mkdirs the parent before the first connect.
DB_PATH=./flight_tracker.db
LOG_LEVEL=INFO

Startup validation (fail-fast, in bot.py): required vars present; API_PROVIDER ∈ {google-flights2, sky-scrapper} and matches RAPIDAPI_HOST; TRACKED_ROUTES parse as XXX-YYY; LEAD_DAYS ≥ 1; 1 ≤ WINDOW_DAYS ≤ 60; 0 < DROP_THRESHOLD_PCT ≤ 90; MIN_HISTORY_DAYS ≥ 3; 0 ≤ BASELINE_TRIM_PCT < 25; FARE_FLOOR < FARE_CEILING; ZoneInfo(TIMEZONE) constructs (crashes loudly on missing tzdata rather than silently running on UTC); RUN_TIME_LOCAL parses as HH:MM. Config errors exit non-zero (the restart policy keeps surfacing them); data/API errors never exit.

12. Risks & mitigations

Risk Mitigation
Quota cliff — BASIC (150/mo) funds only ~28 of the 30–31 core runs under the 11-request pre-run gate — 2–3 quota_low tail days every month even with the degradation set; the default profile (155–170+/mo) is a PRO-tier profile Pre-run gate before spending; mid-run QuotaExhausted abort; local counter cross-checked with headers (provider header binds even if the limit is mis-set); link chain quota-gated; documented BASIC degradation set; PRO recommended
Fake-cheap quote → false alert (the worst failure: user books a nonexistent deal, stops trusting the bot) Four stacked tiers: quote band (80–12k EUR) → median/3.0 series band → 10% trimmed mean → confirmation re-fetch before any send (on unless CONFIRM_BEFORE_ALERT=0 on BASIC)
ALG→PUS empties are the norm empty outcome, NULL low, zero retries, route_no_data notice after 3 consecutive days; plan documents it as expected behavior for that route
Timezone skew (UTC container vs Algiers days → mislabeled dates, double/skipped days) Every date from ZoneInfo(TIMEZONE) computed once per run; 09:15 slot far from midnight; tzdata pinned; explicit dates on every API call; PK-based exactly-once
Methodology drift (changing country_code/provider mid-series → phantom drops) params_hash on every run/stat; baselines same-fingerprint only; changes re-prime with visible warmup
429 triple meaning (throttle vs hard-limit vs missing header) Header + Retry-After triangulation; conservative abort branch; 429-path logging with masked key
SQLite corruption (second writer, volume damage) Single-instance/no-scale rule; WAL + short transactions; SIGKILL-safe by construction; quick_check at startup → quarantine + salvage + db_corrupt notice (§5 kind enum) as the documented runbook
Discord delivery failure (channel deleted, permissions, outage) Decoupled pending→sent machine, ≤10 retries, discord_send_failed notice, alerts visible in /status
Provider shape drift (the upstream is a scraper) Defensive parsing degrades to invalid/NULL rows (never a crash); all-empty-run tripwire notices within a day; raw layer preserves what came in
First-week silence (min-history guard) By design — warmup progress visible in /status; documented in the runbook so day-1 silence isn't read as breakage

13. Operational runbook

First deploy: ① Discord developer portal — create app → Bot → copy DISCORD_BOT_TOKEN; invite with bot + applications.commands scopes; note the alert channel ID (and optional admin channel ID). No privileged intents needed. ② RapidAPI — subscribe to the Google Flights 2 API page (or every call 403s); copy RAPIDAPI_KEY; decide your tier — the shipped defaults are the PRO profile (confirmation re-fetch, price-graph fallback, resolved booking links); on free BASIC flip the four knobs marked in the quota section of .env.example (see §9 cost model). ③ cp .env.example .env, fill in. ④ docker compose up -d --build. ⑤ Check /status — routes seeded, warmup counters visible. Expect no alerts for the first 7 days (baseline warmup, by design).

Day-to-day: /status (per-route state, quota, last run); docker logs flight-tracker-bot. The three expected operational events: 401/403 → fix key/subscription in the RapidAPI dashboard, no restart needed (next day's run self-heals); quota exhausted → upgrade tier or wait for monthly reset (the bot skips runs safely and says so); empty PUS → expected, do nothing.

Verification plan for the implementation phase (in order):

  1. python tracker.py standalone (no Discord): seeds DB, runs one live cycle, prints per-route outcomes — validates adapters, parsing, validation, and stat derivation in isolation. Check the printed lows are EUR-plausible (they must sit inside the §7 80–12,000 EUR band; a DZD/USD-scale reading means the adapter is mislabeling currency — stop and fix before anything else).
  2. DB inspection via stdlib Python (the sqlite3 CLI ships neither on stock Windows 11 nor in python:3.11-slim): python -c "import sqlite3; c=sqlite3.connect('flight_tracker.db'); c.row_factory=sqlite3.Row; [print(t, c.execute(f'select count(*) from {t}').fetchone()[0]) for t in ['meta','routes','runs','fare_quotes','route_day_stats','alerts','system_alerts']]; r=c.execute('select * from route_day_stats order by stat_date desc limit 1').fetchone(); print(dict(r) if r else 'no stats yet')" (connect to whatever DB_PATH step 1 used) — row counts per table plus a stat row's full audit columns.
  3. Synthetic alert test on a scratch DB: seed ≥ 7 synthetic prior route_day_stats rows per route (carrying the current params_hash, daily_low_eur well above today's expected low so no median-band exclusions bite), set only DROP_THRESHOLD_PCT=1, run the bot end-to-end (embed + booking chain + delivery machine), then reset config. Click the static fallback link variant too and confirm it opens a one-way search for the intended route and date.
  4. docker compose config → up -d --build → healthcheck goes healthy → /checkflights and /status respond.
  5. Restart durability: docker compose restart mid-day → no double-run, no duplicate alerts, heartbeat resumes.
  6. Error paths: bad key (expect clean auth_error + notice), unreachable channel (expect discord_send_failed + retry, alert still queryable).

Implementation order: database.py → tracker.py → standalone smoke test → bot.py → healthcheck.py → Docker files → end-to-end verification.

Out of scope for v1 (each documented, not built): per-departure-date alerting (today's route-level window low is the recorded metric per spec); automatic cross-provider failover (switching providers re-primes the series — a deliberate .env change); per-itinerary carrier/stops detail (5×N calls — paid tier only); sub-daily cadence (paid tier); backfill of missed days (perishable data).

14. Repo hygiene note

.gitignore covers .env, *.db, *.db-wal, *.db-shm, .research_gf2/, __pycache__/, and litellm_config.yaml — that file contains a live-looking API key in plaintext; it must not be committed, and the key should be rotated if it has ever been shared. If this repo will live on a remote, consider moving the litellm config out of the project directory entirely.