Skip to main content

Spec: Best Execution Engine

Status: Approved Author: Kevin Sawyer (Product Owner) + AI Architect Date: 2026-03-17

Overview​

The Best Execution (BestEx) engine is the analytical core of PowerSeller SaaS. Given a portfolio of mortgage loans and a set of investor pricing scenarios, it determines the optimal investor, delivery window, and servicing strategy for each loan — maximizing the lender's profit across the entire pipeline. The engine evaluates every permutation of loan × investor × instrument × delivery window × servicing option, computes a total price incorporating base pricing, feature adjustments, servicing values, buy-up/buy-down optimization, warehouse income, finance costs, and early delivery credits, then ranks every scenario to surface the best execution path for each loan. This is the Tier 1 differentiator identified in the solution strategy — the first feature to be ported from PowerBuilder to .NET 8.

Business Context​

Best Execution analysis is the primary reason customers buy PowerSeller. Secondary marketing desks at mortgage lenders run BestEx daily (often multiple times) to decide where to deliver each loan for maximum profitability. A single basis point improvement across a $500M pipeline can represent $50,000 in additional revenue.

The legacy PowerBuilder BestEx engine has been in production for 15+ years and represents the deepest domain logic in the system. It is tightly coupled to DDE price feeds, stored procedures, and PowerBuilder DataWindows. Porting it to a modern API-first architecture enables:

  • Cloud execution — Run analysis from anywhere, not just the desktop app
  • Scalability — Process larger portfolios faster via modern compute
  • Integration — Feed results to the pooling engine, risk analysis, and downstream systems via API
  • Auditability — Full analysis history with immutable result snapshots
  • Dual UX — Serve both new customers (modern dashboard) and migrating power users (dense grid)

This spec covers the BestEx analysis engine itself. Price ingestion (the modern replacement for DDE feeds) is a separate Tier 2 concern — the engine consumes validated prices from rmcat_todays_prices regardless of how they got there.

PowerBuilder Reference​

The legacy BestEx implementation spans two module folders:

FolderKey FilesRole
rmbestx/NVO objects, DataWindowsBestEx analysis pipeline — the 24-step process, profile management, ranking, result posting
pricing/NVO objects, DataWindowsPrice ingestion, validation, par rate computation, DPC calculations, feature adjustments

The 24-step pipeline is orchestrated by a master NVO that calls each step in sequence. Profile configuration is managed through DataWindow-backed maintenance windows. Results are posted to rmcat_bestex_analysis and ranked via SQL RANK() functions.

See the Pricing Module Deep Dive for the full reverse-engineered analysis of the legacy code.

Requirements​

Profile Management​

  • Create, read, update, and delete BestEx analysis profiles
  • Each profile has a name, type, description, and configuration flags (archive_results, run_loan_eligibility)
  • Configure profile strategies: buy-down strategy, buy-up strategy, excess servicing strategy
  • Configure profile parameters: warehouse loan flag, price adjustment toggle, MBS delivery position upper limit, delivery window upper limit, loan filter SQL syntax
  • Associate instrument/investor mappings to profiles (rmcat_bx_setup_instr_inv)
  • Configure lock windows per profile (list of lock_window_in_days values)
  • Configure loan statuses and their sequence for eligibility filtering
  • Support profile selection with quote expiration date and MBS optimization flag
  • Validate profile completeness before allowing analysis run (must have at least one instrument mapping, one lock window, and valid parameters)

Loan Selection​

  • Select loans for analysis from the active pipeline (loan table, 157 columns)
  • Support explicit loan selection (user-picked subset via rmcat_bx_selected_loans)
  • Support filter-based selection via SQL syntax defined in profile parameters
  • Extract key loan attributes: loan_id, loan_amount, note_rate, instrument_name, pool_name, state, lock_expiration_date, close_date, lock_date, curr_status, borr_credit_score, ltv, cltv, purpose_code, occupancy_code, property_type, amort_type, num_of_units, product_code
  • Run loan eligibility analysis when profile flag is set
  • Identify and exclude unqualified loans with error descriptions

