Reconciliation (Audit framework)

We have a framework to reconcile the donations/refunds/chargebacks etc in CiviCRM with the reports we receive from the payment processors.

We do this to achieve 2 goals

1) ensure that all donor gifts are shown in CiviCRM (sometimes other notifications are not received) and that all the information we want is stored. Some information, like the fees, the converted settled amount and the reference for the batch the contribution settles in first becomes available to us when we receive audit/reconcilation reports from the processors. (

2) ensure that we can accurately send finance batches of the amouts settled by each of our settlement providers (currently adyen, paypal, braintree, dlocal, trustly, checkout.com and chariot). There is further data on this on the Fundraising/Data and flow/Intacct page - note that that page is intended to be readable by people outside fr-tech

The priorities are slightly different for the 2 goals. For the first goal our priority is to get the donations in as soon as we can - so we process 'whatever we get, when we get it'. In practice this means we process both payment and settlement reports from the payment processors and payment reports from Gravy. (In most cases the first report we receive is the processor payment report and the other 2 are for redundancy). We only move the report from 'incoming' to 'completed' when all transactions in it are in CiviCRM.

For the second goal our priority is to verify the exact total that settled in a batch and ensure that all the transactions that contributed to that total are recorded in CiviCRM against the batch with final amount information in the settled currency. Determining the exact total varies by payment processor so the table below shows how we reach this amount based on the information we receive from each payment processor.

The information from the settlement providers is our primary source of information. Gravy also receives these reports in some cases and returns them to us with minor formatting changes. Gr4vy sales people have promoted their reports as a better source but in practice we have found that they often don't exist until months after we have implemented a processor, and they are inferior to those from the payment providers as they don't provide settlement batch total information for validation purposes, and they don't always match what is settled, especially around the more obscure scenarios. We do the additional work to get the gravy ones working but are not quite sure if we gain anything through this so we focus our efforts on the reports we get from the primary source. We cannot use gravy reports instead of our process as all records must be matched with CiviCRM donations in order to enhance them with GL-related information.

We retain the information we get from the primary sources on disk and can, if necessary, access it from there. We currently never delete them and have primary data from PayPal and Adyen going back over 12 years!


	Report type	Name	Batch total calculation	Gravy report availability	Schedule, files, notes
