expense-report-generator

# expense-report-generator skill

## What this app does

expense-report-generator is a local web app (Express + SQLite) for tracking work expenses and generating PDF expense reports. It supports manual entry, receipt OCR (Tesseract.js), CSV bulk import, category management, and filtered PDF output (pdf-lib).

## Running the app

```bash
# Install and start
cd expense-report-generator
pnpm install
pnpm run dev           # development: API on :3000, Vite on :5173
pnpm run build && pnpm start   # production: serves on :3000
```

Default URL: http://localhost:5173 (dev) or http://localhost:3000 (prod).

Environment variables (`.env`):
```
PORT=3000
DATABASE_PATH=./data/expenses.db
UPLOAD_DIR=./tmp/receipts
MAX_FILE_BYTES=10485760
OCR_CONFIDENCE_THRESHOLD=75
```

## REST API reference

### Expenses

| Method | Path | Description |
|--------|------|-------------|
| GET | /api/expenses | List all expenses (supports ?category_id, ?from, ?to, ?currency, ?q) |
| POST | /api/expenses | Create expense |
| GET | /api/expenses/:id | Get single expense |
| PUT | /api/expenses/:id | Update expense |
| DELETE | /api/expenses/:id | Delete expense |
| GET | /api/expenses/export/csv | Export all as CSV |

### Receipt OCR

| Method | Path | Description |
|--------|------|-------------|
| POST | /api/receipts/ocr | Upload receipt image, returns extracted fields |

### Categories

| Method | Path | Description |
|--------|------|-------------|
| GET | /api/categories | List all categories |
| POST | /api/categories | Create category |
| PUT | /api/categories/:id | Update category |
| DELETE | /api/categories/:id | Delete category (affected expenses become Uncategorized) |

### Reports

| Method | Path | Description |
|--------|------|-------------|
| GET | /api/reports | List all generated reports |
| POST | /api/reports | Generate a new PDF report |
| GET | /api/reports/:id/pdf | Download PDF file |
| DELETE | /api/reports/:id | Delete report |

## Common curl examples

Add an expense:
```bash
curl -s -X POST http://localhost:3000/api/expenses \
  -H "Content-Type: application/json" \
  -d '{
    "date": "2026-03-20",
    "vendor": "United Airlines",
    "amount": 420.00,
    "currency": "USD",
    "category_id": 1,
    "description": "NYC to SFO - Q1 conference"
  }' | jq .
```

Upload and OCR a receipt:
```bash
curl -s -X POST http://localhost:3000/api/receipts/ocr \
  -F "receipt=@/path/to/receipt.jpg" | jq .
# Returns: { vendor, date, amount, confidence, rawText }
```

Generate a PDF report:
```bash
curl -s -X POST http://localhost:3000/api/reports \
  -H "Content-Type: application/json" \
  -d '{
    "title": "Q1 2026 Expenses",
    "dateFrom": "2026-01-01",
    "dateTo": "2026-03-31",
    "currency": "USD",
    "categoryIds": [1, 2, 3, 4, 5]
  }' | jq .
# Returns: { id, title, total, pdf_path }
```

Download the PDF:
```bash
curl -s http://localhost:3000/api/reports/1/pdf -o q1_2026.pdf
```

Export expenses as CSV:
```bash
curl -s "http://localhost:3000/api/expenses/export/csv?from=2026-01-01&to=2026-03-31" \
  -o expenses_q1.csv
```

## CSV import format

The bulk import endpoint accepts a CSV file with the following columns:

```
date,vendor,amount,category,description,currency
2026-03-20,United Airlines,420.00,Travel,NYC to SFO - Q1 conf.,USD
2026-03-19,The Capital Grille,187.50,Meals,Client dinner,USD
```

Rules:
- `date`: ISO 8601 (YYYY-MM-DD), required
- `vendor`: string, required
- `amount`: positive decimal without currency symbol, required
- `category`: case-insensitive match against existing category names; unknown values default to Uncategorized
- `description`: optional, max 1000 chars
- `currency`: optional ISO 4217 code, defaults to app default currency
- Rows where date or amount are invalid are skipped and listed in the response as errors
- Rows with matching (date, vendor, amount) already in the database are skipped as duplicates

Import via API:
```bash
curl -s -X POST http://localhost:3000/api/expenses/import \
  -F "file=@expenses_q1.csv" | jq .
# Returns: { imported, skipped, errors: [{ row, reason }] }
```

## Default categories (seeded on first run)

| ID | Name | Color |
|----|------|-------|
| 1 | Travel | #2563eb |
| 2 | Meals | #16a34a |
| 3 | Accommodation | #7c3aed |
| 4 | Office supplies | #ea580c |
| 5 | Software | #d97706 |
| 6 | Other | #64748b |

## Behavior notes

- Receipt images are stored in `UPLOAD_DIR` with UUID filenames. The `receipt_path` column in the database stores the relative path.
- OCR runs synchronously on upload. For large images (>5 MB) it may take a few seconds.
- PDF files are stored in `data/pdfs/`. The report row stores the relative `pdf_path`.
- Deleting a category sets all affected `category_id` values to NULL (displayed as "Uncategorized").
- All database queries use parameterized statements. Do not interpolate user input into SQL strings.
- The app does not support multi-user authentication. It is designed for local single-user use.

## Troubleshooting

**OCR returns empty fields:** The image may be too small or low-contrast. Resize to at least 800px wide before uploading. Tesseract.js performs best on scanned documents with dark text on white background.

**PDF generation is slow:** Large reports (100+ line items) with receipt thumbnails enabled can take 5-10 seconds. Disable "Include receipt thumbnails" in Settings for faster output.

**SQLite "database is locked":** Only one process should access the database at a time. Stop any other running instances of the app before starting a new one.