Match a payment to a bank row, or explain why not.
A payment reconciliation prototype: CSV import, deterministic AED matching, exception investigation, and an audit trail. A React client sits on a Nest API.
Supplied screenshot. Reconciliation. The rows are the prototype example, not a production volume.
Supplied screenshot. CSV import.
Problem
A payment and a bank settlement can share a reference and still disagree on amount, date, or identity. Someone has to see the discrepancy without rewriting the source rows.
Intended users
A seeded analyst and a different seeded approver inside one demo workspace. This is not a multi-tenant production deployment.
Role
Personal project. The local commit history is under asghar002345, including the React import and reconciliation screens, client sign-in, and the Render deploy setup. The Nest API, matcher, and investigations are in the same repository.
Implemented scope
One workspace, one AED provider account, payments and bank CSVs up to about 1000 rows and 1 MB, then a reconciliation run and an investigation that a different user approves.
Workflows
Sign in. The API takes the user and role from the token. A role sent in the body is ignored.
Import a payments CSV and a bank CSV. Every row is validated first. One invalid row publishes nothing. Importing the same file hash again does not add canonical rows.
Run reconciliation. Results are MATCHED, AMOUNT_MISMATCH, PAYMENT_WITHOUT_BANK_ENTRY, BANK_ENTRY_WITHOUT_PAYMENT, AMBIGUOUS, or OUTSIDE_SETTLEMENT_WINDOW.
Open an investigation, propose a resolution, and have a different user approve or reject it. The audit event commits with the decision. Source amounts stay as imported.
Architecture
01
React client
Vite pages for sign-in, payments, CSV import, reconciliation, and investigation detail. Routes require a session.
Responsibility
Show the workspace work
Concern
Do not trust a role sent by the browser
02
Nest API
Validates input, reads the caller from the token, and writes payments, bank entries, matches, and investigations.
Responsibility
Hold the transaction boundary
Concern
Constraints still belong in the database
03
PostgreSQL
AED amounts are BIGINT fils. Source ids are unique inside the workspace and provider account. One active match per payment and per bank entry.
Responsibility
Keep evidence and decisions
Concern
Approval must not rewrite imported amounts
04
Worker
Outbox and BullMQ process accepted webhooks and async jobs. The worker does not invent a bank settlement.
Responsibility
Finish work after the request returns
Concern
The same event id has one business effect
Decisions
Money is integer fils
AED is stored as BIGINT fils, 10000 for 100.00. Expected net is gross minus fee. The teaching case is 100.00 gross, 3.00 fee, and a bank row of 94.00: a 3.00 shortfall, not a match. Approval records a decision and does not change the stored amounts.
Ambiguous rows are not auto-picked
If more than one payment or bank row shares a normalized reference, the result is AMBIGUOUS and the candidates stay visible. The matcher does not keep the first row.
The proposer cannot approve
The analyst proposes. A different approver decides. A stale update fails a version check. The decision and the audit row commit together, and audit rows are append-only for the application role.
What holds
Decision
Deterministic one-to-one matching on workspace, provider account, normalized reference, and AED. A bank row counts only on the payment’s UTC date or the next three calendar days. Fuzzy matching, multi-currency conversion, and live bank APIs stay out of this prototype.
Failure handling
An invalid CSV publishes no batch. A repeated file does not duplicate canonical rows. Two concurrent decisions do not both succeed. A resolved investigation remains an amount mismatch in the source data.
Evidence
docs/SPEC.md, the Nest modules, and the Vite pages for login, payments, import, reconciliation, and investigations. Recent commits touch the client flows and the Render blueprint.
Status
The prototype can import, reconcile, investigate, and record an approval. The public sign-in page is https://reconcile-ops.vercel.app/login.
Limits
The linked URL is the public sign-in page. The import and reconciliation images were supplied for this portfolio. Counts and timestamps printed on them are not production volume. The GitHub remote was not confirmed public, so it is omitted. Multi-tenant administration, fuzzy matching, live bank APIs, and OCR are planned, not shipped.