Payment Database Architecture,
Ledger Design, and Resiliency in Modern Nigerian Financial Engineering

A technical research paper on fintech infrastructure, idempotency, and double-entry systems

Research Paper

August 2026

Abstract

Modern fintech architecture requires far more than basic transactional endpoints; it demands robust, thread-safe, and immutable data storage patterns capable of handling network latency, webhook failures, and concurrent race conditions. In the Nigerian financial ecosystem—characterized by high-volume instant settlements via the Nigeria Inter-Bank Settlement System (NIBSS), intermittent mobile connectivity, and stringent regulatory oversight by the Central Bank of Nigeria (CBN)—database design is the cornerstone of system reliability. This paper explores the architectural nuances of designing production-grade payment databases. It contrasts traditional transaction histories with double-entry accounting ledgers, analyzes idempotency mechanisms to prevent duplicate charges, unpacks webhook event sourcing, and provides comparative implementations across PostgreSQL and MongoDB.

Keywords: payment database, double-entry ledger, idempotency, webhook resilience, PostgreSQL, MongoDB, Nigerian fintech, NIBSS, CBN

1. Introduction

The proliferation of digital payments in Nigeria has transformed consumer behavior, shifting the economy rapidly toward electronic channels. However, while junior developers frequently implement rudimentary endpoints such as POST /payment, real-world production environments expose systems to severe anomalies: dropped network packets during third-party gateway callbacks, concurrent double-clicks on checkout buttons, and out-of-order webhook delivery from payment service providers (PSPs) like Paystack, Flutterwave, or direct banking switches.

When a payment system fails, the root cause is rarely a broken API route; rather, it is a flawed database schema or an absence of proper transactional state management. Financial systems demand strict data integrity, immutability, and auditability. This paper examines the technical blueprints required to design resilient payment databases tailored to the realities of high-scale engineering.

2. How to Design a Payment Database

Designing a payment database diverges significantly from standard CRUD (Create, Read, Update, Delete) application design. While a standard user profile table can tolerate eventual consistency or casual updates, a payment database must prioritize atomicity, immutability, and traceability.

Core design principles for payment databases include:

Immutability: Once a payment record reaches a terminal state (SUCCESS or FAILED), its core financial attributes (amount, currency, source, destination) must never be updated in place. Corrections are handled via compensating entries or reversals.
Precision Data Types: Financial values must never be stored using floating-point numbers (FLOAT or DOUBLE) due to floating-point rounding errors. Instead, they must be stored as integers representing the base currency unit (e.g., Kobo for Nigerian Naira) or using fixed-precision decimals (DECIMAL(18, 4)).
Auditability: Every state transition must be timestamped and linked to an immutable event trail to satisfy both internal auditing and regulatory compliance mandates set by the CBN and the Nigerian Financial Intelligence Unit (NFIU).

3. Payment Database Schema Explained

A robust payment database schema isolates concerns across distinct, relationally linked tables. Rather than collapsing a transaction into a single flat table, a production architecture separates intents, attempts, actual transactions, and ledger postings.

The foundational entities of a payment database schema include:

Customers: Stores user identifiers, KYC status, and linked bank or card profiles.
Payment Intents: Represents a user's intent to pay before execution begins (capturing amount, currency, metadata, and expected merchant details).
Payment Attempts: Tracks every individual execution try against payment gateways or bank switches (crucial for handling retries).
Transactions: The authoritative record of a movement or attempted movement of value.
Ledger Entries: Double-entry accounting records reflecting the balance adjustments across system accounts.

4. How to Store Payment Transactions

Storing payment transactions requires careful handling of lifecycle states and temporal data. A transaction row should capture not just the financial value, but the contextual vector of the transaction.

Essential Transaction Table Fields:

id: Unique identifier (UUIDv4 or ULID for sortability).
reference: A unique merchant- or system-generated reference string.
amount: Stored as an integer (e.g., value in Kobo).
currency: ISO 4217 code (e.g., NGN).
status: Enum (PENDING, SUCCESS, FAILED, REVERSED).
channel: Gateway or rail used (e.g., NIP, CARD, USSD).
gateway_reference: The external transaction reference returned by the PSP or switch.
created_at / updated_at: High-precision timestamps.

5. Payment Transaction vs. Payment Event

A common architectural anti-pattern is treating a payment transaction as a mutable record that gets overwritten every time a status changes. To maintain system health, engineers must distinguish between a Transaction and a Payment Event.

Payment Transaction (Entity): The current aggregate state of a payment. It is a snapshot row that says: "Payment X is currently SUCCESSFUL."
Payment Event (Log): An immutable, append-only record of something that happened to the payment. Examples include payment.initiated, payment.processing, payment.gateway_acknowledged, and payment.settled.

By storing payment events in an event-sourcing or audit log table, engineers can reconstruct the exact timeline of a transaction during dispute resolutions or incident post-mortems.

6. How to Store Payment Status

