finance-app/CLAUDE.md

# Finance App — Claude Context

Personal finance web app for tracking bank transactions, monitoring net worth, and visualizing spending. Single-user, self-hosted via Docker Compose.

## Tech Stack

| Layer | Choice |
|---|---|
| Framework | Next.js 16+ App Router + TypeScript |
| Database | PostgreSQL + Prisma ORM |
| Auth | NextAuth.js v5 (Credentials provider, bcrypt, JWT sessions) |
| UI | Tailwind CSS + shadcn/ui |
| Charts | Recharts |
| CSV Parsing | Papa Parse (server-side only — never client-side) |
| Validation | Zod on every API route before any DB access |
| Deployment | Docker Compose (postgres:16-alpine + Next.js app) |

## Build Status

**Phases 1–6 complete.**

## Implementation Phases

| # | Phase | Status |
|---|---|---|
| 1 | Scaffold: create-next-app, shadcn, Prisma schema, Docker, .env | Done |
| 2 | Auth: NextAuth, middleware, login page, seed script | Done |
| 3 | Accounts CRUD: API routes + AccountList UI | Done |
| 4 | CSV Upload: bank profiles, parser, normalizer, upload API + UI | Done |
| 5 | Transaction browsing: paginated API, filters, edit dialog | Done |
| 6 | Budgets: CRUD API, UI, assign transactions to budgets | Done |
| 7 | Dashboard: aggregate API, NetWorthCard, CashFlowCard, BudgetSummary | Not started |
| 8 | Graphs: Recharts components, BalanceSnapshot read path | Not started |
| 9 | Security hardening: HTTP headers, rate limiting, Origin check | Not started |
| 10 | Docker polish: multi-stage Dockerfile, health checks, README | Not started |

---

## Prisma 7 Notes

- Generator provider is `prisma-client` (not `prisma-client-js`)
- Client output: `src/generated/prisma/` — import as `@/generated/prisma/client`
- **Requires Driver Adapter** — no URL in constructor. Use `@prisma/adapter-pg` with `pg`:
  ```ts
  import { Pool } from 'pg'
  import { PrismaPg } from '@prisma/adapter-pg'
  const pool = new Pool({ connectionString: process.env.DATABASE_URL })
  const adapter = new PrismaPg(pool)
  new PrismaClient({ adapter })
  ```
- Datasource URL configured in `prisma.config.ts` (for CLI) and via `Pool` at runtime
- Singleton: `src/lib/prisma.ts`
- Seed: `npx prisma db seed` (runs `tsx prisma/seed.ts`)

---

## Database Schema

Store all money as **integer cents** (never floats). `100.10` → `10010`.

```prisma
// prisma/schema.prisma

generator client {
  provider = "prisma-client"
  output   = "../src/generated/prisma"
}

datasource db {
  provider = "postgresql"
}

model User {
  id           String    @id @default(cuid())
  email        String    @unique
  passwordHash String    // bcrypt hash, cost factor 12
  createdAt    DateTime  @default(now())
  updatedAt    DateTime  @updatedAt
  accounts     Account[]
  budgets      Budget[]
}

enum AccountType {
  BANK        // Checking, savings, investment — counts toward net worth
  CREDIT_CARD // Tracking only — never counted in net worth or cash flow
}

model Account {
  id                  String      @id @default(cuid())
  userId              String
  user                User        @relation(fields: [userId], references: [id], onDelete: Cascade)
  name                String
  institution         String?
  type                AccountType
  currency            String      @default("USD")
  currentBalanceCents Int         @default(0)
  isActive            Boolean     @default(true)
  createdAt           DateTime    @default(now())
  updatedAt           DateTime    @updatedAt
  transactions        Transaction[]
  uploads             CsvUpload[]
  @@index([userId])
}

enum TransactionType {
  DEBIT   // Money out (spending, withdrawals, purchases)
  CREDIT  // Money in (deposits, payments received, refunds)
}

model Transaction {
  id           String          @id @default(cuid())
  accountId    String
  account      Account         @relation(fields: [accountId], references: [id], onDelete: Cascade)
  uploadId     String?
  upload       CsvUpload?      @relation(fields: [uploadId], references: [id])
  budgetId     String?
  budget       Budget?         @relation(fields: [budgetId], references: [id], onDelete: SetNull)
  date         DateTime
  description  String
  amountCents  Int             // Always positive; direction from `type` field
  type         TransactionType
  category     String?
  notes        String?
  dedupeHash   String          // SHA-256(accountId|date|description|amountCents) — prevents re-upload duplicates
  createdAt    DateTime        @default(now())
  updatedAt    DateTime        @updatedAt
  @@unique([dedupeHash])
  @@index([accountId, date])
  @@index([date])
  @@index([budgetId])
}

// User-defined spending groups, primarily for CC transactions
model Budget {
  id           String        @id @default(cuid())
  userId       String
  user         User          @relation(fields: [userId], references: [id], onDelete: Cascade)
  name         String        // e.g. "Groceries", "Entertainment"
  limitCents   Int?          // Optional monthly cap
  color        String?       // Hex color for UI, e.g. "#6366f1"
  isActive     Boolean       @default(true)
  createdAt    DateTime      @default(now())
  updatedAt    DateTime      @updatedAt
  transactions Transaction[]
  @@index([userId])
}

model CsvUpload {
  id             String        @id @default(cuid())
  accountId      String
  account        Account       @relation(fields: [accountId], references: [id])
  fileName       String
  rowCount       Int
  importedCount  Int
  skippedCount   Int
  status         String        // PENDING | SUCCESS | PARTIAL | FAILED
  errorMessage   String?
  uploadedAt     DateTime      @default(now())
  transactions   Transaction[]
  @@index([accountId])
}

// Monthly balance snapshots for net worth trend graphs.
// No FK to Account intentionally — snapshots survive account deletion.
model BalanceSnapshot {
  id           String   @id @default(cuid())
  accountId    String
  year         Int
  month        Int      // 1–12
  balanceCents Int
  computedAt   DateTime @default(now())
  @@unique([accountId, year, month])
  @@index([year, month])
}
```

