Commit Vault – The Banking App

A Secure Full-Stack Financial Management Ecosystem

Project By: PALLAVI JAIN (35720802724)

Overview
CommitVault is a secure, full-stack financial management ecosystem designed to
handle complex banking operations with high data integrity. The core philosophy of the
project is "Database-First Logic," where critical business rules—such as fund transfer
validations, audit logging, and portfolio aggregation—are handled directly by the MySQL
engine through Stored Procedures, Triggers, and Views. This architecture ensures that
the system remains secure and consistent regardless of the frontend or middleware
being used.

Core Features
 Real-Time Dashboard: Aggregates data from multiple accounts into a single
"Total Portfolio" view using MySQL Views.
 Atomic Fund Transfers: Uses ACID-compliant Stored Procedures to ensure that
money is never "lost" during a transfer (either both accounts update, or neither
does).
 Automated Audit Trail: Database triggers automatically log every account
creation and transaction into a secure audit_logs table for administrative
oversight.
 Security & Validation: Implements strict ENUM constraints and Check
Constraints to prevent negative balances or invalid data entry at the database
level.
Technical Stack
Frontend (Client Layer)
 Library: [Link] (v18+)
 Build Tool: Vite (for optimized production bundling)
 Styling: Tailwind CSS (Utility-first framework for responsive UI)
 Icons: Lucide-React
 API Client: Axios (Handling asynchronous requests to the Azure API)
Backend (Middleware Layer)
 Runtime: [Link]
 Framework: [Link]
 Database Driver: mysql2/promise (supporting connection pooling and
async/await syntax)
 Security: CORS (Cross-Origin Resource Sharing) and dotenv for environment
variable protection.
Infrastructure & Deployment
 Database Hosting: Aiven Cloud (Managed MySQL Instance)
o Why: Provides high availability and built-in SSL encryption.
 Backend Hosting: Microsoft Azure App Service
o Why: Enterprise-grade scaling and seamless integration with GitHub for
CI/CD.
 Frontend Hosting: Vercel
o Why: Fast global delivery via Edge Networks and automatic SSL
management.
System Workflow (The "Request Journey")
1. User Action: User clicks "Confirm Transfer" on the React frontend.
2. API Call: Axios sends a POST request with JSON data to the Azure API.
3. Database Handshake: The API verifies the request and executes a Stored
Procedure on the Aiven Cloud DB.
4. Database Execution:
o The Procedure checks for an "Insu icient Balance" using a row lock.
o If valid, it updates two rows in accounts and inserts two rows in
transactions.
o If invalid, it performs a ROLLBACK, and the [Link] catch block logs the
failure in failed_transactions.
5. UI Update: The API returns a success/error message, and the React frontend
triggers a re-fetch of the updated balances.
ER Diagram
Frontend:
[Link]
// --- SECTION 1: IMPORTS & SETUP ---

require('dotenv').config();

const express = require('express');

const mysql = require('mysql2/promise');

const cors = require('cors');

const app = express();

[Link]([Link]());

