Loan Data Dictionary

This guide explains the core persistence tables Embarc uses to store loans, repayment schedules, monetary movements, and related balances. It focuses on the columns you most often read when building integrations or troubleshooting loan state. Where behaviour differs between Fixed Schedule and Dynamic Schedule products we call that out explicitly.

Conventions

Numeric columns are stored in major currency units (scale depends on tenant currency settings unless noted).

Dates are stored in tenant-local time (DATE without time component).

Most “*_derived” fields are recalculated by the platform after every servicing action (repayments, waivers, interest accrual, etc.).

Loan Master

Column	Description	Notes / schedule differences
`id`	Primary key.	Referenced by every child table.
`account_no`	System-generated loan number (unique).	Often used in statements/receipts.
`external_id`	Optional external reference (UUID/string).	Keep unique; integration middleware relies on it for idempotency.
`client_id`	Borrower reference (`m_client.id`).	Dynamic Schedule loans treat down payments as the first repayment for this borrower; no special linkage required.
`product_id`	Loan product. Drives schedule generator, charges, accounting rules.	Choose Dynamic Schedule products when you expect down payments, buy-downs, or staged interest.
`loan_type_enum`	Borrower type enum.	Embarc deployments typically use `INDIVIDUAL`.
`loan_status_id`	Current lifecycle state (submitted, approved, disbursed, closed…).	Approval changes this from `100` (submitted) to `200` (approved).
`fund_id`, `loan_officer_id`, `loanpurpose_cv_id`	Operational metadata for reporting.	Unchanged by schedule type.
`loan_transaction_strategy_code` / `_name`	Repayment allocation strategy. Dynamic Schedule products must use `advanced-payment-allocation-strategy`.
`term_frequency`, `term_period_frequency_enum`	Full loan term (e.g., 12 months).
`submittedon_date`, `approvedon_date`, `disbursedon_date`, `closedon_date`, `writtenoffon_date`, etc.	Lifecycle timestamps.	Dynamic Schedule loans may have `rescheduledon_date` populated when you regenerate staged grids.
`expected_disbursedon_date` / `expected_maturedon_date`	Planned dates.
`principal_amount_proposed`	Amount requested in the application.
`approved_principal`	Amount approved. Copied to schedule as the base principal.
`net_disbursal_amount`	Amount expected to disburse net of disbursement charges.
`principal_disbursed_derived`, `principal_repaid_derived`, `principal_outstanding_derived`	Running totals for principal.	If the loan has a down-payment period the amount collected in that period reduces `principal_outstanding_derived` immediately even though the first instalment might still be in the future.
`interest_charged_derived`, `interest_repaid_derived`, `interest_outstanding_derived`	Interest aggregates.	For Dynamic Schedule loans with capitalisation, these exclude deferred income (see balance tables below).
`fee_charges__derived`, `penalty_charges__derived`	Aggregates for fees and penalties.
`total_overpaid_derived`, `overpaidon_date`	Amount currently held as overpayment (credit balance).	Applies to both schedule types.
`is_npa`, `charged_off_on_date`	Non-performing / charge-off tracking.	Dynamic Schedule charge-off behaviour is configurable (`charge_off_behaviour` on the product); the column indicates whether charge-off has been applied.
`enable_installment_level_delinquency`	When true, Embarc tracks delinquency per instalment.	Often enabled for Dynamic Schedule BNPL so each instalment can be surfaced separately.
`interest_recalcualated_on`	Last business date on which daily interest was recomputed.	Used by both schedules when interest recalculation is enabled.

Dynamic Schedule auxiliary tables

Dynamic Schedule loans create additional rows that store precomputed interest grids and promotional balances:

m_progressive_loan_model (json_model, business_date) – snapshot of the advanced schedule used for future interest forecasts.
m_loan_capitalized_income_balance – deferred interest amounts pending recognition.
m_loan_buy_down_fee_balance – outstanding promotional subsidies for buy-down products.

Fixed Installment loans never populate these tables.

Loan Summary – aggregate balances

This table mirrors the derived totals in m_loan, but it keeps historical aggregates even when some loan fields are migrated. Use it when you need a single-row summary without recalculating through every schedule row.

