Varappuzha DB — Schema Reference (Claude Session Prompt)
Database: PostgreSQL 13 | Schema: varappuzha | Type: Cooperative Bank / NBFC Core Banking System
How to use this file
Paste this into your Claude (VSCode) session as context before asking queries, writing SQL, or building features against this database.
Domain Map — Table Groups
1. CORE ACCOUNT & CUSTOMER
| Table |
Purpose |
customer |
Master record — name, DOB, caste, PAN, membership_no, photo, KYC flags |
cust_addr |
Customer addresses (multiple addr_type per customer) |
cust_phone |
Phone numbers |
cust_joint |
Joint holders on a customer record |
cust_guardian / cust_major_to_minor |
Guardian linkage & minor→major conversion |
cust_type |
Customer type lookup |
cust_history |
Audit history of customer changes |
cust_id_change |
Customer ID change log |
act_master |
Core savings/OA account — balances, status, agent, card_acct_num |
act_joint |
Joint holders on an account |
act_param_detail |
ATM/cheque/mobile banking flags per account |
act_nominee_detail |
Nominee details for accounts |
act_authorize |
Authorised signatories |
act_lien / act_freeze |
Lien & freeze on accounts |
act_charges |
Charges applied to account |
act_closing |
Account closure details |
act_action_history |
Account-level audit trail |
act_interest / act_interest_trial |
Interest applied / trial runs |
pass_book / pass_book_tmp |
Passbook entries |
2. SAVINGS / OPERATIVE ACCOUNTS (OA)
| Table |
Purpose |
op_ac_product |
OA product master (prod_id, ac_hd_id, behavior) |
op_ac_account_param |
Account-level params per OA product |
op_ac_int_cat_master |
Interest category master |
op_ac_intratemaint_param |
Interest rate maintenance |
op_ac_intpay_param |
Interest payment params |
op_ac_intrecv_param |
Interest receivable params |
op_ac_charges_param |
Charges config per OA product |
op_ac_achead_param |
Account head mappings |
op_ac_spclitem_param |
Special item params (ATM, cheque, etc.) |
3. DEPOSITS (FD / RD / MDS)
| Table |
Purpose |
deposit_acinfo |
Deposit account master — prod_id, cust_id, deposit_no, auto_renewal, agent_id |
deposit_sub_acinfo |
Sub-account: deposit_dt, maturity_dt, rate_of_int, deposit_amt, balances |
deposits_product |
Product master for deposits |
deposits_prod_achd |
Account head mapping per deposit product |
deposits_prod_intpay |
Interest payment config |
deposits_prod_rd |
RD (recurring deposit) specific config |
deposits_prod_renewal |
Renewal config |
deposits_prod_scheme |
Scheme linkage |
deposits_prod_tax |
TDS / tax config |
deposit_recurring / deposit_rdinstall |
RD installment tracking |
deposit_roi_group / *_cat / *_prod / *_type_rate |
Interest rate group slabs |
deposit_tds_deduction |
TDS deducted on deposits |
deposit_freeze / deposit_lien |
Freeze & lien on deposits |
deposit_nominee_detail |
Nominees for deposits |
deposit_poa |
Power of attorney |
deposit_standing_instru / *_cr |
Standing instructions (debit/credit) |
deposit_ren_list / deposit_ren_tmp |
Renewal staging tables |
deposit_dayend_balance |
Day-end balances per deposit account |
deposit_interest / daily_deposit_interest |
Interest applied & daily logs |
deposit_provision |
Provision entries |
MDS (Monthly Deposit Scheme / Chit-like):
| Table |
Purpose |
mds_application |
MDS application |
mds_master_maintenance |
MDS scheme master |
mds_scheme_details |
Scheme details (installment_no, members, prized_amount, bonus) |
mds_product_general_details / mds_product_other_details |
MDS product config |
mds_trans_details |
Transactions per MDS member |
mds_installment_schedule |
Installment schedule |
mds_prized_money_details |
Prizing / auction results |
mds_money_payment_details |
Payments to prized members |
mds_closure_details |
Closure |
mds_acct_head |
Account head mapping |
mds_security_land / mds_salary_security_details |
Securities |
mds_member_receipt_entry / mds_receipt_entry |
Receipt entries |
4. LOANS
| Table |
Purpose |
loans_borrower |
Borrower master (borrow_no → cust_id, constitution, branch) |
loans_facility_details |
Loan account master (acct_num, prod_id, balances, interest dates, gold details) |
loans_sanction_details |
Sanction terms (limit, repayment_frequency, moratorium) |
loans_sanction |
Sanction master |
loans_product |
Loan product master (behaves_like: TL/OD/KCC, interest_type, emi_flat_rate) |
loans_prod_achd |
Account heads per loan product |
loans_prod_intcalc |
Interest calculation config |
loans_prod_intrec |
Interest receivable config |
loans_prod_charges |
Charges config |
loans_prod_classification |
Sector/priority classification |
loans_classify_details |
Loan-level classification (NPA, priority sector, asset_status) |
loans_installment |
EMI schedule |
loans_repay_schedule |
Repayment schedule master |
loans_repayment |
Repayment transactions |
loans_interest |
Interest calculated |
loans_int_maintenance |
Rate maintenance per account |
loans_disbursement |
Disbursement records |
loans_drawing_power |
Drawing power (OD/KCC) |
loans_guarantor_details |
Guarantors |
loans_security_details / *_gold_stock / *_land / *_vehicle / *_salary / *_member |
Security types |
loans_doc |
Documents received |
loans_dayend_balance |
Day-end balances |
loan_trans_details |
Detailed transaction log |
loans_closing_int_tmp |
Closing interest temp |
loans_ots_details / loans_ots_installment |
One-Time Settlement |
loans_waive_off / loan_interest_waive_off / loans_rebate_interest_details |
Waivers & rebates |
Agricultural Loans — mirror of above with agri_loans_* / agriloans_* prefix.
Advances (OD/Bills) — adv_* / advances_* / bills_* tables mirror the loans structure for overdraft and bills discounting.
5. SHARES
| Table |
Purpose |
share_acct |
Share account master |
share_acct_details |
Share holdings detail (share_no_from/to, value) |
share_conf_details |
Share configuration |
share_dividend / share_dividend_calc_* |
Dividend config & calculations |
share_joint / share_nominee_detail / share_poa |
Joint / nominee / POA |
share_transfer |
Share transfer log |
share_resolution |
Board resolutions for share ops |
share_priority |
Share priority rules |
share_prod_loans |
Share-backed loan linkage |
6. GL / ACCOUNTING
| Table |
Purpose |
gl |
General Ledger — ac_hd_id, opn_bal, cur_bal, branch_code |
gl_abstract |
GL abstract (daily summary by date & branch) |
ac_hd |
Account head master (mjr/sub, ac_hd_id, ac_hd_desc) |
mjr_ac_hd |
Major account head master |
sub_ac_hd |
Sub account head |
ac_hd_param |
GL param — credit/debit rules, balance type |
ac_hd_acct_info |
Balances per sub account head |
accounthead_table |
Account head type lookup |
branch_gl / branch_gl_group |
Branch-wise GL groupings |
gl_limit |
GL limit controls |
trans_ref_gl |
Transaction → GL mapping |
addtogl |
Pending additions to GL |
7. TRANSACTIONS
| Table |
Purpose |
cash_trans |
Cash transactions (act_num, prod_type, amount, trans_dt, batch_id, authorize_status) |
transfer_trans |
Transfer transactions (same fields + initiated_branch, gl_trans_act_num) |
all_charges_maintenance |
Charge definitions |
trans_parameters |
Transaction parameter config |
trans_value_date |
Value date overrides |
cash_trans_del / transfer_trans_del |
Deleted transaction archives |
cash_trans_temp |
Staging for cash transactions |
batch_process |
Batch process log |
daily_deposit_trans |
Daily deposit transaction summary |
8. CLEARING & REMITTANCE
| Table |
Purpose |
inward_clearing / outward_clearing |
Clearing instrument records |
inward_bouncing / outward_return |
Returned instruments |
outward_tally / inward_tally |
Tally reconciliation |
clearing_param / clearing_bank_param |
Clearing config |
cheque_issue / cheque_stop_payment |
Cheque management |
remittance_product / remit_issue / remit_issue_trans |
DD/PO/RTGS product & issuance |
rtgs_neft_acknowledgement / rtgs_outward_ack |
RTGS/NEFT acks |
pay_in_slip |
Pay-in slip details |
9. BRANCH / SYSTEM ADMIN
| Table |
Purpose |
branch_master |
Branch master (branch_code, IFSC, MICR, IP, working hours) |
user_master |
System users (user_id, pwd, role, branch, suspension) |
role_master |
Roles & hierarchy |
access_lvl_master |
Menu/screen/function access |
day_end |
Current application date per branch |
daily_daybegin_status / *_final |
Day-begin status |
daily_dayend_status / *_final |
Day-end status |
holiday_master |
Holiday calendar |
parameters |
Global system parameters |
param_settings |
Additional param settings |
admin_param |
Password policy config |
screen_master / menu_master / func_id_master |
UI navigation config |
terminal_master |
Terminal / workstation config |
user_login_history |
Login/logout audit |
password_history |
Password change history |
screen_access_history |
Screen access audit |
log / err_log |
System logs |
10. EMPLOYEE / PAYROLL / HR
| Table |
Purpose |
employee_master |
Employee master (employeeid, name, branch, designation, scale_id) |
employee_basic / employee_addr / employee_other_details / employee_present_details |
Employee detail tables |
employee_dependent_details / employee_relative_director |
Family info |
payroll / payroll_increment / payroll_leave |
Payroll processing |
salary_master / salary_details / salary_grade / salary_structure |
Salary config |
salary_credit |
Salary disbursement |
salary_recovery_list_master / *_detail |
Salary recovery from loan accounts |
emp_leave / leave_application / leave_master |
Leave management |
paycodes_master / pay_account / pay_settings |
Paycode & deduction config |
scale_master / scale_details |
Pay scale master |
income_tax_employee / incometax_slab / incometax_calculation |
IT config |
11. AGENT / COLLECTION
| Table |
Purpose |
agent_master |
Field agent master |
agent_prod_mapping |
Agent → product mapping with commission slabs |
agent_collection_prod |
Products an agent collects for |
agent_commision_slab / agent_commission_calc_slab |
Commission slabs |
agents_monthly_schedule |
Monthly commission settlement |
agent_leave_details |
Leave & substitute agent |
12. LOCKER
| Table |
Purpose |
locker_product / locker_config / locker_config_details |
Locker product setup |
locker_master |
Individual locker assignment |
locker_issue_joint / locker_operation |
Joint holders & operations |
locker_freeze / locker_surrender |
Freeze & surrender |
locker_prod_charges / locker_issue_charges |
Charges |
13. INVESTMENT / BORROWINGS
| Table |
Purpose |
investment_master |
Investment (FD with other banks, govt securities) |
investment_deposit / *_renewal |
Investment deposit tracking |
investment_trans_details |
Investment transactions |
borrowing_master / borrowing_disbursal |
Borrowings from higher-level banks |
drf_product / drf_transaction / drf_interest_rates |
DRF (Deposit Renewal Fund) |
14. MPR / REPORTS
| Table |
Purpose |
mpr_* tables |
Monthly Progress Report data — shares, deposits, loans by category, caste, product |
rpt_* tables |
Report configuration — loan OD buckets, amount-wise slabs |
query_report_master / query_report_parameters |
Custom query report builder |
report_screens / report_template_master |
Report templates |
gl_abstract |
Aggregated GL for balance sheet |
balancesheet_balancefinal / *_balanceupdate |
Balance sheet finalization |
15. MISCELLANEOUS / SUPPORT
| Table |
Purpose |
standing_instruction / si |
Standing instructions |
sms_parameter / sms_subscription / sms_acknowledgment |
SMS alerts |
atm_card_master / atm_card_transaction |
ATM card management |
ace_upi_card_master / ace_upi_transaction |
UPI integration |
tds_config / tds_collected / tds_exemption |
TDS management |
service_tax_details / *_settings / *_trans |
Service tax |
forex_exchange_rate / forex_currency_exchange / forex_denomination_master |
Forex |
other_bank / other_bank_branch / other_bank_account_products |
Other bank master |
ifsc_bank_branch |
IFSC directory |
id_generation |
Auto-number generation config |
lookup_master / lookup_master_desc |
Code lookup tables |
npa_history |
NPA classification history |
trading_* |
Trading module (paddy, goods purchase/sale/stock) |
gahan_* |
Gold appraisal details |
locker_* |
(see Locker section above) |
reconciliation_trans |
Reconciliation entries |
visitor_diary |
Visitor register |
Key Relationships (Quick Reference)
customer (cust_id)
├── act_master (cust_id → act_num) ──── cash_trans / transfer_trans (act_num)
├── deposit_acinfo (cust_id → deposit_no)
│ └── deposit_sub_acinfo (deposit_no)
├── loans_borrower (cust_id → borrow_no)
│ └── loans_facility_details (borrow_no → acct_num)
│ └── loans_sanction_details (borrow_no, sanction_no)
├── share_acct (cust_id → share_acc_num)
└── agent_master (agent_id links via deposit_acinfo.agent_id)
ac_hd (ac_hd_id) ──── gl (ac_hd_id) ──── ac_hd_param (ac_hd_id)
branch_master (branch_code) ─── user_master (branch_code) ─── day_end (branch_code)
Common Status Flags
authorize_status: 'AUTHORIZED' | 'CREATED' | 'DELETED'status: 'ACTIVE' | 'CLOSED' | 'DELETED'prod_type in transactions: 'OA' (Operative) | 'DEP' | 'LOAN' | 'ADV' | 'SUS' (Suspense)behaves_like in products: 'SB', 'CA', 'RD', 'FD', 'TL', 'OD', 'KCC'
Schemas (Packages — stored procedures only, no tables)
pkg_app_exec, pkg_app_valdate, pkg_fin_rpt, pkg_gn_pbk, pkg_reports_general, pkgdayend, pkggenreports, pkgreports, pkgschedule, pkgtrans
Auto-generated from varappuzhadb_schema_Only.sql (PostgreSQL 13, dumped 2025-08-19)