Deep Dive: Desktop App Plugin Architecture
Plugin Architecture Overview
The PowerSeller Desktop App supports 32 plugins distributed as separate PBL (PowerBuilder Library) files. Plugins extend the application with investor-specific rate sheet parsing, bid management, pool fill optimization, and buy-up/buy-down grid conversion — all without modifying the core application.
Plugin Framework
Every plugin follows a standard structural pattern:
| Component | Role |
|---|---|
NVO (n_cst_rate_sheet_<vendor>) | Non-Visual Object containing all business logic — Excel parsing, HTTP calls, data transformation |
Window (w_<vendor>) | Sheet window opened inside the MDI frame |
Menu (m_plugin) | Dynamic menu item added to the Plug-ins menu at startup |
| DataWindows | .srd objects for display grids and report output |
| Tab UOs | User Object tabs for multi-panel layouts within the window |
The base framework lives in plugin/n_cst_rate_sheet_fn.sru, which provides shared pricing merge logic used by all 26 rate sheet plugins. Key shared functions:
| Function | Purpose |
|---|---|
of_update_pricing() | Normalizes vendor-specific staging data into pscat_inst_dde_links_multi |
of_apply_adjustments() | Applies post-merge price adjustments |
of_apply_global_adjustment_value() | Applies site-wide global adjustments |
Plugin Registration and Licensing
Plugins register in pxcat_site_plugins with the following record structure:
| Column | Purpose |
|---|---|
plugin_id | Unique identifier |
window_name | PowerBuilder window class to open |
menu_label | Display text in Plug-ins menu |
menu_order | Sort position |
current_version | Version string |
expiration_date | License expiration date |
data_mgr_association | Available when Data Manager is licensed |
secondary_mgr_association | Available when Secondary Manager is licensed |
risk_mgr_association | Available when Risk Manager is licensed |
post_closing_association | Available when Post-Closing Manager is licensed |
app_runner_association | Available when AppRunner is licensed |
Plugins also participate in the macro automation system via pxcat_macro_modules and pxcat_macro_module_plugins, allowing scheduled batch execution of plugin operations.
Plugin Lifecycle
BidMgr (Bid Management) — The Key Discovery
BidMgr is the single most important plugin for understanding the PSX relationship. Analysis of the source code reveals that BidMgr and the core bid_pkg module serve fundamentally different purposes — a distinction that was invisible from the module dependency graph alone.
BidMgr vs. bid_pkg: Two Different Directions
| Aspect | BidMgr (plugin) | bid_pkg (core module) |
|---|---|---|
| Direction | Inbound — seller loan tapes come IN to the platform | Outbound — loans go OUT to investor bids |
| Business context | A correspondent lender or aggregator receives loan tapes from sellers and needs to price them competitively | A lender packages pools of owned loans and solicits bids from investors |
| Tables | psbid_loans, psbid_loan_confirm, psbid_tracking | rmcat_bidpkg_*, rmcat_bx_bidpkg_* |
| Workflow | Import tape → BestEx/inquiry analysis → payup adjustments → margin rules → accept to pipeline | Package pools → solicit bids → select winner → settle |
| WTPO production usage | Active — 6,592 loans, 42,732 confirmations | Empty tables — no production data |
| Complexity | Full ETL pipeline with map-driven field translation, Excel OLE parsing, pricing integration | Simpler pool-to-bid packaging flow |
BidMgr Workflow
BidMgr Workflow Steps (Detailed)
| Step | Action | Details |
|---|---|---|
| 1 | Import seller loan tape | Excel file parsed via OLE automation. Map-driven ETL translates seller-specific column layouts into psbid_loans |
| 2 | Assign bid_id | Format: YYYYMMDD + sequence number. Recorded in psbid_tracking |
| 3 | Seed confirmations | psbid_loan_confirm populated from imported loans — one confirmation row per loan |
| 4 | Copy to loan_inquiry | Loans staged for pricing engine consumption |
| 5 | Run BestEx/inquiry analysis | Execute pricing against configured investor profiles |
| 6 | Apply payup adjustments | Per-loan adjustments from psbid_payup_percent |
| 7 | Apply margin rules | Margin calculations based on bid parameters |
| 8 | Update confirmations | psbid_loan_confirm updated with proxy MBS price, base price, and investor-specific prices |
| 9 | Accept to pipeline (optional) | Loans inserted into the main loan table with company suffix rules governing the company field value |
| 10 | Export results | Output in CSV, Excel, PDF, XML, or via email |
BidMgr Data Model
| Table | Purpose | WTPO Row Count |
|---|---|---|
psbid_loans | Imported seller loan tape data | 6,592 |
psbid_loan_confirm | Pricing confirmations per loan | 42,732 |
psbid_tracking | Bid assignment tracking (bid_id → date → status) | Active |
psbid_payup_percent | Payup adjustment percentages | Configured |
psbid_column_map | Seller-specific Excel column mapping definitions | Configured |
psbid_margin_rules | Margin calculation rules | Configured |
psbid_export_config | Export format and destination settings | Configured |
psbid_company_suffix | Company field suffix rules for pipeline acceptance | Configured |
Why This Matters
BidMgr implements the exact workflow that PSX (Xarbi) replaces:
- A seller submits a loan tape
- The system normalizes the seller's field layout
- Pricing runs against available investors
- Results are returned as confirmations
The difference: BidMgr uses manual SQL conversion rules per seller and Excel OLE parsing with hardcoded column positions. PSX uses automatic field detection, a declarative stacking engine, and anonymous marketplace matching.
Rate Sheet Plugins (26 Investor-Specific Parsers)
Common Architecture
Every rate sheet plugin follows the same structural pattern:
Data flow:
- Per-investor NVO (
n_cst_rate_sheet_<vendor>) loads the investor's rate sheet via Excel OLE automation or HTTPS/XML - Parsed data goes to a per-investor staging table (
psb2b_pricing_<vendor>) - Shared
n_cst_rate_sheet_fn.of_update_pricing()normalizes staging data intopscat_inst_dde_links_multi of_apply_adjustments()andof_apply_global_adjustment_value()apply post-processing
Complete Plugin Inventory
| # | Plugin PBL | Investor | Parse Format | Staging Table |
|---|---|---|---|---|
| 1 | ahmratesheet.pbl | AmeriHome Mortgage (AHM) | Excel OLE — CoverSheet + Base Prices with dynamic width | psb2b_pricing_ahm |
| 2 | amfratesheet.pbl | AmeriFirst Financial (AMF) | Excel OLE | psb2b_pricing_amf |
| 3 | caliberratesheet.pbl | Caliber Home Loans | Excel OLE | psb2b_pricing_cal |
| 4 | famcratesheet.pbl | Finance of America Mortgage (FAMC) | Excel OLE | psb2b_pricing_famc |
| 5 | fhlbratesheet.pbl | Federal Home Loan Bank (FHLB) | Excel OLE | psb2b_pricing_fhlb |
| 6 | fhlbcincinnati.pbl | FHLB Cincinnati | Excel OLE | psb2b_pricing_fhlb_cin |
| 7 | fhlbindianapolis.pbl | FHLB Indianapolis | Excel OLE | psb2b_pricing_fhlb_ind |
| 8 | flagstarratesheet.pbl | Flagstar Bank | Excel OLE | psb2b_pricing_flagstar |
| 9 | fmcratesheet.pbl | Federal Mortgage Corp (FMC) | Excel OLE | psb2b_pricing_fmc |
| 10 | fnm_fre_b2b.pbl | Fannie Mae / Freddie Mac B2B | HTTPS/XML API (MBS pricing) | psb2b_pricing_fnm, psb2b_pricing_fre |
| 11 | jpmcbratesheet.pbl | JPMorgan Chase Bank (JPMCB) | Excel OLE | psb2b_pricing_jpmcb |
| 12 | masshousingratesheet.pbl | MassHousing | Excel OLE | psb2b_pricing_mh |
| 13 | mrcratesheet.pbl | Mortgage Research Center (MRC) | Excel OLE | psb2b_pricing_mrc |
| 14 | nrzratesheet.pbl | New Residential Investment (NRZ) | Excel OLE | psb2b_pricing_nrz |
| 15 | phmratesheet.pbl | PennyMac (PHM) | Excel OLE — flat Price tab | psb2b_pricing_phm |
| 16 | phhratesheet.pbl | PHH Mortgage | Excel OLE | psb2b_pricing_phh |
| 17 | phlratesheet.pbl | Plaza Home Lending (PHL) | Excel OLE | psb2b_pricing_phl |
| 18 | pnmacratesheet.pbl | PennyMac (PNMAC) | Excel OLE | psb2b_pricing_pnmac |
| 19 | radianratesheet.pbl | Radian Group | Excel OLE | psb2b_pricing_radian |
| 20 | rwtratesheet.pbl | Redwood Trust (RWT) | Excel OLE | psb2b_pricing_rwt |
| 21 | tmsratesheet.pbl | The Money Source (TMS) | Excel OLE | psb2b_pricing_tms |
| 22 | truistratesheet.pbl | Truist Financial | Excel OLE — hierarchical labeled rows | psb2b_pricing_truist |
| 23 | usbratesheet.pbl | U.S. Bank (USB) | Excel OLE | psb2b_pricing_usb |
| 24 | wfratesheet.pbl | Wells Fargo | Excel OLE | psb2b_pricing_wf |
| 25 | plugin.pbl | Base plugin framework | N/A — shared functions | N/A |
| 26 | powerfill.pbl | PowerFill pool optimization | N/A — optimization engine | pfill_* |
The fnm_fre_b2b.pbl plugin is unique: it uses HTTPS/XML API calls instead of Excel OLE automation. All other rate sheet plugins parse Excel files downloaded by users or received via email.
What Varies Per Investor
Every investor publishes rate sheets in a different Excel layout, requiring custom PowerScript parsing logic for each:
| Investor | Layout Complexity |
|---|---|
| PennyMac | Flat Price tab — single worksheet, columnar rates × prices |
| Truist | Hierarchical labeled rows — rate categories with nested sub-sections |
| AmeriHome | CoverSheet + Base Prices — multi-worksheet with dynamic column width based on product count |
| FHLMC (via fnm_fre_b2b) | 4 worksheets — separate tabs for 15yr, 20yr, 30yr fixed and ARM products |
| Flagstar | Multi-tab with LLPA adjustments embedded in separate worksheets |
| JPMCB | Complex grid format with product/coupon matrix and nested adjustment tables |
This per-investor parsing complexity is the core problem: every new investor requires a new PBL, custom PowerScript parsing code, a new staging table, and QA testing against that investor's actual rate sheet format. Format changes by the investor require code updates.
PowerFill (Pool Fill Optimization)
Purpose
PowerFill optimizes loan-to-trade allocation under configurable constraint sets. Given a set of open trades with target amounts and a pool of eligible loans, PowerFill determines the optimal loan allocation that minimizes carry cost and maximizes fill rates while respecting securitization rules.
Architecture
The heavy optimization logic lives in SQL Server stored procedures — approximately 19,000 lines of T-SQL — with the PowerBuilder UI serving primarily as a configuration and execution front-end.
Data Model
| Table | Purpose |
|---|---|
pfill_constraints | Constraint definitions (loan eligibility rules per trade/pool) |
pfill_guides | Fill priority guide rankings |
pfill_trade_params | Trade-level parameters (target amount, tolerance, priority) |
pfill_carry_cost | Carry cost tables for optimization scoring |
pfill_loan_ordering | Loan sort/priority rules for allocation sequencing |
Key Capabilities
- Constraint-based allocation — loans must satisfy all applicable securitization rules for the target pool
- Tolerance limits — trades can be overfilled or underfilled within configurable tolerance bands
- Priority ordering — trades and loans are ranked to determine allocation sequence
- Carry cost optimization — minimizes warehouse financing cost by accelerating delivery of high-carry loans
- Multi-pass allocation — multiple passes with different strategies (exact fit, best fit, fill remaining)
The PSSaaS Pooling module would port PowerFill's optimization logic from T-SQL to .NET, enabling modern constraint solvers and better integration with the pooling engine's 12-source eligibility framework.
BUBD Grid Converter (Buy-Up/Buy-Down)
Overview
The BUBD Grid Converter parses agency buy-up/buy-down grid files and converts them to normalized rows used by the BestEx servicing strategy optimization. Buy-up/buy-down grids define the price adjustments a lender receives (or pays) for choosing a higher or lower servicing fee than the standard rate.
Supported Agency Formats
| Agency | Format | Source |
|---|---|---|
| FNMA (Fannie Mae) | Excel workbook | Published monthly by FNMA |
| FHLMC (Freddie Mac) | Excel workbook or XML | Published monthly by FHLMC |
| GMAC | Custom format | Investor-specific |
Data Flow
How BUBD Feeds BestEx
The normalized guarantee fee ratios in pscat_master_guar_fee_ratios are consumed by the BestEx engine during Phase 3 (Rate Optimization):
- For each loan, BestEx evaluates multiple servicing spread scenarios
- Each scenario references the BUBD grid to determine the price adjustment for that spread
- The optimal buy-up or buy-down level is selected based on net execution (total price including the BUBD adjustment)
- This feeds into pass-through rate computation and ultimately the scenario ranking
Key Details
- Supports multiple master commitments and grid months per import
- Grid structure: coupon rate × servicing spread matrix, where each cell contains a price adjustment (positive = buy-down credit, negative = buy-up cost)
- Imported monthly as agencies publish updated grids
Mapping to the PSSaaS + PSX Ecosystem
Plugin Replacement Strategy
| Plugin | Replaced By | How | Status |
|---|---|---|---|
| BidMgr | PSX (Xarbi) | Loan tape ingestion with automatic field detection, AI-driven pricing via stacking engine, anonymous marketplace matching, bid request generation | PSX v6.0 — pricing validated |
| Rate sheet plugins (24) | PSX | AI-driven rate sheet extraction replaces 24 custom Excel parsers; schema profiles replace per-investor code; 8-phase deterministic stacking replaces imperative pricing logic | PSX v6.0 — 90 ADRs |
| fnm_fre_b2b | PSSaaS Price Ingestion (future) | GSE API-based pricing feeds integrated directly into PSSaaS | Architecture phase |
| PowerFill | PSSaaS Pooling module (future) | Port 19K lines of T-SQL optimization logic to .NET with modern constraint solvers | Architecture phase |
| BUBD Grid Converter | PSSaaS Price Ingestion (future) | API-based agency fee grid import with automated monthly updates | Architecture phase |
| bid_pkg (core) | PSX (Xarbi) + PSSaaS Bid Manager (future) | PSX marketplace replaces solicited bid process; PSSaaS manages trade-side bid tracking | Architecture phase |
Desktop Plugin → PSX Capability Mapping
| Desktop Capability | PSX Replacement | Key Difference |
|---|---|---|
Manual Excel column mapping per seller (psbid_column_map) | field_registry.py — canonical field model with alias detection | Automatic detection vs. manual SQL rules |
| 24 hardcoded Excel OLE parsers (per-investor PowerScript) | AI-driven rate sheet extraction agents | AI adapts to format changes; no code updates needed |
| Imperative per-investor pricing logic in PowerScript | stacking_engine.py — 8-phase deterministic LLPA evaluation | Declarative stacking layers vs. imperative code per investor |
Per-investor staging tables (psb2b_pricing_*) | Schema profiles — buyer-specific configuration as data | Configuration vs. dedicated database tables |
| Manual BestEx run after import | PSSaaS BestEx enrichment wiring (PSX ADR-042) | Automated pricing feedback loop |
SQL conversion rules for field normalization (pxcat_conversion_values) | Recursive AI extraction with canonical field registry | AI-powered vs. rule-table-driven |
| No CRA/affordable housing analysis | CRA eligibility with e11tec integration | Built-in compliance layer |
PSX Capabilities Referenced
PSX (v6.0, 90 ADRs) replaces the plugin architecture with:
| PSX Component | What It Replaces | Description |
|---|---|---|
| field_registry.py | psbid_column_map, pxcat_conversion_values | Canonical field model with automatic alias detection — no manual column mapping |
| AI rate sheet extraction | 24 Excel OLE parser PBLs | Recursive AI agents parse any rate sheet format without hardcoded layout logic |
| stacking_engine.py | Per-investor pricing code in n_cst_rate_sheet_<vendor> | 8-phase deterministic LLPA evaluation with declarative layer composition |
| Schema profiles | Per-investor PBL + staging table | Buyer-specific configuration stored as data, not compiled code |
| Bid request generation | Manual phone/email bid solicitation | Automated bid creation and distribution through the marketplace |
| CRA eligibility (e11tec) | Not available in Desktop App | Community Reinvestment Act eligibility scoring |
| BestEx enrichment (ADR-042) | Manual BestEx run in Desktop App | Automated PSSaaS ↔ PSX pricing feedback loop |
| Anonymous matching | Direct investor relationships only | Marketplace model with privacy-preserving loan attributes |
Architectural Comparison
Summary Statistics
| Metric | Value |
|---|---|
| Total plugins | 32 PBLs |
| Rate sheet parsers | 24 investor-specific |
| Utility plugins | 2 (base framework, PowerFill) |
| Specialty plugins | 1 (BidMgr) + BUBD converter logic |
| Staging tables | 37 (psb2b_* prefix) |
| T-SQL in PowerFill | ~19,000 lines |
| Unique Excel layouts parsed | 24 (every investor different) |
| WTPO BidMgr loans processed | 6,592 loans, 42,732 confirmations |
| PSX ADRs replacing this architecture | 90 |