Loan Table Baseline Schema (ps_install)
This is the standard loan table schema from the ps_install database — the baseline used for all new PowerSeller Desktop App implementations. 143 columns. Customer-specific columns are added on top of this base.
Source: DDL provided by Product Owner on 2026-03-17.
Column Inventory
Identification
| Column | Type | Nullable | Notes |
|---|
loan_id | varchar(30) | PK | Primary key |
agency_loan_number | varchar(30) | Yes | Agency-assigned loan number |
mers_mortgage_id | varchar(30) | Yes | MERS MIN |
fha_case_number | varchar(15) | Yes | FHA case number |
servicing_number | varchar(20) | Yes | Servicer loan number |
universalloanidentifier | varchar(45) | Yes | ULI (MISMO) |
mod_loan_number | varchar(20) | Yes | Modification reference |
mod_reference_code | varchar(20) | Yes | Modification code |
Amounts
| Column | Type | Nullable | Notes |
|---|
loan_amount | numeric(12,2) | Yes | Current loan amount |
orig_loan_amount | numeric(11,2) | Yes | Original loan amount |
appraised_value | numeric(11,2) | Yes | Appraised property value |
purchase_price | numeric(11,2) | Yes | Property purchase price |
issue_date_prin_bal | numeric(11,2) | Yes | Principal balance at issuance |
unsched_prin_bal | numeric(11,2) | Yes | Unscheduled principal balance |
curr_pi_pymt | numeric(10,2) | Yes | Current P&I payment |
orig_pi_pymt | numeric(10,2) | Yes | Original P&I payment |
curr_piti_pymt | numeric(10,2) | Yes | Current PITI payment |
Rates and Pricing
| Column | Type | Nullable | Notes |
|---|
note_rate | numeric(9,6) | Yes | Current note rate |
discount_points | numeric(6,3) | Yes | Discount points |
buy_price | numeric(8,5) | Yes | Purchase price (secondary mkt) |
srp_paid | numeric(8,5) | Yes | Service release premium |
mkt_sale_price | numeric(9,6) | Yes | Marketing sale price |
orig_interest_rate | numeric(6,4) | Yes | Original interest rate |
arm_margin | numeric(6,3) | Yes | ARM margin |
percent_increase | numeric(6,4) | Yes | Rate increase percentage |
life_of_loan_rate_cap | numeric(6,4) | Yes | Lifetime rate cap |
rate_cap_percent | numeric(9,6) | Yes | Periodic rate cap |
Dates
| Column | Type | Nullable | Notes |
|---|
lock_expiration_date | datetime | Yes | Lock expiration |
close_date | datetime | Yes | Closing date |
time_stamp | datetime | Yes | Record timestamp |
lock_date | datetime | Yes | Lock date |
application_date | datetime | Yes | Application date |
curr_pi_date | datetime | Yes | Current P&I date |
estimated_close_date | datetime | Yes | Estimated closing date |
orig_pi_date | datetime | Yes | Original P&I date |
mkt_shipped_date | datetime | Yes | Shipped to investor date |
note_date | datetime | Yes | Note date |
maturity_pi_date | datetime | Yes | Maturity date |
mod_date | datetime | Yes | Modification date |
first_adjustment_date | datetime | Yes | First ARM adjustment date |
mkt_lock_date | datetime | Yes | Marketing lock date |
mkt_lock_expiration_date | datetime | Yes | Marketing lock expiration |
mkt_purchase_date | datetime | Yes | Marketing purchase date |
disbursement_date | datetime | Yes | Disbursement date |
llpa_eligible_date | datetime | Yes | LLPA eligibility date |
import_los_date | datetime | Yes | LOS import date |
import_servicing_date | datetime | Yes | Servicing import date |
thirdpartyloanacquisitiondate | datetime | Yes | Third-party acquisition date |
Borrower
| Column | Type | Nullable | Notes |
|---|
borr_last_name | varchar(35) | Yes | Primary borrower last name |
borr_first_name | varchar(30) | Yes | Primary borrower first name |
borr_mid_name | varchar(30) | Yes | Primary borrower middle name |
borr_suffix | varchar(10) | Yes | Primary borrower suffix |
borr_social_security | varchar(11) | Yes | Primary borrower SSN |
borr_credit_score | numeric(4,0) | Yes | Primary borrower credit score |
coborr_last_name | varchar(35) | Yes | Co-borrower last name |
coborr_first_name | varchar(30) | Yes | Co-borrower first name |
coborr_mid_name | varchar(30) | Yes | Co-borrower middle name |
coborr_suffix | varchar(10) | Yes | Co-borrower suffix |
coborr_social_security | varchar(11) | Yes | Co-borrower SSN |
coborr_credit_score | numeric(4,0) | Yes | Co-borrower credit score |
coborr2_first_name | varchar(30) | Yes | 2nd co-borrower first name |
coborr2_last_name | varchar(35) | Yes | 2nd co-borrower last name |
coborr2_social_security | varchar(11) | Yes | 2nd co-borrower SSN |
coborr3_first_name | varchar(30) | Yes | 3rd co-borrower first name |
coborr3_last_name | varchar(35) | Yes | 3rd co-borrower last name |
coborr3_social_security | varchar(11) | Yes | 3rd co-borrower SSN |
coborr4_first_name | varchar(30) | Yes | 4th co-borrower first name |
coborr4_last_name | varchar(35) | Yes | 4th co-borrower last name |
coborr4_social_security | varchar(11) | Yes | 4th co-borrower SSN |
first_time_borrower | varchar(5) | Yes | First-time buyer flag |
debt_to_income_ratio | numeric(6,3) | Yes | DTI ratio |
Property
| Column | Type | Nullable | Notes |
|---|
property_street_address | varchar(40) | Yes | Street address |
property_city | varchar(30) | Yes | City |
property_zip_code | varchar(10) | Yes | ZIP code |
property_county | varchar(30) | Yes | County |
property_type | varchar(20) | Yes | Property type (SFR, Condo, etc.) |
state | varchar(2) | Yes | State code |
num_of_units | numeric(3,0) | Yes | Number of units |
census_tract | varchar(11) | Yes | Census tract |
msa_number | varchar(12) | Yes | MSA number |
statecountycode | varchar(5) | Yes | State + county FIPS code |
flood_insurance_flag | varchar(3) | Yes | Flood zone flag |
Loan Characteristics
| Column | Type | Nullable | Notes |
|---|
amort_type | varchar(25) | Yes | Amortization type |
amortization_term | numeric(3,0) | Yes | Amortization term (months) |
orig_term | numeric(3,0) | Yes | Original term (months) |
remaining_term | numeric(3,0) | Yes | Remaining term (months) |
lock_days | numeric(3,0) | Yes | Lock period in days |
loan_type | varchar(30) | Yes | Loan type (Conv, FHA, VA, etc.) |
product_code | varchar(50) | Yes | Product code |
purpose_code | varchar(30) | Yes | Loan purpose |
occupancy_code | varchar(20) | Yes | Occupancy type |
ltv | numeric(7,3) | Yes | Loan-to-value ratio |
cltv | numeric(7,3) | Yes | Combined LTV |
baseltv | numeric(7,4) | Yes | Base LTV |
cash_out_indicator | varchar(5) | Yes | Cash-out flag |
buydown_flag | varchar(5) | Yes | Buydown flag |
int_only | varchar(5) | Yes | Interest-only flag |
subordinate_financing_flag | varchar(5) | Yes | Subordinate financing flag |
index_code | varchar(100) | Yes | ARM index code |
mh_type | varchar(20) | Yes | Manufactured housing type |
aus_engine | varchar(20) | Yes | AUS engine (DU, LP, etc.) |
aus_results | varchar(30) | Yes | AUS recommendation |
fha_va_section_of_act | varchar(5) | Yes | FHA/VA section |
loss_mit_flag | varchar(3) | Yes | Loss mitigation flag |
down_pymt_assist | varchar(5) | Yes | Down payment assistance flag |
Insurance and Fees
| Column | Type | Nullable | Notes |
|---|
mi_coverage_percent | numeric(3,0) | Yes | MI coverage percentage |
mi_financed_single_prem | numeric(8,2) | Yes | Financed single premium MI |
upfront_mip_amt | numeric(10,2) | Yes | Upfront MIP amount |
annual_mip_amt | numeric(10,2) | Yes | Annual MIP amount |
upfront_mip_rate | numeric(9,6) | Yes | Upfront MIP rate |
annual_mip_rate | numeric(9,6) | Yes | Annual MIP rate |
Investor / Marketing
| Column | Type | Nullable | Notes |
|---|
instrument_name | varchar(30) | Yes | Assigned instrument |
pool_name | varchar(30) | Yes | Assigned pool |
packet_name | varchar(30) | Yes | Servicing packet |
source_name | varchar(30) | Yes | Loan source |
mkt_commitment_number | varchar(30) | Yes | Marketing commitment |
mkt_confirmation | varchar(30) | Yes | Marketing confirmation |
mkt_investor | varchar(30) | Yes | Marketing investor |
mkt_investor_loan_number | varchar(30) | Yes | Investor loan number |
mkt_marketing_program | varchar(30) | Yes | Marketing program |
mkt_master_commitment | varchar(30) | Yes | Master commitment |
investorprogram | varchar(50) | Yes | Investor program |
asc_best_efforts_flag | varchar(1) | Yes | Best efforts flag |
Status
| Column | Type | Nullable | Notes |
|---|
curr_status | varchar(50) | Yes | Current loan status |
loan_officer | varchar(50) | Yes | Loan officer name |
branch | varchar(30) | Yes | Originating branch |
GSE Program Identifiers
| Column | Type | Nullable | Notes |
|---|
loanaffordableindicator | varchar(5) | Yes | Affordable lending flag |
refinanceprogramidentifier | varchar(40) | Yes | Refinance program ID |
investorcollateralprogramid | varchar(35) | Yes | Investor collateral program |
projectlegalstructuretype | varchar(15) | Yes | Project legal structure |
projectattachmenttype | varchar(8) | Yes | Project attachment type |
constructionmethodtype | varchar(20) | Yes | Construction method |
loanprogramidentifier | varchar(60) | Yes | Loan program ID |
refinancecashouttype | varchar(30) | Yes | Refinance cash-out type |
studentloancashoutrefi | varchar(5) | Yes | Student loan cash-out refi flag |
conformingloanlimitindicator | varchar(5) | Yes | Conforming limit flag |
jumboindicator | varchar(5) | Yes | Jumbo flag |
mersorigmortgageeofrecordind | varchar(5) | Yes | MERS original mortgagee flag |
ami_percent | numeric(5,2) | Yes | Area median income percentage |
escrowindicator | varchar(5) | Yes | Escrow flag |
loanlevelcreditscorevalue | int | Yes | Loan-level credit score (MISMO) |
Import Tracking
| Column | Type | Nullable | Notes |
|---|
import_los_source | varchar(30) | Yes | LOS import source name |
import_los_date | datetime | Yes | LOS import timestamp |
import_servicing_source | varchar(30) | Yes | Servicing import source |
import_servicing_date | datetime | Yes | Servicing import timestamp |
WTPO Custom Columns (14 additional)
These columns exist in PS608 (Watermark TPO) but NOT in ps_install:
| Column | Type | Nullable | Purpose |
|---|
highbalanceindicator | varchar(5) | Yes | High-balance conforming flag |
bid_id | numeric(12,0) | Yes | Bid packaging reference |
annualincome | numeric(16,2) | Yes | Borrower annual income |
doctype | varchar(30) | Yes | Documentation type |
interestonly | varchar(5) | Yes | Interest-only flag |
selfemployedindicator | varchar(5) | Yes | Self-employed flag |
LienPriorityType | varchar(20) | Yes | Lien position |
enoteindicator | varchar(5) | Yes | eNote flag |
assumableindicator | varchar(5) | Yes | Assumability flag |
baseloanamount | numeric(12,2) | Yes | Base loan amount |
appraisal_waiver_flag | varchar(5) | Yes | Appraisal waiver flag |
borrower_income | numeric(10,2) | Yes | Borrower income |
nmls_id_company | varchar(30) | Yes | Company NMLS ID |
nmls_id_lo | varchar(30) | Yes | Loan officer NMLS ID |