Price Ingestion (Read-Only Consumer)​

  • Read validated prices from rmcat_todays_prices (the engine does not own price ingestion)
  • Read par rates from rmcat_todays_par_rates
  • Read feature adjustment rules from rmcat_price_adjustments and rmcat_price_adjustment_values
  • Read profit margin configuration from rmcat_profit_margin
  • Read price adjustment group definitions from rmcat_price_adjustment_groups
  • Support profile-specific price overrides via rmcat_bx_todays_prices
  • Support servicing source definitions via rmcat_bx_svcng_source_defs

Analysis Pipeline (7 Phases, 24 Steps)​

Phase 1: Setup​

  • Step 1 — Clear analysis tables: Truncate rmcat_bestex_analysis and rmcat_bestex_analysis_error for the current profile run
  • Step 2 — Import loans: Load selected loans into the analysis working set
  • Step 3 — Analyze loan eligibility: Evaluate each loan against profile criteria (status, instrument, investor eligibility)
  • Step 4 — Identify unqualified loans: Move ineligible loans to error table with descriptive reason codes

Phase 2: Pricing Windows​

  • Step 5 — Calculate pricing windows: Determine available delivery windows for each investor instrument
  • Step 6 — Calculate lock windows: Cross-reference lock window configuration with loan lock dates to determine eligible windows
  • Step 7 — Analyze pooled loan eligibility: Check eligibility for loans already assigned to pools (pool name, program, master commitment constraints)
  • Step 8 — Calculate eligible dates: Compute settlement dates and market dates for each scenario

Phase 3: Rate Optimization​

  • Step 9 — Determine optimal buy-up/buy-down servicing: Evaluate servicing strategies per the profile's buy-up/buy-down/excess strategy configuration
  • Step 10 — Compute pass-through scenarios: Calculate pass-through rates for each loan/investor/instrument combination based on note rate, guarantee fee, and servicing fee

Phase 4: Value Components​

  • Step 11 — Get guarantee fee and servicing values: Look up guarantee fees and compute servicing values per investor/instrument
  • Step 12 — Calculate feature adjustments: Apply price adjustment rules by sequence number priority (lowest sequence wins within group)
  • Step 13 — Get forward coupon prices: Retrieve or interpolate forward prices for the computed coupon rates

Phase 5: Income and Cost​

  • Step 14 — Calculate early delivery credits: Compute coupon price adjustments for early delivery scenarios
  • Step 15 — Calculate warehouse income: Compute income earned while holding loans in warehouse (based on warehouse rate × days to hold)
  • Step 16 — Calculate finance cost: Compute borrowing cost for warehouse financing (based on finance rate × days to hold)
  • Step 17 — Calculate junk fees: Sum flat fees and point fees applicable to each scenario

Phase 6: Final Calculations​

  • Step 18 — Calculate adjustment caps: Apply maximum/minimum bounds to feature adjustments
  • Step 19 — Calculate price caps and total price: Compute total price per the total price formula, applying investor max price caps where defined
  • Step 20 — Calculate cost basis: Determine the accounting cost basis for gain/loss calculation
  • Step 21 — Calculate unit gains: Compute profit_price and profit_amount per scenario

Phase 7: Ranking and Output​

  • Step 22 — Post result set and rank scenarios: Insert results into rmcat_bestex_analysis and apply ranking criteria
  • Step 23 — Populate report tables: Aggregate results for reporting views
  • Step 24 — Archive results: Persist analysis snapshot to history when archive_results is enabled

Ranking​

  • Rank scenarios per loan using configurable ranking criteria
  • Support multiple ranking dimensions applied in priority order:
    • tpe — Total price excluding factor (descending)
    • tpi — Total price including factor (descending)
    • ipf — Investor price factor
    • inv — Investor rank from counterparty configuration
    • del — Delivery window in days (ascending — shorter is better)
    • edc — Early delivery credit (descending)
  • Compute rank_number (overall rank per loan), rank_per_investor, and rank_per_lock_window
  • Ranking is implemented via SQL RANK() / DENSE_RANK() window functions

