Expense Tracker - PERN Stack Application

A full-stack expense tracking web application built with PostgreSQL, Express, React, and Node.js (PERN Stack).

📋 Table of Contents

• Features
• Technology Stack
• Database Schema
• Project Structure
• Getting Started
• API Documentation
• Environment Variables
• Development
• Deployment

---

✨ Features

Core Functionality

• User Authentication: Secure JWT-based authentication with bcrypt password hashing
• Account Management: Multiple accounts (cash, bank, credit card) with balance tracking
• Transaction Tracking: Record income and expenses with categories, descriptions, and dates
• Category Management: Custom income and expense categories per user
• Budget Management: Set budget limits per category with date ranges
• Budget Alerts: Automatic alerts when approaching or exceeding budget limits
• Recurring Transactions: Automate regular income/expenses with configurable intervals (daily, weekly, monthly, yearly)
• Transaction Tags: Tag transactions for better organization and filtering
• Attachments: Upload and store receipts or related documents
• Dashboard: Overview of account balances, recent transactions, and budget status
• Reporting: Filter and analyze transactions by date range, category, account, and tags

---

🛠 Technology Stack

Backend

• Runtime: Node.js
• Framework: Express.js 5.x
• Database: PostgreSQL
• Authentication: JWT (jsonwebtoken) + bcryptjs
• Security: Helmet, CORS, express-validator
• Logging: Morgan
• Environment: dotenv
• Cookie Parsing: cookie-parser

Frontend

• Framework: React 19.x
• Build Tool: Vite 5.x
• Routing: React Router DOM 7.x
• HTTP Client: Axios 1.x
• Styling: CSS (customizable to Tailwind or Material-UI)
• Testing: Vitest

Development Tools

• Server: Nodemon for hot reloading
• Testing: Vitest (frontend), Jest-compatible
• Package Manager: npm

---

🗄 Database Schema

The application uses PostgreSQL with the following main tables:

Users

Stores user account information with hashed passwords.

• Fields: id, full_name, email, password_hash, created_at, updated_at

Accounts

User's financial accounts (bank, cash, credit card) with balance tracking.

• Fields: id, user_id, name, type, balance, created_at, updated_at

Categories

Income and expense categories customizable per user.

• Fields: id, user_id, name, type (income/expense), created_at
• Unique constraint on user_id + name

Transactions

All financial transactions linked to accounts and categories.

• Fields: id, user_id, account_id, category_id, amount, type (income/expense), description, transaction_date, created_at
• Indexed on user_id, account_id, category_id

Recurring Transactions

Scheduled recurring income/expenses with interval settings.

• Fields: id, user_id, account_id, category_id, amount, type, interval (daily/weekly/monthly/yearly), next_run_date, description, created_at

Budgets

Budget limits per category with start/end dates.

• Fields: id, user_id, category_id, amount_limit, start_date, end_date, created_at

Budget Alerts

Alerts triggered when budgets reach certain thresholds.

• Fields: id, budget_id, alert_type (50%, 75%, 100%), message, created_at

Tags

Custom tags for organizing transactions.

• Fields: id, user_id, name, created_at
• Unique constraint on user_id + name

Transaction Tags

Many-to-many relationship between transactions and tags.

• Fields: transaction_id, tag_id (composite primary key)

Attachments

File uploads (receipts, documents) linked to transactions.

• Fields: id, transaction_id, file_url, created_at
• Indexed on transaction_id

Full schema available in: expense.postgre.sql

---

📁 Project Structure

Expense/
├── client/                 # React frontend (Vite)
│   ├── public/            # Static assets
│   ├── src/
│   │   ├── assets/        # Images, fonts, etc.
│   │   ├── components/    # Reusable components
│   │   ├── context/       # React context providers
│   │   ├── hooks/         # Custom React hooks
│   │   ├── pages/         # Page components
│   │   ├── routes/        # Route definitions
│   │   ├── services/      # API service layer
│   │   ├── utils/         # Utility functions
│   │   ├── App.js         # Main app component
│   │   ├── App.css        # App styles
│   │   ├── index.css      # Global styles
│   │   └── main.jsx       # Entry point
│   ├── index.html         # HTML template
│   ├── .env.example       # Environment template
│   ├── .gitignore
│   ├── package.json
│   └── vite.config.js     # Vite configuration
│
├── server/                # Node.js backend (Express)
│   ├── src/
│   │   ├── config/        # Configuration files
│   │   │   └── database.js     # PostgreSQL connection pool
│   │   ├── controllers/   # Route controllers
│   │   ├── middleware/    # Custom middleware
│   │   │   ├── auth.js          # JWT authentication
│   │   │   └── errorHandler.js  # Error handling
│   │   ├── models/        # Database models
│   │   ├── routes/        # API routes
│   │   ├── services/      # Business logic
│   │   ├── utils/         # Utility functions
│   │   ├── validations/   # Input validation schemas
│   │   └── app.js         # Express app setup
│   ├── .env.example       # Environment template
│   ├── .gitignore
│   ├── index.js           # Server entry point
│   └── package.json
│
├── expense.postgre.sql    # PostgreSQL schema
├── .gitignore
└── README.md              # This file

