Fundraising tech/Queue message formats

This page documents the in-flight format for messages sent and received by fundraising components.

OverviewEdit

Incoming donations are sent between subsystems as a single packet of data, in a flat dictionary. We use JSON over the wire, and PHP arrays internally. Subscription creations and modifications, refunds, and unsubscribe requests are also sent using similar techniques, and are documented here as well.

We use message queues to decouple the CRM database from public payments servers, and to not block performance-sensitive tasks.

Legacy format notice: This is the current schema for our messages. Please do not make edits here unless they reflect the existing queue consumer code. Message formats have developed over time and suffer from inconsistency and accumulated cruft.

Common fieldsEdit

Some metadata is added to queue messages regardless of producer and consumer.

source_name 
freeform name of the application which generated this message
source_type 
category of application.
payments 
came in direct from donatewiki or a banner (payments.wikimedia.org)
listener 
real time messages sent by the payment processor, coming in via a IPN listener service.
audit 
created by the nightly reconciliation job
direct 
message was generated within our CRM system itself, for example a hand-entered donation or refund.
source_host
machine name where initial intake interfacing occurred.
source_run_id
process identifier of the generating code
source_version
revision level of the originating code
source_enqueued_time
unix timestamp encoding when the message was first added to a queue. This header is not updated during requeueing or other operations.

Queue: donationsEdit

This is the most common type of donation message. They land in the "donations" queue, and are processed by the DonationsQueueConsumer in the queue2civicrm module. The fields that are most commonly sent by payments-wiki are listed first (up through utm_source). Fields from supplemental_address_1 on down are handled in the donation import code, but are mostly only used by processes that call wmf_civicrm_import_message directly, not things that send to queues.

gateway
Gateway identifier string, e.g. "paypal" or "ingenico".
gateway_txn_id
Transaction ID string used by the gateway.
order_id (alias invoice_id)
An ID string generated on our side. Usually the contribution_trakcing_id plus a sequence number, separated by a '.' or a '-'. Stored in civicrm_contribution.invoice_id. Needs to be globally unique
contribution_tracking_id
associates a contribution with a row in drupal.contribution_tracking
gateway_session_id
A temporary session ID generated by the payment processor when we redirect a donor their way. FIXME: Sent by Ingenico Connect and PayPal Express Checkout to donations queue, but probably only needs to be sent to the pending queue for use by orphan rectifiers. Not stored in Civi.
completion_message_id
If a message does not contain enough information to import a donation into Civi, this property gives the ID of a message in the pending queue with the rest of the data. Currently used for AstroPay and Amazon IPN messages.
date
Time donation was received, in unix timestamp seconds since epoch. Stored in civicrm_contribution.receive_date
currency
(required) Original currency of transaction. Do not use the deprecated original_currency or original_gross fields, these are too confusing. We'll introduce "settled_currency", etc., when it becomes necessary to track FOREX across processor accounts.
gross
(required) Total amount of transaction, in original currency.
fee
(optional) Fees charged by the payment processor, in original currency. If unspecified, this will be assumed zero, or calculated from gross - net if available.
net
(optional) Amount after subtracting fees.
email
Donor's email. If unspecified, we may substitute with "nobody@wikimedia.org" for validation purposes. This default is stripped out again before storing to the database.
first_name
middle_name
last_name
street_address
city
state_province
postal_code
country
Billing or mailing address country—not necessarily the same as the contribution_tracking country of web origin.
user_ip
In dotted quad notation (paymentswiki is only available via ipv4)
payment_method
Primary payment method, e.g. "cc". TODO: enumerate.
payment_submethod
Payment method details, e.g. "visa", "mc". TODO: enumerate.
gateway_status
Raw gateway status code, if available.
opt_in
(optional) "0" or "1", saved to the Opt In custom field in the Communications group. When exporting to Silverpop, a "0" in opt_in will land the donor on the unsubscribe list. When not present or null, indicates that the donor has not been shown the opt_in choice.
language
Our best guess at the donor's preferred contact language.
recurring
"0" for one-time donations, "1" for recurring donations
recurring_payment_token
when sent with a recurring donation, this is stored in the civicrm_payment_token field to be used to charge future installments.
contact_id
When sent, this indicates we should use an existing CiviCRM contact for the new contribution (and update contact information from the message)
contact_hash
When sent, this provides a validity check for contact_id. The existing contact will only be updated if this value matches the civicrm_contact.hash. Note that payments-wiki should never send ID without hash.
utm_campaign
Mapped into the direct_mail_appeal custom field.
utm_medium
general type of referrer, used to update or create contribution tracking. Special value 'Endowment' also changes the financial type of the contribution to 'Endowment Gift'.
utm_source
often identifies the banner or email which inspired the donor to give, used to update or create contribution tracking
supplemental_address_1
Legacy address field
contact_type
(optional) "Organization" if you want to create an Org record in Civi. This is normally implicit, according to which name fields were filled out. Defaults to "Individual".
organization_name
(optional) If given in place of first/last_name, an Organization contact will be created rather than an Individual.
gift_source 
Maps to "Campaign" custom field
restrictions 
Maps to "Fund" custom field
import_batch_number
Conceptually broken, this should be renamed. It's a number internal to AZ Lockbox.
check_number 
anonymous
(legacy, if sent would just update contribution_tracking)
optout
(legacy, if sent would just update contribution_tracking)
contact_source
Maps to contact_source field in civicrm_contact, defaults to "online donation".
notes
Text blob will be stored as a CiviCRM note, associated with the contact. Can't find anything that sends this.

