bank-statement-api / docs /DATA_FLOW.md
3v324v23's picture
Feat: Added New column in xlsx
fbb917f
|
Raw History Blame Contribute Delete
7.11 kB
# BeFree Data Flow Lifecycle
This document describes the complete lifecycle of a bank statement as it passes through the BeFree Bank Statement Processor, from initial PDF upload to finalized Excel export.
---
## Data Lifecycle Diagram
```
[ Upload PDF ]
β”‚
β–Ό (POST /api/v1/statements/upload)
[ Save PDF to Disk ] ──► Overwatch Event (stage="upload")
β”‚
β–Ό (bank-extractor)
[ Text & Coordinates Extraction ] ──► Overwatch Events (stage="layout_detection", "coordinate_extraction")
β”‚
β–Ό (core.helpers / models.transactions)
[ Normalization ] ──► RawTransaction to NormTransaction ──► Overwatch Event (stage="normalisation")
β”‚
β–Ό (core.helpers / core.statement_io)
[ Math Audit ] ──► StatementAudit calculated ──► Overwatch Event (stage="audit")
β”‚
β–Ό (Cache JSON)
[ Cache NormTransaction List ] ──► Stored as data.json
β”‚
β–Ό (bank-classifier / ClassificationPipeline)
[ Three-Tier Classification ] ──► RuleEngine ──► MLEngine (Disabled) ──► LLMEngine (Gemini)
β”‚ β”‚
β”œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β—„β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
β–Ό
[ Rule Promotion ] ──► RuleLearner logs suggestions ──► Overwatch Event (stage="rule_learned")
β”‚
β–Ό (Logging)
[ Write classification_latest.jsonl ]
β”‚
β–Ό (POST /api/v1/statements/.../transactions)
[ Human-in-the-Loop Review ] ──► User corrections saved to data/corrections/
β”‚
β–Ό (GET /api/v1/statements/.../download)
[ Excel / CSV Export ] ──► Compiled via openpyxl ──► Saved to data/output/{id}.xlsx
```
---
## Detailed Data Lifecycle Stages
### 1. Ingestion (Upload)
- **Endpoint**: `POST /api/v1/statements/upload` (handled by `routers/upload.py`)
- **Actions**:
- The client uploads one or more PDF files.
- A unique `statement_id` (UUIDv4) is generated.
- The PDF file is written to the filesystem at `data/uploads/{statement_id}/statement.pdf`.
- **Overwatch Event**: Emits log with `stage="upload"`.
### 2. Layout Detection & Text Extraction
- **Module**: `bank-extractor` (background tasks managed in `core/extraction.py`)
- **Actions**:
- The extractor parses the PDF document page-by-page.
- Detects headers and column boundaries (bounding box coordinates `x_min`, `x_max`, `y`).
- Outputs a list of `RawTransaction` models containing string representation of rows.
- **Data Model**: `shared.models.RawTransaction`
- **Overwatch Events**: Emits logs with `stage="layout_detection"` and `stage="coordinate_extraction"`.
### 3. Normalization
- **Module**: `core/helpers.py` (specifically `_to_transaction_row_global` / `_sort_dicts_chronologically`)
- **Actions**:
- The string values of `RawTransaction` are cleaned.
- Debit/Credit columns are coerced into `Decimal` structures.
- Date strings (e.g. `24 Nov`, `12/05/2026`) are parsed into standard `datetime.date` objects.
- A unique `tx_id` (e.g., `tx_page_index_hash`) is generated for each row.
- **Data Model**: `models.transactions.TransactionRowModel`
- **Overwatch Event**: Emits log with `stage="normalisation"`.
### 4. Statement Auditing
- **Module**: `core/helpers.py` / `core/statement_io.py`
- **Actions**:
- Performs mathematical verification of statement pages:
$$\text{Computed Closing Balance} = \text{Opening Balance} + \sum \text{Credits} - \sum \text{Debits}$$
- Flags balance mismatches or sequence gaps between pages.
- **Data Model**: `shared.models.StatementAudit` and `shared.models.PageAudit`
- **Cache**: The verified `NormTransaction` array and the `StatementAudit` result are saved as a JSON cache file at `data/uploads/{statement_id}/data.json`.
- **Overwatch Event**: Emits log with `stage="audit"`.
### 5. Classification
- **Module**: `bank-classifier` (via `ClassificationPipeline.classify_batch()`)
- **Actions**:
- Executes a three-stage classification process:
1. **RuleEngine**: Scans the transaction description against keywords loaded from the database table `classification_rules`. If a keyword matches, it assigns the category.
2. **MLEngine**: An offline Machine Learning classifier (disabled by default, `use_ml=False`).
3. **LLMEngine**: Passes unclassified transactions in batches to the Google Gemini API with a system prompt detailing available categories.
- **RuleLearner**: Promotes frequently matched LLM categories to candidate database rules.
- **Logging**: The classification results are logged to `data/logs/classification_latest.jsonl`.
- **Overwatch Events**: Emits `stage="classification_started"`, `stage="transaction_classified"`, `stage="classification_completed"`, and `stage="rule_learned"`.
### 6. Human-in-the-Loop (HITL) Review
- **Module**: `bank-hitl` / `routers/review.py` / `routers/transactions.py`
- **Actions**:
- Transactions marked as `flagged` (due to low extraction confidence or balance issues) are displayed in the review interface.
- Users can correct dates, swap debit/credit columns, modify descriptions, or assign manual category overrides.
- Manual edits are written to `data/corrections/{statement_id}_corrections.json` and applied to the active transaction log cache.
### 7. Export
- **Endpoint**: `GET /api/v1/statements/{id}/download?format=xlsx`
- **Actions**:
- Loads the corrected `NormTransaction` log from cache.
- Uses `openpyxl` to build a styled spreadsheet.
- Generates a summary dashboard tab containing monthly breakdowns and expense category ratios.
- Writes the final workbook to `data/output/{statement_id}.xlsx` and streams it to the user.
---
## The App Settings Configuration File
The system behavior is controlled by `data/settings.json`. It is loaded at server startup and can be modified via `PATCH /api/v1/settings`.
```json
{
"ocrEnabled": true,
"confidenceThreshold": 0.8,
"autoExportEnabled": false,
"auditRetentionDays": 90,
"defaultExportFormat": "xlsx",
"extractLast3Months": true
}
```
### Settings Impact on Data Flow:
- `ocrEnabled`: If true, triggers optical character recognition fallback on scanned PDFs inside `bank-extractor`.
- `confidenceThreshold`: Transactions extracted with a confidence score below this threshold are marked as `flagged` (status set to `review` or `flagged`), forcing them into the Human-in-the-loop review queue.
- `auditRetentionDays`: Governs the database event prune window inside `overwatch/store.py` (deletes events older than the specified retention period).
- `extractLast3Months`: If set to `true`, the `GET /api/v1/statements/{id}/transactions` endpoint filters and flags transactions falling outside the latest 3 calendar months of the statement's date range. Only transactions within the latest 3-month window are selected for automatic classification, optimizing Gemini API usage costs.