PostgreSQL & Claude Power an Automated AI Pipeline for Horse Race Predictions

Over the past six months, a developer has been building an AI system for horse racing predictions. This is not a simple "past results → prediction" model, but rather a multi-stage pipeline encompassing data quality management, feature engineering, Claude inference, and ranked recommendations.

Here's what was learned during its development.

Architecture Overview

The core architecture of the AI prediction system is structured as follows:

• Data Scrape: netkeiba scrape
• Data Storage: PostgreSQL (horse_races / horse_entries)
• Daily Batch Processing: fetch_horse_racing.py
• AI Inference Endpoint: Supabase Edge Function (ai-hub: horse.predict)
• LLM Inference: Claude haiku
• Results Ensemble: horse_race_predictions_ensemble
• Accuracy Evaluation: evaluate_accuracy.ts (weekly evaluation)

Data Quality Score (DQS)

Prediction accuracy is primarily determined by data quality. The developer scores 15 fields to produce a DQS (0-100). A partial example of the DQS calculation logic is shown below:

(  CASE WHEN weight IS NOT NULL THEN 10 ELSE 0 END +  CASE WHEN weight_diff IS NOT NULL THEN 10 ELSE 0 END +  CASE WHEN last_3f IS NOT NULL THEN 15 ELSE 0 END +  CASE WHEN prev_last_3f IS NOT NULL THEN 10 ELSE 0 END +  CASE WHEN jockey_id IS NOT NULL THEN 10 ELSE 0 END +  CASE WHEN trainer_id IS NOT NULL THEN 10 ELSE 0 END +  CASE WHEN odds IS NOT NULL THEN 15 ELSE 0 END  -- + 8 more fields...) AS data_quality_score

Entries with a DQS below 60 are skipped. This single filter improved prediction accuracy more significantly than any changes made to the prediction model itself.

Feature Engineering: Ranking Score

Eight factors are weighted based on their empirical contribution to generate a ranking score. Key factors and their weights include:

• Historical place rate: 25% (Most stable signal)
• Final 3F time (last_3f): 20% (Late speed is a predictive indicator)
• Inverse of odds: 15% (Market wisdom)
• Jockey win rate: 15% (Jockey effect is substantial)
• Weight change: 10% (Condition signal)
• Last 3F vs previous race: 10% (Momentum trend)
• Best time record: 5% (Ceiling indicator of performance)

Claude Inference Prompt

Claude is utilized to generate explanations, not just scores. The structure of the inference prompt is as follows:

const prompt = `You are a horse racing prediction specialist.[RACE INFORMATION]<<>>${raceInfo}<<>>[HORSE DATA]<<>>${horseData}<<>>Recommend top 3 horses considering:1. Prioritize horses with DQS >= 702. Emphasize best time record and final 3F3. Flag weight changes of ±10kg as risk factors4. Explain each recommendation in under 100 charactersOutput format: JSON`;

The <<>> blocks within the prompt are crucial for protecting against prompt injection from scraped race data.

Solving the N+1 Query Problem

The initial implementation suffered from an N+1 query problem, resulting in 2 queries per race for 50 races, totaling 100 queries per evaluation run.

// Before: N+1 queriesfor (const race of races) {  // query for race details  // query for horse entries}

To optimize this, the developer leveraged PostgreSQL's WITH RECURSIVE CTE (Common Table Expression) and the JSONB_AGG function to consolidate multiple queries into a single, efficient operation:

-- After: Single optimized queryWITH race_data AS (  SELECT    r.id AS race_id,    JSONB_BUILD_OBJECT(      'race_details', r,      'horse_entries', (        SELECT JSONB_AGG(he)        FROM horse_entries he        WHERE he.race_id = r.id      )    ) AS data  FROM    races r  WHERE    r.date = '2023-10-26')SELECT JSONB_AGG(data) FROM race_data;

This approach retrieves all race details and their respective horse entries in a single database roundtrip, significantly enhancing data fetching efficiency.