LOS Backend Database Documentation

Complete schema reference for the Loan Origination System — multi-tenant, multi-brand lending platform.

Database

PostgreSQL

ORM

Prisma

Total Tables

~120+

Schema File

prisma/schema.prisma

Overview System Overview

This is a Loan Origination System (LOS) — a multi-tenant SaaS platform where multiple lending companies (Brands) operate independently on the same infrastructure.

Brands = lending companies (tenants). Each brand has its own config, loan rules, users, and partner staff.
Partner Users = internal staff — credit executives, collection agents, operations, admins.
Users = end customers applying for loans.
The platform manages the complete loan lifecycle: onboarding → KYC → application → BRE evaluation → disbursement → repayment → closure.

High-Level Data Flow

Brand (Tenant)
  ├── BrandConfig          → runtime settings, feature flags
  ├── BrandProviders       → which 3rd-party APIs to use per service
  ├── LoanRules            → products, eligibility, fee structures
  ├── PartnerUsers         → internal staff with roles + permissions
  └── Users (customers)
        ├── UserDetails    → personal info, KYC data
        ├── Documents      → PAN, Aadhaar files
        ├── Employment     → company, salary, payslips
        ├── UserBankAccount → account verified via penny drop
        └── Loans
              ├── Evaluation   → BRE rule results
              ├── LoanAgreement → e-sign via SignDesk/Signzy/Digitap
              ├── Disbursement → fee deductions, net amount
              ├── Repayment    → obligation schedule
              └── PaymentRequest → transactions → receipts

Reference Entity Relationship Flow


━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
 MASTER / LOOKUP DATA
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
master_states → master_districts → master_pincodes
master_banks  (standalone lookup: bank name, IFSC pattern)
master_category → master_value   (generic key-value config store)
  └── referenced by users (status_id, occupation_type_id, rejected_by_id)

brand_category → brand_value     (brand-level overrides of master_category/master_value)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
 BRAND  (multi-tenant root)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
brands
  ├── [1:1 config]
  │     ├── brand_configs
  │     ├── brand_details
  │     ├── brand_themes
  │     ├── brand_policy_links
  │     ├── brand_reloan_configs
  │     └── brand_loan_agreement_configs
  │
  ├── [operational]
  │     ├── brand_api_keys
  │     ├── brand_bank_account
  │     ├── brand_providers          (payment / KYC providers per brand)
  │     ├── brand_evaluation_items   → evaluation_item (master)
  │     ├── brand_status_reasons     → referenced by loan_status_brand_reasons
  │     ├── brand_non_repayment_dates
  │     ├── brand_sub_domains
  │     ├── brand_paths
  │     ├── brand_targets
  │     ├── brand_coupons            → loan_coupon_redemptions
  │     ├── brand_rating_matrix
  │     ├── brand_blogs
  │     ├── brand_setting_audit_logs (audit trail for brand config changes)
  │     └── brand_cards
  │
  ├── [notification / reminder]
  │     ├── notifications            → notification_targets → partner_users
  │     ├── email_reminder_configs
  │     ├── whatsapp_reminder_configs
  │     └── brand_acefone_configs    (dialer / voice config)
  │
  ├── [blocklists]
  │     ├── brand_blocklisted_pan
  │     ├── brand_blocklisted_mobile
  │     ├── brand_blocklisted_aadhar
  │     ├── brand_blocklisted_account_number
  │     └── brand_blocklisted_pincode
  │
  ├── [loan product rules]
  │     └── loan_rules
  │           ├── loan_rule_tenures (1:1)
  │           │     └── loan_charge_config → loan_charge_taxes
  │           └── loan_penalty
  │
  ├── partner_user_brand_roles       → partner_users (see Partner section)
  │
  └── users                          (see User section below)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
 USER  (customer / borrower)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
users
  ├── [1:1 profile]
  │     ├── user_details
  │     └── user_status               (current status snapshot)
  │
  ├── [auth / session]
  │     ├── user_login_tokens
  │     ├── user_otp_verifications
  │     └── devices                   (registered devices / push tokens)
  │
  ├── [lifecycle & tracking]
  │     ├── user_rejections
  │     ├── user_reloan
  │     ├── user_geo_tags
  │     ├── user_audit_logs
  │     ├── user_reminders            → user_reminder_audit_logs
  │     ├── user_salaries
  │     ├── onboarding_journey
  │     ├── utm_tracking
  │     ├── call_me_requests
  │     ├── user_notifications
  │     ├── reloan_automation_log
  │     └── user_allotted_partner_user_logs   (which partner user is assigned)
  │
  ├── [contact / address]
  │     ├── alternate_addresses
  │     └── alternate_phone_numbers
  │
  ├── [identity & KYC documents]
  │     ├── documents  (PAN + Aadhaar)
  │     │     └── pan_aadhaar_verification → pan_aadhaar_address
  │     ├── other_documents
  │     ├── pan_details_log             (PAN API call log)
  │     ├── mobile_to_pan_verification  (mobile ↔ PAN cross-check)
  │     ├── mobile_verification_logs    (OTP / mobile verification history)
  │     ├── aadhaar_digi_locker_log     (DigiLocker callback log)
  │     ├── digitap_some_table          (Digitap KYC session data)
  │     └── kyccart_some_table          (KYCCart session data)
  │
  ├── [calls / communication]
  │     └── user_calls
  │           ├── user_call_events      (individual call events)
  │           └── user_call_recordings  (recording files)
  │
  ├── [employment]
  │     ├── employment (1:1)
  │     │     └── payslips
  │     ├── previous_employments
  │     ├── phone_to_uan_log            (mobile → UAN lookup log)
  │     └── uan_to_employment_log       (UAN → employment details log)
  │
  ├── [bank account & statements]
  │     └── user_bank_account
  │           ├── penny_drop_log        (penny-drop verification log)
  │           └── bank_account_statement
  │                 ├── finbox_some_table
  │                 ├── scoreme_some_table
  │                 └── cart_some_table
  │
  ├── [credit bureau]
  │     ├── equifax_some_table
  │     ├── cirprov2_some_table
  │     ├── user_cibil_reports
  │     └── user_cibil_report_logs
  │
  ├── [scoreme / BDA]
  │     └── scoreme_bda_report
  │
  ├── [account aggregator]
  │     └── aa_consent_requests
  │           ├── aa_consent_notifications
  │           ├── aa_data_sessions → aa_financial_insights
  │           └── aa_periodic_data_responses
  │
  ├── [public inquiry]
  │     └── public_loan_inquiries       (pre-login / open loan inquiry form)
  │
  └── loans                             (see Loan section below)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
 LOAN  (the core lending lifecycle)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
