Skip to main content

PowerFill Module — Phase 6a (run-orchestration scaffold + BX pre-steps + candidate-builder + carry-cost integration)

Date: 2026-04-17 Agent: Systems Architect (with PO-confirmed Q6/Q9 defaults from Phase 6 kickoff approval) Scope: Synchronous best-effort POST /api/powerfill/run endpoint, two BX pre-step procedures ported from legacy NVO into 006_*.sql, PowerFillCandidateBuilder C# pipeline that consumes BX outputs and writes pfill_loan2trade_candy_level_01, Phase 5 carry-cost calculator integration per Q6 Option A (per-constraint batching), POST /api/powerfill/candidates/preview diagnostic endpoint, status sentinel bump to phase-6a-candidate-builder-ready. Sub-phase 6a of Phase 6 per the sub-phase breakdown.

Why​

Phase 6 is the largest single phase of the PowerFill module (3-4 weeks per the spec estimate, 5 sub-phases per the breakdown). Sub-phase 6a is the upstream prerequisite that every later sub-phase consumes — without a populated pfill_loan2trade_candy_level_01, 6b's allocation engine has nothing to allocate over, and 6c-6e pile on top of 6b. Building 6a first proves the run-orchestration plumbing works end-to-end and gives 6b/6c/6d/6e a stable upstream to test against. The BX cash-grid + settle-and-price procedures are bundled into 6a (rather than split into a 6a-pre) because they have no consumers other than the candidate-builder; splitting would create a deploy state with procs that nothing calls.

What was done​

NVO transcription (delegated to fast subagent)​

Two procedures ported verbatim from n_cst_powerfill.sru into a new 006_CreatePowerFillRunProcedures.sql (738 lines):

  • psp_pfill_bx_cash_grids — NVO 12834-13137 (~303 lines). Signature: @as_price_floor NUMERIC(11,8) (verified during implementation; the kickoff/initial plan said VARCHAR — F1-6a-NEW Truth Rot finding). Builds dynamic best-execution coupon grids for BX trades via 4 idempotent ##bcg_NNNN temp tables.
  • psp_pfill_bx_settle_and_price — NVO 11345-11710 (~365 lines). No parameters. Settles & prices BX phantom trades using rmusr_payups, pscat_comments window-to-set, pscat_inst_dde_links_multi DDE prices. References upstream tables that may not exist in local seed schema (F9-6a-NEW; SQL Server deferred name resolution lets CREATE succeed; PowerFillRunService catches EXEC-time SqlException 208).

Each CREATE block has PRINT N'PowerFill 006: created <proc>' per A32. Idempotent DROP-if-exists then CREATE WITH ENCRYPTION matching the Phase 3 003 deploy pattern.

C# code (Architect-self-implemented)​

  • Contracts/RunContracts.cs — RunRequest (all-nullable input), ResolvedRunRequest (every field populated, with Resolve() static factory implementing F5/Q9 defaults precedence: request → tenant pfill_preflight_settings → legacy w_powerfill.srw:154-169), RunResponse (with structured per-step RunStepResult + RunSummary + RunWarning), CandidatePreviewRequest/CandidatePreviewResponse, RunScope enum, RunStatus enum. Full [JsonPropertyName] snake_case discipline. Accepts both PSSaaS PascalCase and legacy short codes (co/cl, po/pc).
  • Services/PowerFillCandidateBuilder.cs (~520 LOC) — the heart of 6a. Per plan §3.2:
    1. Cash-market-map rebuild (raw SQL extracted from NVO 105-117): DELETE FROM pfill_cash_market_map; INSERT INTO ... SELECT FROM rmcat_bx_setup_instr_inv ⋈ pscat_instruments WHERE rm_trade_type_family LIKE 'wl' AND cash_grid_type IS NOT NULL.
    2. Constraint priority loop in BR-7 ASC order with HashSet cross-constraint dedup (E5).
    3. Per constraint: eligible-loans (cash-market mapping + scope filter + min_status hierarchical filter via pscat_loan_stages.stage_rank + lockdown via rmcat_loan.pool_name ∈ pfill_lockdown_guide where lock_pool='y' + max_eligible_days), eligible-trades (constraint instrument match + max_trade_settle_days + lockdown trade_id), cartesian restricted by cash market map, BR-6 price-floor filter when bx_price_floor is set, interest_earning_days = DATEDIFF(D, COALESCE(close_date, lock_expiration_date), settlement_date) per NVO 1232-1233, calculator call (Q6 Option A: per-constraint batch via PowerFillCarryCostCalculator.ComputeAsync), bulk INSERT into pfill_loan2trade_candy_level_01 with EF change-tracker reset between constraints (E4).
    4. Sec-rule filter (BR-1) deferred to 6b's T-SQL port — see decisions below for rationale; emits a SEC_RULE_DEFINITION_MISSING warning surface so operators see what 6b will enforce.
  • Services/PowerFillRunService.cs (~280 LOC) — 3-step orchestrator modeled on PowerFillPreprocessService (Phase 3 pattern). Synchronous best-effort; fail-fast on first step failure; structured per-step error capture; SqlException summarization. bx_cash_grids skipped when bx_price_floor is null per A12. Passes bx_price_floor as typed decimal parameter to the proc (not stringified) per F1-6a-NEW (NUMERIC(11,8) signature).
  • Endpoints/RunEndpoints.cs — 2 Minimal API endpoints. POST /run returns 200 on Status=Complete, 500 on Status=Failed, 400 when option validation fails (ResolvedRunRequest.Resolve throws ArgumentException). POST /candidates/preview returns 200 + a candidate set without writing to pfill_loan2trade_candy_level_01.
  • DI registrations in PowerFillModule.cs; status sentinel bumped to phase-6a-candidate-builder-ready.

