Spec: PowerFill Engine

Status: Draft Author: Kevin Sawyer (Product Owner) + AI Architect Date: 2026-04-16 Reference: PowerFill Deep Dive

Overview

The PowerFill engine is the PSSaaS pool allocation optimizer. Given a pipeline of eligible loans, a set of open agency/whole-loan trades, current pool assignments, and configurable constraints, PowerFill determines the optimal loan-to-pool allocation that maximizes trade fulfillment and delivery value while respecting securitization rules and lockdown decisions.

This spec ports the Desktop App's PowerFill plugin (~19K lines of T-SQL + ~5K lines of PowerBuilder UI) to the PSSaaS modular monolith. The target is behavioral parity with the Desktop App — same inputs, same outputs, same decision logic — delivered through modern APIs, modern audit trails, and a SaaS-native workflow.

2026-04-16 correction (Phase 1 planning, Architect): When extracting DDL directly from n_cst_powerfill.sru (line 6004, of_update_database), the actual pfill_* table count is 17, not 13. One legacy table is pfill_epci_params, not pfill_ect_params (see the PowerFill Deep Dive for the authoritative inventory). The three tables previously missing from the spec are pfill_loan_constraint_pool, pfill_loan_ordering, pfill_payups.

Business Context

A mid-market lender doing agency delivery has dozens of open Fannie/Freddie/Ginnie trades at any time. Each trade has a target amount, tolerance band, settlement date, coupon/instrument requirements, and pool-eligibility rules. Matching the right loans to the right trades is an optimization problem that a human cannot solve manually.

PowerFill has been in production in the Desktop App for over 15 years. Losing it in the PSSaaS transition would mean losing the feature that differentiates PowerSeller from the competition for mid-market lenders doing agency delivery. This is a Tier 1 feature parity requirement.

The PSSaaS port enables:

• Cloud execution — Run PowerFill from anywhere
• Scheduled runs — Scheduled PowerFill runs (e.g., daily at close, intraday on significant pipeline changes)
• API integration — Feed results to pooling, risk, and reporting systems without manual export
• Auditability — Every run captured with inputs, outputs, and decision trail
• Multi-tenancy — One PowerFill engine, N tenants, each with their own constraints and carry cost curves

PowerBuilder Reference

The legacy implementation lives at X:\dev\other\PowerSeller-App\plugins\powerfill\:

Component	Files	Role
NVO (engine)	n_cst_powerfill.sru	~19K lines of T-SQL embedded in PB string literals, deployed as stored procedures
Window shell	w_powerfill.srw, w_maint_pfill_constraint_order.srw	5-tab main window + constraint priority modal
User objects	uo_tab_powerfill_constraints.sru, uo_tab_powerfill_cost_carry.sru	Tab implementations
Menu	m_powerfill.srm	Pre-Process / PowerFill / Reports / Constraint actions
DataWindows	~15 .srd files	Reports, maintenance grids, lists, options
Package	powerfill.pbg	Library group manifest

See the PowerFill Deep Dive for the full reverse-engineering — every file, every table, every procedure, every pool action semantic.

Interpretations of ambiguous or undocumented code behavior are tracked in the PowerFill Assumptions Log.

Scope

This spec covers:

• Constraint configuration and priority management
• Carry cost curve configuration
• Lockdown guide configuration
• Pre-processing (cash grid, settle/price, embedded pay-up cash, trade parameters)
• Core allocation engine (psp_powerfill_conset equivalent)
• Post-allocation user-event processing (psp_powerfillUE equivalent)
• Run-output queries for reports (Guide, Recap, Switching, Pool Candidates, Existing Disposition, Pooling Guide, Cash Trade Slotting)
• API contracts for all of the above

Out of scope (covered by other specs):

• BestEx analysis itself (consumed as input, see BestEx spec)
• Pool creation/maintenance (consumed as input, handled by the core pool module)
• Trade creation/maintenance (consumed as input, handled by the core trade module)
• Applying accepted recommendations (user-driven, handled by the core pool-trade-relation module)
• Pricing ingestion (handled by the price ingestion module)

Requirements

Constraint Management

• Create, read, update, and delete PowerFill constraints (pfill_constraints)
• Associate constraints to securitization rules (pfill_constraint_sec_rule_rel)
• Support hierarchical constraints: investor → instrument → sub-constraint (preserving legacy tree model per ADR-023)
• Support constraint priority ordering (lower number = higher precedence)
• Bulk re-prioritize constraints via a priority editor
• Validate constraint completeness before allowing a PowerFill run (at least one constraint defined)
• Constraint delete guard (F3 resolved 2026-04-16, PO Option A): Do not allow DELETE on pfill_constraints while rows exist in pfill_constraint_sec_rule_rel for that composite key (API returns 409; client must delete associations first). This is the authoritative delete rule. The prior wording "Prevent deletion of constraints referenced by active lockdown rules" is superseded because pfill_lockdown_guide has no constraint_name, no investor_id, and no FK to pfill_constraints — there is no enforceable join path against the preserved legacy schema. A lockdown-derived guard remains a TBD follow-up pending PowerBuilder UI review (ue_delete_constraint, uo_tab_powerfill_constraints); if review reveals a real constraint↔lockdown linkage, an additive guard will be specified in a future phase. Documentation chain: powerfill-phase4-f3-architect-escalation, assumptions A33.

Carry Cost Configuration

