Skip to main content

​
Payment Reconciliation

Payment reconciliation ensures your internal records match Orsunpay’s transaction data and provider settlements. This guide covers best practices for maintaining accurate financial records.

​
Reconciliation Sources

​
1. Orsunpay API Data

Primary source of transaction information:
  • Real-time transaction status
  • Processing fees and amounts
  • Provider mapping and IDs
  • Customer and order details

​
2. Webhook Events

Real-time status updates:
  • Transaction status changes
  • Settlement notifications
  • Refund confirmations
  • Chargeback alerts

​
3. Provider Reports

Direct provider data:
  • Settlement files
  • Fee breakdowns
  • Exchange rates
  • Payout schedules

​
Daily Reconciliation Process

​
Step 1: Fetch Transaction Data

async function getDailyTransactions(date) {
  const startDate = new Date(date);
  startDate.setHours(0, 0, 0, 0);
  
  const endDate = new Date(date);
  endDate.setHours(23, 59, 59, 999);

  const transactions = await orsunpay.transactions.list({
    dateFrom: startDate.toISOString(),
    dateTo: endDate.toISOString(),
    status: 'SUCCESS',
    limit: 100
  });

  return transactions.data;
}

​
Step 2: Compare with Internal Records

async function reconcileTransactions(orsunpayTxns, internalOrders) {
  const discrepancies = [];

  for (const txn of orsunpayTxns) {
    const order = internalOrders.find(o => o.id === txn.orderId);
    
    if (!order) {
      discrepancies.push({
        type: 'missing_internal_record',
        transactionId: txn.id,
        orderId: txn.orderId
      });
      continue;
    }

    // Check amounts match
    if (order.amount !== txn.amount) {
      discrepancies.push({
        type: 'amount_mismatch',
        transactionId: txn.id,
        orderId: txn.orderId,
        internalAmount: order.amount,
        orsunpayAmount: txn.amount
      });
    }

    // Check status alignment
    if (order.status === 'pending' && txn.status === 'SUCCESS') {
      discrepancies.push({
        type: 'status_sync_needed',
        transactionId: txn.id,
        orderId: txn.orderId,
        internalStatus: order.status,
        orsunpayStatus: txn.status
      });
    }
  }

  return discrepancies;
}

​
Step 3: Settlement Reconciliation

async function reconcileSettlements(date) {
  // Get settlement data from Orsunpay
  const settlements = await orsunpay.settlements.list({
    date: date,
    status: 'completed'
  });

  // Compare with expected settlement amounts
  for (const settlement of settlements) {
    const expectedAmount = await calculateExpectedSettlement(
      settlement.merchantId,
      settlement.period
    );

    if (Math.abs(settlement.amount - expectedAmount) > 1) { // 1 cent tolerance
      await flagSettlementDiscrepancy({
        settlementId: settlement.id,
        expected: expectedAmount,
        actual: settlement.amount,
        difference: settlement.amount - expectedAmount
      });
    }
  }
}

​
Handling Discrepancies

​
Amount Mismatches

async function resolveAmountMismatch(discrepancy) {
  const transaction = await orsunpay.transactions.retrieve(
    discrepancy.transactionId
  );
  
  // Check if it's a currency conversion issue
  if (transaction.currency !== transaction.processingCurrency) {
    const convertedAmount = transaction.amount * transaction.currencyRate;
    if (Math.abs(convertedAmount - discrepancy.internalAmount) < 1) {
      return 'currency_conversion_difference';
    }
  }

  // Check if fees are included/excluded differently
  const amountWithFees = transaction.amount + (transaction.feeAmount || 0);
  if (amountWithFees === discrepancy.internalAmount) {
    return 'fee_inclusion_difference';
  }

  return 'manual_review_required';
}

​
Status Synchronization

async function syncTransactionStatus(orderId, orsunpayStatus) {
  const statusMapping = {
    'SUCCESS': 'completed',
    'DECLINED': 'failed',
    'EXPIRED': 'expired',
    'CANCELED': 'cancelled'
  };

  const internalStatus = statusMapping[orsunpayStatus];
  
  await updateOrderStatus(orderId, internalStatus);
  
  // Trigger any status-dependent actions
  if (internalStatus === 'completed') {
    await fulfillOrder(orderId);
    await sendConfirmationEmail(orderId);
  }
}

​
Monthly Reconciliation Report

async function generateMonthlyReport(year, month) {
  const startDate = new Date(year, month - 1, 1);
  const endDate = new Date(year, month, 0, 23, 59, 59, 999);

  const report = {
    period: `${year}-${month.toString().padStart(2, '0')}`,
    summary: {
      totalTransactions: 0,
      totalVolume: 0,
      totalFees: 0,
      successRate: 0,
      refundedAmount: 0,
      chargebackAmount: 0
    },
    byPaymentMethod: {},
    byProvider: {},
    discrepancies: []
  };

  // Fetch all transactions for the month
  const transactions = await fetchAllTransactions(startDate, endDate);
  
  // Calculate summary metrics
  report.summary.totalTransactions = transactions.length;
  report.summary.totalVolume = transactions
    .filter(t => t.status === 'SUCCESS')
    .reduce((sum, t) => sum + t.amount, 0);
  
  report.summary.totalFees = transactions
    .reduce((sum, t) => sum + (t.feeAmount || 0), 0);
  
  report.summary.successRate = 
    transactions.filter(t => t.status === 'SUCCESS').length / 
    transactions.length * 100;

  // Group by payment method
  transactions.forEach(txn => {
    if (!report.byPaymentMethod[txn.paymentMethod]) {
      report.byPaymentMethod[txn.paymentMethod] = {
        count: 0,
        volume: 0,
        fees: 0
      };
    }
    
    const method = report.byPaymentMethod[txn.paymentMethod];
    method.count++;
    if (txn.status === 'SUCCESS') {
      method.volume += txn.amount;
      method.fees += txn.feeAmount || 0;
    }
  });

  return report;
}

