---
title: Shrink the hand-maintained schema surface in █████████████████████
type: task
status: completed
description: >-
  Restructure the ███ ████████ ████████ guideline so it stops restating facts the database can
  answer, relocates coaching attribution into the league repo beside its DDL, and delivers the
  remaining non-derivable traps as hook-surfaced per-table sheets.
base_uri: user:task/league/shrink-nfl-database-analysis-schema-surface.md
created_at: '2026-09-13T13:02:34.078Z'
entity_id: a00232ba-ee49-4ba6-a3e0-5c73252178fb
finished_at: '2026-09-13T13:18:00.638Z'
observations:
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
relations:
  - follows [[█████████████████████-█████████████-██████-████████]]
  - follows [[███████████████████████-█████-██████-████████████████]]
  - relates [[█████████████████████████████-████████-███████████]]
tags:
  - user:tag/league-xo-football.md
updated_at: '2026-09-13T13:18:17.483Z'
visibility_analyzed_at: '2026-11-06T00:18:05.569Z'
---

# Implementation Plan: Shrink the hand-maintained schema surface in █████████████████████

## Overview

- Restructure `█████████████████████████████████████████████` so that it stops restating facts the league database can answer for itself, leaving only knowledge the database cannot supply.
- Reduce drift by removing the surface that drifts, rather than by adding more detection over it.
- Deliver the remaining knowledge through hook-driven progressive disclosure, so a session receives the traps for the tables it is actually querying and loads nothing else.
- Impact: analysis sessions stop reading a column inventory the file itself instructs them not to trust, and start receiving per-table traps at the moment they write a query. Maintenance moves to whoever changes a table, in the same commit as the change.
- Ordering: follows the two Completed tasks that built and ran the drift gate — `███████████████████████████████` and `█████████████████████████████████`. Those gated and swept the `table.column` surface; this reduces the surface they gate. No sibling task owns this scope.

## Background

- `█████████████████████████████████████████████` is a table-level schema reference for `league_production`, roughly 495 body lines, carrying about 175 backticked identifiers and 29 qualified `table.column` pairs.
- `db/gates/check-league-schema-consumers.mjs` in the league repo already reads this file. `scripts/check-cluster-gates.mjs:152` passes `--root ../../../guideline/nfl` to it. Its gate 1 judges qualified `table.column` tokens only, so the bare-identifier majority of the file is outside any oracle.
- `db/schema.postgres.sql` is the generated source of truth, produced by `yarn export:schema`, never hand-edited (`db/README.md`).
- The `entity-context` hook matches edited paths, commands, and content against `surface_on` triggers and injects applicable guidelines (`hooks/README.md:98`). `surface_on` moved from the `guideline` type to `entity`, so it now covers the whole checkout (`hooks/README.md:101`). The file already declares four `surface_on.commands` entries.
- `hooks/tests/surfacing-live-wiring.test.mjs` exists to assert that surfacing triggers actually fire.

### Confirmed drift at plan time

Three claims in the file are false against `league_production`, all in the bare-identifier surface no gate reads:

- `projections` is named as a table; live names are `projections_index_yYYYY` and `projections_history_yYYYY`.
- `schedule` is named as a table under "Calendar"; no such table exists.
- `league_format_player_projection_values.projected_points_added` does not exist; the column is `projected_points_added_net`. The file also instructs filtering a `-999` sentinel on the wrong name.

A check of 38 documented columns against `information_schema.columns` found only the third one missing, so the column-level body is otherwise sound and the drift is concentrated in table names and prose-described columns.

### Assumptions

- The drift gate reads this file today — checked: `grep -n USER_BASE_ROOTS scripts/check-cluster-gates.mjs` shows `../../../guideline/nfl` passed to `check-league-schema-consumers`.
- Gate 1 judges only qualified pairs, so bare identifiers are unchecked — unchecked by execution, source-of-truth: the gate's own header comment describing gate 1 as "every `table.column` token in the corpus".
- `surface_on` fires on Bash command content, which is how a `base db query league "... <table> ..."` call would trigger a trap sheet — checked: `hooks/README.md:98` names Bash as a trigger surface and commands as a matched input.
- `db/schema.postgres.sql` regenerates from the live database and is not hand-edited — checked: `db/README.md` states it is generated by `yarn export:schema`.
- The three stale claims above — checked: `information_schema` queries run against `league_production` on 2026-09-13.
- Coaching Attribution content describes league-repo DDL and scripts, so it can move into that repo without losing an audience — unchecked assumption about reader habits; mitigated by leaving a routing line behind in the guideline.

## Design

The file's claims split three ways, and only the third should be hand-written.

