receipt-scanning-tools

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

# 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

PROJECT_ROOT=/home/adamsl/planner/nonprofit_finance_db

Receipt Scanning Tools Directory

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

-- 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

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

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

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

# 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

# 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:
4. Gifts and Love Offerings (12 ministries: Guiding Light, Mel Trotter, Salvation Army, Samaritan Purse, Jews for Jesus, etc.)
5. Ministers and Workers (2 people: EG Adams, Snowplow Person)
6. Presents for ROL Friends and Members (15 people: James Abney, Baker family, Karen Cook, etc.)
7. Food & Supplies (grocery stores: Meijer, Gordon Foods, Walmart, etc.)
8. Option to add custom merchant with category
9. Enter receipt amount and date
10. Select expense category from hierarchical list
11. Review summary and confirm
12. 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()) ``

1. Update query to use actual columns
2. 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_