Error Handling​

  • Capture per-loan errors with: description, profile_name, loan_id, instrument_name, investor_instrument_name, lock_window_in_days, pricing_window_in_days
  • Non-fatal errors (loan excluded from one scenario but valid in others) do not halt the run
  • Fatal errors (profile misconfiguration, no valid prices) abort the run with a clear error response
  • All errors are queryable via the API after the run completes
  • Common error categories: no price available, loan ineligible for instrument, lock expired, missing feature adjustment data, invalid servicing configuration

Archiving​

  • Archive analysis results when archive_results flag is set on the profile
  • Each archived run gets a unique run identifier with timestamp
  • Archive stores the full result set (all 67 columns of rmcat_bestex_analysis)
  • Support querying analysis history by profile, date range, and loan
  • Archive is append-only — historical runs are never modified

Business Rules​

Price Validity​

  1. Prices must be no more than 7 days old from the analysis run date
  2. Only numeric, positive price values are accepted
  3. MBS, Whole Loan, and Best Efforts prices must be monotonic — higher rate must yield higher price; non-monotonic rows are excluded from analysis
  4. Each instrument/window combination requires at least 3 rate/price pairs to support interpolation
  5. Only active instruments with authorized hedging flag are included
  6. Prices exactly equal to 100.000 are excluded from par rate calculations (prevents pollution)
  7. Price source is tracked as 'd' (DDE/feed) or 'm' (modified/manual); modified prices are never auto-removed

Par Rate Computation​

  1. Par rate is the note rate at which an instrument prices at par (100)
  2. Standard interpolation formula:
par_rate = low_rate + (100 - low_price) × (high_rate - low_rate) / (high_price - low_price)
  1. Daily price change for window interpolation:
daily_price_change = (price_from - price_to) / (window_to - window_from)
interpolated_price = days_to_delivery × daily_price_change + base_price
  1. Interpolated pricing applies only to instruments with interpolate_prices = 'y' and only for FRE, FNM, FHLB guarantors
  2. Both pass-through and note-rate cash grid types are supported for interpolation

DPC (Dollar Price Change)​

  1. Raw DPC calculation:
lower_rate_dpc = (next_price - current_price) / (next_rate - current_rate)
higher_rate_dpc = (current_price - prev_price) / (current_rate - prev_rate)
  1. Continuum DPCs extend raw DPCs to arbitrary rate shifts using increment bias weighting:
potential_dpc = primary_dpc × increment_bias + contrary_dpc × (1 - increment_bias)
computed_dpc = |achieved_value_shift + potential_dpc × residual_shift| / |total_shift|

Feature Adjustments​

  1. Adjustments are grouped by price_adjustment_group and price_adjustment_name
  2. Each adjustment has a sequence_number; when multiple adjustments in the same group match a loan, only the lowest sequence number applies
  3. Three adjustment dimensions per rule: price_adjustment, rate_adjustment, fee_adjustment
  4. BestEx uses the "BestEx" adjustment context (one of 9 defined contexts in the system)
  5. Adjustments are matched to loans via SQL syntax rules that evaluate loan attributes (credit score, LTV, CLTV, purpose, occupancy, property type, etc.)
  6. Adjustment caps may limit the total adjustment applied in either direction

Servicing Strategies​

  1. Three configurable strategies per profile: buy-up strategy, buy-down strategy, excess servicing strategy
  2. Buy-up: Lender pays a higher guarantee fee in exchange for a lower coupon rate and higher security price
  3. Buy-down: Lender receives a lower guarantee fee resulting in a higher coupon rate and lower security price
  4. max_fee_buy_up and max_fee_buy_down define the bounds for each investor/instrument
  5. excess_servicing_ratio and actual_excess_servicing track the servicing spread retained by the lender
  6. servicing_uvalue captures the unit value of the servicing right
  7. Buy-up/buy-down ratios are grouped via buyup_ratio_group_id and excess servicing grids via xs_grid_id

Total Price Formula​

  1. The total price is computed as:
calc_price = base_price
  + required_servicing
  + excess_servicing_value
  + msr_price_adjustment
  - msr_fee_adjustment_normalized
  + buyup_value
  - junk_fee
  + total_price_adjustment
  - fee_adjustment_normalized
  + early_delivery_credit
  1. If an investor max price cap exists: calc_price = MIN(calc_price, investor_max_price)
  2. Final total price adds carry: total_price = calc_price + warehouse_income - finance_cost
  3. Each component must be computed to full decimal precision; rounding occurs only on final profit_amount

Unit Gain Formula​

  1. Unit gain (profit price): profit_price = total_price - cost_basis
  2. Profit amount: profit_amount = ROUND((total_price - cost_basis) / 100 × loan_amount, 2)
  3. Cost basis is determined by the cost_basis_name configuration — may vary by investor or instrument

Warehouse Income and Finance Cost​

  1. Warehouse income: earned based on the loan's warehouse rate applied over days_to_hold
  2. Finance cost: borrowing cost based on the finance rate applied over days_to_hold
  3. days_to_hold is computed from the loan's close date (or current date if not closed) to the settlement date
  4. days_post_closing tracks the aging of closed loans

Early Delivery Credit​

  1. Early delivery credit is a coupon_price_adjustment applied when delivery occurs before the standard settlement date
  2. Credit amount depends on the number of days early and the coupon rate differential

Ranking Criteria​

  1. Ranking is applied per loan across all valid scenarios
  2. Profile-defined ranking criteria are applied in sequence as SQL RANK() window function ORDER BY clauses
  3. Three rank columns are computed: overall (rank_number), per-investor (rank_per_investor), per-lock-window (rank_per_lock_window)
  4. Rank #1 per loan represents the best execution scenario
  5. Ties are broken by the next ranking criterion in the configured sequence

API Design​

Endpoint Map​

Request / Response Shapes​

// --- Profile Management ---

interface BestExProfile {
  profileName: string;
  profileType: string;
  description: string;
  archiveResults: boolean;
  runLoanEligibility: boolean;
}

interface ProfileStrategies {
  profileName: string;
  buydownStrategy: string;
  buyupStrategy: string;
  excessStrategy: string;
}

interface ProfileParameters {
  profileName: string;
  warehouseLoan: boolean;
  priceAdjustment: boolean;
  mbsDeliveryPosUpperLimit: number;
  windowDeliveryUpperLimit: number;
  loanFilterSyntax: string | null;
}

interface ProfileInstrumentMapping {
  profileName: string;
  instrumentName: string;
  programName: string;
  investorInstrumentName: string;
}

interface ProfileLockWindow {
  profileName: string;
  lockWindowInDays: number;
}

// --- Analysis Execution ---

interface RunAnalysisRequest {
  profileName: string;
  quoteExpirationDate: string | null;   // ISO 8601
  mbsOptimized: boolean;
  selectedLoanIds: number[] | null;     // null = use profile filter
}

interface RunAnalysisResponse {
  runId: string;                        // GUID
  profileName: string;
  status: "queued" | "running" | "completed" | "failed";
  submittedAt: string;                  // ISO 8601
  estimatedLoanCount: number;
}

interface AnalysisStatus {
  runId: string;
  profileName: string;
  status: "queued" | "running" | "completed" | "failed";
  currentPhase: number | null;          // 1-7
  currentStep: number | null;           // 1-24
  stepDescription: string | null;
  loansProcessed: number;
  totalLoans: number;
  errorsCount: number;
  submittedAt: string;
  startedAt: string | null;
  completedAt: string | null;
  elapsedMs: number | null;
}

// --- Analysis Results ---

interface AnalysisResultsRequest {
  page: number;                         // 1-based
  pageSize: number;                     // default 50, max 500
  sortBy: string;                       // column name
  sortDirection: "asc" | "desc";
  filters: ResultFilter[];
}

interface ResultFilter {
  field: string;
  operator: "eq" | "neq" | "gt" | "gte" | "lt" | "lte" | "in" | "contains";
  value: string | number | string[] | number[];
}

interface AnalysisResultRow {
  // Loan identity
  loanId: number;
  instrumentName: string;
  noteRate: number;
  loanAmount: number;
  state: string;

