---
title: Refuse a DVOA write whose value columns are at the wrong scale
type: task
status: completed
description: >-
  Route every nfl_dvoa_* insert through upsert_dvoa_rows, which grades a whole import run before
  writing any of it (per-family value ceilings and a pooled-median floor), so a x100 or /100 scale
  error is refused with nothing written.
base_uri: user:task/league/add-dvoa-magnitude-range-check.md
created_at: '2026-10-26T23:10:31.956Z'
entity_id: 7bfcaf67-7008-4f66-97cc-ba73a9c988ed
finished_at: '2026-10-27T02:59:01.340Z'
observations:
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
relations:
  - relates [[████████████████████████████-█████████████████-████-██████████-███████]]
started_at: '2026-10-27T02:29:36.985Z'
tags:
  - user:tag/league-xo-football.md
updated_at: '2026-10-27T11:11:23.155Z'
visibility_analyzed_at: '2026-11-06T00:17:38.251Z'
---

# Refuse a DVOA write whose value columns are at the wrong scale

## Overview

- **Goal.** Refuse an `nfl_dvoa_*` import whose DVOA or VOA columns are scaled by 100 or by 1/100. The refusal happens before any row of the run is written.
- **Gap.** Two existing guards are blind to a uniform scale error:
  - The unit identity (offense − defense + special teams = total) is linear, so a value scaled by 100 still balances.
  - The header assertions check positions, not values.
- **Precedent.** Commit 999cc784b (2026-10-23) added `to_percent`, which wrote the per-game VOA as percent. Nothing refused it. Commit a7527c5a5 (2026-10-26) repaired it by hand, and no guard has been added since.
- **Impact.** The next scale defect from any DVOA importer fails loudly, writes nothing, and leaves the `_index` tables on their last good week. Without the guard it would land and reach the data views and the published dataset.
- **Ordering.** This follows [[████████████████████████████-█████████████████-████-██████████-███████]], whose Design named the check but never built it.

## Background

- **Writers:**
  - Two importers write through `libs-server/dvoa/upsert-dvoa-rows.mjs`: `scripts/import-dvoa-sheets.mjs` and `scripts/import-dvoa-team-history.mjs`.
  - Three importers insert directly: drive (`scripts/import-dvoa-drive-stats.mjs:192`), pace (`scripts/import-dvoa-pace-stats.mjs:313`) and player (`scripts/import-dvoa-player-stats.mjs:753`).
  - On the write path, all five share only `chunk_rows`.
  - The player skill-positions `.update` (`import-dvoa-player-stats.mjs:825-830`) writes QBR, ALEX and DYAR splits, with no DVOA or VOA column.
  - No other writer exists apart from hand-reviewed adhoc SQL.
- **A run writes several batches:**
  - Sheets writes unit, team and gamelog rows sheet by sheet for one week (`import-dvoa-sheets.mjs:1038-1076`).
  - Team-history writes two to three batches (`import-dvoa-team-history.mjs:625-636`, `:701-708`).
  - Player writes passing, then rushing, then receiving, each a whole multi-season workbook (`import-dvoa-player-stats.mjs:771-801`).
  - A refusal raised inside one upsert call would therefore leave earlier batches of the same run written.
- **Families.** `libs-server/dvoa/dvoa-tables.mjs` defines three families keyed `history_table`/`history_key` with an optional index.
  - `nfl_dvoa_team_gamelogs` is a single table filed under `history_table`, which is already a misnomer.
  - Drive, pace and the player tables have the same single-table shape.
- **Scale is declared nowhere:**
  - The data-view fields and the schema carry no unit.
  - Suffixes misclassify 18 columns:
    - Five special-teams `_dvoa` columns hold points: `field_goal_extra_point_dvoa`, `kick_dvoa`, `kick_return_dvoa`, `punt_dvoa` and `punt_return_dvoa`.
    - Six `non_adjusted_*` columns hold fractions.
    - Seven columns hold fractions with the token mid-name: `{total,offense,defense,special_teams}_dvoa_including_playoffs` and `{offense,defense,special}_voa_unadjusted`.
  - A further 150 integer `*_dvoa_rank` columns carry the token but are ranks.
  - The pattern `(^|_)d?voa(_|$)|^non_adjusted_|_dave$`, excluding `_rank$`, matches 106 columns across the non-index tables. All are `numeric`, and the five point columns are the only non-fractions among them.
- **Placement is prescribed:**
  - `docs/guides/gates.md:237` names "a percentage at the wrong scale" as a data failure that blocks the write.
  - `docs/guides/scripts.md:111` puts a refusal inside the writer, not at each caller.
