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; $WFX: Wielerfoxes, game currency

Game Mechanics

I liked the PdcVds because they had you follow and root for your team over the 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.

Rules

Here’s the general idea, fixed in ../

As simple as they come:

1. assemble team of exactly 25 riders
2. no debt, balance must always be >0
3. before start of season (=first UCI race)
4. no debts, your balance should always be $>=0$
5. each rider earns points by placing on races, rider score is the sum of points
6. points pre-determined per race class/result (rank, jersey)
7. Team score is sum of rider scores
8. Final Winner/Ranking at the end of season (last UCI race)

Authoritative Data Source is Procyclingstats

Strategy Considerations

Research riders (up? down? new team? getting old? finally blooming? … ), compare to prices and assemble a squad of hopefuls within the given budget.

And also: How many other people will pick this rider? Because: If everyone has $x$, $x$ will not get you ahead of anyone, but they will leave you behind if you don’t have $x$.

So: mix sure scorers (usually expensive!) with incredible breakthroughs/comebacks (usually cheap) that no one but you sees coming (Arnaud de Lie, Mark Cavendish, I am forever grateful to you!)

I want a nice game of balance and overhinking everything!

Pricing

The PdcVds people had a price fixing conference (I guess Belgian Beer was involved). I have algorithms (hitting many keys many times was involved). Price Setting (and budget allocation) are, I think, the central part of a good game. Make it hard to just stuff a team with all-winners across every terrain, encourage speculating on midlevel riders. Ie. By all means, get Pogacar, but then you might have to packfill your team to meet the budget.

General Idea:

• take scores of previous season
• calculate a new price from these scores
• calculation should encourage balanced teams (not too many superstars, pick promising middle to higher tier riders)
• price curve should flatten out in the middle tier, rise sharply
• compress the scores to a scale from 1-30 (just numbers)
• have a special high Pogi price (the VDS people did too)

The goal is that if you pick too many stars you have to fill out the rest of the team with less promising riders. Tradeoff: Sure scores that maybe everyone will pick, or discover more obscure riders that might score high, or might not -> encourage risk taking

Figure out for which riders my strategy grossly undervalued (There’s one quite obvious!)

For more details about pricing see https://wielerfox.testha.se/data/pricing.html

Scoring

Is actually less critical and complicated

• Marry the old PdcVds scoring scheme to PCS race classes

done.

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

Details:

• take PCS classification, map to custom race tiers
• allocate points for results within those tiers (see above)

Weird/unrealistic ideas

Impact score

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.

Trading, Floating Prices

An alternative game mode could allow buying and selling riders during the season This could be a way to engage more over time. This would go against my goal of low maintenance playing.

Benefits:

• more motivation to follow events
• speculation as additional strategic element, with floating prices

But it would require more/constant effort if you care about winning.

Rules

1. Riders get an initial price at the start of the season (same as regular mode)
2. Build your team before season start at the fixed price.
3. After start: Fire a rider earn the current price, Hire a rider, spend the current price.
4. Each transfer costs a fee
5. Current Score is always the sum of scores of riders currently on your team
6. Final Score is the sum of scores of riders on your team at season end.

The (significant) transfer fee that is lost on each trade should discourage excessive trading.

Considered and discarded: Team Score is sum of points earned by rider while on the team. So the options are:

1. A rider on your team during Paris Roubaix wins and gets 250 points. Your team would accumulate those points and keep them even after you fire them later.
2. A rider accumulates points and can be hired/fired with these points.

This makes a lot of difference!

1. would let you act very dynamically, thinking about which rider will do well in upcoming races, and who won’t.
2. would be more of long-term bets. If you think a rider’s season is a write-off not much to lose, but also the opposite. You still will have to pick riders with good overall seasons. But maybe you can change track instead of being stuck with your initial choice.

Discarded because:

• boring strategies: hire a spring team, kick them all out and buy GT riders instead.
• technical difficulty, imho not fixable: there is a time delay between the actual result, PCS update and WFX import. Stupid Arbitrage/dispute possibilities.

