Skip to main content

PowerFill Module — Phase 3 Complete

Date: 2026-04-16 Agent: Systems Architect (planning + Commits 1, 3, 4) + fast subagent delegate (Commit 2 SQL transcription) Scope: Ports the three T-SQL procedures invoked by the Desktop App's w_powerfill.srw::ue_perform_preprocess event, plus a C# orchestration service and a POST /api/powerfill/preprocess endpoint. Four Phase-0 Truth Rot findings corrected (F1–F4); first use of the v4 Required Delegation Categories practice and the v4.1 Gate Output Action disposition format.

Why​

Phase 2 gave PowerFill read-only preflight — "Is this tenant ready?". Phase 3 delivers the first write path: run the preparatory mutations the Desktop App runs before allocation. Nothing downstream (Phase 4 constraint CRUD, Phase 6 allocation engine) works without these three procedures populating pfill_lockdown_guide, pfill_epci_params, and pfill_trade_params from upstream pscat_* tables.

What Was Done​

Truth-Rot back-propagation (Commit 1)​

Four Primary-Source Verification Gate findings, each with an explicit Gate Output Action disposition per process-discipline v4.1:

  • F1 — Procedure name psp_pfill_ect_params (not psp_pfill_epci_params). The Phase 1 correction renamed the TABLE from pfill_ect_params → pfill_epci_params; the PROCEDURE that populates it is still named psp_pfill_ect_params in the NVO (lines 6890, 6909; confirmed at w_powerfill.srw:240). The Phase 3 kickoff mis-propagated the Phase 1 table-rename to the procedure name. Disposition: corrected in place. Spec §Pre-Processing bullet 4 updated; assumptions log A21 amended; PowerFill Phase 2 devlog cleaned up.

  • F2 — Desktop App Pre-Process event invokes 3 procs, not 5. The spec and kickoff listed 5 procedures under "Pre-Processing". w_powerfill.srw::ue_perform_preprocess (lines 194-290) actually invokes only psp_add_to_pool_lockdown_guide, psp_pfill_ect_params, psp_pfill_trade_params. The two BX procedures (psp_pfill_bx_cash_grids, psp_pfill_bx_settle_and_price) run in ue_perform_powerfill (the Run event, lines 94-192), take a @as_price_floor Run option, and consume Phase-6-scope upstream data (pscat_inst_dde_links_multi, rmcat_setup_risk_parameters, etc.). Disposition: scope-changed. Phase 3 scope reduced from 5 procs to 3; BX procs moved to Phase 6. Spec §Pre-Processing amended with per-step event-boundary annotations; assumptions log A6 amended.

  • F3 — pscat_trade_cash_grid missing from pssaas-db. Required by psp_pfill_trade_params (NVO line 5983). Disposition: corrected in place via infra/sql/init/seed-schema.sql extension (Commit 2). Phase 6 columns (rate, sequence_number, price, syntax_name) added now to avoid a second seed-schema extension.

  • F4 — pscat_pools and pscat_trades_pools_relation missing columns the Phase 3 procs reference. Caught by the integration test in Commit 4 (live-system verification late in the cycle). Missing: pscat_pools.investor_instrument_name, pscat_pools.pool_name_column_name, pscat_trades_pools_relation.designated_amount. Disposition: corrected in place via infra/sql/init/seed-schema.sql extension.

Delegation Plan outcome (v4 §8 first use)​

The plan classified work clusters against the v4 Required Delegation Categories:

ClusterDispatchOutcome
C1 — Truth-Rot back-propagationSelfDone; would delegate next time for simple prose edits
C2 — Transcribe 3 procs + seed-schema extensionFast subagent (Task / generalPurpose)Subagent delivered in one cycle; 261-line 003 script with correct Truth-Rot trap avoidance (0 matches for psp_pfill_epci_params; 57 UNION rows in CTE matching NVO; WITH ENCRYPTION preserved); 2 documented preserved-quirks from verbatim NVO port
C3 — C# orchestration service + DTOs + endpoint + module wiringSelfSingle Alternatives-First decision surfaced during impl: added thin IPowerFillProcedureRunner seam for unit-testability (EF InMemory cannot execute raw SQL)
C4 — 5 unit testsSelf (below threshold)5 tests, all green
C5 — 1 integration testSelfCaught F4 at its first run — Primary-Source Verification Gate's live-system arm doing its job
C6 — Devlog + handoff updatesSelfThis entry

C2 delegation was net-positive: subagent's Truth-Rot trap avoidance was rigorous (explicit grep verification) and freed the Architect session for orchestration decisions. Prompt was ~180 lines (plan §12.3) including explicit "DO NOT 'fix' the procedure name" anti-pattern. No Deliberate Non-Delegation entries were required — the only cluster matching a default-delegate category was delegated.

SQL — 3 procedures + seed-schema extensions (Commit 2)​

  • src/backend/PowerSeller.SaaS.Modules.PowerFill/Sql/003_CreatePowerFillProcedures.sql (261 lines) — idempotent DROP PROCEDURE IF EXISTS + CREATE PROCEDURE WITH ENCRYPTION for the 3 procs. Each procedure has a section header citing NVO line range. File header explains why the procedure is _ect_params while the table is _epci_params (Truth-Rot trap warning for future readers).
  • infra/sql/init/seed-schema.sql extended with:
    • pscat_trade_cash_grid (Phase 3 + Phase 6 columns)
    • pscat_pools.investor_instrument_name, pscat_pools.pool_name_column_name
    • pscat_trades_pools_relation.designated_amount

