Badminton Session Tracker

Plan doc v0.2 · 16 Nov 2026 · source: Telegram @sgbadmintontelecom (chat -1001191346636) · target: Cloudflare Pages + D1

1. Shape of the thing

Three stages, each one independently re-runnable, with the raw text never lost:

 glastrier chromium (CDP 10.21.4.4:9223)
   └── scrape ──►  raw/*.jsonl        (append-only, per run, never edited)
                        │
                        ├── parse ──►  staging/sessions.jsonl   (structured + confidence)
                        │              staging/review.jsonl     (needs a human)
                        ▼
                  upsert ──► Cloudflare D1 (badminton)
                        │
                        └──► Pages function /api/sessions ──► calendar + list UI

Rule The raw message table is the source of truth. Every other table can be dropped and rebuilt from raw/*.jsonl in a single command. A bad parse run is re-derived, never patched in place.

Scope

2. Data schema (D1 / SQLite)

Five tables. raw_messages mirrors Telegram; sessions is one row per bookable slot; venues is curated; runs is the audit trail; parse_log keeps the model's reasoning cheap to re-check.

raw_messages — what Telegram gave us

columntypenotes
message_idINTEGER PKTelegram's own id (data-message-id). Monotonic, dedupe key.
posted_dayTEXT (YYYY-MM-DD)From timeline date header. Day-accurate at best — see edge case E1.
posted_timeTEXT (HH:MM)SGT, from .message-time.
senderTEXTDisplay name as shown ("Roy LHH"). Not unique, not an identity.
textTEXT NOT NULLVerbatim, newlines preserved. Never rewritten.
has_mediaINTEGER1 if the post had a photo/poster (posters often carry the real details → needs_review).
scraped_atTEXTISO timestamp of the scrape run.

sessions — what the site queries

columntypenotes
idTEXT PKsha1(message_id|date|start|venue_key) — stable across re-parses.
message_idINTEGER FKProvenance. One message can yield several sessions (multi-slot posts).
session_dateTEXT (YYYY-MM-DD)Normalised. NULL only when the post genuinely omits a resolvable date.
start_time / end_timeTEXT (HH:MM)24h SGT. end_time NULL if unstated.
is_recurringINTEGER1 for weekly fixtures ("every Tue 8-10pm") — the calendar renders the next occurrence.
venue_keyTEXT FKSlug into venues; NULL when unmatched (parked in review).
courtsINTEGER"3.5 courts" → 3 with courts_note kept.
level_raw / level_normTEXTRaw string kept forever; normalised to the LI/MI/HI ladder (see §4 S6).
price_raw / price_cents / price_basisTEXT / INTEGER / TEXTBasis ∈ per_pax, per_court, unknown. Never silently compare across bases.
shuttle_brandTEXT"RSL Supreme" — people care.
slots_rawTEXT"last 2 slots" etc., not machine-usable but shown.
contact_rawTEXTPII — never exported to the site.
parse_statusTEXTok · partial · needs_review · skip
confidenceREAL0–1, from the validators in §5, not the model's self-report.

venues

columntypenotes
venue_keyTEXT PK"yishun-sports-hall"
name / areaTEXT"Yishun Sports Hall" / "North"
aliasesTEXT (JSON)["Yishun SH", "YSportshall", "Yishun Sports Hall"] — the fuzzy matcher's training data.
lat / lonREALGeocoded once via OSM/Nominatim, cached. Enables the map later without redoing work.
curatedINTEGER1 = human-confirmed. Uncurated rows are never auto-merged into another venue.

runs & parse_log

tablecolumns
runsid, started_at, finished_at, kind (scrape|parse|upsert), messages_seen, sessions_new, sessions_updated, status, note
parse_logmessage_id, parser_version, model, fields_json, issues_json, created_at — lets you diff parser v3 vs v4 on the same message without re-calling the model

3. Cleaning pipeline

Posts are free-form, bilingual (EN / 中文), and full of shorthand. Ten stages, each with a hard rule; anything that fails a stage lands in review.jsonl instead of guessing.

S0 · Capture

Wheel-scroll the chat, emit one JSON object per .Message node. Normalise whitespace: collapse \n{3,} → \n\n, strip zero-width chars, keep emoji (they carry meaning: 🏸 ✅ ❌).

S1 · Classify: session ad or not

Rules first, model second. Ad if ≥2 of: a date token, a time token, a venue token, a level token, a slot/price token. Chatter and pure adverts ("selling Yonex 88D") are skip with a reason code (no_date, no_venue, sales, coaching_biz).

Ambiguous ones ("anyone wants to play this weekend?") → needs_review, never a guess: they lack date+venue by design.

S2 · Extract

Regex pass for the common labelled shapes seen in the channel — Date:, Time:, Venue:, Level:, @venue, Open for:, free-text lines. Model pass only for what regex leaves empty, batched 20 posts/call, JSON-schema output, temperature 0. Fields missing → null. The model is forbidden to invent a venue or a date; it may only copy spans from the text, and every non-null field is checked as a substring/derivative of the raw text (§5).

S3 · Dates

input seen in channelruleoutput
21 Nov 2026, Satabsolute2026-11-21
17/ 11/2026D/M/Y (SG default); reject M/D/Y if D>122026-11-17
21/11/26DD/MM/YY, century = 2000s2026-11-21
TUESDAY, tonight, this Satresolve against posted_day; if the word'd weekday is 6 days behind, roll forwardcomputed date + date_assumed=1
weekday mismatch vs stated date ("21 Nov 2026, Sun")trust the numeric date, keep a date_conflict issue, confidence −0.32026-11-21
no date at allno inference from posted_dayNULL + needs_review

Sessions dated before the run are archived, not deleted (is_past derived), so the calendar can show history.

S4 · Times & multi-slot posts

S5 · Venues

Canonicalise in three passes: exact alias hit → normalised fuzzy (lowercase, strip @, "SH"/"Sports Hall"/"Sportshall" fold, drop parentheticals like @Geylang) with token-set ratio ≥ 0.85 → model tiebreak only for the top-3 candidates, with the candidate list shown in parse_log. New venues are created curated=0 and surface in a weekly "new venues" digest for you to confirm — merging uncurated venues automatically is how you end up with 40 spellings of Toa Payoh.

S6 · Levels

SG ladder, normalised: HB (high beginner) < LI (low intermediate) < MI < HI (high intermediate) < ADV. Parse ranges ("LI to HI", "HB-LI"), and reject-as-unknown anything else ("intermediate", "social") → level_norm='ANY' with raw kept. Do not map "intermediate" to MI silently.

S7 · Price, courts, shuttles

$7/pax → price_cents=700, basis=per_pax; $42/court → 4200, per_court; free → 0. Court split ("share cost") → basis=unknown. Courts: "3.5 courts" → courts=3 + courts_note='3.5'.

S8 · Dedupe & merge

S9 · Validate & score

Confidence starts at 1.0 and is docked for each issue: assumed date (−0.15), assumed end (−0.1), uncurated venue (−0.2), weekday/date conflict (−0.3), am/pm inferred (−0.2), model-only field (no regex anchor) (−0.25). parse_status: 0 issues → ok; issues but date+venue+start present → partial; anything else → needs_review.

S10 · Review queue

review.jsonl + a D1 view. Weekly digest to Telegram: "14 sessions need review" with the raw text inline, so you can accept/correct in one reply. Corrections are written as overrides keyed by message_id, applied after parsing, so a parser upgrade never eats your fixes.

4. Edge cases & the calls I'd make

idcasedecision
E1Timeline date header only updates on real scroll events; programmatic scroll leaves it staleScroll with real wheel events; record (header, first visible id) per step. Session dates come from post text anyway, so a fuzzy posted_day can't corrupt the calendar.
E2Poster image carries the details, body says "see poster"parse_status='needs_review', has_media=1. OCR later if it turns out to be common — measure first.
E3Chinese-only postSame pipeline; the model handles 中文 labels (日期/时间/地点/水平), regex pass is additive not required.
E4"DM me for details" with no timepartial: date + venue recorded, start_time NULL.
E5Message edited after scrapev1: not detected (no edit hook). text_hash compared on each re-scrape of recent messages so edits within the re-scrape window are caught.
E6Session fills up / is cancelled in later messagesv1: ignore. v2: a session_updates table fed by "full", "cancelled", "left 1 slot" replies.
E7Recurring weekly fixturesOne sessions row, is_recurring=1, plus rrule TEXT. Calendar shows next occurrence only.
E8Same session posted by two different sellersNot merged (different contacts, possibly different prices). Site can group visually by date+venue+time.

5. Quality gates

6. PII policy

Posts are full of phone/WhatsApp numbers and names. Stance: collect privately, publish nothing personal.

7. Your calls

Q1 · Site access. (a) public badminton.rowentey.xyz, contacts + senders hidden ← recommended · (b) Cloudflare Access-gated so the contact details survive · (c) both: public masked view + a private "admin" view.
Q2 · Publish cadence. scrape at 07:00 and 21:00 SGT ← recommended, or once nightly?
Q3 · Raw text on the site. Sessions-only cards (clean, recommendation) vs full-text search over raw posts too.
Q4 · Parse model. Cheap pinned model for bulk (thousands of posts), current default for the review queue only. Agree?
Q5 · Backfill floor. Hard stop at 1 Nov 2026 as agreed, or deep-scroll to mid-Oct (id ~1260000) while the tab is already open?
Q6 · Level taxonomy. Is HB < LI < MI < HI < ADV right for how you and these posters actually grade? This one changes the filters.

8. Build order (after your calls)

  1. D1 database + schema + migrations + repo skeleton (no scraping yet).
  2. Scraper + day bucketing; dump 2 Nov → now to raw/; report counts before any parsing.
  3. Fixtures + regex pass + validators; measure on the fixture set.
  4. Model pass for the remainder; review queue + weekly digest.
  5. Venue curation of the top 30; geocode.
  6. Pages site: API, calendar, list, filters; deploy + verify.
  7. Cron twice daily + failure alerts + vault note.

Plan file: /opt/data/.hermes/plans/2026-11-16_214800-badminton-sessions-tracker.md (markdown twin of this doc). Nothing is built yet — decisions Q1–Q6 gate step 1.