- **Structure** — table exists, column exists, type, partition range. The database answers this in one query, and the file already instructs readers to verify against `information_schema.columns` rather than trust the listing. Full maintenance cost, no authority. Removed.
- **Measurements** — coverage percentages, null rates, populated year ranges. The database answers these too, but the query is non-obvious. These decay continuously rather than only on DDL, so they are worse than structure. Replaced by the query that produces them.
- **Semantics** — why a column lies, what a join silently does, who owns a field. The database cannot answer these, they survive schema changes, and they are the file's actual value. Kept, and split for disclosure.

### Target shape

- A short guideline carrying only cross-cutting rules that apply to any query against this database and do not drift: check the query library first, verify structure yourself, filter `play_type != 'NOPL'`, scope to a year partition, drop the `-999` sentinel, read constraints from `pg_constraint` rather than `information_schema.table_constraints`, read the clock as bare `now()`.
- A routing index of table name to a one-line purpose, with no columns, types, or coverage numbers. A name-and-purpose line changes only on a drop or rename, which `db/gates/check-dropped-table-consumers.mjs` already covers.
- A set of per-table trap sheets, one small entity per high-traffic table or tight cluster, each declaring `surface_on.commands` for its table names.
- Coaching Attribution relocated into the league repo beside the DDL it describes, so the schema change and the doc change land in the same commit.

### Trap sheet inventory

Derived from the semantics already present in the current file:

- `pff_player_gamelogs` — no `season_type` column, preseason and postseason rows present, directional bias downward, snap-rank starter misidentification.
- `player_contracts` — void years counted by `MAX(season_year)`, the literal `'Total'` row, why row presence is not a rostered signal.
- `player_changelog` — the time-windowed join requirement and the whole-season `GROUP BY` back-propagation anti-pattern.
- `player_receiving_gamelogs` — advanced columns NULL before 2019, `route_share` partial coverage, the dropback-based reconstruction.
- `nfl_plays` — `NOPL` filter, partition scoping, `run_concept` non-random nulls.
- `nfl_team_gamelogs` — no `season_type` column, so REG filtering needs an `nfl_games` join.
- `player_gamelogs` — the `is_active` trap for games played, REG filtering, pre-2006 receiving coverage.
- `historical_injury_index` — use it rather than rebuilding the five-table join; per-tier coverage floors.
- `league_format_*` projection cluster — the `-999` sentinel, period-is-a-table rather than a reserved `week` value, the 2021 backtest coverage split.
- `composite_market_value_daily` — `composite_coverage_score` gating, `format_category` scoping.

### Knowledge Base Impact

- `█████████████████████████████████████████████` shrinks substantially and gains a routing role.
- New trap-sheet entities under a `guideline/nfl/league/query-traps/` directory, each with its own `surface_on`.
- A new `ABOUT.md` for that directory.
- `guideline/nfl/league/ABOUT.md` gains routing lines for the new directory.
- `repository/active/league/docs/coaching-attribution.md` created; registered in that repo's `CLAUDE.md` Key Documentation table.
- Observations on the existing guideline recording the three repaired claims are unnecessary — the repair is the record. Existing observations that merely restate coverage numbers become candidates for pruning under persistence restraint.

## Notes

- The file is public (`public_read: true`); new trap sheets inherit nothing automatically and must not have visibility set by hand.
- A trap sheet whose trigger never fires is a silent failure, worse than a stale line because nothing is visibly wrong. Every sheet needs a live-wiring assertion.
- A sibling session building a coin-toss dataset is live in the league repo. Coaching Attribution content overlaps its subject area; check for conflicting edits before committing the relocation.
- Commit the league-repo relocation and the user-base edits as separate coherent units, since they are separate repositories.

## Plan

### Immediate Repair

- [x] **Fix the three confirmed stale claims**
  - File: `█████████████████████████████████████████████`
  - Purpose: the claims are wrong today and a reader acting on them writes a failing query; repairing them first means the restructure never carries them forward.
  - Changes:
    - Correct `projections` to the live partitioned names.
    - Remove `schedule` from the Calendar cluster.
    - Correct `projected_points_added` to `projected_points_added_net`, including the `-999` sentinel instruction.
  - Verify: re-run the `information_schema` existence check for all three names and confirm zero missing.

### Relocation

- [x] **Create the coaching attribution reference in the league repo**
  - File: `repository/active/league/docs/coaching-attribution.md`
  - Purpose: the content describes `nfl_coaches`/`nfl_game_coaches` DDL, constraints, the importer's ownership contract and the sentinel-DOB convention — all league-repo artifacts. Colocation makes the DDL change and the doc change one commit, which removes the drift mechanism rather than detecting it.
  - Changes:
    - Move the constraint definitions, the `coach_id` format contract, the sentinel-DOB convention, the importer ownership contract for `nfl_games.{home,away}_play_caller`, and the interim-coordinator seed list.
    - Replace the per-season coverage percentage tables with the query that produces them.
    - Drop the migration audit trail line; the adhoc files are their own record.
  - Verify: `node db/gates/check-league-schema-consumers.mjs` over the repo roots returns no new findings for the created file.

