Project 14: Bank Statement Reconciliation CLI

Build a safe command-line reconciler that turns imperfect bank and payment-processor exports into explainable matches, rejections, duplicates, and review queues.

Quick Reference

Attribute	Value
Difficulty	Level 1 - Beginner/Tinkerer
Suggested Seniority	Junior
Time Estimate	6-10 hours
Main Programming Language	Elixir
Alternative Programming Languages	Erlang, Gleam
Coolness Level	Level 3 - Genuinely Clever
Business Potential	Level 2 - Micro-SaaS/Pro Tool
Prerequisites	Basic functions, lists, maps, and command-line use
Key Topics	Pattern matching, guards, pipelines, Enum, Stream, structs, tagged results

1. Learning Objectives

By completing this project, you will:

Turn untrusted CSV rows into explicit Elixir data shapes using multi-clause functions, guards, structs, and tagged result tuples.
Build a readable transformation pipeline whose stages normalize, validate, index, match, and report without mutating shared state.
Distinguish business ambiguity from parser failure: malformed rows become rejection records, while plausible unmatched transactions remain reviewable.
Use lazy file streams for bounded memory and deterministic sorting for reproducible reports.
Design matching rules that produce an explanation trace rather than an unexplained yes/no decision.

2. All Theory Needed (Per-Concept Breakdown)

Pattern-Matched Transformation Pipelines

Fundamentals

Elixir programs often model work as a sequence of transformations over immutable values. Pattern matching does more than extract fields: it states which data shape a function accepts, while guards refine acceptable values using safe predicates. Multi-clause functions let normal rows, blank rows, malformed dates, invalid amounts, and unsupported currencies follow visibly different paths. Pipelines make the happy path readable, but tagged tuples such as “ok with value” and “error with reason” keep failure explicit. Enum eagerly processes finite collections; Stream builds lazy transformations that run only when consumed. A reconciliation tool benefits from all of these ideas because financial exports are not trustworthy input. Every row must either become a validated transaction or a preserved rejection with source file and line number. No exception should erase evidence, and no implicit coercion should silently change money.

Deep Dive into the concept

A transformation pipeline begins by choosing boundaries. Raw bytes belong at the ingestion boundary; parsed columns belong at the format-adapter boundary; normalized transactions belong to the domain; match decisions belong to the reconciliation layer; CSV or JSON rows belong to the reporting layer. Mixing those representations creates accidental complexity. For example, a matcher that still knows whether a bank used “15/07/2026” or “2026-07-15” cannot be tested independently of the input format. A stronger design converts both exports into one canonical transaction struct containing source, source row, external reference, booking date, integer minor units, currency, description, and a stable row identifier.

Pattern matching makes each transition inspectable. A parser can return one of three broad outcomes: a canonical candidate, a deliberately skipped row such as a header, or a rejection record. Multi-clause functions keep these cases adjacent and exhaustive. Guards are appropriate for simple structural checks such as non-empty references or integer amounts within a supported range. Business checks involving multiple records belong in ordinary functions, not in guards. That distinction prevents guards from becoming opaque and preserves useful error details.

Money must not be represented as a floating-point approximation. The project should normalize decimal text into integer minor units according to a declared currency scale. “10.10” becomes 1010 cents; locale-specific separators are handled by a source adapter, not guessed globally. A malformed amount produces a tagged rejection. This design makes equality exact and lets totals be checked without rounding drift.

The pipe operator should reveal stages, not hide control flow. A good pipeline may read as “stream rows, attach line numbers, parse, partition accepted from rejected, normalize, reconcile, render.” It should not force every function to return an incompatible shape merely to preserve piping. The “with” construct is useful for a single row with several dependent validations: parse date, parse money, validate currency, then construct the struct. Its “else” branches should translate low-level failures into the project’s stable rejection taxonomy.