loans
  ├── [1:1 meta]
  │     ├── loan_details
  │     └── cam_calculators
  │
  ├── [financials]
  │     ├── disbursements → deductions → tax_deductions
  │     ├── repayments    → fee_breakdowns → tax_fee_breakdowns
  │     ├── cost_summaries
  │     ├── early_repayment_discounts
  │     ├── penalties
  │     ├── loan_ledger                  (full GL ledger entries)
  │     └── repayment_timelines          (partner-assigned repayment schedule)
  │
  ├── [evaluation]
  │     └── evaluations → evaluation_item
  │
  ├── [status history]
  │     └── loan_status_history
  │           └── loan_status_brand_reasons
  │
  ├── [agreements / esign]
  │     └── loan_agreements
  │           └── loan_agreement_references
  │                 ├── signdesk_some_table
  │                 ├── signzy_some_table
  │                 └── digitap_esign_some_table
  │
  ├── [unsigned data]
  │     └── unsigned_data_agreement     (pre-esign data capture)
  │
  ├── [documents / ops]
  │     ├── loan_no_due_certificates
  │     ├── loan_email_logs
  │     ├── loan_xlsx_files
  │     ├── loan_coupon_redemptions      → brand_coupons
  │     ├── field_visit
  │     ├── loan_allotted_partner_user_logs   (partner assignment per loan)
  │     └── loan_collection_allocated_partner_logs
  │
  ├── [payments]
  │     └── payment_request  (one per payment attempt)
  │           ├── payment_disbursal_transaction
  │           │     └── payment_provider_log  (raw API log)
  │           ├── payment_collection_transaction
  │           │     ├── payment_transaction_receipt
  │           │     └── payment_provider_log
  │           ├── payment_partial_collection_transaction
  │           │     ├── payment_transaction_receipt
  │           │     └── payment_provider_log
  │           └── payment_autopay_transaction
  │
  └── [lendbox / co-lending]
        ├── lendbox_workflow
        └── lendbox_api_logs             (Lendbox API call log)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
 PARTNER USER  (internal team: ops, credit, collections, etc.)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
partner_users
  ├── reportsTo / subordinates (self-referential hierarchy)
  │
  ├── [auth]
  │     ├── partner_login_tokens
  │     ├── partner_otp_verifications
  │     └── partner_user_password_reset_tokens
  │
  ├── [roles & permissions]
  │     ├── partner_user_brand_roles  → partner_roles (brand-scoped role)
  │     ├── partner_user_global_roles → partner_roles (global role)
  │     ├── partner_user_permissions  → partner_permissions (granular permissions)
  │     └── partner_user_primary_permissions → partner_permissions (primary permission)
  │
  ├── [activity]
  │     ├── partner_user_login_logs
  │     ├── partner_user_activity_sessions → partner_user_activity_logs
  │     └── partner_user_inactivity_alerts
  │
  ├── [codes & audits]
  │     ├── partner_user_codes
  │     ├── partner_user_code_audit_logs
  │     ├── partner_user_audit_logs        (action audit trail)
  │     └── partner_user_report_logs       (downloaded / generated reports)
  │
  ├── [availability & config]
  │     ├── partner_unavailability_dates
  │     ├── partner_user_dialer_configs
  │     └── partner_user_rating_matrix
  │
  ├── [loan ops (cross-references)]
  │     ├── loan_status_history            (partner who changed status)
  │     ├── loan_allotted_partner_user_logs
  │     ├── loan_collection_allocated_partner_logs
  │     ├── user_allotted_partner_user_logs
  │     ├── service_request_logs           (partner who handled service req)
  │     └── repayment_timelines            (partner-created schedules)
  │
  └── partner_user_rules (1:1)             (custom rule overrides per agent)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
 SERVICE REQUESTS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
service_request_logs   → loan (FK)   → partner_user (assigned to)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
 LEAD MANAGEMENT
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
lead_forms → lead_matches → users / documents

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
 NOTIFICATION SYSTEM
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
notifications (created by partner_user, scoped to brand/partner_role)
  └── notification_targets → partner_users

whatsapp_message_logs  (outbound WhatsApp messages, linked to user/loan)

Total tables: 168. Every model in the Prisma schema is represented above. The tree is grouped by domain: Master/Lookup → Brand → User → Loan → Partner User → Service Requests → Leads → Notifications. Cross-domain foreign-key references (e.g. a loan referencing a partner_user) are shown inline with a → arrow. (1:1) denotes a strict one-to-one relation. All other branches are one-to-many unless noted.

Module 1 Brand & Configuration

🏢

Multi-tenant setup. Every customer, loan, and partner user belongs to a brand.

Brands are the top-level tenants. All configuration, rules, integrations, and data are scoped per brand.

~25 tables

brands

The central tenant table. Everything in the system is scoped to a brand.

Column	Type	Description
id	UUID PK	Brand identifier
name	String UNIQUE	Brand name
domain	String UNIQUE	Custom domain
loanPrefix	String UNIQUE	Prefix for loan IDs (e.g. `8BL`)
customerPrefix	String?	Prefix for customer IDs
defaultLoanRiskCategory	Enum?	Default risk bucket for loans
onPartner	Boolean	Whether brand uses partner portal
partner_version / web_version	String?	App version tags
is_lendbox_integrated	Boolean	Lendbox co-lending integration
max_multi_device_session	Int	Max concurrent sessions per user
isUATServices	Boolean	UAT/test mode flag

