Skip to main content

PowerFill A54 Fix — Greg-Demo Readiness

Date: 2026-04-19 Agent: PSSaaS Systems Architect (Opus 4.7 High Thinking) Scope: Mid-stream targeted fix between Phase 8 W1 (complete) and Phase 8 W2 / Phase 9 (queued). Closes the long-deferred A54 PRIMARY KEY violation in psp_powerfill_pool_guide so PSSaaS can demonstrate end-to-end Complete PowerFill runs against PS_DemoData. Two surgical fixes inside the legacy proc body, both within ADR-021's amended new §Narrow Bug-Fix Carve-Out. Sentinel bumped to phase-8-superset-ready-a54-fixed.

Why​

A54 has been deferred since Phase 6c (2026-04-19 morning). Every PSSaaS PowerFill run against PS_DemoData since 6c terminated as Failed at Step 5 (pool_guide) due to a latent legacy proc bug (SqlException 2627: Violation of PRIMARY KEY constraint 'PK__##cte_po__...'). 6 of the 8 Phase 8 W1 Superset dashboards were therefore empty.

The PO has decided that Greg himself is the next consumer of PSSaaS PowerFill output, in a Greg-demo context. Greg is the canonical domain authority who would normally consult on this kind of legacy-proc bug. Per ADR-021 §Narrow Bug-Fix Carve-Out clause (d) (added in this commit), the demo IS the consultation. The empirical evidence + this fix itself become the consultation material Greg reviews live.

Three forcing functions:

  1. Greg needs to see PowerFill produce real allocation output end-to-end, not empty dashboards
  2. The "Bug as Feature" demo narrative builds credibility with Greg (a domain expert who values rigor)
  3. A54 closure is the prerequisite for downstream W2 (React UI) + Phase 9 (Parallel Validation) — both compound in value when there's real allocation output to surface / validate

What Was Done​

Code (1 file edit + 1 sentinel bump)​

  • src/backend/PowerSeller.SaaS.Modules.PowerFill/Sql/009_CreatePoolGuideProcedure.sql (+30 LOC + 16 comment LOC) — Two surgical fixes inside psp_powerfill_pool_guide proc body:
    • ##cte_posting_set_1300 PK extension — added pa_key NUMERIC(1, 0) NOT NULL column + extended PK from (trade_id, loan_id) to (trade_id, loan_id, pa_key); updated INSERT column list; mirrored _1400's author-intended pa_key derivation in the SELECT (identical CASE expression on pool_action's first letter).
    • pt13 INNER JOIN qualifier extension — added AND ps1200_13.settlement_date = pt13.settlement_date. The inline subquery already selected (trade_id, settlement_date) and GROUPed by both — but the legacy JOIN qualified on trade_id alone, producing fan-out when a trade has multiple distinct settlement_dates across its loans.
  • src/backend/PowerSeller.SaaS.Modules.PowerFill/PowerFillModule.cs — sentinel bumped from phase-8-superset-ready to phase-8-superset-ready-a54-fixed.

Documentation (architecturally load-bearing)​

  • docs-site/docs/adr/adr-021-powerfill-port-strategy.md — Status amended to "Proposed (2026-04-16) — Amended 2026-04-19"; new §"Narrow Bug-Fix Carve-Out" section with 4 acceptance criteria + canonical first instance (A54) write-up + "What this is NOT" guardrails + "Forward-only" note. Revision Triggers section gains 2 new bullets.
  • docs-site/docs/specs/powerfill-assumptions-log.md — A54 marked RESOLVED with full empirical-resolution arc (5-run dup-key signature evolution table); A56 marked RESOLVED as A54-side-effect closure; A65 added (multi-pa_key Switching + settlement-date variance are two distinct triggers; both fire on PS_DemoData; PS608 customer DB behavior unknown — Phase 9 probe needed); A66 added (UE clear-and-rebuild-empty on syn-trade-empty datasets — surfaced behavior, not a bug; A58 framing carries a sub-clarification).
  • docs-site/docs/specs/powerfill-engine.md — Phase 6 carry-over checklist item marked DONE; Phase 7 PoC validation note appended with A54 fix update; Phased Implementation table Phase 8 row updated with A54 fix sub-entry; BR-9 amended with A66 note about UE's end-to-end-rebuild behavior.
  • docs-site/docs/superset/powerfill-dashboards.md — A54/A56 carry-over rendering section completely rewritten with pre-fix vs post-fix row count table; new canonical proof-of-life identified (Hub Dashboard 1 instead of Cash Trade Slotting Dashboard 8).
  • docs-site/docs/handoffs/powerfill-a54-fix-greg-demo-readiness.md (NEW) — Greg-demo readiness completion report with full empirical-resolution arc, "Bug as Feature" demo narrative for PO copy-paste, post-fix dashboard row counts, and Counterfactual Retro.
  • docs-site/docs/handoffs/pssaas-session-handoff.md (amended) — sentinel + backlog row updates.
  • This devlog entry.

