Reconstruct 2026-03-14 daily files (Kitsune list API 500s the whole day)

Summary

The 2026-03-14 daily files could not be re-scraped during the UTC backfill/re-scrape effort: the SUMO/Kitsune question list endpoint returns HTTP 500 for that day's window under every query form tried. As a result 2026/2026-03-14-* is still in its stale (pre-re-scrape) state and needs a per-ID reconstruction.

Context

Background: after the Kitsune API switched to truthful UTC (~2026-02-19T15:33–16:15Z), the now-removed kludge_time_from_bogusZ_to_utc corrupted created/updated, and the day-window filter ran on shifted times (mis-bucketing). This was fixed by removing the kludge, backfilling timestamps, and re-scraping Jan 31→Jun 6 with the fixed code. All days re-scraped cleanly except 2026-03-14.

Symptom / root cause

GET /api/2/question/?product=thunderbird&created__gt=2026-03-13T23:59:59&created__lt=2026-3-15&ordering=created → HTTP 500 (HTML error page, not JSON).

Confirmed it is not ordering- or volume-related — all of these still 500 for the full-day window:

• ordering=created, -created, none (omitted), updated, -updated, id, -id, last_answer, num_answers
• page_size=1, 5, 100

Bisecting the day by time sub-windows shows it is one poisoned question that breaks the server-side serialization of any result set containing it:

• Sub-windows that exclude it return HTTP 200 (e.g. early-day and late-day slices).
• The poisoned question is in the created param slice ≈ 10:07–10:30 (the API interprets naive created__gt/lt params in Pacific while returning created in UTC, so this is UTC ≈ 17:07–17:30).
• The last good question just before it is id 1570863 (created 2026-03-14T16:57:06.110567Z); the next safe slice (10:30–11:00 param) returns count 0.

This looks like an upstream Kitsune API bug (a single record breaks list serialization). Worth reporting upstream separately.

Current state of the data

• 2026/2026-03-14-…-creator-answers-desktop-all-locales.csv: stale. Timestamps are correct +0000 (from the earlier backfill), but 14 rows are mis-bucketed (their created date is actually Mar 13 or Mar 15).
• Those 14 rows are the only cross-file duplicate question IDs in the whole Jan 31→Jun 6 range (each also appears, correctly, in the re-scraped Mar 13/Mar 15 files).
• Every other re-scraped day has correct UTC timestamps and correct day-bucketing (0 membership errors, 0 duplicates).

Proposed fix (full reconstruct)

1. Fetch 2026-03-14 via the working time sub-windows (those that return 200), collecting raw question JSON, skipping only the poisoned slice (UTC ≈ 17:07–17:30).
2. Recover the poisoned question(s) by scanning IDs after 1570863 via the single-object endpoint GET /api/2/question/{id}/ (which serializes one record and should not 500), keeping those whose created is on 2026-03-14 UTC.
3. Apply the same flattening/UTC handling as get-tb-creator-answers-questions-for-arbitrary-time-period.rb, filter to [2026-03-14 00:00:00, 2026-03-14 23:59:59] UTC, sort by created, write the CSV.
4. Re-run get-tb-answers-from-questions-file-for-arbitrary-time-period.rb 2026 3 14 2026 3 14 against the rebuilt questions file.
5. Verify: all +0000, all rows on 2026-03-14, and the 14 cross-file duplicate IDs are gone.

Acceptance criteria

• 2026-03-14 questions + answers files reflect a fresh scrape with correct UTC and correct membership.
• No cross-file duplicate question IDs remain in the 2026 daily files.
• No rows in the Mar 14 file have a created date other than 2026-03-14.

References

• Re-scrape commits: 2573ab3ed4 (bulk), plus the failed-day re-runs.
• Tooling: rerun-failed-rescrape-days.rb, backfill-utc-timestamps.rb, verify-utc-backfill.rb.
• AAQ_ORDERING override (2bc8458d41) handles days that 500 only under ascending order — but 2026-03-14 500s under all orderings, so it needs this per-ID approach.