How Do You Know a Shopping Bonus Is Actually Good?

Published on March 22, 2026 • 2 min read

Python Cloudflare SQLite Data Modeling

Written up in October 2026. Dates are when I actually built it.

Airline shopping portals love to scream “5X POINTS!!!” at you. Is that a great rate, or just a Tuesday? The portal will never tell you, because it only shows you today. So I built a tracker that remembers. Code’s here.

The data model is the whole project

I put everything in Cloudflare D1 (SQLite) and spent most of my time on the schema, because the questions I wanted to answer (“is this an all-time high?”, “when does it expire?”) are all just queries if the tables are right:

  • offer_snapshots is append-only history: store, timestamp, base rate, effective rate, fixed points. An index on (gmid, scraped_at DESC) makes “latest snapshot per store” cheap.
  • offer_type has a CHECK constraint for flat, tiered or fixed_pts, because these portals really do have three different kinds of offer and I’d rather the database refuse garbage than my UI guess.
  • bonus_events is a different shape on purpose. A bonus isn’t a snapshot, it’s a thing with a lifespan, so it’s one row per (store, description) with first_seen, last_seen, expires_at and is_active. Re-scraping updates the row instead of making a new one.
  • scrape_errors so a store that quietly breaks shows up instead of looking “unchanged”.

Cron, but make it boring

The Worker has a scheduled handler on a four-hour cron that runs three stages in order: sweep the store list, fetch per-store bonuses, fetch spend-threshold bonuses. I originally ran the Python scrapers from CI and moved them into the Worker’s own cron to kill a moving part.

Two dedupe bugs, opposite directions

Writing a snapshot on every run fills the table with identical rows, so I only insert when something changed. My first change check ignored base rate and fixed points, which meant real changes got dropped as duplicates. Fixed by comparing every field that matters.

Then the reverse: change-only writes mean a stable store has no recent rows, so “unchanged” and “scraper is dead” look identical. The fix was a daily heartbeat snapshot, one per UTC day regardless of changes. Getting the “does today already have a row” subquery right took a second fix (it needed scraped_at in the inner query).

Takeaway: whatever you leave out of a dedupe key is a change you will silently lose.