Database – Loan Transactions (loan_tx)

Column

Data Type

Column Info

Value Notes

Other

id

int(11)

The ID of the transaction. This will be the actual ID for logged transactions, or an incremented ID (unique to this table) for scheduled and forecast payments. Scheduled and forecast payments supply information about the amortization of the loan and make the appropriate adjustments to future principal and interest, but are not transactions that were actually logged on the loan.

entity_id

int(11)

The ID of the entity the transaction is associated with.

entity_type

varchar(100)

The type of entity the transaction is associated with. This field should always equal Entity.Loan, because transactions should be associated with loans.

tx_id

char(40)

This is the unique identifier for a transaction used within this table. This is a concatenation of the entity_id, the mod_id, the type, and the period. If it is a logged transaction, instead of using the payment period, it will concatenate a transaction ID assigned for this table, with a 0 at the end. It will look something like this: 1-0-fpm5. This transaction is from loan 1, mod 0. It is a forecast payment in payment period number 5.

mod_id

int(11)

When a loan is modified, the user can choose whether transactions will persist on the loan after the modification. This ID shows the modification that a transaction is associated with. Modifications can be found in the loan_modification_entity table.

date

datetime

This is the date the transaction applies to the loan.

period

int(11)

This is the payment period in which the transaction applies. This is an integer value. Payment periods are number starting with 0.

period_start

date

This is the start date for the payment period in which the transaction applies.

period_end

date

This is the end date for the period in which the transaction applies.

title

varchar(255)

This is a human readable title for scheduled and forecast payments. For logged transactions, this title was added by the user.

type

char(20)

This is the type of transaction. The options are origination, scheduledPayment, forecastPayment, payment, credit, advancement, fee, suspendInterest, resumeInterest, apdAdjustment, and dpdAdjustment

• origination - This shows information about the loan amount and underwriting fee.
• scheduledPayment - This shows a payment that was scheduled on the loan.
• forecastPayment - This shows a future projected payment on the loan.
• payment - This is a payment that was logged on the loan.
• credit - This is a credit that was logged on the loan.
• advancement - This is an advancement that was logged on the loan.
• fee - This is a fee that was assessed on the loan.
• suspendInterest - This suspends the accrual of interest on the loan.
• resumeInterest - This resumes the accrual of interest on the loan.
• apdAdjustment - This adjusts the amount past due on the loan.
• dpdAdjustment - This adjusts the days past due on the loan.

info_only

tinyint(1)

This shows whether a transaction is information-only. The following types of transaction are considered info-only: origination, apdAdjustment, dpdAdjustment, suspendInterest, resumeInterest

1 - yes, 0 - no

info_details

varchar(255)

If there is additional information associated with an  info-only transaction, this field with hold a JSON object containing that information.

payment_id

bigint(21)

This is an ID that is used in this table to differentiate logged transactions of the same type, that occur in the same period. This ID will be present for all logged transactions, not just payments.

payment_display_id

bigint(21)

This value is only relevant to payments, and is the ID of the payment that is shown to the user in the software user interface.

payment_amount

decimal(10,2)

This field is relevant to transactions with a type of payment, forecastPayment, advancement, or credit. This is the amount of the transaction. For advancement transactions, this value with be negative.

payment_i

decimal(10,2)

This field is relevant to transactions with a type of payment, forecastPayment, or credit. This is the amount of the transaction that applied to interest on the loan.

payment_p

decimal(10,2)

This field is relevant to transactions with a type of payment, forecastPayment, advancement, or credit. This is the amount of the transaction that applied to principal on the loan. For advancement transactions, this value will be negative.

payment_d

decimal(10,2)

This field is relevant to transactions with a type of payment, forecastPayment, advancement, or credit. This is the amount of the transaction that applied to discount on the loan. For advancement transactions, this value will be negative.

payment_f

decimal(10,2)

This field is relevant to transactions with a type of payment, forecastPayment, or credit. This is the amount of the transaction that applied to fees on the loan.

payment_e

decimal(10,2)