  // Investor / delivery
  investorId: number;
  investorInstrumentName: string;
  passThruRate: number;
  lockWindowInDays: number;
  pricingWindowInDays: number;
  mbsDeliveryPosition: number;
  settlementDate: string;
  marketDate: string;

  // Pricing components
  price: number;
  priceSource: string;
  priceAdjustment: number;
  rateAdjustment: number;
  feeAdjustment: number;
  couponPriceAdjustment: number;
  buyupBuydnFee: number;

  // Servicing
  servicingFee: number;
  guaranteeFee: number;
  servicingType: string;
  maxExcessServicing: number;
  actualExcessServicing: number;
  excessServicingRatio: number;
  excessServicingValue: number;
  servicingUvalue: number;
  actualFeeBuyUp: number;
  buyUpRatio: number;

  // Cost / income
  costBasis: number;
  costBasisName: string;
  warehouseIncome: number;
  financeCost: number;
  junkFeePaid: number;
  flatFee: number;
  pointFee: number;
  daysToHold: number;
  daysPostClosing: number;

  // Results
  investorAdjustedPrice: number;
  totalPrice: number;
  profitPrice: number;
  profitAmount: number;
  rankNumber: number;
  rankPerInvestor: number;
  rankPerLockWindow: number;

  // Commitment context
  guarantorName: string | null;
  masterCommitmentId: number | null;
  masterAgreementId: number | null;
  poolName: string | null;
  programName: string | null;
  zoneName: string | null;
  dealerName: string | null;
  packetName: string | null;
  contractName: string | null;
}

interface AnalysisResultsResponse {
  runId: string;
  profileName: string;
  runDate: string;
  totalRows: number;
  page: number;
  pageSize: number;
  rows: AnalysisResultRow[];
}

interface AnalysisSummary {
  runId: string;
  profileName: string;
  runDate: string;
  totalLoans: number;
  totalScenarios: number;
  totalErrors: number;
  totalProfitAmount: number;           // sum of rank-1 profit amounts
  averageProfitPerLoan: number;
  bestInvestorBreakdown: InvestorSummary[];
  elapsedMs: number;
}

interface InvestorSummary {
  investorId: number;
  investorName: string;
  loanCount: number;                   // loans where this investor is rank 1
  totalAmount: number;                 // sum of loan amounts
  averageTotalPrice: number;
  totalProfitAmount: number;
}

// --- Errors ---

interface AnalysisError {
  loanId: number;
  description: string;
  profileName: string;
  instrumentName: string | null;
  investorInstrumentName: string | null;
  lockWindowInDays: number | null;
  pricingWindowInDays: number | null;
}

interface AnalysisErrorsResponse {
  runId: string;
  totalErrors: number;
  errors: AnalysisError[];
}

// --- Loan Selection ---

interface SelectedLoansRequest {
  loanIds: number[];
}

interface LoanPreviewRequest {
  filterSyntax: string;
}

interface LoanPreviewResponse {
  matchingLoanCount: number;
  sampleLoans: LoanSummary[];          // first 25 matches
}

interface LoanSummary {
  loanId: number;
  loanAmount: number;
  noteRate: number;
  instrumentName: string;
  state: string;
  currentStatus: string;
  lockExpirationDate: string | null;
}

// --- Analysis History ---

interface AnalysisHistoryEntry {
  runId: string;
  profileName: string;
  runDate: string;
  totalLoans: number;
  totalScenarios: number;
  totalErrors: number;
  totalProfitAmount: number;
  elapsedMs: number;
  archived: boolean;
}

interface AnalysisHistoryResponse {
  entries: AnalysisHistoryEntry[];
  totalCount: number;
}

Endpoint Details​

