Designing a Payments Ledger for a Paystack Integration

Most applications start with an orders table. The order has a status column that flips from "pending" to "paid" when the customer pays. This works fine until you need to answer any of these questions:

• How much did Paystack charge us in fees last month?
• A customer says they were charged twice. Were they?
• We received a settlement of NGN 2,340,000 yesterday. Which transactions does that cover?
• A refund was issued but the customer says they did not receive it. What happened?
• Our revenue this month is NGN 5M gross but our bank only shows NGN 4.7M. Where is the difference?

An orders table cannot answer these questions because it tracks product fulfillment, not money movement. A payments ledger tracks money movement. Every charge, every fee deduction, every refund, every settlement, every chargeback. Each one is a separate entry with a timestamp, an amount, and a reference back to the Paystack transaction.

For the full verification and reconciliation pipeline that uses this ledger, see the complete verification and reconciliation guide.

Here is a production-ready ledger schema for PostgreSQL:

CREATE TABLE payment_ledger (
  id              BIGSERIAL PRIMARY KEY,
  reference       VARCHAR(100) NOT NULL,
  event_type      VARCHAR(50) NOT NULL,
  -- 'charge.success', 'refund.processed', 'chargeback.created',
  -- 'settlement.processed', 'reversal'

  direction       VARCHAR(10) NOT NULL DEFAULT 'credit',
  -- 'credit' = money in (charges), 'debit' = money out (refunds, chargebacks)

  gross_amount    BIGINT NOT NULL,
  -- Full amount the customer paid (in smallest currency unit)

  fee_amount      BIGINT NOT NULL DEFAULT 0,
  -- Paystack processing fees

  net_amount      BIGINT NOT NULL,
  -- gross_amount - fee_amount (what you actually receive or refund)

  currency        VARCHAR(3) NOT NULL,
  channel         VARCHAR(30),
  -- 'card', 'bank', 'ussd', 'mobile_money', 'bank_transfer'

  paystack_id     BIGINT,
  -- Paystack's internal transaction ID

  customer_email  VARCHAR(255),
  order_id        BIGINT,
  settlement_id   BIGINT,
  -- Populated during settlement reconciliation

  metadata        JSONB DEFAULT '{}',
  -- Full Paystack response payload

  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  settled_at      TIMESTAMPTZ,

  UNIQUE(reference, event_type)
);

-- Indexes for common query patterns
CREATE INDEX idx_ledger_reference ON payment_ledger(reference);
CREATE INDEX idx_ledger_created ON payment_ledger(created_at);
CREATE INDEX idx_ledger_settled ON payment_ledger(settled_at) WHERE settled_at IS NOT NULL;
CREATE INDEX idx_ledger_event_type ON payment_ledger(event_type);
CREATE INDEX idx_ledger_order ON payment_ledger(order_id) WHERE order_id IS NOT NULL;
CREATE INDEX idx_ledger_settlement ON payment_ledger(settlement_id) WHERE settlement_id IS NOT NULL;

Key design decisions in this schema:

• BIGINT for money. Not FLOAT, not DOUBLE, not NUMERIC. BIGINT stores kobo, pesewas, and cents as exact integers. No rounding errors, no precision problems. A BIGINT holds values up to 9.2 quintillion, which is more than enough.
• Three money columns. Gross is what the customer paid. Fee is what Paystack took. Net is what you receive. You need all three for different reports.
• Direction column. Charges are credits (money coming in). Refunds and chargebacks are debits (money going out). This makes balance calculations a simple SUM with a CASE statement.
• UNIQUE on (reference, event_type). One reference can have multiple events: a charge, then a refund, then a chargeback. But only one charge per reference, one refund per reference. This prevents duplicates while allowing the full lifecycle.
• JSONB metadata. The full Paystack response, stored as structured JSON. Six months from now, you will want to see the card type, the bank name, the gateway response, the IP address. Store it all now.

Write to the ledger from your webhook handler, immediately after you verify the transaction. Use ON CONFLICT DO NOTHING to make the insert idempotent:

// Record a charge in the ledger
async function recordCharge(txn, orderId) {
  await db.query(
    'INSERT INTO payment_ledger '
    + '(reference, event_type, direction, gross_amount, fee_amount, '
    + 'net_amount, currency, channel, paystack_id, customer_email, '
    + 'order_id, metadata) '
    + 'VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) '
    + 'ON CONFLICT (reference, event_type) DO NOTHING',
    [
      txn.reference,
      'charge.success',
      'credit',
      txn.amount,
      txn.fees,
      txn.amount - txn.fees,
      txn.currency,
      txn.channel,
      txn.id,
      txn.customer ? txn.customer.email : null,
      orderId,
      JSON.stringify(txn),
    ]
  );
}

// Record a refund in the ledger
async function recordRefund(refundData, originalReference) {
  await db.query(
    'INSERT INTO payment_ledger '
    + '(reference, event_type, direction, gross_amount, fee_amount, '
    + 'net_amount, currency, metadata) '
    + 'VALUES ($1, $2, $3, $4, $5, $6, $7, $8) '
    + 'ON CONFLICT (reference, event_type) DO NOTHING',
    [
      originalReference,
      'refund.processed',
      'debit',
      refundData.amount,
      0,  // Refund fees depend on your Paystack configuration
      refundData.amount,
      refundData.currency,
      JSON.stringify(refundData),
    ]
  );
}

The ON CONFLICT DO NOTHING clause is critical. Paystack can send the same webhook multiple times. Your sweeper job might re-process the same transaction. With this clause, the second insert silently does nothing instead of throwing an error or creating a duplicate.

Never update a ledger entry. Ledgers are append-only. If a charge is refunded, you add a new refund entry. You do not change the original charge entry. This gives you a complete history of every financial event.

Here are the queries you will run most often:

-- Daily revenue summary
SELECT
  currency,
  SUM(CASE WHEN direction = 'credit' THEN net_amount ELSE 0 END) as total_revenue,
  SUM(CASE WHEN direction = 'debit' THEN net_amount ELSE 0 END) as total_refunds,
  SUM(fee_amount) as total_fees,
  COUNT(CASE WHEN event_type = 'charge.success' THEN 1 END) as charge_count,
  COUNT(CASE WHEN event_type = 'refund.processed' THEN 1 END) as refund_count
FROM payment_ledger
WHERE created_at >= '2026-07-01' AND created_at < '2026-07-02'
GROUP BY currency;

-- All events for a specific transaction
SELECT event_type, direction, gross_amount, fee_amount, net_amount,
       created_at, settled_at
FROM payment_ledger
WHERE reference = 'order_abc123_1689012345'
ORDER BY created_at;

-- Unsettled transactions (charges with no settlement date)
SELECT reference, gross_amount, net_amount, currency, created_at
FROM payment_ledger
WHERE event_type = 'charge.success'
  AND settled_at IS NULL
  AND created_at < NOW() - INTERVAL '3 days'
ORDER BY created_at;

-- Net balance by currency (what Paystack owes you)
SELECT
  currency,
  SUM(CASE WHEN direction = 'credit' THEN net_amount
           WHEN direction = 'debit' THEN -net_amount
           ELSE 0 END) as balance
FROM payment_ledger
WHERE settled_at IS NULL
GROUP BY currency;

The unsettled transactions query is particularly useful. If transactions from three or more days ago still have no settled_at date, either your settlement reconciliation is behind, or there is a problem with the settlement itself. For more on this, see the settlement reconciliation guide.

The ledger and the orders table serve different purposes. The orders table tracks what the customer bought and whether it was fulfilled. The ledger tracks money movement. They are linked by the order_id column in the ledger.

