Transaction Reconciliation

Guide to reconciling transactions between your system and SePay to ensure no transactions are missed.

Why is transaction reconciliation needed?

When integrating SePay Webhooks, your system receives transactions in real-time. However, in some cases webhooks may not reach your system:

Your server is temporarily down (downtime, deploy, restart)
Network errors between SePay and your server
Webhook timeout (server takes too long to process)
Maximum retry attempts exceeded (7 retries)

To ensure no transactions are missed, you need to perform periodic reconciliation by calling the Transaction API and matching with the data in your database.

Reconciliation Process

1. Fetch transactions from SePay

Call the API to get the list of transactions within the reconciliation period:

GET

https://my.sepay.vn/userapi/transactions/list

transaction_date_minstring

Start time, format YYYY-MM-DD HH:mm:ss

transaction_date_maxstring

End time, format YYYY-MM-DD HH:mm:ss

account_numberstring

Filter by bank account number (optional)

limitinteger

Maximum number of transactions returned (default: 5000)

Example: Fetch all transactions on 01/03/2026

cURL

curl -X GET "https://my.sepay.vn/userapi/transactions/list?transaction_date_min=2026-03-01%2000:00:00&transaction_date_max=2026-03-01%2023:59:59" \
-H "Content-Type: application/json" \
-H "Authorization: Bearer YOUR_API_TOKEN"

2. Match with your database

Compare the transaction list from SePay with the transactions stored in your database. Use the id field (SePay transaction ID) to identify which transactions are missing.

3. Add missing transactions

For transactions that exist on SePay but not in your database, save them and process the corresponding business logic (e.g., update order status).

Sample Reconciliation Code

<?php
$sepayToken = 'YOUR_API_TOKEN';
 
// Connect to database
$conn = new mysqli('localhost', 'webhooks_receiver', 'EL2vKpfpDLsz', 'webhooks_receiver');
if ($conn->connect_error) {
    die('MySQL connection failed: ' . $conn->connect_error);
}
 
// Fetch transactions from SePay in the last 24 hours
$dateMin = date('Y-m-d H:i:s', strtotime('-24 hours'));
$dateMax = date('Y-m-d H:i:s');
 
$url = 'https://my.sepay.vn/userapi/transactions/list?'
    . http_build_query([
        'transaction_date_min' => $dateMin,
        'transaction_date_max' => $dateMax
    ]);
 
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_HTTPHEADER, [
    'Content-Type: application/json',
    'Authorization: Bearer ' . $sepayToken
]);
$response = curl_exec($ch);
curl_close($ch);
 
$data = json_decode($response, true);
$sepayTransactions = $data['transactions'] ?? [];
echo count($sepayTransactions) . " transactions from SePay\n";
 
// Get saved reference_numbers
$result = $conn->query(
    "SELECT reference_number FROM tb_transactions WHERE created_at >= '{$dateMin}'"
);
$existingRefs = [];
while ($row = $result->fetch_assoc()) {
    $existingRefs[$row['reference_number']] = true;
}
 
// Find and add missing transactions
$missingCount = 0;
foreach ($sepayTransactions as $tx) {
    if (!isset($existingRefs[$tx['reference_number']])) {
        echo "Missing: ID={$tx['id']}, amount={$tx['amount_in']}, ref={$tx['reference_number']}\n";
 
        $stmt = $conn->prepare(
            'INSERT INTO tb_transactions
                (gateway, transaction_date, account_number, sub_account,
                 amount_in, amount_out, accumulated, code,
                 transaction_content, reference_number, body)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
        );
        $stmt->bind_param('ssssdddssss',
            $tx['bank_brand_name'],
            $tx['transaction_date'],
            $tx['bank_account_id'],
            $tx['bank_sub_acc_id'],
            $tx['amount_in'],
            $tx['amount_out'],
            $tx['accumulated'],
            $tx['code'],
            $tx['transaction_content'],
            $tx['reference_number'],
            $tx['description']
        );
        $stmt->execute();
        $missingCount++;
    }
}
 
echo "Done. Added {$missingCount} transactions.\n";
$conn->close();
?>

Tip

Set up a cron job or scheduled task to run reconciliation automatically, for example every hour or every day.

Bash

# Run reconciliation every hour (crontab)
0 * * * * node /path/to/reconcile.js >> /var/log/reconcile.log 2>&1

Reconciliation Strategies

Time-range based reconciliation

Suitable for periodic reconciliation (hourly, daily). Use transaction_date_min and transaction_date_max to fetch transactions within a fixed time range.

Reconciliation using `since_id`

Suitable for continuous reconciliation. Save the last processed transaction ID, then use the since_id parameter to only fetch newer transactions.