Inspiration

Payment dashboards can look convincing while counting a payment twice, retaining an old pending state, dropping an invalid amount, or mixing currencies. LedgerLens makes those failure modes visible and teaches the engineer how to investigate them.

What it does

LedgerLens is a payment-data investigation workspace. It preserves original reporting payloads, tracks repeated observations, validates records, resolves current states, and withholds conflicting latest values. The browser app shows activity by currency, searchable transactions, source-version timelines, quarantine reasons, SQL checks, and pipeline run history.

Each refresh builds and tests a separate warehouse before publication. A failed refresh leaves the previous passing dataset available. A replay increases observation counts without inventing another payment.

The NVIDIA-powered investigator explains aggregate SQL evidence with that evidence visible beside its answer. It cannot execute SQL, edit data, or initiate payments.

How we built it

Python extracts PayPal sandbox Transaction Search pages using OAuth. DuckDB stores source history and analytical results. Set-based SQL ingestion replaces per-record database calls while preserving validation. dbt models identify ambiguity, choose the latest unambiguous state, and calculate daily activity separately for each currency. Three SQL checks gate publication. Parquet supplies a typed export.

FastAPI serves a responsive HTML/CSS/JavaScript interface with bounded parameterized queries and local-origin protection. NVIDIA NIM runs Nemotron 3.5 Lightning remotely. Credentials stay encrypted locally and are excluded from public source.

Challenges we ran into

A payment observation is not the same thing as a transaction. Same-time conflicting values cannot safely be resolved by arbitrary tie-breaking. Windows database readers also needed read-only connections so refreshes could copy immutable warehouse versions safely. We tested that reads remain available during a refresh and that failed builds preserve the published dataset.

AI output also needed careful evaluation: a plausible explanation can speculate about causes not established by the source. The investigator receives explicit evidence scope and rejection reasons, while figures remain SQL's responsibility and the evidence stays inspectable.

Accomplishments

The reproducible 30-day demonstration contains 1,444 observations, 1,264 distinct payloads, eight quarantined records, one ambiguous latest key, and 1,199 current records after ambiguity is excluded. Sixteen Python tests, three SQL checks, and end-to-end HTTP behaviors pass. A fresh Windows clone/environment and an independent clean Linux CI build also pass.

A same-input, fresh-database ingestion microbenchmark verified identical normalized records and quarantine outputs: the original per-row method took 21.2549 seconds and set-based insertion took 0.0515 seconds. This is a small ingestion-only comparison, excluding dbt, disk and API collection, not an application-wide speedup claim.

Current integration status

Actual PayPal sandbox authentication and reporting access are verified. The new sandbox account currently returns zero reporting transactions, so the populated demo is explicitly synthetic; it is not presented as real customer activity. Actual NVIDIA inference is verified with the owner's account.

LedgerLens does not claim production accounting, revenue calculation, settlement reconciliation, FX conversion or fraud detection. Completed signed activity remains separate by currency.

What we learned and what's next

Source preservation, explicit data contracts, ambiguity handling, and tested publication matter more than a long list of services. Next we will validate nonzero sandbox activity through reporting, expand merchant/event key validation, and use measured workload needs to decide when incremental dbt models or scheduled extraction are justified.

AI assistance and source

Codex assisted research, implementation, testing, documentation, and submission preparation. Runtime explanations use NVIDIA NIM. The source is MIT-licensed at https://github.com/BharathGelivi/ledgerlens, with setup instructions, reproducible synthetic inputs, and automated verification.