Column	Description	Notes
`total_principal_derived`	Total contractual principal.
`principal_disbursed_derived`, `principal_repaid_derived`, `principal_outstanding_derived`	Principal movement snapshots.
`interest_charged_derived`, `interest_repaid_derived`, `interest_outstanding_derived`	Interest aggregates. Dynamic schedule loans with interest capitalization move deferred interest into `capitalized_income_derived` until recognition.
`fee_charges__derived`, `penalty_charges__derived`	Fee/penalty aggregates (charged, repaid, waived, written off, outstanding).
`total_expected_repayment_derived`, `total_repayment_derived`	Expected vs collected cash to date.
`total_expected_costofloan_derived`, `total_costofloan_derived`	Interest + fees + penalties expected/collected.
`total_waived_derived`, `total_writtenoff_derived`, `total_outstanding_derived`	Overall loan state used for dashboards and reporting.

No behavioural differences between schedule types appear in this table; it’s purely aggregated data.

Loan Disbursement Detail – tranche tracking

Column	Description	Notes
`loan_id`	Parent loan.
`expected_disburse_date`	Planned tranche disbursement.	Approval populates this from the application or overrides.
`disbursedon_date`	Actual disbursement date (null until executed).
`principal`	Amount assigned to this tranche.
`net_disbursal_amount`	Amount after tranche-specific disbursement charges.
`is_reversed`	Flag indicating the tranche was reversed (e.g., undo disbursement).

Dynamic schedule loans use the same structure. If down payment is enabled, the down-payment amount shows up as the first repayment schedule period rather than a tranche.

Loan Repayment Schedule – instalment plan

Each row represents a single repayment period (including down-payment or additional adjustment periods).

Column	Description	Dynamic Schedule notes
`installment`	Instalment sequence number (starts at 1).	Down-payment periods are flagged with `is_down_payment = true` and typically use instalment 0 or 1 depending on configuration.
`fromdate` / `duedate`	Period boundaries.	Dynamic Schedule daily interest recalculation may insert additional rows flagged with `recalculated_interest_component = true`.
`principal_amount`	Contracted principal due in the period.	Down payment shows the upfront contribution here.
`principal_completed_derived`, `principal_writtenoff_derived`	Amounts already satisfied or written off.	Partial prepayments reduce `principal_completed_derived`, even if the due date is in the future.
`interest_amount`, `interest_completed_derived`, `interest_waived_derived`, `interest_writtenoff_derived`, `accrual_interest_derived`	Interest movements.	When Dynamic Schedule interest recalculation runs, `recalculated_interest_component` rows capture the incremental interest until the next scheduled period.
`fee_charges_amount`, `penalty_charges_amount` and their `_completed/_waived/_writtenoff` counterparts	Instalment-level fees/penalties.	Dynamic Schedule loans often keep instalment fees here to align with merchant agreements.
`total_paid_in_advance_derived` / `total_paid_late_derived`	Credits paid before/after due date.	Important when interpreting `delinquent.nextPaymentAmount`: the delinquency API subtracts advance payments to surface the remaining balance.
`obligations_met_on_date`	When populated, the instalment is considered fully satisfied.	If `null`, the instalment remains open even if the due date is in the future.
`recalculated_interest_component`	Marks rows created by interest recalculation.	Common for Dynamic Schedule loans with daily interest; Fixed Schedule loans may also set this when interest recalculation is enabled, but fewer rows are generated.
`is_additional`	Flags adjustment rows (e.g., post-approval corrections).
`is_down_payment`	True when the row represents a down-payment period.	Always `false` for Fixed Schedule loans.
`is_re_aged`	True when the instalment was shifted by a re-age command.

Loan Transaction – cash movements and adjustments

Column	Description	Schedule details
`id`	Primary key.
`loan_id`	Parent loan.
`transaction_type_enum`	Enumerated type: repayment, disbursement, interest waiver, charge-off, down payment, buy-down fee, capitalized income, etc.	Dynamic Schedule loans introduce additional transaction types (`downPayment`, `buyDownFee`, `capitalizedIncome*`, `interestPaymentWaiver`).
`transaction_date`	Effective date of the transaction.
`submitted_on_date`	When the command was submitted.
`amount`	Gross amount processed.
`principal_portion_derived`, `interest_portion_derived`, `fee_charges_portion_derived`, `penalty_charges_portion_derived`, `overpayment_portion_derived`	How the amount was allocated.	For Dynamic Schedule buy-down/capitalisation transactions the principal/interest portions may be zero while deferred income fields move in the balance tables.
`unrecognized_income_portion`	Amount held as deferred income (used for capitalised interest/buy-downs).	Mostly non-zero on Dynamic Schedule transactions.
`is_reversed`	Indicates the transaction has a matching reversal entry.
`external_id` / `reversal_external_id`	Optional references for reconciliation.
`outstanding_loan_balance_derived`	Loan balance immediately after the transaction (principal only).
`manually_adjusted_or_reversed`	Flag when back-office manually adjusted the transaction.
`charge_refund_charge_type`	Charge type associated with charge refunds.

