Personal Financial Tracking & Cash Flow Analytics System

A production-grade personal financial management system combining a normalized PostgreSQL relational database, automated transaction triggers, advanced SQL analytical queries, and an interactive Streamlit monitoring dashboard.

---

Table of Contents

1. Project Overview
2. Architecture & Pipeline
3. Database Schema & Design
4. Automated Triggers & Logic
5. Analytical SQL Queries
6. Interactive Web Dashboard
7. Tech Stack
8. Project Structure
9. Installation & Setup

---

1. Project Overview

Managing personal cash flow effectively requires real-time transaction tracking, automated account reconciliations, and structured financial metrics. This project implements an end-to-end data pipeline to:

• Normalize financial data across accounts, expense/income categories, and double-entry transaction ledgers using PostgreSQL.
• Automate real-time account balance updates directly within the database engine using PL/pgSQL triggers.
• Formulate complex SQL analytical queries utilizing window functions, Common Table Expressions (CTEs), and month-over-month lag analysis.
• Surface real-time KPIs, savings rates, and spending distributions through an interactive Streamlit and Plotly dashboard.

---

2. Architecture & Pipeline

  PostgreSQL Database (newdatabases)
  ├── accounts (Checking, Savings, Credit Card, Cash)
  ├── categories (Income, Expense)
  └── transactions (Ledger)
           │
           ▼ (PL/pgSQL Trigger: trg_update_account_balance)
     Auto-updates accounts.current_balance
           │
           ├──► Jupyter Notebook / VS Code (EDA & Static Plots via SQLAlchemy)
           │
           └──► Streamlit Web App (app.py) ──► Plotly Interactive Visuals 

3. Database Schema & Design

The schema enforces referential integrity through foreign keys and check constraints:

• accounts: Stores account records, types (Checking, Savings, Credit Card, Cash), and live balances.
• categories: Standardized transaction classifications tagged with cash flow direction (Income or Expense).
• transactions: Central transaction ledger logging amounts, dates, descriptions, and foreign key relations. CREATE TABLE accounts ( account_id SERIAL PRIMARY KEY, account_name VARCHAR(100) NOT NULL, account_type VARCHAR(20) NOT NULL CHECK (account_type IN ('Checking', 'Savings', 'Credit Card', 'Cash')), current_balance NUMERIC(12, 2) DEFAULT 0.00, created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP );

CREATE TABLE categories ( category_id SERIAL PRIMARY KEY, category_name VARCHAR(100) NOT NULL UNIQUE, category_type VARCHAR(10) NOT NULL CHECK (category_type IN ('Income', 'Expense')) );

CREATE TABLE transactions ( transaction_id SERIAL PRIMARY KEY, transaction_date DATE NOT NULL, account_id INT NOT NULL REFERENCES accounts(account_id) ON DELETE RESTRICT, category_id INT NOT NULL REFERENCES categories(category_id) ON DELETE RESTRICT, amount NUMERIC(10, 2) NOT NULL CHECK (amount > 0), description TEXT, created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP );

4. Automated Triggers & Logic

To maintain balance accuracy automatically upon transaction insertions, a PostgreSQL PL/pgSQL function and trigger execute after every insert:

CREATE OR REPLACE FUNCTION update_account_balance()
RETURNS TRIGGER AS $$
DECLARE
    v_category_type VARCHAR(10);
BEGIN
    SELECT category_type INTO v_category_type
    FROM categories
    WHERE category_id = NEW.category_id;

    IF v_category_type = 'Income' THEN
        UPDATE accounts
        SET current_balance = current_balance + NEW.amount
        WHERE account_id = NEW.account_id;
    ELSIF v_category_type = 'Expense' THEN
        UPDATE accounts
        SET current_balance = current_balance - NEW.amount
        WHERE account_id = NEW.account_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_account_balance
AFTER INSERT ON transactions
FOR EACH ROW
EXECUTE FUNCTION update_account_balance();

5. Analytical SQL Queries

