Database

MySQL/MariaDB, accessed via PyMySQL with DictCursor and autocommit=True. See architecture for how the two connections fit into the overall data flow.

Configuration

config.json (git-ignored; copy from config.example.json):

{
  "database": {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "your_username",
    "password": "your_password",
    "source_db": "your_source_db",
    "target_db": "your_target_db"
  }
}

get_db_config() layers env vars over this: DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, DB_SOURCE, DB_TARGET. If unset, source_db/target_db default to "temp-qdg". Config file path can be overridden with DB_CONFIG_PATH (default config.json).

Source DB — tables read

Per schema.json, the wider schema includes meeting, race, and run; a courses table also exists (not in schema.json, joined by id). The API currently reads only race, run, and courses — meeting is not queried anywhere in trainer_stats_api.py.

Columns actually consumed from run (aliased in the trainer/jockey history query): race_id, race_date, race_number (→ race_no), trainer_id, trainer_name, jockey_id, jockey_name, runner_id, runner_name, finish_position (→ finish_pos), starting_price_value (→ sp_price), course_id.

From race: id (join key), rmeeting_id (→ meeting_id), distance.

From courses: id (join key), name.

Comments in the code (build_trainer_history_sql / build_jockey_history_sql) claim run is indexed on race_date, trainer_id/jockey_id, course_id, and race_id — this is asserted in comments only and hasn't been independently verified against the live schema.

History query pattern (CTE)

build_trainer_history_sql / build_jockey_history_sql (near-identical, one per entity type):

WITH target_trainers AS (
    SELECT DISTINCT ru.trainer_id
    FROM run ru
    WHERE ru.course_id = %(course_value)s
      AND ru.race_date >= %(target_date)s AND ru.race_date < %(target_date_plus_1)s
      AND ru.trainer_id IS NOT NULL
)
SELECT
    r.rmeeting_id AS meeting_id, DATE(ru.race_date) AS race_date, c.name AS race_course,
    ru.course_id AS race_courseid, ru.race_number AS race_no, r.distance AS race_dist,
    ru.trainer_id AS trainer_id, ru.trainer_name AS trainer, ru.runner_id AS runner_id,
    ru.runner_name AS runner, ru.finish_position AS finish_pos, ru.starting_price_value AS sp_price
FROM run ru
JOIN race r ON r.id = ru.race_id
JOIN courses c ON ru.course_id = c.id
WHERE ru.trainer_id IN (SELECT trainer_id FROM target_trainers)
  AND ru.race_date >= %(lookback_start)s AND ru.race_date < %(target_date)s

Two-step logic: first find which trainers/jockeys are running at the target meeting (target_date, one calendar day), then pull all of those entities' rows over the prior 365 days (lookback_start = target_date - 365 days) — this lookback set is what feeds build_entity_payload and becomes the price-band IV baseline (see architecture).

Known quirk — course_value typing when only courseName is given

fetch_trainer_rows / fetch_jockey_rows set the course_value bind parameter to course_id when a course_id is supplied, otherwise to course_name (a string). But build_trainer_history_sql(use_course_id) / build_jockey_history_sql(use_course_id) build their target_filter as:

target_filter = "ru.course_id = %(course_value)s" if use_course_id else "ru.course_id = %(course_value)s"

Both branches of this ternary are identical — the query always filters on the integer ru.course_id column, even when use_course_id is False and course_value is actually a course name string. This is only reachable via /trainer-stats / /jockey-stats when called with courseName and no courseId (the camelCase endpoints only ever pass course_id, never hit this path). Observed directly in the current source on 2026-08-12; not confirmed whether this is an intentional fallback (relying on MySQL's implicit string→int coercion) or a bug that silently returns no/wrong rows for courseName-only requests. Flag before relying on the courseName query parameter.

Target DB — table written

meeting_stats_payloads, created on first /save-stats call:

CREATE TABLE IF NOT EXISTS meeting_stats_payloads (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    meeting_date DATE NOT NULL,
    course_name VARCHAR(100) NOT NULL,
    generated_at DATETIME NOT NULL,
    payload_json JSON NOT NULL,
    UNIQUE KEY uq_meeting (meeting_date, course_name)
)

One row per (meeting_date, course_name). persist_meeting_payload() inserts with ON DUPLICATE KEY UPDATE generated_at = NOW(), payload_json = VALUES(payload_json) — re-saving the same date+course overwrites in place, no history kept. payload_json holds {"meeting": {"date", "course", "courseId"}, "trainers": [...], "jockeys": [...]} exactly as submitted to /save-stats (not recomputed).

/saved-stats reads it back by (meeting_date, course_name), resolving course_id → name via a SELECT name FROM courses WHERE id = %s against the source DB first. If the table doesn't exist yet, the pymysql.err.ProgrammingError is swallowed and {"status": "not_found"} is returned — same response as a genuine cache miss, so an unset-up target DB looks identical to "nothing saved yet" from the caller's side.

Connection lifecycle

db_source_connection() / db_target_connection() are @contextmanager functions that open a fresh pymysql.connect(...) and close it at the end of each with block — one connection per request, no pooling.