💰 Personal Finance & Expense Tracker

A comprehensive database management system for tracking personal finances, expenses, budgets, and recurring payments with powerful analytics and reporting capabilities.

---

📋 Table of Contents

• Overview
• Features
• Tech Stack
• Database Architecture
• Installation
• Usage
• API Documentation
• Project Structure
• Documentation
• Screenshots
• Contributing
• Future Enhancements
• License

---

🎯 Overview

Simple Explanation (Teacher-Friendly)

This project is a Personal Finance Management System built using MySQL database. It helps users track their daily expenses and income across multiple accounts (like bank accounts, cash, credit cards).

Think of it as a digital notebook where you record every rupee you spend or earn. The system automatically:

• Updates your account balances
• Categorizes your spending (food, transport, entertainment, etc.)
• Creates monthly reports showing where your money goes
• Warns you if you're exceeding your budget
• Manages recurring payments like subscriptions

The database is properly normalized (follows 1NF, 2NF, 3NF rules) to avoid data redundancy. It uses triggers to automatically update balances and stored procedures to generate complex reports efficiently.

Technical Explanation (GitHub-Friendly)

A full-stack financial management application with a robust MySQL relational database backend. The system implements:

• Multi-account management with real-time balance tracking
• Double-entry transaction logging for data integrity
• Many-to-many tagging system for flexible expense categorization
• Budget monitoring with threshold alerts
• Recurring payment automation using scheduled tasks
• Complex analytical queries for financial insights
• Database triggers for automatic balance updates and constraint validation
• Stored procedures for report generation and data aggregation
• RESTful API built with Flask/Node.js
• Normalized schema (3NF) with proper indexing strategies

Real-World Use Cases

1. Personal Budget Management: Track monthly spending across categories
2. Multi-Account Consolidation: Manage cash, multiple bank accounts, credit cards in one place
3. Financial Goal Tracking: Monitor savings progress and budget adherence
4. Tax Preparation: Generate annual expense reports by category
5. Subscription Management: Track and predict recurring payments
6. Split Expense Tracking: Tag transactions for shared expenses with roommates/family
7. Financial Analytics: Understand spending patterns with visual reports

Why This Project Matters

• Data Integrity: Proper DBMS principles ensure no financial data is lost or corrupted
• Scalability: Normalized schema handles thousands of transactions efficiently
• Automation: Triggers and procedures reduce manual work and human errors
• Privacy: Self-hosted solution keeps sensitive financial data secure
• Learning Value: Demonstrates real-world application of DBMS concepts (normalization, ACID properties, stored procedures, triggers, indexing)
• Professional Portfolio: Showcases database design, backend development, and system architecture skills

---

✨ Features

Core Features

• ✅ Multi-Account Management - Track cash, bank accounts, credit cards, digital wallets
• ✅ Transaction Tracking - Record income and expenses with detailed metadata
• ✅ Category Management - Organize transactions with custom categories
• ✅ Flexible Tagging - Add multiple tags to transactions for cross-category analysis
• ✅ Budget Planning - Set monthly/yearly budgets per category with alerts
• ✅ Recurring Payments - Automate tracking of subscriptions and regular bills
• ✅ Real-time Balance Updates - Automatic balance calculation using triggers
• ✅ Negative Balance Prevention - Database-level constraints for data integrity

Reporting & Analytics

• 📊 Monthly Summary Reports - Income vs Expense breakdowns
• 📈 Category-wise Analysis - Spending patterns by category
• 💡 Budget vs Actual - Compare planned vs actual spending
• 🔍 Transaction Search - Filter by date, category, tags, accounts
• 📅 Recurring Payment Dashboard - View upcoming bills

Database Features

• 🔒 ACID Compliance - Ensures transaction reliability
• 🗂️ 3NF Normalized Schema - Eliminates data redundancy
• ⚡ Optimized Indexes - Fast query performance
• 🔄 Automatic Triggers - Balance updates and validation
• 📦 Stored Procedures - Complex report generation
• 🔐 User Authentication - Secure multi-user support

---

🛠️ Tech Stack

Database

• MySQL 8.0+ - Relational database management system
• MySQL Workbench - Database design and administration

Backend

• Python 3.8+ - Primary programming language
• Flask 2.0+ - Web framework for REST API
• SQLAlchemy - ORM for database interactions
• PyMySQL - MySQL database connector

Frontend (Optional)

• React.js - UI framework
• Chart.js - Data visualization
• Axios - API client

Development Tools

• Git - Version control
• Postman - API testing
• Draw.io - ER diagram creation
• VSCode - Code editor

---

🏗️ Database Architecture

The system uses 8 core tables with proper relationships:

Users → Accounts → Transactions ← Categories
                        ↓
                  Transaction_Tags
                        ↓
                      Tags
                        
Budgets (linked to Users + Categories)
Recurring_Payments (linked to Users + Categories)