A. Monthly Cash Flow & Savings Rate Aggregates inflows and outflows to compute net monthly savings and overall savings percentage:

SELECT 
    TO_CHAR(t.transaction_date, 'YYYY-MM') AS month_year,
    SUM(CASE WHEN c.category_type = 'Income' THEN t.amount ELSE 0 END) AS total_income,
    SUM(CASE WHEN c.category_type = 'Expense' THEN t.amount ELSE 0 END) AS total_expense,
    SUM(CASE WHEN c.category_type = 'Income' THEN t.amount ELSE -t.amount END) AS net_savings,
    ROUND(
        (SUM(CASE WHEN c.category_type = 'Income' THEN t.amount ELSE -t.amount END) * 100.0) /
        NULLIF(SUM(CASE WHEN c.category_type = 'Income' THEN t.amount ELSE 0 END), 0), 
        2
    ) AS savings_rate_percentage
FROM transactions t
JOIN categories c ON t.category_id = c.category_id
GROUP BY TO_CHAR(t.transaction_date, 'YYYY-MM')
ORDER BY month_year;

B. Expense Category Pareto Breakdown Calculates the relative percentage of each expense category against overall spending using window functions:

SELECT 
    c.category_name,
    SUM(t.amount) AS category_total,
    ROUND(
        SUM(t.amount) * 100.0 / SUM(SUM(t.amount)) OVER (), 
        2
    ) AS percentage_of_total_expenses
FROM transactions t
JOIN categories c ON t.category_id = c.category_id
WHERE c.category_type = 'Expense'
GROUP BY c.category_name
ORDER BY category_total DESC;

C. Month-over-Month Burn Variance (CTE + LAG()) Measures consecutive spending deltas to identify cost fluctuations across periods:

WITH monthly_metrics AS (
    SELECT 
        TO_CHAR(t.transaction_date, 'YYYY-MM') AS period,
        SUM(t.amount) AS monthly_spend
    FROM transactions t
    JOIN categories c ON t.category_id = c.category_id
    WHERE c.category_type = 'Expense'
    GROUP BY TO_CHAR(t.transaction_date, 'YYYY-MM')
)
SELECT 
    period,
    monthly_spend,
    LAG(monthly_spend, 1) OVER (ORDER BY period) AS prev_month_spend,
    monthly_spend - LAG(monthly_spend, 1) OVER (ORDER BY period) AS spend_difference
FROM monthly_metrics;

6. Interactive Web Dashboard

Built with Streamlit and Plotly to display real-time database metrics:

• Account Balance Cards: Tracks current balances across checking, savings, credit cards, and cash wallets.
• Summary Metrics: Real-time KPI scorecards for Total Income, Total Expenses, Net Savings, and Savings Rate %.
• Cash Flow Comparison: Side-by-side grouped bar charts for monthly revenue vs expenses.
• Donut Chart: Interactive category-wise spending distribution.
• Transaction Explorer: Filterable and searchable transaction table view.

7. Tech Stack

• Database Engine: PostgreSQL 18
• Database Admin & Tools: pgAdmin 4, VS Code SQL Tools (jupysql)
• Programming Language: Python 3.13+
• Data Engineering: pandas, SQLAlchemy, psycopg2-binary
• Visualization & UI: streamlit, plotly, matplotlib, seaborn

8. Project Structure

financial_tracking_tool_with_sql/
├── schema.sql                 # DDL tables, seed data, and trigger definitions
├── queries.sql                # Analytical queries (window functions, CTEs)
├── new.ipynb                  # Exploration & static visualization notebook
├── app.py                     # Interactive Streamlit web application
├── requirements.txt           # Environment package dependencies
└── README.md                  # Project documentation

9. Installation & Setup

1. Clone the repository git clone https://github.com/Pranay-1403/financial-tracking-tool.git cd financial_tracking_tool_with_sql

2. Install dependencies pip install -r requirements.txt

3. Initialize Database

• Create database newdatabases in PostgreSQL.
• Execute schema.sql to generate tables and triggers.

4. Run the Dashboard streamlit run app.py