Financial Reconciliation & Settlement Architecture

---

1. Executive Summary & Core Financial Invariants

Debelu operates as an escrow financial intermediary under Nigerian regulatory standards (CBN Guidelines on Electronic Payments). Because the platform manages real funds originating from buyers and terminating in vendor commercial bank accounts via Paystack, absolute ledger integrity is non-negotiable.

The platform enforces three mathematical invariants across every financial transaction:

Invariant 1: The Macro Balance Sheet

$$\text{Assets (Physical Paystack Settlement Balance)} = \text{Liabilities (Escrow Balances} + \text{Vendor Available Wallets)} + \text{Equity (Retained Platform Commissions)}$$

Invariant 2: Zero Unallocated Inflows or Outflows

Every single Kobo captured via Paystack checkout must map to exactly one public.orders record, and every Kobo disbursed via Paystack Transfer must map to an approved public.payout_requests record with an immutable reference.

Invariant 3: Canonical Debit Synchronization

No payout or refund can be marked as executed unless its reserved debit record in public.transactions exactly equals the disbursed amount, belongs to the correct vendor, and has not been previously restored or reversed.

---

2. Settlement & Reconciliation Topology

graph TD
    subgraph Payment Inflows
        B[Buyer] -->|Paystack Checkout| PS_IN[Paystack Collections Account]
        PS_IN -->|Webhook charge.success| API[Debelu Backend API]
        API -->|Atomic Escrow Reservation| ESCROW[Escrow Liability Account]
    end

    subgraph Internal Ledger Engine
        ESCROW -->|Delivery PIN Confirmed| RELEASE[process_order_fund_release]
        RELEASE -->|Credit Net Vendor Balance| WALLET[public.user_private_info.balance]
        RELEASE -->|Credit Platform Fee| EQUITY[Retained Commissions Ledger]
    end

    subgraph Payout Outflows
        WALLET -->|Vendor Withdrawal Request| PREQ[public.payout_requests]
        PREQ -->|Batch Payout Dispatch| PS_OUT[Paystack Transfer API]
        PS_OUT -->|NUBAN Interbank Transfer| VBANK[Vendor Commercial Bank]
    end

    subgraph Dual-Authorization Reconciliation
        RECON_M[Maker / Financial Ops] -->|Prepare Proposal & Verify Paystack API| RECON_SVC[PayoutTransferReconciliationService]
        RECON_SVC -->|Inspect Transfer Context| DB[(PostgreSQL Ledger)]
        RECON_C[Checker / Finance Director] -->|Review & Fresh Re-Verification| RECON_SVC
        RECON_SVC -->|Atomic Settlement Execution| DB
    end

---

3. Payout Transfer Reconciliation Engine

