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)

2) ensure that we can accurately send finance batches of the amouts settled by each of our settlement providers (currently adyen, paypal, braintree, dlocal and trustly).

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 (we don't do anything with these but I have added a column to indicate status). Gravy and CiviCRM can be considered secondary sources. In the case of CiviCRM all records are ingressed into CiviCRM where they can be validated, queried and matched 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
Adyen	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.	Identical to what we get from Adyen directly (column names differ)
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	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)
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
Dlocal	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
Braintree	api - returned in json		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

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

The WMFAudit.parse api call processes the reconciliation files. 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 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.

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

How to run locally

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

- logInterval (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

If a refund is successful it will put a message on the refund queue:

wmf-cv api4 -vv WMFQueue.Consume timeLimit=280 queueConsumer=Refund queueName=refund

params

gateway=adyen

logSearchPastDays=1

fileLimit=1

file=settlement_detail_report_batch_4.csv

settleMode=queue

isMoveCompletedFile=0

Schedule and Files

Adyen

Runs 3 times a day

payments_account_report - nightly file, has the previous days transactions

settlement_detail_report_batch - weekly file every Friday, has the previous weeks transactions

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.

Amazon (DISCONTINUED)

No longer runs

settlement_data - weekly file on Tuesday

refund_data - only if there are refunds, not always present, weekly file on Tuesday

Braintree

Runs every night at 00:00 UTC

transactions, refunds, chargebacks stored in 1 json file but disbursement date for chargebacks may not be consistent with the other 2.

Dlocal

Runs every night at 00:20 UTC

files - nightly

Gravy

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.

Ingenico (DISCONTINUED)

No longer runs

wikimedia_report - nightly file, has previous days transactions

Paypal

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

Fundraiseup (DISCONTINUED)

Runs nightly at 01:00 UTC

Including new donations, new recurring, recurring cancellations, and refunds.

Payment Processor Specific Information

Adyen

Fundraising/Data and flow/PSP integrations/Adyen Checkout#Audits

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.

Dlocal (formerly Astropay)

Fundraising/Data and flow/PSP integrations/dLocal#Audits

Braintree

Using graphQL run and consume the report nightly

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

PayPal

PayPal uses a python script deployed in the fundraising tools repository: https://phabricator.wikimedia.org/diffusion/WFTO/browse/master/audit/paypal/

We currently use PayPal's Express Checkout integration, and classify those donations using gateway value 'paypal_ec'. However, we still have some legacy recurring donations from the old integration, and those are classified with gateway value 'paypal'.

Log

check audit parse log from frlog1002, located at /srv/archive/civi/process-control/<yyyymmdd>/<paymentMethod>_audit_parse-<yyyymmdd>-xxxxxx.log.civi1001.bz2

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. The solution is to 'manually' 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.