Oche - Game Session Dashboard

A live scoreboard for competitive-socialising sessions (darts, golf, and friends): see players and scores update in real time, edit scores, browse past matches, and watch the game video.

Built for the 501 Entertainment Cloud Developer take-home - the same domain as the 501 Hub (venue-facing live games + match history). Stack choices favour edge latency, secure multi-tenant data, and practical media handling.

Submitting? Start with SUBMISSION.md - one-page coverage of the brief, live URLs, and graded README pointers.

Live app	https://oche.humza-butt.space
Staging	https://oche-staging.humza-butt.space
API	https://oche-api.humza-butt.space · OpenAPI /docs
Contributing	CONTRIBUTING.md · Code of Conduct · Security

Stack

Vite + React 19 SPA (TanStack Query, Tailwind v4 + shadcn) · Hono on Cloudflare Workers · Neon Postgres via Hyperdrive with Row-Level Security on every table (Drizzle) · SQLite-backed Durable Object for hibernatable WebSockets · R2 media · staging + production on Cloudflare. See ARCHITECTURE.md.

Quick start

Works on macOS, Linux, and Windows (PowerShell). No cp or psql required - use the npm scripts.

npm run setup                  # install + .env / .dev.vars templates
# Fill DATABASE_URL (+ DATABASE_URL_STAGING for deploy) in .env, then:
npm run setup:dev-vars:sync    # sync secrets into apps/api/.dev.vars (JWT + media signing for local venue switch)
npm run cf:sync:dry            # preview what would upload to Cloudflare Workers + Pages
npm run cf:sync                # push secrets/vars from .env → staging + production
npm run db:prepare             # migrate + force-rls + seed (one command)
npm run dev                    # web :5173 + api :8787
npm run check                  # typecheck + lint + unit tests + bundle budget
npm run db:rls:check           # confirm RLS is enabled + forced everywhere

First deploy to staging (full pipeline):

npm run deploy:staging:full    # migrate + force-rls + rls check + seed + deploy

See docs/ENVIRONMENTS.md for staging vs production URLs and secrets.

API

Method	Path	Purpose
GET	/sessions	list sessions (owner-scoped, paginated)
GET	/sessions/:id	one session + players + recent score events
POST	/sessions	create a session with players
PATCH	/sessions/:id	update status and/or player scores (broadcasts live)
WS	/sessions/:id/live	live score/status stream (Durable Object)

All bodies are Zod-validated (strict); errors return { error, issues? }. Every request resolves a principal and runs DB work inside withPrincipal() so RLS enforces isolation.

---

Frontend performance optimisation

Venue operators expect instant score feedback and smooth scrolling through long match histories. The SPA uses route-level code splitting (lazy routes), TanStack Query for caching, request dedupe, and optimistic score updates with rollback on error, and a virtualised history list (@tanstack/react-virtual) with cursor pagination. Player avatars use srcset where the CDN supports it, with fixed width/height to avoid layout shift, plus loading="lazy" and decoding="async". Video uses preload="metadata" until the user plays. CI enforces a bundle-size budget (npm run size). A PWA service worker caches read-only session lists for flaky venue Wi‑Fi.

Efficient handling of images & video

Game photos and session video are served from R2 on Cloudflare’s CDN (zero egress fees on R2). Objects use immutable hashed keys and long cache lifetimes where public. Player photos use responsive srcset; private uploads use signed, short-lived URLs. Video is MP4 with HTTP byte-range so seeking does not download the whole file - poster frame and metadata-only preload first. The schema supports HLS (hls_url); an offline FFmpeg script (npm run media:transcode) demonstrates transcoding without running paid Container workers in the take-home. Upload from the UI validates mime type and size.

What I'd do differently at scale

At demo scale the current design is sufficient; at 501’s venue volume I would add: keyset pagination with composite indexes (partially done); Postgres read replicas behind Hyperdrive pooling; queue-based transcoding (R2 event → Queue → FFmpeg Container, retries/DLQ, 360p/720p/HLS renditions); Durable Object sharding + hibernation for WebSocket rooms (or pub/sub fan-out); KV edge cache for hot GET /sessions/:id; structured tracing across Worker → DO → Hyperdrive → Neon with error budgets; per-IP rate limiting in a Durable Object instead of in-memory per isolate. See docs/PERFORMANCE.md and docs/SCALE.md.

Security concerns & production mitigations

501 systems handle player data across 40+ countries - the design assumes mistakes in application code and enforces isolation in the database.

• Input validation - Zod .strict() on every body; unknown keys rejected.
• Row-Level Security - every table RLS-enabled and forced; the app connects as a NOBYPASSRLS role; access keyed on a transaction-scoped app.current_owner, so a forgotten WHERE cannot leak another venue’s data. score_events is append-only. (Take-home: GUC from API key header. Production: JWT → Neon authenticatedRole + authUid().) See docs/RLS.md.
• SQL injection - parameterised Drizzle queries only.
• XSS - React escaping + strict CSP; CORS allowlisted per environment.
• Media - signed, expiring URLs; strict mime/size validation on upload.
• PII / GDPR - player names and photos are personal data: minimisation, retention limits, erasure path. Staging data is disposable and never seeded from production.
• Secrets & isolation - Wrangler secrets per environment; staging and production fully isolated (Neon branch, Worker env, R2 bucket each).
• Production hardening - real auth + RBAC, WAF, secret rotation, monitoring/alerting, backups, least-privilege Cloudflare tokens, dependency scanning. See SECURITY.md and docs/AUTH.md.

---

Project layout & docs

apps/web (SPA) · apps/api (Hono Worker + Durable Object) · packages/db (schema + RLS) · packages/shared (Zod + WS types) · scripts (DX) · docs/ (ADRs, diagrams, roadmap & checklists). Build it phase-by-phase with docs/PROMPTS.md.

Community: CONTRIBUTING.md · CODE_OF_CONDUCT.md · .github/SUPPORT.md · SECURITY.md

License: MIT.