MethodPathDescriptionAuth
GET/api/bestex/profilesList all profiles for the tenantTenant user
POST/api/bestex/profilesCreate a new profileTenant admin
GET/api/bestex/profiles/{name}Get profile detailTenant user
PUT/api/bestex/profiles/{name}Update profileTenant admin
DELETE/api/bestex/profiles/{name}Delete profile (soft delete if archived runs exist)Tenant admin
GET/api/bestex/profiles/{name}/strategiesGet profile strategiesTenant user
PUT/api/bestex/profiles/{name}/strategiesUpdate strategiesTenant admin
GET/api/bestex/profiles/{name}/parametersGet profile parametersTenant user
PUT/api/bestex/profiles/{name}/parametersUpdate parametersTenant admin
GET/api/bestex/profiles/{name}/instrumentsList instrument mappingsTenant user
PUT/api/bestex/profiles/{name}/instrumentsReplace instrument mappingsTenant admin
GET/api/bestex/profiles/{name}/lock-windowsList lock windowsTenant user
PUT/api/bestex/profiles/{name}/lock-windowsReplace lock windowsTenant admin
POST/api/bestex/analysis/runSubmit analysis run (async)Tenant user
GET/api/bestex/analysis/{runId}/statusPoll run statusTenant user
GET/api/bestex/analysis/{runId}/resultsGet results (paginated, filterable)Tenant user
GET/api/bestex/analysis/{runId}/errorsGet errors for a runTenant user
GET/api/bestex/analysis/{runId}/summaryGet run summary with investor breakdownTenant user
GET/api/bestex/analysis/historyList past runs (paginated)Tenant user
GET/api/bestex/loans/selectedGet currently selected loansTenant user
PUT/api/bestex/loans/selectedSet selected loansTenant user
POST/api/bestex/loans/previewPreview loans matching a filterTenant user

Async Execution Model​

BestEx analysis is compute-heavy (a 1,000-loan portfolio × 20 investors × 6 lock windows = 120,000 scenarios). The API uses an async request/response pattern:

  1. Client POSTs to /api/bestex/analysis/run — returns immediately with runId and status: "queued"
  2. Engine processes the run on a background thread (or via RabbitMQ job queue in the full profile)
  3. Client polls GET /api/bestex/analysis/{runId}/status for progress updates
  4. When status: "completed", client fetches results via GET /api/bestex/analysis/{runId}/results

Future enhancement: WebSocket or Server-Sent Events for real-time progress streaming.

Data Model​

Data Flow​

Table Classification​

Read-Only (Engine Consumes)​

TableDescriptionApprox. Rows
loanActive pipeline loansVaries by tenant (100–5,000)
rmcat_todays_pricesCurrent validated prices~92,000
rmcat_todays_par_ratesComputed par ratesVaries
rmcat_price_adjustment_groupsAdjustment group definitions~308
rmcat_price_adjustment_valuesAdjustment values by investor~8,257
rmcat_price_adjustmentsAdjustment rules~6,233
rmcat_profit_marginProfit margin config per investor/instrumentVaries

Read/Write (Profile Configuration)​

TableDescription
rmcat_bx_profile_namesProfile definitions
rmcat_bx_profile_strategiesProfile servicing strategies
rmcat_bx_setup_parametersProfile execution parameters
rmcat_bx_setup_instr_invProfile instrument/investor mappings
rmcat_bx_setup_lock_windowsProfile lock window configurations
rmcat_bx_setup_statusesProfile loan status filters
rmcat_bx_selected_loansUser-selected loan subset
rmcat_bx_selected_profilesActive profile selection
rmcat_bx_todays_pricesProfile-specific price overrides
rmcat_bx_svcng_source_defsServicing source definitions

Write (Engine Outputs)​

TableDescription
rmcat_bestex_analysisAnalysis results — 67 columns per scenario
rmcat_bestex_analysis_errorPer-loan error records
rmcat_pricing_analysis_historyRun history metadata

EF Core Entity Design​

The BestEx bounded context maps to these aggregate roots and entities:

// Expressed as C#-style pseudocode for spec purposes

// Aggregate root: BestExProfile
class BestExProfile {
  ProfileName: string           // PK
  ProfileType: string
  Description: string
  ArchiveResults: bool
  RunLoanEligibility: bool

