The two-ledger pattern: separating money in from money out

An auditor sat across the table and asked one question: "Show me, for every rupee this NGO received last month, where it came from."

If the answer is a SQL query, the platform is in good shape. If the answer is "let me get back to you on that," the platform is in trouble. We had built it so the answer was a query. That decision started with a refusal.

The decision

Money that flows into the platform from donors is one ledger. Money that flows out of the platform to NGOs is another ledger. They never share a table.

Most teams hear "donation platform" and reach for a single transactions table with a type column (incoming / outgoing). It is faster to start. It is impossible to audit cleanly when you scale.

We separated:

sql-- Money in
create table donor_payments (
  id                 uuid primary key,
  donor_id           uuid not null,
  cause_id           uuid not null,
  amount_paise       bigint not null,
  rail               text not null,           -- 'upi_mandate' | 'card_subscription' | 'one_time_upi' | 'one_time_card'
  rail_ref           text not null,           -- gateway-side identifier
  status             text not null,           -- 'initiated' | 'succeeded' | 'failed' | 'refunded'
  charged_at         timestamptz,
  recorded_at        timestamptz not null default now(),
  idempotency_key    text not null unique
);

-- Money out
create table ngo_payouts (
  id                 uuid primary key,
  ngo_id             uuid not null,
  payout_run_id      uuid not null,
  amount_paise       bigint not null,
  rail               text not null,           -- 'imps' | 'rtgs' | 'neft'
  rail_ref           text,                    -- bank-side reference, populated after settlement
  status             text not null,           -- 'pending' | 'in_flight' | 'settled' | 'failed' | 'reversed'
  initiated_at       timestamptz not null,
  settled_at         timestamptz,
  idempotency_key    text not null unique
);

-- Run-level metadata
create table payout_runs (
  id                 uuid primary key,
  cycle_start        date not null,
  cycle_end          date not null,
  authorised_by      uuid not null,           -- finance team member who clicked "release"
  authorised_at      timestamptz not null
);

The two tables share nothing operationally. They live on the same database for join convenience, but the platform never updates one based on the other. Reconciliation lives in views, not in writes.

Why combining them goes wrong

The combined-table approach looks innocent:

sql-- DON'T DO THIS
create table transactions (
  id              uuid primary key,
  type            text not null,            -- 'incoming' | 'outgoing'
  party_id        uuid not null,            -- donor for incoming, ngo for outgoing
  amount_paise    bigint not null,
  status          text not null,
  ...
);

Three problems show up within a quarter:

1. The status enum diverges. A donor payment has states (initiated, succeeded, failed, refunded) that have nothing to do with payout states (pending, in_flight, settled, failed, reversed). Sharing the column means the enum becomes a union, and every consumer of status has to know which subset applies given the type.
2. The party_id polymorphism rots. "It's a donor when type is incoming, an ngo when type is outgoing" is fine in your head and broken in queries. Foreign-key constraints become "deferred check via app code." The app code drifts.
3. The audit answer is two queries with a UNION. When the auditor asks the source-of-funds question, you write a union of incoming and outgoing transactions and hope the join fields make sense. They rarely do.

Separated tables answer "where did this NGO's money come from" with a clean join through a view, not a fragile column.

The reconciliation view

Reconciliation is the join, materialised:

sqlcreate view ngo_funding_reconciliation as
select
  np.id              as payout_id,
  np.ngo_id,
  np.amount_paise    as payout_amount,
  np.payout_run_id,
  pr.cycle_start,
  pr.cycle_end,
  -- pull all donor payments to causes belonging to this NGO,
  -- in this cycle, that succeeded
  (
    select coalesce(sum(dp.amount_paise), 0)
    from donor_payments dp
    join causes c on c.id = dp.cause_id
    where c.ngo_id = np.ngo_id
      and dp.status = 'succeeded'
      and dp.charged_at >= pr.cycle_start
      and dp.charged_at < pr.cycle_end + interval '1 day'
  ) as supporting_donor_total
from ngo_payouts np
join payout_runs pr on pr.id = np.payout_run_id;

This view is what an auditor - or your finance team, or the NGO themselves - looks at to ask "for this payout, what donors funded it?" The view doesn't move money. It just exposes the relationship.

