ROL Finance Plan

1 Verify Extracted Statement Data

Some source documents may be fuzzy, OCR-generated, or manually translated into structured data.

For every statement or financial document:

• Extract usable transaction data.
• Use a Python/calculator tool to verify totals.
• Confirm that individual expenses, deposits, and balances add up correctly.
• Flag any math mismatch before inserting data into the database.

2 Determine vendor_key and Category

For each expense, determine the correct vendor_key from the transaction description.

After the vendor_key is identified:

1. Look up the vendor in the existing lookup tools.
2. Assign the correct expense category.
3. If the vendor is unknown, add it to the evolving lookup list after review.

Location of tools for this system:

/home/adamsl/rol_finances/tools/

This folder also contains lookup lists that grow as new vendors and categories are discovered.

3 Prevent Duplicate Data

The system must prevent the same transaction from being entered more than once.

Sometimes receipt data and bank statement data may describe the same real-world transaction. For example, a receipt may have a matching bank statement row, or a bank statement row may have matching receipt data.

When the system finds a match, the records should be linked together instead of being inserted as separate expenses.

The system should also guard against accidental duplicate entries where the same record is entered more than once.

4 Store Verified Data in the Database

Only insert or update data after verification is complete.

When matching data is found:

• If data from a receipt matches an existing expense record from a statement, populate the receipt_url column of that expense record with the path to the receipt.
• If data from a statement matches an existing expense record from a receipt, populate the source_file column of that expense record with the document path.
• Verify that the vendor_key and category match between the linked records.

If the vendor_key or category does not match:

The mismatch must be reviewed and resolved before continuing.

5 Project Plan — 2025 Church Financial Report Completion

Current status as of 2026-07-28: the 2025 books are further along than the 2026-07-22 snapshot showed, but the 2025 church financial report is still not finishable. A fresh account-by-account audit (database queries joined against the actual statement files on disk, not just the stale table) found that Amex Personal / Platinum Business and Choice Privileges 7580 are fully processed for all 12 months — a big correction from the prior UNKNOWN/assumed status — and that Prime Chase is actually processed for January–June (not just June as previously recorded). The database now contains 868 2025 expense rows (up from 745) plus 312 2025 bank-ledger transaction rows (unchanged; the transactions table still only reaches January–June). 132 2025 expenses now carry a receipt_url and 369 carry a source_file, but 104 2025 expenses remain uncategorized (up from 72, concentrated in March, June, April, May, and a smaller January tail; February and July–December are fully categorized). The top-level document inventory now shows bank statements: 213 files and receipts: 330 files, still with no dedicated invoices/ or supporting_documents/ folders. The real remaining gaps are concentrated in three accounts: FNBO 4851 (documents exist for all 12 months but only January is processed), Diners Club 0587/1391 (only January processed), and Fifth Third ROL 6285 / Personal 5938 / ROL 3119, which all have genuine document gaps in the second half of the year, not just processing gaps.

Missing documents or unfinished tasks

• Genuine document gaps (no source file found in any structure): Fifth Third ROL 6285 May–December 2025; Fifth Third Personal 5938 August–December 2025; Fifth Third ROL 3119 October–December 2025. These need to be chased as actual missing statements, not reprocessed from an existing file.
• Processing-only gaps (document present, not yet imported): FNBO 4851 February–December; Diners Club 0587/1391 February–December; JetBlue Barclays 3965 March–December; Prime Chase July–December; Fifth Third ROL 3119 January–September (repeated processing attempts have failed per its report.html).
• Fifth Third ROL 3119 needs its processing pipeline debugged before reprocessing is retried again — the existing report.html for the January statement shows explicit FAIL/error status, and no import batch for this account has ever completed.
• Folder normalization: invoices and supporting documents still have no dedicated top-level folders; several accounts (6285, 5938, 3119, JetBlue, Diners) have real source documents scattered across the legacy month folders, the year-folder structure, and the readable_documents root itself — worth consolidating so future gap-audits don't have to re-derive this.
• Uncategorized expenses: clear the 104 uncategorized 2025 expenses, with highest priority on the 38 March and 28 June rows.
• Receipt matching: identify which church expenses still require receipt attachment versus which legitimately have no receipt (132 of 868 currently have a receipt_url).
• Unmatched/missing-source review: review expenses with blank source_file or blank receipt_url and decide whether they need a missing-document chase, statement re-import, or are valid statement-only rows.
• Final year-end verification: once source completeness is proven, rerun duplicate review, total checks, category review, and final report math at the year level.

