Database — TEMCO Banking App SRS

Schema	Type	Engine	Purpose
`temco_system`	Primary (R/W)	MariaDB 10.6	All banking operations — 175 existing tables
`ijts_system`	Read-only Slave	MariaDB 10.6	External master replica — student data source
`admin_callingDB`	External	MariaDB 10.6	CRM / Calling system

Existing Entity Groups

175 tables currently in temco_system organized into functional groups

Group A: User & Authentication

15 tables

user_loginuser_roleuser_role_has_system_interface user_role_has_permissionuser_login_has_roleuser_login_has_system_interface user_login_groupuser_role_managementuser_type permissionlogin_sessioncom_login_attempt com_session_tokenpassword_reset_tokenotp_tokens

Group B: Member & Profile

18 tables

general_user_profilemembermember_bank_accounts member_documentsmember_has_membermember_organizations_history membership_levelmembership_level_has_share_certificatemember_category member_has_categorymobile_nonominess gendereducation_levelprofeession relationshipregistration_typespot_member

Group C: Organization & Branch

16 tables

general_organization_profilegop_has_memberorganization_branches organization_typeorganization_sub_typesorg_branchers org_categoryorg_category_managerorg_departments org_chat_of_accountbranch_managerbranch_level business_informationbusiness_sectorgross_anual_turnover employee

Group D: Loan Management

27 tables

loanloan_managerloan_type loan_termloan_durationloan_status loan_status_managerloan_offeroffer_manager loan_interest_rateinterest_managerloan_installement_plan loan_payment_historyloan_applicant_gurantorloan_applicant_has_branch loan_customerloan_doc_commentloan_document_status_manager loan_documents_schedulergurantor_managergurantor_salary_info guarantor_documentsgurantor_countpenalty approval_levelapproval_statusrepayment_period

Group E: Student Loan & Eligibility

6 tables

materialized_student_loan_eligible_student_tablescholarship_catergory scholarship_managerintakeintake_manager package_manager

Group F: Finance & Accounting

16 tables

fin_chart_of_accountfin_account_categoryfin_journal_entry fin_journal_entry_linefin_journal_entry_managerfin_voucher fin_voucher_itemfin_voucher_item_managerfin_voucher_approval fin_partnerfin_partner_has_typefin_partner_type fin_fiscal_yearfin_fiscal_periodfin_revenue_center fin_bank_reconciliation

Group G: Voucher & Payment

14 tables

vouchervoucher_itemvoucher_type voucher_statusvoucher_approval_managervoucher_attachment transaction_typecredit_or_debitpay_order_settlement_guide pay_order_settlement_statementpay_order_settlment_voucher_manager pay_sheetgeneral_journal_entrygeneral_journal_entry_manager

Group H: Document Management

14 tables

universal_user_documentuniversal_org_documents_manager document_verificationdocument_data_verification document_creatordocument_fields_manager document_image_pathdocument_inactive document_itemsdocuments_submission_status master_documentscreated_document submitted_user_documentuploaded_document_file_path

Group I: Reference & Lookup

20+ tables

bankbank_accountbank_statement branch_of_the_bankswift_codesaccount_type countryprovincedistrict citydivisional_secretarialgn_division currency_ratescurrency_rate_typeshare_certificate share_typestatuspriority designationdepartments

Group J: System & Security

12 tables

system_interfaceinterface_menuinterface_menu_gop_manager interface_sub_menusettingssettings_type security_activity_logsdata_changed_log_manager whatsapp_configwhatsapp_message_log unlock_rate_limitflyway_schema_history

New Tables Required

84 new tables across 6 functional categories

Deposits

10 new tables

fixed_depositfd_interest_rate_slab fd_certificatefd_renewal_history fd_lienrecurring_deposit rd_installment_schedulerd_payment_history savings_interest_tieraccount_dormancy

Transactions

12 new tables

interbank_transferceft_batch slips_batchstanding_order standing_order_execution_logdirect_debit_mandate direct_debit_execution_logcheque_book cheque_leafcheque_clearing cheque_return_reasondemand_draft

Payment Gateway

10 new tables

merchantmerchant_api_key payment_transactionpayment_refund merchant_settlementbiller biller_categorybill_payment qr_merchantqr_payment_transaction

Joint Venture

11 new tables

joint_venturejv_partner jv_signatory_rulejv_agreement jv_pnl_periodjv_pnl_distribution jv_income_expenseescrow_account escrow_conditionescrow_release escrow_dispute

Investments

22 new tables

investment_accountsecurity_master stock_transactionportfolio_holding dividend_recordmoney_market_instrument mm_transactionrepo_agreement government_bondbond_coupon_schedule bond_transactionunit_trust_fund unit_holderunit_transaction fund_nav_historymanaged_portfolio portfolio_mandateportfolio_allocation portfolio_feecorporate_bond bond_issuercredit_rating_history

Specialized Lending

19 new tables

property_valuationproperty_details mortgage_registrationvehicle_details vehicle_valuationdealer gold_itemgold_price_rate pawn_ticketvault_storage overdraft_facilityoverdraft_utilization_log lease_assetlease_agreement lease_schedulemicro_group micro_group_memberrepayment_frequency auction

Entity Relationship Overview

┌─────────────────────────────────────────────────────────────┐
│                    USER & MEMBER MANAGEMENT                  │
│  general_user_profile ←→ user_login ←→ user_role            │
│  member ←→ member_bank_accounts ←→ membership_level         │
│  nominess ←→ member_has_member ←→ mobile_no                 │
└──────────────────────────┬──────────────────────────────────┘
                           │ FK: general_user_profile.id
┌──────────────────────────▼──────────────────────────────────┐
│                    ORGANIZATION MANAGEMENT                   │
│  general_organization_profile ←→ gop_has_member             │
│  organization_branches ←→ branch_manager                    │
│  business_information ←→ business_sector                    │
└──────────────────────────┬──────────────────────────────────┘
                           │
    ┌──────────────────────┼──────────────────────┐
    ▼                      ▼                      ▼
┌──────────────┐  ┌──────────────┐  ┌──────────────────────┐
│ LOAN MGMT    │  │ DEPOSIT MGMT │  │ TRANSACTION SERVICES │
│ (27 existing │  │ (10 new)     │  │ (12 new)             │
│  + 19 new)   │  │              │  │                      │
│ loan         │  │ fixed_deposit│  │ interbank_transfer   │
│ loan_manager │  │ recurring_dep│  │ standing_order       │
│ installment  │  │ savings_tier │  │ cheque_clearing      │
└──────┬───────┘  └──────┬───────┘  └──────────┬───────────┘
       │                 │                      │
       ▼                 ▼                      ▼
┌─────────────────────────────────────────────────────────────┐
│                    FINANCE & ACCOUNTING                      │
│  fin_chart_of_account ←→ fin_journal_entry                  │
│  fin_voucher ←→ fin_voucher_item ←→ fin_voucher_approval   │
│  voucher ←→ voucher_item ←→ transaction_type                │
└─────────────────────────────────────────────────────────────┘

Database Design