supercontest.models

Tables for the app.

class supercontest.models.AccessToken(**kwargs)

Bases: Base, SQLAlchemyBaseAccessTokenTable[int]

A signed-in session: a SHA-256 of the cookie’s value, and whose it is.

fastapi-users’ own table (token, created_at), keyed to a user whose deletion deletes it too. token holds the hash, never the token: see supercontest.api.auth.HashedDatabaseStrategy. One row per sign-in, deleted at sign-out, so a session can be ended one at a time – and all at once, on a password change.

created_at: Mapped[datetime]

token: Mapped[str]

user_id: Mapped[int]

class supercontest.models.Base(**kwargs: Any)

Bases: AsyncAttrs, DeclarativeBase

The declarative base every model maps from; its metadata is the whole schema.

AsyncAttrs is for the rare deliberate lazy read: await game.awaitable_attrs.week. Every relationship below is lazy="raise", so any other unloaded read is an error.

metadata: ClassVar[MetaData] = MetaData()

Every table, constraint and index, named by NAMING_CONVENTION.

registry: ClassVar[registry] = <sqlalchemy.orm.decl_api.registry object>

Refers to the _orm.registry in use where new _orm.Mapper objects will be associated.

class supercontest.models.Contestant(**kwargs)

Bases: Base

One side of a game.

opponent_score, margin and coverage_id are the database’s, not the caller’s: supercontest.models.coverage_triggers computes them on every write, and whatever a caller passes for them is replaced. score too is the database’s to clear: NULL while the game is unstarted, with the three derived columns, and never NULL once it has started. A write to score or to the game’s line also rewrites rows the ORM did not write – the opponent, or both sides – so a session that changes either and reads margins afterwards refreshes the game’s contestants first.

coverage: Mapped[Coverage | None]

coverage_id: Mapped[int | None]

game: Mapped[Game]

game_id: Mapped[int]

get_opponent() → Contestant

Don’t need it as an ID FK col, or a relationship, because then there are complications when creating this object (its opponent ID doesn’t exist yet and self-relationships can get messy) - so this convenience function just provides it when called.

Returns:

The Contestant object for the opponent.

Raises:

ValueError – If there’s not exactly 2 opponents in the game associated with this contestant.

id: Mapped[int]

location: Mapped[Location]

location_id: Mapped[int]

margin: Mapped[float | None]

opponent_score: Mapped[int | None]

picks: Mapped[list[Pick]]

prediction: Mapped[Prediction]

prediction_id: Mapped[int]

score: Mapped[int | None]

side: Mapped[int]

team: Mapped[Team]

team_id: Mapped[int]

class supercontest.models.Coverage(**kwargs)

Bases: Base

contestants: Mapped[list[Contestant]]

id: Mapped[int]

name: Mapped[str]

points: Mapped[float]

class supercontest.models.EmailSend(**kwargs)

Bases: Base

One scheduled broadcast that has gone out: a job’s mail for one week, at most once.

The relay that triggers a job retries it when the request outlasts its wait, while the first attempt may still be sending. The job claims its row before it sends – the primary key is the claim – and a job whose row is already there sends nothing.

job: Mapped[str]

recipients: Mapped[int]

How many addresses it went to.

season: Mapped[int]

sent_at: Mapped[datetime]

week: Mapped[int]

class supercontest.models.Game(**kwargs)

Bases: Base

contestants: Mapped[list[Contestant]]

datetime: Mapped[datetime]

get_contestants(by: Literal['prediction', 'location', 'coverage'], fail: bool = True) → tuple[Contestant, Contestant]

Returns the Contestant objects in the matchup, by various classifications.

Parameters:

• by – “prediction” or “location” or “coverage”

• fail – If True, will error if pickem/neutral/push. Else, will return in order of contestants[0], contestants[1]

Returns:

(favorite, underdog) or (home, visitor) or (coverer, noncoverer)

Raises:

ValueError – If fail=True and no favorite/home/coverer in this game.

id: Mapped[int]

line: Mapped[float]

status: Mapped[Status]

status_id: Mapped[int]

week: Mapped[Week]

week_id: Mapped[int]

class supercontest.models.League(**kwargs)

Bases: Base

id: Mapped[int]

name: Mapped[str]

season: Mapped[Season]

season_id: Mapped[int]

users: Mapped[list[User]]

class supercontest.models.Location(**kwargs)

Bases: Base

contestants: Mapped[list[Contestant]]

id: Mapped[int]

name: Mapped[str]

class supercontest.models.Pick(**kwargs)

Bases: Base

A user’s pick of one contestant.

