Coverage for packages / core / storage / models.py: 100%

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( 

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

43 ) 

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

45 

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

47 updated_at = Column( 

48 DateTime, server_default=func.now(), onupdate=func.now(), nullable=False 

49 ) 

50 

51 # Relationships 

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

53 

54 def __repr__(self): 

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

56 

57 

58class Race(Base): 

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

60 

61 __tablename__ = "races" 

62 

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

64 meeting_id = Column( 

65 String(64), 

66 ForeignKey("meetings.id", ondelete="CASCADE"), 

67 nullable=False, 

68 index=True, 

69 ) 

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

71 race_number = Column(Integer, nullable=False) 

72 

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

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

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

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

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

78 

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

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

81 

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

83 updated_at = Column( 

84 DateTime, server_default=func.now(), onupdate=func.now(), nullable=False 

85 ) 

86 

87 # Composite unique constraint 

88 __table_args__ = ( 

89 UniqueConstraint("meeting_id", "race_number", name="uq_meeting_race"), 

90 ) 

91 

92 # Relationships 

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

94 starters = relationship( 

95 "Starter", back_populates="race", cascade="all, delete-orphan" 

96 ) 

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

98 

99 def __repr__(self): 

100 return ( 

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

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

103 ) 

104 

105 

106class Horse(Base): 

107 """Horse dimension table.""" 

108 

109 __tablename__ = "horses" 

110 

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

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

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

114 

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

116 updated_at = Column( 

117 DateTime, server_default=func.now(), onupdate=func.now(), nullable=False 

118 ) 

119 

120 # Relationships 

121 starters = relationship( 

122 "Starter", foreign_keys="Starter.horse_id", back_populates="horse" 

123 ) 

124 

125 def __repr__(self): 

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

127 

128 

129class Driver(Base): 

130 """Driver dimension table. 

131 

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

133 """ 

134 

135 __tablename__ = "drivers" 

136 

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

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

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

140 

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

142 updated_at = Column( 

143 DateTime, server_default=func.now(), onupdate=func.now(), nullable=False 

144 ) 

145 

146 # Relationships 

147 starters = relationship( 

148 "Starter", foreign_keys="Starter.driver_id", back_populates="driver" 

149 ) 

150 

151 def __repr__(self): 

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

153 

154 

155class Trainer(Base): 

156 """Trainer dimension table. 

157 

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

159 """ 

160 

161 __tablename__ = "trainers" 

162 

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

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

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

166 

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

168 updated_at = Column( 

169 DateTime, server_default=func.now(), onupdate=func.now(), nullable=False 

170 ) 

171 

172 # Relationships 

173 starters = relationship( 

174 "Starter", foreign_keys="Starter.trainer_id", back_populates="trainer" 

175 ) 

176 

177 def __repr__(self): 

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

179 

180 

181class Starter(Base): 

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

183 

184 __tablename__ = "starters" 

185 

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

187 race_id = Column( 

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

189 ) 

190 

191 horse_id = Column( 

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

193 ) 

194 driver_id = Column( 

195 Integer, 

196 ForeignKey("drivers.id", ondelete="SET NULL"), 

197 nullable=True, 

198 index=True, 

199 ) 

200 trainer_id = Column( 

201 Integer, 

202 ForeignKey("trainers.id", ondelete="SET NULL"), 

203 nullable=True, 

204 index=True, 

205 ) 

206 

207 runner_number = Column( 

208 Integer, nullable=True, index=True, comment="Saddlecloth number" 

209 ) 

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

211 barrier_position = Column( 

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

213 ) 

214 handicap_m = Column( 

215 Integer, nullable=True, comment="Handicap in meters (back marks)" 

216 ) 

217 

218 placing = Column( 

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

220 ) 

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

222 

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

224 

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

226 updated_at = Column( 

227 DateTime, server_default=func.now(), onupdate=func.now(), nullable=False 

228 ) 

229 

230 # Relationships 

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

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

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

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

235 

236 def __repr__(self): 

237 return ( 

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

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

240 ) 

241 

242 

243class RatingSnapshot(Base): 

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

245 

246 __tablename__ = "rating_snapshots" 

247 

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

249 

250 entity_type = Column( 

251 Enum(EntityType), 

252 nullable=False, 

253 index=True, 

254 comment="horse, driver, or trainer", 

255 ) 