The transfer fee would make short term ownership (hire just for the time it takes to cross the finish line first) unattractive/unprofitable

Could run the point accumulation per day (member at that day, selling/buying only effective on next day?)

Price Float

This would be a nice additional dimension:

• 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)

Data Model

  flowchart TD
    competitions --> races
    competitions --> riders_seasons
    competitions --> teams
    competitions --> wielerfox_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_seasons --> rider_prices
    riders_seasons --> manifested_riders
    
    stages --> stage_results
    
    users --> transfers
    users --> wielerfox_teams
    
    wielerfox_teams --> transfers
    wielerfox_teams --> manifested_teams
    
    subgraph Wielerfox
        wielerfox_teams
        transfers
        manifested_teams
        users
    end
    
    subgraph PCS Imported
        races
        teams
        riders
        riders_seasons
        rider_prices
        results
        stages
        stage_results
        start_lists
        manifested_riders
    end

    subgraph Core
        rider_prices
        competitions
    end
    
    subgraph Meta
        race_scores
    end

note: deepseek helped out with this diagram

Tables

Core:

• competitions: One row per year/season + male/female, for both pdc and wielerfox 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. Could have arbitrary competitions, e.g. Cyclocross, MTB, …
• race_scores: lookup table for how many points a result earns the rider.
• rider_prices: Records price changes. Authoritative is the last price <= date

PCS Data:

• riders: All the riders I can scrape from PCS. Those are unique across competitions/seasons. Basically name, DOB, …
• 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)
• manifested_riders: deserialised rider data, mostly for current price and score at date

Wielerfox Data:

• users: the great and good of the Wielerfox community, ie. me
• wielerfox_teams: competition teams
• manifested_teams: serialized version of the state of a team, one for each day, when a result comes in or a transfer happens.
• 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.
  • actions are $XFER_IN$, $XFER_OUT$
  • if I’m not completely stupid, the last transfer is authoritative: XFER_IN -> currently in team, XFER_OUT -> currently not in team, no matter what happened before.
  • records rider price at transaction + fees (1 $WFX if after season start)
  • initial budget - sum of all transfer prices (* -1 for XFER_OUT) + sum(fees) = current_budget

Score:

• rider: current score is sum of race + stage scores before current date
• wfox team: some of rider scores for riders currently in team

The last calculation is not easy and leads to noticable response times (despite indices and materialized view for results)

Additional problem: ranking. For this we have to do this calculation for all teams to see which rank each team has.

And we want to sort by rank.

My solution: Manifested Teams

• deserialize team with current members
• current score sum
• current ranks for each result score type (WFX, UCI, PCS)
• current budget + value

create one manifest per date. Results come in per day, no greater resolution required. Problem: transfers can happen at any time of the day, but I just update the day’s manifest with the latest transfers

There is only one manifest per day. It’s just updated when things happen.

Manifesting happens via software triggers on results, transfers and price changes.

The serialized version also contains the json serialization, so all api requests are served directly from manifests

Rider Manifests

Riders also proved too complex for dynamic serving (response times are noticable)

• sum up result scores until current date
• determine latest price before current date

As before ranking has to calculate the results sum for all riders

Similar Manifests created for riders:

• current score sum
• current rank
• current price
• current ranks in each points scale (WFX, PCS, UCI)

Again: manifest also contains serialized JSON, so API requests are served directly from the manifest.

Again: Manifests created/updated by software triggers on

• results
• price changes

Competitions

Male/Female are completely distinct. Some data is across seasons.

Keyed to a specific competition (year/gender):

• riders_seasons
• races
• stages (via race)
• results (via race/stage)
• WFX teams

In theory I could import also riders/results/… for any sort of league (MTB, Cyclocross) in a separate competition.

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 wielerfox_teams_members table and the budget of the wielerfox_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:

Manifesting Teams

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
• value (sum of current prices)

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!