---
title: Store placed wager legs in their own table
type: task
status: completed
description: >-
  Replace placed_wagers.selections JSON with a placed_wager_legs table of parsed leg columns written
  transactionally by the importers, move every reader onto it, then drop the column.
base_uri: user:task/league/store-placed-wager-legs.md
created_at: '2026-09-28T01:40:14.926Z'
entity_id: 03118a7f-f19d-4d83-b1a7-941bfd67ac23
finished_at: '2026-09-28T16:14:32.860Z'
observations:
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
relations:
  - follows [[██████████████████████-█████████-██████-███-█████-██████-████████]]
  - relates [[████████████████████████-█████-████-█████-█████████]]
started_at: '2026-09-28T03:01:53.664Z'
tags:
  - user:tag/league-xo-football.md
updated_at: '2026-09-30T04:52:07.270Z'
visibility_analyzed_at: '2026-11-06T00:18:06.913Z'
---

# Implementation Plan: Store placed wager legs in their own table

## Overview

- Replace the `placed_wagers.selections` JSON column with a `placed_wager_legs` table: one row per leg, with the packed standard selection id parsed into columns once, at import.
- Impact:
  - Wager queries stop parsing packed leg keys with `split_part` or regex, which is the query-trap guideline's main hazard.
  - The wagers view moves its leg reads from JSON to indexed columns.
- Ordering:
  - This follows [[██████████████████████-██████-███████]], whose engine gains a leg-table load path here.
  - It follows [[██████████████████████-█████████-██████-███-█████-██████-████████]], whose helper supplies round-robin line pricing.
  - The ███████████████████████████████ continuation repairs 12 Fanatics leg prices inside `selections`. That repair must land before this task's backfill, or the backfill must re-run after it.

## Background

- **Legs live in `placed_wagers.selections`**, a JSON array of `{id, odds, status}` objects, sometimes with `corrupt_id`.
  - `id` is built by `libs-shared/format-standard-selection-id.mjs` and parsed by `libs-shared/parse-standard-selection-id.mjs`.
  - The parser returns `esbid`, `market_type`, `pid`, `team`, `selection_type` and `line` (lines 39-73).
- **Measured 2026-09-27:**
  - 455,516 legs across 64,552 wagers, from `select count(*), count(distinct wager_id) from placed_wagers w, jsonb_array_elements(w.selections) l`.
  - 440,826 legs start with `ESBID:`, and none of those lacks `MARKET:`. The rest are 14,690 `UNKNOWN|` legs.
  - Three DraftKings round robins have zero legs: wagers 289991, 289992 and 289993 (`jsonb_array_length(selections) = 0`).
- **Readers of `selections`**, per plan review:
  - `api/routes/wagers.mjs`, including the history route's whole-row payload at `:346`, asserted by `test/wagers.spec.mjs:137` and seeded at `:238-255`.
  - `libs-server/wager-leg-progress.mjs` (`grade_wager_legs`; sole caller `api/routes/wagers.mjs:495`).
  - `libs-server/wager-market-type-pnl.mjs` and `scripts/analyze-wager-market-type-pnl.mjs` (which has a `--json` flag).
  - `libs-server/detect-profit-only-returns.mjs` and `scripts/repair-profit-only-wager-returns.mjs`.
  - The three importers.
  - `scripts/dev-fixture-projection.json:250`.
  - Swagger at `api/routes/wagers.mjs:247,273`, `api/swagger/config.mjs:1802` and `api/swagger/response-validation-holdout.json:144`, and `docs/guides/api.md:42`.
  - The wagers view load path from █████████████████.
  - Checked clean: no database views or functions, nothing in `private/`, no test reads a source file as text, and `app/` has no reader.
- **Infrastructure lists** a new user-data table must join:
  - `scripts/dev-fixture-projection.json`
  - the scrub list in `db/gates/check-dev-fixture-scrub.mjs:51-55`
  - the user-data dump in `scripts/postgres-backup.sh:53-90`, without which a restore brings back wagers without legs
  - the exclusions in `db/tools/generate-reader-role-grants.mjs:113,162`
- **The importers' upserts run outside any transaction**, and FanDuel batches 1,000 rows.
- **Round robins:** 3 FanDuel `ACCn` round robins, where the product of legs per game equals `bet_count`; and 45 DraftKings round robins from 2024. For those, one-leg-per-game never matches, and k-leg combinations are ambiguous: k=1 and k=n−1 both match on 41 of 42 checked.
- **Lane ownership:**
  - The importers are the importer lane's: [[████████████████████████-██-████████████████-█████-███████-███-████-███████]].
  - `libs-server/wager-leg-progress.mjs` is being extended by thread 9910dbff (threshold and game clock on the leg payload).

