-- ===================================================================== -- FinLend Core — Production DDL -- Target : MySQL 8.0.16+ (InnoDB, utf8mb4) | MariaDB 10.6+ -- Entity : NBFC / Nidhi Company / MFI (JLG-SHG) — multi-entity -- Author : Infossel Soft Solutions -- --------------------------------------------------------------------- -- MONEY CONVENTION (read this before touching any amount column) -- --------------------------------------------------------------- -- Every monetary column is BIGINT and stores PAISE (1 INR = 100 paise). -- Rationale: exact integer arithmetic end-to-end in PHP with zero -- float drift, no DECIMAL<->float coercion at the PDO boundary, and -- penny-perfect reconciliation of amortisation schedules. Presentation -- layer divides by 100 exactly once, in FinLend\Core\Money. -- Rates are stored in BASIS POINTS (bps): 100 bps = 1.00%. -- --------------------------------------------------------------------- -- IMMUTABILITY -- Ledger (acc_journal_lines), audit_logs and loan_repayments are -- APPEND-ONLY. Corrections are contra/reversal rows, never UPDATEs. -- Triggers at the bottom of this file enforce this at the DB level so -- that a compromised application account still cannot rewrite history. -- ===================================================================== SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; SET @OLD_SQL_MODE = @@SQL_MODE; SET SQL_MODE = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; -- ===================================================================== -- 01. ORGANISATION, BRANCH & FINANCIAL CALENDAR -- ===================================================================== CREATE TABLE organisations ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, code VARCHAR(16) NOT NULL COMMENT 'Short tenant code, appears in all human-readable refs', legal_name VARCHAR(200) NOT NULL, trade_name VARCHAR(200) NULL, entity_type ENUM('nbfc','nidhi','mfi','nbfc_mfi','section8_mfi','coop') NOT NULL COMMENT 'Selects the regulatory rule-pack applied at runtime', cin VARCHAR(21) NULL COMMENT 'MCA Corporate Identity Number', rbi_cor_number VARCHAR(32) NULL COMMENT 'RBI Certificate of Registration (NBFCs)', rbi_layer ENUM('base','middle','upper','top') NULL COMMENT 'RBI Scale Based Regulation layer', nidhi_ndh4_status ENUM('not_applicable','pending','approved','rejected') NOT NULL DEFAULT 'not_applicable', gstin VARCHAR(15) NULL, pan VARCHAR(10) NULL, registered_address VARCHAR(500) NULL, incorporation_date DATE NULL, fy_start_month TINYINT UNSIGNED NOT NULL DEFAULT 4 COMMENT 'Indian FY starts in April', base_currency CHAR(3) NOT NULL DEFAULT 'INR', logo_path VARCHAR(255) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_org_code (code), UNIQUE KEY uq_org_cin (cin), KEY idx_org_active (is_active) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE branches ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, parent_branch_id INT UNSIGNED NULL COMMENT 'Self-reference: region -> cluster -> branch hierarchy', code VARCHAR(16) NOT NULL COMMENT 'Used as prefix in member/loan account numbers', name VARCHAR(150) NOT NULL, branch_type ENUM('head_office','regional','branch','collection_centre','business_correspondent') NOT NULL DEFAULT 'branch', address_line1 VARCHAR(200) NULL, address_line2 VARCHAR(200) NULL, city VARCHAR(100) NULL, district VARCHAR(100) NULL, state_code CHAR(2) NULL COMMENT 'GST state code, e.g. 33 = Tamil Nadu', pincode CHAR(6) NULL, latitude DECIMAL(10,7) NULL COMMENT 'For geo-fencing field collections', longitude DECIMAL(10,7) NULL, phone VARCHAR(20) NULL, email VARCHAR(150) NULL, opening_date DATE NULL, cash_limit BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Paise. Max cash-in-hand before forced remittance', -- Every branch has its own control accounts so the trial balance is -- provable branch-wise without scanning the whole journal. cash_gl_id INT UNSIGNED NULL, bank_gl_id INT UNSIGNED NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_branch_code (org_id, code), KEY idx_branch_org_active (org_id, is_active), KEY idx_branch_parent (parent_branch_id), CONSTRAINT fk_branch_org FOREIGN KEY (org_id) REFERENCES organisations (id) ON DELETE RESTRICT, CONSTRAINT fk_branch_parent FOREIGN KEY (parent_branch_id) REFERENCES branches (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Business-day control. Nothing may post to a CLOSED day; the EOD job -- flips the flag after NPA marking + accrual + trial-balance proof. CREATE TABLE business_days ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, branch_id INT UNSIGNED NULL COMMENT 'NULL = org-wide holiday', business_date DATE NOT NULL, status ENUM('open','closing','closed','reopened') NOT NULL DEFAULT 'open', is_holiday TINYINT(1) NOT NULL DEFAULT 0, holiday_reason VARCHAR(150) NULL, opened_by INT UNSIGNED NULL, closed_by INT UNSIGNED NULL, opened_at DATETIME(6) NULL, closed_at DATETIME(6) NULL, eod_report_hash CHAR(64) NULL COMMENT 'SHA-256 of the day-end trial balance snapshot', PRIMARY KEY (id), UNIQUE KEY uq_busday (org_id, branch_id, business_date), KEY idx_busday_status (org_id, status, business_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 02. RBAC — ROLES, PERMISSIONS, USERS, SESSIONS -- ===================================================================== CREATE TABLE roles ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, slug VARCHAR(48) NOT NULL COMMENT 'super_admin, admin, branch_manager, loan_officer, collector, accountant, auditor, customer', name VARCHAR(100) NOT NULL, description VARCHAR(255) NULL, scope ENUM('global','branch','self') NOT NULL DEFAULT 'branch' COMMENT 'Row-level data scope automatically injected into every query', is_system TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'System roles cannot be deleted', requires_2fa TINYINT(1) NOT NULL DEFAULT 0, max_approval_amount BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Paise. 0 = no sanction authority', created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_role_slug (org_id, slug), CONSTRAINT fk_role_org FOREIGN KEY (org_id) REFERENCES organisations (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE permissions ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, slug VARCHAR(64) NOT NULL COMMENT 'module.action e.g. loan.approve, repayment.reverse', module VARCHAR(32) NOT NULL, description VARCHAR(255) NULL, is_sensitive TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Sensitive grants force a maker-checker record', PRIMARY KEY (id), UNIQUE KEY uq_perm_slug (slug), KEY idx_perm_module (module) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE role_permissions ( role_id SMALLINT UNSIGNED NOT NULL, permission_id SMALLINT UNSIGNED NOT NULL, granted_by INT UNSIGNED NULL, granted_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (role_id, permission_id), KEY idx_rp_perm (permission_id), CONSTRAINT fk_rp_role FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE, CONSTRAINT fk_rp_perm FOREIGN KEY (permission_id) REFERENCES permissions (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE users ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, branch_id INT UNSIGNED NULL COMMENT 'Home branch. NULL for head-office/global users', employee_code VARCHAR(32) NULL, username VARCHAR(64) NOT NULL, email VARCHAR(150) NULL, mobile VARCHAR(15) NULL, full_name VARCHAR(150) NOT NULL, password_hash VARCHAR(255) NOT NULL COMMENT 'password_hash() with PASSWORD_ARGON2ID; bcrypt accepted for legacy rows', password_algo VARCHAR(16) NOT NULL DEFAULT 'argon2id', password_changed_at DATETIME(6) NULL, must_change_password TINYINT(1) NOT NULL DEFAULT 1, role_id SMALLINT UNSIGNED NOT NULL, -- TOTP secret is AES-256-GCM encrypted, never stored in plaintext twofa_secret_enc VARBINARY(255) NULL, twofa_enabled TINYINT(1) NOT NULL DEFAULT 0, twofa_recovery_codes VARBINARY(2048) NULL COMMENT 'Encrypted JSON array of single-use hashed codes', status ENUM('active','suspended','locked','resigned') NOT NULL DEFAULT 'active', failed_login_count TINYINT UNSIGNED NOT NULL DEFAULT 0, locked_until DATETIME(6) NULL, last_login_at DATETIME(6) NULL, last_login_ip VARBINARY(16) NULL COMMENT 'INET6_ATON packed, IPv4+IPv6 safe', -- Field-staff attributes (collectors / loan officers) is_field_staff TINYINT(1) NOT NULL DEFAULT 0, cash_holding_limit BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Paise. Max cash a collector may hold', device_binding_token VARCHAR(128) NULL COMMENT 'Ties the mobile app to one device', created_by INT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), deleted_at DATETIME(6) NULL COMMENT 'Soft delete — user rows are never hard-deleted (audit integrity)', PRIMARY KEY (id), UNIQUE KEY uq_user_username (org_id, username), UNIQUE KEY uq_user_employee (org_id, employee_code), KEY idx_user_branch (branch_id, status), KEY idx_user_role (role_id), KEY idx_user_email (email), CONSTRAINT fk_user_org FOREIGN KEY (org_id) REFERENCES organisations (id) ON DELETE RESTRICT, CONSTRAINT fk_user_branch FOREIGN KEY (branch_id) REFERENCES branches (id) ON DELETE RESTRICT, CONSTRAINT fk_user_role FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Extra per-user grants/denies layered on top of the role matrix CREATE TABLE user_permission_overrides ( user_id INT UNSIGNED NOT NULL, permission_id SMALLINT UNSIGNED NOT NULL, effect ENUM('allow','deny') NOT NULL, valid_until DATE NULL COMMENT 'Temporary elevation, e.g. leave cover', granted_by INT UNSIGNED NOT NULL, granted_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (user_id, permission_id), CONSTRAINT fk_upo_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_upo_perm FOREIGN KEY (permission_id) REFERENCES permissions (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Users may be granted visibility over branches beyond their home branch CREATE TABLE user_branch_access ( user_id INT UNSIGNED NOT NULL, branch_id INT UNSIGNED NOT NULL, access_level ENUM('read','write') NOT NULL DEFAULT 'read', PRIMARY KEY (user_id, branch_id), CONSTRAINT fk_uba_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_uba_branch FOREIGN KEY (branch_id) REFERENCES branches (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE user_sessions ( id CHAR(64) NOT NULL COMMENT 'SHA-256 of the session id — raw id never lands on disk', user_id INT UNSIGNED NOT NULL, ip_address VARBINARY(16) NULL, user_agent VARCHAR(255) NULL, device_fingerprint VARCHAR(128) NULL, channel ENUM('web','mobile_app','api') NOT NULL DEFAULT 'web', payload TEXT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), last_activity_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), expires_at DATETIME(6) NOT NULL, revoked_at DATETIME(6) NULL, PRIMARY KEY (id), KEY idx_sess_user (user_id, expires_at), KEY idx_sess_expiry (expires_at), CONSTRAINT fk_sess_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE login_attempts ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NULL, username_tried VARCHAR(64) NOT NULL, user_id INT UNSIGNED NULL, ip_address VARBINARY(16) NULL, user_agent VARCHAR(255) NULL, outcome ENUM('success','bad_password','unknown_user','locked','2fa_failed','2fa_success') NOT NULL, attempted_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), KEY idx_la_user_time (username_tried, attempted_at), KEY idx_la_ip_time (ip_address, attempted_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 03. MEMBERS / CUSTOMERS, KYC & GROUPS (SHG / JLG) -- --------------------------------------------------------------------- -- PII HANDLING -- Aadhaar and PAN are stored as: -- *_enc VARBINARY — AES-256-GCM ciphertext (nonce||tag||cipher) -- *_bidx CHAR(64) — HMAC-SHA256 blind index, so we can still do -- exact-match de-duplication without decrypting -- *_last4 CHAR(4) — display-safe suffix for the UI and receipts -- Under the Aadhaar Act (as amended) a lender that is not an -- authorised authentication agency must NOT retain the full Aadhaar -- number. Keep AADHAAR_MODE=offline_xml and populate -- aadhaar_reference_no from the offline eKYC XML instead; the -- aadhaar_enc column stays NULL unless legal counsel signs off. -- ===================================================================== CREATE TABLE members ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, branch_id INT UNSIGNED NOT NULL, member_no VARCHAR(32) NOT NULL COMMENT 'Human-readable: {BRANCH}{YY}{SEQ}, generated in a serialisable txn', member_type ENUM('individual','joint','proprietorship','partnership','pvt_ltd','llp','trust','shg','jlg') NOT NULL DEFAULT 'individual', -- ---- Identity ------------------------------------------------- salutation ENUM('Mr','Mrs','Ms','Dr','M/s') NULL, first_name VARCHAR(80) NOT NULL, middle_name VARCHAR(80) NULL, last_name VARCHAR(80) NULL, full_name_search VARCHAR(255) GENERATED ALWAYS AS (TRIM(CONCAT_WS(' ', first_name, middle_name, last_name))) STORED, father_or_spouse_name VARCHAR(160) NULL, mother_name VARCHAR(160) NULL, date_of_birth DATE NULL, gender ENUM('male','female','transgender','not_disclosed') NULL, marital_status ENUM('single','married','widowed','divorced','not_disclosed') NULL, -- ---- KYC identifiers (encrypted at rest) ---------------------- aadhaar_enc VARBINARY(255) NULL, aadhaar_bidx CHAR(64) NULL, aadhaar_last4 CHAR(4) NULL, aadhaar_reference_no VARCHAR(64) NULL COMMENT 'UIDAI offline eKYC reference — preferred over the raw number', aadhaar_verified_at DATETIME(6) NULL, aadhaar_verify_mode ENUM('offline_xml','okyc','digilocker','biometric','manual') NULL, pan_enc VARBINARY(255) NULL, pan_bidx CHAR(64) NULL, pan_last4 CHAR(4) NULL, pan_verified_at DATETIME(6) NULL, pan_name_match_score TINYINT UNSIGNED NULL COMMENT '0-100 fuzzy match of NSDL name vs entered name', voter_id VARCHAR(32) NULL, driving_licence VARCHAR(32) NULL, passport_no VARCHAR(32) NULL, ckyc_number VARCHAR(14) NULL COMMENT 'CERSAI CKYC Identifier', ckyc_downloaded_at DATETIME(6) NULL, -- ---- Contact & address ---------------------------------------- mobile VARCHAR(15) NOT NULL, mobile_verified_at DATETIME(6) NULL, alt_mobile VARCHAR(15) NULL, email VARCHAR(150) NULL, address_line1 VARCHAR(200) NULL, address_line2 VARCHAR(200) NULL, village_or_area VARCHAR(120) NULL, city VARCHAR(100) NULL, district VARCHAR(100) NULL, state_code CHAR(2) NULL, pincode CHAR(6) NULL, latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL, residence_type ENUM('owned','rented','parental','company_provided','other') NULL, years_at_address TINYINT UNSIGNED NULL, -- ---- Livelihood / MFI socio-economic profile ------------------ occupation VARCHAR(120) NULL, occupation_category ENUM('agriculture','allied_agri','trade','services','manufacturing','salaried','other') NULL, monthly_income BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Paise. Applicant only', annual_household_income BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Paise. RBI Microfinance Directions 2022 cap: Rs 3,00,000 for a loan to qualify as microfinance', household_size TINYINT UNSIGNED NOT NULL DEFAULT 1, earning_members TINYINT UNSIGNED NOT NULL DEFAULT 1, is_rural TINYINT(1) NOT NULL DEFAULT 0, ration_card_type ENUM('apl','bpl','antyodaya','none') NULL, -- ---- Nidhi membership ------------------------------------------ is_nidhi_member TINYINT(1) NOT NULL DEFAULT 0, shares_held INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Nidhi Rules 2014 r.9/r.11: min 10 equity shares (Rs 100) to be admitted', share_certificate_no VARCHAR(32) NULL, membership_date DATE NULL, -- ---- Banking ---------------------------------------------------- primary_bank_account_id BIGINT UNSIGNED NULL, -- ---- Risk / AML ------------------------------------------------- kyc_status ENUM('pending','in_progress','verified','rejected','re_kyc_due') NOT NULL DEFAULT 'pending', kyc_verified_at DATETIME(6) NULL, kyc_verified_by INT UNSIGNED NULL, kyc_next_review_date DATE NULL COMMENT 'RBI KYC MD: 2y high-risk / 8y medium / 10y low', risk_category ENUM('low','medium','high') NOT NULL DEFAULT 'low', is_pep TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Politically Exposed Person — mandates enhanced due diligence', aml_screening_status ENUM('not_screened','clear','potential_match','confirmed_match') NOT NULL DEFAULT 'not_screened', aml_screened_at DATETIME(6) NULL, is_blacklisted TINYINT(1) NOT NULL DEFAULT 0, blacklist_reason VARCHAR(255) NULL, -- ---- Portal access ---------------------------------------------- portal_user_id INT UNSIGNED NULL COMMENT 'Optional linked users row with role=customer', passbook_no VARCHAR(32) NULL, photo_path VARCHAR(255) NULL, signature_path VARCHAR(255) NULL, -- ---- Lifecycle -------------------------------------------------- status ENUM('prospect','active','dormant','closed','rejected','deceased') NOT NULL DEFAULT 'prospect', introduced_by_member_id BIGINT UNSIGNED NULL, relationship_officer_id INT UNSIGNED NULL COMMENT 'Owning loan officer', onboarded_channel ENUM('branch','field_app','web_portal','bc_agent','api') NOT NULL DEFAULT 'branch', created_by INT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), deleted_at DATETIME(6) NULL, PRIMARY KEY (id), UNIQUE KEY uq_member_no (org_id, member_no), -- Blind indexes give us duplicate detection without ever decrypting UNIQUE KEY uq_member_aadhaar_bidx (org_id, aadhaar_bidx), UNIQUE KEY uq_member_pan_bidx (org_id, pan_bidx), KEY idx_member_branch_status (branch_id, status), KEY idx_member_mobile (org_id, mobile), KEY idx_member_name (org_id, full_name_search(64)), KEY idx_member_kyc (org_id, kyc_status, kyc_next_review_date), KEY idx_member_officer (relationship_officer_id, status), KEY idx_member_risk (org_id, risk_category, is_pep), CONSTRAINT fk_member_org FOREIGN KEY (org_id) REFERENCES organisations (id) ON DELETE RESTRICT, CONSTRAINT fk_member_branch FOREIGN KEY (branch_id) REFERENCES branches (id) ON DELETE RESTRICT, CONSTRAINT fk_member_officer FOREIGN KEY (relationship_officer_id) REFERENCES users (id) ON DELETE SET NULL, CONSTRAINT fk_member_intro FOREIGN KEY (introduced_by_member_id) REFERENCES members (id) ON DELETE SET NULL, CONSTRAINT chk_member_hh CHECK (earning_members <= household_size) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE member_bank_accounts ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, member_id BIGINT UNSIGNED NOT NULL, account_holder_name VARCHAR(160) NOT NULL, account_no_enc VARBINARY(255) NOT NULL COMMENT 'AES-256-GCM', account_no_bidx CHAR(64) NOT NULL, account_no_last4 CHAR(4) NOT NULL, ifsc CHAR(11) NOT NULL, bank_name VARCHAR(120) NOT NULL, branch_name VARCHAR(120) NULL, account_type ENUM('savings','current','od','cc') NOT NULL DEFAULT 'savings', is_primary TINYINT(1) NOT NULL DEFAULT 0, penny_drop_status ENUM('not_done','pending','verified','failed') NOT NULL DEFAULT 'not_done', penny_drop_ref VARCHAR(64) NULL, penny_drop_name VARCHAR(160) NULL COMMENT 'Name returned by the bank — compare against account_holder_name', verified_at DATETIME(6) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_mba (member_id, account_no_bidx, ifsc), KEY idx_mba_member (member_id, is_primary), CONSTRAINT fk_mba_member FOREIGN KEY (member_id) REFERENCES members (id) ON DELETE CASCADE, CONSTRAINT chk_ifsc CHECK (ifsc REGEXP '^[A-Z]{4}0[A-Z0-9]{6}$') ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE member_nominees ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, member_id BIGINT UNSIGNED NOT NULL, nominee_name VARCHAR(160) NOT NULL, relationship VARCHAR(60) NOT NULL, date_of_birth DATE NULL, share_percent DECIMAL(5,2) NOT NULL DEFAULT 100.00, guardian_name VARCHAR(160) NULL COMMENT 'Mandatory when the nominee is a minor', address VARCHAR(400) NULL, mobile VARCHAR(15) NULL, applies_to ENUM('all','deposits','loans','shares') NOT NULL DEFAULT 'all', created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), KEY idx_nominee_member (member_id), CONSTRAINT fk_nominee_member FOREIGN KEY (member_id) REFERENCES members (id) ON DELETE CASCADE, CONSTRAINT chk_nominee_share CHECK (share_percent > 0 AND share_percent <= 100) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE kyc_documents ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, member_id BIGINT UNSIGNED NULL, loan_application_id BIGINT UNSIGNED NULL, doc_category ENUM('poi','poa','income','photo','signature','property','group_resolution','other') NOT NULL, doc_type VARCHAR(64) NOT NULL COMMENT 'aadhaar, pan, voter_id, ration_card, itr, salary_slip, bank_statement...', doc_number_masked VARCHAR(64) NULL, file_path VARCHAR(255) NOT NULL COMMENT 'Stored outside webroot; served through an authorised controller only', file_hash CHAR(64) NOT NULL COMMENT 'SHA-256 — proves the artefact was never swapped post-upload', mime_type VARCHAR(80) NOT NULL, file_size_bytes INT UNSIGNED NOT NULL, issue_date DATE NULL, expiry_date DATE NULL, verification_status ENUM('pending','verified','rejected','expired') NOT NULL DEFAULT 'pending', verified_by INT UNSIGNED NULL, verified_at DATETIME(6) NULL, rejection_reason VARCHAR(255) NULL, ocr_extracted_json JSON NULL COMMENT 'Raw OCR/API payload for later re-validation', uploaded_by INT UNSIGNED NULL, uploaded_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), KEY idx_kycdoc_member (member_id, doc_category, verification_status), KEY idx_kycdoc_appl (loan_application_id), KEY idx_kycdoc_expiry (org_id, expiry_date), CONSTRAINT fk_kycdoc_member FOREIGN KEY (member_id) REFERENCES members (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Microfinance group structure: CENTRE -> GROUP (JLG/SHG) -> MEMBER -- --------------------------------------------------------------------- CREATE TABLE centres ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, branch_id INT UNSIGNED NOT NULL, centre_code VARCHAR(24) NOT NULL, centre_name VARCHAR(150) NOT NULL, village VARCHAR(120) NULL, meeting_day ENUM('mon','tue','wed','thu','fri','sat','sun') NOT NULL, meeting_time TIME NOT NULL, meeting_frequency ENUM('weekly','fortnightly','monthly') NOT NULL DEFAULT 'weekly', meeting_latitude DECIMAL(10,7) NULL COMMENT 'Geo-fence anchor for collection attendance', meeting_longitude DECIMAL(10,7) NULL, geofence_radius_m SMALLINT UNSIGNED NOT NULL DEFAULT 200, field_officer_id INT UNSIGNED NULL, formed_on DATE NULL, status ENUM('active','dormant','closed') NOT NULL DEFAULT 'active', created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_centre_code (org_id, centre_code), KEY idx_centre_branch (branch_id, status), KEY idx_centre_officer (field_officer_id, meeting_day), CONSTRAINT fk_centre_branch FOREIGN KEY (branch_id) REFERENCES branches (id) ON DELETE RESTRICT, CONSTRAINT fk_centre_officer FOREIGN KEY (field_officer_id) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE member_groups ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, branch_id INT UNSIGNED NOT NULL, centre_id BIGINT UNSIGNED NULL, group_code VARCHAR(24) NOT NULL, group_name VARCHAR(150) NOT NULL, group_type ENUM('jlg','shg','federation') NOT NULL DEFAULT 'jlg', formation_date DATE NOT NULL, -- JLG norms: 4-10 members; SHG: 10-20 min_members TINYINT UNSIGNED NOT NULL DEFAULT 4, max_members TINYINT UNSIGNED NOT NULL DEFAULT 10, leader_member_id BIGINT UNSIGNED NULL, secretary_member_id BIGINT UNSIGNED NULL, -- SHG savings-led attributes group_savings_bal BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Paise', group_bank_acct_id BIGINT UNSIGNED NULL, -- Group Recognition Test (GRT) / Compulsory Group Training (CGT) cgt_completed_on DATE NULL, grt_conducted_on DATE NULL, grt_conducted_by INT UNSIGNED NULL, grt_result ENUM('pending','passed','failed') NOT NULL DEFAULT 'pending', joint_liability TINYINT(1) NOT NULL DEFAULT 1, grading ENUM('A','B','C','D') NULL COMMENT 'Group grading drives ticket-size eligibility', status ENUM('forming','active','dormant','dissolved') NOT NULL DEFAULT 'forming', created_by INT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_group_code (org_id, group_code), KEY idx_group_centre (centre_id, status), KEY idx_group_branch (branch_id, status), CONSTRAINT fk_group_branch FOREIGN KEY (branch_id) REFERENCES branches (id) ON DELETE RESTRICT, CONSTRAINT fk_group_centre FOREIGN KEY (centre_id) REFERENCES centres (id) ON DELETE SET NULL, CONSTRAINT chk_group_size CHECK (min_members >= 2 AND max_members >= min_members) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE group_members ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, group_id BIGINT UNSIGNED NOT NULL, member_id BIGINT UNSIGNED NOT NULL, role_in_group ENUM('member','leader','secretary','treasurer') NOT NULL DEFAULT 'member', joined_on DATE NOT NULL, exited_on DATE NULL, exit_reason VARCHAR(255) NULL, status ENUM('active','exited','suspended') NOT NULL DEFAULT 'active', PRIMARY KEY (id), UNIQUE KEY uq_group_member_active (group_id, member_id, joined_on), KEY idx_gm_member (member_id, status), CONSTRAINT fk_gm_group FOREIGN KEY (group_id) REFERENCES member_groups (id) ON DELETE CASCADE, CONSTRAINT fk_gm_member FOREIGN KEY (member_id) REFERENCES members (id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 04. LOAN PRODUCTS -- --------------------------------------------------------------------- -- A product is a *rule pack*, not a row of loose columns: everything -- the origination and servicing engines need to behave differently is -- declared here so no product logic is ever hard-coded in PHP. -- ===================================================================== CREATE TABLE loan_products ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, code VARCHAR(24) NOT NULL, name VARCHAR(150) NOT NULL, -- Which regulatory rule-pack governs this product regulatory_class ENUM('nbfc_retail','nbfc_business','nidhi_gold','nidhi_property','nidhi_deposit_backed', 'nidhi_term','mfi_jlg','mfi_shg','mfi_individual','gold','vehicle','msme','personal') NOT NULL, loan_category ENUM('individual','group','joint') NOT NULL DEFAULT 'individual', -- ---- Interest --------------------------------------------------- interest_method ENUM('reducing_balance','flat','rule_78','simple_daily','bullet') NOT NULL DEFAULT 'reducing_balance', interest_rate_bps INT UNSIGNED NOT NULL COMMENT 'Annual nominal rate in basis points. 1800 = 18.00% p.a.', min_rate_bps INT UNSIGNED NULL COMMENT 'Risk-based pricing floor', max_rate_bps INT UNSIGNED NULL COMMENT 'Risk-based pricing ceiling', rate_type ENUM('fixed','floating') NOT NULL DEFAULT 'fixed', benchmark_code VARCHAR(24) NULL COMMENT 'Repo / MCLR / internal PLR for floating products', spread_bps INT NULL, day_count_convention ENUM('actual_365','actual_360','30_360','actual_actual') NOT NULL DEFAULT 'actual_365', -- ---- Amount & tenure ------------------------------------------- min_principal BIGINT UNSIGNED NOT NULL COMMENT 'Paise', max_principal BIGINT UNSIGNED NOT NULL COMMENT 'Paise', principal_multiple_of BIGINT UNSIGNED NOT NULL DEFAULT 100000 COMMENT 'Paise. 100000 = amounts must be multiples of Rs 1000', min_tenure SMALLINT UNSIGNED NOT NULL COMMENT 'In repayment_frequency units', max_tenure SMALLINT UNSIGNED NOT NULL, repayment_frequency ENUM('daily','weekly','fortnightly','monthly','quarterly','half_yearly','yearly','bullet') NOT NULL DEFAULT 'monthly', moratorium_months TINYINT UNSIGNED NOT NULL DEFAULT 0, moratorium_type ENUM('none','principal_only','full_emi_holiday') NOT NULL DEFAULT 'none', grace_days TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Days after due date before an instalment turns overdue', -- ---- Charges (RBI 2023: "penal CHARGES", not penal interest; -- they must not be capitalised and no further interest may be -- computed on them) --------------------------------------- processing_fee_bps INT UNSIGNED NOT NULL DEFAULT 0, processing_fee_flat BIGINT UNSIGNED NOT NULL DEFAULT 0, processing_fee_min BIGINT UNSIGNED NOT NULL DEFAULT 0, processing_fee_max BIGINT UNSIGNED NOT NULL DEFAULT 0, documentation_fee BIGINT UNSIGNED NOT NULL DEFAULT 0, insurance_required TINYINT(1) NOT NULL DEFAULT 0, insurance_premium_bps INT UNSIGNED NOT NULL DEFAULT 0, gst_applicable_on_fees TINYINT(1) NOT NULL DEFAULT 1, gst_rate_bps INT UNSIGNED NOT NULL DEFAULT 1800 COMMENT '18% GST on fee income', penal_charge_mode ENUM('flat_per_instalment','percent_of_overdue','percent_per_annum','none') NOT NULL DEFAULT 'percent_per_annum', penal_charge_value INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'bps when percent-based, paise when flat', penal_charge_cap BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Paise. 0 = uncapped', penal_compounding TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Must remain 0 for RBI-regulated retail lending', foreclosure_charge_bps INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Must be 0 for floating-rate individual loans and all microfinance loans', part_payment_allowed TINYINT(1) NOT NULL DEFAULT 1, -- ---- Security / collateral -------------------------------------- collateral_required TINYINT(1) NOT NULL DEFAULT 0, collateral_type ENUM('none','gold','property','vehicle','deposit','shares','stock','guarantor') NOT NULL DEFAULT 'none', max_ltv_bps INT UNSIGNED NULL COMMENT 'RBI cap for gold loans by NBFCs: 7500 bps (75%)', guarantors_required TINYINT UNSIGNED NOT NULL DEFAULT 0, -- ---- Eligibility / compliance gates ----------------------------- min_age TINYINT UNSIGNED NOT NULL DEFAULT 21, max_age_at_maturity TINYINT UNSIGNED NOT NULL DEFAULT 65, min_cibil_score SMALLINT UNSIGNED NULL, bureau_check_mandatory TINYINT(1) NOT NULL DEFAULT 1, max_household_income BIGINT UNSIGNED NULL COMMENT 'Paise. 30000000 (Rs 3L) for RBI-qualifying microfinance', max_flioi_bps INT UNSIGNED NULL COMMENT 'Fixed Loan Instalment to Income ratio cap. RBI MFI 2022: 5000 bps (50% of monthly household income)', max_lender_count TINYINT UNSIGNED NULL COMMENT 'MFI multiple-lending guardrail', requires_nidhi_member TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Nidhi Rules: lending only to members', min_shares_required INT UNSIGNED NOT NULL DEFAULT 0, -- ---- Accounting mapping ----------------------------------------- gl_principal_id INT UNSIGNED NULL, gl_interest_income_id INT UNSIGNED NULL, gl_interest_receivable_id INT UNSIGNED NULL, gl_fee_income_id INT UNSIGNED NULL, gl_penal_income_id INT UNSIGNED NULL, gl_provision_id INT UNSIGNED NULL, -- ---- Workflow ---------------------------------------------------- approval_levels TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Maker-checker depth for sanction', auto_disburse TINYINT(1) NOT NULL DEFAULT 0, nach_mandatory TINYINT(1) NOT NULL DEFAULT 0, effective_from DATE NOT NULL, effective_to DATE NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by INT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_product_code (org_id, code), KEY idx_product_active (org_id, is_active, effective_from), KEY idx_product_class (org_id, regulatory_class), CONSTRAINT fk_product_org FOREIGN KEY (org_id) REFERENCES organisations (id) ON DELETE RESTRICT, CONSTRAINT chk_product_principal CHECK (max_principal >= min_principal), CONSTRAINT chk_product_tenure CHECK (max_tenure >= min_tenure), CONSTRAINT chk_product_ltv CHECK (max_ltv_bps IS NULL OR max_ltv_bps <= 10000) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Branch-level availability + local overrides on rate and ticket size CREATE TABLE branch_product_config ( branch_id INT UNSIGNED NOT NULL, product_id INT UNSIGNED NOT NULL, is_enabled TINYINT(1) NOT NULL DEFAULT 1, rate_override_bps INT UNSIGNED NULL, max_principal_override BIGINT UNSIGNED NULL, PRIMARY KEY (branch_id, product_id), CONSTRAINT fk_bpc_branch FOREIGN KEY (branch_id) REFERENCES branches (id) ON DELETE CASCADE, CONSTRAINT fk_bpc_product FOREIGN KEY (product_id) REFERENCES loan_products (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 05. LOAN ORIGINATION (LOS) -- ===================================================================== CREATE TABLE loan_applications ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, branch_id INT UNSIGNED NOT NULL, application_no VARCHAR(32) NOT NULL, member_id BIGINT UNSIGNED NOT NULL, group_id BIGINT UNSIGNED NULL COMMENT 'Set for JLG/SHG applications', product_id INT UNSIGNED NOT NULL, -- ---- Requested terms -------------------------------------------- requested_principal BIGINT UNSIGNED NOT NULL COMMENT 'Paise', requested_tenure SMALLINT UNSIGNED NOT NULL, loan_purpose VARCHAR(255) NULL, purpose_category ENUM('income_generation','consumption','education','medical','housing','refinance','other') NOT NULL DEFAULT 'income_generation' COMMENT 'RBI MFI: at least 50% of the microfinance book must be income-generation', -- ---- Sanctioned terms (populated at approval) ------------------- approved_principal BIGINT UNSIGNED NULL, approved_tenure SMALLINT UNSIGNED NULL, approved_rate_bps INT UNSIGNED NULL, approved_emi BIGINT UNSIGNED NULL, -- ---- Credit decisioning ------------------------------------------ credit_score_internal SMALLINT UNSIGNED NULL COMMENT '0-1000 internal scorecard output', credit_grade ENUM('A+','A','B','C','D','E') NULL, bureau_score SMALLINT UNSIGNED NULL, bureau_name VARCHAR(24) NULL, existing_emi_obligation BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Paise/month, from bureau + declared', computed_flioi_bps INT UNSIGNED NULL COMMENT 'Fixed obligation to income ratio, computed at underwriting', decision_engine_json JSON NULL COMMENT 'Full rule-by-rule trace: which gates passed/failed and why', -- ---- Workflow ---------------------------------------------------- status ENUM('draft','submitted','kyc_pending','bureau_pending','under_review','field_verification', 'recommended','approved','rejected','cancelled','expired','disbursed','withdrawn') NOT NULL DEFAULT 'draft', current_approval_level TINYINT UNSIGNED NOT NULL DEFAULT 0, rejection_reason_code VARCHAR(48) NULL, rejection_remarks VARCHAR(500) NULL, sourced_by INT UNSIGNED NULL COMMENT 'Loan officer who sourced the file', submitted_at DATETIME(6) NULL, approved_by INT UNSIGNED NULL, approved_at DATETIME(6) NULL, sanction_valid_until DATE NULL, -- ---- Field verification ------------------------------------------- fi_status ENUM('not_required','pending','positive','negative','refer') NOT NULL DEFAULT 'pending', fi_done_by INT UNSIGNED NULL, fi_done_at DATETIME(6) NULL, fi_latitude DECIMAL(10,7) NULL, fi_longitude DECIMAL(10,7) NULL, fi_remarks VARCHAR(500) NULL, -- ---- Source --------------------------------------------------------- channel ENUM('branch','field_app','web_portal','dsa','api','bc_agent') NOT NULL DEFAULT 'branch', created_by INT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_appl_no (org_id, application_no), KEY idx_appl_member (member_id, status), KEY idx_appl_branch_status (branch_id, status, created_at), KEY idx_appl_product (product_id, status), KEY idx_appl_group (group_id), KEY idx_appl_officer (sourced_by, status), CONSTRAINT fk_appl_org FOREIGN KEY (org_id) REFERENCES organisations (id) ON DELETE RESTRICT, CONSTRAINT fk_appl_branch FOREIGN KEY (branch_id) REFERENCES branches (id) ON DELETE RESTRICT, CONSTRAINT fk_appl_member FOREIGN KEY (member_id) REFERENCES members (id) ON DELETE RESTRICT, CONSTRAINT fk_appl_group FOREIGN KEY (group_id) REFERENCES member_groups (id) ON DELETE SET NULL, CONSTRAINT fk_appl_product FOREIGN KEY (product_id) REFERENCES loan_products (id) ON DELETE RESTRICT, CONSTRAINT chk_appl_amount CHECK (requested_principal > 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Maker-checker trail. Every state transition of an application is a row -- here; the application row only ever holds the *current* state. CREATE TABLE loan_approvals ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, application_id BIGINT UNSIGNED NOT NULL, approval_level TINYINT UNSIGNED NOT NULL, actor_id INT UNSIGNED NOT NULL, actor_role_slug VARCHAR(48) NOT NULL, action ENUM('recommend','approve','reject','refer_back','cancel','override') NOT NULL, from_status VARCHAR(32) NOT NULL, to_status VARCHAR(32) NOT NULL, sanctioned_amount BIGINT UNSIGNED NULL, sanctioned_rate_bps INT UNSIGNED NULL, conditions TEXT NULL COMMENT 'Sanction conditions precedent', remarks VARCHAR(1000) NULL, ip_address VARBINARY(16) NULL, acted_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), KEY idx_appr_appl (application_id, approval_level), KEY idx_appr_actor (actor_id, acted_at), CONSTRAINT fk_appr_appl FOREIGN KEY (application_id) REFERENCES loan_applications (id) ON DELETE CASCADE, CONSTRAINT fk_appr_actor FOREIGN KEY (actor_id) REFERENCES users (id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 06. CREDIT BUREAU -- --------------------------------------------------------------------- -- RBI requires membership of and reporting to ALL FOUR CICs. Reports -- are cached: re-pulling the same PAN/Aadhaar within BUREAU_CACHE_DAYS -- returns the stored copy, which both saves the per-hit fee and keeps -- the enquiry count on the customer's own report low. -- ===================================================================== CREATE TABLE bureau_checks ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, member_id BIGINT UNSIGNED NOT NULL, application_id BIGINT UNSIGNED NULL, bureau ENUM('cibil','crif_highmark','equifax','experian') NOT NULL, enquiry_purpose ENUM('new_loan','review','collection','pre_approved','portfolio_monitoring') NOT NULL DEFAULT 'new_loan', enquiry_type ENUM('individual','microfinance','commercial') NOT NULL DEFAULT 'individual', request_reference VARCHAR(64) NOT NULL COMMENT 'Our idempotency key sent to the bureau', bureau_reference VARCHAR(64) NULL COMMENT 'Control number returned by the bureau', -- ---- Outcome ------------------------------------------------------- status ENUM('queued','sent','success','no_hit','failed','timeout','cached') NOT NULL DEFAULT 'queued', score SMALLINT UNSIGNED NULL COMMENT 'CIBIL 300-900; -1/NA mapped to NULL', score_band ENUM('no_history','poor','fair','good','very_good','excellent') NULL, score_reason_codes VARCHAR(255) NULL, -- ---- Derived aggregates used by the scorecard --------------------- total_accounts SMALLINT UNSIGNED NULL, active_accounts SMALLINT UNSIGNED NULL, total_outstanding BIGINT UNSIGNED NULL COMMENT 'Paise', total_emi_obligation BIGINT UNSIGNED NULL COMMENT 'Paise/month', overdue_accounts SMALLINT UNSIGNED NULL, total_overdue_amount BIGINT UNSIGNED NULL, max_dpd_24m SMALLINT UNSIGNED NULL, write_off_count SMALLINT UNSIGNED NULL, settled_count SMALLINT UNSIGNED NULL, suit_filed_count SMALLINT UNSIGNED NULL, enquiries_last_90d SMALLINT UNSIGNED NULL, -- MFI-specific aggregates (CRIF High Mark microfinance enquiry) mfi_lender_count TINYINT UNSIGNED NULL COMMENT 'Distinct NBFC-MFIs currently lending to this borrower', mfi_total_outstanding BIGINT UNSIGNED NULL, -- ---- Raw payloads (encrypted — a bureau report is regulated PII) -- request_payload_enc BLOB NULL, response_payload_enc LONGBLOB NULL, response_hash CHAR(64) NULL, pdf_path VARCHAR(255) NULL, -- ---- Ops ------------------------------------------------------------ http_status SMALLINT UNSIGNED NULL, error_code VARCHAR(48) NULL, error_message VARCHAR(500) NULL, latency_ms INT UNSIGNED NULL, cost_paise INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Per-hit price, for vendor cost reconciliation', valid_until DATETIME(6) NULL COMMENT 'Cache horizon; a hit inside this window is reused', requested_by INT UNSIGNED NULL, requested_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), responded_at DATETIME(6) NULL, PRIMARY KEY (id), UNIQUE KEY uq_bureau_reqref (org_id, bureau, request_reference), KEY idx_bureau_member (member_id, bureau, requested_at), KEY idx_bureau_appl (application_id, status), KEY idx_bureau_cache (member_id, bureau, valid_until), CONSTRAINT fk_bureau_member FOREIGN KEY (member_id) REFERENCES members (id) ON DELETE CASCADE, CONSTRAINT fk_bureau_appl FOREIGN KEY (application_id) REFERENCES loan_applications (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Internal scorecard: rules are data, so credit policy changes without -- a code release and every past decision remains explainable. CREATE TABLE credit_scorecards ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, product_id INT UNSIGNED NULL COMMENT 'NULL = applies to all products', version VARCHAR(16) NOT NULL, name VARCHAR(120) NOT NULL, rules_json JSON NOT NULL COMMENT '[{code,label,source,operator,value,weight,knockout}]', cutoff_approve SMALLINT UNSIGNED NOT NULL, cutoff_refer SMALLINT UNSIGNED NOT NULL, effective_from DATE NOT NULL, effective_to DATE NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by INT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_scorecard (org_id, name, version), KEY idx_scorecard_active (org_id, is_active, effective_from) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE credit_assessments ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, application_id BIGINT UNSIGNED NOT NULL, scorecard_id INT UNSIGNED NULL, scorecard_version VARCHAR(16) NULL, total_score SMALLINT UNSIGNED NOT NULL, grade ENUM('A+','A','B','C','D','E') NOT NULL, recommendation ENUM('approve','refer','decline') NOT NULL, knockout_triggered VARCHAR(64) NULL COMMENT 'Rule code that hard-declined the file, if any', rule_results_json JSON NOT NULL COMMENT 'Per-rule score contribution — the audit answer to "why was I declined?"', -- Bank statement analyser output bsa_provider VARCHAR(32) NULL, bsa_avg_balance BIGINT NULL COMMENT 'Paise; signed — overdrawn accounts go negative', bsa_total_credits BIGINT UNSIGNED NULL, bsa_total_debits BIGINT UNSIGNED NULL, bsa_bounce_count SMALLINT UNSIGNED NULL, bsa_salary_detected TINYINT(1) NULL, bsa_months_analysed TINYINT UNSIGNED NULL, bsa_raw_json JSON NULL, assessed_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), KEY idx_ca_appl (application_id, assessed_at), CONSTRAINT fk_ca_appl FOREIGN KEY (application_id) REFERENCES loan_applications (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 07. LOAN MANAGEMENT (LMS) — ACCOUNTS, SCHEDULE, REPAYMENTS -- ===================================================================== CREATE TABLE loan_accounts ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, branch_id INT UNSIGNED NOT NULL, loan_account_no VARCHAR(32) NOT NULL COMMENT '{BRANCH}/{PRODUCT}/{YY}/{SEQ}', application_id BIGINT UNSIGNED NOT NULL, member_id BIGINT UNSIGNED NOT NULL, group_id BIGINT UNSIGNED NULL, product_id INT UNSIGNED NOT NULL, -- ---- Frozen terms: the product may change later, this loan may not principal BIGINT UNSIGNED NOT NULL COMMENT 'Paise. Sanctioned amount', interest_rate_bps INT UNSIGNED NOT NULL, interest_method ENUM('reducing_balance','flat','rule_78','simple_daily','bullet') NOT NULL, tenure SMALLINT UNSIGNED NOT NULL, repayment_frequency ENUM('daily','weekly','fortnightly','monthly','quarterly','half_yearly','yearly','bullet') NOT NULL, emi_amount BIGINT UNSIGNED NOT NULL COMMENT 'Paise. Level instalment; last one absorbs rounding', day_count_convention ENUM('actual_365','actual_360','30_360','actual_actual') NOT NULL DEFAULT 'actual_365', grace_days TINYINT UNSIGNED NOT NULL DEFAULT 0, penal_charge_mode ENUM('flat_per_instalment','percent_of_overdue','percent_per_annum','none') NOT NULL DEFAULT 'percent_per_annum', penal_charge_value INT UNSIGNED NOT NULL DEFAULT 0, -- ---- Dates ----------------------------------------------------------- sanction_date DATE NOT NULL, disbursement_date DATE NULL, first_due_date DATE NULL, maturity_date DATE NULL, closure_date DATE NULL, -- ---- Running balances. Denormalised on purpose: recomputing these -- from the ledger on every list screen does not survive a real -- portfolio. They are re-proved against the ledger nightly by -- the EOD reconciliation job. disbursed_amount BIGINT UNSIGNED NOT NULL DEFAULT 0, principal_outstanding BIGINT UNSIGNED NOT NULL DEFAULT 0, principal_paid BIGINT UNSIGNED NOT NULL DEFAULT 0, interest_accrued BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Accrual basis: earned but not yet received', interest_paid BIGINT UNSIGNED NOT NULL DEFAULT 0, interest_waived BIGINT UNSIGNED NOT NULL DEFAULT 0, penal_charged BIGINT UNSIGNED NOT NULL DEFAULT 0, penal_paid BIGINT UNSIGNED NOT NULL DEFAULT 0, penal_waived BIGINT UNSIGNED NOT NULL DEFAULT 0, fees_charged BIGINT UNSIGNED NOT NULL DEFAULT 0, fees_paid BIGINT UNSIGNED NOT NULL DEFAULT 0, total_repaid BIGINT UNSIGNED NOT NULL DEFAULT 0, excess_credit BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Unapplied advance sitting on the account', -- ---- Delinquency / asset classification ------------------------------ overdue_principal BIGINT UNSIGNED NOT NULL DEFAULT 0, overdue_interest BIGINT UNSIGNED NOT NULL DEFAULT 0, overdue_instalments SMALLINT UNSIGNED NOT NULL DEFAULT 0, dpd SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Days past due of the OLDEST unpaid instalment', max_dpd_ever SMALLINT UNSIGNED NOT NULL DEFAULT 0, oldest_overdue_date DATE NULL, asset_classification ENUM('standard','smaA1','smaA2','sma1','sma2','substandard','doubtful1','doubtful2','doubtful3','loss') NOT NULL DEFAULT 'standard', npa_flag TINYINT(1) NOT NULL DEFAULT 0, npa_date DATE NULL, npa_reason VARCHAR(120) NULL, provision_rate_bps INT UNSIGNED NOT NULL DEFAULT 0, provision_amount BIGINT UNSIGNED NOT NULL DEFAULT 0, -- RBI 12 Nov 2021 clarification: an NPA may be upgraded to standard -- only when the ENTIRE arrears of interest AND principal are cleared. upgraded_from_npa_on DATE NULL, restructured_flag TINYINT(1) NOT NULL DEFAULT 0, restructure_count TINYINT UNSIGNED NOT NULL DEFAULT 0, write_off_date DATE NULL, write_off_amount BIGINT UNSIGNED NOT NULL DEFAULT 0, -- ---- Servicing -------------------------------------------------------- collection_mode ENUM('cash','nach','upi_autopay','standing_instruction','cheque','pdc') NOT NULL DEFAULT 'cash', nach_mandate_id BIGINT UNSIGNED NULL, collection_officer_id INT UNSIGNED NULL, centre_id BIGINT UNSIGNED NULL, status ENUM('pending_disbursal','active','overdue','npa','closed','foreclosed','written_off','cancelled') NOT NULL DEFAULT 'pending_disbursal', -- ---- Accounting linkage ----------------------------------------------- last_accrual_date DATE NULL COMMENT 'Guards against double-accruing interest on re-run', created_by INT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), version INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Optimistic-lock counter for concurrent posting', PRIMARY KEY (id), UNIQUE KEY uq_loan_no (org_id, loan_account_no), UNIQUE KEY uq_loan_appl (application_id), KEY idx_loan_member (member_id, status), KEY idx_loan_branch_status (branch_id, status, dpd), KEY idx_loan_npa (org_id, npa_flag, asset_classification), KEY idx_loan_officer (collection_officer_id, status), KEY idx_loan_centre (centre_id, status), KEY idx_loan_dpd (org_id, dpd, principal_outstanding), KEY idx_loan_accrual (org_id, status, last_accrual_date), CONSTRAINT fk_loan_org FOREIGN KEY (org_id) REFERENCES organisations (id) ON DELETE RESTRICT, CONSTRAINT fk_loan_branch FOREIGN KEY (branch_id) REFERENCES branches (id) ON DELETE RESTRICT, CONSTRAINT fk_loan_appl FOREIGN KEY (application_id) REFERENCES loan_applications (id) ON DELETE RESTRICT, CONSTRAINT fk_loan_member FOREIGN KEY (member_id) REFERENCES members (id) ON DELETE RESTRICT, CONSTRAINT fk_loan_product FOREIGN KEY (product_id) REFERENCES loan_products (id) ON DELETE RESTRICT, CONSTRAINT fk_loan_centre FOREIGN KEY (centre_id) REFERENCES centres (id) ON DELETE SET NULL, CONSTRAINT chk_loan_principal CHECK (principal > 0), CONSTRAINT chk_loan_paid CHECK (principal_paid <= principal + 1) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE loan_disbursements ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, loan_account_id BIGINT UNSIGNED NOT NULL, tranche_no TINYINT UNSIGNED NOT NULL DEFAULT 1, gross_amount BIGINT UNSIGNED NOT NULL COMMENT 'Paise. Sanctioned tranche', deductions_json JSON NULL COMMENT '[{code,label,amount}] — PF, insurance, GST, first EMI, old-loan closure', total_deductions BIGINT UNSIGNED NOT NULL DEFAULT 0, net_amount BIGINT UNSIGNED NOT NULL COMMENT 'Actually paid out to the borrower', disbursement_date DATE NOT NULL, mode ENUM('neft','rtgs','imps','upi','cheque','dd','cash','wallet') NOT NULL, bank_account_id BIGINT UNSIGNED NULL, utr_number VARCHAR(40) NULL, cheque_no VARCHAR(20) NULL, status ENUM('pending','processing','success','failed','reversed') NOT NULL DEFAULT 'pending', failure_reason VARCHAR(255) NULL, voucher_id BIGINT UNSIGNED NULL, authorised_by INT UNSIGNED NULL, disbursed_by INT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_disb_tranche (loan_account_id, tranche_no), KEY idx_disb_date (disbursement_date, status), KEY idx_disb_utr (utr_number), CONSTRAINT fk_disb_loan FOREIGN KEY (loan_account_id) REFERENCES loan_accounts (id) ON DELETE RESTRICT, CONSTRAINT chk_disb_net CHECK (net_amount <= gross_amount) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- The amortisation schedule. One row per instalment. Generated once at -- disbursal and regenerated (with a new version) only on restructure, -- part-prepayment or rate reset — never edited in place. -- --------------------------------------------------------------------- CREATE TABLE loan_schedules ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, loan_account_id BIGINT UNSIGNED NOT NULL, schedule_version SMALLINT UNSIGNED NOT NULL DEFAULT 1, instalment_no SMALLINT UNSIGNED NOT NULL, due_date DATE NOT NULL, -- ---- Demand ------------------------------------------------------- opening_principal BIGINT UNSIGNED NOT NULL, principal_due BIGINT UNSIGNED NOT NULL DEFAULT 0, interest_due BIGINT UNSIGNED NOT NULL DEFAULT 0, fee_due BIGINT UNSIGNED NOT NULL DEFAULT 0, insurance_due BIGINT UNSIGNED NOT NULL DEFAULT 0, total_due BIGINT UNSIGNED NOT NULL, closing_principal BIGINT UNSIGNED NOT NULL, -- ---- Collection ---------------------------------------------------- principal_paid BIGINT UNSIGNED NOT NULL DEFAULT 0, interest_paid BIGINT UNSIGNED NOT NULL DEFAULT 0, fee_paid BIGINT UNSIGNED NOT NULL DEFAULT 0, insurance_paid BIGINT UNSIGNED NOT NULL DEFAULT 0, penal_due BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Accrued penal charges on THIS instalment', penal_paid BIGINT UNSIGNED NOT NULL DEFAULT 0, waived_amount BIGINT UNSIGNED NOT NULL DEFAULT 0, total_paid BIGINT UNSIGNED NOT NULL DEFAULT 0, last_paid_date DATE NULL, penal_calc_upto DATE NULL COMMENT 'Idempotency guard for the daily penal-accrual job', -- ---- State ---------------------------------------------------------- status ENUM('pending','partial','paid','overdue','waived','rescheduled','written_off') NOT NULL DEFAULT 'pending', days_past_due SMALLINT UNSIGNED NOT NULL DEFAULT 0, is_moratorium TINYINT(1) NOT NULL DEFAULT 0, is_active_version TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_sched (loan_account_id, schedule_version, instalment_no), -- The workhorse index: "give me every unpaid instalment due on/before -- date X, oldest first" is the hot path for allocation, DPD, NPA, -- demand sheets and reminder jobs alike. KEY idx_sched_due (loan_account_id, is_active_version, status, due_date), KEY idx_sched_duedate_global (due_date, status), CONSTRAINT fk_sched_loan FOREIGN KEY (loan_account_id) REFERENCES loan_accounts (id) ON DELETE CASCADE, CONSTRAINT chk_sched_total CHECK (total_due = principal_due + interest_due + fee_due + insurance_due) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Money actually received. APPEND-ONLY: a mistake is corrected by a -- reversal row that points at the original, never by an UPDATE. -- --------------------------------------------------------------------- CREATE TABLE loan_repayments ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, branch_id INT UNSIGNED NOT NULL, loan_account_id BIGINT UNSIGNED NOT NULL, receipt_no VARCHAR(32) NOT NULL COMMENT 'Pre-printed/sequential; shown on the borrower receipt', -- Idempotency key. A retried field-app sync, a duplicated NACH file -- or a double-clicked cashier form all collapse onto one row. idempotency_key VARCHAR(64) NOT NULL, value_date DATE NOT NULL COMMENT 'Date the money is treated as received (drives interest)', posting_date DATE NOT NULL COMMENT 'Business day it hit the books — may differ from value_date', amount BIGINT UNSIGNED NOT NULL COMMENT 'Paise. Gross amount received', -- ---- Component split, denormalised from repayment_allocations for -- fast receipts and reporting. Guaranteed to sum to `amount`. principal_component BIGINT UNSIGNED NOT NULL DEFAULT 0, interest_component BIGINT UNSIGNED NOT NULL DEFAULT 0, penal_component BIGINT UNSIGNED NOT NULL DEFAULT 0, fee_component BIGINT UNSIGNED NOT NULL DEFAULT 0, insurance_component BIGINT UNSIGNED NOT NULL DEFAULT 0, advance_component BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Surplus parked as excess_credit', -- ---- Instrument ------------------------------------------------------- payment_mode ENUM('cash','upi','neft','imps','rtgs','nach','cheque','dd','card','wallet','adjustment','write_off') NOT NULL, transaction_type ENUM('regular','advance','part_prepayment','foreclosure','settlement','recovery_post_writeoff','reversal') NOT NULL DEFAULT 'regular', reference_no VARCHAR(64) NULL COMMENT 'UTR / UPI RRN / cheque no / NACH item ref', bank_name VARCHAR(120) NULL, cheque_date DATE NULL, -- ---- Field collection provenance --------------------------------------- collected_by INT UNSIGNED NULL, collection_sheet_id BIGINT UNSIGNED NULL, channel ENUM('branch_counter','field_app','nach_auto','payment_gateway','portal','api','bulk_upload') NOT NULL DEFAULT 'branch_counter', latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL, device_id VARCHAR(64) NULL, -- ---- Reversal linkage ---------------------------------------------------- status ENUM('pending','cleared','bounced','reversed','cancelled') NOT NULL DEFAULT 'cleared', reversal_of_id BIGINT UNSIGNED NULL COMMENT 'Set on the contra row; points at the row being undone', reversed_by_id BIGINT UNSIGNED NULL COMMENT 'Set on the original; points at its contra row', reversal_reason VARCHAR(255) NULL, bounce_reason VARCHAR(255) NULL, bounce_charge BIGINT UNSIGNED NOT NULL DEFAULT 0, -- ---- Accounting ---------------------------------------------------------- voucher_id BIGINT UNSIGNED NULL COMMENT 'Journal voucher created by this posting', receipt_pdf_path VARCHAR(255) NULL, remarks VARCHAR(500) NULL, created_by INT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_repay_receipt (org_id, receipt_no), UNIQUE KEY uq_repay_idem (org_id, idempotency_key), KEY idx_repay_loan (loan_account_id, value_date, status), KEY idx_repay_branch_date (branch_id, posting_date, payment_mode), KEY idx_repay_collector (collected_by, posting_date), KEY idx_repay_sheet (collection_sheet_id), KEY idx_repay_ref (reference_no), KEY idx_repay_reversal (reversal_of_id), CONSTRAINT fk_repay_loan FOREIGN KEY (loan_account_id) REFERENCES loan_accounts (id) ON DELETE RESTRICT, CONSTRAINT fk_repay_branch FOREIGN KEY (branch_id) REFERENCES branches (id) ON DELETE RESTRICT, CONSTRAINT fk_repay_collector FOREIGN KEY (collected_by) REFERENCES users (id) ON DELETE SET NULL, CONSTRAINT fk_repay_reversal FOREIGN KEY (reversal_of_id) REFERENCES loan_repayments (id) ON DELETE RESTRICT, CONSTRAINT chk_repay_amount CHECK (amount > 0), CONSTRAINT chk_repay_split CHECK (amount = principal_component + interest_component + penal_component + fee_component + insurance_component + advance_component) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Which receipt paid which instalment, component by component. This is -- what makes "explain this borrower's ledger line by line" answerable. CREATE TABLE repayment_allocations ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, repayment_id BIGINT UNSIGNED NOT NULL, schedule_id BIGINT UNSIGNED NULL COMMENT 'NULL when the money went to advance/excess credit', loan_account_id BIGINT UNSIGNED NOT NULL, instalment_no SMALLINT UNSIGNED NULL, component ENUM('penal','fee','insurance','interest','principal','advance') NOT NULL, amount BIGINT UNSIGNED NOT NULL, allocation_order TINYINT UNSIGNED NOT NULL COMMENT 'Waterfall sequence actually applied', created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), KEY idx_alloc_repay (repayment_id), KEY idx_alloc_sched (schedule_id, component), KEY idx_alloc_loan (loan_account_id, created_at), CONSTRAINT fk_alloc_repay FOREIGN KEY (repayment_id) REFERENCES loan_repayments (id) ON DELETE RESTRICT, CONSTRAINT fk_alloc_sched FOREIGN KEY (schedule_id) REFERENCES loan_schedules (id) ON DELETE RESTRICT, CONSTRAINT chk_alloc_amount CHECK (amount > 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Non-instalment charges levied on a loan (bounce, legal, valuation...) CREATE TABLE loan_charges ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, loan_account_id BIGINT UNSIGNED NOT NULL, charge_code VARCHAR(32) NOT NULL, charge_label VARCHAR(150) NOT NULL, charge_type ENUM('processing','documentation','insurance','penal','bounce','legal','valuation','foreclosure','other') NOT NULL, base_amount BIGINT UNSIGNED NOT NULL, gst_amount BIGINT UNSIGNED NOT NULL DEFAULT 0, total_amount BIGINT UNSIGNED NOT NULL, paid_amount BIGINT UNSIGNED NOT NULL DEFAULT 0, waived_amount BIGINT UNSIGNED NOT NULL DEFAULT 0, charge_date DATE NOT NULL, status ENUM('pending','paid','partial','waived','reversed') NOT NULL DEFAULT 'pending', waived_by INT UNSIGNED NULL, waiver_reason VARCHAR(255) NULL, voucher_id BIGINT UNSIGNED NULL, created_by INT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), KEY idx_charge_loan (loan_account_id, status), KEY idx_charge_date (charge_date, charge_type), CONSTRAINT fk_charge_loan FOREIGN KEY (loan_account_id) REFERENCES loan_accounts (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Asset-classification history — the auditor's first question is always -- "when did this slip, and who moved it back?" CREATE TABLE npa_history ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, loan_account_id BIGINT UNSIGNED NOT NULL, as_on_date DATE NOT NULL, from_classification VARCHAR(16) NOT NULL, to_classification VARCHAR(16) NOT NULL, dpd SMALLINT UNSIGNED NOT NULL, principal_outstanding BIGINT UNSIGNED NOT NULL, provision_rate_bps INT UNSIGNED NOT NULL, provision_amount BIGINT UNSIGNED NOT NULL, movement ENUM('downgrade','upgrade','no_change','write_off','recovery') NOT NULL, triggered_by ENUM('eod_job','manual','restructure','settlement') NOT NULL DEFAULT 'eod_job', actioned_by INT UNSIGNED NULL, remarks VARCHAR(500) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_npa_daily (loan_account_id, as_on_date), KEY idx_npa_date (as_on_date, to_classification), CONSTRAINT fk_npa_loan FOREIGN KEY (loan_account_id) REFERENCES loan_accounts (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE loan_collaterals ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, loan_account_id BIGINT UNSIGNED NULL, application_id BIGINT UNSIGNED NULL, collateral_type ENUM('gold','property','vehicle','deposit','shares','stock','machinery','other') NOT NULL, description VARCHAR(500) NOT NULL, -- Gold specifics (Nidhi / gold-loan NBFCs) gold_gross_weight_mg INT UNSIGNED NULL COMMENT 'Milligrams — integers only, no float drift', gold_net_weight_mg INT UNSIGNED NULL, gold_purity_carat DECIMAL(4,2) NULL, gold_item_count TINYINT UNSIGNED NULL, gold_rate_per_gram BIGINT UNSIGNED NULL COMMENT 'Paise. Board-approved rate on the appraisal date', appraiser_name VARCHAR(150) NULL, -- Property / vehicle specifics property_survey_no VARCHAR(64) NULL, vehicle_reg_no VARCHAR(20) NULL, vehicle_engine_no VARCHAR(40) NULL, vehicle_chassis_no VARCHAR(40) NULL, -- Valuation market_value BIGINT UNSIGNED NOT NULL, realisable_value BIGINT UNSIGNED NOT NULL, ltv_bps INT UNSIGNED NULL COMMENT 'Computed: principal / realisable_value', valuation_date DATE NOT NULL, valued_by VARCHAR(150) NULL, next_revaluation_due DATE NULL, -- Custody storage_location VARCHAR(150) NULL, packet_no VARCHAR(40) NULL, insurance_policy_no VARCHAR(64) NULL, insurance_expiry DATE NULL, cersai_asset_id VARCHAR(40) NULL COMMENT 'CERSAI security-interest registration', status ENUM('pledged','partially_released','released','auctioned','seized','lost') NOT NULL DEFAULT 'pledged', released_on DATE NULL, released_by INT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), KEY idx_coll_loan (loan_account_id, status), KEY idx_coll_appl (application_id), KEY idx_coll_reval (next_revaluation_due, status), CONSTRAINT fk_coll_loan FOREIGN KEY (loan_account_id) REFERENCES loan_accounts (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE loan_guarantors ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, loan_account_id BIGINT UNSIGNED NULL, application_id BIGINT UNSIGNED NULL, guarantor_member_id BIGINT UNSIGNED NULL COMMENT 'Set when the guarantor is also a member', name VARCHAR(160) NOT NULL, relationship VARCHAR(60) NULL, mobile VARCHAR(15) NULL, pan_last4 CHAR(4) NULL, address VARCHAR(400) NULL, guaranteed_amount BIGINT UNSIGNED NOT NULL DEFAULT 0, liability_type ENUM('joint_and_several','limited') NOT NULL DEFAULT 'joint_and_several', consent_captured_at DATETIME(6) NULL, status ENUM('active','released','invoked') NOT NULL DEFAULT 'active', created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), KEY idx_guar_loan (loan_account_id, status), KEY idx_guar_member (guarantor_member_id), CONSTRAINT fk_guar_loan FOREIGN KEY (loan_account_id) REFERENCES loan_accounts (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 08. COLLECTIONS — FIELD SHEETS, NACH / e-MANDATE -- ===================================================================== CREATE TABLE collection_sheets ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, branch_id INT UNSIGNED NOT NULL, sheet_no VARCHAR(32) NOT NULL, collection_date DATE NOT NULL, centre_id BIGINT UNSIGNED NULL, officer_id INT UNSIGNED NOT NULL, sheet_type ENUM('centre_meeting','door_to_door','branch_counter','recovery_drive') NOT NULL DEFAULT 'centre_meeting', -- Demand (frozen when the sheet is generated, so the field copy and -- the office copy can never silently diverge) expected_accounts SMALLINT UNSIGNED NOT NULL DEFAULT 0, expected_amount BIGINT UNSIGNED NOT NULL DEFAULT 0, collected_accounts SMALLINT UNSIGNED NOT NULL DEFAULT 0, collected_amount BIGINT UNSIGNED NOT NULL DEFAULT 0, cash_amount BIGINT UNSIGNED NOT NULL DEFAULT 0, digital_amount BIGINT UNSIGNED NOT NULL DEFAULT 0, -- Cash accountability cash_remitted BIGINT UNSIGNED NOT NULL DEFAULT 0, remittance_ref VARCHAR(64) NULL, remitted_at DATETIME(6) NULL, variance_amount BIGINT NOT NULL DEFAULT 0 COMMENT 'Signed. collected_cash - remitted; must be 0 to close', -- Meeting proof attendance_count SMALLINT UNSIGNED NOT NULL DEFAULT 0, meeting_photo_path VARCHAR(255) NULL, start_latitude DECIMAL(10,7) NULL, start_longitude DECIMAL(10,7) NULL, geofence_ok TINYINT(1) NULL, status ENUM('generated','downloaded','in_progress','submitted','reconciled','closed','disputed') NOT NULL DEFAULT 'generated', synced_at DATETIME(6) NULL COMMENT 'When the offline field app pushed it back', reconciled_by INT UNSIGNED NULL, reconciled_at DATETIME(6) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_sheet_no (org_id, sheet_no), KEY idx_sheet_officer_date (officer_id, collection_date, status), KEY idx_sheet_branch_date (branch_id, collection_date), KEY idx_sheet_centre (centre_id, collection_date), CONSTRAINT fk_sheet_branch FOREIGN KEY (branch_id) REFERENCES branches (id) ON DELETE RESTRICT, CONSTRAINT fk_sheet_officer FOREIGN KEY (officer_id) REFERENCES users (id) ON DELETE RESTRICT, CONSTRAINT fk_sheet_centre FOREIGN KEY (centre_id) REFERENCES centres (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE collection_sheet_lines ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, sheet_id BIGINT UNSIGNED NOT NULL, loan_account_id BIGINT UNSIGNED NOT NULL, member_id BIGINT UNSIGNED NOT NULL, demand_amount BIGINT UNSIGNED NOT NULL, overdue_amount BIGINT UNSIGNED NOT NULL DEFAULT 0, penal_amount BIGINT UNSIGNED NOT NULL DEFAULT 0, collected_amount BIGINT UNSIGNED NOT NULL DEFAULT 0, repayment_id BIGINT UNSIGNED NULL, visit_status ENUM('pending','paid','partial','not_paid','absent','refused','shifted','ptp') NOT NULL DEFAULT 'pending', ptp_date DATE NULL COMMENT 'Promise-to-pay captured in the field', non_payment_reason VARCHAR(160) NULL, remarks VARCHAR(500) NULL, captured_at DATETIME(6) NULL, latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL, PRIMARY KEY (id), UNIQUE KEY uq_sheet_line (sheet_id, loan_account_id), KEY idx_csl_loan (loan_account_id), KEY idx_csl_ptp (ptp_date, visit_status), CONSTRAINT fk_csl_sheet FOREIGN KEY (sheet_id) REFERENCES collection_sheets (id) ON DELETE CASCADE, CONSTRAINT fk_csl_loan FOREIGN KEY (loan_account_id) REFERENCES loan_accounts (id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE nach_mandates ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, member_id BIGINT UNSIGNED NOT NULL, loan_account_id BIGINT UNSIGNED NULL, umrn VARCHAR(20) NULL COMMENT 'Unique Mandate Reference Number issued by NPCI', mandate_reference VARCHAR(40) NOT NULL COMMENT 'Our own reference sent to the sponsor bank', mandate_type ENUM('nach_debit','esign_nach','enach_netbanking','enach_debit_card','upi_autopay','si') NOT NULL, -- Bank details (encrypted) bank_account_id BIGINT UNSIGNED NULL, account_no_enc VARBINARY(255) NULL, account_no_last4 CHAR(4) NULL, ifsc CHAR(11) NOT NULL, bank_name VARCHAR(120) NOT NULL, account_type ENUM('savings','current','cc','od','other') NOT NULL DEFAULT 'savings', -- Mandate terms max_amount BIGINT UNSIGNED NOT NULL COMMENT 'Paise. Debit cap per presentation', frequency ENUM('monthly','quarterly','half_yearly','yearly','as_and_when','weekly','daily') NOT NULL DEFAULT 'monthly', debit_day TINYINT UNSIGNED NULL COMMENT 'Day of month the sponsor bank presents', first_collection_date DATE NULL, valid_from DATE NOT NULL, valid_upto DATE NULL, until_cancelled TINYINT(1) NOT NULL DEFAULT 0, -- Lifecycle status ENUM('draft','pending_auth','submitted','active','rejected','cancelled','expired','suspended') NOT NULL DEFAULT 'draft', registration_date DATE NULL, rejection_code VARCHAR(16) NULL, rejection_reason VARCHAR(255) NULL, cancelled_at DATETIME(6) NULL, provider VARCHAR(32) NULL, provider_mandate_id VARCHAR(64) NULL, esign_doc_path VARCHAR(255) NULL, consent_ip VARBINARY(16) NULL, consent_at DATETIME(6) NULL, created_by INT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_nach_ref (org_id, mandate_reference), UNIQUE KEY uq_nach_umrn (umrn), KEY idx_nach_loan (loan_account_id, status), KEY idx_nach_member (member_id, status), KEY idx_nach_debitday (status, debit_day), CONSTRAINT fk_nach_member FOREIGN KEY (member_id) REFERENCES members (id) ON DELETE RESTRICT, CONSTRAINT fk_nach_loan FOREIGN KEY (loan_account_id) REFERENCES loan_accounts (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- One NPCI presentation batch (ACH DEBIT file) per sponsor bank per date CREATE TABLE nach_batches ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, batch_reference VARCHAR(40) NOT NULL, presentation_date DATE NOT NULL COMMENT 'Settlement date at NPCI', generated_on DATE NOT NULL, sponsor_bank_ifsc CHAR(11) NOT NULL, utility_code VARCHAR(20) NOT NULL, total_items INT UNSIGNED NOT NULL DEFAULT 0, total_amount BIGINT UNSIGNED NOT NULL DEFAULT 0, success_items INT UNSIGNED NOT NULL DEFAULT 0, success_amount BIGINT UNSIGNED NOT NULL DEFAULT 0, failed_items INT UNSIGNED NOT NULL DEFAULT 0, failed_amount BIGINT UNSIGNED NOT NULL DEFAULT 0, file_path VARCHAR(255) NULL, file_hash CHAR(64) NULL, response_file_path VARCHAR(255) NULL, status ENUM('draft','generated','uploaded','presented','partially_settled','settled','failed') NOT NULL DEFAULT 'draft', generated_by INT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_nach_batch (org_id, batch_reference), KEY idx_nachbatch_date (presentation_date, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE nach_presentations ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, batch_id BIGINT UNSIGNED NOT NULL, mandate_id BIGINT UNSIGNED NOT NULL, loan_account_id BIGINT UNSIGNED NOT NULL, item_reference VARCHAR(40) NOT NULL, presentation_date DATE NOT NULL, amount BIGINT UNSIGNED NOT NULL, status ENUM('queued','presented','success','returned','cancelled') NOT NULL DEFAULT 'queued', settlement_date DATE NULL, return_code VARCHAR(16) NULL COMMENT 'NPCI return reason code, e.g. 02 = insufficient funds', return_reason VARCHAR(255) NULL, repayment_id BIGINT UNSIGNED NULL COMMENT 'Set once the credit is posted to the loan', bounce_charge_id BIGINT UNSIGNED NULL, retry_count TINYINT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_nach_item (batch_id, item_reference), KEY idx_np_mandate (mandate_id, presentation_date), KEY idx_np_loan (loan_account_id, status), KEY idx_np_status (status, presentation_date), CONSTRAINT fk_np_batch FOREIGN KEY (batch_id) REFERENCES nach_batches (id) ON DELETE CASCADE, CONSTRAINT fk_np_mandate FOREIGN KEY (mandate_id) REFERENCES nach_mandates (id) ON DELETE RESTRICT, CONSTRAINT fk_np_loan FOREIGN KEY (loan_account_id) REFERENCES loan_accounts (id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 09. NIDHI DEPOSITS & SHARE CAPITAL -- --------------------------------------------------------------------- -- Nidhi Rules 2014 constraints the engine enforces: -- r.6(d) no current accounts; r.14 deposits <= 20x Net Owned Funds -- r.13 FD 6-60 months, RD 12-60 months -- r.15 interest on FD not > the max rate SCBs may pay -- r.14(6) unencumbered term deposits >= 10% of outstanding deposits -- ===================================================================== CREATE TABLE deposit_products ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, code VARCHAR(24) NOT NULL, name VARCHAR(150) NOT NULL, deposit_type ENUM('savings','fixed','recurring','cumulative_fd','mis','pigmy','daily') NOT NULL, interest_rate_bps INT UNSIGNED NOT NULL, senior_citizen_bonus_bps INT UNSIGNED NOT NULL DEFAULT 0, compounding ENUM('none','monthly','quarterly','half_yearly','yearly','on_maturity') NOT NULL DEFAULT 'quarterly', payout_frequency ENUM('on_maturity','monthly','quarterly','half_yearly','yearly') NOT NULL DEFAULT 'on_maturity', min_amount BIGINT UNSIGNED NOT NULL, max_amount BIGINT UNSIGNED NULL, min_tenure_months SMALLINT UNSIGNED NOT NULL, max_tenure_months SMALLINT UNSIGNED NOT NULL, premature_allowed TINYINT(1) NOT NULL DEFAULT 1, premature_penalty_bps INT UNSIGNED NOT NULL DEFAULT 100, premature_lock_months TINYINT UNSIGNED NOT NULL DEFAULT 3, tds_applicable TINYINT(1) NOT NULL DEFAULT 1, tds_threshold BIGINT UNSIGNED NOT NULL DEFAULT 4000000 COMMENT 'Paise. Sec 194A threshold', tds_rate_bps INT UNSIGNED NOT NULL DEFAULT 1000, loan_against_allowed TINYINT(1) NOT NULL DEFAULT 1, max_loan_ltv_bps INT UNSIGNED NOT NULL DEFAULT 8000, gl_liability_id INT UNSIGNED NULL, gl_interest_expense_id INT UNSIGNED NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, effective_from DATE NOT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_depprod_code (org_id, code), KEY idx_depprod_active (org_id, is_active), CONSTRAINT chk_dep_tenure CHECK (max_tenure_months >= min_tenure_months) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE deposit_accounts ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, branch_id INT UNSIGNED NOT NULL, account_no VARCHAR(32) NOT NULL, member_id BIGINT UNSIGNED NOT NULL, product_id INT UNSIGNED NOT NULL, deposit_type ENUM('savings','fixed','recurring','cumulative_fd','mis','pigmy','daily') NOT NULL, principal_amount BIGINT UNSIGNED NOT NULL DEFAULT 0, instalment_amount BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'RD/pigmy periodic deposit', interest_rate_bps INT UNSIGNED NOT NULL, tenure_months SMALLINT UNSIGNED NOT NULL DEFAULT 0, open_date DATE NOT NULL, maturity_date DATE NULL, maturity_amount BIGINT UNSIGNED NOT NULL DEFAULT 0, current_balance BIGINT UNSIGNED NOT NULL DEFAULT 0, interest_accrued BIGINT UNSIGNED NOT NULL DEFAULT 0, interest_paid BIGINT UNSIGNED NOT NULL DEFAULT 0, tds_deducted BIGINT UNSIGNED NOT NULL DEFAULT 0, last_interest_date DATE NULL, auto_renew TINYINT(1) NOT NULL DEFAULT 0, lien_amount BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Marked when used as loan collateral', lien_loan_id BIGINT UNSIGNED NULL, nominee_id BIGINT UNSIGNED NULL, form15g_15h_on_file TINYINT(1) NOT NULL DEFAULT 0, status ENUM('active','matured','closed','prematurely_closed','dormant','lien_marked') NOT NULL DEFAULT 'active', closure_date DATE NULL, created_by INT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_dep_acct (org_id, account_no), KEY idx_dep_member (member_id, status), KEY idx_dep_maturity (org_id, maturity_date, status), KEY idx_dep_branch (branch_id, deposit_type, status), CONSTRAINT fk_dep_member FOREIGN KEY (member_id) REFERENCES members (id) ON DELETE RESTRICT, CONSTRAINT fk_dep_product FOREIGN KEY (product_id) REFERENCES deposit_products (id) ON DELETE RESTRICT, CONSTRAINT fk_dep_branch FOREIGN KEY (branch_id) REFERENCES branches (id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE deposit_transactions ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, deposit_account_id BIGINT UNSIGNED NOT NULL, receipt_no VARCHAR(32) NOT NULL, idempotency_key VARCHAR(64) NOT NULL, txn_type ENUM('deposit','withdrawal','interest_credit','tds_debit','penalty','maturity_payout','premature_closure','transfer_in','transfer_out','reversal') NOT NULL, amount BIGINT UNSIGNED NOT NULL, balance_after BIGINT UNSIGNED NOT NULL, value_date DATE NOT NULL, payment_mode ENUM('cash','upi','neft','imps','rtgs','cheque','transfer','adjustment') NOT NULL, reference_no VARCHAR(64) NULL, voucher_id BIGINT UNSIGNED NULL, reversal_of_id BIGINT UNSIGNED NULL, remarks VARCHAR(255) NULL, created_by INT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_deptxn_idem (idempotency_key), KEY idx_deptxn_acct (deposit_account_id, value_date), CONSTRAINT fk_deptxn_acct FOREIGN KEY (deposit_account_id) REFERENCES deposit_accounts (id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE share_transactions ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, member_id BIGINT UNSIGNED NOT NULL, txn_type ENUM('allotment','transfer_in','transfer_out','surrender','forfeiture') NOT NULL, share_count INT UNSIGNED NOT NULL, face_value BIGINT UNSIGNED NOT NULL COMMENT 'Paise per share. Nidhi Rules: Rs 10 nominal, min 10 shares', total_amount BIGINT UNSIGNED NOT NULL, certificate_no VARCHAR(32) NULL, distinctive_from BIGINT UNSIGNED NULL, distinctive_to BIGINT UNSIGNED NULL, txn_date DATE NOT NULL, voucher_id BIGINT UNSIGNED NULL, created_by INT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), KEY idx_share_member (member_id, txn_date), CONSTRAINT fk_share_member FOREIGN KEY (member_id) REFERENCES members (id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 10. DOUBLE-ENTRY ACCOUNTING -- --------------------------------------------------------------------- -- acc_chart_of_accounts -> the GL tree -- acc_vouchers -> journal header (one business event) -- transactions -> journal LINES (the double-entry legs) -- -- Invariant: SUM(debit) = SUM(credit) for every voucher_id. Enforced -- in FinLend\Domain\Accounting\Ledger::post() inside the same DB -- transaction that writes the business row, and re-proved nightly. -- ===================================================================== CREATE TABLE acc_chart_of_accounts ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, code VARCHAR(20) NOT NULL COMMENT 'Hierarchical, e.g. 1000 / 1100 / 1101', name VARCHAR(150) NOT NULL, parent_id INT UNSIGNED NULL, account_type ENUM('asset','liability','equity','income','expense') NOT NULL, account_subtype VARCHAR(48) NULL COMMENT 'cash, bank, loan_asset, deposit_liability, interest_income...', normal_balance ENUM('debit','credit') NOT NULL, is_group TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Group accounts cannot be posted to directly', is_bank_account TINYINT(1) NOT NULL DEFAULT 0, is_cash_account TINYINT(1) NOT NULL DEFAULT 0, branch_id INT UNSIGNED NULL COMMENT 'Set for branch-specific control accounts', -- Schedule III (Companies Act) grouping, for statutory financials schedule3_head VARCHAR(120) NULL, -- RBI return mapping so NBS-9 / DNBS-13 line items are auto-derived rbi_return_code VARCHAR(24) NULL, opening_balance BIGINT NOT NULL DEFAULT 0 COMMENT 'Signed paise, as at fy start', is_active TINYINT(1) NOT NULL DEFAULT 1, is_system TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_coa_code (org_id, code), KEY idx_coa_parent (parent_id), KEY idx_coa_type (org_id, account_type, is_active), CONSTRAINT fk_coa_org FOREIGN KEY (org_id) REFERENCES organisations (id) ON DELETE CASCADE, CONSTRAINT fk_coa_parent FOREIGN KEY (parent_id) REFERENCES acc_chart_of_accounts (id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE acc_vouchers ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, branch_id INT UNSIGNED NOT NULL, voucher_no VARCHAR(32) NOT NULL, voucher_type ENUM('receipt','payment','journal','contra','disbursement','accrual','provision', 'depreciation','write_off','opening','closing','reversal') NOT NULL, voucher_date DATE NOT NULL, posting_date DATE NOT NULL COMMENT 'Business day; must reference an OPEN business_days row', fy_year SMALLINT UNSIGNED NOT NULL COMMENT 'e.g. 2026 for FY 2026-27', narration VARCHAR(500) NOT NULL, total_debit BIGINT UNSIGNED NOT NULL, total_credit BIGINT UNSIGNED NOT NULL, -- Polymorphic link back to the business event that caused the entry source_module VARCHAR(32) NOT NULL COMMENT 'loan_repayment, disbursement, deposit, charge, eod_accrual...', source_id BIGINT UNSIGNED NULL, idempotency_key VARCHAR(64) NOT NULL, basis ENUM('cash','accrual') NOT NULL DEFAULT 'accrual', status ENUM('draft','posted','reversed') NOT NULL DEFAULT 'posted', reversal_of_id BIGINT UNSIGNED NULL, reversed_by_id BIGINT UNSIGNED NULL, approved_by INT UNSIGNED NULL, created_by INT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_voucher_no (org_id, voucher_no), UNIQUE KEY uq_voucher_idem (org_id, idempotency_key), KEY idx_voucher_date (org_id, posting_date, voucher_type), KEY idx_voucher_branch (branch_id, posting_date), KEY idx_voucher_source (source_module, source_id), CONSTRAINT fk_voucher_branch FOREIGN KEY (branch_id) REFERENCES branches (id) ON DELETE RESTRICT, CONSTRAINT chk_voucher_balanced CHECK (total_debit = total_credit) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- The general ledger. APPEND-ONLY (enforced by triggers below). CREATE TABLE transactions ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, branch_id INT UNSIGNED NOT NULL, voucher_id BIGINT UNSIGNED NOT NULL, line_no SMALLINT UNSIGNED NOT NULL, account_id INT UNSIGNED NOT NULL COMMENT 'FK to acc_chart_of_accounts', debit BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Paise', credit BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Paise', -- Sub-ledger dimensions, so a GL line can always be traced to a party member_id BIGINT UNSIGNED NULL, loan_account_id BIGINT UNSIGNED NULL, deposit_account_id BIGINT UNSIGNED NULL, cost_centre_id INT UNSIGNED NULL, posting_date DATE NOT NULL, value_date DATE NOT NULL, fy_year SMALLINT UNSIGNED NOT NULL, narration VARCHAR(500) NULL, instrument_type ENUM('cash','bank','transfer','adjustment','notional') NOT NULL DEFAULT 'transfer', reference_no VARCHAR(64) NULL, created_by INT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_txn_line (voucher_id, line_no), -- Trial balance / ledger extraction path KEY idx_txn_account_date (org_id, account_id, posting_date), KEY idx_txn_branch_date (branch_id, posting_date), KEY idx_txn_loan (loan_account_id, posting_date), KEY idx_txn_member (member_id, posting_date), KEY idx_txn_deposit (deposit_account_id, posting_date), KEY idx_txn_fy (org_id, fy_year, account_id), CONSTRAINT fk_txn_voucher FOREIGN KEY (voucher_id) REFERENCES acc_vouchers (id) ON DELETE RESTRICT, CONSTRAINT fk_txn_account FOREIGN KEY (account_id) REFERENCES acc_chart_of_accounts (id) ON DELETE RESTRICT, CONSTRAINT fk_txn_branch FOREIGN KEY (branch_id) REFERENCES branches (id) ON DELETE RESTRICT, CONSTRAINT fk_txn_loan FOREIGN KEY (loan_account_id) REFERENCES loan_accounts (id) ON DELETE RESTRICT, -- Exactly one side of every line must carry a value CONSTRAINT chk_txn_one_side CHECK ((debit = 0) <> (credit = 0)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Pre-aggregated closing balances per account per day. Turns a trial -- balance from a full-journal scan into a single indexed lookup. CREATE TABLE acc_daily_balances ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, branch_id INT UNSIGNED NOT NULL, account_id INT UNSIGNED NOT NULL, balance_date DATE NOT NULL, opening_balance BIGINT NOT NULL DEFAULT 0 COMMENT 'Signed', total_debit BIGINT UNSIGNED NOT NULL DEFAULT 0, total_credit BIGINT UNSIGNED NOT NULL DEFAULT 0, closing_balance BIGINT NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uq_adb (org_id, branch_id, account_id, balance_date), KEY idx_adb_date (balance_date, account_id), CONSTRAINT fk_adb_account FOREIGN KEY (account_id) REFERENCES acc_chart_of_accounts (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 11. IMMUTABLE AUDIT TRAIL -- --------------------------------------------------------------------- -- Every row carries the SHA-256 of the previous row for the same org -- (`prev_hash` -> `row_hash`). Deleting or editing any historic row -- breaks the chain, and the nightly verifier reports exactly where. -- This satisfies the Companies (Accounts) Rules audit-trail mandate -- and gives an RBI inspection a tamper-evident record. -- ===================================================================== CREATE TABLE audit_logs ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, branch_id INT UNSIGNED NULL, -- Who user_id INT UNSIGNED NULL, username VARCHAR(64) NULL COMMENT 'Denormalised — survives a user rename', role_slug VARCHAR(48) NULL, impersonated_by INT UNSIGNED NULL, -- What event_type ENUM('create','update','delete','view','login','logout','login_failed','export', 'approve','reject','reverse','waive','disburse','post','config_change','api_call','permission_change') NOT NULL, entity_type VARCHAR(64) NOT NULL COMMENT 'members, loan_accounts, transactions...', entity_id BIGINT UNSIGNED NULL, action VARCHAR(120) NOT NULL COMMENT 'Human-readable: "Reversed receipt RCP/2026/00412"', -- Before/after. PII-bearing fields are redacted by the Audit writer -- before serialisation; only changed keys are stored. old_values JSON NULL, new_values JSON NULL, changed_fields VARCHAR(500) NULL, amount_involved BIGINT NULL COMMENT 'Signed paise, for money-movement events', -- Where / how ip_address VARBINARY(16) NULL, user_agent VARCHAR(255) NULL, request_method VARCHAR(8) NULL, request_uri VARCHAR(255) NULL, request_id CHAR(36) NULL COMMENT 'Correlates every log line of one HTTP request', session_id CHAR(64) NULL, channel ENUM('web','mobile_app','api','system','cron') NOT NULL DEFAULT 'web', -- Risk severity ENUM('info','notice','warning','critical') NOT NULL DEFAULT 'info', is_sensitive TINYINT(1) NOT NULL DEFAULT 0, -- Tamper evidence prev_hash CHAR(64) NULL, row_hash CHAR(64) NOT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), KEY idx_audit_entity (entity_type, entity_id, created_at), KEY idx_audit_user (user_id, created_at), KEY idx_audit_org_time (org_id, created_at), KEY idx_audit_event (event_type, created_at), KEY idx_audit_severity (org_id, severity, created_at), KEY idx_audit_request (request_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 12. INTEGRATIONS, MESSAGING, JOBS & SETTINGS -- ===================================================================== CREATE TABLE integration_logs ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NULL, provider VARCHAR(48) NOT NULL COMMENT 'cibil, crif_highmark, karza, gupshup, digio, razorpay...', category ENUM('bureau','kyc','sms','whatsapp','email','nach','payment','banking','esign','other') NOT NULL, operation VARCHAR(64) NOT NULL, direction ENUM('outbound','inbound_webhook') NOT NULL DEFAULT 'outbound', entity_type VARCHAR(48) NULL, entity_id BIGINT UNSIGNED NULL, request_id CHAR(36) NULL, endpoint VARCHAR(255) NULL, http_method VARCHAR(8) NULL, request_headers JSON NULL COMMENT 'Auth headers redacted at write time', request_body_enc BLOB NULL, response_code SMALLINT UNSIGNED NULL, response_body_enc LONGBLOB NULL, status ENUM('pending','success','failed','timeout','retrying') NOT NULL DEFAULT 'pending', error_message VARCHAR(500) NULL, attempt_no TINYINT UNSIGNED NOT NULL DEFAULT 1, latency_ms INT UNSIGNED NULL, cost_paise INT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), KEY idx_intlog_provider (provider, created_at), KEY idx_intlog_entity (entity_type, entity_id), KEY idx_intlog_status (status, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE message_templates ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, code VARCHAR(48) NOT NULL COMMENT 'emi_reminder_t3, receipt_ack, overdue_d7, otp_login...', channel ENUM('sms','whatsapp','email','push','ivr') NOT NULL, language VARCHAR(8) NOT NULL DEFAULT 'en', subject VARCHAR(200) NULL, body TEXT NOT NULL COMMENT 'Placeholders: {{member_name}}, {{amount}}, {{due_date}}...', -- TRAI DLT compliance for Indian SMS dlt_template_id VARCHAR(32) NULL, dlt_entity_id VARCHAR(32) NULL, dlt_status ENUM('not_registered','pending','approved','rejected') NOT NULL DEFAULT 'not_registered', -- Meta WhatsApp Business template wa_template_name VARCHAR(64) NULL, wa_template_status ENUM('not_submitted','pending','approved','rejected','paused') NOT NULL DEFAULT 'not_submitted', wa_category ENUM('utility','authentication','marketing') NOT NULL DEFAULT 'utility', is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_msgtpl (org_id, code, channel, language), KEY idx_msgtpl_active (org_id, is_active) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE message_queue ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, template_id INT UNSIGNED NULL, channel ENUM('sms','whatsapp','email','push','ivr') NOT NULL, recipient VARCHAR(160) NOT NULL, member_id BIGINT UNSIGNED NULL, loan_account_id BIGINT UNSIGNED NULL, subject VARCHAR(200) NULL, body TEXT NOT NULL, payload_json JSON NULL COMMENT 'Rendered template variables, for resend/debug', -- RBI Fair Practices / recovery-agent norms: no contact before 08:00 -- or after 19:00. The dispatcher refuses to send outside this window. send_after DATETIME(6) NULL, priority TINYINT UNSIGNED NOT NULL DEFAULT 5, status ENUM('queued','sending','sent','delivered','read','failed','cancelled','suppressed') NOT NULL DEFAULT 'queued', provider VARCHAR(32) NULL, provider_message_id VARCHAR(96) NULL, attempts TINYINT UNSIGNED NOT NULL DEFAULT 0, max_attempts TINYINT UNSIGNED NOT NULL DEFAULT 3, last_error VARCHAR(500) NULL, cost_paise INT UNSIGNED NOT NULL DEFAULT 0, queued_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), sent_at DATETIME(6) NULL, delivered_at DATETIME(6) NULL, PRIMARY KEY (id), KEY idx_mq_dispatch (status, priority, send_after), KEY idx_mq_member (member_id, channel, queued_at), KEY idx_mq_provider_msg (provider_message_id), KEY idx_mq_loan (loan_account_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE job_queue ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, queue VARCHAR(48) NOT NULL DEFAULT 'default', job_class VARCHAR(150) NOT NULL, payload JSON NOT NULL, idempotency_key VARCHAR(64) NULL, available_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), reserved_at DATETIME(6) NULL, reserved_by VARCHAR(64) NULL COMMENT 'Worker id — prevents two workers claiming one job', attempts TINYINT UNSIGNED NOT NULL DEFAULT 0, max_attempts TINYINT UNSIGNED NOT NULL DEFAULT 3, status ENUM('pending','reserved','processing','completed','failed','dead') NOT NULL DEFAULT 'pending', last_error TEXT NULL, completed_at DATETIME(6) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_job_idem (idempotency_key), KEY idx_job_claim (queue, status, available_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE settings ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NULL COMMENT 'NULL = platform-wide default', branch_id INT UNSIGNED NULL COMMENT 'Most specific match wins: branch > org > platform', setting_key VARCHAR(96) NOT NULL, setting_value TEXT NULL, value_type ENUM('string','int','bool','json','date','encrypted') NOT NULL DEFAULT 'string', category VARCHAR(48) NOT NULL DEFAULT 'general', description VARCHAR(255) NULL, is_editable TINYINT(1) NOT NULL DEFAULT 1, updated_by INT UNSIGNED NULL, updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_setting (org_id, branch_id, setting_key), KEY idx_setting_cat (category) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Atomic, gap-free document numbering. Claimed with SELECT ... FOR UPDATE -- so two cashiers can never mint the same receipt number. CREATE TABLE number_sequences ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, branch_id INT UNSIGNED NULL, sequence_key VARCHAR(48) NOT NULL COMMENT 'member_no, loan_no, receipt_no, voucher_no...', prefix VARCHAR(24) NOT NULL DEFAULT '', fy_year SMALLINT UNSIGNED NULL COMMENT 'Set when the series resets every financial year', current_value BIGINT UNSIGNED NOT NULL DEFAULT 0, padding TINYINT UNSIGNED NOT NULL DEFAULT 5, updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_seq (org_id, branch_id, sequence_key, fy_year) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Generated statutory returns, kept with their file hash as filing proof CREATE TABLE compliance_reports ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, org_id INT UNSIGNED NOT NULL, report_code VARCHAR(32) NOT NULL COMMENT 'NDH-1, NDH-3, DNBS-13, NBS-9, CIC_MONTHLY, CKYC_UPLOAD, FIU_CTR, FIU_STR', report_name VARCHAR(150) NOT NULL, regulator ENUM('rbi','mca','fiu_ind','cersai','cic','gst','income_tax','internal') NOT NULL, period_type ENUM('daily','monthly','quarterly','half_yearly','annual','adhoc') NOT NULL, period_start DATE NOT NULL, period_end DATE NOT NULL, due_date DATE NULL, file_path VARCHAR(255) NULL, file_format ENUM('xml','csv','txt','xlsx','pdf','json') NOT NULL DEFAULT 'csv', file_hash CHAR(64) NULL, record_count INT UNSIGNED NOT NULL DEFAULT 0, total_amount BIGINT UNSIGNED NOT NULL DEFAULT 0, status ENUM('pending','generating','generated','submitted','acknowledged','rejected','revised') NOT NULL DEFAULT 'pending', submitted_at DATETIME(6) NULL, acknowledgement_no VARCHAR(64) NULL, rejection_reason VARCHAR(500) NULL, generated_by INT UNSIGNED NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), UNIQUE KEY uq_compliance (org_id, report_code, period_start, period_end), KEY idx_comp_due (org_id, status, due_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 13. DEFERRED FOREIGN KEYS -- (added last because they point at tables created further down) -- ===================================================================== ALTER TABLE branches ADD CONSTRAINT fk_branch_cash_gl FOREIGN KEY (cash_gl_id) REFERENCES acc_chart_of_accounts (id) ON DELETE SET NULL, ADD CONSTRAINT fk_branch_bank_gl FOREIGN KEY (bank_gl_id) REFERENCES acc_chart_of_accounts (id) ON DELETE SET NULL; ALTER TABLE members ADD CONSTRAINT fk_member_primary_bank FOREIGN KEY (primary_bank_account_id) REFERENCES member_bank_accounts (id) ON DELETE SET NULL, ADD CONSTRAINT fk_member_portal_user FOREIGN KEY (portal_user_id) REFERENCES users (id) ON DELETE SET NULL; ALTER TABLE member_groups ADD CONSTRAINT fk_group_leader FOREIGN KEY (leader_member_id) REFERENCES members (id) ON DELETE SET NULL, ADD CONSTRAINT fk_group_secretary FOREIGN KEY (secretary_member_id) REFERENCES members (id) ON DELETE SET NULL; ALTER TABLE loan_products ADD CONSTRAINT fk_prod_gl_principal FOREIGN KEY (gl_principal_id) REFERENCES acc_chart_of_accounts (id) ON DELETE SET NULL, ADD CONSTRAINT fk_prod_gl_int_income FOREIGN KEY (gl_interest_income_id) REFERENCES acc_chart_of_accounts (id) ON DELETE SET NULL, ADD CONSTRAINT fk_prod_gl_int_recv FOREIGN KEY (gl_interest_receivable_id) REFERENCES acc_chart_of_accounts (id) ON DELETE SET NULL, ADD CONSTRAINT fk_prod_gl_fee FOREIGN KEY (gl_fee_income_id) REFERENCES acc_chart_of_accounts (id) ON DELETE SET NULL, ADD CONSTRAINT fk_prod_gl_penal FOREIGN KEY (gl_penal_income_id) REFERENCES acc_chart_of_accounts (id) ON DELETE SET NULL, ADD CONSTRAINT fk_prod_gl_provision FOREIGN KEY (gl_provision_id) REFERENCES acc_chart_of_accounts (id) ON DELETE SET NULL; ALTER TABLE loan_accounts ADD CONSTRAINT fk_loan_mandate FOREIGN KEY (nach_mandate_id) REFERENCES nach_mandates (id) ON DELETE SET NULL, ADD CONSTRAINT fk_loan_collofcr FOREIGN KEY (collection_officer_id) REFERENCES users (id) ON DELETE SET NULL; ALTER TABLE loan_repayments ADD CONSTRAINT fk_repay_voucher FOREIGN KEY (voucher_id) REFERENCES acc_vouchers (id) ON DELETE RESTRICT, ADD CONSTRAINT fk_repay_sheet FOREIGN KEY (collection_sheet_id) REFERENCES collection_sheets (id) ON DELETE SET NULL; ALTER TABLE loan_disbursements ADD CONSTRAINT fk_disb_voucher FOREIGN KEY (voucher_id) REFERENCES acc_vouchers (id) ON DELETE RESTRICT; ALTER TABLE nach_presentations ADD CONSTRAINT fk_np_repayment FOREIGN KEY (repayment_id) REFERENCES loan_repayments (id) ON DELETE SET NULL; ALTER TABLE collection_sheet_lines ADD CONSTRAINT fk_csl_repayment FOREIGN KEY (repayment_id) REFERENCES loan_repayments (id) ON DELETE SET NULL; -- ===================================================================== -- 14. IMMUTABILITY TRIGGERS -- --------------------------------------------------------------------- -- Defence in depth. Even if the application is compromised, the ledger -- and the audit trail cannot be rewritten through the app's DB grant. -- Corrections must be posted as reversal rows. -- ===================================================================== DELIMITER $$ CREATE TRIGGER trg_transactions_no_update BEFORE UPDATE ON transactions FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'transactions is append-only: post a reversal voucher instead of updating a ledger line'; END$$ CREATE TRIGGER trg_transactions_no_delete BEFORE DELETE ON transactions FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'transactions is append-only: ledger lines cannot be deleted'; END$$ CREATE TRIGGER trg_audit_no_update BEFORE UPDATE ON audit_logs FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'audit_logs is immutable'; END$$ CREATE TRIGGER trg_audit_no_delete BEFORE DELETE ON audit_logs FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'audit_logs is immutable'; END$$ CREATE TRIGGER trg_repayment_no_delete BEFORE DELETE ON loan_repayments FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'loan_repayments is append-only: reverse the receipt instead of deleting it'; END$$ -- A receipt may only ever move forward through its lifecycle, and its -- money columns are frozen the moment it is written. CREATE TRIGGER trg_repayment_guard_update BEFORE UPDATE ON loan_repayments FOR EACH ROW BEGIN IF NEW.amount <> OLD.amount OR NEW.loan_account_id <> OLD.loan_account_id OR NEW.value_date <> OLD.value_date OR NEW.principal_component <> OLD.principal_component OR NEW.interest_component <> OLD.interest_component OR NEW.penal_component <> OLD.penal_component THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Financial fields of a posted receipt are immutable; post a reversal'; END IF; IF OLD.status IN ('reversed','cancelled') AND NEW.status <> OLD.status THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'A reversed or cancelled receipt cannot be re-opened'; END IF; END$$ -- Nothing may post into a closed business day. CREATE TRIGGER trg_voucher_day_open BEFORE INSERT ON acc_vouchers FOR EACH ROW BEGIN DECLARE v_status VARCHAR(16); SELECT status INTO v_status FROM business_days WHERE org_id = NEW.org_id AND (branch_id = NEW.branch_id OR branch_id IS NULL) AND business_date = NEW.posting_date ORDER BY branch_id IS NULL LIMIT 1; IF v_status IS NOT NULL AND v_status = 'closed' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot post: the business day is closed. Re-open it or post to the current day.'; END IF; END$$ DELIMITER ; -- ===================================================================== -- 15. REPORTING VIEWS -- Amounts are converted to rupees here, once, at the edge. -- ===================================================================== CREATE OR REPLACE VIEW v_trial_balance AS SELECT t.org_id, t.branch_id, b.name AS branch_name, t.fy_year, coa.code AS account_code, coa.name AS account_name, coa.account_type, ROUND(SUM(t.debit) / 100, 2) AS total_debit, ROUND(SUM(t.credit) / 100, 2) AS total_credit, ROUND((SUM(t.debit) - SUM(t.credit)) / 100, 2) AS net_balance FROM transactions t JOIN acc_chart_of_accounts coa ON coa.id = t.account_id JOIN branches b ON b.id = t.branch_id GROUP BY t.org_id, t.branch_id, b.name, t.fy_year, coa.code, coa.name, coa.account_type; CREATE OR REPLACE VIEW v_loan_portfolio AS SELECT la.org_id, la.branch_id, b.name AS branch_name, lp.code AS product_code, lp.name AS product_name, lp.regulatory_class, la.asset_classification, COUNT(*) AS account_count, ROUND(SUM(la.principal) / 100, 2) AS sanctioned_amount, ROUND(SUM(la.disbursed_amount) / 100, 2) AS disbursed_amount, ROUND(SUM(la.principal_outstanding) / 100, 2) AS principal_outstanding, ROUND(SUM(la.overdue_principal + la.overdue_interest) / 100, 2) AS total_overdue, ROUND(SUM(la.provision_amount) / 100, 2) AS provision_held, ROUND(AVG(la.dpd), 1) AS avg_dpd FROM loan_accounts la JOIN loan_products lp ON lp.id = la.product_id JOIN branches b ON b.id = la.branch_id WHERE la.status IN ('active','overdue','npa') GROUP BY la.org_id, la.branch_id, b.name, lp.code, lp.name, lp.regulatory_class, la.asset_classification; -- Portfolio at Risk — the single number an MFI board asks for first. CREATE OR REPLACE VIEW v_par_summary AS SELECT org_id, branch_id, ROUND(SUM(principal_outstanding) / 100, 2) AS gross_loan_portfolio, ROUND(SUM(CASE WHEN dpd > 0 THEN principal_outstanding ELSE 0 END) / 100, 2) AS par_1, ROUND(SUM(CASE WHEN dpd > 30 THEN principal_outstanding ELSE 0 END) / 100, 2) AS par_30, ROUND(SUM(CASE WHEN dpd > 60 THEN principal_outstanding ELSE 0 END) / 100, 2) AS par_60, ROUND(SUM(CASE WHEN dpd > 90 THEN principal_outstanding ELSE 0 END) / 100, 2) AS par_90, ROUND(100 * SUM(CASE WHEN dpd > 30 THEN principal_outstanding ELSE 0 END) / NULLIF(SUM(principal_outstanding), 0), 2) AS par_30_pct, ROUND(100 * SUM(CASE WHEN npa_flag = 1 THEN principal_outstanding ELSE 0 END) / NULLIF(SUM(principal_outstanding), 0), 2) AS gnpa_pct FROM loan_accounts WHERE status IN ('active','overdue','npa') GROUP BY org_id, branch_id; CREATE OR REPLACE VIEW v_collection_efficiency AS SELECT s.org_id, s.branch_id, s.collection_date, s.officer_id, u.full_name AS officer_name, COUNT(DISTINCT s.id) AS sheets, SUM(s.expected_accounts) AS expected_accounts, SUM(s.collected_accounts) AS collected_accounts, ROUND(SUM(s.expected_amount) / 100, 2) AS demand, ROUND(SUM(s.collected_amount) / 100, 2) AS collected, ROUND(100 * SUM(s.collected_amount) / NULLIF(SUM(s.expected_amount), 0), 2) AS efficiency_pct FROM collection_sheets s JOIN users u ON u.id = s.officer_id GROUP BY s.org_id, s.branch_id, s.collection_date, s.officer_id, u.full_name; SET FOREIGN_KEY_CHECKS = 1; SET SQL_MODE = @OLD_SQL_MODE; -- ===================================================================== -- END OF SCHEMA -- =====================================================================