PowerFill Plugin — Deep Dive

Full reverse-engineering of the Desktop App's PowerFill plugin. This document is the primary reference for the PSSaaS PowerFill module spec, ADRs 021-023, and every downstream implementation phase.

Source: X:\dev\other\PowerSeller-App\plugins\powerfill\ (25 files). Size: ~19,000 lines of T-SQL embedded in PowerBuilder string literals, plus ~5,000 lines of PB window/menu/DataWindow code.

What PowerFill Does

PowerFill is the Desktop App's pool allocation optimization engine. Given:

• A pipeline of eligible loans (closed and/or rate-locked)
• A set of open agency/whole-loan trades awaiting fulfillment
• Existing pool allocations
• Configurable constraints (investor/instrument eligibility, securitization rules)
• Carry cost curves

PowerFill produces recommendations for every eligible loan specifying whether it should:

• Remain in its current pool (already optimally placed)
• Leave its current pool (reclassify as unallocated)
• Join a new pool (previously unallocated)
• Switch from one pool to another (move to a better destination)

The goal is to maximize trade fulfillment (filling open trade commitments) and maximize delivery value (price-only or price-plus-carry depending on configuration). Every accepted recommendation produces either a pool allocation change or a "kickout" (loans that couldn't be allocated).

Why It Matters

A secondary marketing desk at a $1B/year lender has dozens of open agency trades at any given time. Each trade has a target amount, tolerance band, settlement date, coupon/instrument constraints, and pool-eligibility rules. Matching the right loans to the right trades is an optimization problem with hundreds of variables and hundreds of constraints.

Doing this manually is impossible. The Desktop App's BidMgr + PowerFill + pool engine combination is what makes PowerSeller one of the only viable choices for mid-market lenders doing agency delivery — the competition either doesn't solve this problem or costs 10x more.

Porting PowerFill to SaaS is therefore a Tier 1 feature parity requirement, not optional.

File Inventory

Windows and Menus

File	Role
w_powerfill.srw	Main window ("PowerFill Trades") — 5 tabs + options DataWindow
w_maint_pfill_constraint_order.srw	Modal for constraint priority editing
m_powerfill.srm	Menu with Pre-Process, PowerFill, Reports actions + Constraint CRUD

Tabs (User Objects)

File	Tab	Role
uo_tab_powerfill_constraints.sru	Constraints	Tree view + securitization rule assignment
uo_tab_powerfill_cost_carry.sru	Cost and Carry	Carry cost curve maintenance
(uo_tab_with_dw — shared base)	Guide	d_powerfill_guide_report
(uo_tab_with_dw — shared base)	Recap	d_powerfill_recap
(uo_tab_with_dw — shared base)	Lockdown	d_maint_lockdown_guide

Non-Visual Object (the engine)

File	Role
n_cst_powerfill.sru	~19K lines of T-SQL embedded in PowerBuilder string literals. Deploys stored procedures to the tenant database, manages plugin version, builds DB objects, deploys reports.

DataWindows — Reports (Display Only)

File	Report Title	Data Source
d_powerfill_guide_report.srd	PowerFill guide	pfill_powerfill_guide
d_powerfill_recap.srd	PowerFill Trade Capacity Fulfillment Recap	pfill_trade_base
powerfill_switching_thumbnail.srd	Thumbnail recap for pool-switching recommendations	pfill_pool_guide (filter pool_action = 'Swapped In')
powerfill_pool_candidates.srd	Pool candidates composition	pfill_pool_guide + v_loan_loan_shipped
powerfill_existing_pool_disposition.srd	Disposition recommendations for existing pools	pfillv_existng_pool_disposition (view)
powerfill_pooling_guide.srd	Pooling guidance	pfill_pool_guide
cash_trade_slotting_guide.srd	Cash trade slotting (FlexRate ranges, settlement dates)	trade/loan joins
powerfill_tradeside_eval.srd	Template for Secondary Manager tradeside reports	varies
d_powerfill_tradeside_eval.srd	Tradeside evaluation	varies
d_powerfill_vmd_queries.srd	PS 7.0 VMD Search object syntax (for saved searches)	pxcat_vmd_*

DataWindows — Maintenance

File	Maintains
d_maint_lockdown_guide.srd	pfill_lockdown_guide (pool lockdown flags)
d_maint_powerfill_constraints.srd	pfill_constraints (constraint definitions)
d_maint_pfill_constraint_priority.srd	Constraint priority ordering
d_maint_powerfill_cost_carry.srd	pfill_carry_cost (carry cost curves)

DataWindows — Options and Lists

File	Role
d_powerfill_options.srd	Top-of-window options (scope, price mode, status, eligibility windows, BX price floor)
d_list_powerfill_inv_instr.srd	Investor instrument picker
d_list_powerfill_cost_carry_instr.srd	Cost/carry instrument picker
d_list_sec_rules_for_constraints.srd	Securitization rule picker for constraint tree

Package Manifest

File	Role
powerfill.pbg	Library group file listing all plugin objects

Database Tables — Owned by PowerFill

PowerFill creates and manages 17 tables prefixed pfill_*. Each tenant database gets these tables when the plugin is first deployed. The NVO's of_update_database() handles DDL deployment (n_cst_powerfill.sru lines 6094–6638).

2026-04-16 correction (Phase 1 planning, Architect): Original deep dive listed 13 tables. Direct extraction from the NVO DDL function revealed 17. The table previously called pfill_ect_params is actually pfill_epci_params (line 6094). Three tables were missing entirely from the earlier inventory: pfill_loan_constraint_pool, pfill_loan_ordering, pfill_payups. See the PowerFill Phase 1 plan and the "Phase-0 Truth Rot" antipattern nomination for the process lesson.

Table	NVO line	Row Scope	Purpose
pfill_constraints	6119	Configuration (persistent)	Constraint tree: investor → instrument → sub-constraint hierarchy
pfill_constraint_sec_rule_rel	6142	Configuration (persistent)	Links constraints to securitization rules (pscat_securitization_rules)
pfill_loan_constraint_pool	6163	Run intermediate (likely)	Loan-constraint-pool assignments produced during allocation (scope confirmation deferred to Phase 2)
pfill_lockdown_guide	6194	Configuration (persistent)	Pool/trade lockdown flags — tells PowerFill which pools/trades NOT to touch
pfill_carry_cost	6332	Configuration (persistent)	Carry cost curves by investor instrument (days × rate). Seed insert at NVO line 6660
pfill_trade_params	6309	Run-scoped (rebuilt)	Per-trade parameters (target, tolerance, settlement windows)
pfill_epci_params	6094	Run-scoped (rebuilt)	Embedded pay-up cash instrument parameters (EPCI — best guess: "Embedded Pay-up Cash Instrument"; keyed by cash_payup_instrument)
pfill_payups	6537	Configuration (persistent)	Pay-up pricing grid per investor instrument / coupon / loan-amount bucket
pfill_cash_market_map	6350	Run-scoped (rebuilt)	Mapping from loan instrument to cash grid / market instrument
pfill_trade_base	6488	Run output	Trade-level fulfillment status (pre-session need/space, post-session need/space)
pfill_powerfill_guide	6462	Run output	Core allocation output — every loan-trade pair with recommendation
pfill_pool_guide	6599	Run output	Pool-level output — every loan's pool action (Remain, Leave, Join, Switch)
pfill_cblock_guide	6226	Run output	"Cblock" guide (Contiguous block? Cash block? — to be confirmed)
pfill_trade_cblock_base	6253	Run output	Trade-level cblock base
pfill_loan2trade_candy_level_01	6426	Run intermediate	Loan-to-trade candidacy scoring, level 1 pass
pfill_loan_ordering	6393	Run intermediate	Ordered loan list per trade for allocation passes
pfill_kickout_guide_01	6367	Run output	Loans that failed allocation ("kickouts"), level 1

Note: the NVO also creates rmusr_payups (line 6564) in the rmusr_ namespace — a user-customizable Risk Manager table that shares pay-up semantics with pfill_payups. This table is NOT owned by PowerFill despite being created by the plugin; it is Risk Manager's data. Excluded from PowerFill's domain model.

Related Views

The NVO's of_update_database() creates two views (originally the Phase 0 deep dive only documented one; Phase 2 Primary-Source Verification found the forensics view):

• pfillv_existng_pool_disposition (defined in of_get_pfillv_existng_pool_disposition, NVO line 12485) — per-loan pool disposition joining rmcat_loan at-risk loans, loan, loan_shipped, and pfill_pool_guide. Drives the Existing Pool Disposition report.
• pfillv_pf_forensics_tradeside (defined in of_get_pfillv_pf_forensics_tradeside, NVO line 11187) — per-trade forensics joining pfill_trade_base, pfill_loan2trade_candy_level_01, pfill_powerfill_guide, pfill_cblock_guide, pfill_trade_params, and rmcat_ra_history_trades. Produces needless-orphan counts, pair-off volume, pair-off cost, and market price comparisons. Drives Phase 7 forensics reports.

Database Tables — Consumed by PowerFill (Read-Only)

PowerFill reads from ~20 tables across the core catalog:

Namespace	Tables	Purpose
Core loan	loan, loan_shipped (via v_loan_loan_shipped view)	Pipeline and shipped loan data
pscat_*	pscat_trades, pscat_trades_pools_relation, pscat_trade_cash_grid, pscat_pools, pscat_pool_security_relation, pscat_securities, pscat_pools_sec_rule_rel, pscat_instruments, pscat_guarantors, pscat_companies, pscat_inst_dde_links_multi, pscat_rm_zone_states, pscat_settlement_dates, pscat_loan_stages, pscat_comments, pscat_pair_offs	Secondary marketing catalog: trades, pools, instruments, securitization rules, settlement dates, pair-offs
rmcat_*	rmcat_loan, rmcat_bx_setup_instr_inv, rmcat_ra_history_loans, rmcat_ra_history_trades, rmcat_risk_analysis_history, rmcat_setup_risk_parameters, rmcat_todays_prices	Risk / BestEx setup: which instrument maps to which investor, eligibility, historical risk analysis, current prices
pxcat_*	pxcat_site_plugins, pxcat_database_info	Plugin version tracking, run metadata

The Main Window (w_powerfill.srw)

The window is titled "PowerFill Trades" and has a persistent layout:

1. Top options row — bound to d_powerfill_options DataWindow
2. Tab control — 5 tabs described below
3. Menu bar — m_powerfill with Pre-Process, PowerFill, Reports, and Constraint actions

Options Row Fields

User preferences are keyed under the "PowerFill" section and include:

Field	Meaning
scope	Which loans to include: closed-only vs closed+locked
price_value	"Price only" vs "Price + Carry" scoring mode
status_code	Minimum loan status required for eligibility
max_eligible_days	Maximum days to eligibility for inclusion
max_trade_settle_days	Maximum days to trade settlement for inclusion
eligible_settle_buffer_days	Buffer: loan eligible N days before trade settles
bx_price_floor	Minimum BestEx price for eligibility

These drive the stored procedure calls via parameters.

The Five Tabs

1. Guide (d_powerfill_guide_report)

Shows the core PowerFill output — the recommendation set. Loan-by-loan, trade-by-trade, with columns including:

• pa_key — pool action key (identifies the recommendation group)
• trade_id, trade_instrument, trade_amount
• loan_id, loan_amount, note_rate, rate (pass-through)
• pool_action — Remain, Leaving, Joining, Switching (and internal values like Swapped In)
• tolerance_amount, eligible_date, settlement_date, curr_status

2. Recap (d_powerfill_recap — "PowerFill Trade Capacity Fulfillment Recap")

Shows per-trade fulfillment status, before and after the PowerFill run:

• trade_id, instrument_name, settlement_date, trade_amount
• min_rate, max_rate (derived from assigned loans)
• pre_session_need, pre_session_space — fulfillment gap before PowerFill
• post_session_need, post_session_space — fulfillment gap after PowerFill

This answers the VP question: "How much trade capacity did PowerFill fill?"

3. Lockdown (d_maint_lockdown_guide)

Editable grid for pfill_lockdown_guide. Users flag pools/trades with lock_pool = 'y' to exclude them from the next run. Columns:

• pool_name, settlement_date, investor_instrument_name, trade_id
• designated_amount, trade_amount, lock_pool (y/n)

4. Constraints (uo_tab_powerfill_constraints)

Tree view editor for pfill_constraints + pfill_constraint_sec_rule_rel. Users define:

• Investor-level constraints ("Fannie Mae loans only")
• Instrument-level constraints ("FNMA 30yr fixed only")
• Securitization rule references ("must satisfy pscat_securitization_rules.rule_name = 'FNMA30Y'")
• Priority ordering (lower priority number = higher precedence)

Opens the modal w_maint_pfill_constraint_order.srw for bulk priority editing.

5. Cost and Carry (uo_tab_powerfill_cost_carry)

Editable grid for pfill_carry_cost. Carry cost curve per investor instrument — basically "if we hold this loan N more days, the cost is X basis points."

The Menu (m_powerfill.srm)

Inherits standard File / Edit / Window / Help from m_run_module_window.

PowerFill-specific items:

Menu	Item	Action Event
Constraints	Add Constraint…	ue_add_constraint
	Delete Constraint	ue_delete_constraint
	Expand All / Collapse All / Expand Current	ue_expand_all / ue_collapse_all / ue_expand_current
	Prioritize Constraints…	ue_prioritize_constraints (opens modal)
Actions	Pre-Process	ue_perform_preprocess
	PowerFill	ue_perform_powerfill
	Reports	ue_run_reports

The Engine — n_cst_powerfill.sru

This NVO is ~19,000 lines and contains the entire PowerFill algorithm as T-SQL stored procedure text embedded in PowerBuilder string literals. The procedures are deployed to the tenant database during plugin initialization (of_update_database → of_stored_procedures).

Key Procedures

Procedure	Role
psp_pfill_bx_cash_grids	Build BestEx cash grids for the current scope (optional pre-step)
psp_pfill_bx_settle_and_price	Compute settlement dates and prices from BestEx context
psp_add_to_pool_lockdown_guide	Apply lockdown rules to the working set
psp_pfill_ect_params	Compute embedded pay-up cash parameters
psp_pfill_trade_params	Compute per-trade parameters (target, tolerance, windows)
psp_powerfill_conset	Core allocation engine — constrained set allocation
psp_powerfillUE	User Event follow-up — post-processing after core allocation

Runtime Flow (from w_powerfill.srw ue_perform_powerfill)

1. If bx_price_floor is set: call psp_pfill_bx_cash_grids (refreshes price floors)
2. Call psp_pfill_bx_settle_and_price (settlement + price computation)
3. Call psp_powerfill_conset with options: scope, price mode, status, eligibility/settlement windows, BX floor
4. Call psp_powerfillUE with the same options
5. Refresh the Guide and Recap tabs (retrieve from pfill_powerfill_guide and pfill_trade_base)

The Core Allocation (psp_powerfill_conset) — High-Level Flow

The procedure's logic, derived from the embedded T-SQL:

1. Clear working sets — truncate run-scoped pfill_* tables
2. Build the cash market map — map loan instruments to investor cash grids using rmcat_bx_setup_instr_inv
3. Iterate constraint priority — walk pfill_constraints in priority order
4. For each constraint level:
   • Build loan-to-trade candidacy (pfill_loan2trade_candy_level_01) with pricing, carry, eligibility
   • Apply securitization rule filters (pscat_securitization_rules via pfill_constraint_sec_rule_rel)
   • Apply loan-stage filters (pscat_loan_stages)
   • Integrate lockdown constraints
   • Integrate carry cost adjustments
5. Allocate volume — multiple passes to fill trades within tolerance:
   • Exact fit pass
   • Best fit pass
   • Fill remaining pass
   • Orphan handling
6. Detect pool actions — compare current pool assignment (from pscat_trades_pools_relation) to proposed assignment, produce Remain / Leaving / Joining / Switching
7. Track kickouts — loans that couldn't be allocated go to pfill_kickout_guide_01
8. Write outputs — populate pfill_powerfill_guide, pfill_pool_guide, pfill_trade_base

Exact pass semantics and tolerance behavior are captured in the PowerFill Assumptions Log, since they're reverse-engineered rather than explicitly documented.

Pool Action Semantics

The pool_action column in pfill_pool_guide and pfill_powerfill_guide uses this vocabulary:

Value	Meaning
Remaining	Loan stays in its current pool — PowerFill confirms it's optimally placed
Leaving	Loan should be removed from its current pool (typically because a better placement exists OR it's no longer eligible)
Joining	Loan was previously unallocated; PowerFill allocates it to a new pool
Switching	Loan moves from Pool A to Pool B
Swapped In	Internal value — loan was "swapped in" to a pool as part of a switching operation (the destination side of a Switch)

The Switching Thumbnail report filters to pool_action = 'Swapped In' specifically, since that identifies loans arriving in new pools as a result of switches.

Reports Output

Report DataWindow	Shows
d_powerfill_recap	Per-trade capacity fulfillment before/after
powerfill_switching_thumbnail	Volume/value of loans PowerFill moved between pools
powerfill_pool_candidates	All loan candidates evaluated per trade (pre-allocation view)
powerfill_existing_pool_disposition	Per-existing-pool recommendations (keep/dissolve/adjust)
powerfill_pooling_guide	Detailed pooling recommendations
cash_trade_slotting_guide	Cash trades slotted by FlexRate and settlement date
d_powerfill_guide_report	Full guide — all recommendations

All reports are read-only; they query the pfill_* output tables populated by the engine run.

Integration Points

Upstream (PowerFill reads from)

• BestEx (rmcat_bx_setup_instr_inv, rmcat_todays_prices) — price context and instrument-investor setup
• Risk Manager (rmcat_loan, rmcat_ra_history_*) — risk analysis context
• Core Trading (pscat_trades, pscat_pools, pscat_trades_pools_relation) — trades and current pool state
• Securitization (pscat_securitization_rules, pscat_securities, pscat_pools_sec_rule_rel) — agency compliance rules. (The Phase 0 deep dive originally cited pscat_securities_sec_rules; the NVO primary source actually uses pscat_securitization_rules — confirmed in Phase 2 via direct query of the NVO at line 660 and the pssaas-db seed schema. pscat_securities and pscat_pools_sec_rule_rel are confirmed real tables per legacy/entity-relationship.md but are not yet in the dev seed; they will be added when Phase 3 or 6 needs them.)
• Plugin Framework (pxcat_site_plugins) — version tracking

Downstream (PowerFill writes to; nothing consumes pfill_* except PowerFill itself)

PowerFill is terminal — its output is the final recommendation set. The user (Secondary Marketing analyst) reviews the Guide tab and manually applies accepted recommendations by:

1. Using the Lockdown tab to commit specific recommendations
2. Running the core pool/trade-pool-relation update procedures (outside PowerFill)
3. Re-running BestEx and Risk analysis to see the impact

This means the PSSaaS port must preserve this review-and-apply pattern — PowerFill recommends, humans decide, downstream systems reflect the decision.

Sample Tenant Data

In PS_DemoData (the sanitized old-client database used for PSSaaS testing):

Table	Row Count
pfill_carry_cost	295 rows (configured carry cost curves)
pfill_trade_base	0 rows (no recent PowerFill run)
pfill_pool_guide	0 rows
pfill_powerfill_guide	0 rows

The lack of run output means this client either didn't use PowerFill regularly, or their last run was cleared. The carry cost configuration suggests PowerFill was set up — just not run recently.

Key Takeaways for PSSaaS Port

1. The engine is ~19K lines of T-SQL, not PowerBuilder code. The PB layer is shell + forms + DataWindows + menu glue. Porting strategy must address the T-SQL.

2. Preserve the "recommend, review, apply" workflow. PowerFill doesn't auto-commit changes. Users gate every recommendation. The SaaS equivalent must do the same.

3. Run output is ephemeral; configuration is persistent. pfill_* has two kinds of tables — the 4 configuration tables survive between runs; the 9 run-output tables get rebuilt each run.

4. Tight coupling to BestEx setup. PowerFill reads rmcat_bx_setup_instr_inv directly. The PSSaaS port must either keep this coupling or introduce a well-defined contract between the BestEx and PowerFill modules.

5. Lockdown is the human-in-the-loop mechanism. Users lock pools/trades BEFORE running PowerFill to tell it "don't touch these." Any SaaS equivalent must make lockdown editing first-class.

6. Pool action vocabulary is settled. Remaining, Leaving, Joining, Switching (and internal Swapped In) are the canonical states. The PSSaaS port uses the same terms.

7. Securitization rules are deal-breaker constraints. If a loan violates a pool's securitization rule, it cannot join that pool — no exceptions. This is the hardest kind of constraint and must be preserved exactly.

8. Carry cost is a scoring modifier, not a hard constraint. It affects which allocation wins when multiple are feasible, but never eliminates a candidate.

9. 19K lines of T-SQL have 15 years of tuning embedded. ADR-022 (algorithm approach) chooses to port verbatim rather than rewrite, precisely because we cannot know which lines embody critical domain knowledge vs which are accidental complexity.

10. No Tom/Greg available up-front. We interpret the code as the authoritative spec. Every interpretation is logged in the Assumptions Log with source citation, ready for future critique.