receipt-scanning-tools

What this skill does

# Receipt Scanning Tools Skill

## Project Overview

This skill helps you work with the receipt scanning tools in the nonprofit_finance_db project. It includes manual entry tools, automated OCR scanning, and database integration for tracking receipts and expenses.

## Critical Project Paths

### Virtual Environment
```bash
# Virtual environment location (IMPORTANT!)
VENV_PATH=/home/adamsl/planner/.venv

# Always activate before running Python scripts:
source /home/adamsl/planner/.venv/bin/activate
```

### Project Root
```bash
PROJECT_ROOT=/home/adamsl/planner/nonprofit_finance_db
```

### Receipt Scanning Tools Directory
```bash
TOOLS_DIR=/home/adamsl/planner/nonprofit_finance_db/receipt_scanning_tools
```

### Key Files

**Receipt Tools:**
- `receipt_scanning_tools/receipt_tools_menu.py` - **Main menu** for all receipt tools
- `receipt_scanning_tools/manual_entry.py` - Manual receipt entry CLI tool
- `receipt_scanning_tools/delete_expenses_by_date.py` - Delete expenses by date tool

**Backend Services:**
- `app/services/receipt_parser.py` - Receipt parsing service
- `app/services/receipt_engine.py` - AI-powered OCR engine
- `app/api/receipt_endpoints.py` - REST API endpoints
- `app/models/receipt_models.py` - Data models
- `app/db/pool.py` - Database connection pool

## Database Schema

### Categories Table
```sql
-- Actual schema (no category_path column!)
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    parent_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

-- To get full category path, use recursive query:
WITH RECURSIVE category_hierarchy AS (
    SELECT id, name, parent_id, name as full_path
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id,
           CONCAT(ch.full_path, ' > ', c.name) as full_path
    FROM categories c
    INNER JOIN category_hierarchy ch ON c.parent_id = ch.id
)
SELECT * FROM category_hierarchy ORDER BY full_path;
```

### Merchants Table
```sql
CREATE TABLE merchants (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL UNIQUE,
    category VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name),
    INDEX idx_category (category)
);

-- Categories for church non-profit:
-- Gifts and Love Offerings: Guiding Light, Mel Trotter Ministries,
--                            Salvation Army, Samaritan Purse, Jews for Jesus,
--                            Intercessors for America, Segals in Israel,
--                            Chosen People, Columbia Orphanage, Right to Life,
--                            Johnsons in Dominican Republic, Jewish Voice
-- Ministers and Workers: EG Adams, Snowplow Person
-- Presents for ROL Friends and Members: James Abney, Cliff Baker, Annie Baker,
--                                        Karen Cook, Richard Meninga, Karen Roark,
--                                        Hannah Schneider, Rebecca Esposito,
--                                        Karen Vander Vliet, Mark Vander Vliet,
--                                        Alexander Vander Vliet, John Roark,
--                                        Joshua McKay, Eddie Hoekstra, Ian Gonzalez
-- Food & Supplies: Meijer, Gordon Foods, Walmart, Target, Costco,
--                  Sams Club, Kroger, Aldi, Family Fare, Spartan Stores
```

### Expenses Table
```sql
CREATE TABLE expenses (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    org_id BIGINT UNSIGNED NOT NULL,
    expense_date DATE NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    category_id BIGINT UNSIGNED,
    merchant_id BIGINT UNSIGNED,  -- Links to merchants table
    method ENUM('CASH','CARD','BANK','OTHER'),
    description VARCHAR(255),
    receipt_url VARCHAR(500),
    paid_by_contact_id BIGINT UNSIGNED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (org_id) REFERENCES organizations(id),
    FOREIGN KEY (category_id) REFERENCES categories(id),
    FOREIGN KEY (merchant_id) REFERENCES merchants(id)
);
```

### Receipt Metadata Table
```sql
CREATE TABLE receipt_metadata (
    id INT PRIMARY KEY AUTO_INCREMENT,
    expense_id INT NOT NULL,
    model_name VARCHAR(100),
    confidence_score DECIMAL(3,2),
    raw_response TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (expense_id) REFERENCES expenses(id)
);
```

## Common Commands

### Database Inspection
```bash
# Connect to database (with venv activated)
source /home/adamsl/planner/.venv/bin/activate
cd /home/adamsl/planner/nonprofit_finance_db

# Check categories structure
python3 -c "
from app.db import get_connection
with get_connection() as conn:
    cursor = conn.cursor()
    cursor.execute('DESCRIBE categories')
    for row in cursor.fetchall():
        print(row)
"

# View categories with hierarchy
python3 -c "
from app.db import get_connection
with get_connection() as conn:
    cursor = conn.cursor()
    cursor.execute('''
        WITH RECURSIVE category_hierarchy AS (
            SELECT id, name, parent_id, name as full_path, 0 as level
            FROM categories
            WHERE parent_id IS NULL
            UNION ALL
            SELECT c.id, c.name, c.parent_id,
                   CONCAT(ch.full_path, \" > \", c.name) as full_path,
                   ch.level + 1
            FROM categories c
            INNER JOIN category_hierarchy ch ON c.parent_id = ch.id
        )
        SELECT id, name, full_path FROM category_hierarchy ORDER BY full_path
    ''')
    for row in cursor.fetchall():
        print(f'{row[0]:3d} | {row[1]:30s} | {row[2]}')
"
```

### Running Tools
```bash
# Always activate venv first!
source /home/adamsl/planner/.venv/bin/activate
cd /home/adamsl/planner/nonprofit_finance_db

# Run main receipt tools menu (RECOMMENDED)
python3 receipt_scanning_tools/receipt_tools_menu.py

# Or run individual tools directly:
python3 receipt_scanning_tools/manual_entry.py
python3 receipt_scanning_tools/delete_expenses_by_date.py

# Other services:
python3 api_server.py  # API server
python3 scripts/view_transactions.py  # Transaction viewer
```

## Typical Workflows

### Workflow 1: Manual Receipt Entry
1. Activate virtual environment
2. Run manual entry tool
3. **Select merchant from categorized menu**:
   - **Gifts and Love Offerings** (12 ministries: Guiding Light, Mel Trotter, Salvation Army, Samaritan Purse, Jews for Jesus, etc.)
   - **Ministers and Workers** (2 people: EG Adams, Snowplow Person)
   - **Presents for ROL Friends and Members** (15 people: James Abney, Baker family, Karen Cook, etc.)
   - **Food & Supplies** (grocery stores: Meijer, Gordon Foods, Walmart, etc.)
   - Option to add custom merchant with category
4. Enter receipt amount and date
5. Select expense category from hierarchical list
6. Review summary and confirm
7. Save to database

**Merchant Selection Features:**
- **Organized by category** for church non-profit giving and ministry
- Pre-populated with actual ministry partners and recipients
- Separate categories for love offerings, worker support, and member gifts
- Option to add custom merchant names with category selection (5 categories)
- Merchants stored in database for reuse across entries
- Alphabetically sorted within each category

### Workflow 2: Fix Schema Issues
When you encounter column errors like "Unknown column 'category_path'":

1. Check actual table schema:
   ```python
   from app.db import get_connection
   with get_connection() as conn:
       cursor = conn.cursor()
       cursor.execute('DESCRIBE categories')
       print(cursor.fetchall())
   ```

2. Update query to use actual columns
3. Use recursive CTE for hierarchical path if needed

### Workflow 3: Add New Receipt Scanning Tool
1. Create new Python file in `receipt_scanning_tools/`
2. Import database connection: `from app.db import get