Key Relationships:

• One User → Many Accounts (1:N)
• One Account → Many Transactions (1:N)
• One Category → Many Transactions (1:N)
• Many Transactions ↔ Many Tags (M:N via Transaction_Tags)
• One User → Many Budgets (1:N)

See ER_DIAGRAM.md for detailed entity-relationship documentation.

---

🚀 Installation

Prerequisites

# MySQL 8.0 or higher
mysql --version

# Python 3.8 or higher
python --version

# pip package manager
pip --version

Step 1: Clone Repository

git clone https://github.com/yourusername/personal-finance-tracker.git
cd personal-finance-tracker

Step 2: Create Virtual Environment

# Windows
python -m venv venv
venv\Scripts\activate

# Linux/Mac
python3 -m venv venv
source venv/bin/activate

Step 3: Install Dependencies

pip install -r requirements.txt

Step 4: Configure Database

# Create MySQL database
mysql -u root -p

# In MySQL prompt:
CREATE DATABASE finance_tracker;
EXIT;

Step 5: Run Database Schema

mysql -u root -p finance_tracker < database/schema.sql
mysql -u root -p finance_tracker < database/procedures.sql
mysql -u root -p finance_tracker < database/triggers.sql
mysql -u root -p finance_tracker < database/seed_data.sql

Step 6: Configure Environment

Create .env file in project root:

DB_HOST=localhost
DB_USER=root
DB_PASSWORD=your_password
DB_NAME=finance_tracker
SECRET_KEY=your_secret_key_here

Step 7: Run Application

# Start Flask server
python app.py

# Server runs on http://localhost:5000

---

💻 Usage

Adding Your First Account

curl -X POST http://localhost:5000/api/accounts \
  -H "Content-Type: application/json" \
  -d '{
    "user_id": 1,
    "account_name": "Main Bank Account",
    "account_type": "bank",
    "balance": 50000.00
  }'

Recording a Transaction

curl -X POST http://localhost:5000/api/transactions \
  -H "Content-Type: application/json" \
  -d '{
    "account_id": 1,
    "category_id": 2,
    "amount": 500.00,
    "type": "expense",
    "description": "Grocery shopping",
    "tags": ["food", "essential"]
  }'

Generating Monthly Report

curl http://localhost:5000/api/reports/monthly?month=11&year=2025

See API_DOCUMENTATION.md for complete API reference.

---

📚 API Documentation

Authentication

POST   /api/auth/register    Register new user
POST   /api/auth/login       User login

Accounts

GET    /api/accounts         List all accounts
POST   /api/accounts         Create new account
PUT    /api/accounts/:id     Update account
DELETE /api/accounts/:id     Delete account

Transactions

GET    /api/transactions     List transactions (with filters)
POST   /api/transactions     Add transaction
PUT    /api/transactions/:id Update transaction
DELETE /api/transactions/:id Delete transaction

Categories

GET    /api/categories       List all categories
POST   /api/categories       Create category

Reports

GET    /api/reports/monthly         Monthly summary
GET    /api/reports/category        Category breakdown
GET    /api/reports/budget-status   Budget vs actual

Recurring Payments

GET    /api/recurring              List recurring payments
POST   /api/recurring              Add recurring payment
PUT    /api/recurring/:id          Update recurring payment
DELETE /api/recurring/:id          Delete recurring payment

Full API documentation with request/response examples: API_DOCUMENTATION.md

---

📁 Project Structure

personal-finance-tracker/
│
├── app.py                      # Flask application entry point
├── requirements.txt            # Python dependencies
├── .env.example               # Environment variables template
├── README.md                  # This file
│
├── database/                  # Database files
│   ├── schema.sql            # Table definitions
│   ├── procedures.sql        # Stored procedures
│   ├── triggers.sql          # Database triggers
│   ├── seed_data.sql         # Sample data
│   └── indexes.sql           # Index definitions
│
├── docs/                      # Documentation
│   ├── ER_DIAGRAM.md         # Entity-Relationship diagram
│   ├── NORMALIZATION.md      # Normalization explanation
│   ├── DFD.md                # Data Flow Diagrams
│   ├── USE_CASES.md          # Use case diagrams
│   ├── SEQUENCE_DIAGRAMS.md  # Sequence diagrams
│   ├── FLOWCHARTS.md         # Process flowcharts
│   ├── API_DOCUMENTATION.md  # API reference
│   ├── VIVA_QUESTIONS.md     # Interview questions
│   └── TEACHER_GUIDE.md      # Professor-friendly explanation
│
├── api/                       # Flask API routes
│   ├── __init__.py
│   ├── auth.py               # Authentication routes
│   ├── accounts.py           # Account management
│   ├── transactions.py       # Transaction routes
│   ├── categories.py         # Category routes
│   ├── reports.py            # Report generation
│   └── recurring.py          # Recurring payments
│
├── models/                    # Database models (SQLAlchemy)
│   ├── __init__.py
│   ├── user.py
│   ├── account.py
│   ├── transaction.py
│   ├── category.py
│   ├── tag.py
│   └── budget.py
│
├── utils/                     # Helper functions
│   ├── __init__.py
│   ├── db.py                 # Database connection
│   ├── validators.py         # Input validation
│   └── helpers.py            # Utility functions
│
└── tests/                     # Unit tests
    ├── test_accounts.py
    ├── test_transactions.py
    └── test_reports.py