Laziness matters at the input edge. File.stream can avoid loading a multi-gigabyte export, and Stream.with_index can preserve line numbers. Reconciliation itself may require indexes in memory, so the learner must choose what is bounded. One practical approach streams and validates both inputs, then builds a map from a composite candidate key to a list of processor transactions. Lists remain necessary because duplicate keys are valid evidence; mapping one key to one value would silently overwrite duplicates.

Matching is a policy, not mere equality. Begin with a strict rule using normalized reference, currency, and amount. A second rule may allow a bounded date difference when the reference and amount match. Each rule should return a confidence label and evidence, such as “exact reference and amount; booking dates differ by one day.” Consuming a match prevents one processor row from matching several bank rows. Ambiguous candidate sets go to review rather than being resolved by arbitrary list order.

Determinism is an operational feature. Maps do not communicate business ordering, asynchronous file reads may finish differently, and source exports can contain duplicates. Before rendering, sort by a stable tuple such as source date, source row, and stable identifier. Given identical files and configuration, report bytes should be identical. This enables checksum comparison in CI and makes user trust easier to earn.

The central invariants are: accepted transactions have canonical dates, currencies, and integer amounts; every input data row appears exactly once among matched, unmatched, duplicate, or rejected outputs; one row cannot be consumed by two matches; rejected rows preserve their original location; totals reconcile to the sum of categorized rows. Failure modes include silent row loss, float rounding, overwriting duplicate keys, locale guessing, ambiguous matches selected nondeterministically, and eagerly loading the whole file.

How this fits in the project

The entire CLI is one explicit value pipeline. Pattern matching defines adapters and result categories; streams bound ingestion memory; maps and MapSet support indexes and consumed identifiers; structs give normalized transactions and match evidence stable shapes.

Definitions & key terms

Canonical transaction: Source-independent representation used by matching rules.
Tagged result: A tuple whose first element identifies success, skip, rejection, or another outcome.
Guard: A restricted predicate that refines a pattern-matched function clause.
Minor units: Integer representation of money, such as cents.
Deterministic report: Output whose bytes and ordering are stable for identical inputs.
Ambiguous match: A source row with multiple equally valid candidates.

Mental model diagram

bank.csv ---------> bank adapter ----\
  raw rows          ok/error/skip      \
                                       > canonical transactions
processor.csv ----> processor adapter  /       |
                                               v
                                     deterministic indexes
                                               |
                       +-----------------------+------------------+
                       v                       v                  v
                   exact match            ambiguous          unmatched
                       |                       |                  |
                       +------------- explanation records -------+
                                               |
                                               v
                             matched.csv + review.csv + rejected.csv

How it works

Validate CLI options and require explicit input formats and currency assumptions.
Stream rows with source and line metadata.
Dispatch each row to the selected adapter.
Normalize dates, references, descriptions, and integer minor units.
Partition valid transactions from preserved rejections.
Build duplicate-preserving candidate indexes.
Apply matching rules from strictest to loosest and record evidence.
Verify conservation-of-rows and monetary-total invariants.
Sort every category deterministically and write reports.

Minimal concrete example

normalize row:
  if date, amount, currency, reference are valid
    -> success(canonical transaction)
  else
    -> rejection(source, line, field, reason, original row)

match bank transaction:
  candidates = processor index[reference, currency, amount]
  zero candidates -> unmatched
  one unused candidate -> matched with exact-rule explanation
  several candidates -> ambiguous review item

Common misconceptions

A pipeline does not mean errors disappear; failure values should remain part of the pipeline contract.
Pattern matching validates shape, not every business rule.
A Map cannot represent duplicates if one key is assigned one value.
Floating-point arithmetic is not acceptable merely because exported amounts have two decimal places.

Check-your-understanding questions

Why should adapters produce a canonical struct before reconciliation?
Why does the candidate index map a key to a list?
Which guarantee makes rerunning the command auditable?

Check-your-understanding answers

