How do I reconcile MonCash transactions with my orders at the end of the day?

Question

Our accountant compares the MonCash business statement with our sales report every evening, and they never quite match. What's a reliable reconciliation process?

Accepted Answer

Mismatches usually come from four places: payments you never confirmed (customer closed the tab), orders you marked paid without a real payment, fees, and timezone cut-offs.

1. Store MonCash's view of every payment, not just your own

CREATE TABLE moncash_payments (
  transaction_id  text PRIMARY KEY,
  reference       text NOT NULL,          -- the orderId you sent
  amount_htg      numeric(12,2) NOT NULL, -- as reported by MonCash
  payer           text,                   -- phone number (see privacy note)
  status          text NOT NULL,
  seen_at         timestamptz NOT NULL DEFAULT now()
);

2. Run a sweeper every few minutes: for each attempt still PENDING after ~10 minutes, call RetrieveOrderPayment. That catches payments whose customer never came back.

3. Nightly report, in one timezone (America/Port-au-Prince):

SELECT o.id, o.total_htg, m.amount_htg, m.transaction_id,
       CASE
         WHEN m.transaction_id IS NULL AND o.status = 'PAID' THEN 'PAID WITHOUT MONCASH RECORD'
         WHEN m.transaction_id IS NOT NULL AND o.status <> 'PAID' THEN 'MONEY RECEIVED, ORDER NOT PAID'
         WHEN m.amount_htg <> o.total_htg THEN 'AMOUNT MISMATCH'
       END AS problem
FROM orders o
FULL JOIN payment_attempts a ON a.order_id = o.id
FULL JOIN moncash_payments m ON m.reference = a.id
WHERE (o.created_at AT TIME ZONE 'America/Port-au-Prince')::date = CURRENT_DATE - 1;

Rows with a problem are what a human reviews. Everything else should match the portal statement, apart from any fees, which you record separately rather than subtracting from order totals.

1 Answer