---

🚀 Getting Started

Prerequisites

• Node.js (v18 or higher)
• PostgreSQL (v12 or higher)
• npm (comes with Node.js)

Installation

1. Clone the repository

   git clone <repository-url>
   cd Expense

2. Setup PostgreSQL Database

   # Create database
   createdb expense_tracker
   
   # Run schema
   psql -d expense_tracker -f expense.postgre.sql

3. Setup Server

   cd server
   
   # Copy environment template
   cp .env.example .env
   
   # Edit .env with your database credentials
   # Then install dependencies
   npm install

4. Setup Client

   cd ../client
   
   # Copy environment template
   cp .env.example .env
   
   # Install dependencies
   npm install

Running the Application

1. Start the backend server (from server/ directory)

   npm run dev

   Server runs on http://localhost:5000

2. Start the frontend (from client/ directory, in a new terminal)

   npm run dev

   Client runs on http://localhost:3000

3. Access the application

   • Frontend: http://localhost:3000
   • Backend API: http://localhost:5000
   • Health Check: http://localhost:5000/api/health

---

📡 API Documentation

Base URL

http://localhost:5000/api

Authentication

Most endpoints require JWT authentication. Include the token in the Authorization header:

Authorization: Bearer <your_jwt_token>

Endpoints (Planned Structure)

Authentication

• POST /api/auth/register - Register new user

  {
    "full_name": "John Doe",
    "email": "john@example.com",
    "password": "secure123"
  }

• POST /api/auth/login - Login user

  { "email": "john@example.com", "password": "secure123" }

• POST /api/auth/logout - Logout user
• POST /api/auth/refresh - Refresh JWT token

Users

• GET /api/users/me - Get current user profile
• PUT /api/users/me - Update user profile

Accounts

• GET /api/accounts - Get all user accounts
• POST /api/accounts - Create new account

  { "name": "Main Bank", "type": "bank", "balance": 5000.0 }

• GET /api/accounts/:id - Get account by ID
• PUT /api/accounts/:id - Update account
• DELETE /api/accounts/:id - Delete account

Categories

• GET /api/categories - Get all categories
• POST /api/categories - Create category

  { "name": "Groceries", "type": "expense" }

• PUT /api/categories/:id - Update category
• DELETE /api/categories/:id - Delete category

Transactions

• GET /api/transactions - Get transactions (with filters)
  • Query params: ?from=2024-01-01&to=2024-12-31&category_id=1&account_id=1&type=expense
• POST /api/transactions - Create transaction

  {
    "account_id": 1,
    "category_id": 2,
    "amount": 150.5,
    "type": "expense",
    "description": "Weekly groceries",
    "transaction_date": "2024-11-29"
  }

• GET /api/transactions/:id - Get transaction by ID
• PUT /api/transactions/:id - Update transaction
• DELETE /api/transactions/:id - Delete transaction

Budgets

• GET /api/budgets - Get all budgets
• POST /api/budgets - Create budget

  {
    "category_id": 2,
    "amount_limit": 500.0,
    "start_date": "2024-11-01",
    "end_date": "2024-11-30"
  }

• PUT /api/budgets/:id - Update budget
• DELETE /api/budgets/:id - Delete budget
• GET /api/budgets/:id/status - Check budget status

Recurring Transactions

• GET /api/recurring - Get recurring transactions
• POST /api/recurring - Create recurring transaction

  {
    "account_id": 1,
    "category_id": 3,
    "amount": 1200.0,
    "type": "income",
    "interval": "monthly",
    "next_run_date": "2024-12-01",
    "description": "Monthly salary"
  }

• PUT /api/recurring/:id - Update recurring transaction
• DELETE /api/recurring/:id - Delete recurring transaction

Tags

• GET /api/tags - Get all tags
• POST /api/tags - Create tag
• DELETE /api/tags/:id - Delete tag

Reports

• GET /api/reports/summary - Get financial summary
  • Query params: ?start_date=2024-11-01&end_date=2024-11-30
• GET /api/reports/by-category - Category breakdown
• GET /api/reports/by-account - Account-wise report
• GET /api/reports/trends - Spending trends over time

Attachments

• POST /api/attachments - Upload attachment (receipt/document)
• DELETE /api/attachments/:id - Delete attachment

---

🔐 Environment Variables

Server (.env)

# Server Configuration
PORT=5000
NODE_ENV=development

# Database Configuration
DB_HOST=localhost
DB_PORT=5432
DB_NAME=expense_tracker
DB_USER=postgres
DB_PASSWORD=your_password_here