Bug fix discovered during empirical PoC​

SharedDomain/Loan.cs — BorrCreditScore and NumOfUnits properties were typed int? without [Column(TypeName)]. PS_DemoData stores them as NUMERIC(4,0) and NUMERIC(3,0) respectively. The first PowerFill query that read them threw InvalidCastException: Unable to cast object of type 'System.Decimal' to type 'System.Int32' at SqlBuffer.GetInt32. Fixed in-place with [Column(TypeName = "numeric(4,0)")] / [Column(TypeName = "numeric(3,0)")]. EF Core's standard decimal-to-int conversion handles the runtime read correctly. BestEx tests still pass (no behavior change for the InMemory test path; the fix only affects SQL Server reads).

Tests (delegated to fast subagent — 27 test methods, 33 invocations)​

  • Contracts/ResolvedRunRequestTests.cs — 14 invocations covering F5/Q9 defaults precedence rule, legacy short-code acceptance (co/cl, po/pc), bad-input rejection. All pass.
  • Services/PowerFillRunServiceTests.cs — 10 tests covering step-orchestration semantics. 9 pass, 1 skipped (Run_AllStepsSucceed_ReportsComplete — needs candidate-builder InMemory support; blocked by EF Core 8 InMemory provider's lack of ExecuteSqlRawAsync).
  • Services/PowerFillCandidateBuilderTests.cs — 3 tests (constructor shape, warning sentinels, plus 1 [SkippableFact] placeholder cataloguing the 12 algorithmic edge-case tests blocked by the same InMemory limitation). 2 pass, 1 documented skip.

Production-code change request filed (NOT applied) for adding SQLite InMemory provider OR extracting IPowerFillCandidateBuilder interface to unblock the 12 edge-case tests in a follow-on commit. 143 total tests passing, 6 skipped, 0 failed.

Spec + assumptions log​

  • powerfill-engine.md §Run Options amended to match legacy w_powerfill.srw:154-169 defaults per Q9 Option C (PO-confirmed in Phase 6 kickoff). Spec table now shows scope=ClosedAndLocked, min_status=Docs Out (with tenant override), max_eligible_days=0 (no limit), max_trade_settle_days=0 (no limit, with tenant override). Each row cites the legacy source line. New row in §Run APIs documents POST /candidates/preview.
  • powerfill-assumptions-log.md — added A41 (NUMERIC(11,8) signature, F1-6a-NEW), A42 (BX upstream table dependencies, F9-6a-NEW), A43 (PSSaaS service-account EXECUTE permission gap, escalated as 6a-PERM-1). A1.0 unchanged (still placeholder; 6a deliberately did not advance it). Added entry #17 to the Open Questions summary cross-referencing A43.

Deploy verification (all three arms)​

  • arm (a) sentinel: curl /api/powerfill/status → {"module":"PowerFill","status":"phase-6a-candidate-builder-ready"} ✓
  • arm (b) live API: POST /run against local pssaas-db returns 500 + structured RunResponse with Steps[1].error_message = "SqlException 208: Invalid object name 'rmusr_payups'." (F9-6a-NEW documented behavior); against PS_DemoData returns 500 + error_message = "SqlException 229: The EXECUTE permission was denied" (6a-PERM-1 escalation); POST /candidates/preview against PS_DemoData returns 200 + 0 candidates (E13: pfill_trade_base empty for FHLMC small-balance instruments — data-state observation, not a code defect; preview pipeline ran end-to-end with 688 cash-market-map rows rebuilt).
  • arm (c) live DB probe: OBJECT_ID('dbo.psp_pfill_bx_cash_grids') AND OBJECT_ID('dbo.psp_pfill_bx_settle_and_price') both non-null on local pssaas-db AND on PS_DemoData ✓

Process discipline incidents​

Two new Primary-Source Verification Gate findings during implementation​

The kickoff-phase verification gate caught 5 findings (commit 9adcccc); implementation surfaced 2 MORE:

  • F1-6a-NEW — psp_pfill_bx_cash_grids parameter is NUMERIC(11,8), not VARCHAR(...). The plan's "as VARCHAR per legacy invocation pattern" was an over-translation of the PB call-site String(...) cast (which is a PB array-typing artifact, not a proc signature). Caught when the subagent transcribing 006 read NVO line 12837 directly. Disposition: corrected in place. A41 captures the fact; PowerFillRunService passes the parameter as a typed decimal.
  • F9-6a-NEW — psp_pfill_bx_settle_and_price references rmusr_payups, rmcat_setup_risk_parameters, pscat_comments, pscat_inst_dde_links_multi, pscat_trade_cash_grid — none in the local seed schema. Caught when grep against seed-schema.sql for upstream table names returned 0 matches. Disposition: deferred with justification. SQL Server's deferred name resolution lets CREATE succeed; runtime EXEC failure is documented expected behavior on local pssaas-db. A42 captures the fact + the planned PowerFillRunService catch-and-report path.

The Primary-Source Verification Gate's value at the implementation phase is non-zero — both findings shaped real code (parameter type, error-handling path), neither was theoretical.

One escalation pulled (6a-PERM-1)​

POST /run against PS_DemoData fails at Step 2 with SqlException 229: The EXECUTE permission was denied. Cause: kevin_pssaas_dev has db_ddladmin (CREATE/DROP procs) but lacks the rights to GRANT EXECUTE on dbo-owned procs (no WITH GRANT OPTION, not db_owner). Affects ALL PowerFill procs deployed to PS_DemoData, not just 6a — Phase 3 preprocess hits the same wall (latent because Phase 3-5 PoC paths used table reads only). Architect attempted self-implementing a GRANT EXECUTE inside 006_*.sql:

  • GRANT TO <self> → Msg 15281: Cannot grant ... to yourself
  • GRANT TO public → Msg 15151: do not have permission

Both blocked by the role gap. Andon-cord pulled rather than implementing a workaround that breaks tenant-onboarding scenarios. Escalated to PO as 6a-PERM-1: one-time tenant-DB setup GRANT EXECUTE ON SCHEMA::dbo TO kevin_pssaas_dev (or ALTER ROLE db_executor ADD MEMBER ...) by a db_owner-class principal. A43 captures disposition; Phase 6e tenant-onboarding checklist must include this step.

Counterfactual Retro observations​

Plan §12 has 7 named observations. Two highlights:

  • Sec-rule predicate evaluation in C# is a trap. PS_DemoData's pscat_securitization_rules.syntax column is literal T-SQL fragments (e.g., state = 'NY', COALESCE(orig_loan_amount,loan_amount) <= 85000). The plan §3.2 step 5.4 contemplated implementing FICO/LTV/property-type/occupancy predicate evaluation in C#; in implementation it became clear that re-implementing a T-SQL expression evaluator with NULL three-valued logic + INT-vs-NUMERIC promotion + COALESCE semantics is unbounded scope with high parity risk. Decision in-flight: 6a treats sec rules as informational with a structured warning; 6b's allocation T-SQL port evaluates them inline by string substitution into its own dynamic SQL. Should have planned this disposition from the start; the legacy syntax pattern was visible in PS_DemoData all along, just hadn't queried it during planning.
  • Test delegation cost-benefit corrected. Phase 5 retro flagged a "test delegation skip" miscalibration (Architect self-implemented all 27 tests instead of delegating). Phase 6a corrects that — the unit-test cluster was delegated, and the subagent produced 27 tests + 1 documented skip + 1 production-code change request (SQLite InMemory recommendation), all clean. The recalibration was correct: SQL transcription AND parameterized boilerplate test suites both fit the fast-subagent pattern when the brief is tight.

Files produced / modified​

New (Phase 6a):

  • src/backend/PowerSeller.SaaS.Modules.PowerFill/Sql/006_CreatePowerFillRunProcedures.sql — 2 BX procs transcribed verbatim
  • src/backend/PowerSeller.SaaS.Modules.PowerFill/Contracts/RunContracts.cs — Phase 6a public API DTOs
  • src/backend/PowerSeller.SaaS.Modules.PowerFill/Services/PowerFillCandidateBuilder.cs — candidate-builder pipeline
  • src/backend/PowerSeller.SaaS.Modules.PowerFill/Services/PowerFillRunService.cs — synchronous orchestrator
  • src/backend/PowerSeller.SaaS.Modules.PowerFill/Endpoints/RunEndpoints.cs — 2 Minimal API endpoints
  • src/backend/tests/PowerSeller.SaaS.Modules.PowerFill.Tests/Contracts/ResolvedRunRequestTests.cs — 14 invocations
  • src/backend/tests/PowerSeller.SaaS.Modules.PowerFill.Tests/Services/PowerFillRunServiceTests.cs — 10 tests
  • src/backend/tests/PowerSeller.SaaS.Modules.PowerFill.Tests/Services/PowerFillCandidateBuilderTests.cs — 3 tests
  • docs-site/docs/handoffs/powerfill-phase-6a-completion.md — completion report
  • docs-site/docs/devlog/2026-04-17-powerfill-phase-6a.md — this entry

Modified:

  • src/backend/PowerSeller.SaaS.Modules.PowerFill/PowerFillModule.cs — DI + endpoint mapping; status sentinel bumped to phase-6a-candidate-builder-ready
  • src/backend/PowerSeller.SaaS.SharedDomain/Loan.cs — BorrCreditScore and NumOfUnits [Column(TypeName)] annotations (cross-cutting fix discovered at first 6a PoC)
  • docs-site/docs/specs/powerfill-engine.md — §Run Options amended to legacy defaults per Q9 Option C; §Run APIs row added for /candidates/preview + clarified /run Phase 6a synchronous semantics
  • docs-site/docs/specs/powerfill-assumptions-log.md — added A41/A42/A43 + entry #17 to Open Questions summary; A1.0 status note
  • .cursor/plans/powerfill-phase-6a.plan.md (gitignored working artifact) — §2 verification-gate findings table + §11 risks (R-PERM-1, R-DATA-1, R-ENT-1) + §12 Counterfactual Retro filled in

Out of scope / deferred:

  • Sub-phase 6b (multi-pass allocation), 6c (pool actions), 6d (UE pass), 6e (async runs / audit / single-active-run guard) — separate sub-phases per the breakdown
  • The 4 read APIs over pfill_loan2trade_candy_level_01 (/runs/{id}/candidates, etc.) — Phase 7
  • React UI — Phase 8

Verification​

# Build (run inside pssaas-api container per AGENTS.md technical lesson):
docker exec pssaas-api dotnet build /app/PowerSeller.SaaS.sln --nologo
# → Build succeeded. 0 Warning(s). 0 Error(s).

docker exec pssaas-api dotnet test /app/PowerSeller.SaaS.sln --nologo --no-build
# → BestEx:    Passed  32 / 32 / 0 skipped
# → Api:       Passed   1 /  1 / 0 skipped
# → PowerFill: Passed 110 / 110 / 6 skipped (4 pre-existing env-gated SQL +
#                                            2 new InMemory-blocked Phase 6a)
# → Total: 143 passed, 6 skipped, 0 failed

Live API verification commands captured in the completion report.

What's next​

  • Sub-phase 6b — Multi-pass allocation engine. Ports psp_powerfill_conset body (NVO 50-5886). PO must answer Open Q4 (multi-pass semantics; Architect default Option A: empirical NVO trace) before 6b kickoff. PO must also arrange the 6a-PERM-1 GRANT so 6b can demonstrate end-to-end allocation against PS_DemoData. The 6b plan inherits the primary-source citation index from 6a plan §appendix.
  • 6a-PERM-1 escalation — PO arranges GRANT EXECUTE ON SCHEMA::dbo TO kevin_pssaas_dev on PS_DemoData (and on every future tenant DB) by a db_owner-class principal. Phase 6e tenant-onboarding checklist must include this step.
  • Production-code change request — filed for unblocking the 12 algorithmic edge-case tests via SQLite InMemory provider OR IPowerFillCandidateBuilder interface extraction. Recommended timing: as part of 6b setup so the 6b candidate-builder evolution has full test coverage.

Risks captured​

  • R1-PERM-1 — PSSaaS service account EXECUTE permission gap on PS_DemoData (escalated; affects all PowerFill procs).
  • R2-DATA — PS_DemoData data shape produces CandidateCount: 0 even after 6a-PERM-1 fix (FHLMC constraint instruments not present in pfill_trade_base); 6b allocation work will surface the need for either synthetic trade data or constraint reconfiguration.
  • R3-ENT — Schema-drift entity audit: only BorrCreditScore and NumOfUnits were caught this session; Phase 9 backlog item is a full sweep + integration test that round-trips every property of every Domain entity through SqlBuffer.
  • R4-Sync — Phase 6a's synchronous POST /run is single-pod safe but blocks the request thread for the run duration. Production usage waits for 6e's async conversion. Documented in code comments.