# Receipts Archive

Local receipt/warranty archive for Michael.

## Structure

- `receipts.sqlite` — searchable SQLite database.
- `images/YY-MM-images/` — original receipt/warranty images, grouped by month.

## Current database tables

- `receipts` — one row per receipt/document image, including receipt-level `notes`.
- `receipt_items` — line items linked to `receipts.id`.

## Suggested conventions

- `category` is broad and filterable, e.g. `Home purchases`, `Business expenses`, `Vehicle`, `Tools`, `Medical`, `Travel`.
- `doc_type` distinguishes `receipt`, `invoice`, `warranty_certificate`, `quote`, etc.
- Keep images immutable; fix OCR/database fields rather than replacing the source image.
- Use `image_sha256` to avoid duplicate imports.

## Local import/OCR wrapper

```bash
# Dry-run OCR/parse without touching the DB
knowledge/receipts/scripts/receipt_import.py /path/to/receipt.jpg --dry-run --json

# Import a new receipt image/PDF into the archive and DB
knowledge/receipts/scripts/receipt_import.py /path/to/receipt.jpg --category "Home purchases" --notes "Warranty for office"

# Re-OCR/update an existing DB row
knowledge/receipts/scripts/receipt_import.py knowledge/receipts/images/26-05-images/example.jpg --receipt-id 2 --category "Personal care"
```

The wrapper keeps originals immutable, writes OCR derivatives under `knowledge/receipts/ocr/<sha-prefix>/`, uses local Tesseract/OCRmyPDF only, and stores parsed fields plus raw OCR text in `receipts.sqlite`.

## Useful queries

```sql
-- Recent records
SELECT id, category, doc_type, merchant_name, receipt_date, total, image_path
FROM receipts
ORDER BY COALESCE(receipt_date, captured_at) DESC;

-- Filter by category
SELECT id, merchant_name, receipt_date, total, image_path
FROM receipts
WHERE category = 'Home purchases';

-- Search items/text
SELECT r.id, r.category, r.merchant_name, i.description, i.sku, r.image_path
FROM receipts r
JOIN receipt_items i ON i.receipt_id = r.id
WHERE i.description LIKE '%powerbank%' OR r.ocr_raw_text LIKE '%powerbank%';
```