Reconcile bank transactions with Mercury in ERPNext

This document covers mercury_integration, Avunu's ERPNext app that connects a site to Mercury Bank. Use it when you want Mercury transactions to land in ERPNext as Bank Transactions, reconcile themselves where the data allows, and when you want to bill customers and pay people and vendors from the same place.

The app needs Frappe v16 (>=16.0.0,<17.0.0), Python 3.12 or newer, and the erpnext, payments and hrms apps on the site.

How the pieces fit

Everything is configured on one Single doctype, Mercury Settings. The app adds custom fields to core doctypes instead of creating its own tables:

The Mercury transaction UUID is stored in the core transaction_id field of the Bank Transaction. That is the deduplication key for everything that follows.

Every inbound event and every outbound payout attempt is logged as an Integration Request with the service set to Mercury. Frappe's Webhook Request Log is not used, because core only writes it for outbound webhooks.

Set up the integration

1. Open Mercury Settings, choose the Company, paste the API token, and tick Enabled. Saving validates the token by listing your accounts, so a bad token fails right there. For testing, tick Use Sandbox and use the Sandbox API Token field instead.

2. Click Mercury > Sync Accounts. This creates a Mercury Bank and one Bank Account per Mercury account, each with a GL account under your company's Bank group.

3. Click Mercury > Register Webhook (production only, see below).

4. Click Mercury > Backfill Transactions and enter how many days to import. Then tick Automatic Transaction Sync so the hourly job keeps going.

5. Optionally set Alert Recipients to a comma-separated list of email addresses. Sync, billing and payout failures are emailed there.

If you already have Bank Accounts for the same Mercury accounts, for example from an earlier bank feed, Sync Accounts adopts them rather than creating duplicates. It matches on account number first (an exact match, then a tail match of at least four digits) and then on account name. Archived or deleted Mercury accounts are never created, and any mapped Bank Account that points at one is disabled.

Choose a token

• A read-only token covers bank sync, auto journal entries and AR polling.

• Creating AR invoices needs a read-write or custom-scoped token.

• Direct Send payouts need a read-write token and your server's static egress IP whitelisted in Mercury.

• Request Approval payouts (the default) need no IP whitelist, but a second Mercury user must approve each payment in the Mercury app.

Webhooks and the poller

Events reach the site two ways, and both feed the same pipeline.

• Webhook. Register Webhook creates an endpoint pointing at /api/method/mercury_integration.webhooks.webhook on your site URL, stores the endpoint ID and signing secret, and asks Mercury to send a verification event. The site must therefore be reachable from the internet. Mercury returns the signing secret only when the endpoint is created, so running the button again deletes the old endpoint and creates a new one.

• Poller. Every 15 minutes the app reads Mercury's events API from its saved cursor. It is a complete channel on its own and is the only one in the Mercury sandbox, which has no webhooks.

The webhook receiver checks the Mercury-Signature header (t=<timestamp>,v1=<hex>, an HMAC-SHA256 over <timestamp>.<raw body>, five minutes of tolerance), records the event, queues it and answers ok. Mercury does not retry non-2xx responses except 429, so the receiver acknowledges quickly and does the work in the background.

Each event is stored once, keyed on the Mercury event ID, so duplicates from the two channels are harmless. The request_description of the Integration Request says which channel delivered it: Webhook, Poll or Replay. Only transaction events trigger work. The handler fetches the current transaction from the API instead of trusting the payload, so out-of-order delivery converges on the right state.

A daily health check reads the endpoint status from Mercury. If Mercury disabled the endpoint after repeated failures, the check sets it active again and emails you. The poller covers any gap.

To re-run recent events, click Mercury > Replay Events and enter a number of hours (default 24).

How transactions sync

The hourly job queues one sync per enabled Bank Account that has a mercury_account_id. Each run re-reads from three days before the account's last_integration_date. On the first run it starts at Sync Start Date, or 12 months back if that is empty.

For each Mercury transaction the app does the following:

• A posted transaction becomes a submitted Bank Transaction. Mercury amounts are signed, so a negative amount is a withdrawal and a positive amount is a deposit.

• A pending transaction is ignored unless Create Pending Transactions is ticked, in which case it becomes a draft that is updated and submitted when it posts.

• The description joins the counterparty name with the bank description (or note, or external memo). Check numbers are stored without Mercury's leading zeros in reference_number.

• A failed, cancelled, reversed or blocked transaction deletes its draft or cancels its unreconciled Bank Transaction. If the Bank Transaction is already reconciled, the app emails you instead of touching it.

• A changed posting date is corrected in place. A changed amount cancels and recreates an unreconciled Bank Transaction, or alerts you if it is reconciled.

Map GL codes and book journal entries automatically

A Mercury GL code is the accounting classification you put on a transaction in Mercury. The app maps a code to the ERPNext account whose Account Name equals it exactly. Mercury exposes GL codes read-only, so you publish them by file upload.