- **Verdict precedent.** The ESPN line-win-rate grader (`libs-server/grade-espn-line-win-rates-run.mjs:90-91,165-166,270-314`) returns `{ passed, failures, data_failures, write_blocked, summary }` against `minimum_plausible_rate`/`maximum_plausible_rate`. Its importer throws `grade.summary` (`scripts/import-espn-line-win-rates.mjs:476`).
- **Production census, 2026-10-27**, over the graded columns:

| Family | All-time maximum absolute value | Ceiling | Column-batches the ceiling would miss under ×100 |
|---|---|---|---|
| Team season | 2.357 | 5 | 9 of 6,366 |
| Team gamelog | 2.174 | 5 | 1 of 2,101 |
| Unit | 10.987 | 20 | 6 of 2,945 |
| Player passing | 55.071 | 150 | 29 of 180 |
| Player rushing | 65.393 | 150 | 2 of 180 |
| Player receiving | 22.319 | 50 | 0 of 180 |

  - A column-batch is one table, column, season, season type and week. The player misses are all small postseason batches. The player importer writes a whole multi-season workbook per run, so graded at run grain the ceiling sees every season at once.
  - The pooled median runs over every graded value of a run. At the smallest run grain (team: one season type and week across all three team tables; player: one table and season), its minimum is 0.0619 for team runs and 0.1407 for player runs.
  - A single sheet graded alone sits far lower: the `specialDvoa` sheet reached 0.0098 in 2021 week 11, and 0 on the truncated 2024–2026 weeks. That is why grading is per run, not per batch.

<details><summary>Census SQL (run with <code>base db query league --file</code>)</summary>

```sql
-- DVOA scale census: graded columns = numeric columns matching the fraction pattern, minus the special-teams point columns.
with graded as (
  select table_name, column_name from information_schema.columns
  where table_name in ('nfl_dvoa_team_seasonlogs_history','nfl_dvoa_team_unit_seasonlogs_history','nfl_dvoa_team_gamelogs',
                       'nfl_dvoa_player_passing_seasonlogs','nfl_dvoa_player_rushing_seasonlogs','nfl_dvoa_player_receiving_seasonlogs')
    and column_name ~ '(^|_)d?voa(_|$)|^non_adjusted_|_dave$' and column_name !~ '_rank$'
    and column_name not in ('field_goal_extra_point_dvoa','kick_dvoa','kick_return_dvoa','punt_dvoa','punt_return_dvoa')
), stored as (
  select 'nfl_dvoa_team_seasonlogs_history' tn, season_year, season_type, week, to_jsonb(x) j from nfl_dvoa_team_seasonlogs_history x
  union all select 'nfl_dvoa_team_unit_seasonlogs_history', season_year, 'REG', week, to_jsonb(x) from nfl_dvoa_team_unit_seasonlogs_history x
  union all select 'nfl_dvoa_team_gamelogs', season_year, season_type, week, to_jsonb(x) from nfl_dvoa_team_gamelogs x
  union all select 'nfl_dvoa_player_passing_seasonlogs', season_year, season_type, null, to_jsonb(x) from nfl_dvoa_player_passing_seasonlogs x
  union all select 'nfl_dvoa_player_rushing_seasonlogs', season_year, season_type, null, to_jsonb(x) from nfl_dvoa_player_rushing_seasonlogs x
  union all select 'nfl_dvoa_player_receiving_seasonlogs', season_year, season_type, null, to_jsonb(x) from nfl_dvoa_player_receiving_seasonlogs x
), v as (
  select s.tn, s.season_year, s.season_type, s.week, g.column_name, abs((s.j->>g.column_name)::numeric) a
  from stored s join graded g on g.table_name = s.tn where s.j->>g.column_name is not null
)
-- Per-family maximum and ceiling misses:
, cb as (select tn, column_name, season_year, season_type, week, max(a) mx from v group by 1,2,3,4,5)
select tn, round(max(mx),3) legit_max,
  count(*) filter (where mx*100 <= case when tn ~ 'passing|rushing' then 150 when tn ~ 'receiving' then 50 when tn ~ 'unit' then 20 else 5 end) missed,
  count(*) column_batches from cb group by tn order by tn;
-- Pooled median at the smallest run grain:
, runs as (
  select case when tn ~ 'player' then tn else 'team' end run, season_year, season_type, coalesce(week,-1) week, count(*) n,
    percentile_cont(0.5) within group (order by a) pooled_median from v group by 1,2,3,4)
select run, round(min(pooled_median)::numeric,4) min_pooled_median, round(max(pooled_median)::numeric,4) max_pooled_median, min(n) min_values,
  (array_agg(season_year||' '||season_type||' w'||week order by pooled_median))[1] lowest from runs group by run order by run;
```

</details>

## Design

