Merchant Financial Ledger
Technical Design Document

Project	Mercury Settlement Platform (UAT)
Module	Ledger Core (New Umbrella App)
Prepared by	Settlement Module Team
Date	June 2026
Scope	Internal — Finance and Operations only
Status	Design Plan — Ready for Discussion

Background & Motivation
How This Differs From the Event Logger
Core Concept — Per Merchant Per Settlement Date
Database Design
How Ledger Entries Get Created
The Balance Rule
Outstanding Balances — Finance View
Edge Cases
UI Design
Umbrella App Structure
Implementation Steps
Relationship With Event Logger

1. Background & Motivation

The Mercury Settlement platform processes payouts to hundreds of merchants across daily settlement cycles. The current system tracks the process of settlement well — recon runs, MIS is generated, payouts are transmitted. However it does not maintain a clear record of the financial position of each merchant at any point in time.

Concretely: if Finance needs to answer the question "How much does Mercury currently owe merchant X, and for which settlement dates?" — there is no single place to look. The answer is spread across core_transactions, settlement_mis_items, payout_items, and merchant_adjustments.

The Merchant Financial Ledger solves this by maintaining a running financial record per merchant per settlement date — every credit and debit that affects what Mercury owes a merchant, structured so that each settlement cycle must add up to zero when the merchant is fully paid.

Objectives

Financial clarity — know exactly what Mercury owes each merchant at any point in time.
Settlement cycle tracking — each settlement date is an independent cycle with a clear open and close.
Outstanding balance monitoring — Finance sees a live list of all unpaid or partially paid cycles.
Deduction traceability — every MDR charge, VAT amount, and adjustment is a named entry in the ledger — nothing is a black box.
Audit readiness — for any merchant on any date, the full financial breakdown is available in one place.

2. How This Differs From the Event Logger

These are two separate systems serving two different purposes. Both are needed and they complement each other.

Dimension	Settlement Event Logger	Merchant Financial Ledger
What it records	What happened and when in the pipeline	The financial impact of what happened
A typical row	"MIS approved at 09:14 by Finance L2"	"+AED 1,420.00 Gross settlement — 342 txns"
Unit	Pipeline event (process milestone)	Money entry (financial transaction)
Per	Per settlement date (pipeline view)	Per merchant per settlement date (financial view)
Must balance?	No — events are just a timeline	Yes — every cycle must sum to zero when closed
Primary audience	Operations, Engineering	Finance, Accounting, Audit
Question it answers	"Did the SFTP fetch succeed today?"	"Does Mercury still owe merchant X any money?"

Simple way to remember the difference: The Event Logger tells you the story of the settlement. The Merchant Ledger tells you the score — who owes what and whether it has been settled.

3. Core Concept — Per Merchant Per Settlement Date

Each merchant has an independent ledger cycle for each settlement date. The cycle opens when the MIS is approved and closes when the bank confirms the payout and the balance reaches zero.

Example — Same Merchant, Two Settlement Dates

These two cycles are completely independent of each other:

Merchant: MID 419926360000000 \| Settlement Date: 18-Jun-2026
Entry	Type	Side	Amount
Gross settlement — 342 txns	Gross Settlement	Credit	+AED 1,420.00
MDR deduction (10%)	MDR Deduction	Debit	−AED 142.00
VAT on MDR (5%)	VAT Deduction	Debit	−AED 7.10
Adjustment — chargeback recovery	Adjustment	Debit	−AED 25.00
Net payable to merchant	Running Balance	—	AED 1,245.90
Bank payout confirmed (19-Jun)	Payout Credited	Debit	−AED 1,245.90
Closing balance	Closed	—	AED 0.00 ✓

Merchant: MID 419926360000000 \| Settlement Date: 19-Jun-2026
Entry	Type	Side	Amount
Gross settlement — 218 txns	Gross Settlement	Credit	+AED 980.00
MDR deduction (10%)	MDR Deduction	Debit	−AED 98.00
VAT on MDR (5%)	VAT Deduction	Debit	−AED 4.90
Net payable to merchant	Running Balance	—	AED 877.10
Bank payout confirmed (20-Jun)	Payout Credited	Debit	−AED 877.10
Closing balance	Closed	—	AED 0.00 ✓