When a Paystack bank transfer webhook is dropped, arrives out-of-order, or destination bank switches fail silently, the PayoutTransferReconciliationService ([debelu-backend/src/services/PayoutTransferReconciliationService.ts](file:///c:/Users/frank/OneDrive/Desktop/Chisom/Debelu/New%20Debelu%20Marketplace/debelu-backend/src/services/PayoutTransferReconciliationService.ts)) executes an auditable, dual-authorization reconciliation protocol.

3.1 Strict Reference Specification

Payout references are deterministic UUIDv4 mappings: $$\text{Reference} = \text{"payout-"} + \text{payoutId.replaceAll("-", "")}$$ Matches regex: /^payout-[a-f0-9]{32}$/

3.2 Dual-Authorization (Maker-Checker) Lifecycle

sequenceDiagram
    autonumber
    actor Maker as Staff (Maker)
    participant ReconSvc as PayoutTransferReconciliationService
    participant Paystack as Paystack Transfer API
    participant DB as Postgres (Security Definer RPC)
    actor Checker as Finance Director (Checker)

    Maker->>ReconSvc: 1. prepare(actorId, reconId, payoutId, reason)
    ReconSvc->>DB: 2. reconciliation_transfer_context(payoutId)
    Note over ReconSvc,DB: Validates vendorId !== actorId
    ReconSvc->>Paystack: 3. GET /transfer/verify/:reference (timeout 20s)
    Paystack-->>ReconSvc: 4. Returns verified outcome (amount, NUBAN, transfer_code)
    ReconSvc->>DB: 5. propose_payout_reconciliation(verified, reason)
    DB-->>ReconSvc: 6. Returns Receipt (State: pending_review)
    
    Note over ReconSvc,Checker: Time passes (Inspection window)

    Checker->>ReconSvc: 7. review(actorId, reconId, decision='approve', reason)
    Note over ReconSvc: Enforces: Checker !== Maker AND Checker !== Vendor
    ReconSvc->>Paystack: 8. Fresh GET /transfer/verify/:reference
    Paystack-->>ReconSvc: 9. Returns live outcome
    Note over ReconSvc: Asserts live outcome matches proposed snapshot exactly
    ReconSvc->>DB: 10. review_payout_reconciliation(decision, liveVerified)
    DB->>DB: 11. Atomic verification of reserved debit in transactions
    DB->>DB: 12. UPDATE transactions SET status='Completed' & payout_requests SET status='processed'
    DB-->>ReconSvc: 13. Returns Settlement Receipt (State: executed)

3.3 Maker-Checker Enforcement Invariants

The reconciliation receipt schema strictly validates:

1. Self-Reconciliation Ban: requestedBy !== snapshot.vendorId and reviewedBy !== snapshot.vendorId.
2. Distinct Checker Requirement: reviewedBy !== requestedBy (enforced at both application and database level).
3. Double Verification (Anti-Drift): The Paystack terminal outcome is queried over HTTPS when the proposal is drafted, and re-queried over HTTPS immediately prior to approval. If the provider state changed in the interim, approval fails with 409 Conflict ("Provider outcome changed. Prepare a new reconciliation proposal").
4. Table Privilege Revocation: Direct INSERT, UPDATE, DELETE, and TRUNCATE operations on public.payout_reconciliation_commands are permanently revoked from service_role and authenticated. All state modifications MUST execute through Postgres Security Definer stored procedures (propose_payout_reconciliation, review_payout_reconciliation).

---

4. Reversal & Restitution Ledger Flow

In Nigerian interbank banking, a transfer initially acknowledged by the central switch (NIBSS) may subsequently suffer a reversal due to recipient account freeze, invalid destination branch, or interbank settlement timeout.

-- Executed atomically within review_payout_reconciliation when status='reversed'
BEGIN;
  -- 1. Mark payout request as failed
  UPDATE public.payout_requests 
  SET status = 'failed', transfer_failure = 'Provider reversed transfer'
  WHERE id = v_payout_id;

  -- 2. Post canonical Reversal record to double-entry ledger
  INSERT INTO public.transactions (
    id, vendor_id, amount, type, status, description, provider_reference
  ) VALUES (
    'PAYOUT-REV-' || v_payout_id,
    v_vendor_id,
    v_amount_minor / 100.0,
    'Reversal',
    'Completed',
    'Restitution of reversed bank transfer',
    v_transfer_code
  );

  -- 3. Restore vendor wallet balance
  UPDATE public.user_private_info 
  SET balance = balance + (v_amount_minor / 100.0), updated_at = now()
  WHERE id = v_vendor_id;

  -- 4. Dispatch in-app and email notification to vendor
  INSERT INTO public.notifications (user_id, title, message, category, priority)
  VALUES (v_vendor_id, 'Payout Restored', 'Transfer of funds failed at destination bank. Funds returned to wallet.', 'finance', 'high');
COMMIT;

---

5. Reviewed Payout Batches (ReviewedPayoutBatchService.ts)

To optimize interbank transfer fees and mitigate transaction flooding, Debelu processes non-urgent vendor payouts via Reviewed Payout Batches.

5.1 Batch Dispatch Protocol

1. Batch Generation: Gathers all pending payout requests past their 24-hour verification cooldown.
2. Velocity & Ceiling Verification: Evaluates platform risk controls:
   • Maximum single withdrawal limit (default ₦2,000,000).
   • Daily vendor withdrawal cap (default ₦5,000,000).
   • 24-hour cooling off period post-NUBAN bank account modification.
3. Two-Person Batch Signoff: The batch must be authorized by an administrator holding the canApprovePayouts administrative permission before the background queue worker (ReviewedPayoutDispatchWorker.ts) receives dispatch clearance.

---

6. Audit Logging & Export Integrity

6.1 Cryptographic Hash Verification

To satisfy Nigerian tax authorities (FIRS), the Central Bank of Nigeria (CBN), and independent financial auditors, Debelu provides high-volume, chunked ledger exports ([AuditExportService.ts](file:///c:/Users/frank/OneDrive/Desktop/Chisom/Debelu/New%20Debelu%20Marketplace/debelu-backend/src/services/AuditExportService.ts)):

• Immutable Streaming: Audit logs stream in deterministic chunks (1,000 records per slice) sorted by created_at ASC, id ASC.
• Payload Hashing: Each exported block includes an SHA-256 integrity digest computed across all transaction IDs, amounts, timestamps, and destination bank codes in that slice.
• Tamper-Evident Verification: Modifying a historical transaction amount post-export causes immediate checksum failure upon audit verification.

---

7. Edge Cases & Resilience Protocols

Incident Scenario	System Behavior	Recovery Action
Paystack API Outage during Verification	Verification GET times out (>20s).	Service throws 503 Service Unavailable with message: "Provider verification unavailable. No settlement was changed." Zero database state is mutated.
Tampered Debit Record	Transaction debit amount in DB modified from -100.01 to -99.00.	RPC checks amount === -snapshot.amountMinor. Mismatch raises PostgreSQL error 40001. Proposal is locked in pending_review and flagged for fraud investigation.
Concurrent Approval Attempts	Two finance directors approve reconciliation simultaneously.	PostgreSQL row-level lock (SELECT ... FOR UPDATE). First caller executes; second receives replayed: true with existing executed receipt.
Vendor Suspended during Reconciliation	Vendor account deactivated between proposal and review.	Approval check tests profiles.status = 'active'. Deactivated vendor causes immediate 42501 exception; settlement is refused.