Mapping table `m_loan_transaction_repayment_schedule_mapping`

Links transactions to specific schedule rows and records allocation amounts (principal_portion_derived, interest_portion_derived, fee_charges_portion_derived, penalty_charges_portion_derived, amount). Useful when you need to reconcile which transactions cleared which installments.

Loan Charge and related tables

Column	Description	Notes
`charge_time_enum`	When the charge is assessed (disbursement, installment-specific, overdue, specified-date).	Dynamic schedule loans often attach installment-level fees to mirror merchant agreements.
`due_for_collection_as_of_date`	Charge due date.
`charge_calculation_enum`	Amount type (flat, percentage of principal, etc.).
`amount`	Original charge amount.
`amount_paid_derived`, `amount_waived_derived`, `amount_writtenoff_derived`, `amount_outstanding_derived`	Lifecycle of the charge.	Delinquency amounts exclude charges unless `amount_outstanding_derived` is still positive.
`is_penalty`, `is_paid_derived`, `waived`, `is_active`	Status flags.
`external_id`	External reference for reconciliation.

Supporting tables:

m_loan_charge_paid_by – how a repayment settled specific charges (loan_transaction_id, loan_charge_id, allocation amounts).
m_loan_installment_charge – splits instalment-based charges per schedule row for Dynamic Schedule products.
m_loan_overdue_installment_charge – generated by the overdue-charge job for instalment-level penalties.

Payments, payment instruments, and autopay

`em_payment_instrument`

Column	Description	Notes
`id`	Primary key.	Parent row for every stored instrument.
`customer_id`	Borrower (`m_client.id`).	Enforces same-owner rule for loans, autopay, and payments.
`payment_instrument_type`	Enum (`BANK_ACCOUNT`, future types such as `DEBIT_CARD`).	Determines which child table stores instrument details.
`nick_name`	Friendly label shown in UI/history.	Unique per customer is recommended.
`status`	Enum (`ACTIVE`, `INACTIVE`).	Only active instruments appear in payment/autopay pickers.
`is_deleted`	Soft-delete flag.	`TRUE` hides the instrument but keeps audit trail.
`version`	Optimistic locking counter.	Incremented on each update.
`created_at`, `updated_at`	Timestamps (UTC).
`created_by`, `updated_by`	`m_appuser` references.	Captures who created/last modified.

`em_bank_payment_instrument`

Child table with bank-specific fields (1:1 via payment_instrument_id).

Column	Description	Notes
`account_holder_name` / `account_holder_type`	Legal owner of the account (`INDIVIDUAL`, `BUSINESS`).
`account_type`	Enum (`CHECKING`, `SAVINGS`).
`account_number`, `routing_number`	Stored encrypted/masked.	Mask before exposing downstream.
`bank_name`	Free-form issuer label.
`verification_state`	Enum (`PENDING`, `VERIFIED`, `REJECTED`, `REVOKED`).	Autopay requires `VERIFIED`.
`external_id`	Optional reference to external processors/KYC providers.
`created_at`, `updated_at`, `version`, `created_by`, `updated_by`	Standard auditing columns.

`em_autopay`

Column	Description	Notes
`id`	Primary key.
`loan_id`	Loan enrolled in autopay (`m_loan.id`).
`customer_id`	Borrower (`m_client.id`).	Must equal the instrument’s owner.
`payment_instrument_id`	FK to `em_payment_instrument`.	Instrument must be active & verified.
`status`	Enum (`ACTIVE`, `PAUSED`, `CANCELLED`).	Scheduler only pulls `ACTIVE` rows.
`cancelled_reason`	Enum reason when status = `CANCELLED`.	E.g., `CUSTOMER_REQUEST`, `LOAN_CLOSED`, `PAYMENT_FAILURES_EXCEEDED`.
`enrollment_date`	Timestamp of initial enrollment.
`cancelled_at`, `cancelled_by`	When/who cancelled (if applicable).
`created_at`, `updated_at`, `created_by`, `updated_by`	Audit trail.
`version`	Optimistic locking counter.

`em_payment`

