Coverage for tipsharks-elo-api / packages / core / storage / models.py: 94%

1"""SQLAlchemy models for TipSharks database schema.""" 

2 

3from enum import StrEnum 

4 

5from sqlalchemy import ( 

6 Boolean, 

7 Column, 

8 Date, 

9 DateTime, 

10 Enum, 

11 Float, 

12 ForeignKey, 

13 Integer, 

14 String, 

15 UniqueConstraint, 

16) 

17from sqlalchemy.dialects.postgresql import JSONB 

18from sqlalchemy.ext.declarative import declarative_base 

19from sqlalchemy.orm import relationship 

20from sqlalchemy.sql import func 

21 

22Base = declarative_base() 

23 

24 

25class EntityType(StrEnum): 

26 """Entity types for ratings.""" 

27 

28 HORSE = "horse" 

29 DRIVER = "driver" 

30 TRAINER = "trainer" 

31 

32 

33class Meeting(Base): 

34 """Racing meeting/meet from TAB API.""" 

35 

36 __tablename__ = "meetings" 

37 

38 id = Column(String(64), primary_key=True, comment="TAB meeting ID") 

39 meeting_date = Column(Date, nullable=False, index=True) 

40 venue = Column(String(255), nullable=False) 

41 category = Column(String(1), nullable=False, index=True, comment="Racing type: T, H, or G") 

42 raw_json = Column(JSONB, nullable=False, comment="Original TAB API response") 

43 

44 created_at = Column(DateTime, server_default=func.now(), nullable=False) 

45 updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now(), nullable=False) 

46 

47 # Relationships 

48 races = relationship("Race", back_populates="meeting", cascade="all, delete-orphan") 

49 

50 def __repr__(self): 

51 return f"<Meeting(id={self.id}, venue='{self.venue}', date={self.meeting_date}, category='{self.category}')>" 

52 

53 

54class Race(Base): 

55 """Individual race within a meeting from TAB API.""" 

56 

57 __tablename__ = "races" 

58 

59 id = Column(Integer, primary_key=True, autoincrement=True) 

60 meeting_id = Column( 

61 String(64), ForeignKey("meetings.id", ondelete="CASCADE"), nullable=False, index=True 

62 ) 

63 tab_event_id = Column(String(64), nullable=True, index=True, comment="TAB event ID") 

64 race_number = Column(Integer, nullable=False) 

65 

66 distance_m = Column(Integer, nullable=True, comment="Distance in meters") 

67 start_type = Column(String(50), nullable=True, comment="Mobile or Standing") 

68 gait = Column(String(50), nullable=True, comment="Pace or Trot") 

69 weather = Column(String(64), nullable=True, comment="Weather at race time") 

70 track_condition = Column(String(64), nullable=True, comment="Track condition") 

71 

72 race_datetime = Column(DateTime, nullable=True, index=True) 

73 raw_json = Column(JSONB, nullable=False, comment="Original TAB API response") 

74 

75 created_at = Column(DateTime, server_default=func.now(), nullable=False) 

76 updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now(), nullable=False) 

77 

78 # Composite unique constraint 

79 __table_args__ = (UniqueConstraint("meeting_id", "race_number", name="uq_meeting_race"),) 

80 

81 # Relationships 

82 meeting = relationship("Meeting", back_populates="races") 

83 starters = relationship("Starter", back_populates="race", cascade="all, delete-orphan") 

84 rating_snapshots = relationship("RatingSnapshot", back_populates="race") 

85 

86 def __repr__(self): 

87 return ( 

88 f"<Race(id={self.id}, meeting_id={self.meeting_id}, " 

89 f"race_number={self.race_number})>" 

90 ) 

91 

92 

93class Horse(Base): 

94 """Horse dimension table.""" 

95 

96 __tablename__ = "horses" 

97 

98 id = Column(Integer, primary_key=True, comment="TAB horse ID") 

99 name = Column(String(255), nullable=False, index=True) 

100 raw_json = Column(JSONB, nullable=True, comment="Additional horse metadata") 

101 

102 created_at = Column(DateTime, server_default=func.now(), nullable=False) 

103 updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now(), nullable=False) 

104 

105 # Relationships 

106 starters = relationship("Starter", foreign_keys="Starter.horse_id", back_populates="horse") 

107 

108 def __repr__(self): 

109 return f"<Horse(id={self.id}, name='{self.name}')>" 

110 

111 

112class Driver(Base): 

113 """Driver dimension table. 

114 

115 Driver IDs are generated from name hash since TAB API only provides names. 

116 """ 

117 

118 __tablename__ = "drivers" 