If supporting_donor_total is less than payout_amount for any row, something is wrong: either we paid out more than we collected, or a donor payment was misattributed, or the cycle boundary is off. The view turns "is the platform honest" into a SQL query that returns rows when the answer is no.

Recurring billing without coupling rails

Recurring donors used two rails:

• UPI auto-pay mandates (NPCI rail) for retail donors with UPI-enabled bank accounts. Mandate-based. The platform sends a debit instruction; the donor's bank fulfils it (or doesn't) without donor interaction.
• Card subscriptions for donors whose UPI mandate setup failed or who preferred cards.

Both produce payment events with different schemas and different timing characteristics. UPI mandates settle T+0 in most cases. Card subscriptions settle T+1 or T+2 with retry windows.

The internal abstraction was a single donor_payment_event:

tstype DonorPaymentEvent = {
  donorId: string;
  causeId: string;
  amountPaise: number;
  rail: "upi_mandate" | "card_subscription" | "one_time_upi" | "one_time_card";
  railRef: string;
  status: "initiated" | "succeeded" | "failed" | "refunded";
  chargedAt?: Date;
  idempotencyKey: string;     // deterministic per rail, prevents replays
};

// Each rail's webhook handler maps rail-specific payloads into this shape
function fromUpiMandateWebhook(payload: UpiMandatePayload): DonorPaymentEvent { ... }
function fromCardWebhook(payload: CardWebhookPayload): DonorPaymentEvent { ... }

Two webhook handlers, one downstream pipeline. Adding Stripe later, or NACH, or whatever, was a connector exercise - write a mapper, plug in the webhook route, the rest of the platform was untouched.

Idempotency on both sides

Webhooks retry. The unique constraint stops duplicates. Don't rely on application-level deduplication for this; it will lose a race the day a webhook arrives twice in the same second.

For donor payments, the idempotency key was {rail}-{railRef}-{event_type} - derived from the gateway's own payment identifier and the event the gateway was reporting. A retried "payment.succeeded" webhook computed the same key and hit the unique constraint.

For NGO payouts, the idempotency key was {payout_run_id}-{ngo_id}. A payout run, once authorised, generated one entry per beneficiary NGO. Retries during execution did not create duplicates.

The constraint at the database level is the load-bearing piece. The app-level dedupe is fine for the happy path. The constraint is what saves you the day reality drifts off the happy path.

Failed payments and the soft-churn problem

The interesting failure mode in recurring donations isn't outright cancellation. It's soft churn: a donor's UPI mandate quietly fails because their bank balance dipped below the debit amount once, and the mandate auto-cancels after three failed attempts. The donor never clicked anything. They didn't decide to leave.

Without active intervention, the donor goes quiet. Lifetime value drops. Nobody notices for two months.

The platform watched for this directly:

sql-- Donors whose last 3 attempted charges failed
select donor_id, max(charged_at) as last_failure_at
from donor_payments
where rail = 'upi_mandate'
  and status = 'failed'
  and charged_at > now() - interval '60 days'
group by donor_id
having count(*) >= 3
order by last_failure_at desc;

Operations team got a daily list. The intervention was a polite, specific email - "your support for this cause was paused because three monthly debits failed; here's the link to retry or change rail." Recovery rate ran significantly higher than ignoring it.

What I'd do differently

Make the reconciliation view materialised from day one. The view-as-query approach worked early. At scale, the recursive subquery on every load got slow. We materialised it eventually. Should have started materialised.

Separate the FX rail. Some donors paid in USD via card networks. We treated FX-converted amounts as the same column as INR amounts and tracked currency in a side field. Cleaner is two columns: amount_paise for the native paise amount and amount_paise_inr for the converted-to-INR amount (NULL when no conversion). The rate used at conversion time should also be on the row. We added this later under audit pressure.

Treat NGO bank account changes as events. When an NGO updated their settlement bank account, we updated their record. Cleaner is to have an ngo_bank_accounts history table where the current account is just order by effective_from desc limit 1. Disputes about "the payout went to the old account" then have a clean answer.

---

If you are building any platform where money flows between strangers: separate inflows and outflows at the schema level. Combined transaction tables look right on day one and are unrecoverable on day three hundred. The audit answer is a query, not an apology.