Column	Description	Notes
`id`	Primary key.
`loan_id`	Loan the payment belongs to.	Required for all rows.
`external_id`	Unique idempotency/payload reference.	Enforced by `uq_em_payment_external_id`.
`payment_instrument_id`	Optional FK to `em_payment_instrument`.	Populated for initiated payments and autopay executions.
`autopay_id`	Optional FK to `em_autopay`.	Links settled autopay runs back to the enrollment.
`amount`	Gross payment amount (loan currency).
`effective_date`	Business date applied to the loan.
`status`	Enum (`PENDING`, `PROCESSING`, `SETTLED`, `RETURNED`, `FAILED`, `CANCELED`, `CHARGEBACK`).	Drives which timeline columns below are populated.
`type`, `source`	Free-form descriptors (e.g., `REPAYMENT`, `REFUND`; `PORTAL`, `CSR`).	Useful for reporting.
`is_external_payment`	`TRUE` when payment was logged after the fact.
`payment_method`	Enum (`ACH`, `RTP`, `WIRE`, `CARD`, `CHECK`, `CASH`, `MONEY_ORDER`, `OTHER`).
`trace_number`, `check_number`, `account_number`, `routing_number`, `bank_name`, `payor_name`, `payee_name`	Processor metadata.	Mask values before exposing externally.
`metadata`	JSONB payload for processor responses or custom fields.
`note`	Free-form comment.
`created_at`, `updated_at`, `created_by`, `updated_by`	Audit trail.
`processed_at`, `settled_at`, `expected_settlement_at`, `returned_at`, `failed_at`, `canceled_at`, `chargeback_at`, `effective_at`	Timestamps that track the payment lifecycle.	Use to build settlement dashboards and SLAs.
`failure_reason`, `failure_details`, `failure_log`, `return_reason`, `return_details`, `cancel_reason`, `cancel_details`, `return_amount`, `chargeback_amount`, `chargeback_at`	Captures why a payment reversed and any partial refund amounts.
`loan_transaction_id`	FK to the posting entry in `m_loan_transaction`.	Links processor record to ledger impact.
`return_loan_transaction_id`, `chargeback_loan_transaction_id`	FKs to reversal transactions created for returns/chargebacks.
`processor_transaction_id`, `processor_return_id`, `processor_chargeback_id`	External processor identifiers.
`version`	Optimistic locking counter.

Payments are the system-of-record for processor state. Pair them with m_loan_transaction data when reconciling the general ledger.

Additional tables frequently used in reporting

Table	Purpose	Notes
`m_loan_arrears_aging`	Snapshot of arrears ageing (principal, interest, fee, penalty past due buckets).	Populated by COB/delinquency jobs.
`m_loan_installment_delinquency_tag`	When instalment-level delinquency is enabled, this table records the current tag (`range_id`) per instalment.	Dynamic Schedule BNPL implementations set this flag to surface which instalments are past due.
`m_loan_interest_recalculation_additional_details`	Stores recalculation metadata for Fixed Schedule loans (e.g., compounding strategy).	Dynamic Schedule loans rely more on the progressive model tables instead.

Interpreting data by schedule type

Consideration	Fixed Schedule loans	Dynamic Schedule loans
Installment outstanding (`principal_amount - principal_completed`)	Represents the true remaining balance and is what the delinquency API surfaces. Partial prepayments reduce it immediately.	The same calculation applies, but if daily interest recalculation is enabled additional rows (`recalculated_interest_component = true`) hold interim interest until the next scheduled due date.
Down payment	Not applicable; first installment behaves like any other repayment.	Represented as a schedule row with `is_down_payment = true` and accompanied by a `downPayment` loan transaction.
Buy-down / capitalised income	Not tracked in schedule totals.	Additional balance tables (`m_loan_buy_down_fee_balance`, `m_loan_capitalized_income_balance`) store deferred revenue; related transactions use the custom types in `m_loan_transaction`.
Delinquency next payment	Straightforward outstanding amount from the next unpaid schedule row.	Amount is predicted via the dynamic interest model; ensure `next-payment-due-date` config is set appropriately to avoid pointing to the last repayment date.

Usage tips

Always filter by is_reversed = false when aggregating loan transactions; reversal entries remain in the table for audit.
Schedule rows are immutable—the platform creates adjustment rows (is_additional = true) instead of editing past records. When reconciling, include these rows in your calculations.
Charges vs installment totals – Statement designs often need both schedule data and outstanding charges (amount_outstanding_derived). Partial installment payments may leave charges unpaid even though the schedule row shows zero outstanding.
Dynamic Schedule analytics – To mirror how Embarc predicts the next payment for dynamic (precomputed) plans, read the stored progressive loan model (m_progressive_loan_model) and run it through the same interest-forecast logic used for Pay-in-N/BNPL schedules. This applies the advanced payment allocation rules, daily interest accrual, and down-payment adjustments exactly as Embarc does. For Fixed Schedule loans you simply sum the remaining amounts on the standard repayment schedule.