---

📖 Documentation

Comprehensive documentation is available in the docs/ folder:

Document	Description
ER_DIAGRAM.md	Complete ER diagram with entity descriptions
NORMALIZATION.md	Normalization process (1NF → 2NF → 3NF)
DFD.md	Data Flow Diagrams (Level 0, 1, 2)
USE_CASES.md	Use case diagrams and descriptions
SEQUENCE_DIAGRAMS.md	Sequence diagrams for key flows
FLOWCHARTS.md	Process flowcharts
API_DOCUMENTATION.md	Complete API reference
VIVA_QUESTIONS.md	20+ viva questions with answers
TEACHER_GUIDE.md	Professor-friendly project explanation

---

📸 Screenshots

Dashboard

Main dashboard showing account balances, recent transactions, and spending summary

Transaction List

Detailed transaction history with filtering and search

Monthly Report

Visual analytics with category breakdown and budget comparison

Budget Management

Budget creation and monitoring interface

---

🤝 Contributing

Contributions are welcome! Please follow these guidelines:

How to Contribute

1. Fork the repository

git clone https://github.com/yourusername/personal-finance-tracker.git

2. Create a feature branch

git checkout -b feature/your-feature-name

3. Make your changes

   • Write clean, documented code
   • Follow existing code style
   • Add tests for new features

4. Commit your changes

git commit -m "Add: Description of your feature"

5. Push to your fork

git push origin feature/your-feature-name

6. Open a Pull Request
   • Describe your changes
   • Reference any related issues
   • Wait for review

Code Style

• Follow PEP 8 for Python code
• Use meaningful variable names
• Add comments for complex logic
• Write docstrings for functions

Reporting Issues

• Use GitHub Issues
• Provide clear description
• Include steps to reproduce
• Add relevant logs/screenshots

---

🚀 Future Enhancements

Phase 1 - Intelligence

• 🤖 AI-based Auto-categorization - Machine learning to automatically categorize transactions
• 📊 Predictive Analytics - Forecast future expenses based on historical data
• 💡 Smart Budget Recommendations - AI suggests optimal budget allocations

Phase 2 - Integration

• 🏦 Bank API Integration - Automatic transaction import from banks
• 🔗 UPI Payment Tracking - Real-time capture of UPI transactions
• 📧 Email Bill Parsing - Extract transaction data from email receipts
• 📱 SMS Parser - Auto-capture transactions from bank SMS

Phase 3 - Advanced Features

• 📸 OCR Bill Scanning - Scan and extract data from paper receipts
• 🌍 Multi-currency Support - Track expenses in different currencies
• 👥 Shared Budgets - Family/roommate expense splitting
• 🎯 Financial Goals - Savings goal tracking with milestones

Phase 4 - Analytics

• 📈 Investment Tracking - Monitor stocks, mutual funds, crypto
• 💰 Net Worth Calculator - Assets vs liabilities tracking
• 📉 Spending Trends - Year-over-year comparison
• 🔔 Smart Alerts - Unusual spending detection

Phase 5 - Platform

• 📱 Mobile App (React Native) - iOS and Android apps
• 🌐 Web Dashboard - Rich web interface with React
• 🔐 End-to-end Encryption - Enhanced data security
• ☁️ Cloud Sync - Multi-device synchronization

---

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

---

👨‍💻 Author

Your Name

• GitHub: @yourusername
• LinkedIn: Your Name
• Email: [email protected]

---

🙏 Acknowledgments

• MySQL documentation and community
• Flask framework developers
• Database design best practices from academic resources
• Open source community for inspiration

---

📞 Support

If you found this project helpful:

• ⭐ Star this repository
• 🐛 Report bugs via Issues
• 💡 Suggest features
• 🤝 Contribute via Pull Requests

---

Built with ❤️ using MySQL, Python, and Flask

---

📝 Resume Summary

Personal Finance & Expense Tracker | MySQL + Python + Flask

Engineered a production-grade financial management system with MySQL backend, implementing normalized database schema (3NF), automated triggers for real-time balance updates, and stored procedures for complex analytical queries. Developed RESTful API with Flask supporting multi-account management, budget tracking, and recurring payment automation. Demonstrated expertise in database design, transaction management, indexing optimization, and backend architecture, managing 8+ interconnected tables with proper referential integrity and ACID compliance.