Multi-channel
e-commerce reconciliation.

1. Project Context & Industry Background

E-commerce sellers listing across multiple digital marketplaces (Amazon, Flipkart, Shopify, Myntra) process thousands of individual orders daily. Managing accounting for these transactions is exceptionally complex. Marketplaces settle payments in bulk batches every 7 to 14 days, bundling order payouts while deducting warehouse storage, referral commissions, dynamic shipping costs, return processing fees, and advertising click costs.

For sellers, manual reconciliation is impossible. Discrepancies like missing customer returns (where a refund is issued but the item is never returned to the warehouse), shipping weight overcharges, and unpaid order settlements often go unnoticed, directly impacting operating margins. AutoReconcile sought to build a SaaS portal allowing sellers to upload raw marketplace spreadsheets and instantly receive a transaction-by-transaction reconciliation audit, flagging claims, and calculating true net profitability.

2. Operational & Database Challenges

Designing a high-throughput, multi-sheet reconciliation tool introduced significant engineering challenges:

• Varying Spreadsheet Layouts: Each marketplace modifies its report formats regularly. The system required a flexible parser engine that could automatically detect target data headers without breaking.
• Memory and Scale Management: Sellers frequently upload ledger sheets containing millions of rows, exceeding standard server memory boundaries and causing database bottlenecks.
• Credit-Based SaaS Billing: The SaaS required a transactional billing ledger. Sellers had to be charged per row processed (e.g., ₹0.25 per order), requiring transactional checks before file parsing begins.
• Discrepancy Matching Logic: The database had to cross-reference multiple event logs (Order timestamps, Payment settlement references, Return arrival scans, and Ad spend clicks) to isolate billing errors.

3. System Architecture & Technical Implementation

Hedonist Solutions engineered a high-availability full-stack application (React Web + React Native Mobile + FastAPI + MongoDB) optimized for spreadsheet processing:

// Asynchronous FastAPI Reconciliation Engine

The backend utilizes a Python FastAPI framework. When a seller uploads files (Payment, Order, Ad, and Return sheets), the API streams the files directly to private AWS S3 storage. The backend triggers a worker process that reads the files using an optimized pandas parser. To prevent memory allocation errors, large sheets are parsed in stream chunks (using openpyxl read-only modules).

// Deduplication & Column Mapping Algorithms

We built a mapping algorithm that converts column headers into standardized keys. The engine parses the headers, compares them against a synoynm directory, and maps the columns dynamically. The matching engine then consolidates transactions using the unique Order ID as the primary key, matching the expected sale amount against the actual settled bank payout:

Any discrepancy—such as a shipping fee higher than expected based on package weight registries—is logged in a 'disputes' collection, allowing the seller to export a pre-formatted claims spreadsheet.

// Credit-Based SaaS Billing System

We implemented a credit ledger table in MongoDB. Each seller account contains a wallet document. Before the parser worker begins processing, it calculates the row count of the uploaded order sheet and verifies that the wallet balance has enough credits (0.25 credits required per order line). The credit deduction is committed atomically, protecting against concurrent file upload exploits.

4. Business Outcomes