• Create, read, update, and delete carry cost curves (pfill_carry_cost)
• Curves are keyed by investor instrument + day bucket
• Support tenant-specific carry cost curves (one tenant's curves don't affect another's)
• Validate curve monotonicity or flag non-monotonic curves (optional warning)

Carry Cost Computation (Phase 5)

The carry cost computation is the runtime evaluator that consumes the configured pfill_carry_cost curves to produce, for each (loan, trade) candidate pair, two scoring fields used by the allocation engine: carry_cost and prx_plus_carry. The algorithm is a verbatim port from the Desktop App NVO n_cst_powerfill.sru lines 1332-1364 (within psp_powerfill_conset) and re-emitted at lines 14429-14472 (within psp_powerfillUE). Phase 5 ships the calculator as a C# service (ADR-021 hybrid model: module orchestration in C#, allocation engine in T-SQL); Phase 6 wires it into the candidate-builder.

Inputs (per (loan, trade) pair):

Field	Type	Source
loan_id	string	candidate row identity
trade_id	string	candidate row identity
market	string	pfill_cash_market_map.investor_instrument_name (Phase 6 lookup)
interest_earning_days	int	DATEDIFF(D, COALESCE(close_date, lock_expiration_date), settlement_date) (NVO line 1232-1233, 1442); can be negative — calculator does not filter
price	decimal	trade price for this loan, already feature-adjusted upstream
note_rate	decimal	loan note rate

Run-level option:

• price_mode ∈ PricePlusCarry — corresponds to legacy @as_price_value parameter values 'po' and 'pc'. Default: PricePlusCarry. Source: Run Options table above.

Algorithm:

Step 1 — Average rate lookup (NVO line 1333-1347)

  avg_rate := AVG(pcc.annual_rate)
              over all rows in pfill_carry_cost
              WHERE TRIM(pcc.investor_instrument_name) = TRIM(input.market)
                AND pcc.on_day <= input.interest_earning_days <= pcc.to_day
                AND pcc.annual_rate IS NOT NULL

  If no row matches: avg_rate := NULL

Step 2 — carry_cost (NVO line 1348-1354)

  If avg_rate IS NULL:
    carry_cost := NULL
  Else:
    carry_cost := round_to_NUMERIC(9,6)( avg_rate * interest_earning_days / 365 )

Step 3 — prx_plus_carry (NVO line 1361-1364)

  If price_mode == PriceOnly:
    net_carry_adjustment := 0
  Else (PricePlusCarry):
    If carry_cost IS NULL:
      net_carry_adjustment := NULL                           -- propagates
    Else:
      net_interest         := note_rate * interest_earning_days / 365
      net_carry_adjustment := net_interest - carry_cost      -- income MINUS cost-of-funds

  If net_carry_adjustment IS NULL:
    prx_plus_carry := NULL                                   -- candidate has unscored carry
  Else:
    prx_plus_carry := round_to_NUMERIC(9,6)( price + net_carry_adjustment )

Sign convention: the loan's net interest income during the carry period is added to price; cost-of-funds carry is subtracted. A high-coupon loan held longer scores higher (lender benefits); a high cost-of-funds environment scores lower.

Output (per (loan, trade) pair):

• average_annual_rate (decimal?) — Step 1 output, NULL when no curve match
• carry_cost (decimal?) — Step 2 output, NULL when average_annual_rate is NULL; always populated regardless of price_mode
• prx_plus_carry (decimal?) — Step 3 output; equals price in PriceOnly mode regardless of match success; NULL only in PricePlusCarry mode when curve match failed
• match_status (enum) — diagnostic only, NEVER a filter verdict (per A2). Values: Matched, MatchedAveraged (multiple curve rows averaged), InstrumentNotInCurve, DaysOutsideCoverage, RateIsNull
• matched_row_count (int) — 0 = no match; 1 = single bucket; 2+ = averaged (overlap)

Edge cases (each verbatim NVO behavior unless noted):

Edge case	Behavior
Negative interest_earning_days (loan closed after settle)	BETWEEN on_day AND to_day excludes (legacy buckets start at 0); returns DaysOutsideCoverage
Instrument missing from pfill_carry_cost	InstrumentNotInCurve; carry_cost = NULL
Day-bucket gap between curve rows	DaysOutsideCoverage; carry_cost = NULL; no interpolation, no extrapolation
Day exactly on bucket boundary	Closed inclusive [on_day, to_day] (T-SQL BETWEEN) — matches the bucket containing the day
Day exceeds highest to_day in curve	DaysOutsideCoverage; no extrapolation, no cap to max bucket
Multiple matching rows (overlapping buckets)	AVG of annual_rate values; match_status = MatchedAveraged (defensive — schema permits overlap)
annual_rate IS NULL for a matching row	T-SQL AVG excludes the row; if all matching rows have NULL → RateIsNull
to_day IS NULL (open-ended bucket)	Treat as +∞ — matches if days >= on_day. Defensive extension; NVO BETWEEN x AND NULL evaluates UNKNOWN and drops the row. Phase 9 critique can revisit.
'po' (PriceOnly) mode	prx_plus_carry = price; carry_cost still computed for diagnostic visibility
Calculator never disqualifies a loan	Per A2 — match status is diagnostic; Phase 6 allocation owns ranking decisions

Tenant isolation: Calculator reads pfill_carry_cost via TenantDbContext, scoped to the authenticated tenant's database (ADR-005). No cross-tenant data access.

Financial precision: All arithmetic uses decimal (CLAUDE.md invariant #5). The rounding mode for NUMERIC(9,6) coercion is MidpointRounding.AwayFromZero to match T-SQL CAST(decimal AS NUMERIC(9,6)) semantics for parity with the Desktop App.

'any' semantics: The 'any' value in pfill_carry_cost.investor_instrument_name is treated as a literal-match (NVO line 1342, 14447 use literal equality tcl.market = pcc.investor_instrument_name). It is NOT a wildcard fallback. Future product enhancement candidate.

Caller surface (Phase 5):

• C# service PowerFillCarryCostCalculator in Modules.PowerFill/Services/. Two methods: batched (ComputeAsync(IReadOnlyList<CarryCostInput>, CarryPriceMode, CancellationToken)) and single-pair (ComputeOneAsync(...)). One DB query per batch (groups inputs by distinct market, single IN @markets query against the PK).
• Diagnostic API POST /carry-cost/preview (see API Contracts table below).
• Phase 5 ships with no production caller; Phase 6 candidate-builder will be the first.

Lockdown Management

• Create, read, update, and delete lockdown entries (pfill_lockdown_guide)
• Lockdown entries scope: pool_name, settlement_date, investor_instrument_name, trade_id, designated_amount, trade_amount, lock_pool flag
• Bulk lock/unlock by selection criteria
• Lockdown entries persist across PowerFill runs until explicitly cleared or modified
• Report which lockdowns are currently active (lock_pool = 'y')

Pre-Processing

2026-04-16 correction (Phase 3 planning, Architect; Primary-Source Verification Gate): The Desktop App's w_powerfill.srw::ue_perform_preprocess event (lines 194-290) invokes only three procedures. The two BX procedures (psp_pfill_bx_cash_grids, psp_pfill_bx_settle_and_price) are invoked from the separate ue_perform_powerfill event (lines 94-192) — they are part of the Run path, not Pre-Process. Pre-Process steps below preserve the spec's logical grouping but identify which event actually invokes each procedure. The PSSaaS POST /api/powerfill/preprocess endpoint (Phase 3) implements only the 3 ue_perform_preprocess procs; the 2 BX procs move to Phase 6 (Run). See PowerFill Phase 3 plan §2.3 finding F2.

• Pre-Process step 1: BX Cash Grids (Desktop App event: ue_perform_powerfill; PSSaaS phase: 6, Run) — Refresh BestEx cash grids for the current scope (psp_pfill_bx_cash_grids equivalent). Skippable if bx_price_floor is unset.
• Pre-Process step 2: Settle and Price (Desktop App event: ue_perform_powerfill; PSSaaS phase: 6, Run) — Compute settlement dates and prices from BestEx context (psp_pfill_bx_settle_and_price equivalent)
• Pre-Process step 3: Lockdown application (Desktop App event: ue_perform_preprocess; PSSaaS phase: 3) — Apply lockdown rules to the working set (psp_add_to_pool_lockdown_guide equivalent)
• Pre-Process step 4: EPCI parameters (Desktop App event: ue_perform_preprocess; PSSaaS phase: 3) — Compute embedded pay-up cash instrument parameters. Procedure is named psp_pfill_ect_params (NVO line 6890) and writes INTO the pfill_epci_params table (NVO line 6911); the Phase 1 table rename (ect_params → epci_params) did NOT apply to the procedure name. Preserve ugly per ADR-006.
• Pre-Process step 5: Trade parameters (Desktop App event: ue_perform_preprocess; PSSaaS phase: 3) — Build per-trade parameters: target, tolerance, settlement windows (psp_pfill_trade_params equivalent)
• Pre-processing is idempotent — re-running produces the same result given the same inputs
• Pre-processing can run independently of the main PowerFill pass (matches Desktop App menu's "Pre-Process" action)

Core Allocation Engine

• Build cash market map — Map loan instruments to investor cash grids using rmcat_bx_setup_instr_inv (populates pfill_cash_market_map)
• Iterate constraint priority — Walk pfill_constraints in priority order (lower number first)
• Build loan-to-trade candidacy — For each constraint level, score each loan-trade pair on pricing, carry cost, eligibility date, settlement date alignment
• Apply securitization rule filters — Exclude loans that violate the target pool's securitization rules (hard constraint — no exceptions)
• Apply loan-stage filters — Respect pscat_loan_stages minimum status requirements
• Apply lockdown constraints — Do not touch loans/trades/pools flagged in pfill_lockdown_guide
• Apply carry cost adjustments — Score candidates by price or price-plus-carry (per options)
• Multi-stage allocation — Per constraint, execute three stages in order: Stage 1 (Core Cycle 1 — bubble pass + Single-X inner loop + reranking), Stage 2 (Reranking + marginal-cost substitution, capped at 20 iterations or convergence), Orphan Sweep (final fallback for unallocated loans). Per ADR-022. Per A1 revision (2026-04-18 Phase 6b empirical NVO trace) — the original Phase 0 "exact fit / best fit / fill remaining / orphan handling" labels do NOT appear in the legacy NVO and were Phase 0 invention; the canonical labels are the NVO comment markers (Setup, Stage 1, Stage 2, Orphan Sweep)
• Detect pool actions — Compare current assignment to proposed, produce Remaining, Leaving, Joining, Switching (with internal Swapped In for the destination side of a Switch)
• Track kickouts — Loans that can't be allocated go to pfill_kickout_guide_01 with failure reason
• Write run outputs — Populate pfill_powerfill_guide, pfill_pool_guide, pfill_trade_base, pfill_cblock_guide, pfill_trade_cblock_base

Post-Allocation (UE Pass)

• Phase 6d — After core allocation, run post-processing (psp_powerfillUE verbatim port; NVO 13246-19801 = of_get_psp_powerfillue_syntax_a + of_get_psp_powerfillue_syntax_b; deployed via 011_CreatePowerFillUeProcedure.sql). Same 6 parameters as conset (per A40 / F-6d-5).
• Phase 6d — UE rebuilds pfill_powerfill_guide (NVO 13441 DELETE + NVO 13453-18949 re-allocate); populates 3 pfill_syn_* tables (pfill_syn_trade_base / pfill_syn_powerfill_guide_all_rank / pfill_syn_powerfill_guide) + pfill_powerfill_log; re-EXECs psp_powerfill_pool_guide at NVO 19795 to apply synthesis-based price/carry UPDATEs. The post-UE pfill_pool_guide is the user-visible state; the 6c pre-UE snapshot is intermediate.
• Phase 6d — UE auto-creates pfill_powerfill_log if missing (NVO 13265-13275; per A37). PSSaaS deploys this table explicitly via 010_CreatePowerFillSynTradesSchema.sql so the dependency is visible at DDL deploy time; the UE proc's IF-NOT-EXISTS guard is preserved verbatim for legacy-coexistence safety.
• RESOLVED 2026-04-19 (A54 + A56) — End-to-end UE PoC against PS_DemoData. The previously-deferred A54 latent legacy ##cte_posting_set_1300 PK violation in psp_powerfill_pool_guide is now closed via two surgical fixes inside ADR-021's amended §Narrow Bug-Fix Carve-Out (PK extension to 3 cols + pt13 JOIN qualifier extension). End-to-end Complete run achieved on PS_DemoData (run 43e8f148-3d1f-4c40-9f76-a43f84efdfb9, 30s wall-clock). Step 5 (pool_guide) writes 515 rows mid-run; Step 6 (ue) runs end-to-end. A66 (NEW) documents the UE clear-and-rebuild-empty behavior on syn-trade-empty datasets: UE clears + rebuilds the user-facing tables based on syn-trade-augmented logic; PS_DemoData lacks syn-trade-eligible loan/trade pairs (no arbitrage opportunities in the snapshot), so the post-UE state shows 0 rows in the 7 user-facing run-output tables and pfill_cash_market_map; only pfill_powerfill_log retains UE's 12 forensic events. Phase 9 parallel-validation against a customer DB with real syn-trade opportunities is the gate to confirm UE's full rebuild logic. See powerfill-a54-fix-greg-demo-readiness + A54 / A56 / A66 in assumptions log.
• Phase 7 — Finalize recap statistics (pre-session / post-session need and space) surfaced via GET /runs/{run_id}/recap reading pfill_trade_base. The 32-col pre_session_* / post_session_* columns (per the entity definition) are populated by Step 4 (psp_powerfill_conset) and surfaced verbatim by Phase 7's RecapRow DTO; aggregation is left to the Phase 8 dashboard layer.
• Phase 7 — pfillv_existng_pool_disposition view already deployed in Phase 2 (002_CreatePowerFillViews.sql); Phase 7 surfaces via GET /runs/{run_id}/existing-disposition reading the existing PoolDispositionReadModel. F-7-7 finding (per A62): PS_DemoData has the legacy WITH ENCRYPTION version of the view that pre-dates the note_rate column — Phase 7 service catches SqlException 207 and degrades to an empty payload + explanatory Note pending Backlog #24's deploy decision.
• Validate run output integrity (no orphaned references, no negative fills) — Phase 7+

Run Options

PowerFill runs accept these options, mapping to the Desktop App's d_powerfill_options DataWindow fields. Defaults match the legacy Desktop App (w_powerfill.srw:154-169) per ADR-006 schema/behavior preservation — see Phase 6 Open Question Q9 (resolved 2026-04-17, Architect Option C: spec amends to match legacy):

Option	Values	Default	Purpose	Legacy source
scope	ClosedOnly / ClosedAndLocked (also accepts legacy short codes co / cl)	ClosedAndLocked	Which loans to include — closed-only or closed-or-locked	w_powerfill.srw:154 ('cl')
price_mode	PriceOnly / PricePlusCarry (also accepts po / pc)	PricePlusCarry	Scoring mode (see §Carry Cost Computation)	w_powerfill.srw:157 ('pc')
min_status	loan status code	Docs Out (or tenant pfill_preflight_settings.min_status if set)	Minimum status for eligibility	w_powerfill.srw:160 ('Docs Out')
max_eligible_days	integer	0 (no limit — legacy semantics)	Max days to loan eligibility; 0 means unlimited	w_powerfill.srw:163 ('0')
max_trade_settle_days	integer	0 (no limit; tenant pfill_preflight_settings.max_trade_settle_days overrides)	Max days to trade settlement; 0 means unlimited	w_powerfill.srw:166 ('0')
eligible_settle_buffer_days	integer	0	Loan must be eligible N days before trade settles	w_powerfill.srw:169 ('0')
bx_price_floor	decimal or null	null (BestEx cash-grids step is skipped when null per A12)	Minimum BestEx price for eligibility	parameter to psp_pfill_bx_cash_grids (NVO 12837); w_powerfill.srw:114

Q9 resolution provenance: the prior spec defaults (ClosedOnly, 30, 60) diverged from the legacy ('cl', 0, 0). The Phase 6 Primary-Source Verification Gate (Phase 6 sub-phase breakdown, open questions) flagged this as a Phase-0 Truth Rot finding (F5). PO accepted Architect default Option C (2026-04-17): spec amends to legacy. The Phase 6a POST /api/powerfill/run endpoint applies these defaults via ResolvedRunRequest.Resolve. Phase 9 parallel-validation against the Desktop App will exercise this default-options path; any future divergence requires an ADR superseding this resolution.

Run Execution Model

• Phase 6e — PowerFill runs are asynchronous: POST /api/powerfill/run returns 202 Accepted + run_id immediately + Location: /api/powerfill/runs/{run_id}; the run executes in the in-memory Channel<RunJob> background worker per ADR-024. Sub-phases 6a-6d shipped a synchronous best-effort surface; 6e converts it to true async.
• Phase 6e — Each run has a unique run_id (UUID) and captures: tenant id, user id, started/ended UTC timestamps, lifecycle status, JSON-serialised options, input loan/trade counts (when known), output guide/kickout counts, failure context, and a serialised RunResponse snapshot. Persisted to pfill_run_history (script 012_CreatePfillRunHistoryTable.sql).
• Phase 6e — Run status values: Pending / PreProcessing / Allocating / PostProcessing / Complete / Failed / Cancelled. The first four are active states (BR-8 enforcement set); the last three are terminal. The active set is encoded both as a SQL filtered unique index predicate (012) and as PowerFillRunHistoryService.ActiveStatuses — drift between the two silently breaks BR-8, so RunStatus serialisation tests pin both byte-for-byte.
• Phase 6e — A tenant can have at most one active run at a time (BR-8). Enforced by the SQL filtered unique index ux_pfill_run_history_tenant_active on pfill_run_history (tenant_id) WHERE status IN active-set. Endpoint catches SqlException 2627 and translates to 409 Conflict with the active run's id+status in the body.
• Phase 6e — Failed/Cancelled runs trigger BR-9 cleanup: the 7 user-facing pfill_* run-output tables are cleared (pfill_loan2trade_candy_level_01, pfill_powerfill_guide, pfill_kickout_guide_01, pfill_trade_base, pfill_cblock_guide, pfill_trade_cblock_base, pfill_pool_guide). The 4 syn-trades + log tables (pfill_syn_trade_base, pfill_syn_powerfill_guide_all_rank, pfill_syn_powerfill_guide, pfill_powerfill_log) are PRESERVED for forensic value (per A58: UE writes them BEFORE the inner pool_guide EXEC at NVO 19795 fires A54; partial content is evidence of what UE managed to produce before the failure point). Failure context is in pfill_run_history.failure_step + failure_message.
• Phase 6e — Run output is retained indefinitely in pfill_run_history; historical runs can be queried via GET /api/powerfill/runs (paginated list) and GET /api/powerfill/runs/{run_id} (full RunResponse deserialised from response_json).

Audit Trail

• Phase 6e — Every run captured in pfill_run_history (script 012_CreatePfillRunHistoryTable.sql; PSSaaS-only addition, not in legacy Desktop App).
  • Canonical 11 columns (spec line 257): run_id (UUID, PK), tenant_id, user_id, started_at_utc, ended_at_utc, status, options_json, input_loan_count, input_trade_count, output_guide_count, output_kickout_count
  • Q3 Option B addition (PO-confirmed): input_loan_ids_json (NVARCHAR(MAX)) — JSON array of loan_ids considered at run start; ~80KB for 10K loans; forensic recall ("which loans were in this run's input?").
  • Q7 Option B addition (PO-confirmed): failure_step (VARCHAR(64)) + failure_message (NVARCHAR(MAX)) — populated when the run terminates as Failed/Cancelled.
  • Phase 6e addition: response_json (NVARCHAR(MAX)) — full JSON-serialised RunResponse captured at finalise time. Lets GET /runs/{run_id} return the canonical 6a-6d response shape (including per-step results + warnings) without re-querying tables that BR-9 cleanup just cleared.
• Phase 6e — BR-8 filtered unique index ux_pfill_run_history_tenant_active on (tenant_id) WHERE status IN ('Pending','PreProcessing','Allocating','PostProcessing').
• Phase 6e — Cursor pagination index ix_pfill_run_history_tenant_started_at on (tenant_id, started_at_utc DESC) INCLUDE (status, ended_at_utc).
• Phase 7 — Snapshot replay tables (Q3 Option C) — pfill_run_history_loans + pfill_run_history_trades per-run snapshots — DEFERRED. The audit row's PK doesn't change between Option B and Option C; the JSON column can be supplemented with snapshot tables in Phase 7+ without breaking the endpoint contract.
• Phase 7+ — All constraint, carry cost, and lockdown modifications captured in a pfill_config_audit table (who, what, when, before, after). Not in 6e scope.

API Contracts

The PSSaaS PowerFill module exposes these endpoints under /api/powerfill/:

Configuration APIs

All paths are under /api/powerfill/. Composite keys use URL segments (trim/padding handled server-side for CHAR columns). 412 = optimistic concurrency (row_version on constraint PUT and concurrency_token on reprioritize). 409 on constraint delete = sec-rule associations still present (F3 interim guard).

Method	Path	Purpose
GET	/settings/preflight	Read merged preflight defaults (pfill_preflight_settings singleton)
PUT	/settings/preflight	Upsert preflight defaults
GET	/constraints	List constraints; optional query investor_id, instrument
POST	/constraints	Create constraint (409 if duplicate composite key)
PUT	/constraints/{investorId}/{instrument}/{constraintName}	Update constraint body + row_version (412 if stale)
DELETE	/constraints/{investorId}/{instrument}/{constraintName}	Delete constraint (409 if pfill_constraint_sec_rule_rel rows exist — F3)
POST	/constraints/reprioritize	Bulk re-prioritize; body includes concurrency_token (412 if stale)
GET	/constraints/{investorId}/{instrument}/{constraintName}/sec-rules	List sec-rule associations for a constraint
POST	/constraints/{investorId}/{instrument}/{constraintName}/sec-rules	Add association
DELETE	/constraints/{investorId}/{instrument}/{constraintName}/sec-rules/{ruleName}	Remove association
GET	/carry-cost	List carry cost rows; optional query investor_instrument_name
POST	/carry-cost	Create row (409 if (instrument, on_day) exists); response may include monotonicity warnings
PUT	/carry-cost/{investorInstrument}/{onDay}	Upsert/update; route must match body
DELETE	/carry-cost/{investorInstrument}/{onDay}	Delete row
GET	/lockdown	List lockdown entries; optional query active_only=true
POST	/lockdown	Create lockdown (201 or 409 duplicate pool_name)
PUT	/lockdown/{poolName}	Update lockdown (404 if missing — not an upsert)
DELETE	/lockdown/{poolName}	Delete lockdown
POST	/lockdown/bulk	Bulk lock/unlock

Calculator APIs (Phase 5)

Method	Path	Purpose
POST	/carry-cost/preview	Diagnostic: compute carry_cost and prx_plus_carry for a batch of (loan, trade) inputs without writing anything. Pure transformation; does not look up real loan/trade data. Symmetric with the GET /settings/preflight diagnostic-visibility pattern. Request and response bodies shown below.

Request body for POST /carry-cost/preview:

{
  "items": [
    {
      "loan_id": "L1",
      "trade_id": "T1",
      "market": "30 fnma cash",
      "interest_earning_days": 20,
      "price": 99.5,
      "note_rate": 6.25
    }
  ],
  "price_mode": "PricePlusCarry"
}

price_mode is one of "PriceOnly" or "PricePlusCarry".

Response body:

{
  "results": [
    {
      "loan_id": "L1",
      "trade_id": "T1",
      "average_annual_rate": 0.270000,
      "carry_cost": 0.014795,
      "prx_plus_carry": 99.827671,
      "match_status": "Matched",
      "matched_row_count": 1
    }
  ]
}

Run APIs

Method	Path	Purpose
POST	/preflight	Validate inputs, return readiness report (does not execute)
POST	/preprocess	Run pre-processing only (equivalent to Desktop App's "Pre-Process" menu action)
POST	/run	Kick off a PowerFill run. Phase 6e: returns 202 Accepted + RunSubmissionResponse (run_id + Pending status + Location header pointing at /runs/{run_id}); the run executes in PowerFillRunBackgroundService per ADR-024. Returns 409 Conflict + RunConflictResponse if BR-8 active run; 503 Service Unavailable if the queue is saturated; 400 + error if options invalid. The 6-step orchestration body (Steps 1-6) preserved verbatim from 6a-6d; the worker thread invokes PowerFillRunService.ExecuteResolvedAsync with status transitions through PreProcessing → Allocating → PostProcessing as steps run. Sub-phases 6a-6d shipped a synchronous best-effort surface (200 + RunResponse / 500 / 400) which is preserved as the legacy ExecuteAsync entry point for tests. RunSummary includes the same fields as before (6b's allocated/kicked-out/cblock per A47, 6c's pool_guide_count per A53, 6d's 6 post-UE counters per A55/A56). Note: per A56 the 6d Step 6 is structurally deployed but not yet exercised end-to-end on PS_DemoData — A54 in Step 5 short-circuits the run before Step 6 reaches UE; Phase 9 parallel-validation is the gate for full PoC observation. The 6e PoC validates the orchestration layer (async behaviour, audit row, BR-8, BR-9, GET endpoints, cancel) against the same A56 outcome.
POST	/candidates/preview	Phase 6a diagnostic — runs the candidate-builder pipeline (BX procs SKIPPED) and returns the candidate set without writing to pfill_loan2trade_candy_level_01. Symmetric with /carry-cost/preview.
GET	/runs	Phase 6e: paginated list of runs for the current tenant, most-recent first. Query: ?limit=N (default 25, max 100), ?before=<run_id> for cursor pagination. Returns RunListResponse with runs[], next_cursor, total_returned.
GET	/runs/{run_id}	Phase 6e: full RunResponse for the run; deserialised from pfill_run_history.response_json when terminal, synthesised as a partial in-flight projection from the audit row when active. Returns 404 if the run_id doesn't belong to the current tenant.
POST	/runs/{run_id}/cancel	Phase 6e: signal cancellation. Worker honours at next step boundary + via ExecuteSqlInterpolatedAsync(ct) propagation. Returns 202 Accepted + RunCancelResponse if signalled; 409 Conflict if the run is already terminal; 404 if not found.

Output APIs (Phase 7 — promoted to canonical contract; ADR-025)

The 8 read endpoints below surface the PowerFill run-output tables as JSON. Per ADR-025 the design is latest-Complete-wins — the underlying pfill_* tables hold the OUTPUT OF THE MOST RECENT RUN per BR-9 (clear-on-Failed/Cancelled) + BR-10 (overwrite-on-next-run), so the {run_id} URL parameter is validated against pfill_run_history and a freshness verdict applied (per A60):

Freshness verdict	HTTP status	Behavior
RunNotFound — {run_id} doesn't belong to this tenant	404 Not Found	error body explains run_id is unknown to this tenant
Stale — {run_id} exists but a later run has overwritten the pfill_* tables	410 Gone	response body carries the latest run_id in the note field for client redirection
Current — {run_id} IS the latest run AND in an active or Complete state	200 OK	response carries actual rows (or in-flight partial state if active)
TerminalEmpty — {run_id} IS the latest run but terminated as Failed/Cancelled (BR-9 cleared the user-facing tables)	200 OK + empty Rows + note carrying failure context (failure_step + failure_message)	reports reading non-BR-9-cleared sources (Existing Disposition, Cash Trade Slotting) may still surface data; per-report note clarifies

Cursor pagination on the 4 unbounded reports (Guide, Recap, Pool Candidates, Kickouts) per ADR-025 §A7.3: keyset on the natural composite PK; default ?limit=100, max 1000. Single-column-PK reports use ?after=<value>; multi-column-PK reports use ?after_<col>=<value> query-param pairs. Switching, Existing Disposition, Pooling Guide, and Cash Trade Slotting return all rows (small volume); pagination can be added in Phase 7+ if a customer DB shows large volumes.

Method	Path	Source table(s)	Pagination	Phase 7 PoC behavior on PS_DemoData (per A54+A56)
GET	/runs/{run_id}/guide	pfill_powerfill_guide (Step 4 output)	?limit=N&after=<loan_id>	empty + TerminalEmpty Note (BR-9 cleared)
GET	/runs/{run_id}/recap	pfill_trade_base (Step 4 output)	?limit=N&after_trade_id=<...>&after_rate=<...>	empty + TerminalEmpty Note
GET	/runs/{run_id}/switching	pfill_pool_guide filtered pool_action IN ('Swapped In','Swapped Out') per BR-3	none	empty + TerminalEmpty Note (A54-blocked)
GET	/runs/{run_id}/pool-candidates	pfill_loan2trade_candy_level_01 (Step 3 output, per A51)	?limit=N&after_loan_id=<...>&after_trade_id=<...>	empty + TerminalEmpty Note
GET	/runs/{run_id}/existing-disposition	view pfillv_existng_pool_disposition (Phase 2's 002_*.sql)	none	empty + TerminalEmpty Note + PS_DemoData schema-drift Note (per A62 — view note_rate column missing on PS_DemoData)
GET	/runs/{run_id}/pooling-guide	pfill_pool_guide aggregated by trades_pool	none	empty + TerminalEmpty Note (A54-blocked)
GET	/runs/{run_id}/cash-trade-slotting	pfill_cash_market_map (NOT BR-9-cleared per A58) + optional pscat_trade_cash_grid join	none	688 real rows on PS_DemoData (cash_market_map survives BR-9 per A58); per-trade slot fields null per A12 (Step 1 skipped)
GET	/runs/{run_id}/kickouts	pfill_kickout_guide_01 (Step 4 output)	?limit=N&after_loan_id=<...>&after_top_trade_id=<...>	empty + TerminalEmpty Note

Phase 7 PoC validation (post-f72a392 against PS_DemoData; sentinel phase-7-reports-ready): all 8 endpoints respond per the contract above; Stale + 404 paths verified against the existing audit row history; Cash Trade Slotting returned real data (688 rows) demonstrating that A58's preservation scope is observable from the read APIs even on Failed runs.

A54 fix update (post-6d19d24 + amendments, against PS_DemoData; sentinel phase-8-superset-ready-a54-fixed): end-to-end Complete-run is now empirically achievable (run 43e8f148-... 30s wall-clock; 7c9dfe50-... reproduces). The Hub dashboard shows 11 runs total with the latest Complete (output_guide_count=515). However, A66 (NEW) documents that psp_powerfillUE clears + rebuilds-empty the user-facing run-output tables on syn-trade-empty datasets like PS_DemoData, so the BR-9-cleared and the A58-previously-preserved report endpoints all return empty payloads on the post-Complete state. Cash Trade Slotting which previously showed 688 rows now shows 0 (UE supersedes Step 4's pfill_cash_market_map per A66 sub-finding). End-to-end real-data exercise on the user-facing reports remains a Phase 9 carry-over (closes when run against a customer DB with real syn-trade opportunities). The Phase 7 contracts themselves are unchanged; the empty-with-Note responses correctly reflect the post-UE state.

Per-run snapshot replay (Q3 Option C — pfill_run_history_loans / pfill_run_history_trades) — explicitly DEFERRED per spec line 263. The latest-Complete-wins semantics is the v1 contract; future Phase 7+ can add snapshot replay tables without breaking the endpoint shape.

Tenant Isolation (ADR-005)

• All pfill_* tables live in the tenant database
• All reads and writes scoped to the authenticated tenant's database
• Constraint, carry cost, and lockdown configurations are per-tenant
• No cross-tenant data access under any circumstance

Phase 4 PSSaaS-only tables

These tables are not part of the legacy Desktop App schema; they are introduced by PSSaaS and live in the tenant database alongside the legacy pfill_* tables. Schema preservation (ADR-006) does not apply because there is nothing to preserve.

• pfill_preflight_settings (script 004) — singleton row (id = 1, CHECK (id = 1)) holding tenant-overridable preflight defaults: min_status, max_prices_age_days, max_trade_settle_days, updated_at_utc. Source of truth for the values merged into PreflightRequest when a JSON field is omitted. If the table is missing (e.g. 004 not yet deployed against an older tenant DB), the API and preflight service degrade to built-in static defaults (Closed / 2 / 60); the GET response includes a source field of tenant, defaults, or builtin so consumers can tell.
• pfill_constraints.rv_rowversion (script 005) — additive ROWVERSION column on the existing legacy table. Powers 412 Precondition Failed on constraint PUT and POST /constraints/reprioritize. Not present in the Desktop App; safe additive change because legacy queries don't SELECT * from this table directly (verified: legacy procs reference column lists explicitly).

Phase 6d PSSaaS-explicit tables (synthetic-trades + run-trace log)

These 4 tables exist on PowerSeller Desktop App customer DBs (legacy psp_powerfillUE populates them at runtime) but were not declared in the Phase 1 reverse-engineering source (of_update_database at NVO 6094). Phase 6d makes them PSSaaS-explicit so the dependency is visible at DDL deploy time. Per A28/A37 RESOLVED in 6d.

• pfill_syn_trade_base (script 010) — 11 cols, PK (syn_trade_id, rate) per F-6d-8 empirical PS_DemoData PK probe. Synthesizable trade variants populated by UE at NVO 19019-19082 (TRUNCATE + INSERT each run).
• pfill_syn_powerfill_guide_all_rank (script 010) — 40 cols, no PK (heap; ranked candidate set with multiple rows per loan). Populated by UE at NVO 19083-19250 (first pass) + 19450-19790 (second pass for unallocated loans). Four parallel rank columns: payup_only_rank, payup_plus_carry_rank, price_only_rank, prx_and_carry_rank.
• pfill_syn_powerfill_guide (script 010) — 40 cols (same shape as _all_rank), PK (loan_id) per F-6d-8. The recommended synthesis per loan (filtered down from _all_rank). Populated by UE at NVO 19255-19347. Read by post-UE invocation of psp_powerfill_pool_guide UPDATE blocks at NVO 11167-11181 (per A52) + the pfillv_pf_forensics_tradeside view (Phase 7).
• pfill_powerfill_log (script 010) — 5 cols, PK (log_id IDENTITY). Run-trace log auto-created by the legacy UE proc at NVO 13265-13275 if missing; PSSaaS deploys explicitly per A37 so the dependency is visible at DDL deploy time. The verbatim-port UE proc body retains its IF-NOT-EXISTS guard for legacy-coexistence safety. Per-run TRUNCATE+INSERT pattern (UE writes 6+ log rows per run).

The 3 pfill_syn_* tables + pfill_powerfill_log are populated by psp_powerfillUE (script 011); they are NOT populated by psp_powerfill_conset (script 008) or psp_powerfill_pool_guide (script 009). Per A56, the 6d PoC against PS_DemoData has not yet exercised the population path because A54 in Step 5 short-circuits the run before Step 6/UE; Phase 9 parallel-validation is the gate.

Phase 6e PSSaaS-explicit tables (async run audit + BR-8 enforcement)

This is the PSSaaS-only audit/concurrency surface introduced in sub-phase 6e. There is no legacy Desktop App equivalent (the legacy plugin is single-user, per-machine, no run history). Schema preservation (ADR-006) does not apply because there is nothing to preserve.

• pfill_run_history (script 012) — 14 cols, PK (run_id UUID). One row per submitted run; lifecycle managed by PowerFillRunHistoryService (INSERT on the request thread; UPDATEs from the background worker as the run transitions through active states; finalisation with serialised RunResponse snapshot in response_json at terminal-status). Persists the canonical 11 spec columns + Q3 Option B's input_loan_ids_json (forensic) + Q7 Option B's failure_step + failure_message + Phase 6e's response_json (full RunResponse snapshot so GET /runs/{run_id} returns the canonical 6a-6d response shape without re-querying tables that BR-9 cleanup just cleared).

  • Filtered unique index ux_pfill_run_history_tenant_active: (tenant_id) WHERE status IN ('Pending','PreProcessing','Allocating','PostProcessing'). The BR-8 enforcement vehicle — SqlException 2627 on INSERT translates to HTTP 409. Survives all in-process state loss (pod restart, channel reset).
  • Cursor index ix_pfill_run_history_tenant_started_at: (tenant_id, started_at_utc DESC) INCLUDE (status, ended_at_utc). Supports GET /runs?limit=N&before=<run_id> cursor pagination.

The 4 syn-trades + log tables introduced in Phase 6d are intentionally PRESERVED on Failed/Cancelled runs (per A58) because UE populates them at NVO 19019-19790 BEFORE the inner pool_guide EXEC at NVO 19795 fires the documented A54 PK violation. Their content is forensically valuable evidence of what UE managed to produce before the failure point.

PowerFill-owned table count progression: Phase 1 = 17; +1 (preflight settings, Phase 4) = 18; +4 (syn-trades + log, Phase 6d) = 22; +1 (run history, Phase 6e) = 23.

Integration with Other Modules

• Consumes BestEx setup from rmcat_bx_setup_instr_inv — read-only
• Consumes current prices from rmcat_todays_prices — read-only
• Consumes trades from pscat_trades and trade-pool relations from pscat_trades_pools_relation — read-only
• Consumes pools from pscat_pools — read-only
• Consumes securitization rules from pscat_securitization_rules — read-only (Phase 0 originally cited pscat_securities_sec_rules / pscat_pools_sec_rule_rel; those are not the authoritative names per Phase 2 verification)
• Does NOT modify any table outside the pfill_* namespace
• Applying PowerFill recommendations (creating/modifying pool-trade-loan relations) is handled by a separate module — this module only recommends

Business Rules

BR-1: Securitization Rules are Hard Constraints

If a loan violates the target pool's securitization rule (e.g., excluded property type, FICO below threshold, LTV above threshold), it cannot be allocated to that pool. No exceptions, no warnings — it's excluded from candidacy at the filter stage.

BR-2: Lockdown Takes Absolute Precedence

If pfill_lockdown_guide.lock_pool = 'y' for a pool/trade:

• Loans currently in that pool are not reassigned
• The trade's fulfillment is not modified
• The pool appears in output reports with its current state preserved

BR-3: Pool Action State Machine

Every loan in pfill_pool_guide has exactly one pool_action. The legacy psp_powerfill_pool_guide proc (NVO 8770-11185, ported to 009_CreatePoolGuideProcedure.sql per ADR-021) emits 9 distinct values across user-facing and internal states (per A53 revision 2026-04-19):

User-facing (4 values):

• Remaining — Loan was in pool X, stays in pool X
• Leaving — Loan was in pool X, will be removed (no destination yet)
• Joining — Loan was unallocated, will be allocated to pool Y
• Switching — Loan was in pool X, moves to pool Y. Generates paired internal Swapped In (destination) + Swapped Out (source).

Internal (4 values, used by Phase 7 Switching Thumbnail report and others):

• Swapped In (pa_key=1) — destination side of a Switch. UPDATE at NVO 11157-11160 re-keys pool_action='Switching' AND pa_key=1 rows to Swapped In.
• Swapped Out (pa_key=4) — source side of a Switch. UPDATE at NVO 11162-11165 re-keys pa_key=4 AND pool_action='Leaving' rows to Swapped Out. Paired with Swapped In for full Switch visibility.
• Vacated (pa_key=6 after re-key) — Pool became empty after Switching. UPDATE at NVO 11146-11149 re-keys pa_key=5 rows to Vacated.
• Ineligible (pa_key=5 after re-key) — Loan would have left but isn't eligible. UPDATE at NVO 11151-11155 re-keys pa_key=4 AND curr_status <> 'Eligible' rows to Ineligible.

(Note: Eligible appears in the proc body at NVO 11132-11134 but is a curr_status normalization (SET curr_status='Eligible' WHERE curr_status='ELIGIBLE'), NOT a pool_action value.)

The Switching Thumbnail report (Phase 7) filters on pool_action IN ('Swapped In', 'Swapped Out') to surface paired Switch sides.

Phase 6c snapshot vs Phase 6d post-UE: Sub-phase 6c populates pfill_pool_guide via the pre-UE invocation of psp_powerfill_pool_guide (Step 5 in the run pipeline). Phase 6d's UE pass populates pfill_syn_powerfill_guide and re-invokes psp_powerfill_pool_guide at NVO 19795, so the user-facing pfill_pool_guide is the post-UE state. The 6c output is the pre-UE snapshot — useful for 6d's parity validation but not the user-visible final.

BR-4: Trade Tolerance Bands

Trades have a tolerance amount (from pscat_trades.tolerance_amount or the trade parameter proc). PowerFill may over-fill or under-fill within the band. Allocations outside the band are rejected.

The assign column on pfill_powerfill_guide and pfill_pool_guide carries 3-character tolerance/disposition codes (e.g., ypd = "yes, partial design" / ypo = "yes, partial overfill" / etc.). Tolerance enforcement is integrated into the multi-stage allocation engine (psp_powerfill_conset, Phase 6b) rather than being a separate post-allocation step. Pool-guide derivation (Phase 6c) preserves the assign value alongside the pool_action per loan.

BR-5: Carry Cost Never Disqualifies

Carry cost adjusts scoring but never makes a loan ineligible. A loan with high carry cost simply scores lower vs alternatives; it can still win allocation if no better candidate exists.

BR-6: Price Floor is Exclusion, Not Scoring

If bx_price_floor is set and a loan's BestEx price is below the floor, the loan is excluded entirely from PowerFill — not merely deprioritized.

BR-7: Priority Order Matters

Constraints apply in priority order (ascending). Higher-priority constraints run first and may consume loans/trade capacity that lower-priority constraints would have used. This is the legacy behavior and must be preserved.

BR-8: Run Isolation

Only one PowerFill run per tenant can be active at a time. Attempting a second run while one is in progress returns HTTP 409 Conflict.

Phase 6e implementation (per Q2 Option A, PO-confirmed): enforced via the SQL filtered unique index ux_pfill_run_history_tenant_active on pfill_run_history (tenant_id) WHERE status IN ('Pending', 'PreProcessing', 'Allocating', 'PostProcessing') (012_CreatePfillRunHistoryTable.sql). The endpoint catches SqlException 2627 on INSERT and translates to 409 Conflict with RunConflictResponse body (active_run_id + active_status). Survives all in-process state loss (pod restart, channel reset). The active-status set is encoded BOTH as the SQL predicate AND as PowerFillRunHistoryService.ActiveStatuses — drift between them silently breaks BR-8, so RunStatus contract tests pin both.

BR-9: Run Output is Immutable / Failed-Run Cleanup

Once a run completes, its output tables are not modified until the next run. Users review the results; if they want different results, they change options/constraints/lockdowns and re-run.

Phase 6e failure-state semantics (per Q7 Option B, PO-confirmed): when a run fails or is cancelled, the orchestrator clears the 7 user-facing run-output tables (pfill_loan2trade_candy_level_01, pfill_powerfill_guide, pfill_kickout_guide_01, pfill_trade_base, pfill_cblock_guide, pfill_trade_cblock_base, pfill_pool_guide) and populates pfill_run_history.failure_step + failure_message. The 4 syn-trades + log tables (pfill_syn_trade_base, pfill_syn_powerfill_guide_all_rank, pfill_syn_powerfill_guide, pfill_powerfill_log) are PRESERVED for forensics per A58 — UE writes them BEFORE the inner psp_powerfill_pool_guide EXEC at NVO 19795 fires the documented A54 PK violation, so they retain valuable evidence of what UE managed to produce before the failure point. Failure forensics goes through the audit row's failure_message rather than through stale row data.

A54 fix update (2026-04-19, A66): with A54 + A56 resolved and the proc body running end-to-end, psp_powerfillUE itself rebuilds the user-facing tables AND the syn-trades + log tables according to its own logic. A58's "preservation across BR-9" is preserved across post-Failed cleanup, but UE's normal end-to-end run rebuilds those tables per its own design intent. On syn-trade-empty datasets like PS_DemoData, UE writes 0 rows to most tables (only pfill_powerfill_log retains UE's 12 forensic events). On customer DBs with real syn-trade opportunities, UE will populate them with the synthetic-trade-augmented allocation. BR-9's Failed/Cancelled semantics (this section) remain unchanged; A66 describes the post-Complete state, which is governed by UE's logic, not BR-9.

BR-10: Run Output Overwrites Prior Run

The next completed run overwrites prior run output in pfill_* run-output tables. Historical output, if preserved, is captured per ADR-021.

Data Contracts

Run Options (JSON)

{
  "scope": "ClosedAndLocked",
  "price_mode": "PricePlusCarry",
  "min_status": "Closed",
  "max_eligible_days": 30,
  "max_trade_settle_days": 60,
  "eligible_settle_buffer_days": 3,
  "bx_price_floor": 99.50
}

Run Status Response

{
  "run_id": "7f4a9b0e-8c1d-4e5f-a1b2-3c4d5e6f7a8b",
  "tenant_id": "watermark-tpo",
  "user_id": "kevin@powerseller.com",
  "started_at": "2026-04-16T14:30:00Z",
  "ended_at": "2026-04-16T14:33:42Z",
  "status": "Complete",
  "options": { /* as above */ },
  "summary": {
    "input_loan_count": 8742,
    "input_trade_count": 47,
    "output_guide_count": 8512,
    "output_kickout_count": 230,
    "pool_actions": {
      "Remaining": 5123,
      "Leaving": 418,
      "Joining": 2712,
      "Switching": 259
    },
    "trades_fully_filled": 41,
    "trades_partially_filled": 5,
    "trades_unfilled": 1
  }
}

PowerFill Guide Row (from /runs/{run_id}/guide)

Mirrors pfill_powerfill_guide columns:

{
  "pa_key": 12,
  "trade_id": "TRD-20260416-003",
  "loan_id": "2350260412001",
  "pool_action": "Switching",
  "note_rate": 6.500,
  "rate": 6.000,
  "loan_amount": 450000.00,
  "trade_amount": 5000000.00,
  "tolerance_amount": 50000.00,
  "eligible_date": "2026-04-20",
  "settlement_date": "2026-04-25",
  "curr_status": "Closed",
  "trade_instrument": "FNMA 30yr 6.0",
  "source_pool": "FNMA-20260425-6.0-A",
  "target_pool": "FNMA-20260425-6.0-B",
  "scoring_price": 101.25,
  "scoring_carry": 0.08,
  "scoring_total": 101.17
}

Algorithm — High-Level Pseudocode

Per ADR-022, the core allocation algorithm is a faithful port of the legacy iterative passes:

# Pre-processing (idempotent)
if bx_price_floor is set:
    refresh_bx_cash_grids()
compute_settle_and_price()
apply_lockdown_to_working_set()
compute_ect_params()
compute_trade_params()
build_cash_market_map()

# Core allocation
clear_run_output_tables()
constraints = load_constraints(ordered_by_priority_ascending)

for constraint in constraints:
    candidates = build_loan_to_trade_candidacy(
        constraint=constraint,
        pricing=from_bx_settle_and_price,
        carry=from_pfill_carry_cost,
        eligibility=apply_sec_rules(constraint) and apply_loan_stages() and apply_lockdown(),
        scope=options.scope,
        date_windows=options.max_eligible_days, options.max_trade_settle_days,
        price_floor=options.bx_price_floor,
    )

    # Multi-stage allocation within this constraint (per A1 revision)
    setup_per_constraint(candidates)        # NVO ~1208-1908 — populate working slice
    stage_1_core_cycle(candidates)          # NVO ~1909-3967 — bubble + single-X + rerank
    stage_2_marginal_cost(candidates,
        max_iterations=20)                  # NVO ~3968-5521 — converge or cap at 20
    orphan_sweep(candidates,
        loop_guard=@j)                      # NVO ~5524-5810 — final fallback
    write_to_pfill_powerfill_guide(allocations)
    write_to_pfill_pool_guide(derive_pool_actions(allocations))      # 6c
    write_to_pfill_cblock_guide(constraint_capacity_blocks)          # internal A47
    write_to_pfill_trade_cblock_base(per_trade_constraint_blocks)    # internal A47

# Remaining unallocated go to kickouts after all constraints processed
write_to_pfill_kickout_guide_01(unallocated_loans)

# Post-processing (psp_powerfillUE equivalent — Phase 6d scope)
finalize_recap(pfill_trade_base)
build_existing_pool_disposition_view()
validate_output_integrity()

The exact semantics of each stage (Setup, Stage 1's bubble pass + Single-X inner loop, Stage 2's marginal-cost substitution, Orphan Sweep) are documented per-stage in Assumption A1 (revised 2026-04-18 by Phase 6b empirical NVO trace). The legacy proc body deploys verbatim per ADR-021; per-stage-semantic correctness validation against Desktop App output is the Phase 9 parallel-validation gate.

Architectural Decisions

Three ADRs govern implementation approach:

• ADR-021: Port Strategy — hybrid (T-SQL engine in tenant DB + C# orchestration)
• ADR-022: Allocation Algorithm — port iterative passes verbatim
• ADR-023: Constraint Model — preserve legacy tree structure

Phased Implementation

The full module plan is scoped across 10 phases (Phase 0 = this spec). Subsequent phases implement the spec in order:

Phase	Scope	Estimate
0	This spec + ADRs + assumptions log	In progress
1	Domain model + EF Core schema for pfill_* tables	3-5 days
2	Data access + preflight validation	3-5 days
3	Pre-processing procedures	5-7 days
4	Constraint engine + CRUD APIs	5-7 days
5	Carry cost calculator + CRUD APIs	3-5 days
6	Core allocation engine (biggest phase)	3-4 weeks → DONE in ~5 sessions across 3 calendar days (sentinel phase-6e-async-runs-ready since f72a392)
7	Reports / recap query APIs	1 week → DONE in ~1 Architect-session (sentinel phase-7-reports-ready); 8 GET endpoints; ADR-025 documents per-run vs current-state semantics
8	Superset dashboards + React UI	1-3 weeks → W1 (Superset) DONE + A54 fix shipped + W2 (React UI) DONE in ~3 Architect-sessions over 1 calendar day (sentinel phase-8-superset-react-ready-a54-fixed). W1 (Superset): 8 dashboards + 25 charts deployed to PSX Superset against PS_DemoData; A63+A64 added; F-7-8 / 688-row Cash Trade Slotting pattern verified end-to-end through Superset's engine. A54 fix (mid-stream): PS_DemoData runs achieve Complete end-to-end via two surgical fixes within ADR-021's amended §Narrow Bug-Fix Carve-Out; A65+A66 added; dashboard 1 (Hub) shows 11 runs with the latest Complete; dashboards 14-19 + Cash Trade Slotting render 0 rows post-UE per A66; Greg-demo-readiness completion report at powerfill-a54-fix-greg-demo-readiness. W2 (React UI) DONE in ~1 Architect-session (consistent with 6e/7/8-W1 velocity): greenfield Vite + React + TypeScript + Tailwind scaffold under src/frontend/ (~50 source files, 270KB gzipped JS / 17KB gzipped CSS); 4-page operator workflow (Home / Submit / Runs List / Run Status) + 8 Phase 7 report pages with 4-verdict freshness banner UX (per A60 + A66 — including the load-bearing TerminalEmpty 2-source split: yellow for Failed/Cancelled, blue for Complete + syn-trade-empty); "View in Superset" deep-links to W1 dashboard IDs 13-20; new frontend Deployment + Service in pssaas-staging namespace; ingress path /app added; new GHCR image ghcr.io/kevinsawyer/powerseller-saas/frontend; GHA workflow extended (path-filter + build + idempotent rollout); ADR-026 documents the framework + build-pipeline choice; A67 documents the F-W2-CONTRACT-1 ReportContracts.cs XML doc-comment Truth Rot finding caught at planning time. Phase 8 fully complete. Completion report: powerfill-phase-8-w2-completion.
8.5	Ecosystem Auth + Embedded Superset SDK	2-3 Architect-sessions → DONE in ~1 Architect-session 2026-04-20 (sentinel phase-8-5-ecosystem-ready; supersedes phase-9-validation-ready). Ships oauth2-proxy in front of /app/ + /api/ (W1) + replaces W2 anchor-link "View in Superset" with @superset-ui/embedded-sdk <EmbeddedDashboard> (W2) + .NET 8 SupersetGuestTokenClient doing the 3-step Superset handshake (login → CSRF → guest_token POST) at /api/superset/guest-token (W3) + infra/superset/register-powerfill-embeds.py per-dashboard registration script (W3 delegated) + A68 long-term decoupling code shape (TenantMiddleware OIDC tenant_id claim wins over legacy X-Tenant-Id header; TenantRegistry.Resolve returns Tenant(TenantId, ConnectionString) record; W4) + ADR-027 (Accepted) + ADR-029 (Accepted; A68 PARTIALLY RESOLVED). 250 .NET tests pass (200 PowerFill + 32 BestEx + 8 NEW Superset + 10 Api with 9 NEW TenantMiddleware tests; 6 skipped; was 233 pre-Phase-8.5). Frontend bundle: 272.69KB JS gzipped (vs 270KB pre-Phase-8.5; SDK lazy-loaded in lib-Cb7LCDYX.js chunk). Per PO disposition (Phase 8.5 plan §3 (d)): canonical-identity convention deferred to Phase 10+; W4 ships code-shape only with no migration. Pending PSX Infra collaboration (Keycloak pssaas-app client + Superset embedding pre-flight verification per cross-project-relay 2026-04-20-pssaas-keycloak-pssaas-app-client-request); cross-boundary cutover verification recipe applied bilaterally post-collaboration. Completion report: powerfill-phase-8-5-completion.
9	Validation + Tom/Greg critique + cutover	2-3 weeks → Validation harness DONE + first comparison run DONE in ~1 Architect-session 2026-04-20 (sentinel phase-9-validation-ready). Ships the Python parallel-validation harness at tools/parallel-validation/ (~10 source files including harness.py + snapshot.py + pssaas_invoker.py + sqlcmd_invoker.py + diff_engine.py + report_renderer.py + Jinja2 template + README + harness_config.yaml); ADR-028 documents the 3 Alternatives-First decisions (invocation path = direct sqlcmd; language = Python 3.10+ in WSL Ubuntu; output = Markdown) + the Frame D Hybrid framing per PO-confirmed pre-plan exchange (renumbered from initial ADR-027 — a parallel Collaborator-authored ADR-027 for Superset Embedding Strategy landed in commit ece500e during the Phase 9 dispatch window per the canonical "ADRs are numbered sequentially and never renumbered" rule); A67 closure (XML doc-comment fix in ReportContracts.cs); A69 NEW (state-dependent UE failure on non-empty post-pool_guide state surfaced by the harness's first run — exactly the class of finding Phase 9's harness was designed to catch). The first comparison run output at docs-site/docs/devlog/2026-04-20-powerfill-phase-9-first-validation-run.md is the load-bearing demo asset; the Capability × Environment matrix per practice #13 explicitly carries "NOT MEASURABLE HERE" cells for the legacy-vs-fixed-body parity question (deferred to operator-driven post-Phase-9 sweep against customer DBs without A54 triggers) + for the Customer DB column. Cutover scope explicitly deferred to Phase 10+ per the kickoff's framing (the spec-line-651 "cutover" wording was a 2026-03 spec-line assumption superseded by the Phase 8.5 / 9 / 10 split). Completion report at powerfill-phase-9-completion.
10	Production cutover (per-customer; superseded the Phase 9 cutover scope)	TBD when Greg/Tom sign off + first beta customer migration plan lands

Each phase gets its own plan and PR.

Acceptance Criteria

Phase 0 (This Spec)

• Spec approved by Product Owner (Kevin)
• ADRs 021-023 accepted by Product Owner
• Legacy deep dive reviewed for accuracy
• Assumptions log captures every interpretation with code citation
• Phase 1 can begin without waiting for external review

Full Module (Phases 1-9)

• PowerFill API endpoints deployed to pssaas-staging
• Parallel run against Desktop App on PS_DemoData — output matches within documented tolerance
• Tom or Greg review and sign off (or document deltas)
• At least one beta customer running PSSaaS PowerFill alongside Desktop App for validation
• Migration playbook for first production cutover

Open Questions

Logged in PowerFill Assumptions Log for future Tom/Greg critique:

• Exact semantics of the psp_powerfill_conset allocation passes RESOLVED 2026-04-18 (A1 revision): structure is 3 stages per constraint inside an outer per-constraint priority loop; per-stage semantics (Stage 2 convergence, Orphan Sweep cap) are clear from the NVO; Phase 9 parallel-validation is the per-loan correctness gate.
• Carry cost computation formula (derived from pfill_carry_cost usage patterns) RESOLVED 2026-04-16 (Phase 5 — A11 verified line-by-line against NVO 1332-1364).
• Meaning of pfill_cblock_guide / pfill_trade_cblock_base RESOLVED 2026-04-18 (A47): "cblock" = constraint capacity block. Per-trade-per-constraint capacity exhaustion tracking inside the conset proc.
• Whether pfill_powerfill_guide and pfill_pool_guide are redundant or serve distinct purposes
• Handling of pair-offs (pscat_pair_offs) — are they treated as trades or excluded?
• Kickout resolution — does the user re-run with modified constraints, or is there a manual override path?

Success Metrics

• Parity: PSSaaS PowerFill produces the same pool actions as Desktop App PowerFill for 95%+ of loans on identical inputs. Documented deltas have acceptable business explanations.
• Performance: Full pipeline run (~10K loans, ~50 trades) completes in under 5 minutes on standard SQL MI pricing tier.
• Reliability: Failed runs leave tables in a consistent state (no orphaned rows, no missing references).
• Auditability: Every run is reconstructable from the audit trail (inputs + options + output).
• Adoption: At least one customer migrates from Desktop App PowerFill to PSSaaS PowerFill in year one.