Devlog: 2026-03-17 — Loan Table Schema Baseline
What Happened
Product Owner provided the ps_install loan table DDL — the baseline schema used for new PowerSeller Desktop App implementations. Compared it against the WTPO production schema (PS608) to determine the standard vs. custom column split.
Findings
- Standard schema (ps_install): 143 columns
- WTPO custom columns: 14
- WTPO total: 157 columns (143 + 14)
Jay's earlier estimate of "a dozen or so" custom columns per customer was confirmed.
WTPO Custom Columns
| Column | Type | Likely Purpose |
|---|---|---|
highbalanceindicator | varchar(5) | High-balance conforming loan flag |
bid_id | numeric(12,0) | Link to bid packaging module |
annualincome | numeric(16,2) | Borrower annual income |
doctype | varchar(30) | Documentation type (Full, Alt, etc.) |
interestonly | varchar(5) | Interest-only flag |
selfemployedindicator | varchar(5) | Self-employed borrower flag |
LienPriorityType | varchar(20) | First/second lien position |
enoteindicator | varchar(5) | eNote flag |
assumableindicator | varchar(5) | Assumability flag |
baseloanamount | numeric(12,2) | Base loan amount before fees |
appraisal_waiver_flag | varchar(5) | PIW/ACE appraisal waiver |
borrower_income | numeric(10,2) | Borrower income amount |
nmls_id_company | varchar(30) | Company NMLS ID (compliance) |
nmls_id_lo | varchar(30) | Loan officer NMLS ID (compliance) |
All custom columns are simple types (varchar, numeric, int) — no complex structures.
Implications for PSSaaS
Data Model Strategy
The loan entity in PSSaaS should:
- Model the 143 standard columns as typed C# properties (the core
Loanentity) - Support tenant-specific extensions via a JSON column (
custom_data) or an extension table - Never require code changes for customer-specific columns — extensions are configuration, not code
Recommended Approach: JSON Extension Column
Add a custom_data column (nvarchar(max), JSON) to the loan table. Benefits:
- SQL Server supports
OPENJSON,JSON_VALUE, computed columns, and JSON path indexes - EF Core 8 supports JSON column mapping natively
- Customers define their custom field names and types in a configuration table
- The API exposes custom fields as a dictionary in the loan response
- No schema migrations needed when a customer adds a field
Alternative: Extension Table
A loan_custom_fields table with (loan_id, field_name, field_value, field_type). More relational but harder to query efficiently.
The JSON approach is recommended for PSSaaS as it's the modern SQL Server pattern and EF Core 8 supports it natively.
Source Data
ps_installDDL provided directly by the Product Owner- PS608 (WTPO) schema extracted from Azure SQL MI earlier in this session