Stakes book and condition book transformations create duplicate purse distribution data

Summary

When running core_planned_race_stakes_book after core_planned_race_condition_book, a unique constraint violation occurs on the planned_race_purse_distribution table.

Error

psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "uq_planned_race_purse_distribution"
DETAIL:  Key (planned_race_id, finish_position)=(7573, 4) already exists.

Root Cause

Both transformation assets can create purse distributions for the same planned race:

1. core_planned_race_condition_book creates planned races and their purse distributions from condition book PDFs
2. core_planned_race_stakes_book attempts to insert purse distributions that may reference the same planned_race_id values

The stakes book asset deletes races where stakes_book_source_file IS NOT NULL before inserting, but this doesn't handle cases where:

• A race was created by condition books (has planned_race_card_id)
• The same race also appears in stakes books and tries to add purse distributions

Affected Files

• systems/equibase_web/src/equibase_web/stakes_core/assets.py - core_planned_race_stakes_book
• systems/equibase_web/src/equibase_web/stakes_core/sql/stakes_books.sql - insert_purse_distributions_from_stakes_books

Proposed Solutions

1. Add ON CONFLICT handling to the purse distribution INSERT statements to handle duplicates gracefully (UPDATE or IGNORE)

1. Improve race matching logic to detect when a stakes book race already exists from condition books and update rather than create duplicates

1. Clear related child data before inserting - when stakes book processing finds matching races, clear their existing purse distributions first

Reproduction Steps

1. Truncate planned race tables
2. Run raw_condition_book asset
3. Run core_planned_race_condition_book asset
4. Run core_planned_race_stakes_book asset → Error occurs

🤖 Generated with Claude Code