---
title: Add Points Over Expected Column
type: task
status: completed
description: >-
  Add a role-matched player_points_over_expected_from_plays data-view column, serving issue 212's
  headline example. A naive subtraction of the two shipped operand columns is wrong for 27.5% of
  skill-position player-seasons because the expected variant drops the lateral and stat-sourced
  roles.
base_uri: user:task/league/add-points-over-expected-column.md
created_at: '2026-09-23T19:42:31.512Z'
entity_id: 56a9a852-61de-429a-9b49-97a39272f4c8
finished_at: '2026-09-23T20:35:46.127Z'
observations:
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
relations:
  - relates [[user:task/github/mistakia/league/212-add-over-expected-parameter.md]]
  - relates [[██████████████████████-████████-█████-████████]]
tags:
  - user:tag/league-xo-football.md
updated_at: '2026-09-23T21:31:24.680Z'
visibility_analyzed_at: '2026-11-06T00:17:38.964Z'
---

## Overview

- Add a single `player_points_over_expected_from_plays` data-view column: actual minus expected fantasy points, computed **per scoring role** over exactly the roles that carry a fitted model.
- Expected outcome: the headline example in [[user:task/github/mistakia/league/212-add-over-expected-parameter.md]] is served by one column for any `scoring_format_id`. The issue's other two examples — "WOs and FPTs DIFF", "PPG and PPG N+1 DIFF" — are NOT served and are deferred with a stated reason below.
- **Impact.** Today a user wanting points over expected adds the two operand columns and subtracts by eye. That subtraction is WRONG for 27.5% of skill-position player-seasons (measured below), and nothing tells them so. This replaces a silently-wrong manual workaround with a correct column.
- **Ordering.** Follows [[██████████████████████-████████-█████-████████]], which shipped both operands. Blocks nothing. Independent of [[███████████████████████-████████-█████-█████████████]] in both directions.

## Background

`libs-server/data-views-column-definitions/player-fantasy-points-from-plays-column-definitions.mjs` is ONE builder with TWO variants. `variant: 'expected'` swaps each per-role scoring expression for its expected twin and **drops the roles that have no fitted model**:

- the lateral role (no model; its points stay with the rushing or receiving role)
- every stat-sourced role — kicking, returns, two-point conversions, `fumbles_lost`, missed extra points — all attributed from `nfl_play_stats` rather than `nfl_plays`

So the two shipped columns are **not over the same role set**, and their difference is not points over expected. It is points over expected *plus* every point from a role the expected variant dropped.

### The measurement that shapes this plan

2024 REG, positions QB/RB/WR/TE, from `player_gamelogs` joined to `nfl_games`:

| Quantity | Player-seasons |
| --- | --- |
| Total skill-position player-seasons | 699 |
| Carrying at least one dropped-role term | 192 (27.5%) |
| — with a fumble lost | 159 |
| — with a two-point conversion | 65 |
| — with a punt or kickoff return touchdown | 10 |

The bias is not uniform. Fumbles lost score NEGATIVE in most formats, so a naive difference **understates** a fumbler's points over expected; return touchdowns and two-point conversions **overstate**. Both directions are live in the same population.

## Assumptions

- Column ids are permanent — they persist in immutable short URLs. `player_points_over_expected_from_plays` is chosen once and never renamed.
- The role-matched difference is the correct definition of points over expected, not `actual_column - expected_column`. This is the plan's central claim and the reason a third variant exists rather than a subtraction of two existing columns.
- The existing two columns stay exactly as they are. Nothing in this plan changes their emitted SQL, so their goldens must not move — a moved golden on either is a defect in this work, not a blessing candidate.

## Phase: emit the role-matched difference

Add `variant: 'over_expected'` to the shared builder. It takes the **expected** variant's role set (lateral dropped, stat-sourced dropped) and emits `(actual_expression - expected_expression)` per surviving arm rather than either expression alone.

- Extend `generate_fantasy_points_table_alias` with the new variant prefix. The alias already keys on variant precisely so two relations over the same rows cannot share a CTE — a missing prefix here serves whichever was built first.
- Both inner generators are already imported and already called side by side in this file (`generate_*_scoring_inner` and `generate_*_expected_scoring_inner`), so the difference needs no new scoring expression.

**Verify.** A player with a return touchdown or a lost fumble in 2024 REG: the new column must NOT equal the two shipped columns subtracted. Pick one from the 192 and assert the three numbers disagree in the predicted direction. A test that picks a player from the other 507 cannot distinguish the role-matched difference from the naive one and is vacuous — state which player was used and why they are in the affected set.

## Phase: declare it client-side

- `libs-shared/data-view-fields-index.mjs` — the field description, beside lines 38 and 39.
- `app/core/data-views-fields/fantasy-points-from-plays-table-fields.js` — the table field.
- Reuse the existing `scoring_format_id` and play-filter params; add no new param.

**Verify.** The client/server capability parity spec (`data-views.capability-parity.spec.mjs`) covers the advertised-versus-served param set. Confirm it collects the new column by name in the log rather than inferring coverage from a passing total.

## Phase: grade it

Run the FULL suite — `yarn test:isolated`. Two harnesses grade a column-definition change and **no `.spec` glob collects either of them**: `test/libs-server.data-view-queries.mjs` (SQL only) and `test/libs-server.data-view-queries-result-equivalence.mjs` (executed results). See [[user:guideline/nfl/league/data-view-column-definition-test-selection.md]].

**Verify.** Assert on the PRESENCE of the `N passing / N failing` summary line and the logged exit code, never on the absence of failures — a suite that aborts during module load emits no summary and every grep for failures reads green. Confirm both harness files by name in the log.

## Deferred, with reason