119 

120 id = Column(Integer, primary_key=True, comment="Generated from name hash") 

121 name = Column(String(255), nullable=False, index=True) 

122 raw_json = Column(JSONB, nullable=True, comment="Additional driver metadata") 

123 

124 created_at = Column(DateTime, server_default=func.now(), nullable=False) 

125 updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now(), nullable=False) 

126 

127 # Relationships 

128 starters = relationship("Starter", foreign_keys="Starter.driver_id", back_populates="driver") 

129 

130 def __repr__(self): 

131 return f"<Driver(id={self.id}, name='{self.name}')>" 

132 

133 

134class Trainer(Base): 

135 """Trainer dimension table. 

136 

137 Trainer IDs are generated from name hash since TAB API only provides names. 

138 """ 

139 

140 __tablename__ = "trainers" 

141 

142 id = Column(Integer, primary_key=True, comment="Generated from name hash") 

143 name = Column(String(255), nullable=False, index=True) 

144 raw_json = Column(JSONB, nullable=True, comment="Additional trainer metadata") 

145 

146 created_at = Column(DateTime, server_default=func.now(), nullable=False) 

147 updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now(), nullable=False) 

148 

149 # Relationships 

150 starters = relationship("Starter", foreign_keys="Starter.trainer_id", back_populates="trainer") 

151 

152 def __repr__(self): 

153 return f"<Trainer(id={self.id}, name='{self.name}')>" 

154 

155 

156class Starter(Base): 

157 """Runner/starter in a race from TAB API.""" 

158 

159 __tablename__ = "starters" 

160 

161 id = Column(Integer, primary_key=True, autoincrement=True) 

162 race_id = Column( 

163 Integer, ForeignKey("races.id", ondelete="CASCADE"), nullable=False, index=True 

164 ) 

165 

166 horse_id = Column( 

167 Integer, ForeignKey("horses.id", ondelete="SET NULL"), nullable=True, index=True 

168 ) 

169 driver_id = Column( 

170 Integer, ForeignKey("drivers.id", ondelete="SET NULL"), nullable=True, index=True 

171 ) 

172 trainer_id = Column( 

173 Integer, ForeignKey("trainers.id", ondelete="SET NULL"), nullable=True, index=True 

174 ) 

175 

176 runner_number = Column(Integer, nullable=True, index=True, comment="Saddlecloth number") 

177 barrier = Column(Integer, nullable=True, comment="Starting barrier/gate number") 

178 barrier_position = Column(String(10), nullable=True, comment="Harness position: 1F, 2B, etc.") 

179 handicap_m = Column(Integer, nullable=True, comment="Handicap in meters (back marks)") 

180 

181 placing = Column(Integer, nullable=True, comment="Final placing (1=winner, NULL=DNF/no result)") 

182 did_not_finish = Column(Boolean, default=False, comment="DNF, pulled up, etc.") 

183 

184 raw_json = Column(JSONB, nullable=False, comment="Original runner data from TAB") 

185 

186 created_at = Column(DateTime, server_default=func.now(), nullable=False) 

187 updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now(), nullable=False) 

188 

189 # Relationships 

190 race = relationship("Race", back_populates="starters") 

191 horse = relationship("Horse", back_populates="starters") 

192 driver = relationship("Driver", back_populates="starters") 

193 trainer = relationship("Trainer", back_populates="starters") 

194 

195 def __repr__(self): 

196 return ( 

197 f"<Starter(id={self.id}, race_id={self.race_id}, " 

198 f"horse_id={self.horse_id}, placing={self.placing})>" 

199 ) 

200 

201 

202class RatingSnapshot(Base): 

203 """Rating snapshot for an entity at a point in time (after a race).""" 

204 

205 __tablename__ = "rating_snapshots" 

206 

207 id = Column(Integer, primary_key=True, autoincrement=True) 

208 

209 entity_type = Column( 

210 Enum(EntityType), nullable=False, index=True, comment="horse, driver, or trainer" 

211 ) 

212 entity_id = Column(Integer, nullable=False, index=True, comment="ID in respective table") 

213 

214 as_of_race_id = Column( 

215 Integer, 

216 ForeignKey("races.id", ondelete="CASCADE"), 

217 nullable=False, 

218 index=True, 

219 comment="Rating after this race", 

220 ) 

221 

222 rating = Column(Float, nullable=False, comment="Elo rating") 

223 rd = Column(Float, nullable=True, comment="Rating deviation (uncertainty)") 

224 

225 meta = Column( 

226 JSONB, 

227 nullable=True, 

228 comment="Additional metadata: components, K used, race count, etc.", 

229 ) 