### Assumptions

- The parser handles every `ESBID:` leg. Partly checked: no such leg lacks `MARKET:`. The backfill counts failures by key and must report zero.
- A DraftKings round robin's lines cannot be rebuilt from stored data. Checked by plan review, as above. Their line fields stay null.
- Admitting one import cycle between the reader deploy and the drop is enough to prove the readers work. Unchecked; the drop step verifies it.

## Design

**Table `placed_wager_legs`**, keyed `(wager_id, leg_index)`, with `wager_id` referencing `placed_wagers` on delete cascade.

The parser field map:

| Parser field | Column |
| --- | --- |
| `id` | `standard_selection_id` |
| `esbid` | `esbid` |
| `market_type` | `market_type` |
| `pid` | `selection_pid` |
| `team` | `selection_team` |
| `selection_type` | `selection_side` |
| `line` | `selection_metric_line` |

- `selection_pid` and `selection_side` match `prop_market_selections`. `selection_team` and `selection_metric_line` extend the same `selection_` prefix.
- Odds are stored as `odds_american`.
- `book_status` reuses the name the grader already uses (`libs-server/wager-leg-progress.mjs:45,436`).
- `corrupt_standard_selection_id` preserves `corrupt_id`.
- Line fields:
  - `containing_line_count` and `containing_line_potential_return_amount`, both computed by █████████████████'s line module.
  - They are null for DraftKings round robins, and whenever a rule does not reproduce `bet_count` exactly.
- Indexes: `(selection_pid, market_type)`, `esbid`, `standard_selection_id`.

**`placed_wagers.wager_nfl_week_id`** is persisted at import. The source is the legs' `nfl_games.nfl_week_id`, else the `nfl_week_id` of the first `nfl_games.kickoff_at` at or after `placed_at`. It replaces █████████████████'s query-time derivation.

**Importers**
- Each importer wraps its upsert in `db.transaction`.
- The upsert takes `.returning(['wager_id','book_wager_id'])`, then deletes and reinserts the legs for those ids.
- Legs are built by one shared module, `libs-server/placed-wager-legs/build-placed-wager-legs.mjs`.

**Readers**
- The grader and the `/wagers` API move to leg rows together, in one commit.
- The history route serves `legs[]` instead of `selections`. Its spec, swagger, holdout and docs change in the same commit.
- The wagers view reads `placed_wager_legs`.

**The drop** runs only after the reader deploy is live and one scheduled import cycle has passed, and only after the population checks.

Knowledge Base Impact:
- `█████████████████████████████████████████████████` replaces the packed-id trap with `placed_wager_legs` guidance.
- `docs/guides/data.md`'s wagers view section names the leg table.

Scratch: none.

## Notes

- Sequence the grader edit after thread 9910dbff's leg-payload change lands. Check with `base thread list --running` and that thread's commits.
- Commit risky steps alone: the importer transactions, and the drop.

## Plan

- [x] **Agree who builds the importer and backfill steps with the importer lane**
  - Verify: an observation on this task names the builder.
- [x] **Create the leg table and the week column**
  - File: a private ad hoc DDL file, headed `-- STATUS: PENDING` per the private ad hoc README. Apply with `yarn db:exec`, then run `yarn export:schema`.
  - Verify: `\d placed_wager_legs` lists every Design column; the schema diff shows only the new table, the column and their indexes.
- [x] **Add the new table to the user-data infrastructure lists**
  - Files: `scripts/dev-fixture-projection.json`, `db/gates/check-dev-fixture-scrub.mjs`, `scripts/postgres-backup.sh`, `db/tools/generate-reader-role-grants.mjs`
  - Verify: `rg -n placed_wager_legs` hits all four; `node db/gates/check-dev-fixture-scrub.mjs` passes; a `--dry-run` or listing of the backup's user tables includes it.
- [x] **Build leg rows in one shared module**
  - File: `libs-server/placed-wager-legs/build-placed-wager-legs.mjs`, using the parser and █████████████████'s line module.
  - Verify: new spec `test/libs-server.placed-wager-legs.spec.mjs` covers a single, a parlay, the 144-line FanDuel ACC round robin (the sum of `containing_line_count` equals 144 × 9), a DraftKings round robin (null line fields), an `UNKNOWN|` leg, a `TEAM:` leg and a `corrupt_id` leg.
- [x] **Resolve the persisted week**
  - File: `libs-server/placed-wager-legs/resolve-wager-nfl-week-id.mjs`
  - Verify: spec cases for a leg-resolved wager, an all-`UNKNOWN` wager and a Thursday-placed wager. █████████████████'s query-time derivation agrees on every 2026 wager, via a comparison query recorded in the verify.
