SportRealTime — Real-time Sports Commentary App ⚽🏀🏈

A Node.js + Express app for live sports match management and real-time commentary over WebSockets, with auth, subscriptions, notifications, and structured match events — backed by PostgreSQL (Neon) via Drizzle ORM, seeded with real fixtures/results from TheSportsDB.

---

✨ Key Features

• Express REST API — matches, commentary, structured match events, subscriptions, notifications
• Auth — JWT-based register/login/logout, session tracking, role-based access (viewer / commentator / admin)
• WebSocket server (via ws) for real-time broadcasts (/ws) — match creation, commentary, structured events, score/status updates
• Real sports data — fixtures and results pulled from TheSportsDB's free API (top European soccer leagues) instead of static seed data
• PostgreSQL + Drizzle ORM (drizzle-orm/node-postgres) — works with any Postgres, including a free Neon project
• Migrations using drizzle-kit
• Zod validation schemas for requests
• APM (apminsight) integration via environment variable (APM_LICENSE_KEY) (optional)
• Arcjet middleware used for request protection/rate limits (optional)
• React + Vite client (client/) — matches list, match detail, subscriptions, notifications, profile

---

📁 Project Layout (important files)

• src/
  • index.js — Express server + WebSocket attach + CORS
  • ws/server.js — WebSocket server + subscription management
  • routes/ — auth.js, matches.js, commentary.js, events.js, subscriptions.js, notifications.js
  • db/
    • db.js — Postgres pool (pg) + Drizzle instance
    • schema.js — DB schema (users, matches, commentary, subscriptions, match_events, notifications)
  • validation/ — Zod schemas per route
  • services/sportsdb.js — TheSportsDB API client + event→match mapper
  • seed/seed.js — pulls real fixtures/results and simulates one match live
  • arcjet.js — Arcjet protection middleware
• drizzle.config.js — Drizzle Kit config for migrations
• client/ — Vite React app
• .env — environment variables (ignored by git)

---

🚀 Quickstart

Prerequisites: Node.js (LTS), npm, a PostgreSQL database (local, or a free Neon project)

1. Install dependencies

npm install

2. Add environment variables (create .env at project root — see .env.example)

# Postgres connection string (Neon example includes ?sslmode=require)
DATABASE_URL=postgresql://user:password@localhost:5432/sportrealtime

# Server
PORT=8000
HOST=0.0.0.0

# JWT
JWT_SECRET=change-this-to-a-long-random-secret
JWT_EXPIRES_IN=7d

# TheSportsDB — free public test key works out of the box
THESPORTSDB_API_KEY=123

# Optional APM / Arcjet
APM_LICENSE_KEY=
ARCJET_KEY=

Note: Do not commit .env with secrets.

3. Generate / apply migrations

npm run db:generate
npm run db:migrate

4. Run the server

npm run dev   # development with watcher
npm start     # production-mode

5. Seed real fixtures (requires running server)

npm run seed

6. Run the client

cd client
npm install
npm run dev

---

📦 API Overview

• GET / — health/status,
• /auth — register, login, logout, me, profile update
• /matches — list (query: limit, status, sport, search), get by id, create, patch score, patch status
• /matches/:id/commentary — list/create commentary
• /matches/:id/events — structured match events (auth required; commentator/admin to post)
• /subscriptions — authenticated subscription CRUD
• /notifications — authenticated notifications

WebSocket endpoint: ws://<host>/ws (?token=<jwt> for authenticated subscription restore)

• Client → server: { type: 'subscribe', matchId }, { type: 'unsubscribe', matchId }
• Server → client: match_created, commentary, match_event, match_status, subscriptions_restored

---

🌐 Deployment (Render + Neon)

• Database: create a free Neon project, copy the pooled connection string into DATABASE_URL.
• API: Render Web Service — build npm install, start npm start. Set DATABASE_URL, JWT_SECRET, THESPORTSDB_API_KEY, and CORS_ORIGIN (the client's Render Static Site URL) as environment variables.
• Client: Render Static Site — root client/, build npm run build, publish dir dist. Set VITE_API_URL and VITE_WS_URL to the API service's URL/host.

See LLM_DEPLOYMENT_CONTEXT.md for a fuller deployment reference.

---

🔒 Security & Secrets

• APM key rotation: If a secret is accidentally committed, rotate it immediately, remove it from repo, and scrub git history (use git filter-repo or BFG).
• .gitignore includes .env and apminsight.json to avoid committing secrets.
• Arcjet middleware is used for rate-limiting / bot protection (enabled only if ARCJET_KEY set).

---

🛠️ Troubleshooting & Notes

• If drizzle-kit isn't found during npm run db:migrate, ensure drizzle-kit is installed as a dev dependency.
• If migrations fail, confirm DATABASE_URL is reachable and includes ?sslmode=require for managed Postgres (Neon).
• If npm run seed reports no fixtures, check network access to thesportsdb.com and THESPORTSDB_API_KEY.
• If imports fail (module not found), run npm install to update node_modules and commit package.json/package-lock.json.

---

✅ Suggested Next Steps

• Add unit/integration tests for route handlers and validation
• Add CI (GitHub Actions) to run lint/tests and migrations against a test DB
• Add pagination & cursors to commentary listing
• Add DB indexes for performance (e.g., commentary(match_id, created_at desc))

---

📬 Contributing

PRs welcome. For security-sensitive changes (rotating keys, scrubbing secrets), coordinate changes with the team and do not push secrets to the repo.