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.

​Payment Reconciliation

​Reconciliation Sources

​1. Orsunpay API Data

​2. Webhook Events

​3. Provider Reports

​Daily Reconciliation Process

​Step 1: Fetch Transaction Data

​Step 2: Compare with Internal Records

​Step 3: Settlement Reconciliation

​Handling Discrepancies

​Amount Mismatches

​Status Synchronization

​Monthly Reconciliation Report

​Automated Reconciliation

​Schedule Daily Jobs

​Reconciliation Dashboard

​Best Practices

​1. Implement Idempotent Operations

​2. Handle Time Zones Consistently

​3. Implement Robust Error Handling

​Compliance and Auditing

​Maintain Audit Trail

​Financial Controls

Payment Reconciliation

Reconciliation Sources

1. Orsunpay API Data

2. Webhook Events

3. Provider Reports

Daily Reconciliation Process

Step 1: Fetch Transaction Data

Step 2: Compare with Internal Records

Step 3: Settlement Reconciliation

Handling Discrepancies

Amount Mismatches

Status Synchronization

Monthly Reconciliation Report

Automated Reconciliation

Schedule Daily Jobs

Reconciliation Dashboard

Best Practices

1. Implement Idempotent Operations

2. Handle Time Zones Consistently

3. Implement Robust Error Handling

Compliance and Auditing

Maintain Audit Trail

Financial Controls