PoC verification (DV Gate all 3 arms)​

  • Arm (a) sentinel: curl /api/powerfill/status → {"module":"PowerFill","status":"phase-8-superset-ready-a54-fixed"} ✓
  • Arm (c) DB probe: psp_powerfill_pool_guide deployed cleanly to PS_DemoData (modify_date = 2026-04-19 20:00:56); 8/8 dashboard SQL queries execute clean (no SqlException); empirical fan-out source confirmed via mid-run probe of pfill_powerfill_guide for trade 36177868 (3 distinct settlement_dates)
  • Arm (b) live API run: Run 43e8f148-3d1f-4c40-9f76-a43f84efdfb9 completed end-to-end in 30 seconds with Status=Complete, pool_guide_count: 515, ue step succeeded, 12 events in pfill_powerfill_log. Reproducible (run 7c9dfe50-... 31s).

Tests​

  • 233/233 pass (32 BestEx + 200 PowerFill + 1 Api + 6 skipped) — Phase 7/8 W1 baseline preserved exactly (no test regressions; no new tests needed since this is a SQL-side fix to an existing proc body).

Why Specifically (Reasoning)​

The fix shape evolution (Diagnostic-First Rule application)​

The kickoff hypothesized a single fix: extend _1300's 2-col PK to 3 cols (mirror _1400). I planned around that hypothesis — wrong. Empirical PoC after deploying the PK-only fix produced a NEW dup-key shape: (36177868, 3385000026, 3) — three identical rows with the same pa_key=3 (Remaining), not different pool_action values.

Diagnostic-First Rule kicked in. Mid-run probe of pfill_powerfill_guide revealed trade 36177868 had 4 rows with 3 distinct settlement_dates across its loans. The actual fan-out source was the pt13 INNER JOIN qualifying on trade_id only despite the inline subquery selecting (trade_id, settlement_date).

Second surgical fix added (1-line JOIN qualifier extension) — also mirroring legacy author intent (the settlement_date column was selected for pt13 with no other downstream use; clear oversight). Re-deployed and re-ran. Run completed end-to-end.

Why the carve-out (ADR-021 amendment)​

The kickoff explicitly directed: "Modifying a legacy proc body deviates from ADR-021's verbatim-port discipline; it needs a documented exception." I chose to formalize the exception as a §Narrow Bug-Fix Carve-Out within ADR-021 (not a sibling ADR-026), with 4 acceptance criteria:

  • (a) Empirically reproducible
  • (b) Mirrors legacy author intent (as evidenced by adjacent code)
  • (c) Documented in assumption log with a Phase 9 parallel-validation hook
  • (d) Tom/Greg consultation recorded (PO+recipient signature OR demo-as-consultation note)

The A54 fix satisfies all 4. Demo-as-consultation per (d) is PO-confirmed.

Why two ANDON-cord pulls​

This session had three Reviewable Chunks checkpoints, all PO-confirmed:

  1. Plan-presentation (start of session) — Three-layer Gate findings + Alternatives-First framing + sentinel decision. PO confirmed Option A + sentinel bump.
  2. ANDON #1: Diagnostic-First reveal — Post-PK-only-fix PoC failure prompted STOP-and-report. Presented the empirical fan-out finding + refined fix shape. PO confirmed Option A' (PK + JOIN qualifier).
  3. ANDON #2: A66 surfaced finding — Post-both-fix Complete-but-empty-tables outcome prompted STOP-and-report. Presented A66 framing + 2 paths forward (demo as-is vs investigate UE preconditions). PO confirmed Path (i).

Pattern observation: Reviewable Chunks at sub-checkpoint boundaries (not just workstream boundaries) IS the right granularity for empirical-discovery sessions. Without these 3 ANDON-cord pulls I would have shipped the wrong fix shape OR continued with an in-scope-expansion the PO didn't authorize.

Sentinel bump rationale​