Adyen Fundraising/Data and flow/PSP integrations/Adyen Checkout#Audits	csv Settlement report	settlement_batch	Adyen has a row `payout` and each transaction has a batch number. The records with the batch number add up to the batch payout row.	Available, mostly identical to what we get from Adyen directly (column names differ) but not cover obscure adjustments in all cases	We receive 3 reports - 2 versions of the settlement report with different names and one payment report. - payments_account_report - nightly file, has the previous days transactions. comes in a little sooner so by processing it as well we are able to start the processing a little earlier. - settlement_detail_report_batch - daily now that we settle daily settlement_detail_xxxx_batch - same weekly file with a different name - we get both because Gravy wanted a different name. Currently parsing both due to perceived risk that Gravy requirements will change and one will go away & it will be the one we are parsing. Runs multiple times a day but we only parse one file at a time to try to avoid double queueing to the settle queue - less of an issue now we skip the gravy-requested file
PayPal	csv Settlement report	STL-***	PayPal has a footer section that gives the details for amounts by currency. We calculate the file total as Credits - Debits + Fee Credits - Fee Debits . To get the batch total we then deduct any Debits that relate to accounts transfers or expense re-imbursements	Available with Data gaps: No information to allow us to determine batch total. Some transactions missing - eg. https://phabricator.wikimedia.org/T418191 affects 4 settlement batches but is only present in 2 of the gravy files (skipped) Otherwise mostly the same as the primary source (some formatting changes)	We only process the TTR & STL reports, Others are moved to ignored. TTR is the payments report. Runs nightly at 17:35 UTC TRR - nightly Transaction Detail Report - this is used by the audit and has all the single transactions STL - Settlement Report - this is used by the audit and has all the settled transactions SAR - nightly Subscription Agreement Report - this 'disappeared' - possibly Gravy related. WIkimedia - not used
Trustly	csv Settlement rport	P11KFUN-	Trustly provides the batch total in the footer and in payout rows. We use the payout rows (in case there is ever more than one per file)	n/a - they did announce it was 'there' manyl months after we were up & running with reports direct from Trustly but I did check on 21 Jul 2026 as part of this documenation update & don't see them
Dlocal (formerly Astropay) Fundraising/Data and flow/PSP integrations/dLocal#Audits	csv Settlement report	cross_border	Dlocal provides the batch total in the header. The calculations are all done before rounding (5 decimal places) so we calculate a rounding transaction which we code as a fee	n/a	Runs every night at 00:20 UTC files - nightly
Braintree	api - returned in json Using graphQL		We retrieve the transactions by disbursement date. We have to add these up to get the batch total. I went through a period of manually verifying these against the Disbursement reports in the UI and these calculated totals accurately reflect the Donations + Refunds that show up in the UI. Chargebacks sit outside those 2 - they are not counted in the main disbursement reports in the UI or retrievable by disbursement dates. These are low volume and we are currently treating them as separate batches.	n/a	Runs every night at 00:00 UTC 2 json files created - one for transactions and refunds and the other for chargebacks (if any). Chargebacks are a bit messy in that we can't search by the date the chargeback settled so we try using a wider window to try and catch them - seems to work
Stripe	csv	payout row		n/a
CheckoutCom	csv settlement report with a payout report to specify payout amount	payouts + settlement_breakdown	We get the transactions from the settlement breakdown and the final payout from the payouts report. We round if there is a misalignment	n/a
Chariot	api - returned in json	deposits + donations	When a deposit is available we retrieve the donations for it	n/a

Gravy parsing note

Runs every night at 02:10 UTC

files - nightly at 01:00 UTC. We only parse the payments file and this appears to run after other files so is a bit of a null op.

Process control jobs for the audit parsing

This documents the workflow to process audit files from payment processors and import missing messages into CiviCRM.

Most payment processors have 1 or 2 audit jobs that we run multiple times a day (some only once a day). We follow the job naming pattern of *_audit_download and *_audit_parse - eg.

braintree_audit_download

braintree_audit_parse

The download jobs generally run a custom Maintenance script located in the smash-pig standalone codebase that will either retrieve and compile data from the processor's api or download files from an SFTP location. For the latter there is a generic script. When compiling from the api we store either as raw json or converted to a csv depending on the data source complexity (e.g braintree is stored as a json as that can be parsed fairly easily from the raw json but for chariot we are combining data from 2 api calls so the `GetReport` code combines these to a csv with a lot of processing.

The WMFAudit.parse api call processes the reconciliation files. These files will contain an api call like

wmf-cv api4 -vv WMFAudit.parse gateway=braintree

The code in the civicrm extension handles reading the list of files from the directory, searching for existing transactions in the database, and finding missing information in the smashpig.pending tables (with a legacy fall back to searching payments-wiki logs (mounted at /srv/archive/frlog/logs)) for each transaction that isn't in the database. The code to parse the individual files to an array of normalized transactions lives under the SmashPig codebase, in classes that implement the AuditParser interface , whereas the code to reconcile those files lives in the CiviCRM wmf-civicrm extension.

Audit files are located on civi1001 in /var/spool/audit/[payment-processor] and divided into two directories: incoming and completed.

How to run the parser (works locally too)

Run the audit parser for one gateway:

wmf-cv api4 -vv WMFAudit.parse gateway=adyen logSearchPastDays=12

Run with just one specific file:

wmf-cv api4 -vv WMFAudit.parse gateway=adyen logSearchPastDays=12 file=payments_accounting_report_2024_04_06.csv

Additional optional parameters

- settleMode ("queue" or "now", blank is implicitly false) - should the settle queue be populated from these

- file (specify file name) - if set then only one file will be processed