brand_configs

Per-brand feature flags and workflow settings — the main configuration table controlling all business logic toggles.

Column	Type	Description
brandId	UUID UNIQUE FK	→ brands
salaryThresholdAmount	Int	Minimum salary for eligibility
rejectionDuration	Int	Days before rejected user can re-apply (default: 15)
bankStatementHistoryMonths	Int	How many months of bank statements required
esignExpiryDayCount	Int	E-sign link expiry in days (default: 2)
isAA	Boolean	Enable Account Aggregation flow
loanAgreementVersion	Int	Loan agreement template version
minLoanAmountRequired	Int	Minimum loan amount (default: 15000)
forceEmployment	Boolean	Require employment data
autoAllocationType	String	Auto-allocation trigger (LOGIN / etc.)
evaluationVersion	String	BRE version (V1/V2)
collection_auto_allocation	Boolean	Auto-assign collection agents
loan_auto_allocation	Boolean	Auto-assign credit executives
user_auto_allocation	Boolean	Auto-assign users to partners
enable_central_dedup	Boolean	Central deduplication check
max_age / min_age	Int	Customer age eligibility bounds
is_cam_calculation_required	Boolean	CAM (Credit Appraisal Memo) required
is_automated_reloan	Boolean	Auto re-loan trigger after closure
user_auto_rejection	Boolean	Auto-reject stale users
user_auto_rejection_days	Int	Inactivity days before auto-reject
sunday_off	Boolean	Exclude Sundays from repayment calculation
requiresUserPhoto / requiresUserVideo	Boolean	Selfie / video KYC required
skip_loan_evaluation_approval_days	Int	Days after which evaluation approval is auto-skipped
dashboard_version / loan_ops_version	String	UI version tags

brand_providers

Maps which third-party provider a brand uses for each service type. Supports primary/secondary provider fallback.

Column	Description
type	`PENNYDROP`, `LOAN_AGREEMENT`, `BRE`, `PAN_DETAILS_PLUS`, `DISBURSEMENT`, `SMS`, `EMAIL`, `WHATSAPP`, `ACCOUNT_AGGREGATION`, etc.
provider	`DIGITAP`, `SIGNDESK`, `SIGNZY`, `FINBOX`, `SCOREME`, `RAZORPAY`, `CASHFREE`, `MSG91`, `FINDUIT`, etc.
isPrimary	Default provider for this type
isActive / isDisabled	Status flags

Constraint: UNIQUE(brandId, type, provider) — each brand-provider-type combo is unique.

brand_evaluation_items

BRE (Business Rule Engine) rules configured per brand. Defines what parameters are evaluated during loan underwriting.

Column	Description
parameter	What to evaluate (e.g. CREDIT_SCORE, SALARY, AGE)
requiredValue	Pass condition value
sources	Array of data sources (e.g. CIBIL, AA, BSA)
stage	Evaluation stage: ONE, TWO, THREE, FOUR
priority	Rule execution order

Blocklist Tables

Five tables blocking specific identifiers from applying for loans on a brand:

Table	Blocked Field	Notes
brand_blocklisted_pan	PAN card number	+ customer_name, reason, dpd
brand_blocklisted_mobile	Mobile number	+ customerName, reason, dpd
brand_blocklisted_aadhar	Aadhaar number	+ customerName, reason, dpd
brand_blocklisted_account_number	Bank account number	+ customerName, reason, dpd
brand_blocklisted_pincode	Pincode (serviceability)	+ reason, dpd

Other Brand Tables

Table	Purpose
brand_details	Legal info: GST, CIN, RBI registration, lender name, address
brand_policy_links	T&C, privacy policy, FAQ, contact URLs shown to users
brand_themes	White-label UI colors, fonts, dark mode config
brand_bank_account	Brand's bank accounts for disbursal & collection
brand_api_keys	API keys for programmatic access to the LOS
brand_loan_agreement_configs	Legal contact details embedded in loan agreements
brand_non_repayment_dates	Holidays — dates excluded from repayment schedule
brand_sub_domains	Multiple signup funnels per brand with marketing source tags
brand_targets	Monthly credit/debit disbursement targets
brand_coupons	Discount coupons for loan processing fees
brand_reloan_configs	Auto-reloan amount hike rules (fixed / percentage)
brand_rating_matrix	Scoring matrix (ratings 1–10 with weightages) per role/metric
brand_acefone_configs	Telephony dialer integration settings
brand_status_reasons	Configurable rejection/status reason codes
brand_paths	Navigation menu items for partner portal
brand_blogs	CMS blog posts per brand (DRAFT/PUBLISHED/ARCHIVED)
brand_category / brand_value	Flexible key-value config catalog per brand
email_reminder_configs	Email reminder templates with day offsets and loan status filters
whatsapp_reminder_configs	WhatsApp reminder template configs per brand

Module 2 Partner User Management

👥

Internal staff — credit executives, collection agents, operations, admins.

Partner users manage the loan workflow. They have brand-scoped roles, granular permissions, and a manager hierarchy.

~15 tables

partner_users

Column	Type	Description
id	UUID PK	Partner user ID
email	String UNIQUE	Login email
password	String	Hashed password (bcrypt)
name	String?	Display name
employee_id	String?	HR employee ID
phone_number	String?	Mobile number
reportsToId	UUID? FK → self	Manager (self-referential hierarchy)
isActive / is_disabled	Boolean	Account state flags
isReloanSupport	Boolean	Can handle reloan cases
is_fresh_loan_support	Boolean	Can handle fresh loan applications

Self-referential relation: reportsTo / subordinates builds a manager hierarchy tree for the entire org chart.

