Reconciliation & Reporting Module

Overview

The Reconciliation module handles account reconciliation with external systems (banks, gateways), discrepancy detection and resolution, and end-of-day batch reconciliation. The Reporting component provides comprehensive reports on transactions, portfolios, and system activities.

API Prefix: /api/v1/reconciliation

Route Verification Status

• Source Router: app/api/v1/endpoints/reconciliation.py
• Live Route Count: 12
• Verification State: Verified (2026-06-28)
• Canonical Tracking: docs/API_ROUTE_DOCUMENTATION_TODO.md
• Verification Method: Route decorators extracted from @r.<method>(...) in app/api/v1/endpoints/reconciliation.py.

Key Features

1. Transaction Reconciliation

• Match internal transactions with bank statements
• Identify pending transactions
• Detect discrepancies
• Reconciliation workflows
• Manual reconciliation review

2. Discrepancy Management

• Detect unmatched transactions
• Categorize discrepancies
• Investigation workflow
• Resolution tracking
• Root cause analysis

3. End-of-Day Processing

• Daily reconciliation jobs
• Account balance settlement
• Interest calculations
• Fee processing
• Daily reporting

4. Multi-Source Reconciliation

• Bank reconciliation
• Payment gateway reconciliation
• Internal ledger reconciliation
• Multi-currency reconciliation

API Endpoints (Verified Against Live Router)

For the complete reconciliation endpoint inventory (all 12 routes with method, effective path, and source line), use: - docs/API_ROUTE_LIVE_REFERENCE.md -> section reconciliation.py (12 routes)

Permission Dependency Notes (Verified)

Confirmed explicit permission checks in reconciliation.py include: - Reporting and visibility: fetch_reconciliation_report, view_reconciliation_legs, view_reconciliation_status, view_reconciliation_report. - Discrepancy operations: fetch_reconciliation_discrepancy, get_reconciliation_discrepancy, resolve_reconciliation_discrepancy. - Execution and artifacts: reconcile_transaction, run_reconciliation, download_reconciliation.

Standard Error Response Matrix

{
   "status_code": 400,
   "error_code": "RECONCILIATION_ERROR",
   "message": "Human-readable summary",
   "details": {}
}

Common mappings: - 400 invalid reconciliation/discrepancy operation. - 401 unauthenticated request. - 403 permission denied. - 404 reconciliation/discrepancy/report not found. - 409 conflicting reconciliation state. - 422 schema validation failure. - 429 throttled request. - 500 internal reconciliation processing error.

Database Models

Reconciliation Models

• ReconciliationRecord: Reconciliation session
• ReconciliationMatch: Matched transaction pairs
• ReconciliationDiscrepancy: Unmatched items
• ReconciliationReport: Generated report

Transaction Legs

• TransactionLeg: Individual debit/credit entries
• LedgerEntry: Accounting ledger entries
• SettlementEntry: Settlement records

Reconciliation Types

Daily Reconciliation

Process:
1. End of day (midnight)
2. Pull all transactions for day
3. Pull bank statement
4. Match transactions by:
   - Amount
   - Date
   - Reference number
5. Identify discrepancies
6. Generate report
7. Notify stakeholders

Typical discrepancies:
- Pending transactions (not yet cleared)
- Timing differences (posted different day)
- Amount mismatches (fee differences)
- Missing transactions (both sides)

Discrepancy Types

1. Amount Mismatch
   - Transaction amount differs
   - Often due to fees

2. Missing Transaction
   - In internal ledger but not on statement
   - Usually pending or failed

3. Extra on Statement
   - On bank statement but not in system
   - Usually bank fees or corrections

4. Date Difference
   - Transaction date vs posting date
   - Normal for batched processing

5. Duplicate
   - Transaction recorded twice
   - System error or manual entry

Reconciliation Workflow

Daily Reconciliation Flow

1. Trigger (Automated at midnight)
   ├─ Fetch all transactions
   ├─ Fetch bank statement
   └─ Start reconciliation job

2. Matching Engine
   ├─ Try exact match (amount + date)
   ├─ Try fuzzy match (similar amount, date range)
   ├─ Try by reference number
   └─ Flag unmatched