- **Grade the whole run, then write it.**
  - `upsert_dvoa_rows({ batches, dry_run })` takes every batch of a run as `{ family, rows, merge_columns, write_index }`.
  - It grades all of them together and throws `summary` when `write_blocked`, before the first write.
  - It then writes each batch as today. With `dry_run`, it returns the verdict after grading.
  - Each importer collects its batches and makes one call.
- **One write path, as the clean end state:**
  - Register drive, pace and the three player tables as families.
  - Drive and pace hold no DVOA column, so they gain no scale protection. They move so that every DVOA insert has one writer and `chunk_rows` becomes private to it.
  - The player skill-positions `.update` stays direct, because it is update-only and writes no graded column.
- **Family shape.** Rename the keys to `table` and `conflict_key`, with an optional `index_table` and `index_key`, so a single-table family is not filed as a history.
- **Scale declaration, kept minimal:**
  - Grade every numeric column matching `DVOA_VALUE_COLUMN_PATTERN` (`(^|_)d?voa(_|$)|^non_adjusted_|_dave$`, excluding `_rank$`) as a fraction by default.
  - Each family declares only its exceptions in `point_columns`, plus `maximum_plausible_value`.
  - A new point column that is left undeclared fails loudly at the ceiling, since point values reach 37.8 against a ceiling of 5. No separate rule for unclassified columns is needed.

| Family | `maximum_plausible_value` |
|---|---|
| `team_seasonlogs`, `team_gamelogs` | 5 |
| `team_unit_seasonlogs` | 20 |
| `player_passing_seasonlogs`, `player_rushing_seasonlogs` | 150 |
| `player_receiving_seasonlogs` | 50 |

  - `team_drive_seasonlogs` and `team_pace_seasonlogs` declare neither key.
- **Grader.** A pure `grade_dvoa_scale({ batches })` in `libs-server/dvoa/grade-dvoa-scale.mjs` returns `{ passed, data_failures, write_blocked, summary }`. Each data failure names the rule, family, column, row count and worst value. The grader has two rules:
  - **Ceiling (catches ×100, even on one column).** Any absolute graded value above its family's `maximum_plausible_value` fails.
  - **Pooled floor (catches a systemic ÷100).** The median absolute value over every graded value in the run must be at least `MINIMUM_PLAUSIBLE_POOLED_MEDIAN` = 0.01. The rule is graded only when the run carries at least `MINIMUM_VALUES_FOR_POOLED_MEDIAN` = 32 values. Stored runs sit between 0.0619 (a team week) and 0.6315 (a player season), so the floor is 6× under the legit minimum and every legit run ÷100 (at most 0.0063) falls below it. A lower floor of 0.003 was tried first: the run-grain census showed it missed ÷100 on 155 of 728 runs, nearly all player seasons, whose medians sit higher than team weeks. The cost is that a lone `specialDvoa` sheet (0.0098) would fail graded alone, and no importer writes one alone.
  - Only the columns present in the rows are graded, so partial `merge_columns` writes work unchanged.
  - `round_float_error_counts` touches only count columns (`import-dvoa-player-stats.mjs:739-749`), so grading before or after it is equivalent.
- **No registered data check.** The importers are the only writers, and gates.md puts this defect at the write. A check in the private data-check registry would duplicate the refusal.
- **Accepted gap.** A ÷100 error on a single column is not caught. No measure separates it at weekly grain.
- **New dependencies.** None.
- **Knowledge Base Impact:**
  - `████████████████████████████████████████████` says every DVOA column is a fraction, in both its description and the section at `:30`. Correct both to name the point columns.
  - `███████████████████████████████` needs two edits: say the `--dry` step now reports the scale verdict (`:53`), and replace "chunked upserts" with the graded `upsert_dvoa_rows` (`:41`).
  - No other league guide or `text/league/` doc names the write path. No ABOUT.md, tag or new guideline changes.
- **Working tier.** No scratch slug. The census is the SQL above.
- **Orchestrator shape.** This is a bounded single task, not an orchestrator.

## Notes

- **Ceiling margins are about 2× above the all-time maximum**, which follows the ESPN grader's lesson: choose bounds that separate the scales, not bounds fitted to history.
  - A legitimately new extreme refuses loudly.
  - The repair is raising one number after confirming the value against the source.
- **The 2026-10-23 per-game VOA defect is caught by the ceiling**, since legit per-game VOA tops out at 2.174. It is the regression fixture.
- **Two failures the guard does not cover:**
  - The one-decimal truncation of 2024–2026 rows. A magnitude check cannot see it, and the re-pull tracked in the DVOA domain doc covers it.
  - The mislabeled "Fractions" heading in a private ad hoc DDL file. That file is a historical record.
- **Adhoc SQL writes bypass the guard by construction.** They are hand-reviewed.
- **The importers' dry-run flag is `--dry`.**
- **Test conventions:**
  - Specs live flat as `test/<area>.<name>.spec.mjs` (`docs/guides/test.md:151-190`).
  - The spec and the export it imports land in the same commit.
  - A negative control asserts that the mutation changed the input before grading (`docs/guides/gates.md:143-153`).