- isStopOnFirstMissing (bool) - primarily for debug usage

- rowLimit (int) - primarily for debug usage

- offset (int) -primarily for debug usage

- l ogInterval (int) - how often should progress be output

- isMoveCompletedFile (bool) should the file be moved afterwards - mostly for test & debug

- isCompleted (bool) look in the completed folder - test & debug usage

Refunds & Donations getting into Civi & getting settled

Missing donations will be queued to CiviCRM. If they are in CiviCRM but the audit has additional settlement data that will be added to the settlement queue - these queues can be processed using process-control or with the api


Process control	API
run-job -j donations_queue_consume	`wmf-cv api4 -vv WMFQueue.Consume timeLimit=280` queueConsumer=Donation queueName=donations
run-job -j refund_queue_consume	`wmf-cv api4 -vv WMFQueue.Consume timeLimit=280 queueConsumer=Refund queueName=refund`
run-job -j settle_queue_consume	wmf-cv api4 -vv WMFQueue.Consume timeLimit=173 queueConsumer=Settle queueName=settle

Resolving Audit issues

Audit issues come to fr-tech attention in one of 2 ways

fail mail
the daily finance batch summary email

In the case of fail mail the job has failed and you should either look at the log output (see below) or try re-running the job & look at the output you get.

For the emails there are a few things to check

1) if the subject line says ' 1 batch needs attention ` this means that there is a NEW issue with a batch not adding up to the total it should settle to (or at least a new batch with an issue, if previously identified). The batch will be updated at this point to having a status of 'Needs attention' and will not trigger that subject line on subsequent days.

2) if the subject line mentions ' files older than ' this means some files are failing to clear - this likely means some transactions in the audit files are not being matched to donations in CiviCRM or have failed to create donations

3) if the subject line says one contribution needs attention it means it has not identified a GL code for this contribution. As of Aug 2026 these are almost definitely offline contributions that have not been tagged with 'is_major_gift' - and the quick answer is to add is_major_gift to that specific donation - we were still finalising the definition of is_major_gift in Prague and have not revisited since. If this gets painful we could do an interim code fix but it's probably going to be looked at again properly imminently.

4) Other than the subject line there is a table further down of open & needs attention batches. These should be checked to make sure what is there makes sense. Any that are 'needs attention' should have a phab next to them and should be being actively worked on. Any that are 'a bit old' may also need some attention

How to resolve issues

- if the file has not cleared and / or you have a fail mail then either try re-running the job or check the log output per the log output section below