This field is relevant to transactions with a type of payment, forecastPayment, or credit. This is the amount of the transaction that applied to escrow on the loan.

payment_e_breakdown

varchar(2000)

If a transaction applied to escrow, this field will hold a JSON object showing the IDs of the escrow buckets it applied to, and the amounts that applied to each bucket. Escrow buckets can be found in the escrow_subset_entity table.

fees_paid_details

varchar(2000)

If a transaction applied to fees, this field will hold a JSON object showing the IDs of the fees it applied to, and the amounts applied to each fee. Fees can be found in the charge_entity table.

charge_amount

decimal(10,2)

This field applies to transactions with a type of fee or scheduledPayment. This is the amount of the transaction.

charge_i

decimal(10,2)

This field applies to transactions with a type of scheduledPayment. This is the interest portion of the payment. Scheduled payments are not logged, so this will only show the amount scheduled for the payment, and not anything that actually applied to the loan.

charge_p

decimal(10,2)

This field applies to transactions with a type of scheduledPayment. This is the principal portion of the payment. Scheduled payments are not logged, so this will only show the amount scheduled for the payment, and not anything that actually applied to the loan.

charge_d

decimal(10,2)

This field applies to transactions with a type of scheduledPayment. This is the discount portion of the payment. Scheduled payments are not logged, so this will only show the amount scheduled for the payment, and not anything that actually applied to the loan.

charge_f

decimal(10,2)

This field applies to transactions with a type of fee or scheduledPayment. This is the fees portion of the payment or fee. Scheduled payments are not logged, so this will only show the amount scheduled for the payment, and not anything that actually applied to the loan.

charge_e

decimal(10,2)

This field applies to transactions with a type of scheduledPayment. This is the escrow portion of the payment. Scheduled payments are not logged, so this will only show the amount scheduled for the payment, and not anything that actually applied to the loan.

charge_e_breakdown

varchar(2000)

If a portion of a scheduled payment applied to escrow, this field will hold a JSON object showing the IDs of the escrow buckets it applied to, and the amounts that applied to each bucket. Escrow buckets can be found in the escrow_subset_entity table.

future

tinyint(4)

This value shows whether the transaction will apply to the loan in the future, meaning after today. This value gets updated with daily maintenance to ensure it is correct.

1 - yes, 0 - no

principalonly

tinyint(4)

This value shows whether the transaction will apply to principal only. This is only relevant to payment, scheduled payment, forecast payment, and credit transactions.

1 - yes, 0 - no

advancement

tinyint(1)

This value shows whether the transaction is an advancement. This should only show 1 for transactions with a type of advancement.

1 - yes, 0 - no

payoff_fee

tinyint(4)

This value shows whether the transaction is a payoff fee. Payoff fees don't come due on the loan, but must be paid in order to pay off the loan. This is only relevant to transactions with a type of fee.

1 - yes, 0 - no

charge_off

tinyint(4)

This value shows whether the transaction has been flagged as a charge off. A charge off transaction is like an accounting write down or write off of the loan. It is meant to lower the amount owed on the loan, when some portion of the loan is deemed uncollectible. This is only relevant to transactions with the type of payment or credit.

1 - yes, 0 - no

payment_type

int(11)

The ID of the payment type. Payment type specifies the waterfall application of payments. Payment types can be found in the custom_payment_type_entity table.

adb_days

int(11)

This is the number of days used to calculate the average daily loan balance for a payment period in which the transaction occurred. This should equal the number of days in the payment period. This is not relevant to info_only transactions.

adb

decimal(10,2)

This is the average principal balance of the loan during the payment period in which the transaction occurred. This is not relevant to info_only transactions.

principal_balance

decimal(10,2)

This is the loan's principal balance after the transaction applies. This is not relevant to info_only transactions.

displayorder

decimal(10,2)

This is a numeric value that shows the order in which transactions that occur on the same date should be shown in the transactions report.

deleted

tinyint(4)

This shows whether the entity has been deleted. This table does not use this field, but rather completely deletes records when they aren't needed.

1 - yes, 0 - no

lastUpdated

timestamp

Timestamp of when the record was last updated.