Payment status management is notoriously complex because payments are asynchronous. A client might initiate a transfer, close their browser, and leave the system waiting for a webhooks callback from a bank switch.

Statuses must move strictly through a defined state machine:

PENDING \to PROCESSING \to { SUCCESS, FAILED, ABANDONED, EXPIRED }

To prevent race conditions where a delayed webhook updates a transaction after a timeout handler has already marked it as failed, database schemas utilize state versioning or strict conditional updates:

UPDATE transactions 
SET status = 'SUCCESS', updated_at = NOW() 
WHERE id = 'txn_123' AND status = 'PENDING';

If zero rows are updated, the application knows a conflicting state change already occurred, preventing data corruption.

7. How to Track Payment Attempts

In emerging markets like Nigeria, network timeouts, bank server downtimes, and dropped sessions are common. A single user checkout session may involve multiple attempts.

If a user tries to pay via a card, experiences a timeout, and retries via a bank transfer, creating a new transaction record for every attempt shatters analytics and reconciliation. Instead:

The Payment Intent remains constant.
Multiple Payment Attempts are linked to that single intent via a foreign key (payment_intent_id).
Each attempt records its own gateway response code, error message, latency, and routing path, allowing engineering teams to calculate gateway success rates and fallback routing metrics.

8. How to Prevent Duplicate Transactions

Duplicate charges—where a user's account is debited twice for a single order due to network retries or double-clicking—are catastrophic for fintech trust. Preventing duplicates requires two layers of defense:

Client-Side / API Level (Idempotency Keys): The API client sends a unique Idempotency-Key header with every POST /payment request. The backend hashes and stores this key in a high-speed cache (e.g., Redis) or database with a strict TTL. If a duplicate request arrives with the same key, the server bypasses execution and returns the original cached response.
Database Level (Unique Constraints): Enforcing a UNIQUE constraint on database columns such as (merchant_id, order_reference) or idempotency_key ensures that even if application-level locks fail, the database engine will reject duplicate inserts at the storage layer.

9. How to Build a Payment Ledger

A transactional database tells you the state of a payment; a ledger tells you the truth of where the money actually lives. Relying solely on a users table balance field (e.g., balance = balance - 1000) is a fatal architectural mistake because it leaves no audit trail.

Modern payment ledgers rely on Double-Entry Bookkeeping, where every financial operation consists of balanced Debits and Credits across accounts.

\sum Debits = \sum Credits across every transaction posting.

Ledger Structure: Instead of updating balances in place, every money movement writes immutable ledger entry lines. To find a user's balance, the system computes the sum of all debits and credits associated with their ledger account ID.

10. Ledger vs. Transaction History

While terms are often used interchangeably by junior developers, enterprise architectures treat them distinctly:

Dimension	Transaction History	Payment Ledger
Primary Purpose	Tracks the lifecycle, status, and gateway metadata of a payment request.	Tracks the legal and financial ownership, movement, and balancing of funds.
Accounting Model	Single-table status updates or event logs.	Strict double-entry accounting (balanced debits and credits).
Immutability	Can sometimes transition through lifecycle states (Pending → Success).	Strictly immutable. Past entries are never edited; adjustments require counter-entries.
Primary Consumer	Product apps, user UI dashboards, and customer support.	Finance teams, internal treasury, auditors, and automated reconciliation engines.

11. How to Store Webhook Events

Payment gateways communicate transaction completions asynchronously via webhooks. Storing and processing webhooks requires an architecture that prevents lost events and duplicate execution.

A production-grade webhook ingestion pipeline follows three steps:

Ingestion & Raw Logging: The endpoint receives the webhook, verifies its cryptographic signature (e.g., HMAC SHA-512), and immediately inserts the raw JSON payload into an immutable webhook_events table with a status of RECEIVED. An immediate 200 OK is returned to the PSP to prevent timeout retries.
Queueing: An asynchronous worker picks up the stored webhook event.
Idempotent Processing: The worker checks if the event_id has already been processed. If not, it updates the corresponding transaction state and marks the webhook event as PROCESSED.

12. Database Transactions in Payment Systems

When orchestrating operations that touch multiple tables (e.g., updating a user wallet, creating a ledger entry, and recording a transaction), database transactions governed by ACID properties are mandatory.

Isolation Levels: In high-concurrency fintech environments, default isolation levels can lead to race conditions like Lost Updates or Phantom Reads. Financial engines typically require SERIALIZABLE or strict row-level locking (SELECT ... FOR UPDATE) when reading and writing account balances to ensure two threads cannot withdraw from the same wallet simultaneously.
Rollbacks: If any step in the payment pipeline fails (e.g., the ledger posting fails after the gateway confirms success), the entire database transaction rolls back, or a structured compensation workflow is triggered to initiate an automatic refund.

13. How to Design Payment Tables

To anchor the theoretical principles discussed above, consider a production-ready relational schema layout.

Relational Schema Design (PostgreSQL Focus)