1. Click Mercury > Export GL Codes. The browser downloads a bare, single-column CSV with no header and opens the upload page at app.mercury.com/accounting/mapping/gl-codes.

2. Upload the file there.

3. Repeat after you add or rename accounts.

The export lists every enabled, non-group Liability, Income, Expense and Equity account in the company. Asset accounts are left out because the bank side of the entry is an asset. Names shared by two accounts, and names with a comma, quote, line break or leading or trailing space, are skipped and listed in a warning, because they could never match verbatim.

What the auto journal does

Tick Enable Auto Journal Entries to let the app book a Journal Entry for each transaction that carries a GL code. All of these must hold:

• The transaction is posted and is not an internal transfer.

• It has an attachment, unless you untick Require Attachment (on by default).

• The Bank Transaction is submitted and not yet reconciled, and no Journal Entry already uses the Mercury transaction ID as its cheque_no.

• Its amount is within Maximum Amount, where zero means no limit.

The entry has one leg per GL allocation plus the bank leg, so split transactions work. Every cent of the transaction must be coded or the entry is not booked. The posting date is the transaction's posted date, and the Mercury transaction ID goes in cheque_no, which is how the app avoids booking twice. Profit and Loss legs get Default Cost Center, or the company default when that is a ledger rather than a group. The app then reconciles the Bank Transaction to the entry and copies the Mercury attachments (up to 32 MB each) onto both documents.

If the code cannot be booked, you get an email naming the reason:

• It matches no account name, or more than one.

• It maps to an Asset account.

• It maps to a Receivable or Payable account, and the Mercury counterparty is not linked to an Employee or Supplier. Those accounts need a party, which the app resolves through the recipient mapping.

• The allocations do not add up to the transaction amount.

Late coding and the daily sweep

Mercury sends no event when someone adds a GL code. The hourly sync only revisits three days, so a transaction coded later would be missed. The daily sweep fixes this. It rechecks every unreconciled Mercury Bank Transaction within Daily Backfill (Days) (default 90, zero turns it off), books what became codeable, and emails one digest of anything coded but unbookable. It costs one API call per transaction, so keep the window as short as you can.

To run it on demand, start with a dry run:

bench --site <SITE_NAME> execute mercury_integration.sync.gl_codes.reevaluate_unreconciled

Then apply it. bench execute reads --kwargs as a Python expression, so write False, not false. You can pass from_date to bound the sweep:

bench --site <SITE_NAME> execute mercury_integration.sync.gl_codes.reevaluate_unreconciled --kwargs "{'dry_run': False, 'from_date': '<YYYY-MM-DD>'}"

Internal transfers

With Auto Reconcile Internal Transfers on (the default), a transfer between two Mercury accounts becomes one Bank Entry Journal Entry reconciled to both Bank Transactions. The app pairs the two sides using the counterparty name, which ends in the other account's last four digits, then looks for the opposite amount within three days. The first side to sync waits, and the second books the entry. An ambiguous pair is emailed to you. To backfill transfers that are already synced, run mercury_integration.sync.transfers.reconcile_existing_transfers the same way as above. It also defaults to a dry run.

Bill customers through Mercury

Tick Enable AR Gateway and set the Destination Bank Account (it must already have a Mercury account ID) and the AR Clearing Account. Create the clearing account first as a Bank-type account under Current Assets. Saving registers a Payment Gateway named Mercury and a Payment Gateway Account that is not the default, so nothing changes for existing billing yet.

The flow for each Payment Request on that gateway:

1. On submission, the app creates the Mercury customer if needed (it needs an email address, taken from the Payment Request or else from the ERPNext Customer) and a Mercury invoice for the grand total. The pay page URL goes into payment_url.

2. Invoice Email Sender decides who emails the payer: ERP (the usual Payment Request email with the pay link) or Mercury.

3. Every 15 minutes the app polls open invoices. Processing moves the Payment Request to Initiated. Paid creates a Payment Entry against the clearing account, referenced by the Mercury invoice ID.

4. When the deposit appears as a Bank Transaction on the destination account, the app books a Bank Entry from clearing to bank and reconciles it. It looks for invoices paid between seven days before and one day after the deposit date. It matches on the exact amount and only books when exactly one invoice matches, and emails you when several do.

If invoice creation fails, the payer is not emailed, you get an alert, and an hourly job retries. A Recreate Mercury Invoice button on the Payment Request does it by hand. Cancelling a Payment Request cancels an unpaid invoice, and is blocked while the invoice is processing or paid. An invoice cancelled in Mercury cancels the Payment Request.

Overdue reminders go out daily by default, every 7 days up to 3 times (Reminder Interval (Days), Maximum Reminders).

Know the limits before you promise anything to customers:

• Payment is payer-initiated on the pay page. The app cannot pull ACH from a customer, so it does not replace mandate-based auto-charging (see Billing Subscriptions Automatically with GoCardless).

• The gateway supports USD only.