game_id is the contestant’s game, stored so the database can refuse a second pick in one game – a duplicate or the other side – from every writer, with UNIQUE (user_id, game_id). A unique index cannot be passed by two concurrent inserts, as a trigger’s read can. The foreign key is the pair (contestant_id, game_id), so game_id can never name another game than its contestant’s. A writer sets both.

contestant: Mapped[Contestant]

contestant_id: Mapped[int]

created_at: Mapped[datetime | None]

game_id: Mapped[int]

id: Mapped[int]

updated_at: Mapped[datetime | None]

user: Mapped[User]

user_id: Mapped[int]

class supercontest.models.Prediction(**kwargs)

Bases: Base

contestants: Mapped[list[Contestant]]

id: Mapped[int]

name: Mapped[str]

class supercontest.models.Season(**kwargs)

Bases: Base

A season, season_start <= now < season_end: half-open, like its weeks.

Both bounds are instants. Each is a Pacific midnight – the league’s day – stored, like every time in the schema, as a timestamptz.

id: Mapped[int]

leagues: Mapped[list[League]]

season: Mapped[int]

season_end: Mapped[datetime]

season_start: Mapped[datetime]

weeks: Mapped[list[Week]]

class supercontest.models.Status(**kwargs)

Bases: Base

abbv: Mapped[str]

espn_abbv: Mapped[str]

finished: Mapped[bool]

games: Mapped[list[Game]]

id: Mapped[int]

inprogress: Mapped[bool]

name: Mapped[str]

unstarted: Mapped[bool]

class supercontest.models.Team(**kwargs)

Bases: Base

abbv: Mapped[str]

city: Mapped[str]

contestants: Mapped[list[Contestant]]

id: Mapped[int]

name: Mapped[str]

class supercontest.models.User(**kwargs)

Bases: Base, SQLAlchemyBaseUserTable[int]

A user. The five auth columns are fastapi-users’ own table’s.

email, hashed_password, is_active, is_superuser and is_verified come from SQLAlchemyBaseUserTable: the library owns their names, types, lengths and defaults. What is declared here is the id, the contest’s own columns, and one change to the library’s email column: it is unique CASE-INSENSITIVELY. Every lookup – the library’s get_by_email and ours – compares lower(email), so the unique index is on that expression, which also serves the lookups; the library’s plain unique index would allow A@x.com beside a@x.com and serve neither.

created_at: Mapped[datetime | None]

email: Mapped[str]

email_all_picks: Mapped[bool]

email_when_picks_closing: Mapped[bool]

email_when_picks_open: Mapped[bool]

first_name: Mapped[str | None]

hashed_password: Mapped[str]

id: Mapped[int]

is_active: Mapped[bool]

is_superuser: Mapped[bool]

is_verified: Mapped[bool]

last_name: Mapped[str | None]

leagues: Mapped[list[League]]

picks: Mapped[list[Pick]]

class supercontest.models.Week(**kwargs)

Bases: Base

games: Mapped[list[Game]]

id: Mapped[int]

season: Mapped[Season]

season_id: Mapped[int]

week: Mapped[int]

week_end: Mapped[datetime]

week_start: Mapped[datetime]

class supercontest.models.WeekTotal(**kwargs)

Bases: Base

One user’s week, totalled: what every season and all-time view is built from.

Maintained by the database, like a contestant’s coverage: triggers in supercontest.models.week_totals recompute a row whenever a pick, a contestant’s coverage or margin, or a game’s status or week changes, from whoever the writer is. Nothing in the app writes it. Each recompute is the full aggregate of that user’s week, not a running sum, so it cannot drift.

The counts follow the results engine’s own buckets: a pick is unstarted, in progress (covering, pushing, noncovering) or finished (cover, push, noncover), by its game’s status and its contestant’s coverage. Points are summed separately for finished and in-progress games; the margin sum is of finished picks, for the all-time average.

cover: Mapped[int]

covering: Mapped[int]

margin_finished: Mapped[float]

noncover: Mapped[int]

noncovering: Mapped[int]

points_finished: Mapped[float]

points_inprogress: Mapped[float]

push: Mapped[int]

pushing: Mapped[int]

unstarted: Mapped[int]

updated_at: Mapped[datetime]

user_id: Mapped[int]

week_id: Mapped[int]

Modules

coverage_triggers	The database computes each contestant's margin, coverage and opponent score.
models	The data schema for the application.
reference_data	The reference rows: fixed rows that are part of the schema's meaning, not contest data.
week_totals	The database keeps week_totals -- each user's week, totalled -- current on every write.