Backfill chestshop_sale.txn_id for existing sales (~787K NULL rows per network)

Sibling of PAR-234 (forward fix). Once new sales populate txn_id, backfill the historical rows so the whole sales back‑catalogue links to the ledger — it's genuinely useful data (sale↔money drill‑through, reconciliation, analytics).

Scope

Pair each NULL‑txn_id chestshop_sale row to its ledger transaction and set txn_id. As of now: ~787,092 rows in democracycraft (all NULL); statecraft similar.

Pairing approach

For each sale, match a ChestShop ledger txn (plugin_system='ChestShop') by:

• shop side: shop_account_id (or shop_owner_uuid_bin) appears as a posting account;
• counterparty: customer_uuid_bin;
• amount: total_price == ABS(ledger_postings.amount);
• time: occurred_at ≈ settlement_time within a tight window (±a few seconds).

Under the legacy two‑hop, a sale maps to a txn pair (buyer→System, System→seller) — pick and document a canonical leg (e.g. the seller‑credit txn) to store. This is the same pairing PAR‑159 needs for the explorer two‑hop collapse, so share the logic.

Caveats

• Fuzzy: ambiguous when the same shop sells the same item at the same price within the window — recover unambiguous matches, log/skip the rest, and report a match‑rate. Don't guess a 1:1 where it isn't certain.
• Idempotent: only fills WHERE txn_id IS NULL; safe to re‑run.
• Bounded/batched: pairing ~787K sales against ~1.57M ChestShop txns — chunk by time window; run off‑peak.

Ops

One‑off data migration per environment (democracycraft, statecraft), run against prod via a controlled/manual path (like the Flyway/admin one‑offs), not auto‑applied.

Acceptance

• A high share of pre‑fix sales get a correct txn_id (target/report the %); unmatched rows logged with a reason and left NULL.
• Spot‑check: linked total_price equals the matched posting amount; re‑running changes nothing.

Depends on / pairs with PAR-234; shares pairing logic with PAR-159.

Implementation evidence — 2026-10-22

• Work started in isolated branch/worktree par-235-sale-ledger-backfill, based on current origin/develop (4339da00); no matching PAR-235 implementation/PR found. Status set to In progress.
• Added manual Python/PyMySQL tooling under ops/sale-ledger-backfill/: explicit UTC range, bounded time batches and row/candidate caps, read-only dry-run by default, NULL-only apply, advisory serialization, per-batch transactions/resumption, private JSONL audit evidence and match-rate summary. No Flyway/startup automation.
• Matching requires exact ChestShop plugin, two balanced postings, price, identity, direction and tight time. Legacy two-hop requires supported normalized messages, ordered legs within 1s and unique partners. Canonical persisted link is seller-credit (customer-credit for SELL). Competing sales across batch/range halos and existing claims across all time block reuse. Ambiguous/unmatched rows remain NULL. PAR-159 analytics remains separately owned; assumptions relayed through parent.
• Verified PAR-234 current forward plumbing in EconomyServiceImpl → Transaction → MarketSyncServiceImpl → MarketServiceImpl/Treasury sale mapper. Existing targeted tests passed: 65 EconomyServiceImplTest + 43 MarketSyncServiceImplTest.
• Disposable MariaDB verification: 17 checks pass, including exact money/identity/time, BUY/SELL legacy canonical leg, ambiguity, global/linked claims, cross-batch competition, admin/free/unsupported traffic, SELECT-only dry-run, CLI report privacy/no overwrite, reruns, advisory locks, row caps, unchanged ledger/balances, and injected second-batch failure/rollback followed by resumption. Command: PAR235_TEST_SOCKET="$PWD/.context/p235.sock" PYTHONPATH="$PWD/.context/par235/deps" python3 -m unittest discover -s ops/sale-ledger-backfill -v. Test DB names uniquely generated and task-local socket/server only.
• Mutation sensitivity demonstrated: removing competing-sale protection causes the matching test to fail with two erroneous match outcomes.
• bash gradlew :chestshop:test --tests '*EconomyServiceImplTest' --tests '*MarketSyncServiceImplTest' --no-daemon passed. bash gradlew build -PskipIT --no-daemon stopped at existing Treasury JaCoCo coverage gate: 0.80 lines vs 0.93 required; 0.71 branches vs 0.83 required. No runtime upgrade or unrelated coverage changes made.
• Pending: representative authorized clone match-rate/plan/spot-check evidence, and separate environment-specific production execution authorization. No production/shared staging data/config accessed or changed; task is not Done.

Draft PR — 2026-10-22

• Draft PR: https://github.com/ParadauxIO/hibernia-economy/pull/84 (base develop, head par-235-sale-ledger-backfill, commit 3a9fa575fe4b7e2b12f2fee108c11e56d32521d9). Read back and confirmed draft status/base/head/revision and uploaded diagram URL.
• Final verification: all 17 disposable tests pass; bash gradlew spotlessCheck --no-daemon passed. The 108 focused PAR-234 tests passed as recorded above. Root skipIT build coverage failure remains disclosed in the PR.
• Confirmed no task-owned test DBs remain and shut down only the disposable PAR-235 server after verifying its exact datadir. Working tree is clean. No runtime/shared staging/production changes.
• Implementation and reviewable draft are delivered. Task remains In progress pending representative authorized clone match-rate/query-plan/spot-check evidence and separate production execution authorization; do not treat PR creation as data-backfill completion.

Merge evidence — 2026-10-22

User authorized merge if ready. Verified current-head CI green (full JVM build, Treasury coverage 95.58%, migrations, Explorer, CodeQL and ci-gate), no review findings/conflicts. PR #84 merged into develop at 2026-10-22T09:26:51Z; merge commit d8a6f2ae13bf660a92f34baf0e9125de5ce8f957. Earlier local skipIT coverage limitation is superseded for build readiness by passing full CI. Tooling merge does not execute a backfill. No production/shared staging changes made. Keep In progress: representative authorized clone query-plan/match-rate/spot-check validation and separate production execution authorization remain outstanding.