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.

{
  "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.