256 entity_id = Column( 

257 Integer, nullable=False, index=True, comment="ID in respective table" 

258 ) 

259 

260 as_of_race_id = Column( 

261 Integer, 

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

263 nullable=False, 

264 index=True, 

265 comment="Rating after this race", 

266 ) 

267 

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

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

270 

271 meta = Column( 

272 JSONB, 

273 nullable=True, 

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

275 ) 

276 

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

278 

279 # Composite index for efficient lookups 

280 __table_args__ = ( 

281 UniqueConstraint( 

282 "entity_type", 

283 "entity_id", 

284 "as_of_race_id", 

285 name="uq_entity_race_snapshot", 

286 ), 

287 ) 

288 

289 # Relationship 

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

291 

292 def __repr__(self): 

293 return ( 

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

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

296 ) 

297 

298 

299class BarrierAdjustment(Base): 

300 """Learned adjustments for barrier positions.""" 

301 

302 __tablename__ = "barrier_adjustments" 

303 

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

305 

306 venue = Column( 

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

308 ) 

309 start_type = Column( 

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

311 ) 

312 distance_bucket = Column( 

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

314 ) 

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

316 

317 adjustment = Column( 

318 Float, 

319 default=0.0, 

320 nullable=False, 

321 comment="Rating points to add for this barrier", 

322 ) 

323 

324 sample_count = Column( 

325 Integer, default=0, nullable=False, comment="Number of observations" 

326 ) 

327 

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

329 updated_at = Column( 

330 DateTime, server_default=func.now(), onupdate=func.now(), nullable=False 

331 ) 

332 

333 __table_args__ = ( 

334 UniqueConstraint( 

335 "venue", 

336 "start_type", 

337 "distance_bucket", 

338 "barrier", 

339 name="uq_barrier_adjustment", 

340 ), 

341 ) 

342 

343 def __repr__(self): 

344 return ( 

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

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

347 ) 

348 

349 

350class HandicapAdjustment(Base): 

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

352 

353 __tablename__ = "handicap_adjustments" 

354 

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

356 

357 venue = Column( 

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

359 ) 

360 start_type = Column( 

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

362 ) 

363 distance_bucket = Column( 

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

365 ) 

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

367 

368 adjustment = Column( 

369 Float, 

370 default=0.0, 

371 nullable=False, 

372 comment="Rating points to add for this handicap", 

373 ) 

374 

375 sample_count = Column( 

376 Integer, default=0, nullable=False, comment="Number of observations" 

377 ) 

378 

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

380 updated_at = Column( 

381 DateTime, server_default=func.now(), onupdate=func.now(), nullable=False 

382 ) 

383 

384 __table_args__ = ( 

385 UniqueConstraint( 

386 "venue", 

387 "start_type", 

388 "distance_bucket", 

389 "handicap_m", 

390 name="uq_handicap_adjustment", 

391 ), 

392 ) 

393 

394 def __repr__(self): 

395 return ( 

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

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

398 ) 

399 

400 

401class PredictionHistory(Base): 

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

403 

404 __tablename__ = "prediction_history" 

405 

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

407 race_id = Column( 

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

409 ) 

410 horse_id = Column( 

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

412 ) 

413 predicted_win_prob = Column(Float, nullable=False) 

414 predicted_place_prob = Column(Float, nullable=False) 

415 predicted_placing = Column(Integer, nullable=False) 

416 actual_placing = Column(Integer, nullable=True) 

417 brier_score = Column(Float, nullable=True) 

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

419 

420 

421class AuditLog(Base): 

422 """Audit log for tracking data changes and corrections.""" 

423 

424 __tablename__ = "audit_logs" 

425 

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

427 table_name = Column(String(64), nullable=False, index=True) 

428 record_id = Column(String(64), nullable=False, index=True) 

429 action = Column( 

430 String(20), nullable=False, index=True 

431 ) # INSERT, UPDATE, DELETE, CORRECT 

432 old_values = Column(JSONB, nullable=True) 

433 new_values = Column(JSONB, nullable=True) 

434 changed_by = Column(String(255), nullable=True, index=True) # user identifier 

435 change_reason = Column(String(500), nullable=True) 

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