FinTrust schema

All tables live in the fintrust schema, so write fintrust.customers in your queries.

fintrust.customers

One row per bank customer.

• customer_id (INT) — PK
• full_name (VARCHAR(80))
• city (VARCHAR(40))
• segment (VARCHAR(20)) — 'retail', 'premium', 'business'
• kyc_status (VARCHAR(20)) — 'VERIFIED', 'PENDING', 'REJECTED'
• joined_date (DATE)

fintrust.accounts

Accounts held by customers; a customer can hold several.

• account_id (INT) — PK
• customer_id (INT) — FK → customers.customer_id
• account_type (VARCHAR(20)) — 'SAVINGS', 'CURRENT', 'CREDIT_CARD'
• opened_date (DATE)
• status (VARCHAR(10)) — 'ACTIVE', 'CLOSED', 'FROZEN'
• balance (DECIMAL(14,2)) — current balance in INR; negative for card dues

fintrust.transactions

Money movements on accounts (INR).

• txn_id (INT) — PK
• account_id (INT) — FK → accounts.account_id
• txn_at (TIMESTAMP)
• amount (DECIMAL(14,2)) — always positive
• txn_type (VARCHAR(6)) — 'DEBIT' or 'CREDIT'
• merchant (VARCHAR(60)) — nullable; NULL for ATM withdrawals and transfers
• merchant_category (VARCHAR(20)) — nullable; 'shopping', 'food', 'grocery', 'travel', 'entertainment', 'health', 'fuel', 'salary', 'transfer'
• channel (VARCHAR(10)) — 'POS', 'ONLINE', 'UPI', 'ATM', 'NEFT'

fintrust.loans

Loans issued to customers.

• loan_id (INT) — PK
• customer_id (INT) — FK → customers.customer_id
• account_id (INT) — FK → accounts.account_id; account the EMIs are debited from
• loan_type (VARCHAR(12)) — 'HOME', 'PERSONAL', 'AUTO', 'EDUCATION'
• principal (DECIMAL(14,2))
• interest_rate (DECIMAL(5,2)) — % per annum
• tenure_months (INT)
• start_date (DATE)
• end_date (DATE) — nullable; set when the loan is closed or written off
• status (VARCHAR(10)) — 'ACTIVE', 'CLOSED', 'DEFAULTED'

fintrust.repayments

The EMI schedule: one row per instalment due, with what was paid.

• repayment_id (INT) — PK
• loan_id (INT) — FK → loans.loan_id
• due_date (DATE)
• amount_due (DECIMAL(12,2))
• payment_date (DATE) — nullable; NULL while unpaid
• amount_paid (DECIMAL(12,2)) — 0 while unpaid

fintrust.fraud_signals

Alerts raised on transactions by rules, the ML model or staff.

• signal_id (INT) — PK
• txn_id (INT) — FK → transactions.txn_id
• signal_type (VARCHAR(12)) — 'RULE_ENGINE', 'ML_MODEL', 'MANUAL'
• score (DECIMAL(4,3)) — 0.000 to 1.000; higher is riskier
• created_at (TIMESTAMP)
• review_outcome (VARCHAR(10)) — nullable; 'fraud' or 'legit' after an analyst review; NULL if not reviewed