Payments, reconciliation, and Rp198 million missing from the report

School Merchandise Sales

A transaction platform for school hubs with Midtrans payments — and the audit that found revenue nobody had been counting.

The problem

Merchandise sales across many school hubs ran through Midtrans payment links and were recorded into a spreadsheet. The reports looked tidy, and that was exactly the problem: nobody had ever reconciled the spreadsheet against what Midtrans actually held.

The user journey

1. A hub staffer opens the platform, picks products, and the system prices them against that hub's own list.
2. Payment routes to that hub's Midtrans link, and proof of payment is photographed from inside the ordering flow.
3. Payment status is pulled from Midtrans every 30 minutes and matched back against the transaction record.
4. The team gets a daily report in their Telegram group, and can ask for any breakdown in plain language.

What was built

A Node.js service wiring together the ordering flow, Midtrans, Google Sheets as an operational store the team can still open themselves, and a Telegram bot for reporting and questions.

Architecture

The hard part

The reconciliation. I pulled the complete history from all 121 Midtrans payment links — each returning records going back to 2024 — and compared it row by row against 8,375 spreadsheet rows.

The outcome

• 137 rows carried the wrong status: 113 had actually settled but were recorded as unpaid, and 23 were recorded as settled when they were not.
• 8 corrupted rows containing only a status column — caused by a row-shift during automated writes — were found and removed.
• One bug meant orders containing more than one item were counted at the price of the first item only. 685 of 1,105 orders were affected, and Rp198,381,500 had never appeared in any report.
• After the fix, a full re-match returned zero discrepancies.

What isn't finished

The routine sync never re-checks rows whose status is already final, so similar corruption in future would still need a manual audit like this one. I record that as a known limit, not a closed problem.

My role

Product, implementation, payment integration, data audit, and operations.

Recorded by

Ihsan Nugraha

Checked

6 Aug 2026, 17:17 WIB

How to verify

Click any URL on this page

Recorded by