It isolates source quirks from domain matching and makes rules independently testable.
Real exports can contain duplicate references or repeated amounts; overwriting would destroy evidence.
Deterministic ordering plus conservation and total invariants make identical inputs produce verifiable results.

Real-world applications

The pattern applies to invoice reconciliation, marketplace payouts, inventory imports, migration dry runs, payroll checks, and any integration that must preserve invalid input for human review.

Where you will apply it

You will apply it in adapters, validation, indexes, matching rules, totals, and report rendering. Unlike Projects 1-13, this project is deliberately a pure, finite data transformation: it does not teach GenServer state, supervision recovery, ETS caching, distributed nodes, LiveView, or GenStage.

References

Key insight

When every input row becomes an explicit value and every decision carries evidence, ordinary Elixir transformations become a trustworthy audit system.

Summary

Pattern matching provides visible branches, immutable transformations provide local reasoning, streams bound input work, and deterministic reports turn a small CLI into an auditable reconciliation product.

Homework/Exercises

Define the categories required to account for every row exactly once.
Design a composite match key that remains strict without depending on descriptions.
Explain how an amount parser should treat negative values, thousands separators, and unsupported currencies.

Solutions

Accepted rows should end as matched, unmatched, duplicate, or ambiguous; malformed rows end as rejected; headers are documented skips.
Start with normalized reference, currency, and integer amount; use date tolerance only in a separate rule.
Preserve sign, parse separators according to the declared adapter, and reject currencies whose scale or policy is unknown.

3. Project Specification

3.1 What You Will Build

Build “reconcile”, a CLI that accepts bank and processor CSV files plus explicit format names. It emits a summary and four reports: matched pairs with explanations, unmatched transactions, duplicates/ambiguities, and malformed-row rejections.

Scope boundary: Do not add a web UI, database, distributed worker, ETS cache, or long-running process. This project teaches core Elixir data modeling and transformation and must not become a smaller version of Projects 3, 4, 5, 8, or 10.

3.2 Functional Requirements

Support two documented fixture formats through separate adapters.
Preserve source filename and line number for every data row.
Normalize money into integer minor units and dates into Date values.
Apply at least two ordered matching rules and attach an explanation.
Identify duplicate and ambiguous candidates without overwriting them.
Emit deterministic CSV reports and a human-readable summary.
Exit nonzero for invalid command configuration, but not merely because business rows require review.

3.3 Non-Functional Requirements

Stream raw ingestion and document which structures are materialized.
Never log or print full account numbers; mask configured sensitive columns.
Produce identical report bytes for identical inputs and configuration.
Complete the supplied 100,000-row fixture within a learner-defined budget.

3.4 Example Usage / Output

$ mix reconcile --bank fixtures/bank.csv --processor fixtures/gateway.csv --out tmp/run-001
reconciliation_id=run-001
bank_rows=8 processor_rows=7
matched=5 unmatched_bank=1 unmatched_processor=0
duplicates=2 ambiguous=1 rejected=1
bank_total=154230 processor_total=154230 matched_total=149230 currency=BRL
reports=tmp/run-001/{matched,review,rejected,summary}.csv
status=review_required

3.5 Data Formats / Schemas / Protocols

Canonical transaction fields: stable id, source, line, external reference, booking date, integer amount, currency, normalized description, and masked account suffix. A match record contains both ids, rule id, confidence, and evidence. A rejection contains source, line, field, stable reason code, safe detail, and original column count.

3.6 Edge Cases

Byte-order marks, blank lines, repeated headers, quoted commas, and CRLF input.
Negative refunds and zero-value adjustments.
Duplicate references and two equal payments on the same date.
Missing currency, locale-specific decimal separators, and invalid calendar dates.
One input ending after validation has begun; output must be written atomically or clearly marked incomplete.

3.7 Real World Outcome

3.7.1 How to Run

Prepare the two fixture exports, invoke the Mix task with an empty output directory, and inspect both terminal summary and generated reports. Run again into a second directory and compare checksums.

