Building the Archive: Replaying Past Daily Challenges

Building PriceGuess #9 March 11, 2026 · 6 min read

The Problem: "I Missed Yesterday!"

One of the most common pieces of feedback I received from PriceGuess players was frustration about missing daily challenges. Unlike Wordle, where missing a day just means missing that word, PriceGuess features real products with real prices from Israeli supermarkets. Players wanted to go back and try challenges they had missed - or replay ones they had lost.

Building this seemingly simple feature turned out to be one of the trickiest parts of the project, primarily because of how the scraper stores data. Let me walk you through the architecture, the bugs, and the lessons learned.

Architecture: How Daily Challenges Work

Every day, a CRON Worker scrapes approximately 10 random products from Shufersal (Israel's largest supermarket chain) and stores them in a D1 database table called daily_challenges. Each row has a challenge_date and an item_code, with a unique constraint on the pair.

The daily challenge page calls getTodayChallenge(), which queries:

SELECT challenge_date, item_name, target_price, ...
FROM daily_challenges
WHERE challenge_date = ?
ORDER BY id ASC
LIMIT 1

The ORDER BY id ASC LIMIT 1 is key - it ensures that among the ~10 products scraped for any given day, the same product is always selected as "the" daily challenge. This is deterministic: every player sees the same product on the same day.

The Archive API

For the archive, I built two API endpoints:

GET /api/archive/challenges - Returns the list of past challenges (last 90 days) with the user's play status (won, lost, or not played). This feeds the archive calendar UI.

GET /api/archive/[date] - Returns the full challenge data for a specific date, including the product name, image, unit, and target price. The game component uses this to render the same gameplay experience as the daily challenge.

The Three Bugs That Broke Everything

When I first deployed the archive, it looked correct at a glance - but users quickly reported that something was very wrong. Three bugs, all stemming from the same root cause.

Bug 1: Every Entry Showed the Same Date

The archive list showed 90 entries, but they all displayed the same date even though they had different product names. The original query was:

SELECT dc.challenge_date, dc.item_name, ...
FROM daily_challenges dc
WHERE dc.challenge_date < ?
ORDER BY dc.challenge_date DESC
LIMIT 90

The problem? With 10 products per day, this returns 10 rows for each day. So the 90-row limit meant I was getting about 9 days of data, with each day repeated 10 times. Since each row had a different item_name but the same challenge_date, users saw what appeared to be the same date over and over.

Bug 2: All Entries Showed "Lost" After Playing One

After playing one archive challenge, every entry in the list turned yellow (indicating a loss). This happened because all entries for the same date shared the same localStorage key: priceguess_daily_archive_2026-03-10. Playing any entry for that date set the key, which the calendar checked for all entries of the same date.

Bug 3: Different Dates Opened the Same Game

Clicking on different entries opened the same game every time. Since all entries for a date had the same challenge_date, the API endpoint /api/archive/[date] received the same date parameter and returned the same product (via LIMIT 1).

The Fix: One Entry Per Date

The root cause was clear: the archive API needed to return exactly one entry per date - the same product that was selected as the daily challenge. The fix used a subquery to find the first product per date:

SELECT dc.challenge_date, ...
FROM daily_challenges dc
INNER JOIN (
  SELECT challenge_date, MIN(id) as min_id
  FROM daily_challenges
  WHERE challenge_date < ?
  GROUP BY challenge_date
) first_per_day ON dc.id = first_per_day.min_id
ORDER BY dc.challenge_date DESC
LIMIT 90

This INNER JOIN with MIN(id) mirrors the daily challenge selection logic (ORDER BY id ASC LIMIT 1), ensuring consistency. I also added ORDER BY id ASC to the archive detail endpoint.

Bonus Fix: Database Persistence

I noticed another subtle bug: when playing an archive challenge, the game component sent the challengeDate as archive_2026-03-10 (with the archive_ prefix) to the guess persistence API. This meant the guesses table stored archive_2026-03-10 as the challenge date, which never matched the daily_challenges table's date format (2026-03-10).

The fix was simple but important: strip the archive_ prefix before saving to the database, while keeping it in localStorage to maintain separate state between daily and archive games.

The Calendar UI

With the data issues fixed, I built a clean archive calendar that shows:

The date and day of week for each past challenge
Color-coded status: green for wins, amber for losses, white for unplayed
Emoji indicators for quick scanning
Both server-side (database) and client-side (localStorage) status tracking

I intentionally removed the product name from the archive list. Showing the product name before playing would spoil the surprise and give players an unfair advantage in guessing the price.

Key Takeaways

When your data model stores multiple items per logical unit (date), every query that assumes one-to-one mapping will break. The fix is always at the data access layer, not the UI.

Match your selection logic everywhere - If the daily challenge uses ORDER BY id ASC LIMIT 1, the archive must use the same selection criteria.
Be careful with localStorage keys - When multiple items share a natural key (date), collisions are inevitable. Namespace your keys carefully.
Strip prefixes at the boundary - UI-level prefixes (archive_) should not leak into database persistence.
Use React keys for state reset - Adding key={challengeDate} to the game component ensures React creates a fresh instance when switching between archive challenges.

What's Next

With the archive working reliably, the next step is adding detailed statistics - streak tracking, archive completion percentage, and cross-player comparisons. If users want to see how they stack up against others on past challenges, the data is already there in the database. Stay tuned!

You can try the archive right now at price.etiel.dev/archive (requires a free Google login).