​
Automated Reconciliation

​
Schedule Daily Jobs

// Using node-cron for scheduling
const cron = require('node-cron');

// Run daily reconciliation at 2 AM
cron.schedule('0 2 * * *', async () => {
  const yesterday = new Date();
  yesterday.setDate(yesterday.getDate() - 1);
  
  try {
    await performDailyReconciliation(yesterday);
    console.log('Daily reconciliation completed successfully');
  } catch (error) {
    console.error('Daily reconciliation failed:', error);
    await alertFinanceTeam(error);
  }
});

// Run monthly reconciliation on the 1st of each month
cron.schedule('0 3 1 * *', async () => {
  const lastMonth = new Date();
  lastMonth.setMonth(lastMonth.getMonth() - 1);
  
  const report = await generateMonthlyReport(
    lastMonth.getFullYear(),
    lastMonth.getMonth() + 1
  );
  
  await emailMonthlyReport(report);
});

​
Reconciliation Dashboard

async function getReconciliationDashboard() {
  const today = new Date();
  const thirtyDaysAgo = new Date(today.getTime() - 30 * 24 * 60 * 60 * 1000);

  return {
    pendingReconciliation: await getPendingItems(),
    recentDiscrepancies: await getDiscrepancies(thirtyDaysAgo, today),
    reconciliationHealth: await getReconciliationHealth(),
    settlementStatus: await getSettlementStatus()
  };
}

async function getPendingItems() {
  // Items that need manual review
  return await db.reconciliation_items
    .where('status', 'pending_review')
    .orderBy('created_at', 'desc')
    .limit(10);
}

async function getReconciliationHealth() {
  const last7Days = new Date(Date.now() - 7 * 24 * 60 * 60 * 1000);
  
  const totalTransactions = await countTransactions(last7Days);
  const reconciledTransactions = await countReconciledTransactions(last7Days);
  
  return {
    reconciliationRate: (reconciledTransactions / totalTransactions) * 100,
    avgReconciliationTime: await getAvgReconciliationTime(last7Days),
    outstandingDiscrepancies: await countOutstandingDiscrepancies()
  };
}

​
Best Practices

​
1. Implement Idempotent Operations

async function reconcileTransaction(transactionId) {
  const lockKey = `reconcile_${transactionId}`;
  
  // Prevent concurrent reconciliation
  const lock = await acquireLock(lockKey, 300); // 5 minute lock
  if (!lock) {
    throw new Error('Transaction already being reconciled');
  }

  try {
    // Check if already reconciled
    const existing = await db.reconciliation_records
      .where('transaction_id', transactionId)
      .first();
    
    if (existing && existing.status === 'reconciled') {
      return existing;
    }

    // Perform reconciliation
    const result = await performReconciliation(transactionId);
    
    // Save result
    await db.reconciliation_records.insert({
      transaction_id: transactionId,
      status: 'reconciled',
      reconciled_at: new Date(),
      details: result
    });

    return result;
  } finally {
    await releaseLock(lockKey);
  }
}

​
2. Handle Time Zones Consistently

function normalizeToUTC(dateString, timezone = 'UTC') {
  const date = new Date(dateString);
  
  // Always work in UTC for reconciliation
  return new Date(date.getTime() - date.getTimezoneOffset() * 60000);
}

// Use consistent date ranges
function getDayRange(date, timezone = 'UTC') {
  const start = new Date(date);
  start.setUTCHours(0, 0, 0, 0);
  
  const end = new Date(date);
  end.setUTCHours(23, 59, 59, 999);
  
  return { start, end };
}

​
3. Implement Robust Error Handling

async function safeReconciliation(operation, context) {
  const maxRetries = 3;
  let lastError;

  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await operation();
    } catch (error) {
      lastError = error;
      
      // Don't retry certain errors
      if (error.code === 'resource_not_found' || 
          error.status === 401 || 
          error.status === 403) {
        throw error;
      }

      if (attempt < maxRetries) {
        const delay = Math.pow(2, attempt) * 1000; // Exponential backoff
        await new Promise(resolve => setTimeout(resolve, delay));
      }
    }
  }

  // Log failed reconciliation for manual review
  await logFailedReconciliation(context, lastError);
  throw lastError;
}

​
Compliance and Auditing

​
Maintain Audit Trail

async function auditReconciliation(reconciliationData) {
  await db.audit_log.insert({
    event_type: 'reconciliation_performed',
    entity_type: 'transaction',
    entity_id: reconciliationData.transactionId,
    user_id: reconciliationData.performedBy,
    details: {
      reconciliation_status: reconciliationData.status,
      discrepancies_found: reconciliationData.discrepancies,
      resolution_actions: reconciliationData.actions
    },
    timestamp: new Date()
  });
}

​
Financial Controls

async function validateReconciliation(data) {
  const validations = [
    {
      rule: 'amounts_must_balance',
      check: () => data.debits === data.credits,
      message: 'Total debits must equal total credits'
    },
    {
      rule: 'no_duplicate_transactions',
      check: () => new Set(data.transactionIds).size === data.transactionIds.length,
      message: 'Duplicate transactions detected'
    },
    {
      rule: 'settlement_amounts_valid',
      check: () => data.settlementAmount >= 0,
      message: 'Settlement amount cannot be negative'
    }
  ];

  const failures = validations.filter(v => !v.check());
  
  if (failures.length > 0) {
    throw new ReconciliationValidationError(failures);
  }
}
Regular reconciliation is crucial for maintaining accurate financial records and detecting issues early. Automate where possible but always have manual review processes for discrepancies.