Role & Permission System

Table	Description
partner_roles	Named roles: CREDIT_EXECUTIVE, COLLECTION_AGENT, OPS, ADMIN, etc. Grouped by `role_group_id`
partner_user_brand_roles	Role assigned to a user within a specific brand. PK: `(brandId, partnerUserId, roleId)` — different roles across brands
partner_user_global_roles	Role assigned across all brands (super-admin). PK: `(partnerUserId, roleId)`
partner_permissions	Fine-grained permissions: VIEW_LOANS, EDIT_USER, DISBURSE, etc.
partner_user_permissions	Permission grants per user. Type: READ / WRITE / ALL
partner_user_primary_permissions	One default primary permission level per user

Activity & Session Tracking

Table	Description
partner_user_login_logs	Daily login tracking. `UNIQUE(partnerUserId, date)` — one record per user per day with first login + last logout
partner_user_activity_sessions	Full session records: startTime, endTime, totalEvents, inactiveTimeMs, pageViews[], userAgent, screenResolution
partner_user_activity_logs	Individual events per session: eventType, pageUrl, mouseX, mouseY, scrollPos, timestamp
partner_user_inactivity_alerts	Records when a user was warned about inactivity (inactiveTimeSeconds, lastActivityTime, currentPage)

Other Partner Tables

Table	Description
partner_login_tokens	JWT access + refresh tokens with device tracking and logout flag
partner_user_password_reset_tokens	Password reset flow: token, expiresAt, usedAt, redirectLink
partner_user_rules	Per-user overrides: isReloanSupport, isCxAllCustomer (see all customers, not just assigned)
partner_user_codes	Encrypted QR/invite codes with audit trail (generatedBy, deactivatedBy)
partner_user_code_audit_logs	Code generation/deactivation audit
partner_unavailability_dates	Leave dates — impacts auto-allocation of users/loans
partner_user_dialer_configs	Telephony: agent number, caller ID, skill routing
partner_user_rating_matrix	Monthly performance ratings (1–10) per metric type with weightages
partner_user_report_logs	Audit of report downloads/exports

Module 3 End User Management

🙋

End customers — borrowers applying for loans.

Each user is tied to one brand. Complete lifecycle tracking from signup to loan closure.

~20 tables

users

Primary customer entity.

Column	Type	Description
id	UUID PK
email / phoneNumber	String?	UNIQUE per brand (same contact can register on multiple brands)
brandId	UUID FK	→ brands
brandSubDomainId	UUID? FK	Which subdomain/funnel they signed up from
formattedUserId	String UNIQUE	Human-readable ID (e.g. CUST00123)
onboardingStep	Int	Current onboarding step (1-based)
isActive	Boolean	Active account
isEmailVerified / isPhoneVerified / isWhatsappVerified	Boolean	Verification flags
migrationStatus	Enum	ONBOARDED_NEW / MIGRATED / PARTIALLY_MIGRATED
pan_number / aadhaar_number	String?	Masked PAN (10 chars) and Aadhaar (last 4)
is_pan_verified / is_aadhaar_verified	Boolean?	Verification status
is_aadhaar_pan_linked	Boolean?	Aadhaar-PAN linkage status
allocated_partner_user_id	UUID?	Assigned credit executive / agent
allocated_at	DateTime?	Assignment timestamp
status_id	BigInt FK → master_value	User status from master data
occupation_type_id	BigInt FK → master_value	Job category
is_auto_rejected	Boolean?	System auto-rejected flag
lendbox_user_id	String?	Co-lending platform user reference

user_details

Extended personal profile — one-to-one with users.

Column	Description
firstName / middleName / lastName	Name parts
gender / dateOfBirth	Demographics
address / city / state / pincode	Current address
profilePicUrl / profileVideoUrl	KYC selfie / video
fathersName / mothersName / spouseName	Family details
maritalStatus / religion / residenceType	Personal info
creditScore	Stored credit score
aAdharName / aAdharDOB	Aadhaar-matched name & DOB
pan_name / pan_dob / pan_fathername	PAN-matched details
geoLatitude / geoLongitude / geolocation_pincode	GPS coordinates & derived pincode
isFraudulentByCMS	Fraud flag from CART/CMS provider
linkedAadhaarNumberByDigiLocker / linkedAadhaarNumberByPanPlus	Aadhaar confirmed via providers
aadhaar_digilocker_gz_key / pan_details_gz_key	S3 keys for compressed KYC data
profile_photo_key / profile_video_key	S3 keys for media files

user_status

KYC and loan eligibility status — one-to-one with users.

Column	Description
kycCompleted	Full KYC done flag
loanEligible	Eligible for a loan
accountStatus	ACTIVE / HOLD / PENDING / REJECTED
currentStatus	SALARIED / SELF_EMPLOYED / OTHER
termsAccepted	T&C accepted by customer

Other User Tables

Table	Description
user_login_tokens	JWT access + refresh tokens with device tracking
user_otp_verifications	OTP attempts (6-digit, typed, used flag, expiry)
user_rejections	History of rejections with type, source (ADMIN/SYSTEM), and end date
user_reloan	Re-loan requests linking customer to their previous loan
user_geo_tags	GPS location records (reverse-geocoded to address)
user_audit_logs	Full event audit trail with serial number per user
user_status_brand_reasons	Specific rejection/status reasons attached to user status
alternate_addresses	Additional addresses (office, parent) with proof document
alternate_phone_numbers	Secondary contacts with relationship and OTP verification
user_reminders	Scheduled SMS/WhatsApp/push reminders with retry logic
user_reminder_audit_logs	Reminder lifecycle events
user_salaries	Multiple salary entries per month (when multi-salary is enabled)
utm_tracking	Marketing attribution: UTM params, landing page, fbclid, clickid
onboarding_journey	Step-by-step signup funnel progress. `UNIQUE(userId, stepNumber)`
user_allotted_partner_user_logs	History of which partner user was assigned to the customer
user_cibil_reports	Stored credit bureau reports (score, HTML/JSON/PDF keys)
user_cibil_report_logs	API call logs for all credit bureau queries
call_me_requests	Customer callback requests with message and resolution flag
user_notifications	In-app notifications for customers (type, JSON payload, readAt)
devices	Device fingerprints (fpId, OS, appVersion, fcmToken, platformType)

