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 Contents 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.