YAML Lender Mapping System

The lender mapping system enables flexible ingestion of payment/NACH CSV files from different lenders without code changes. Each lender has a YAML configuration file that defines how CSV columns map to a standardized 10-column schema in the RAW database layer.

Standardized Schema

Every payment file, regardless of lender, is normalized to these 10 columns before being stored in the RAW table:

Column	Type	Description
loan_id	text	Loan identifier (external ID in Fineract)
amount	numeric	Transaction/repayment amount
date	timestamp	Transaction date
status	text	Payment status (success/failed/bounce)
charge	numeric	Fees, charges, penalties
note	text	Remarks, reason descriptions
payment_id	text	UTR, payment reference
sub_reference_id	text	UMRN / mandate reference
source_file	text	S3 key of the uploaded file
lender	text	Lender identifier (auto-filled)

YAML Configuration Structure

Each YAML file is located at config/lender_mappings/{lender_key}.yml:

lender_key: arthmate
version: 1

# Map CSV column names to standardized fields
columns:
  loan_id:                    # Exact CSV header (case-insensitive)
    target: loan_id           # Standardized column name
    type: text                # text | numeric | date | timestamp
  Amount:
    target: amount
    type: numeric
  Transaction Date:
    target: date
    type: date
  Status:
    target: status
    type: text
  reason:
    target: note
    type: text

# Date format string (Python strptime format)
date_formats:
  date: "%d-%m-%Y"

# Row-level filters - only matching rows are loaded into RAW
filters:
  - column: amount
    op: ">"
    value: 0
  - column: status
    op: "in"
    value: ["success", "successful", "paid", "cleared", "clear",
            "settled", "realized", "received", "realised", "captured"]

# Constant values for every row
defaults:
  lender: arthmate

Column Mapping

How Matching Works

CSV headers are matched case-insensitively with whitespace trimmed:

# YAML key          CSV header it matches
"Link ID"        -> "Link ID", "link id", "LINK ID"
"Debit Amt "     -> "Debit Amt ", "debit amt"
" Final Amt "    -> " Final Amt ", "final amt"

Type Casting

Type	Behavior
text	Trimmed, empty strings become NULL
numeric	Commas stripped, converted to float. Invalid values become NULL
date	Parsed using date_formats.date if specified
timestamp	Parsed using format if specified, else auto-detected by pandas

Auto-Parse Dates

If date_formats is empty ({}), pandas auto-detects the format. This handles Excel CSV exports where date formats vary (e.g., 4/5/2026 vs 2026-04-05):

date_formats: {}   # Auto-detect - use when format varies between exports

Post-Compute Operations

Perform column arithmetic after type casting but before filtering:

# DMI Payment: amount = net_pmt - other_charges_amount
post_compute:
  - column: amount           # Target column to update
    op: subtract             # subtract | add
    value_column: charge     # Column to subtract

After this, amount holds net_pmt - other_charges_amount and charge retains the original charge value.

Filters

Filters run in order. Rows that don't match are rejected (saved to RAW.RAW_REJECTED_RECORDS for visibility in the Bad Records tab).

Supported Operators

Operator	Description	Example
>	Greater than (numeric)	amount > 0
>=	Greater than or equal	amount >= 100
==	Equals (case-insensitive)	status == "success"
!=	Not equals	status != "failed"
in	In list (case-insensitive)	status in ["success", "clear"]
not_in	Not in list	status not_in ["bounce"]

Status Filtering

Files with a status column should filter to success-only records:

filters:
  - column: status
    op: "in"
    value: ["success", "successful", "paid", "cleared", "clear",
            "settled", "realized", "received", "realised", "captured"]

Files without a status column (e.g., Shivalik Payment) have no status filter - all records are loaded.

Current Lender Configurations

Lender Key	File Type	Loan ID Column	Status Column	Date Format
arthmate	NACH Status	loan_id	Status (CLEAR/BOUNCE)	%d-%m-%Y
dmi	NACH Status	Opportunity Name	Status (Clear/Bounce)	auto
dmi_payment	Payment Report	loan_number	status (Received)	%Y-%m-%d %H:%M
gb	Cashfree NACH	Link ID	Transaction Status (SUCCESS)	%Y-%m-%d %H:%M:%S
ikf	NACH Status	Consumer Reference Number	Status (Cleared)	%d.%m.%Y
shivalik	NACH Status	LAN	settlement_status (SUCCESS)	%d-%m-%Y
shivalik_payment	Payment Report	Ref 1	(none)	auto
ugro	Cashfree NACH	Link ID	Transaction Status (SUCCESS)	%Y-%m-%d %H:%M:%S
ugro_razorpay	RZP Settlement	order_receipt	(amount > 0 only)	%d/%m/%Y %H:%M:%S
vivriti	RZP Settlement	order_receipt	(amount > 0 only)	%d/%m/%Y %H:%M:%S

Loan ID Resolution

Some lenders include the loan ID embedded in a larger string. The BASE dbt layer handles parsing:

Lender	Raw Value	Parsed Loan ID	Method
GB	GB00682_Apr26_NitinSingh	GB00682	split_part(loan_id, '_', 1)
UGRO	TURNO01446_Apr26_Name	TURNO01446	split_part(loan_id, '_', 1)
Vivriti	VCPL003039_Mar26_Shiva	VCPL003039	split_part(loan_id, '_', 1)
UGRO Razorpay	TURNO00991_ENACH	TURNO00991	split_part(loan_id, '_', 1)

UMRN Fallback (GB & UGRO Cashfree)

When Link ID is blank in Cashfree NACH files, the system:

1. Extracts subReferenceId from the Order Note column
2. Looks up the UMRN Master table to resolve the loan ID
3. Falls back to blank (record flagged as bad) if no match

See UMRN Master for details.