Entity-Relationship Model — SQL MI Ground Truth
Extracted directly from Azure SQL Managed Instance, database PS608 (Watermark TPO / MWFI Holdings tenant), March 2026. This replaces the earlier PB DataWindow approximation which only captured ~170 tables referenced by PowerBuilder UI code. The actual database contains 585 base tables and 258 views.
Schema Overview
| Metric | PB Extraction (old) | SQL MI Ground Truth |
|---|---|---|
| Base tables | ~170 | 585 |
| Views | 2 | 258 |
| Primary key constraints | — | 550 |
| Foreign key constraints | — | 283 (313 FK column mappings) |
| SQL triggers | — | 45 |
| Unique constraints | — | 13 |
| Default constraints | — | 7 |
| Table-valued functions | — | 7 |
| Stored procedures | — | 30 (mostly legacy dt_* source control) |
The PB extraction only surfaced tables touched by DataWindow SQL. The true schema is 3.4x larger — entire subsystems like B2B pricing integration (psb2b_*), ULDD delivery (psuldd_*), bid packaging (psbid_*, rmcat_bidpkg_*), SFAS hedge accounting (rmcat_sfas_*), and servicing valuation (rmcat_svcng_*) were invisible from the UI layer.
Table Prefix Taxonomy
All 585 tables organized by naming convention. Prefixes reveal the module architecture.
Core Business — pscat_* (115 tables)
The "PowerSeller Catalog" — master data and transactional tables for trading, pooling, pricing, pipeline management, and document tracking.
| Subdomain | Example Tables | Count |
|---|---|---|
| Instruments & Pricing | pscat_instruments (102 cols), pscat_instrument_dde_links, pscat_inst_dde_links_multi, pscat_dde_result_names, pscat_rate_sheet_configuration | ~12 |
| Trading | pscat_trades (66 cols), pscat_trade_counterparties, pscat_trade_arms, pscat_trade_builder, pscat_pair_offs, pscat_trades_best_efforts | ~18 |
| Pools & Securities | pscat_pools (110 cols), pscat_securities (41 cols), pscat_pool_fee_overrides, pscat_pool_security_relation | ~12 |
| Programs & Commitments | pscat_programs, pscat_master_commitments, pscat_master_agreements, pscat_guarantors | ~20 |
| Reservations | pscat_reservations, pscat_reserv_loan_rel, pscat_reserv_roll_rel, pscat_reservation_gfee | ~12 |
| Pipeline & Docs | pscat_loan_statuses, pscat_loan_stages, pscat_documents, pscat_loans_documents_relation | ~15 |
| Reference Data | pscat_companies, pscat_contacts, pscat_sources, pscat_conforming_loan_limits | ~10 |
| Securitization Rules | pscat_securitization_rules, pscat_guar_sec_rules_relation, pscat_inv_inst_sec_rule_rel | ~8 |
| Margin Management | pscat_margin_activity, pscat_margin_adjust, pscat_margin_tracking | ~4 |
Loan Pipeline — loan* (9 tables)
The loan lifecycle is modeled as parallel "snapshot" tables sharing an identical 157-column schema.
| Table | Cols | Purpose |
|---|---|---|
loan | 157 | Active pipeline — current state of each loan |
loan_inquiry | 157 | Rate-lock inquiry / what-if copy |
loan_notional | 157 | Notional (hypothetical) loans for risk modeling |
loan_modifications | 157 | Loan modification snapshots |
loan_shipped | 157 | Purchased/settled loans — final state |
loan_history | 150 | Historical snapshots on status changes |
loan_extended | 2 | Extension columns (currently just unused) |
loan_history_errors | 4 | Fallout processing error log |
loan_user | 7 | Per-loan document tracking status |
Risk Management — rmcat_* (178 tables)
The largest module by table count. Covers position analysis, best execution, hedge optimization, SFAS hedge accounting, fallout analysis, and MBS pricing.
| Subdomain | Example Tables | Count |
|---|---|---|
| Profile Configuration | rmcat_profile_names, rmcat_setup_profile_parameters, rmcat_setup_risk_parameters, rmcat_segment_names | ~12 |
| Loan/Trade Analysis | rmcat_loan (136 cols), rmcat_trades (79 cols), rmcat_bestex_analysis (67 cols) | ~12 |
| Best Execution (BX) | rmcat_bx_profile_names, rmcat_bx_setup_parameters, rmcat_bx_todays_prices | ~14 |
| SFAS Hedge Accounting | rmcat_sfas_loans, rmcat_sfas_trades, rmcat_sfas_sim_asset_groups, rmcat_sfas_parameters | ~22 |
| Pricing & Par Rates | rmcat_todays_prices, rmcat_par_rates, rmcat_dpcs, rmcat_mbs_dealer_prices | ~12 |
| Bid Packaging | rmcat_bidpkg_analysis, rmcat_bidpkg_investor_bids, rmcat_bidpkg_inv_price_grids | ~14 |
| Fallout Analysis | rmcat_fa_profiles, rmcat_fa_details, rmcat_fa_loan_stats, rmcat_fa_closing_ratios | ~10 |
| Servicing Valuation | rmcat_svcng_contracts, rmcat_svcng_packets, rmcat_svcng_msr_prices | ~14 |
| Adjustments | rmcat_adj_agg_values_* (10 variants), rmcat_adj_ind_values_* (10 variants) | ~20 |
| Price Adjustments | rmcat_price_adjustment_groups, rmcat_price_adjustments, rmcat_price_adjustment_values | ~6 |
| Position Reconciliation | rmcat_risk_pos_recon, rmcat_risk_pos_recon_arc, rmcat_risk_pos_recon_prior | ~12 |
| History | rmcat_ra_history_loans (137 cols), rmcat_ra_history_trades (80 cols) | ~8 |
| Sec 105/109 | rmcat_sec105, rmcat_sec109 and archive/prep variants | ~6 |
| Rate Cones | rmcat_rate_cones, rmcat_rate_cone_values | ~2 |
Risk Temp/Working — rmtmp_* (40 tables)
Scratch tables used during risk analysis calculations. Populated and cleared during processing runs.
Risk Archives — rmarc_* (7 tables)
Archived best execution, inquiry, and notional analysis results plus historical price/par-rate/yield data.
Risk Reports — rmrep_* (8 tables)
Pre-computed report data for position detail, proxy hedging, benchmark factors, and loan/trade preparation.
Risk Report SFAS — rmrpt_* (8 tables)
SFAS loan/trade volume reporting (current, prior, archived, summarized).
Risk User — rmusr_* (5 tables)
User-specific best execution output: all feasible executions, best execution, last/next top executions.
Platform / System — pxcat_* (105 tables)
Security, configuration, UI metadata, macros, import/export, and the Visual Metadata Designer (VMD) query builder.
| Subdomain | Example Tables | Count |
|---|---|---|
| Users & Security | pxcat_users, pxcat_groups, pxcat_groups_users, pxcat_password_history, pxcat_login_attempts | ~10 |
| Menus & Controls | pxcat_menus, pxcat_controls, pxcat_menus_controls, pxcat_groups_menus | ~6 |
| Import/Export | pxcat_external_file_maps, pxcat_external_db_maps, pxcat_external_xml_maps, pxcat_column_mappings | ~18 |
| VMD Query Builder | pxcat_vmd_queries, pxcat_vmd_selects, pxcat_vmd_wheres, pxcat_vmd_joins, pxcat_vmd_functions | ~25 |
| Data Pipelines | pxcat_vmd_data_pipelines, pxcat_vmd_data_pipeline_cols, pxcat_vmd_pipe_history | ~8 |
| Macros | pxcat_macros, pxcat_macro_modules, pxcat_macro_parameters | ~8 |
| Reference | pxcat_states, pxcat_counties, pxcat_holidays, pxcat_site_info | ~10 |
| Validation | pxcat_acceptable_values, pxcat_alarm_values, pxcat_conversion_values | ~10 |
| Metadata | pxcat_tables, pxcat_table_columns, pxcat_syntax_tables, pxcat_database_settings | ~8 |
B2B Pricing Integration — psb2b_* (37 tables)
Automated pricing feeds from GSEs and investors. One psb2b_pricing_* table per counterparty.
| Table Pattern | Purpose |
|---|---|
psb2b_pricing_fnm / _fre | Fannie Mae / Freddie Mac MBS pricing |
psb2b_pricing_ahm / _mh / _phm | Various investor whole-loan pricing |
psb2b_pricing_jpmcb / _usb / _truist | Bank counterparty pricing |
psb2b_pricing_cal / _mrc / _radian | Correspondent/MI pricing |
psb2b_bubd_fre | Freddie Mac buy-up/buy-down grids |
psb2b_products / _products_fnm / _products_aot | Product definitions per entity |
psb2b_instr_prod_rel / _inv_instr_prod_rel | Instrument-to-product mappings |
psb2b_income_limits | AMI income limits for affordable housing |
ULDD — psuldd_* (19 tables)
Uniform Loan Delivery Dataset — structured data for GSE loan delivery.
| Table | Cols | Purpose |
|---|---|---|
psuldd_loan_closed | 150 | Full loan data for closed delivery |
psuldd_borrower | 94 | Borrower demographics, HMDA data |
psuldd_property | 92 | Property details for delivery |
psuldd_borrower_ps6404 | 67 | PS-specific borrower extension |
psuldd_loan_arm | 29 | ARM-specific delivery fields |
psuldd_loan_servicing | 31 | Servicing transfer data |
psuldd_loan_party | 13 | Loan party roles |
| Others | — | Closing costs, escrow, down payments, investor features |
Bid Management — psbid_* (8 tables)
Whole-loan bid packaging: loan mapping to bids, confirmations, payup tracking.
Archive — psarc_* (20 tables)
Archived copies of core trading tables (psarc_trades, psarc_pools, psarc_securities, etc.) with identical schemas plus archive timestamps.
PowerFill — pfill_* (4 tables)
Synthetic trade optimization for pool fill: carry cost tables, synthetic trade base, and the PowerFill guide rankings.
PS Temp — pstmp_* (4 tables)
Working tables for fallout processing and archive exclusion.
PS User — psusr_* (5 tables)
User-workspace tables for ASC 815 (Sec 105) analysis and trade valuation options.
Watermark TPO — wtpo_* (2 tables)
Customer-specific tables for E11tec fraud detector integration: investor results and loan-level detector output.
Other — supp_*, ps_*, pbcat*, dtproperties (12 tables)
| Prefix | Count | Purpose |
|---|---|---|
pbcat* | 5 | PowerBuilder catalog metadata (legacy) |
ps_core_stats | 1 | Fallout analysis core statistics |
psrep_* | 1 | Margin report blocks |
pxarc_* | 1 | Activity log archive |
supp_tbl_companies | 1 | Supplemental company list |
dtproperties | 1 | SQL Server diagram properties |
Foreign_Key_Table | 1 | FK metadata view |
The loan Table — 157 Columns
The most important table in the system. Every active pipeline loan is a row. The same schema is replicated across loan_inquiry, loan_notional, loan_modifications, and loan_shipped.
Identification & Keys
| Column | Type | Description |
|---|---|---|
loan_id | varchar(30) | PK — Loan identifier |
agency_loan_number | varchar(30) | Agency (FHLMC/FNMA) loan number |
mers_mortgage_id | varchar(30) | MERS registration ID |
fha_case_number | varchar(15) | FHA case number |
servicing_number | varchar(20) | Servicing system loan number |
universalloanidentifier | varchar(45) | CFPB Universal Loan Identifier |
mkt_investor_loan_number | varchar(30) | Investor-assigned loan number |
mkt_commitment_number | varchar(30) | Investor commitment number |
mkt_confirmation | varchar(30) | Trade confirmation number |
bid_id | numeric(12,0) | Link to bid package |
Loan Amounts
| Column | Type | Description |
|---|---|---|
loan_amount | numeric(12,2) | Current loan amount |
orig_loan_amount | numeric(11,2) | Original loan amount |
baseloanamount | numeric(12,2) | Base loan amount (before MI/fees) |
issue_date_prin_bal | numeric(11,2) | Principal balance at issue date |
unsched_prin_bal | numeric(11,2) | Unscheduled principal balance |
purchase_price | numeric(11,2) | Property purchase price |
appraised_value | numeric(11,2) | Appraised value |
annualincome | numeric(16,2) | Borrower annual income |
borrower_income | numeric(10,2) | Borrower income |
Rates & Pricing
| Column | Type | Description |
|---|---|---|
note_rate | numeric(9,6) | Current note rate |
orig_interest_rate | numeric(6,4) | Original interest rate |
discount_points | numeric(6,3) | Discount points |
buy_price | numeric(8,5) | Buy/sell price |
srp_paid | numeric(8,5) | Service release premium |
mkt_sale_price | numeric(9,6) | Investor sale price |
arm_margin | numeric(6,3) | ARM margin |
life_of_loan_rate_cap | numeric(6,4) | Lifetime rate cap |
rate_cap_percent | numeric(9,6) | Periodic rate cap |
percent_increase | numeric(6,4) | Rate change percent |
Dates
| Column | Type | Description |
|---|---|---|
application_date | datetime | Loan application date |
lock_date | datetime | Rate lock date |
lock_expiration_date | datetime | Rate lock expiration |
close_date | datetime | Actual closing date |
estimated_close_date | datetime | Expected closing date |
note_date | datetime | Note date |
disbursement_date | datetime | Disbursement date |
mkt_lock_date | datetime | Investor lock date |
mkt_lock_expiration_date | datetime | Investor lock expiration |
mkt_purchase_date | datetime | Investor purchase date |
mkt_shipped_date | datetime | Shipping date |
first_adjustment_date | datetime | ARM first adjustment |
maturity_pi_date | datetime | Maturity date |
llpa_eligible_date | datetime | LLPA eligibility date |
thirdpartyloanacquisitiondate | datetime | Third-party acquisition date |
import_los_date | datetime | LOS import timestamp |
import_servicing_date | datetime | Servicing import timestamp |
time_stamp | datetime | Last modification timestamp |
mod_date | datetime | Modification effective date |
Borrower Information
| Column | Type | Description |
|---|---|---|
borr_first_name / last_name / mid_name / suffix | varchar | Primary borrower name |
borr_social_security | varchar(11) | Borrower SSN |
borr_credit_score | numeric(4,0) | Borrower credit score |
coborr_first_name / last_name / mid_name / suffix | varchar | Co-borrower name |
coborr_social_security | varchar(11) | Co-borrower SSN |
coborr_credit_score | numeric(4,0) | Co-borrower credit score |
coborr2_* / coborr3_* / coborr4_* | varchar | Additional co-borrowers (name + SSN) |
loanlevelcreditscorevalue | int | Representative credit score |
first_time_borrower | varchar(5) | First-time homebuyer flag |
selfemployedindicator | varchar(5) | Self-employed flag |
debt_to_income_ratio | numeric(6,3) | DTI ratio |
Property
| Column | Type | Description |
|---|---|---|
property_street_address | varchar(40) | Street address |
property_city | varchar(30) | City |
state | varchar(2) | State code |
property_zip_code | varchar(10) | ZIP code |
property_county | varchar(30) | County name |
statecountycode | varchar(5) | FIPS state+county |
property_type | varchar(20) | Property type |
num_of_units | numeric(3,0) | Number of units |
occupancy_code | varchar(20) | Occupancy type |
census_tract | varchar(11) | Census tract |
msa_number | varchar(12) | MSA number |
Loan Characteristics
| Column | Type | Description |
|---|---|---|
loan_type | varchar(30) | Conv/FHA/VA/USDA |
purpose_code | varchar(30) | Purchase/Refinance/etc. |
amort_type | varchar(25) | Fixed/ARM/etc. |
amortization_term | numeric(3,0) | Amortization term (months) |
orig_term | numeric(3,0) | Original term |
remaining_term | numeric(3,0) | Remaining term |
product_code | varchar(100) | Product code |
ltv | numeric(7,3) | Loan-to-value ratio |
cltv | numeric(7,3) | Combined LTV |
baseltv | numeric(7,4) | Base LTV |
index_code | varchar(100) | ARM index code |
cash_out_indicator | varchar(5) | Cash-out refinance flag |
refinancecashouttype | varchar(30) | Cash-out refinance type |
interestonly / int_only | varchar(5) | Interest-only flag |
subordinate_financing_flag | varchar(5) | Subordinate financing |
buydown_flag | varchar(5) | Rate buydown |
flood_insurance_flag | varchar(3) | Flood zone indicator |
loanaffordableindicator | varchar(5) | Affordable housing flag |
conformingloanlimitindicator | varchar(5) | Conforming limit flag |
jumboindicator | varchar(5) | Jumbo loan flag |
highbalanceindicator | varchar(5) | High-balance conforming |
enoteindicator | varchar(5) | eNote flag |
assumableindicator | varchar(5) | Assumable flag |
LienPriorityType | varchar(20) | Lien position |
doctype | varchar(30) | Documentation type |
Insurance / MI
| Column | Type | Description |
|---|---|---|
mi_coverage_percent | numeric(3,0) | MI coverage percentage |
mi_financed_single_prem | numeric(8,2) | Single premium MI amount |
upfront_mip_amt / annual_mip_amt | numeric(10,2) | FHA MIP amounts |
upfront_mip_rate / annual_mip_rate | numeric(9,6) | FHA MIP rates |
loss_mit_flag | varchar(3) | Loss mitigation flag |
ami_percent | numeric(5,2) | AMI percentage |
Investor / Marketing
| Column | Type | Description |
|---|---|---|
instrument_name | varchar(30) | FK → pscat_instruments |
pool_name | varchar(30) | FK → pscat_pools |
source_name | varchar(30) | Loan source/channel |
packet_name | varchar(30) | Servicing packet assignment |
mkt_investor | varchar(30) | Investor name |
mkt_marketing_program | varchar(30) | Marketing program |
mkt_master_commitment | varchar(30) | Master commitment |
asc_best_efforts_flag | varchar(1) | Best efforts delivery flag |
curr_status | varchar(50) | Current pipeline status |
branch | varchar(30) | Originating branch |
loan_officer | varchar(50) | Loan officer |
investorprogram | varchar(50) | Investor program |
investorcollateralprogramid | varchar(35) | Collateral program ID |
down_pymt_assist | varchar(5) | Down payment assistance |
Import Tracking
| Column | Type | Description |
|---|---|---|
import_los_source | varchar(30) | LOS import source identifier |
import_los_date | datetime | When imported from LOS |
import_servicing_source | varchar(30) | Servicing import source |
import_servicing_date | datetime | When imported from servicing |
nmls_id_company | varchar(30) | Company NMLS ID |
nmls_id_lo | varchar(30) | Loan officer NMLS ID |
Payments
| Column | Type | Description |
|---|---|---|
curr_pi_pymt | numeric(10,2) | Current P&I payment |
curr_piti_pymt | numeric(10,2) | Current PITI payment |
orig_pi_pymt | numeric(10,2) | Original P&I payment |
curr_pi_date | datetime | Current P&I date |
orig_pi_date | datetime | Original P&I date |
escrowindicator | varchar(5) | Escrow flag |
GSE Program Identifiers
| Column | Type | Description |
|---|---|---|
aus_engine | varchar(20) | AUS engine (DU/LP) |
aus_results | varchar(30) | AUS recommendation |
refinanceprogramidentifier | varchar(40) | Refi program (HARP, etc.) |
projectlegalstructuretype | varchar(15) | Condo project type |
projectattachmenttype | varchar(8) | Attached/detached |
constructionmethodtype | varchar(20) | Construction method |
loanprogramidentifier | varchar(60) | Loan program ID |
studentloancashoutrefi | varchar(5) | Student loan cash-out flag |
mersorigmortgageeofrecordind | varchar(5) | MERS mortgagee of record |
appraisal_waiver_flag | varchar(5) | Appraisal waiver |
mh_type | varchar(20) | Manufactured housing type |
Core Entity ER Diagrams
Trading Domain
Pricing Domain
Risk Management Domain
Pipeline & Commitment Domain
Securitization Rules Domain
Foreign Key Relationship Map
The database has 283 foreign key constraints with 313 FK column mappings. These cluster around a small number of hub entities.
Hub Entities (Most Referenced)
FK Relationship Groups
pscat_instruments (referenced by ~20 tables)
The central entity of the pricing/trading system. Referenced by:
pscat_trades.instrument_name— trades are for an instrumentpscat_securities.instrument_name— securities belong to an instrumentpscat_inst_dde_links_multi.instrument_name— pricing data linkspscat_instrument_dde_links.instrument_name— DDE linkspscat_inst_dde_rslt_names_rel.instrument_name— result name associationspscat_programs_instruments_rel.instrument_name— program membershippscat_programs_inv_inst_rel.instrument_name— investor instrument mappingpscat_inv_inst_sec_rule_rel.instrument_name— securitization rulespscat_trade_builder.instrument_name— trade builder templatespscat_rm_inv_delivery_win.instrument_name— delivery windowspscat_rm_bestex_inst_names.instrument_name— BestEx instrument listpscat_bestefforts_ins_pgm_inv.instrument_name— best efforts mappingpscat_best_efforts_inst_rel_071.instrument_name— best efforts (v071)pscat_trade_arms.instrument_name— ARM trade detailspscat_treasury_dde_links_multi.instrument_name— treasury pricingrmcat_instrument_par_rate_rel.instrument_name— par rate mappingrmcat_setup_instr_prog_inv.instrument_name— risk setuprmcat_sfas_group_inst_rel.instrument_name— SFAS groupsrmcat_inst_benchmark_values.instrument_name— benchmarks
pscat_trade_counterparties (referenced by ~15 tables)
pscat_trades.trade_counterparty_name— trade executionpscat_trade_builder.trade_counterparty_name— trade templatespscat_margin_activity.counterparty_name— margin callspscat_margin_tracking.trade_counterparty_name— margin monitoringpscat_trade_cntrprtys_doc_rel.counterparty_name— documentspscat_trade_cp_sec_rule_rel.counterparty_name— securitization rules
pscat_guarantors (referenced by ~12 tables)
pscat_instruments.guarantor_name— instrument guarantorpscat_pools.guarantor_name— pool guarantorpscat_programs.guarantor_name— program guarantorpscat_master_commitments.guarantor_name— commitment guarantorpscat_master_agreements.guarantor_name— agreement guarantorpscat_reservations.guarantor_name— reservation guarantorpscat_securities.guarantor_name— security guarantorpscat_guar_doc_relation.guarantor_name— document requirementspscat_guar_inst_terms_rel.guarantor_name— instrument termspscat_guar_sec_rules_relation.guarantor_name— securitization rulespscat_instr_product_name_rel.guarantor_name— product naming
pscat_pools (referenced by ~10 tables)
pscat_trades_pools_relation.pool_name— trade-to-pool designationpscat_pool_security_relation.pool_name— pool-to-securitypscat_pools_sec_rule_rel.pool_name— securitization rulespscat_pools_ach.pool_name— ACH bankingpscat_pools_certificate.pool_name— certificatespscat_pool_fee_overrides.pool_name— fee overridespscat_states.pool_name— state concentration limitspscat_pool_note_rate_cpn_adj.pool_name— note rate adjustments
pscat_trades (referenced by ~8 tables)
pscat_pair_offs.trade_id— pair-off designationspscat_trades_pools_relation.trade_id— pool designationspscat_trades_securities_rel.trade_id— security designationspscat_trades_assignments.trade_id— trade assignmentspscat_trades_best_efforts.trade_id— best efforts detailspscat_trades_documents_rel.trade_id— documentspscat_trades_rollover_rel.trade_id— rolloverspscat_trade_shipment_relation.trade_id— shipment trackingpscat_trade_cash_grid.trade_id— cash window pricing
rmcat_profile_names (referenced by ~10 tables)
rmcat_loan.profile_name— loan analysisrmcat_trades.profile_name— trade analysisrmcat_setup_profile_parameters.profile_name— configurationrmcat_setup_segment_names.profile_name— segmentsrmcat_setup_instr_prog_inv.profile_name— instrumentsrmcat_bestex_analysis.profile_name— best execution resultsrmcat_sfas_loans.profile_name— SFAS analysisrmcat_sfas_trades.profile_name— SFAS analysisrmcat_margin_tracking.profile_name— margin tracking
pscat_reservations (referenced by ~8 tables)
pscat_reserv_loan_rel.reservation_id— loan assignmentspscat_reserv_cancellation_rel.reservation_id— cancellationspscat_reserv_consolidation_rel.reservation_id— consolidationspscat_reserv_roll_rel.reservation_id— rolloverspscat_reserv_merge_rel.reservation_id— mergespscat_reservation_affiliates.reservation_id— affiliatespscat_reservation_gfee.reservation_id— guarantee fees
pscat_securitization_rules (referenced by ~8 tables)
Applied across multiple entity types — guarantors, instruments, pools, master commitments, programs, trade counterparties, and master agreements.
Views Inventory (258 views)
Views by Prefix
| Prefix | Count | Purpose |
|---|---|---|
v_* | ~60 | Position/gain summary views (hedge, loan, trade, timeslot) |
psview_uld4a_* / psview_uld5_* | ~50 | ULDD v4a and v5 delivery views |
rmcatv_* | ~20 | Risk analysis aggregate views |
rmrepv_* | ~15 | Risk reporting views (MBS, DPC, MTM) |
psusrv_* | ~20 | User-facing data views (risk, notional, rate sheets) |
pxcatv_* | ~15 | System metadata views (FK columns, PKs, triggers) |
psrepv_* | ~8 | Trade/security/pool summary reporting |
wtpov_* | ~6 | Investor export views (Citi, Citizens, Truist, RBB) |
rmtmpv_* | ~3 | Temporary calculation views |
psvmd_* | 1 | VMD fallout study base |
pscatv_* | 1 | RM investor instruments |
psulddv_* | 1 | ULDD table research |
loan_view | 1 | Base loan view |
psv_* | 1 | Pooled loan table |
Key Views
| View | Purpose |
|---|---|
loan_view | Base view over loan table |
v_pos_sum / v_pos_sum_segroll | Position summary with segment rollup |
v_loan_and_loan_shipped / v_loan_loan_shipped | Combined active and shipped loans |
rmcatv_loan_analysis | Risk analysis enriched loan view |
rmcatv_bestex_analysis | Best execution analysis view |
psview_uldd_loans | ULDD-formatted loan data |
psrepv_trade_summary | Trade position summary |
psrepv_security_summary | Security/pool summary |
psusrv_notional_analysis | Notional analysis user view |
pxcatv_foreign_key_columns | FK metadata introspection |
wtpov_investor_export_base | Base view for investor exports |
Triggers Inventory (45 triggers)
By Operation Type
| Prefix | Count | Operation | Purpose |
|---|---|---|---|
tad_* | 7 | AFTER DELETE | Cascade deletes on reference data |
tai_* | 5 | AFTER INSERT | Auto-populate fields on loan insert |
taiud_* | 1 | AFTER INSERT/UPDATE/DELETE | DDE result name sync |
tau_* | 8 | AFTER UPDATE | Status sync, timestamp updates |
tbi_* | 3 | BEFORE INSERT | Validate DDE link inserts |
tbu_* | 3 | BEFORE UPDATE | Validate DDE link updates |
ti_* | 7 | INSERT | Auto-populate delivery windows, pair-offs |
tu_* | 8 | UPDATE | Cascade updates on instruments, trades |
Triggers by Table
| Table | Triggers | Description |
|---|---|---|
loan | tai_loan, tau_loan | After insert/update — status sync, timestamp |
loan_inquiry | tai_loan_inquiry, tau_loan_inquiry | Inquiry pipeline sync |
loan_notional | tai_loan_notional, tau_loan_notional | Notional loan sync |
pscat_instruments | tad_pscat_instruments, tu_pscat_instruments | Cascade instrument changes |
pscat_trades | tu_pscat_trades | Cascade trade updates |
pscat_trade_counterparties | tu_pscat_trade_countrpart | Cascade counterparty updates |
pscat_pair_offs | ti_pscat_pair_offs, tu_pscat_pair_offs | Pair-off balance tracking |
pscat_dde_result_names | tad_pscat_dde_result_names, taiud_pscat_dde_result_names | DDE result sync |
pscat_loan_statuses | tad_pscat_loan_statuses | Status cascade |
pscat_cash_grid_syntax | tad_pscat_cash_grid_syntax | Cash grid cleanup |
pscat_rm_inv_delivery_win | ti_pscat_rm_inv_delivery_win, tu_pscat_rm_inv_delivery_win | Delivery window sync |
pxcat_dde_applications | tad_pxcat_dde_applications | DDE app cleanup |
pxcat_users | tad_pxcat_users | User cleanup cascade |
psusr_trd_val_options | tai_psusr_trd_val, tau_psusr_trd_val | Trade valuation sync |
pscat_instrument_dde_links | tbi_dde_links, tbu_dde_links | DDE link validation |
pscat_inst_dde_links_multi | tbi_dde_links_multi, tbu_dde_links_multi | Multi-coupon link validation |
pscat_treasury_dde_links_multi | tbi_treasury_dde_links, tbu_treasury_dde_links | Treasury link validation |
rmcat_par_rate_basis | tad_rmcat_par_rate_basis | Par rate cleanup |
rmcat_segment_name | tad_rmcat_segment_name | Segment cleanup |
rmcat_mbs_inst_delivery_pos | ti_rmcat_mbs_inst_delivery_pos, tu_rmcat_mbs_inst_delivery_pos | MBS delivery sync |
rmcat_msr_prices | ti_rmcat_msr_prices, tu_rmcat_msr_prices | MSR pricing sync |
rmcat_bidpkg_investor_inst | ti_rmcat_bidpkg_investor_inst, tu_rmcat_bidpkg_investor_inst | Bid package sync |
pscat_settlement_dates | ti_single_settlement_date | Settlement date validation |
Various pxcat_user_preference* | tau_user_preference_value_* | Preference cascades |
reservation_product_list | — | Reservation product trigger |
*_distinct_active_product | ti_*, tu_* | Active product maintenance |
Stored Procedures and Functions
Stored Procedures (30)
Most stored procedures are legacy dt_* Visual SourceSafe integration stubs from PowerBuilder. Only a few are business-relevant:
| Procedure | Purpose |
|---|---|
dt_addtosourcecontrol | VSS add to source control |
dt_addtosourcecontrol_u | VSS add (Unicode) |
dt_adduserobject / _u | VSS user object |
dt_checkinobject / _u | VSS check-in |
dt_checkoutobject / _u | VSS check-out |
dt_displayoaerror / _u | VSS error display |
dt_droppropertiesbyid | VSS property cleanup |
dt_dropsourcecontrol | VSS disconnect |
dt_dropuserobjectbyid | VSS user object removal |
dt_generateansiname | ANSI name generation |
dt_getoaborecord / _u | VSS OA record |
dt_getpropertiesbyid / _u | VSS property retrieval |
dt_isaborecord / _u | VSS record check |
dt_removefromsourcecontrol | VSS remove |
dt_setpropertybyid / _u | VSS property set |
dt_validateloginparams / _u | VSS login validation |
dt_verstamp006 / 007 | VSS version stamps |
dt_whaborecord / _u | VSS WH record |
Table-Valued Functions (7)
These are inline TVFs used by DataWindows and reports for complex query encapsulation. Names not captured in this extraction but they serve pricing interpolation, pipeline filtering, and position calculations.
Data Volume Analysis
Tables With Significant Row Counts
| Table | Rows | Observations |
|---|---|---|
rmcat_possible_loans | 732K | Risk analysis candidate loans — largest table |
rmcat_eligible_loans | 561K | Risk-eligible loan universe |
pxcat_vmd_pipe_log_db_errors | 504K | Data pipeline error log (accumulated) |
pscat_inst_dde_links_multi | 444K | Instrument pricing data (coupon × result × date) |
loan_notional | 434K | Notional (synthetic) loans for risk modeling |
Module Usage Signals
Many tables have zero rows, indicating modules not activated by this customer (Watermark TPO):
| Empty Module Area | Tables | Interpretation |
|---|---|---|
pscat_builder_commitments | 0 rows | Builder forward commitments not used |
pscat_margin_activity / _tracking | 0 rows | Margin management not active |
pscat_trade_contract_groups | 0 rows | Futures/options contract tracking unused |
rmcat_coh_* | 0 rows | Cost-of-hedge optimization unused |
rmcat_sfas_* (most) | 0 rows | SFAS/ASC 815 hedge accounting not configured |
psbid_* | 0 rows | Whole-loan bid packaging unused |
pfill_* | 0 rows | PowerFill pool optimization unused |
rmcat_bidpkg_* | 0 rows | Bid packaging module unused |
Heavily Used Modules
| Module | Evidence |
|---|---|
| Pipeline Management | loan table has active rows; loan_history captures status transitions |
| Risk Analysis | rmcat_possible_loans (732K) and rmcat_eligible_loans (561K) — heavy risk processing |
| Pricing / DDE | pscat_inst_dde_links_multi (444K) — extensive real-time pricing integration |
| Notional Analysis | loan_notional (434K) — active rate-sheet / what-if analysis |
| Best Execution | Active rmcat_bestex_* data |
| Securitization | pscat_pools, pscat_securities have active data |
Observations
Key Differences from the PB Extraction
-
3.4x more tables — The PB extraction only saw tables referenced by DataWindow SQL (~170). The full schema has 585 base tables, revealing entire subsystems invisible from the UI layer.
-
B2B pricing is massive — 37
psb2b_*tables implement per-counterparty automated pricing feeds (Fannie Mae, Freddie Mac, JPMorgan Chase, US Bank, Truist, PennyMac, and 15+ others). This was completely invisible from the PowerBuilder extraction. -
ULDD delivery is a full subsystem — 19
psuldd_*tables model the complete Uniform Loan Delivery Dataset with borrower demographics (94 cols), property details (92 cols), and ARM data (29 cols). The PB extraction captured none of this. -
Risk module is enormous — 178
rmcat_*tables plus 40rmtmp_*working tables. The SFAS hedge accounting subsystem alone has 22 tables. The PB extraction captured ~72 risk tables. -
Archive pattern is explicit — 20
psarc_*tables mirror core trading tables with identical schemas, used for archiving settled transactions. Thermarc_*prefix (7 tables) archives risk analysis results. -
The loan table schema is replicated 6 times —
loan,loan_inquiry,loan_notional,loan_modifications, andloan_shippedall share the same 157-column schema.loan_historyhas 150 columns (same columns minus some that don't need tracking). This is a classic PowerBuilder pattern: separate tables for each loan "state" rather than a status column on one table. -
Position detail is the widest table —
rmrep_position_detailhas 279 columns, holding pre-computed position summary data across all dimensions. -
Securitization rules are polymorphic —
pscat_securitization_rulescontains SQL syntax strings applied across 6+ entity types via junction tables. This is a rule engine pattern that needs careful SaaS redesign. -
DDE is pervasive — "Dynamic Data Exchange" was the Windows IPC mechanism for real-time pricing. Tables like
pscat_instrument_dde_linksandpscat_inst_dde_links_multi(444K rows) store pricing feed configurations. The SaaS replacement will need API-based feeds instead. -
Customer-specific tables exist —
wtpo_*tables (Watermark TPO) contain customer-specific E11tec fraud detector integration, suggesting the legacy schema allowed per-customer schema extensions.
Implications for the SaaS Data Model
-
Normalize the loan table — 157 columns with 6 copies is unsustainable. Break into
loan_core,loan_borrower,loan_property,loan_pricing,loan_investor, etc. with a single status column and proper history tracking. -
Replace DDE with API feeds — All
*_dde_*tables need reimagining as REST/WebSocket pricing integrations. -
Merge archive patterns — Instead of
psarc_*shadow tables, use soft deletes or temporal tables in Azure SQL. -
Consolidate risk temp tables — The 40
rmtmp_*tables can be replaced with CTEs, temp tables in stored procedures, or in-memory calculation in the .NET application layer. -
Abstract B2B pricing — The per-counterparty
psb2b_pricing_*pattern (one table per investor) should become a generic pricing feed with a counterparty discriminator column. -
Re-examine SFAS — 22 SFAS tables with zero rows at this customer suggests this is a niche feature. Consider making it a separate module or plugin rather than core schema.
-
Schema-per-tenant — With database-per-tenant (ADR-005), the SaaS schema will be identical across tenants. Customer-specific tables like
wtpo_*will not carry over — any tenant-specific needs must go through the extension mechanism.