supercontest.models.models

The data schema for the application.

There are one-to-one, one-to-many, many-to-one, and many-to-many relationships below. Columns have relationships, not tables, of course. For tables that are “simply” associated with others, you can make the statements above in a “simple” sense - eg Season to Week is a one-to-many relationship. But more specificity is required for tables the have relationships across multiple columns - eg games and (2) contestants.

In the SQL layer, all FKs manifest as actual columns and all association tables manifest as actual tables (of course). All integer PKs have associated sequences and column defaults (to nextval) in postgres.

In the ORM layer, the relationships are all queryable as if they were real columns (of course). I have made every relationship bidirectional.

Three contestant columns are computed by the database, not by this module: opponent_score, margin and coverage_id. Triggers set them on every write, whoever the writer is; see supercontest.models.coverage_triggers for the formula. They are stored, not virtual, and the ORM reads them back after each flush rather than computing them. All three – and score itself – are NULL until the game starts: an unstarted game has no score.

Lines, margins and points are numeric in the database – exact, so the coverage formula’s “exactly zero is a push” is an exact comparison – and floats in Python (asdecimal=False). Every value is a whole or half point, which a float holds exactly, so nothing is lost either way.

In general, this module flows from children -> parents, to make dependency readable + ordered.

Module Attributes

`NAMING_CONVENTION`	How every constraint and index is named, so a revision can name what it drops and find it in every database.
`league_user`	The many-many relationship and its association table between User and League.

Classes

`AccessToken`	A signed-in session: a SHA-256 of the cookie's value, and whose it is.
`Base`	The declarative base every model maps from; its `metadata` is the whole schema.
`Contestant`	One side of a game.
`Coverage`
`EmailSend`	One scheduled broadcast that has gone out: a job's mail for one week, at most once.
`Game`
`League`
`Location`
`Pick`	A user's pick of one contestant.
`Prediction`
`Season`	A season, `season_start <= now < season_end`: half-open, like its weeks.
`Status`
`Team`
`User`	A user.
`Week`
`WeekTotal`	One user's week, totalled: what every season and all-time view is built from.

class supercontest.models.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.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.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.models.Coverage(**kwargs)

Bases: Base

contestants: Mapped[list[Contestant]]

id: Mapped[int]

name: Mapped[str]

points: Mapped[float]

class supercontest.models.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.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.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.models.Location(**kwargs)

Bases: Base

contestants: Mapped[list[Contestant]]

id: Mapped[int]

name: Mapped[str]

supercontest.models.models.NAMING_CONVENTION: dict[str, str] = {'ck': 'ck_%(table_name)s_%(constraint_name)s', 'fk': 'fk_%(table_name)s_%(column_0_N_name)s_%(referred_table_name)s', 'ix': 'ix_%(table_name)s_%(column_0_N_name)s', 'pk': 'pk_%(table_name)s', 'uq': 'uq_%(table_name)s_%(column_0_N_name)s'}: How every constraint and index is named, so a revision can name what it drops and find it in every database. A check constraint must be given its own short name, which the convention prefixes. An expression index has no column to name it by, so it is named by hand.

class supercontest.models.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.models.Prediction(**kwargs)

Bases: Base

contestants: Mapped[list[Contestant]]

id: Mapped[int]

name: Mapped[str]

class supercontest.models.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.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.models.Team(**kwargs)

Bases: Base

abbv: Mapped[str]

city: Mapped[str]

contestants: Mapped[list[Contestant]]

id: Mapped[int]

name: Mapped[str]

class supercontest.models.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.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.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]

supercontest.models.models.league_user = Table('league_user_association', MetaData(), Column('league_id', Integer(), ForeignKey('leagues.id'), table=<league_user_association>, primary_key=True, nullable=False), Column('user_id', Integer(), ForeignKey('users.id'), table=<league_user_association>, primary_key=True, nullable=False), schema=None): The many-many relationship and its association table between User and League.