3.7.2 Golden Path Demo

The fixture contains five exact matches, one date-tolerant match, one duplicate processor reference, one unmatched bank fee, and one malformed amount. The report totals account for every row, and every non-exact decision includes a reason.

3.7.3 Exact Terminal Transcript

$ mix reconcile --bank fixtures/bank.csv --processor fixtures/gateway.csv --out tmp/a
matched=6 review=2 rejected=1 status=review_required
$ mix reconcile --bank fixtures/bank.csv --processor fixtures/gateway.csv --out tmp/b
matched=6 review=2 rejected=1 status=review_required
$ shasum -a 256 tmp/a/*.csv tmp/b/*.csv
6d7c... tmp/a/matched.csv
6d7c... tmp/b/matched.csv
1b2a... tmp/a/review.csv
1b2a... tmp/b/review.csv

4. Solution Architecture

4.1 High-Level Design

CLI options -> adapters -> validation -> canonical lists -> rule engine
                                                       |-> invariant checker
                                                       |-> deterministic reports

4.2 Key Components

CLI options and run configuration
Bank and processor format adapters
Canonical Transaction, Rejection, and Match structs
Rule engine with ordered policies
Conservation/total invariant checker
Deterministic CSV renderer and atomic output writer

4.3 Data Structures

Use maps from composite match keys to lists of transactions, a MapSet of consumed stable ids, lists of rejections with line metadata, and a run summary containing integer totals by currency and category.

4.4 Algorithm Overview

Validate inputs, stream and normalize both files, group candidates without loss, match strict-to-loose while tracking consumed ids, classify leftovers, verify invariants, sort, render to temporary files, and rename outputs only after all files succeed.

5. Implementation Guide

5.1 Development Environment Setup

Use the repository’s supported Elixir/OTP versions. Create a Mix project, add only a well-documented CSV parser if desired, and commit small anonymized fixtures. Verify “mix test” and “mix help reconcile” before implementing rules.

5.2 Project Structure

lib/
  reconcile/cli
  reconcile/adapters/bank
  reconcile/adapters/processor
  reconcile/transaction
  reconcile/rules
  reconcile/report
test/
  fixtures/
  adapters/
  rules/
  reconciliation_test

5.3 The Core Question You’re Answering

How can immutable transformations turn messy financial exports into complete, deterministic, and explainable reconciliation evidence?

5.4 Concepts You Must Understand First

Understand function clauses and guards, structs versus maps, tagged result tuples, Enum versus Stream, integer money, and stable sorting.

5.5 Questions to Guide Your Design

Which source-specific assumptions belong only in adapters?
What is the minimum evidence needed to defend a match?
How will you prove no row disappeared or was matched twice?
Which errors should stop the command, and which should become review records?

5.6 Thinking Exercise

Draw a ledger with ten input row ids. Move each id into exactly one final category and circle every decision that consumes two ids. Now introduce two processor rows with the same reference and amount. Explain why choosing the first row is not a defensible rule.

5.7 The Interview Questions They’ll Ask

When would you use Stream instead of Enum?
How do function clauses and guards improve parser clarity?
Why should money use integers or a decimal type rather than floats?
How would you guarantee deterministic output from map-based indexes?
How does “with” help validation, and when does it become harder to read?
How do you prove a reconciliation conserved all input rows?

5.8 Hints in Layers

Hint 1: Make one adapter pass a five-row fixture before designing matching.

Hint 2: Give every accepted or rejected row a stable source-line identity.

Hint 3: Index candidates to lists and keep a separate consumed-id set.

Hint 4: Compute category counts and integer totals before writing any report; reject the run if conservation fails.

5.9 Books That Will Help

Topic	Book	Chapter
Elixir data transformations	“Programming Elixir >= 1.6” by Dave Thomas	Ch. 5, Anonymous Functions; Ch. 10, Processing Collections
Pattern matching and control flow	“Elixir in Action” by Saša Jurić	Ch. 2, Building Blocks; Ch. 3, Control Flow
Financial domain modeling	“Domain Modeling Made Functional” by Scott Wlaschin	Ch. 4, Understanding Types; Ch. 9, A Complete Example

5.10 Implementation Phases

Phase 1: Foundation (2-3 hours)

Define canonical structs and rejection codes; parse one fixture; add deterministic adapter tests.

Phase 2: Core Functionality (3-4 hours)

Build duplicate-preserving indexes, ordered match rules, consumed-id tracking, totals, and category reports.

Phase 3: Polish & Edge Cases (1-3 hours)

Add masked output, atomic writes, large fixtures, ambiguous cases, help text, and reproducibility checks.

5.11 Key Implementation Decisions

Record decisions about accepted formats, currency scale, date tolerance, rule precedence, ambiguity policy, materialized structures, masking, and exit-code semantics.

6. Testing Strategy

6.1 Test Categories

Adapter unit tests for every row outcome
Table-driven money/date normalization tests
Rule tests for exact, tolerant, ambiguous, and consumed candidates
End-to-end fixture tests with exact report snapshots
Invariant tests for row conservation and totals
Performance test for the large fixture

6.2 Critical Test Cases

Duplicate candidate keys preserve both rows.
An invalid amount becomes a line-specific rejection.
One processor row cannot satisfy two bank rows.
Reversing input order does not change sorted report bytes.
A report write failure does not leave a mixture of final and partial files.
Negative refunds reconcile with sign preserved.

6.3 Test Data

Keep tiny fixtures for one behavior each, one golden mixed fixture, and a generated 100,000-row dataset containing deterministic duplicates and failures. Use fictional names and identifiers.

6.4 Definition of Done

Every input data row is accounted for exactly once.
Money is never represented as floating point.
Exact and tolerant rules produce explanations.
Duplicate and ambiguous rows are preserved for review.
Repeated runs produce byte-identical reports.
Tests cover malformed input, refunds, duplicates, and atomic output.
“mix help reconcile” documents formats, exit codes, and scope.

7. Common Pitfalls & Debugging

7.1 Frequent Mistakes

Rows vanish: A parser filtered errors. Return rejection values and assert conservation.

Duplicates collapse: An index stored one value per key. Store a list and test duplicate fixtures.

Totals differ by cents: Floating-point parsing introduced drift. Parse directly to integer minor units.

Reports reorder: Enumeration order leaked into output. Sort by an explicit stable tuple.

7.2 Debugging Strategies

Run one row through every stage and print only safe shape/reason metadata. Add a reconciliation trace keyed by stable row id. When totals fail, calculate differences per source and category before inspecting individual rows.

7.3 Performance Traps

Repeated list concatenation, rescanning all processor rows for every bank row, materializing raw files before validation, and sorting inside matching loops all create avoidable cost. Index once and sort only final categories.

8. Extensions & Challenges

8.1 Beginner Extensions

Add a JSON summary, configurable date tolerance, and a command that lists supported adapters.

8.2 Intermediate Extensions

Support one-to-many fee aggregation, redact configurable columns, and generate an HTML review report while keeping domain logic renderer-independent.

8.3 Advanced Extensions

Introduce a decimal/currency library, signed run manifests, pluggable rule behaviours, and property tests proving row conservation for generated inputs.

9. Real-World Connections

9.1 Industry Applications

Payment operations, bookkeeping, marketplace settlements, chargeback review, payout verification, and finance migration validation all require explainable transformations over imperfect external data.

Study Elixir’s CSV ecosystem for parsing boundaries and accounting tools such as Beancount or Ledger for ideas about stable financial representations. Do not copy their scope; this project remains a reconciler.

9.3 Interview Relevance

The project demonstrates that a junior Elixir developer can model data explicitly, handle errors without exceptions everywhere, choose lazy versus eager processing, and prove invariants rather than merely print plausible output.