• Card payments net processor fees, and the fee-tolerant funding match (Card Fees Account) is heuristic. Leave Accept Credit Card off until you have seen how settlements arrive.

Pay employees and vendors by ACH

Tick Enable Payouts, choose a Payout Mode, and set Payroll Funding Bank Account and Vendor Funding Bank Account. Both must have Mercury account IDs. Auto Reconcile Payouts is on by default.

Link payees to Mercury recipients

On an Employee or Supplier form, the Mercury menu offers these routes:

• Send Mercury Invite emails a secure link where the payee enters their own bank details. The ERP stores only the recipient ID and status. A daily job turns completed invites into active recipients.

• Match Mercury Contact adopts someone you already pay in Mercury. It lists unlinked Mercury contacts and preselects a star-marked match when one is unambiguous (same email, else same name).

• Enter Bank Details Directly sends routing and account numbers straight to Mercury without storing them.

• Refresh from Mercury reads the recipient's current status on demand, which completes a pending invite without waiting for the daily job.

To adopt many at once, run the bulk matcher. It matches on email, then name, and only writes a link when the pairing is one-to-one in both directions:

bench --site <SITE_NAME> execute mercury_integration.payouts.recipients.link_existing_recipients --kwargs "{'party_type': 'Employee'}"

That is a dry run. Review the result, then add 'dry_run': False. Pass 'parties': ['<EMPLOYEE_ID>'] to limit it to specific records.

Run payroll

Submit the Payroll Entry as usual, which creates the consolidated bank Journal Entry, then click Mercury > Pay via Mercury. A preview lists each salary slip with its recipient status and any warning. Sending is never automatic. Slips are sent one by one in a background job and you get a summary comment on the Payroll Entry.

The amount is the slip's exact net_pay to the cent, not the whole-dollar rounded total. A slip is sendable only when it has an active recipient, a positive amount and no payout in flight.

In Request Approval mode each payment waits in Mercury for a second user to approve. Direct Send executes immediately. The app polls approval requests every 15 minutes and marks a rejected or cancelled one accordingly.

Pay vendors

On a submitted supplier Payment Entry (payment type Pay, not yet cleared), click Mercury > Pay via Mercury and confirm.

How payouts reconcile

When the payout's Bank Transaction arrives, the app matches it on the Mercury transaction ID. Each vendor payment reconciles one-to-one with its Payment Entry. Each payroll payment reconciles against the single consolidated payroll Journal Entry, which clears only after every employee's ACH has landed. The daily sweep retries any payout whose Bank Transaction arrived before its voucher existed. The payout status moves through Pending Approval, Sent, Posted and Reconciled, or ends at Failed, Rejected, Cancelled or Blocked - Duplicate. A Salary Slip cannot be cancelled while its payout is in flight.

Cut over from a previous gateway and bank feed

If you are replacing an existing payment gateway and bank feed, move in stages so each step is verified before the next.

1. Prep. Install the app, create the AR clearing account, and configure and enable Mercury Settings. The new gateway registers as non-default.

2. Payroll first. It has no overlap with your old gateway. Send recipient invites, run one Payroll Entry in Request Approval mode, and check that the Bank Transactions reconcile against the payroll Journal Entry.

3. AR pilot. On one or two Subscription Plans, change Payment Gateway to the Mercury Payment Gateway Account by hand. Do not use repoint_subscription_plans for this: it moves every plan that uses the old gateway account at once. Watch one full cycle: Payment Request, pay page, Paid, clearing Payment Entry, funding Bank Transaction, funding Journal Entry.

4. AR cutover. Notify customers, make Mercury the default with mercury_integration.migrate.set_default_gateway_account, then repoint the remaining plans. The repoint function lists the plans it would change when you leave dry_run at its default, so run it that way first:

   bench --site <SITE_NAME> execute mercury_integration.migrate.repoint_subscription_plans --kwargs "{'from_gateway_account': '<OLD_GATEWAY_ACCOUNT>', 'to_gateway_account': '<MERCURY_GATEWAY_ACCOUNT>'}"

   Then add 'dry_run': False to apply it. Keep the old gateway's webhooks live for at least 60 days for in-flight charges and chargebacks.

5. Bank feed cutover at a chosen timestamp T. Take a final sync from the old feed and run mercury_integration.migrate.snapshot_plaid_integration_ids, run Sync Accounts, disable the old feed's automatic sync, then backfill from T. Audit the overlap with mercury_integration.migrate.find_duplicate_bank_transactions --kwargs '{"around": "<T>"}'. Merge duplicates with mercury_integration.migrate.merge_plaid_mercury_duplicates, dry run first. It writes a CSV report to the site's private files and keeps the reconciled copy.

Where to look when something fails

• Integration Request (service Mercury) shows each event with its status and any traceback, and each payout attempt.

• Error Log holds the details behind the alert emails.

• Mercury Settings shows the webhook status and the time of the last poll.