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.

live 4,873 sessions 777 venues Postgres + Hyperdrive custom domain not attached

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.

Features

Shipped

Planned, not built

Tech stack

System architecture

INGEST · GLASTRIER BROWSER + HERMES POD Telegram Web web.telegram.org @sgbadmintontelecom logged-in session Chromium CDP :9223 · health gate PUBLIC POSTS → GO SYNC CLI · FAIL-FAST STEPS 1 scrape walk newest → backfill to floor 2 raw/*.jsonl hashed · phones redacted 3 parse rules → LLM gap fill (cached) 4 export dedupe · 60-day window Patroni · PostgreSQL HAProxy write pool 10.21.100.1:7000 · db badminton role badminton · TLS SOURCE OF TRUTH pgload D1 badminton: legacy, read-only CLOUDFLARE EDGE → CLIENT badminton-sg.pages.dev day chips · search · level links to t.me post static sessions.json fallback Pages Function GET /api/sessions pg · CORS · 60 s cache from / to / q / level / limit Hyperdrive binding HYPERDRIVE VPC TCP → HAProxy 94f81a6f… JSON pg SELECT rows wrangler deploy · snapshot (fallback) — data path - - deploy / fallback artifact Phones and post text never leave the ingest host
Ingest runs in the Hermes pod against glastrier's browser; reads never touch the origin database directly — the Pages Function goes through Hyperdrive, and the static snapshot is the degraded-mode fallback.

Ingest flow

  1. flock takes the run lock; preflight checks the env file, Cloudflare token + account id, and CDP /json/version.
  2. scrape walks forward to the newest message, then back to the date floor, writing one JSONL per run and updating state/state.json.
  3. parse loads every raw message, runs rules, then asks the model only about incomplete unique texts (cache first).
  4. export writes the 60-day public snapshot and strips post text.
  5. pgload upserts sessions, appends raw messages, prunes rows outside the window.
  6. Deploy runs only if the snapshot hash changed; a Telegram digest closes the run.
The pipeline is fail-fast per step: a failed scrape never parses, and a failed load never deploys. That keeps the live site consistent with the last good database state.

Current status

4,873sessions stored
Postgres, API-verified · 56 days
777venue keys
751 distinct posters
15,931raw messages
7 JSONL runs, since 2026-06-03
95.5%parsed ok
4,652 ok · 221 partial

Verified working

Known issues

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

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.

GET/api/sessions
Returns sessions ordered by date, start time, venue. Live query through Hyperdrive; includes per-day counts.
GET/api/sessions?from=YYYY-MM-DD&to=YYYY-MM-DD
Inclusive date range. Invalid dates are ignored rather than rejected.
GET/api/sessions?level=HI
Exact level_norm match or prefix match (so HB also returns HB-LI).
GET/api/sessions?q=geylang
Case-insensitive substring over venue_raw and poster_name. Post text is never searched or returned.
GET/api/sessions?limit=2000
Default 2000, clamped to 5000. Non-numeric or 0 falls back to the default.
GET/sessions.json
Static snapshot from the last export (60-day window, text stripped). Degraded-mode fallback when the database is unreachable.
GET/
Single-page list: day chips, search, level filter. Pure static HTML + fetch.

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"]
  }]
}

Scheduled jobs

Hermes cron: badminton-sync

Steps, in order (each gates the next)

D1 decommission: removed from the write path on 18 Nov 2026 and now out of preflight too (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

  1. 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.
  2. Attach the domain — add badminton.rowentey.xyz as a Pages custom domain and a proxied CNAME; verify with curl --resolve after the ~30–45 s activation lag.
  3. Add /api/sessions.ics — deterministic UID (session.id), one VEVENT per session, SEQUENCE from updated_at; this closes the calendar gap from the original spec.
  4. Backfill venue coordinates — 777 venues are known by name; geocode once into venues.lat/lon, then add a distance filter.
  5. Resolve poster handles — parse @handle mentions during the rules pass; fall back to the permalink.
  6. Dead-man's switch — a separate daily check that the newest raw_messages.scraped_at is under 24 h old, alerting on silence rather than on errors.
  7. Retire D1 fully — after ~2 weeks of clean Hyperdrive loads, delete the badminton D1 database, drop D1_DATABASE_ID from the env file, and remove cmd/migrate / internal/d1.