Skill: personal-finance

# Skill: personal-finance

## Overview

Full-stack personal finance tracker with CSV import, budget management, category keyword rules, recurring transaction detection, and Chart.js visualizations. Node.js/Express backend, React 18/Vite/TypeScript frontend, SQLite persistence.

## Architecture

```
apps/
  api/     Node.js + Express REST API, SQLite via better-sqlite3
  web/     React 18 + Vite + TypeScript SPA
```

## Database Schema (SQLite with WAL mode)

```sql
-- accounts: checking, savings, credit cards
CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  type TEXT NOT NULL CHECK(type IN ('checking','savings','credit','cash')),
  last_four TEXT,
  currency TEXT NOT NULL DEFAULT 'USD',
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- categories: system defaults + user-defined
CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  color TEXT NOT NULL DEFAULT '#6b7280',
  icon TEXT,
  is_income INTEGER NOT NULL DEFAULT 0,
  is_system INTEGER NOT NULL DEFAULT 0
);

-- category_rules: keyword-based auto-classification
CREATE TABLE category_rules (
  id INTEGER PRIMARY KEY,
  category_id INTEGER NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
  keyword TEXT NOT NULL,
  priority INTEGER NOT NULL DEFAULT 0
);

-- transactions: core ledger
CREATE TABLE transactions (
  id INTEGER PRIMARY KEY,
  account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  date TEXT NOT NULL,          -- ISO 8601 YYYY-MM-DD
  description TEXT NOT NULL,
  amount REAL NOT NULL,        -- negative = expense, positive = income
  category_id INTEGER REFERENCES categories(id),
  notes TEXT,
  is_recurring INTEGER NOT NULL DEFAULT 0,
  recurring_group_id TEXT,     -- UUID grouping detected recurring transactions
  import_hash TEXT UNIQUE,     -- SHA256 of date+description+amount for dedup
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- budgets: monthly spending limits per category
CREATE TABLE budgets (
  id INTEGER PRIMARY KEY,
  category_id INTEGER NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
  month TEXT NOT NULL,         -- YYYY-MM
  limit_amount REAL NOT NULL,
  UNIQUE(category_id, month)
);

-- settings: key/value store
CREATE TABLE settings (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL
);
```

## Key Algorithms

### CSV Import with Bank Format Detection

```typescript
type BankFormat = 'chase' | 'bofa' | 'wellsfargo' | 'generic';

function detectBankFormat(headers: string[]): BankFormat {
  const h = headers.map(s => s.toLowerCase().trim());
  if (h.includes('transaction date') && h.includes('post date')) return 'chase';
  if (h.includes('date') && h.includes('payee') && h.includes('account number')) return 'bofa';
  if (h.includes('date') && h.includes('amount') && h.includes('transaction type')) return 'wellsfargo';
  return 'generic';
}

function normalizeRow(row: Record<string, string>, format: BankFormat): NormalizedTransaction {
  switch (format) {
    case 'chase':
      return {
        date: parseDate(row['Transaction Date']),
        description: row['Description'],
        amount: parseFloat(row['Amount']),
        category: row['Category'] ?? null,
      };
    case 'bofa':
      return {
        date: parseDate(row['Date']),
        description: row['Payee'] ?? row['Description'],
        amount: -Math.abs(parseFloat(row['Amount'])),  // BofA uses positive for debits
        category: null,
      };
    // ... wellsfargo, generic
  }
}
```

### Auto-Categorization

```typescript
function autoCategorize(description: string, rules: CategoryRule[]): number | null {
  // Rules sorted by priority DESC, then category_id ASC
  const descLower = description.toLowerCase();
  for (const rule of rules) {
    if (descLower.includes(rule.keyword.toLowerCase())) {
      return rule.category_id;
    }
  }
  return null;
}
```

### Duplicate Detection (import dedup hash)

```typescript
import { createHash } from 'crypto';

function importHash(date: string, description: string, amount: number): string {
  return createHash('sha256')
    .update(`${date}|${description}|${amount.toFixed(2)}`)
    .digest('hex');
}
```

### Recurring Transaction Detection

