Fundraising tech/Queue message formats

Note: This documentation is being copied and updated bit by bit from wikitech.mediawiki.org but is not yet complete.

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

Overview
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, there is a message roadmap with plans to fix this.

Common fields
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: donations
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&mdash;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: recurring
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 fields

 * 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_payment
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_signup

 * 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&mdash;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_failed

 * failure_count
 * failiure_retry_date
 * failiure_retry_date

txn_type subscr_eot
No special fields

txn_type subscr_cancel

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

Queue: refund
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: pending
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-init
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-antifraud
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.