- [x] **Write legs and weeks from the three importers, in transactions**
  - Files: `scripts/import-draftkings-wagers.mjs`, `scripts/import-fanduel-wagers.mjs`, `scripts/import-fanatics-wagers.mjs`
  - Verify: the three importer specs assert leg rows and the week, and assert that a re-import replaces legs rather than duplicating them.
- [x] **Backfill legs and weeks for every existing wager**
  - File: `scripts/backfill-placed-wager-legs.mjs`: idempotent, prints parse failures by key.
  - Verify:
    - Total leg rows equal `select sum(jsonb_array_length(selections)) from placed_wagers` at run time.
    - Exactly three wagers have no legs: 289991, 289992 and 289993.
    - Zero wagers lack `wager_nfl_week_id`.
    - Zero parse failures on `ESBID:` legs.
- [x] **Validate leg storage across the whole population, before any reader moves**
  - For every wager, the leg row count equals `jsonb_array_length(selections)`.
  - For every single and parlay, the summed `containing_line_count` equals the leg count.
  - For every FanDuel round robin, the product of legs per game equals `bet_count`, and exactly one rule matched.
  - Failure caught: a builder that silently drops legs, or a line rule applied to the wrong book.
- [x] **Move the grader and the `/wagers` API to leg rows in one commit** (league 33bed4c65)
  - The grader reads `placed_wager_legs` itself through `libs-server/placed-wager-legs/load-placed-wager-legs.mjs`, so the wagers view's call is unchanged. The history route serves `legs[]`, the active route's game test reads `placed_wager_legs.esbid`, and neither serves `selections`.
  - Verified: old and new grader output identical on the latest 3,000 production wagers (21,378 legs); the active selection returns the same wagers at 36 hours and 30 days; four specs pass under the response validator, and the hold-out entry for the history route's `selections` drift is retired. Rendering checked by that parity rather than by a `yarn dev:live` screenshot, since the page reads only the graded legs.
- [x] **Move the wagers view load path to leg rows** (league 987036e81)
  - `load_wager_set` filters on `wager_nfl_week_id`. The view reads legs from `placed_wager_legs` as `{ id, odds, status }` into `build_wager_lines`, which moved to `libs-server/placed-wager-legs/` and now throws on a wager with no selections array.
  - Verified: `run-wagers-view.mjs --format json` output is byte-identical before and after for 2026 weeks 2 and 3 and 2025 week 11.
  - Deployed 2026-09-28 at league be1ef4a93 on both hosts (bundle `main.4baf3bbf.js`); production leaks answer the new three-row list and anonymous callers still get 404.
  - The drop below may start after the first import after that deploy in which every book succeeds, expected at the daily 10:15 ET window (14:15Z). Read `runtime/league/book-wager-import-windows.json` for a covered window after 05:35Z before starting.
- [x] **Move profit-only detection to leg rows** (league a44d07723). Verdicts byte-identical on all 101 production WON wagers. Market-type P&L needed no move: the record engine's leg grain replaced it.
- [x] **Drop `placed_wagers.selections`** (code league cf96fbee3, drop league 27292dcea, applied 2026-09-28)
  - The importers build legs from the book payload in memory, joined to the merged row on `book_wager_id`; the one-off backfill script is removed, since it read the column.
  - Verified before the drop: the 14:18Z import covered every book, and the leg table reproduced all 455,516 JSON legs on every key (`id`, `odds`, `status`, `corrupt_id`) with zero differences. No deployed API or worker file names the column, and the only writer runs on the Mac from the shared checkout.
  - Verified after: the full suite passes with the column absent (13,937 passing); `check-knex-column-resolution` and `check-dev-fixture-scrub` pass; the column count is 0.
  - Deployed 2026-09-28 at league bc0d8f1b2 on both hosts, ahead of the nightly `derive-dev-fixture` run (00:23Z), which fails closed on a projected column the database no longer has. Both deployed projections carry no `selections` entry.
- [x] **Update the █████████████ █████ █████ and the data guide** (user-base 457a4a4493)
  - The trap sheet is rewritten for the leg table and surfaces on `placed_wager_legs` queries too. The data guide already named the leg table; the api guide's stale `selections` clause is retired (league 55b61c1a6).
  - Files: `█████████████████████████████████████████████████`, league `docs/guides/data.md`
  - Verify: `rg -c split_part █████████████████████████████████████████████████` returns 0, and `rg -c placed_wager_legs` on both files returns at least 1.