C# orchestration (Commit 3)​

  • PowerFillPreprocessService orchestrates the 3 procs in w_powerfill.srw::ue_perform_preprocess order. Stops on first failure; reports per-step status. No explicit transaction — matches Desktop App semantics; procs are individually idempotent via LEFT JOIN IS NULL / NOT EXISTS guards so partial-failure re-run converges.
  • IPowerFillProcedureRunner + TenantDbContextProcedureRunner — thin seam around ExecuteSqlRawAsync, exists solely for unit testability (EF InMemory provider doesn't support raw SQL). Procedure-name validation (letters/digits/underscore only) guards against future misuse even though Phase 3 callers pass compile-time constants. EF1002 interpolation warning suppressed with a #pragma scoped to the validated call site.
  • POST /api/powerfill/preprocess endpoint. Returns 200 + report on Complete; 500 + report on PartialFailure; 400 on invalid request.
  • PowerFillModule DI registration + endpoint mapping.

Tests (Commit 3 + Commit 4)​

  • 5 unit tests (PowerFillPreprocessServiceTests) — all green — cover: happy path, proc 1 fails, proc 2 fails (procs 1 succeeded, 3 absent), cancellation between procs, null request throws.
  • 1 integration test (ProceduresIntegrationTests) — transient DB → seed + 001 + 002 + 003 → re-apply 003 (idempotency) → resolve PowerFillPreprocessService → RunAsync returns Complete with 3 Succeeded steps → re-invoke idempotent → information_schema.routines lists all 3 procs.
  • Skipped when PFILL_TEST_SQLSERVER env var is unset (same pattern as Phase 2's ViewsIntegrationTests).

Test totals​

  • 66/66 pass (32 BestEx + 1 Api + 33 PowerFill [30 unit + 3 integration]) when PFILL_TEST_SQLSERVER is set. Matches plan §10.4 target.
  • Zero regressions against BestEx, Phase 1, or Phase 2.

Files Produced / Modified​

New​

Modified​

  • infra/sql/init/seed-schema.sql — new pscat_trade_cash_grid; extra cols on pscat_pools and pscat_trades_pools_relation
  • src/backend/PowerSeller.SaaS.Modules.PowerFill/PowerFillModule.cs — DI + endpoint wiring
  • docs-site/docs/specs/powerfill-engine.md — Pre-Processing section corrected (F1, F2)
  • docs-site/docs/specs/powerfill-assumptions-log.md — A6 and A21 amended; A27 added (tenant-specific CTE defaults)
  • docs-site/docs/devlog/2026-04-16-powerfill-phase-2.md — removed stale psp_pfill_epci_params reference
  • docs-site/docs/handoffs/pssaas-session-handoff.md — Phase 3 completion entry

Key Decisions​

  • Alternatives-First §7.1 — single 003_* SQL script, not per-proc files or EF migrations. Matches 001/002 pattern; revisit when Phase 6 adds the Run procs.
  • Alternatives-First §7.2 — direct ExecuteSqlRawAsync in service, no typed repository. Procs are zero-parameter for Phase 3; per-proc types add cost before value. Added a single-method IPowerFillProcedureRunner seam for unit testability (orthogonal to the "no typed repo" decision).
  • Alternatives-First §7.3 — no explicit C# transaction. Matches Desktop App semantics; all 3 procs are self-idempotent. Phase 6 Run gets a txn per ADR-021 because allocation is not idempotent.
  • Alternatives-First §7.4 — bx_price_floor handling deferred. F2 moved BX procs to Phase 6.
  • Consolidation Gate §6.2 — PowerFillPreprocessService stands alone. Does NOT extend PowerFillPreflightService; conflating read-only diagnosis with mutating orchestration would violate CQS.

Assumptions Flagged​

  • A27 (new) — psp_pfill_trade_params CTE embeds ~58 tenant-specific instrument-name defaults ('15 fnma cash 85k', '30 fhlmc cash NY', etc.). Preserved verbatim per ADR-006; flagged for Phase 9 modernization critique (likely belongs in a per-tenant reference table).
  • A28 / amends A6 — Desktop App Pre-Process event invokes 3 procs; BX procs are Run-event territory.
  • A29 / amends A21 — Procedure is psp_pfill_ect_params; table is pfill_epci_params; Phase 1 rename did not apply to the procedure.

Antipattern Nomination​

Gate Output Under-Weighting — running a gate, producing a finding, and then treating the finding as a footnote rather than re-scoping around it. Accepted by PO; added to process-discipline.md v4.1 with the mandatory Gate Output Action disposition format (corrected in place / scope-changed / deferred with justification). This devlog uses that format for all four findings above. First use of the practice at scale.

Risks Captured​

  1. CTE defaults in psp_pfill_trade_params are tenant-specific residue (assumption A27). If a new tenant's instruments don't match any of the ~58 names, pfill_trade_params will be empty and Phase 6 allocation will have no per-trade bounds. Mitigation: Phase 9 parity validation will catch this via Desktop App comparison; Phase 9 modernization will replace the CTE with a reference table.
  2. WITH ENCRYPTION preserved per ADR-006 — sp_helptext on the PSSaaS-deployed procs returns nothing, making live debugging harder. Mitigation: the NVO is the authoritative source; use Sql/003_CreatePowerFillProcedures.sql for review.
  3. Single-active-run-per-tenant not enforced in Phase 3. Two concurrent preprocess requests against the same tenant will race on the pfill_* tables. Phase 6 adds the BR-8 guard. Mitigation: documented in plan §9 and §3.2; no user-visible symptom in Phase 3 because preprocess is the only write surface.
  4. PS_DemoData deployment of 003 awaits kevin_pssaas_dev credentials (Backlog #22, #23 extend to #25 for 003). Validation against realistic data waits until that step runs.

What's Next​

Phase 4 — Constraint + Carry Cost + Lockdown CRUD APIs. Per spec lines 76-98 and 165-182. Also makes the Phase 2 preflight thresholds (min_status, max_prices_age_days, max_trade_settle_days) tenant-configurable (Phase 2 plan Open Q #4; handoff backlog #24).