### Key schema decisions
- `currentBalanceCents` is always **recomputed by summing all transactions** after each upload — never incremented by delta — to handle out-of-order historical imports
- `dedupeHash` unique constraint lets `createMany({ skipDuplicates: true })` silently handle re-uploads
- `budgetId` uses `onDelete: SetNull` — deleting a budget unlinks its transactions, never deletes them
- `BalanceSnapshot` has no FK so historical net worth graphs survive account deletion

---

## CSV Bank Profiles

All 4 user banks with exact column mappings. Implement in `src/lib/csv/bank-profiles.ts`.

### Discover High Yield Savings → `BANK`
- **Strategy**: B (split debit/credit columns)
- `date` ← `Transaction Date`
- `description` ← `Transaction Description`
- `debitAmount` ← `Debit`
- `creditAmount` ← `Credit`
- `balance` ← `Balance` (reference only)
- Ignore: `Transaction Type`

### Discover Credit Card → `CREDIT_CARD`
- **Strategy**: A (single signed column)
- `date` ← `Trans. Date`
- `description` ← `Description`
- `amount` ← `Amount` — **positive = DEBIT (charge), negative = CREDIT (payment/refund)**
- Ignore: `Post Date`, `Category`

### Huntington Checking → `BANK`
- **Strategy**: A (single signed column)
- `date` ← `Date`
- `description` ← `Description`
- `amount` ← `Amount` — **negative = DEBIT, positive = CREDIT**
- Ignore: `Category`, `Split`, `Tags`

### Fidelity → `BANK` (investment — cash activity only)
- **Strategy**: A (single signed column)
- `date` ← `Run Date`
- `description` ← `Description`
- `amount` ← `Amount($)` — **negative = DEBIT (purchase), positive = CREDIT (dividend/sale)**
- `balance` ← `Cash Balance ($)` (reference only)
- Ignore: `Action`, `Symbol`, `Type`, `Price($)`, `Quantity`, `Commission ($)`, `Fees ($)`, `Accrued Interest ($)`, `Settlement Date`
- **Known limitation**: tracks cash activity only, not total portfolio value (securities not included)

### Amount parsing strategies
```typescript
// Strategy A — single signed column
// Sign convention varies per bank; each profile has invertAmountSign: boolean
function parseStrategyA(raw: string, invert: boolean): { amountCents: number; type: TransactionType }

// Strategy B — separate debit/credit columns (Discover Savings)
// The non-empty/non-zero column determines type and amount
function parseStrategyB(debitRaw: string, creditRaw: string): { amountCents: number; type: TransactionType }

// All amounts via:
function parseCents(raw: string): number {
  return Math.round(parseFloat(raw.replace(/[$,\s]/g, '')) * 100)
}
```

### Upload flow
1. Server parses header row → attempts profile auto-detection by matching column names
2. **Match found**: auto-proceeds; client shows "Detected: [Bank Name]"
3. **No match**: returns `{ requiresMapping: true, headers, sampleRows }` → client renders `ColumnMapper` for manual column assignment
4. After mapping resolved: normalize all rows → compute `dedupeHash` → `createMany({ skipDuplicates: true })` → recompute `currentBalanceCents` (full sum) → write/update `BalanceSnapshot` for each affected month

---

## Key Features

### Account types
- **BANK** (Discover Savings, Huntington Checking, Fidelity): counted in net worth and cash flow
- **CREDIT_CARD** (Discover CC): tracked only — never in net worth or cash flow calculations