Module 4 KYC & Document Verification

🪪

Identity documents, verification results, and call records.

Tracks PAN, Aadhaar, other documents, and all third-party verification API results.

~8 tables

documents

Primary KYC documents — PAN and Aadhaar. One of each per user.

Column	Description
type	`AADHAAR` or `PAN`
frontDocumentUrl / backDocumentUrl	Document image URLs
documentNumber	PAN/Aadhaar number (trigram indexed)
status	PENDING / APPROVED / REJECTED
userDataStatus	NOT_VERIFIED / VERIFIED_BY_USER / VERIFIED_BY_ADMIN
providerData	JSON response from verification provider
isApprovedByAdmin	Manual admin approval flag

Constraint: UNIQUE(userId, type) — one PAN and one Aadhaar per user.

pan_aadhaar_verification

Result of PAN + Aadhaar cross-verification (checks if Aadhaar is linked to PAN). One-to-one with document.

pan_aadhaar_address

Address parsed from the PAN+Aadhaar verification response. One-to-one with verification.

other_documents

Secondary documents — voter ID, driving license, passport, salary slips, Form 16, ITR, rent agreement, etc. Can be linked to a specific loan.

Call Records

Table	Description
user_calls	Call session linking partnerUser + user + brand + optional loan
user_call_events	Telephony events: callStatus, duration, answerDuration, fromNumber, toNumber, endCallCause
user_call_recordings	Recording file reference, startTime, endTime, filePrivateUrl

repayment_timeline

Promise-to-Pay (PTP) records — documents/evidence uploaded by collection agents during calls.

field_visit

Flag record indicating whether a field visit is required for a specific loan.

service_request_logs

Full API request/response audit log: action, method, URL, IP, userAgent, requestBody, responseStatus, responseTime.

Module 5 Employment & Bank Details

🏦

Employment information, payslips, and bank accounts.

Income verification data used for loan eligibility and disbursal.

5 tables

employment

Current employment — one per user.

Column	Description
companyName / designation	Job details
joiningDate / salary	When they joined and their monthly salary
companyAddress / pinCode	Office location
uanNumber	EPFO Universal Account Number
modeOfSalary	BANK_TRANSFER / CASH / CHEQUE / OTHER
employmenttype	FULL_TIME / PART_TIME / CONTRACT / INTERN / FREELANCE / GIG / etc.
expectedDateOfSalary	Day of month salary is credited (default: 5)
officialEmail	Office email address
userDataStatus	Verification status

payslips

Monthly salary slips. UNIQUE(userId, employmentId, month, year)

Column	Description
month / year	Payslip period
filePrivateKey	S3 key for the PDF
filePassword	Password if PDF is encrypted

user_bank_account

Column	Description
accountNumber / ifscCode / bankName	Account details
accountType	Current / Savings
isPrimary	Primary account for disbursal
verificationMethod	MANUAL / PENNY_DROP / API
verificationStatus	PENDING / VERIFIED / FAILED / RETRYING
pennyDropResponse / pennyDropStatus	Last penny drop API result
pennyVerifiedName	Account holder name returned from penny drop

bank_account_statement

Bank statement files per account. Has fraud detection fields (isFraud, fraudScore) and is the parent for BSA analysis tables (FinBox, ScoreMe, CART).

previous_employments

Past employment records from EPFO. Stores UAN, memberId, establishment name, joining/exit dates.

Module 6 Loan Lifecycle

📋

The core of the LOS — from application to closure.

Loan products, rules, fees, evaluations, agreements, ledger, and all lifecycle tracking.

~20 tables

Loan Status Flow

ONBOARDING→ PENDING→ CX_APPROVED→ SM_APPROVED→ APPROVED→ AWAITING_DISBURSAL→ DISBURSED→ ACTIVE→ PARTIALLY_PAID→ PAID / COMPLETED

Terminal states: REJECTED CANCELLED DEFAULTED OVERDUE WRITE_OFF SETTLED

loan_rules

Loan product definitions per brand. Each rule type is a distinct product (e.g. LOAN-1, LOAN-2).

Column	Description
ruleType	Loan product type string (e.g. LOAN-1)
minAmount / maxAmount	Loan amount range
maxCompleteLoanCount	Max loans allowed per customer
isActive / isDisabled / isVisible	Status flags

loan_rule_tenures

Tenure settings for a loan rule (one-to-one).

Column	Description
loan_type	EMI or PAYDAY_LOAN
minTermDays / maxTermDays	Duration range
gracePeriod	Post-due grace days
allowPrepayment	Early repayment allowed
minPostActiveTermDays	Minimum days in active state before post-active
is_fee_always_principal	Fee calculated on principal only
is_skip_disbursement_day_fee	No fee charged on disbursement day

loan_charge_config + loan_charge_taxes

Fee structure per loan rule/tenure. Each fee can have tax components.

Column	Description
type	processing / convenience / documentation / interest
valueType	fixed or percentage
chargeValue	Fee rate or amount
chargeMode	INCLUSIVE (inside amount) or EXCLUSIVE
isRecurringDaily	Daily recurring fee (overdue interest)

loans

The central loan record.