- if there is an issue with a batch then you will need to figure out why it is not balancing. Once you have resolved it you will need to reset the status (generally I reset it to 'open' and then re-run the audit file allowing that to update it to `total_verified` - but ultimately it needs a status of total_verified or validated before it can be exported. It can be hard to track down why these don't balance but here are some things that might help

check the settle queue is clear - in some cases it just ran before all settlement transactions were processed
you can check all batch statuses at https://civicrm.wikimedia.org/civicrm/accounting/batches and link through from there to see the transactions

You can try re-validating the batch to check / get more information - you can do that in the ui screen above but usually the CLI is better - eg in this case I can see the credits (donations) match but there was an expected debit of -3.10 which is missing - I now know I'm looking for negative transaction of $3.10 that did not get to CiviCRM

wmf-cv -vv api4 Batch.validate +w id=8535

  "totals": {
            "debit": "0.00",
            "credit": "298.86",
            "fee_debit": "0.16",
            "fee_credit": "0.00",
            "fee": "0.16",
            "settled": "298.70",
            "count": 42
        },
        "expected": {
            "count": 43,
            "credit": 298.86,
            "debit": -3.1,
            "fee": -0.16,
            "settled": 295.6
        },
        "validation": {
            "count": 1,
            "credit": 0,
            "debit": -3.1,
            "fee": 0,
            "settled": -3.1
        }

Generally you will need to access the source file and try to find what is in there but not in CiviCRM. The view that is linked in the email and from the batch summary ( e.g ) gives you scope to filter on donations of various amounts to try to bisect what is missing. You will find the file in the folder in the setting `wmf_audit_directory_audit` - eg. try
```
wmf-cv Setting.get | grep audit
```
within that directory you are looking in either (eg) trustly/incoming or trustly/completed e.g I have found that one of the transactions in the batch I'm looking for has the transaction ID 8197772279 so from the trustly directory:
```
grep 8197772279 */*
zgrep 8197772279 */*
```
Once you have found the file you might find it easier to move it back to incoming & unzip it. Re-running the parse on it *might* give a clue
In this case I found a row in the csv grepping for 3.10 with a status of 'reversed' and then looked to see if it was in Civi & on not finding it I felt ready to log a phab - after logging the phab go back to the batch summary and update the batch to have the phab next to it.

Log output

check audit parse log from frlog1002, located at /srv/archive/civi/process-control/<yyyymmdd>/<paymentMethod>_audit_parse-<yyyymmdd>-xxxxxx.log.civi1001.bz2 - you can also just re-run the job at any time & see what it does.

Generally the logs will show you the first transaction that was missing from CiviCRM and if this transaction persists after the file has been processed once then you should generally investigate and resolve this transaction and then try re-running it.

Result print example as:

Done! Final stats:

Total number of donations in audit file: xxx

Number missing from database: xxx

Missing transactions found in logs: xxx

Missing transactions not found in logs: xxx

Missing transaction summary:

Regular donations: 2

Returned from hook drush_wmf_audit_parse_audit [1.13 sec, 36.01 MB] [debug]

		xxxxxxxx: 1
		xxxxxxxx: 1

Refunds and chargebacks: 0

Recurring donations: 0

Command dispatch complete [1.13 sec, 35.92 MB] [notice]

Transaction IDs:

		xxx xxxxxxxxxx
		xxx xxxxxxxxxx

Initial stats on recon files: Array

(

	    [/var/spool/audit/xxx/incoming/xxx] => 0
	    [/var/spool/audit/xxx/incoming/xxx] => 0
	    [/var/spool/audit/xxx/incoming/xxx] => 2

)

File Wrangling

Sometimes the audit processor can't resolve all the transactions in a file, even after trying for several days. This can lead to a build-up of files in the incoming directory and to subsequent processor runs getting longer and longer till finally they start timing out. Generally it is best to stay on top of these and to run individual files if needed. If files are not processed they will not get to Intacct.

As a last resort we can manually temporarily move the older files from the incoming to the completed directory. Since our personal accounts don't have permissions to move the files, we do this with a one-off process-control job such as ingenico_move_audit_files. Since process control runs each command as a separate process under python, we need to wrap any file globs that we want expanded with 'sh -c', for example:

sh -c "mv /srv/archive/civi1001/audit/globalcollect/incoming//wx1*202010[01][0-5]*xml* /var/spool/audit/globalcollect/"

Adding a New Payment Processor

Once the new processor code has been added

1. Enable the Module

2. Add the folders

This is done via puppet by adding the processor name to the $audit_processors array in modules/civicrm/manifests/audit.pp . Additionally, some files (YAML usually) may be needed for the audit configuration. Those vary by processor but are stored in the same audit.pp manifest.

Legacy processors ....

Amazon

Instead of an SFTP download, we have to call methods on the Amazon Pay SDK to get our reports. This is kicked off with the DownloadReports php script in SmashPig.

Fundraiseup

Process control jobs

fundraise-up_audit.yaml - Runs at 1AM UTC daily. Calls fundraise-up_audit_download and then fundraise-up_audit_parse.

fundraise-up_audit_download - Downloads fundraiseup export files from the Fundraiseup SFTP server. This files contains the new donations, new recurrings, cancelled recurring, and refunded transactions from Fundraiseup.

fundraise-up_audit_parse - Imports the transactions from the exported files. Calls cv api4 --user=admin -vv WMFAudit.parse gateway=fundraiseup logSearchPastDays=12