```typescript
function detectRecurring(transactions: Transaction[]): Transaction[][] {
  // Group by normalized description (uppercase, strip digits)
  const groups = new Map<string, Transaction[]>();
  for (const tx of transactions) {
    const key = tx.description.toUpperCase().replace(/\d+/g, '').trim();
    if (!groups.has(key)) groups.set(key, []);
    groups.get(key)!.push(tx);
  }
  // Return groups with 3+ transactions at 28-32 day intervals
  const recurring: Transaction[][] = [];
  for (const [, group] of groups) {
    if (group.length < 3) continue;
    const sorted = group.sort((a, b) => a.date.localeCompare(b.date));
    const intervals = sorted.slice(1).map((tx, i) => {
      const days = (new Date(tx.date).getTime() - new Date(sorted[i].date).getTime()) / 86400000;
      return days;
    });
    const isMonthly = intervals.every(d => d >= 25 && d <= 35);
    if (isMonthly) recurring.push(group);
  }
  return recurring;
}
```

### Budget vs Actual SQL Query

```sql
SELECT
  c.id,
  c.name,
  b.limit_amount,
  COALESCE(SUM(ABS(t.amount)), 0) AS spent,
  b.limit_amount - COALESCE(SUM(ABS(t.amount)), 0) AS remaining,
  ROUND(COALESCE(SUM(ABS(t.amount)), 0) * 100.0 / b.limit_amount, 1) AS pct_used
FROM budgets b
JOIN categories c ON c.id = b.category_id
LEFT JOIN transactions t
  ON t.category_id = c.id
  AND strftime('%Y-%m', t.date) = b.month
  AND t.amount < 0
WHERE b.month = ?
GROUP BY c.id, b.limit_amount
ORDER BY pct_used DESC;
```

## API Endpoints

| Method | Path | Description |
|--------|------|-------------|
| POST | /api/auth/login | Session login |
| POST | /api/auth/logout | Destroy session |
| GET | /api/accounts | List accounts |
| POST | /api/accounts | Create account |
| PUT | /api/accounts/:id | Update account |
| DELETE | /api/accounts/:id | Delete account |
| GET | /api/transactions | List with filters (account, category, month, search, page) |
| POST | /api/transactions | Create single transaction |
| PUT | /api/transactions/:id | Update transaction |
| DELETE | /api/transactions/:id | Delete transaction |
| POST | /api/transactions/import | Import parsed CSV rows |
| GET | /api/budgets?month=YYYY-MM | Budget vs actual for month |
| POST | /api/budgets | Set budget for category/month |
| DELETE | /api/budgets/:id | Remove budget |
| GET | /api/categories | List categories with rules |
| POST | /api/categories | Create custom category |
| PUT | /api/categories/:id | Update category/rules |
| DELETE | /api/categories/:id | Delete custom category |
| GET | /api/charts/spending | Monthly spending over time |
| GET | /api/charts/by-category?month= | Spending by category |
| GET | /api/recurring | List detected recurring transactions |
| GET | /api/export?format=csv&from=&to= | Export transactions |

## Frontend Key Components

- `TransactionTable` - virtualized list with date grouping, inline category edit
- `BudgetCard` - progress bar with over/under state and color transitions
- `CSVImporter` - drag-drop, format detection, preview table, duplicate skip option
- `CategoryRuleEditor` - keyword chip editor with priority drag reorder
- `ChartSpending` - Chart.js bar chart with income vs expense bars
- `RecurringList` - upcoming timeline sidebar with 14-day forecast
- `MobileNav` - bottom tab bar for screens under 768px

## Environment Variables

```
PORT=3000
SESSION_SECRET=<random 32-byte hex>
DB_PATH=./data/finance.db
BCRYPT_ROUNDS=12
CORS_ORIGIN=http://localhost:5173
NODE_ENV=development
```

## pnpm Commands

```bash
pnpm install          # install all workspace deps
pnpm dev              # start api (port 3000) + web (port 5173) concurrently
pnpm build            # tsc + vite build
pnpm test             # vitest + supertest
pnpm db:migrate       # run DB migrations
pnpm db:seed          # seed sample data (dev only)
```