The Balance Rule: Sum of all credit entries minus sum of all debit entries = 0 when the cycle is fully closed. If the result is not zero, the cycle is still open — Mercury has an outstanding obligation to the merchant for that settlement date.

Why Per Date (Not Cumulative)?

Each settlement date is a discrete business event with its own MIS, its own payout batch, and its own bank confirmation. They are naturally independent.
If a merchant's 18-Jun balance is still open (e.g. due to a risk hold), it does not affect the 19-Jun cycle. Finance can see and manage them separately.
Closing a cycle to zero is a clear, auditable signal that the merchant has been fully paid for that specific date.
Querying outstanding balances is a simple WHERE balance != 0 — no complex date arithmetic needed.

4. Database Design

Table 1 — `merchant_ledger_entries`

One row per financial entry. This is the detailed ledger — every credit and debit.

CREATE TABLE merchant_ledger_entries (
  id                BIGSERIAL    PRIMARY KEY,
  merchant_mid      VARCHAR      NOT NULL,
  settlement_date   DATE         NOT NULL,
  entry_type        VARCHAR      NOT NULL,
  side              VARCHAR      NOT NULL,    -- credit | debit
  amount            DECIMAL      NOT NULL,    -- always positive; side gives direction
  running_balance   DECIMAL      NOT NULL,    -- balance after this entry
  reference_type    VARCHAR      NULL,        -- settlement_mis | payout_item | adjustment | etc.
  reference_id      BIGINT       NULL,        -- ID of the source record (soft link)
  description       TEXT         NOT NULL,    -- human readable: "Gross settlement — 342 txns"
  posted_by         VARCHAR      NULL,        -- "System" or user name
  posted_at         TIMESTAMP    NOT NULL DEFAULT NOW()
);

Entry Types

entry_type	Side	Meaning	Triggered By
`gross_settlement`	Credit	Total gross transaction amount for the merchant on this date	MIS L2 approval
`mdr_deduction`	Debit	MDR fee charged to merchant	MIS L2 approval
`vat_deduction`	Debit	VAT on MDR (5% UAE)	MIS L2 approval
`adjustment_debit`	Debit	Deduction adjustment (chargeback recovery, AR recovery, etc.)	Adjustment approved
`adjustment_credit`	Credit	Addition adjustment (goodwill credit, correction, etc.)	Adjustment approved
`risk_hold`	Debit	Amount withheld due to risk flag — reduces net payable	Risk hold applied
`risk_release`	Credit	Risk hold amount released back to merchant	Risk hold released
`payout_credited`	Debit	Bank has confirmed the transfer to merchant — clears the balance	Bank confirmation approved

Table 2 — `merchant_ledger_summary`

One row per merchant per settlement date. This is the summary view — Finance queries this table for the outstanding balances dashboard. It is updated each time an entry is added.

CREATE TABLE merchant_ledger_summary (
  id                BIGSERIAL    PRIMARY KEY,
  merchant_mid      VARCHAR      NOT NULL,
  settlement_date   DATE         NOT NULL,
  gross_amount      DECIMAL      NOT NULL  DEFAULT 0,
  mdr_total         DECIMAL      NOT NULL  DEFAULT 0,
  vat_total         DECIMAL      NOT NULL  DEFAULT 0,
  adjustments_total DECIMAL      NOT NULL  DEFAULT 0,  -- net of credits and debits
  risk_hold_total   DECIMAL      NOT NULL  DEFAULT 0,
  net_payable       DECIMAL      NOT NULL  DEFAULT 0,  -- gross minus all deductions
  paid_amount       DECIMAL      NOT NULL  DEFAULT 0,  -- confirmed by bank
  balance           DECIMAL      NOT NULL  DEFAULT 0,  -- net_payable minus paid_amount
  status            VARCHAR      NOT NULL  DEFAULT 'open',
  opened_at         TIMESTAMP    NULL,    -- when MIS L2 was approved
  closed_at         TIMESTAMP    NULL,    -- when balance reached zero
  UNIQUE (merchant_mid, settlement_date)
);

