56 lines
2.0 KiB
Markdown
56 lines
2.0 KiB
Markdown
# 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%';
|
|
```
|