[Link](cors({

origin: ['[Link] '[Link]

credentials: true

}));

// --- SECTION 2: DATABASE CONNECTION ---

const db = [Link]({

host: [Link].DB_HOST,

port: parseInt([Link].DB_PORT) || 3306, // <-- Forces this to be a number

user: [Link].DB_USER,

password: [Link].DB_PASSWORD,

database: 'CommitVault',

ssl: {

rejectUnauthorized: false

});
// --- THE DIAGNOSTIC TEST ---

[Link]()

.then(conn => {

[Link](" SUCCESS: Connected to Aiven Cloud Database!");

[Link]();

})

.catch(err => {

[Link](" ERROR: Could not connect to Aiven.");

[Link]([Link]); // This will spit out the exact reason!

});

// --- SECTION 3: API ROUTES ---

// Route A: Fetching the Dashboard Data

[Link]('/api/dashboard/:customerId', async (req, res) => {

try {

const { customerId } = [Link];

const [summary] = await [Link]('SELECT * FROM account_summary_view

WHERE customer_id = ?', [customerId]);

const [transactions] = await [Link](`

SELECT t.transaction_id, t.transaction_type, [Link], t.transaction_date,

[Link]

FROM transactions t

JOIN accounts a ON t.account_id = a.account_id

WHERE a.customer_id = ?

ORDER BY t.transaction_date DESC LIMIT 5

 `, [customerId]);

[Link]({ summary: summary[0], transactions });

} catch (error) {

[Link](500).json({ error: [Link] });

});

// Route B: Processing the Transfer

[Link]('/api/transfer', async (req, res) => {

try {

const { senderAccountId, receiverAccountId, amount } = [Link];

await [Link]('CALL ProcessFundTransfer(?, ?, ?)', [senderAccountId,

receiverAccountId, amount]);

[Link]({ message: 'Transfer Successful!' });

} catch (error) {

[Link](400).json({ error: [Link] });

});

// Route C: Get All Customers (For the Navbar Dropdown)

[Link]('/api/customers', async (req, res) => {

try {

// Fetching from the actual customers table so the dropdown populates!

const [rows] = await [Link]('SELECT * FROM customers');

[Link](rows);
 } catch (err) {

[Link](500).json({ error: [Link] });

});

// Route D: Get Accounts for a Specific Customer (For the Transfer Dropdown)

// Check this in [Link]

[Link]('/api/accounts/:customerId', async (req, res) => {

const { customerId } = [Link];

try {

// Is it 'customer_id' or 'user_id' in your Aiven DB?

const [rows] = await [Link]('SELECT * FROM accounts WHERE customer_id = ?',

[customerId]);

[Link]("Accounts found:", rows); // Add this to see the result in your VS Code
terminal

[Link](rows);

} catch (err) {

[Link](500).json({ error: [Link] });

});

// --- SECTION 4: SERVER START ---

const PORT = [Link] || 5000;

[Link](PORT, () => {

[Link](`Server running on port ${PORT}`);

});
Backend:
[Link]
import { useState, useE ect } from 'react';

import axios from 'axios';

import { ShieldCheck, Send, History, Wallet, Bell, Loader2, UserCircle } from 'lucide-
react';

export default function App() {

const API_URL = [Link].VITE_API_URL;

// --- STATE MANAGEMENT ---

const [data, setData] = useState({ summary: null, transactions: [] });

const [loading, setLoading] = useState(true);

// New States for dynamic selection

const [customers, setCustomers] = useState([]);

const [accounts, setAccounts] = useState([]);

const [currentCustomerId, setCurrentCustomerId] = useState(1); // Defaults to user 1

on load

const [senderAccountId, setSenderAccountId] = useState(''); // Selected account to

send money FROM

// Transfer Form States

const [transferAmount, setTransferAmount] = useState('');

const [receiverId, setReceiverId] = useState('');

// 1. Fetch ALL customers on load

useE ect(() => {

[Link](`${API_URL}/api/customers`)
 .then(res => setCustomers([Link]))

.catch(err => [Link](err));

}, []);

// 2. Fetch Dashboard & Accounts when currentCustomerId changes

useE ect(() => {

setLoading(true);

// Fetch Dashboard Stats

[Link](`${API_URL}/api/dashboard/${currentCustomerId}`)

.then(res => setData([Link]))

.catch(err => [Link](err));

// Fetch specific accounts for the Transfer Dropdown

[Link](`${API_URL}/api/accounts/${currentCustomerId}`)

.then(res => {

setAccounts([Link]);

// Auto-select their first account

setSenderAccountId([Link] > 0 ? [Link][0].account_id : '');

setLoading(false);

})

.catch(err => {

[Link](err);

setLoading(false);

});

}, [currentCustomerId]);

const handleTransfer = async (e) => {

 [Link]();

if (!senderAccountId) return alert("Select an account to send from.");

try {

await [Link](`${API_URL}/api/transfer`, {

senderAccountId: parseInt(senderAccountId),

receiverAccountId: parseInt(receiverId),

amount: parseFloat(transferAmount)

});

alert('Transfer Successful!');

setTransferAmount('');

setReceiverId('');

const dashResponse = await

[Link](`${API_URL}/api/dashboard/${currentCustomerId}`);

setData([Link]);

} catch (error) {

alert([Link]?.data?.error || 'Transfer failed');

};

if (loading) return (

<div className="min-h-screen bg-slate-900 flex items-center justify-center text-

emerald-500">

<Loader2 className="w-12 h-12 animate-spin" />

</div>

);
return (

<div className="min-h-screen bg-slate-900 text-slate-200 font-sans p-6">

<nav className="flex justify-between items-center mb-10 border-b border-slate-

700 pb-4 max-w-[90%] mx-auto">

<div className="flex items-center gap-2">

<ShieldCheck className="text-emerald-500 w-8 h-8" />

<h1 className="text-2xl font-bold text-white tracking-wide">Commit<span

className="text-emerald-500">Vault</span></h1>

</div>

<div className="flex items-center gap-4">

<UserCircle className="w-7 h-7 text-slate-400" />

<select

value={currentCustomerId}

onChange={(e) => setCurrentCustomerId([Link])}

className="bg-slate-800 text-white border border-slate-700 p-2 rounded-lg

outline-none focus:border-emerald-500 cursor-pointer"

>

{[Link](customer => (

<option key={customer.customer_id} value={customer.customer_id}>

{customer.first_name} {customer.last_name}

</option>

))}

</select>

<Bell className="w-7 h-7 text-slate-400 cursor-pointer ml-4" />

</div>

</nav>

<div className="max-w-[90%] mx-auto grid grid-cols-1 md:grid-cols-3 gap-6">

 <div className="md:col-span-1 space-y-6">

<div className="bg-slate-800 p-6 rounded-2xl border border-slate-700 shadow-

lg">

<h2 className="text-slate-400 text-sm uppercase tracking-wider mb-2 flex

items-center gap-2">

<Wallet className="w-4 h-4" /> Total Portfolio

</h2>

<p className="text-4xl font-bold text-white mb-1">

₹{parseFloat([Link]?.total_portfolio_balance || 0).toLocaleString('en-US',
{ minimumFractionDigits: 2 })}

</p>

<p className="text-emerald-400 text-sm">{[Link]?.total_accounts}

Active Accounts</p>

</div>

<form onSubmit={handleTransfer} className="bg-slate-800 p-6 rounded-2xl

border border-slate-700 shadow-lg flex flex-col gap-4">

<h3 className="text-white font-bold flex items-center gap-2"><Send

className="w-4 h-4 text-emerald-500"/> Quick Transfer</h3>

{/* ADD THIS RIGHT ABOVE THE RECEIVER ID INPUT */}

<select

value={senderAccountId}

onChange={(e) => setSenderAccountId([Link])}

className="w-full bg-slate-900 text-white p-3 rounded-xl border border-slate-

700 focus:border-emerald-500 outline-none cursor-pointer"

required

>

<option value="" disabled>Select From Account...</option>

{[Link](acc => (
 <option key={acc.account_id} value={acc.account_id}>

{acc.account_type} - ID: {acc.account_id}

(₹{parseFloat([Link]).toLocaleString()})

</option>

))}

</select>

<input

type="number"

placeholder="Receiver Account ID (e.g. 2)"

value={receiverId}

onChange={(e) => setReceiverId([Link])}

className="w-full bg-slate-900 text-white p-3 rounded-xl border border-slate-

700 focus:border-emerald-500 outline-none"

required

/>

<input

type="number"

placeholder="Amount (₹)"

value={transferAmount}

onChange={(e) => setTransferAmount([Link])}

className="w-full bg-slate-900 text-white p-3 rounded-xl border border-slate-

700 focus:border-emerald-500 outline-none"

required

/>

<button type="submit" className="w-full bg-emerald-600 hover:bg-emerald-500

text-white p-3 rounded-xl font-bold transition-colors shadow-lg">

Confirm Transfer

</button>
 </form>

</div>

<div className="md:col-span-2 bg-slate-800 rounded-2xl border border-slate-700

shadow-lg overflow-hidden">

<div className="p-6 border-b border-slate-700">

<h2 className="text-lg font-bold text-white flex items-center gap-2"><History

className="w-5 h-5"/> Recent Transactions</h2>

</div>

<div className="p-0 overflow-x-auto">

<table className="w-full text-left border-collapse">

<thead>

<tr className="bg-slate-900/50 text-slate-400 text-xs uppercase tracking-

wider">

<th className="p-4 font-medium">Date</th>

<th className="p-4 font-medium">Description</th>

<th className="p-4 font-medium text-right">Amount</th>

</tr>

</thead>

<tbody className="divide-y divide-slate-700">

{[Link]((txn) => (

<tr key={txn.transaction_id} className="hover:bg-slate-700/50 transition-

colors">

<td className="p-4 text-sm text-slate-300">

{new Date(txn.transaction_date).toLocaleDateString()}

</td>

<td className="p-4 text-sm">

<p className="text-white font-medium">{[Link]}</p>

<p className="text-xs text-slate-500">{txn.transaction_type}</p>

 </td>

<td className={`p-4 text-right font-bold ${txn.transaction_type.includes('In')

|| txn.transaction_type === 'Deposit' ? 'text-emerald-400' : 'text-red-400'}`}>

{txn.transaction_type.includes('In') || txn.transaction_type === 'Deposit' ? '+' :

'-'}₹{parseFloat([Link]).toFixed(2)}

</td>

</tr>

))}

{[Link] === 0 && (

<tr><td colSpan="3" className="p-8 text-center text-slate-500">No

transactions found.</td></tr>

)}

</tbody>

</table>

</div>

</div>

</div>

</div>

);

}
Backend:

-- TABLE 1: Customers (Contains personal KYC data)

CREATE TABLE customers (

customer_id INT PRIMARY KEY AUTO_INCREMENT,

first_name VARCHAR(50) NOT NULL,

last_name VARCHAR(50) NOT NULL,

email VARCHAR(100) UNIQUE NOT NULL,

phone_number VARCHAR(15) UNIQUE NOT NULL,

kyc_status ENUM('Pending', 'Verified', 'Rejected') DEFAULT 'Pending',

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

-- TABLE 2: Accounts (1-to-Many relationship with Customers)

CREATE TABLE accounts (

account_id INT PRIMARY KEY AUTO_INCREMENT,

customer_id INT NOT NULL,

account_type ENUM('Savings', 'Current', 'Salary') DEFAULT 'Savings',

balance DECIMAL(15, 2) NOT NULL DEFAULT 0.00,

status ENUM('Active', 'Dormant', 'Closed') DEFAULT 'Active',

last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE

CURRENT_TIMESTAMP,

CONSTRAINT fk_customer

FOREIGN KEY (customer_id)

REFERENCES customers(customer_id)

ON DELETE RESTRICT,

CONSTRAINT chk_positive_balance

CHECK (balance >= 0) -- Unit I: Check Constraint

);

-- TABLE 3: Transactions (1-to-Many relationship with Accounts)

CREATE TABLE transactions (

transaction_id VARCHAR(36) PRIMARY KEY, -- Using UUIDs/Strings for realistic TXN

IDs

account_id INT NOT NULL,

transaction_type ENUM('Deposit', 'Withdrawal', 'Transfer_In', 'Transfer_Out') NOT

NULL,

amount DECIMAL(15, 2) NOT NULL,

transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

description VARCHAR(255),

CONSTRAINT fk_account

FOREIGN KEY (account_id)

REFERENCES accounts(account_id)

ON DELETE CASCADE,

CONSTRAINT chk_positive_amount

CHECK (amount > 0) -- Cannot process a negative or zero transaction

);

-- TABLE 4: Audit_Logs (For Database Security tracking - Impresses Examiners)

CREATE TABLE audit_logs (

log_id INT PRIMARY KEY AUTO_INCREMENT,

action_type VARCHAR(50) NOT NULL,

table_a ected VARCHAR(50) NOT NULL,

action_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

details TEXT

);
INSERT INTO customers (first_name, last_name, email, phone_number, kyc_status)
VALUES

('Aarav', 'Sharma', 'aarav.s@[Link]', '+919876543210', 'Verified'),

('Priya', 'Patel', 'priya.p@[Link]', '+919876543211', 'Verified'),

('Rohan', 'Verma', 'rohan.v@[Link]', '+919876543212', 'Pending');

INSERT INTO accounts (customer_id, account_type, balance, status) VALUES

(1, 'Savings', 15000.50, 'Active'),

(1, 'Current', 50000.00, 'Active'),

(2, 'Savings', 8500.75, 'Active'),

(3, 'Salary', 0.00, 'Dormant');

INSERT INTO transactions (transaction_id, account_id, transaction_type, amount,

description) VALUES

('TXN-10001', 1, 'Deposit', 5000.00, 'Initial Account Funding'),

('TXN-10002', 2, 'Deposit', 20000.00, 'Business Revenue'),

('TXN-10003', 1, 'Withdrawal', 1000.00, 'ATM Withdrawal'),

('TXN-10004', 3, 'Deposit', 8500.75, 'Salary Credit');

CREATE VIEW Account_Summary_View AS

SELECT

c.customer_id,

CONCAT(c.first_name, ' ', c.last_name) AS full_name, -- Unit II: String Function

COUNT(a.account_id) AS total_accounts, -- Unit II: Aggregate Function

SUM([Link]) AS total_portfolio_balance,

MAX(t.transaction_date) AS last_activity_date -- Unit II: Date/Aggregate Function

FROM customers c

LEFT JOIN accounts a ON c.customer_id = a.customer_id -- Unit II: JOIN

LEFT JOIN transactions t ON a.account_id = t.account_id

GROUP BY c.customer_id, full_name;

DELIMITER $$

-- TRIGGER: Automatically log whenever a new account is created

CREATE TRIGGER after_account_creation

AFTER INSERT ON accounts

FOR EACH ROW

BEGIN

INSERT INTO audit_logs (action_type, table_a ected, details)

VALUES ('INSERT', 'accounts', CONCAT('New account ', NEW.account_id, ' created for
customer ', NEW.customer_id));

END$$

-- STORED PROCEDURE: Secure Fund Transfer between two accounts

CREATE PROCEDURE ProcessFundTransfer(

IN p_sender_account_id INT,

IN p_receiver_account_id INT,

IN p_transfer_amount DECIMAL(15, 2)

BEGIN

-- Exception Handling for Rollback (Unit III)

DECLARE current_balance DECIMAL(15, 2);

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

ROLLBACK; -- Unit II: Transaction Control Command

INSERT INTO audit_logs (action_type, table_a ected, details)

VALUES ('ERROR', 'transactions', 'Transfer failed. Rolled back.');

 END;

-- Check sender's balance before transferring

SELECT balance INTO current_balance FROM accounts WHERE account_id =

p_sender_account_id FOR UPDATE;

IF current_balance >= p_transfer_amount THEN

START TRANSACTION; -- Unit II: Transaction Control Command

-- 1. Deduct from sender

UPDATE accounts SET balance = balance - p_transfer_amount WHERE account_id =

p_sender_account_id;

INSERT INTO transactions (transaction_id, account_id, transaction_type, amount,

description)

VALUES (UUID(), p_sender_account_id, 'Transfer_Out', p_transfer_amount,

CONCAT('Sent to account ', p_receiver_account_id));

-- 2. Add to receiver

UPDATE accounts SET balance = balance + p_transfer_amount WHERE account_id

= p_receiver_account_id;

INSERT INTO transactions (transaction_id, account_id, transaction_type, amount,

description)

VALUES (UUID(), p_receiver_account_id, 'Transfer_In', p_transfer_amount,

CONCAT('Received from account ', p_sender_account_id));

COMMIT; -- Unit II: Transaction Control Command

ELSE

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insu icient Funds for Transfer';

END IF;

END$$

DELIMITER ;