phase-8-superset-ready → phase-8-superset-ready-a54-fixed.

  • Distinguishes pre-fix vs post-fix Phase 8 dashboard state
  • Makes the fix observable from /api/powerfill/status without re-deriving from commit log
  • Doesn't reset Phase 8 progress (W1 deliverable unchanged; this is runtime block remediation)
  • Architect-chosen suffix-style preserves phase tracking AND surfaces the fix
  • PO confirmed at planning checkpoint

What's Next​

NextNotes
Pre-push docs-build checkdocker build -f docs-site/Dockerfile.prod docs-site — mandatory per Phase 6e/7/8-W1 lesson before any commit involving new docs-site/docs/** files
Atomic commitsArchitect commits; PO pushes per .cursorrules
Phase 8 W2 (React UI)Ready to start as planned per Backlog row #21d / W2 kickoff. Runtime block is closed; W2 can build on a working end-to-end pipeline.
Phase 9 (Parallel Validation Harness)Can now use the pre-fix vs post-fix dup-key signature evolution as a known-good test fixture. Harness's "data-shape compatibility" pre-flight (per A65) is now well-specified. A66's UE syn-trade question becomes a Phase 9 / Greg-consultation followup.
Greg-demo readinessThe completion report at docs-site/docs/handoffs/powerfill-a54-fix-greg-demo-readiness.md is the consultation material Greg reviews live. Includes a copy-paste-ready "Bug as Feature" demo narrative for the PO.

Process Discipline Banking​

  1. Backlog re-read pass at planning time is now 2-session corroborated (F-8-BR-1 in Phase 8 W1; F-A54-6 in this session) — eligible for canonical promotion at next process-discipline revision per the existing Phase 7 CR #1 nomination.

  2. Diagnostic-First Rule for carve-out fix sizing. Banking for future kickoffs: include an empirical fan-out-source verification step BEFORE sizing the fix. Cost ~10 min; upside avoids a wrong-fix iteration. The kickoff's "STOP and report" pre-emptive instruction was load-bearing even though the specific predicted scenario didn't match.

  3. Alternatives-First Gate option-space framing. Banking: when planning a carve-out, the Alternatives-First options should include the diagnosis hypotheses as well as the fix shape options. The empirical evidence may change the option space itself, not just the chosen option.

  4. Suffix-style sentinel bumps for runtime-block remediation fixes. phase-8-superset-ready-a54-fixed is the canonical pattern: phase progress preserved, fix observable from /status, no commit-log re-derivation needed.

  5. A58-style "preservation across BR-9" framings need explicit "if this side-effect goes away" sections. A58's preservation was a de-facto property of A56 short-circuiting UE; with A54+A56 closed and UE running end-to-end, UE itself supersedes those tables. Banking: framings that depend on side-effects of other open issues should state their fragility upfront.

  6. Demo-as-consultation IS a viable ADR-021 §Narrow Bug-Fix Carve-Out clause (d) disposition when the immediate downstream consumer of the fix is the would-be-consulted domain authority. Reversibility is the safety net: if Greg rejects the carve-out, the fix is git revert-able.

  7. Sub-phase calendar time: ~1 Architect-session. Consistent with 6a-6e + Phase 7/8 W1 velocity at ~2x complexity (2 fixes + 4 assumption-log entries + ADR amendment + new completion report). Empirical Diagnostic-First detour + ANDON-cord protocol added ~40 min total but produced a measurably better fix.

Files Touched (Summary)​

  • src/backend/PowerSeller.SaaS.Modules.PowerFill/Sql/009_CreatePoolGuideProcedure.sql — +30 LOC + 16 comment LOC; 2 surgical fixes inside psp_powerfill_pool_guide
  • src/backend/PowerSeller.SaaS.Modules.PowerFill/PowerFillModule.cs — sentinel bump (1 line)
  • docs-site/docs/adr/adr-021-powerfill-port-strategy.md — Status note + new §Narrow Bug-Fix Carve-Out + Revision Triggers
  • docs-site/docs/specs/powerfill-assumptions-log.md — A54 RESOLVED + A56 RESOLVED + A65 NEW + A66 NEW
  • docs-site/docs/specs/powerfill-engine.md — Phase 6 carry-over checklist + Phase 7 PoC note + Phased Implementation Phase 8 row + BR-9
  • docs-site/docs/superset/powerfill-dashboards.md — A54/A56 carry-over rendering section rewrite
  • docs-site/docs/handoffs/powerfill-a54-fix-greg-demo-readiness.md — NEW completion report
  • docs-site/docs/handoffs/pssaas-session-handoff.md — sentinel + backlog row updates
  • docs-site/docs/devlog/2026-04-19f-a54-fix.md — this entry