Project plan · updated 2026-11-18
SG Badminton Sessions
An index of open badminton sessions posted in the @sgbadmintontelecom
Telegram community: scraped from the public channel, parsed into structured sessions, and served as a
filterable list at badminton-sg.pages.dev.
Overview
Organisers in the channel post ad-hoc games (venue, date, time, level, price, courts). There is no directory and posts scroll away, so booking a game means reading a firehose. This project turns those posts into a queryable dataset and a mobile-first page, while keeping the operator's contact route on the original post rather than republishing phone numbers.
- Source of truth: Postgres on the Patroni cluster (glastrier). D1 was removed from the write path on 18 Nov 2026.
- Cadence: twice daily (07:00 and 21:00 SGT), incremental and idempotent.
- Public surface: static page + one JSON endpoint. No accounts, no writes, no post text.
Features
Shipped
- Incremental Telegram scrape — drives the logged-in Telegram Web session over CDP, walks to the newest message, then scrolls back to a date floor. State (
min_id/max_id/last_file) makes re-runs cheap; media-only posts are skipped. - Two-stage parsing — deterministic rules extract date, time, venue, courts, level, price, shuttle; a model pass fills only the fields rules leave empty, batched 15–25 posts per call with 3 workers.
- Post-text cache — LLM answers are keyed by text hash (
state/llm_cache.jsonl, 1.2k entries), so a re-scrape costs almost no model tokens. - Session identity + repost merge — id =
sha1(date|start|venue_key|poster); duplicate ads collapse and their message ids are kept inextra_message_ids. - Honest uncertainty —
date_assumed/end_assumedflags and anissues[]array travel to the UI instead of being silently "fixed". - PII redaction — SG phone numbers and
wa.melinks are scrubbed at capture, with a re-scrub pass over the stored corpus. - Idempotent Postgres load — upsert sessions, append raw messages by id, prune sessions that fell out of the export window; the run summary reports new/updated/pruned.
- Filterable list UI — day chips, free-text venue/organiser search, level filter, price and courts tags, link back to the organiser's post.
- Resilient read path — Hyperdrive query first; on failure the page still has the last exported
sessions.jsonsnapshot. - Change-gated deploys — Pages deploy only runs when the snapshot hash changes, so a quiet day costs no deployment.
- Ops guardrails —
flocksingle instance, preflight for credentials and browser reachability, per-step exit gating, Telegram digest per run.
Planned, not built
- Calendar subscription — a stable
.icsfeed so sessions land in a phone calendar (the original spec promised a calendar; today it is a list only). - Custom domain —
badminton.rowentey.xyzis not in DNS; the project answers only onbadminton-sg.pages.dev. - Organiser identity —
poster_handleis unresolved for every row; contact is always the post permalink. - Geo search —
venues.lat/lonexist but are unpopulated, so no "near me" filter. - Watchlist alerts — no per-level/per-venue subscription or push when a matching session appears.
- Dead-man's switch — failures notify on the run, but nothing alerts if the cron stops firing entirely.
Tech stack
- Ingest CLI: Go 1.25.4 (arm64,
/opt/data/tools/go), modulegithub.com/TeyLab/badminton-sync. Deps:coder/websocket(CDP),pgx/v5(load). No Cloudflare SDK — D1 was reached over its REST API. - Scrape target:
web.telegram.orgin glastrier's Chromium, CDP at10.21.4.4:9223. No Telegram API credentials; the logged-in browser session is the auth. - Parser: in-process rules (
internal/parse) + gap-fill LLMdeepseek-v4.1-flash, called throughhermes -z(CLI backend) so no model key is stored in the project; HTTP backend exists as a fallback. - Database: PostgreSQL on Patroni (glastrier), reached through HAProxy write pool. App role
badminton, TLS on. - Edge: Cloudflare Pages project
badminton-sg, TypeScript Pages Function,pg(node-postgres) on thenodejs_compatflag. - Connectivity: Hyperdrive binding
HYPERDRIVE(94f81a6f5bc04c4290056f33d29a8998) → Workers VPC TCP service → HAProxy → Patroni primary. - Orchestration: Hermes cron job (no-agent mode) →
scripts/badminton_sync.sh; deployment viawrangler pages deploy. - Legacy: Cloudflare D1 database
badminton(57b01b17-eeab-4a7e-b67e-b86df5fc6e64, 10.4 MB) — read-only, no longer written.
System architecture
Ingest flow
flocktakes the run lock; preflight checks the env file, Cloudflare token + account id, and CDP/json/version.scrapewalks forward to the newest message, then back to the date floor, writing one JSONL per run and updatingstate/state.json.parseloads every raw message, runs rules, then asks the model only about incomplete unique texts (cache first).exportwrites the 60-day public snapshot and strips post text.pgloadupserts sessions, appends raw messages, prunes rows outside the window.- Deploy runs only if the snapshot hash changed; a Telegram digest closes the run.
Current status
Verified working
- Site: badminton-sg.pages.dev returns 200 with the live page; API reports
source: hyperdrive, i.e. reads are hitting Patroni, not the static fallback. - Read path: date-range, level and text filters all return expected subsets; 3 sessions found for 2026-11-18 at time of check.
- Write path: the load pass upserts in ~1 s and reports new/updated/pruned counts (last run: 74 new, 4,562 updated, 7 pruned); 777 venues and 15,931 raw messages are present.
- D1 decommissioned from writes: the 07:00 SGT run failed on D1's free-tier daily row-write cap (every run rewrites ~5.4k rows); the script was repointed to
pgloadat 15:17 SGT. Next scheduled run is 21:00 SGT.
Known issues
- Time inference errors: 67 sessions have a start hour before 07:00 or at/after 23:00 — mostly am/pm ambiguity in posts like "2 to 4". The 2026-11-18 02:00 entry is one of them.
- Missing fields: 41 sessions have no start time; prices parsed for 2,293/4,873 (47%); courts for 833.
- Assumptions are common: 199 assumed dates and 399 assumed end times. They are flagged in the UI but still inferred.
- Poster handles: 0 resolved of 751 posters, so every contact is a permalink rather than a direct Telegram handle.
- No custom domain:
badminton.rowentey.xyzhas no DNS record and is not attached to the Pages project. - Fragile scrape dependency: the pipeline needs a logged-in Telegram Web session in glastrier's Chromium; if that session expires, runs fail loudly but produce no data.
DB schemas
Postgres is authoritative. Migrations are additive, applied in filename order by cmd/pgmigrate; applied files are never edited. The D1 schema mirrors this with text/blob types and is frozen.
-- db/pg/0001_init.sql (abridged: indexes elided) CREATE TABLE raw_messages ( message_id bigint PRIMARY KEY, posted_day date, posted_time text, sender_name text, text text NOT NULL, -- phones redacted at capture text_hash text NOT NULL, -- sha1, detects edits on re-scrape has_media boolean NOT NULL DEFAULT false, permalink text NOT NULL, scraped_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE venues ( venue_key text PRIMARY KEY, -- slug, e.g. sbh-geylang name text NOT NULL DEFAULT '', area text, aliases jsonb NOT NULL DEFAULT '[]'::jsonb, lat double precision, lon double precision, curated boolean NOT NULL DEFAULT false, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE sessions ( id text PRIMARY KEY, -- sha1(date|start|venue_key|poster_key) message_id bigint NOT NULL, poster_name text, poster_handle text, contact_url text, -- t.me permalink session_date date, start_time text, -- HH:MM, 24h SGT end_time text, end_assumed boolean NOT NULL DEFAULT false, date_assumed boolean NOT NULL DEFAULT false, is_recurring boolean NOT NULL DEFAULT false, rrule text, venue_key text, venue_raw text, courts integer, courts_note text, level_raw text, level_norm text, -- HB|LI|MI|HI|ADV|ANY (+ ranges) price_raw text, price_cents integer, price_basis text, -- per_pax|per_court|unknown shuttle_brand text, slots_raw text, parse_status text NOT NULL, -- ok|partial|needs_review|skip confidence real, extra_message_ids jsonb NOT NULL DEFAULT '[]'::jsonb, issues_json jsonb NOT NULL DEFAULT '[]'::jsonb, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE runs ( id bigserial PRIMARY KEY, started_at timestamptz NOT NULL DEFAULT now(), finished_at timestamptz, kind text NOT NULL, -- scrape|parse|upsert messages_seen integer NOT NULL DEFAULT 0, sessions_new integer NOT NULL DEFAULT 0, sessions_updated integer NOT NULL DEFAULT 0, status text NOT NULL, -- running|ok|failed note text ); CREATE TABLE parse_log ( message_id bigint NOT NULL, parser_version text NOT NULL, model text, fields_json jsonb, issues_json jsonb, created_at timestamptz NOT NULL DEFAULT now(), PRIMARY KEY (message_id, parser_version) ); CREATE TABLE schema_migrations ( name text PRIMARY KEY, applied_at timestamptz NOT NULL DEFAULT now() );
Indexes that matter
sessions(session_date),(venue_key),(message_id),(parse_status),(level_norm),(level_raw).raw_messages(posted_day)plus a GIN index onto_tsvector('simple', text)for corpus search.- D1 legacy:
badminton(57b01b17…) still holds v1 tables; no writes, no reads from the app.
API endpoints
One public read endpoint plus two static artifacts. No authentication; responses are cacheable for 60 s and CORS-open so the page and third parties can call it.
level_norm match or prefix match (so HB also returns HB-LI).venue_raw and poster_name. Post text is never searched or returned.Response shape
{
"generated_at": "2026-11-18T07:28:11.803Z",
"source": "hyperdrive", // or "snapshot" when degraded
"counts": { "sessions": 3 },
"days": { "2026-11-18": 3 }, // counts for day chips
"sessions": [{
"id": "d3a7d26843954466",
"message_id": "1265393",
"poster_name": "Ke Wang",
"poster_handle": null,
"contact_url": "https://t.me/sgbadmintontelecom/1265393",
"session_date": "2026-11-18",
"start_time": "02:00", "end_time": "04:00",
"end_assumed": true, "date_assumed": false,
"is_recurring": false,
"venue_key": "bu-kit-canberra-sport-hall",
"venue_raw": "Bu kit Canberra Sport hall",
"courts": null, "level_raw": "MB", "level_norm": "HB",
"price_raw": null, "price_cents": null, "price_basis": null,
"parse_status": "ok", "confidence": 0.9,
"issues": ["end_assumed"]
}]
}
- Not exposed: raw post
text, sender ids, phone numbers,extra_message_ids. - Errors:
500if the Hyperdrive binding is missing,503withdetailif the query fails. The page falls back to the snapshot client-side.
Scheduled jobs
Hermes cron: badminton-sync
- Schedule:
0 7,21 * * *(07:00 and 21:00 Asia/Singapore), no-agent mode, job id010456d27458. - Script:
/opt/data/scripts/badminton_sync.sh— one lock, one log per run inprojects/badminton-sessions/logs/, digest to Telegram viahermes send. - Single instance:
flock -n /opt/data/run/badminton-sync.lock; a slow run makes the next one exit 0 rather than overlap. - Preflight: Cloudflare env file readable, token + account id present, CDP
10.21.4.4:9223reachable within 8 s; each failure notifies and exits 1. D1 credentials are no longer checked.
Steps, in order (each gates the next)
sync scrape -until <today> -max-duration 20m— newest-message walk then backfill.sync parse -llm-limit 400 -batch 25 -workers 3— rules + cached LLM gap fill (~2 min typical).sync export -out preview— 60-day window, hashed before/after for the deploy gate.sync pgload -dsn /opt/data/.postgres_badminton.env— upsert + prune into Patroni.wrangler pages deploy— only whenpreview/sessions.jsonchanged; writesstate/deployed.sha256.- Digest:
badminton-sync: <time> ok · <scrape/parse summary> · URL.
D1_DATABASE_ID is no longer required). The database itself stays read-only during a soak
period; delete it after a couple of weeks of clean Hyperdrive loads, together with cmd/migrate.Gaps & next steps
- Fix time inference — treat bare "2 to 4" as afternoon when the venue context implies evening; re-parse the 67 implausible rows and track the count as a release gate.
- Attach the domain — add
badminton.rowentey.xyzas a Pages custom domain and a proxied CNAME; verify withcurl --resolveafter the ~30–45 s activation lag. - Add
/api/sessions.ics— deterministic UID (session.id), one VEVENT per session,SEQUENCEfromupdated_at; this closes the calendar gap from the original spec. - Backfill venue coordinates — 777 venues are known by name; geocode once into
venues.lat/lon, then add a distance filter. - Resolve poster handles — parse
@handlementions during the rules pass; fall back to the permalink. - Dead-man's switch — a separate daily check that the newest
raw_messages.scraped_atis under 24 h old, alerting on silence rather than on errors. - Retire D1 fully — after ~2 weeks of clean Hyperdrive loads, delete the
badmintonD1 database, dropD1_DATABASE_IDfrom the env file, and removecmd/migrate/internal/d1.