### Budgets (for CC transactions primarily)
- User creates named budgets with optional monthly spending caps and a hex color
- Any transaction can be assigned to a budget via `budgetId`
- `BudgetCard` shows: name, current-month DEBIT total, limit, progress bar (green <75% / yellow 75-100% / red >100%)
- Dashboard has a `BudgetSummary` section with all active budgets
- Deleting a budget sets `budgetId = null` on all its transactions (transactions not deleted)

### Net worth calculation
```sql
-- Always filter type = 'BANK'; credit card accounts never appear here
SELECT SUM(currentBalanceCents) FROM Account WHERE userId = ? AND type = 'BANK' AND isActive = true
```

---

## Security Requirements

- **All API routes**: validate with Zod before any DB access; scope every Prisma query by `userId`
- **Auth**: bcrypt cost 12; JWT sessions 1-hour expiry; `middleware.ts` protects all `/(app)` routes and `/api/*` except `/api/auth`
- **CSV upload**: max 10 MB; check MIME type + file extension server-side; never write file to disk; parse in memory then discard
- **Raw SQL** (aggregation queries only): use `Prisma.sql` tagged templates — never string concatenation
- **HTTP security headers** in `next.config.ts`: `Content-Security-Policy`, `X-Frame-Options: DENY`, `X-Content-Type-Options: nosniff`, `Referrer-Policy: strict-origin-when-cross-origin`
- **Docker**: Next.js runs as non-root user (`USER node`); PostgreSQL binds to `127.0.0.1` only

---

## .env Variables Required

```bash
# Database
DATABASE_URL="postgresql://financeapp:password@localhost:5432/financeapp"

# NextAuth
NEXTAUTH_SECRET="<generate with: openssl rand -base64 32>"
NEXTAUTH_URL="http://localhost:3000"

# Seed user (used by prisma/seed.ts only)
SEED_EMAIL="your@email.com"
SEED_PASSWORD="your-secure-password"

# Postgres (used by docker-compose.yml)
POSTGRES_USER="financeapp"
POSTGRES_PASSWORD="password"
POSTGRES_DB="financeapp"
```

---

## Project File Structure (target)

```
finance-app/
├── .env                          # Never committed
├── .env.example
├── .gitignore
├── docker-compose.yml
├── Dockerfile
├── next.config.ts
├── tailwind.config.ts
├── components.json               # shadcn/ui config
├── prisma/
│   ├── schema.prisma
│   ├── seed.ts
│   └── migrations/
└── src/
    ├── middleware.ts
    ├── app/
    │   ├── (auth)/login/page.tsx
    │   ├── (app)/
    │   │   ├── layout.tsx
    │   │   ├── dashboard/page.tsx
    │   │   ├── accounts/page.tsx
    │   │   ├── accounts/[id]/page.tsx
    │   │   ├── transactions/page.tsx
    │   │   ├── upload/page.tsx
    │   │   ├── budgets/page.tsx
    │   │   └── graphs/page.tsx
    │   └── api/
    │       ├── auth/[...nextauth]/route.ts
    │       ├── accounts/route.ts + [id]/route.ts
    │       ├── transactions/route.ts + [id]/route.ts
    │       ├── upload/route.ts
    │       ├── budgets/route.ts + [id]/route.ts
    │       └── dashboard/route.ts
    ├── components/
    │   ├── ui/                   # shadcn/ui generated
    │   ├── layout/               # Sidebar, TopNav, PageHeader
    │   ├── accounts/             # AccountList, AccountCard, CreateAccountDialog, AccountBadge
    │   ├── transactions/         # TransactionTable, TransactionFilters, EditTransactionDialog
    │   ├── upload/               # UploadDropzone, UploadForm, ColumnMapper, UploadPreview
    │   ├── budgets/              # BudgetList, BudgetCard, CreateBudgetDialog, BudgetProgress
    │   ├── dashboard/            # NetWorthCard, CashFlowCard, MonthlyBalanceCard, BudgetSummary
    │   └── graphs/               # MonthlySpendingChart, NetWorthTrendChart, CategoryBreakdownChart, CashFlowChart, BudgetChart
    └── lib/
        ├── auth.ts               # NextAuth config (imported by route + middleware)
        ├── prisma.ts             # Singleton PrismaClient
        ├── csv/
        │   ├── bank-profiles.ts  # All 4 bank profiles
        │   ├── parser.ts         # Profile detection + Papa Parse wrapper
        │   └── normalizer.ts     # Row → Transaction, cents parsing, dedupeHash
        ├── validations/          # account.ts, transaction.ts, upload.ts, budget.ts (Zod schemas)
        └── utils/
            ├── currency.ts       # cents → display string formatter
            ├── dates.ts          # month boundary helpers
            └── cn.ts             # shadcn class merge utility
```