Payment Reconciliation Architecture: Designing for Provider and Database Drift

In distributed financial engineering, your database state and the payment provider’s state will eventually drift apart. Network dropouts, dropped webhooks, database transaction rollbacks, and server crashes during checkout mean that a customer may be successfully debited while your system marks the transaction as Pending or Failed.

Relying solely on real-time webhooks or front-end redirect callbacks is insufficient. A production-grade payment architecture requires an automated, background Reconciliation System to detect, repair, and audit state drift.

---

1. The Reality of State Drift

Distributed state drift occurs in two primary directions:

Divergence Type A: Provider Charged, Database Pending/Failed

Customer Debited by Gateway ──→ Webhook Dropped / Network Timeout ──→ Database Status: PENDING

• Impact: Customer loses money but receives no order fulfillment or subscription access. High support cost and reputational damage.
• Remediation: The reconciliation engine queries the provider’s verification API, detects success, and atomically executes state repair and fulfillment.

Divergence Type B: Database Marked Completed, Provider Unconfirmed / Refunded

Database Status: COMPLETED ──→ Provider Charge Chargeback / Reversal ──→ Gateway Status: REVERSED

• Impact: System grants access or ships goods without retaining funds. Financial loss.
• Remediation: Reconciliation detects gateway status reversed or refunded, flags the transaction for manual review, and revokes access.

---

2. Reconciliation System Architecture

A robust payment reconciliation system operates as a scheduled background worker (e.g., ASP.NET Core BackgroundService, Quartz.NET, or AWS Lambda scheduled task).

graph TD
    A[Cron Schedule / Hourly Trigger] --> B[Fetch Unresolved 'Pending' Transactions > 15 mins old]
    B --> C{Transactions Found?}
    C -->|No| D[Log Clean Audit Run]
    C -->|Yes| E[For Each Transaction: Query Provider Verification API]
    E --> F{Provider Status?}
    F -->|Success| G[Execute Atomic State Repair & Fulfillment]
    F -->|Failed / Abandoned| H[Mark Database Status Failed]
    F -->|Still Pending / Unverified| I[Schedule Secondary Polling Window]
    G --> J[Record Reconciliation Audit Trail Entry]
    H --> J

---

3. Data Model for Audit Trails & State History

Never overwrite transaction status in place without preserving historical state transitions. Financial systems require immutable State Transition Audit Logs.

namespace Tofali.Payments.Reconciliation;

public enum TransactionState
{
    Pending = 1,
    Processing = 2,
    Completed = 3,
    Failed = 4,
    ReconciledRepair = 5,
    ManualReviewRequired = 6
}

public sealed class PaymentAuditRecord
{
    public Guid Id { get; set; } = Guid.NewGuid();
    public string TransactionReference { get; set; } = string.Empty;
    public TransactionState PreviousState { get; set; }
    public TransactionState NewState { get; set; }
    public string TriggerSource { get; set; } = string.Empty; // "Webhook", "ReconciliationJob", "ManualAdmin"
    public string? ExternalProviderStatus { get; set; }
    public DateTimeOffset TransitionTimestamp { get; set; } = DateTimeOffset.UtcNow;
    public string Description { get; set; } = string.Empty;
}

---

4. Periodic Reconciliation Worker Implementation

The background job queries transactions that have been in Pending or Processing status for longer than the expected checkout SLA (e.g., 15 minutes) and polls the provider’s official server-side verification endpoint:

namespace Tofali.Payments.Reconciliation;

using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;

public sealed class PaymentReconciliationWorker : BackgroundService
{
    private readonly IServiceProvider _serviceProvider;
    private readonly ILogger<PaymentReconciliationWorker> _logger;
    private static readonly TimeSpan CheckInterval = TimeSpan.FromMinutes(15);

    public PaymentReconciliationWorker(
        IServiceProvider serviceProvider,
        ILogger<PaymentReconciliationWorker> logger)
    {
        _serviceProvider = serviceProvider;
        _logger = logger;
    }

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        _logger.LogInformation("Payment Reconciliation Worker started.");

        using var timer = new PeriodicTimer(CheckInterval);
        while (await timer.WaitForNextTickAsync(stoppingToken) && !stoppingToken.IsCancellationRequested)
        {
            try
            {
                await PerformReconciliationRunAsync(stoppingToken);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Unhandled exception during payment reconciliation execution run.");
            }
        }
    }

    private async Task PerformReconciliationRunAsync(CancellationToken cancellationToken)
    {
        _logger.LogInformation("Starting scheduled payment reconciliation run at {Timestamp}", DateTimeOffset.UtcNow);

        // Resolve scoped database and provider verification services
        using var scope = _serviceProvider.CreateScope();
        var reconciliationEngine = scope.ServiceProvider.GetRequiredService<IReconciliationEngine>();

        var report = await reconciliationEngine.ReconcilePendingTransactionsAsync(
            cutoffTime: DateTimeOffset.UtcNow.AddMinutes(-15),
            cancellationToken: cancellationToken);

        _logger.LogInformation(
            "Reconciliation run completed. Audited: {Audited}, Repaired: {Repaired}, Flagged for Review: {Flagged}",
            report.TotalAudited, report.TotalRepaired, report.TotalFlaggedForReview);
    }
}

---

5. Architectural Principles for Reconciliation

1. Deterministic Verification APIs: Query canonical server-side APIs:
   • Paystack: GET https://api.paystack.co/transaction/verify/{reference}
   • Flutterwave: GET https://api.flutterwave.com/v3/transactions/{id}/verify
   • Monnify: https://docs.monnify.com (Transaction Status API)
2. Exponential Polling Backoff: If a transaction remains Pending at the gateway, poll at 15 minutes, 1 hour, 6 hours, and 24 hours before declaring it expired/abandoned.
3. Manual Review Queue: Transactions with discrepancies in amounts (e.g. partial payment) or unexpected currency mismatches must enter a ManualReviewRequired queue rather than auto-repairing.
4. Idempotency Guard Integration: State repairs performed by reconciliation must call the same idempotent domain fulfillment handlers as webhook handlers to avoid double-fulfillment.

---

6. Official Gateway References

• Paystack Transaction Verification: https://paystack.com/docs/api
• Flutterwave Verification Reference: https://developer.flutterwave.com/docs/integration-guides/webhooks/
• Monnify Transaction Status API: https://docs.monnify.com
• Last Tested Date: August 22, 2026 UTC