Column	Description
formattedLoanId	Human-readable loan ID (e.g. 8BL000123)
amount	Requested amount
status	Current loan status (see flow above)
ruleType	Loan product applied (FK logic to loan_rules)
applicationDate / approvalDate / disbursementDate / closureDate	Key lifecycle dates
loan_due_date / loan_tenure_days	Repayment due date and term
closingType	NORMAL / SETTLEMENT / WRITE_OFF
loan_applied_amount	What customer requested
loan_cx_approved_amount	Amount approved by Credit Executive
loan_sm_sh_approved_amount	Amount approved by Sanction Manager
loan_cx_assigned_partner_user_id	Assigned credit executive
loan_collection_assigned_partner_user_id	Assigned collection agent
is_repeat_loan	Is this a re-loan
is_cam_calculation_required	CAM required for this specific loan
lendbox_loan_id	Co-lending platform reference
forceBsaReportByPass / forceCreditReportByPass	Bypass BSA/CIBIL checks

Financial Calculation Tables

Table	Description
loan_details	Computed schedule: durationDays, dueDate, principal, grossPeriod, postActiveDate, prepayment bounds
disbursements	Disbursal math: grossAmount, totalDeductions, netAmount (what customer receives)
deductions	Individual fee line items deducted upfront (processing fee, convenience, etc.)
tax_deductions	GST/TDS amounts on each deduction
repayments	Repayment obligation: totalObligation, totalFees
fee_breakdowns	Individual fee components in repayment schedule
tax_fee_breakdowns	Tax on each repayment fee
cost_summaries	High-level: totalTaxes, effectiveAPR
early_repayment_discounts	Discount amount offered for prepayment
penalties	Applied overdue penalties on the loan
loan_ledger	Double-entry ledger: principal/interest/penalty components per entry with running balances

cam_calculators

Credit Appraisal Memo filled by credit executives. Records 3 months of salary data, FOIR calculation, ROI, obligations, and recommended loan amount.

evaluations + evaluation_item

BRE evaluation record per loan. Each evaluation_item is one rule result: parameter, requiredValue, actualValue, source, ELIGIBLE/NOT_ELIGIBLE, manually overridden flag.

Loan Agreement Tables

Table	Description
loan_agreements	One per loan. Status: NOT_SENT → SENT → SIGNED / REJECTED / EXPIRED
loan_agreement_references	Each signing attempt. New reference on re-send. Provider: SIGNDESK / SIGNZY / DIGITAP
signdesk_some_table	SignDesk-specific response/callback data per reference
signzy_some_table	Signzy-specific data per reference
digitap_esign_some_table	Digitap e-sign data per reference

Other Loan Tables

Table	Description
loan_status_history	Immutable audit of all status changes with partner who changed it and reason
loan_status_brand_reasons	Specific brand reasons attached to a status history entry
loan_no_due_certificates	NOC/closure certificates: NO_DUE_LETTER / WRITE_OFF_LETTER / SETTLEMENT_LETTER
loan_email_logs	Email delivery records (type, recipient, success, error)
loan_xlsx_files	Excel files for PAYOUT and BENEFICIARY reports
loan_coupon_redemptions	Coupon usage per loan with pre/post-discount values
loan_allotted_partner_user_logs	History of loan → credit executive assignments
loan_collection_allocated_partner_logs	History of loan → collection agent assignments with deallocation tracking
reloan_automation_log	Log of automated reloan eligibility checks

Module 7 Payments & Transactions

💳

All money movement — disbursal, collection, partial payments, autopay.

Every payment flows through a PaymentRequest which spawns typed transactions.

7 tables

Payment Flow

Loan
  └── PaymentRequest  (one per type per loan — DISBURSEMENT / COLLECTION / PARTIAL_COLLECTION / AUTOPAY_CONSENT)
        ├── PaymentDisbursalTransaction         → bank transfer to customer
        ├── PaymentCollectionTransaction        → full repayment from customer
        │     └── PaymentTransactionReceipt     → receipt PDF
        ├── PaymentPartialCollectionTransaction → part-payment
        │     └── PaymentTransactionReceipt
        └── payment_autopay_transaction         → UPI mandate setup

All API calls → PaymentProviderLog

payment_request

Column	Description
type	DISBURSEMENT / COLLECTION / PARTIAL_COLLECTION / AUTOPAY_CONSENT
status	PENDING / SUCCESS / FAILED / RETRYING / CANCELLED / TIMEOUT
currency	Default INR

Constraint: UNIQUE(loanId, type) — one request per transaction type per loan.

payment_disbursal_transaction

Column	Description
amount	Decimal(12,2) — net amount transferred
method	MANUAL / RAZORPAY / CASHFREE / ICICI / IDFC / etc.
accountHolderName / bankAccountNumber / ifscCode	Destination account
transferMode	NEFT / IMPS / UPI
externalRef	Provider's transaction reference ID
retryCount	Retry attempts
responseJSON	Raw provider response
createdByPartnerId / opsPartnerId	Who initiated / who ops-approved

payment_collection_transaction

Column	Description
totalFees / totalTaxes / totalPenalties	Breakdown of amounts collected
penaltyDiscount	Discount waived on penalty
opsApprovalStatus	PENDING / APPROVED / REJECTED (ops must approve)
closingType	NORMAL / SETTLEMENT / WRITE_OFF
isPaymentComplete	Full settlement flag
isReloanApplicable	Trigger reloan offer after this collection
principalAmount / excessAmount / roundOffDiscount	Detailed amount breakdown
paymentLink	Payment link URL sent to customer
platformType	WEB or PARTNER

payment_autopay_transaction

UPI autopay mandate setup. Stores maxAuthorizedAmount for the mandate, metadata, and provider callback in responseData.

payment_transaction_receipt

Receipt record generated after a successful collection or partial-collection. One-to-one with the collection transaction. Stores receipt PDF S3 key, receipt number, and generated timestamp.

payment_provider_log

Raw API call logs: provider, apiEndpoint, correlationId, requestHeaders/Body, responseBody/Status, errorMessage.

Module 8 Third-Party API Integrations

🔌

External provider integrations — KYC, credit, BSA, e-sign, disbursal.

Each provider has its own log/response table for raw data storage and audit.

~15 tables

Bank Statement Analysis (BSA)

