Settling a fantasy gameweek exactly once

When a fantasy football gameweek ends, a sequence has to happen in order. Every manager's squad gets scored, with captains doubled, triple-captain tripled, the bench counted only if the bench-boost chip is active, and transfer penalties subtracted. Then everyone is ranked for that gameweek. Then season totals update. Then the top managers get their rewards.

The constraint that shaped the whole design: the job that kicks this off runs on a cron every five minutes, and scoring a gameweek can take longer than five minutes. So the pipeline has to be safe to start again while a previous run is still going. Idempotency here isn't a refinement you add later. It's the only thing standing between a manager and being paid twice.

A batch, then a chain

Each manager's scoring is its own job, so the work parallelizes. The ordering between stages is enforced by the batch's then and finally, not by sleeping or polling:

Bus::batch($perManagerJobs)              // one scoring job per manager
    ->allowFailures()
    ->then(fn () => ComputeGameweekRanksJob::dispatch($gameweekId))
    ->finally(function () use ($gameweekId, $isFinished) {
        UpdateManagerRanksJob::dispatch();              // season totals
        if ($isFinished) {
            AttachRewardsForGameweekJob::dispatch($gameweekId);
        }
    })
    ->dispatch();

Score, then rank, then season-rank and rewards. No coordinator polling a status column, no arbitrary delays.

Idempotent at every stage

Every job in the pipeline is ShouldBeUnique with a scoped key, so if the five-minute cron fires while a gameweek is mid-settlement, the duplicate dispatch is simply dropped:

class ComputeGameweekRanksJob implements ShouldQueue, ShouldBeUnique
{
    public $tries = 2;
    public $backoff = [120, 240]; // minutes-scale: it's waiting on other jobs and the DB, not milliseconds
    public $timeout = 300;
    public $uniqueFor = 300;      // long enough to cover a real run, short enough to release a dead one

    public function uniqueId(): string
    {
        return "gw:{$this->gameweekId}:rank";
    }
}

Per-manager scoring adds WithoutOverlapping on top, and reward payout uses updateOrCreate so a re-run tops up the same row instead of inserting a second payment. The constants aren't decoration: uniqueFor has to outlast a real run but release if a worker dies mid-job, and the backoff is in minutes because the things it waits on don't recover in milliseconds.

The ranking is a cursor loop, not a window function

Dense-ranking thousands of managers is exactly what SQL's DENSE_RANK() exists for, and I didn't use it.

$conn = DB::connection('db_fantasy');
$rank = 0; $position = 0; $lastPoints = null;

foreach (
    $conn->table('gameweek_scores')
        ->where('gameweek_id', $this->gameweekId)
        ->orderByDesc('points')
        ->orderBy('manager_id')        // deterministic tiebreaker
        ->select('id', 'points')
        ->cursor() as $row
) {
    $position++;
    if ($lastPoints !== $row->points) { // ties share a rank
        $rank = $position;
        $lastPoints = $row->points;
    }
    $conn->table('gameweek_scores')->where('id', $row->id)->update(['rank' => $rank]);
}

The reasons are memory and control. cursor() streams the rows instead of hydrating every manager at once. The dense-rank logic is a few lines anyone can read and adjust. The tiebreaker is explicit (manager_id), not whatever the database decides when points are equal. A window-function UPDATE would be shorter, and I'd reach for it if the row count grew by an order of magnitude, but at this scale the streaming version is predictable, easy to reason about under retry, and honest about exactly how ties resolve.

A database of its own

All of this runs on a dedicated db_fantasy connection with read/write splitting, separate from the core users-and-payments database. Fantasy settlement is bursty and write-heavy at precisely the moment matches end, which is also when people are opening the app. Isolating it means a gameweek rollover can't contend with someone trying to subscribe or pay.

The boring version, on purpose

The whole pipeline is built around one assumption: it will be interrupted, re-dispatched, and retried, and none of that is allowed to pay anyone twice or rank anyone wrong. Once that's the starting condition instead of an edge case, the rest follows. Unique jobs so duplicates evaporate. Explicit ordering through the batch. A streaming rank you can hold in your head. A database that doesn't fight the rest of the app. None of it is clever, and that's the point: settlement is where clever goes to cause incidents.