- [x] **Register the new doc**
  - File: `repository/active/league/CLAUDE.md`
  - Purpose: an unregistered doc is undiscoverable; the Key Documentation table is the index.
  - Changes: add a row for `docs/coaching-attribution.md`.
  - Verify: `grep -n coaching-attribution CLAUDE.md` returns the row.

- [x] **Replace the Coaching Attribution section with a routing line**
  - File: `█████████████████████████████████████████████`
  - Purpose: preserve discoverability for the user-base reader without holding the content where it drifts.
  - Changes: delete the section, leave one line naming the two tables and pointing at the league-repo doc.
  - Verify: `grep -c nfl_coaches █████████████████████████████████████████████` reflects only the routing line and the routing index entry.

### Surface Reduction

- [x] **Remove structure listings**
  - File: `█████████████████████████████████████████████`
  - Purpose: these carry maintenance cost with no authority, since the file already directs readers to `information_schema`.
  - Changes: strip per-table column inventories, leaving table name and one-line purpose; keep the existing schema-discovery-hygiene instruction and strengthen it into the file's stated contract.
  - Verify: count backticked identifiers before and after; the qualified-pair count should not rise, and no removed identifier should be a semantic claim rather than a structural one.

- [x] **Replace measurements with their queries**
  - File: `█████████████████████████████████████████████`
  - Purpose: a number in prose has an expiry date nobody tracks; the query is permanently true.
  - Changes: convert remaining coverage percentages and populated-year ranges into fenced SQL a reader can run.
  - Verify: each fenced block executes against `league_production` and returns rows.

- [x] **Rewrite the file as cross-cutting rules plus a routing index**
  - File: `█████████████████████████████████████████████`
  - Purpose: this is the layer that is always loaded, so it must hold only what applies to every query and never drifts.
  - Changes: restructure into the cross-cutting rule set and the table-to-purpose routing index; update the description frontmatter to match the new role via `base entity update`.
  - Verify: the file's own claims re-checked against `information_schema`; `base entity get` shows the updated description.

### Progressive Disclosure

- [x] **Create the trap-sheet directory and its ABOUT**
  - File: `█████████████████████████████████████████`
  - Purpose: directory-level routing so the set is discoverable without the hook.
  - Changes: create via `base entity create`; index each sheet with a one-line summary.
  - Verify: `base entity get` resolves the ABOUT and every linked sheet.

- [x] **Create one trap sheet per table or cluster in the inventory**
  - Files: `guideline/nfl/league/query-traps/<table>.md`
  - Purpose: each sheet holds only non-derivable semantics for one table, so it changes only when that table's semantics change and the person changing the table is the one who edits it.
  - Changes: create each via `base entity create` with `surface_on.commands` naming its table names; move the corresponding semantics out of the main file.
  - Verify: for each sheet, confirm the semantics it received no longer appear in the main file, and that no semantic content was dropped in transit.

- [x] **Assert the surfacing wiring**
  - File: `hooks/tests/surfacing-live-wiring.test.mjs`
  - Purpose: a sheet whose trigger never fires is an invisible failure; this is the one check that distinguishes a working split from a silent one.
  - Changes: add an assertion per sheet that a representative `base db query league` command naming its table surfaces that sheet.
  - Verify: run the hooks test suite; assertions pass, and a deliberately misspelled table name in a scratch run does not surface the sheet.

### Testing & Validation

- [x] **Population-level surfacing check**
  - Purpose: an asset-level spot check cannot distinguish a working split from one where a single sheet surfaces for everything or where most sheets are dead. This catches a split that looks wired but covers one table.
  - Changes: run a command naming each inventoried table in turn and record which sheets surface.
  - Verify: every sheet surfaces for at least its own table names, and no sheet surfaces for a table outside its declared set.

- [x] **Run the drift gate over the restructured corpus**
  - Purpose: confirm the restructure did not introduce new unresolvable pairs and that the reduced surface still passes.
  - Changes: none; execution only.
  - Verify: `yarn check:cluster` limited to the `league-schema-consumers` gate returns OK with no new findings, and its negative control still fails as designed.

### Knowledge Base Updates

- [x] **Route to the new directory from the league guideline index**
  - File: `guideline/nfl/league/ABOUT.md`
  - Changes: add a line for the trap-sheet directory describing when a reader would enter it.
  - Verify: `grep -n query-traps guideline/nfl/league/ABOUT.md` returns the line.

- [x] **Prune observations that restate relocated or derivable content**
  - File: `█████████████████████████████████████████████`
  - Purpose: the observation log carries coaching-backfill completion entries and coverage numbers that the relocation and the measurement rule make redundant on this entity.
  - Changes: review each observation against the file's new role; leave gotchas that became trap-sheet content referenced rather than duplicated.
  - Verify: read the remaining observation list and confirm each still describes this entity's current scope.