Status Values for Summary

Status	Meaning
`open`	MIS approved, entries posted, payout not yet confirmed by bank
`on_hold`	A risk hold is applied — net payable is reduced, cycle cannot close
`partial`	Bank confirmed payment but amount does not match net payable (short payment)
`closed`	Balance = 0 — merchant fully paid for this settlement date

Indexes

-- Primary query: outstanding balances per date
CREATE INDEX idx_ledger_summary_balance
  ON merchant_ledger_summary (settlement_date, balance, status);

-- Merchant history: all cycles for a merchant
CREATE INDEX idx_ledger_summary_merchant
  ON merchant_ledger_summary (merchant_mid, settlement_date DESC);

-- Entries per merchant per date
CREATE INDEX idx_ledger_entries_merchant_date
  ON merchant_ledger_entries (merchant_mid, settlement_date, posted_at);

5. How Ledger Entries Get Created

Entries are written to the ledger at specific points in the settlement pipeline — triggered by the same events that the Event Logger records, but writing financial data instead of audit data.

Trigger Map

Settlement Event	Ledger Entries Created	Per
MIS L2 Approved	`gross_settlement` (credit) `mdr_deduction` (debit) `vat_deduction` (debit)	One set per merchant in the MIS
Adjustment Approved (L2)	`adjustment_debit` or `adjustment_credit`	One entry per adjustment
Risk Hold Applied	`risk_hold` (debit)	One entry per merchant affected
Risk Hold Released	`risk_release` (credit)	One entry per merchant
Bank Confirmation Approved	`payout_credited` (debit) — closes the cycle	One entry per merchant in the batch

Example — MIS L2 Approval Creates Three Entries Per Merchant

When Finance L2 approves the MIS for 18-Jun-2026 covering 47 merchants, the ledger receives 3 entries × 47 merchants = 141 rows inserted at once.

-- For merchant MID 419926360000000 on 18-Jun-2026:

INSERT INTO merchant_ledger_entries VALUES
  (merchant_mid: '419926360000000', settlement_date: '2026-06-18',
   entry_type: 'gross_settlement', side: 'credit', amount: 1420.00,
   running_balance: 1420.00,
   reference_type: 'settlement_mis_item', reference_id: 214,
   description: 'Gross settlement — 342 transactions'),

  (merchant_mid: '419926360000000', settlement_date: '2026-06-18',
   entry_type: 'mdr_deduction', side: 'debit', amount: 142.00,
   running_balance: 1278.00,
   reference_type: 'settlement_mis_item', reference_id: 214,
   description: 'MDR deduction — 10% on AED 1,420.00'),

  (merchant_mid: '419926360000000', settlement_date: '2026-06-18',
   entry_type: 'vat_deduction', side: 'debit', amount: 7.10,
   running_balance: 1270.90,
   reference_type: 'settlement_mis_item', reference_id: 214,
   description: 'VAT on MDR — 5% on AED 142.00');

Note: After MIS approval, if there are no adjustments, the running balance at this point is the net payable to the merchant. The summary table is updated in the same operation — merchant_ledger_summary.net_payable and balance are set, status is set to open.

Example — Bank Confirmation Closes the Cycle

-- Finance Ops approves bank confirmation batch
-- Bank has credited AED 1,245.90 to merchant 419926360000000

INSERT INTO merchant_ledger_entries VALUES
  (merchant_mid: '419926360000000', settlement_date: '2026-06-18',
   entry_type: 'payout_credited', side: 'debit', amount: 1245.90,
   running_balance: 0.00,   -- balance hits zero
   reference_type: 'payout_item', reference_id: 88,
   description: 'Bank payout confirmed — ref: ENBD-20260619-0441');