Table	Provider	Key Fields
finbox_some_table	FinBox	sessionId, statementId, initiateProcessing
scoreme_some_table	ScoreMe	referenceId, bsaReportJson, bsaReportXlsxPrivateKey
cart_some_table	CART	referenceId, bsaReportJson, bsaReportDownloadJson

Credit Bureau Reports

Table	Provider	Key Fields
equifax_some_table	Equifax	braReportJson, cibil_pdf_key, cibil_json_gz_key, generated_cibil_report_html_key, password
cirprov2_some_table	CIR Pro V2	rawReportJson, cibil_html_key, cibil_json_gz_key

KYC Providers

Table	Provider	Key Fields
digitap_some_table	Digitap	panDetailsPlus, mobilePrefill, pennyDropResponse, intiateKycAuto, kycUnifiedv1Details
kyccart_some_table	KYC CART	mobileAge, employmentHistory
aadhaar_digi_locker_log	DigiLocker	requestType, digiLockerId, callbackData

Verification Logs

Table	What It Logs
penny_drop_log	Bank account penny drop: accountNumber, ifsc, status (SUCCESS/FAILED/NAME_MISMATCH), nameMatch
pan_details_log	PAN verification: pan, status, panHolderName, aadhaarLinked, cachedResponseData
phone_to_uan_log	Phone → EPFO UAN lookup
uan_to_employment_log	UAN → Employment history lookup
mobile_to_pan_verifications	Phone → PAN reverse lookup (Truecaller-style)
mobile_verification_logs	General mobile verification API calls

Lendbox (Co-lending)

Table	Description
lendbox_api_logs	API call logs to Lendbox platform
lendbox_workflow	Step-by-step workflow state: step_name, step_status, retry_count

scoreme_bda_report

ScoreMe Bureau Data Analysis (BDA) report per user. Stores the BDA report JSON response and report metadata returned by the ScoreMe bureau analysis API. One record per user.

unsigned_data_agreement

Stores the pre-signed loan agreement content (HTML/data) before the customer signs it.

Module 9 Notifications & Communication

🔔

In-app, email, and WhatsApp communication.

4 tables

Table	Description
notifications	Partner portal alerts: title, message, priority (HIGH/MEDIUM/LOW), scheduledAt, partnerRoleId target, brandId scope
notification_targets	Maps specific partner users to a notification. Tracks isRead, readAt, acknowledgedAt per user
whatsapp_message_logs	WhatsApp delivery records: phoneNumber, templateName, status, deliveredAt, readAt, aiSensyResponse
user_notifications	In-app notifications for customers: type, JSON payload, readAt

Module 10 Audit & Logs

📝

Complete audit trail for all system actions.

6 tables

Table	What It Audits
user_audit_logs	Customer journey events with serial number, platformType, context JSON
partner_user_audit_logs	Admin actions on partner user accounts
brand_setting_audit_logs	Changes to brand configuration
partner_user_code_audit_logs	Code generation and deactivation audit
partner_user_report_logs	Report download/export audit with format, dateRange, fileSize
public_loan_inquiries	Public loan status checks via mobile/PAN with OTP verification lifecycle

Module 11 Master / Reference Data

📚

System-wide reference tables — geography, banks, configurable dropdowns.

7 tables

Table	Description
master_states	Indian states: name, 2-letter code, isUT (Union Territory) flag
master_districts	Districts/cities linked to states. `UNIQUE(name, stateId)`
master_pincodes	6-digit pincodes with cityId, stateId, area, status (ACTIVE/BLOCKED/SUSPECTED)
master_banks	Bank list with name and code
master_category	Categories for dropdown values (e.g. USER_STATUS, REJECTION_TYPE)
master_value	Values within each category. Used for users.status_id, occupation_type_id, rejected_by_id
brand_category / brand_value	Brand-scoped configurable key-value catalog (same structure as master, but per-brand)
migrations	Database migration tracking: id, description, executed_at, checksum

Module 12 Lead Management

🎯

Facebook/Google ad leads with fuzzy matching against existing users.

2 tables

lead_forms

Leads captured from social media ad forms.

Column	Description
ad_id / campaign_id / form_id	Ad attribution IDs
full_name / email / phone / pan	Captured lead details
is_organic	Organic vs paid traffic
status	PENDING / PROCESSED / FAILED / DUPLICATE

lead_matches

Results of matching a lead against existing users or documents in the system.

Column	Description
entityType	USER or DOCUMENT (what was matched)
matchType	EXACT / PARTIAL / FUZZY
matchField	EMAIL / PHONE / PAN / FULL_NAME
confidence	Match confidence score (0.0 – 1.0)
status	ACTIVE / INACTIVE / VERIFIED / REJECTED

Module 13 Account Aggregation (AA)

🏛️

RBI's Account Aggregator framework for financial data sharing with customer consent.

Customer gives consent → bank shares transaction data → system analyzes it for loan underwriting.

5 tables

AA Flow

aa_consent_requests   ← customer gives consent via AA handle
  ├── aa_consent_notifications    ← status webhooks from AA provider
  ├── aa_periodic_data_responses  ← periodic data refresh callbacks
  └── aa_data_sessions            ← one session per bank account (FIP)
        └── aa_financial_insights ← extracted financial analytics (1:1)

aa_consent_requests

Column	Description
clientTransactionId	UNIQUE internal tracking ID
aaCustomerHandleId	Customer's AA handle (e.g. user@onemoney)
consentStatus	PENDING / ACTIVE / REJECTED / PAUSED / REVOKED / EXPIRED
provider	FINDUIT or CART
approvedAt / rejectedAt / revokedAt / expiresAt	Consent lifecycle timestamps

aa_data_sessions