## Plan

### Core Implementation

- [x] **Reshape and extend the families**
  - Description: In `libs-server/dvoa/dvoa-tables.mjs`, rename `history_table`/`history_key` to `table`/`conflict_key`.
  - Add the families `team_drive_seasonlogs`, `team_pace_seasonlogs`, `player_passing_seasonlogs`, `player_rushing_seasonlogs` and `player_receiving_seasonlogs`, with keys taken from the importers.
  - Add `point_columns` and `maximum_plausible_value` per the Design table.
  - Export `is_dvoa_value_column` and `DVOA_PLAYER_KEY`.
  - Purpose: Gives one write path and a minimal scale declaration.

- [x] **Write the grader**
  - Description: `libs-server/dvoa/grade-dvoa-scale.mjs`, which exports `grade_dvoa_scale({ batches })`, `MINIMUM_PLAUSIBLE_POOLED_MEDIAN` and `MINIMUM_VALUES_FOR_POOLED_MEDIAN`.
  - Purpose: Gives a pure, run-level verdict in the ESPN grader's shape.

- [x] **Make `upsert_dvoa_rows` grade the whole run first**
  - Description: Change the signature to `{ batches, dry_run }`. Grade, throw `summary` when `write_blocked`, return the verdict on `dry_run`, then write each batch.
  - Make `chunk_rows` module-private, and update the JSDoc.
  - Purpose: A refusal writes nothing from the run.

- [x] **Move every importer to one call per run**
  - Description: Each of the five importers collects its batches and makes one `upsert_dvoa_rows` call, passing `dry_run` instead of skipping the call.
  - Player batches use family `player_${role}_seasonlogs`, with `round_float_error_counts` applied to rows first.
  - Delete what becomes dead:
    - `DRIVE_TABLE` and `DRIVE_KEY` (`import-dvoa-drive-stats.mjs:29-30`)
    - `PACE_TABLE` and `PACE_KEY` (`import-dvoa-pace-stats.mjs:29-30`)
    - `PLAYER_KEY` and `PLAYER_TABLE_BY_ROLE`, replaced by `DVOA_PLAYER_KEY` and the families (the split-season merge from 82c3ad2ca still reads the key)
    - `upsert_player_rows`
    - the three `chunk_rows` imports
  - The skill-positions `.update` and the absorbed-row deletes read the table from the family; the deletes run after the graded upsert.
  - Purpose: Removes the bypasses and the dry-run blind spot.

### Knowledge Base Updates

- [x] **Correct the DVOA query-trap sheet**
  - Description: Update the description of `█████████████████████████████████████████████████` with `base entity update`, and edit the section at `:30`. Both should state the fraction rule plus the five special-teams point columns.

- [x] **Update the DVOA domain doc**
  - Description: In `███████████████████████████████`, name the graded `upsert_dvoa_rows` at `:41`, and say the `--dry` step reports the scale verdict at `:53`.

### Validation & Review

- [x] **Spec the grader**
  - Description: `test/libs-server.grade-dvoa-scale.spec.mjs`, committed with the grader.
  - Details:
    - A real-shaped multi-batch run passes.
    - The 2026-10-23 fixture, per-game VOA ×100 on one column, is refused. The test first asserts the mutation changed the batch.
    - ÷100 on every graded column is refused.
    - A partial-column batch is graded only on its own columns.
    - The point columns are not graded, and an undeclared point-scaled column fails the ceiling.
    - `_rank` columns are never graded.
    - A run under 32 values skips the floor.

- [x] **Spec the write path**
  - Description: Add a database-backed `test/libs-server.upsert-dvoa-rows.spec.mjs`, following the DB-backed pattern of `test/libs-server.dvoa-season-weeks.spec.mjs`. It covers four behaviours:
    - It upserts a row into each family and replays it idempotently.
    - A blocked run writes nothing, including batches graded before the failing one.
    - `dry_run` writes nothing.
    - `merge_columns` leaves the other columns intact.
  - Purpose: No existing DVOA spec exercises a write path, so this is what catches regressions from the move.

- [x] **Production census as the false-refusal control**
  - Description: Run the grader read-only over every stored run at run grain: each season type and week across the three team tables, and each player workbook. Every run must pass.
  - Purpose: Shows that the ceilings and the floor do not refuse real data. Each failing run is either a real defect to surface or a bound to correct before shipping.

- [x] **Dry-run every importer**
  - Description: Run each of the five importers with `--dry` against its latest source. Each must report a passing verdict and write nothing.

- [x] **Review and commit**
  - Description: Run `review-software`. Then commit the code and both specs as one unit by pathspec, and commit the KB edits.