3. Analysis
   ├─ Categorize discrepancies
   ├─ Identify root causes
   ├─ Calculate impact
   └─ Generate analysis

4. Report Generation
   ├─ Summary statistics
   ├─ Exception list
   ├─ Reconciliation rate %
   └─ Recommendations

5. Resolution
   ├─ Manual review of exceptions
   ├─ Apply corrections
   ├─ Post adjustments if needed
   └─ Close reconciliation

Discrepancy Resolution

1. Identify Discrepancy
   - Transaction doesn't match

2. Investigate
   - Check internal records
   - Check bank statement
   - Review transaction details
   - Determine root cause

3. Categorize
   - Timing issue → Defer
   - Amount error → Adjust
   - Missing → Follow-up with bank
   - Duplicate → Reverse

4. Resolution
   - Apply correction
   - Update records
   - Document decision
   - Close case

Reporting Examples

Daily Summary Report

Date: June 20, 2024
========================================
Transactions Processed:        12,450
Total Value:                   825,500,000 ZMW

Matched Transactions:          12,400 (99.6%)
Pending Transactions:          30
Discrepancies:                 20 (0.2%)

By Transaction Type:
- P2P Transfer:               8,500 (68%)
- Merchant Payment:           2,100 (17%)
- Bill Payment:               1,200 (10%)
- Loan Repayment:             650 (5%)

Reconciliation Status:         COMPLETE
Exceptions:                    20
Action Required:              Yes

Discrepancy Report

Top Discrepancies:
1. Missing Transactions: 12
   - Amount: 450,000 ZMW
   - Pending for 3 days
   - Action: Follow-up with bank

2. Amount Mismatches: 5
   - Total Variance: 12,500 ZMW
   - Usually fee-related
   - Action: Investigate fees

3. Duplicates: 2
   - Amount: 8,000 ZMW
   - Action: Reverse duplicates

4. Timing Issues: 1
   - Amount: 5,000 ZMW
   - Defer until next day

Controllers & Business Logic

Reconciliation Controllers

• controller/reconciliation.py: Main reconciliation
• daily_reconciliation(): Run daily job
• match_transactions(): Match logic
• identify_discrepancies(): Find mismatches

• generate_report(): Create report

• controller/discrepancy.py: Discrepancy management

• categorize_discrepancy(): Classify issue
• investigate(): Investigation workflow
• resolve(): Apply resolution
• post_adjustment(): Post correction

Reporting Controllers

• controller/reporting.py: Report generation
• generate_daily_report(): Daily report
• generate_portfolio_report(): Portfolio analysis
• generate_exception_report(): Exception listing
• export_report(): Export to PDF/Excel

Batch Processing Configuration

Daily Job Schedule

{
  "daily_reconciliation": {
    "schedule": "0 0 * * *",
    "timeout_minutes": 30,
    "retry_on_failure": true,
    "max_retries": 3,
    "notification_on_completion": true
  },
  "end_of_day": {
    "schedule": "0 1 * * *",
    "timeout_minutes": 60,
    "tasks": [
      "reconcile_transactions",
      "calculate_interest",
      "process_fees",
      "settle_accounts"
    ]
  }
}

Error Handling

Reconciliation Errors

• RECONCILIATION_FAILED: Job failed
• DISCREPANCY_UNRESOLVED: Can't resolve issue
• STATEMENT_UNAVAILABLE: Bank statement not received
• AMOUNT_MISMATCH: Amount doesn't match
• TRANSACTION_NOT_FOUND: Transaction missing

Testing

Test Categories

• Transaction matching logic
• Discrepancy detection
• Reconciliation workflow
• Report generation
• Batch job scheduling

Performance Considerations

1. Batch Processing: Run during off-peak hours
2. Parallel Matching: Process transactions in parallel
3. Statement Caching: Cache bank statements
4. Report Generation: Async report creation
5. Archive Old Data: Archive old reconciliation records

Integration Points

External Services

• Banking System: Receive statements
• Payment Gateways: Verify transactions

Internal Modules

• Transaction Processing: Transaction data
• Wallet System: Balance tracking
• Reporting: Report generation

Related Documentation

• Transaction Processing - Transaction Recording
• Wallet - Balance Reconciliation