**The generic difference parameter is NOT in this plan, and it is not analogous to `rate_type`.** The issue asks for one by analogy, but the analogy does not survive contact with the architecture:

- `rate_type` (now the `output` param) picks a DENOMINATOR UNIT from a fixed vocabulary — `game`, `team_play`, `player_route`. It is a property of ONE column, and the whole `libs-server/data-views/period-denominator/` tree exists to serve it.
- A difference needs a second COLUMN INSTANCE with its own params. "PPG and PPG N+1 DIFF" is the same column at two different `year_offset` values; "WOs and FPTs DIFF" is two unrelated columns.

Nothing in the system expresses a binary operation across two column instances. Grepped and confirmed absent: no `diff_type`, no `difference_type`, no `over_expected_type`, no `'diff'` literal, and no derived-, computed- or formula-column machinery anywhere in `libs-shared/` or `libs-server/data-views/`. The `*_over_expected` fields that do exist are pre-existing vendor columns from a data supplier (completion percentage, rush yards, catch rate) and are unrelated.

That feature is a **derived-column tier** — a column whose source is other columns in the same view — and it needs its own design covering operand identity, param divergence between operands, null handling when one operand is absent, and what a derived column means under an `output` aggregation. It is a materially larger piece of work than the analogy in the issue suggests, and it should not be started inside this plan.

Issue 212 therefore stays OPEN after this task completes, with its headline example served and its other two examples outstanding.

### Knowledge Base Impact

- **Repo docs.** `docs/glossary.md` gains the new column. `docs/fantasy-points-column-definition.md` gains the role-matching argument — that is the non-obvious half and the thing a future reader will otherwise rediscover by shipping a wrong subtraction.
- **Canonical doc.** [[user:text/league/expected-stats-model.md]] gains the dropped-role asymmetry and the 27.5% figure.
- **Relations.** `relates` to [[user:task/github/mistakia/league/212-add-over-expected-parameter.md]] (partially closes) and [[██████████████████████-████████-█████-████████]] (supersedes its false closure claim, corrected in that task's body).

## Plan

- [x] Add `variant: 'over_expected'` to the shared builder, emitting `(actual - expected)` per arm over the expected variant's role set
- [x] Extend `generate_fantasy_points_table_alias` with the new variant prefix so the CTE alias cannot collide
- [x] Declare the column in `libs-shared/data-view-fields-index.mjs` and `app/core/data-views-fields/fantasy-points-from-plays-table-fields.js`
- [x] Prove the role-matching with a player from the 192 affected 2024 player-seasons, asserting the new column differs from the naive subtraction in the predicted direction
- [x] Run the full `yarn test:isolated`, confirming both uncollectable harnesses by name and asserting on the summary line — run twice, both times RED from a sibling's in-flight mirror work and never from this change (see observations). Proven green instead by a targeted run of the previously-failing spec plus both harnesses: 417 passing, 0 failing, exit 0, both harnesses present by name. **A clean full-suite run with this change has not been observed**, because the shared tree was not green at any point today.
- [x] Update `docs/glossary.md`, `docs/fantasy-points-column-definition.md` and [[user:text/league/expected-stats-model.md]]

## The no-clean-full-suite caveat is closed, 2026-09-23

Recorded by another session on close and retired here by measurement, because three of the sessions involved had ended before the evidence existed.

`5bb94b66e` is **deployed**, in `DEPLOY_ACK_RANGE=9` at 21:00Z, on both hosts at `e52c86cbc` with the frontend bundle and the API agreeing on that sha — so the column is selectable in the picker rather than a server capability nobody can reach.

A clean full suite then ran against that exact deployed commit, in a throwaway worktree pinned to `e52c86cbc`, installed `--immutable`, `private` initialised, and none of the shared tree's uncommitted paths present:

```
12009 passing (8m)
1 failing
```

**The single failure is an artifact of the clean worktree and not this change.** `test/api.unmatched-api-json-404.spec.mjs`, case "control: a real SPA route still renders index.html", expected 200 and answered 404 — because `dist/` is gitignored, so a fresh worktree has no `index.html` for the SPA fallback to serve. Proved rather than assumed: `yarn build` in the same worktree, then that spec alone, gives 4 passing and exit 0. The two readings differ on the one variable that changed.

**Correction, 2026-09-23.** An earlier version of this section claimed "the column's own specs executed rather than being silently absent — 15 matches by name in the run log". That was wrong. The grep used an OR pattern, `points.over.expected|fantasy.points.from.plays`, and all 15 matches were the PRE-EXISTING sibling column. Checked precisely afterwards: `points_over_expected` appears **0 times** in 37,537 lines of suite output.

**Nothing in the suite exercises this column.** The client/server capability-parity spec looks like it would enumerate every column and does not — it iterates a hand-built `cases` list, and this column is outside that sample. The lane owner found this independently and it is the correct reading.

So be precise about what the green proves: **no regression** anywhere in 12,009 tests, which is what mattered for the deploy. It does **not** prove the column returns correct numbers, and no automated test does.

What does prove that was done against production before the commit, by the lane owner, and it is stronger than a spec would have been: both builder paths (the `with` builder and the role-union path under an output aggregation), plus an affected case and a negative control chosen from the population rather than at random. KaVontae Turpin 2024 reads -2.14 where a naive subtraction reads +8.86, and the 11.00 gap is exactly his two return touchdowns less his lost fumble — so the arithmetic identifies the cause rather than merely differing. Ja'Marr Chase, carrying no dropped-role term, agrees with the subtraction to the cent.

**The absence of coverage is debt against this column's FUTURE, not a defect in it** — it means a later change could break it silently. Recorded here because the lane that found it has ended.

**Worth keeping for the next reader:** running the suite in a fresh worktree costs one build, or that control fails and reads like a routing regression. It is the only spec in the suite that depends on a build artifact.