-- 1. Payment Intents Table
CREATE TABLE payment_intents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id UUID NOT NULL,
    amount BIGINT NOT NULL, -- Stored in Kobo (e.g., 500000 = ₦5,000.00)
    currency VARCHAR(3) NOT NULL DEFAULT 'NGN',
    status VARCHAR(50) NOT NULL,
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 2. Payment Attempts Table
CREATE TABLE payment_attempts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    payment_intent_id UUID REFERENCES payment_intents(id),
    gateway_name VARCHAR(50) NOT NULL, -- e.g., 'PAYSTACK', 'FLUTTERWAVE', 'NIP'
    gateway_reference VARCHAR(255) UNIQUE,
    status VARCHAR(50) NOT NULL,
    raw_response JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 3. Ledger Accounts Table
CREATE TABLE accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_id UUID NOT NULL,
    account_type VARCHAR(50) NOT NULL, -- 'CUSTOMER_WALLET', 'FEE_REVENUE', 'SETTLEMENT_HOLDING'
    currency VARCHAR(3) NOT NULL DEFAULT 'NGN'
);

-- 4. Immutable Ledger Entries (Double-Entry)
CREATE TABLE ledger_entries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    transaction_id UUID NOT NULL,
    account_id UUID REFERENCES accounts(id),
    entry_type VARCHAR(10) NOT NULL CHECK (entry_type IN ('DEBIT', 'CREDIT')),
    amount BIGINT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

14. MongoDB Payment Database Design

While relational databases (SQL) are the industry standard for financial ledgers due to ACID guarantees and foreign key constraints, Document databases like MongoDB can be utilized effectively for specific layers—such as storing raw webhook payloads, audit logs, and complex nested payment metadata—provided proper document modeling and session transactions are enforced.

Document Model Strategy (MongoDB)

In MongoDB, payment intents and their associated attempts can be modeled using a hybrid approach: referencing core accounts while embedding bounded contextual events.

// Payment Intent Document Structure in MongoDB
{
  "_id": ObjectId("64a2f8b1c9e77b21e4a12345"),
  "customer_id": ObjectId("64a2f7a5c9e77b21e4a54321"),
  "amount": 1500000, // ₦15,000.00 in Kobo
  "currency": "NGN",
  "status": "SUCCESS",
  "attempts": [
    {
      "attempt_id": "att_01HXYZ...",
      "gateway": "paystack",
      "gateway_reference": "PSK_REF_987654",
      "status": "FAILED",
      "error_message": "Insufficient funds",
      "timestamp": ISODate("2026-08-11T10:00:00Z")
    },
    {
      "attempt_id": "att_02HXYZ...",
      "gateway": "flutterwave",
      "gateway_reference": "FLW_REF_123456",
      "status": "SUCCESS",
      "error_message": null,
      "timestamp": ISODate("2026-08-11T10:05:00Z")
    }
  ],
  "metadata": {
    "order_id": "ORD-2026-0811",
    "ip_address": "102.89.33.12"
  },
  "created_at": ISODate("2026-08-11T09:58:00Z"),
  "updated_at": ISODate("2026-08-11T10:05:01Z")
}

Crucial Note on MongoDB: When executing financial balance updates or multi-document ledger postings in MongoDB, developers must utilize multi-document ACID sessions (session.startTransaction()) to ensure that partial writes never occur across distributed cluster shards.

15. PostgreSQL Payment Database Design

PostgreSQL remains the gold standard for backend financial engineering due to its robust support for native JSON/JSONB data types, advanced indexing, strict constraint checks, and serializable transaction isolation.

Advanced PostgreSQL Practices for Fintech:

Row-Level Locking for Concurrency: When executing balance transfers between wallets, lock the target rows to prevent race conditions:
SELECT balance FROM accounts WHERE id = 'acc_123' FOR UPDATE;
JSONB for Flexible Gateway Payloads: Storing raw PSP responses in a JSONB column allows engineering teams to inspect unstructured webhook data without altering core relational schemas, while still permitting indexed queries on specific JSON keys:
CREATE INDEX idx_attempts_gateway_response ON payment_attempts USING gin (raw_response);
Partitioning High-Volume Tables: For systems processing millions of transactions daily, transactions and ledger_entries tables should be partitioned by date (e.g., range partitioning per month) to maintain optimal query performance and simplify data archiving compliance.

References

Central Bank of Nigeria (CBN). (2023). Regulatory Framework and Guidelines on Instant Payment Systems and Electronic Channels in Nigeria. Abuja: CBN Publications.

Kleppmann, M. (2017). Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems. O'Reilly Media.

Fowler, M. (2002). Patterns of Enterprise Application Architecture. Addison-Wesley.

PostgreSQL Global Development Group. (2026). PostgreSQL 16/17 Documentation: Concurrency Control and Transaction Isolation. Retrieved from https://www.postgresql.org/docs/

MongoDB, Inc. (2026). MongoDB Manual: Multi-Document Transactions in Distributed Architectures. Retrieved from https://www.mongodb.com/docs/

— 15 —