230 

231 created_at = Column(DateTime, server_default=func.now(), nullable=False) 

232 

233 # Composite index for efficient lookups 

234 __table_args__ = ( 

235 UniqueConstraint( 

236 "entity_type", 

237 "entity_id", 

238 "as_of_race_id", 

239 name="uq_entity_race_snapshot", 

240 ), 

241 ) 

242 

243 # Relationship 

244 race = relationship("Race", back_populates="rating_snapshots") 

245 

246 def __repr__(self): 

247 return ( 

248 f"<RatingSnapshot(entity_type={self.entity_type.value}, " 

249 f"entity_id={self.entity_id}, rating={self.rating:.1f})>" 

250 ) 

251 

252 

253class BarrierAdjustment(Base): 

254 """Learned adjustments for barrier positions.""" 

255 

256 __tablename__ = "barrier_adjustments" 

257 

258 id = Column(Integer, primary_key=True, autoincrement=True) 

259 

260 venue = Column(String(255), nullable=True, comment="Specific venue or NULL for global") 

261 start_type = Column(String(50), nullable=True, comment="mobile/standing or NULL for any") 

262 distance_bucket = Column(String(50), nullable=False, comment="e.g., '<1700', '1700-2000'") 

263 barrier = Column(Integer, nullable=False, comment="Barrier number") 

264 

265 adjustment = Column( 

266 Float, default=0.0, nullable=False, comment="Rating points to add for this barrier" 

267 ) 

268 

269 sample_count = Column(Integer, default=0, nullable=False, comment="Number of observations") 

270 

271 created_at = Column(DateTime, server_default=func.now(), nullable=False) 

272 updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now(), nullable=False) 

273 

274 __table_args__ = ( 

275 UniqueConstraint( 

276 "venue", 

277 "start_type", 

278 "distance_bucket", 

279 "barrier", 

280 name="uq_barrier_adjustment", 

281 ), 

282 ) 

283 

284 def __repr__(self): 

285 return ( 

286 f"<BarrierAdjustment(venue={self.venue}, barrier={self.barrier}, " 

287 f"adjustment={self.adjustment:.2f})>" 

288 ) 

289 

290 

291class HandicapAdjustment(Base): 

292 """Learned adjustments for handicaps (back marks).""" 

293 

294 __tablename__ = "handicap_adjustments" 

295 

296 id = Column(Integer, primary_key=True, autoincrement=True) 

297 

298 venue = Column(String(255), nullable=True, comment="Specific venue or NULL for global") 

299 start_type = Column(String(50), nullable=True, comment="mobile/standing or NULL for any") 

300 distance_bucket = Column(String(50), nullable=False, comment="e.g., '<1700', '1700-2000'") 

301 handicap_m = Column(Integer, nullable=False, comment="Handicap in meters") 

302 

303 adjustment = Column( 

304 Float, default=0.0, nullable=False, comment="Rating points to add for this handicap" 

305 ) 

306 

307 sample_count = Column(Integer, default=0, nullable=False, comment="Number of observations") 

308 

309 created_at = Column(DateTime, server_default=func.now(), nullable=False) 

310 updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now(), nullable=False) 

311 

312 __table_args__ = ( 

313 UniqueConstraint( 

314 "venue", 

315 "start_type", 

316 "distance_bucket", 

317 "handicap_m", 

318 name="uq_handicap_adjustment", 

319 ), 

320 ) 

321 

322 def __repr__(self): 

323 return ( 

324 f"<HandicapAdjustment(venue={self.venue}, handicap_m={self.handicap_m}, " 

325 f"adjustment={self.adjustment:.2f})>" 

326 ) 

327 

328 

329class PredictionHistory(Base): 

330 """Record of win/place predictions and actual results for accuracy tracking.""" 

331 

332 __tablename__ = "prediction_history" 

333 

334 id = Column(Integer, primary_key=True, autoincrement=True) 

335 race_id = Column( 

336 Integer, ForeignKey("races.id", ondelete="CASCADE"), nullable=False, index=True 

337 ) 

338 horse_id = Column( 

339 Integer, ForeignKey("horses.id", ondelete="CASCADE"), nullable=False, index=True 

340 ) 

341 predicted_win_prob = Column(Float, nullable=False) 

342 predicted_place_prob = Column(Float, nullable=False) 

343 predicted_placing = Column(Integer, nullable=False) 

344 actual_placing = Column(Integer, nullable=True) 

345 brier_score = Column(Float, nullable=True) 

346 created_at = Column(DateTime, server_default=func.now(), nullable=False)