Queue: recurringEdit

This queue accepts both messages about individual payments that are part of a recurring series and messages about the start and stop of the recurring series itself. As of January 2020, we are considering moving notifications of individual payments to the donations queue.

Common fieldsEdit

txn_type
Indicates the subtype of message - values inherited from PayPal IPNs
subscr_payment 
An individual payment associated with a recurring series
subscr_signup
A new recurring series has started
subscr_failed 
An individual payment has failed but may be retried later
subscr_eot 
The recurring series has expired
subscr_cancel 
The recurring series has been canceled by the donor
subscr_modify 
Some detail of the recurring series has changed (currently unsupported)
subscr_id
Indicates which recurring series the message is about. Maps to civicrm_contribution_recur.trxn_id

txn_type subscr_paymentEdit

After being associated with a contact and a civicrm_contribution_recur via a lookup on subscr_id, these payments are imported using the same function as one-time payments (wmf_civicrm_contribution_message_import). Therefore, all fields under Queue: donations (above) are supported.

txn_type subscr_signupEdit

contribution_tracking_id
used to associate the subscription with an existing contact, usually in the case of donors who have made a one-time donation then added a recurring donation of a smaller amount.
email
Donor's email. If unspecified, we may substitute with "nobody@wikimedia.org" for validation purposes. This default is stripped out again before storing to the database.
first_name
middle_name
last_name
street_address
city
state_province
postal_code
country
Billing or mailing address country—not necessarily the same as the contribution_tracking country of web origin.
original_currency
FIXME why are we still using original_ prefixes here?
original_gross
FIXME too
frequency_unit
We only support "month"
frequency_interval
We only support "1"
installments
In theory this should support recurring series that expire after a fixed number of payments. In practice it seems to be 0.
start_date
Date on which the first payment in this series should be charged. Stored in civicrm_contribution_recur.start_date
create_date
Date on which the recurring series was set up. Stored in civicrm_contribution_recur.create_date
recurring_payment_token
If set, creates an entry in civicrm_payment_token to use for charging future payments
order_id
saved to civicrm_contribution_recur.invoice_id
user_ip
required if recurring_payment_token is given, stored in civicrm_payment_token table

txn_type subscr_failedEdit

failure_count
failiure_retry_date

txn_type subscr_eotEdit

No special fields

txn_type subscr_cancelEdit

cancel_date
Unix timestamp of effective date of series cancellation. Stored in cancel_date and end_date fields of civicrm_contribution_recur

Queue: refundEdit

Handles notifications for both refunds (initiated by us) and chargebacks (initiated by the donor).

gateway (required) 
our codename for the payment processor, same as in donations queue
type 
Refund subtype, one of (chargeback, refund)
date 
gross_currency 
gross 
total charged to us
net 
total refunded to donor (not yet implemented)
fee 
fee associated with refund or chargeback (may be high for chargebacks, not yet implemented)
gateway_parent_id 
Gateway transaction ID of the original transaction
gateway_refund_id 
Gateway transaction ID of the refund

Queue: pendingEdit

Sends donor information to a 'pending' table for short-term storage. The 'pending' table indexes by gateway, gateway_account, order_id and gateway_txn_id. Other fields are stored as a JSON blob. Besides the fields listed below, the fields in the 'donations' queue listed first (those sent from payments-wiki) are also sent to pending.

gateway (required) 
date (required) 
used to expire old pending data
gateway_txn_id 
Payment processor's ID for the donation. Note that this is only assigned before redirect for astropay.
order_id 
Our ID for the donation. One of (gateway_txn_id, order_id) is required

Queue: payments-initEdit

Sends basic contribution information to the payments_initial table in the fredge database.

