--- title: Fix the dev-fixture leak scan splitting multi-line values into prose needles type: task status: completed description: >- The fixture leak scan splits psql output on newlines, so a 6,919-character article body becomes 132 prose needles and two of them match ordinary data; both reported leaks are false positives and no column needs a disposition. base_uri: user:task/league/fix-dev-fixture-leak-scan-newline-needles.md created_at: '2026-09-26T05:31:27.526Z' entity_id: 4422cc78-fc07-4ba5-ba10-202721d79908 finished_at: '2026-09-26T15:16:49.961Z' observations: - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ owner_identity_uri: █████████████████████████ priority: High public_read: true started_at: '2026-09-26T14:35:31.096Z' tags: - user:tag/league-xo-football.md - user:tag/software-task.md updated_at: '2026-09-26T15:16:49.961Z' visibility_analyzed_at: '2026-11-05T23:49:21.683Z' --- ## Problem `cron:league:league-derive-dev-fixture` has failed nightly since 2026-09-25, writing no dev fixture. The signal reads as a missing-disposition failure and is not one. The leak scan in `assert_no_scrubbed_value_survives` reports `2 scrubbed value(s) still appear in the fixture, duplicated outside their own column`, then lists 23 scrubbed columns and says "Find the sink and give it a disposition." Both leaks are FALSE POSITIVES and no column needs a disposition. ## Root cause `scripts/derive-dev-fixture.mjs:262` splits the psql needle output on newlines: ```js for (const value of stdout.split('\n')) { const trimmed = value.trim() if (trimmed) needles.add(trimmed) } ``` `league_newspaper_articles.body_markdown` holds 6 values, every one of them multi-line, 1365 to 6919 characters. `psql -At` emits those across many output lines, so one value becomes 132 separate needles, each an ordinary line of prose. The `length >= 12` guard cannot stop this: it is applied in SQL to the whole value, never to the fragments. Two of those 132 prose fragments legitimately appear elsewhere in ordinary data, and the scan reports them as leaked secrets: - one matches `league_newspaper_articles.byline` - one matches `teams.name` Nothing sensitive is exposed. `body_markdown` is replaced wholesale with `'scrubbed newspaper article body'`, so no part of an article body reaches the fixture at all. ## What was ruled out Measured read-only against production, values never leaving the database: - All 12 columns named in the signal already carry dispositions in `scripts/dev-fixture-projection.json`. The missing-disposition path is a different error string at `derive-dev-fixture.mjs:353-356` and did not fire. - All 74 `keep` columns across the 47 emitted tables: no needle present, exact or substring. Validated against a control that returns 135 hits, so the zero is sighted. - `placed_wagers.selections` jsonb: clean across 3,773 distinct long string scalars, with a self-match control returning 3,773. - `waivers.reason`, the one partial redaction whose own output can still carry a needle: clean. - Schema DDL, column defaults, constraints, index definitions, enum labels and comments: clean. ## The decision, ruled 2026-09-26 Read the needles through the same `\copy TO STDOUT` that writes the data blocks. COPY TEXT escapes a newline to a literal `\n`, so every value arrives as exactly one line already in the byte form the fixture holds — comparable to the artifact by construction, and greppable, since `grep -F -f` patterns are line-delimited and a needle carrying a raw newline cannot be expressed as one pattern at all. Two alternatives were considered and rejected: - **A hand-written COPY escaper in JS.** Reimplements Postgres escaping next to a serializer already in the file, and COPY TEXT escapes `\b`, `\f` and `\v` too, so it is a correctness risk for no gain. - **Base64 transport with a JS decode.** `encode(..., 'base64')` wraps at 76 characters in Postgres — measured, first newline at position 77 — so it reintroduces the same problem unless stripped. What this gives up: a sink holding only PART of a multi-line value is no longer caught. The only multi-line scrubbed column is `league_newspaper_articles.body_markdown`, which is newspaper prose replaced wholesale. Every actual secret here is single-line and loses nothing. Also rejected: a disposition KIND separating "secret" from "bulk-replaced" so prose could skip the scan. It is a fail-open lever with a threshold nobody can calibrate, and whole-value needles make it buy nothing. ## Plan Done in league `e3585a645`: - Needles read through `\copy TO STDOUT`, with the length floor moved onto the serialized needle where it can bound what grep matches. - The scan NAMES THE SINK. It tracks needle to owning column, locates each match against the enclosing `COPY public.` header and tab position, and reports `owner -> sink` as `table.column` pairs, never values. The 23-column dump that overran the signal payload is gone and the pair list is capped at eight. - Needles piped to `grep -F -f -` on stdin rather than a `0600` file, so the plaintext secret set never reaches the filesystem and cannot survive a SIGKILL. Canary moved last in the stream, so sighting it also proves the stream arrived whole. - Proven as a differing pair against production, not a single reading: the real `users` projection reads CLEAN, while scrubbing `users.username` and keeping `users.email` — one real email contains its username — reads RED and names `users.username -> users.email`. Full run writes 47 tables with the scan clean at 1212 needles, and `check-dev-fixture-scrub` passes with its five negative controls red. Deployed and closed out 2026-09-26: - Deployed via `yarn deploy:all` at `460967f27`. The range was eight commits, cleared by their owners first — ███████████ cleared its five catalog commits with a full green suite, repl-243fcb9c cleared `c6c692e46`, and `9a43e75ce` was judged on merits as an eight-line additive entry whose session had ended. Both hosts read `460967f27` on branch `master`, and the served HTML's bundle exists on disk. - The DEPLOYED script was then run from the host checkout: leak scan clean, 47 tables, exit 0. Source carrying the fix and the artifact lacking the defect are different claims, so both were checked. - Signal 132097 auto-resolved on the runs oracle — `[Fix] auto-resolved by runs primitive: success report` — after the job ran green through `job-wrapper.sh`, replacing a `latest-dev.tar.gz` that had been stale since 09-24. Never hand-closed. Escalation meta 132234 self-closes on the next age sweep. The reusable trap was promoted out to [[user:guideline/silent-search-failures.md]]: a newline-delimited pattern list cannot hold a pattern containing a newline, and it shreds rather than erroring — the rare false-POSITIVE direction in that document. ## Do not Do not weaken the fail-closed refusal to make the fixture generate. The refusal is correct behaviour; the needle construction feeding it is the defect. ## Key locations - Leak scan: `scripts/derive-dev-fixture.mjs:223-326`, needle split at `:262` - Dispositions: `scripts/dev-fixture-projection.json` - Emitted table list: `scripts/postgres-backup.sh:53-101` - Cron: `server/crontab-main/league-backups.cron:81`, 00:23 daily - Log: `/var/log/league/league-postgres-backup.log` on host `league`