Building LedgerLens Recon: Why Not Every Reconciliation Problem Needs Kafka

Field Note — LedgerLens Recon

---

The reconciliation tooling industry has a scale bias. Every blog post, every conference talk, every vendor pitch assumes you're reconciling millions of transactions across dozens of sources in real time. The solution is always the same: event streaming, distributed orchestration, microservices, data lakes.

But most reconciliation problems I encounter are smaller, simpler, and more deterministic:

These problems don't need Kafka. They need correctness, idempotency, and a report someone can actually read.

LedgerLens Recon was built for this category: a lightweight, auditable reconciliation CLI that matches Stripe payments against an internal ledger and produces a color-coded Excel report. Small, focused, and correct.

---

The Philosophy: Right-Sized Tools

Engineering culture rewards complexity. A system built on Kafka, Spark, and Airflow is seen as more "serious" than a Python script. But complexity has costs: operational overhead, debugging difficulty, team onboarding time, and failure modes that are hard to reason about.

LedgerLens Recon takes the opposite stance: use the simplest tool that can do the job correctly. For batch reconciliation of moderate volume, that tool is a well-structured Python CLI with strong testing and clear reporting.

---

Architecture: Modular and Testable

Package Layout

The codebase is organized into functional modules, not layers:

ledgerlens/
  data_sources/
    stripe_client.py       # Stripe API integration with mock mode
    db_client.py           # SQLAlchemy ORM client with CSV fallback
    csv_client.py          # Standalone CSV parser for credential-free runs
  reconciliation/
    matcher.py             # Core matching engine
    categorizer.py         # Match / Mismatch / Missing classification
    confidence_scorer.py   # 0.0–1.0 confidence calculation
  reporting/
    excel_writer.py        # openpyxl-based report generation
    summary_generator.py   # Aggregate statistics
  utils/
    config.py              # Config-driven thresholds
    logger.py              # Structured logging
    validators.py          # Input validation

This layout makes the codebase navigable. A developer looking for the matching logic goes to reconciliation/matcher.py. A developer looking for report formatting goes to reporting/excel_writer.py. There are no surprise dependencies — the matcher doesn't import the Excel writer, and the Stripe client doesn't import the database client.

Stripe Client with Mock Mode

Every external dependency has a mock mode. The Stripe client can run in two configurations:

class StripeClient:
    def __init__(self, mode: Literal["live", "mock"] = "live"):
        self.mode = mode
        if mode == "mock":
            self._load_mock_data()

    def fetch_charges(self, since: datetime) -> list[Charge]:
        if self.mode == "mock":
            return self._mock_charges
        return self._api_fetch_charges(since)

Mock mode uses a JSON file of realistic charge shapes — same fields, same data types, same edge cases (refunds, disputes, currency conversions) — but with fake IDs and amounts. This lets the entire pipeline run without live credentials, which is essential for:

The mock data is kept in sync with the real API via a scheduled job that fetches a small sample of sanitized production data and updates the mock file. If the Stripe API changes (new fields, deprecated fields), the mock data reflects it.

SQLAlchemy DB Client with CSV Fallback

The internal ledger is typically a PostgreSQL database, but not always. Some teams keep their ledger in a CSV export from their accounting software. The DB client handles both:

class DBClient:
    def __init__(self, connection_string: str | None = None, csv_path: str | None = None):
        if connection_string:
            self.engine = create_engine(connection_string)
            self.mode = "database"
        elif csv_path:
            self.df = pd.read_csv(csv_path)
            self.mode = "csv"

    def fetch_ledger_entries(self, since: datetime) -> list[LedgerEntry]:
        if self.mode == "database":
            return self._query_database(since)
        return self._query_csv(since)

The CSV fallback is not a toy feature. It is the primary use case for many small teams. A finance manager who exports their ledger to CSV every morning should be able to run reconciliation without setting up a database connection.

Matcher: Transaction ID as Primary Key

The matching engine uses transaction ID as the primary key, with amount and timestamp tolerance for validation:

def match(self, stripe_charges: list[Charge], ledger_entries: list[LedgerEntry]) -> list[MatchResult]:
    results = []
    ledger_by_txn_id = {e.transaction_id: e for e in ledger_entries}

    for charge in stripe_charges:
        if charge.id in ledger_by_txn_id:
            ledger_entry = ledger_by_txn_id[charge.id]
            confidence = self._score_match(charge, ledger_entry)

            if confidence >= self.config.auto_match_threshold:
                results.append(MatchResult(status="MATCHED", confidence=confidence, ...))
            elif confidence >= self.config.review_threshold:
                results.append(MatchResult(status="REVIEW", confidence=confidence, ...))
            else:
                results.append(MatchResult(status="MISMATCH", confidence=confidence, ...))
        else:
            results.append(MatchResult(status="MISSING_LEDGER", confidence=0.0, ...))

    # Check for ledger entries with no corresponding Stripe charge
    for entry in ledger_entries:
        if entry.transaction_id not in {c.id for c in stripe_charges}:
            results.append(MatchResult(status="MISSING_STRIPE", confidence=0.0, ...))

    return results

The match scoring checks:

1. Exact transaction ID match: If the IDs match, confidence starts at 0.8.

2. Amount match: If the amounts match (within currency precision), confidence += 0.1.

3. Timestamp match: If the timestamps are within the configured tolerance (default 24 hours), confidence += 0.1.

A perfect match (ID + amount + timestamp) scores 1.0. A match with only ID and amount scores 0.9. A match with only ID scores 0.8 — high enough to flag for review, not high enough to auto-match.

The thresholds are configurable:

matcher:
  auto_match_threshold: 0.95
  review_threshold: 0.70
  timestamp_tolerance_hours: 24
  amount_tolerance_percent: 0.01  # 1% for currency conversion rounding

Excel Report with Color Coding

The output is an Excel file, not a JSON dump or a terminal table. Finance teams live in Excel. A JSON report that requires a developer to interpret is useless to a finance manager.

The report has two sheets:

1. Summary sheet: Aggregate statistics — total transactions, match rate, mismatch count, missing count, total value at risk.

2. Detail sheet: One row per transaction, color-coded:

- Green: Matched (confidence >= 0.95)

- Yellow: Review (confidence 0.70–0.94)

- Red: Mismatch or Missing (confidence < 0.70)

def write_report(self, results: list[MatchResult], output_path: str):
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Reconciliation Results"

    # Header row
    headers = ["Transaction ID", "Stripe Amount", "Ledger Amount", "Status", "Confidence", "Notes"]
    ws.append(headers)

    for result in results:
        row = [result.transaction_id, result.stripe_amount, result.ledger_amount, 
               result.status, result.confidence, result.notes]
        ws.append(row)

        # Color coding
        cell = ws.cell(row=ws.max_row, column=4)  # Status column
        if result.status == "MATCHED":
            cell.fill = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid")
        elif result.status == "REVIEW":
            cell.fill = PatternFill(start_color="FFEB9C", end_color="FFEB9C", fill_type="solid")
        else:
            cell.fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")

    wb.save(output_path)

The color coding is trivial to implement but transformative for usability. A finance manager can scan the spreadsheet and immediately see where to focus attention. Red rows need investigation. Yellow rows need a quick check. Green rows are done.

---

Key Challenges

Keeping the Tool Simple While Meeting Production Basics

The tension in LedgerLens Recon is between simplicity and production readiness. A simple script is easy to write but hard to trust. A production system is trustworthy but complex.

Our solution was to draw a hard line: the core logic (matching, scoring, reporting) is simple and readable. The production basics (structured logging, config-driven thresholds, unit tests, mock modes) are wrapped around it, not baked into it. A developer can read the matcher in 10 minutes and understand exactly how it works. The logging and testing infrastructure is separate and doesn't clutter the business logic.

Making the Excel Report a Usable Artifact

The Excel report is not an afterthought. It is the product. The matching logic exists to produce the report; the report is what the user actually uses.

We invested in report usability:

---

Lessons Learned

Not every reconciliation problem needs orchestration — clarity and idempotency often matter more than scale. A well-designed CLI that runs in 30 seconds and produces a clear report is more valuable than a distributed system that runs in 5 seconds but requires a team to maintain.

A well-designed report can be the actual product, with the matching logic underneath it just doing its job quietly. The report is what users see, trust, and act on. The matching logic is just the mechanism. Invest in the report.

Deterministic output is a feature. Reruns should produce the same report, byte-for-byte, given the same inputs. This makes diffs meaningful, audits possible, and debugging straightforward. We enforce deterministic ordering and stable formatting to achieve this.

---

LedgerLens Recon is open source at [github.com/Gwerdonatus/Ledgerlens-recon](https://github.com/Gwerdonatus/Ledgerlens-recon)