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
- In: session adverts — friendly games, court bookings with open slots, training sessions, tournament notices.
- Out (v1): coaching-business ads, racket/shuttle sales, unrelated chatter, "looking for coach" posts. They stay in
raw_messageswithparse_status='skip'+ reason, so nothing is silently dropped. - Window: backfill to 1 Nov 2026, then incremental twice daily.
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
| column | type | notes |
|---|---|---|
| message_id | INTEGER PK | Telegram's own id (data-message-id). Monotonic, dedupe key. |
| posted_day | TEXT (YYYY-MM-DD) | From timeline date header. Day-accurate at best — see edge case E1. |
| posted_time | TEXT (HH:MM) | SGT, from .message-time. |
| sender | TEXT | Display name as shown ("Roy LHH"). Not unique, not an identity. |
| text | TEXT NOT NULL | Verbatim, newlines preserved. Never rewritten. |
| has_media | INTEGER | 1 if the post had a photo/poster (posters often carry the real details → needs_review). |
| scraped_at | TEXT | ISO timestamp of the scrape run. |
sessions — what the site queries
| column | type | notes |
|---|---|---|
| id | TEXT PK | sha1(message_id|date|start|venue_key) — stable across re-parses. |
| message_id | INTEGER FK | Provenance. One message can yield several sessions (multi-slot posts). |
| session_date | TEXT (YYYY-MM-DD) | Normalised. NULL only when the post genuinely omits a resolvable date. |
| start_time / end_time | TEXT (HH:MM) | 24h SGT. end_time NULL if unstated. |
| is_recurring | INTEGER | 1 for weekly fixtures ("every Tue 8-10pm") — the calendar renders the next occurrence. |
| venue_key | TEXT FK | Slug into venues; NULL when unmatched (parked in review). |
| courts | INTEGER | "3.5 courts" → 3 with courts_note kept. |
| level_raw / level_norm | TEXT | Raw string kept forever; normalised to the LI/MI/HI ladder (see §4 S6). |
| price_raw / price_cents / price_basis | TEXT / INTEGER / TEXT | Basis ∈ per_pax, per_court, unknown. Never silently compare across bases. |
| shuttle_brand | TEXT | "RSL Supreme" — people care. |
| slots_raw | TEXT | "last 2 slots" etc., not machine-usable but shown. |
| contact_raw | TEXT | PII — never exported to the site. |
| parse_status | TEXT | ok · partial · needs_review · skip |
| confidence | REAL | 0–1, from the validators in §5, not the model's self-report. |
venues
| column | type | notes |
|---|---|---|
| venue_key | TEXT PK | "yishun-sports-hall" |
| name / area | TEXT | "Yishun Sports Hall" / "North" |
| aliases | TEXT (JSON) | ["Yishun SH", "YSportshall", "Yishun Sports Hall"] — the fuzzy matcher's training data. |
| lat / lon | REAL | Geocoded once via OSM/Nominatim, cached. Enables the map later without redoing work. |
| curated | INTEGER | 1 = human-confirmed. Uncurated rows are never auto-merged into another venue. |
runs & parse_log
| table | columns |
|---|---|
| runs | id, started_at, finished_at, kind (scrape|parse|upsert), messages_seen, sessions_new, sessions_updated, status, note |
| parse_log | message_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).
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 channel | rule | output |
|---|---|---|
21 Nov 2026, Sat | absolute | 2026-11-21 |
17/ 11/2026 | D/M/Y (SG default); reject M/D/Y if D>12 | 2026-11-17 |
21/11/26 | DD/MM/YY, century = 2000s | 2026-11-21 |
TUESDAY, tonight, this Sat | resolve against posted_day; if the word'd weekday is 6 days behind, roll forward | computed date + date_assumed=1 |
| weekday mismatch vs stated date ("21 Nov 2026, Sun") | trust the numeric date, keep a date_conflict issue, confidence −0.3 | 2026-11-21 |
| no date at all | no inference from posted_day | NULL + 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
07:30 pm - 09:30 pm,7.30-9.30pm,1930-2130,8 to 10→20:00–22:00style pairs.2-4pm, 2-5pm, 3-6pm→ three session rows (one message, three slots). This is why dedupe is content-keyed, not message-keyed.2hrswith a start only →end = start + 2h,end_assumed=1.- Ambiguous am/pm (
7-9) → 19:00–21:00 if start ≤ 9 and the venue is indoor; else needs_review.
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
- Key:
session_date + start_time + venue_key. Reposts (this channel repeats the same session daily) collapse to one row. - On collision: keep the earliest
message_idas provenance, append the others tosessions.extra_message_ids, prefer the most recently posted non-null field values (later posts usually carry fresher slot counts). - Never merge across different
venue_keyor different start times.
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
| id | case | decision |
|---|---|---|
| E1 | Timeline date header only updates on real scroll events; programmatic scroll leaves it stale | Scroll 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. |
| E2 | Poster 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. |
| E3 | Chinese-only post | Same pipeline; the model handles 中文 labels (日期/时间/地点/水平), regex pass is additive not required. |
| E4 | "DM me for details" with no time | partial: date + venue recorded, start_time NULL. |
| E5 | Message edited after scrape | v1: not detected (no edit hook). text_hash compared on each re-scrape of recent messages so edits within the re-scrape window are caught. |
| E6 | Session fills up / is cancelled in later messages | v1: ignore. v2: a session_updates table fed by "full", "cancelled", "left 1 slot" replies. |
| E7 | Recurring weekly fixtures | One sessions row, is_recurring=1, plus rrule TEXT. Calendar shows next occurrence only. |
| E8 | Same session posted by two different sellers | Not merged (different contacts, possibly different prices). Site can group visually by date+venue+time. |
5. Quality gates
- Fixture set: 30 hand-labelled posts (chosen to cover E2–E8, EN + 中文) in
parse/fixtures.jsonl. Any parser change must hit ≥90% field accuracy on date/start/venue before shipping. - No-hallucination check: every extracted venue/date/time token must appear in (or be a documented transform of) the raw text; failures log to
issues_jsonand drop confidence. - Run audit:
runsrow per execution; scrape must reportmessages_seen, parse must account forseen = ok + partial + review + skipexactly. - Idempotency: running parse+upsert twice changes zero rows.
- Site: API row count == D1 count for the same filter; zero contact numbers in any public response; 390px layout checked.
6. PII policy
Posts are full of phone/WhatsApp numbers and names. Stance: collect privately, publish nothing personal.
contact_rawnever leaves the DB — not in the API payload, not in JSON exports served by Pages.- The public site shows venue + date + time + level + price + a link to the original message in the channel.
- If the site stays public, no
senderfield either (it's a real name). Gated-by-Access option keeps contacts visible to you only.
7. Your calls
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.8. Build order (after your calls)
- D1 database + schema + migrations + repo skeleton (no scraping yet).
- Scraper + day bucketing; dump 2 Nov → now to
raw/; report counts before any parsing. - Fixtures + regex pass + validators; measure on the fixture set.
- Model pass for the remainder; review queue + weekly digest.
- Venue curation of the top 30; geocode.
- Pages site: API, calendar, list, filters; deploy + verify.
- 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.