-- Summary updated:
UPDATE merchant_ledger_summary
  SET paid_amount = 1245.90, balance = 0.00,
      status = 'closed', closed_at = NOW()
  WHERE merchant_mid = '419926360000000'
    AND settlement_date = '2026-06-18';

6. The Balance Rule

The fundamental rule of the Merchant Financial Ledger is:

For every merchant on every settlement date:

Gross Settlement
− MDR Deduction
− VAT Deduction
± Adjustments
− Risk Holds
+ Risk Releases
− Payout Credited
───────────────
= 0.00 (when fully closed)

If the result is not zero, the cycle is still open and Mercury has an outstanding financial obligation to that merchant for that settlement date.

This rule is enforced at the application level. The merchant_ledger_summary.balance field always reflects the current outstanding amount. When it reaches zero, the cycle is marked closed.

7. Outstanding Balances — Finance View

The most important query for Finance is: "Which merchants still have an outstanding balance and why?"

Outstanding Balances Dashboard

Merchant MID	Settlement Date	Net Payable	Paid Amount	Outstanding Balance	Status	Days Open
419926360000000	19-Jun-2026	AED 877.10	AED 0.00	AED 877.10	Open	1
Mercury_CB6F6A5	19-Jun-2026	AED 620.00	AED 0.00	AED 620.00	Open	1
Mercury0BCFEFDC	18-Jun-2026	AED 340.00	AED 0.00	AED 340.00	On Hold	2

The third merchant's 18-Jun cycle is still open two days later because a risk hold was applied. The first two are open because their bank confirmation has not yet been processed. Finance can see all three at a glance and take action accordingly.

Closed Cycles — Historical View

Merchant MID	Settlement Date	Net Payable	Paid Amount	Status	Opened	Closed
419926360000000	18-Jun-2026	AED 1,245.90	AED 1,245.90	Closed ✓	19-Jun 11:32	19-Jun 14:18
Mercury_CB6F6A5	18-Jun-2026	AED 890.00	AED 890.00	Closed ✓	19-Jun 11:32	19-Jun 14:18

8. Edge Cases

Case 1 — Partial Bank Confirmation

The bank confirms payment for 45 out of 47 merchants in a batch. The 2 failed merchants are not credited.

45 merchants — payout_credited entry posted, cycle closes to zero, status → closed
2 merchants — no entry posted, balance stays at net payable, status remains open
Finance sees exactly which 2 merchants are still outstanding in the dashboard
When the bank retries or a manual transfer is made, the payout_credited entry is posted and the cycle closes

The two failed merchants' cycles are fully visible in the outstanding dashboard. Nothing is hidden or merged into the batch total. Finance knows exactly who is unpaid and for how much.

Case 2 — Adjustment After MIS Approval

A chargeback recovery adjustment of AED 25.00 is raised against a merchant after the MIS is already approved but before the payout is transmitted.

Entry	Side	Amount	Running Balance
Gross settlement	Credit	+AED 1,420.00	AED 1,420.00
MDR deduction	Debit	−AED 142.00	AED 1,278.00
VAT on MDR	Debit	−AED 7.10	AED 1,270.90
Adjustment — chargeback recovery (posted later)	Debit	−AED 25.00	AED 1,245.90
Bank payout confirmed	Debit	−AED 1,245.90	AED 0.00
Closing balance	—	—	AED 0.00 ✓

The adjustment entry is simply added to the existing cycle. The summary table's net_payable and balance are updated. The payout transmission picks up the revised net payable automatically.

Case 3 — Risk Hold Applied

A risk hold of AED 340.00 is placed on a merchant. The hold reduces the net payable, and the cycle stays open until the hold is either released or the held amount is confirmed as forfeited.