-- Orders table (simplified)
CREATE TABLE orders (
  id                  BIGSERIAL PRIMARY KEY,
  user_id             BIGINT NOT NULL,
  product_id          BIGINT NOT NULL,
  status              VARCHAR(20) NOT NULL DEFAULT 'pending',
  paystack_reference  VARCHAR(100) UNIQUE,
  expected_amount     BIGINT NOT NULL,
  expected_currency   VARCHAR(3) NOT NULL,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Joining orders with ledger entries
SELECT
  o.id as order_id,
  o.status as order_status,
  o.product_id,
  l.event_type,
  l.gross_amount,
  l.fee_amount,
  l.net_amount,
  l.settled_at
FROM orders o
LEFT JOIN payment_ledger l ON l.order_id = o.id
WHERE o.id = 123
ORDER BY l.created_at;

When an order has multiple ledger entries (a charge, then a partial refund), the join returns multiple rows. This is correct. Each row represents a separate financial event tied to the same order.

A common mistake is putting fee_amount and net_amount in the orders table instead of the ledger. This works until you have refunds or chargebacks, which create additional financial events that do not fit in a single row on the orders table.

If your business operates across multiple Paystack-supported countries, your ledger will contain entries in different currencies. NGN, GHS, KES, ZAR, USD. You must never add amounts across currencies. NGN 100,000 plus KES 10,000 is not KES 110,000 or NGN 110,000. It is meaningless.

Always group and aggregate by currency:

-- Monthly revenue by currency (correct)
SELECT
  currency,
  SUM(net_amount) as total_net
FROM payment_ledger
WHERE event_type = 'charge.success'
  AND created_at >= '2026-07-01'
  AND created_at < '2026-08-01'
GROUP BY currency;

-- DO NOT do this:
-- SELECT SUM(net_amount) FROM payment_ledger WHERE ...
-- This adds NGN and GHS together, which is wrong

If you need a single "total revenue" number for management reports, convert each currency to a base currency using a consistent exchange rate. Store the conversion rate used so the calculation is reproducible. See the multi-country currency accounting guide for the full pattern.

Ledger entries should be immutable. Once written, never update or delete them. If you made a mistake (recorded the wrong amount, assigned to the wrong order), add a correction entry rather than changing the original:

// Correcting a ledger entry (add a correction, do not update)
async function correctLedgerEntry(originalReference, correctionReason) {
  // Fetch the original entry
  var original = await db.query(
    'SELECT * FROM payment_ledger WHERE reference = $1 AND event_type = $2',
    [originalReference, 'charge.success']
  );

  if (original.rows.length === 0) return;

  var entry = original.rows[0];

  // Add a reversal entry
  await db.query(
    'INSERT INTO payment_ledger '
    + '(reference, event_type, direction, gross_amount, fee_amount, '
    + 'net_amount, currency, metadata) '
    + 'VALUES ($1, $2, $3, $4, $5, $6, $7, $8)',
    [
      originalReference,
      'correction.reversal',
      entry.direction === 'credit' ? 'debit' : 'credit',
      entry.gross_amount,
      entry.fee_amount,
      entry.net_amount,
      entry.currency,
      JSON.stringify({
        reason: correctionReason,
        original_entry_id: entry.id,
        corrected_at: new Date().toISOString(),
      }),
    ]
  );

  // Add the correct entry
  // (you would pass the correct values here)
}

Immutability means your ledger is an audit trail. Anyone can look at the full history and see exactly what happened, when it happened, and why corrections were made. This is invaluable during financial audits, tax reviews, and chargeback disputes.

For a deeper exploration of this principle, see the double-entry bookkeeping guide.

A high-volume Paystack integration can generate thousands of ledger entries per day. Over months and years, the table grows large. Two strategies keep it manageable:

Table partitioning. PostgreSQL supports range partitioning on the created_at column. Each month gets its own partition. Queries that filter by date only scan the relevant partitions:

-- Partitioned ledger table
CREATE TABLE payment_ledger (
  id              BIGSERIAL,
  reference       VARCHAR(100) NOT NULL,
  event_type      VARCHAR(50) NOT NULL,
  direction       VARCHAR(10) NOT NULL DEFAULT 'credit',
  gross_amount    BIGINT NOT NULL,
  fee_amount      BIGINT NOT NULL DEFAULT 0,
  net_amount      BIGINT NOT NULL,
  currency        VARCHAR(3) NOT NULL,
  channel         VARCHAR(30),
  paystack_id     BIGINT,
  customer_email  VARCHAR(255),
  order_id        BIGINT,
  settlement_id   BIGINT,
  metadata        JSONB DEFAULT '{}',
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  settled_at      TIMESTAMPTZ,
  UNIQUE(reference, event_type, created_at)
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE payment_ledger_2026_07 PARTITION OF payment_ledger
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE payment_ledger_2026_08 PARTITION OF payment_ledger
  FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

Archiving. Ledger entries older than your regulatory retention period (typically 7 years for financial records in most African countries) can be exported to cold storage (S3, GCS) and removed from the active table. Keep the exports in a queryable format like Parquet or CSV so you can search them if needed.

Start with a single unpartitioned table. Add partitioning when the table exceeds a few million rows and queries start slowing down. Do not over-engineer on day one.