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.