# BeFree Utility & Diagnostic Scripts This directory contains diagnostic, verification, migration, and analysis scripts for the BeFree Bank Statement Processor backend. ## Environment Setup Most of these scripts require the virtual environment to be activated and the `DATABASE_URL` or `GOOGLE_API_KEY` environment variables to be set in your `.env` file at the project root. ```bash # Activate virtual environment source venv/bin/activate # Make sure you are in the project root to run these python scripts/.py ``` --- ## Script Index ### 1. `check_neon.py` - **Purpose**: Connects to the PostgreSQL database (e.g. NeonDB) configured via `DATABASE_URL`, lists all tables in the `public` schema along with their row counts, and prints a sample of 3 rows from any table containing "overwatch" in its name. - **When to run**: To quickly verify database connectivity and check database row metrics. - **Usage**: ```bash python scripts/check_neon.py ``` ### 2. `inspect_legacy.py` - **Purpose**: Connects to the database and prints column metadata (name and data type) and 2 sample rows for the legacy tables: `audit_logs`, `classification_flat`, and `learned_rule_logs`. - **When to run**: To inspect the schema of legacy tables and determine the backfill shape before migrating data. - **Usage**: ```bash python scripts/inspect_legacy.py ``` ### 3. `verify_overwatch.py` - **Purpose**: Verifies that the Overwatch event store (`LogStore`) is functional by querying events, listing documents, and fetching the aggregated summary of the first document. - **When to run**: After deploying or setting up the database, to ensure that the Overwatch structured logging API can read and write correctly. - **Usage**: ```bash python scripts/verify_overwatch.py ``` ### 4. `compare_living_expenses.py` - **Purpose**: Compares a broker's human-classified living expense spreadsheet against a BeFree-generated spreadsheet. It matches transactions, detects naming inconsistencies, highlights reclassified items, identifies missed/extra items, calls Google Gemini for serviceability impact assessments, and produces a formatted multi-sheet comparison workbook. - **When to run**: When conducting quality assurance audits or comparing automated pipeline results against manual broker outputs. - **Dependencies**: Requires `pandas`, `openpyxl`, and `google-generativeai`. Needs `GOOGLE_API_KEY` set. - **Usage**: ```bash python scripts/compare_living_expenses.py [output_name.xlsx] ``` ### 5. `clean_and_promote_rules.py` - **Purpose**: Cleans active classification rules by de-duplicating keywords within the same category/criteria, resolves keyword category conflicts (cross-checks against `classification_flat` and domain heuristics), and filters/promotes high-quality learned rules from `learned_rule_logs` (applying word length, stop-word filters, and confidence thresholds). - **When to run**: Periodically to maintain classification hygiene and promote auto-learned rules to production. - **Usage**: - Run in dry-run mode (default): ```bash python scripts/clean_and_promote_rules.py ``` - Commit changes to the database: ```bash python scripts/clean_and_promote_rules.py --commit ``` ### 6. `backfill_overwatch.py` - **Purpose**: One-time backfill script to populate the `overwatch_events` table from historical records in the legacy `audit_logs` (mapping actions like `upload_statement` to `upload` stage) and `learned_rule_logs` tables. - **When to run**: When upgrading a database from an older version of BeFree to backfill processing history. - **Usage**: ```bash python scripts/backfill_overwatch.py ``` ### 7. `create_learned_rule_logs_table.py` - **Purpose**: Creates the `learned_rule_logs` table (and adds the `category_id` column if it is missing) using SQLAlchemy ORM. - **When to run**: Running database setup or migrations for rule learning. - **Usage**: ```bash python scripts/create_learned_rule_logs_table.py ``` ### 8. `fix_extracted_balances.py` - **Purpose**: Scans all `.xlsx` files in `data/output/`, converts any trailing CR-suffixed balances into negative numbers (standardizing credit/overdraft balances), and saves a `.fixed.xlsx` copy. - **When to run**: To clean historical generated outputs that had CR balance formatting issues. - **Usage**: ```bash python scripts/fix_extracted_balances.py ``` ### 9. `migrate_rules_to_db.py` - **Purpose**: Idempotently seeds `ClassificationCategory` and `ClassificationRule` tables in the database from the static YAML definitions (`default_rules.yaml`) and mapping configurations. - **When to run**: During initial setup or rule reset to establish the database as the source of truth for the rule classification engine. - **Usage**: ```bash python scripts/migrate_rules_to_db.py ```