# JWT Configuration
JWT_SECRET=your_jwt_secret_key_here
JWT_EXPIRE=7d

# CORS Configuration
CORS_ORIGIN=http://localhost:3000

Client (.env)

# API Configuration
VITE_API_URL=http://localhost:5000/api

# App Configuration
VITE_APP_NAME=Expense Tracker

---

💻 Development

Available Scripts

Server

npm start       # Start production server
npm run dev     # Start development server with nodemon
npm test        # Run tests

Client

npm run dev     # Start development server (Vite)
npm run build   # Build for production
npm run preview # Preview production build
npm test        # Run tests (Vitest)

Code Structure Guidelines

• Controllers: Handle HTTP requests/responses, call services
• Services: Contain business logic, interact with models
• Models: Database queries and data access layer
• Middleware: Request processing (auth, validation, error handling)
• Validations: Input validation schemas using express-validator
• Routes: Define API endpoints and connect to controllers

Adding New Features

1. Database: Add tables/columns in expense.postgre.sql
2. Backend:
   • Create model in server/src/models/
   • Create service in server/src/services/
   • Create controller in server/src/controllers/
   • Add routes in server/src/routes/
   • Add validation in server/src/validations/
3. Frontend:
   • Create service in client/src/services/
   • Create components in client/src/components/
   • Create pages in client/src/pages/
   • Add routes in client/src/routes/

Security Best Practices

• ✅ Passwords hashed with bcryptjs (salt rounds: 10)
• ✅ JWT tokens for stateless authentication
• ✅ Helmet for security headers
• ✅ CORS configured with specific origin
• ✅ Input validation with express-validator
• ✅ SQL injection prevention (parameterized queries)
• ✅ Environment variables for secrets
• ✅ Error handling middleware (no sensitive data exposed)
• ✅ Cookie parsing with secure options
• ✅ Morgan for request logging

---

🚢 Deployment

Production Checklist

• Set NODE_ENV=production
• Use strong, unique JWT_SECRET (minimum 32 characters)
• Configure PostgreSQL with strong credentials
• Enable HTTPS/SSL certificates
• Set up automated database backups
• Configure CORS with production domain
• Use process manager (PM2, Docker)
• Set up logging and monitoring (e.g., Winston, Sentry)
• Optimize database with proper indexes
• Build frontend for production (npm run build)
• Enable rate limiting for API endpoints
• Set up CDN for static assets
• Configure reverse proxy (Nginx)

Deployment Options

Backend Options

• Railway (recommended for PostgreSQL + Node.js)
• Render
• Heroku
• DigitalOcean App Platform
• AWS EC2 + RDS

Frontend Options

• Vercel (recommended for Vite/React)
• Netlify
• AWS S3 + CloudFront
• Cloudflare Pages

Database Options

• Railway PostgreSQL
• Supabase
• AWS RDS PostgreSQL
• DigitalOcean Managed Databases
• ElephantSQL

Example Production Build

# Build client
cd client
npm run build

# The build folder can be served by backend or deployed separately
# For separate deployment, upload 'build' folder to your hosting service

# Run server in production
cd ../server
NODE_ENV=production npm start

---

🧪 Testing

Backend Tests

cd server
npm test

Frontend Tests

cd client
npm test

Test Coverage

• Unit tests for services and utilities
• Integration tests for API endpoints
• Component tests for React components
• E2E tests for critical user flows

---

📝 License

This project is licensed under the ISC License.

---

🤝 Contributing

Contributions are welcome! Please follow these steps:

1. Fork the repository
2. Create a feature branch (git checkout -b feature/AmazingFeature)
3. Commit your changes (git commit -m 'Add some AmazingFeature')
4. Push to the branch (git push origin feature/AmazingFeature)
5. Open a Pull Request

---

🐛 Troubleshooting

Common Issues

Database connection failed

• Verify PostgreSQL is running
• Check credentials in .env
• Ensure database exists (createdb expense_tracker)

Port already in use

• Change PORT in server .env
• Kill process using the port

Frontend can't connect to API

• Verify backend is running
• Check VITE_API_URL in client .env
• Ensure CORS is properly configured

Vite build errors

• Delete node_modules and reinstall
• Clear Vite cache: rm -rf node_modules/.vite

---

📧 Support

For issues and questions:

• Open an issue in the repository
• Check existing issues for solutions
• Review the documentation

---

🗺️ Roadmap

Future enhancements planned:

• Mobile responsive design
• Dark mode theme
• Export data to CSV/PDF
• Multi-currency support
• Shared household accounts
• Bank account integration (Plaid)
• Budget recommendations (ML)
• Mobile app (React Native)
• Email notifications
• Data visualization charts
• Scheduled reports
• Two-factor authentication

---

Happy Tracking! 💰📊