Entry	Side	Amount	Running Balance
Gross settlement	Credit	+AED 800.00	AED 800.00
MDR deduction	Debit	−AED 80.00	AED 720.00
VAT on MDR	Debit	−AED 4.00	AED 716.00
Risk hold applied	Debit	−AED 340.00	AED 376.00
Bank payout confirmed (AED 376.00 only)	Debit	−AED 376.00	AED 0.00
Closing balance (hold still active separately)	—	—	AED 0.00 ✓

The risk hold is managed via the existing Risk Holds module. Once the held amount is released, a risk_release credit entry is posted and a separate payout is triggered for the released amount, opening and closing a new ledger cycle for that release.

9. UI Design

Two views are needed — both internal, for Finance and Operations only.

View 1 — Merchant Ledger Detail

Shows the full entry-by-entry ledger for a specific merchant on a specific settlement date. Accessed by clicking a merchant from the outstanding balances list or by searching.

Filters: merchant MID / name, settlement date
Table: all ledger entries in posted order, with running balance column
Summary strip: gross amount, total deductions, net payable, paid amount, current balance
Status badge: Open / On Hold / Partial / Closed
Each entry links to its source record (e.g. clicking the MIS item row navigates to that MIS)

View 2 — Outstanding Balances Dashboard

Shows all open settlement cycles across all merchants — the primary Finance monitoring view.

Default filter: status = open OR on_hold OR partial
Sortable by: settlement date, balance amount, days open, merchant
Columns: Merchant, Settlement Date, Net Payable, Paid Amount, Outstanding, Status, Days Open
Total row: sum of all outstanding balances — the total Mercury currently owes merchants
Export to CSV for Finance reconciliation
Click any row to drill into the merchant ledger detail for that cycle

Menu Placement

Add a new section in the left navigation called Ledger with two items:

Menu Item	Path	Permission
Outstanding Balances	/admin/ledger/outstanding	ledger.balances.view
Merchant Ledger	/admin/ledger/merchant	ledger.merchant.view

10. Umbrella App Structure

The Merchant Ledger is implemented as a new app — ledger_core — within the existing umbrella structure.

tmsuat_apps/
├── apps/
│   ├── platform_core/        ← shared repo, auth, users
│   ├── platform_web/         ← web layer (LiveView, controllers)
│   ├── settlement_core/      ← settlement pipeline logic
│   ├── tms_core/
│   ├── risk_core/
│   └── ledger_core/          ← NEW — merchant financial ledger
│       ├── lib/
│       │   └── ledger_core/
│       │       ├── merchant_ledger_entry.ex    (Ecto schema)
│       │       ├── merchant_ledger_summary.ex  (Ecto schema)
│       │       ├── ledger_writer.ex            (writes entries)
│       │       ├── ledger_query.ex             (reads + balance queries)
│       │       └── application.ex
│       └── mix.exs

Why a Separate App and Not Inside settlement_core?

Reason	Explanation
Different domain	Settlement is about process (recon, MIS, payout flow). Ledger is about financial position (what is owed). These are separate concerns.
Future extensibility	In future, chargebacks, refunds, or risk recoveries from other modules may also affect merchant balances. A standalone `ledger_core` can accept entries from any module — not just settlement.
Independent deployability	The ledger can be built, tested, and deployed without touching the settlement pipeline.
Clean dependency direction	`settlement_core` calls into `ledger_core` to post entries. `ledger_core` does not depend on `settlement_core` at all.

Dependency rule: settlement_core → ledger_core (settlement posts entries to ledger). ledger_core never imports or calls settlement_core. This keeps the dependency graph clean and prevents circular references.

11. Implementation Steps