  // Navigation
  Strategies: ProfileStrategies           // 1:1
  Parameters: ProfileParameters           // 1:1
  InstrumentMappings: ProfileInstrumentMapping[]  // 1:N
  LockWindows: ProfileLockWindow[]        // 1:N
  Statuses: ProfileStatus[]               // 1:N
}

// Aggregate root: AnalysisRun (created per execution)
class AnalysisRun {
  RunId: Guid                   // PK
  ProfileName: string
  RunDate: DateTime
  Status: AnalysisRunStatus     // enum
  CurrentPhase: int?
  CurrentStep: int?
  TotalLoans: int
  LoansProcessed: int
  ErrorsCount: int
  SubmittedAt: DateTime
  StartedAt: DateTime?
  CompletedAt: DateTime?

  // Navigation (large — loaded on demand, not eagerly)
  Results: AnalysisResult[]
  Errors: AnalysisError[]
}

// Entity: AnalysisResult (maps to rmcat_bestex_analysis row)
class AnalysisResult {
  // Composite identity within a run
  RunId: Guid
  LoanId: int
  InvestorId: int
  InvestorInstrumentName: string
  LockWindowInDays: int
  PricingWindowInDays: int

  // All 67 columns from rmcat_bestex_analysis
  // See AnalysisResultRow in API Design for full field list
}

// Entity: AnalysisError (maps to rmcat_bestex_analysis_error)
class AnalysisError {
  RunId: Guid
  LoanId: int
  Description: string
  ProfileName: string
  InstrumentName: string?
  InvestorInstrumentName: string?
  LockWindowInDays: int?
  PricingWindowInDays: int?
}

Key design decisions:

  • BestExProfile is the primary aggregate root for configuration — all profile sub-tables are managed through it
  • AnalysisRun is the aggregate root for execution — tracks state and owns results/errors
  • Results and errors are loaded lazily; the API paginates over them rather than loading entire sets into memory
  • All monetary fields (price, totalPrice, profitAmount, etc.) use decimal — enforced at the EF Core model level via HasPrecision(18, 6) or HasColumnType("decimal(18,6)")
  • The legacy schema is preserved initially (ADR-006); EF Core entity names map directly to existing table names via [Table("rmcat_bestex_analysis")] attributes

UX Notes​

Modern Mode (Default — New Customers)​

The modern BestEx experience is a guided workflow with progressive disclosure:

  1. Profile Setup Wizard — Step-by-step profile creation: name/description → select instruments/investors → configure lock windows → set strategies → review and save
  2. Loan Selection — Visual loan picker with summary cards showing count, total amount, average rate. Filter bar for common attributes. "Select All" and smart filters.
  3. Run Analysis — Single "Run Analysis" button with a progress indicator showing current phase/step. Estimated time remaining based on loan count.
  4. Results Dashboard — Summary cards at top: total profit, best investor breakdown (donut chart), average gain per loan. Below: a clean table showing rank-1 results per loan (best scenario only). Click to expand and see all scenarios for a loan.
  5. Drill-Down — Clicking a loan opens a detail panel showing all scenarios ranked, with the pricing component breakdown (base price, adjustments, servicing, carry, total). Visual comparison across investors.
  6. Scenario Comparison — Side-by-side comparison of 2–4 scenarios for a single loan, highlighting the differences in each component.
  7. Export — Export results to CSV/Excel with column selection.

Power Mode (Opt-In — Migrating Customers)​

The power mode BestEx experience mirrors the desktop app's dense data grid:

  1. Profile Management — Flat table of profiles with inline editing. All configuration on one screen with tabbed sub-sections (strategies, parameters, instruments, lock windows).
  2. Loan Selection — AG Grid with all loan fields visible, checkbox selection, column filtering/sorting. SQL syntax filter input for power users.
  3. Run Analysis — Toolbar button with console-style progress log showing each step.
  4. Results Grid — Full AG Grid showing all 67 columns of rmcat_bestex_analysis. Column grouping, pinning, filtering, sorting. Color-coded rank column. Row grouping by loan ID.
  5. Keyboard Navigation — Full keyboard support for grid navigation, selection, and actions.
  6. Export — Same as modern mode, plus the ability to export the raw result set with all columns.