Recommended order of work (fastest practical path)

1. Build the master 2025 document checklist first. Do not keep processing blindly. List every expected church account/month/document type and mark each as scanned, processed, categorized, duplicate-checked, stored, or missing.
2. Finish statement coverage before chasing individual receipts. The fastest way to close the books is to guarantee all church statements are present and fully processed month by month; that establishes the complete transaction backbone.
3. Resolve all remaining statement FAIL / PASS-WITH-NOTES items that affect completeness. January is close enough that its exceptions should be closed immediately rather than rediscovered later.
4. Clear uncategorized expenses next. Once statement coverage is stable, work the 72 uncategorized rows in descending month-count order: June, March, May, April.
5. Then attach receipts/invoices/supporting docs to already-stored transactions. This is faster than trying to reconstruct the whole year from receipts first.
6. Then review unmatched receipts and possible duplicates. Anything still unmatched after full statement coverage becomes a short exception queue instead of a giant mixed backlog.
7. Only after the above, produce the final church 2025 report package.

Milestones and completion criteria

1. Milestone 1 — Source inventory complete.
   Completion criteria: every expected 2025 church document is listed, and each one is marked either processed and stored in the correct folder or explicitly missing/pending human retrieval.
2. Milestone 2 — Statement backbone complete.
   Completion criteria: every 2025 church statement has been scanned or ingested, archived to the correct year/month/account path, parsed, duplicate-checked, and stored; statement totals reconcile; open statement FAILs are resolved or formally excluded with justification.
3. Milestone 3 — Transaction classification complete.
   Completion criteria: zero church expenses remain uncategorized unless they are intentionally held for documented policy review.
4. Milestone 4 — Supporting documents linked.
   Completion criteria: each receipt/invoice/supporting document is either matched to a stored transaction, linked as a counterpart/support document, or placed on a short human-review exception list.
5. Milestone 5 — Year-end reconciliation complete.
   Completion criteria: no unresolved duplicate candidates, missing statements, unexplained unmatched receipts, or known total discrepancies remain in the church 2025 data set.
6. Milestone 6 — Final financial report ready.
   Completion criteria: year totals can be produced from verified data, with category summaries and supporting documents in place, and the remaining exception list is empty or explicitly approved by a human.

Blockers requiring human assistance

• Locating any church statements, receipts, invoices, or check images that were never scanned or are only present as paper originals.
• Answering ambiguous categorization policy questions where the statement text is too vague to distinguish business/church/personal purpose.
• Providing context for check numbers, transfers, and card payments that do not expose enough merchant detail in the source statement.
• Confirming whether missing later-month statements actually exist, belong to the church accounts, or were intentionally not used.
• Approving treatment of any lingering support-only or excluded documents that should not create accounting rows.

Fast completion strategy

The quickest accurate route is to treat this as a coverage problem first, cleanup problem second. First prove the full 2025 church statement set exists and is processed into the database. Second, clear the remaining uncategorized and unsupported rows. Third, link receipts and invoices to transactions already known to exist. This avoids the slow path of trying to reconstruct the year document-by-document without first knowing whether the transaction backbone is complete.

Immediate next actions:

1. Chase the three accounts with genuine document gaps first: Fifth Third ROL 6285 (May–Dec), Fifth Third Personal 5938 (Aug–Dec), and Fifth Third ROL 3119 (Oct–Dec) — confirm whether these statements exist anywhere (paper, online banking portal, email) before assuming they were never generated.
2. Debug the Fifth Third ROL 3119 import pipeline — every processing attempt on record has failed, so even the documents already on hand (Jan–Sep) haven't produced usable data.
3. Process the already-present-but-unimported statements: FNBO 4851 (Feb–Dec), Diners Club 0587/1391 (Feb–Dec), JetBlue Barclays 3965 (Mar–Dec), and Prime Chase (Jul–Dec) — these are pure processing work, no document chasing required.
4. Work down the 104 uncategorized expenses, starting with March (38) and June (28).
5. Generate the final year-level exception list, then produce the church financial report from only the verified rows.

6 Quick Links