Reconciliation CSV

Prerequisites

• Role with payments:read (finance is the day-to-day user).

Every payment rail publishes a periodic settlement report. Reconciliation is comparing our transaction ledger against theirs and flagging any diffs. TwistCue supports CSV upload per rail; the API auto-parses and produces a per-day diff report.

Where

Payments → Reconciliation (/payments/reconciliation).

Uploading

1. Pick the rail: apple, google, card_web, dlocal_*, pawapay_*, vas_*.
2. Drag the provider CSV into the drop zone.
3. The API parses in-place (streaming), matches rows to Purchase by transaction ID.
4. A report renders inline:

Section	Contents
Matched	Every row that lines up (our amount, their amount, match ✓).
Missing on our side	Rows in the provider CSV we have no record of. Usually: dropped webhooks. Action: manually replay via the transaction detail page.
Missing on their side	Rows we have that the provider doesn’t. Usually: refunded/reversed since the webhook. Action: check refund status.
Amount mismatch	Same transaction ID, different amount. Usually: FX conversion drift. Action: log a discrepancy for the P&L.

Daily rollups

Finance runs recon weekly typically, but the rollup_revenue_daily table is built by the rollups-hourly cron regardless. That gives us:

• gross_cents, net_cents, refunds_cents, tx_count per (day, rail, country).

Compare the rollup totals against the provider CSV’s daily totals as a first-pass sanity check before drilling into per-transaction diffs.

Supported CSV shapes

The parser is per-rail and expects specific column headers:

• Apple: transaction_id, original_transaction_id, product_id, price, currency, refund_flag, settlement_date
• Google: order_id, product_id, charged_amount, merchant_currency, transaction_type
• dLocal: payment_id, merchant_ref, amount_paid, currency_paid, country, status, date_paid
• pawaPay: deposit_id, customer_msisdn, amount, currency, status, created_at
• Card MID / VAS: custom per provider: see the CSV format spec on the upload page.

Discrepancies workflow

For each amount mismatch or missing-on-our-side row:

1. Click the row to see both sides.
2. Determine root cause:
   • Dropped webhook: trigger a manual replay (planned feature). Interim: hit the provider’s transaction lookup API and manually reconcile.
   • FX drift: a few cents off. Accept if within tolerance (finance sets the threshold).
   • Refund not yet propagated: wait a day and re-upload.
3. Mark the row as resolved with a note. The note is stored on the recon session record.

The economy monitor

Analytics → Economy (/analytics/economy) shows coin flow and complements reconciliation:

• Coins minted (from purchases + promos + comps)
• Coins burned (from unlocks)
• Bonus liability: outstanding bonus coins that could be spent (finance treats this as a soft liability on the balance sheet)

See Economy monitor guide.

API calls

Action	Endpoint
Upload CSV	POST /v1/admin/payments/reconciliation (multipart)

Full details in API: Payments.