Design Decision Document

(always wanted to write one of those)

Note: WFX: Wielerfox; PCS: Procyclingstats; PdcVds: Podicum Cafe Virtual Directeur Sportif (RIP); Points/Score: equiv UCI points, earned by riders; W₣X: The game currency. Pronounced either never or however you want.

Goals

Create a game that adds some extra spice to following bike racing:

• research a little bit about riders and their prospects for the next season
• look for surprise breakthroughs/comebacks
• gain some extra happiness from watching your favorites win (because they earned you some points)
• gain at least some happiness when your unfavorites win - pick them for your team, at least you got some points out of it
• think about the whole season ahead

Mechanics

The main decisions are:

• rider pricing
• point value of each race, placing

Then it comes down to:

• team size
• budget

There are some possible variations:

• fixed team with deadline to commit before season start (original PDC)?
• allow transfers (kick out, hire riders) during the seaons?
• static pricing?
• float prices based on demand?

The main decision is commit team, no changing? or allow changes during the season

Commit

• research before, intense selection phase

Cons:

• celebratory/nervous feeling when submitting
• maybe a little bit boring
• regrets
• retirements/injuries

retirements/injuries however reflect the risk real teams take!

I liked the PdcVds because they had you follow and root for your team over thoine whole season. This was nice and feels a lot better than the: select riders for the next race style of all others (tell me if I’m wrong)

Playing does not require much maintenance after initial selection, just watch your team raking in the points and hooray or boohoo as appropriate.

Wolf of Paddestraat

• additional motivation/immersion by watching prices/swapping riders
• no regrets

Cons:

• needs more attention
• adds complexity
• unforseeable effects (price dynamics ok?)
• possible to “break” competition?

Floating Prices

• hiring increases a rider’s price
• selling decreases

imho this is a must if I introduce transfers.

Swapping out an injured/retiring rider without float:

• gain price back (maybe was high?)
• lose points so far
• can just hire someone else who will have earned more points than you gave up by the end of season, at the same price

with float:

• gain price back (but everyone will kick them out, so might not get much, if late)
• lose points
• other riders who do well, or are expected to, will be more expensive now!

End of season without float:

• try save WFX/earn WFX by trading
• just swap out your losers with winners (now you know who!) before season ends

End of season with float:

• ok, you have a lot of WFX
• but the winners will be expensive
• you won’t get much for your losers.

Speculation:

Buy low/sell high games, or speed-trading/arbitraging to bloat your budget should be discouraged. Hence the transfer fee. Should be high enough to close any loopholes.

Parameters for Price float/transfer fee see XXX (as soon as I figure it out)

Decision:

Wolf of Paddestraat, because it was technically more challenging!

Initial Pricing

Project a price for the next season based on past results

• this seems rational and not surprising, I guess. Here’s the R notebook

Old PDC had some kind of “price conference”, cannot replicate that (no collaboratores)

Race Scoring

Is actually less critical and complicated

• Marry the old PdcVds scoring scheme to PCS race classes

done.

But I would like to put higher reward on oneday races and stage results. Discuss.

Technical Details

Data Model

  flowchart TD
    competitions --> races
    competitions --> riders_seasons
    competitions --> teams
    competitions --> wsk_teams
    
    races --> results
    races --> stages
    races --> start_lists
    
    riders --> riders_seasons
    teams --> riders_seasons
    
    riders_seasons --> results
    riders_seasons --> stage_results
    riders_seasons --> start_lists
    riders_seasons --> transfers
    riders_season  --> rider_prices
    
    stages --> stage_results
    
    users --> transfers
    users --> wsk_teams
    
    wsk_teams --> transfers
    wsk_teams --> manifested_teams
    
    subgraph Wielerskalle
        wsk_teams
        transfers
        manifested_teams
        users
    end
    
    subgraph PCS Imported
        races
        teams
        riders
        riders_seasons
        results
        stages
        stage_results
        start_lists
        rider_prices
    end
    
    subgraph Meta
        race_scores
    end

note: deepseek helped out with this diagram

Tables

Wielerskalle:

• competitions: year/season, male/female, for both pdc and wsk data. Some of the data is keyed to a competition, you can switch and look at history. After all, this is how pdcvds and pcs have it
• users: the great and good of the WFX community, ie. me
• wsk_teams: competition teams
• transfers: records of hire/fire of riders. The state of team $T$ at time $t$ is the sum of all transfers $\tau$ $<t$ see below why this is necessary.
• manifested_teams: serialized version of the state of a team, one for each day, when a result comes in or a transfer happens.

PCS Data:

• riders: All the riders I can scrape from PCS. Those are unique across competitions/seasons
• riders_season: for each season/competition a rider participates in. Mostly for Team membership. What will I do when midseason transfers will become a real thing??
• teams: UCI Teams, also across competitions. Since it’s hard to track the lineage of a team, I just create a new one for each season.
• races: UCI races. Again, distinct for each season
• stages: For simplicity this is not completely normalized. A race can have 0:n stages
• results: oneday results or overall results for a stage race. Different types: rank or jersey (overall)
• stage_results: see above, not really normalized. these are either stage ranking or intermediate jersey ranking (typically we only care about #1)
• start_lists: per race, need that for showing upcoming races for your team, also calculating race days for a rider (not accurate, would need start list for stages, too)
• rider_prices: record of price for rider $x$ at time $t$. support for price floating -> actual purchase price is price at time of transfer (copied to the transfer record for ease of querying)
• race_scores: lookup table for how many points a result earns the rider.

PCS Data

I think this is how you would expect it to be. There are some points for discussion.

• should you normalize stage races?

Of course one could treat a stage as a “subrace”, ie. linked to a parent race. Now you don’t need the awkward stage_results and would not have to collect points from 2 tables. Considered and discarded. Makes it a bit easier. Apart from summing points there is hardly a place where I need stages as races, rather the opposite. I have a race list, and a stage list for stage races, 2 queries either way, one with a where parent is null, on with where parent=race. Also keeping stage results separate just reflects the difference between intermdiate and overall results. I also don’t think/hope that they will come up with a more complicated or type of race hierarchy.

• should you import more/detailed data? nope. only what is strictly necessary for the competition. I don’t want to mirror PCS. Eg. I don’t care about DNF/DNS, 0 points is 0 points either way. There is just no result now. Although: should you get points for intermediate jersey if you DNF? Nah. Bad for race day calculation, but that’s hardly existential.

WFX teams

Here it gets a bit more interesting.

See above, the decision to support trading riders during the season makes it a bit harder. There are two options:

• update a wsk_teams_members table and the budget of the wsk_team. Easy! But, now you have to update the score as soon as the result comes in. No way to look at scores retroactively, because you don’t know which members you had in a race, membership has changed since then. You would need to maintain a start list etc. etc. I really like history, so no go.
• record transfers. Now you can always know the state of a team at any time by “replaying” the transfers. The score however is a bit more difficult to calculate: Sum of points of riders currently in your team, for which you have to replay the transfers. But: Now you can get the score for your team for any timepoint, therefore any race. Similarily the budget is always the sum of in/out of all transfers that happened before.

I went for the transaction recording option. Reaons:

• Not horribly dependend on updating scores as the points/results come in
• can do what-if experiments to evaluate scoring schemes, rider prices
• for me most important: get nice plots of score and ranking across the season for your team.

No need to actually replay the whole transaction history either for membership. Just get all riders for which the last transfer before time $t$ was IN (I think). Similar: budget is just initial - sum of transfer prices (if we record buying as negative). The whole thing is not terribly compute intense, no need to sell your children for a GPU! But as it is more interesting to think it won’t be just me and my AI friends (elsewhere), the season development plots in particular get a bit slow. Hence the chicken way out:

Static or SPA?

Without trading it would actually be better to render static html. sort/filter tables work quite well with datatables. Only the team builder would benefit from more Vue

Updates are rare -> once a day when results come in.

Variable teams

Even then, assuming that transfers would be rare we can render static html

Decision:

SPA: mostly technical interest

State

Points are fairly easy:

• take date
• calculate current riders at date (from transfers table)
• calculate points of rider (from results until date)
• sum up for team

With a materialized view for the combined results (from stage + GC/oneday) fast enough

But ranking needs to do this for all teams

This turns out to be way too slow

Why dynamic calculation -> Statistics!

could update each team with points when result comes in. But I wanted to have a plot that shows development of ranking over the season, which means I need to be able to calculate points + rank for each day.

Manifesting Teams

Since the dynamic calculation is too slow:

Every time a relevant update happens, which are:

• transfer
• result insert

a serialized JSON of the updated team state is stored in a special table. We don’t need a resolution finer than one day, so a serialization for a day is upserted. (race/stage == day, I hate split stages, why are they still a thing, treat them just as 2 results with the same date).

The serialization includes:

• score
• rank
• members
• budget

The team is just jsonified with the same schema that the API uses for returning. So I just shunt this from the table to the client when they need a historic state. Also the current for that matter!

Manifesting Riders

A team manifest requires points calculation for all members, potentially 25 * $n_teams$

Rider prices are also dynamic and calculated from the riders_prices table for each date.

Rider manifests based on the same events speed up team manifest and

• ranking riders is now easy
• plotting season developments
• plotting price development

Summary:

Allowing transfers during the season made a fairly simple concept technically quite hard.

Impact score

Just a fantasy:

Reward things like news/social media mentions in connection with a race. would love to be able to tally mentions by the Eurosport commentators. Would also recognise desperadoes and, above all good domestiques! This goes into my old gripe that a sponsor doesn’t actually want wins, they want PR, and the more impact on a race a rider has the better. Winning is just a special(big) impact. Making a race interesting/fun also gets your logo on TV and your team name mentioned.

• Weak version: Extra points for breakaway, in particular for breakaway wins.