Shared Interactions​

Both modes support:

  • Run history — View past analysis runs, compare results between runs
  • Error review — View loans excluded from analysis with error descriptions
  • Real-time status — Polling-based progress updates during analysis runs
  • Profile cloning — Duplicate a profile as a starting point for a new configuration

Acceptance Criteria​

Functional Correctness​

  1. Parity with PowerBuilder: BestEx results must match the PowerBuilder desktop app output for the same loan pool, profile configuration, and price snapshot — to within ±$0.01 per loan on profit_amount and ±0.001 on total_price
  2. All 24 steps execute: Every step in the 7-phase pipeline executes in sequence; skipping a step is a bug unless the profile configuration explicitly excludes it
  3. Ranking is deterministic: Given the same inputs, the same ranking criteria always produce the same rank assignments
  4. Error isolation: A loan failing in one scenario does not prevent its evaluation in other scenarios; errors are captured per-scenario, not per-loan
  5. Financial precision: All intermediate and final monetary calculations use decimal. No float or double anywhere in the pricing pipeline. Rounding to 2 decimal places occurs only on the final profit_amount

Profile Management​

  1. Profile CRUD operations work correctly with validation (name uniqueness, required fields)
  2. Deleting a profile with archived analysis runs performs a soft delete
  3. Profile validation prevents running analysis on an incomplete profile (no instruments, no lock windows, etc.)

Analysis Execution​

  1. Analysis runs asynchronously — the POST /run endpoint returns within 500ms
  2. Status polling returns accurate phase/step information during execution
  3. A 1,000-loan portfolio with 20 investors and 6 lock windows completes in under 60 seconds on standard infrastructure
  4. Concurrent analysis runs for the same tenant are queued (not executed in parallel) to prevent data corruption in shared analysis tables
  5. Analysis runs for different tenants execute independently (database-per-tenant isolation)

API Contract​

  1. All endpoints enforce tenant isolation — no cross-tenant data access under any circumstance
  2. Results pagination works correctly with filtering and sorting
  3. The summary endpoint correctly aggregates rank-1 results
  4. Error responses include structured error details per the AnalysisError shape
  5. API responses use camelCase JSON property naming

UX​

  1. Modern mode shows a clear progress indicator during analysis
  2. Power mode grid displays all 67 result columns with working sort/filter
  3. Both modes allow exporting results to CSV
  4. Drill-down from a loan to its full scenario list works in both modes

Regression​

  1. A reference test suite using a known loan pool and price snapshot produces bit-identical results across code changes (golden file test)
  2. The reference test suite is derived from actual production data (anonymized) from at least one existing PowerSeller customer

Out of Scope​

The following are explicitly not covered by this specification:

TopicReasonFuture Spec
Price ingestion pipelineTier 2 concern. BestEx consumes prices from rmcat_todays_prices regardless of source. The modern replacement for DDE feeds is a separate feature.Price Ingestion Engine spec
DDE replacementThe legacy DDE mechanism is a Windows-only technology. The SaaS replacement (REST/WebSocket price feeds) is a separate concern.Price Feed Integration spec
Pooling engine integrationBestEx identifies the best investor; pooling assigns loans to pools under that investor. The pooling engine is a separate Tier 1 feature.Pooling Engine spec
Risk analysis integrationBestEx results feed into risk analysis (position reconciliation, hedging). This integration is a Tier 2 concern.Risk Analysis Engine spec
Rate sheet generationCreating investor-facing rate sheets from pricing data is a downstream concern.Rate Sheet spec
What-If analysisHypothetical scenario analysis using phantom trades is a separate feature that builds on BestEx.What-If Analysis spec
Bulk sales / bid packagingBulk loan sales to investors use BestEx data but have their own workflow.Bid Packaging spec
User authentication / authorizationTenant isolation and RBAC are cross-cutting concerns defined in ADR-013.Identity spec
Price adjustment rule managementCRUD for feature adjustment rules is a separate admin feature. BestEx only reads them.Price Adjustments Admin spec

Pipeline Flow (Implementation View)​