overview

Overview

Version 1 · Initial project overview derived from trainer_stats_api.py, CLAUDE.md, and FULL_PROJECT_DOCUMENTATION.md

Historical version

Stats (Meeting Stats API)

Single-file FastAPI service (trainer_stats_api.py, app title "Meeting Stats API", currently v2.0.0) that computes trainer and jockey performance statistics for horse-racing meetings — win/place rates, impact values by SP price band, meeting averages, top horses — and optionally persists/serves those stats. It also serves a small static dashboard, login page, and interactive API docs/test pages from static/.

There is no separate module structure; the entire backend lives in one file.

Architecture / data flow

Source DB (read-only: race, run, courses)
        │  fetch_trainer_rows() / fetch_jockey_rows()
        ▼
build_entity_payload()   <- pure Python, no DB dependency; same function
  (calc_wps, calc_race_wps,   drives both trainers and jockeys by swapping
   calc_price_band_ivs, ...)  key names
        │  JSON payload
        ▼
Returned directly to caller (GET), or persisted via POST /save-stats
into Target DB: meeting_stats_payloads

build_entity_payload is the key architectural seam — it can be fed rows from the DB or (in principle) from a JSON payload with the same shape.

Optional external API bypass: if config.json has external_api.enabled: true, the /trainerStats, /jockeyStats, /meetingStats endpoints fetch already-computed stats from a third-party API instead of querying the local DB (fetch_meeting_stats_from_external_api). It logs in, caches the resulting JWT until near expiry, and retries once on a 401. When disabled (the default), these endpoints fall back to the local DB + local calculation path.

Two DB connections

Source DB (db_source_connection): read-only, queries race, run, courses.
Target DB (db_target_connection): write, stores results in meeting_stats_payloads (auto-created via CREATE TABLE IF NOT EXISTS; one row per (meeting_date, course_name), re-save overwrites via ON DUPLICATE KEY UPDATE).
Both configured under config.json → database.source_db / database.target_db; can point at the same database.

Endpoint naming — two parallel sets

/trainer-stats, /jockey-stats (kebab-case): always compute locally from the DB, accept courseName or courseId, no external API bypass, no USER_CONTEXT tracking.
/trainerStats, /jockeyStats, /meetingStats (camelCase): the "public" endpoints meant for external consumers; accept only courseId; check the external API first if enabled; update USER_CONTEXT. Use these for external integrations — they're the ones documented at /api-docs.

Stats calculation (pure Python, no DB dependency)

calc_wps — runner-level wins/places/starts.
calc_race_wps — race-level WPS, deduplicating multiple runners per race for the same entity.
calc_meeting_averages — avg runners/races/wins per meeting across the 365-day lookback.
calc_price_band_ivs — Impact Value per SP price band; entity win rate vs. baseline win rate across all_rows (the entity's full historical dataset). Bands (PRICE_BANDS): 1.01-1.99, 2.00-4.50, 4.60-10.00, 10.50-25.00, 26.00+.
Other per-entity fields: wins_30d, avg_finishing_pos, last_win_date, top_horses (top 3 by win %).

SQL pattern

Trainer/jockey history queries use a CTE (target_trainers / target_jockeys) to first identify which entities are running at the target meeting, then fetch all their historical rows within a 365-day lookback window from run. Filtering is by course_id (int) if provided, else by course_code/course_name (string).

Configuration

Copy config.example.json → config.json before running (git-ignored). Path can be overridden with DB_CONFIG_PATH; every DB field can also be overridden via env var: DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, DB_SOURCE, DB_TARGET. external_api block is optional — omit or set enabled: false to always use the local DB path.

Auth

Single shared static bearer token (tds-stats-secure-token-2026), checked by the verify_token dependency. Accepted via Authorization: Bearer, ?token=, or ?sso_token= query param. POST /login with admin/admin123 (hardcoded) simply returns this same static token — there is no per-user session, expiry, or revocation mechanism. This is a shared API key, not real authentication.

The only server-side state is USER_CONTEXT — an in-memory dict keyed by client IP, set on /trainerStats, /jockeyStats, /meetingStats calls, expiring after 5 minutes, read via GET /api/user-context. Purely a UI convenience cache (lets the UI restore "last meeting queried"); not persisted across restarts and not an auth mechanism.

GET /login redirects to an external Appsmith SSO login when the request looks Appsmith-embedded (referer contains appsmith, no ?direct=). POST /api/logout proxies to an external auth service by sessionId.

Frontend

Static files served from static/ (mounted at /static), no build step:

index.html → / — main dashboard (searchable trainer/jockey grids, filters, detail panel)
login.html → /login fallback — local login form
api_docs.html → /api-docs — hand-written interactive endpoint reference with "Try it"
api_test.html → /api-test — manual API tester
query_executor.html — ad-hoc query tool, not routed

Running

pip install fastapi uvicorn pymysql pydantic python-multipart
copy config.example.json config.json   # then fill in real credentials
uvicorn trainer_stats_api:app --host 0.0.0.0 --port 8006 --reload
# or: python trainer_stats_api.py

Access: http://127.0.0.1:8006/ (dashboard), /docs (Swagger), /redoc (ReDoc), /api-docs (custom reference).

As a persistent Windows service:

.\.venv\Scripts\python.exe run_service.py   # or .\run_service.bat

Install/uninstall as Administrator: install_service.bat / uninstall_service.bat.

Testing

test_db_connection.py — verifies DB connectivity using config.json
test_api.py — exercises API endpoints
quick_test.py — basic smoke test
regenerate_sample.py — regenerates trainers_complete.json sample data for local UI testing without a DB

Security notes

Single shared static token: anyone with it has full access to every endpoint; no per-user scoping, expiry, or revocation short of changing the constant and redeploying.
Hardcoded login credentials (admin/admin123) are effectively decorative — /login returns the same static token regardless of who logs in.
config.example.json must never contain real credentials — it is the template only.
config.json should stay out of version control; prefer env vars in production.
DB queries use parameterized queries (PyMySQL %s/%(name)s placeholders) — no string-interpolated SQL found; keep this pattern for new queries.
CORS is currently wide open (allow_origins=["*"], allow_credentials=True) — acceptable for an internal token-gated API, worth tightening before wider exposure.