Finance Tracker

🗄️ DATABASE: PostgreSQL
│
├─ users                 User accounts (via Keycloak SSO)
├─ transactions          Imported bank transactions
├─ categories            Expense categories (custom + defaults)
├─ categorization_rules  "If description contains 'UBER' → Transportation"
└─ ai_suggestions        OpenAI's category guesses (stored for review)

🖥️ INTERFACE: Next.js (Frontend)
│
├─ /dashboard            Spending overview (charts, totals by category)
├─ /import               CSV upload interface
├─ /transactions         List view with manual category overrides
├─ /categories           Manage custom categories
└─ /settings             OpenAI API key input, budget goals

⚡ API LOGIC: Python (FastAPI) Backend
│
├─ /api/import           Parse CSV, extract transactions
├─ /api/categorize       Send to OpenAI, get category predictions
├─ /api/transactions     CRUD for transaction management
└─ /api/reports          Generate spending reports

1. User uploads CSV (e.g., from Chase credit card)
   └─ Example row:
       Date: 2025-01-15
       Description: "UBER *TRIP 2X3Y4"
       Amount: -$18.42

2. Backend parses CSV → extract transactions

3. For each transaction, check rules first:
   └─ IF description matches rule → apply category
   └─ ELSE → send to OpenAI API

4. OpenAI API call:
   Prompt: "Categorize this transaction:
            Description: UBER *TRIP 2X3Y4
            Amount: $18.42
            
            Choose from: Food, Transportation, Entertainment,
            Housing, Utilities, Healthcare, Other"
   
   Response: "Transportation"

5. Store AI suggestion in database (not auto-applied)

6. User reviews suggestions in UI:
   └─ Accept (save category)
   └─ Override (choose different category)
   └─ Create rule ("Always categorize UBER as Transportation")

7. Once approved, charts update with new data

RULE CREATION PROMPT:
"Create a rule so future Starbucks transactions are auto-categorized?"

IF YES:
  └─ Save rule:
      IF description CONTAINS "STARBUCKS"
      THEN category = "Food"

NEXT MONTH:
  └─ New transaction: "STARBUCKS #1234 SAN FRANCISCO"
  └─ Rule matches → auto-categorize as "Food"
  └─ No AI call needed (faster + saves OpenAI API costs)

CHASE CREDIT CARD CSV:
  Columns: Transaction Date, Post Date, Description, Category, Type, Amount

BANK OF AMERICA CSV:
  Columns: Date, Description, Amount, Running Bal.

IMPORT INTERFACE:
  User uploads CSV
  └─ Backend detects headers
  └─ Shows mapping UI:
      "Which column is the transaction date?"
      → User selects "Transaction Date"
      
      "Which column is the description?"
      → User selects "Description"
      
      "Which column is the amount?"
      → User selects "Amount"
      
  └─ Save mapping preferences per bank
  └─ Next import auto-detects bank and uses saved mapping

SPENDING BY CATEGORY (THIS MONTH):
  SELECT category, SUM(amount) as total
  FROM transactions
  WHERE user_id = ? 
    AND date >= '2025-01-01'
    AND date < '2025-02-01'
  GROUP BY category
  ORDER BY total DESC;

SPENDING TREND (LAST 6 MONTHS):
  SELECT 
    DATE_TRUNC('month', date) as month,
    SUM(amount) as total
  FROM transactions
  WHERE user_id = ?
    AND date >= NOW() - INTERVAL '6 months'
  GROUP BY month
  ORDER BY month;

PRODUCTION DEPLOY:
1. Build frontend:
   cd frontend && npm run build
   → Generates static files in .next/

2. Build Docker images:
   docker build -t finance-frontend:latest ./frontend
   docker build -t finance-backend:latest ./backend

3. Stop old containers:
   docker stop finance-frontend finance-backend
   docker rm finance-frontend finance-backend

4. Start new containers:
   docker run -d --name finance-frontend \\
     --network bened-platform_bened-network \\
     finance-frontend:latest
   
   docker run -d --name finance-backend \\
     --network bened-platform_bened-network \\
     --env-file .env \\
     finance-backend:latest

5. Nginx at finance.bened.works proxies:
   └─ / → frontend container
   └─ /api → backend container

Finance Tracker → Operations Portal (Planned)
  └─ Export categorized expenses
  └─ Used for LLC/nonprofit expense reporting

Keycloak → Finance Tracker
  └─ SSO login (same user identity across BENED apps)