#	Step	Work	App	Impact on Existing Code
1	Create umbrella app	Scaffold `ledger_core` app inside the umbrella	ledger_core	None — new app
2	Migrations	Create `merchant_ledger_entries` and `merchant_ledger_summary` tables with indexes	ledger_core	None — new tables
3	Ecto schemas	Create `MerchantLedgerEntry` and `MerchantLedgerSummary` schemas with changesets	ledger_core	None — new files
4	LedgerWriter module	Create `LedgerCore.LedgerWriter.post_mis_entries/1` and `post_payout_credited/1` functions	ledger_core	None — new file
5	Wire MIS L2 approval	Call `LedgerWriter.post_mis_entries(mis)` inside `Context.l2_approve_settlement_mis/3` after successful approval	settlement_core	Additive only — one new call after existing logic
6	Wire bank confirmation	Call `LedgerWriter.post_payout_credited(payout_item)` inside `BankConfirmationService.process_confirmed_payout/1`	settlement_core	Additive only
7	Wire adjustments	Call `LedgerWriter.post_adjustment(adjustment)` inside `Context.l2_approve_adjustment/3`	settlement_core	Additive only
8	Wire risk holds	Call `LedgerWriter.post_risk_hold/post_risk_release` from risk hold module	risk_core / settlement_core	Additive only
9	LedgerQuery module	Create query functions: outstanding balances, merchant ledger detail, balance for a merchant+date	ledger_core	None — new file
10	LiveView pages	Create `OutstandingBalancesLive` and `MerchantLedgerLive` in platform_web	platform_web	None — new files
11	Menu + permissions	Add Ledger section to menu provider, add `ledger.balances.view` and `ledger.merchant.view` permissions	ledger_core / platform_core	Additive only

All changes to existing apps are additive only. Steps 5, 6, 7, and 8 add one new function call each to existing context functions — no existing logic is modified. The settlement and risk pipelines continue to work exactly as they do today.

12. Relationship With the Event Logger

The Settlement Event Logger and the Merchant Financial Ledger are built at the same time but serve completely different purposes. They are written from the same trigger points but record different information.

Trigger Point	Event Logger records	Ledger records
MIS L2 Approved	"MIS for 18-Jun-2026 approved by Finance L2 at 11:32"	Gross settlement, MDR, VAT entries per merchant (141 rows for 47 merchants)
Bank Confirmation Approved	"Batch #11: 47 merchants confirmed, AED 58,420"	Payout credited entry per merchant — closes 47 cycles to zero
Adjustment Approved	"Adjustment of AED 25 applied to MID 419926..."	Adjustment debit entry, running balance updated
Risk Hold Applied	"Risk hold of AED 340 applied to MID Mercury0BC..."	Risk hold debit entry, summary status → on_hold

From the same trigger, two things are written: the Event Logger gets one process audit row, and the Ledger gets one or more financial entries. They are independent writes — a failure in ledger writing does not affect event logging, and vice versa.

Together, these two systems give Mercury complete settlement intelligence:

The Event Logger answers: What happened in the settlement pipeline today and did it succeed?

The Merchant Ledger answers: What does Mercury currently owe each merchant and has it been paid?

One tells the story. The other keeps the score.

Merchant Financial LedgerTechnical Design Document

Contents

1. Background & Motivation

Objectives

2. How This Differs From the Event Logger

3. Core Concept — Per Merchant Per Settlement Date

Example — Same Merchant, Two Settlement Dates

Why Per Date (Not Cumulative)?

4. Database Design

Table 1 — merchant_ledger_entries

Entry Types

Table 2 — merchant_ledger_summary

Status Values for Summary

Indexes

5. How Ledger Entries Get Created

Trigger Map

Example — MIS L2 Approval Creates Three Entries Per Merchant

Example — Bank Confirmation Closes the Cycle

6. The Balance Rule

7. Outstanding Balances — Finance View

Outstanding Balances Dashboard

Closed Cycles — Historical View

8. Edge Cases

Case 1 — Partial Bank Confirmation

Case 2 — Adjustment After MIS Approval

Case 3 — Risk Hold Applied

9. UI Design

View 1 — Merchant Ledger Detail

View 2 — Outstanding Balances Dashboard

Menu Placement

10. Umbrella App Structure

Why a Separate App and Not Inside settlement_core?

11. Implementation Steps

12. Relationship With the Event Logger

Merchant Financial Ledger
Technical Design Document

Table 1 — `merchant_ledger_entries`

Table 2 — `merchant_ledger_summary`