--- title: Extend keeptradecut importer to ingest draft pick (RDP) values type: task status: completed description: >- Remove the RDP skip in scripts/import-keeptradecut.mjs (line 81) and ingest KTC draft pick values into keeptradecut_rankings so picks have market value coverage. Required prerequisite for the picks side of ████████████████████████████████. Today the importer skips position=RDP rows; the table therefore has zero pick rows and any pick-asset composite degrades to draft_pick_model_value only. base_uri: user:task/league/extend-keeptradecut-importer-to-ingest-pick-values.md created_at: '2026-05-19T18:48:34.514Z' entity_id: 54789789-7db1-4f38-ba88-a57ed3ef063b finished_at: '2026-05-19T21:58:57.849Z' observations: - ████████████████████████████████████████████████ owner_identity_uri: █████████████████████████ priority: Medium public_read: true relations: - relates [[████████████████████-█████████-██████-█████-████████]] - relates [[███████████████████████-█████-█████████-████████]] - blocks [[user:task/league/add-keeptradecut-liquidity-tracking.md]] started_at: '2026-05-19T20:40:41.598Z' tags: - user:tag/league-xo-football.md updated_at: '2026-10-26T05:00:25.964Z' visibility_analyzed_at: '2026-11-05T23:49:14.984Z' --- ## Overview The KTC importer skips `position === 'RDP'` rows at `scripts/import-keeptradecut.mjs:81`, leaving `keeptradecut_rankings` with zero pick rows. Plan B's composite market-value index needs per-pick KTC values to populate `ktc_value` for pick assets; without them pick composites lean on `league_format_draft_pick_value` alone and the KTC market signal for picks is unrecoverable. This task ingests KTC's RDP rows under a stable synthetic pid scheme, adds a lookup table that captures pick metadata KTC's namespace cannot carry, and runs a one-time `--full` backfill to load ~2.7 years of pick history. ## Background ### KTC RDP shape (validated live 2026-05-19) - 36 RDP rows in `playersArray` from `https://keeptradecut.com/dynasty-rankings`: 3 years (2026, 2027, 2028) x 3 slots (Early, Mid, Late) x 4 rounds (1st-4th). No round-aggregate row. - `playerName` is rigid: `" <1st|2nd|3rd|4th>"`. `position = 'RDP'`, `positionID = 5`, `team = 'FA'`, `draftYear = 0`. `slug` embeds the stable integer KTC playerID (`2026-early-1st-1527`). - The histories POST (`/dynasty-rankings/histories`) returns identical-shape items for picks: `{ playerID, oneQB: { valueHistory, tepHistory, teppHistory, tepppHistory }, superflex: { valueHistory, ... } }`. Both axes populated with 181 daily compressed `YYMMDD` entries (~6 months) on the cheap path. - The per-slug page (used by `--full` mode) exposes 982 daily entries (~2.7 years) on both axes. `positionalRankHistory` is undefined for picks (RDP has no positional-rank concept); `overallRankHistory` is populated. ### Plan B consumer Plan B's pick asset identity is `(pick_year, pick_round, pick_original_owner_tid)`. KTC publishes picks league-agnostically and has no `pick_original_owner_tid`. The two namespaces cannot be unified at ingest time; Plan B's `extract-ktc-per-asset.mjs` will need a separate pick branch that joins `keeptradecut_rankings` to a KTC pick lookup table on `pid` and resolves to `(year, round, slot)`, then applies Plan B's own draft-order projection to bind to `original_owner_tid`. ### Target table `keeptradecut_rankings (pid varchar(25), qb smallint, d integer, v integer, type smallint)` with unique key `(pid, d, qb, type)`. `pid` is not FK-bound; synthetic ids are safe as long as they fit varchar(25) and do not collide with the player pid regex `FNAM-LNAM-YYYY-YYYY-MM-DD`. ## Design ### Identity scheme Synthetic pid `KTCPICK-` (e.g., `KTCPICK-1527`, ~12 chars). KTC's integer playerID is the canonical primary key on their side and is stable. The structured `(year, round, slot)` metadata lives in a sibling lookup table rather than being baked into the pid string, so a future KTC vocabulary change (5th-round picks, slot rename) does not invalidate stored pids. ### New table: `keeptradecut_pick` ```sql CREATE TABLE public.keeptradecut_pick ( pid varchar(25) PRIMARY KEY, ktc_player_id integer NOT NULL UNIQUE, ktc_player_name text NOT NULL, year smallint NOT NULL, round smallint NOT NULL, slot smallint NOT NULL, -- 1=Early, 2=Mid, 3=Late created_at integer NOT NULL, updated_at integer NOT NULL, CONSTRAINT keeptradecut_pick_slot_chk CHECK (slot BETWEEN 1 AND 3), CONSTRAINT keeptradecut_pick_round_chk CHECK (round BETWEEN 1 AND 4) ); CREATE INDEX idx_keeptradecut_pick_yrs ON public.keeptradecut_pick (year, round, slot); GRANT SELECT ON TABLE public.keeptradecut_pick TO league_readonly; ``` ### Importer changes (`scripts/import-keeptradecut.mjs`) Module-level additions: ```js const KTC_PICK_SLOT = { Early: 1, Mid: 2, Late: 3 } const KTC_PICK_ROUND = { '1st': 1, '2nd': 2, '3rd': 3, '4th': 4 } const KTC_PICK_NAME_RE = /^(\d{4}) (Early|Mid|Late) (1st|2nd|3rd|4th)$/ const parse_ktc_pick_name = (name) => { const m = KTC_PICK_NAME_RE.exec(name) if (!m) return null return { year: Number(m[1]), slot: KTC_PICK_SLOT[m[2]], round: KTC_PICK_ROUND[m[3]] } } ``` Replace the line-81 skip with a branch that sets `pid` either from `player_row.pid` (existing path, moved into an `else`) or from `KTCPICK-` after upserting `keeptradecut_pick`. The downstream `inserts.push({ pid, ... })` loop is unchanged. ```js let pid if (keeptradecut_player.position === 'RDP') { const meta = parse_ktc_pick_name(keeptradecut_player.playerName) if (!meta) { log(`unparseable RDP playerName: ${keeptradecut_player.playerName} (id=${item.playerID})`) continue } pid = `KTCPICK-${item.playerID}` const now = dayjs().unix() if (!dry) { await db('keeptradecut_pick') .insert({ pid, ktc_player_id: item.playerID, ktc_player_name: keeptradecut_player.playerName, year: meta.year, round: meta.round, slot: meta.slot, created_at: now, updated_at: now }) .onConflict('pid') .merge(['ktc_player_name', 'year', 'round', 'slot', 'updated_at']) } } else { // existing find_player_row / updatePlayer block pid = player_row.pid } ``` Defensive change in the `--full` block: split the existing six player `.forEach` calls into two parallel sequences -- one for the player path (unchanged, `.forEach` retained so any future KTC regression surfaces as an exception), and one for the pick path that uses `?.forEach` on `playerOneQB.overallValue`, `playerOneQB.overallRankHistory`, `playerSuperflex.overallValue`, `playerSuperflex.overallRankHistory` only. `positionalRankHistory` is omitted from the pick branch entirely (KTC does not publish it for RDP). Scope of the change is confined to the RDP code path; the player path retains strict access. In v1 the picks-only metric is `type = VALUE`. `OVERALL_RANK` is populated in `--full` mode but unused by Plan B; ingest it for parity with players. `POSITION_RANK` is undefined for picks and is naturally skipped by the optional-chaining guard. ### Initial backfill Once code and schema land, run `NODE_ENV=production node scripts/import-keeptradecut.mjs --full` once. This loads 982 days per pick per axis (36 picks x 2 axes x 982 days approx 71k VALUE rows, plus OVERALL_RANK rows in full mode). Walltime adds approx 36 x 4 s = 2.4 min to the existing full-mode walltime. Idempotent via the existing `.onConflict(['pid','d','qb','type']).ignore()`. Daily cron continues to use the cheap path, which appends new days automatically. ### Knowledge Base Impact - Update `text/league/player-valuation-system.md`: add a subsection under the KTC section documenting RDP ingest, the synthetic pid scheme `KTCPICK-`, the `keeptradecut_pick` lookup-table contract (columns and slot enum), `type = VALUE` plus `OVERALL_RANK`-in-full-mode-only stance, and `--full` as the initial-backfill mechanism (982 days vs 181). - Update `███████████████████████████████████████████████`: clarify in prose that when this task ships, `extract-ktc-per-asset.mjs` still needs a small pick branch (join `keeptradecut_rankings` to `keeptradecut_pick` on `pid`) - the "no change to extractor" line in Plan B was aspirational. - No new domain entry doc, no new tag, no ABOUT.md change, no new guideline. ### Working tier No scratch slug. All artifacts (one DDL adhoc, one script edit, one schema export, two KB-doc edits) fit in the task body. ### Notes - **OVERALL_RANK over-ingest:** picks get `type = 3` rows in `--full` mode for parity with the player path. Plan B does not read them today; storage cost is ~36 x 982 x 2 axes approx 71k rows, negligible. Deliberate; not a candidate for trimming. - **Rollback:** the changes are additive. To revert: `DROP TABLE keeptradecut_pick;` and `DELETE FROM keeptradecut_rankings WHERE pid LIKE 'KTCPICK-%';` then revert the importer commit. No data loss to the player path. - **Liquidity-tracking coordination:** `task/league/add-keeptradecut-liquidity-tracking.md` modifies the same script and reads `item.oneQBValues` / `item.superflexValues` -- both of which are populated for RDP rows in `playersArray`. Once this task ships, the liquidity task must either explicitly skip RDP rows or extend its `keeptradecut_liquidity` schema to accept the synthetic `KTCPICK-` pids. The `blocks` relation on this task captures the ordering dependency. ## Tasks 1. - [x] **Schema.** Create `db/adhoc/2026-05-19-keeptradecut-pick.sql` with the DDL above. Run `yarn db:exec db/adhoc/2026-05-19-keeptradecut-pick.sql`. Run `yarn export:schema`. Commit both the adhoc file and the updated `db/schema.postgres.sql`. 2. - [x] **Importer parser.** In `scripts/import-keeptradecut.mjs`, add `KTC_PICK_SLOT`, `KTC_PICK_ROUND`, `KTC_PICK_NAME_RE`, `parse_ktc_pick_name` at module level (inline; single caller). 3. - [x] **Importer RDP branch.** Replace lines 81-84 with `let pid` followed by the `if (position === 'RDP') { ... } else { ... }` branch above. Move `const inserts = []` (currently line 113) to before the `if`. Move the existing `find_player_row` / `updatePlayer` block into the `else` branch -- **including the `try/catch` and the `if (!player_row) { log(...); continue }` not-found guard** -- and set `pid = player_row.pid` as the final statement of the `else`. Delete the original `const { pid } = player_row` at line 114. Both the `catch (err) { ...; continue }` and the not-found `continue` must remain nested inside the `else` so a failed player lookup short-circuits before `pid` is read, never leaving `pid` undefined on the player path. 4. - [x] **Importer full-mode pick branch.** In the `--full` block, add a `position === 'RDP'` branch that pushes inserts only for `playerOneQB.overallValue`, `playerOneQB.overallRankHistory`, `playerSuperflex.overallValue`, `playerSuperflex.overallRankHistory` using `?.forEach` (omit `positionalRankHistory`; KTC does not publish it for picks). Leave the existing six player `.forEach` calls strictly untouched -- they retain hard access so a future KTC field removal surfaces as an exception rather than silently zero rows. 5. - [x] **Dry-run validation.** Run `NODE_ENV=production node scripts/import-keeptradecut.mjs --dry`. Confirm log emits 36 RDP entries resolved to `KTCPICK-` pids with no exceptions, and that at least one RDP entry reports `inserts.length > 0` (defends against `oneQB`/`superflex` being null on the histories endpoint for picks). The `keeptradecut_pick` upsert is guarded by `if (!dry)` and is intentionally skipped during dry-run -- only parser output and value-extraction are exercised; the lookup-table write path is validated in T6 instead. 6. - [x] **Initial backfill.** Run `NODE_ENV=production node scripts/import-keeptradecut.mjs --full` once. Then assert: - `SELECT count(*) FROM keeptradecut_pick` returns 36. - `SELECT count(*) FROM keeptradecut_rankings WHERE pid = 'KTCPICK-1527' AND type = 1 AND qb = 1` returns >= 982. - Same query with `qb = 2` returns >= 982. - `SELECT count(*) FROM keeptradecut_rankings WHERE pid = 'KTCPICK-1527' AND type = 3` returns > 0 (OVERALL_RANK ingested in full mode; this catches a silent `?.forEach` swallow). - `SELECT max(d) FROM keeptradecut_rankings WHERE pid = 'KTCPICK-1527'` is within 24 h of now. 7. - [x] **KB doc: valuation system.** Update `text/league/player-valuation-system.md` per the Knowledge Base Impact bullet. 8. - [x] **KB doc: Plan B.** Update `███████████████████████████████████████████████` to correct the "no extractor change" line and call out the required pick branch in `extract-ktc-per-asset.mjs`. 9. - [x] **Close.** Set this task's `status: Completed`, add `[completion] ` observation, refresh `updated_at`. ## Validation fixture `KTCPICK-1527` (KTC's `2026 Early 1st`) is the canonical fixture. Acceptance after T6: - `keeptradecut_pick` row: `(KTCPICK-1527, 1527, '2026 Early 1st', 2026, 1, 1, ...)`. - `keeptradecut_rankings`: >= 982 rows for `(pid='KTCPICK-1527', type=1, qb=1)` and same for `qb=2`, with the latest `d` within 24 h. ## Relation to composite market value (Plan B) Plan B's `extract-ktc-per-asset.mjs` will need a new pick branch joining `keeptradecut_rankings` to `keeptradecut_pick` on `pid` to surface `(year, round, slot, qb, d, v)` for the composite blender. Until that branch lands in Plan B, this task's output is unconsumed but harmless. The KTC importer changes are independently valuable as a complete KTC market-data feed for picks.