--- title: Retype low-cardinality TEXT data-view columns to SELECT type: task status: completed description: >- Retype the eight league data-view columns that are enumerable attributes wrongly declared as free text so they offer a generated value picker, leaving the 27 external-identifier columns and two genuinely free-text columns as TEXT. base_uri: user:task/league/retype-low-cardinality-text-columns-to-select.md created_at: '2026-09-22T04:09:43.520Z' entity_id: e4286573-c00f-4cdd-9161-9e76d6397cc9 finished_at: '2026-10-10T18:27:36.785Z' observations: - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ owner_identity_uri: █████████████████████████ public_read: true relations: - blocked_by [[user:task/league/resolve-generated-option-sets-on-column-values.md]] - succeeds [[user:task/react-table/add-column-header-quick-filter.md]] - >- relates [[user:task/github/mistakia/league/143-set-acceptable-values-for-various-data-view-column-fields.md]] started_at: '2026-10-10T17:18:36.973Z' tags: - user:tag/league-xo-football.md updated_at: '2026-11-06T00:04:18.610Z' visibility_analyzed_at: '2026-11-06T00:18:03.204Z' --- # Implementation Plan: Retype Low-Cardinality TEXT Columns to SELECT ## Overview - Retype the league data-view columns that are enumerable attributes wrongly declared as free text, so they offer a value picker instead of a substring box - Draw every retyped column's vocabulary from a generated option set, never a hand-written list - Leave genuinely free-text and identifier columns as TEXT, deliberately and on the record **Impact.** Of 38 TEXT-typed data-view columns, only two are genuinely free text. Filtering `player_college_division` today means typing a substring and hoping; after this it is a picker over the 30 values that exist. The columns affected are among the most natural things to filter a player view by — college, division, depth, combine designations, team. **Ordering.** Blocked by `user:task/league/resolve-generated-option-sets-on-column-values.md`, which supplies the mechanism; retyping without it would mean hand lists, and a hand list on a picker-only control makes any missing value unfilterable. Sequences after `user:task/react-table/add-column-header-quick-filter.md` so the retyped columns land into a header panel that already has a value-set body. Absorbs the untriaged GitHub-sourced stub `user:task/github/mistakia/league/143-set-acceptable-values-for-various-data-view-column-fields.md`. ## Background **The current TEXT population, measured.** `grep -rn "TABLE_DATA_TYPES.TEXT" app/core/data-views-fields/` returns 44 hits, of which 7 are `column_params` declarations rather than columns, leaving 38 TEXT columns. Twenty-seven of those are external-system identifier fields (`player_espn_id`, `player_gsisid`, `player_sleeper_id` and so on). The remainder: | Column | Distinct values | Non-null rows | Disposition | | --- | --- | --- | --- | | `player_forty_yard_dash_designation` | 2 | 3,658 | retype | | `player_ten_yard_split_designation` | small | — | retype | | `player_pro_forty_yard_dash_designation` | small | — | retype | | `player_sixty_yard_shuttle_designation` | small | — | retype | | `team_name` | 32 | — | retype | | `player_college_division` | 30 | 12,180 | retype | | `player_position_depth` | 70 | 28,884 | retype | | `player_college` | 944 | 28,417 | retype, generated | | `player_hometown` | 860 | 1,297 | leave TEXT | | `player_name` | open | — | leave TEXT | Counts measured against the live league database. The three designation siblings were not individually counted because their columns are named differently from the field ids; count them during implementation rather than assuming they match the 2 measured for `forty_yard_dash_designation`. **Why the identifier columns stay TEXT.** An external-system id is a lookup key, not a vocabulary. A picker over 28,000 ESPN ids is unusable, and the right interaction is exact match, which TEXT already offers. `user:task/react-table/add-column-header-quick-filter.md` leads the TEXT body with Equals precisely for this population. **Why `player_hometown` stays TEXT.** 860 distinct values across only 1,297 non-null rows is an open set with almost no repetition — a picker would be a list nearly as long as the result set, and a new hometown appears with every player added. **The picker-only trap.** Under `data_type: SELECT` react-table routes to a `FilterValuePicker` while other data types fall through to a `TextField`, so a value absent from the declared list is unfilterable rather than merely unlisted, and a declared option matching no rows returns an empty table reading as "no rows met this condition". Neither direction raises anything. This is why the prerequisite task exists and why no retyped column may carry a hand list. ### Assumptions - **Every retyped column's values come from one table column.** Checked for `college`, `college_division`, `position_depth`, `hometown` and `forty_yard_dash_designation` via `player_value_path` in `app/core/data-views-fields/player-table-fields.js`; unchecked for the three designation siblings and `team_name`. - **`player_college` at 944 values is acceptable in the picker.** Unchecked. It is five times the largest existing set (194 play callers). If the picker is unusable at that size the column needs a searchable control and this plan grows a step; verify by rendering it before retyping the rest. - **College is an open set.** Unchecked as a trend, but treated as open: new programs and name changes appear, which is the argument for generation over a static list. - **Retyping changes no stored filter's meaning.** Unchecked and the main migration risk: a saved view holding a `LIKE '%Alabama%'` row on `player_college` becomes a picker-backed column where that stored operator is no longer offered. Enumerate affected saved views before landing. ## Design Each retyped column declares `option_set: ''` and carries no `column_values` of its own, exactly as a param declares one today. The generator gains one record per set in its declarative list at `scripts/generate-column-param-option-sets.mjs:161-190`, each a name plus a knex query selecting distinct non-null values from the owning table. Labels equal values for all of these, so every set emits bare scalars rather than `{ value, label }` records. Set naming follows the existing per-role convention (`head_coach`, `offense_play_caller`, `referee`), naming what the values ARE rather than which table holds them: `college`, `college_division`, `position_depth`, `team_name`, and one per combine designation. Each name is full-word and greppable per [[user:guideline/write/write-software.md]]. The retyped columns keep their existing `operators` behaviour, which `DATA_TYPE_OPERATORS[SELECT]` resolves to `=`, `!=`, `IN`, `NOT IN` and both null checks. **Knowledge Base Impact:** no `text/` entity describes the TEXT/SELECT split, and none becomes stale. The GitHub-sourced stub `143-set-acceptable-values-for-various-data-view-column-fields` is absorbed and should be closed pointing here. No new tag. One observation candidate on the league repo ABOUT: the measured finding that 27 of 38 TEXT columns are identifier fields, since it is the kind of thing a future session would otherwise re-derive. **Scratch tier:** not needed. **Orchestrator shape:** bounded to a single task. ## Notes - **Retype in two waves, smallest first.** The designations and `college_division` are tiny and low-risk; `player_college` at 944 is the one that tests whether the picker holds up. Landing the small ones first means the mechanism is proven before the hard case. - **A saved view is the migration hazard, not the column.** The stored `where` row survives retyping untouched, but its operator may no longer be offered by the control that now renders it. Enumerate before landing rather than discovering it from a user report. - **Staleness has no scheduled job by design.** The generated sets are refreshed by re-running the generator; `libs-server/param-vocabulary-drift.mjs` reports rot. A retyped open set inherits that posture — but NOT automatically, and this is the sharpest thing to get right here. The drift scan does not reach a column-level declaration at all today, so a set introduced by this task rots silently until the structured-source step below lands. Do that step BEFORE the retyping waves, not after: a picker whose vocabulary is quietly stale is worse than the substring box it replaced, because the user cannot tell a missing option from an absent value. ## Plan ### Generated sets - [x] **`scripts/generate-column-param-option-sets.mjs`** — add one record per new set to the declarative list at `:161-190`: `college`, `college_division`, `position_depth`, `team_name`, and one per combine designation, each selecting distinct non-null values from its owning table. Purpose: the vocabularies must be generated, since a hand list on a picker-only control makes a missing value unfilterable. - Verify: run the generator; `libs-shared/column-param-option-sets/` gains one module per set with counts matching a `SELECT COUNT(DISTINCT ...)` against each source column - [x] **Give each new set a STRUCTURED source, and extend `libs-server/param-vocabulary-drift.mjs` to scan column-level declarations** — the generator's set records carry `source` as prose (`'distinct nfl_games.referee'`), which no scanner can query. Each new record needs a machine-readable `{ table, column }` alongside it, and the drift module needs a third vocabulary function reading the client field registry for column-level `option_set` declarations, keyed on the set name and deduplicated against the sets the param surface already reports. Purpose: the prerequisite task shipped the resolution seams but could NOT close this one — `data_view_param_vocabularies` iterates the param registry and sources each entry from `DATA_VIEW_PARAM_SOURCES` keyed by param key, so a column naming a set has no source to scan. Until this lands, every set introduced here goes stale silently, which is the exact property the generated-set mechanism exists to remove. - Verify: run the drift report and confirm each new set is NAMED in it; then perturb one set's module by deleting a value it has and confirm the report moves — a scan that cannot report is indistinguishable from a clean one - [x] **Count the three uncounted designation columns** — `player_ten_yard_split_designation`, `player_pro_forty_yard_dash_designation`, `player_sixty_yard_shuttle_designation`. Purpose: the plan assumes they are small like their measured sibling and must not retype one that is not. - Verify: a `SELECT COUNT(DISTINCT ...)` per column, recorded as an observation on this task ### Retyping, small sets first - [x] **`app/core/data-views-fields/player-table-fields.js`** — retype `player_college_division`, `player_position_depth` and the four combine designations to `data_type: SELECT` with `option_set` naming their generated set and no `column_values`. Purpose: the low-risk wave that proves the mechanism. - Verify: each column renders a picker with the generated values in the browser; each set arrives as its own chunk in the network panel - [x] **`app/core/data-views-fields/team-table-fields.js`** — retype `team_name` the same way. Purpose: 32 values, the clearest picker case in the set. - Verify: picker renders 32 teams; filtering returns rows - [x] **`app/core/data-views-fields/player-table-fields.js`** — retype `player_college`, the 944-value case. Purpose: the one that tests the picker's limit; land it only after the small wave is green. - Verify: picker renders and is usable at 944 options; if it is not, stop and record the finding rather than shipping an unusable control ### Migration safety - [x] **Enumerate saved views holding a `where` row on any retyped column** — query the saved-view store for rows whose `column_id` is in the retyped set and whose operator is not one `DATA_TYPE_OPERATORS[SELECT]` offers. Purpose: the stored row survives retyping but its operator may no longer be offered. - Verify: the query and its row count recorded as an observation before any retyping lands; if non-empty, decide per view rather than repairing automatically, since a filter is reversible user state ### Knowledge Base Updates - [x] **`user:task/github/mistakia/league/143-set-acceptable-values-for-various-data-view-column-fields.md`** — set status `Abandoned` with a `[superseded-by]` observation pointing here. Purpose: it is an untriaged stub describing exactly this work. - Verify: `base entity get` shows the status and observation - [x] **`user:repository/active/league/ABOUT.md`** — add an observation recording that 27 of 38 TEXT-typed data-view columns are external-system identifier fields and are deliberately left as TEXT. Purpose: otherwise a future session re-derives the census before touching the same columns. - Verify: `base entity get user:repository/active/league/ABOUT.md --json` shows it