Recon — The Reconciliation Engine

Every payments company in India wakes up to the same question at 7am: does the bank’s record of yesterday match ours, to the paisa? When it doesn’t, the gap is real money someone must explain before the day’s first standup. Recon is the engine that answers that question — and building it is your first portfolio project that solves a problem a company literally pays a team to solve every single morning.

The Real Problem

A payment aggregator processes 80,000 transactions on a normal Tuesday. The bank sends back a settlement file the next morning — a CSV with its own column names, its own date format, amounts in rupees-with-decimals. The aggregator’s own ledger sits in PostgreSQL, in paise. Finance needs to know, before 9am: which transactions match, which the bank has but the ledger doesn’t (money received but not recorded), which the ledger has but the bank doesn’t (recorded but never settled), which got recorded twice, and which match by id but differ by amount — even by one paisa.

Get this wrong and the company either loses money it can’t trace or reports numbers it can’t defend to an auditor. The people who own this are titled Reconciliation Engineer, Finance Engineering, or just Backend Engineer on a payments team — and “I built a reconciliation engine” is a sentence that makes a fintech recruiter lean forward, because it is the unglamorous core of the business.

What You Are Building

A batch engine — Java 21, Spring Boot, PostgreSQL, the splitease-api stack — that ingests two transaction feeds, matches them, classifies every discrepancy, and produces a report finance can act on. It runs from the command line (or a single endpoint), is safe to re-run, and is fast enough to chew through six figures of rows without flinching.

Non-goals — write these down, they are half the design. No UI: finance consumes a CSV and a summary, nothing more. No real bank API: you feed it files, which is exactly how real recon runs. No auto-fixing: Recon finds and classifies discrepancies; deciding what to do about each is a human (or a downstream) call. A tool that silently “corrects” a money mismatch is a liability, not a feature — and saying that in an interview signals you understand the domain.

Why This Lands Jobs

• A hiring manager reads “reconciliation engine” and sees someone who has touched the actual problem their company is built around — not another CRUD app.
• It feeds the data-handling interview round (“how would you process a messy 1M-row file?”), the complexity round (the matching story below), and the correctness round (“how do you make a batch job safe to re-run?”).
• The proof artifact is concrete: a measured throughput number and a sample exceptions report you can show on screen.
• Honest limits: this is batch, single-machine, files-on-disk. It does not prove streaming, distributed processing, or real bank-integration quirks. Say “I built the core correctly and I know where it gets harder at scale” — that is the stronger claim.

Concepts It Cements

This project is the exam for half the site, and it cashes in the parts about handling data correctly.

Design It Yourself First

Answer these in writing before reading the reference design. This is the part AI cannot do for you, and the part interviews actually test.

1. What is your match key when a bank row’s reference is blank or malformed? Do you drop it, bucket it as unmatchable, or fuzzy-match?
2. One pass or two? Do you load both feeds fully, or stream one against a map of the other — and which feed becomes the map?
3. Where do duplicates hide? The same reference can appear twice in the bank file, twice in the ledger, or once in each. Are those the same case or three different ones?
4. What exactly makes a re-run idempotent? What do you key a “run” on so the same files produce no new state?
5. Can you hold 1,000,000 rows in a HashMap? Roughly how much memory is that, and what is your plan when one feed is too big to hold?
6. An amount-mismatch of 1 paisa on a matched reference — is that a “miss” or a “match with a warning”? Your answer is a product decision; state it.

Do not scroll past this without written answers. The reference design is here to check your thinking, not replace it.

The Reference Design

The pipeline is four honest stages, and the second one is the stage beginners skip and seniors obsess over.

flowchart TD
    A["bank CSV"] --> N["normalize: dates, amounts, encoding"]
    L["ledger rows from PostgreSQL"] --> N
    N --> M["match: build map from smaller feed, stream the larger"]
    M --> C["classify into 5 buckets"]
    C --> R["report: exceptions CSV plus summary"]
    C --> P["persist run, keyed by input hash"]

Data model sketch:

CREATE TABLE recon_run (
  id BIGSERIAL PRIMARY KEY,
  input_hash TEXT NOT NULL UNIQUE,     -- idempotency lives here
  ran_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  matched_count INT, exception_count INT
);
CREATE TABLE recon_exception (
  run_id BIGINT NOT NULL REFERENCES recon_run(id),
  reference TEXT,
  bucket TEXT NOT NULL,                 -- missing_in_bank | missing_in_ledger | duplicate | amount_mismatch
  bank_paise BIGINT, ledger_paise BIGINT
);

The two hardest correctness problems, called out:

• Duplicates on both sides. A reference appearing twice in each feed is matched (pair them off), but twice in only one feed is a duplicate exception. Decide the pairing rule explicitly — first-to-first by timestamp is defensible.
• Amount drift. Same reference, amounts differ by a paisa. This is its own bucket (amount_mismatch), not a miss — because the transaction happened, the recording is just wrong, and that is a completely different investigation for finance.

Build Plan

1. Schema + walking skeleton. Create the two tables; a main that reads a tiny hand-made bank CSV and a seeded ledger and prints “matched N”. Done when: it runs end to end on 5 rows.
2. The normalizer, with ugly inputs. Parse mixed date formats and 1,250.00-style amounts to paise BIGINT; write tests for the nasty cases (trailing spaces, empty reference, negative amounts). Done when: every ugly-input test passes.
3. The matcher at scale. Build the map-and-stream match; seed a 100k-row ledger with generate_series, export a matching bank CSV, and measure the match time — your first resume number. Done when: 100k×100k matches in seconds, timed with System.nanoTime.
4. The classifier + exceptions CSV. Implement all five buckets including the duplicate and amount-drift edge cases; write the exceptions CSV. Done when: a deliberately corrupted bank file produces the right counts in each bucket.
5. Idempotent runs + summary. Hash the input, skip or no-op on a repeat, persist the run and exceptions, print the summary table. Done when: running the same files twice creates exactly one recon_run.
6. Package + document. A runnable CLI/job, a README that takes a stranger from clone to a real run, and a demo script. Done when: the README alone gets someone to a clean run.

Definition of Done

• Handles a genuinely messy bank CSV (mixed dates, comma amounts, blank fields) without crashing
• All five buckets correct, including duplicates-both-sides and 1-paisa drift
• Money is BIGINT paise everywhere; the normalizer never touches double
• Re-running identical inputs creates no new state (one recon_run)
• A measured throughput number (rows matched per second) from a real 100k run
• README with a demo: run it, show the summary and a sample exceptions CSV

Stretch Goals

• Fuzzy matching when references are missing — match on amount + date window (forces a careful precision/recall conversation).
• A web view of the latest run’s exceptions (forces a read-only endpoint and pagination — SQL capstone patterns).
• Multi-day runs with a trend of exception counts over time (forces date_trunc aggregation).

Resume Bullets It Generates

Fill the placeholders from your own measured runs — never claim a number you did not measure.

• Built a reconciliation engine in Java/Spring Boot that matches and classifies N transactions across two feeds in M seconds, using an O(n) hash-map match in place of a naive O(n²) scan.
• Implemented a five-way discrepancy classifier (missing, duplicate, amount-drift) with paise-exact BIGINT arithmetic, producing an auditable exceptions report.
• Designed the batch to be idempotent via input-content hashing, making re-runs safe — a correctness property finance teams depend on.
• Handled real-world messy input (mixed date formats, comma-separated amounts, malformed references) through a dedicated normalization layer with full test coverage.

Interview Stories It Seeds

• “Tell me about handling messy data.” → Situation: bank files with inconsistent formats. Action: a dedicated normalization layer converting everything to one internal shape before any matching, tested against the ugly cases. Result: the matcher stayed simple because the mess was contained upstream.
• “How did you make a batch job safe to re-run?” → keyed each run on a hash of the input bytes; a repeat is a no-op. The honest framing: at-least-once execution is a fact of life, so the job must be idempotent.
• “You’re matching 100k against 100k — walk me through the complexity.” → the O(n²) nested-loop version and why it dies; building a HashMap from the smaller feed and streaming the larger for O(n); the memory trade-off and what you’d do if neither feed fit in memory.

Project started? Add it to today’s tracker