Column	Description
fipId / fipName	Bank (Financial Information Provider) that shared data
maskedAccountNumber	Masked account number
status	PENDING / RECEIVED / PROCESSED / FAILED / EXPIRED
jsonData / xmlData / csvData	Raw financial data in various formats
transactionSummary / balanceInfo / accountDetails	Extracted structured data
aa_json_gz_key / aa_pdf_key / aa_report_key	S3 file keys
bsa_metadata / far_metadata	BSA and FAR analysis metadata
generated_bsa_report_html_key	S3 key for generated BSA report

Reference All Enums

loan_status_enum

PENDINGONBOARDINGCREDIT_EXECUTIVE_APPROVEDSANCTION_MANAGER_APPROVEDAPPROVEDAWAITING_DISBURSEMENTDISBURSEDACTIVEPARTIALLY_PAIDPOST_ACTIVEPAIDCOMPLETEDOVERDUEDEFAULTEDREJECTEDCANCELLEDSETTLEDWRITE_OFF

TransactionStatusEnum

PENDINGSUCCESSFAILEDRETRYINGCANCELLEDTIMEOUT

TransactionTypeEnum

DISBURSEMENTCOLLECTIONPARTIAL_COLLECTIONAUTOPAY_CONSENT

PaymentMethodEnum

MANUALRAZORPAYCASHFREEPAYTRINGICICIIDFCRAZORPAY_AUTOPAYLENDBOX

agreement_status_enum

NOT_SENTSENTSIGNEDREJECTEDEXPIRED

ContractSigningProvider

SIGNDESKSIGNZYDIGITAP

FeeType

processingconveniencedocumentationinterest

FeeValueType / ChargeMode

fixedpercentageINCLUSIVEEXCLUSIVE

TaxType

GSTTDSVATCESSCUSTOM

PenaltyType / closingTypeEnum

SIMPLECOMPOUNDNORMALSETTLEMENTWRITE_OFF

PermissionType

READWRITEALL

LoanTypeEnum

EMIPAYDAY_LOAN

DocumentTypeEnum

AADHAARPAN

document_status_enum

PENDINGAPPROVEDREJECTED

user_data_status

NOT_VERIFIEDVERIFIED_BY_USERVERIFIED_BY_ADMIN

AccountStatus

ACTIVEHOLDPENDINGREJECTED

gender_enum / current_status_enum

MALEFEMALEOTHERSALARIEDSELF_EMPLOYED

marital_status_enum

SINGLEMARRIEDDIVORCEDWIDOWED

ModeOfSalary

BANK_TRANSFERCASHCHEQUEOTHER

user_bank_verification

MANUALPENNY_DROPAPIPENDINGVERIFIEDFAILEDRETRYING

employment_type_enum

FULL_TIMEPART_TIMECONTRACTTEMPORARYINTERNFREELANCECASUALGIGAPPRENTICE

ResidenceTypeEnum

RENTEDOWNED

RelationshipEnum

SPOUSEBROTHERSISTERFATHERMOTHERFRIENDCOLLEAGUEOTHER

RejectionType / RejectionSource

DOCUMENT_INSUFFICIENTCREDIT_SCORE_LOWKYC_FAILEDADMINSYSTEMAUTOMATEDSUPPORT

platform_type

WEBPARTNER

MigrationStatus

ONBOARDED_NEWMIGRATEDPARTIALLY_MIGRATED

AAConsentStatus / AADataStatus

PENDINGACTIVEREJECTEDPAUSEDREVOKEDEXPIREDRECEIVEDPROCESSEDFAILED

AAProvider

FINDUITCART

PennyDropStatus

SUCCESSFAILEDPENDINGNAME_MISMATCH

PanVerificationStatus

SUCCESSFAILEDINVALIDPENDING

ReloanStatus

PENDINGAPPROVEDREJECTEDCANCELLED

EligibilityStatusEnum / evaluation_stage_enum

ELIGIBLENOT_ELIGIBLEONETWOTHREEFOUR

LeadFormStatus / LeadMatchType / LeadMatchField

PENDINGPROCESSEDFAILEDDUPLICATEEXACTPARTIALFUZZYEMAILPHONEPANFULL_NAME

EmailType

SEVEN_DAY_REMINDERTHREE_DAY_REMINDERONE_DAY_REMINDERSAME_DAY_REMINDEROVERDUE_REMINDERPAYMENT_DUE_REMINDERFINAL_NOTICECUSTOM_REMINDER

OpsApprovalStatusEnum

PENDINGAPPROVEDREJECTED

BrandProviderType

PENNYDROPLOAN_AGREEMENTBREPAN_DETAILS_PLUSPHONE_TO_UANUAN_TO_EMPLOYMENTUPI_AUTOPAYFULL_PAYMENTPART_PAYMENTAADHAAR_DIGILOCKERDISBURSEMENTSMSEMAILWHATSAPPACCOUNT_AGGREGATION

BrandProviderName

DIGITAPNEOKREDSIGNDESKSIGNZYSIGNZY_HYBRIDFINBOXSCOREMECARTEQUIFAXCIRPROKYCKARTCMS_FINTECHCLOUDRAZORPAYRAZORPAY_AUTOPAYCASHFREEPAYTRINGICICIIDFCMANUALLENDBOXMSG91SENDGRIDBREVOMAILGUNZEPTONEXMONIMBUSITSMSGATEWAYHUBTWILIOFINDUIT

certificate_type_enum

NO_DUE_LETTERWRITE_OFF_LETTERSETTLEMENT_LETTER

NonRepaymentDateType

BRAND_NON_REPAYMENTPARTNER_UNAVAILABILITY

brand_status_enum

APPROVEDREJECTEDHOLDACTIVEPENDINGDORMANT

BlogStatus / pincode_status

DRAFTPUBLISHEDARCHIVEDACTIVEBLOCKEDSUSPECTED

PublicInquiryStatus

INITIATEDOTP_SENTOTP_VERIFIEDCOMPLETEDFAILEDEXPIRED

notification_priority_enum

HIGHMEDIUMLOW

Generated from prisma/schema.prisma · 2026-03-20 · LOS Backend Database Documentation