contribution_tracking_id 
gateway 
order_id 
gateway_txn_id 
validation_action 
What we decided to do with the payment after examining it for fraud
challenge 
likely fraud? We haven't been using this
process 
go ahead and process payment
reject 
very likely fraud - cancel the payment
review 
potential fraud - leave for Donor Services to capture
payments_final_status 
How far the payment got through the machinery of the payment processor.
complete 
we're getting paid
cancelled 
donor backed out
failed 
card rejected or other failure
pending 
donor will pay later (bank transfer and some cash methods)
timeout 
TODO when do we use this?
payment_method 
payment_submethod 
country 
amount 
(in original currency)
currency_code 
server 
where the message came from
date 
Sent as UNIX timestamp, converted to SQL datetime for storage

Queue: payments-antifraudEdit

Sends fraud scores to the payments_fraud and payments_fraud_breakdown tables in fredge.

contribution_tracking_id 
gateway 
order_id 
validation_action 
(see above)
user_ip 
expects dotted quad ipv4, transformed with ip2long before being stored as varbinary(16)
payment_method 
risk_score 
total risk score for the attempt
server 
date 
Sent as UNIX timestamp, converted to SQL datetime for storage
score_breakdown 
A map of fraud filter names to individual risk scores. Each of these gets its own row in payments_fraud_breakdown.

Queue: unsubscribeEdit

The consumer for this queue sets is_opt_out on the contact attached to the indicated contribution AND all contacts matching the email address.

email 
required
contribution-id 
required, pk from civicrm_contribution table

Queue: opt-inEdit

The consumer for this queue can do more than just setting the `opt_in` field of the 'Communications' custom group - it can actually create new contacts or update names and mailing addresses of existing contacts. Thus in addition to the fields below, this queue can accept all contact and location related fields described in the Queue: donations section above.

email 
required
contact_id 
pk in civicrm_contact table
contact_hash 
must match `hash` column from row indicated by contact_id in order to update details of an existing contact
utm_source 
mapped to optin_source
utm_medium 
mapped to optin_medium
utm_campaign 
mapped to optin_campaign

Queue: banner-historyEdit

banner_history_id 
required, 10-20 digit hexadecimal string
contribution_tracking_id 
required, pk from drupal.contribution_tracking table

Job queuesEdit

Besides the gateway-specific jobs mentioned below, all job queues can accept a DeletePendingJob which looks like this

class 
'SmashPig\Core\Jobs\DeletePendingJob'
payload 
[ 'gateway' => (required), 'order_id' => (required) ]

Queue: jobs-adyenEdit

Jobs sent here are of type RunnableJob. The class is expected to have a fromJson static method that takes the full message.

php-message-class
Fully qualified name of Job class

Job class ProcessCaptureRequestJobEdit

Sent by the Adyen IPN listener in response to a successful Authorization IPN. When run, the job decides whether to capture or cancel the donation.

account 
which Adyen account was used
currency 
3 letter ISO code
amount 
merchantReference 
our invoice_id
pspReference 
aka the gateway_txn_id
avsResult 
1 or 2 digit code corresponding to different accuracy of address and name, or availability of AVS for the card
cvvResult 
1 digit code corresponding to correctness of CVV code or availability of CVV checks for the card

Job class DownloadReportJobEdit

Sent by the Adyen IPN listener in response to a ReportAvailable IPN message

gateway 
always 'adyen'
account 
which Adyen account was used
reportUrl 
where to download the report

Job class RecordCaptureJobEdit

Sent by the Adyen IPN listener in response to a successful Capture IPN message

account 
which Adyen account was used
currency 
3 letter ISO code
amount 
originalReference 
pspReference of the authorization, stored as gateway_txn_id
merchantReference 
our invoice_id

Queue: jobs-amazonEdit

When the Amazon IPN listener recieves a successful PaymentCapture message it sends this queue a RecordPaymentJob, which implements the simpler 'Runnable' interface.

class
'\SmashPig\PaymentProviders\Amazon\RecordPaymentJob'
payload
the decoded PaymentCapture message
order_id 
our invoice_id
contribution_tracking_id 
pk to drupal.contribution_tracking
date 
as in 'donations' queue
fee 
as in 'donations' queue
gateway_txn_id 
as in 'donations' queue
gateway_status 
as in 'donations' queue
order_reference_id 
Amazon's general ID for the donation or series.

Queue: jobs-paypalEdit

The IPN listener sends jobs implementing the 'RunnableJob' interface here with the raw IPN message included as the 'payload' field

php-message-class
'\SmashPig\PaymentProviders\PayPal\Job'
payload
see PayPal IPN documentation

Queue: jobs-ingenicoEdit

The Ingenico audit processor can send a TokenizeRecurringJob here to request tokenizing a recurring payment that we first see in the audit files.

class
'\SmashPig\PaymentProviders\Ingenico\TokenizeRecurringJob'
payload
The full donation details from the audit file, which can includes anything that gets sent to the 'donations' queue as described above